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
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        // The public configuration uses a SQLite file path for convenience,
116        // while tests and advanced deployments may provide a full sqlite URL.
117        // Sqlx's generic `Pool::connect` leaves file creation disabled, which
118        // makes a fresh `snip-sync init` fail on its first start. Normalize
119        // both forms and explicitly create the file when needed.
120        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        // Use prefix to narrow candidate set; prefix may be NULL for legacy rows
279        // that were already hashed before the prefix optimization was added.
280        // The IS NULL fallback ensures these users can still authenticate.
281        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        // Upsert with last-write-wins conflict resolution.
571        // Tie-breaking uses device_id string comparison, which is correct for
572        // lowercase hex UUIDs (lexicographic == numeric for lowercase hex).
573        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            // Migrate plaintext to hashed
631            if !stored.starts_with("$argon2") {
632                new_hash = hash_api_key(&stored)?;
633                needs_update = true;
634            }
635
636            // Backfill prefix if missing
637            if prefix.is_none() {
638                // For plaintext keys, prefix from plaintext; for hashed keys, we can't
639                // compute prefix without the original key, so we skip those — they'll
640                // still work via the `IS NULL` fallback in get_user_by_api_key.
641                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        // Should find user with correct API key
693        let found = db.get_user_by_api_key(api_key).await.unwrap();
694        assert_eq!(found, Some(user_id));
695
696        // Should not find user with wrong API key
697        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        // Verify the stored key is an Argon2 hash, not plaintext
709        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        // Verify prefix is stored
721        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        // Duplicate should fail with Conflict
750        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        // Empty name
760        assert!(db.create_library(&user_id, "").await.is_err());
761
762        // Name with slash
763        assert!(db.create_library(&user_id, "my/lib").await.is_err());
764
765        // Name too long
766        assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
767
768        // Valid names
769        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        // Update with newer timestamp
861        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        // Insert snippet at updated_at: 200
892        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        // Attempt to upsert same ID with older timestamp (should be rejected)
908        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        // Newer data should be preserved
922        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        // Empty library should return 0
938        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        // Add a snippet
967        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        // Delete the library
983        db.delete_library(&user_id, &lib_id).await.unwrap();
984
985        // Should fail to delete again
986        assert!(db.delete_library(&user_id, &lib_id).await.is_err());
987
988        // Snippet should be marked deleted
989        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        // Insert a plaintext API key directly (simulating old schema)
1003        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        // Run migration
1013        let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1014        assert_eq!(migrated, 1);
1015
1016        // Verify the key is now hashed
1017        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        // Verify prefix was backfilled
1026        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        // Verify the original key still works
1038        let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1039        assert_eq!(found, Some(user_id));
1040
1041        // Running migration again should migrate 0
1042        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        // Create multiple users
1051        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        // Each user's key should resolve correctly
1056        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        // Verify all rows have prefixes
1071        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        // Soft-delete the snippet
1184        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        // Without include_deleted: should not see deleted snippet
1200        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        // With include_deleted: should see deleted snippet
1207        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}