Repository navigation
Plan 20 Data Model Part 3
Part 3 of 3 of 20 — Data Model · Previous
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:
- A
DOblock counts, forclasses.teacher_idandowner_idoftests,questions,question_groupsandmedia_assets, the rows still NULL. The first table that has any stops the migration with23502, whose message,TABLEandCOLUMNname it, before anything changes. Without itVALIDATEwould stop too, naming a relation and no more. -
VALIDATE CONSTRAINTonclasses_teacher_id_not_null,tests_owner_id_not_null,questions_owner_id_not_null,question_groups_owner_id_not_nullandmedia_assets_owner_id_not_null. PG18 validates aNOT NULLthis way, under SHARE UPDATE EXCLUSIVE with one scan of the table, so reads and writes continue. -
DROP TRIGGERontests,questionsandquestion_groups, thenapp.fill_owner_from_created_by(); onmedia_assets, thenapp.fill_owner_from_uploaded_by(); onclasses, thenapp.classes_fill_teacher(). A trigger goes before its function, and there is noIF EXISTSanywhere, 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, whenlock_timeoutturns 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 afterdeadlock_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.
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.
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:
-
Observedates the retired files nothing names and clears the date of those named again, in two statements. -
Duelists the retired files dated at or before now minus seven days, in id order, a hundred at a time. -
Purgetakes one file in its own transaction.SELECT … FOR UPDATE SKIP LOCKEDtakes 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, setspurged_atand writes the audit rowmedia.purgedwith 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,Purgeanswers aPurgeMarkErrorand 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.DeletetreatsNoSuchKeyas 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.
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 quizzivy_maintenance, which 00139_grant_maintenance_role.sql (§55) gave what 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.
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.
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.
DELETEthe section'stest_section_unitsandtest_section_questionsrows for the bank question, renumber the section's units and recompute the standalone ordinals from them, then insert a newquestionsrow withcontext_group_id,context_ordinalandcontext_option_order = 'shuffle'(through the questions module'sCreateGroupMember, with new question, option and blank ids), and renumber the group's members so the copy sits atindex. 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 afterindexup by one and insert the question'stest_section_unitsandtest_section_questionsrows, then recompute the standalone ordinals. The group stays when it empties. -
Between groups, or within one. One
UPDATEofcontext_group_idandcontext_ordinalfor a member that changes group; then arow_number()-style renumber of each touched group's members (unnestof the ids and their new ordinals), asRemoveFromSectionrenumbers 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:
-
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. - The source, the destination and every group the request lists are resolved inside this test, so a
foreign or missing id is
ErrNotFoundbefore any revision is compared. - The touched groups
FOR UPDATE, in id order, each revision compared with the request's. - The touched groups' members
FOR UPDATE, in id order (lockGroupMembers). -
ensureSectionUnitson the section whose units the move rewrites (the source section of a copy-in, the destination of a move-out). A section that has notest_section_unitsyet is seeded first, and seeding takesquestions.LockForDraftUseon 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. -
lockBankQuestion, which isquestions.LockForDraftUseon the bank row. On a copy-in it comes before the row is read, and it is the lockSoftDeletecontends on. On a move-out it comes after theUPDATE, before the insert intotest_section_questionsthat AGENTS.md requires it for: the row lock is held already from step 4, and this re-asserts that the question is not deleted. - Copy-in only:
lockGroupAssetson the copied question's media, the row locks ofmedia.LockForVersionUsein 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. -
SET CONSTRAINTSon the three ordinal constraints,DEFERRED; then the writes above. - After the writes, the same constraints and
group_gap_bindings_memberare setIMMEDIATE, so a violation surfaces inside the move;readGraphreads every touched group back and runsBundle.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 failLockForDraftUse, which filterscontext_group_id IS NULL, with "a question in the test has been deleted", and a later "Remove group" would hard-delete a row theirtest_section_questionsforeign key still restricts (group_reference). The bank list is defined bycontext_group_id IS NULL(questions_owner_bank_idxis 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_answersreferencestest_version_questions, andsource_question_idisON 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_idis the test's owner by the group's ownership, but a member written before ownership existed carries anowner_idbackfilled fromcreated_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.
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:
- the member has
copied_from_id; - the origin is a live bank question:
questions.LockForDraftUsefinds it (not deleted, not a member), and the caller's scope reaches it (BankQuestion, as the copy-in reads its source); - the test's owner owns it (
NotOwnedBy), the invariant the outline writer holds for every standalone question, so the next autosave accepts it; - 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_unitswould refuse it within a section); - 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:
- the gap check and a read of the member's
copied_from_id, under the member's lock from step 4; -
ensureSectionUnitson the destination. When it has to seed, it takesLockForDraftUsein 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 (TestRestoreLocksQuestionsInIdOrderAgainstAnotherTestsAutosavereproduces it with40P01against the unsorted order); -
LockForDraftUseon 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; - a restore:
DELETEthe member, renumber the group,LockForDraftUseon the origin again directly before the inserts intotest_section_unitsandtest_section_questions(AGENTS.md), and insert it atindex. A member that leaves as it is: theUPDATE, the renumber,LockForDraftUseon 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.
app.email_outbox (00128, D-51) holds every message the app has to send. A module enqueues in its own
transaction with email.Outbox.Enqueue and calls Notify after the commit; the API's and the worker's
dispatchers send what is due.
What a row is. template (a slug, the list is Go's), locale (vi or en; the recipient's
users.locale, else vi, unless the caller names one), to_address (one bare address, 3 to 254
characters, copied), to_user_id (ON DELETE SET NULL), params (a JSON object of at most 4096 bytes,
each template's own type, decoded strictly), secret_ciphertext (the AES-256-GCM seal of the one secret a
link carries, under the email-outbox key derived from SECRETS_KEY, with the row id as associated
data, so a ciphertext copied to another row does not open), dedupe_key (unique), deliver_after,
attempts (0 to 10), last_error_code, provider_id, and the two outcomes sent_at and failed_at,
which a check keeps from both being set. No sentence is stored: the subject, text and HTML are rendered
when the row is sent, from the template, the params, the organization's short name (its name when
it has none, as the settings snapshot reads it) and the locale.
Indexes. email_outbox_pending_idx (deliver_after) WHERE sent_at IS NULL AND failed_at IS NULL is
the dispatcher's claim and the earliest-due read its timer waits on; it holds the queue only.
email_outbox_sent_idx (sent_at) WHERE sent_at IS NOT NULL serves the day's budget count.
email_outbox_to_user_idx serves the cascade from users.
The dispatcher. One batch at a time across both processes, under pg_try_advisory_xact_lock(73819, 43) (§28): it counts the sends since local midnight in APP_TIMEZONE; at or over EMAIL_DAILY_LIMIT it
moves every due row to the next local midnight, otherwise it claims up to 10 due rows FOR UPDATE SKIP LOCKED, sends them EMAIL_SEND_RATE a second apart, and records each outcome in the same transaction. A
sent row and a failed row lose secret_ciphertext in that statement. A transient failure backs off 1, 5
and 15 minutes and then hourly; the tenth attempt, or any permanent failure, sets failed_at. A row whose
secret cannot be opened, whose params no longer decode (a wrong type; a field the binary does not know is
ignored) or which no longer renders fails closed with secret_unreadable, params_unreadable or
render_failed. A row of a template the binary does not send is not its row: the claim, the day's
deferral and the timer all skip it, so it stays pending, secret and attempts intact, for the newer binary
the API and the worker run side by side while a deploy rolls out. A release therefore never removes or
renames a template while rows of it can be pending. The timer is armed only for the earliest pending
deliver_after of a known template, so an empty outbox never wakes the database.
Cancelling. Disabling an account (T-R5.9b) calls email.CancelPending(ctx, tx, userID, at) on the disabling
transaction: every pending row whose to_user_id is that account is set to failed_at = at,
last_error_code = 'cancelled' and secret_ciphertext = NULL, so a queued invite or reset link can no longer be
sent and carries no sealed token. The statement claims its rows FOR UPDATE SKIP LOCKED: a row a dispatcher
holds in its batch is sent, which is the same as the message having gone a moment before the disable. Enabling the
account again requeues nothing; an Admin who wants the person back sends a new link through Reset. cancelled
joins the codes a failed row can carry and is not retried.
Retention. The daily housekeeping (T-R5.16, core/jobs.Housekeep) deletes, oldest first and 500 at a time,
the rows whose sent_at or failed_at is more than 30 days old (email.Retention). A pending row is never
touched, however old. The delete reads the table in order of the outcome's instant without an index: the table
holds a day's budget of mail a day for 30 days, a few thousand rows at most, and a partial index on failed_at
would cost every failure a write for a scan that runs once a day. A pruned row frees its dedupe_key: the only
producer today keys on the export id, which never comes round again, but a producer whose key can repeat after 30
days would send again. The app role has full DML on the table (it is not append-only: it is a queue).
What is recorded, and when. A password or Google sign-in stamps last_sign_in_at, last_sign_in_method and
last_active_at in one statement after the session's refresh token is stored. It is not part of that
transaction, which holds the user FOR SHARE: two sign-ins of one account would each have to upgrade the
lock to write and would wait for each other. A failed stamp is logged and the sign-in goes on. changePassword
sets password_changed_at in the statement that sets the hash. A temporary password set by someone else does
not, so a student who has never chosen one reads no "Changed …" line.
What "active" means. An authenticated request that day. adapters.Principals.Resolve, which the permission
gate and the docs gate both call, tells the identity module's StampActivity of each user it resolved and did
not find disabled. The collector lets a user through once every five minutes per instance, and again at the
first request of a new local day, and writes what waits every 30 seconds or at 200 entries, only while any
waits, in one transaction: last_active_at raised with greatest, in user id order so two instances take row
locks in the same order, and the days with ON CONFLICT DO NOTHING. A user deleted in between drops out of both
statements. A failed write is logged and its entries dropped, since activity is a statistic and a queue behind a
database that is down only grows. updated_at moves with every last_active_at write, because the trigger has
no column list; nothing reads it for a user.
Retention. The daily housekeeping (T-R5.16) deletes the days older than 400 days, domain.ActivityDaysKept,
counted back from the start of the local day in APP_TIMEZONE, 500 rows a batch and oldest day first over
user_activity_days_day_idx. R5's readers look back 14 days at most, so nothing the product shows is lost;
last_active_at on the user is never pruned.
Status. domain.StatusOf applies DG-54's order: disabled, must change password, never signed in
(last_sign_in_at IS NULL), active. The contract's AccountStatus is that value, and listStudents filters by
it (accountStatus, any subset), because the order makes the Status facet's states disjoint and two flags
cannot express every pair of them. neverSignedIn is the plain predicate beside mustChangePassword.
00130_create_organization_settings.sql adds app.organization_settings, one row for the whole
installation, keyed id boolean PRIMARY KEY DEFAULT true CHECK (id) (D-53). The migration inserts
the row, named Quizzivy, and revokes INSERT and DELETE from quizzivy_app; the API only
UPDATEs it, under SELECT … FOR UPDATE, with the set_updated_at trigger.
-
Profile.
name(1 to 120 afterbtrim), and nullableshort_name(1 to 40),website(≤ 200),contact_email(3 to 254),front_desk_phone(^[0-9+ ().-]{6,30}$),front_desk_hours(≤ 120) andaddress(≤ 300). An optional text that is empty after trimming is stored asNULL, never as''. -
Logos.
logo_sha256,logo_typeand the_dark_pair. A hash is 32 bytes, a type isimage/pngorimage/svg+xml, and each pair is both NULL or both set. The application writes onlyimage/png;image/svg+xmlis a reserved value no code writes (D-019), and a row that held it would read as no logo. The bytes are not in the database: they are objects atbranding/{sha256}.png, content-addressed, so a key's bytes never change and a new logo is a new URL. -
Sign-in.
google_sign_in,password_sign_in,force_password_changeandjoin_codes_enabledareboolean NOT NULL DEFAULT true;allowed_google_domains text[] NOT NULL DEFAULT '{}'holds at most 50 lower-case domains and no NULL element; an empty list allows every domain.CHECK (google_sign_in OR password_sign_in). -
Retention.
keep_answers_months(12 or 24, NULL for ever),keep_focus_events_months(6, 12, 13 or 24, default 13) andanonymise_disabled_after_months(12 or 24, NULL never). T-R5.17a applies them; this table only holds the choice. -
Revision.
revision integer NOT NULL DEFAULT 1. A save names the revision it read; theUPDATEruns only when it is still the stored one, andrevisiongoes up by one.updated_byisREFERENCES app.users ON DELETE SET NULL.
The lockout judgement is a query over app.users, inside the save's transaction, and writes
nothing. An active account (disabled_at IS NULL) has a way in under a proposed policy when
password sign-in is on and password_hash IS NOT NULL, or when Google sign-in is on, the server
has Google configured and the account has a user_identities row for google whose
email_at_link is in an allowed domain (any domain when the list is empty). A policy is refused
when the actor is among the accounts left with none, or when every active built-in Admin is.
email_at_link is the address at the time of linking, not today's; an Admin whose Google address
changed domain since is counted by the domain they linked with, which is the one sign-in checks.
Audit. Every save writes organization.updated in the same transaction, with fields, the
contract paths of what changed, and the new revision. No value is in the diff.
Cache. Each server keeps the row in memory for at most 30 seconds, refreshed only when something asks and single-flight, and replaced at once on the server that saved. Another server therefore serves the old policy for up to 30 seconds after a change, and an idle server never queries.
Tests. organization/domain/tests/settings_test.go (the validation table),
organization/application/tests (revision conflict, the snapshot's reads and refresh, the
logo-in-store check), organization/repositories/tests (the constraints, the lockout outcomes and
counts, the concurrent saves, the audit row, the grants), organization/http/tests (the public
document's exact keys, the logo headers and caches, the error mapping).
app.data_exports (00134, D-54) holds one row for each request for the centre's data. An Admin asks, the
worker builds a ZIP of CSV files into the private import bucket at exports/{id}.zip, and the requester
fetches it from an app page, up to three times.
What a row is. requested_by (ON DELETE CASCADE), include_answers, status (queued, running,
ready, failed, expired), attempts (0 to 3, the claims so far), storage_key (1 to 200 characters),
bytes (above 0), error_code (1 to 64 characters), and the instants created_at, started_at (the
current claim), completed_at, expires_at (24 hours after ready), downloaded_at and downloads. ready requires
storage_key, bytes and expires_at; failed requires error_code. A read reports a ready row past
expires_at as expired. The daily housekeeping (T-R5.16) makes it true in the table: it deletes the object,
then sets status = 'expired' and storage_key = NULL on the row, guarded by status = 'ready' AND expires_at <= now() so a second run changes nothing. The row stays, with bytes, expires_at and downloads, for the audit
trail of who asked.
One at a time. data_exports_active_idx is a unique index on the constant true over the rows
queued or running. A second request inserts into it, fails with 23505 and answers
EXPORT_IN_PROGRESS; a finished or failed export leaves the index at once.
One an hour (D-021). In the same transaction, before the insert, the request reads the newest row of
requested_by with status <> 'failed' and created_at > now() - 1 hour on data_exports_requested_by_idx;
if there is one it answers RATE_LIMITED with the seconds until that row is an hour old. A failed row never
counts, and a refused request leaves no row, so neither a 409 nor a 429 spends the hour. Two requests of one
actor at once both pass the read, and the active index lets only one insert.
The claim. One transaction first fails the running row whose lease (30 minutes from started_at) has
lapsed and whose attempts has reached 3 (WORKER_INTERRUPTED), then takes the oldest queued row or
lapsed running row FOR UPDATE SKIP LOCKED, sets started_at = now() and adds one to attempts. Complete,
fail and release each name the attempt they hold and change the row only while it is running under that
attempt, so a worker whose lease was taken changes nothing. Complete also enqueues data_export_ready into
email_outbox (§49) in the same transaction.
The fetch (D-024). UPDATE … SET downloaded_at = coalesce(downloaded_at, now()), downloads = downloads + 1 WHERE requested_by = $actor AND status = 'ready' AND expires_at > now() AND downloads < 3 RETURNING storage_key
is the whole check; it signs the address and audits data_export.downloaded before it commits, so a failure to
sign counts nothing. downloads smallint NOT NULL DEFAULT 0 is checked between 0 and 3, and downloaded_at keeps
the first fetch. The server cannot tell whether a transfer finished (the browser reads the object from storage),
so a failed transfer may be tried twice more without building another ZIP. Another admin's export finds no row
for them and answers as a missing one.
Reading the tables. The worker reads every file in one REPEATABLE READ, read-only transaction with a
20-minute statement timeout. Each file's columns are listed in exports/domain/files.go and the snapshot
fails a query whose columns differ, so a new column on a table never reaches a file unnoticed. A time is
written as ISO 8601 in UTC and a boolean as true or false; NULL is an empty cell.
Tests. exports/domain/tests (the catalogue, vi and en for every column, no secret column names, the
README), exports/application/worker/tests (the flow with fakes), exports/repositories/tests (the index,
the claim and its races, the lease, the three fetches, the audit rows, the notice, every query's columns),
server/tests/data_export_flow_test.go (API, worker and MinIO), data_export_heap_test.go (50,000 attempts).
00135_create_set_password_tokens.sql adds app.set_password_tokens (D-55). A link is the way an account
chooses a password without anyone typing one for it: an invite carries a new account's first password, a reset
any later one. The token is 32 random bytes in unpadded base64url; the row keeps sha256(token) and nothing
that could be presented. The raw token lives in two places only, the link in the email and the sealed secret
of the outbox row that carries it, which is cleared when the row is sent or fails (§49).
States. A link is usable while used_at and revoked_at are NULL, now < expires_at and its account is
not disabled. Every other state is one domain.ErrSetPasswordLinkInvalid, and the HTTP layer answers it as one
410 SET_PASSWORD_LINK_INVALID with one body, so nothing says which state a link is in. A link is revoked by
issuing another to the same account (RevokeUnused), using one (Claim revokes the account's other unused
links), or disabling the account: Students.Update runs the same revokeUnusedLinks in the transaction that
sets disabled_at, beside the revoke of the refresh families, and the anonymisation command revokes them (§55).
Enabling the account again issues nothing, so a disable then an enable within 24 hours leaves the old link
revoked and an Admin sends a new one through Reset. The lookup's AccountDisabled check and the claim's
disabled_at IS NULL stay as the second line, for a link issued in the instant before a disable.
Issuing. IssueSetPasswordLink is a command other commands call with a domain.LinkTx, the writes of one
transaction the caller owns: Recipient, RevokeUnused, Insert, EndSessions, Enqueue, Audit and
AfterCommit. repositories.SetPasswordLinks.InTx begins that transaction for a caller that has none;
Writes(tx) returns the same writes over a transaction the caller already holds (T-R5.9a creates the account
in it), and the function that runs what was registered with AfterCommit, which the caller runs after its own
commit and never after a rollback. That is where Principals.Forget and the dispatcher's wake-up wait, so
neither fires for a transaction that rolled back. Enqueue maps the outbox's ErrNoSecretsKey to
domain.ErrSetPasswordUnavailable, which the HTTP layer answers 501, as media does without storage. Order
inside the transaction is: read the recipient, revoke unused links, insert the link, (for a reset someone else
asked for) end sessions, enqueue the message, audit.
Claiming. Claim is one transaction: UPDATE set_password_tokens SET used_at … WHERE token_hash = $1 AND used_at IS NULL AND revoked_at IS NULL AND expires_at > $now RETURNING user_id, purpose; then the account's
password hash, must_change_password = false, password_changed_at and session_epoch + 1 (refused, and the
whole claim rolled back, for a disabled account, so a refused claim leaves the link unused); then the account's
other unused links revoked, its refresh tokens revoked, and user.password_set written as the account itself
with {"purpose": …}. The lock order is the link, then the user, then its refresh tokens, the order issuing
takes them in, so issuing and using links at once cannot deadlock. The caller forgets the principal, reloads the
account and issues the session (SignInSetPasswordLink, stamped like any sign-in, §50). A token that is
unknown, used, revoked, expired or for a disabled account is refused by Find and Usable before the password
is hashed, so an invalid token costs the server no argon2 work.
Self-service. RequestSetPasswordLink issues a reset to the signed-in account with By set to itself and
KeepSessions, writes user.password_link_requested in the same transaction, and ends nothing: the account
asked while signed in on purpose, and using the link ends the other sessions. A Google-only account keeps its
user_identities row, since setting a password adds a way in and removes none.
Not here. A reset does not clear the account's current password; T-R5.9b's "a link reset clears the password" belongs to that task.
Retention. The daily housekeeping (T-R5.16) deletes a link 30 days after it ended, by being used, revoked or
expiring, whichever came first (least(used_at, revoked_at, expires_at), domain.SetPasswordLinksKept), the
earliest first and 500 at a time. A link that can still be used has not ended and is never touched, and a token
whose row is gone is refused as any unknown one is. The batch locks its rows FOR UPDATE SKIP LOCKED: a link that
was never revoked is also a target of RevokeUnused, and a delete that waited on rows in its own order could
deadlock with it; a locked row is left for the next run. The scan has no index of its own, because the table holds the
links of 30 days; the cascade from users keeps using set_password_tokens_user_idx. The anonymisation command
revokes a user's unused links (§55) and leaves the rows to this sweep, which removes them 30 days on like any other.
Tests. identity/domain/tests/setpassword_test.go (token shape, the purposes, the usable states),
identity/application/tests/set_password_test.go (every path against fakes: the order of the writes, forgets
after the commit, the policy, a disabled account, an unusable link costs no hash) and
set_password_db_test.go (the real repository, the outbox and the app role: hash-only storage, a sealed token,
a reissue revokes, eight concurrent submits set once, expiry at exactly 24 hours, a rolled-back issue leaves no
link, mail or wake-up, issuing and using at once do not deadlock), identity/http/tests/set_password_test.go
(byte-identical 410 bodies in both languages, the 200 with its cookie, the 400, 422 and 501 mappings),
platform/db/tests/set_password_tokens_test.go (each constraint fires, the cascade and the SET NULL) and
core/router/tests/set_password_limits_test.go (a class of 40 behind one address, the per-address and
per-token limits, a weak password spends the token's budget, the per-user limit of the request).
identity/application/tests/prune_set_password_links_test.go and prune_set_password_links_db_test.go (the cutoff
and the limit against a fake store; against Postgres, ten links in every state either side of 30 days, a live link
untouched, the earliest first from rows inserted newest first, the edge to the microsecond, a row another transaction
holds skipped without waiting, and a second run changing nothing).
00136_create_user_import_jobs.sql and 00137_create_user_import_rows.sql add app.user_import_jobs (D-56) and
app.user_import_rows (D-57). An Admin uploads a CSV or XLSX file of people; the API reads it, judges every row
against what the Admin may give, and stores the job and the rows that passed in one transaction; the worker then
creates the accounts a few at a time and, for a sheet job, hands the temporary passwords out once.
Judging a file. The columns are full_name and email (required) and role and class (optional), found in
the header whatever their case or order; any other column is ignored. A header that lacks a required column, or
names one twice, is refused as a whole. Every other rule refuses one cell, so one pass shows everything to correct,
and a row with a refused cell is counted once in invalidCount and never stored. The cells are composed (NFC) and the
name's whitespace collapsed before they are judged.
| Code | Field | Meaning |
|---|---|---|
REQUIRED, TOO_LONG, INVALID
|
full_name, email
|
empty; over 200 characters (254 for an address); a control or invisible character, or an address that is not a bare local@domain
|
FORMULA_LIKE |
email |
a valid address that starts with a character a spreadsheet reads as a formula (=, +, -, @, a tab or carriage return, or a full-width form, after any spaces); the sheet would write it '+x@…, which does not sign in (D-039) |
DUPLICATE_IN_FILE |
email |
the address, in any letter case, appeared in an earlier row, whether that row was refused or not |
UNKNOWN_ROLE, AMBIGUOUS_ROLE
|
role |
no role has that name or key; two roles do (a custom role named like a built-in key) |
ROLE_NOT_ALLOWED |
role |
the role holds a permission the importer lacks (the subset rule of §5) |
ROLE_NOT_GIVABLE |
role |
the Assistant, which no one is given. The plan called this ROLE_NOT_ASSIGNABLE; the code generator prefixes a constant that two enums share, and the other is an ErrorCode, so the row code has its own name |
UNKNOWN_CLASS, AMBIGUOUS_CLASS
|
class |
no active class has that name; two do |
CLASS_NOT_ALLOWED |
class |
the importer lacks scope.all, or the row's role is not a student target |
A role is named by its built-in key (student), its name (Học viên; the accents count) or a custom role's name,
compared without case or extra whitespace; empty takes the dialog's default, which is the built-in Student or
Teacher role and one the importer must be able to give, or the whole request is refused 403 TARGET_ROLE_NOT_ALLOWED. An address that already belongs to an account, of any state, is not refused: its row is
stored settled skipped_exists, counted as existingEmails, and the account is never changed. A file in which no
row would create an account is refused 422 USER_IMPORT_NOTHING_TO_CREATE.
Reading a file. The format is chosen by the file's first four bytes (PK\x03\x04 is a workbook), never by its
name or the browser's type. The platform's reader (T-R5.13) takes it under its default limits, 5 MiB, 2,000 rows,
20 columns and 1,000 characters a cell; each of its errors has its own code and a vi/en message that says what to
do, and no message carries the reader's error text, since that can hold up to 64 KiB of the file. One file is held
at a time (a one-slot semaphore), a waiting upload that is cancelled leaves it.
Creating the job. createUserImport re-reads and re-judges the file; the preview is never trusted. A
requestId the caller has used returns that job without reading the file. Otherwise the job and the accepted rows
are inserted in one transaction with ON CONFLICT (created_by, request_id) DO NOTHING, so two requests at once
store one job, and user_import.requested is audited with the counts and the format, never with the file's names or
addresses. Both deliveries need SECRETS_KEY (a link by email and a sheet's passwords are sealed); without it, or
without the import worker, the operations answer 501. A sheet also needs password sign-in on
(422 PASSWORD_SIGN_IN_DISABLED).
Reading a job. getUserImport finds the job by id and created_by; another user's job is a missing one.
sheet is null for an email job and otherwise says whether the sheet can still be downloaded: the job is done,
created an account, has not been downloaded and has not passed sheet_expires_at.
Running a job. The worker claims the oldest queued job, or a running one whose started_at is over 30
minutes old (ImportLease), in one statement FOR UPDATE SKIP LOCKED, and adds one to attempts. A job that has
been claimed three times and is claimable again is failed WORKER_INTERRUPTED instead, in the same transaction
that erases its sealed passwords. A run is cut off after 25 minutes (ImportRunLimit) and the job goes back to
queued with the attempt spent; a shutdown puts it back and takes the attempt away. Every write the worker makes to
a job names the attempt it holds (status = 'running' AND attempts = $n), so a worker whose claim was taken over
writes nothing. The rows still pending are the work left, so a resumed job needs no state beyond the table.
A batch of 25 rows is one transaction. It locks the job row, locks each role of the batch FOR SHARE and compares
its revision with the one the worker read the role at (a role edited in between sends the worker to read it again,
three times at most), inserts the account with ON CONFLICT ((lower(email))) DO NOTHING (no row back is a
skipped_exists), adds the class membership (joined_via 'admin', added_by the importer; a class archived since
the job was stored is left out, the account still counts as created and the row forgets the class), issues the invite for an email job,
audits user.created {role, roleId, delivery, via: import, jobId} with the importer as the actor, settles the
row and moves created_count and skipped_count. The batch that makes them reach total_rows sets done,
completed_at and, for a sheet job that created an account, sheet_expires_at 24 hours on. The importer is read
again before every batch: a disabled account or one that lost a right fails the job IMPORTER_NOT_ALLOWED, and a
sheet that can no longer be sealed or a sign-in policy that turned passwords off fails it DELIVERY_UNAVAILABLE.
The accounts made before a failure stay. A failed job's sealed passwords are erased in the transaction that fails
it, so those accounts are reached by a password reset.
The hand-out sheet. POST /admin/user-imports/{id}/sheet locks the requester's job row FOR UPDATE, answers
SHEET_UNAVAILABLE (410) unless the job is done, delivered by sheet, created an account, has not been
downloaded and is within sheet_expires_at, opens each created row's secret_ciphertext in ordinal order,
sets every ciphertext NULL, sets sheet_downloaded_at (WHERE sheet_downloaded_at IS NULL, one row, as a second
guard), writes user_import.sheet_downloaded (the row count, never a password) and commits, so two requests at once
give the file to one and an unreadable ciphertext leaves the sheet whole. A job with no sealed row left is
SHEET_UNAVAILABLE and nothing is written. The role and class names on a line are read at that moment; the class
column is blank for a student the worker did not place in it.
Erasing what nobody took. The housekeeping step import_sheets (the API's 24-hour tick, D-038) clears
secret_ciphertext on the rows of the sheet jobs whose sheet_expires_at is at or before the sweep's time and that
were not downloaded, at most a batch of rows at a time, locking each job FOR UPDATE SKIP LOCKED so a download in
flight finishes first. It changes nothing else; no index serves it, since the jobs are few.
Retention. The rows keep full names and addresses for as long as the job exists, so the daily housekeeping
(T-R5.16, the step import_jobs) deletes a job 30 days after completed_at (domain.UserImportsKept), the earliest
first and 500 at a time, and the ON DELETE CASCADE of user_import_rows.job_id takes its rows. A job that is
queued or running has no completed_at and is never touched. The accounts, the classes and the audit trail
stay. The job is locked FOR UPDATE SKIP LOCKED, the lock TakeSheet and EraseExpiredSheets take first, so a
download in flight is passed over and left for the next run. No index serves it, since the table holds the jobs of
30 days. The step is attached whether or not the import worker is configured, so a server that turned imports off
still removes what it stored. An account anonymised before its job's 30 days are up keeps its old name and address
in the job's row until then. Known limit (D-044, #701 QC-H-18, H-19, H-22): the delete locks the job SKIP LOCKED
but not its rows, which a class delete or a user delete locks (their SET NULL) in another order, so the prune can wait
for them and, with a class or user delete running in the same moments, be chosen as a deadlock victim (40P01); the
step reports it and the next run goes on, and if the delete is the victim its action fails and is repeated. The same
cycle exists between a class delete and a user delete without the prune. If it shows in a production log, delete the
rows first in (job_id, ordinal) order with SKIP LOCKED and then the empty jobs, and fix the row order of the two
deletes. getUserImport and the sheet answer 404 for a removed job, as for any unknown id, which
the Users screen (T-R5.9b) has to read as "no longer kept".
Tests. identity/application/tests/prune_user_imports_test.go and prune_user_imports_db_test.go (the cutoff and
the limit against a fake store; against Postgres, jobs in every state either side of 30 days with their rows, an
open job 60 days old kept, the account an import made untouched, the earliest first from jobs inserted newest first,
the edge to the microsecond, a job another transaction holds skipped without waiting) and
server/tests/housekeeping_flow_test.go (the start-up run, also with the import worker off). identity/domain/tests
(the rules table, the sniff, the header, the file name), identity/application/tests/user_imports_db_test.go
(preview counts, the stored rows and their order, the audit diff, replay, another user's job, nothing to create, the
subset rule, classes, deliveries, 2,000 rows, one file at a time), identity/http/tests/user_imports_test.go (the form,
each reader error's code and both languages, no echo of the file), core/adapters/tests/user_import_tables_test.go,
platform/email/tests/budget_test.go, server/tests/user_import_api_flow_test.go (the real router: 401, 403, 413,
422, 501, the rate limit). The worker and the sheet: identity/application/tests/user_import_worker_db_test.go
(batches, the lease and its loss, the third claim, each failure code, a role edited in between, an archived class,
the sheet's single use) and server/tests/user_import_flow_test.go (the router and the worker runner together).
00139_grant_maintenance_role.sql gives quizzivy_maintenance what cmd/maintenance uses and nothing else (D-60).
scripts/provision-db.sh creates the role (LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE CONNECTION LIMIT 2,
password from QUIZZIVY_MAINTENANCE_PASSWORD) and grants it CONNECT, as it does the other two. On a database
that is not local it sets that password and no other, and grants no CREATEDB; a local run keeps the development
defaults. The migration
fails when the role is missing, so there is no conditional grant. The role owns nothing, creates nothing, has
no privilege on a sequence (an identity column's insert needs none) and none on ai_usage_ledger, and holds
INSERT alone on audit_log and no UPDATE or TRUNCATE on attempt_events. It holds USAGE on schema app
and on schema public, the second because app.immutable_unaccent calls public.unaccent and the trigram
indexes on a user's name and email call it on every write: scripts/provision-db.sh denies public to
everyone else, and the first role-world run of the accounts step failed on exactly that. It has no CREATE
anywhere. Down revokes every grant the role holds in the schema, the sequences and USAGE on both schemas. The CONNECTION LIMIT is cluster-wide, so
the command's pool is capped at two and the tests that connect as the role do not run at the same time.
Which answers are due (D-035). shared/retention.EndedBefore over AttemptJoins reads an attempt's own
student's close, greatest(least(closed_at, closes_at), assignment_student_overrides.closes_at)
(schedule.CloseOf("o")), so an assignment reopened for one student does not lose that student's answers while it
is open to them. The events step reads the same close (retention.ClosedBefore, D-041): the 00029 command read
coalesce(closed_at, closes_at) on the assignment alone, which predates R4's per-student closes (00101), so it was a
rule nobody had revisited and not a decision, and two steps that disagree about when an assignment closed would be
harder to explain than one. The step pages by (received_at, id) on attempt_events_retention_idx, each batch
starting after the last event the one before took, so events an extension keeps alive are read once and not again by
every later batch (QC-RS-19: 150,000 held events cost the unpaged step 5 minutes 41 seconds for 446,900 events, and 7
seconds paged).
The guard on app.users (D-042). The role's UPDATE on the identity columns is what anonymising needs and is also
what a leaked credential would use to take an account over. app.users_maintenance_guard() is a SECURITY INVOKER
trigger function with a pinned search_path, no EXECUTE for PUBLIC, fired BEFORE UPDATE FOR EACH ROW WHEN (current_user = 'quizzivy_maintenance'); it raises 42501 unless the new row is anonymised:
email = 'anonymous-' || id || '@anonymous.invalid', password_hash IS NULL, must_change_password = false,
disabled_at IS NOT NULL, and phone, avatar_key and display_name NULL. That is exactly what the anonymise
statement writes and all it writes (full_name is a name, not a credential, and is not pinned). The role holds no
INSERT on user_identities or set_password_tokens, so it cannot reopen an anonymised row. Every other role pays one
current_user test. Which accounts may be anonymised is not the trigger's rule: the sweep and anonymize-user
skip an account that owns classes or content (ownsContent, which reads the six owner columns the role holds
SELECT on) or has an attempt in progress, and users_last_admin refuses the last active Admin. The credential can
therefore anonymise any other account by direct SQL, a teacher's included, and operations.md states that among the
costs of a leak (QC-RS-17; the owner chose on 2026-10-16 not to enforce the owner rule in the trigger).
Anonymisation clears users.avatar_key and does not delete the photo object, which T-R5.16's housekeeping removes
for anonymised accounts (email = 'anonymous-' || id || '@anonymous.invalid', §56).
| Table | Privilege | Columns | Used by |
|---|---|---|---|
organization_settings |
SELECT |
the three retention columns | the sweep, retain-integrity
|
attempts |
SELECT, UPDATE
|
id, assignment_id, student_id, status, submitted_at, deadline_at; deadline_at
|
the sweep and anonymize-user read; window-schedule extends |
assignments |
SELECT, UPDATE
|
id, created_by, published_at, closed_at, closes_at; closes_at
|
the same |
assignment_student_overrides |
SELECT, UPDATE
|
assignment_id, student_id, closes_at; closes_at
|
the answers and events steps read a student's own close; window-schedule (T-R4.12b) extends |
attempt_answers |
SELECT, UPDATE
|
attempt_id, question_id, payload; payload
|
the answers step |
attempt_events |
SELECT, DELETE
|
id, attempt_id, received_at |
the events step |
users |
SELECT, UPDATE
|
id, email, full_name, display_name, role_id, disabled_at; email, full_name, display_name, phone, avatar_key, password_hash, must_change_password, disabled_at
|
the accounts step |
student_like_roles, roles, role_permissions
|
SELECT |
all | the placeholder name; the AI status read |
user_identities, refresh_tokens
|
SELECT (user_id), DELETE
|
the accounts step | |
set_password_tokens |
SELECT, UPDATE
|
user_id, used_at, revoked_at; revoked_at
|
the accounts step revokes unused links |
notifications |
SELECT, INSERT, UPDATE, DELETE
|
id, user_id, kind, params, target, dedupe_key; user_id, kind, params, target, dedupe_key; kind, params, target, read_at
|
the accounts step deletes; the AI commands upsert |
notification_preferences |
SELECT |
user_id, event, in_app |
the AI commands' notices |
classes, tests, questions, question_groups, media_assets
|
SELECT (teacher_id) or (owner_id)
|
the owner check | |
audit_log |
INSERT |
all | every command |
maintenance_windows |
SELECT, INSERT, UPDATE
|
all; starts_at, ends_at; ends_at, cancelled_at
|
the window commands |
class_join_codes |
SELECT, UPDATE
|
id, class_id, revoked_at, code_ciphertext, key_id, lookup_scheme; code_hash, code_ciphertext, key_id
|
rekey-join-codes |
ai_credit_periods, ai_teacher_credits
|
SELECT, INSERT, UPDATE
|
all | ai-credits |
ai_settings, ai_credit_requests
|
SELECT, UPDATE
|
all | ai-credits |
ai_reservations |
SELECT, DELETE
|
all | ai-reservations-sweep |
platform/db/tests/privileges_test.go reads the role's effective column privileges from the catalogue and
compares them with its own list (maintenanceGrants), which this table mirrors, in both directions: a grant nothing uses fails as surely as a missing one.
What column privileges cost the SQL. FOR UPDATE and FOR SHARE need UPDATE on the table, so a row is
locked only where the role holds it; RETURNING col needs SELECT on col, so a data-modifying CTE that feeds
audit_log returns nothing the role cannot read (RETURNING 1, or no RETURNING, needs none); and
ON CONFLICT … DO UPDATE needs SELECT on its inference columns and on every EXCLUDED column it reads. A
missing grant on notifications is not an error to the AI commands: cqrs.Announce swallows a notice's
failure, so the teacher was simply not told, and the role test asserts that the notification exists.
Settings to cutoffs. keep_answers_months, keep_focus_events_months and
anonymise_disabled_after_months (§51) become (now() at time zone 'UTC' - N months) at time zone 'UTC',
read once per step, and every comparison is a strict <. A row exactly at the cutoff stays.
Answers. An attempt is eligible when it has ended, status IN ('submitted', 'timed_out', 'graded', 'voided'), coalesce(submitted_at, deadline_at) is before the cutoff (a voided attempt that was never submitted
ends at its deadline) and the close of its assignment for its student, schedule.CloseOf("o") (greatest(least(closed_at, closes_at), override.closes_at)), is too. The rule
is shared/retention.EndedBefore, which the sweep, its dry run and the settings page's impact count (T-R5.17b)
all build their SQL from, and the statuses are listed so that a status added later has to be considered. An
attempt in progress is never eligible, however overdue, because the lazy close that ends it scores it from the
stored answers; the report counts those as overdueInProgress (OverdueInProgressBefore). Each eligible
answer whose payload is not already {"removed": true} gets that payload (D-61); the scores, comments and grader stay. A batch
is -batch attempts picked by id after the last one (a keyset, not an offset), their answers locked
FOR UPDATE OF x SKIP LOCKED, and one retention.answers_removed {cutoff, attempts, answers, databaseRole}
entry for the batch when it removed any. An answer row another transaction holds is skipped and taken by the next run.
Events. RetainEvents with keep_focus_events_months: an event is eligible when received_at is before the cutoff
and the assignment has closed for the attempt's student before it (retention.ClosedBefore, the same close as the
answers, D-041). At the default 13 it deletes what 00029's command deleted except the events of a student whose
extension is still open. A batch is -batch events in (received_at, id) order, and one integrity.retained entry
for the batch.
Accounts. A user disabled before the cutoff, not already anonymous (email = 'anonymous-' || id || '@anonymous.invalid'), is anonymised unless they own a class, a test, a question, a group, a media asset or an
assignment, or have an attempt in progress. Owners and the in-progress are counted in the report, not touched.
users.created_by is not read: an Admin reaches an account they created through the Users directory.
The statement is one data-modifying CTE per batch: it deletes the notifications whose params hold the user's
full or display name as a value (the names are a MATERIALIZED set the notifications are joined to; written as
= ANY(array) inside a correlated EXISTS, the planner re-aggregated the 500 users' names for every
notification and a batch took 256 seconds over 50,000 notifications, where the join takes 0.4), the Google link and the refresh tokens, revokes the unused set-password links
(revoked_at = now() where user_id matches and used_at and revoked_at are NULL, the statement a disable
runs, so §53's states stay true and the role needs no DELETE there), rewrites the user, and writes
user.anonymized for each. The student placeholder name is for a user whose role is in student_like_roles,
"Người dùng đã ẩn danh" for any other. anonymize-user runs the same statement for one id on request, without
the cutoff, and refuses an owner or an attempt in progress; the users_last_admin trigger refuses the last
active Admin, because anonymising disables the account.
Audit. Every sweep entry has actor_user_id NULL and databaseRole in its diff, so the log shows which
credential did it. attempt_answers is an All-only entity in the audit console (audit/domain/categories.go), so only the All tab
lists it.
Tests. core/maintenance/tests/retention_test.go runs each step at, just before and just after its cutoff
in two worlds, one transaction on a shared scratch database as the owner and one scratch database per test with
the role's own pool, so a missing grant fails a test and not production. The defaults touch no answer and no
account; owners and in-progress attempts are skipped; a notification naming an anonymised user is gone; a second
run changes nothing; a dry run writes nothing and runs read-only; and every audit entry has a NULL actor.
role_test.go runs the window commands, rekey-join-codes, the AI commands and retain-integrity as the role.
A scratch database is created by the migrate role (CREATEDB, scripts/provision-db.sh), migrated, and dropped
with the test; TEST_MAINTENANCE_DATABASE_URL names the role, and in CI its absence fails the test.
app.avatar_sweeps (00140_create_avatar_sweeps.sql, D-59) holds one row for each anonymised account whose
objects under avatars/{user_id}/ the daily housekeeping has deleted: user_id uuid PRIMARY KEY REFERENCES app.users(id) ON DELETE CASCADE and swept_at timestamptz NOT NULL. The app role may read and add rows and
cannot change or delete one; the row goes with the account. Down drops the table (IF EXISTS).
Which accounts are due. repositories.AvatarSweeps.Due reads the accounts in the shape anonymisation leaves
(core/maintenance/anonymize.go, and what users_maintenance_guard requires of the maintenance role's writes):
email = 'anonymous-' || id || '@anonymous.invalid', disabled_at IS NOT NULL and avatar_key IS NULL, with no
row here, in id order, at most limit. An account re-enabled or given a photo again is not due. The read is a scan of
users with an anti-join on this table's key; no index serves the predicate, which costs little at a centre's size.
Marking. identity.SweepAvatarObjects deletes every object under the account's prefix first and then
INSERT … ON CONFLICT (user_id) DO NOTHING, so a second machine that swept the same account keeps the first mark,
and an account whose objects could not be listed or deleted has no row and is tried at the next run. A server
without media storage has no step, so it writes no row and a bucket configured later still finds every account. A
photo written under a marked account's prefix later is not removed (anonymisation leaves no way to write one).
Part 3 of 3 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.
Generated from the repository's documentation on every push to develop.
Start here
Product
Implementation plan
- 00 Architecture overview
- 10 Phase 0: Scaffold and contract
- 11 Phase 1: Auth, join, shells
- 12 Phase 2: Admin authoring
- 13 Phase 3: Assign and take
- 14 Phase 4: Grade and results
- 15 Phase 5: Hardening
- 16 Phase 4.5: The student at a desk
- 17 Word import: milestone plan
- 18 Word import: teacher workflow
- 19 Versioned content contract
- 20 Data model
- 30 Risk register
- 40 Open items
- 50 Non-functional requirements
- 60 Backend architecture
- 91 September 2026 issue remediation
- 92 Student layouts and interaction rules
Redesign (Phase R)
- 70 Redesign overview
- 71 R1: Foundations (v0.7.0)
- 72 R2: Access (v0.8.0)
- 73 R3: Student console (v0.9.0)
- 74 R4: Teacher workspace (v0.10.0)
- 74d D4: Deck update (v0.11.0)
- 74e E1: Moving a question across a group
- 75 R5: Admin console and email (v0.12.0)
- 76 R6: Question types and scoring (v0.13.0)
- 77 R7: Collaboration (v0.14.0)
- 78 R8: Schedule (v0.15.0)
- 79 R9: Insights (v0.16.0)
- 80 R10: Learn (v0.17.0)
- 81 R11: Landing, leads, v1.0 (v1.0.0)
Setup and operations
- CI
- Deploying
- DNS for quizzivy.com
- Google OAuth client
- Production operations
- Cloudflare R2 bucket
- Word import worker
- R4 rehearsal on a Neon branch
Design
Team
- Agent team
- Verification environment
- Team ledger
- R4 readiness
- R4 verification strategy
- T-R4.51 browser verification
Code READMEs