# 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:

```bash
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.
