Database schema planning and migration

A Wildo application declares its data as decorated schemas, and on a relational database those declarations have to become physical tables and columns. The wildo db commands make that a reviewable sequence: compare the current model against a committed baseline, generate a migration draft from the difference, apply pending migrations in order, and correct the ledger when a human has intervened.

The problem it solves

When the shape of a database is derived from application code, the temptation is to let the application reconcile the database at start-up. That works until the first change that cannot be inferred safely. Dropping a column and renaming one look identical from the outside; a column that gains a constraint needs the existing rows to satisfy it first; and a fleet of instances starting together will each try to reconcile the same database at the same time.

The other half of the problem is review. A schema change is one of the few changes whose worst case is unrecoverable, and it is exactly the kind of change that is hard to see in a code diff, because what changed was a decorator on a field. Turning it into a generated file that is committed, read and approved makes the destructive part visible before it runs rather than after.

What it does

For a relational application, the physical schema is planned from the model, the difference is reported, and a migration is written from that difference — with the plan and the migration advancing as one change.

The baseline is a committed file describing the plan the application’s model implies. Asking for the difference recomputes that plan from the live model and reports what separates the two, each change classified as safe, destructive, or requiring a data migration. A check mode exits non-zero on any drift — the ratchet: it does not decide what is correct, it refuses to let the answer stay implicit, because an unenforced snapshot rots.

The model is computed in a process spawned inside the application’s own workspace rather than imported into the command line. That is deliberate: the model is the application’s own modules merged with the framework core, resolved through the application’s own dependencies — and importing it here would bind the application to the command line’s framework version and to its schema library’s decorator state. The comparison then happens in the command line, because comparing two plans is a pure step.

The migration runner carries the guarantees. Its state lives in a table inside the database being migrated, deliberately not modelled as a resource, because it has to be readable before the resource system boots and must be unreachable from the API. Exclusion is a session-scoped lock in that same database, so the lock lives in the same failure domain as the writes it guards and releases itself when its holder dies, rather than relying on an external expiry that can lapse mid-migration. Instances that do not win the lock wait and then proceed, and every instance runs the schema verification afterwards regardless — so a runner defect cannot silently become a served schema.

Each migration runs in its own transaction. Because schema statements are transactional here, a failed migration rolls back whole: the database lands exactly at the previous migration, the ledger records the failure with its error, and recovery is to fix the draft and run again rather than to reconstruct a half-applied state.

What you decide

wildo db diff
wildo db generate --name <label>
wildo db migrate

Migrations are committed folders beside the plan they advance.

Limits

This is the relational path. An application on the document store does not use it.

Several things are refused rather than automated, each naming its remedy. A migration whose file has changed after being applied is refused, because the repository and the database then disagree about history; re-accepting the current bytes is a deliberate command. A statement that drops a table or a column is refused unless the file carries an acknowledgement — a reviewed artifact in the migration rather than a flag on the command. Index creation outside a transaction is refused outright, because supporting it would cost the whole-rollback property everything else depends on.

The reverse case is tolerated on purpose: a deployment shipping fewer migrations than the ledger records is a rollback, and refusing it would break the recovery a fleet depends on.

There is no automatic reversal. Undoing a migration means writing the migration that undoes it.