Migrations¶

Migrations are the single highest-risk change in this codebase. A broken migration aborts Railway deploys and blocks every environment above it. This chapter is the contract the AI follows every time it touches prisma/schema.prisma.

Read this chapter before running npx prisma migrate dev. Every time.

For pipeline context (dev/stage/prod, migrate dev vs migrate deploy, Railway wiring), see chapter 10.

Rule 1 — Pre-commit migrations are editable¶

Committed migrations are immutable. Uncommitted migrations are not.

Once a migration SQL file is committed (merged to any shared branch, or even pushed to a branch others are tracking), treat it as frozen — fix it with a new migration on top. But while a migration still only exists locally on your working branch and has not been committed, it is fair game to fold a follow-up schema edit into it instead of stacking a new migration.

This matters because the AI tends to create one new migration per schema tweak. Over a single feature that touches three fields, that leaves a trail of add-x, fix-x-nullable, add-x-default migrations where one coherent add-x should exist. The trail is noise at best and a deploy hazard at worst.

The rule, step by step¶

Before running npx prisma migrate dev:

Run git status and inspect prisma/migrations/.
If there is an untracked migration folder under prisma/migrations/, stop and ask the user:

"There's an uncommitted migration <timestamp>_<name>. Fold this change into it, or create a new one?"
If the user says fold:
Delete the uncommitted migration folder: rm -rf prisma/migrations/<timestamp>_<name>.
Run npx prisma migrate reset to wipe the local DB and re-apply remaining (committed) migrations. This is safe — it only touches local Docker Postgres.
Re-run npx prisma migrate dev --name <same-or-better-name>. Prisma now generates one migration containing the combined diff.
If the user says new: proceed with migrate dev as a normal new migration.

If there is no uncommitted migration, skip straight to Rule 2.

Rule 2 — Deploy-safety audit on every new migration¶

After prisma migrate dev generates the SQL, read the generated migration.sql and walk it against the hazard checklist below. If any hazard matches, the AI does not commit. It surfaces the matched hazard(s) to the user with the safer path, and waits for the user's decision.

The generated file lives at prisma/migrations/<timestamp>_<name>/migration.sql.

Hazard checklist¶

#	Pattern in SQL	Why it breaks deploy	Safer path
1	`ADD COLUMN ... NOT NULL` without `DEFAULT`	Fails on any non-empty table. Existing rows have no value.	Add the column nullable → backfill via `UPDATE` (inline in the same migration, or a separate data migration) → `ALTER COLUMN ... SET NOT NULL`. Three statements, one migration for small tables; three migrations for large tables.
2	`CREATE UNIQUE INDEX` / `ADD CONSTRAINT ... UNIQUE`	Fails if duplicates already exist in the column(s).	Query for duplicates first (`SELECT col, COUNT() FROM t GROUP BY col HAVING COUNT() > 1`). Write a dedupe data step. Then add the constraint.
3	`DROP COLUMN` / `DROP TABLE`	Old application pods still query it during a rolling deploy. Queries 500 until the old pods are recycled.	Two-release dance: stop writing/reading the column in release N (code change only), drop in release N+1 (schema change only). Never the same release.
4	`ALTER COLUMN ... TYPE` with incompatible cast (e.g. `TEXT` → `INTEGER`)	Postgres rejects the ALTER on populated tables.	Add a new column with the target type → backfill → swap references in code → drop the old column in a later release.
5	`RENAME COLUMN` / `ALTER TABLE ... RENAME TO`	Same failure mode as drop — old pods query the old name during the deploy window.	Add-new / dual-write / drop-old across releases. Prisma often generates renames when you rename a field in `schema.prisma` — consider whether the rename is worth the ceremony, or add a new field and deprecate the old one instead.
6	Enum value removed or renamed (`ALTER TYPE ... DROP VALUE`, or Prisma's drop-and-recreate pattern for enums)	Fails if any row still references the removed value.	Migrate rows off the value in a data step first (`UPDATE t SET col = 'NEW' WHERE col = 'OLD'`), then alter the enum.
7	`ADD CONSTRAINT ... FOREIGN KEY`	Fails on orphan rows in the child table.	Clean or null-out orphans in a preceding data step (`DELETE FROM child WHERE parent_id NOT IN (SELECT id FROM parent)` or `UPDATE child SET parent_id = NULL WHERE ...`).
8	`DropForeignKey` immediately followed by table recreation in the same migration	Prisma's destructive-change path — it drops the table and recreates it, wiping every row. Easy to miss in the generated SQL.	Do not commit this migration. Revisit the `schema.prisma` edit — usually caused by reordering fields, changing a primary key type, renaming a relation, or changing an `@@id` composite. Adjust the edit so Prisma generates a non-destructive diff.

Process flow¶

1. Check for uncommitted migration (Rule 1)
   ├── fold → delete folder, migrate reset, re-run migrate dev
   └── new  → continue

2. Run: npx prisma migrate dev --name <descriptive-name>

3. Read: prisma/migrations/<timestamp>_<name>/migration.sql

4. Walk the hazard checklist against every statement in the file.

5. Clean?
   ├── yes → report one-line summary of the migration's effect, state "safe to commit"
   └── no  → list each matched hazard with the offending SQL lines,
             propose the safer path from the table,
             STOP — do not commit, wait for user decision.

Worked example¶

User asks: "Add a required email column to staff."

AI: git status → no uncommitted migration. Proceeding.
AI: npx prisma migrate dev --name add-staff-email

Generated SQL:
    ALTER TABLE "staff" ADD COLUMN "email" TEXT NOT NULL;

AI: Hazard #1 matched — ADD COLUMN NOT NULL without DEFAULT.
    The `staff` table has existing rows in every environment.
    This migration will fail on the first environment that runs
    `prisma migrate deploy` against a populated DB (dev, stage, prod).

    Safer path:
      1. Add `email TEXT` (nullable) in this migration.
      2. Backfill with a reasonable value — do we have one? Options:
         (a) Use `identificationCode || '@placeholder.local'`
         (b) Leave null and make the app treat null as "not yet set"
      3. If (a): follow up with `ALTER COLUMN email SET NOT NULL` in
         the same migration (inline) or a subsequent one.

    Waiting for your decision before committing.

User: Do (b) — nullable is fine, we'll enforce it in the DTO.

AI: Edits schema.prisma to make email optional, reruns migrate dev
    (folding into the existing uncommitted migration per Rule 1),
    verifies the new SQL is just `ADD COLUMN "email" TEXT;`, reports
    clean, asks permission to commit.

This is the shape of every migration interaction from now on.