Migrations and schema changes

src/lib/db/schema.ts is the single source of truth for the relational layer — 25 pgTable definitions and every Postgres enum, in one file. Every structural change starts by editing that file and ends with a generated migration directory checked into drizzle/.

The tooling is drizzle-kit and drizzle-orm, both tracking the floating rc dist-tag (package.json:73,103); the currently resolved version is 1.0.0-rc.4, and pin changes come from the lockfile, not from a version range in package.json. The v1 release-candidate layout is different from the v0 layout most drizzle documentation still describes, so read the next section before you go looking for files that are deliberately absent.

The layout

One directory per migration, named <timestamp>_<name>, containing exactly two files:

There are 19 such directories at present, from 20260507070419_fearless_praxagora (the initial schema) to 20260722020726_statement_timeout_role_default.

• migration.sql — the DDL. Statements are separated by the --> statement-breakpoint marker, which drizzle-kit uses to split the file when applying it.
• snapshot.json — the post-migration schema state ({ id, prevIds, version, dialect, ddl, renames }, version: 8, dialect: "postgres"). This is what the next db:generate diffs against to compute the new DDL. It is generated output; never hand-edit it.

Applied state lives in the database, not in the repo: drizzle-kit records applied migrations in drizzle.__drizzle_migrations (the default table and schema; drizzle.config.ts does not override them).

Which connection string

Two consumers read different variables in different orders. This trips people up more than anything else on this page.

The dedicated MIGRATIONS_DATABASE_URL exists because DDL through a transaction-mode pooler is unreliable (drizzle.config.ts:2-5). The runtime must use the pooled endpoint because each serverless instance claims up to POSTGRES_POOL_MAX (default 3) sockets and direct connections exhaust the server.

The runtime enforces its half fail-closed. In NODE_ENV=production, isDirectConnectionUrl() throws at client construction rather than falling through to the next candidate (src/lib/db/client.ts:32-48, 97-104):

<NAME> points at a direct Postgres host. Use the pooled/transaction connection string
for the app runtime in production.

"Direct" means a Supabase db.<ref>.supabase.co host on any port other than 6543, or a CapyDB *.db.capydb.dev host on any port other than 6432.

Generating a migration

pnpm db:generate --name add_story_snapshot_locale

pnpm db:migrate

pnpm db:check (drizzle-kit check --dialect postgresql) validates the checked-in migration folder for conflicts. Because the dialect is passed on the command line it reads only drizzle/ — verified to run to completion with no database URL set at all, which is how it passes in the CI quality job (.github/workflows/ci.yml).

Four rules that break migrations here

1. Every migration must be expand/contract

Production migrates before next build. If the migration succeeds and the build then fails, nothing new deploys and the old code keeps serving traffic against the new schema.

So every migration has to be backward-compatible with the code currently deployed: add columns nullable or defaulted first, and drop a column only in a later deploy, after no running code reads it (docs/ops/launch-checklist.md:29-35).

2. New enum values go on the end, and cannot be used in the transaction that adds them

The repo convention is to append new values to the end of the as const array in schema.ts, so drizzle emits a plain ALTER TYPE … ADD VALUE. dataErasureStatusValues carries the comment explaining why 'purging' sits last (src/lib/db/schema.ts:616-618). Insert a value mid-list and drizzle-kit will emit an ordered form instead — ALTER TYPE "data_erasure_status" ADD VALUE 'failed' BEFORE 'purged'; in 20260710001550_skinny_lorna_dane.

The sharper trap is the second half. Postgres will not let you use a new enum value in the same transaction that adds it, and a ::text cast is not IMMUTABLE so it cannot appear in an index predicate either. That same migration adds 'failed' on line 1 and rebuilds two partial unique indexes on lines 40 and 42 — and their predicates are written entirely in pre-existing values:

CREATE UNIQUE INDEX "data_erasure_requests_pending_tenant_uq"
  ON "data_erasure_requests" ("tenant_scope_id")
  WHERE "status" NOT IN ('purged', 'cancelled') AND "subject_type" = 'tenant';

NOT IN ('purged','cancelled') is the workaround for "everything unresolved" written without naming the new value. The reasoning is recorded at src/lib/db/schema.ts:960-962.

Removing an enum value is not a one-liner at all. 20260706223957_remove_trialing shows the full dance: swap the column to text, rewrite the affected rows, drop the type, recreate it without the value, cast the column back.

ALTER TABLE "tenant_subscriptions" ALTER COLUMN "status" SET DATA TYPE text;--> statement-breakpoint
UPDATE "tenant_subscriptions" SET "status" = 'active' WHERE "status" = 'trialing';--> statement-breakpoint
DROP TYPE "billing_subscription_status";--> statement-breakpoint
CREATE TYPE "billing_subscription_status" AS ENUM('free', 'active', 'past_due', 'canceled');--> statement-breakpoint
ALTER TABLE "tenant_subscriptions" ALTER COLUMN "status" SET DATA TYPE "billing_subscription_status" USING "status"::"billing_subscription_status";

3. Long DDL must lift the statement timeout itself

Migration 20260722020726_statement_timeout_role_default runs:

ALTER ROLE <current_user> IN DATABASE <current_database> SET statement_timeout = '10s'

It exists because CapyDB's PgBouncer pooler does not forward the statement_timeout startup parameter — it originally rejected it outright with 08P01 unsupported startup parameter, which refused every pooled connection and took down all Postgres-backed surfaces, and now silently drops it instead. The role default is the pooler-safe replacement, applied Postgres-side at session start.

The consequence for migration authors: migrations run as the same role and therefore inherit the same 10-second cap. Any migration doing an index build or a table rewrite must start with

SET statement_timeout = 0;

No checked-in migration currently uses CREATE INDEX CONCURRENTLY; index work so far has been small enough to fit inside the cap.

4. Backfill before you constrain

A CHECK constraint added to a table with legacy rows fails on the rows that predate the invariant. 20260717002329_editorial-state-timestamp-checks backfills first, then constrains:

UPDATE "editorial_story_drafts" SET "submitted_for_review_at" = "updated_at" WHERE "editorial_state" = 'in_review' AND "submitted_for_review_at" IS NULL;--> statement-breakpoint
-- two more backfills --> statement-breakpoint
ALTER TABLE "editorial_story_drafts" ADD CONSTRAINT "editorial_story_drafts_in_review_submitted_check" CHECK ("editorial_state" != 'in_review' OR "submitted_for_review_at" IS NOT NULL);

The comment at the top of that file records why updated_at was chosen as the fill value. Write that comment. The SQL is checked in forever and the reasoning is not recoverable from the diff.

The production path

Vercel's buildCommand is pnpm run vercel:build, which is:

tsx scripts/deploy/prod-migrate.ts && NODE_ENV=production next build

prod-migrate.ts decides what to do from VERCEL_ENV alone:

A non-zero exit from drizzle-kit migrate exits the script with that status, which aborts the build. Nothing deploys. That is deliberate: the production state store has no in-memory fallback, so a deployment must never serve traffic against an unmigrated schema.

Verifying a migration landed

GET /api/health is public, no-store, and probes the platform_state table specifically after a SELECT 1 (src/app/api/health/route.ts:53-77):

curl -s https://ermisai.com/api/health | jq '.checks.database'

checks.database.migrated must be true. When the table is missing or unreachable you get HTTP 503 and:

platform_state unreachable (run db:migrate before serving traffic): <error>

Redis being down does not affect this — only the database check drives the 200/503.

Applying migrations by hand

Only needed for restores and non-Vercel environments.

pnpm db:migrate   # drizzle-kit migrate, via the local .bin shim

drizzle-kit migrate accepts only --config and --ignore-conflicts; there is no --dialect escape hatch, so it always loads drizzle.config.ts and therefore always needs one of the three connection variables to be a valid postgres:/postgresql: URL. Otherwise it throws before doing anything.

The restore procedure in docs/ops/disaster-recovery.md re-runs pnpm db:migrate after the restore, confirms database.migrated: true, and then redrives the two flushes (POST /api/admin/ai/ledger/retry/flush, POST /api/admin/ai/metering/flush).

Seeding reference data

Migrations create structure; they do not populate the plan catalog or the source registry. That is pnpm db:seed-bootstrap (scripts/db/seed-bootstrap.ts), which upserts the billing plans, the whole canonical source catalog and a dataset-version row in one transaction, then prints:

Bootstrap seed completed.
billing_plans=<n>
source_catalog_entries=<n>
source_catalog_dataset_versions=<n>
catalog_entries=<n>
catalog_version=<v>

pnpm db:seed-bootstrap writes the plan catalog, the source catalog and a dataset-version row into Postgres in one transaction. Without it the app still runs: billing_plans is upserted lazily by seedBillingPlans() on the first plan read (src/lib/db/billing-repository.ts:465-467), and an empty source_catalog_entries silently falls back to the bundled news-sources/catalog.v1.json (src/lib/db/source-catalog-repository.ts:613-614). The real hazard is the opposite of "nothing to show": a failed or skipped sync is indistinguishable from a healthy DB-backed catalog, and per-source telemetry / ingest_enabled edits made in Postgres do not exist until a sync has run.

pnpm db:studio opens Drizzle Studio against the same resolved connection if you need to inspect rows directly.