1use argon2::{
2 Algorithm, Argon2, Params, Version,
3 password_hash::{PasswordHash, PasswordHasher, PasswordVerifier, SaltString},
4};
5use base64::Engine;
6use chrono::Utc;
7use sha2::{Digest, Sha256};
8use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
9use std::path::Path;
10use std::str::FromStr;
11use thiserror::Error;
12use uuid::Uuid;
13
14const ARGON2_MEMORY_KIB: u32 = 1 << 14; const ARGON2_ITERATIONS: u32 = 3;
16const ARGON2_PARALLELISM: u32 = 4;
17
18#[derive(Debug, Error)]
19pub enum DbError {
20 #[error("Database error: {0}")]
21 Database(#[from] sqlx::Error),
22 #[error("Not found: {0}")]
23 NotFound(String),
24 #[error("Conflict: {0}")]
25 Conflict(String),
26 #[error("Validation error: {0}")]
27 Validation(String),
28 #[error("Internal error: {0}")]
29 Internal(String),
30}
31
32pub type DbResult<T> = Result<T, DbError>;
33
34#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
35pub struct Snippet {
36 pub id: String,
37 pub description: String,
38 pub command: String,
39 pub tags: Vec<String>,
40 pub created_at: i64,
41 pub updated_at: i64,
42 pub device_id: String,
43 pub deleted: bool,
44 pub encrypted: bool,
45}
46
47#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
48pub struct Library {
49 pub id: String,
50 pub name: String,
51 pub created_at: i64,
52 pub snippet_count: i64,
53}
54
55#[derive(Clone)]
56pub struct Database {
57 pool: SqlitePool,
58}
59
60fn hash_api_key(api_key: &str) -> DbResult<String> {
61 let mut salt_bytes = [0u8; 16];
62 getrandom::fill(&mut salt_bytes)
63 .map_err(|e| DbError::Internal(format!("Failed to generate salt: {}", e)))?;
64 let salt_b64 = base64::engine::general_purpose::STANDARD_NO_PAD.encode(salt_bytes);
65 let salt = SaltString::from_b64(&salt_b64)
66 .map_err(|e| DbError::Internal(format!("Failed to create salt: {}", e)))?;
67 let params = Params::new(
68 ARGON2_MEMORY_KIB,
69 ARGON2_ITERATIONS,
70 ARGON2_PARALLELISM,
71 None,
72 )
73 .map_err(|e| DbError::Internal(format!("Invalid Argon2 params: {}", e)))?;
74 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
75 let hash = argon2
76 .hash_password(api_key.as_bytes(), &salt)
77 .map_err(|e| DbError::Internal(format!("Failed to hash API key: {}", e)))?
78 .to_string();
79 Ok(hash)
80}
81
82fn verify_api_key(api_key: &str, hash: &str) -> bool {
83 let parsed = match PasswordHash::new(hash) {
84 Ok(p) => p,
85 Err(_) => return false,
86 };
87 let params = match Params::new(
88 ARGON2_MEMORY_KIB,
89 ARGON2_ITERATIONS,
90 ARGON2_PARALLELISM,
91 None,
92 ) {
93 Ok(p) => p,
94 Err(_) => return false,
95 };
96 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
97 argon2.verify_password(api_key.as_bytes(), &parsed).is_ok()
98}
99
100fn compute_api_key_prefix(api_key: &str) -> String {
102 let hash = Sha256::digest(api_key.as_bytes());
103 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
104 encoded.chars().take(8).collect()
105}
106
107fn saturating_i32(v: i64) -> i32 {
108 v.min(i32::MAX as i64) as i32
109}
110
111type SnippetRow = (String, String, String, String, i64, i64, String, i32, i32);
112
113impl Database {
114 pub async fn connect(url: &str, max_connections: u32) -> DbResult<Self> {
115 let options = if url.starts_with("sqlite:") {
121 SqliteConnectOptions::from_str(url)?
122 } else {
123 SqliteConnectOptions::new().filename(url)
124 }
125 .create_if_missing(true);
126
127 let filename = options.get_filename();
128 if filename != Path::new(":memory:")
129 && let Some(parent) = filename.parent()
130 && !parent.as_os_str().is_empty()
131 {
132 std::fs::create_dir_all(parent).map_err(|e| {
133 DbError::Internal(format!(
134 "Failed to create database directory {}: {}",
135 parent.display(),
136 e
137 ))
138 })?;
139 }
140
141 let pool = SqlitePoolOptions::new()
142 .max_connections(max_connections)
143 .connect_with(options)
144 .await?;
145
146 sqlx::query("PRAGMA journal_mode=WAL")
147 .execute(&pool)
148 .await?;
149 sqlx::query("PRAGMA foreign_keys=ON").execute(&pool).await?;
150
151 sqlx::query(
152 "
153 CREATE TABLE IF NOT EXISTS users (
154 id TEXT PRIMARY KEY,
155 api_key TEXT UNIQUE NOT NULL,
156 api_key_prefix TEXT,
157 created_at INTEGER NOT NULL,
158 updated_at INTEGER NOT NULL
159 )
160 ",
161 )
162 .execute(&pool)
163 .await?;
164
165 sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_api_key_prefix ON users(api_key_prefix)")
166 .execute(&pool)
167 .await?;
168
169 sqlx::query(
170 "
171 CREATE TABLE IF NOT EXISTS libraries (
172 id TEXT PRIMARY KEY,
173 user_id TEXT NOT NULL,
174 name TEXT NOT NULL,
175 created_at INTEGER NOT NULL,
176 deleted_at INTEGER,
177 FOREIGN KEY (user_id) REFERENCES users(id),
178 UNIQUE(user_id, name)
179 )
180 ",
181 )
182 .execute(&pool)
183 .await?;
184
185 sqlx::query(
186 "
187 CREATE TABLE IF NOT EXISTS snippets (
188 id TEXT PRIMARY KEY,
189 user_id TEXT NOT NULL,
190 library_id TEXT NOT NULL,
191 description TEXT NOT NULL,
192 command TEXT NOT NULL,
193 tags TEXT NOT NULL DEFAULT '[]',
194 created_at INTEGER NOT NULL,
195 updated_at INTEGER NOT NULL,
196 device_id TEXT NOT NULL,
197 deleted INTEGER NOT NULL DEFAULT 0,
198 encrypted INTEGER NOT NULL DEFAULT 0,
199 FOREIGN KEY (user_id) REFERENCES users(id),
200 FOREIGN KEY (library_id) REFERENCES libraries(id)
201 )
202 ",
203 )
204 .execute(&pool)
205 .await?;
206
207 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_user ON snippets(user_id)")
208 .execute(&pool)
209 .await?;
210 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_library ON snippets(library_id)")
211 .execute(&pool)
212 .await?;
213 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_updated ON snippets(updated_at)")
214 .execute(&pool)
215 .await?;
216 sqlx::query(
217 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_updated \
218 ON snippets(user_id, library_id, updated_at, deleted)",
219 )
220 .execute(&pool)
221 .await?;
222 sqlx::query("CREATE INDEX IF NOT EXISTS idx_libraries_user ON libraries(user_id)")
223 .execute(&pool)
224 .await?;
225
226 Ok(Self { pool })
227 }
228
229 pub async fn ping(&self) -> DbResult<()> {
230 sqlx::query("SELECT 1").execute(&self.pool).await?;
231 Ok(())
232 }
233
234 pub fn pool(&self) -> &SqlitePool {
235 &self.pool
236 }
237
238 pub async fn create_user(&self, api_key: &str) -> DbResult<String> {
239 let user_id = Uuid::new_v4().to_string();
240 let now = Utc::now().timestamp();
241 let api_key_hash = hash_api_key(api_key)?;
242 let api_key_prefix = compute_api_key_prefix(api_key);
243
244 let mut tx = self.pool.begin().await?;
245
246 sqlx::query("INSERT INTO users (id, api_key, api_key_prefix, created_at, updated_at) VALUES (?, ?, ?, ?, ?)")
247 .bind(&user_id)
248 .bind(&api_key_hash)
249 .bind(&api_key_prefix)
250 .bind(now)
251 .bind(now)
252 .execute(&mut *tx)
253 .await?;
254
255 let default_lib_id = Uuid::new_v4().to_string();
256 sqlx::query("INSERT INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)")
257 .bind(&default_lib_id)
258 .bind(&user_id)
259 .bind("default")
260 .bind(now)
261 .execute(&mut *tx)
262 .await?;
263
264 tx.commit().await?;
265
266 tracing::info!(
267 "Created user {} with default library {}",
268 user_id,
269 default_lib_id
270 );
271
272 Ok(user_id)
273 }
274
275 pub async fn get_user_by_api_key(&self, api_key: &str) -> DbResult<Option<String>> {
276 let prefix = compute_api_key_prefix(api_key);
277
278 let rows: Vec<(String, String)> = sqlx::query_as(
282 "SELECT id, api_key FROM users WHERE api_key_prefix = ? OR api_key_prefix IS NULL",
283 )
284 .bind(&prefix)
285 .fetch_all(&self.pool)
286 .await?;
287
288 for (user_id, stored_hash) in rows {
289 if verify_api_key(api_key, &stored_hash) {
290 return Ok(Some(user_id));
291 }
292 }
293
294 Ok(None)
295 }
296
297 pub async fn create_library(&self, user_id: &str, name: &str) -> DbResult<String> {
298 if !name
299 .chars()
300 .all(|c| c.is_alphanumeric() || c == '-' || c == '_')
301 {
302 return Err(DbError::Validation(
303 "Invalid library name. Use only letters, numbers, dash, and underscore."
304 .to_string(),
305 ));
306 }
307 if name.is_empty() || name.len() > 64 {
308 return Err(DbError::Validation(
309 "Library name must be 1-64 characters".to_string(),
310 ));
311 }
312
313 let lib_id = Uuid::new_v4().to_string();
314 let now = Utc::now().timestamp();
315
316 let result = sqlx::query(
317 "INSERT OR IGNORE INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)",
318 )
319 .bind(&lib_id)
320 .bind(user_id)
321 .bind(name)
322 .bind(now)
323 .execute(&self.pool)
324 .await?;
325
326 if result.rows_affected() == 0 {
327 return Err(DbError::Conflict(format!(
328 "Library '{}' already exists",
329 name
330 )));
331 }
332
333 Ok(lib_id)
334 }
335
336 pub async fn list_libraries(
337 &self,
338 user_id: &str,
339 limit: i32,
340 offset: i32,
341 ) -> DbResult<(Vec<Library>, i32)> {
342 let total: (i64,) = sqlx::query_as(
343 "SELECT COUNT(*) FROM libraries WHERE user_id = ? AND deleted_at IS NULL",
344 )
345 .bind(user_id)
346 .fetch_one(&self.pool)
347 .await?;
348
349 let rows: Vec<(String, String, i64, i64)> = sqlx::query_as(
350 "SELECT l.id, l.name, l.created_at, COUNT(s.id) as snippet_count \
351 FROM libraries l \
352 LEFT JOIN snippets s ON s.library_id = l.id AND s.deleted = 0 \
353 WHERE l.user_id = ? AND l.deleted_at IS NULL \
354 GROUP BY l.id \
355 ORDER BY l.name LIMIT ? OFFSET ?",
356 )
357 .bind(user_id)
358 .bind(limit)
359 .bind(offset)
360 .fetch_all(&self.pool)
361 .await?;
362
363 let libraries = rows
364 .into_iter()
365 .map(|(id, name, created_at, snippet_count)| Library {
366 id,
367 name,
368 created_at,
369 snippet_count,
370 })
371 .collect();
372
373 Ok((libraries, saturating_i32(total.0)))
374 }
375
376 pub async fn delete_library(&self, user_id: &str, library_id: &str) -> DbResult<()> {
377 let now = Utc::now().timestamp();
378 let mut tx = self.pool.begin().await?;
379
380 let result = sqlx::query(
381 "UPDATE libraries SET deleted_at = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
382 )
383 .bind(now)
384 .bind(library_id)
385 .bind(user_id)
386 .execute(&mut *tx)
387 .await?;
388
389 if result.rows_affected() == 0 {
390 tx.rollback().await?;
391 return Err(DbError::NotFound(
392 "Library not found or already deleted".to_string(),
393 ));
394 }
395
396 sqlx::query(
397 "UPDATE snippets
398 SET deleted = 1, updated_at = MAX(updated_at, ?)
399 WHERE library_id = ? AND user_id = ? AND deleted = 0",
400 )
401 .bind(now)
402 .bind(library_id)
403 .bind(user_id)
404 .execute(&mut *tx)
405 .await?;
406
407 tx.commit().await?;
408 Ok(())
409 }
410
411 pub async fn get_default_library(&self, user_id: &str) -> DbResult<String> {
412 let (lib_id,): (String,) = sqlx::query_as(
413 "SELECT id FROM libraries WHERE user_id = ? AND name = 'default' AND deleted_at IS NULL",
414 )
415 .bind(user_id)
416 .fetch_optional(&self.pool)
417 .await?
418 .ok_or_else(|| DbError::NotFound("Default library not found".to_string()))?;
419
420 Ok(lib_id)
421 }
422
423 pub async fn verify_library_ownership(
424 &self,
425 user_id: &str,
426 library_id: &str,
427 ) -> DbResult<bool> {
428 let (count,): (i64,) = sqlx::query_as(
429 "SELECT COUNT(*) FROM libraries WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
430 )
431 .bind(library_id)
432 .bind(user_id)
433 .fetch_one(&self.pool)
434 .await?;
435
436 Ok(count > 0)
437 }
438
439 pub async fn get_snippets(
440 &self,
441 user_id: &str,
442 library_id: &str,
443 since: i64,
444 limit: i32,
445 offset: i32,
446 include_deleted: bool,
447 ) -> DbResult<(Vec<Snippet>, i32)> {
448 let total: (i64,) = sqlx::query_as(
449 "SELECT COUNT(*) FROM snippets \
450 WHERE user_id = ? \
451 AND library_id = ? \
452 AND updated_at > ? \
453 AND (? OR deleted = 0)",
454 )
455 .bind(user_id)
456 .bind(library_id)
457 .bind(since)
458 .bind(include_deleted)
459 .fetch_one(&self.pool)
460 .await?;
461
462 let rows: Vec<SnippetRow> = sqlx::query_as(
463 "SELECT id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted \
464 FROM snippets \
465 WHERE user_id = ? \
466 AND library_id = ? \
467 AND updated_at > ? \
468 AND (? OR deleted = 0) \
469 ORDER BY updated_at DESC, id DESC \
470 LIMIT ? OFFSET ?",
471 )
472 .bind(user_id)
473 .bind(library_id)
474 .bind(since)
475 .bind(include_deleted)
476 .bind(limit)
477 .bind(offset)
478 .fetch_all(&self.pool)
479 .await?;
480
481 let snippets = rows
482 .into_iter()
483 .map(
484 |(
485 id,
486 description,
487 command,
488 tags_str,
489 created_at,
490 updated_at,
491 device_id,
492 deleted,
493 encrypted,
494 )| {
495 let tags: Vec<String> = serde_json::from_str(&tags_str).inspect_err(|e| {
496 tracing::warn!(snippet_id = %id, error = %e, "Failed to parse tags JSON, using empty list");
497 }).unwrap_or_default();
498 Snippet {
499 id,
500 description,
501 command,
502 tags,
503 created_at,
504 updated_at,
505 device_id,
506 deleted: deleted != 0,
507 encrypted: encrypted != 0,
508 }
509 },
510 )
511 .collect();
512
513 Ok((snippets, saturating_i32(total.0)))
514 }
515
516 #[cfg(test)]
517 pub async fn upsert_snippet(
518 &self,
519 snippet: &Snippet,
520 user_id: &str,
521 library_id: &str,
522 ) -> DbResult<()> {
523 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
524 let deleted = snippet.deleted as i32;
525 let encrypted = snippet.encrypted as i32;
526
527 sqlx::query(
528 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
529 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
530 ON CONFLICT(id) DO UPDATE SET
531 description = excluded.description,
532 command = excluded.command,
533 tags = excluded.tags,
534 updated_at = excluded.updated_at,
535 device_id = excluded.device_id,
536 deleted = excluded.deleted,
537 encrypted = excluded.encrypted
538 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
539 AND (excluded.updated_at > snippets.updated_at \
540 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id))",
541 )
542 .bind(&snippet.id)
543 .bind(user_id)
544 .bind(library_id)
545 .bind(&snippet.description)
546 .bind(&snippet.command)
547 .bind(&tags_json)
548 .bind(snippet.created_at)
549 .bind(snippet.updated_at)
550 .bind(&snippet.device_id)
551 .bind(deleted)
552 .bind(encrypted)
553 .execute(&self.pool)
554 .await?;
555
556 Ok(())
557 }
558
559 pub async fn upsert_snippet_in_tx(
560 &self,
561 tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
562 snippet: &Snippet,
563 user_id: &str,
564 library_id: &str,
565 ) -> DbResult<()> {
566 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
567 let deleted = snippet.deleted as i32;
568 let encrypted = snippet.encrypted as i32;
569
570 sqlx::query(
574 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
575 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
576 ON CONFLICT(id) DO UPDATE SET
577 description = excluded.description,
578 command = excluded.command,
579 tags = excluded.tags,
580 updated_at = excluded.updated_at,
581 device_id = excluded.device_id,
582 deleted = excluded.deleted,
583 encrypted = excluded.encrypted
584 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
585 AND (excluded.updated_at > snippets.updated_at \
586 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id))",
587 )
588 .bind(&snippet.id)
589 .bind(user_id)
590 .bind(library_id)
591 .bind(&snippet.description)
592 .bind(&snippet.command)
593 .bind(&tags_json)
594 .bind(snippet.created_at)
595 .bind(snippet.updated_at)
596 .bind(&snippet.device_id)
597 .bind(deleted)
598 .bind(encrypted)
599 .execute(&mut **tx)
600 .await?;
601
602 Ok(())
603 }
604
605 pub async fn get_latest_timestamp(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
606 let (timestamp,): (i64,) = sqlx::query_as(
607 "SELECT COALESCE(MAX(updated_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
608 )
609 .bind(user_id)
610 .bind(library_id)
611 .fetch_one(&self.pool)
612 .await?;
613
614 Ok(timestamp)
615 }
616
617 pub async fn migrate_plaintext_api_keys(&self) -> DbResult<usize> {
618 let rows: Vec<(String, String, Option<String>)> =
619 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
620 .fetch_all(&self.pool)
621 .await?;
622
623 let mut migrated = 0;
624
625 for (user_id, stored, prefix) in rows {
626 let mut needs_update = false;
627 let mut new_hash = stored.clone();
628 let mut new_prefix = prefix.clone();
629
630 if !stored.starts_with("$argon2") {
632 new_hash = hash_api_key(&stored)?;
633 needs_update = true;
634 }
635
636 if prefix.is_none() {
638 if !stored.starts_with("$argon2") {
642 new_prefix = Some(compute_api_key_prefix(&stored));
643 needs_update = true;
644 }
645 }
646
647 if needs_update {
648 sqlx::query("UPDATE users SET api_key = ?, api_key_prefix = ? WHERE id = ?")
649 .bind(&new_hash)
650 .bind(&new_prefix)
651 .bind(&user_id)
652 .execute(&self.pool)
653 .await?;
654 migrated += 1;
655 }
656 }
657
658 if migrated > 0 {
659 tracing::info!("Migrated {} API keys (hash or prefix update)", migrated);
660 }
661
662 Ok(migrated)
663 }
664}
665
666#[cfg(test)]
667mod tests {
668 use super::*;
669
670 async fn setup_db() -> Database {
671 Database::connect("sqlite::memory:", 5).await.unwrap()
672 }
673
674 #[tokio::test]
675 async fn test_file_database_is_created_from_path() {
676 let temp = tempfile::tempdir().unwrap();
677 let path = temp.path().join("nested").join("snippets.db");
678
679 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
680
681 assert!(path.exists());
682 }
683
684 #[tokio::test]
685 async fn test_create_and_verify_user() {
686 let db = setup_db().await;
687 let api_key = "test-api-key-12345";
688
689 let user_id = db.create_user(api_key).await.unwrap();
690 assert!(!user_id.is_empty());
691
692 let found = db.get_user_by_api_key(api_key).await.unwrap();
694 assert_eq!(found, Some(user_id));
695
696 let not_found = db.get_user_by_api_key("wrong-key").await.unwrap();
698 assert_eq!(not_found, None);
699 }
700
701 #[tokio::test]
702 async fn test_api_key_is_hashed() {
703 let db = setup_db().await;
704 let api_key = "plaintext-key-should-be-hashed";
705
706 let _user_id = db.create_user(api_key).await.unwrap();
707
708 let rows: Vec<(String, String, Option<String>)> =
710 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
711 .fetch_all(&db.pool)
712 .await
713 .unwrap();
714
715 assert_eq!(rows.len(), 1);
716 assert!(
717 rows[0].1.starts_with("$argon2"),
718 "API key should be stored as Argon2 hash"
719 );
720 assert!(rows[0].2.is_some(), "API key prefix should be stored");
722 assert_eq!(rows[0].2.as_ref().unwrap().len(), 8);
723 }
724
725 #[tokio::test]
726 async fn test_create_default_library() {
727 let db = setup_db().await;
728 let user_id = db.create_user("key").await.unwrap();
729
730 let lib_id = db.get_default_library(&user_id).await.unwrap();
731 assert!(!lib_id.is_empty());
732
733 let (libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
734 assert_eq!(total, 1);
735 assert_eq!(libraries[0].name, "default");
736 }
737
738 #[tokio::test]
739 async fn test_create_additional_library() {
740 let db = setup_db().await;
741 let user_id = db.create_user("key").await.unwrap();
742
743 let lib_id = db.create_library(&user_id, "work").await.unwrap();
744 assert!(!lib_id.is_empty());
745
746 let (_libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
747 assert_eq!(total, 2);
748
749 let err = db.create_library(&user_id, "work").await.unwrap_err();
751 assert!(matches!(err, DbError::Conflict(_)));
752 }
753
754 #[tokio::test]
755 async fn test_invalid_library_names() {
756 let db = setup_db().await;
757 let user_id = db.create_user("key").await.unwrap();
758
759 assert!(db.create_library(&user_id, "").await.is_err());
761
762 assert!(db.create_library(&user_id, "my/lib").await.is_err());
764
765 assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
767
768 assert!(db.create_library(&user_id, "valid-name").await.is_ok());
770 assert!(db.create_library(&user_id, "valid_name").await.is_ok());
771 assert!(db.create_library(&user_id, "ValidName123").await.is_ok());
772 }
773
774 #[tokio::test]
775 async fn test_upsert_and_get_snippets() {
776 let db = setup_db().await;
777 let user_id = db.create_user("key").await.unwrap();
778 let lib_id = db.get_default_library(&user_id).await.unwrap();
779
780 let snippet = Snippet {
781 id: "snip-1".to_string(),
782 description: "Test snippet".to_string(),
783 command: "echo hello".to_string(),
784 tags: vec!["test".to_string()],
785 created_at: 100,
786 updated_at: 100,
787 device_id: "device-1".to_string(),
788 deleted: false,
789 encrypted: false,
790 };
791
792 db.upsert_snippet(&snippet, &user_id, &lib_id)
793 .await
794 .unwrap();
795
796 let (snippets, total) = db
797 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
798 .await
799 .unwrap();
800 assert_eq!(total, 1);
801 assert_eq!(snippets[0].description, "Test snippet");
802 assert_eq!(snippets[0].command, "echo hello");
803 assert_eq!(snippets[0].tags, vec!["test"]);
804 }
805
806 #[tokio::test]
807 async fn test_delete_library_preserves_future_tombstone_timestamp() {
808 let db = setup_db().await;
809 let user_id = db.create_user("key").await.unwrap();
810 let library_id = db.create_library(&user_id, "future-delete").await.unwrap();
811 let future_timestamp = Utc::now().timestamp() + 100;
812
813 let snippet = Snippet {
814 id: "future-snippet".to_string(),
815 description: "desc".to_string(),
816 command: "echo hi".to_string(),
817 tags: vec![],
818 created_at: 0,
819 updated_at: future_timestamp,
820 device_id: "device".to_string(),
821 deleted: false,
822 encrypted: false,
823 };
824 db.upsert_snippet(&snippet, &user_id, &library_id)
825 .await
826 .unwrap();
827
828 db.delete_library(&user_id, &library_id).await.unwrap();
829
830 let (snippets, total) = db
831 .get_snippets(&user_id, &library_id, future_timestamp - 1, 10, 0, true)
832 .await
833 .unwrap();
834 assert_eq!(total, 1);
835 assert!(snippets[0].deleted);
836 assert_eq!(snippets[0].updated_at, future_timestamp);
837 }
838
839 #[tokio::test]
840 async fn test_upsert_updates_existing() {
841 let db = setup_db().await;
842 let user_id = db.create_user("key").await.unwrap();
843 let lib_id = db.get_default_library(&user_id).await.unwrap();
844
845 let snippet = Snippet {
846 id: "snip-1".to_string(),
847 description: "Original".to_string(),
848 command: "echo old".to_string(),
849 tags: vec![],
850 created_at: 100,
851 updated_at: 100,
852 device_id: "device-1".to_string(),
853 deleted: false,
854 encrypted: false,
855 };
856 db.upsert_snippet(&snippet, &user_id, &lib_id)
857 .await
858 .unwrap();
859
860 let updated = Snippet {
862 id: "snip-1".to_string(),
863 description: "Updated".to_string(),
864 command: "echo new".to_string(),
865 tags: vec!["updated".to_string()],
866 created_at: 100,
867 updated_at: 200,
868 device_id: "device-1".to_string(),
869 deleted: false,
870 encrypted: false,
871 };
872 db.upsert_snippet(&updated, &user_id, &lib_id)
873 .await
874 .unwrap();
875
876 let (snippets, _) = db
877 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
878 .await
879 .unwrap();
880 assert_eq!(snippets.len(), 1);
881 assert_eq!(snippets[0].description, "Updated");
882 assert_eq!(snippets[0].command, "echo new");
883 }
884
885 #[tokio::test]
886 async fn test_upsert_rejects_older_timestamp() {
887 let db = setup_db().await;
888 let user_id = db.create_user("key").await.unwrap();
889 let lib_id = db.get_default_library(&user_id).await.unwrap();
890
891 let snippet = Snippet {
893 id: "snip-1".to_string(),
894 description: "Newer".to_string(),
895 command: "echo newer".to_string(),
896 tags: vec![],
897 created_at: 100,
898 updated_at: 200,
899 device_id: "d".to_string(),
900 deleted: false,
901 encrypted: false,
902 };
903 db.upsert_snippet(&snippet, &user_id, &lib_id)
904 .await
905 .unwrap();
906
907 let older = Snippet {
909 id: "snip-1".to_string(),
910 description: "Older".to_string(),
911 command: "echo older".to_string(),
912 tags: vec![],
913 created_at: 100,
914 updated_at: 100,
915 device_id: "d".to_string(),
916 deleted: false,
917 encrypted: false,
918 };
919 db.upsert_snippet(&older, &user_id, &lib_id).await.unwrap();
920
921 let (snippets, _) = db
923 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
924 .await
925 .unwrap();
926 assert_eq!(snippets.len(), 1);
927 assert_eq!(snippets[0].description, "Newer");
928 assert_eq!(snippets[0].command, "echo newer");
929 }
930
931 #[tokio::test]
932 async fn test_get_latest_timestamp() {
933 let db = setup_db().await;
934 let user_id = db.create_user("key").await.unwrap();
935 let lib_id = db.get_default_library(&user_id).await.unwrap();
936
937 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
939 assert_eq!(ts, 0);
940
941 let snippet = Snippet {
942 id: "snip-1".to_string(),
943 description: "Test".to_string(),
944 command: "echo".to_string(),
945 tags: vec![],
946 created_at: 100,
947 updated_at: 500,
948 device_id: "d".to_string(),
949 deleted: false,
950 encrypted: false,
951 };
952 db.upsert_snippet(&snippet, &user_id, &lib_id)
953 .await
954 .unwrap();
955
956 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
957 assert_eq!(ts, 500);
958 }
959
960 #[tokio::test]
961 async fn test_delete_library() {
962 let db = setup_db().await;
963 let user_id = db.create_user("key").await.unwrap();
964 let lib_id = db.create_library(&user_id, "temp").await.unwrap();
965
966 let snippet = Snippet {
968 id: "snip-1".to_string(),
969 description: "Test".to_string(),
970 command: "echo".to_string(),
971 tags: vec![],
972 created_at: 100,
973 updated_at: 100,
974 device_id: "d".to_string(),
975 deleted: false,
976 encrypted: false,
977 };
978 db.upsert_snippet(&snippet, &user_id, &lib_id)
979 .await
980 .unwrap();
981
982 db.delete_library(&user_id, &lib_id).await.unwrap();
984
985 assert!(db.delete_library(&user_id, &lib_id).await.is_err());
987
988 let (snippets, _) = db
990 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
991 .await
992 .unwrap();
993 assert_eq!(snippets.len(), 0);
994 }
995
996 #[tokio::test]
997 async fn test_migrate_plaintext_api_keys() {
998 let db = setup_db().await;
999 let user_id = Uuid::new_v4().to_string();
1000 let now = Utc::now().timestamp();
1001
1002 sqlx::query("INSERT INTO users (id, api_key, created_at, updated_at) VALUES (?, ?, ?, ?)")
1004 .bind(&user_id)
1005 .bind("plaintext-api-key")
1006 .bind(now)
1007 .bind(now)
1008 .execute(&db.pool)
1009 .await
1010 .unwrap();
1011
1012 let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1014 assert_eq!(migrated, 1);
1015
1016 let (stored_hash,): (String,) = sqlx::query_as("SELECT api_key FROM users WHERE id = ?")
1018 .bind(&user_id)
1019 .fetch_one(&db.pool)
1020 .await
1021 .unwrap();
1022
1023 assert!(stored_hash.starts_with("$argon2"));
1024
1025 let (prefix,): (Option<String>,) =
1027 sqlx::query_as("SELECT api_key_prefix FROM users WHERE id = ?")
1028 .bind(&user_id)
1029 .fetch_one(&db.pool)
1030 .await
1031 .unwrap();
1032 assert!(
1033 prefix.is_some(),
1034 "API key prefix should be backfilled by migration"
1035 );
1036
1037 let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1039 assert_eq!(found, Some(user_id));
1040
1041 let migrated_again = db.migrate_plaintext_api_keys().await.unwrap();
1043 assert_eq!(migrated_again, 0);
1044 }
1045
1046 #[tokio::test]
1047 async fn test_api_key_prefix_lookup() {
1048 let db = setup_db().await;
1049
1050 let user1 = db.create_user("key-one-alpha").await.unwrap();
1052 let user2 = db.create_user("key-two-beta").await.unwrap();
1053 let user3 = db.create_user("key-three-gamma").await.unwrap();
1054
1055 assert_eq!(
1057 db.get_user_by_api_key("key-one-alpha").await.unwrap(),
1058 Some(user1)
1059 );
1060 assert_eq!(
1061 db.get_user_by_api_key("key-two-beta").await.unwrap(),
1062 Some(user2)
1063 );
1064 assert_eq!(
1065 db.get_user_by_api_key("key-three-gamma").await.unwrap(),
1066 Some(user3)
1067 );
1068 assert_eq!(db.get_user_by_api_key("nonexistent").await.unwrap(), None);
1069
1070 let rows: Vec<(String,)> =
1072 sqlx::query_as("SELECT api_key_prefix FROM users WHERE api_key_prefix IS NOT NULL")
1073 .fetch_all(&db.pool)
1074 .await
1075 .unwrap();
1076 assert_eq!(rows.len(), 3);
1077 }
1078
1079 #[tokio::test]
1080 async fn test_library_ownership_verification() {
1081 let db = setup_db().await;
1082 let user1_id = db.create_user("user1-api-key").await.unwrap();
1083 let user2_id = db.create_user("user2-api-key").await.unwrap();
1084 let lib_id = db.create_library(&user1_id, "private").await.unwrap();
1085
1086 assert!(
1087 db.verify_library_ownership(&user1_id, &lib_id)
1088 .await
1089 .unwrap()
1090 );
1091 assert!(
1092 !db.verify_library_ownership(&user2_id, &lib_id)
1093 .await
1094 .unwrap()
1095 );
1096
1097 let nonexistent_lib = Uuid::new_v4().to_string();
1098 assert!(
1099 !db.verify_library_ownership(&user1_id, &nonexistent_lib)
1100 .await
1101 .unwrap()
1102 );
1103 }
1104
1105 #[tokio::test]
1106 async fn test_library_ownership_prevents_cross_user_access() {
1107 let db = setup_db().await;
1108 let user1_id = db.create_user("user1-key").await.unwrap();
1109 let user2_id = db.create_user("user2-key").await.unwrap();
1110 let user1_lib = db.create_library(&user1_id, "my-library").await.unwrap();
1111
1112 let snippet = Snippet {
1113 id: "snip-1".to_string(),
1114 description: "User 1 secret".to_string(),
1115 command: "secret command".to_string(),
1116 tags: vec![],
1117 created_at: 100,
1118 updated_at: 100,
1119 device_id: "device-1".to_string(),
1120 deleted: false,
1121 encrypted: false,
1122 };
1123
1124 db.upsert_snippet(&snippet, &user1_id, &user1_lib)
1125 .await
1126 .unwrap();
1127
1128 let (snippets, _) = db
1129 .get_snippets(&user1_id, &user1_lib, 0, 10, 0, false)
1130 .await
1131 .unwrap();
1132 assert_eq!(snippets.len(), 1);
1133
1134 let (snippets, _) = db
1135 .get_snippets(&user2_id, &user1_lib, 0, 10, 0, false)
1136 .await
1137 .unwrap();
1138 assert_eq!(snippets.len(), 0);
1139 }
1140
1141 #[tokio::test]
1142 async fn test_deleted_library_not_owned() {
1143 let db = setup_db().await;
1144 let user_id = db.create_user("test-key").await.unwrap();
1145 let lib_id = db.create_library(&user_id, "temp-lib").await.unwrap();
1146
1147 assert!(
1148 db.verify_library_ownership(&user_id, &lib_id)
1149 .await
1150 .unwrap()
1151 );
1152
1153 db.delete_library(&user_id, &lib_id).await.unwrap();
1154
1155 assert!(
1156 !db.verify_library_ownership(&user_id, &lib_id)
1157 .await
1158 .unwrap()
1159 );
1160 }
1161
1162 #[tokio::test]
1163 async fn test_get_snippets_include_deleted() {
1164 let db = setup_db().await;
1165 let user_id = db.create_user("key").await.unwrap();
1166 let lib_id = db.get_default_library(&user_id).await.unwrap();
1167
1168 let snippet = Snippet {
1169 id: "snip-1".to_string(),
1170 description: "Test".to_string(),
1171 command: "echo".to_string(),
1172 tags: vec![],
1173 created_at: 100,
1174 updated_at: 100,
1175 device_id: "d".to_string(),
1176 deleted: false,
1177 encrypted: false,
1178 };
1179 db.upsert_snippet(&snippet, &user_id, &lib_id)
1180 .await
1181 .unwrap();
1182
1183 let deleted = Snippet {
1185 id: "snip-1".to_string(),
1186 description: "Test".to_string(),
1187 command: "echo".to_string(),
1188 tags: vec![],
1189 created_at: 100,
1190 updated_at: 200,
1191 device_id: "d".to_string(),
1192 deleted: true,
1193 encrypted: false,
1194 };
1195 db.upsert_snippet(&deleted, &user_id, &lib_id)
1196 .await
1197 .unwrap();
1198
1199 let (snippets, _) = db
1201 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1202 .await
1203 .unwrap();
1204 assert_eq!(snippets.len(), 0);
1205
1206 let (snippets, _) = db
1208 .get_snippets(&user_id, &lib_id, 0, 10, 0, true)
1209 .await
1210 .unwrap();
1211 assert_eq!(snippets.len(), 1);
1212 assert!(snippets[0].deleted);
1213 assert_eq!(snippets[0].updated_at, 200);
1214 }
1215}