Our session history rebuilds last week's quiz from the pack, and the pack is not where the quiz was recorded

PubTrivia keeps every quiz you have run: the final leaderboard in full, and a question-by-question record of what each table answered, for the landlord who gets a complaint a week later. The data for that is all in the database. The screen that reads it back had a bug I only found by reading two files in the same sitting, and it is the kind that no test failure and no error report will ever hand you.

What a session actually records

When a host starts a session, the questions are drawn by Postgres:

// 5. Draw a random question_order for this session.
//    Postgres does the shuffling and the slicing, so a 500-question pack used
//    for a 20-question round returns 20 ids rather than 500 that are then
//    shuffled and thrown away.
const drawnQuestions = await db
    .select({ id: questionsTable.id })
    .from(questionsTable)
    .where(eq(questionsTable.pack_id, packId))
    .orderBy(sql`random()`)
    .limit(validConfig.questions_count)

const question_order = drawnQuestions.map((q) => q.id)

Two things follow from random(). The first is the product reason it exists: a venue running the same pack on consecutive Tuesdays should not get the same twenty questions in the same order. The second is that the identity of the quiz is now that array of ids, and nothing else in the database knows it. So it is stored, on the session, in its config, and the live game reads from it:

export async function resolveQuestionId(
    config: SessionConfig,
    packId: string,
    index: number,
    executor: Selector = db,
): Promise<string | undefined> {
    const fromOrder = config.question_order?.[index]
    if (fromOrder) return fromOrder
    ...
}

question_order[N] is the question shown at session index N. That is the record. It survives the night, it is in a jsonb column, and it is correct.

What the history screen reads instead

db.select()
    .from(questionsTable)
    .where(eq(questionsTable.pack_id, session.pack_id))
    .orderBy(questionsTable.order_index)
    .limit(session.questions_count),

The first questions_count questions of the pack, in pack order. Not the ones that were asked. The session's own question_order is sitting two fields away in the row this query already loaded, and the query does not look at it.

Then the answer statistics are joined on by id:

const questionBreakdown = questions.map((q, i) => {
    const stats = answerMap.get(q.id) ?? { total: 0, correct: 0 }
    return {
        question_number: i + 1,
        question_text: q.question_text,
        correct_answer: q.correct_answer,
        total_answers: stats.total,
        correct_answers: stats.correct,
        accuracy_pct: stats.total > 0 ? Math.round((stats.correct / stats.total) * 100) : 0,
    }
})

Join a list of questions that were not asked against answers keyed by question id, and every row gets { total: 0, correct: 0 } from the ??. The screen renders them as Q1 to Q20 with a progress bar at 0% and "0 / 0 correct". The questions that were actually asked, and that have perfectly good answer rows pointing at them, do not appear at all.

A twenty-question round on a pack of twenty is fine, because the random draw and pack order contain the same set. Our packs run to more than 800 questions across thirteen of them, and the whole point of a big pack is to draw a small round from it. So the failure rate of this screen is roughly "every session a venue would actually run".

What makes it hard to notice is that it does not look broken. The other half of the same modal is right: the leaderboard tab reads leaderboard_entries, the totals read answers, and those are stored facts about the session. Names, ranks, scores and player counts all check out. Only the questions tab is a reconstruction, and a reconstruction renders as neat rows of zeroes rather than as an error.

The fix, and the two-line version of it

The ids are already there. The fetch becomes "these questions" instead of "the first N of the pack", and then sorts into the recorded order, because IN does not promise one:

const order: string[] = Array.isArray(config.question_order) ? config.question_order : []

const rows = order.length
    ? await db.select().from(questionsTable).where(inArray(questionsTable.id, order))
    : await db.select().from(questionsTable)
        .where(eq(questionsTable.pack_id, session.pack_id))
        .orderBy(questionsTable.order_index)
        .limit(session.questions_count)

const byId = new Map(rows.map(r => [r.id, r]))
const questions = order.length ? order.map(id => byId.get(id)).filter(Boolean) : rows

The fallback branch is the old query, kept on purpose: sessions that predate question_order have an empty array, and pack order is the only guess available for them. That is exactly the comment the game engine already carries above resolveQuestionId, which is the clue I should have followed the first time I read it.

The deeper problem is that history points at living rows

Fixing the order does not make the history a record. It makes it a correct pointer into a mutable table, which is a different thing, and the quiz builder is allowed to change what it points at.

Editing a question writes over the text in place:

await db
    .update(questionsTable)
    .set({
        question_text: (formData.get('question_text') as string).trim(),
        ...
    })
    .where(eq(questionsTable.id, questionId))

So fixing a typo in a question today changes what the history says was asked in March. The answer rows do not move, which means the accuracy figure stays attached to the old wording while the screen shows the new one.

Deleting a question is worse, and it is worse in the safe direction. Our foreign keys are deliberately all ON DELETE no action:

ALTER TABLE "answers" ADD CONSTRAINT "answers_question_id_questions_id_fk"
  FOREIGN KEY ("question_id") REFERENCES "public"."questions"("id")
  ON DELETE no action ON UPDATE no action;

Every answer references its question, so Postgres refuses to delete a question anybody has ever answered. Which means deletePack, which deletes the pack's questions first, cannot delete a pack that has been used, and the host sees whatever an unhandled foreign key violation looks like in a server action rather than a sentence explaining that their history depends on it.

And deleteQuestion re-indexes the survivors:

UPDATE questions SET order_index = v.order_index
FROM (VALUES ('<id>'::uuid, 0), ...) AS v(id, order_index)
WHERE questions.id = v.id

Which is the right thing for the builder and another way for pack order to stop meaning what it meant on the night. Every one of these is an argument for the same thing: the fix above makes history read the recorded ids, and the real repair is for a completed session to own a copy of the question text it asked, rather than a pointer to a row somebody can edit.

What I would take from this

The system had the right data and read the wrong data, and the reason is boring: two features were built at different times by somebody who, in each sitting, knew what a question was. The live engine learned that a session has its own order. The history screen was written before that and never got told.

Two habits come out of it. First, when you add a field that changes what the canonical answer to a question is, grep for every other query that answers the same question, because "which questions did this session ask" had two implementations and only one got the memo. Second, be suspicious of any screen whose empty state and its wrong state look identical. "0 / 0 correct" on twenty rows is indistinguishable from a quiet night, and that is the only reason this survived as long as it did.