Spec 13 Database Design - SekiroKenjii/Quizzivy GitHub Wiki

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

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.

• 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.

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);

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 and a staff password reset bump it (§5). 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))
);
-- 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), and a revoked or expired scheme-1 row stays until R5 removes the lookup. code_hash stays 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. An explicit privileged retention operation may delete events only after both assignment closure and server receipt are more than thirteen calendar months old, using server received_at and a UTC cutoff; each batch is audited. The audit log remains retained. Disabled accounts are not automatically erased. Manual requested student anonymization 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).

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.

• 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 live 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 or expired one keeps 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, roles, role_permissions and student_like_roles; R5 grants the role commands' writes. 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).

• 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).

• 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.

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.

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.

• 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.}