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 1a — "uncommitted" is not the same as "unapplied"¶

Step 2's test is untracked in git. That is the right test for whether folding is allowed, and the wrong test for whether folding is safe. A migration can be uncommitted and still already applied — to your local sis_dev, to the disposable e2e databases (sis_e2e_template + its per-worker clones), or to a Railway environment you pushed to earlier. Folding into a file in that state produces two failures, both silent:

The appended statements never run. prisma migrate deploy never re-runs or re-checksums a migration already recorded in _prisma_migrations; it only applies pending ones. Every database that applied the pre-fold file keeps the pre-fold schema, reports no pending migration, and raises no error. The app then boots against a schema missing whatever was appended.
The checksum no longer matches. Prisma stores a hash of each applied migration and refuses to run against a database whose recorded hash disagrees with the file on disk ("migration was modified after it was applied"). That is bookkeeping, not schema, and no migration can repair it — it needs the recorded checksum for that one row corrected. It is also not drift, so do not resolve it with a reset.

Step 3's migrate reset protects against (1) locally only. It cannot reach any other database.

So before folding, also confirm the target file has not been applied anywhere but a database you are willing to reset. npx prisma migrate status against each environment answers it.

If you discover a fold after the fact, do NOT un-fold by editing the file — removing the appended statements changes the checksum and breaks every healthy environment in order to repair the unhealthy ones. Repair forward instead: a new migration that repeats the appended DDL idempotently, so it is a no-op where the fold landed and a fix where it did not. Worked example in the tree: 20260729090000_c3_dependency_closure_repair, repairing C3's DDL that was folded into 20260728120000_membership_valid_time. Note what idempotent has to mean statement by statement — ADD COLUMN IF NOT EXISTS, DROP INDEX IF EXISTS, CREATE TABLE IF NOT EXISTS, SET NOT NULL (already a no-op), a pg_constraint lookup in a DO block for every ADD CONSTRAINT (PostgreSQL has no IF NOT EXISTS there), DROP POLICY IF EXISTS before CREATE POLICY, and a NOT EXISTS guard on any backfill whose re-run would violate a constraint rather than merely duplicate.

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.
9	`CREATE EXTENSION`	Needs a role with `CREATE` on the database. `migrate deploy` runs as the DB owner in every environment we operate, but a locked-down role wedges the whole migration (ch12's P3009 path).	Write it `IF NOT EXISTS` so the recovery re-run is safe, and verify once against the target environment before promoting. The program's `btree_gist` — required by any `EXCLUDE USING gist` with a uuid equality column — is created in `20260727190000_student_placement_valid_time`; later EXCLUDE migrations need not repeat it.

Sanctioned exception — the constant-USING column wipe. An ALTER COLUMN ... TYPE <t> USING '<constant>'::<t> that casts every row to one literal is a deliberate wipe, not a hazard-#4 violation. It is accepted when the pre-existing values are known-throwaway and you are converting a free-text/legacy column to a curated enum in a dev-dropable environment. Precedent: 20260622120000_align_teacher_staff_diet_type_enum did ALTER COLUMN "diet_type" TYPE "diet_type" USING 'STANDARD'::"diet_type" (+ SET DEFAULT 'STANDARD'), resetting all teacher/staff diets to STANDARD on purpose (contrast the student-side conversion in 20260409150816, which preserved values via a CASE). Call the wipe out explicitly in the migration's header comment and in the change's summary so a reviewer signs off on the data loss knowingly — the hazard checklist is about unintended loss, and this is intended. Production caveat (2026-07-16): the project now has production tenants, so this sanctioned wipe applies only in dev/stage-resettable environments or on genuinely throwaway columns — never against populated production data. Breaking changes remain permitted but are no longer free: flag FE-breaking contract changes and weigh real prod data / rolling-deploy impact before any destructive step.

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_unit_shape_chk` on `curriculum_subjects`	CHECK	Subject units migration `20260716092919_subject_units`	A UNIT (subject with `parent_subject_id` set) inherits its container's containment and is never directly in a block/track: `parent_subject_id IS NULL OR (option_block_id IS NULL AND track_id IS NULL)`. Belt for the nested-DTO structure; one-level-deep is enforced by the DTO, not SQL.
`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), COALESCE(parent_subject_id, zero-uuid))`	Curriculum grid remodel migration; extended by `20260716092919_subject_units` to add the `parent_subject_id` column	Per-container subject-name uniqueness — the same name may repeat across tracks/blocks/parents but not within one container. The `parent_subject_id` term lets units of different containers (and a unit vs a top-level subject) share a name. 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 (tenantid 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`.
`timetables_open_revision_unique` on `timetables`	Partial UNIQUE `(revision_of_id) WHERE status = 'DRAFT'`	Migration `20260726160000_timetable_revision_history`	At most one OPEN revision per published timetable. Replaced the plain `@@unique([revisionOfId])` when republish started retaining the version it supersedes: a retained version is ARCHIVED and shares the published row's `revision_of_id`, so a total unique would reject the second republish of any lineage. Recreated without its predicate this stays "present" while breaking every republish — hence the predicate assertion in `db-constraints.e2e-spec.ts`, not just an existence check. The companion plain `timetables_revision_of_id_idx` is declarative (Postgres does not auto-index FKs, and the partial unique covers only DRAFTs).
`tenant_isolation_attendance_day_events`, `tenant_isolation_attendance_contact_logs` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260725155816_attendance_admin_day_register`	Class-S coverage for the two communication-loop tables (both carry required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`.
`timetables_effective_shape_chk` on `timetables`	CHECK	Migration `20260727120000_timetable_valid_time_and_temporal_epoch`	The valid-time interval's shape is tied to `status`: DRAFT `NULL/NULL`, PUBLISHED `set/NULL` (open), ARCHIVED `set/set`. It is what makes "which version governed date D" answerable without consulting `status` — a DRAFT with an interval, or a PUBLISHED row without one, would both resolve for dates they never governed. Note the forward obligation it creates: the future AY-archival cascade must choose a closing boundary, because it cannot move a row to ARCHIVED and leave `effective_until` NULL.
`timetables_effective_order_chk` on `timetables`	CHECK	Migration `20260727120000_timetable_valid_time_and_temporal_epoch`	`effective_from <= effective_until` whenever both are set. Equality is legal and load-bearing: `[X, X)` is the empty interval — retained evidence of a version superseded before it ever governed a day, matched by no date. Do not "fix" this to a strict `<`.
`student_placements_concurrent_excl` on `student_placements`	EXCLUDE USING gist `(student_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migration `20260727190000_student_placement_valid_time`	At most ONE placement interval covering any given day for a student — the program's first exclusion constraint, and the reason `HomeroomAssignment.studentId @unique`-style single-row uniqueness can become "one concurrent X" without losing history. Two open rows always overlap, so "≤ 1 open placement" is implied rather than separately enforced. The `'[)'` half-open bound is load-bearing: `old.valid_until = X` and `new.valid_from = X` do not overlap, which is exactly what makes the move rule (close at X, open at X) legal. Requires the `btree_gist` extension for the uuid equality column — that `CREATE EXTENSION` lives in this same migration and later EXCLUDE migrations need not repeat it. Recreated as a plain UNIQUE it would still be "present" while permitting two concurrent placements, so `db-constraints.e2e-spec.ts` asserts `contype = 'x'`, not just the name. It does not prevent lost updates — a racing command that reads, then closes an already-closed row, produces non-overlapping rows the constraint accepts; the compare-and-swap predicate in `student-placement.queries.ts` is what rejects that, and both are required.
`homeroom_assignments_concurrent_excl` on `homeroom_assignments`	EXCLUDE USING gist `(student_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migration `20260728120000_membership_valid_time`	At most one concurrent homeroom membership per student. Closed and empty intervals remain legal history; overlapping effective intervals are rejected independently of application checks.
`subject_group_assignments_group_concurrent_excl` on `subject_group_assignments`	EXCLUDE USING gist `(student_id WITH =, subject_group_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migration `20260728120000_membership_valid_time`	Prevents overlapping episodes for the same student in the same subject group while allowing leave/rejoin history.
`subject_group_assignments_subject_concurrent_excl` on `subject_group_assignments`	EXCLUDE USING gist `(student_id WITH =, curriculum_subject_id WITH =, academic_year_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migration `20260728120000_membership_valid_time`	Enforces at most one concurrent subject group for a subject, student, and academic year, including races across different groups.
`student_curriculum_selections_concurrent_excl` on `student_curriculum_selections`	EXCLUDE USING gist `(student_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migration `20260728120000_membership_valid_time`	Makes curriculum, track, carried flags, and choices one non-overlapping effective-dated aggregate. The application still uses compare-and-swap for lost-update protection.
`subject_group_teachers_concurrent_excl` on `subject_group_teachers`	EXCLUDE USING gist `(subject_group_id WITH =, teacher_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migrations `20260728120000_membership_valid_time` + `20260729090000_c3_dependency_closure_repair`	At most one concurrent assignment episode per (course, teacher) — it replaced the declarative `@@unique([subjectGroupId, teacherId])`, which made re-assigning a teacher to a course they had left impossible because the closed episode still occupied the pair. `db-constraints.e2e-spec.ts` therefore asserts both directions: this constraint present with `contype = 'x'`, and `subject_group_teachers_subject_group_id_teacher_id_key` absent — a schema-squash that reinstated the unique would break re-assignment while every existence check still passed. Teacher assignment is a write-authorization input, so a hole here is an authz bug, not a display bug.
`combined_class_memberships_concurrent_excl` on `combined_class_memberships`	EXCLUDE USING gist `(subject_group_id WITH =, daterange(valid_from, valid_until, '[)') WITH &&)`	Migrations `20260728120000_membership_valid_time` + `20260729090000_c3_dependency_closure_repair`	At most one concurrent membership episode per section — keyed on `subject_group_id` alone, not the (combination, section) pair. That is deliberate and load-bearing: the invariant is "a section is in at most one combination at a time", which a pair key would NOT enforce (it would happily allow the same section in two combinations concurrently). So this constraint is the `≤1 combination per section` rule; the nullable `subject_groups.combined_class_id` forward head is a denormalized convenience re-synced in the same transaction, not the enforcement. A mid-year move stays legal because `'[)'` lets close-on-CC-1-at-X and open-on-CC-2-at-X coexist.
`tenant_isolation_student_placements` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260727190000_student_placement_valid_time`	Class-S coverage for the placement history table (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`. Note the EXCLUDE above is deliberately keyed on `student_id` alone — tenancy is RLS's job, not the constraint's.
`tenant_isolation_combined_class_memberships` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260728120000_membership_valid_time`	Class-S coverage for the membership history table (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts` + `src/tenants/tenant-reset.coverage.ts`. `subject_group_teachers` needs no row of its own — it is a pre-existing Class-C table whose policy came with the full-coverage migration.
`attendance_day_slots_anchor_xor` on `attendance_day_slots`	CHECK	Migration `20260726124135_attendance_day_shape_snapshot`	A frozen day-slot has exactly ONE anchor (`subject_group_id` / `combined_class_id` / `activity_id`), and `anchor_key` agrees with it (`'sg:'`/`'cc:'`/`'act:'` + the id). Extends the `scheduled_lessons_anchor_xor` shape with the key agreement: `anchor_key` exists only because Postgres treats NULLs as distinct, so the nullable triple can't carry the unique index — which makes a drifted key a silent uniqueness hole. The unique itself (`attendance_day_slot_unique`) is declarative and migrate-diff-visible, so it carries no row here.
`tenant_isolation_attendance_day_slots` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260726124135_attendance_day_shape_snapshot`	Class-S coverage for the frozen day-shape table (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`.
`scheduled_breaks_axis_chk` on `scheduled_breaks`	CHECK	Migration `20260709205707_manual_flow_template_decouple`	A break's coordinate + stored extent stay on the 5-minute grid inside one day: `start_tick` 0..287, positive 5′-multiple `duration_minutes`, `start_tick*5 + duration_minutes ≤ 1440`. Belt for the service-side floor check.
`scheduled_lessons_anchor_xor` on `scheduled_lessons`	CHECK	Migration `20260712120000_combined_class_first_class_anchor`	A lesson has exactly ONE teaching-unit anchor: `(subject_group_id IS NULL) <> (combined_class_id IS NULL)`. Dual-anchor XOR for combined-class first-class lessons (iteration-3 spec); Prisma can't express it.
`scheduled_break_audiences_one_of_chk` on `scheduled_break_audiences`	CHECK	Introduced by `20260709205707_manual_flow_template_decouple`; widened by `20260826130000_timetable_roster_weekday_supervisors`	An audience selector row carries exactly ONE of `department_id`/`curriculum_id`/`grade_id`/`track_id`/`homeroom_id`/`student_id` (rows union; each resolves live to students).
`scheduled_break_audiences_selector_key` on `scheduled_break_audiences`	UNIQUE index (COALESCE) `(break_id, COALESCE(each ref, zero-uuid))`	Introduced by `20260709205707_manual_flow_template_decouple`; widened by `20260826130000_timetable_roster_weekday_supervisors`	No duplicate selector row per break across all six refs. COALESCE(zero-uuid) emulates NULLS NOT DISTINCT (same pattern as `curriculum_subjects_container_name_key`).
`day_bounds_one_of_chk` on `day_bounds`	CHECK	Migration `20260709205707_manual_flow_template_decouple`	A day-bounds override targets exactly ONE cascade level: one of `department_id`/`curriculum_id`/`grade_id` set.
`day_bounds_range_chk` on `day_bounds`	CHECK	Migration `20260709205707_manual_flow_template_decouple`	Sane wall-clock range on the 5-minute grid: `0 ≤ start_minute < end_minute ≤ 1440`, both 5′-multiples.
`day_bounds_scope_alldays_key` + `day_bounds_scope_weekday_key` on `day_bounds`	Partial UNIQUE pair (COALESCE) `(tenant_id, COALESCE(refs, zero-uuid)[, weekday]) WHERE weekday IS [NOT] NULL`	Migration `20260709205707_manual_flow_template_decouple`	One override per (tenant, ref, weekday), with `weekday IS NULL` as the all-days row. Split into two partial indexes because a nullable enum can't ride the COALESCE emulation (`enum::text` is not IMMUTABLE) — same split pattern as the attendance uniques.
`tenant_isolation_<t>` policies (+ `ENABLE`/`FORCE`) on `scheduled_breaks`, `scheduled_break_audiences`, `scheduled_break_duties`, `day_bounds`	RLS policy (USING + WITH CHECK)	Migration `20260709205707_manual_flow_template_decouple`	Class-S coverage for the four manual-flow iteration-3 tables (all carry required `tenant_id`); registered in `src/prisma/rls-coverage.ts`.
`scheduled_activities_axis_chk` on `scheduled_activities`	CHECK	Migration `20260713134617_timetable_activities`	An activity's coordinate + stored extent stay on the 5-minute grid inside one day (`start_tick` 0..287, positive 5′-multiple `duration_minutes`, `start_tick*5 + duration_minutes ≤ 1440`) and the `weekdays` repeat set is non-empty.
`scheduled_activity_audiences_kind_ref_chk` on `scheduled_activity_audiences`	CHECK	Migration `20260713134617_timetable_activities`	A kind-discriminated selector row: `EVERYONE` carries no ref, every other `kind` carries exactly its matching ref (`department_id`/`curriculum_id`/`grade_id`/`track_id`/`homeroom_id`/`student_id`).
`scheduled_activity_audiences_selector_key` on `scheduled_activity_audiences`	UNIQUE index (COALESCE) `(activity_id, kind, COALESCE(each ref, zero-uuid))`	Migration `20260713134617_timetable_activities`	No duplicate selector row per activity (`kind` included so two `EVERYONE` rows dedupe). Same NULLS-NOT-DISTINCT COALESCE pattern as the break selector key.
`tenant_isolation_<t>` policies (+ `ENABLE`/`FORCE`) on `scheduled_activities`, `scheduled_activity_audiences`, `scheduled_activity_teachers`	RLS policy (USING + WITH CHECK)	Migration `20260713134617_timetable_activities`	Class-S coverage for the three activity tables (all carry required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`.
`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_attendance_justifications` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260804165840_attendance_justifications`	Class-S coverage for the family-submitted justification table (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`.
`tenant_isolation_notifications` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260804202357_notifications_inbox`	Class-S coverage for the notification-engine inbox table (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`. The inbox channel writes under `withTenantGuc`, so the policy holds for background (sweeper-driven) sends too.
`disciplinary_note_state_coherent` on `disciplinary_notes`	CHECK	Migration `20260811120000_disciplinary_notes`	Pins the four note lifecycle states to coherent content, delivery, acknowledgement, and withdrawal field sets. Active text must be non-blank; acknowledged and withdrawn provenance cannot leak into another state.
`assignment_details_nonblank`, `assignment_due_on_or_after_assigned` on `assignments`; `course_day_lesson_content_text_nonblank` on `course_day_lesson_contents`	CHECK set	Migration `20260824120000_class_register_assignments`	Assignment details remain non-blank, due/test date cannot precede the selected assigned date, and a retained lesson-content row is either a cleared `NULL` tombstone or non-blank text.
`tenant_isolation_disciplinary_notes` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260811120000_disciplinary_notes`	Class-S coverage for family-visible disciplinary notes (required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts`. The background delivery sweeper claims rows under `withTenantGuc`.
`mailing_groups_shared_name_key` on `mailing_groups`	Partial UNIQUE `(tenant_id, name) WHERE owner_user_id IS NULL`	Migration `20260805104913_communications_module`	Shared (admin-curated) mailing groups share one per-tenant name namespace. P2002 on it maps to `MAILING_GROUP_NAME_TAKEN`.
`mailing_groups_private_name_key` on `mailing_groups`	Partial UNIQUE `(tenant_id, owner_user_id, name) WHERE owner_user_id IS NOT NULL`	Migration `20260805104913_communications_module`	Private mailing groups are unique per (tenant, owner) — two users may both own a "My class" group. Same P2002 mapping as the shared namespace.
`tenant_isolation_<t>` policies (+ `ENABLE`/`FORCE`) on `mailing_groups`, `mailing_group_members`, `communications`, `communication_recipients`, `communication_mailing_groups`	RLS policy (USING + WITH CHECK)	Migration `20260805104913_communications_module`	Class-S coverage for the five communications tables (all carry required `tenant_id`); registered in `src/prisma/rls-coverage.ts` + `tenanted-models.ts` + `src/tenants/tenant-reset.coverage.ts`. The sender sweeper writes under `withTenantGuc`; the webhook consumer looks recipients up by PK on the tenant-less webhook path (invitations precedent).
`invitation_deliveries_one_active` on `invitation_deliveries`	Partial UNIQUE `(invitation_id) WHERE status IN ('QUEUED','SENDING')`	Migration `20260810122741_invitation_delivery_outbox`	At most one in-flight invitation send attempt. The queue transaction's active-delivery check is the friendly gate; this index is the race-proof truth.
`tenant_isolation_invitation_deliveries` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260810122741_invitation_delivery_outbox`	Class-S coverage for the invitation outbox/attempt ledger; sender claims and completion writes run under `withTenantGuc`, and reset cascades from `Invitation`.
`tenant_isolation_password_reset_requests` (+ `ENABLE`/`FORCE`)	RLS policy (USING + WITH CHECK)	Migration `20260817120000_password_reset`	Class-S coverage for password-recovery credential/outbox rows. Public commands and the durable sender write under `withTenantGuc`; tenant reset deletes the table explicitly because the bootstrap admin User survives.
`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.

| grade_entry_revisions_positive_revision, grade_entry_revisions_reason_required, grade_entry_revisions_scalar_level_parity, grade_entry_revisions_scalar_range, grade_entry_revisions_teacher_snapshot_alignment, grade_entry_revisions_snapshot_status_shape | CHECK set on grade_entry_revisions | Migration 20260812150000_grade_revision_ledger | Enforces positive sequence numbers, mandatory reasons after creation, scalar/level parity and 0–100 range, aligned dated teacher arrays, and the complete-vs-legacy evidence shape. LEGACY_INCOMPLETE requires a nonblank explanation; COMPLETE requires dated identity/context, actor display/role, frozen v1 scale objects, stored result, and calculation version. | | grade_criterion_mark_revisions_ordinal_nonnegative | CHECK on grade_criterion_mark_revisions | Migration 20260812150000_grade_revision_ledger | Frozen criterion marks retain a deterministic nonnegative ordinal. | | grade_entry_revisions_criteria_definitions_shape | CHECK on grade_entry_revisions | Migration 20260812150003_grade_criterion_definition_snapshot | Every COMPLETE revision carries a JSON-array snapshot of the full criterion definition set, independently of which criteria were actually marked. Legacy-incomplete rows may remain null. | | grade_revision_parent_consistency, grade_criterion_revision_tenant_consistency | BEFORE INSERT triggers | Migration 20260812150000_grade_revision_ledger | A revision must agree with its root on tenant/year/student and previous-revision chain; a criterion revision must agree with its parent revision's tenant. This closes cross-tenant/cross-root links that separate same-table FKs cannot express. | | grade_entry_revisions_immutable, grade_criterion_mark_revisions_immutable | BEFORE UPDATE/DELETE triggers | Migration 20260812150000_grade_revision_ledger | Rejects in-place mutation and deletion of ledger evidence. The only bypass is the transaction-local app.grade_ledger_maintenance=on used by controlled development tenant reset/recovery, never an HTTP grade command. | | grade_entries_head_consistency, grade_revisions_head_consistency | Deferred constraint triggers | Migration 20260812150000_grade_revision_ledger | At transaction commit, every root points to its own highest revision and the root state/revision number agree with that head. Deferral permits create-root → append-revision → advance-head in one transaction. | | grade_entry_stable_identity | BEFORE UPDATE trigger | Migration 20260812150002_grade_root_identity_guard | Makes root id/tenant/academic-year/student identity immutable; only lifecycle/head movement is operationally legal. | | tenant_isolation_grade_entry_revisions, tenant_isolation_grade_criterion_mark_revisions (+ ENABLE/FORCE) | RLS policy (USING + WITH CHECK) | Migrations 20260812150000_grade_revision_ledger + 20260812150001_grade_revision_rls_policy_repair | Class-S isolation for both required-tenant_id ledger tables. The repair migration changes the initially applied predicate to the pooled-safe canonical app.current_tenant_id expression without rewriting an applied migration checksum. Both tables are in the RLS, strict-tenancy, and tenant-reset registries. |

| tenant_isolation_push_device_registrations (+ ENABLE/FORCE) | RLS policy (USING + WITH CHECK) | Migration 20260813140000_push_device_registrations | Class-S isolation for Firebase Installation ID registrations (required tenant_id); registered in the RLS, strict-tenancy, and tenant-reset registries. | | tenant_isolation_scheduled_notification_claims (+ ENABLE/FORCE) | RLS policy (USING + WITH CHECK) | Migration 20260825140000_scheduled_notification_claims | Class-S isolation for automatic per-recipient notification milestone claims (required tenant_id); scheduled scans and claims run under withTenantGuc, and the table is registered in the RLS, strict-tenancy, and tenant-reset registries. | | assignment_attachment_payload_shape on assignment_attachments | CHECK | Migration 20260825150000_assignment_attachments | Enforces the exact mixed-wrapper branch: FILE has a File id and no URL/label; LINK has no File id, a nonblank URL, and a nullable label that is nonblank when present. Cross-row count and byte caps remain serialized service invariants. | | tenant_isolation_assignment_attachments (+ ENABLE/FORCE) | RLS policy (USING + WITH CHECK) | Migration 20260825150000_assignment_attachments | Class-S isolation for the required-tenant Assignment child wrapper. Reset cascades from Assignment; File deletion remains RESTRICT until the wrapper is gone. The migration also appends the Assignment collection values to FileUsage and FileOwnerType. |

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.

The same 20260826130000_timetable_roster_weekday_supervisors migration adds the schema-visible, required scheduled_activity_teachers.weekdays enum array with an empty-array default. Existing rows therefore retain their old all-days meaning during rollout. This column is intentionally not a row in the raw-only table above; prisma/schema.prisma models it directly, while the constraint E2E also pins its NOT NULL + default shape.

New tenant-bearing model checklist¶

A migration that introduces a model holding tenant data touches four drift-guarded registries besides the schema — each has a CI guard, but fix them at authoring time, not after the red build:

RLS policy in the migration itself (ENABLE/FORCE + tenant_isolation_<table>; copy the class's predicate shape from the latest RLS migration) plus the class entry in src/prisma/rls-coverage.ts — the decision tree lives in that file's header and chapter 02 §4.0.1. Guards: rls-coverage.drift.spec.ts (classification) + test/db-constraints.e2e-spec.ts (policy exists in the DB).
src/prisma/tenanted-models.ts — add the Prisma model name to STRICTLY_TENANTED_MODELS when tenantId is required (Class S ≡ this set; it feeds the misroute tripwire's delegate list). Guard: tenanted-models.drift.spec.ts parses schema.prisma and fails on any mismatch.
This chapter's raw-SQL table — one row per raw constraint the migration adds, plus the constraint name in test/db-constraints.e2e-spec.ts.
Tenant-reset coverage — classify the model in src/tenants/tenant-reset.coverage.ts (DELETE/PARTIAL/CASCADE), and for DELETE/PARTIAL add the deleteMany to resetTenantSetupData (src/tenants/tenants.queries.ts) in FK-safe order and the matching line in tools/reset-tenant.sql. Guard: tenant-reset.coverage.drift.spec.ts (Class S is required in the coverage map — this fires on every STRICTLY_TENANTED_MODELS addition).

Adjacent, often forgotten with the same feature (their guards live in the unit suite, not this chapter): new enum members reachable from a validated DTO need VALUE_LABELS entries (label-coverage.drift.spec.ts), new error params need a text-or-internal classification in ERROR_TEXT_PARAMS / ERROR_INTERNAL_PARAMS (error-messages.drift.spec.ts), new action verbs must join ACTION_NAMES (decorators.interfaces.ts — a type error, not a drift spec), and new FileUsage / FileOwnerType members break the exhaustive switches in src/files/files.service.ts.

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 (attendance day register 20260725155816): the two attendance partial uniques (attendance_daily_unique on (tenant_id, student_id, date) WHERE start_tick IS NULL, attendance_period_unique on the same columns WHERE start_tick IS NOT NULL) were dropped when start_tick became mandatory. With no NULL ticks the constraint is expressible declaratively — @@unique([tenantId, studentId, date, startTick], map: "attendance_cell_unique") — so it is migrate-diff-visible and carries no row here. Note the generated migration proposed ALTER INDEX attendance_period_unique RENAME TO attendance_cell_unique, which would have silently preserved the WHERE predicate; the hand-edit replaced it with DROP + CREATE.

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.

The C2 membership migration is also the canonical uniqueness-to-EXCLUDE precedent for effective-dated relations. This is the first attendance release, so there is no production attendance history or legacy membership chronology to reconstruct; the migration's defensive nullable-add/backfill/promote sequence exists for schema safety and test/dev rows, not as a product data-conversion promise.

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.