Engineering

Database and migrations

One Neon Postgres instance, shared by every app.

  • Project wandering-field-60318866

  • Production branch br-quiet-unit-a50rute1

  • Development 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:generate writes SQL into apps/api/migrations. Review, commit, apply.

  • apps/track — raw SQL files in apps/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 COLUMN across accounts, campaigns, links, creators and post_metrics — including campaigns.brief, campaigns.status, and every fee_bps / creator_share_bps column

  • 5 DROP INDEX and 11 DROP CONSTRAINT

The safe command is:

Shell
pnpm --filter acrune-track db:diff    # push --explain: prints, changes nothing

The 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

  1. Write the migration file. Commit it.

  2. Apply to the development branch, then production.

  3. If the table is fully described in src/db/schema.ts, add it to tablesFilter so the diff stays honest. If it is only partially described, leave it out.

  4. Add the file and SHA-256 to database/baseline-manifest.json and 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.