Database migrations

Migrations are plain SQL, forward-only, and applied before the new release starts. This page answers how a migration is written and numbered, who runs it and when, and how a breaking column change ships without downtime.

Write migrations as plain `.sql` files, one sequence per database

Impact: HIGH a reviewer reads exactly what runs; old migrations never change meaning

Files are packages/db/migrations/NNNN_<module>_<change>.sql. NNNN is a zero-padded sequential integer, never a timestamp. The module segment records which feature owns the table.
One sequence and one ledger per database, holding every module’s DDL. That total order keeps cross-module foreign keys satisfiable. Each database’s sequence is in its own dialect.
One statement per chunk, split on --> statement-breakpoint. Our loader, fromSqlDirectory(dir, throughId?), reads the files up front and runs each chunk with sql.unsafe. A .sql file cannot import domain code, so its meaning is frozen.
Review rejects DEFAULT gen_random_uuid(), SERIAL, identity columns and AUTOINCREMENT on entity tables, and any DEFAULT on an instant column (now(), CURRENT_TIMESTAMP, unixepoch()). Ids and times come from the app; timestamp without a time zone is never used.

❌ Incorrect — a TS migration importing domain code, with database-minted values:

import { LabelingRule } from "../labeling/domain"     // meaning drifts with the domain
export default sql`CREATE TABLE labeling_rules (
  id SERIAL PRIMARY KEY, created_at timestamp DEFAULT now())`

✅ Correct — numbered SQL, app-minted id, no defaults:

-- 0012_labeling_rules.sql
CREATE TABLE labeling_rules (
  id uuid PRIMARY KEY,
  org_id uuid NOT NULL REFERENCES orgs (id),
  repository_id uuid NOT NULL REFERENCES github_repositories (id),
  label text NOT NULL,
  created_at timestamptz NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX labeling_rules_org_repository_label_key
  ON labeling_rules (org_id, repository_id, label);

Source: notes/07-schema-and-data/db-migrations.md · Decisions 1, 4, amended

Guard the ledger by `(id, name)`; let CI own the numbering

Impact: HIGH a diverged database stops instead of skipping migrations silently

effect/sql’s Migrator.make({}) keeps its ledger table, lock, single transaction and duplicate check. In front of it, guard(manifest) compares names: a recorded id this build knows must carry the same name, or it fails with LedgerDiverged. Ids above ours (a newer release) are allowed.
CI checks: ids are unique and contiguous from 1 to N on the PR merged with main, and merged migrations are immutable (ci:migrations-immutable, only additions allowed).
A diverged database stops and a human decides. Two PRs that both add 0048 turn main red; the second author renumbers in a fix PR (see CI pipelines).
Accepted cost: the guard reads the @stability unstable Migrator’s ledger table directly. A test pins it against the real Migrator.

❌ Incorrect — editing a merged migration, trusting the plain ledger:

git diff --name-status --diff-filter=MD "$(git merge-base origin/main HEAD)" -- packages/db/migrations/
M       packages/db/migrations/0004_labeling_rules.sql   # a database that ran the old 0004 never sees this

✅ Correct — the guard runs first, then the Migrator:

const manifest = yield* Manifest.fromDirectory(MIGRATIONS_DIR)
yield* Ledger.guard(manifest)                      // LedgerDiverged if 48 is recorded under another name
const ran = yield* Migrator.make({})({ loader: fromSqlDirectory(MIGRATIONS_DIR) })

Source: notes/07-schema-and-data/db-migrations.md · Decision 2, amended

Run `migrate` before rollout; the app only checks

Impact: HIGH no DDL rights on the request role, no crash-looping replicas

migrate is its own entrypoint with its own SqlClient and owner credentials (a separate Redacted config key). It is the first step of each deploy job and finishes before any instance of the new release starts. A failed migrate stops the job; the previous release keeps serving.
The app’s role is DML-only (Postgres). At layer build it runs requireApplied: a diverged ledger or a missing migration fails the layer, so the process refuses to boot and names what is missing. A database that is ahead is fine.
Tests migrate on layer build (TestDatabase.layer). Local development runs pnpm run migrate before the server starts (see local dev setup).
On D1 (Worker profile) alchemy’s Cloudflare.D1.Database resource applies the same files at alchemy deploy, before the Worker that binds it. guard and requireApplied read its d1_migrations ledger. There is no migrate bin and no owner credential there.

❌ Incorrect — migrate on boot, as the request-serving role:

const MainLayer = HttpLayer.pipe(
  Layer.provide(MigratorLayer),       // PgMigrator.layer: every replica needs DDL rights,
                                      // and one bad migration crash-loops them all
  Layer.provide(DbLayer),
)

✅ Correct — the app checks; migrate ran before rollout:

export const requireApplied = (manifest: ReadonlyMap<number, string>) =>
  Effect.gen(function* () {
    yield* guard(manifest)
    const have = new Set((yield* recorded).map((r) => r.migration_id))
    const missing = [...manifest].filter(([id]) => !have.has(id)).map(([id, n]) => `${id}_${n}`)
    if (missing.length > 0) return yield* new MigrationsMissing({ missing })
  })

Source: notes/07-schema-and-data/db-migrations.md · Decision 3, amended

Never write a down migration

Impact: MEDIUM a reverse path that never runs is never tested

No down migrations. A mistake is fixed by the next migration, which still passes review and the N−1 rule below.
A rollback redeploys an earlier production SHA, skips migrate, and runs release N−1 against schema N. requireApplied allows a database that is ahead.
Losing data is a restore problem: provider-managed point-in-time restore into a new database, never over production in place (see deployment and environments).

❌ Incorrect — paired up/down files:

0048_labeling_drop_label.up.sql     DROP COLUMN label
0048_labeling_drop_label.down.sql   ADD COLUMN label text   -- the data does not come back

✅ Correct — forward-only, a repair is the next number:

0048_labeling_drop_label.sql
0049_labeling_repair_label_name.sql

Source: notes/07-schema-and-data/db-migrations.md · Decision 5, amended

Ship breaking changes as expand → backfill → contract

Impact: HIGH rollout and rollback both stay error-free

The rule: a migration shipped in release N must be safe for release N−1’s code. N−1 is still serving while it lands, and N−1 is what a rollback returns to. This holds on every dialect, D1 included.
Safe in one release: a new table, a nullable or defaulted column, an index on a small table, relaxing a constraint. Needs expand/contract: drop or rename a table or column, change a type, add NOT NULL / CHECK / UNIQUE on existing data.
The expand migration’s header names the contract migration it waits for. No statement that cannot run in a transaction (CREATE INDEX CONCURRENTLY, VACUUM).
Accepted cost: a rename takes three to four releases, with dual-write code in the mapper.

❌ Incorrect — a rename in one release:

-- 0048: N−1 still reads `label`; its Row decode fails until the new code is live
ALTER TABLE labeling_rules RENAME COLUMN label TO label_name;

✅ Correct — across releases:

R12  0048_labeling_add_label_name.sql      ADD COLUMN label_name text
     toRow writes label and label_name; fromRow reads label; backfill label_name
R13  0049_labeling_label_name_not_null.sql ALTER label_name SET NOT NULL
     0050_labeling_label_nullable.sql      ALTER label DROP NOT NULL
     fromRow reads label_name; toRow still writes both
R14  toRow stops writing label
R15  0052_labeling_drop_label.sql          DROP COLUMN label

Source: notes/07-schema-and-data/db-migrations.md · Decision 6

Keep small set-based backfills in the sequence

Impact: MEDIUM all pending migrations share one transaction and its locks

A backfill may be a migration (NNNN_<module>_backfill_<what>.sql) when it is set-based SQL with no app code, idempotent by its own WHERE, and touches few enough rows to finish in seconds.
Otherwise it is a backfill job in the owning module, run between expand and contract: dry run by default, keyset batches with one small transaction each, done decided by the data, every value decoded through the domain Schema. The contract migration waits until it has run in every environment.
A migration that transforms data gets its own test: migrate through fromSqlDirectory(dir, N - 1), insert old-shape rows, migrate through N, assert.

❌ Incorrect — a non-idempotent rewrite of a large table in the sequence:

-- holds its locks until every pending migration commits
UPDATE dashboards SET config = config || '{"v": 3}';

✅ Correct — small, set-based, a no-op on re-run:

-- 0049_labeling_backfill_label_name.sql
UPDATE labeling_rules SET label_name = label WHERE label_name IS NULL;

Source: notes/07-schema-and-data/db-migrations.md · Decisions 7, 8

Deferred

A batched backfill job — trigger: a backfill touches more than 10k rows, or takes more than 5 s on staging.