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 sha2::{Digest, Sha256};
7use sqlx::AssertSqlSafe;
8use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
9use std::path::{Path, PathBuf};
10use std::str::FromStr;
11use std::time::Duration;
12use thiserror::Error;
13use time::OffsetDateTime;
14use uuid::Uuid;
15
16const ARGON2_MEMORY_KIB: u32 = 1 << 14; // 16 MiB — OWASP minimum for Argon2id
17const ARGON2_ITERATIONS: u32 = 3;
18const ARGON2_PARALLELISM: u32 = 4;
19
20#[derive(Debug, Error)]
21pub enum DbError {
22    #[error("Database error: {0}")]
23    Database(#[from] sqlx::Error),
24    #[error("Not found: {0}")]
25    NotFound(String),
26    #[error("Conflict: {0}")]
27    Conflict(String),
28    #[error("Validation error: {0}")]
29    Validation(String),
30    #[error("Internal error: {0}")]
31    Internal(String),
32}
33
34pub type DbResult<T> = Result<T, DbError>;
35
36/// Result of [`Database::upsert_snippet_in_tx`].
37///
38/// `snippets.id` is a *global* primary key, so an insert that collides with a
39/// row owned by a different user or library cannot be written at all. SQLite
40/// reports a false `WHERE` on `DO UPDATE` as a silent no-op (0 rows affected,
41/// no error), which is why the outcome has to be classified explicitly instead
42/// of treating "no rows affected" as success.
43#[derive(Debug, Clone, Copy, PartialEq, Eq)]
44pub enum UpsertOutcome {
45    /// The row was inserted or updated.
46    Written,
47    /// A newer version of the same record already exists under the same user
48    /// and library; last-write-wins correctly kept it.
49    Stale,
50    /// A row with the same id already belongs to a different user and/or
51    /// library. The write was discarded — reporting this as `Stale` would
52    /// silently lose the snippet.
53    ForeignScope,
54}
55
56#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
57pub struct Snippet {
58    pub id: String,
59    pub description: String,
60    pub command: String,
61    pub tags: Vec<String>,
62    pub created_at: i64,
63    pub updated_at: i64,
64    pub device_id: String,
65    pub deleted: bool,
66    pub encrypted: bool,
67}
68
69#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
70pub struct Library {
71    pub id: String,
72    pub name: String,
73    pub created_at: i64,
74    pub snippet_count: i64,
75}
76
77/// One page of snippets plus the paging facts derived from the page.
78#[derive(Debug, Clone)]
79pub struct SnippetPage {
80    /// The rows of this page (never more than the requested limit).
81    pub snippets: Vec<Snippet>,
82    /// True when rows exist beyond this page.
83    pub has_more: bool,
84    /// Exact number of rows in the filtered range.
85    pub total: i32,
86}
87
88/// The cursor the delta filter selects on.
89///
90/// The server keeps two cursors side by side. The **timestamp cursor**
91/// (`visible_at`) is second-granular and stays the filter for clients that
92/// send no sequence cursor. The **write-sequence cursor** (`sequence`) is
93/// server-assigned, strictly increasing per library, and exact. The request
94/// picks the form: a non-zero sequence cursor means the sequence form, and
95/// anything else falls back to the timestamp form.
96#[derive(Debug, Clone, Copy, PartialEq, Eq)]
97pub enum DeltaCursor {
98    /// The timestamp cursor: `visible_at >= value`.
99    Timestamp(i64),
100    /// The write-sequence cursor: `sequence >= value`.
101    Sequence(i64),
102}
103
104impl DeltaCursor {
105    /// The SQL predicate and its bound value for this cursor form.
106    ///
107    /// The predicate is a literal, never caller input, so the assembled
108    /// statement stays free of injected values.
109    fn predicate(self) -> (&'static str, i64) {
110        match self {
111            DeltaCursor::Timestamp(value) => ("AND visible_at >= ?", value),
112            DeltaCursor::Sequence(value) => ("AND sequence >= ?", value),
113        }
114    }
115}
116
117#[derive(Clone)]
118pub struct Database {
119    pool: SqlitePool,
120}
121
122fn hash_api_key(api_key: &str) -> DbResult<String> {
123    let mut salt_bytes = [0u8; 16];
124    getrandom::fill(&mut salt_bytes)
125        .map_err(|e| DbError::Internal(format!("Failed to generate salt: {}", e)))?;
126    let params = Params::new(
127        ARGON2_MEMORY_KIB,
128        ARGON2_ITERATIONS,
129        ARGON2_PARALLELISM,
130        None,
131    )
132    .map_err(|e| DbError::Internal(format!("Invalid Argon2 params: {}", e)))?;
133    let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
134    let hash = argon2
135        .hash_password_with_salt(api_key.as_bytes(), &salt_bytes)
136        .map_err(|e| DbError::Internal(format!("Failed to hash API key: {}", e)))?
137        .to_string();
138    Ok(hash)
139}
140
141fn verify_api_key(api_key: &str, hash: &str) -> bool {
142    let parsed = match PasswordHash::new(hash) {
143        Ok(p) => p,
144        Err(_) => return false,
145    };
146    let params = match Params::new(
147        ARGON2_MEMORY_KIB,
148        ARGON2_ITERATIONS,
149        ARGON2_PARALLELISM,
150        None,
151    ) {
152        Ok(p) => p,
153        Err(_) => return false,
154    };
155    let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
156    argon2.verify_password(api_key.as_bytes(), &parsed).is_ok()
157}
158
159/// Computes the first 8 chars of base64(SHA-256(api_key)) for indexed lookup.
160fn compute_api_key_prefix(api_key: &str) -> String {
161    let hash = Sha256::digest(api_key.as_bytes());
162    let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
163    encoded.chars().take(8).collect()
164}
165
166/// Derives a stable prefix from a stored hash for legacy users whose
167/// `api_key_prefix` is NULL. The prefix is independent of the original
168/// API key (which is unrecoverable from the Argon2 hash), so legacy users
169/// cannot authenticate after backfill — but the fallback that turned
170/// NULL-prefix users into an O(N) Argon2id DoS amplifier is removed.
171fn derive_prefix_from_hash(stored_hash: &str) -> String {
172    let hash = Sha256::digest(stored_hash.as_bytes());
173    let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
174    encoded.chars().take(8).collect()
175}
176
177fn saturating_i32(v: i64) -> i32 {
178    v.min(i32::MAX as i64) as i32
179}
180
181type SnippetRow = (String, String, String, String, i64, i64, String, i32, i32);
182
183/// Count the rows of the filtered range.
184///
185/// `>=` (not `>`) is required for delta correctness: the watermark returned
186/// to the client is the maximum cursor value of the library, so a strict `>`
187/// would skip the boundary row itself. Re-delivering boundary rows is safe —
188/// merges are idempotent by snippet identity.
189async fn count_snippets(
190    conn: &mut sqlx::SqliteConnection,
191    user_id: &str,
192    library_id: &str,
193    cursor: DeltaCursor,
194    include_deleted: bool,
195) -> DbResult<i32> {
196    let (predicate, cursor_value) = cursor.predicate();
197    // The only dynamic part of the statement is `predicate`, which is a
198    // fixed literal chosen by the cursor form — no caller input reaches the
199    // SQL text, and every value is bound as a parameter.
200    let sql = AssertSqlSafe(format!(
201        "SELECT COUNT(*) FROM snippets \
202         WHERE user_id = ? \
203           AND library_id = ? \
204           {predicate} \
205           AND (? OR deleted = 0)"
206    ));
207    let total: (i64,) = sqlx::query_as(sql)
208        .bind(user_id)
209        .bind(library_id)
210        .bind(cursor_value)
211        .bind(include_deleted)
212        .fetch_one(&mut *conn)
213        .await?;
214
215    Ok(saturating_i32(total.0))
216}
217
218/// Read one page of the filtered range, newest first.
219async fn fetch_snippets(
220    conn: &mut sqlx::SqliteConnection,
221    user_id: &str,
222    library_id: &str,
223    cursor: DeltaCursor,
224    limit: i32,
225    offset: i32,
226    include_deleted: bool,
227) -> DbResult<Vec<Snippet>> {
228    let (predicate, cursor_value) = cursor.predicate();
229    // See `count_snippets`: the only dynamic part is a fixed literal.
230    let sql = AssertSqlSafe(format!(
231        "SELECT id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted \
232         FROM snippets \
233         WHERE user_id = ? \
234           AND library_id = ? \
235           {predicate} \
236           AND (? OR deleted = 0) \
237         ORDER BY updated_at DESC, id DESC \
238         LIMIT ? OFFSET ?"
239    ));
240    let rows: Vec<SnippetRow> = sqlx::query_as(sql)
241        .bind(user_id)
242        .bind(library_id)
243        .bind(cursor_value)
244        .bind(include_deleted)
245        .bind(limit)
246        .bind(offset)
247        .fetch_all(&mut *conn)
248        .await?;
249
250    rows.into_iter()
251        .map(
252            |(
253                id,
254                description,
255                command,
256                tags_str,
257                created_at,
258                updated_at,
259                device_id,
260                deleted,
261                encrypted,
262            )| {
263                // Surface corruption to repair flows instead of
264                // silently substituting an empty tag list.
265                let tags: Vec<String> = serde_json::from_str(&tags_str).map_err(|e| {
266                    DbError::Internal(format!(
267                        "corrupt tags JSON for snippet {id} (row must be repaired): {e}"
268                    ))
269                })?;
270                Ok(Snippet {
271                    id,
272                    description,
273                    command,
274                    tags,
275                    created_at,
276                    updated_at,
277                    device_id,
278                    deleted: deleted != 0,
279                    encrypted: encrypted != 0,
280                })
281            },
282        )
283        .collect::<DbResult<Vec<Snippet>>>()
284}
285
286const MIGRATION_BATCH_SIZE: i64 = 100;
287
288/// Bump the library's write-sequence counter and return the new value.
289///
290/// The counter row is created on first use. The bump runs inside the
291/// caller's transaction, so it is committed with the row write or rolled
292/// back with it: a failed write never consumes a sequence value, and a
293/// consumed value is never handed out twice.
294async fn bump_library_sequence(
295    tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
296    library_id: &str,
297) -> DbResult<i64> {
298    sqlx::query(
299        "INSERT INTO library_sequences (library_id, sequence) VALUES (?, 0) \
300         ON CONFLICT(library_id) DO NOTHING",
301    )
302    .bind(library_id)
303    .execute(&mut **tx)
304    .await?;
305
306    let (sequence,): (i64,) = sqlx::query_as(
307        "UPDATE library_sequences SET sequence = sequence + 1 WHERE library_id = ? RETURNING sequence",
308    )
309    .bind(library_id)
310    .fetch_one(&mut **tx)
311    .await?;
312
313    Ok(sequence)
314}
315
316/// Create (or tighten) the directory that holds the SQLite database so that it
317/// is reachable only by the service account on Unix.
318///
319/// A newly created directory is created *with* `0o700` as part of the creation
320/// call, so there is no window in which it exists with a broader mode. A
321/// pre-existing directory may predate this policy: it is tightened when
322/// possible, and a failure is a warning naming the observed mode rather than a
323/// startup abort, because a read-only or administrator-managed deployment can
324/// legitimately refuse the change.
325#[cfg(unix)]
326fn prepare_data_directory(path: &Path) -> std::io::Result<()> {
327    use std::os::unix::fs::{DirBuilderExt, PermissionsExt};
328
329    let existed = path.exists();
330    std::fs::DirBuilder::new()
331        .recursive(true)
332        .mode(0o700)
333        .create(path)?;
334
335    if existed {
336        let mode = std::fs::metadata(path)?.permissions().mode() & 0o777;
337        if mode & 0o077 != 0 {
338            match std::fs::set_permissions(path, std::fs::Permissions::from_mode(0o700)) {
339                Ok(()) => tracing::info!(
340                    "Tightened server data directory {} from mode {:#05o} to 0o700",
341                    path.display(),
342                    mode
343                ),
344                Err(error) => tracing::warn!(
345                    "Server data directory {} is accessible to other local users \
346                     (mode {:#05o}) and could not be tightened to 0o700: {}",
347                    path.display(),
348                    mode,
349                    error
350                ),
351            }
352        }
353    }
354    Ok(())
355}
356
357#[cfg(not(unix))]
358fn prepare_data_directory(path: &Path) -> std::io::Result<()> {
359    // Windows ACL enforcement is out of scope; the directory is still created.
360    std::fs::create_dir_all(path)
361}
362
363/// Restrict the database file and its WAL/shared-memory siblings to `0o600`.
364///
365/// The siblings are created by SQLite on first write, so this runs after the
366/// schema has been touched. Contents are ciphertext, but metadata and Argon2id
367/// hashes are readable by any local user who can open the file.
368#[cfg(unix)]
369fn restrict_database_files(filename: &Path) {
370    use std::os::unix::fs::PermissionsExt;
371
372    let mut targets = vec![filename.to_path_buf()];
373    for suffix in ["-wal", "-shm"] {
374        let mut sibling = filename.as_os_str().to_owned();
375        sibling.push(suffix);
376        targets.push(PathBuf::from(sibling));
377    }
378
379    for target in targets {
380        let mode = match std::fs::metadata(&target) {
381            Ok(metadata) => metadata.permissions().mode() & 0o777,
382            // A missing `-wal`/`-shm` is normal: SQLite may not have created it
383            // yet, or may have removed it on a clean checkpoint.
384            Err(error) if error.kind() == std::io::ErrorKind::NotFound => continue,
385            Err(error) => {
386                tracing::debug!(
387                    "Unable to inspect database file {}: {}",
388                    target.display(),
389                    error
390                );
391                continue;
392            }
393        };
394        if mode & 0o077 == 0 {
395            continue;
396        }
397        if let Err(error) =
398            std::fs::set_permissions(&target, std::fs::Permissions::from_mode(0o600))
399        {
400            tracing::warn!(
401                "Database file {} is accessible to other local users (mode {:#05o}) \
402                 and could not be tightened to 0o600: {}",
403                target.display(),
404                mode,
405                error
406            );
407        }
408    }
409}
410
411#[cfg(not(unix))]
412fn restrict_database_files(_filename: &Path) {}
413
414impl Database {
415    pub async fn connect(url: &str, max_connections: u32) -> DbResult<Self> {
416        // The public configuration uses a SQLite file path for convenience,
417        // while tests and advanced deployments may provide a full sqlite URL.
418        // Sqlx's generic `Pool::connect` leaves file creation disabled, which
419        // makes a fresh `snip-sync init` fail on its first start. Normalize
420        // both forms and explicitly create the file when needed.
421        let options = if url.starts_with("sqlite:") {
422            SqliteConnectOptions::from_str(url)?
423        } else {
424            SqliteConnectOptions::new().filename(url)
425        }
426        .create_if_missing(true)
427        .busy_timeout(Duration::from_secs(5));
428
429        let filename = options.get_filename().to_path_buf();
430        if filename != Path::new(":memory:")
431            && let Some(parent) = filename.parent()
432            && !parent.as_os_str().is_empty()
433        {
434            prepare_data_directory(parent).map_err(|e| {
435                DbError::Internal(format!(
436                    "Failed to create database directory {}: {}",
437                    parent.display(),
438                    e
439                ))
440            })?;
441        }
442
443        // `foreign_keys` is a **per-connection** SQLite setting (unlike
444        // `journal_mode`, which SQLite persists in the database file header).
445        // Running the pragma against the pool set it on whichever single
446        // connection it happened to check out — at most 1 of
447        // `max_connections` — leaving the schema's declared FKs unenforced on
448        // the rest. `after_connect` runs for every connection the pool opens.
449        let pool = SqlitePoolOptions::new()
450            .max_connections(max_connections)
451            .after_connect(|conn, _meta| {
452                Box::pin(async move {
453                    sqlx::query("PRAGMA foreign_keys=ON").execute(conn).await?;
454                    Ok(())
455                })
456            })
457            .connect_with(options)
458            .await?;
459
460        sqlx::query("PRAGMA journal_mode=WAL")
461            .execute(&pool)
462            .await?;
463
464        sqlx::query(
465            "
466            CREATE TABLE IF NOT EXISTS users (
467                id TEXT PRIMARY KEY,
468                api_key TEXT UNIQUE NOT NULL,
469                api_key_prefix TEXT,
470                created_at INTEGER NOT NULL,
471                updated_at INTEGER NOT NULL
472            )
473            ",
474        )
475        .execute(&pool)
476        .await?;
477
478        sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_api_key_prefix ON users(api_key_prefix)")
479            .execute(&pool)
480            .await?;
481
482        sqlx::query(
483            "
484            CREATE TABLE IF NOT EXISTS libraries (
485                id TEXT PRIMARY KEY,
486                user_id TEXT NOT NULL,
487                name TEXT NOT NULL,
488                created_at INTEGER NOT NULL,
489                deleted_at INTEGER,
490                FOREIGN KEY (user_id) REFERENCES users(id),
491                UNIQUE(user_id, name)
492            )
493            ",
494        )
495        .execute(&pool)
496        .await?;
497
498        sqlx::query(
499            "
500            CREATE TABLE IF NOT EXISTS snippets (
501                id TEXT PRIMARY KEY,
502                user_id TEXT NOT NULL,
503                library_id TEXT NOT NULL,
504                description TEXT NOT NULL,
505                command TEXT NOT NULL,
506                tags TEXT NOT NULL DEFAULT '[]',
507                created_at INTEGER NOT NULL,
508                updated_at INTEGER NOT NULL,
509                device_id TEXT NOT NULL,
510                deleted INTEGER NOT NULL DEFAULT 0,
511                encrypted INTEGER NOT NULL DEFAULT 0,
512                visible_at INTEGER NOT NULL DEFAULT 0,
513                sequence INTEGER NOT NULL DEFAULT 0,
514                FOREIGN KEY (user_id) REFERENCES users(id),
515                FOREIGN KEY (library_id) REFERENCES libraries(id)
516            )
517            ",
518        )
519        .execute(&pool)
520        .await?;
521
522        // The per-library write-sequence counter. One row per library holds
523        // the last sequence the server handed out; the next write takes the
524        // next value. The counter lives in the database, so a server restart
525        // does not reset it.
526        sqlx::query(
527            "
528            CREATE TABLE IF NOT EXISTS library_sequences (
529                library_id TEXT PRIMARY KEY,
530                sequence INTEGER NOT NULL DEFAULT 0
531            )
532            ",
533        )
534        .execute(&pool)
535        .await?;
536
537        sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_user ON snippets(user_id)")
538            .execute(&pool)
539            .await?;
540        sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_library ON snippets(library_id)")
541            .execute(&pool)
542            .await?;
543        sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_updated ON snippets(updated_at)")
544            .execute(&pool)
545            .await?;
546        sqlx::query(
547            "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_updated \
548             ON snippets(user_id, library_id, updated_at, deleted)",
549        )
550        .execute(&pool)
551        .await?;
552        // The delta cursor lives in `visible_at`, not in the client-chosen
553        // `updated_at`; this index serves the `visible_at >= since` range.
554        sqlx::query(
555            "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_visible \
556             ON snippets(user_id, library_id, visible_at, deleted)",
557        )
558        .execute(&pool)
559        .await?;
560        sqlx::query("CREATE INDEX IF NOT EXISTS idx_libraries_user ON libraries(user_id)")
561            .execute(&pool)
562            .await?;
563
564        // The write-sequence cursor owns its column, its counter, and its
565        // indexes together: the unique index cannot be created before the
566        // column exists, and the column cannot be backfilled before the
567        // counter table exists. The migration runs inside `connect` so the
568        // server never serves a database whose sequence cursor is half
569        // installed.
570        let db = Self { pool };
571        db.migrate_snippet_sequence().await?;
572
573        // Runs after the schema writes so SQLite has created `-wal`/`-shm`.
574        restrict_database_files(&filename);
575
576        Ok(db)
577    }
578
579    pub async fn ping(&self) -> DbResult<()> {
580        sqlx::query("SELECT 1").execute(&self.pool).await?;
581        Ok(())
582    }
583
584    pub fn pool(&self) -> &SqlitePool {
585        &self.pool
586    }
587
588    pub async fn create_user(&self, api_key: &str) -> DbResult<String> {
589        let user_id = Uuid::new_v4().to_string();
590        let now = OffsetDateTime::now_utc().unix_timestamp();
591        let api_key_hash = hash_api_key(api_key)?;
592        let api_key_prefix = compute_api_key_prefix(api_key);
593
594        let mut tx = self.pool.begin().await?;
595
596        sqlx::query("INSERT INTO users (id, api_key, api_key_prefix, created_at, updated_at) VALUES (?, ?, ?, ?, ?)")
597            .bind(&user_id)
598            .bind(&api_key_hash)
599            .bind(&api_key_prefix)
600            .bind(now)
601            .bind(now)
602            .execute(&mut *tx)
603            .await?;
604
605        let default_lib_id = Uuid::new_v4().to_string();
606        sqlx::query("INSERT INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)")
607            .bind(&default_lib_id)
608            .bind(&user_id)
609            .bind("default")
610            .bind(now)
611            .execute(&mut *tx)
612            .await?;
613
614        tx.commit().await?;
615
616        tracing::info!(
617            "Created user {} with default library {}",
618            user_id,
619            default_lib_id
620        );
621
622        Ok(user_id)
623    }
624
625    pub async fn get_user_by_api_key(&self, api_key: &str) -> DbResult<Option<String>> {
626        let prefix = compute_api_key_prefix(api_key);
627
628        // Prefix is always populated for new users (see create_user) and is
629        // backfilled by migrate_plaintext_api_keys. The legacy NULL-prefix
630        // fallback was removed because N NULL-prefix users turned every auth
631        // attempt into O(N) Argon2id verification — a DoS amplifier.
632        let rows: Vec<(String, String)> =
633            sqlx::query_as("SELECT id, api_key FROM users WHERE api_key_prefix = ?")
634                .bind(&prefix)
635                .fetch_all(&self.pool)
636                .await?;
637
638        for (user_id, stored_hash) in rows {
639            if verify_api_key(api_key, &stored_hash) {
640                return Ok(Some(user_id));
641            }
642        }
643
644        Ok(None)
645    }
646
647    pub async fn create_library(&self, user_id: &str, name: &str) -> DbResult<String> {
648        if !name
649            .chars()
650            .all(|c| c.is_alphanumeric() || c == '-' || c == '_')
651        {
652            return Err(DbError::Validation(
653                "Invalid library name. Use only letters, numbers, dash, and underscore."
654                    .to_string(),
655            ));
656        }
657        if name.is_empty() || name.len() > 64 {
658            return Err(DbError::Validation(
659                "Library name must be 1-64 characters".to_string(),
660            ));
661        }
662
663        let lib_id = Uuid::new_v4().to_string();
664        let now = OffsetDateTime::now_utc().unix_timestamp();
665
666        let result = sqlx::query(
667            "INSERT OR IGNORE INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)",
668        )
669        .bind(&lib_id)
670        .bind(user_id)
671        .bind(name)
672        .bind(now)
673        .execute(&self.pool)
674        .await?;
675
676        if result.rows_affected() == 0 {
677            return Err(DbError::Conflict(format!(
678                "Library '{}' already exists",
679                name
680            )));
681        }
682
683        Ok(lib_id)
684    }
685
686    pub async fn list_libraries(
687        &self,
688        user_id: &str,
689        limit: i32,
690        offset: i32,
691    ) -> DbResult<(Vec<Library>, i32)> {
692        let total: (i64,) = sqlx::query_as(
693            "SELECT COUNT(*) FROM libraries WHERE user_id = ? AND deleted_at IS NULL",
694        )
695        .bind(user_id)
696        .fetch_one(&self.pool)
697        .await?;
698
699        let rows: Vec<(String, String, i64, i64)> = sqlx::query_as(
700            "SELECT l.id, l.name, l.created_at, COUNT(s.id) as snippet_count \
701             FROM libraries l \
702             LEFT JOIN snippets s ON s.library_id = l.id AND s.deleted = 0 \
703             WHERE l.user_id = ? AND l.deleted_at IS NULL \
704             GROUP BY l.id \
705             ORDER BY l.name LIMIT ? OFFSET ?",
706        )
707        .bind(user_id)
708        .bind(limit)
709        .bind(offset)
710        .fetch_all(&self.pool)
711        .await?;
712
713        let libraries = rows
714            .into_iter()
715            .map(|(id, name, created_at, snippet_count)| Library {
716                id,
717                name,
718                created_at,
719                snippet_count,
720            })
721            .collect();
722
723        Ok((libraries, saturating_i32(total.0)))
724    }
725
726    pub async fn delete_library(&self, user_id: &str, library_id: &str) -> DbResult<()> {
727        let now = OffsetDateTime::now_utc().unix_timestamp();
728        let mut tx = self.pool.begin().await?;
729
730        let result = sqlx::query(
731            "UPDATE libraries SET deleted_at = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
732        )
733        .bind(now)
734        .bind(library_id)
735        .bind(user_id)
736        .execute(&mut *tx)
737        .await?;
738
739        if result.rows_affected() == 0 {
740            tx.rollback().await?;
741            return Err(DbError::NotFound(
742                "Library not found or already deleted".to_string(),
743            ));
744        }
745
746        // Tombstone the rows. Every tombstoned row takes a fresh write
747        // sequence, so a client that syncs on the sequence cursor sees the
748        // deletion instead of keeping the row it already had.
749        let rows: Vec<(String,)> = sqlx::query_as(
750            "SELECT id FROM snippets WHERE library_id = ? AND user_id = ? AND deleted = 0",
751        )
752        .bind(library_id)
753        .bind(user_id)
754        .fetch_all(&mut *tx)
755        .await?;
756
757        for (id,) in rows {
758            let sequence = bump_library_sequence(&mut tx, library_id).await?;
759            sqlx::query(
760                "UPDATE snippets
761                 SET deleted = 1, updated_at = MAX(updated_at, ?), visible_at = MAX(visible_at, ?), sequence = ?
762                 WHERE id = ?",
763            )
764            .bind(now)
765            .bind(now)
766            .bind(sequence)
767            .bind(&id)
768            .execute(&mut *tx)
769            .await?;
770        }
771
772        tx.commit().await?;
773        Ok(())
774    }
775
776    pub async fn get_default_library(&self, user_id: &str) -> DbResult<String> {
777        let (lib_id,): (String,) = sqlx::query_as(
778            "SELECT id FROM libraries WHERE user_id = ? AND name = 'default' AND deleted_at IS NULL",
779        )
780        .bind(user_id)
781        .fetch_optional(&self.pool)
782        .await?
783        .ok_or_else(|| DbError::NotFound("Default library not found".to_string()))?;
784
785        Ok(lib_id)
786    }
787
788    pub async fn verify_library_ownership(
789        &self,
790        user_id: &str,
791        library_id: &str,
792    ) -> DbResult<bool> {
793        let (count,): (i64,) = sqlx::query_as(
794            "SELECT COUNT(*) FROM libraries WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
795        )
796        .bind(library_id)
797        .bind(user_id)
798        .fetch_one(&self.pool)
799        .await?;
800
801        Ok(count > 0)
802    }
803
804    pub async fn get_snippets(
805        &self,
806        user_id: &str,
807        library_id: &str,
808        since: i64,
809        limit: i32,
810        offset: i32,
811        include_deleted: bool,
812    ) -> DbResult<(Vec<Snippet>, i32)> {
813        // The read-only `GetSnippets` RPC carries a timestamp cursor only, so
814        // it always filters on the timestamp cursor.
815        let page = self
816            .get_snippets_page(
817                user_id,
818                library_id,
819                DeltaCursor::Timestamp(since),
820                limit,
821                offset,
822                include_deleted,
823            )
824            .await?;
825        Ok((page.snippets, page.total))
826    }
827
828    /// Fetch one page of snippets together with the paging facts derived
829    /// from it.
830    ///
831    /// The page is read with `LIMIT limit + 1`, so `has_more` comes from the
832    /// rows themselves instead of a second `COUNT(*)` scan over the same
833    /// range. The exact `total` is taken from the page whenever the page is
834    /// the last one (the range then holds exactly `offset + rows` rows); when
835    /// the range continues past the page — or the requested offset is past
836    /// the end of the range — the range is still counted so `total` keeps
837    /// its exact meaning.
838    pub async fn get_snippets_page(
839        &self,
840        user_id: &str,
841        library_id: &str,
842        cursor: DeltaCursor,
843        limit: i32,
844        offset: i32,
845        include_deleted: bool,
846    ) -> DbResult<SnippetPage> {
847        let mut tx = self.pool.begin().await?;
848
849        let page_limit = limit.max(0);
850        let mut snippets = fetch_snippets(
851            &mut tx,
852            user_id,
853            library_id,
854            cursor,
855            page_limit.saturating_add(1),
856            offset,
857            include_deleted,
858        )
859        .await?;
860
861        let has_more = snippets.len() > page_limit as usize;
862        snippets.truncate(page_limit as usize);
863
864        let total = if !has_more && (!snippets.is_empty() || offset == 0) {
865            offset.saturating_add(snippets.len() as i32)
866        } else {
867            count_snippets(&mut tx, user_id, library_id, cursor, include_deleted).await?
868        };
869
870        tx.commit().await?;
871
872        Ok(SnippetPage {
873            snippets,
874            has_more,
875            total,
876        })
877    }
878
879    #[cfg(test)]
880    pub async fn upsert_snippet(
881        &self,
882        snippet: &Snippet,
883        user_id: &str,
884        library_id: &str,
885    ) -> DbResult<()> {
886        // Delegate to the production write path so a test fixture gets the
887        // same cursor bookkeeping as a real push.
888        let mut tx = self.pool.begin().await?;
889        self.upsert_snippet_in_tx(&mut tx, snippet, user_id, library_id)
890            .await?;
891        tx.commit().await?;
892        Ok(())
893    }
894
895    pub async fn upsert_snippet_in_tx(
896        &self,
897        tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
898        snippet: &Snippet,
899        user_id: &str,
900        library_id: &str,
901    ) -> DbResult<UpsertOutcome> {
902        let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
903        let deleted = snippet.deleted as i32;
904        let encrypted = snippet.encrypted as i32;
905
906        // The server assigns the write sequence. The counter is bumped
907        // **first** so this transaction takes SQLite's write lock before it
908        // reads the watermark: two concurrent writes to one library serialize
909        // here and receive distinct sequence values instead of both reading
910        // the same counter. The bump shares the transaction with the row
911        // write, so a failed write consumes no sequence value.
912        let sequence = bump_library_sequence(tx, library_id).await?;
913
914        // The timestamp delta cursor is the library's `visible_at` watermark,
915        // not the client-chosen `updated_at`: a row stored *below* the
916        // watermark would never be delivered to any client, because the
917        // timestamp delta pull filters on `visible_at >= since`. The stored
918        // `updated_at` keeps the client's logical modification time verbatim
919        // — that is what the server's last-write-wins rule and the client's
920        // merge order compare — while `visible_at` carries the cursor position
921        // the row is delivered at.
922        let (watermark,): (i64,) = sqlx::query_as(
923            "SELECT COALESCE(MAX(visible_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
924        )
925        .bind(user_id)
926        .bind(library_id)
927        .fetch_one(&mut **tx)
928        .await?;
929        let visible_at = snippet.updated_at.max(watermark);
930
931        // Upsert with last-write-wins conflict resolution.
932        // Tie-breaking uses device_id string comparison, which is correct for
933        // lowercase hex UUIDs (lexicographic == numeric for lowercase hex).
934        // When updated_at and device_id are equal, fall back to content
935        // comparison (command, description) to provide a deterministic
936        // winner that mirrors the client's fingerprint tie-break.
937        //
938        // The conflict comparison uses the client's raw `updated_at`, so the
939        // rule is unchanged by the cursor bookkeeping; only `visible_at` and
940        // `sequence` move, and neither moves backwards.
941        let result = sqlx::query(
942            "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted, visible_at, sequence)
943              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
944              ON CONFLICT(id) DO UPDATE SET
945                 description = excluded.description,
946                 command = excluded.command,
947                 tags = excluded.tags,
948                 updated_at = excluded.updated_at,
949                 device_id = excluded.device_id,
950                 deleted = excluded.deleted,
951                 encrypted = excluded.encrypted,
952                 visible_at = MAX(snippets.visible_at, excluded.visible_at),
953                 sequence = excluded.sequence
954              WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
955            AND (excluded.updated_at > snippets.updated_at \
956                 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id) \
957                 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command > snippets.command) \
958                 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command = snippets.command AND excluded.description > snippets.description))",
959        )
960        .bind(&snippet.id)
961        .bind(user_id)
962        .bind(library_id)
963        .bind(&snippet.description)
964        .bind(&snippet.command)
965        .bind(&tags_json)
966        .bind(snippet.created_at)
967        .bind(snippet.updated_at)
968        .bind(&snippet.device_id)
969        .bind(deleted)
970        .bind(encrypted)
971        .bind(visible_at)
972        .bind(sequence)
973        .execute(&mut **tx)
974        .await?;
975
976        if result.rows_affected() > 0 {
977            return Ok(UpsertOutcome::Written);
978        }
979
980        // Zero rows written. Either the existing record under this same
981        // user/library is newer (normal last-write-wins dedupe), or the id is
982        // already held by a different scope and the write was dropped. Only a
983        // scope probe can tell them apart.
984        let foreign: Option<(String, String)> = sqlx::query_as(
985            "SELECT user_id, library_id FROM snippets \
986             WHERE id = ? AND (user_id != ? OR library_id != ?) LIMIT 1",
987        )
988        .bind(&snippet.id)
989        .bind(user_id)
990        .bind(library_id)
991        .fetch_optional(&mut **tx)
992        .await?;
993
994        Ok(match foreign {
995            Some(_) => UpsertOutcome::ForeignScope,
996            None => UpsertOutcome::Stale,
997        })
998    }
999
1000    pub async fn get_latest_timestamp(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
1001        // The cursor is the `visible_at` watermark, not `MAX(updated_at)`:
1002        // `updated_at` is chosen by the client and may sit below the current
1003        // watermark, which would make the cursor non-monotone.
1004        let (timestamp,): (i64,) = sqlx::query_as(
1005            "SELECT COALESCE(MAX(visible_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
1006        )
1007        .bind(user_id)
1008        .bind(library_id)
1009        .fetch_one(&self.pool)
1010        .await?;
1011
1012        Ok(timestamp)
1013    }
1014
1015    /// The library's write-sequence watermark.
1016    ///
1017    /// This is the cursor the server hands back to a client that syncs on the
1018    /// sequence path. It is the highest sequence any row of the library
1019    /// holds, so it never moves backwards and never skips a row.
1020    pub async fn get_latest_sequence(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
1021        let (sequence,): (i64,) = sqlx::query_as(
1022            "SELECT COALESCE(MAX(sequence), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
1023        )
1024        .bind(user_id)
1025        .bind(library_id)
1026        .fetch_one(&self.pool)
1027        .await?;
1028
1029        Ok(sequence)
1030    }
1031
1032    /// Add the write-sequence cursor: its column, its per-library counter,
1033    /// and the unique index that turns a counter race into a loud failure.
1034    ///
1035    /// The backfill gives every row a sequence that preserves the
1036    /// pre-migration `updated_at` ordering (ranked inside the row's library,
1037    /// ties broken by the row id), so a migrated database selects the same
1038    /// rows as an unmigrated one for the first sync and every client's stored
1039    /// cursor stays valid. The counter is then seeded from the highest
1040    /// sequence of each library, so the next write can never collide with a
1041    /// backfilled row.
1042    ///
1043    /// Idempotent: it is safe to run on every start.
1044    pub async fn migrate_snippet_sequence(&self) -> DbResult<bool> {
1045        let columns: Vec<String> =
1046            sqlx::query_scalar("SELECT name FROM pragma_table_info('snippets')")
1047                .fetch_all(&self.pool)
1048                .await?;
1049
1050        let migrated = if columns.iter().any(|name| name == "sequence") {
1051            false
1052        } else {
1053            sqlx::query("ALTER TABLE snippets ADD COLUMN sequence INTEGER NOT NULL DEFAULT 0")
1054                .execute(&self.pool)
1055                .await?;
1056            // The rank of the row inside its library, ordered by the
1057            // pre-migration `updated_at` and then by the row id. The
1058            // backfilled ordering therefore preserves the pre-migration
1059            // ordering, and every row of a library gets a distinct sequence.
1060            sqlx::query(
1061                "WITH ranked AS (
1062                     SELECT id,
1063                            ROW_NUMBER() OVER (
1064                                PARTITION BY user_id, library_id
1065                                ORDER BY updated_at ASC, id ASC
1066                            ) AS rn
1067                     FROM snippets
1068                 )
1069                 UPDATE snippets
1070                 SET sequence = (SELECT rn FROM ranked WHERE ranked.id = snippets.id)",
1071            )
1072            .execute(&self.pool)
1073            .await?;
1074            true
1075        };
1076
1077        // The counter must never hand out a value a row already holds, so it
1078        // is seeded from the highest sequence of each library. The seed runs
1079        // through a subquery with a `WHERE` clause: SQLite needs one to tell
1080        // the upsert's `ON CONFLICT` apart from a join's `ON`.
1081        sqlx::query(
1082            "INSERT INTO library_sequences (library_id, sequence)
1083             SELECT library_id, highest
1084             FROM (
1085                 SELECT library_id, MAX(sequence) AS highest
1086                 FROM snippets
1087                 GROUP BY library_id
1088             ) WHERE true
1089             ON CONFLICT(library_id) DO UPDATE
1090                SET sequence = MAX(library_sequences.sequence, excluded.sequence)",
1091        )
1092        .execute(&self.pool)
1093        .await?;
1094
1095        // The unique index is the enforcement mechanism for the concurrent
1096        // counter race: two rows sharing a (library_id, sequence) are
1097        // rejected loudly instead of silently making one of them invisible.
1098        sqlx::query(
1099            "CREATE UNIQUE INDEX IF NOT EXISTS idx_snippets_library_sequence \
1100             ON snippets(library_id, sequence)",
1101        )
1102        .execute(&self.pool)
1103        .await?;
1104
1105        // The range index for the sequence form of the delta filter.
1106        sqlx::query(
1107            "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_sequence \
1108             ON snippets(user_id, library_id, sequence, deleted)",
1109        )
1110        .execute(&self.pool)
1111        .await?;
1112
1113        if migrated {
1114            tracing::info!("Migrated snippets table: added the sequence write-cursor column");
1115        }
1116
1117        Ok(migrated)
1118    }
1119
1120    /// Add the `visible_at` cursor column to an existing database.
1121    ///
1122    /// The column carries the delta cursor position of a row; `updated_at`
1123    /// keeps the client's logical modification time. Existing rows are
1124    /// backfilled with their current `updated_at`, so a migrated database
1125    /// selects exactly the same rows as before the migration and every
1126    /// client's stored cursor stays valid.
1127    pub async fn migrate_snippet_visible_at(&self) -> DbResult<bool> {
1128        let columns: Vec<String> =
1129            sqlx::query_scalar("SELECT name FROM pragma_table_info('snippets')")
1130                .fetch_all(&self.pool)
1131                .await?;
1132        if columns.iter().any(|name| name == "visible_at") {
1133            return Ok(false);
1134        }
1135
1136        sqlx::query("ALTER TABLE snippets ADD COLUMN visible_at INTEGER NOT NULL DEFAULT 0")
1137            .execute(&self.pool)
1138            .await?;
1139        sqlx::query("UPDATE snippets SET visible_at = updated_at")
1140            .execute(&self.pool)
1141            .await?;
1142        sqlx::query(
1143            "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_visible \
1144             ON snippets(user_id, library_id, visible_at, deleted)",
1145        )
1146        .execute(&self.pool)
1147        .await?;
1148
1149        tracing::info!("Migrated snippets table: added the visible_at delta-cursor column");
1150        Ok(true)
1151    }
1152
1153    pub async fn migrate_plaintext_api_keys(&self) -> DbResult<usize> {
1154        let mut migrated = 0;
1155        let mut last_id = String::new();
1156
1157        loop {
1158            let rows: Vec<(String, String, Option<String>)> = sqlx::query_as(
1159                "SELECT id, api_key, api_key_prefix FROM users \
1160                 WHERE id > ? ORDER BY id LIMIT ?",
1161            )
1162            .bind(&last_id)
1163            .bind(MIGRATION_BATCH_SIZE)
1164            .fetch_all(&self.pool)
1165            .await?;
1166
1167            if rows.is_empty() {
1168                break;
1169            }
1170
1171            // Each batch commits atomically: a crash mid-batch leaves every
1172            // row in the batch at its pre-migration state, so auth never sees
1173            // a half-migrated user (hash updated but prefix stale).
1174            let mut tx = self.pool.begin().await?;
1175            for (user_id, stored, prefix) in rows {
1176                last_id = user_id.clone();
1177                let mut needs_update = false;
1178                let mut new_hash = stored.clone();
1179                let mut new_prefix = prefix.clone();
1180
1181                // Migrate plaintext to hashed
1182                if !stored.starts_with("$argon2") {
1183                    new_hash = hash_api_key(&stored)?;
1184                    needs_update = true;
1185                }
1186
1187                // Backfill prefix if missing
1188                if prefix.is_none() {
1189                    if stored.starts_with("$argon2") {
1190                        // Legacy hashed key: derive a stable prefix from the stored hash.
1191                        // The original API key is unrecoverable, so this user will no
1192                        // longer be able to authenticate — but the NULL-prefix fallback
1193                        // that amplified auth into an O(N) Argon2id DoS is removed.
1194                        new_prefix = Some(derive_prefix_from_hash(&stored));
1195                    } else {
1196                        new_prefix = Some(compute_api_key_prefix(&stored));
1197                    }
1198                    needs_update = true;
1199                }
1200
1201                if needs_update {
1202                    sqlx::query("UPDATE users SET api_key = ?, api_key_prefix = ? WHERE id = ?")
1203                        .bind(&new_hash)
1204                        .bind(&new_prefix)
1205                        .bind(&user_id)
1206                        .execute(&mut *tx)
1207                        .await?;
1208                    migrated += 1;
1209                }
1210            }
1211            tx.commit().await?;
1212        }
1213
1214        if migrated > 0 {
1215            tracing::info!("Migrated {} API keys (hash or prefix update)", migrated);
1216        }
1217
1218        Ok(migrated)
1219    }
1220}
1221
1222#[cfg(test)]
1223mod tests {
1224    use super::*;
1225
1226    async fn setup_db() -> Database {
1227        Database::connect("sqlite::memory:", 5).await.unwrap()
1228    }
1229
1230    #[tokio::test]
1231    async fn test_file_database_is_created_from_path() {
1232        let temp = tempfile::tempdir().unwrap();
1233        let path = temp.path().join("nested").join("snippets.db");
1234
1235        let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1236
1237        assert!(path.exists());
1238    }
1239
1240    /// The server's data directory holds ciphertext, but also metadata and
1241    /// Argon2id hashes. On Unix no other local user may traverse or read it.
1242    #[cfg(unix)]
1243    #[tokio::test]
1244    async fn data_directory_is_created_owner_only() {
1245        use std::os::unix::fs::PermissionsExt;
1246
1247        let temp = tempfile::tempdir().unwrap();
1248        let dir = temp.path().join("server-data");
1249        let path = dir.join("snippets.db");
1250
1251        let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1252
1253        let mode = std::fs::metadata(&dir).unwrap().permissions().mode() & 0o777;
1254        assert_eq!(
1255            mode, 0o700,
1256            "the data directory must be created owner-only, got {mode:#05o}"
1257        );
1258    }
1259
1260    /// A database file that already exists with a broader mode is tightened in
1261    /// place; the contents are never rewritten by the permission change.
1262    #[cfg(unix)]
1263    #[tokio::test]
1264    async fn database_file_is_restricted_to_owner_only() {
1265        use std::os::unix::fs::PermissionsExt;
1266
1267        let temp = tempfile::tempdir().unwrap();
1268        let dir = temp.path().join("server-data");
1269        std::fs::create_dir_all(&dir).unwrap();
1270        let path = dir.join("snippets.db");
1271        // Simulate a pre-existing world-readable database.
1272        std::fs::write(&path, b"").unwrap();
1273        std::fs::set_permissions(&path, std::fs::Permissions::from_mode(0o644)).unwrap();
1274
1275        let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1276
1277        let mode = std::fs::metadata(&path).unwrap().permissions().mode() & 0o777;
1278        assert_eq!(
1279            mode & 0o077,
1280            0,
1281            "the database file must not be group/world accessible, got {mode:#05o}"
1282        );
1283    }
1284
1285    /// A pre-existing broader directory is tightened rather than abandoned.
1286    #[cfg(unix)]
1287    #[tokio::test]
1288    async fn preexisting_broad_directory_is_tightened() {
1289        use std::os::unix::fs::PermissionsExt;
1290
1291        let temp = tempfile::tempdir().unwrap();
1292        let dir = temp.path().join("server-data");
1293        std::fs::create_dir_all(&dir).unwrap();
1294        std::fs::set_permissions(&dir, std::fs::Permissions::from_mode(0o755)).unwrap();
1295        let path = dir.join("snippets.db");
1296
1297        let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1298
1299        let mode = std::fs::metadata(&dir).unwrap().permissions().mode() & 0o777;
1300        assert_eq!(mode, 0o700, "an existing broad mode must be tightened");
1301    }
1302
1303    /// The in-memory database has no filesystem path and must not trip the
1304    /// permission helpers.
1305    #[tokio::test]
1306    async fn memory_database_skips_file_permission_handling() {
1307        let _db = setup_db().await;
1308    }
1309
1310    #[tokio::test]
1311    async fn test_create_and_verify_user() {
1312        let db = setup_db().await;
1313        let api_key = "test-api-key-12345";
1314
1315        let user_id = db.create_user(api_key).await.unwrap();
1316        assert!(!user_id.is_empty());
1317
1318        // Should find user with correct API key
1319        let found = db.get_user_by_api_key(api_key).await.unwrap();
1320        assert_eq!(found, Some(user_id));
1321
1322        // Should not find user with wrong API key
1323        let not_found = db.get_user_by_api_key("wrong-key").await.unwrap();
1324        assert_eq!(not_found, None);
1325    }
1326
1327    #[tokio::test]
1328    async fn test_api_key_is_hashed() {
1329        let db = setup_db().await;
1330        let api_key = "plaintext-key-should-be-hashed";
1331
1332        let _user_id = db.create_user(api_key).await.unwrap();
1333
1334        // Verify the stored key is an Argon2 hash, not plaintext
1335        let rows: Vec<(String, String, Option<String>)> =
1336            sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
1337                .fetch_all(&db.pool)
1338                .await
1339                .unwrap();
1340
1341        assert_eq!(rows.len(), 1);
1342        assert!(
1343            rows[0].1.starts_with("$argon2"),
1344            "API key should be stored as Argon2 hash"
1345        );
1346        // Verify prefix is stored
1347        assert!(rows[0].2.is_some(), "API key prefix should be stored");
1348        assert_eq!(rows[0].2.as_ref().unwrap().len(), 8);
1349    }
1350
1351    #[tokio::test]
1352    async fn test_create_default_library() {
1353        let db = setup_db().await;
1354        let user_id = db.create_user("key").await.unwrap();
1355
1356        let lib_id = db.get_default_library(&user_id).await.unwrap();
1357        assert!(!lib_id.is_empty());
1358
1359        let (libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
1360        assert_eq!(total, 1);
1361        assert_eq!(libraries[0].name, "default");
1362    }
1363
1364    #[tokio::test]
1365    async fn test_create_additional_library() {
1366        let db = setup_db().await;
1367        let user_id = db.create_user("key").await.unwrap();
1368
1369        let lib_id = db.create_library(&user_id, "work").await.unwrap();
1370        assert!(!lib_id.is_empty());
1371
1372        let (_libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
1373        assert_eq!(total, 2);
1374
1375        // Duplicate should fail with Conflict
1376        let err = db.create_library(&user_id, "work").await.unwrap_err();
1377        assert!(matches!(err, DbError::Conflict(_)));
1378    }
1379
1380    #[tokio::test]
1381    async fn test_invalid_library_names() {
1382        let db = setup_db().await;
1383        let user_id = db.create_user("key").await.unwrap();
1384
1385        // Empty name
1386        assert!(db.create_library(&user_id, "").await.is_err());
1387
1388        // Name with slash
1389        assert!(db.create_library(&user_id, "my/lib").await.is_err());
1390
1391        // Name too long
1392        assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
1393
1394        // Valid names
1395        assert!(db.create_library(&user_id, "valid-name").await.is_ok());
1396        assert!(db.create_library(&user_id, "valid_name").await.is_ok());
1397        assert!(db.create_library(&user_id, "ValidName123").await.is_ok());
1398    }
1399
1400    #[tokio::test]
1401    async fn test_upsert_and_get_snippets() {
1402        let db = setup_db().await;
1403        let user_id = db.create_user("key").await.unwrap();
1404        let lib_id = db.get_default_library(&user_id).await.unwrap();
1405
1406        let snippet = Snippet {
1407            id: "snip-1".to_string(),
1408            description: "Test snippet".to_string(),
1409            command: "echo hello".to_string(),
1410            tags: vec!["test".to_string()],
1411            created_at: 100,
1412            updated_at: 100,
1413            device_id: "device-1".to_string(),
1414            deleted: false,
1415            encrypted: false,
1416        };
1417
1418        db.upsert_snippet(&snippet, &user_id, &lib_id)
1419            .await
1420            .unwrap();
1421
1422        let (snippets, total) = db
1423            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1424            .await
1425            .unwrap();
1426        assert_eq!(total, 1);
1427        assert_eq!(snippets[0].description, "Test snippet");
1428        assert_eq!(snippets[0].command, "echo hello");
1429        assert_eq!(snippets[0].tags, vec!["test"]);
1430    }
1431
1432    /// Delta queries must include rows at exactly the watermark: the cursor
1433    /// has second granularity and the client stores `MAX(visible_at)` as
1434    /// `last_sync`. With strict `>`, a row written in the same second as the
1435    /// watermark was invisible to every later incremental sync.
1436    #[tokio::test]
1437    async fn test_delta_query_includes_watermark_boundary_row() {
1438        let db = setup_db().await;
1439        let user_id = db.create_user("key").await.unwrap();
1440        let lib_id = db.get_default_library(&user_id).await.unwrap();
1441
1442        let snippet = Snippet {
1443            id: "boundary-snip".to_string(),
1444            description: "Boundary".to_string(),
1445            command: "echo boundary".to_string(),
1446            tags: vec![],
1447            created_at: 500,
1448            updated_at: 500,
1449            device_id: "device-1".to_string(),
1450            deleted: false,
1451            encrypted: false,
1452        };
1453        db.upsert_snippet(&snippet, &user_id, &lib_id)
1454            .await
1455            .unwrap();
1456
1457        let latest = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1458        assert_eq!(latest, 500);
1459
1460        // A subsequent incremental sync sends `since = MAX(visible_at)`.
1461        let (snippets, total) = db
1462            .get_snippets(&user_id, &lib_id, latest, 10, 0, false)
1463            .await
1464            .unwrap();
1465        assert_eq!(total, 1, "row at exactly `since` must be returned");
1466        assert_eq!(snippets[0].id, "boundary-snip");
1467    }
1468
1469    /// A row whose client-chosen `updated_at` sits *below* the library's
1470    /// watermark must still be delivered: the delta cursor lives in
1471    /// `visible_at`, which is clamped to the watermark, while `updated_at`
1472    /// keeps the client's logical modification time.
1473    #[tokio::test]
1474    async fn test_insert_below_watermark_is_still_delivered() {
1475        let db = setup_db().await;
1476        let user_id = db.create_user("key").await.unwrap();
1477        let lib_id = db.get_default_library(&user_id).await.unwrap();
1478
1479        let make = |id: &str, updated_at: i64| Snippet {
1480            id: id.to_string(),
1481            description: format!("desc-{id}"),
1482            command: format!("echo {id}"),
1483            tags: vec![],
1484            created_at: updated_at,
1485            updated_at,
1486            device_id: "device-1".to_string(),
1487            deleted: false,
1488            encrypted: false,
1489        };
1490
1491        let mut tx = db.pool.begin().await.unwrap();
1492        assert_eq!(
1493            db.upsert_snippet_in_tx(&mut tx, &make("newer", 1000), &user_id, &lib_id)
1494                .await
1495                .unwrap(),
1496            UpsertOutcome::Written
1497        );
1498        assert_eq!(
1499            db.upsert_snippet_in_tx(&mut tx, &make("older", 500), &user_id, &lib_id)
1500                .await
1501                .unwrap(),
1502            UpsertOutcome::Written
1503        );
1504        tx.commit().await.unwrap();
1505
1506        let watermark = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1507        assert_eq!(watermark, 1000);
1508
1509        let (snippets, total) = db
1510            .get_snippets(&user_id, &lib_id, watermark, 10, 0, false)
1511            .await
1512            .unwrap();
1513        assert_eq!(
1514            total, 2,
1515            "the row pushed below the watermark must still be delivered by a delta pull"
1516        );
1517
1518        // The stored logical time is the client's own value, unchanged.
1519        let older = snippets
1520            .iter()
1521            .find(|s| s.id == "older")
1522            .expect("the older row must be present");
1523        assert_eq!(older.updated_at, 500);
1524    }
1525
1526    /// The cursor bookkeeping must not change the server's last-write-wins
1527    /// rule: the comparison uses the client's raw `updated_at`, so a newer
1528    /// push of a row that was stored below the watermark still wins.
1529    #[tokio::test]
1530    async fn test_update_below_the_watermark_still_uses_the_raw_timestamp() {
1531        let db = setup_db().await;
1532        let user_id = db.create_user("key").await.unwrap();
1533        let lib_id = db.get_default_library(&user_id).await.unwrap();
1534
1535        let make = |id: &str, command: &str, updated_at: i64| Snippet {
1536            id: id.to_string(),
1537            description: "desc".to_string(),
1538            command: command.to_string(),
1539            tags: vec![],
1540            created_at: 0,
1541            updated_at,
1542            device_id: "device-1".to_string(),
1543            deleted: false,
1544            encrypted: false,
1545        };
1546
1547        // `other` sets the watermark at 1000.
1548        let mut tx = db.pool.begin().await.unwrap();
1549        db.upsert_snippet_in_tx(
1550            &mut tx,
1551            &make("other", "echo other", 1000),
1552            &user_id,
1553            &lib_id,
1554        )
1555        .await
1556        .unwrap();
1557        tx.commit().await.unwrap();
1558
1559        // This row lands below the watermark.
1560        let mut tx = db.pool.begin().await.unwrap();
1561        assert_eq!(
1562            db.upsert_snippet_in_tx(&mut tx, &make("row", "echo one", 500), &user_id, &lib_id)
1563                .await
1564                .unwrap(),
1565            UpsertOutcome::Written
1566        );
1567        tx.commit().await.unwrap();
1568
1569        // A newer push of the same row must win: the comparison reads the raw
1570        // `updated_at`, not the clamped cursor.
1571        let mut tx = db.pool.begin().await.unwrap();
1572        assert_eq!(
1573            db.upsert_snippet_in_tx(&mut tx, &make("row", "echo two", 700), &user_id, &lib_id)
1574                .await
1575                .unwrap(),
1576            UpsertOutcome::Written
1577        );
1578        tx.commit().await.unwrap();
1579
1580        // A genuinely older push is still rejected.
1581        let mut tx = db.pool.begin().await.unwrap();
1582        assert_eq!(
1583            db.upsert_snippet_in_tx(&mut tx, &make("row", "echo older", 600), &user_id, &lib_id)
1584                .await
1585                .unwrap(),
1586            UpsertOutcome::Stale
1587        );
1588        tx.commit().await.unwrap();
1589
1590        let (snippets, _) = db
1591            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1592            .await
1593            .unwrap();
1594        let row = snippets
1595            .iter()
1596            .find(|s| s.id == "row")
1597            .expect("the updated snippet must be present");
1598        assert_eq!(row.command, "echo two");
1599        assert_eq!(row.updated_at, 700);
1600    }
1601
1602    /// An existing database is upgraded in place: the backfill keeps every
1603    /// row at its current cursor position, so the rows a client pulls do not
1604    /// change across the migration.
1605    #[tokio::test]
1606    async fn test_migrate_snippet_visible_at_backfills_the_cursor() {
1607        let temp = tempfile::tempdir().unwrap();
1608        let db_path = temp.path().join("legacy.sqlite");
1609        let db = Database::connect(db_path.to_str().unwrap(), 1)
1610            .await
1611            .unwrap();
1612        let user_id = db.create_user("key").await.unwrap();
1613        let lib_id = db.get_default_library(&user_id).await.unwrap();
1614
1615        // Simulate a pre-migration database: the column does not exist.
1616        sqlx::query("DROP INDEX IF EXISTS idx_snippets_user_library_visible")
1617            .execute(db.pool())
1618            .await
1619            .unwrap();
1620        sqlx::query("ALTER TABLE snippets DROP COLUMN visible_at")
1621            .execute(db.pool())
1622            .await
1623            .unwrap();
1624        let snippet = Snippet {
1625            id: "legacy-1".to_string(),
1626            description: "legacy".to_string(),
1627            command: "echo legacy".to_string(),
1628            tags: vec![],
1629            created_at: 100,
1630            updated_at: 100,
1631            device_id: "device-1".to_string(),
1632            deleted: false,
1633            encrypted: false,
1634        };
1635        sqlx::query(
1636            "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
1637             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
1638        )
1639        .bind(&snippet.id)
1640        .bind(&user_id)
1641        .bind(&lib_id)
1642        .bind(&snippet.description)
1643        .bind(&snippet.command)
1644        .bind("[]")
1645        .bind(snippet.created_at)
1646        .bind(snippet.updated_at)
1647        .bind(&snippet.device_id)
1648        .bind(0)
1649        .bind(0)
1650        .execute(db.pool())
1651        .await
1652        .unwrap();
1653
1654        assert!(db.migrate_snippet_visible_at().await.unwrap());
1655        // A second run is a no-op.
1656        assert!(!db.migrate_snippet_visible_at().await.unwrap());
1657
1658        assert_eq!(
1659            db.get_latest_timestamp(&user_id, &lib_id).await.unwrap(),
1660            100
1661        );
1662        let (snippets, total) = db
1663            .get_snippets(&user_id, &lib_id, 100, 10, 0, false)
1664            .await
1665            .unwrap();
1666        assert_eq!(total, 1);
1667        assert_eq!(snippets[0].id, "legacy-1");
1668        assert_eq!(snippets[0].updated_at, 100);
1669    }
1670
1671    #[tokio::test]
1672    async fn test_delete_library_preserves_future_tombstone_timestamp() {
1673        let db = setup_db().await;
1674        let user_id = db.create_user("key").await.unwrap();
1675        let library_id = db.create_library(&user_id, "future-delete").await.unwrap();
1676        let future_timestamp = OffsetDateTime::now_utc().unix_timestamp() + 100;
1677
1678        let snippet = Snippet {
1679            id: "future-snippet".to_string(),
1680            description: "desc".to_string(),
1681            command: "echo hi".to_string(),
1682            tags: vec![],
1683            created_at: 0,
1684            updated_at: future_timestamp,
1685            device_id: "device".to_string(),
1686            deleted: false,
1687            encrypted: false,
1688        };
1689        db.upsert_snippet(&snippet, &user_id, &library_id)
1690            .await
1691            .unwrap();
1692
1693        db.delete_library(&user_id, &library_id).await.unwrap();
1694
1695        let (snippets, total) = db
1696            .get_snippets(&user_id, &library_id, future_timestamp - 1, 10, 0, true)
1697            .await
1698            .unwrap();
1699        assert_eq!(total, 1);
1700        assert!(snippets[0].deleted);
1701        assert_eq!(snippets[0].updated_at, future_timestamp);
1702    }
1703
1704    #[tokio::test]
1705    async fn test_upsert_updates_existing() {
1706        let db = setup_db().await;
1707        let user_id = db.create_user("key").await.unwrap();
1708        let lib_id = db.get_default_library(&user_id).await.unwrap();
1709
1710        let snippet = Snippet {
1711            id: "snip-1".to_string(),
1712            description: "Original".to_string(),
1713            command: "echo old".to_string(),
1714            tags: vec![],
1715            created_at: 100,
1716            updated_at: 100,
1717            device_id: "device-1".to_string(),
1718            deleted: false,
1719            encrypted: false,
1720        };
1721        db.upsert_snippet(&snippet, &user_id, &lib_id)
1722            .await
1723            .unwrap();
1724
1725        // Update with newer timestamp
1726        let updated = Snippet {
1727            id: "snip-1".to_string(),
1728            description: "Updated".to_string(),
1729            command: "echo new".to_string(),
1730            tags: vec!["updated".to_string()],
1731            created_at: 100,
1732            updated_at: 200,
1733            device_id: "device-1".to_string(),
1734            deleted: false,
1735            encrypted: false,
1736        };
1737        db.upsert_snippet(&updated, &user_id, &lib_id)
1738            .await
1739            .unwrap();
1740
1741        let (snippets, _) = db
1742            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1743            .await
1744            .unwrap();
1745        assert_eq!(snippets.len(), 1);
1746        assert_eq!(snippets[0].description, "Updated");
1747        assert_eq!(snippets[0].command, "echo new");
1748    }
1749
1750    #[tokio::test]
1751    async fn test_upsert_rejects_older_timestamp() {
1752        let db = setup_db().await;
1753        let user_id = db.create_user("key").await.unwrap();
1754        let lib_id = db.get_default_library(&user_id).await.unwrap();
1755
1756        // Insert snippet at updated_at: 200
1757        let snippet = Snippet {
1758            id: "snip-1".to_string(),
1759            description: "Newer".to_string(),
1760            command: "echo newer".to_string(),
1761            tags: vec![],
1762            created_at: 100,
1763            updated_at: 200,
1764            device_id: "d".to_string(),
1765            deleted: false,
1766            encrypted: false,
1767        };
1768        db.upsert_snippet(&snippet, &user_id, &lib_id)
1769            .await
1770            .unwrap();
1771
1772        // Attempt to upsert same ID with older timestamp (should be rejected)
1773        let older = Snippet {
1774            id: "snip-1".to_string(),
1775            description: "Older".to_string(),
1776            command: "echo older".to_string(),
1777            tags: vec![],
1778            created_at: 100,
1779            updated_at: 100,
1780            device_id: "d".to_string(),
1781            deleted: false,
1782            encrypted: false,
1783        };
1784        db.upsert_snippet(&older, &user_id, &lib_id).await.unwrap();
1785
1786        // Newer data should be preserved
1787        let (snippets, _) = db
1788            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1789            .await
1790            .unwrap();
1791        assert_eq!(snippets.len(), 1);
1792        assert_eq!(snippets[0].description, "Newer");
1793        assert_eq!(snippets[0].command, "echo newer");
1794    }
1795
1796    #[tokio::test]
1797    async fn test_get_latest_timestamp() {
1798        let db = setup_db().await;
1799        let user_id = db.create_user("key").await.unwrap();
1800        let lib_id = db.get_default_library(&user_id).await.unwrap();
1801
1802        // Empty library should return 0
1803        let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1804        assert_eq!(ts, 0);
1805
1806        let snippet = Snippet {
1807            id: "snip-1".to_string(),
1808            description: "Test".to_string(),
1809            command: "echo".to_string(),
1810            tags: vec![],
1811            created_at: 100,
1812            updated_at: 500,
1813            device_id: "d".to_string(),
1814            deleted: false,
1815            encrypted: false,
1816        };
1817        db.upsert_snippet(&snippet, &user_id, &lib_id)
1818            .await
1819            .unwrap();
1820
1821        let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1822        assert_eq!(ts, 500);
1823    }
1824
1825    #[tokio::test]
1826    async fn test_delete_library() {
1827        let db = setup_db().await;
1828        let user_id = db.create_user("key").await.unwrap();
1829        let lib_id = db.create_library(&user_id, "temp").await.unwrap();
1830
1831        // Add a snippet
1832        let snippet = Snippet {
1833            id: "snip-1".to_string(),
1834            description: "Test".to_string(),
1835            command: "echo".to_string(),
1836            tags: vec![],
1837            created_at: 100,
1838            updated_at: 100,
1839            device_id: "d".to_string(),
1840            deleted: false,
1841            encrypted: false,
1842        };
1843        db.upsert_snippet(&snippet, &user_id, &lib_id)
1844            .await
1845            .unwrap();
1846
1847        // Delete the library
1848        db.delete_library(&user_id, &lib_id).await.unwrap();
1849
1850        // Should fail to delete again
1851        assert!(db.delete_library(&user_id, &lib_id).await.is_err());
1852
1853        // Snippet should be marked deleted
1854        let (snippets, _) = db
1855            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1856            .await
1857            .unwrap();
1858        assert_eq!(snippets.len(), 0);
1859    }
1860
1861    #[tokio::test]
1862    async fn test_migrate_plaintext_api_keys() {
1863        let db = setup_db().await;
1864        let user_id = Uuid::new_v4().to_string();
1865        let now = OffsetDateTime::now_utc().unix_timestamp();
1866
1867        // Insert a plaintext API key directly (simulating old schema)
1868        sqlx::query("INSERT INTO users (id, api_key, created_at, updated_at) VALUES (?, ?, ?, ?)")
1869            .bind(&user_id)
1870            .bind("plaintext-api-key")
1871            .bind(now)
1872            .bind(now)
1873            .execute(&db.pool)
1874            .await
1875            .unwrap();
1876
1877        // Run migration
1878        let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1879        assert_eq!(migrated, 1);
1880
1881        // Verify the key is now hashed
1882        let (stored_hash,): (String,) = sqlx::query_as("SELECT api_key FROM users WHERE id = ?")
1883            .bind(&user_id)
1884            .fetch_one(&db.pool)
1885            .await
1886            .unwrap();
1887
1888        assert!(stored_hash.starts_with("$argon2"));
1889
1890        // Verify prefix was backfilled
1891        let (prefix,): (Option<String>,) =
1892            sqlx::query_as("SELECT api_key_prefix FROM users WHERE id = ?")
1893                .bind(&user_id)
1894                .fetch_one(&db.pool)
1895                .await
1896                .unwrap();
1897        assert!(
1898            prefix.is_some(),
1899            "API key prefix should be backfilled by migration"
1900        );
1901
1902        // Verify the original key still works
1903        let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1904        assert_eq!(found, Some(user_id));
1905
1906        // Running migration again should migrate 0
1907        let migrated_again = db.migrate_plaintext_api_keys().await.unwrap();
1908        assert_eq!(migrated_again, 0);
1909    }
1910
1911    #[tokio::test]
1912    async fn test_api_key_prefix_lookup() {
1913        let db = setup_db().await;
1914
1915        // Create multiple users
1916        let user1 = db.create_user("key-one-alpha").await.unwrap();
1917        let user2 = db.create_user("key-two-beta").await.unwrap();
1918        let user3 = db.create_user("key-three-gamma").await.unwrap();
1919
1920        // Each user's key should resolve correctly
1921        assert_eq!(
1922            db.get_user_by_api_key("key-one-alpha").await.unwrap(),
1923            Some(user1)
1924        );
1925        assert_eq!(
1926            db.get_user_by_api_key("key-two-beta").await.unwrap(),
1927            Some(user2)
1928        );
1929        assert_eq!(
1930            db.get_user_by_api_key("key-three-gamma").await.unwrap(),
1931            Some(user3)
1932        );
1933        assert_eq!(db.get_user_by_api_key("nonexistent").await.unwrap(), None);
1934
1935        // Verify all rows have prefixes
1936        let rows: Vec<(String,)> =
1937            sqlx::query_as("SELECT api_key_prefix FROM users WHERE api_key_prefix IS NOT NULL")
1938                .fetch_all(&db.pool)
1939                .await
1940                .unwrap();
1941        assert_eq!(rows.len(), 3);
1942    }
1943
1944    #[tokio::test]
1945    async fn test_library_ownership_verification() {
1946        let db = setup_db().await;
1947        let user1_id = db.create_user("user1-api-key").await.unwrap();
1948        let user2_id = db.create_user("user2-api-key").await.unwrap();
1949        let lib_id = db.create_library(&user1_id, "private").await.unwrap();
1950
1951        assert!(
1952            db.verify_library_ownership(&user1_id, &lib_id)
1953                .await
1954                .unwrap()
1955        );
1956        assert!(
1957            !db.verify_library_ownership(&user2_id, &lib_id)
1958                .await
1959                .unwrap()
1960        );
1961
1962        let nonexistent_lib = Uuid::new_v4().to_string();
1963        assert!(
1964            !db.verify_library_ownership(&user1_id, &nonexistent_lib)
1965                .await
1966                .unwrap()
1967        );
1968    }
1969
1970    #[tokio::test]
1971    async fn test_library_ownership_prevents_cross_user_access() {
1972        let db = setup_db().await;
1973        let user1_id = db.create_user("user1-key").await.unwrap();
1974        let user2_id = db.create_user("user2-key").await.unwrap();
1975        let user1_lib = db.create_library(&user1_id, "my-library").await.unwrap();
1976
1977        let snippet = Snippet {
1978            id: "snip-1".to_string(),
1979            description: "User 1 secret".to_string(),
1980            command: "secret command".to_string(),
1981            tags: vec![],
1982            created_at: 100,
1983            updated_at: 100,
1984            device_id: "device-1".to_string(),
1985            deleted: false,
1986            encrypted: false,
1987        };
1988
1989        db.upsert_snippet(&snippet, &user1_id, &user1_lib)
1990            .await
1991            .unwrap();
1992
1993        let (snippets, _) = db
1994            .get_snippets(&user1_id, &user1_lib, 0, 10, 0, false)
1995            .await
1996            .unwrap();
1997        assert_eq!(snippets.len(), 1);
1998
1999        let (snippets, _) = db
2000            .get_snippets(&user2_id, &user1_lib, 0, 10, 0, false)
2001            .await
2002            .unwrap();
2003        assert_eq!(snippets.len(), 0);
2004    }
2005
2006    #[tokio::test]
2007    async fn test_deleted_library_not_owned() {
2008        let db = setup_db().await;
2009        let user_id = db.create_user("test-key").await.unwrap();
2010        let lib_id = db.create_library(&user_id, "temp-lib").await.unwrap();
2011
2012        assert!(
2013            db.verify_library_ownership(&user_id, &lib_id)
2014                .await
2015                .unwrap()
2016        );
2017
2018        db.delete_library(&user_id, &lib_id).await.unwrap();
2019
2020        assert!(
2021            !db.verify_library_ownership(&user_id, &lib_id)
2022                .await
2023                .unwrap()
2024        );
2025    }
2026
2027    #[tokio::test]
2028    async fn test_get_snippets_include_deleted() {
2029        let db = setup_db().await;
2030        let user_id = db.create_user("key").await.unwrap();
2031        let lib_id = db.get_default_library(&user_id).await.unwrap();
2032
2033        let snippet = Snippet {
2034            id: "snip-1".to_string(),
2035            description: "Test".to_string(),
2036            command: "echo".to_string(),
2037            tags: vec![],
2038            created_at: 100,
2039            updated_at: 100,
2040            device_id: "d".to_string(),
2041            deleted: false,
2042            encrypted: false,
2043        };
2044        db.upsert_snippet(&snippet, &user_id, &lib_id)
2045            .await
2046            .unwrap();
2047
2048        // Soft-delete the snippet
2049        let deleted = Snippet {
2050            id: "snip-1".to_string(),
2051            description: "Test".to_string(),
2052            command: "echo".to_string(),
2053            tags: vec![],
2054            created_at: 100,
2055            updated_at: 200,
2056            device_id: "d".to_string(),
2057            deleted: true,
2058            encrypted: false,
2059        };
2060        db.upsert_snippet(&deleted, &user_id, &lib_id)
2061            .await
2062            .unwrap();
2063
2064        // Without include_deleted: should not see deleted snippet
2065        let (snippets, _) = db
2066            .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
2067            .await
2068            .unwrap();
2069        assert_eq!(snippets.len(), 0);
2070
2071        // With include_deleted: should see deleted snippet
2072        let (snippets, _) = db
2073            .get_snippets(&user_id, &lib_id, 0, 10, 0, true)
2074            .await
2075            .unwrap();
2076        assert_eq!(snippets.len(), 1);
2077        assert!(snippets[0].deleted);
2078        assert_eq!(snippets[0].updated_at, 200);
2079    }
2080
2081    // ---------------------------------------------------------------------
2082    // Write-sequence cursor
2083    // ---------------------------------------------------------------------
2084
2085    fn sequence_snippet(id: &str, updated_at: i64) -> Snippet {
2086        Snippet {
2087            id: id.to_string(),
2088            description: "Test".to_string(),
2089            command: "echo".to_string(),
2090            tags: vec![],
2091            created_at: updated_at,
2092            updated_at,
2093            device_id: "d".to_string(),
2094            deleted: false,
2095            encrypted: false,
2096        }
2097    }
2098
2099    async fn snippet_sequences(db: &Database, library_id: &str) -> Vec<(String, i64)> {
2100        sqlx::query_as("SELECT id, sequence FROM snippets WHERE library_id = ? ORDER BY sequence")
2101            .bind(library_id)
2102            .fetch_all(db.pool())
2103            .await
2104            .unwrap()
2105    }
2106
2107    /// The sequence watermark is the highest sequence the library holds.
2108    #[tokio::test]
2109    async fn test_sequence_watermark_is_the_library_maximum() {
2110        let db = setup_db().await;
2111        let user_id = db.create_user("key").await.unwrap();
2112        let lib_id = db.get_default_library(&user_id).await.unwrap();
2113
2114        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 0);
2115
2116        db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2117            .await
2118            .unwrap();
2119        db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2120            .await
2121            .unwrap();
2122
2123        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 2);
2124        assert_eq!(snippet_sequences(&db, &lib_id).await.len(), 2);
2125    }
2126
2127    /// Every write takes a sequence strictly greater than the library's
2128    /// current maximum, whatever timestamp the client pushed — including a
2129    /// push whose timestamp is older than the rows already stored.
2130    #[tokio::test]
2131    async fn test_write_assigns_a_sequence_above_the_library_maximum() {
2132        let db = setup_db().await;
2133        let user_id = db.create_user("key").await.unwrap();
2134        let lib_id = db.get_default_library(&user_id).await.unwrap();
2135
2136        db.upsert_snippet(&sequence_snippet("a", 300), &user_id, &lib_id)
2137            .await
2138            .unwrap();
2139        db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_id)
2140            .await
2141            .unwrap();
2142        db.upsert_snippet(&sequence_snippet("c", 200), &user_id, &lib_id)
2143            .await
2144            .unwrap();
2145
2146        let rows = snippet_sequences(&db, &lib_id).await;
2147        let sequences: Vec<i64> = rows.iter().map(|(_, sequence)| *sequence).collect();
2148        assert_eq!(sequences, vec![1, 2, 3]);
2149        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 3);
2150    }
2151
2152    /// Two concurrent pushes to one library take distinct sequence values.
2153    ///
2154    /// The counter is bumped inside the write transaction, so the two
2155    /// transactions serialize on it; the unique index on
2156    /// `(library_id, sequence)` is the enforcement that turns a shared
2157    /// value into a loud failure instead of a silent one.
2158    #[tokio::test]
2159    async fn test_concurrent_pushes_to_one_library_get_distinct_sequences() {
2160        // A file-backed database: the in-memory database shares one cache
2161        // across the pool, which serializes writes with a table lock
2162        // instead of the write lock a real deployment uses.
2163        let temp = tempfile::tempdir().unwrap();
2164        let db_path = temp.path().join("concurrent.sqlite");
2165        let db = Database::connect(db_path.to_str().unwrap(), 5)
2166            .await
2167            .unwrap();
2168        let user_id = db.create_user("key").await.unwrap();
2169        let lib_id = db.get_default_library(&user_id).await.unwrap();
2170
2171        let first_snippet = sequence_snippet("a", 100);
2172        let second_snippet = sequence_snippet("b", 200);
2173        let first = db.upsert_snippet(&first_snippet, &user_id, &lib_id);
2174        let second = db.upsert_snippet(&second_snippet, &user_id, &lib_id);
2175        let (first, second) = tokio::join!(first, second);
2176        first.unwrap();
2177        second.unwrap();
2178
2179        let rows = snippet_sequences(&db, &lib_id).await;
2180        assert_eq!(rows.len(), 2);
2181        assert_ne!(
2182            rows[0].1, rows[1].1,
2183            "concurrent writes to one library must not share a sequence value"
2184        );
2185    }
2186
2187    /// A rolled-back write consumes no sequence value: the counter bump
2188    /// shares the transaction with the row write.
2189    #[tokio::test]
2190    async fn test_rolled_back_write_does_not_consume_a_sequence_value() {
2191        let db = setup_db().await;
2192        let user_id = db.create_user("key").await.unwrap();
2193        let lib_id = db.get_default_library(&user_id).await.unwrap();
2194
2195        let mut tx = db.pool().begin().await.unwrap();
2196        db.upsert_snippet_in_tx(&mut tx, &sequence_snippet("a", 100), &user_id, &lib_id)
2197            .await
2198            .unwrap();
2199        tx.rollback().await.unwrap();
2200
2201        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 0);
2202        assert!(snippet_sequences(&db, &lib_id).await.is_empty());
2203
2204        // The next write takes the first sequence value.
2205        db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_id)
2206            .await
2207            .unwrap();
2208        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 1);
2209    }
2210
2211    /// The unique index is the enforcement of the counter race: two rows
2212    /// that share a `(library_id, sequence)` are rejected loudly.
2213    #[tokio::test]
2214    async fn test_unique_index_rejects_a_shared_sequence_value() {
2215        let db = setup_db().await;
2216        let user_id = db.create_user("key").await.unwrap();
2217        let lib_id = db.get_default_library(&user_id).await.unwrap();
2218
2219        let insert = |id: &str, sequence: i64| {
2220            sqlx::query(
2221                "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted, visible_at, sequence)
2222                 VALUES (?, ?, ?, 'd', 'echo', '[]', 1, 1, 'dev', 0, 0, 1, ?)",
2223            )
2224            .bind(id)
2225            .bind(&user_id)
2226            .bind(&lib_id)
2227            .bind(sequence)
2228        };
2229
2230        insert("a", 1).execute(db.pool()).await.unwrap();
2231        let error = insert("b", 1).execute(db.pool()).await.unwrap_err();
2232        assert!(
2233            matches!(error, sqlx::Error::Database(_)),
2234            "a shared sequence value must be rejected by the unique index, got {error:?}"
2235        );
2236    }
2237
2238    /// The migration backfills the sequence so that the backfilled ordering
2239    /// preserves the pre-migration `updated_at` ordering, and seeds the
2240    /// counter from the backfill so the next write cannot collide.
2241    #[tokio::test]
2242    async fn test_migrate_snippet_sequence_backfills_the_updated_at_ordering() {
2243        let temp = tempfile::tempdir().unwrap();
2244        let db_path = temp.path().join("legacy.sqlite");
2245        let db = Database::connect(db_path.to_str().unwrap(), 1)
2246            .await
2247            .unwrap();
2248        let user_id = db.create_user("key").await.unwrap();
2249        let lib_id = db.get_default_library(&user_id).await.unwrap();
2250
2251        // Simulate a pre-migration database: no sequence column, no indexes.
2252        sqlx::query("DROP INDEX IF EXISTS idx_snippets_library_sequence")
2253            .execute(db.pool())
2254            .await
2255            .unwrap();
2256        sqlx::query("DROP INDEX IF EXISTS idx_snippets_user_library_sequence")
2257            .execute(db.pool())
2258            .await
2259            .unwrap();
2260        sqlx::query("ALTER TABLE snippets DROP COLUMN sequence")
2261            .execute(db.pool())
2262            .await
2263            .unwrap();
2264
2265        // Three legacy rows, inserted out of `updated_at` order.
2266        for (id, updated_at) in [("c", 300_i64), ("a", 100), ("b", 200)] {
2267            sqlx::query(
2268                "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
2269                 VALUES (?, ?, ?, 'd', 'echo', '[]', ?, ?, 'dev', 0, 0)",
2270            )
2271            .bind(id)
2272            .bind(&user_id)
2273            .bind(&lib_id)
2274            .bind(updated_at)
2275            .bind(updated_at)
2276            .execute(db.pool())
2277            .await
2278            .unwrap();
2279        }
2280
2281        assert!(db.migrate_snippet_sequence().await.unwrap());
2282        // A second run is a no-op.
2283        assert!(!db.migrate_snippet_sequence().await.unwrap());
2284
2285        let rows: Vec<(String, i64, i64)> =
2286            sqlx::query_as("SELECT id, updated_at, sequence FROM snippets ORDER BY sequence")
2287                .fetch_all(db.pool())
2288                .await
2289                .unwrap();
2290        assert_eq!(rows.len(), 3);
2291        // The backfilled ordering preserves the pre-migration `updated_at`
2292        // ordering.
2293        assert_eq!(rows[0].0, "a");
2294        assert_eq!(rows[1].0, "b");
2295        assert_eq!(rows[2].0, "c");
2296        assert!(rows[0].2 < rows[1].2 && rows[1].2 < rows[2].2);
2297
2298        // The counter is seeded from the backfill, so the next write takes a
2299        // fresh value instead of colliding with a backfilled row.
2300        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 3);
2301        db.upsert_snippet(&sequence_snippet("d", 400), &user_id, &lib_id)
2302            .await
2303            .unwrap();
2304        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 4);
2305    }
2306
2307    /// A client that holds a sequence cursor receives exactly the rows
2308    /// written after that sequence.
2309    #[tokio::test]
2310    async fn test_sequence_cursor_selects_the_rows_written_after_it() {
2311        let db = setup_db().await;
2312        let user_id = db.create_user("key").await.unwrap();
2313        let lib_id = db.get_default_library(&user_id).await.unwrap();
2314
2315        db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2316            .await
2317            .unwrap();
2318        db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2319            .await
2320            .unwrap();
2321        db.upsert_snippet(&sequence_snippet("c", 300), &user_id, &lib_id)
2322            .await
2323            .unwrap();
2324
2325        let page = db
2326            .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(2), 100, 0, true)
2327            .await
2328            .unwrap();
2329        assert_eq!(page.total, 2);
2330        let mut ids: Vec<&str> = page
2331            .snippets
2332            .iter()
2333            .map(|snippet| snippet.id.as_str())
2334            .collect();
2335        ids.sort_unstable();
2336        assert_eq!(ids, vec!["b", "c"]);
2337
2338        // The cursor is inclusive: the boundary row is re-delivered, which
2339        // is safe because merges are idempotent by snippet identity.
2340        let page = db
2341            .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(3), 100, 0, true)
2342            .await
2343            .unwrap();
2344        assert_eq!(page.total, 1);
2345        assert_eq!(page.snippets[0].id, "c");
2346    }
2347
2348    /// An old client that sends no sequence cursor receives the rows the
2349    /// timestamp filter would have selected.
2350    #[tokio::test]
2351    async fn test_timestamp_cursor_still_selects_the_timestamp_rows() {
2352        let db = setup_db().await;
2353        let user_id = db.create_user("key").await.unwrap();
2354        let lib_id = db.get_default_library(&user_id).await.unwrap();
2355
2356        db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2357            .await
2358            .unwrap();
2359        db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2360            .await
2361            .unwrap();
2362
2363        let timestamp_page = db
2364            .get_snippets_page(&user_id, &lib_id, DeltaCursor::Timestamp(0), 100, 0, true)
2365            .await
2366            .unwrap();
2367        let sequence_page = db
2368            .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(0), 100, 0, true)
2369            .await
2370            .unwrap();
2371
2372        assert_eq!(timestamp_page.total, 2);
2373        assert_eq!(timestamp_page.total, sequence_page.total);
2374        let mut timestamp_ids: Vec<&str> = timestamp_page
2375            .snippets
2376            .iter()
2377            .map(|snippet| snippet.id.as_str())
2378            .collect();
2379        let mut sequence_ids: Vec<&str> = sequence_page
2380            .snippets
2381            .iter()
2382            .map(|snippet| snippet.id.as_str())
2383            .collect();
2384        timestamp_ids.sort_unstable();
2385        sequence_ids.sort_unstable();
2386        assert_eq!(timestamp_ids, sequence_ids);
2387    }
2388
2389    /// The delta filter stays scoped per user and per library: a sequence
2390    /// cursor from one library must not select another library's rows, and
2391    /// another user's rows are never selected.
2392    #[tokio::test]
2393    async fn test_sequence_cursor_is_scoped_to_its_library_and_user() {
2394        let db = setup_db().await;
2395        let user_id = db.create_user("key").await.unwrap();
2396        let lib_a = db.get_default_library(&user_id).await.unwrap();
2397        let lib_b = db.create_library(&user_id, "other").await.unwrap();
2398
2399        let other_user = db.create_user("other-key").await.unwrap();
2400        let other_lib = db.get_default_library(&other_user).await.unwrap();
2401
2402        db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_a)
2403            .await
2404            .unwrap();
2405        // Both libraries hold sequence value 1: the sequence space is
2406        // per library.
2407        db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_b)
2408            .await
2409            .unwrap();
2410        db.upsert_snippet(&sequence_snippet("c", 100), &other_user, &other_lib)
2411            .await
2412            .unwrap();
2413
2414        let page = db
2415            .get_snippets_page(&user_id, &lib_a, DeltaCursor::Sequence(1), 100, 0, true)
2416            .await
2417            .unwrap();
2418        assert_eq!(page.total, 1);
2419        assert_eq!(page.snippets[0].id, "a");
2420
2421        // Another user's library is never selected.
2422        let page = db
2423            .get_snippets_page(
2424                &other_user,
2425                &other_lib,
2426                DeltaCursor::Sequence(1),
2427                100,
2428                0,
2429                true,
2430            )
2431            .await
2432            .unwrap();
2433        assert_eq!(page.total, 1);
2434        assert_eq!(page.snippets[0].id, "c");
2435    }
2436
2437    /// A tombstone takes a fresh write sequence, so a client on the sequence
2438    /// cursor sees the deletion.
2439    #[tokio::test]
2440    async fn test_delete_library_gives_the_tombstone_a_fresh_sequence() {
2441        let db = setup_db().await;
2442        let user_id = db.create_user("key").await.unwrap();
2443        let lib_id = db.get_default_library(&user_id).await.unwrap();
2444
2445        db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2446            .await
2447            .unwrap();
2448        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 1);
2449
2450        db.delete_library(&user_id, &lib_id).await.unwrap();
2451
2452        assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 2);
2453        let page = db
2454            .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(2), 100, 0, true)
2455            .await
2456            .unwrap();
2457        assert_eq!(page.total, 1);
2458        assert!(page.snippets[0].deleted);
2459    }
2460}