Diesel Migration & Model Workflow

The desktop app keeps its data in one SQLite file. Migrations are raw SQL, compiled into the binary and applied at startup. There is no migrator package, no diesel.toml, no DATABASE_URL, and no cargo make db:* task.

Where things live

init_pool runs every pending migration when the app opens the database, so a migration ships by being in the directory. Never edit a migration that has been released: users already ran it. Add a new one.

Workflow

1. Create the directory packages/desktop/migrations/YYYY-MM-DD-HHMMSS_<name>/ with up.sql and down.sql. Names sort by date; use 000000, 010000, ... for several on one day.
2. Write the SQL (patterns below). Start up.sql with a one-line comment saying what the column or table means when that is not obvious from its name.
3. Update schema.rs by hand to mirror the migration. A column added with ALTER TABLE ... ADD COLUMN goes at the end of its table! block, because that is where SQLite puts it. A new table also goes in allow_tables_to_appear_in_same_query!, and each foreign key gets a joinable! line.
4. Update the models in packages/orm/src/models/ and the exports in models/mod.rs.
5. Update the store function and the shared record if the UI needs the new data.
6. Verify: cargo test -p desktop store. The store tests build a database from the embedded migrations and insert through the models, so a schema/model mismatch fails there.

Checking schema.rs against the SQL

diesel print-schema can show what the migrations really produce:

diesel migration run --database-url /tmp/schema.db --migration-dir packages/desktop/migrations
diesel print-schema --database-url /tmp/schema.db

Use it as a reference, do not redirect it over schema.rs. Two differences are expected: it prints TEXT PRIMARY KEY columns as Nullable<Text> (SQLite allows NULL there; the checked-in file says Text), and it orders tables alphabetically.

SQL conventions

-- A Link publishes target_host:target_port at https://<subdomain>.<relay domain>.
CREATE TABLE links (
    id TEXT PRIMARY KEY,
    subdomain TEXT NOT NULL UNIQUE,
    target_host TEXT NOT NULL,
    target_port INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

• id TEXT PRIMARY KEY, filled by the store with Uuid::new_v4().to_string().
• created_at and updated_at on every table. There is no trigger: the store sets updated_at to Utc::now().naive_utc() on every write.
• Foreign keys say what happens on delete: REFERENCES ssh_hosts (id) ON DELETE CASCADE or ON DELETE SET NULL. They are enforced because store/mod.rs sets PRAGMA foreign_keys = ON on each connection.
• Index a foreign key column: CREATE INDEX <table>_<column>_idx ON <table> (<column>);
• Settings, tokens, and secrets are rows in the settings table (store/settings.rs), not new tables or side files.

Patterns

-- add a column                              -- down.sql
ALTER TABLE host_snippets ADD COLUMN pin_color TEXT;
                                             ALTER TABLE host_snippets DROP COLUMN pin_color;

-- add a foreign key column
ALTER TABLE ssh_hosts ADD COLUMN jump_host_id TEXT REFERENCES ssh_hosts (id) ON DELETE SET NULL;

SQLite cannot change a column's type, nullability, or constraints in place. Rebuild the table, as 2026-09-28-000000_local_snippets does:

CREATE TABLE host_snippets_new ( ... );
INSERT INTO host_snippets_new SELECT id, host_id, title, body, created_at, updated_at FROM host_snippets;
DROP TABLE host_snippets;
ALTER TABLE host_snippets_new RENAME TO host_snippets;
CREATE INDEX host_snippets_host_id_idx ON host_snippets (host_id);   -- indexes go with the old table

A NOT NULL column added to an existing table needs a DEFAULT.

Type mapping (SQLite)

Enums are stored as TEXT and converted in the store (ai_access on ssh_hosts). The orm crate enables only diesel's sqlite and chrono features: no JSON, UUID, or array column types.

Model structs

Three structs per table, in packages/orm/src/models/<table>.rs:

#[derive(Debug, Clone, Queryable, Selectable, Serialize, Deserialize)]
#[diesel(table_name = links)]
pub struct Link {            // read: every column, in schema.rs order
    pub id: String,
    pub subdomain: String,
    pub target_host: String,
    pub target_port: i32,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Clone, Insertable)]
#[diesel(table_name = links)]
pub struct NewLink { /* every column, id and both timestamps included */ }

#[derive(Debug, Clone, AsChangeset)]
#[diesel(table_name = links)]
pub struct UpdateLink { /* no id, no created_at; updated_at included */ }

• Queryable reads by position: field order must match the table! column order, so a column appended to the table is appended to the struct.
• AsChangeset skips a None field instead of writing NULL. A nullable column that must be clearable is Option<Option<T>> in the update struct.
• Export from models/mod.rs: pub use link::{Link, NewLink, UpdateLink};

Using them from the store

pub async fn link_create(link: LinkWriteRequest, pool: State<'_, DbPool>) -> Result<LinkRecord, String> {
    let pool = pool.inner().clone();
    run_db(pool, move |connection| {
        let now = Utc::now().naive_utc();
        diesel::insert_into(links::table)
            .values(&NewLink { id: Uuid::new_v4().to_string(), /* ... */ created_at: now, updated_at: now })
            .returning(Link::as_returning())
            .get_result(connection)
            .map(to_record)
            .map_err(internal_error)
    })
    .await
}

run_db moves the query onto a blocking thread; never touch a connection on the UI thread or inside an async task directly. Errors leave the store as String (internal_error, or a specific message for a constraint the user can fix, as write_error in store/links.rs does).