Quiz app — favourites, statistics, exam flags, community stats

Summary

Four focused additions turn the shipped quiz app's existing question and attempt data into personal study tools: saved questions, exam review flags, newest-answer statistics, and clearer community evidence. The design keeps the v1 server-rendered security model intact and costs one table plus one column.

• StatusApproved · 10 August 2026
• ThemeYour own data about questions
• Schema costOne table + one column
• Runtime modelServer-rendered; JavaScript optional
• Predecessor2026-08-08-quiz-app-design.md

Binding constraints

All predecessor constraints continue to apply. These four directly shape the implementation.

Schema and migration

Add migrations/002_favourites_flags.sql. The versioned schema_migration runner applies files in filename order, so shipping requires no runner change; pnpm run migrate picks it up.

-- 002_favourites_flags.sql

CREATE TABLE favourite (
  user_id     uuid NOT NULL REFERENCES "user" (id) ON DELETE CASCADE,
  question_id uuid NOT NULL REFERENCES question (id) ON DELETE CASCADE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, question_id)
);

ALTER TABLE attempt ADD COLUMN flagged boolean NOT NULL DEFAULT false;

ALTER TABLE attempt ADD CONSTRAINT attempt_flag_exam_only
  CHECK (NOT flagged OR exam_id IS NOT NULL);

Feature 1 — Favourites

-- action=favourite
INSERT INTO favourite (user_id, question_id)
SELECT $1, q.id FROM question q WHERE q.slug = $2 AND q.retired_at IS NULL
ON CONFLICT DO NOTHING;

-- action=unfavourite
DELETE FROM favourite f
 USING question q
 WHERE q.slug = $2 AND f.question_id = q.id AND f.user_id = $1;

Surfaces and redirects

Filtering and reading state

listPracticeQuestions, nextPracticeSlug, and randomPracticeSlug gain favouritesOnly?: boolean. Carrying the parameter through question and “Next question” links keeps a favourites-filtered session inside favourites.

AND ($4::boolean IS NOT TRUE
     OR EXISTS (SELECT 1 FROM favourite f
                 WHERE f.user_id = $1 AND f.question_id = q.id))

IS NOT TRUE makes both NULL and false mean “no filter”, matching the other optional filters. getPracticeQuestion returns favourite: boolean. getExamResult avoids N+1 reads with one roster query:

SELECT question_id FROM favourite
 WHERE user_id = $1 AND question_id = ANY($2)

The result is collected into a Set for per-question rendering.

Empty state and language

/practice?fav=1 reuses the existing “No questions match that filter” card and adds: “You have not saved any questions yet. Open a question and tap Save.”

Schema, code, tests

favourite: table and function names, plus query parameter fav

User interface

save / star: the expected friendly action and icon

Domain reference

CONTEXT.md → Quiz → Favourite

Feature 2 — Flag during an exam

The v1 numbered grid solves navigation but not intent: it lets users return to a question without marking which questions need review. Flags add that missing state and surface it in the grid.

UPDATE attempt SET flagged = $4
 WHERE exam_id = $1 AND user_id = $2 AND position = $3;

The handler maps only action=flag and action=unflag to server-side booleans; it never accepts arbitrary client-supplied flagged. position must pass isValidPosition before reaching the integer parameter. A successful mutation redirects back to the same question rather than navigating.

Finished-exam race

1. Validate and establish ownership

   Run existing exam-id, ownership, and isValidPosition checks before mutation.

2. Open a transaction and lock the exam

   Lock the owned row with SELECT ... FOR UPDATE, using the same serialization boundary as answer submission.

3. Check lifecycle under the lock

   Require submitted_at IS NULL and transaction_timestamp() < deadline_at. Never use Date.now().

4. Resolve closed exams

   Submitted exams redirect to results unchanged. Expired-but-unstamped exams are finalized through the existing lifecycle helper.

5. Write the explicit state

   Only an active exam reaches UPDATE attempt SET flagged = $4, then redirects back to the same position.

sequenceDiagram
    participant U as User
    participant P as "exam/[id]/[position].astro"
    participant DB as Database
    participant L as "exam-lifecycle.ts"
    U->>P: "POST action=flag or unflag"
    P->>P: "Validate id, ownership, position"
    P->>DB: "BEGIN, then SELECT exam FOR UPDATE"
    alt "submitted or expired"
        P->>L: "Finalize expired-but-unstamped exam"
        P-->>U: "303 to result"
    else "active before server deadline"
        P->>DB: "UPDATE attempt.flagged"
        P-->>U: "303 to same question"
    end    

Surfaces and accessibility

Feature 3 — Statistics screen

/stats answers three questions: how much of the bank have I attempted, how am I doing overall, and what should I practise next?

WITH latest AS (
  SELECT DISTINCT ON (a.question_id) a.question_id, a.is_correct
    FROM attempt a
   WHERE a.user_id = $1 AND a.answered_at IS NOT NULL
   ORDER BY a.question_id, a.answered_at DESC, a.created_at DESC, a.id DESC
)

answered_at

Determines semantic recency.

created_at

Resolves the realistic tie where two modes answer at the same timestamp precision.

id

Makes synthetic and manually seeded ties deterministic.

Retired questions

Excluded everywhere by joining question with retired_at IS NULL; attempted can never exceed bank size.

Three blocks

Run the two exported reads in Promise.all: getStatsTotals(userId) and getWeakTags(userId, limit). The weak-tag limit is 15.

-- getStatsTotals
WITH latest AS (...)
SELECT
  (SELECT count(*) FROM question WHERE retired_at IS NULL) AS bank,
  count(*)                              AS attempted,
  count(*) FILTER (WHERE l.is_correct)  AS correct
  FROM latest l
  JOIN question q ON q.id = l.question_id AND q.retired_at IS NULL;

-- getWeakTags
WITH latest AS (...)
SELECT t.tag,
       count(*) FILTER (WHERE NOT l.is_correct) AS wrong,
       count(*)                                 AS attempted
  FROM latest l
  JOIN question q ON q.id = l.question_id AND q.retired_at IS NULL
  CROSS JOIN LATERAL unnest(q.tags) AS t(tag)
 GROUP BY t.tag
HAVING count(*) FILTER (WHERE NOT l.is_correct) > 0
 ORDER BY wrong DESC, t.tag
 LIMIT $2;

A user with no answered attempts sees “Answer a few questions and your progress will show up here.” plus a link to /practice, not three zeroes. index.astro gains a Statistics link below Practice; QuizLayout keeps its two-item header.

Feature 4 — Community stats block

ExplanationPanel.astro stops collapsing imported evidence into a sentence shown only on disagreement. On answered practice and exam-result review screens, it exposes agreement, disagreement, and missing data distinctly.

• all emptyHide the entire block.0
• label mismatchCompare imported label with concatenated correct labels such as BC; render amber plus text disagrees.41
• agreementStill render the block so agreement is distinguishable from no imported data.3

The value 41 is the number of imported rows marked “PDF suggested answer looks wrong”; 3 denotes the three fields rendered, not a score.

Testing and success criteria

Extend the existing integration files rather than creating new ones: tests/integration/queries.test.ts, tests/integration/practice-pages.test.ts, and tests/integration/exam-pages.test.ts.

JavaScript-disabled end to end

1. Favourites · e2e/practice.spec.ts

   Save from practice, enable Favourites only, see the question, unsave it, and see the empty state.

2. Flags · e2e/exam.spec.ts

   Flag mid-exam, see the numbered-grid mark, submit, and see the result's Flagged badge.

3. Statistics · new e2e/stats.spec.ts

   Answer a question, render stats, and follow a weak-tag row to a filtered practice list.

Out of scope