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