Database work can outlive the code that introduced it. Begin with the data transition and recovery story, then change the schema.
Where database behaviour lives
apps/server/prisma/schema/*.prismacontains the current split domain schema.apps/server/prisma/migrationscontains historical SQL migrations, but it is not a complete replay of every current schema change.apps/server/prisma/drop-retired-views.sqlremoves retired database objects before schema sync.apps/server/src/db/run-on-bootcontains numbered TypeScript data migrations run on every server start.
The current schema, historical migrations, cleanup SQL, boot-time data work, and release command together form the real contract. Reading only one directory is insufficient.
Know what each command does
| Command | Effect | Boundary |
|---|---|---|
pnpm --dir apps/server prisma:generate | Regenerates the Prisma client from schema in the dependency-managed output. | Local code generation; it does not normally create a tracked schema artefact. |
pnpm --dir apps/server prisma:push | Drops retired objects, pushes the current schema, then deploys checked-in SQL migrations. | Mutates the database named by the environment. Use only an identified disposable or explicitly authorised database. |
pnpm --dir apps/server migrate | Drops retired objects and runs Prisma db push --accept-data-loss. | Destructive and incomplete as a full release migration: it does not run prisma migrate deploy. Never use as an exploratory command or present it alone as the release procedure. |
DATABASE_URL is used by the running server. DIRECT_URL is the Prisma command datasource. Resolve both before a mutating command; a correct command pointed at the wrong database is still an incident.
The change procedure
- Record the model, affected rows, compatibility window, and recovery plan on the task.
- Inspect the current schema, relevant queries, historical migration, cleanup SQL, and boot migration.
- Make the smallest schema change that can coexist with currently deployed code.
- If an existing row needs transformation, choose an idempotent backfill with observable progress.
- Regenerate Prisma output as applicable.
- Review the generated SQL or schema diff for drops, rewrites, locks, nullability, defaults, indexes, retention, and backfill behaviour.
- Exercise the transition against a disposable PostgreSQL database containing representative old data.
- Run server routes, types, and tests.
- Put the complete production migration sequence, expected duration, verifier, and stop condition in the release task. Do not assume the repository’s
migratescript deploys checked-in SQL migrations. - After rollout, verify both the data invariant and the user-visible path that depends on it.
Prefer expand-and-contract for a breaking data shape: add the compatible field or table, deploy readers and writers, backfill, verify, then remove the old shape in a later release.
Retired profile history
The generated history schema, SQL, triggers, tables, and profile-history endpoint have been removed. Do not build new work against the retired schema or endpoint.
Do not restore or query the retired history tables as an implicit audit mechanism. A new audit, revision, or recovery requirement needs an explicit data model, retention rule, privacy review, access boundary, and migration plan.
Boot-time data work
Every numbered TypeScript module under src/db/run-on-boot is discovered, sorted, and awaited on every server start. A new module must therefore be safe to run repeatedly, safe after partial completion, bounded enough for startup, and explicit about failures.
That is the rule for new work, not a claim that every current module already meets it. Current debt includes a disabled migration, migrations that log and continue after some failures, a CDN migration that exits the process when configuration is absent, and a temporary body backfill that scans batches until exhausted. A listening server is therefore not proof that every intended data change completed. Check the evidence for each relevant numbered migration during a release or incident.
Do not use a boot migration for a long, opaque, one-time operation merely because it avoids writing a release step. If work needs progress, pause, resume, or operator judgement, build that behaviour deliberately.