notescraft/apps/notescraft-sync-server/tests/store.rs

//! Store level: idempotent migration of old databases, device lifecycle, audit row order.

use notescraft_sync_server::Store;

/// The legacy schema, before `last_seen_ms`, `revoked` and `updated_ms` existed.
const LEGACY: &str = "
CREATE TABLE vaults (id TEXT PRIMARY KEY, created_ms INTEGER NOT NULL);
CREATE TABLE pairings (code_hash TEXT PRIMARY KEY, vault TEXT NOT NULL, expires_ms INTEGER NOT NULL);
CREATE TABLE devices (id TEXT PRIMARY KEY, vault TEXT NOT NULL, name TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, created_ms INTEGER NOT NULL);
CREATE TABLE files (vault TEXT NOT NULL, path TEXT NOT NULL, hash TEXT NOT NULL, size INTEGER NOT NULL,
    mtime_ms INTEGER NOT NULL, seq INTEGER NOT NULL, deleted INTEGER NOT NULL, PRIMARY KEY (vault, path));
";

fn columns_of(path: &std::path::Path, table: &str) -> Vec<String> {
    let conn = rusqlite::Connection::open(path).expect("open");
    let mut statement = conn.prepare(&format!("PRAGMA table_info({table})")).expect("pragma");
    statement.query_map([], |row| row.get::<_, String>(1)).expect("names").collect::<rusqlite::Result<Vec<String>>>().expect("rows")
}

#[test]
fn migration_adds_missing_columns_and_runs_more_than_once() {
    let dir = std::env::temp_dir().join(format!("nc-sync-store-{}", std::process::id()));
    std::fs::remove_dir_all(&dir).ok();
    std::fs::create_dir_all(&dir).expect("temp");
    let path = dir.join("old.db");
    rusqlite::Connection::open(&path).expect("open").execute_batch(LEGACY).expect("legacy schema");

    Store::open(&path).expect("first open migrates");
    Store::open(&path).expect("second open is idempotent");
    for (table, column) in [
        ("devices", "last_seen_ms"),
        ("devices", "revoked"),
        ("devices", "user_id"),
        ("files", "updated_ms"),
        ("files", "editor_user"),
        ("pairings", "user"),
        ("vaults", "name"),
        ("vaults", "deleted_ms"),
    ] {
        assert!(columns_of(&path, table).iter().any(|name| name == column), "{table}.{column} missing");
    }
    let conn = rusqlite::Connection::open(&path).expect("open");
    for table in ["users", "members", "invites"] {
        // `sqlite_master` row proves the table exists after the migration.
        let count: u32 = conn.query_row("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?1", [table], |row| row.get(0)).expect("query");
        assert_eq!(count, 1, "{table} missing");
    }
    std::fs::remove_dir_all(&dir).expect("cleanup");
}

#[test]
fn rotation_and_revocation_take_the_old_token_out_of_lookup() {
    let store = Store::in_memory().expect("db");
    store.create_user("u-1", "phone user", "#89b4fa", 0).expect("user");
    store.add_device("d", "v", "u-1", "phone", "old-hash", 1).expect("add");
    assert_eq!(store.device_for("old-hash").expect("query"), Some(("d".to_owned(), "u-1".to_owned())));

    assert!(store.rotate_device("d", "v", "new-hash").expect("rotate"));
    assert_eq!(store.device_for("old-hash").expect("query"), None);
    assert_eq!(store.device_for("new-hash").expect("query"), Some(("d".to_owned(), "u-1".to_owned())));

    // `last_seen_ms` moves at most once a minute.
    store.touch_device("new-hash", 10_000).expect("first touch");
    store.touch_device("new-hash", 40_000).expect("too soon, ignored");
    let (_, _, _, last_seen): (String, String, u64, Option<u64>) = store.list_devices("v").expect("rows").remove(0);
    assert_eq!(last_seen, Some(10_000));
    store.touch_device("new-hash", 70_000).expect("a minute later, applied");
    let (_, _, _, last_seen): (String, String, u64, Option<u64>) = store.list_devices("v").expect("rows").remove(0);
    assert_eq!(last_seen, Some(70_000));

    assert!(store.revoke_device("d", "v").expect("revoke"));
    assert_eq!(store.device_for("new-hash").expect("query"), None);
    assert_eq!(store.list_devices("v").expect("rows").len(), 0);
}

#[test]
fn audit_rows_come_back_newest_first_and_capped() {
    let store = Store::in_memory().expect("db");
    for (ts, kind) in [(1_u64, "a"), (2, "b"), (3, "c")] {
        store.audit(ts, kind, Some("v"), Some("d"), None).expect("audit");
    }
    let rows = store.audit_rows(2).expect("rows");
    assert_eq!(rows.iter().map(|row| row.kind.as_str()).collect::<Vec<_>>(), vec!["c", "b"]);
    assert_eq!(rows[0].ts_ms, 3);
    assert_eq!(rows[0].vault.as_deref(), Some("v"));
}