Skip to main content

snip_sync/
db.rs

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