Plan 20 Data Model Part 2

Part 2 of 2 of 20 — Data Model · Previous

20. Frozen group graph (W-08)

Migrations 38–39 add snapshot-owned group context without altering historical question content or assigning old versions a group interpretation. The existing frozen question table retains grading keys, response identities and section-wide ordinals. A separate membership table binds those frozen questions to a group, with member order and option-order policy. This avoids nullable group ownership columns on the populated snapshot table and keeps the legacy read path intact.

Migration 38 builds (id,test_version_section_id) uniquely and concurrently on test_version_questions. Migration 39 attaches that index and creates the graph. Composite foreign keys require every group member and ordered unit to belong to the same frozen section; material gap targets must be members of the same frozen group. Stable blank-gap targets reference the existing frozen blank identity pair. Question-type compatibility, complete unit/member coverage and AST binding bijections remain domain/repository invariants checked before freezing.

Table	Contract and reverse-reference access
test_version_groups	Snapshot section owns the root; title and semantic instructions; section index; no live source-group FK
test_version_group_members	Same-section group/question links; unique question membership and ordered members; option-order policy
test_version_units	Ordered standalone/group union; same-section links; unique unit position, question and group
test_version_group_stimuli	Immutable ordered material content; group identity pair for scoped child links
test_version_group_gap_bindings	Scoped stable gaps, same-group question targets and frozen blank-gap targets; unique response targets and reverse question index
test_version_group_recordings	Independent recording identity, immutable policy/transcript and protected audio kind; reverse asset index
test_version_group_assets	AST reference mirror, same-group recording and asset linkage; reverse media and recording indexes

Deleting an unused version cascades through its owned graph. Frozen blank targets use deferred NO ACTION to permit that whole-version cleanup while rejecting partial dangling targets. Physical media deletion is restricted by frozen graph references; soft deletion and the media library must include them under the same asset locks as publication. New snapshot graph tables revoke UPDATE from the app role; correcting published content creates another version. Audit and attempt event grants remain untouched.

Down is for disposable/pre-enablement data only and refuses any frozen group or ordered-unit rows. The graph Down restores the index owned by migration 38 so an intermediate down/up has identical schema. Once group snapshots exist, keep these migrations and compatible readers even when authoring is disabled. No old version, attempt or shuffle seed is backfilled.

Publication holds the parent test lock, then shared locks on all context roots and referenced question rows in stable ID order before loading prompts and child keys. This prevents a concurrent bank edit from mixing parent text from one revision with options/blanks from another. Group writes already require that parent lock. After validation, every member/material asset is locked once in stable ID order across the full test; section-local lock ordering is insufficient when tests share assets in different authored orders.

The flattened frozen question ordinals remain the response/grading order, while test_version_units preserves grouping and test_version_group_members preserves member policy/order. Material and recording rows receive fresh UUIDv7 identities; gap targets are remapped to the frozen questions, with their stable prompt gap IDs retained. Version listening counts count each question once if it has own audio or any shared recording in its group. Frozen group references participate in media soft deletion and batched library usage lookups. W-09b extends student reachability only through protected material bindings on a version they have an attempt on; preview/attempt safe readers never select keys or transcripts.

W-08b reads the frozen graph under its parent version lock and validates content, member ordering, grading inputs and AST/relational asset mirrors. Restoration locks the target test/version, clears its old owned draft graph, locks all snapshot assets in stable order and creates independent copies in one transaction. New gap identities are remapped through material and question documents together. Standalone snapshot questions remain ordinary independent bank copies, matching the previous restore behavior. Repeated restores delete replaced owned children; failed restores roll back that cleanup as well as every new row.

Duplication holds the source test lock and takes referenced question locks in stable order before copying; it invokes the existing draft-use guard before every standalone reference insertion. These locks are exclusive from the start to avoid shared-to-exclusive lock upgrades between concurrent copies. All copied group assets are locked together before insertion. Source group graphs receive new editable IDs; standalone bank references keep their original duplication meaning. Whole-test deletion clears owned context only after version reference checks, with audit history preserved. Grouped draft/preview and learner read paths must still be integrated before authoring is enabled.

21. Shared recording accounting (W-09c)

00040_create_group_audio_plays.sql keeps the counter identity separate from the immutable asset. attempt_group_audio_plays has primary key (attempt_id, recording_id), a positive integer count and last-play timestamp. The attempt owns the count through a cascading FK; the frozen recording is protected by RESTRICT. A recording-first index supports reverse references and teacher aggregation.

attempt_group_audio_receipts has primary key (attempt_id,play_id), the recording, reporting session and received timestamp. Its composite FK names the corresponding counter and cascades with that owned counter. An (attempt_id,recording_id) index supports that reverse reference. The application can insert/read receipts but cannot update/delete them; existing audit/event privileges are untouched.

The shared-play transaction first takes the same attempt lock as autosave, resume and submission, checking owner, writable session, status and deadline. A relational join verifies the requested recording is bound to a material on that attempt's version. It then looks up the gesture receipt. A matching retry reads the current counter without another event; a different recording is refused. A new gesture increments via UPSERT, inserts its receipt and appends one audio_play event with group/recording/gesture identity and count/limit metadata. Server events retain a NULL client sequence, so they never collide with browser event sequences.

READ COMMITTED is sufficient: the attempt row serializes shared-play writers with each other and with session/closure changes. The whole transaction rolls back if any of its three writes fails. Counters may exceed the frozen policy; monitoring reports that excess and never changes grading. No historical ledger is backfilled. Down restores the prior schema only while the new ledger is empty; populated listening evidence must survive disabling the feature.

22. Frozen delivery algorithm (W-09d)

00041_version_test_delivery.sql adds test_versions.delivery_version: non-null text defaulting to section_v1, checked against section_v1 and group_v1. An enum would make future algorithm expansion/rollback harder; no index is needed because readers resolve by the existing version primary key. Legacy snapshots keep their marker, IDs and seeds. Prerelease snapshots with frozen group rows are classified by an indexed section/group existence join. This is metadata only; no questions, content, options, attempts or answers are rewritten.

The publisher explicitly writes group_v1 for new versions. Its standalone ordering is identical to the historical section algorithm. Attempt and result readers use attempts.test_version_id, never the test's selected default. The marker remains immutable through the same API boundary as all snapshot fields. Down refuses while any group-aware snapshot exists; otherwise it drops only the new column. Tests exercise both reversible empty schema and refusal with data.

23. Private import sources (W-10a)

Migration: 00042_create_word_import_sources.sql. The only PG18-specific construct introduced is built-in uuidv7(). The Neon schema/isolation guidance was applied: text checks for evolving states, bigint revisions/byte counts, timestamptz, indexed foreign keys and explicit ON DELETE RESTRICT. No partitioning, learner-media link or audit privilege change.

• word_imports holds actor-scoped create idempotency, title, lifecycle, optimistic revision and nullable current source revision (wire zero before the first upload). Its composite head FK points to a set owned by that same import. Recency/status indexes serve history; immutable-unaccent trigram indexes serve Vietnamese title and source-filename search. The creator/request unique also indexes the user FK.
• word_import_sources is the durable upload journal and immutable original metadata. Its import/upload unique pins replay identity; SHA-256 is not globally unique. Every storage key has a row before Put. Readiness and completion revision agree via CHECK, and the completion FK cannot name another import's set. Actor/import/revision and pending-age indexes support quota, history and future cleanup inventory.
• word_import_source_sets and word_import_source_set_items preserve every completed source revision. The role primary key allows at most one exam and one answer key; a composite source FK pins import, role and ready=true, rejecting pending bytes and cross-import sources even if application validation is bypassed. The app role cannot UPDATE/DELETE these histories, rewrite original metadata, or delete originals; only source readiness/completion columns receive UPDATE. Reference-safe retention will need its own explicitly reviewed maintenance operation.

Create/reserve acquire advisory transaction lock (73819,10) before any parent row lock. This serializes global and actor quota reservations across API processes. Completion locks the import row and checks status/revision before atomically attaching one new source set and auditing the actor. Exact completed replay succeeds without advancing the head. A stale upload retains its tracked object reservation and quota. No storage call or package inspection runs inside a transaction. History/get reads use short repeatable-read transactions so a head and its source list agree.

Local Docker verification uses the migration owner for fixture setup and the quizzivy_app role for intake, plus a separate private MinIO bucket. Up/down/up is verified on a disposable database. Automatic source retention remains undecided; this migration does not infer it from the integrity-event retention policy.

R4 pasted-text source extension (T-R4.55)

00090_allow_text_import_sources.sql extends the existing format CHECK to docx, doc, pdf and text. Its Down restores the file-only CHECK NOT VALID, so existing text rows remain retained while new text writes are refused. 00091_add_word_import_sources_characters.sql adds nullable integer characters with named range 1–100000 and format constraints: it is non-null exactly for text sources and null for files. Existing file inserts using the old column list remain valid. No owner, grant, index, source-set or append-only audit policy changes.

Intake writes the NFC Unicode code-point count; bytes, hash and private-original metadata keep their existing meaning. Migration 91 Down drops the character column. Empty-purpose 90/91 up/down/up is reversible. After retained text loses its counts through 91 Down, 91 Up must refuse atomically: bytes and hash cannot reconstruct truthful character counts. There is no invented count, backfill or retained-row repair. A separate disposable purpose verifies this refusal and is ordinarily disposed only under its exact lifecycle authorization.

24. Durable import runs (W-11a)

Migration: 00043_create_word_import_runs.sql. PG18 SELECT … FOR UPDATE … SKIP LOCKED is used only for queue allocation. Transaction advisory locks serialize capacity accounting; durable state is in tables, never the lock itself.

word_import_runs references an immutable source set belonging to its import. Request identity pins the source revision, expected import revision, pipeline and attempt budget. A partial unique index permits one active run per import; queue and expiry indexes support allocation/recovery, and actor/source indexes cover FKs. CHECKs couple lease/worker presence, terminal completion, ready stage and private result presence to lifecycle state. Results are bounded JSON objects, not arbitrary SQL or learner payloads. Application column grants protect request/source identity; a terminal-state trigger prevents later result/history mutation.

word_import_run_events references the run and its same import through a composite FK. It contains ordered operational identifiers/codes only. The app role can insert and read but cannot update or delete events. Actor and run indexes cover lookups and references. Existing audit_log/attempt_events grants are unchanged.

Lock order is advisory (73819,11) when allocating/renewing capacity, then parent import, then run. Other transitions start at the parent and never acquire the queue advisory lock later. Eligibility is re-read after locking the parent; snapshots from joined candidate selection do not authorize a stale lease. Processing and storage calls run after commit. Cancellation increments the fence and competes with success on the same parent, preventing a cancelled worker from completing the import.

There are at most 50 requests per import and ten attempts per request. Stage writes are monotonic within an attempt and repeated stages are no-ops, bounding run-event history without truncating it. Down removes events before runs and their guard function. Docker up/down/up checks run on a disposable database.

25. Private stage artifacts (W-13b)

Migration: 00044_create_word_import_artifacts.sql. word_import_artifact_sets pins one bounded output manifest to its producing run/claim and immutable source set item. Composite FKs enforce same-import run/source revision, source role and source identity. Component identity includes processor configuration and upstream artifact lineage; reuse additionally requires the same pipeline version.

word_import_artifacts journals each private object before storage. Generated keys include import/set/file identity, and every takeover receives new writable keys. Unique ordinal/name constraints prevent ambiguous manifests. The manifest has at most 512 files, 64 MiB per file and 256 MiB total; compact application metadata is limited to 60 KiB (128 KiB JSONB allowance for representation spacing). These are internal development bounds, not the approved production envelope.

Reservations take quota advisory lock (73819,10), then parent import and run. Pending and ready set bytes count towards separate actor/global artifact limits; sets per import are also bounded. Storage and checksum IO happen after commit. Every acknowledgement/completion requires the live source/worker/fencing claim. Completed set reads need no mutable snapshot because completed files and metadata cannot change. Only complete sets are reusable, including across explicit retries; old incomplete sets remain tracked and charged. No retention decision is inferred.

Column grants prevent metadata rewrites and deletion. Triggers reject completed row mutation, file additions to completed sets, out-of-plan ordinals and incomplete set completion. Parent/run/source and lookup indexes support reverse references, reuse and pending inventory. Existing append-only audit/event permissions remain unchanged. Down drops owned files before sets and removes the new FK target keys.

Object persistence uses conditional S3 creation with a SHA-256 checksum. Exact lost-response replay verifies the stored checksum, size and MIME type using HeadObject. Incompatible storage implementations fail closed; production provider compatibility is a W-21 gate. Trusted processor reads stage on disk and verify size/digest before parsing. A stage manifest grants no learner media authorization.

26. Review drafts and commits (W-18, W-20)

Migrations: 00045_create_word_import_drafts.sql, 00046_create_word_import_commits.sql, 00047_add_word_import_run_profile.sql, 00048_allow_legacy_word_sources.sql, 00089_add_word_import_draft_counts.sql.

word_import_drafts holds exactly one review copy per import (the primary key is the import). revision is the optimistic-concurrency token for teacher saves: an update names the revision it read and matches zero rows otherwise. run_id is the run whose extraction the draft's source references point into, so the source view and the draft always describe the same evidence. A run that completes while edited_by is null replaces the body and bumps the revision; once a teacher has saved, a newer machine draft is kept in candidate/candidate_run_id and never overwrites the edits. The body is bounded to 8 MiB as JSONB. The application role may update drafts but not delete them; draft saves are not audited individually because autosave would flood the log.

00089 adds nullable open_action_count integer and open_confirm_count integer, each checked nonnegative, without a default or backfill. They store domain.Assess(current body).Summary (Blocking and NeedsDecision) in the same write as the body. A pending candidate preserves the current counters; adoption computes them from the locked candidate. NULL means a historical current draft has not been assessed by this binary, not zero. Valid existing draft shapes can exceed32767 findings, so SMALLINT would impose an unrelated new authoring limit; the existing8MiB body limit remains unchanged. History reads these counters with its facets and rows in one repeatable-read snapshot. Down drops only the two reporting columns.

word_import_commits is append-only (no UPDATE or DELETE for the application role) and has one row per import. It records the request identity, the committed draft revision and its SHA-256 digest. The commit runs in one transaction with the test, section, bank question and group writes it describes, after locking the import row FOR UPDATE; a second concurrent commit waits on that lock, finds the import already committed and rolls back its own writes, and a replay with the same request identity returns the recorded test. test_id is ON DELETE SET NULL so deleting the created test later is not blocked by import history.

word_import_runs.profile stores the recognition profile (for example the paper to read from a multi-paper answer key) so a replayed schedule request must match it. Adding the column with a constant default is metadata-only on PG18. The source format check now admits doc; its Down restores the docx-only check NOT VALID so rows written meanwhile do not block the rollback.

00049 replaces the bank group index on id DESC with (updated_at DESC, id DESC) under the same predicate, which is the order the listing actually uses.

Retention (D-08), in 00050 and 00051, works in three steps. The API runs the sweep daily with the application role, so nothing waits for a manual job:

• Close idle imports. An import left in awaiting_sources, failed or needs_review with updated_at older than 60 days becomes cancelled with closed_idle. Reservations, draft saves, adoptions and commits all touch updated_at under the row lock, so the idle test needs no join.
• Remove the files. A terminal import's objects are removed 30 days after word_import_commits.committed_at, 7 days after cancellation (updated_at), or at once when closed_idle is set. That covers every source and artifact storage_key the import owns.
• Delete the draft, which holds the exam content, and set files_removed_at. This happens only after the objects are gone.

What stays:

• The source and artifact rows stay. They hold no content, and the history shows their filenames.
• runs, run_events, commits and audit_log stay untouched and append-only.

Enforcement:

• word_imports_files_removed_terminal allows files_removed_at only on committed or cancelled imports, which can never be processed again, so stage reuse never meets a missing object.
• word_imports_closed_idle_cancelled allows closed_idle only on cancelled imports. The column has a constant default, so adding it is metadata-only on PG18.
• The partial index word_imports_retention (status, updated_at, id) WHERE files_removed_at IS NULL serves both queries. Both name the predicate and filter by status, and the removal sweep's keyset order is (updated_at, id).
• 00051 grants DELETE on word_import_drafts only. No other table gains a delete grant.

00052 widens the source format check to docx, doc and pdf. Like 00048, its Down restores the narrower check NOT VALID, so PDF rows written meanwhile do not block the rollback.

27. Maintenance windows (T-R1.12)

00053_create_maintenance_windows.sql adds app.maintenance_windows (id uuid PK DEFAULT uuidv7(), starts_at, ends_at, created_at, created_by text DEFAULT current_user, cancelled_at).

• ends_at > starts_at and ends_at - starts_at <= interval '12 hours' are CHECKs.
• EXCLUDE USING gist (tstzrange(starts_at, ends_at) WITH &&) WHERE (cancelled_at IS NULL) keeps live windows from overlapping. It excludes on the range alone, so btree_gist is not needed; [) bounds let one window start as another ends. The same gist index serves the start guard's overlap query.
• created_by is the database role that scheduled the window. No app user does.
• The file revokes INSERT, UPDATE and DELETE from quizzivy_app, narrowing 00009's default privileges: the API reads windows (T-R1.13), and only the operator's role writes them (D-20). privileges_test.go pins it.

window-schedule writes in one statement of data-modifying CTEs, under the advisory lock below: the window, the moved attempts.deadline_at and assignments.closes_at (PG18 RETURNING old.…, new.…, so each audit row carries only the column that moved), and their audit rows with a NULL actor.

28. Advisory-lock registry

Every transaction advisory lock this codebase takes is in one of two namespaces. pg_advisory_xact_lock(73819, <key>) holds the fixed keys of the table below: add a key here before it is used. 73820 holds one lock per media owner, described under the table.

Key	Holder	Serialises
10	Word imports (W-10a, W-13b)	Upload quota reservation
11	Word imports (W-11a)	Run capacity allocation and renewal
40	Maintenance windows (T-R1.12, T-R1.13, T-R4.12b)	window-schedule, window-cancel and window-end take it exclusively; the attempt start guard takes it shared, so no attempt starts between a window's scheduling and its extensions. extendAssignment, updateAssignment, reopenAssignment, setStudentOverrides and deleteStudentOverride take it shared too, before they lock the assignment row (schedule.LockWindows), so none of them runs between a window's scheduling and its extensions, and none can wait on a row that window-schedule holds while it waits on theirs
41	Legacy join-code rotation (T-R4.22)	Each class's rotation across API machines
42	Reserved: R7's notification scheduler	Confirmed by R7

Namespace 73820: one media library's quota (T-R4.17a). pg_advisory_xact_lock(73820, hashtext(<owner id>::uuid::text)) serialises the writers of one owner's media library. media/repositories.RequireQuota takes it first, then sums the owner's library bytes (§6: not deleted, not replaced), and the insert follows in the same transaction, so two uploads that each fit alone cannot both land. The second key is a hash of the owner, which is why it has a namespace of its own: in 73819 a hash could land on one of the fixed keys. Two owners whose hashes collide wait for each other and nothing else. The uuid is cast before it is hashed, so the same owner written in another letter case takes the same lock. T-R4.17b's replace takes this lock for the old file's owner, with the old file left out of the sum. An upload holds no other lock when it takes this one, and its only row is the one it inserts.

29. Roles and permissions (T-R2.1)

00054_create_roles_and_permissions.sql stores the access model of plan 70 §4.1 as data. Its rows are reference data the app cannot run without, so the file writes them (D-21).

• app.permissions (key text PK, group_key, ordinal, in_matrix) holds the catalogue: the deck's eighteen matrix rows in the matrix's order, with "Create student accounts" second in People (DG-03), then the four hidden keys (scope.all, system.api_reference, system.data_export, system.leads) with in_matrix = false, ordinals 4–7 of system. key is checked against ^[a-z]+(\.[a-z_]+)+$, and (group_key, ordinal) is unique. core/tests/catalogue_test.go asserts that the table, access.All() and the contract's PermissionKey enum are the same list in the same order.
• app.roles (id uuid PK DEFAULT uuidv7(), builtin_key UNIQUE, name, description, icon, color, copied_from, revision, created_by, created_at, updated_at). builtin_key is one of admin, teacher, assistant, student, or NULL for a custom role, and roles_builtin_key_immutable refuses any change to it. icon and color are checked against the Edit role dialog's twelve icons and seven colours. Names are unique case-insensitively (roles_name_lower_key). revision starts at 1 and moves with every grant change, so a cache keyed by (id, revision) is never stale. copied_from and created_by are not indexed: the table holds a handful of rows.
• app.role_permissions (role_id, permission_key) is the grant table, PK on the pair, with role_permissions_permission_key_idx for the foreign key to permissions (ON DELETE RESTRICT). A role's grants go with it (ON DELETE CASCADE), although nothing may delete a role (DG-52).
• The built-in rows: Quản trị viên (shield, dark), Giáo viên (graduation-cap, lime), Trợ giảng (hand-helping, blue), Học viên (user, gray), with the deck's descriptions in Vietnamese (D2, D11). The Teacher holds 13 keys, the Assistant 6 and the Student learning.take_tests. The Admin stores nothing: it is the wildcard, with "Take tests" off (DG-51).
• Triggers on role_permissions, all row-level:
  • role_permissions_guard (BEFORE INSERT OR UPDATE) refuses a hidden key for any role, and any key but learning.take_tests for the Admin.
  • role_permissions_keep_student_take_tests (BEFORE DELETE OR UPDATE) keeps learning.take_tests on the built-in Student.
  • role_permissions_bump_revision (AFTER INSERT OR UPDATE OR DELETE) moves roles.revision for every role a grant leaves or joins. It runs as the invoker; R5's role commands get UPDATE on revision with their writes. UPDATE is covered although no role may update a grant: an update is a delete and an insert in one statement.
• All three tables are read-only for quizzivy_app, narrowing 00009's default privileges; R5 (T-R5.8) grants the role commands' writes, never DELETE on roles and never any write on permissions.

00055_create_student_like_roles_view.sql adds app.student_like_roles (id), the one definition of a strict student target (plan 70 §4.3, D-24): the built-in Student, or a custom role holding nothing but learning.take_tests. Built-in roles other than Student are excluded by key, because the Admin stores only its "Take tests" cell and would otherwise match. A single-table view is automatically updatable, so the file revokes INSERT, UPDATE and DELETE on it from quizzivy_app. access.IsStudentLike is the same predicate in Go.

30. A role per user, the session epoch and the last Admin (T-R2.2)

The expand half of moving users from users.role to users.role_id (plan 70 §3, D-25). 00105 (73-r3.md T-R3.1, §42) validated the constraint and dropped the sync trigger, users.role, users_role_active_idx and app.user_role.

• 00056_add_users_role_id.sql adds role_id uuid REFERENCES app.roles ON DELETE RESTRICT, nullable, and backfills it: admin → the Admin role, student → the Student role. users_set_updated_at is disabled for the backfill, so updated_at keeps meaning "the user changed". The file raises if a row is still NULL, then adds users_role_id_not_null NOT NULL role_id NOT VALID (PG18), which holds for every new and updated row.
• users_sync_legacy_role (BEFORE INSERT OR UPDATE OF role, role_id) keeps the two columns in step while v0.7.0 and v0.8.0 machines overlap:
  • an insert without role_id is the old binary's and takes it from role;
  • an insert or update with role_id derives role: student for a role in student_like_roles, admin for any other, over the column default;
  • an update of role alone re-derives role_id. The new binary inserts role_id (the built-in Student, found by builtin_key in the statement) and reads the legacy value through student_like_roles.
• 00057_index_users_role_id.sql builds users_role_id_active_idx ON app.users (role_id) WHERE disabled_at IS NULL CONCURRENTLY, in a NO TRANSACTION file, because the table is populated.
• 00058_add_users_session_epoch.sql adds session_epoch integer NOT NULL DEFAULT 0 with users_session_epoch_check (session_epoch >= 0). The constant default is metadata-only. A command that ends a user's access bumps it; in R2, a disable and a staff password reset.
• 00059_add_users_last_admin_guard.sql adds users_last_admin, AFTER UPDATE OR DELETE, row-level, with no column list. When the old row was an active Admin and the new one is not, it takes FOR NO KEY UPDATE on the Admin row of app.roles, then raises check_violation with constraint users_last_admin if no active Admin remains. The count is its own statement, so under READ COMMITTED it sees a concurrent departure the lock waited for: of two transactions disabling the only two Admins, exactly one commits. Any other change returns before locking, so disabling a student takes no lock on roles.
  • The guarantee holds under READ COMMITTED, which every writer in the app uses, and under SERIALIZABLE. Two REPEATABLE READ transactions count on their own snapshots and could both pass; only a hand-written transaction runs that way. Locking the remaining Admins FOR SHARE would close it, but two departures that leave a third Admin would then deadlock, and that is the likelier case.
  • FOR NO KEY UPDATE, not FOR UPDATE: it serialises the guard against itself without conflicting with the FOR KEY SHARE a foreign-key check on users.role_id takes, so promoting one user to Admin does not hold up another Admin's departure (and two transfers cannot deadlock).
  • No column list: the v0.7.0 binary demotes by writing role, and a column list matches the columns an UPDATE names, not those a BEFORE trigger sets. Naming role would also have made 00105's DROP COLUMN role depend on the trigger.
• The schema's first SECURITY DEFINER function (D-26). The row lock needs UPDATE privilege on app.roles, which 00054 revokes from quizzivy_app. Running as the invoker, R5's demote and disable would fail with 42501 instead of the guard. It is owned by quizzivy_migrate and hardened as the PostgreSQL docs require for definer functions:
  • search_path = pg_catalog, app, pg_temp. An unlisted pg_temp is searched first for relations and types, so a session that can create temporary objects could otherwise plant a pg_temp.uuid domain whose CHECK runs as the owner.
  • Every type and relation inside is schema-qualified.
  • EXECUTE is revoked from PUBLIC, so no role can attach it to a table of its own. A trigger fires regardless of EXECUTE.

platform/db/tests/last_admin_test.go (TestTheLastActiveAdminCannotLeave) proves the guard on a scratch database migrated to head. As the app role it checks that each way out is refused and maps to ErrLastAdmin, that a student change takes no lock on the Admin role, the two-transaction race, that a foreign-key check on the Admin role does not hold the guard up, and the definer's attributes. It carries the integration tag and runs where TEST_DESTRUCTIVE=1 is set, as CI does. It needs a database of its own because the guard counts every active Admin in the database, so "the last one" is not a state a shared database can hold while other packages create Admins.

The v0.7.0 expand test that first held these checks (expand_r2_test.go) also checked the backfill and both insert paths on the way up from v0.7.0's schema. It was retired after v0.10.0 shipped 00105 and 00106: nothing it ran against exists any more.

31. Ownership (T-R2.9)

The expand half of per-teacher ownership (plan 70 §3, D-22, D-23). 00106 (73-r3.md T-R3.2, §43) validated every constraint below and dropped the fill triggers and their functions.

• 00060–00063 add owner_id uuid to tests, questions, question_groups and media_assets, beside the provenance column each already has. Each file:

  • backfills owner_id from created_by (uploaded_by for media) with the table's *_set_updated_at trigger disabled, so no updated_at moves. An open builder tab sends a test's updated_at back as expectedUpdatedAt, and a moved value would fail its next autosave with STALE_WRITE during the deploy. media_assets has no updated_at;
  • raises if a row is still NULL;
  • adds <table>_owner_id_not_null NOT NULL owner_id NOT VALID (PG18);
  • adds a BEFORE INSERT trigger, <table>_fill_owner, that sets owner_id from the provenance column when the v0.7.0 binary inserts without it. 00060 creates app.fill_owner_from_created_by(), which 00061 and 00062 reuse; 00063 creates app.fill_owner_from_uploaded_by().

• 00064 adds users.created_by uuid REFERENCES app.users ON DELETE SET NULL, nullable and not backfilled. NULL means the creator is unknown: every account that predates R2, and a Google self-join, which has none. Deleting the creator clears it rather than taking the account with it.

• 00065 adds classes.teacher_id uuid. app.classes has never had a created_by, so the backfill takes the oldest active Admin (by created_at, then id), the teacher v0.7.0 shows for every class. It raises if no active Admin exists and a class does. classes_fill_teacher (BEFORE INSERT) applies the same rule to the old binary's inserts; it and its function app.classes_fill_teacher() share the name. It reads users.role_id, not the legacy role, so 00105's DROP COLUMN role could not break it.

• 00066–00070 add the foreign keys, <table>_owner_id_fkey and classes_teacher_id_fkey, each REFERENCES app.users ON DELETE RESTRICT and each in its own file after the columns. A column file holds ACCESS EXCLUSIVE on its table until it commits; a reference inline would then ask for SHARE ROW EXCLUSIVE on users while holding it. A v0.7.0 transaction that has written users and next needs a lock on that table would then wait on the migration while the migration waits on it: the student creation that enrols into classes through class_members' foreign key, or a user delete whose foreign-key checks read the referencing rows. The deadlock detector would abort one side, most likely the release. Apart, a column file never locks users beyond ACCESS SHARE, and a foreign-key file takes SHARE ROW EXCLUSIVE on the referencing table, which reads and foreign-key checks do not conflict with, so it simply waits for such a transaction to commit. 00064 keeps its inline reference: it references its own table.

• The new binary names every owner itself; the triggers existed only for the deploy overlap and 00106 dropped them:

  Row	Owner written
  a class	teacher_id = the acting user
  a new test, a bank question, a bank group	the acting user
  a duplicated test	the source test's owner; created_by stays the actor
  a committed Word import's test, its section groups and bank questions	the import's creator; created_by stays the committer (T-R2.12f)
  a section-owned group, a restored draft's question	the test's owner
  a group member	the group's owner
  a media asset	the uploader
  a student created by staff	created_by = the actor
  a Google self-join	created_by = NULL

• Test fixtures and seed/01-dev.sql that insert a class name teacher_id. The class fill is the one that reads other rows: a fixture relying on it would take whichever fixture Admin is oldest at that moment, and a parallel package's delete of that user would then fail one side on the foreign key. The shared fixture in constraints_test.go also runs on scratch databases migrated to versions before 00065, so it names teacher_id only when the column exists. Since 00106 every fixture and seed insert into tests, questions, question_groups and media_assets names owner_id too: a fixture that omits it fails with 23502.

The expand half was proved in expand_r2_test.go: a legacy dataset at v0.7.0's schema by two authors, migrated to the version before 00106, checked for the owner backfill, the filled inserts and the NOT VALID constraints, with TestAddingTheClassTeacherWaitsForTheOldStudentCreateInsteadOfDeadlocking holding a v0.7.0-style student insert open across 00065. Both windows closed with v0.10.0, and the file was retired. What outlives them is in platform/db/tests/contract_state_test.go: the owner foreign keys are validated and RESTRICT, and an account's creator is optional and cleared when the creator goes (TestAnAccountsCreatorIsOptionalAndSurvivesItsCreator).

32. Ownership indexes (T-R2.10)

00071–00077 each hold one CREATE INDEX CONCURRENTLY in a NO TRANSACTION file, because every table is populated and in use; Down is DROP INDEX CONCURRENTLY IF EXISTS. docs/setup/deploy.md lists the drops to run after an interrupted build.

Index	Definition	Serves
classes_teacher_idx	(teacher_id)	a teacher's classes; the RESTRICT check on a user delete
tests_owner_idx	(owner_id, id DESC) WHERE deleted_at IS NULL	a teacher's tests list
questions_owner_bank_idx	(owner_id, id DESC) WHERE deleted_at IS NULL AND context_group_id IS NULL	a teacher's bank, without group members
question_groups_owner_bank_idx	(owner_id, updated_at DESC, id DESC) WHERE owner_section_id IS NULL AND archived_at IS NULL	a teacher's bank groups, in 00049's order
media_assets_owner_idx	(owner_id, kind, created_at DESC) WHERE deleted_at IS NULL	a teacher's media library, as media_assets_kind_created_idx serves everyone's
users_created_by_idx	(created_by) WHERE created_by IS NOT NULL	the accounts a staff member made; the SET NULL scan when they go
assignments_creator_idx	(created_by, id DESC)	a teacher's assignments

The four partial indexes (tests, questions, question groups, media) cannot serve the owner_id RESTRICT checks from 00066–00069, which run on every user delete (today only a student's) and do not filter on the partial predicate. Each check scans its table, as the unindexed created_by and uploaded_by checks already did, which is cheap at today's sizes. Only classes_teacher_idx covers its check.

No plan is forced. At today's volumes a sequential scan is often the right plan (§14), and the indexes earn their place as teachers and rows grow.

33. Encrypted join codes (T-R2.14a)

00078 lets a join code be read back (D5, D-27) without letting a database dump redeem one. Three columns join app.class_join_codes:

Column	Type	Meaning
code_ciphertext	bytea, 36 bytes when set	a 12-byte nonce, the 8-byte code and a 16-byte AES-256-GCM tag; the additional data is the class id's 16 bytes, then the code id's
key_id	smallint, never 0	the 16-bit HKDF fingerprint of the key that sealed the code and keyed its hash
lookup_scheme	smallint NOT NULL DEFAULT 1, 1 or 2	1: code_hash is the SHA-256 of the code (v0.7.0); 2: it is the HMAC-SHA256 under key_id's lookup key

• class_join_codes_scheme_consistent makes a row either legacy (scheme 1, no ciphertext, no key) or fully sealed (scheme 2 with both). The v0.7.0 binary's insert names none of the columns and is a legacy row.
• code_hash keeps its UNIQUE index, and it is still the only lookup path: a typed code is searched as code_hash = ANY(candidates), the keyed hash under the current and the previous key and the legacy SHA-256. The newest matching row is taken, and it counts only against the candidate of its own scheme and key.
• The keys never reach the database. A dump yields ciphertexts and keyed hashes; without JOIN_CODE_KEY neither opens nor can be hashed through the code space. A legacy row's SHA-256 still can, and quickly: beside the stored four-character hint only 2^20 codes remain. That is why R4 replaced the live legacy codes at start-up (D5, below).
• Down refuses while an unrevoked, unexpired scheme-2 code exists, because the previous binary looks codes up by SHA-256 alone.
• The API replaces the legacy rows at start-up from v0.10.0 (T-R4.22): each unrevoked, unexpired scheme-1 row is revoked and a scheme-2 row is inserted for its class with the same expires_at, max_uses and created_by and uses_count (F-67), in one transaction per class under advisory key 41 (§28), audited with a NULL actor; a revoked or expired scheme-1 row is left as it is.
• Lock order for a class and its join codes (F-23). The transactions that lock a class row and one of its code rows take them in these modes and orders:
  • The start-up rotation takes advisory (73819, 41) (§28), then the class row FOR NO KEY UPDATE, then the class's active scheme-1 code row FOR UPDATE. Rotate and Revoke take the class row FOR NO KEY UPDATE, then update the code row, and take no advisory key.
  • Rotate reads archived_at in that same locking SELECT (F-64), so the read and the lock are one statement: an Archive, which holds the same row lock for its plain UPDATE, either committed first, and the locked read returns the archived row (READ COMMITTED re-reads the latest version after the wait), or waits for the Rotate. An archived class answers ErrClassArchived before any code row is touched, after the scope predicate has already answered ErrClassNotFound for a class the caller does not reach. Revoke does not read archived_at and stays allowed on an archived class.
  • A redemption (EnrolNewMember, EnrolExisting) takes the code row FOR UPDATE first, found by its lookup hash, and its member insert then takes KEY SHARE on the class row through the foreign key.
  • NO KEY UPDATE conflicts with itself, with SHARE and with UPDATE, and not with KEY SHARE. A redemption and the three writers above therefore never wait for each other in a cycle: the writer waits for the code row and goes on when the redemption commits. Update and Archive (a plain UPDATE of the class) and AddMember (FOR SHARE) still wait for those writers, and the writers for them.
  • Delete takes the class row FOR UPDATE, as every DELETE does, and removes the code rows by the cascade: the inverse of a redemption's order. It meets one only when the class was archived and deleted between the redemption's claim and its member insert, because a redemption of an archived class is refused before it takes a class lock. That window exists because the claim reads archived_at and self_join_enabled from the class row without a lock (repositories/enrol.go, claimCode), so an Archive can commit between a claim and its insert; this predates F-23 and is unchanged. Delete stays as it is: taking the code rows first would invert it against Rotate and Revoke.
  • A contender outside this list can still abort the rotation as a deadlock or serialization victim; the repository answers domain.ErrRotationContended and the command tries that class once more (T-R4.22).

34. Notifications (T-R4.10a)

Two tables, both new and empty at release, so every column is NOT NULL inline where it must be and no index is built CONCURRENTLY. The app role gets SELECT, INSERT, UPDATE and DELETE on both through 00009's default privileges; neither file writes a grant.

CREATE TABLE app.notifications (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id    uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  kind       text NOT NULL,                       -- dotted key, at most 64 characters
  params     jsonb NOT NULL DEFAULT '{}'::jsonb,  -- an object, at most 4096 bytes as text
  target     jsonb,                               -- NULL, or an object of at most 1024 bytes
  dedupe_key text NOT NULL,                       -- 1 to 200 characters
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  read_at    timestamptz,
  CONSTRAINT notifications_user_dedupe_key UNIQUE (user_id, dedupe_key)
);

CREATE TABLE app.notification_preferences (
  user_id    uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  event      text NOT NULL,
  in_app     boolean NOT NULL,
  email      boolean NOT NULL DEFAULT false,
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, event),
  CONSTRAINT notification_preferences_event_check CHECK (event IN (
    'attempt.submitted', 'attempt.flagged', 'assignment.closing', 'assignment.due_soon', 'result.ready'))
);

• No sentence is stored. The reader's console words a row from kind and params in the reader's language. kind is checked for its shape only (notifications_kind_check), so a release adds a kind in the contract and the domain without a migration.
• params and target are bounded objects (D-28): notifications_params_shape and notifications_target_shape require jsonb_typeof(…) = 'object' and bound octet_length(…::text), as 00047 does. The text length is what the constraint measures because it is the same for a value whatever its storage: pg_column_size moves with compression and TOAST. A NULL target passes its check; the JSON value null does not.
• UNIQUE (user_id, dedupe_key) is what a merge conflicts on. Notify is one INSERT … ON CONFLICT (user_id, dedupe_key) DO UPDATE: a second notice about the same thing updates the row, makes it unread again (read_at = NULL) and, when asked to add, sums count and toGrade with the stored values inside the statement. Nothing reads the row first, so a class handing in within the same seconds loses no increment. The same key for two users is two rows.
• A switch that is off stops the write. The same statement inserts only when no notification_preferences row for the user and the kind's event has in_app = false; a missing row means the default, which is on. email is stored and read by nothing until R7.
• updated_at moves by the trigger on both tables (§13.2), on a merge and on a mark-read alike. created_at never moves: a merged row keeps its id, its place in the list and its retention.
• Retention is 180 days from created_at. PruneNotifications runs at API start-up and then daily and deletes WHERE created_at < now − 180 days; it is idempotent and takes no lock beyond its rows.
• ON DELETE CASCADE from users on both tables: a deleted account takes its notifications and switches with it. Names inside another user's params stay, which is the hand-off in D-28.
• Later releases replace notification_preferences_event_check by name to add switches (R7, R10). The constraint name is part of the interface.

Indexes, and the statement each exists for:

Index	Serves
notifications_user_recent_idx (user_id, id DESC)	The reader's list: WHERE user_id = $1 AND id < $2 ORDER BY id DESC LIMIT n, a keyset on the time-ordered uuidv7 id. The statement orders by the column, not by the id::text it returns, or the index cannot give the order. Its leading column also serves the cascade from users
notifications_unread_idx (user_id) WHERE read_at IS NULL	The unread count the shell polls (GET /me/summary) and "mark all read". Partial, so it holds unread rows only
notifications_created_at_idx (created_at)	The daily prune
notifications_user_dedupe_key (user_id, dedupe_key), unique	The merge's conflict target
notification_preferences_pkey (user_id, event)	The five switches of one user, and the switch lookup inside Notify

repositories/tests/plan_test.go seeds 50,000 rows for 200 users inside a transaction it rolls back and asserts that the list reads notifications_user_recent_idx alone, with the cursor as an index condition and no sort, and that the count uses notifications_unread_idx.

A bell that names an audience (DG-170, spec v0.80) adds kind = ANY($n::text[]) to the list, the count and both markings, where the array holds that console's kinds (notifications/domain/audience.go). It adds no column, index or migration: the list reads the same index in the same order and applies the kind as a filter, and the count finds the user's unread rows through notifications_unread_idx and reads their kind from the few rows it returns. The markings add the same filter to statements that already select by user_id. The plan test asserts the audience list and count as it asserts the others.

35. Caller profile and preferences (T-R4.7)

00085_add_users_profile_fields.sql adds five nullable text columns to app.users. The display-name check accepts 1–80 characters after btrim; the application stores the trimmed value. Phone accepts 6–20 digits, spaces and plus signs. Locale is vi or en. A non-null time zone has 1–64 characters; the application additionally validates it with time.LoadLocation. avatar_key holds the storage key of the photo (avatars/{userId}/{uuid}.png), written and cleared by T-R4.8 (PUT and DELETE /me/avatar) with an audit row each; this migration adds no photo operation and T-R4.8 adds no column. Down drops the five columns and their checks.

00086_add_users_preferences.sql adds preferences jsonb NOT NULL DEFAULT '{}'. Named checks require an object and at most 8192 bytes of normalized preferences::text, measured by octet_length. This is the stored UTF8 representation limit, separate from the HTTP body limit. Down drops the column and both checks. No new index or privilege is needed. Existing inserts that omit these columns retain nullable fields and an empty object.

The contract restricts preference keys and nested assignment-default fields. A PATCH merges top-level keys; a supplied nested object replaces that key's previous object. An empty object is a no-op. Read defaults do not materialize locale, zone or preference keys in the row. The effective display zone is Asia/Ho_Chi_Minh until the caller explicitly stores another valid zone.

Both profile and preference writes lock and read the current row before merging. Mutation and audit insertion use one data-modifying CTE. Only actual changed fields are audited, with sorted private field names; no-op writes produce no audit. Invalid audit metadata rolls back the mutation as well. Concurrent disjoint writes therefore retain both changes, while the same key follows lock acquisition order. Disabled callers are rechecked against the locked row. The legacy full-name rename audit remains compatible.

Private fields appear only in the caller's CurrentUser. Public User projections exclude phone, locale, zone and preferences. Student class, intro and join-preview teacher names use coalesce(display_name, full_name) and add no private field. /join/preview still exposes only the teacher's chosen student-facing name through its existing field. Contract and hosted API/isolation checks cover these public/private projection boundaries.

The independently verified fixture helpers register cleanup before insert, use only newly allocated ordinary Student IDs, drain repository writers before releasing locks, and check exact removal. Audit rows are retained; deleting the owned user leaves the audit actor null through the existing foreign key. Failure coverage requires real reached-stage markers and canceled-Commit proof; successful cleanup cannot stand in for an intended failure branch. No legacy user, role, owner trigger or compatibility alias is removed here.

36. Question level, skill and legacy choice compatibility (T-R4.15)

Migrations 00087_add_questions_level_skill.sql and 00088_add_test_version_questions_level_skill.sql add nullable text columns level and skill to the bank and frozen question tables. Named CHECK sets accept the seven CEFR levels and six skills recorded in spec §7.1; null remains valid. There is no backfill, index, new privilege or trigger. Each Down removes only its two columns and matching checks.

New teacher authoring rejects a choice array above eight. Storage and frozen read contracts remain uncapped so historical nine-option content can be read, bodylessly copied, published and restored. Included import questions undergo the same authoring refusal; a late refusal rolls back earlier writes in the commit transaction. These restrictions do not delete or rewrite old content.

Bank writes, standalone and grouped snapshots, structural copies and restored drafts carry both fields. Snapshot values are independent of subsequent bank edits; existing question/media row-lock ordering is unchanged. Draft list skills combine both standalone and group-member arms, drop nulls and return a distinct sorted array. Metadata facets retain the other dimensions and owner scope while omitting their own dimension. The existing accent-folded search expression is unchanged. No representative query-plan or runtime performance claim is made.

The combined candidate contains the full migration inventory 00001–00091. Its purpose-database app/migrate tests, the isolated 87/88 down/up rehearsal and the separate historical oversized-choice Neon inventory remain acceptance gates. A synthetic nine-option fixture is compatibility evidence, not a count of real historical rows.

37. Alt text for a question's image (T-R4.62a)

Migrations 00097_add_questions_media_alt.sql and 00098_add_test_version_questions_media_alt.sql add one nullable text column, media_alt, to the bank and frozen question tables. Each carries a named CHECK, questions_media_alt_check and test_version_questions_media_alt_check, of char_length(media_alt) BETWEEN 1 AND 1000, added with the column. There is no default, backfill, index, trigger or new privilege, and each Down drops its constraint and column. 00093 to 00096 were reserved for other R4 tasks and are unused: those tasks took the next number above the integration branch's highest when their pull requests opened (T-R4.11 took 00099 and 00100, T-R4.12 00101, T-R4.9 00102), so the inventory skips from 00092 to 00097 and goose applies 00097 across the gap.

Only an image has alt text. Input.Validate refuses a value unless the resolved media kind is image, with a field error on mediaAlt (400 VALIDATION_FAILED, details.mediaAlt), and also refuses a blank one and one over 1000 characters; publish validates again. The bank store writes the value only beside an image, so clearing the media, or changing it to audio, leaves NULL. A write replaces the stored value, as it does for level and skill, so an update that leaves mediaAlt out clears it. A file replacement keeps the question's alt text, because it never changes the kind.

Publish copies the bank value into test_version_questions.media_alt in the statement that freezes the question; the draft read, the lock order and the media.LockForVersionUse and questions.LockForDraftUse calls are unchanged. Restore as draft copies it back from the version, a duplicated group copies the draft's, and the version preview, the student's paper and the result read the frozen value. The version diff puts it in the media part of a question, so a changed alt text is one changed entry and raises unpublishedChanges.

Contract step for the image rule (R5, after v0.10.0)

R4 enforces "alt text only beside an image" in the write path and nowhere in the schema, because an R3 binary can still change an image to audio without touching media_alt, and a check would turn that write into an error during the rolling deploy. Once v0.10.0 is the only binary, the rule moves into the schema, in three files that follow expand then contract:

1. Clear what the rule would refuse, on questions only. UPDATE app.questions SET media_alt = NULL WHERE media_alt IS NOT NULL AND media_asset_kind IS DISTINCT FROM 'image'::app.media_kind. A bank row is editable, and the only rows it can touch are ones an R3 binary left behind. On test_version_questions there is no UPDATE: a published version does not change, and none can hold the pair, because publish validation refuses alt text beside other media and an R3 publish writes NULL. The step runs SELECT count(*) FROM app.test_version_questions WHERE media_alt IS NOT NULL AND media_asset_kind IS DISTINCT FROM 'image'::app.media_kind first and stops for a decision on any other answer than 0, instead of rewriting a version.
2. Add the checks without validating them. ALTER TABLE app.questions ADD CONSTRAINT questions_media_alt_image_check CHECK (media_alt IS NULL OR media_asset_kind IS NOT DISTINCT FROM 'image'::app.media_kind) NOT VALID, and the same on app.test_version_questions as test_version_questions_media_alt_image_check. New and changed rows are checked from here on.
3. Validate. ALTER TABLE ... VALIDATE CONSTRAINT for each, which scans the table under a SHARE UPDATE EXCLUSIVE lock and blocks no write.

The kind needs no trigger and no join: both tables already hold it as media_asset_kind, tied to the asset by the composite foreign key (media_asset_id, media_asset_kind) to media_assets(id, kind) and to media_asset_id by questions_media_pair_complete and tvq_media_pair_complete. The predicate must say IS NOT DISTINCT FROM, as 00011 does for audio: with a plain = 'image' a NULL kind makes the whole check NULL, and a CHECK passes on NULL, so alt text with no media at all would be accepted. Each Down drops its constraint, and the three files are numbered when R5 plans them.

38. Per-student overrides (T-R4.12)

00101_create_assignment_student_overrides.sql adds one table. Down drops it.

CREATE TABLE app.assignment_student_overrides (
  assignment_id    uuid NOT NULL REFERENCES app.assignments(id) ON DELETE CASCADE,
  student_id       uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  closes_at        timestamptz,
  duration_minutes integer,
  extra_attempts   smallint NOT NULL DEFAULT 0,
  reason           text NOT NULL,
  created_by       uuid REFERENCES app.users(id) ON DELETE SET NULL,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (assignment_id, student_id)
  -- and the four named checks in the table below
);

Constraint or index	What it holds
assignment_student_overrides_pkey (assignment_id, student_id)	One override per student and assignment; its leading column serves the cascade from app.assignments and every read of one assignment's overrides
assignment_student_overrides_student_idx (student_id)	The cascade from app.users
..._duration_check	duration_minutes BETWEEN 1 AND 600, the assignment's own bound
..._extra_attempts_check	extra_attempts BETWEEN 0 AND 10
..._reason_check	char_length(reason) BETWEEN 1 AND 500; the command stores the trimmed reason, so a reason of only whitespace never reaches the check
..._changes_check	closes_at IS NOT NULL OR duration_minutes IS NOT NULL OR extra_attempts > 0: a row that changes nothing is a DELETE

created_by is ON DELETE SET NULL, so removing a teacher's account keeps the accommodation they gave. The previous release neither reads nor writes the table, so the table needs no expand step, and a rolled-back binary leaves its rows alone. The app role holds the usual SELECT, INSERT, UPDATE, DELETE through the default privileges. R5's grant_maintenance_role grants quizzivy_maintenance SELECT, UPDATE (closes_at) on it for the maintenance extension (T-R4.12b).

The merge. setStudentOverrides computes the merged row in its wanted CTE, from the existing override, and inserts that row: PostgreSQL checks the CHECKs on the proposed row before it looks for the conflict, so a candidate that carried only the request's fields would fail ..._changes_check on a student whose stored row holds the rest. ON CONFLICT DO UPDATE then takes the merged values from EXCLUDED. The assignment row is held FOR NO KEY UPDATE for the transaction, which serialises writers of one assignment's window and overrides and does not block an attempt's insert (its foreign-key check takes FOR KEY SHARE).

Reading the student's window. schedule.OverrideJoin is the LEFT JOIN on the student, schedule.CloseOf("o") the close it yields, and schedule.Window.WithOverride its Go twin; attempts.Rules, attempts.RulesFor, LoadResult and the student's card and intro all read through them. An override's closes_at never closes a student sooner: the close is greatest(least(closed_at, closes_at), o.closes_at).

Moving the deadlines in progress (12b). extendAssignment, updateAssignment, reopenAssignment and setStudentOverrides end by calling schedule.RecomputeDeadlines in the transaction that changed the window; deleteStudentOverride takes the same two locks and recomputes nothing, because removing an override can only shorten a student's window and a deadline never moves earlier. An update or a reopening recomputes the whole assignment unconditionally, and a shorter close or a Close now moves nothing for the same reason. An attempt whose deadline_at is not after the database's now() is over, swept or not, and is skipped, so no writer revives it. The recompute sets deadline_at of each in_progress attempt to least(started_at + coalesce(o.duration_minutes, a.duration_minutes), CloseOf("o")) where that is later than the deadline the attempt has, and writes one attempt.extended audit entry per attempt moved (deadline_at old and new, the cause, the assignment) through a data-modifying CTE. A deadline never moves earlier: removing an override, or a time limit shorter than the one an attempt started under, leaves a running attempt alone, and a deadline that a maintenance window pushed past the rule stays where it is. The recompute is limited to studentIds for an override and runs over every attempt of the assignment for an extension.

Lock order, always: the maintenance-windows advisory lock (73819, 40) shared, then the assignment row, then the attempt rows in id order. An attempt start takes the lock and the assignment row FOR SHARE before it inserts; the five writers above take the assignment row FOR NO KEY UPDATE (updateAssignment its long-standing FOR UPDATE), both of which conflict with FOR SHARE, so a start waits for them; a plain NO KEY lock does not conflict with the FOR KEY SHARE an attempt's foreign key takes. A start therefore either commits before the writer reads the attempts, and the recompute moves the attempt it inserted, or reads the rules after the writer committed, and computes its deadline from the new window. window-schedule takes the lock exclusively and updates assignments and attempts in one statement, so it never runs among them.

The start race (R12a-5). attempts.Store.Create re-reads the rules inside its transaction, after the maintenance guard, with FOR SHARE OF a, and stores the deadline it computes from that read. The service's earlier read, outside the transaction, decides what the first maintenance check covers and nothing else: the transaction checks again that the student is targeted and that the committed window is open, and when the deadline it computes is later than the one the first check covered, it runs the maintenance check a second time over the longer span. FOR SHARE OF o is not taken: an override is written under the assignment row lock, so the assignment lock already orders a start against every override write, and a lock on the override row would only add a second, unordered path. The override join is a LEFT JOIN, and FOR SHARE on its nullable side is refused by PostgreSQL in any case.

39. Where a device last refreshed (T-R4.9)

00102_add_refresh_tokens_geo_label.sql adds one nullable column. Down drops the constraint, then the column.

ALTER TABLE app.refresh_tokens
  ADD COLUMN geo_label text
    CONSTRAINT refresh_tokens_geo_label_check
    CHECK (char_length(geo_label) BETWEEN 1 AND 80);

The server builds the label as "City, CC" from CF-IPCity and CF-IPCountry, only when CLIENT_IP_HEADER names CF-Connecting-IP and the request carries it. A sign-in stores it, and a rotation stores the label of its own request when it has one and copies the predecessor's when it has none, so a family's head row says where the device last signed in or refreshed. The check scans the table once while the column is added; refresh_tokens is pruned by expiry, so the scan is cheap, and a nullable column with no default rewrites nothing. The label reaches no log and no audit_log row, and leaves the server only in GET /auth/sessions, to the owner of the family.

A session is a family with an unrevoked, unexpired row. The list reads it through refresh_tokens_user_live_idx (user_id) WHERE revoked_at IS NULL, and the current session's lookup by token_hash and refresh_tokens_family_idx; neither needed a new index.

40. Composed text (F-37)

00103_compose_text_nfc.sql changes data and no schema. Be Vietnam Pro draws the precomposed Vietnamese letters, so text a person typed in a decomposed form (Unikey's "Unicode tổ hợp", a file name from a Mac, a paste from a PDF) fell back to another font in a title or a name. From this release the server composes the text it stores (spec §12), and this file brings the rows written before to the same form.

What it rewrites. Each plain column below is set to normalize(col, NFC) where it IS NOT NFC NORMALIZED, one UPDATE per column from a single list in a DO block: tests (title, description), test_sections (title, instructions), questions (sample_answer, transcript, media_alt, tags, and prompt and explanation only where prompt_content / explanation_content is null), question_options.text where content is null, question_blank_answers.answer, question_groups.title, group_stimuli.title, group_recordings.transcript, classes (name, description), users (full_name, display_name), media_assets (display_name, original_filename), word_imports.title, word_import_sources.filename, assignments.student_note, assignment_student_overrides.reason and attempts (teacher_note, void_reason). A tag array is composed element by element, a repeat that composing makes of two spellings is dropped, and the order of first appearance is kept. A value is left as it is when composing would take it past the CHECK that bounds its column (tests.title 200, classes.name 120, attempts.teacher_note 2000 and the rest of the list), which only a few scripts can do (U+0958 grows to two characters): no row can stop the deploy.

What it leaves, and why. The published versions (test_version_*, test_versions.change_note) are frozen. attempt_answers is a student's saved work, and the server composes new saves. attempt_events and audit_log are append-only. Every prose document (prompt_content, explanation_content, question_options.content, question_groups.instructions, group_stimuli.content) and the plain text kept beside one are left together: a write checks that the text equals the document's projection, SQL cannot derive that projection, and composing the two apart breaks the equality where a combining mark opens a text node. The next save of such a row composes the pair, and the web composes it where it draws. Emails, passwords, tokens, join codes, ids, keys, kinds, statuses and URLs are never composed.

The accepted answers. UNIQUE (blank_id, answer) would refuse composing a decomposed answer whose composed twin exists, so the file first deletes, per blank and per composed form, every row but one, preferring the row that is already composed. The grader folds the two spellings into one (normalise composes both sides), so the answer the deleted row gave is still accepted. The write path de-duplicates the same way.

updated_at. app.set_updated_at() sets updated_at := now() on every UPDATE, unconditionally. tests.updated_at is the token an open editor sends back with its autosave (a moved one is a 409), the lists sort on it, and word_imports.updated_at is the clock idle retention reads. So for each of the eight tables that has such a trigger (tests, questions, question_groups, classes, assignments, assignment_student_overrides, users, word_imports) the file runs ALTER TABLE … DISABLE TRIGGER <name> before the rewrite and ENABLE TRIGGER <name> after it, in the same transaction. It names the trigger: never ALL, never session_replication_role. The other tables the file touches have no such trigger.

Lock and duration. One transaction, no batching, no table rewrite, no index build. DISABLE TRIGGER takes SHARE ROW EXCLUSIVE on its table until the transaction commits: a write to one of those eight tables (the autosave of a test, a profile edit) waits, and a read does not. The UPDATEs take ROW EXCLUSIVE and block no reader and no writer of another row. Production holds tens of questions and a handful of accounts (T-R4.15), so the transaction lasts milliseconds to seconds. SET LOCAL lock_timeout = '5s' makes it fail instead of wait: a transaction that holds a conflicting lock for longer than five seconds makes the migration fail at its first ALTER TABLE, rather than queue every writer behind it. A failure rolls the whole transaction back, the disabled triggers included (DISABLE TRIGGER is transactional), so nothing is left half done or disabled, and the deploy is retried; the file is idempotent, so a retry, or a second run after the old binary has written decomposed text, changes only the rows that are not composed.

The deploy log. The DO block raises NOTICEs: how many decomposed duplicate accepted answers were folded, the rows per column that the CHECK guard left (with the table and column, nonzero counts only), or that every listed column is composed. TestTheComposeMigration* captures them through pgx's OnNotice.

Down. A no-op with a comment, a deliberate deviation (D-38). The file is data only and forward-safe.

41. When and where a class meets (T-R4.18)

00104_add_classes_schedule_room.sql adds two nullable columns. Down drops the two constraints, then the two columns. The number follows 00102 because 00103 is reserved for F-37, and the order of merging is the rule: F-37 (00103) merges before T-R4.18 (00104). If the order ever inverts, the second pull request renumbers its migration before it merges. goose refuses a database that has applied 00104 when 00103 is missing ("found 1 missing migrations"), and a deploy never passes -allow-missing to get past it.

ALTER TABLE app.classes
  ADD COLUMN schedule_label text
    CONSTRAINT classes_schedule_label_check
    CHECK (char_length(schedule_label) BETWEEN 1 AND 120),
  ADD COLUMN room text
    CONSTRAINT classes_room_check
    CHECK (char_length(room) BETWEEN 1 AND 60);

The commands trim both and store NULL for a value that is blank once trimmed, so a check never meets an empty string. The checks scan classes once while the columns are added; a nullable column with no default rewrites nothing. Both columns are read by the teacher's class reads, by the class a student joins and by the student's own classes; neither reaches an index, a log or an audit row.

The class average (Class.averageScore) is derived, not stored. It reads the best graded attempt of each live member on each assignment that targets the class, through attempts_student_started_idx (student_id, started_at DESC), assignment_classes_class_idx and attempt_answers_pending_idx; on 25 classes with 3,000 attempts each (75,000 attempts) the plan is an index scan of attempts with no sequential scan and takes 173 ms for the page and 6.8 ms for one class, so no index was added.

42. The legacy role goes (T-R4.49, T-R3.1)

The contract half of §30 (D-25). 00105_drop_users_legacy_role.sql takes the legacy role out once nothing older than v0.8.0 can still be serving. v0.8.0 and the previous binary, v0.9.0, never name users.role, insert role_id themselves, derive what they need from student_like_roles, and accept a token with or without a role claim.

One transaction, SET LOCAL lock_timeout = '5s', in this order:

1. VALIDATE CONSTRAINT users_role_id_not_null under SHARE UPDATE EXCLUSIVE, so reads and writes continue. A row without a role stops the migration here with 23502, before anything is dropped. The constraint has refused every new and updated row since 00056, so a row inserted meanwhile cannot make the validation fail.
2. DROP TRIGGER users_sync_legacy_role and its function. The trigger goes first: its UPDATE OF role list depends on the column, and dropping the column first fails.
3. DROP INDEX users_role_active_idx, DROP COLUMN role, DROP TYPE app.user_role. There is no IF EXISTS anywhere, so a wrong name fails the migration rather than leaving an object.

Steps 2 and 3 hold ACCESS EXCLUSIVE on app.users to commit. Nothing is rewritten, because a dropped column is only marked, so the window is milliseconds at any size. What waits is a read or write of app.users in those milliseconds. A query already running on the table delays the lock request, and every later query queues behind the request, so lock_timeout turns a lock that does not arrive within five seconds into a failed migration: the release stops with the schema as it was (55P03), instead of a sign-in queue. The app role's grants on app.users are table level, so a column added back by Down inherits them.

A deploy that fails after the migration leaves v0.9.0 serving the contracted schema, which it reads. A rollback as far as v0.8.0 works for this migration too; v0.7.0 names the column (docs/setup/deploy.md says roll forward).

Down restores the enum (and USAGE for the app role, granted explicitly rather than left to default privileges), the column, backfilled with users_set_updated_at disabled so no updated_at moves, then NOT NULL DEFAULT 'student', the index, the function and the trigger. The backfill reads app.student_like_roles: a rule over grants alone would call an Admin with "Take tests" on a student. The constraint stays validated, because Down never weakens a valid NOT NULL. Nothing runs it in production.

Tests: platform/db/tests/contract_state_test.go (what is gone and what is kept, and the refusal of a user without role_id) and core/tests/legacy_role_test.go, which fails on the legacy role anywhere in server/, seed/ and docker/ except the directory of migration tests. The stop at a user without a role, the give-up under a held lock and the Down were proved in contract_r3_test.go and the old-binary window in expand_r2_test.go; both were retired after v0.10.0 shipped this migration. TestMigrationsAreReversible still runs this Down, and no longer checks what it restores.

43. Ownership is validated and the fill triggers go (T-R4.49, T-R3.2)

The contract half of §31 (D-22, D-23). 00106_validate_ownership.sql takes the expand half's scaffolding out once nothing older than v0.8.0 can still be serving. v0.8.0 and the previous binary, v0.9.0, name the owner in every insert: a test, a question, a group and a media asset carry owner_id, a class teacher_id. The BEFORE INSERT triggers filled it only for v0.7.0. The five constraints have refused a NULL owner for every new and updated row since 00060 to 00065, so a row written meanwhile cannot make the validation fail.

One transaction, SET LOCAL lock_timeout = '5s', in this order:

1. A DO block counts, for classes.teacher_id and owner_id of tests, questions, question_groups and media_assets, the rows still NULL. The first table that has any stops the migration with 23502, whose message, TABLE and COLUMN name it, before anything changes. Without it VALIDATE would stop too, naming a relation and no more.
2. VALIDATE CONSTRAINT on classes_teacher_id_not_null, tests_owner_id_not_null, questions_owner_id_not_null, question_groups_owner_id_not_null and media_assets_owner_id_not_null. PG18 validates a NOT NULL this way, under SHARE UPDATE EXCLUSIVE with one scan of the table, so reads and writes continue.
3. DROP TRIGGER on tests, questions and question_groups, then app.fill_owner_from_created_by(); on media_assets, then app.fill_owner_from_uploaded_by(); on classes, then app.classes_fill_teacher(). A trigger goes before its function, and there is no IF EXISTS anywhere, so a wrong name fails the migration instead of leaving an object.

The locks, read from pg_locks after each phase on a migrated scratch database: the check holds ACCESS SHARE on the five tables, the validations add SHARE UPDATE EXCLUSIVE, and the drops add ACCESS EXCLUSIVE, all released at commit. The drops come last and touch the catalog only, so the window in which a table refuses a read or a write is milliseconds at any size. What can wait in it is a statement that arrives, or a transaction that is open, at that moment: a query already running on a table delays the lock request, and every later query on the table queues behind the request.

The migration stops in three ways, and each rolls everything back and leaves the schema, the version row and the v0.9.0 binary as they were; the release stops, and a redeploy retries:

• 23502, from the check in step 1, naming the table that holds a row without an owner.
• 55P03, when lock_timeout turns a request that does not arrive within five seconds into a failed migration.
• 40P01, when the deadlock detector ends the migration. A transaction of the previous binary that touches two of the five tables in the opposite order to the migration, and is open at that millisecond, deadlocks with it, and the detector ends one of the two after deadlock_timeout (1 s): the one whose wait the check reaches first, which the release does not choose. The other transaction can be the victim, and one v0.9.0 request then fails with a server error while the migration proceeds. The rollout notes (docs/setup/deploy.md, "Rolling out v0.10.0") say what to do in each case.

The previous binary is safe on both sides of the migration. Its seven INSERT statements name the owner (classes/repositories/postgres.go, media/repositories/store.go, questions/repositories/store.go, tests/repositories/{store,duplicate,group_create,version_draft}.go; a grep of the v0.9.0 tag finds no other INSERT into the five tables outside seed/ and tests), so the triggers it never needed going changes nothing for it, and a rollback to it after 00106 is safe. v0.7.0 does need them: it inserts a test without owner_id, which now fails with 23502. Roll forward, not back past v0.8.0.

Down recreates the three functions and the five triggers byte for byte as 00060, 00063 and 00065 created them (a test compares pg_get_functiondef, pg_get_triggerdef, owner and ACL before 00106 and after Down). The constraints stay validated, because Down never weakens a valid NOT NULL. Nothing runs it in production.

Tests, in platform/db/tests/contract_state_test.go and constraints_test.go: what is gone and what is kept; an insert that omits an owner is refused; and the not-validated constraints in app equal an explicit list, which is empty once 00105 and 00106 have both run. A later expand step adds its constraint to that list and its contract step removes it, so a forgotten NOT VALID fails CI.

contract_r3_ownership_test.go proved the migration itself and was retired after v0.10.0 shipped it: a row without an owner, in each of the five tables, stopped the migration naming that table and left everything as it was; a held lock made it give up with 55P03; the previous binary's inserts, reads and class inserts ran through it without an error; Down restored the objects and a bare insert was filled again; and validating blocked neither a read nor a write.

44. Students can pause (T-D4.13)

Four migrations add the pause rule of spec §11.1, one column and one concern each. Every Down drops its column, and the two constraints with theirs.

ALTER TABLE app.questions
  ADD COLUMN audio_allow_pause boolean
    CONSTRAINT questions_allow_pause_only_with_audio
    CHECK (audio_allow_pause IS NULL OR media_asset_kind IS NOT DISTINCT FROM 'audio'::app.media_kind);
-- the same on app.test_version_questions as tvq_allow_pause_only_with_audio

ALTER TABLE app.group_recordings ADD COLUMN allow_pause boolean NOT NULL DEFAULT true;
ALTER TABLE app.test_version_group_recordings ADD COLUMN allow_pause boolean NOT NULL DEFAULT true;

Why two are nullable. The previous binary inserts into both question tables while a release rolls out, and its insert names no such column. A nullable column with no default takes that insert unchanged, and a question or a version from before the control reads COALESCE(audio_allow_pause, true), which is what every audio question did. The existing biconditional questions_audio_policy_iff_audio is not widened to this column: the previous binary writes no value, and a rule that required one beside every audio policy would refuse its rows. The new CHECKs are the one-way rule questions_max_plays_only_with_audio already uses, with IS NOT DISTINCT FROM, because media_asset_kind is NULL on a question with no media. Why two are not. A recording is a whole policy (allow_seek and show_transcript_after_submit are NOT NULL columns), so there is no absent case to model, and a constant default is stored without a rewrite.

A save that leaves the field out keeps the value. updateQuestion and updateQuestionGroup replace the whole object, and three clients send no allowPause: a tab loaded before the release, whose z.object parsers strip a key they do not name; that tab's device-recovery draft; and every tab until T-D4.15 teaches those parsers the field. So the store keeps the stored value on an update, for a question by its id and for a group's member by its id in the bundle, in one statement, audio_allow_pause = CASE WHEN <audio present> THEN coalesce($new, audio_allow_pause, true) END; and the group update reads the group's recordings' allow_pause by id before it clears and rewrites them, which is the same rule for a recording. A policy new to a question (it had none, or an image) and a recording under a new id are creates, and read true. A create stores true.

Publish copies the bank's audio_allow_pause as it is into test_version_questions (a NULL stays NULL and reads true) and a recording's allow_pause into test_version_group_recordings, in the statements that already freeze the question and the recording. The draft read, the lock order and the media.LockForVersionUse and questions.LockForDraftUse calls are unchanged. Restore as draft copies the frozen value back, and duplicating a test or copying a group carries the draft's through the bundle. The version preview, the student's paper and the teacher's review read COALESCE(…, true); the version diff puts the value in a question's media part and in a group's recording print, so a changed rule is a changed entry, and an unset rule and true are the same rule there.

Tests: tests/application/tests/allow_pause_test.go (a freeze that later edits do not reach, a version from before the column, a save that leaves the field out, a create that leaves it out, a recording replaced by a new id, and copy, duplicate and restore), the attempts and HTTP tests of the student and teacher answers, and the diff cases in tests/domain/tests/diff_test.go.

45. A file that left the library (F-68)

The owner's decision of 2026-10-12. A media file leaves the library when it is replaced (replaced_by) or deleted (deleted_at). Until F-68 its object stayed in storage for ever and the file stopped counting toward its owner's quota at once, even while a published version still served it. Now the file counts while anything names it, and its object is removed seven days after nothing does.

Three states. A file is in the library (deleted_at and replaced_by NULL), or it is retired (one of them set, purged_at NULL), or it is purged (purged_at set: its object is gone and the row stays). A retired file is held while a row names it and pending while none does. media/repositories/retention.go holds the two predicates, retiredFile and namedFile, and every query below is built on them.

What names a file. A row of any of the six tables whose foreign key to media_assets is ON DELETE RESTRICT: a question that is not deleted (questions.media_asset_id), a group's recording or material (group_recordings, group_stimulus_assets, archived groups and the drafts of deleted tests included, as GroupReferences counts them for the delete check), and a frozen version's question, recording or material (test_version_questions, test_version_group_recordings, test_version_group_assets). Attempts, results and grading read an asset only through those three version tables, so a version that holds a file holds it for them. A deleted question names nothing: a deleted bank question would otherwise pin a file for ever. An import review draft names a file in JSON, not in a row, so it does not hold one; a draft that still names a file the sweep removed fails at its commit as one that is gone.

Columns (00111). unreferenced_at is when the file was last seen named by nothing. replaceMedia and deleteMedia write it in their transaction after the write that retires the file, when nothing names it (markRetired), and leave it NULL when something does. The sweep writes it for a file whose last name went afterwards, which no write announces, so the wait starts within one sweep period of the truth (an hour). A binding of the file clears it (00113), and the sweep clears it for a file it finds named. purged_at is when the sweep removed the object. media_assets_purged_check says a purged file was unreferenced first. 00112 is the partial index of the retired files, which the usage figures and the sweep read. A file that left the library before F-68 has no date: the first sweep dates it, and its object goes seven days after that sweep.

The quota (repositories.CheckQuota). One statement sums the owner's files that are in the library or retired and named, under the same advisory lock (73820, owner) (§28). An upload checks before it inserts with Add the new bytes. A replacement takes the lock, inserts the new file, repoints the editable references and marks the old one, and checks last with Add zero, so the sum is the state after the replacement: the old file counts exactly when a row still names it after the repoint, and the check needs no "leave this file out" argument (QuotaCheck.LeaveOut is gone). A replacement that published versions hold needs room for both files. A refusal rolls everything back, and the command deletes the fresh object as it does for every other refusal.

Usage. Usage adds HeldBytes (retired and named, counted toward the quota), PendingBytes (retired and unnamed, counted toward nothing) and PendingDeleteAt (the earliest unreferenced_at of those plus seven days; a file the sweep has not dated yet counts as dated now). The figures follow the scope as the library figures do, so the teacher workspace passes .Own() and scope.all sums every owner.

The sweep (command.Sweep, jobs.SweepRetiredMedia). The API runs it at start-up and every hour, five minutes at most, and ten minutes after a failure or a file it could not remove, on every machine and only where media storage is configured. One pass:

1. Observe dates the retired files nothing names and clears the date of those named again, in two statements.
2. Due lists the retired files dated at or before now minus seven days, in id order, a hundred at a time.
3. Purge takes one file in its own transaction. SELECT … FOR UPDATE SKIP LOCKED takes the row, so a file that a second sweep or a writer holds is skipped. Under the lock it checks again that the file is due and unnamed (a file named again has its date cleared and is left), deletes the object, sets purged_at and writes the audit row media.purged with no actor. Once the delete has succeeded, the mark, the audit row and the commit run under their own ten seconds and not under the sweep's budget, so a sweep whose time ends at that moment still records the removal; if they still fail, Purge answers a PurgeMarkError and the job logs the file's id at ERROR. The object goes before the row is marked: a failure of the delete, of the mark or of the commit leaves the row for the next pass, whose delete of an object already gone succeeds (storage.Client.Delete treats NoSuchKey as success). The delete has thirty seconds, so a stalled storage cannot hold a row lock for long.

Why it cannot race a writer. A writer that freezes or binds a file takes the row first (LockForVersionUse, which answers not found for a purged file, as it does for a deleted one), then writes. Not every writer takes it: a bank question's write checks Readable and inserts, and the foreign key's share lock does not conflict with the sweep's update of an unrelated column. 00113 closes that for every writer at once. Each of the six tables has a BEFORE INSERT OR UPDATE OF media_asset_id trigger that, for a changed non-null binding, updates the file's row to clear unreferenced_at and then refuses the write with check_violation (constraint media_assets_purged_binding) if the file is purged. The update takes the row lock when the file is waiting for removal, so the writer waits for a sweep that holds it and then sees purged_at; a writer that comes first holds the lock and clears the date, and the sweep skips the row and finds it named. A file that is not waiting is not locked. Readable, Get, LockForVersionUse and SoftDelete treat a purged file as gone, and the library predicate excludes it, so a replaced file cannot reappear if its successor's row is removed (replaced_by is ON DELETE SET NULL).

The switch. MEDIA_RETENTION_SWEEP (on by default, or off; anything else stops the start-up) is read where core.Serve starts the sweep. Off starts nothing and logs a warning; quota counting and the dating by replace and delete are not behind it. A bank question's write that names a file the sweep removed in the instant between its Readable check and its insert meets the trigger, which the questions repository maps to the missing-file refusal.

Tests. media/application/tests/retention_test.go (the quota with a version holding the old file, the dating by a replace, a delete and the sweep, the seven days and not before, a publish and a writer racing the sweep in both orders, two sweeps, a failed delete, a purged file read by nobody), retention_storage_test.go (MinIO), sweep_test.go, core/jobs/tests/media_retention_test.go and tests/media_retention_flow_test.go (the answers over HTTP). Each race test fails when the lock, the skip or the trigger is taken out.

Rollout. Expand only. The columns are nullable and the trigger does nothing until a file is purged, so v0.10.0 runs on the new schema. A file v0.10.0 replaced or deleted is dated by the first sweep of the new binary and removed seven days later, so the first object is removed seven days after the new binary starts. Down drops the triggers, the function, the index and the columns; nothing restores an object a sweep removed.

46. The AI assistant's credits (T-D4.31)

Six migrations (00118 to 00123, one table each, none -- +goose NO TRANSACTION, all on new empty tables, so the previous binary is unaffected) hold the budget of the AI assistant that D19 to D22 approved. A credit is $0.001 of the provider's list price; the centre has a pool of 200,000 a month ($200), every teacher an allowance under it (20,000 by default), and a call reserves its ceiling before it reaches the provider and settles what the provider metered after.

The hard stop is two constraints. ai_credit_periods_within_pool and ai_teacher_credits_within_allowance each say used + reserved <= total, and every credit that moves does it in an UPDATE of those rows, so the database refuses the call that would overspend and no code path has to remember to. Reserve takes the month's row and then the teacher's (FOR NO KEY UPDATE, which does not block the foreign keys that point at them), adds the ceiling to both reserved_credits, and inserts the reservation. A check violation is mapped from its constraint's name: ai_teacher_credits_within_allowance is the teacher's refusal and ai_credit_periods_within_pool the pool's, and the teacher's is tried first so a teacher who is out of credits is told so when the pool is short too. A paused teacher is refused by an unlocked read before the transaction begins, and again under the lock.

One lock order, everywhere. The month's pool row, then the teacher's row, then the reservation. Settle and Release lock both parents before they delete the reservation, and the sweep selects whose they are, locks the pools and then the teachers in key order, and only then deletes and returns in one statement. The admin writes that touch one teacher's row take only that row; SetRules locks ai_settings first, which a first use also takes FOR SHARE while it creates the rows from it. No path takes a teacher's row and then the pool's, so no two can deadlock. TestTwoConcurrentReservationsAgainstAPoolWithRoomForOneLeaveExactlyOne runs the race twenty-five times.

A settlement cannot fail on the pool's constraint. Settle charges min(metered, reserved). Moving reserved down by the reservation and used up by no more than that can only lower what the constraints add up, so a call the provider has already been paid for is never refused; a call that cost more than its ceiling is logged, and the ledger keeps the provider's real token counts. A settlement for a reservation the sweep already returned writes nothing and answers ErrReservationGone: the sweep takes only reservations older than ten minutes and a call lasts at most 120 seconds.

Credits are integer arithmetic. credits = ceil((input x p_in + output x p_out + cache_read x p_cache + cache_write x p_in) / 1e9) with the prices in millionths of a dollar per million tokens (config.MicroUSD), so no call is rounded across a credit by floating point. The configuration has no price for a cache write; the client does not ask for caching today, and a write is charged at the input price, which never undercounts by more than the provider's write premium.

The allowance follows the person, the grant follows the month. custom and paused are copied from the teacher's latest earlier row when a month's first row is created (the lookup is ai_teacher_credits_user_idx), so an Admin's pause or custom figure outlives the first of the month. granted_credits is what approved requests added to this month's allowance; it is part of allowance_credits, is not carried over, and survives a change of the default rule. A custom allowance is stored as the figure given plus the month's grants. SetRules rewrites the month's rows that are not custom to greatest(default + granted, used + reserved), so lowering the default never breaks the constraint or takes back a credit that was spent; SetAllowance for one teacher refuses a figure below the use with ErrAllowanceBelowUse instead of raising it.

The warning is raised once. warned_at is written in the UPDATE that records the use, in the same statement that tests used x 100 >= allowance x warn_percent, and old.warned_at IS NULL AND new.warned_at IS NOT NULL in its RETURNING says this call crossed it. The notification is sent after the settlement commits, through cqrs.Announce, so a failure to tell never fails the settlement.

Who is a teacher here. The Admin's page lists every active user whose role is the Admin (the wildcard) or holds ai.use, and every user with a row that month. An allowance, a pause or a request needs an active user who is not in app.student_like_roles. The managers a request tells are the active users of the Admin role and of any role that holds ai.credits.manage. Both keys are inserted by T-D4.32's migration; until it runs only the Admin matches, which is what the wildcard means.

Every Admin write is one statement with its audit row. ai.rules.updated, ai.credits.added, ai.allowance.updated, ai.credit_request.approved and ai.credit_request.declined are written by a data-modifying CTE that feeds audit_log from the changed row's old and new, so the diff cannot disagree with the change. A command run from cmd/maintenance ai-credits is System: actor_user_id is NULL and the diff carries reason: "maintenance" and the operator. The console's dry run runs the same handlers inside a transaction it rolls back, with no notifier, so nothing is written and nobody is told.

Privileges. The app role has full DML on the five tables that hold balances and requests (the default privileges of 00009) and only INSERT and SELECT on ai_usage_ledger; privileges_test.go lists the ledger beside audit_log and attempt_events. cmd/maintenance runs as the owner or maintenance credential MAINTENANCE_DATABASE_URL names. When R5's grant_maintenance_role moves it to quizzivy_maintenance, ai-credits and ai-reservations-sweep need SELECT, INSERT and UPDATE on ai_credit_periods and ai_teacher_credits, SELECT and UPDATE on ai_settings and ai_credit_requests, SELECT and DELETE on ai_reservations, INSERT and UPDATE on notifications (the upsert that tells a teacher), INSERT on audit_log, and SELECT on the users, roles and grants they read; they never write the ledger.

Down. Each file drops its table; the order is the reverse of the up. A Down loses the month's balances, the ledger and the requests, and nothing else references them.

47. Reading a scan with AI (T-D4.33)

One migration, 00125_add_word_imports_read_with_ai.sql, adds word_imports.read_with_ai boolean NOT NULL DEFAULT false. The default is a constant, which PostgreSQL 18 records in the catalog without rewriting the table, and the v0.10.x binary inserts imports without naming the column, so they read as false and the old binary is unaffected. The column belongs to the import, not to a run: a teacher chooses it when creating the import (createWordImport.readWithAi), it is fixed from then on, and a replay of the create with a different value conflicts (IMPORT_CONFLICT) as a changed title does. The worker reads it through Sources, which sets Source.ReadWithAi on every source of the run. There is no index and no check: nothing filters on it. The Down drops the column and loses which imports asked for AI, and nothing else.

48. Moving a question across the group boundary (E1)

POST /teacher/tests/{id}/questions/move (moveTestQuestion, spec §7.3 and §15) puts a standalone question of a test's draft into a shared-context group, takes a member out of its group, and moves a member to another group of the same test or to another place in its own group.

No schema change in v0.11.6. The release had no migration, no new constraint or index and no grant (v0.11.7 added one column and its index, D-50, under "After release" at the end of this section). Every table and column the move writes existed: questions, test_section_units, test_section_questions, question_groups, tests and audit_log. The writes hit the primary keys, the three ordinal constraints below and questions_context_complete, and the gap check hits group_gap_bindings_question_idx. So nothing is expanded or contracted, the up/down/up round trip is unchanged, and the §12 register needs no row: no table deviates. During a rolling deploy an old pod answers the route 404, and every row the new binary writes is a row the old one already reads, because a draft after a move is the same tables in the same shapes: standalone units and members of a group, and a member that left its group is a row with the all-null tuple the check always allowed.

The statements. One transaction, GroupsPostgres.MoveQuestion, in three cases:

• Into a group. DELETE the section's test_section_units and test_section_questions rows for the bank question, renumber the section's units and recompute the standalone ordinals from them, then insert a new questions row with context_group_id, context_ordinal and context_option_order = 'shuffle' (through the questions module's CreateGroupMember, with new question, option and blank ids), and renumber the group's members so the copy sits at index. The bank row is read and never written.
• Out of a group. (From v0.11.7 an unedited copy of a bank question that is still in the bank takes the path under "After release" instead.) One UPDATE app.questions SET context_group_id = NULL, context_ordinal = NULL, context_option_order = NULL, copied_from_id = NULL, owner_id = <the test's owner> WHERE id = $1 AND context_group_id = $2, so the check sees the all-null tuple at once; renumber the source group's members; shift the section's units at and after index up by one and insert the question's test_section_units and test_section_questions rows, then recompute the standalone ordinals. The group stays when it empties.
• Between groups, or within one. One UPDATE of context_group_id and context_ordinal for a member that changes group; then a row_number()-style renumber of each touched group's members (unnest of the ids and their new ordinals), as RemoveFromSection renumbers a section.

Then, for every case: UPDATE app.question_groups SET revision = revision + 1 on each touched group, UPDATE app.tests SET updated_at, and the audit rows, test.question_moved (entity test, a diff of the question, both places, the index and whether it was copied) and one question_group.updated per touched group. The diff holds only facts the transaction already knows under its locks, so it needs no data-modifying CTE (AGENTS.md). No test_version_* row is read or written.

The lock order is §19's, test, then group, then members, then media, with the question rows in ascending id order:

1. checkVersion: app.tests … FOR UPDATE, the revision and the caller's scope; then the archived check and the permission. This is the parent lock publish, the outline autosave, every group write and "Remove group" take first, so the move serialises against all of them.
2. The source, the destination and every group the request lists are resolved inside this test, so a foreign or missing id is ErrNotFound before any revision is compared.
3. The touched groups FOR UPDATE, in id order, each revision compared with the request's.
4. The touched groups' members FOR UPDATE, in id order (lockGroupMembers).
5. ensureSectionUnits on the section whose units the move rewrites (the source section of a copy-in, the destination of a move-out). A section that has no test_section_units yet is seeded first, and seeding takes questions.LockForDraftUse on every question of the section in id order. It comes before the next lock so that a move never holds one question while it waits on an earlier one: the first build locked the moved question and then seeded, and deadlocked (40P01) against another test's autosave that shares both questions.
6. lockBankQuestion, which is questions.LockForDraftUse on the bank row. On a copy-in it comes before the row is read, and it is the lock SoftDelete contends on. On a move-out it comes after the UPDATE, before the insert into test_section_questions that AGENTS.md requires it for: the row lock is held already from step 4, and this re-asserts that the question is not deleted.
7. Copy-in only: lockGroupAssets on the copied question's media, the row locks of media.LockForVersionUse in id order and the caller's read check, as a group's create and update take them. A media soft delete counts owned-member references under the asset row lock (§19), so the member insert must hold it.
8. SET CONSTRAINTS on the three ordinal constraints, DEFERRED; then the writes above.
9. After the writes, the same constraints and group_gap_bindings_member are set IMMEDIATE, so a violation surfaces inside the move; readGraph reads every touched group back and runs Bundle.Validate, which is where the destination's 200 members, 4 MiB budget, audio scope and asset rules refuse a move; then the test is read back and the transaction commits. Any failure rolls everything back.

The gap check comes before any write: a move-out or a move to another group reads group_gap_bindings for the question and refuses with group_gap_bound. A reorder within the same group does not ask, since a gap binds a member to its group, not to a place in it. The member count of a full destination is checked before the insert, so a full group answers group_limits before questions_context_complete (context_ordinal BETWEEN 0 AND 199) can fire.

The deferred constraints. questions_context_ordinal_key (context_group_id, context_ordinal), test_section_units_ordinal_key and test_section_questions_ordinal_key (test_section_id, ordinal) are DEFERRABLE INITIALLY IMMEDIATE, and the move defers them because it renumbers a whole list inside one statement sequence, when two rows briefly share an ordinal. The existing writers defer the same three (mixed_outline.go, group_update.go). group_gap_bindings_member, the foreign key from a gap to its member's (context_group_id, id), is DEFERRABLE INITIALLY DEFERRED. It would refuse the commit of a bound member's leaving anyway; the explicit check gives the teacher a message, and the move sets it immediate before the read-back so the refusal belongs to the move.

Why a copy on the way in, and the same id on the way out.

• In: a copy. A bank question is referenced, not owned, by a draft (D-14). Other drafts may reference the same row (DraftReferences). Moving the same id into a group would turn their reference into a member: their next autosave would fail LockForDraftUse, which filters context_group_id IS NULL, with "a question in the test has been deleted", and a later "Remove group" would hard-delete a row their test_section_questions foreign key still restricts (group_reference). The bank list is defined by context_group_id IS NULL (questions_owner_bank_idx is partial on it), so the same id would also vanish from the bank without a delete, and the data could not tell a bank question from a test-only one. Published versions and grading history do not care: attempt_answers references test_version_questions, and source_question_id is ON DELETE SET NULL (D-07). The snapshot canary edits a bank question after publish, and a copy leaves that row alone. It is also the rule a group already follows: the builder inserts bank groups as complete independent copies, and "no editable source FK is retained" (§19).
• Out: the same id (v0.11.7: unless it is an unedited copy of a bank question that is still there, which goes back as that question). Nothing but its group can reference a member, so there is no second reader to protect, and a copy would only churn ids. The tuple goes to NULL in one statement. The row's owner_id is the test's owner by the group's ownership, but a member written before ownership existed carries an owner_id backfilled from created_by (00061, 00062), so the statement names the test's owner explicitly: the question comes out owned by the test's owner whoever created it, and then lists, opens, autosaves and soft-deletes like any bank question of that teacher.
• Between groups: in place. The same row changes context_group_id; only the group's ordinals and both revisions move.

Tests of v0.11.6. tests/application/tests/group_move_*_test.go, integration, on PostgreSQL 18: the three cases and the clamp of index (TestMoveInCopiesAndLeavesTheBankQuestion, TestMoveOutKeepsTheIdAndReturnsItToTheBank, TestMoveBetweenGroupsBumpsBothRevisions, TestMoveClampsIndexAndCompactsOrdinals), the refusals writing nothing (TestMoveRefusalsWriteNothing and the gap-bound case), the published versions untouched row by row across thirteen tables (TestMoveLeavesPublishedVersionsUntouched), a publish after a move (TestPublishAfterMoveFreezesTheNewUnitsAndOrdinals), the deal (TestMovedQuestionsDealInsideAndOutsideTheirGroups), and the races, which pause one writer inside its lock and watch pg_blocking_pids for the other: against a bank delete, a group save, the outline autosave, publish, a media delete, and another test's autosave that shares questions (TestMoveIntoGroupLocksQuestionsInIdOrderAgainstAnotherTestsAutosave). server/tests/group_move_flow_test.go drives the operation through the real stack.

After release: the copy remembers its origin (v0.11.7)

The first release left a second bank question behind every round trip: a copy went into a group under a new id and came out under that id as a bank question of its own, next to the original. v0.11.7 (decided 2026-10-14, 74e-e1-group-move.md "After release") makes the copy remember where it came from. It changes this section's consequence, not its decision: a copy is still a copy and the bank row is still never written by a move into a group.

The column is questions.copied_from_id uuid, nullable, REFERENCES questions (id) ON DELETE SET NULL (00126, D-50), with the partial index questions_copied_from_idx on the copies (00127). It follows test_version_questions.source_question_id (D-07): informational, never a reason to refuse anything. Nothing writes it but a copy into a group: CreateGroupMember takes it in the member's GroupOwnership and inserts it with the row; a group save (UpdateGroupMember) never touches it; a move between groups or within one leaves it; a member that leaves its group sets it NULL in the same UPDATE that clears its tuple, because it is then an independent question. A copy the v0.11.6 binary wrote has NULL and is never restored.

What decides a restore. On a move out of a group, after the gap check, all of these must hold, or the member leaves as before:

1. the member has copied_from_id;
2. the origin is a live bank question: questions.LockForDraftUse finds it (not deleted, not a member), and the caller's scope reaches it (BankQuestion, as the copy-in reads its source);
3. the test's owner owns it (NotOwnedBy), the invariant the outline writer holds for every standalone question, so the next autosave accepts it;
4. no section of this test already holds it, so a restore never puts one question in a test twice (the unique key of test_section_units would refuse it within a section);
5. the member's content equals the origin's.

The answer says which: restored when all hold; editedCopy when 1 to 4 hold and 5 does not (the builder tells the teacher the question was saved as a new one because it had been edited); neither when 1 to 4 fail, as there is no edit to report for a question that was never a copy or whose original is gone. A concurrent delete of the origin is the second case of 2: LockForDraftUse waits for the deleting transaction and then finds the row deleted, so the move leaves the member as a bank question and does not restore a deleted row; if the move holds the origin's lock first, the delete finds the draft referencing the question again and answers 409.

Content is GroupQuestion.SameContent in the tests domain, one function. It puts both questions through GroupQuestion.Copy with the same fixed identities and compares what comes out, so what Copy remaps (the question, option, blank and rich-prompt gap ids) cannot make an unedited copy differ from its source, and the comparison is by position, not by id. It compares the type, level, skill, prompt and its rich document, points, explanation and its document, sample answer, media asset and alt text, audio policy (plays, seeking, transcript reveal, pausing) and transcript, tags (as a set), the options with their rich content and correctness, in order, and the blanks with their ordinals, case sensitivity, gap bindings and accepted answers (as a set). It leaves out ids, ordinals, context_option_order (a group's choice, not the question's), the owner and the timestamps. Rich documents are compared as JSON values, so whitespace and a stored null are not edits. There is no AI-draft flag on a question in storage or in the contract, so there is nothing of that kind to decide. A test lists every field of the input types and fails on a new one, so that a field added later is compared or declared an identity on purpose, and a restore can never discard an edit to it. The diff's questionParts is a different comparison for a different job: it works on frozen shapes and leaves out tags, level and skill, because a student does not see them.

Removing the copy is a hard DELETE FROM app.questions WHERE id = $1 AND context_group_id = $2, the statement a group save uses for a member it drops and "Remove group" for all of them. A member cannot be soft-deleted: questions_context_complete requires deleted_at IS NULL while the tuple is set. What can reference a member row, from the foreign keys on questions (id): its options and blanks (ON DELETE CASCADE, children); test_version_questions.source_question_id (ON DELETE SET NULL, D-07), which is how a published version froze it; test_section_questions and test_section_units (RESTRICT), which a member is never in; and group_gap_bindings, which the gap check refuses before anything is written. Attempts and grading reference the version's frozen rows, so a published version and an attempt on it keep working: they keep every frozen row, its options, blanks and answers, and lose only the informational link of the copy's frozen question, which is NULL, as it already is when a group save drops a member. A version diff against the draft then reads the original as added and the frozen copy as removed, which is what it is.

The lock order for a move out is the list above through step 4, then:

1. the gap check and a read of the member's copied_from_id, under the member's lock from step 4;
2. ensureSectionUnits on the destination. When it has to seed, it takes LockForDraftUse in one ascending sweep over the section's questions and the origin together (an origin that is gone is skipped there and judged in the next step). The origin is outside the section, so locking it after the sweep would hold a later id while waiting on an earlier one, the order that deadlocked the first copy-in (TestRestoreLocksQuestionsInIdOrderAgainstAnotherTestsAutosave reproduces it with 40P01 against the unsorted order);
3. LockForDraftUse on the origin (a repeat when the sweep took it), then the reads that judge it: BankQuestion, NotOwnedBy, the origin's presence in the test, and the member's content;
4. a restore: DELETE the member, renumber the group, LockForDraftUse on the origin again directly before the inserts into test_section_units and test_section_questions (AGENTS.md), and insert it at index. A member that leaves as it is: the UPDATE, the renumber, LockForDraftUse on the member, the inserts.

A copy-in is unchanged: the source section's seeding already locks the moved question in the same sweep. The audit entry test.question_moved carries restored, editedCopy and removedMemberId.

Tests. tests/application/tests/group_move_origin_test.go: the origin is recorded and kept across groups; an unedited copy of each kind of question (choice with rich options, true or false, both blank forms, short answer with an image, a listening question with a policy and a transcript) comes back as its original, also after a group save that did not touch it; every kind of edit keeps the copy as a new bank question; a deleted, foreign or already-present original does not fail the move; a gap-bound copy is refused; a published version keeps every frozen row but the link; the foreign key and the index. group_move_origin_race_test.go drives the races, both orders, against a delete of the origin and another test's autosave. tests/group_move_flow_test.go drives the round trip over HTTP, and builder-groups.live.spec.ts in a browser against the real API.

Part 2 of 2 of 20 — Data Model · Previous

---

_{Generated from docs/plan/20-data-model.md. Edit that file in the repository; changes made on this page are overwritten.}