Writing and running migrations

This is the how-to for schema changes: where a new migration file goes, how it gets registered, how to run it locally, and how to verify it on both test lanes. The process is stricter than a typical app’s because prod and staging databases carry real fund-accounting data and migrations there are strictly forward-only — the migrator checksums every applied file, so an edited historical migration does not merely misbehave, it hard-fails every subsequent deploy. The two rules that will hurt you if you skip them are that forward-only discipline and the pglite-vs-real-Postgres gap.

OpsDNA does not use Kysely’s built-in migrator or a framework tool. Migrations are plain numbered SQL files driven by a custom migrator (packages/persistence/src/migrator.ts, CLI at packages/persistence/src/cli/migrate.ts). The migrator keeps a ledger table, public.opsdna_migrations, recording each applied migration’s id, filename, and the sha256 checksum of the raw SQL file. On every run it re-hashes local files against the ledger — a mismatch on an applied migration throws Applied migration <id> checksum does not match local file and stops the deploy. That checksum is the forward-only enforcement mechanism.

There are three migration directories, and only one of them is for you:

Per ADR-0025, the pre-cutover migration chain was squashed into a single generated baseline. A fresh database gets the baseline in one shot, then replays post-baseline/; an existing database only ever appends from post-baseline/. The migrator hard-errors if it finds pre-cutover ledger rows against the active catalog — you cannot replay the old chain onto the new world.

Adding a migration

Read DATA-MODEL.md and the schema atlas

Any new table, FK, or identifier decision must match DATA-MODEL.md (layer cake, id policy, one-owner invariant). docs/schema-atlas.html shows every existing table by module.

Create the SQL file in `post-baseline/`

Next number in sequence, filename starting with the id:


packages/persistence/post-baseline/0165_my_feature.sql

Never put product migrations in packages/persistence/migrations/ — that is the frozen legacy chain (post-baseline/README.md says so explicitly).

Register it

Add an entry to the post-baseline catalog in packages/persistence/src/migrations.ts with id, filename, and a real description (descriptions in this repo are small design notes — look at the existing entries). The registry is validated at load: ids must be unique and strictly ascending, and the filename must start with <id>_. Downstream packages (@opsdna/ledger, @opsdna/identity, the portal-* packages) slot their own SQL into the same global sequence via the sqlDir field.

Run it locally


# local Postgres 18 on :55432
docker compose -f docker-compose.postgres.yml up -d
 
DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun run --cwd packages/persistence migrate:status   # see what's pending
DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun run --cwd packages/persistence migrate:latest   # apply

migrate:up applies exactly one pending migration. Each migration runs inside a single transaction, with search_path set to current_schema(), extensions, public (extension types live in the extensions schema after ADR-0025).

Verify — on both lanes


bun run verify   # pglite-backed gate suite
 
DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun run --cwd packages/persistence test:integration   # real Postgres

Green verify + red postgres integration CI job is the classic tell that a schema change slipped the pglite lane. bun run verify runs tests on pglite, which silently passes things real Postgres rejects: function EXECUTE grants, FORCE ROW LEVEL SECURITY behavior, and catalog invariants like the identity-spine rule (every *_identity_id column must be uuid after migration 0311). Any migration change must also pass the real-Postgres lane locally — see Testing.

Never edit an applied migration. Prod and staging have already recorded its checksum; editing the file breaks every subsequent migrate:latest with a checksum error. Fix mistakes with a new forward migration. (This has caused a real prod incident that required manual checksum reconciliation — the rule is not theoretical.)

Migrator behaviors worth knowing

checkFunctionBodies: false in the CLI — a plpgsql body may forward-reference an object a later migration creates. Fine on full replay, fatal on an incremental prod migrate, so the CLI disables body validation (src/cli/migrate.ts).
Schema-prefix rewriting — on the parallel Postgres test lane, domain-schema references (crm, kpi, sharing, …) are rewritten to <suite-prefix>_<schema> so parallel suites sharing one cluster never collide. The ledger checksum is always of the raw file, so prod and tests agree.
pglite baseline dialect — pglite’s PG18 build lacks the pgcrypto contrib module but exposes gen_random_uuid() natively, so the fresh-baseline path strips the CREATE EXTENSION pgcrypto statement when freshBaselineDialect: "pglite" — and refuses if the baseline actually calls pgcrypto-only functions.
Timing — OPSDNA_MIGRATION_TIMING=1 prints per-migration replay timing (used to decide squash/snapshot strategy for CI speed).

In deployed environments, migrations run as the dedicated opsdna_migrator role before code deploys (migrate → CDK → Cloudflare, in that order) — see Roles & RLS.