Spec 13 Database Design

Section of Quizzivy — Frontend Portal & Data Model Specification · Previous · Next

13. Database design (PostgreSQL 18)

13.1 Binding references

Two authorities govern all schema and query work. Where they conflict, the PostgreSQL docs win.

1. Neon postgres-best-practices agent skill — https://github.com/neondatabase/postgres-skills. Install before writing any DDL:

   npx skills add neondatabase/postgres-skills
   

   An Agent Skill package (skills/postgres-best-practices/SKILL.md + references/schema-design.md), curated by Postgres practitioners including a PostgreSQL Core Team member. Its schema-design, indexing, and migration guidance is binding for this project.
2. PostgreSQL 18 official documentation — https://www.postgresql.org/docs/18/index.html. Version-specific behaviour is checked here, not against training-data recollection. Target PostgreSQL 18; do not write DDL that silently degrades on 16/17.

Rule for the agent: load the skill before proposing DDL or non-trivial SQL, and cite the specific docs section for any PG18-specific construct used.

13.2 Conventions

• Schema app, not public. REVOKE CREATE ON SCHEMA public FROM PUBLIC.
• Tables plural snake_case; columns snake_case; no Hungarian prefixes.
• PKs: uuid PRIMARY KEY DEFAULT uuidv7() — PG18's built-in time-ordered UUID avoids the B-tree fragmentation random v4 causes on insert-heavy tables. Use uuidv4() only where hiding creation time genuinely matters.
• timestamptz everywhere; never bare timestamp. Store UTC, render Asia/Ho_Chi_Minh.
• text over varchar(n) unless a real business constraint exists; enforce with CHECK when it does.
• Scores: numeric(8,2). Never float.
• PG enums where the set is genuinely closed (attempt_status); lookup tables otherwise, such as roles (R2), which custom roles keep open. Adding an enum value is easy, removing one is not.
• Every FK has an explicit ON DELETE. Default RESTRICT; CASCADE only for true owned children.
• Every FK column used in a join or filter gets an index — Postgres does not index FKs automatically.
• created_at timestamptz NOT NULL DEFAULT now(); updated_at via trigger, not application code.
• Soft delete only where history matters (questions, tests, media_assets); everything else deletes for real.

13.3 Core tables

Sketch, not final DDL. The agent produces real migrations after loading the skill.

CREATE SCHEMA app;

CREATE TYPE app.test_status    AS ENUM ('draft','published','archived');
CREATE TYPE app.question_type  AS ENUM ('single_choice','multiple_choice','true_false','fill_blank','short_answer');
CREATE TYPE app.attempt_status AS ENUM ('in_progress','submitted','timed_out','graded','voided');
CREATE TYPE app.media_kind     AS ENUM ('image','audio');
CREATE TYPE app.join_source    AS ENUM ('admin','join_code');

CREATE TABLE app.users (
  id            uuid PRIMARY KEY DEFAULT uuidv7(),
  email         text NOT NULL,
  full_name     text NOT NULL,
  role_id       uuid NOT NULL REFERENCES app.roles(id) ON DELETE RESTRICT,
  password_hash text,                              -- NULL = Google-only account
  must_change_password boolean NOT NULL DEFAULT false,
  disabled_at   timestamptz,
  session_epoch integer NOT NULL DEFAULT 0 CHECK (session_epoch >= 0),
  created_by    uuid REFERENCES app.users(id) ON DELETE SET NULL,  -- NULL = unknown or self-joined
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_lower_key ON app.users (lower(email));

ALTER TABLE app.users
  ADD COLUMN display_name text CHECK (char_length(btrim(display_name)) BETWEEN 1 AND 80),
  ADD COLUMN phone text CHECK (phone ~ '^[0-9+ ]{6,20}$'),
  ADD COLUMN avatar_key text,
  ADD COLUMN locale text CHECK (locale IN ('vi', 'en')),
  ADD COLUMN time_zone text CHECK (char_length(time_zone) BETWEEN 1 AND 64),
  ADD COLUMN preferences jsonb NOT NULL DEFAULT '{}'::jsonb
    CHECK (jsonb_typeof(preferences) = 'object')
    CHECK (octet_length(preferences::text) <= 8192);

CREATE TABLE app.user_identities (
  id               uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id          uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  provider         text NOT NULL CHECK (provider IN ('google')),
  provider_user_id text NOT NULL,                  -- Google `sub`, immutable
  email_at_link    text NOT NULL,
  created_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider, provider_user_id)
);
CREATE INDEX ON app.user_identities (user_id);

Set-password links (§5.4), from R5 (00135_create_set_password_tokens.sql, docs/plan/20-data-model.md D-55, §53):

CREATE TABLE app.set_password_tokens (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id    uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  token_hash bytea NOT NULL,                       -- SHA-256 of a 256-bit token
  purpose    text NOT NULL CHECK (purpose IN ('invite', 'reset')),
  created_by uuid REFERENCES app.users(id) ON DELETE SET NULL,
  expires_at timestamptz NOT NULL,
  used_at    timestamptz,
  revoked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT set_password_tokens_token_hash_key UNIQUE (token_hash),
  CHECK (length(token_hash) = 32),
  CHECK (expires_at <= created_at + interval '24 hours')
);
CREATE INDEX set_password_tokens_user_idx ON app.set_password_tokens (user_id);

A link is used by one UPDATE … SET used_at that names the hash and finds it unused, unrevoked and unexpired, so two submits of one link set the password once. used_at, revoked_at and expiry read alike to the lookup. The app role may read, insert and update a link, and the table stores no secret a reader could present.

Activity (§5.7, DG-54), from R5. 00131_add_users_activity.sql adds four nullable columns to users: last_active_at, last_sign_in_at, last_sign_in_method (CHECK IN ('password','google','set_password_link')) and password_changed_at. It backfills last_sign_in_at and last_active_at from the newest refresh token, attempt start and audit entry as actor, with the updated_at trigger off, so an account used before R5 does not read as never signed in. 00132_create_user_activity_days.sql adds user_activity_days(user_id, day), primary key (user_id, day), user_id cascading with the user and an index on day: one row for each local day in APP_TIMEZONE on which the user made an authenticated request, which the Admin's chart counts (§13.8). 00138_index_users_search.sql adds users_search_trgm_idx, a gin trigram index on app.immutable_unaccent(lower(full_name || ' ' || email)), the expression the Users search compares, and ends with ANALYZE app.users.

Email (R5, 00128_create_email_outbox.sql, docs/plan/20-data-model.md §49). email_outbox holds every message the app sends: the recipient (to_user_id, set NULL with the user, and to_address), a template key, the locale, params (an object of at most 4096 bytes), an optional secret_ciphertext, a unique dedupe_key, deliver_after, attempts (at most 10), the last error's code, the provider's id and sent_at or failed_at, never both. The template list is the code's, not a check, so a new template needs no migration. A secret, a set-password token, is sealed with AES-256-GCM under a key derived from SECRETS_KEY with the row id as additional data, and is erased in the statement that marks the row sent or failed. A message is written in the transaction of the change that causes it; the dispatcher, in the API and in the worker, claims rows FOR UPDATE SKIP LOCKED under advisory lock (73819, 43) and keeps to EMAIL_DAILY_LIMIT a local day.

User imports (§5.7, R5; 00136_create_user_import_jobs.sql, 00137_create_user_import_rows.sql, docs/plan/20-data-model.md §54). user_import_jobs is one row per import: its creator (cascading), the caller's request_id (unique per creator, so a repeated request finds the first job), the file's name and format (csv or xlsx, the text never stored), the default role, the delivery (email_link or sheet), the status (queued, running, done, failed with an error_code), 1 to 2,000 rows with their counts, at most three claims, and for a sheet its expiry and the moment it was downloaded. user_import_rows holds the accepted rows, (job_id, ordinal), with the name, address, role, class (set NULL with the class), the outcome (pending, created, skipped_exists), the account made and, for a sheet, the sealed temporary password, which only a created row may hold.

Data exports (§15, R5; 00134_create_data_exports.sql). data_exports is one row per request: the requester (cascading), include_answers, the status (queued, running, ready, failed, expired), at most three claims, the object's key and size, an error code for a failed one, the times, and downloaded_at with downloads (0 to 3). A partial unique index on (true) WHERE status IN ('queued','running') allows one export in progress in the whole centre. The ZIP lies in the private import bucket for 24 hours; the daily housekeeping then deletes it and marks the row expired, which stays.

Avatar sweeps (R5, 00140_create_avatar_sweeps.sql, D-050). avatar_sweeps(user_id, swept_at) records the anonymised accounts whose profile photo objects the daily housekeeping removed, once the prefix is empty; the app role may read and add rows and has no UPDATE or DELETE.

Retention (R5, D16, docs/plan/20-data-model.md §55). The centre's three choices (§5.6) are applied every night by cmd/maintenance retention-sweep, run under its own role (§13.5) by .github/workflows/retention-sweep.yml at 03:00 in Ho Chi Minh City, which applies it only while the repository variable RETENTION_SWEEP_ENABLED is true and is a dry run otherwise. With keep_answers_months set, an attempt that has ended and whose assignment closed for that student before the cutoff has each answer's payload replaced by {"removed": true}: its scores, comments and grader stay, an attempt in progress is never touched, and the read paths show the answer as removed (answerRemoved, §15). Focus-loss events go once both their receipt and the student's close are older than keep_focus_events_months. With anonymise_disabled_after_months set, an account disabled before the cutoff is anonymised whatever its role, unless it owns a class or content or has an attempt in progress. Every comparison is a strict < on a UTC cutoff, every batch writes an audit entry with no actor, and under the defaults (answers kept for ever, focus-loss events 13 months, accounts never anonymised) it removes only focus-loss events. getRetentionImpact counts what tonight's run would do with the sweep's own queries (shared/retention).

Roles and permissions (§5, plan 70 §4), from R2:

CREATE TABLE app.permissions (
  key       text PRIMARY KEY,                      -- 'content.tests.write', ...
  group_key text NOT NULL CHECK (group_key IN ('content','teaching','people','system')),
  ordinal   smallint NOT NULL CHECK (ordinal > 0),
  in_matrix boolean NOT NULL,                      -- false for the four hidden keys
  UNIQUE (group_key, ordinal)
);

CREATE TABLE app.roles (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  builtin_key text UNIQUE CHECK (builtin_key IN ('admin','teacher','assistant','student')),
  name        text NOT NULL CHECK (length(btrim(name)) BETWEEN 1 AND 60),
  description text NOT NULL DEFAULT '' CHECK (length(description) <= 300),
  icon        text NOT NULL,                       -- one of the Edit role dialog's twelve
  color       text NOT NULL,                       -- one of its seven card colours
  copied_from uuid REFERENCES app.roles(id) ON DELETE SET NULL,
  revision    bigint NOT NULL DEFAULT 1 CHECK (revision > 0),
  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()
);
CREATE UNIQUE INDEX roles_name_lower_key ON app.roles (lower(name));

CREATE TABLE app.role_permissions (
  role_id        uuid NOT NULL REFERENCES app.roles(id) ON DELETE CASCADE,
  permission_key text NOT NULL REFERENCES app.permissions(key) ON DELETE RESTRICT,
  PRIMARY KEY (role_id, permission_key)
);

permissions is plan 70 §4.1's catalogue. A migration writes the catalogue, the four built-in roles and their grants (docs/plan/20-data-model.md D-21). A custom role has no builtin_key, and no role's builtin_key changes. The Admin stores no grant but learning.take_tests: it holds every other key as the wildcard. Triggers refuse a hidden key for any role and keep learning.take_tests on the built-in Student, and every grant change bumps the role's revision. The view app.student_like_roles is the one definition of a strict student target (§5.4): the built-in Student, or a custom role holding nothing but learning.take_tests.

users.role_id replaced users.role. Migration 00105 (T-R3.1) validated its NOT NULL and dropped role, its index, app.user_role and the trigger that kept the two in step for the v0.7.0 binary. The trigger users_last_admin refuses a demotion, disable or delete that would leave no active Admin. An access token carries the user's session_epoch, and a token below the stored value is refused; a disable, a staff password reset and, since R5, every Admin write that ends access and the use of a set-password link bump it (§5.2, §5.7). created_by names the staff member who created the account; it is NULL for an account older than R2 and for a Google self-join. Details: docs/plan/20-data-model.md §29–§30.

Classes and join codes (§6):

CREATE TABLE app.classes (
  id                uuid PRIMARY KEY DEFAULT uuidv7(),
  name              text NOT NULL,
  description       text,
  schedule_label    text CHECK (char_length(schedule_label) BETWEEN 1 AND 120),
  room              text CHECK (char_length(room) BETWEEN 1 AND 60),
  self_join_enabled boolean NOT NULL DEFAULT true,
  teacher_id        uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE app.class_join_codes (
  id              uuid PRIMARY KEY DEFAULT uuidv7(),
  class_id        uuid NOT NULL REFERENCES app.classes(id) ON DELETE CASCADE,
  code_hash       bytea NOT NULL,                  -- 1: sha256(normalized code); 2: HMAC-SHA256
  code_ciphertext bytea CHECK (code_ciphertext IS NULL OR length(code_ciphertext) = 36),
  key_id          smallint CHECK (key_id IS NULL OR key_id <> 0),
  lookup_scheme   smallint NOT NULL DEFAULT 1 CHECK (lookup_scheme IN (1, 2)),
  code_hint       text NOT NULL,                   -- last 4 chars, for the teacher's display
  expires_at      timestamptz NOT NULL,
  max_uses        integer CHECK (max_uses IS NULL OR max_uses > 0),
  uses_count      integer NOT NULL DEFAULT 0,
  revoked_at      timestamptz,
  created_by      uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (code_hash),
  CHECK ((lookup_scheme = 2) = (code_ciphertext IS NOT NULL)
     AND (lookup_scheme = 2) = (key_id IS NOT NULL)),
  CONSTRAINT class_join_codes_live_encrypted       -- 00141, R5
    CHECK (revoked_at IS NOT NULL OR code_ciphertext IS NOT NULL)
);
-- one active code per class
CREATE UNIQUE INDEX class_join_codes_one_active
  ON app.class_join_codes (class_id)
  WHERE revoked_at IS NULL;

CREATE TABLE app.class_members (
  class_id   uuid NOT NULL REFERENCES app.classes(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  joined_via app.join_source NOT NULL,
  joined_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (class_id, user_id)
);
CREATE INDEX ON app.class_members (user_id);

The code is stored encrypted, so it can be read back for its teacher and for admins (D5). A code issued from v0.8.0 on is a scheme-2 row: code_ciphertext holds a 12-byte nonce, the 8-byte code and a 16-byte AES-256-GCM tag, sealed under JOIN_CODE_KEY with the class and code ids as additional data; key_id is the 16-bit id derived from that key; and code_hash is the HMAC-SHA256 of the normalized code under a lookup key derived from it. The key never reaches the database, so a dump does not hand over class access. A code issued before v0.8.0 is a scheme-1 row: its SHA-256 and no ciphertext. It redeemed and could not be read back; v0.10.0 replaced every live one at start-up with a scheme-2 code (§6.1, D5). R5 removed the scheme-1 lookup (T-R5.1): 00141_require_encrypted_join_codes.sql refuses to run while a live scheme-1 row remains, revokes an expired one at its expiry, and adds class_join_codes_live_encrypted, so a code that is not revoked has a ciphertext. A revoked scheme-1 row stays as history and redeems nothing. code_hash under the keyed lookup is the only lookup path (§6.5). Key derivation and rotation: §6.1 and docs/plan/20-data-model.md §33.

Media (§11):

CREATE TABLE app.media_assets (
  id                uuid PRIMARY KEY DEFAULT uuidv7(),
  kind              app.media_kind NOT NULL,
  storage_key       text NOT NULL UNIQUE,          -- R2 object key; immutable
  mime_type         text NOT NULL,
  bytes             bigint NOT NULL,
  duration_ms       integer CHECK (duration_ms IS NULL OR duration_ms > 0),
  original_filename text NOT NULL,
  display_name      text CHECK (char_length(display_name) BETWEEN 1 AND 200 AND display_name ~ '\S'),  -- NULL: the library shows original_filename; D-29
  default_max_plays smallint CHECK (default_max_plays IS NULL
                      OR (default_max_plays BETWEEN 0 AND 3 AND kind = 'audio')),  -- 0 is unlimited; D-29
  replaced_by       uuid REFERENCES app.media_assets(id) ON DELETE SET NULL CHECK (replaced_by <> id),  -- the successor of a replaced file; D-29
  width             integer,                       -- an image's pixels, both or neither; D-29
  height            integer,
  checksum_sha256   bytea NOT NULL,                -- dedupe identical re-uploads
  uploaded_by       uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  owner_id          uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  created_at        timestamptz NOT NULL DEFAULT now(),
  deleted_at        timestamptz,
  unreferenced_at   timestamptz,                   -- when a deleted or replaced file was last seen named by nothing; its object goes seven days on; F-68
  purged_at         timestamptz,                   -- when the sweep removed its object; the row stays; F-68
  CONSTRAINT media_assets_purged_check CHECK (purged_at IS NULL OR unreferenced_at IS NOT NULL),
  CHECK (kind <> 'audio' OR duration_ms IS NOT NULL),
  CONSTRAINT media_assets_bytes_by_kind
    CHECK (bytes > 0 AND bytes <= CASE WHEN kind = 'audio' THEN 52428800 ELSE 10485760 END),
  CONSTRAINT media_assets_dimensions_paired CHECK ((width IS NULL) = (height IS NULL)),
  CONSTRAINT media_assets_dimensions_positive CHECK (width > 0 AND height > 0),
  CONSTRAINT media_assets_dimensions_image_only CHECK (width IS NULL OR kind = 'image')
);
CREATE INDEX ON app.media_assets (kind, created_at DESC);

The library's fields are R4's (migrations 00082 to 00084, D-29). display_name is what the library shows and renaming changes it alone; original_filename and the stored object never change. default_max_plays is an audio file's default play limit, 0 to 3 with 0 unlimited, which the editor starts a question's own limit from and which changes no question. replaced_by is set on the old row when a file is replaced: that row stays, because published versions still reference it, and it leaves the library list and the owner's quota (§11.1). width and height are read from an image's header at upload, so a row stored before 00083 has none. The size limit depends on the kind, 50 MiB for audio and 10 MiB for an image. One owner's library may hold MEDIA_OWNER_QUOTA_MIB (5 GiB by default), counting the files in it and the deleted or replaced files that something still names; the writers of one library take advisory lock (73820, hashtext(owner)) before they sum it (docs/plan/20-data-model.md §28). unreferenced_at and purged_at are the retention of a file that left the library (§11.2; migrations 00111 to 00113, docs/plan/20-data-model.md §45): a row that names a purged file is refused by a trigger on the six tables that name a file, and one that names a file waiting for its removal clears the date.

Question bank — normalize; do not stuff options into jsonb. Options and blanks are ordered, queried, and graded against.

Migration 00032_add_option_content.sql adds nullable content jsonb to question_options and test_version_options for the bounded inline AST only. Option identity, order, text and correctness stay relational. No grading key is placed in the AST, and no legacy row is rewritten. Read-compatible binaries are the rollback floor after rich writes; rolling back the migration discards rich formatting and is only a pre-rollout development operation.

Migration 00033_add_question_content.sql adds nullable prompt_content jsonb and explanation_content jsonb to questions and test_version_questions. These columns hold the bounded prose AST from §7.1; companion strings remain exact plain projections. Migration 00034_add_question_gap_bindings.sql enables rich fill-blank prompts and adds nullable, question-scoped unique gap_id to question_blanks and test_version_blanks. Null identifies legacy Markdown. Bank copies and restored drafts remap gap identities and answer bindings together; snapshots freeze them. Answer submissions remain keyed by frozen blank UUID. Down refuses existing rich fill-blank rows instead of silently discarding bindings. No legacy rows are rewritten, no grading keys move, and the same read-compatible rollback floor applies after prose writes.

Migrations 00114 to 00117 (D4, T-D4.13) add the pause rule of §11.1, one column each. questions.audio_allow_pause and test_version_questions.audio_allow_pause are nullable booleans with no default, no backfill and CHECK (audio_allow_pause IS NULL OR media_asset_kind IS NOT DISTINCT FROM 'audio'), and every reader takes COALESCE(…, true). They are nullable because the previous binary inserts these rows during a rolling deploy and writes no value, and because questions_audio_policy_iff_audio is a biconditional over the other two policy columns, which this column therefore stays out of. group_recordings.allow_pause and test_version_group_recordings.allow_pause are boolean NOT NULL DEFAULT true: a recording always has its whole policy, and a constant default is stored without a rewrite. None of the four rewrites or indexes a populated table.

CREATE TABLE app.questions (
  id              uuid PRIMARY KEY DEFAULT uuidv7(),
  type            app.question_type NOT NULL,
  prompt          text NOT NULL,
  media_asset_id  uuid REFERENCES app.media_assets(id) ON DELETE RESTRICT,
  media_alt       text CHECK (char_length(media_alt) BETWEEN 1 AND 1000),  -- image only; D-33
  audio_max_plays integer CHECK (audio_max_plays IS NULL OR audio_max_plays > 0),
  audio_allow_seek boolean NOT NULL DEFAULT false,
  audio_show_transcript_after boolean NOT NULL DEFAULT true,
  audio_allow_pause boolean,                       -- D4: NULL reads as true; audio only (00114)
  transcript      text,
  points          numeric(8,2) NOT NULL CHECK (points > 0),
  explanation     text,
  sample_answer   text,                            -- OQ-4; admin-only, never in a student payload
  tags            text[] NOT NULL DEFAULT '{}',
  level           text CHECK (level IN ('pre_a1','a1','a2','b1','b2','c1','c2')),  -- D-31
  skill           text CHECK (skill IN ('grammar','vocabulary','reading','listening','writing','speaking')),  -- D-31
  created_by      uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  owner_id        uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  deleted_at      timestamptz
);
CREATE INDEX ON app.questions USING gin (tags);
CREATE INDEX ON app.questions USING gin (to_tsvector('simple', prompt));
CREATE INDEX ON app.questions (media_asset_id) WHERE media_asset_id IS NOT NULL;

CREATE TABLE app.question_options (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  question_id uuid NOT NULL REFERENCES app.questions(id) ON DELETE CASCADE,
  ordinal     smallint NOT NULL,
  text        text NOT NULL,
  is_correct  boolean NOT NULL DEFAULT false,
  UNIQUE (question_id, ordinal)
);

question_blanks and question_blank_answers mirror this for fill_blank.

Tests and versioning — the load-bearing decision. On publish, snapshot resolved content into version tables so editing a test can never mutate an in-flight or historical attempt:

tests(id, title, description, status, current_version, last_published_version, owner_id, ...)
test_versions(id, test_id, version, published_at, total_points, change_note, UNIQUE(test_id, version))
test_version_sections(id, test_version_id, ordinal, title, instructions)
test_version_questions(id, test_version_section_id, ordinal, source_question_id,
                       type, prompt, media_asset_id, media_alt, audio_max_plays, audio_allow_seek,
                       audio_show_transcript_after, audio_allow_pause, transcript, points, explanation,
                       sample_answer, level, skill)
test_version_options(id, test_version_question_id, ordinal, text, is_correct)
test_version_blanks / test_version_blank_answers

test_versions.change_note is the teacher's note at publish, 1 to 200 characters, NULL when none (the command stores NULL for a blank one; D-32). level and skill (R4, D-31) are a question's optional CEFR level and skill, closed sets with no backfill; publish freezes both with the question, so a later bank edit never changes a version. Authoring accepts at most eight options for a choice question, but a stored question, a copy, a publish and a restore keep a longer array exactly as it was.

Snapshotting into normalized rows rather than one jsonb blob keeps per-question analytics a plain SQL query later. source_question_id preserves the bank link without coupling to it. media_asset_id points at the same immutable asset — the file is never copied.

Ownership (D3), from R2. tests, questions, question_groups and media_assets carry owner_id, and classes carries teacher_id. Each is NOT NULL REFERENCES app.users ON DELETE RESTRICT and names the teacher the row belongs to. created_by (uploaded_by for media) stays the provenance, so a transfer moves the owner without rewriting who made the row (docs/plan/20-data-model.md D-22). classes has no created_by; the backfill gave every existing class the oldest active Admin, the teacher v0.7.0 showed (D-23). Assignments and Word imports belong to their created_by. Migration 00106 (T-R3.2) validated the NOT NULL of every owner column, which R2 had added NOT VALID, and dropped the triggers that filled them for the v0.7.0 binary's inserts, so an insert that omits an owner fails. Repositories scope every read and write to what the caller reaches, and another teacher's row answers as a missing one does (plan 70 §4.2). Details: docs/plan/20-data-model.md §31–§32.

Per-student overrides (R4). assignment_student_overrides(assignment_id, student_id, closes_at, duration_minutes, extra_attempts, reason, created_by, created_at, updated_at), primary key (assignment_id, student_id), both foreign keys ON DELETE CASCADE, created_by ON DELETE SET NULL. closes_at and duration_minutes are nullable and extra_attempts defaults to 0; a check requires at least one of the three to change something. A student's close is greatest(least(closed_at, closes_at), override.closes_at): an override only ever lengthens, and one that reaches past an early close lifts it for that student alone. shared/schedule holds that rule as SQL and in Go, and the join that reads it.

Assignment options (R4, D-34, D-35). assignments gains review_release (on_submit or after_close, default on_submit), review_show_class_average (default false) and a nullable student_note, 1 to 500 characters once trimmed of the whitespace JavaScript's trim() removes (the command stores NULL for a note that is blank by that rule, so the check never meets an empty string). The defaults are constants, so nothing is rewritten and the previous release's insert keeps its meaning. An assignment still has no status column: its status is a function of its window (D-18), and shared/schedule holds that rule once for every reader.

An attempt in progress follows its window (R4). extendAssignment, updateAssignment (a later close), reopenAssignment and setStudentOverrides set the deadline of each attempt in progress to the earlier of its start plus its student's time limit and its student's close, in the transaction that changed the window, and only where that is later than the deadline the attempt has (schedule.RecomputeDeadlines). A deadline never moves earlier: removing an override, a shorter time limit or close, and closing early leave a running attempt where it is, and so does an attempt whose deadline has passed on the database's clock, which is over whether or not it has been swept and is not revived. A deadline a maintenance window pushed past the rule stays. Each attempt moved is audited as attempt.extended with the old and new deadline and the cause, and an override moves only the attempts of the students it names. The writers take the assignment row and then its attempts, each attempt locked as it is read, so a concurrent submit, expiry or resume of the same attempt finishes before the deadline moves or sees the new one. An attempt starts under the window as it stands when it commits: a start racing an extension stores the extended deadline, or is itself lengthened by the extension, whichever commits first.

Notifications (R4, D-28). notifications(id, user_id, kind, params, target, dedupe_key, created_at, updated_at, read_at) holds what the app tells one user; user_id cascades with the user. kind is a dotted key of at most 64 characters, checked by pattern and not an enum, so a release adds a kind without a migration; params is a JSON object of at most 4096 bytes and target NULL or an object of at most 1024 bytes. No sentence is stored: the reader's console words the item from kind and params, and a name in params is a copy taken when the row was written. UNIQUE (user_id, dedupe_key) is how a second notice about the same thing merges into the row the user already holds. A row is kept 180 days from when it was first written; a daily job deletes older ones. notification_preferences(user_id, event, in_app, email, updated_at), primary key (user_id, event), holds the switches a user has saved for the five governed events; a missing row is the default, in the app and not by email. email is stored so that the release that sends email adds no column; nothing sends it yet.

Word imports (R4, D-40, D-41; the tables are docs/plan/20-data-model.md §23 to §26). word_import_drafts gains nullable open_action_count and open_confirm_count (integers, not negative), written with the review body in the same statement, which the import history reads without parsing the body; NULL is a draft not yet assessed, not zero. word_import_sources.format admits text, and characters (1 to 100000) is non-null exactly for a text source (§16).

Assignments and attempts:

CREATE TABLE app.attempts (
  id              uuid PRIMARY KEY DEFAULT uuidv7(),
  assignment_id   uuid NOT NULL REFERENCES app.assignments(id) ON DELETE RESTRICT,
  test_version_id uuid NOT NULL REFERENCES app.test_versions(id) ON DELETE RESTRICT,
  student_id      uuid NOT NULL REFERENCES app.users(id) ON DELETE RESTRICT,
  attempt_no      smallint NOT NULL CHECK (attempt_no > 0),
  status          app.attempt_status NOT NULL DEFAULT 'in_progress',
  session_id      uuid NOT NULL,                   -- takeover detection
  started_at      timestamptz NOT NULL DEFAULT now(),
  deadline_at     timestamptz NOT NULL,            -- authoritative, server-computed
  submitted_at    timestamptz,
  graded_at       timestamptz,
  score_earned    numeric(8,2),
  score_total     numeric(8,2),
  focus_loss_count integer NOT NULL DEFAULT 0,
  flagged         boolean NOT NULL DEFAULT false,
  UNIQUE (assignment_id, student_id, attempt_no),
  CHECK (deadline_at > started_at)
);
CREATE UNIQUE INDEX attempts_one_live
  ON app.attempts (assignment_id, student_id) WHERE status = 'in_progress';
CREATE INDEX ON app.attempts (assignment_id, status);
CREATE INDEX ON app.attempts (student_id, started_at DESC);

CREATE TABLE app.attempt_answers (
  attempt_id   uuid NOT NULL REFERENCES app.attempts(id) ON DELETE CASCADE,
  question_id  uuid NOT NULL REFERENCES app.test_version_questions(id) ON DELETE RESTRICT,
  payload      jsonb NOT NULL,
  auto_score   numeric(8,2),
  manual_score numeric(8,2),
  final_score  numeric(8,2) GENERATED ALWAYS AS (coalesce(manual_score, auto_score)) VIRTUAL,
  grader_comment text,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (attempt_id, question_id)
);

CREATE TABLE app.attempt_audio_plays (               -- §11.4
  attempt_id  uuid NOT NULL REFERENCES app.attempts(id) ON DELETE CASCADE,
  question_id uuid NOT NULL REFERENCES app.test_version_questions(id) ON DELETE RESTRICT,
  plays       integer NOT NULL DEFAULT 0 CHECK (plays >= 0),
  last_played_at timestamptz,
  PRIMARY KEY (attempt_id, question_id)
);

CREATE TABLE app.attempt_events (                    -- append-only, §10
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  attempt_id  uuid NOT NULL REFERENCES app.attempts(id) ON DELETE CASCADE,
  kind        text NOT NULL,
  occurred_at timestamptz NOT NULL,                  -- client time, offset-corrected
  received_at timestamptz NOT NULL DEFAULT now(),
  client_seq  integer NOT NULL,
  question_id uuid,
  meta        jsonb,
  UNIQUE (attempt_id, client_seq)
);
CREATE INDEX ON app.attempt_events (attempt_id, occurred_at);

final_score uses PG18 virtual generated columns — computed on read, no storage, never stale (the default for generated columns in 18). Grading precedence is a pure function of two columns and must never drift.

attempt_events uses bigint IDENTITY rather than a UUID: a high-volume append-only log read only by attempt_id is better served by a narrow sequential key. The application never updates or deletes it. Only the retention sweep (and maintenance retain-integrity, its focus-events step alone) deletes events, under the maintenance role (§13.5), once both the student's close of the assignment and server received_at are older than the centre's keep_focus_events_months (13 by default), using a UTC cutoff; each batch is audited (Retention, above). The audit log remains retained. A disabled account is anonymised automatically only when the centre sets anonymise_disabled_after_months. Anonymization, by the sweep or by maintenance anonymize-user on request, removes structured identity and credentials while preserving historical IDs and records; free text, old audit entries and backups require separate review (see docs/setup/operations.md).

13.4 Audit log

audit_log(id, actor_user_id, action, entity, entity_id, occurred_at, ip, user_agent, diff jsonb). Required entries: class enrolment (§6.5), join-code generation and rotation, attempt reset/void/extend, password reset, media deletion, test publish.

Use PG18's OLD/NEW in RETURNING to capture the diff in the same statement as the update, rather than a read-then-write.

Reading the log (R5). GET /admin/audit-log and GET /admin/audit-log/export (system.audit.read, Admin only) read it through modules/audit; no other module reads audit_log. An entry's category comes from its entity (domain/categories.go: six tabs; organization, export, maintenance, retention and AI entries are listed under All only), and its sentence from its action (domain/actions.go, vi and en, matched by the same q that matches the actor's name or email and the name of the row the entry is about). The tab counts are one GROUP BY entity over the search and range, whichever tab is selected. The ranges are calendar days in the viewer's stored zone (7d is today and the six days before it). A diff shaped {field: {old, new}} is shown as changes and any other as details; a code, joinCode or code_hash value is shown as its last four characters and a key whose name contains password, passcode, token, secret, authorization, credential, cookie, ciphertext or the name of a key (apiKey, privateKey, accessKey, signingKey), or is exactly otp or pin, is dropped, whatever was stored. audit_log_recent_idx is (occurred_at DESC, id DESC), built CONCURRENTLY by 00133_index_audit_log_recency.sql after dropping any invalid index of that name, so a rerun after an interrupted build repairs it. The export streams CSV or JSON in keyset batches over it, refuses more than 100,000 entries with 422 EXPORT_TOO_LARGE, reads the log as it stood when it began, and is itself audited as audit.exported before its first byte. A search runs with a 30-second statement timeout and jit off, because the planner's cost for its sub-selects otherwise starts JIT compilation that costs a second. A change to what the server writes (a new action or entity) fails modules/audit/domain/tests/catalog_test.go until domain/actions.go or categories.go and both locale files list it.

13.5 Security in the data layer

• Refresh tokens stored as SHA-256 hashes: token_hash, family_id, user_id, expires_at, revoked_at, replaced_by, user_agent, ip, geo_label (the place of the request that issued the token, "City, CC", or NULL; R4).
• Join codes sealed with AES-256-GCM under a key the database never holds, and found by an HMAC-SHA256 of the normalized code under a key derived from it (§13.3). Lookup by hash, compared in constant time. A code issued before v0.8.0 kept its SHA-256 hash until v0.10.0's start-up rotation replaced it (§6.1); a revoked one keeps it as history, and since R5 no lookup reads it.
• Passwords: Argon2id (bcrypt cost ≥ 12 if unavailable).
• sample_answer, transcript, is_correct, and accepted_answers must never reach a student response. Enforce with explicit column lists — no SELECT * in student-facing paths. Add a test that asserts these keys are absent from GET /app/attempts/:id.
• Least-privilege DB roles: the app connects with DML on app only, not as owner. Migrations run as a separate role. The app role only reads permissions and student_like_roles; on roles and role_permissions it holds only what the role commands write (00129_grant_role_writes.sql: insert roles, update their name, description, icon, colour, revision and updated_at, add and remove grants), never DELETE on roles. users_last_admin is the schema's first SECURITY DEFINER function: owned by the migrate role, with search_path = pg_catalog, app, pg_temp and EXECUTE revoked from PUBLIC (docs/plan/20-data-model.md §30).
• app.organization_settings is one row (id boolean PRIMARY KEY DEFAULT true CHECK (id), migration 00130): the app role may SELECT and UPDATE it and has no INSERT or DELETE (§5.6).
• A third role, quizzivy_maintenance (R5, D16), runs cmd/maintenance: LOGIN, CONNECTION LIMIT 2, owning nothing and with no CREATE, created by scripts/provision-db.sh (a migration cannot create a role) and granted by 00139_grant_maintenance_role.sql, which fails when the role is missing. Its grants are column by column and only what the commands read and write: INSERT alone on audit_log, DELETE and a three-column SELECT on attempt_events, UPDATE (payload) on attempt_answers, the login columns of users, and nothing on ai_usage_ledger; privileges_test.go pins the list both ways. A trigger on users that fires for this role alone (users_maintenance_guard) refuses any update that does not leave the account anonymised, so a leaked credential can set no email or password hash and enable no account. The credential is held by the operator and the GitHub environment production-maintenance, never by the API or on Fly.
• Set-password tokens and import-sheet passwords are never stored in the clear: a link is stored as its SHA-256 (§13.3), and the token in the outbox and a sheet's passwords are sealed under keys derived from SECRETS_KEY, which the database never holds, and erased once sent, downloaded or expired.

13.6 PG18 features — use and skip

Feature	Use?	Why
uuidv7()	Yes, all PKs	Time-ordered → index locality; avoids v4 fragmentation
Virtual generated columns	Yes, final_score	Computed on read, no storage, never stale
OLD/NEW in RETURNING	Yes, audit log	One statement instead of read-then-write
B-tree skip scan	Passive	One multicolumn index serves more shapes — do not add redundant prefix indexes preemptively
NOT NULL NOT VALID + VALIDATE	Yes, in migrations	Adds NOT NULL without a full-table lock
Async I/O	Passive	Server-side; no schema impact
Temporal constraints (WITHOUT OVERLAPS)	No in v1	No business rule needs non-overlapping validity periods. Revisit for class scheduling.
OAuth DB authentication	No	That is Postgres connection auth, unrelated to §5's application-level Google Sign-In. Do not confuse the two.

13.7 Migrations

• goose, SQL, forward-only, one concern per migration.
• Expand-contract for anything breaking: add nullable → backfill → start writing → NOT NULL → drop old. Never in one migration.
• CREATE INDEX CONCURRENTLY runs outside a transaction — mark those -- +goose NO TRANSACTION.
• Test every migration against a copy of real data before production. On Neon, a branch per migration, reset from parent between runs.
• Seed data in seed/, never in migrations. The exception is reference data the app cannot run without: the permission catalogue and the built-in roles (docs/plan/20-data-model.md D-21).
• A migration that only rewrites data has a Down that does nothing, and says so. 00103_compose_text_nfc.sql composes the plain text columns a person types to NFC (§12) without moving updated_at; nothing records which rows were decomposed, so nothing can be restored, and NFC and NFD are the same text to a reader (docs/plan/20-data-model.md D-38).

13.8 Query discipline

• No SELECT * in application code.
• Keyset pagination everywhere (WHERE (created_at, id) < ($1,$2) ORDER BY created_at DESC, id DESC LIMIT $3), not OFFSET. uuidv7() PKs make this natural.
• EXPLAIN (ANALYZE, BUFFERS) any query touching attempts, attempt_answers, or attempt_events before merging.
• N+1 is the default failure mode of the grading and monitor screens — one query for an attempt's answers, one query for an assignment's monitor rows.

The Admin's readings (R5). getAdminOverview, getAdminSummary and getUsage (modules/dashboard, all read-only, no migration) count the whole centre under no scope: the caller's keys decide which sections are read at all, and a section a key does not open is null and costs no query. A day is a local date in APP_TIMEZONE: the chart is today and the thirteen days before it with a zero for a day with no row in user_activity_days, its average the mean of students plus staff rounded half up, and "this week" the seven days ending today. A student is a student target of app.student_like_roles and a teacher is every other account, an Admin and a custom role included; the chart's "staff" is the same partition. people.teachers and usage.teachers count the enabled teachers, people.teachersDisabled the disabled ones, and a seat is an account that is not disabled; people.total and people.students still count disabled accounts, as the Users tabs do. The enabled accounts fall into the first of must change password, never signed in (DG-54). The plan is PLAN_NAME, PLAN_RENEWS_ON, PLAN_SEAT_LIMIT, PLAN_TEACHER_LIMIT, PLAN_MEDIA_BYTES and PLAN_IMPORT_BYTES, each optional, validated at start-up and never enforced (DG-55); a meter at 80% of its limit adds the Overview's storage row. The AI figures, the credit-request row and aiCredits, come through the AI module's own AdminCredits query for a holder of ai.credits.manage and are null while the assistant is off. attention[].detail carries the facts a row quotes, not a sentence, so the web words each row in the reader's language.

13.9 Maintenance windows

app.maintenance_windows (id, starts_at, ends_at, created_at, created_by, cancelled_at) (migration 00053, docs/plan/20-data-model.md §27).

• A window runs at most 12 hours, and windows that are not cancelled may not overlap (an exclusion constraint).
• The app role only reads the table. The operator schedules, cancels and ends windows through cmd/maintenance.
• Scheduling extends by the window's length every in-progress attempt that would still be running when it starts, every published, open assignment that would close inside it, and every student override of a published assignment that would close inside it. Every extension is audited as System.
• The API reads the table into a snapshot at most every 30 s, and only while requests arrive. During an active window it answers 503 (§15), and it refuses an attempt start that would run into a window.
• A start and a schedule take the same advisory lock (73819, 40), so a new attempt is either refused or extended. So do every other writer of an assignment's window or overrides: the teacher's extension, the assignment update, the reopening and the two override writes (shared, as a start does), so none of them runs between a window's scheduling and its extensions.

13.10 AI credits

The assistant's budget (D21) is six tables, migrations 00118 to 00123 (docs/plan/20-data-model.md §46). All are new and empty, so a previous binary is unaffected, and they exist whether or not ANTHROPIC_API_KEY is set. A credit is $0.001 of the provider's list price: a call costs ceil(sum(tokens × price) / 1e9) from the provider's reported usage and the tier's configured prices, held as integer millionths of a dollar per million tokens, so no call is rounded across a credit by floating point. The centre has a pool of 200,000 credits a month ($200) and every teacher an allowance under it, 20,000 by default.

Table	Holds
ai_settings	One row, written by its migration: the pool the plan sets, the default allowance, the share of an allowance at which a teacher is told (80) and the rule for a teacher who runs out, request or pause. The pool has one rule for running out, pause everyone (DG-196).
ai_credit_periods	One row a month, created by the month's first use from ai_settings: pool_credits, added_credits (an Admin's approval, not carried over), used_credits, reserved_credits.
ai_teacher_credits	One row per teacher and month: allowance_credits (with the granted_credits that approved requests added), custom, paused, used_credits, reserved_credits, warned_at. custom and paused follow the person into the next month.
ai_reservations	The ceiling a call holds until it settles or is released. A leftover older than ten minutes is returned by ReleaseStaleAiReservations (hourly, and maintenance ai-reservations-sweep).
ai_usage_ledger	One row per settled call: the actor, the feature, the tier, the four token counts the provider reported, the credits charged and the provider's request id.
ai_credit_requests	A teacher's request for more credits, pending, approved or declined, with who decided and when. A partial unique index allows one pending request per teacher and month.

• 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 moves in an UPDATE of those rows. The database refuses the call that would overspend, and no code path has to remember to. A reservation locks the month's row and then the teacher's, in that order everywhere, so two calls cannot deadlock. A settlement charges the least of the metered credits and what was reserved, so a call the provider has been paid for is never refused.
• A month is the calendar month in APP_TIMEZONE (Asia/Ho_Chi_Minh when unset). The pool, the allowances and the use start over on the 1st; credits an Admin adds do not carry over.
• ai_usage_ledger is append-only, as audit_log and attempt_events are: the application role has no UPDATE or DELETE on it, so no handler can rewrite what was spent. Its user_id is set to NULL by the foreign key when a person is deleted, so a row outlives its actor.
• Every Admin write is one audit_log row, from a data-modifying CTE, so the diff cannot disagree with the change. A write from cmd/maintenance ai-credits is System: no actor, reason: "maintenance" and the operator in details.
• Two permission keys (migration 00124): ai.use in Content, granted to the Teacher role, and ai.credits.manage in System, granted to no built-in role; the Admin's wildcard holds both (docs/plan/70-redesign-overview.md §4.1). The import's word_imports.read_with_ai (migration 00125, boolean NOT NULL DEFAULT false) is §16's.

---

Section of Quizzivy — Frontend Portal & Data Model Specification · Previous · Next

---

_{Generated from docs/quizzivy-spec-v0.3.md. Edit that file in the repository; changes made on this page are overwritten.}