Development Guides

Write a SQLite Migration

Guide for adding schema changes to the workspace database.

Goal

Add a new migration that extends the workspace SQLite schema without breaking existing databases, following the rusqlite_migration versioning pattern used in crates/infrastructure/sqlite/src/migrations/mod.rs.

Prerequisites

Understanding of the schema context you are modifying — read the existing migration constants in crates/infrastructure/sqlite/src/migrations/mod.rs before writing a new one
If adding a table for a new repository, plan the full repository implementation alongside the migration

Steps

1. Understand the current schema version

Open crates/infrastructure/sqlite/src/migrations/mod.rs. Note the current value of CURRENT_WORKSPACE_VERSION and the final entry in workspace_migrations(). Your new migration will be the next version.

pub const CURRENT_WORKSPACE_VERSION: usize = 4;  // will become 5

fn workspace_migrations() -> Migrations<'static> {
    Migrations::new(vec![
        M::up(WORKSPACE_V001),
        M::up(WORKSPACE_V002),
        M::up(WORKSPACE_V003),
        M::up(WORKSPACE_V004),  // currently last
    ])
}

2. Define the migration SQL constant

Add a new constant named WORKSPACE_V00X (zero-padded to three digits) immediately after the previous constant. Write additive SQL only — no destructive changes:

/// Workspace schema v005 — add bookmarks table.
const WORKSPACE_V005: &str = r#"
-- ═══════════════════════════════════════════════════════════════════
-- Named timeline anchors (bookmarks)
-- ═══════════════════════════════════════════════════════════════════

CREATE TABLE bookmarks (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    timestamp TEXT NOT NULL,
    created_by TEXT
);

CREATE INDEX idx_bookmarks_timestamp ON bookmarks(timestamp);
"#;

Rules for the SQL:

CREATE TABLE: Use NOT NULL constraints to match domain invariants.
Indexes: Always use IF NOT EXISTS on indexes so the migration is idempotent if applied twice in unusual recovery scenarios.
ALTER TABLE: Safe for adding columns. SQLite silently defaults missing columns to NULL, so if you add a NOT NULL column without a default the migration will fail on non-empty tables — always provide a DEFAULT for NOT NULL additions to existing tables.
Triggers: Drop the old trigger before recreating it (DROP TRIGGER IF EXISTS followed by CREATE TRIGGER).
FTS5 tables: If you are modifying which columns are indexed in a FTS5 virtual table, you must drop and recreate it, then repopulate with INSERT INTO fts SELECT ....
Never implement a downgrade migration. Migrations are one-way only.

3. Register the migration

Add M::up(WORKSPACE_V005) to the end of the vector in workspace_migrations():

fn workspace_migrations() -> Migrations<'static> {
    Migrations::new(vec![
        M::up(WORKSPACE_V001),
        M::up(WORKSPACE_V002),
        M::up(WORKSPACE_V003),
        M::up(WORKSPACE_V004),
        M::up(WORKSPACE_V005),  // <-- new
    ])
}

Order matters. The vector is the canonical migration history and must not be reordered or have entries removed.

4. Update the version constant

Increment CURRENT_WORKSPACE_VERSION to match the new vector length:

pub const CURRENT_WORKSPACE_VERSION: usize = 5;  // was 4

This constant is used by check_workspace_compatibility() to detect when a database was created by a newer app version and needs to be rejected.

5. Audit all affected INSERT and UPDATE statements

After adding a column, search the entire infrastructure/sqlite crate for every INSERT INTO <table> and UPDATE <table> SET statement. SQLite silently defaults missing columns to NULL — there is no compile error or runtime warning if a new NOT NULL column is omitted from an existing insert.

# Find all inserts into the new table
grep -r "INSERT INTO bookmarks" crates/infrastructure/sqlite/src/

# Find all inserts into modified tables (e.g., if you added a column to pages)
grep -r "INSERT INTO pages" crates/infrastructure/sqlite/src/

Update every affected insert and update to include the new column.

6. Write migration tests

Add tests at the bottom of migrations/mod.rs inside #[cfg(test)] mod tests:

#[test]
fn test_v005_migration_creates_bookmarks_table() {
    let mut conn = Connection::open_in_memory().unwrap();
    run_workspace_migrations(&mut conn).expect("migrations should succeed");

    // Verify the table exists and has the expected columns
    let count: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='bookmarks'",
            [],
            |row| row.get(0),
        )
        .unwrap();
    assert_eq!(count, 1, "bookmarks table should exist");
}

#[test]
fn test_v005_migration_from_v004() {
    // Simulate a database at version 4, then migrate
    let mut conn = Connection::open_in_memory().unwrap();
    // Apply only up to V004
    let partial = Migrations::new(vec![
        M::up(WORKSPACE_V001),
        M::up(WORKSPACE_V002),
        M::up(WORKSPACE_V003),
        M::up(WORKSPACE_V004),
    ]);
    partial.to_latest(&mut conn).unwrap();

    // Now apply the full set (including V005)
    run_workspace_migrations(&mut conn).expect("incremental migration should succeed");

    let count: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='bookmarks'",
            [],
            |row| row.get(0),
        )
        .unwrap();
    assert_eq!(count, 1);
}

The second test is important: verify that the migration applies cleanly on top of an existing database, not just on a fresh in-memory database.

Verification

# Run migration-specific tests
cargo test -p infrastructure-sqlite migrations

# Run all infrastructure tests
cargo test -p infrastructure-sqlite

# Smoke-test against the dev database
./tools/dev/reset-app-data.sh --force   # clear stale dev data
pnpm desktop:dev                     # app runs migrations on startup; watch logs for migration_complete

After running pnpm desktop:dev, the log output should include:

migration_starting from_version=4 to_version=5
migration_complete final_version=5

Version compatibility notes

If a user opens a workspace created by a newer version of the app (i.e., db_version > app_version), run_workspace_migrations() returns MigrationError::IncompatibleVersion. The Tauri command layer surfaces this to the UI as an error asking the user to update the app. Never open a too-new database — the schema may contain columns or constraints the current code does not understand.