Engineering
Database and migrations
One Neon Postgres instance, shared by every app.
Project
wandering-field-60318866Production branch
br-quiet-unit-a50rute1Development branch
br-summer-dust-a5or2tcd
The discipline
Migration files are the source of truth, and are committed before they are applied. Production is never hand-edited outside a committed migration.
Apply to the development branch first, then production. Skipping development is
how a migration first runs against real data — and it has already happened once
here: 0035_api_key_scopes.sql reached production and not development, and the
two branches disagreed for weeks.
apps/api—pnpm --filter acrune-api db:generatewrites SQL intoapps/api/migrations. Review, commit, apply.apps/track— raw SQL files inapps/track/migrations, numbered. These are authoritative for every table they cover.
Do not run drizzle-kit push
This is the sharpest edge in the repository, so it is worth being blunt.
apps/track is split-brain: a few tables are described in src/db/schema.ts,
most arrived through raw migrations, and the Drizzle schema is only a partial
mirror of the live database.
tablesFilter in drizzle.config.ts scopes which tables push considers. It
does not scope which columns. So for every table in that filter, push reads
the columns the raw migrations added as drift, and drops them.
In the historical migration-0038 audit against the development branch, push --explain
proposed:
25
DROP COLUMNacrossaccounts,campaigns,links,creatorsandpost_metrics— includingcampaigns.brief,campaigns.status, and everyfee_bps/creator_share_bpscolumn5
DROP INDEXand 11DROP CONSTRAINT
The safe command is:
pnpm --filter acrune-track db:diff # push --explain: prints, changes nothingThe destructive one is named db:push:unsafe so it cannot be reached by muscle
memory. Converging the tracker on a single migration flow — every table
represented, one apply path — is the known gap this guards until it is closed.
apps/api is not exposed the same way: it generates files and applies them.
Adding a table
Write the migration file. Commit it.
Apply to the development branch, then production.
If the table is fully described in
src/db/schema.ts, add it totablesFilterso the diff stays honest. If it is only partially described, leave it out.Add the file and SHA-256 to
database/baseline-manifest.jsonand run the full-schema integration tests. Small unit fixtures alone do not establish compatibility.
Current baseline
database/baseline.sql is a schema-only September 17 snapshot for empty isolated databases. The manifest applies additive migrations through 0059, checking their hashes. It is not a production upgrade script or recovered migration history. The inspected database has no execution journal; verify actual columns, indexes and triggers before applying anything.
Run pnpm verify:baseline --browser for the full-schema HTTP, money and browser checks. node scripts/inspect-schema.mjs --offline describes the expected baseline; with an explicitly supplied database URL it performs a read-only column/type comparison. The detailed evidence and remaining release differences are in repository docs/qa/release-baseline.md.
Tenant columns are client_id; accounts now represent social handles and creators represent people/payees. Campaign and person external IDs are scoped by client. Do not copy historical examples treating account_id as the tenant without checking the current schema.