Database migrations

Ductor manages its PostgreSQL schema with goose. Migrations are goose-style numbered SQL files under infrastructure/storage/postgres/cqrs/migrations/ — hundreds of them, with the highest version in the 450s as of writing — embedded in the binary, so the same ductor you deploy carries the exact schema it expects.

Run migrations

The migrate subcommand takes one positional argument:

ductor migrate up       # apply all pending migrations (default)
ductor migrate status   # list applied and pending migrations
ductor migrate down     # roll back the most recent migration

It uses database.url / DUCTOR_DATABASE_URL. up is the default, so bare ductor migrate applies pending migrations. down exists for local iteration only — production schema changes are forward-only (see Forward-only rollouts in production below).

Auto-migrate vs explicit

Ductor can migrate at startup, but the default is off:

Regardless of that flag, serve always checks migration status at startup and logs a warning if anything is pending:

pending migrations detected — run `ductor migrate up` or set database.auto_migrate=true

Recommended by environment:

• Local / ductor dev — auto-migrate is convenient; the dev command turns it on for you, and the dev compose sets DUCTOR_DATABASE_AUTO_MIGRATE: "true".
• Production — keep it off and run ductor migrate up as an explicit, observable deploy step. This makes each schema change a deliberate action you can gate and log independently of the app rollout. Schema changes are forward-only here: recover from a bad one by rolling the application back and shipping a forward fix-up migration, never ductor migrate down (see Forward-only rollouts in production).

Migrations under Helm

The Helm chart runs migrations as a retained-on-failure pre-install / pre-upgrade Job (<release>-migrate) whose container executes ductor migrate up. So on Kubernetes the schema is migrated before new pods roll — and the app keeps database.auto_migrate off. A failed migration blocks the release and the Job is kept so you can inspect it:

kubectl logs job/ductor-migrate
kubectl describe job/ductor-migrate

Toggle with migrations.enabled (default true). GitOps operators that run migrations separately may set it false, but must run ductor migrate up before application pods start. Migration rollback is operator-controlled database recovery, not an automatic Helm rollback.

Gate rollouts on schema readiness

ductor schema status verifies the schema is fully applied and exits non-zero until it is — a clean CI/CD gate:

ductor schema status --database-url "$DUCTOR_DATABASE_URL" --timeout 15s
# prints: schema=ready

Forward-only rollouts in production

In production the schema only ever moves forward. The Helm migrate Job runs ductor migrate up and never migrate down; treat every applied migration as permanent history. The down command exists for local iteration, but there is no tested down path in production and running it breaks the forward-only invariant.

This works because of a one-release backward-compatibility contract: every schema change must be compatible with the code one release back. During a rolling upgrade the old and new pod images both run against the same database, and the migrate hook applies the new schema before the new pods are healthy — so a migration the previous image can't tolerate takes down the still-running old pods. Keeping each change compatible one release back is exactly what makes an image rollback safe.

Recovering from a bad migration

Because the previous image is compatible with the newer schema, the safe recovery is to roll the application back, not the schema:

Writing migrations that roll out safely

Backward compatibility forces expand/contract for any destructive change:

• Release N (expand) — add the new column/table/constraint and stop reading or writing the old column in code. Ship it and let it roll out everywhere.
• Release N+1 (contract) — drop the now-unused column. Because release N already stopped using it and is fully deployed, no running pod references it.

Dropping a column in the same release that stopped using it is the classic break: the previous image is still SELECTing that column when the migrate hook removes it, and every old pod errors until the rollout completes.

scripts/check-migration-safety.sh (run in make custom-lint / make ci-fast) enforces this on newly added migrations. Two rules catch the expand/contract mistakes above:

-- +goose Up
-- migration-safety: drop-column-verified-unused <prior-release-tag>
ALTER TABLE pools DROP COLUMN legacy_weight;

A migration that both ADD CONSTRAINT ... NOT VALID and VALIDATE CONSTRAINT the same constraint in one file is also rejected. goose wraps a migration in a single transaction, so the ACCESS EXCLUSIVE lock from ADD CONSTRAINT is held through the full-table VALIDATE scan — negating the split that NOT VALID exists to buy. Add the constraint NOT VALID in one migration and VALIDATE CONSTRAINT it in a follow-up. The same header also rejects other blocking DDL on hot tables (multi-column ALTER, non-literal ADD COLUMN defaults, non-CONCURRENTLY index builds, unbounded backfills).

Version numbers are claimed at merge, not at authoring

Goose records only the version number of each applied migration. Two branches that both mint 532_*.sql with different content are indistinguishable to it: whichever file reaches a database first is applied, and the other is treated as already done and silently skipped on every database that ran the first. The symptom is a binary whose code expects objects the schema does not have, which surfaces as masked internal errors, not as a migration failure.

Take the next free number when you merge, not when you start the branch, and never run a feature branch's binary with auto_migrate against a database that other branches share.

Ductor keeps a second ledger, ductor_migration_manifest, with the file name and SHA-256 of every applied version. Every migrate path writes it, and on boot the server compares each applied version against the file the running binary carries. A mismatch refuses to start with both file names in the error, and with auto_migrate off the status check logs the same at error level:

migration version applied as a different file: version 532 was applied as
532_refund_disabled_recipient_pricing.sql (sha256 9c1e…) but this binary
carries 532_decision_market_ref.sql (sha256 2b7a…); the database schema does
not match this build, renumber the migration or repair the database before
starting

Databases migrated before the ledger existed are adopted on the first run from the binary's own files; detection starts from then on. A rollback with ductor migrate down removes the version's row so a renumbered or rewritten file is not mistaken for a collision.

Typical deploy sequence

ductor migrate up

ductor schema status

ductor serve