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