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.

Recovering a wedged deploy (P3009)¶

When prisma migrate deploy fails mid-pipeline, Railway aborts the deploy and the old app version keeps running, but the attempt is recorded in _prisma_migrations (no finished_at). Every subsequent deploy then aborts with P3009 until that record is cleared — new migrations will not apply on top of a failed one.

Postgres wraps each migration in a transaction, so the failed migration's DDL has almost always rolled back: the schema sits at the pre-migration state and only the bookkeeping row is dirty. The actual SQL error is hidden behind P3009 — it lives in _prisma_migrations.logs.

A migration can pass locally yet fail here. The Railway dev DB persists across deploys (it is only re-seeded, never reset), so it carries stale rows a fresh local DB never has. A new CHECK / UNIQUE / FK that those stale rows violate fails only on deploy.

Two tools handle recovery. Both take a connection string — use the Railway public proxy URL (*.proxy.rlwy.net, from dashboard → Postgres → Variables → DATABASE_PUBLIC_URL), since the internal postgres.railway.internal host is unreachable from a local machine.

Tool	Use
`node tools/db-migrations.mjs "<url>" failed`	Read the real error out of `_prisma_migrations.logs`. Also `list`, and `delete <migration_name>` to clear just the failed record (surgical — for transient/drift causes, not data).
`node tools/drop-db-schema.mjs`	Full reset (`DROP SCHEMA public CASCADE` + recreate). Reads the URL from a gitignored `tools/.reset-db.url` or `DATABASE_URL`, so no credentials hit the command line. Prompts for a typed confirmation (`--yes` to skip). Dev/staging only — wipes all data; safe there because those DBs re-seed on every deploy.

After clearing or resetting, trigger a redeploy so migrate deploy (+ db seed on non-prod) rebuilds the schema.

Production has no disposable DB — never drop-db-schema it. Diagnose with db-migrations.mjs failed, fix the offending rows with a forward data step (migrate them off the bad state), then delete the failed record and redeploy.

Schema constraints that live only in raw SQL¶

Some constraints can't be expressed in prisma/schema.prisma and exist only inside migration SQL. They are invisible when reading the schema file alone. Audit this list before assuming "the schema is the truth":

Constraint	Kind	Where declared	What it enforces
`uniq_academic_year_active_per_tenant` on `academic_years`	Partial UNIQUE (Postgres) `(tenant_id) WHERE status = 'ACTIVE'`	Migration `20260525150000_active_year_unique_and_invitation_token_ttl`	At most one ACTIVE academic year per tenant. `getActiveYear`'s `findFirst({orderBy: createdAt desc})` relies on this. (A duplicate twin `academic_years_tenant_id_active_unique` from a fold-rerun was dropped in `20260625160000_wave2b_id_code_unique_and_constraint_cleanup`.)
`curriculum_subjects_mandatory_xor_block_chk` on `curriculum_subjects`	CHECK	Curriculum grid remodel migration `20260604120000_curriculum_grid_remodel`	A `curriculum_subjects` row is either mandatory (`option_block_id IS NULL` and `is_mandatory = true`) or an option-block alternative (`option_block_id IS NOT NULL` and `is_mandatory = false`). Rejects half-states. (Carried from the dropped `study_plan_subjects` CHECK.)
`curriculum_subjects_block_track_chk` on `curriculum_subjects`	CHECK	Curriculum grid remodel migration	An in-block subject inherits its block's track and never carries its own `track_id`: `option_block_id IS NULL OR track_id IS NULL`.
`curriculum_subjects_container_name_key` on `curriculum_subjects`	UNIQUE index (COALESCE) `(curriculum_id, name, COALESCE(track_id, zero-uuid), COALESCE(option_block_id, zero-uuid))`	Curriculum grid remodel migration	Per-container subject-name uniqueness — the same name may repeat across tracks/blocks but not within one container. COALESCE(zero-uuid) emulates NULLS NOT DISTINCT (portable below PG 15). Prisma can't model it, so P2002 maps by index name.
`option_blocks_container_name_key` on `option_blocks`	UNIQUE index (COALESCE) `(curriculum_id, name, COALESCE(track_id, zero-uuid))`	Curriculum grid remodel migration	Per-container option-block-name uniqueness (same name may repeat across tracks).
`curriculum_subject_hours_window_chk` on `curriculum_subject_hours`	CHECK	Migration `20260605140000_curriculum_teaching_windows`	A cell is either taught all year (`taught_all_year = true` and both `teaching_start_date`/`teaching_end_date` NULL) or has a window (`taught_all_year = false` and both dates set with `teaching_start_date <= teaching_end_date`). Rejects half-states; window-⊆-department-calendar is enforced service-side.
`curriculum_rules_scope_ref_chk` on `curriculum_rules`	CHECK	Migration `20260608140404_ib_subject_levels_constraints`, rewritten by `20260623120000_drop_block_rule_scope`	A rule's ref column matches its scope: `(scope_type = 'LEVEL') = (level_id IS NOT NULL)`. ENTIRE_PLAN carries no ref. Belt for the service-layer `assertValidRules`. (Originally also gated `BLOCK`/`option_block_id`; the `BLOCK` scope + `option_block_id` column were dropped 2026-06-23 — per-block cardinality lives on `OptionBlock.minSelections/maxSelections`.)
`subject_levels_platform_name_key` on `subject_levels`	Partial UNIQUE `(name) WHERE tenant_id IS NULL`	Migration `20260608140404_ib_subject_levels_constraints`	Platform-preset subject levels (tenant_id NULL) have unique names across the platform. Mirrors `evaluation_scales_platform_name_key`.
`roles_platform_key_key` on `roles`	Partial UNIQUE `(key) WHERE tenant_id IS NULL`	Migration `20260609133037_role_tenant_nullable`	Platform-preset roles (tenant_id NULL) have unique keys across the platform — one global preset per key, cloned per tenant at provisioning. Mirrors `evaluation_scales_platform_name_key`.
`evaluation_scales_platform_name_key` on `evaluation_scales`	Partial UNIQUE `(name) WHERE tenant_id IS NULL`	Migration `20260518105740_evaluation_scales`	Platform-preset grading scales (tenant_id NULL) have unique names across the platform. The archetype the two `_platform__key` rows above mirror.
`room_types_platform_name_key` on `room_types`	Partial UNIQUE `(name) WHERE tenant_id IS NULL`	Migration `20260615120000_room_types_platform`	Platform-default room types (tenant_id NULL) have unique names across the platform; tenant-custom types are scoped by the full `(tenant_id, name)` unique.
`timetables_tenant_ay_published_unique` on `timetables`	Partial UNIQUE `(tenant_id, academic_year_id) WHERE status = 'PUBLISHED'`	Migration `20260612101247_add_timetables`	At most one PUBLISHED timetable per (tenant, academic year). Backs the publish gate; the index-race surfaces as `TIMETABLE_PUBLISH_CONFLICT` via `mapP2002`.
`attendance_daily_unique` on `attendance_records`	Partial UNIQUE `(tenant_id, student_id, date) WHERE period_ordinal_position IS NULL`	Migration `20260625133520_add_attendance`	One DAILY-mode cell per student per school day (NULL period). Exists only in raw SQL because Postgres treats NULLs as distinct, so a plain composite unique would not dedupe daily cells.
`attendance_period_unique` on `attendance_records`	Partial UNIQUE `(tenant_id, student_id, date, period_ordinal_position) WHERE period_ordinal_position IS NOT NULL`	Migration `20260625133520_add_attendance`	One PERIOD-mode cell per student/date/period. Same raw-SQL-only rationale as the daily index.
`tenant_isolation_<t>` policies (+ `ENABLE`/`FORCE ROW LEVEL SECURITY`) on the 8 PII tables: `students`, `referents`, `guardians`, `teachers`, `staff`, `files`, `attendance_records`, `student_curriculum_selections`	RLS policy (USING + WITH CHECK)	Migration `20260702082621_enable_rls_high_value_pii`	Hard DB-level tenant isolation (Wave-4 RLS, audit #1): rows are visible/writable only when `tenant_id` matches the request GUC `app.current_tenant_id` (`NULLIF(current_setting(…, true), '')::uuid`, scalar-subquery-wrapped). `FORCE` keeps the table owner subject to the policy; the runtime `app_user` role has no BYPASSRLS. See chapter 02.
`tenant_isolation_<t>` policies (+ `ENABLE`/`FORCE`) on the remaining 50 tenant-bearing tables — every Class S/M/C table in `src/prisma/rls-coverage.ts`	RLS policy (USING + WITH CHECK; 3 predicate shapes)	Migration `20260702130000_extend_rls_full_coverage`	RLS full coverage (58/66 tables total): simple `tenant_id` equality (Class S), `IS NULL OR` for the mixed global/tenant catalogs (Class M), explicit `EXISTS`-to-ancestor for children/grandchildren (Class C). Generated from the coverage registry; the migration header documents each shape's rationale + the rollback runbook. Per-table policy existence + `FORCE` are CI-asserted from the registry by `test/db-constraints.e2e-spec.ts`. See chapter 02 §4.

Add a row to this table when introducing any new raw-SQL constraint. The full set is CI-asserted by test/db-constraints.e2e-spec.ts (audit R2): it queries pg_indexes/pg_constraint against the migrated DB and fails if any named constraint here is missing (the guard a db push / schema-squash would otherwise defeat silently). Adding a raw constraint means adding both a row here and a name to that spec.

Removed (parametric roles 20260527120000): user_roles_unique_when_no_department (partial (userId, roleId, tenantId) WHERE department_id IS NULL) was dropped together with the department_id column; parametric assignments moved to user_role_parameters. User-role uniqueness is now a single declarative @@unique([userId, roleId, tenantId]) (user_roles_user_id_role_id_tenant_id_key) — migrate-diff-visible, so no row here.

Removed 20260605120000_drop_ay_calendar: the two periods partial uniques (uniq_period_ay_name_default, uniq_period_dept_name_override) were dropped when the AY-default period set went away (calendar moved entirely per-department). Period-name uniqueness is now a single declarative @@unique([departmentId, name]) (expressible in the schema because Period.departmentId is non-null) — no raw SQL, so no row here.

Migration folder naming¶

Folders are named YYYYMMDDhhmmss_<slug>/. Slugs are immutable once committed to a shared branch. Do not prepend a second timestamp to the slug when folding (20260526090202_20260525150000_active_year_unique_… is a known artifact of a fold-then-rerun and defeats git bisect over the migrations directory). If a fold renames the slug, rename the entire folder — don't suffix.