Migrations

Migration

A forward-only schema migration applied by apply_migrations.

Fields

• version::Int: Monotonically increasing version number. Determines application order and serves as the primary key in the schema_migrations tracking table. Once committed, do not reuse a version number.
• name::String: Short human-readable identifier (snake_case). Logged on application and stored alongside the version for debugging.
• statements::Vector{String}: SQL statements applied in order. Each statement runs through DBInterface.execute, matching how the rest of the repository talks to the database.

apply_migrations(db::DuckDB.DB)::Nothing

Apply every Migration in MIGRATIONS whose version is not yet recorded in schema_migrations, in ascending order. Each migration's statements run sequentially via DBInterface.execute; the version is stamped into schema_migrations only after every statement succeeds, so a crash mid-migration leaves the registry untouched and the migration is retried on next startup.

applied_versions(db::DuckDB.DB)::Set{Int}

Return the set of migration versions already recorded in schema_migrations. Creates the tracking table on first call so a freshly-opened database needs no special bootstrap.

MIGRATIONS

The ordered registry of every Migration that ships with DearDiary. New schema changes must append a new entry here (and add a numbered file under migrations/). Never edit a previously-released migration in place: existing databases have already applied it and will not re-run it.

MIGRATION_001_BASELINE

The baseline schema. Creates every table with CREATE TABLE IF NOT EXISTS, so running it against a database that already has the tables is a no-op. DuckDB cannot open database files written by the SQLite-backed releases, so those deployments start from a fresh file.

MIGRATION_002_MODEL_NAME_RENAME

Recreates model without the UNIQUE(project_id, name) constraint so a model can be renamed after versions have been registered. DuckDB runs an update of a unique-indexed column as a delete followed by an insert, and the foreign key from model_version rejects the delete. create_model and update_model enforce per-project name uniqueness instead.

Both registry tables are copied aside, dropped, created again, and refilled inside one transaction. The tables are never renamed: DuckDB keeps its foreign-key bookkeeping under the original table name, so a renamed table later breaks deletes on project. The final CHECKPOINT folds the schema change into the database file instead of leaving it in the write-ahead log.