Quiz App — Design

Summary

Build a separate, mobile-first Astro product for realistic cloud-certification exams and clue-assisted practice. The server owns every answer and deadline, while pull-request-reviewed CSV in git owns question content, giving resumable sessions, durable history, and a clean path to statistics without putting a client framework or the Gremlin backends in the loop.

• Locationfrontend/astro/apps/quiz
• Product boundaryIndependent app, database, auth, and deployment
• Core modesTimed exam + untimed practice with clues
• Content authorityPull-request-reviewed CSV in git, one file per exam type
• Default exam65 questions · 90 minutes
• StatusApproved for implementation

Architecture

flowchart LR
    U["Signed-in learner"] --> A["Astro server · apps/quiz"]
    A --> BA["Better Auth routes"]
    A --> F["Server-rendered forms"]
    BA --> P["Dedicated quiz Postgres database"]
    F --> P
    C["Reviewed CSV question bank"] --> I["scripts/import.ts"]
    I --> H["content_hash comparison per slug"]
    H --> P
    P -. "no shared data or calls" .- X["backend/bun · backend/go · platform DB"]
    

Runtime dependencies are astro, @astrojs/node, tailwindcss, @tailwindcss/vite, better-auth, pg, zod, marked, and nodemailer. csv-parse is a development dependency because only the import script uses it and it never runs in the server process. Development dependencies also include vitest, playwright, required type packages, and the Better Auth CLI pinned to exactly the runtime package version; the two Better Auth versions move together.

Data model

Better Auth's pinned CLI owns user, session, account, and verification. The app owns four tables. Every primary key is uuid with gen_random_uuid(); every timestamp is timestamptz.

Question

Indexes: unique slug, btree exam_type, and GIN tags. Stable slugs keep old attempts attached after wording corrections. Arrays avoid a tag and join table: WHERE tags @> ARRAY['athena'] uses GIN, while SELECT DISTINCT unnest(tags) supplies filter values. Pull-request review controls typos.

Answer option

Constraints: unique (question_id, label), unique (question_id, position), and a CHECK requiring both why-wrong fields to be NULL exactly when the option is correct. Per-option correctness gives “choose TWO” and single-answer questions one schema; per-option rationale lets feedback sit beside the relevant option.

Exam

Constraints and indexes: unique (id, user_id) supports the ownership-preserving attempt foreign key; question count is 1–200; duration is 1–480; deadline_at > started_at; unique (user_id) WHERE submitted_at IS NULL permits one active exam; (user_id, submitted_at) supports history and resume.

Attempt

Constraints: (exam_id, user_id) REFERENCES exam(id, user_id) ON DELETE CASCADE; position is present exactly for exam attempts; answered_at and is_correct are both NULL or both set; exam attempts cannot reveal options. Indexes: (user_id, question_id); unique exam position for non-NULL exam_id; and one unanswered practice row per (user_id, question_id).

Abandoned practice loads never count as wrong. Exam submission explicitly marks blank roster rows incorrect. The data needed for per-user, exam-type, question, and tag statistics therefore accumulates before a statistics screen exists.

Question content and import

CSV column mapping

One header row, then one question per row. Header names must match exactly; an unexpected, missing, or misspelled column fails the import rather than being silently ignored, because a spreadsheet makes that mistake easy.

Slug is one short hand-written identifier per row, e.g. saa-c03-serverless-flash-sale-site, matching ^[a-z0-9-]+$ and unique across every file. My Answer is the correct answer — your own reviewed conclusion, and what the app scores against. Community Vote and PDF Suggested are provenance only. All three use the same format: one or more label letters with no separator (A, C, AB), read case-insensitively and order-insensitively.

Tags are trimmed, lowercased, and have inner whitespace hyphenated, so S3; Transfer Acceleration becomes {s3, transfer-acceleration} — URL-safe and consistently cased. Empty segments are dropped.

Why Wrong parsing

Why Wrong packs every wrong-option explanation into one cell as a Markdown bullet list, one entry per line:

- **A:** S3 can only serve files. It cannot run the code that takes an order, and S3 is not a database.
- **B:** EC2 instances, two load balancers, and an RDS database all have to be sized, patched, and monitored by you.
- **C:** Running Kubernetes on EKS means you manage a cluster, node groups, and pod scaling.

• line_pattern^- \*\*([A-E]):\*\*\s+(.+)$ on every non-blank linestrict
• label_setMust exactly equal the present-but-incorrect options — no missing, no extraexact
• no_continuationOne entry per line; a wrapped line is indistinguishable from a malformed entry1
• verifiedEntries parsed across the 40 current rows with zero anomalies120

A line that does not match is a validation error, never skipped. The captured text after the label is stored as why_wrong_md for that option; the - **A:** prefix is consumed by the parser and never stored, since the UI renders each explanation beside its own option and would otherwise repeat the label.

The parser runs with columns: true, bom: true, skip_empty_lines: true, and strict column counts. bom: true matters: spreadsheets routinely write a UTF-8 byte-order mark that would otherwise corrupt the first header name.

Importer pipeline

scripts/import.ts runs as pnpm run import or pnpm run import --dry-run. It always reads the entire content/ directory — there is no single-file mode, because retirement can only be decided from the complete source set.

1. Parse every CSV

   Read all *.csv files in content/. The filename stem sets exam_type for every row in that file.

2. Validate before any write

   Zod checks exact header names; present, well-formed, globally unique Slug; non-empty A–D with optional E; a My Answer that references only present options and never selects every option; Why Wrong lines matching the bullet pattern with an exact label set; Community % within 0–100; and Community Vote/PDF Suggested referencing present options. At least one file and one row are required, so an empty directory cannot retire the whole bank.

3. Render Markdown

   marked renders the three Markdown fields; question and option bodies are stored as plain text.

4. Classify by content hash

   Compute a content_hash per row and classify every slug as new, changed, unchanged, restored, or retired.

5. Write in one transaction

   Lock exam against inserts, finalise expired exams, then abort and report remaining active exam IDs. Upsert new, changed, and restored questions by slug with retired_at = NULL; upsert options by (question_id, label) to preserve IDs through wording and ordering edits; delete only labels removed from that question; then set retired_at on previously active questions absent from the source set.

Change detection

Each question row carries a SHA-256 content_hash over a canonical serialisation of every source-derived value: slug, exam type, body, explanation, study note, sorted tags, the provenance columns, and each option's label, body, correctness, and why-wrong text sorted by label. Anything that would change a database row changes the hash; nothing else does. That reduces “what has been imported and what has not” to one comparison per slug.

Skipping unchanged rows is what makes this usable as files grow and accumulate. It also keeps question.updated_at honest: it moves only when the content actually moved, so it is a real “last edited” timestamp rather than “last time anyone ran the importer”. The importer prints a summary and exits non-zero on validation failure:

saa-c03.csv    40 rows    2 new   3 changed   35 unchanged
gcp-ace.csv    25 rows   25 new   0 changed    0 unchanged
retired:        1 (saa-c03-old-question)

--dry-run performs every step including classification and the summary, then rolls the transaction back. It is the safe way to see what a spreadsheet edit would do before it does it. The hash comparison plus the slug upsert make the import idempotent: running it any number of times converges on the same state, and question.id never changes, so all existing attempt rows stay attached.

Removing a whole question from git retires it instead of deleting it. Retired questions are excluded from new exams, practice lists, filters, and random selection, but remain readable through historical results. Re-adding the same slug clears retirement and preserves identity.

Migration order

Quiz migrations are filename-ordered SQL under apps/quiz/migrations/, applied by scripts/migrate.ts and recorded in schema_migration(version, applied_at). Better Auth tables come from the repository-pinned CLI, never a floating pnpm dlx ...@latest.

1. Create the dedicated quiz database on the existing Postgres instance.
2. Enable pgcrypto for gen_random_uuid().
3. Run the pinned Better Auth CLI migration non-interactively.
4. Assert that "user".id has PostgreSQL type uuid.
5. Run scripts/migrate.ts, then scripts/import.ts.

pnpm run setup:db chains this order so application foreign keys never precede the auth schema they reference.

Authentication and request security

export const auth = betterAuth({
  database: pool,
  advanced: { database: { generateId: 'uuid' } },
  emailAndPassword: {
    enabled: true,
    requireEmailVerification: true,
    sendResetPassword,
  },
  emailVerification: { sendVerificationEmail, sendOnSignUp: true },
  databaseHooks: { user: { create: { before: assertEmailAllowed } } },
});

Verification and reset callbacks are absolute URLs derived from BETTER_AUTH_URL; request headers never guess the public origin. The catch-all endpoint at src/pages/api/auth/[...all].ts exports export const ALL: APIRoute = ({ request }) => auth.handler(request);.

src/middleware.ts resolves the session into locals.user and redirects unauthenticated users to /sign-in. Public paths are /sign-in, /sign-up, /verify-email, /forgot-password, /reset-password, /api/auth/*, and static assets. Unverified users cannot enter protected pages.

Better Auth protects its own /api/auth/* mutations. Application authorization is ownership-only: users can read and write only their own exams and attempts; all signed-in users can read questions; nobody can write questions at runtime. Foreign exam IDs return 404 rather than confirming existence with 403.

Application structure

frontend/astro/apps/quiz/
├── content/
│   ├── saa-c03.csv
│   └── gcp-ace.csv
├── migrations/
│   └── 001_quiz.sql
├── scripts/
│   ├── migrate.ts
│   └── import.ts
└── src/
    ├── lib/
    │   ├── auth.ts          Better Auth server + allowlist hook
    │   ├── config.ts        fail-fast environment parsing and numeric bounds
    │   ├── db.ts            pg Pool singleton
    │   ├── email.ts         SMTP verification and reset mail
    │   ├── queries.ts       all SQL, one exported function per query
    │   ├── scoring.ts       pure selected-set scoring
    │   └── clues.ts         pure clue limit and next reveal
    ├── middleware.ts
    ├── layouts/QuizLayout.astro
    ├── components/
    │   ├── QuestionBody.astro
    │   ├── OptionList.astro
    │   ├── ExplanationPanel.astro
    │   └── ExamTimer.astro
    └── pages/
        ├── index.astro
        ├── sign-in.astro
        ├── sign-up.astro
        ├── verify-email.astro
        ├── forgot-password.astro
        ├── reset-password.astro
        ├── api/auth/[...all].ts
        ├── exam/new.astro
        ├── exam/[id]/[position].astro
        ├── exam/[id]/result.astro
        ├── practice/index.astro
        └── practice/[slug].astro

scoring.ts and clues.ts stay pure and database-free. queries.ts is the only SQL-writing module; pages call named query functions. src/lib/config.ts parses environment values once, so page code never reads raw process.env or invents fallback limits.

Exam mode

Creating an exam

GET /exam/new renders exam type, question count, and duration. Defaults are QUIZ_DEFAULT_QUESTION_COUNT=65 and QUIZ_DEFAULT_DURATION_MINUTES=90, but each exam may override them within 1–200 questions and 1–480 minutes.

1. Resolve the active exam

   In one transaction using the database clock, finalise the user's expired exam. If an active exam remains, 303-redirect to its first unanswered position. The partial unique index resolves concurrent creates to the same active exam.

2. Select a roster

   Run SELECT id FROM question WHERE exam_type = $1 AND retired_at IS NULL ORDER BY random() LIMIT $2. If no question exists, re-render the form without inserting.

3. Persist the deadline

   Insert exam with the actual selected count and deadline_at = transaction_timestamp() + duration_minutes. A smaller bank yields a smaller valid exam and is disclosed on the result screen.

4. Fix the sequence

   Insert unanswered attempt rows at positions 1..N, then 303-redirect to /exam/:id/1.

Answering

GET /exam/:id/:position guards in order: foreign ownership → 404; already submitted → result; database now() >= deadline_at → finalise then result; invalid position → first unanswered. The page shows one question, a countdown, and a numbered position grid with answered state. Exactly one correct option renders radios; multiple correct options render checkboxes. No clues, answers, explanations, or study notes appear during an active exam.

The POST repeats ownership, submission, deadline, and position guards inside a transaction while locking the exam row with SELECT ... FOR UPDATE. It deduplicates submitted IDs, rejects IDs outside the question, persists selection, correctness, and answer time, then redirects to the next unanswered position. When none remain it submits and redirects to results. A previously answered row may be overwritten only while the exam is active and another position remains unanswered.

Scoring and results

Scoring is all-or-nothing: the normalized selected option ID set must exactly equal the correct option ID set. Partial “choose TWO,” over-selected, and blank answers score zero.

GET /exam/:id/result shows correct/total, percentage, time taken, and every question in order with the user's answer, correct answer, explanation, why-wrong text for each incorrect option, study note, and tags. A past-deadline active exam is finalized first; a still-active exam redirects to the first unanswered position. Results are never exposed before submission.

Practice mode

GET /practice lists non-retired questions filtered by exam type and tag, labels each with the user's most recent answered result, and offers a random question within the active filter.

Current attempt

GET /practice/:slug loads the unique unanswered practice attempt, creating it with INSERT ... ON CONFLICT DO NOTHING when absent and then selecting the winning row. Creating on page load makes clue state survive refreshes. A retired slug is readable only through history and cannot start a new attempt.

Progressive clues

• clue_limitmin(QUIZ_MAX_CLUES, wrongOptionCount − 1)N−1
• four_option_single3 wrong options; always leave 2 selectable2
• five_option_choose_two3 wrong options; always leave 2 selectable2

1. Lock the current attempt with SELECT ... FOR UPDATE.
2. If revealed count reached the limit, do nothing and 303-redirect back; the button is already hidden.
3. Otherwise choose a random unrevealed incorrect option, append it to revealed_option_ids, and 303-redirect.

The lock serializes rapid clue requests so they cannot duplicate a reveal, exceed the limit, or lose updates. Revealed options are dimmed and struck through, display their why-wrong rationale, and cannot be selected. The −1 cap ensures a clue never hands over the answer.

Answering and retrying

action=answer locks the same attempt, normalizes IDs, and rejects foreign option IDs or any option already eliminated. It then stores selection, correctness, and answer time. Replaying the POST cannot overwrite an answered attempt.

A clue-assisted correct answer still counts as correct; the revealed-ID count preserves the later distinction between aided and unaided accuracy. “Try again” creates a fresh unanswered attempt through the same conflict-safe path, so practice history accumulates.

Timer

sequenceDiagram
    participant B as Browser
    participant A as Astro route
    participant D as Postgres
    B->>A: "GET or POST exam page"
    A->>D: "lock/read exam and compare now()"
    alt "now() >= deadline_at"
        D->>D: "finalise exam at deadline_at"
        A-->>B: "303 /exam/:id/result"
    else "deadline remains"
        A-->>B: "render page with deadline data attribute"
        B->>B: "cosmetic countdown each second"
    end
    

exam.deadline_at is written once and never changed. Every exam GET and POST compares the database clock under the exam-row lock. The roughly ten-line client script reads a data- attribute, updates once per second, and navigates to results at zero. Closing a tab, suspending a phone, pausing JavaScript, or editing the page cannot add time because the server never consults the client clock.

Error handling

Configuration

Configuration is validated once at startup, and defaults obey the same bounds as submitted values. Invalid or incomplete production configuration stops the server rather than weakening auth or changing exam rules.

Testing and success criteria

Unit · Vitest · no database

• scoring.ts: single-answer success/failure; choose-two exact, partial, and over-selected sets; empty selection.
• clues.ts: min(3, wrong − 1); representative four-option and five-option cases both yield two; never pick correct or repeat a revealed option.
• Deadline comparison before, exactly at, and after deadline_at.
• Import validation rejects a duplicate slug, a slug failing ^[a-z0-9-]+$, a missing or misspelled header column, an empty My Answer, a My Answer naming an absent option or selecting every option, a Community % above 100, and an E value with an empty D.
• Why Wrong parsing splits the - **B:** … bullet list into the right options and strips the label prefix; rejects a missing label, an extra label naming a correct option, a non-matching line, and a wrapped continuation line. Round-trips all 120 entries across the 40 real rows with zero anomalies.
• Answer-label parsing normalizes A, AB, ba, and A B to the same label set; rejects AA and AF.
• Tag normalisation turns S3; Transfer Acceleration; into {s3, transfer-acceleration}.
• Markdown scope: explanation, study_note, and why_wrong render to HTML; question and option bodies pass through unrendered and are escaped on output.
• content_hash is identical for identical input and unchanged by reordering tags or options; it changes when any imported field changes and never changes for an ignored column (ID, Q#, Flag).
• Submitted-option validation rejects foreign, duplicate, and revealed IDs; scoring uses normalized sets.

Integration · real Postgres

• Import twice without changing row count; update an explanation in place; preserve question.id and attempt resolution.
• A second import with no file edits classifies every row unchanged, performs no writes, and leaves every updated_at untouched.
• Editing one cell classifies exactly that row as changed and every other row as unchanged.
• Two files in content/ import as two exam types; adding a second file leaves the first file's rows unchanged and retires none of them.
• --dry-run reports the same classification as a real run and commits nothing.
• A no-op import succeeds while an exam is active; an import with real changes aborts and names the active exam.
• Remove an option while preserving survivor IDs; historical selections show the removed-option placeholder.
• Remove, retire, exclude, historically render, and re-add a question with the same ID.
• Exam creation inserts exactly N attempt rows at positions 1..N.
• Concurrent practice loads yield one unanswered attempt; concurrent clues cannot exceed the limit.
• A deadline POST racing a result GET produces one submitted exam and no late persisted answer.
• The generated Better Auth schema exposes "user".id as PostgreSQL uuid before app migration.

End-to-end · Playwright

• Sign in, start an exam, answer two, reload, recover selections and correct remaining time, submit, and verify score.
• Opening an exam after its deadline auto-submits.
• A cross-origin application POST is rejected without mutation.
• Use every practice clue, observe the button disappear, answer, then see why-wrong feedback for all incorrect options including never-revealed ones.
• Reject a non-allowlisted sign-up; block an allowlisted user before verification; complete a password reset.

Out of scope for v1

Immutable question revisions — planned phase 2

v1 refuses to import while any exam is in progress. That is safe but assumes a maintenance window. Once several people take exams throughout the day there may be no moment when nothing is active, and the question bank becomes impossible to update. The fix is to stop editing questions in place: every import creates a new immutable revision, and each exam is pinned to the revision it started with.

1. Alice starts an exam using question 10, revision 1.
2. An import creates question 10, revision 2.
3. Alice's active exam keeps showing and scoring revision 1.
4. Exams started after the import use revision 2.
5. Revision 1 stays readable for Alice's result and historical review.

The importer inserts a new question_revision when content_hash differs instead of updating, and never mutates an existing revision. Reads resolve through attempt.question_revision_id for history and through the latest non-retired revision for new sessions.

Migration from v1: each existing question becomes identity plus one revision — its content columns and content_hash move to revision 1, its answer_option rows re-parent to revision 1, and every existing attempt backfills question_revision_id to that revision. No attempt history is lost, because option IDs are carried over rather than regenerated.

Other deferrals