Migrations

Read this chapter before npx prisma migrate dev. Schema changes must preserve retained data, tenant isolation and the database invariants that Prisma cannot represent. Infrastructure describes deployment phases.

1. Migration ownership and preparation

prisma/schema.prisma describes the Prisma-visible model. prisma/migrations/ is the ordered executable history; raw constraints and policies also live there. prisma.config.ts supplies the administrative datasource, migration directory and seed command.

Before creating a migration:

1. Inspect the schema change and migration working-tree status.
2. If a related migration is uncommitted, ask whether to fold the change into it or create a separate migration.
3. Establish whether that migration has been applied to any retained database. Git status is not evidence of database state.
4. Confirm the actual target database and data-retention requirements before any command that can reset or alter it.
5. Choose a migration name describing the final change.

Treat committed/shared migrations as immutable. Fix them with a new migration. A local uncommitted migration can be folded only when all affected databases can safely be rebuilt and the user has authorized that rebuild. A tracked-but-uncommitted SQL change needs the same review as an untracked folder.

Folding is not simply appending SQL to an applied file: deploy will not apply the appended statements to databases that already recorded the migration. It also changes the stored checksum relationship. Prefer preserving the applied history and creating a forward correction when any retained environment has consumed it.

Folders use YYYYMMDDhhmmss_<slug>/. Do not add a second timestamp inside the slug.

2. Author and audit before applying

Use migrate dev only against a confirmed development database. When data safety requires SQL review before execution, generate with --create-only, inspect and adjust the SQL, then apply it through the authorized workflow. Generate the Prisma client explicitly; migration creation and client generation are separate operations.

A generated diff is a proposal. Read every statement, including indexes, defaults, FK delete actions, enum changes and data updates. Preserve raw SQL objects even when they are absent from schema.prisma.

Hazard checklist

Pattern	Risk	Safe design to consider
Required new column without a suitable default	Existing rows cannot satisfy it	Add nullable, populate from authoritative data, validate, then require
UNIQUE index/constraint	Existing duplicates abort the change	Inventory duplicates; define an approved resolution before adding uniqueness
DROP column/table	Data loss and incompatibility with old readers/writers	Expand/migrate/contract, or an explicitly coordinated stop and approved data removal
Incompatible type change	Invalid casts, table rewrites and lost meaning	Add a new representation, validate conversion and move consumers before removing the old one
Rename	Old runtime still uses the previous name; Prisma may infer drop/add	Preserve the storage name with mapping where possible, or coordinate compatible schema/runtime changes
Enum removal/replacement	Stored rows, defaults and consumers still use a value	Inventory and migrate values, then change the type and dependent defaults safely
New FK	Orphans or wrong tenant relationships	Validate actual parents and tenant identity; repair through an approved domain rule
Table recreation	Data and raw constraints can disappear	Inspect generated drop/create sequences and preserve data, IDs, policies and indexes explicitly
Extension creation	Target role may lack privilege	Confirm target capabilities; use idempotent extension creation where appropriate
New CHECK or index on existing data	Full scans, locks and incompatible rows	Inspect population, lock/statement budgets and supported validation/indexing strategy
Data backfill	Incorrect inference becomes permanent history	Use authoritative inputs, bound the affected set and validate postconditions
Privilege or trigger change	Broadened access, stale writers, inconsistent bindings	Review effective roles, function security, transaction boundaries and writer compatibility

A constant USING expression can overwrite every value. Treat it as deliberate data deletion requiring explicit approval, never as a harmless cast. Development or staging labels alone do not make a database disposable.

For required fields without trustworthy existing values, do not invent placeholder identity or historical facts merely to satisfy NOT NULL. Consider nullable storage with an explicit application rule instead.

When a hazard applies, prepare the concrete safer SQL and explain the remaining data/deployment decision before asking for approval. Do not describe an unaudited or unexecuted migration as verified.

Transactions and compatibility

PostgreSQL migration atomicity depends on the actual statements and explicit transaction boundaries. A failed migration, a previously successful migration, seed, role reconciliation and application startup are separate units. Do not assume a deployment rollback restores the schema.

Use explicit transactions when the operation requires atomic assertions, backfills and privilege changes and all statements permit it. Bound locks for retained data. A valid NOT VALID/later validation strategy is not permission to leave an invariant unenforced indefinitely.

A schema change incompatible with old writers needs coordinated maintenance: stop those replicas/jobs, prevent restarts, apply the change, and retain the stop through later phase failures. The recovery choice is a compatible forward correction or a reviewed restore, not an automatic old-binary restart.

3. Recovery

For P3009 or another failed deploy:

1. Read _prisma_migrations, including the recorded error and completion state.
2. Inspect actual schema and affected data. Determine which statements committed.
3. Compare that state with the exact migration file and intended final state.
4. Choose an approved rollback of partial work or a forward completion.
5. Only then repair migration bookkeeping through the supported Prisma recovery command for that state.

Do not blindly delete ledger rows, manually rewrite checksums or replay partially applied SQL. A forward repair must be safe for every known starting state; IF NOT EXISTS alone does not prove an existing object's definition is correct. Inspect constraints, index predicates, privileges and backfill postconditions.

A database reset is a destructive operation. Confirm the resolved target, ownership and retention requirements; require explicit authorization. Production/customer data is not a disposable migration test fixture.

Keep credentials out of command arguments, logs and tracked files. Hosted internal connection hosts may be unreachable from a developer machine; obtain an appropriate authorized connection without weakening database privileges.

4. Schema constraints that live only in raw SQL

prisma migrate diff cannot establish the complete database contract. Check migration SQL and the relevant database tests for raw indexes, CHECKs, triggers, exclusion constraints and RLS policies.

The tables below explain the durable invariants. They are organized by behavior, not migration chronology. test/db-constraints.e2e-spec.ts contains the common database assertions; domain-specific tests cover additional behavior. When changing a raw invariant, update its meaningful assertion rather than relying only on object-name existence.

Uniqueness and shape

Object(s)	Invariant
uniq_academic_year_active_per_tenant	At most one ACTIVE year per tenant
curriculum_subjects_container_name_key, option_blocks_container_name_key	Names unique within their actual nullable containment coordinates
subject_levels_platform_name_key, roles_platform_key_key, evaluation_scales_platform_name_key, room_types_platform_name_key	Global preset names/keys unique when tenant is null
timetables_tenant_ay_published_unique	At most one PUBLISHED timetable per tenant/year
timetables_open_revision_unique	At most one DRAFT revision per source; retained ARCHIVED versions do not consume that slot
invitation_deliveries_one_active	At most one QUEUED/SENDING attempt per invitation
mailing_groups_shared_name_key, mailing_groups_private_name_key	Separate tenant-shared and per-owner name namespaces
scheduled_break_audiences_selector_key, scheduled_activity_audiences_selector_key	No duplicate selector within its parent, including nullable selector coordinates
day_bounds_scope_alldays_key, day_bounds_scope_weekday_key	One override for each all-days or weekday scope
report_runs_one_running_tenant	At most one RUNNING report job per tenant

Nullable uniqueness needs special care. A composite unique with nullable columns may not represent the intended identity; preserve partial predicates and expression terms. Renaming an index does not change its predicate.

CHECK family	Invariant
curriculum_subjects_mandatory_xor_block_chk, curriculum_subjects_block_track_chk, curriculum_subjects_unit_shape_chk	Mandatory/block shape, track inheritance and unit containment
curriculum_subject_hours_window_chk, curriculum_rules_scope_ref_chk	Complete teaching windows and rule-reference parity
scheduled_lessons_anchor_xor	Exactly one subject-group or combined-class anchor
attendance_day_slots_anchor_xor	Exactly one lesson/activity anchor, with an agreeing anchor key
scheduled_breaks_axis_chk, scheduled_activities_axis_chk, day_bounds_one_of_chk, day_bounds_range_chk	Bounded time-grid coordinates and unambiguous scope
scheduled_break_audiences_one_of_chk, scheduled_activity_audiences_kind_ref_chk	Selector kind/reference shape
timetables_effective_shape_chk, timetables_effective_order_chk	Status matches interval shape; empty intervals are legal
disciplinary_notes_shape_coherent	A note is either active (text present, no withdrawal stamps) or a withdrawn tombstone (no text, no pending arm, stamps present); an INTERNAL note is never armed; class provenance columns are all set or all null
disciplinary_note_recipient_state_coherent	Each recipient's delivery, acknowledgement and withdrawal stamps agree with its lifecycle state; WITHDRAWN leaves the delivery stamps free as evidence of whether the family had seen the note
assignment_details_nonblank, assignment_due_on_or_after_assigned, course_day_lesson_content_text_nonblank	Valid retained register content and dates
assignment_attachment_payload_shape	FILE and LINK wrappers have exactly their permitted fields
scheduled_lesson_assistance_offsets_chk, attendance_assistance_teacher_subset_chk	Bounded assistance offsets and eligible-teacher subset evidence
attendance_records_course_attributions_array	Stored attribution evidence is null or a JSON array
saved_reports_revision_check, report_runs_counts_check, report_runs_state_check	Positive configuration revisions, coherent job counts/state/leases/publication
report_run_chunks_counts_check, report_run_chunks_manifest_check, report_run_chunks_rows_check	Chunk coordinates/counts, manifest placement and JSON row count

communication_recipients_notification_pending_idx is a non-unique partial recovery index. Its predicate requires a recipient-user snapshot, no notification claim, and email status other than PENDING. Preserve the predicate as well as the indexed columns.

The attendance attribution GIN index is Prisma-visible with jsonb_path_ops; the array CHECK is not. Keep representable indexes in the Prisma schema so later diffs do not remove them.

Temporal non-overlap

These exclusion constraints use half-open daterange(valid_from, valid_until, '[)'):

Constraint	Non-overlapping identity
student_placements_concurrent_excl	Student
homeroom_assignments_concurrent_excl	Student
subject_group_assignments_group_concurrent_excl	Student and subject group
subject_group_assignments_subject_concurrent_excl	Student, curriculum subject and academic year
student_curriculum_selections_concurrent_excl	Student
subject_group_teachers_concurrent_excl	Subject group and teacher
combined_class_memberships_concurrent_excl	Subject group

btree_gist supplies equality operators for these GiST constraints. They protect overlapping intervals, not lost updates; application compare-and-swap and graph locks remain necessary.

An end equal to the next start is legal. Empty day intervals retain cancelled plans, and same-day boundary ticks can retain a lived micro-episode. Do not replace <= with < or restore a lifetime pair-unique that would forbid leave/rejoin history.

The six valid-time tables have <table>_tick_range and <table>_micro_episode CHECKs. Ticks are nullable whole-day boundaries or 0–287 grid positions. The TypeScript mirror is src/common/temporal/interval.ts; tick-field schema coverage is in src/common/temporal/tick-columns.drift.spec.ts. Tick refinement does not replace the day-level exclusion constraint.

Identity and immutable evidence

Global login identity checks enforce canonical nonempty email, required credential hash and a nonnegative credential version. users_legacy_password_hash_null prevents tenant-local credential copies.

Person binding triggers enforce the owning User's tenant and established profile type. The established type cannot change or clear. A global LoginIdentity can serve different school-local types; do not restore global-type uniqueness. These trigger functions use narrowly scoped SECURITY DEFINER access, a fixed search path and revoked direct EXECUTE.

The grade ledger protects append-only evidence through:

• Revision CHECKs for positive sequence, required change reason, scalar/level parity and range, teacher snapshot alignment, completeness shape and criterion definitions.
• grade_criterion_mark_revisions_ordinal_nonnegative.
• grade_revision_parent_consistency and grade_criterion_revision_tenant_consistency for cross-row identity.
• grade_entry_revisions_immutable and grade_criterion_mark_revisions_immutable against in-place update/delete.
• grade_entries_head_consistency and grade_revisions_head_consistency as deferred head checks.
• grade_entry_stable_identity for root identity.

The controlled maintenance GUC is not an HTTP mutation escape hatch. See grades for ledger semantics.

5. New tenant-bearing model checklist

Classify by ownership, using src/prisma/rls-coverage.ts:

Class	Ownership	Policy
S	Required tenantId	Tenant equals transaction GUC
M	Nullable tenant plus shared global rows	Global row or matching tenant
C	Ownership through a parent	Explicit parent/ancestor predicate
X	Genuinely global/auth-private	Documented exclusion; review table privileges separately

For S/M/C, the migration must ENABLE and FORCE RLS and add matching USING/WITH CHECK policy predicates. The pooled-safe GUC expression is:

(SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid

Do not maintain a second list of RLS tables in this chapter. The registry and its schema drift test own that inventory. Global auth-private tables deny runtime/PUBLIC table privileges instead of receiving tenant RLS.

For each new model:

1. Add the appropriate RLS classification and migration policy/privileges. A migration's GRANT … TO app_user needs the role to exist, so every bootstrap path (docker:reset, db:setup, the e2e global setup, CI and the Railway pre-deploy) runs db:provision-app-role before migrating, and again afterwards for the grants on new tables.
2. If tenantId is required, add its Prisma name to STRICTLY_TENANTED_MODELS in src/prisma/tenanted-models.ts.
3. Classify each strictly-tenanted model in src/tenants/tenant-reset.coverage.ts as DELETE, PARTIAL or CASCADE.
4. Add explicit reset deletion in FK-safe order to resetTenantSetupData and tools/reset-tenant.sql where needed. Verify the actual FK chain before claiming CASCADE.
5. For a Class-C child, verify parent cleanup and isolation; it does not belong in the required-tenant registry merely because it has tenant data.
6. Document meaningful raw invariants here and add relevant database assertions, including predicates, FK actions or trigger behavior when object existence is insufficient.

Related exhaustiveness points: DTO-reachable enums need localized value labels; error parameters need text/internal classification; action verbs need ACTION_NAMES; new file usage/owner kinds need the files service's exhaustive dispatch.

6. Evidence and documentation

Keep numbered chapters about lasting rules and present contracts. A feature's migration audit, environment inventory, rehearsal output and rollout status belong in its separate authorized change artifacts. Do not append dated storage-audit sections to this chapter.

A source review can establish what SQL is written. Only inspection or authorized execution against a specific database establishes what is applied there. State that distinction when reporting work.