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