Skip to main content

snip_sync/
db.rs

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