# Schema migrations
```bash
uv run python scripts/new_migration.py add_wave_colour # author a draft (no ordinal)
uv run python scripts/check_migrations.py # what CI and the release run
```
Write the SQL below the header, and you are done — there is nothing to paste into
`migrations.rs`. A **draft** carries a stable snake_case name, an immutable
authoring id (a 128-bit token, 32 hex chars), and **no ordinal**; it lives at
`migrations/drafts/<name>__<id>.sql`.
The draft's file *is* its registration: canonicalization discovers it by scanning
the directory, and Rust never sees it until the release cut appends the canonical
`Migration` entry it generates. Because a draft has no ordinal and two branches
authoring the same name mint different 128-bit ids (materially collision-resistant),
concurrent branches never contend,
renumber, or share a registry edit, and the allocator performs no `git fetch` or
rebase. Ordering that matters — a data migration that must run after another — is
declared with `--depends-on` (naming another draft or an already-released
migration), not by a serial number.
## The release cut assigns canonical ids
The release PR is the single publication boundary that turns drafts into canonical
migrations. `lf release run` invokes the canonicalizer with `--release-cut` inside the
release worktree, **after the version bump and before the commit**, so the generated
files are part of the release PR and run under real Rust CI before the queue merges
and tags. It freezes the draft set, rejects missing or cyclic dependencies (and two
drafts sharing a readable name in one cut), and topologically orders it (edges
first, ties broken by name — never merge time, PR number, or wall clock). It then
concatenates the ordered bodies into the release's one
`<major>.<minor>.<patch>.001_release.sql` batch. `-- draft: <name>` markers retain
the authoring identities for dependencies and incident review. The cut appends one
`Migration` entry and deletes all consumed drafts atomically; on any failure it
restores the tree byte-for-byte. The same drafts and version always produce the
same id and diff, so an aborted release regenerates identically. A second batch for
the same package version is rejected. The manual script is a `--check` preview
only; creating canonical files requires `--release-cut`. Rust CI uses the separate
`--materialize-for-tests` authority in its disposable checkout. The release run is
the authority that publishes.
Only the merged release commit is canonical migration authority. Between releases,
ordinary merges add drafts, so main's canonical set does not move and a branch
that is merely behind main — adding only drafts — stays green.
Rust CI materializes the draft set in its disposable checkout before running the
test suite. This exercises the same deterministic schema and generated registry
the release cut would produce without publishing either one. The checkout is
discarded after the job; if the active version already has a canonical batch,
the materializer advances the disposable package to the next patch namespace.
Source builds and the shared store still see only canonical migrations from a
merged release.
The runner temporarily disables foreign-key actions around the transaction so a
SQLite table rebuild cannot cascade-delete child history. It runs
`PRAGMA foreign_key_check` before commit and restores enforcement afterward; a
migration that leaves a dangling reference rolls back as one unit.
Persisted JSON is schema too. Changing a required field, enum variant, or wire
shape in a DTO stored by the database requires an ordinal-free repair draft and
a typed upgrade test seeded with the previous shape. Before commit, the runner
deserializes every registered persisted JSON column into its current Rust type
and reports all incompatible rows together; any failure rolls back the complete
migration transaction.
Before advancing an existing on-disk database, the runner takes a SQLite backup
inside the same exclusive transaction and publishes it atomically beside the
database. The filename carries the previously applied migration and a fingerprint
of the complete ledger plus product schema, so a backup from another branch-local
history cannot be mistaken for the state being repaired.
## The one rule
**A published migration is never edited, renamed, or deleted.** `origin/main`
is the publication boundary because creator dogfooding runs ahead of patch tags.
Databases may already have run it; changing the file changes their history, not
their schema. Repair a published schema with a new forward migration.
`check_migrations.py` compares every migration against both `origin/main` and
the last release tag and fails the build if one moved.
## What the check enforces
- The directory and the `MIGRATIONS` registry name the same migrations, with the
same ids and names. A file nobody registered never runs; a registry entry whose
id, name, and file disagree is a lie about what a database applied.
- The registry is in id order. New canonical ids match the full package version,
use ordinal `001`, and name the single batch `release`. Known historical
three-part ids remain valid; no new one can be introduced.
- Every canonical migration already on `origin/main` has the same ordinal, name, and
bytes.
- Nothing that shipped in the last release tag has changed.
- Every draft under `drafts/` is well-formed: a snake_case name matching its file, a
`-- name:` header, no collision with a released migration name, and a `depends_on`
graph that resolves to other drafts with no cycle. Drafts have no ordinal, so they
are never compared against `origin/main`.
It runs in CI, and — because `lf release` cuts a tag from local state and never
reads a CI result — `lf release check` and `lf release run` run it themselves
before anything is cut. Same script, both paths.
## Identity
```text
0.12.2.001_release.sql
│ │ │ │ └── canonical batch name
│ │ │ └────── ordinal; one batch means every release uses 001
└──┴─┴───────── package major.minor.patch that published the batch
```
- Historical `0.10.001_initial.sql` / `0.11.037_*.sql` ids remain immutable
three-part ids. New canonical migrations always use the four-part format.
- Order is numeric: `(major, minor, patch, ordinal)`, with legacy ids before
release-scoped ids in the same major/minor line. A binary skipped from `1.1.0`
to `1.2.0` applies every missing patch and minor batch in that order.
- The file stem *is* the `schema_migrations.version` string. `MigrationId` in
`migrations.rs` is the only thing that formats or parses it.
- The ledger records the SQL checksum, parent-history fingerprint, build
provenance, source checkout and revision, and package version. Old canonical
rows receive checksums when the provenance migration first runs; their writer
is intentionally left unknown rather than attributed to the upgrading build.
- The active namespace is the full workspace `Cargo.toml` version, so a batch
authored for an earlier or later package release is an error, not a choice.
## What a database can be told
| Behind the chain | applies the missing tail and continues |
| Unpublished build against `~/.lf/loopflow.db` | validates the applied prefix without running its pending migrations |
| Pre-namespace `001_initial` stamp | adopted as `0.10.001_initial` — same bytes, no data moved |
| Known leading migration names under branch-local ordinals | verifies the complete product schema, rewrites the ledger to canonical order, then continues |
| Carries an unknown id | reports the unknown and latest-known ids — a newer release or a divergent local build wrote it |
| Skipped a migration, or drifted from the chain's schema | *delete loopflow.db and rerun* |
## Why there is no separate "schema change without a migration" check
Schema exists only inside these files. The only way to change it without adding a
migration is to edit a shipped one — which the immutability check already rejects.
A second check would restate the first.