PostgreSQL schema & migrations

How CardNexus changes the Postgres schema: the Drizzle schema files, db:push for local dev, and pg:migrate (Drizzle SQL migrations) for dev/prod.

The Postgres schema lives in Drizzle TypeScript files under packages/core/db/src/pg/schema/. Those files are the source of truth — every table, column, index and FK is declared there. There are two ways to get a schema change into a database, and they serve different purposes.

Two paths

	`db:push`	`pg:migrate`
Command	`pnpm --filter @repo/db db:push`	`pnpm --filter @repo/db pg:migrate`
Mechanism	`drizzle-kit push` — diffs the schema files against the live DB and applies the difference directly	`drizzle-kit migrate` — applies committed SQL migration files in order, tracked in a ledger table
Migration files	none (nothing committed)	`src/pg/migrations/*.sql` (committed, reviewed)
Data steps (backfills)	not possible (DDL only)	yes — hand-written SQL in the migration
Use for	local dev — quick schema sync on your machine	dev / prod — deterministic, reviewable, ordered changes

Neither command runs automatically in CI/deploy. Both are manual operator steps, exactly like the Mongo migrate (umzug). Apply schema changes to dev/prod by running pg:migrate yourself against the target database.

Local dev: `db:push`

While iterating on a schema change locally, just edit the schema .ts files and run:


pnpm --filter @repo/db db:push

This diffs your schema against your local DB and applies the changes (--force). It’s fast and needs no migration file — but it can’t do data backfills and it isn’t reviewable, so it never touches dev/prod.

Dev / prod: Drizzle SQL migrations (`pg:migrate`)

Schema changes that ship to dev/prod go through committed SQL migrations, run by an operator. This mirrors the Mongo umzug system: versioned files, applied in order, recorded in a ledger so each runs once.

Migration files: packages/core/db/src/pg/migrations/NNNN_name.sql
Ledger table: drizzle.__drizzle_migrations (tracks which migrations have been applied)
Snapshots: meta/*_snapshot.json — Drizzle’s record of the schema after each migration, used to compute the next diff (see Snapshots below)

Authoring a migration

1. Change the schema files

Edit the Drizzle schema under packages/core/db/src/pg/schema/. These are the source of truth.

2. Generate the migration SQL


pnpm --filter @repo/db exec drizzle-kit generate --name=my_change

This diffs your schema against the latest snapshot and writes a new NNNN_my_change.sql (DDL only) plus an updated snapshot.

3. Add any data steps by hand

generate only emits DDL. If the change needs a backfill (e.g. populating a new NOT NULL column on existing rows), add the SQL to the generated file, separated by --> statement-breakpoint. For a migration that is only data/DDL Drizzle can’t express, create an empty file instead:


pnpm --filter @repo/db exec drizzle-kit generate --custom --name=my_change

then write the SQL yourself.

4. Review & commit

The .sql file is plain, reviewable SQL — read it like any other code change. Commit the .sql, the meta/ snapshot, and _journal.json together.

5. Apply

Locally or against a target DB:


POSTGRES_URI="<target>" pnpm --filter @repo/db pg:migrate

migrate reads the ledger, runs only the not-yet-applied migrations, and records them. Re-running is a safe no-op.

NOT NULL on an existing column

A column that is NOT NULL in the schema but added to a table with existing rows must be backfilled first. The correct order in one migration:


ALTER TABLE "inventory" ADD COLUMN "line_key" text;            --> statement-breakpoint
UPDATE "inventory" SET "line_key" = "id" WHERE "line_key" IS NULL; --> statement-breakpoint
ALTER TABLE "inventory" ALTER COLUMN "line_key" SET NOT NULL;

Adding the column nullable, backfilling, then setting NOT NULL avoids the constraint rejecting existing rows.

Snapshots — why they matter

Each migration has a meta/NNNN_snapshot.json: a full picture of the schema at that point. drizzle-kit generate does not read the live DB — it diffs your schema files against the latest snapshot to produce the next migration. So the snapshot must reflect reality, or the next generate will emit a bogus diff (e.g. re-creating the whole schema).

After a --custom (empty) migration, Drizzle writes an empty snapshot. If the schema isn’t otherwise tracked, fix the snapshot to represent the true schema so the next generate produces a clean diff. You can verify by running generate again — it should report “No schema changes”.

Relationship to the Mongo migrations

Postgres pg:migrate is the Postgres-side counterpart of the Mongo migrate (umzug) command. Both are manual, ordered, idempotent, and tracked. Mongo data migrations live in packages/core/db/src/migrations/*.ts (run via pnpm --filter @repo/db migrate); Postgres schema migrations live in packages/core/db/src/pg/migrations/*.sql (run via pg:migrate).

Code Locations

Component	Location
Schema files (source of truth)	`packages/core/db/src/pg/schema/`
Drizzle config (out dir, ledger table)	`packages/core/db/drizzle.config.ts`
Postgres migrations	`packages/core/db/src/pg/migrations/`
Scripts (`db:push`, `pg:migrate`)	`packages/core/db/package.json`
Mongo migrations (umzug)	`packages/core/db/src/migrations/`