# 1. Data backfills run outside drizzle migrations

Date: 2026-06-07

## Status

Accepted

## Context

We needed to denormalise two join tables onto `recommendation` — `plan_id`
(from `plan_recommendations`) and `material_id` + measure columns (from
`recommendation_materials`) — as the first step toward making those columns the
source of truth and dropping the join tables.

The original migrations (0222–0225) did everything inside drizzle:

1. `ADD COLUMN` + validating `ADD CONSTRAINT FK` + `CREATE INDEX`
2. a single full-table `UPDATE ... FROM <join table>` to backfill

Run against the production `recommendation` table (millions of rows) it ran for
**3.5+ hours with no end in sight** and had to be killed. Diagnosis:

- **drizzle wraps every pending migration in ONE transaction**
  (`session.transaction(...)` in `pg-core/dialect`). So:
  - the `ADD COLUMN` from 0222 took an `AccessExclusiveLock` on `recommendation`
    on the very first statement and **held it for the entire run** — the whole
    table was unreadable/unwritable for hours.
  - an unrelated migration that happened to be pending in the same batch was
    coupled to this one and would have rolled back with it.
  - you cannot `COMMIT` between batches, so the backfill could only be one giant
    statement — huge WAL, table bloat (~2×), and **EBS IO burst balance was
    being drained** with no way to report progress or resume.
  - `CREATE INDEX CONCURRENTLY` is impossible (it can't run in a transaction),
    so the index was built non-concurrently *before* the backfill — meaning the
    `UPDATE` also had to maintain that index for every row.

The `2026_01_06_recommendation_cover.sql` file already in the repo (a manual,
un-journaled `CREATE INDEX CONCURRENTLY`) showed a prior author hitting the same
wall and stepping outside drizzle for it.

## Decision

**Schema DDL stays in drizzle migrations; data backfills and online operations
do not.**

- drizzle migrations contain only instant, metadata-only DDL: `ADD COLUMN`
  (nullable, no default) and `ADD CONSTRAINT FK ... NOT VALID`. These commit in
  milliseconds and release the exclusive lock immediately.
- Backfills run as standalone, idempotent, resumable `tsx` scripts that **commit
  in batches** (keyset-paginated by `id`), with a configurable inter-batch pause
  to protect IO burst balance.
- Index creation (`CREATE INDEX CONCURRENTLY IF NOT EXISTS`) and FK validation
  (`VALIDATE CONSTRAINT`) happen in that script, online, after the data is in.

See `src/app/db/backfill-recommendation-denormalization.ts`.

## Consequences

- Deploys gain a step: after `npm run migration:migrate`, run the relevant
  backfill script. This is a manual ops step, not part of the migrate command.
- Backfills no longer hold long locks, no longer block unrelated migrations, stay
  within IO budget, and report progress + can be resumed after interruption.
- Because the columns are populated out-of-band, any later `SET NOT NULL` /
  drop-join-table step must be its own migration, gated on the backfill having
  completed and verified zero unexpected NULLs (note: `material_id` is expected
  to remain nullable — not every recommendation has a material).
- Trade-off: backfills are no longer atomic with their schema change, and the
  schema can briefly exist with unpopulated columns. The read paths must tolerate
  NULLs until the backfill finishes — which is acceptable and was already the
  case (the columns are new).
