Build a No-Repeat Question Draw for a Small TV-Format Trial

A rehearsal quiz with nine approved questions has a failure mode that doesn't look like a bug. The operator clicks, a question appears, three rounds later the same question appears again, and nobody can say whether the format is thin or the software is broken. If your draw is a single call to a random picker, this is not a defect. Sampling with replacement is supposed to behave that way. Nine items, nine draws, and the chance of at least one repeat is high enough that you'll see it the first afternoon.

The fix isn't more randomness. It's less. You build a finite ordered list once, consume it from the front, and write down where you are. At that point "no repeats" stops being a property of the random number generator and becomes a property of your stored state, which is something you can actually inspect, resume, and defend to a producer.

What follows is a small local drawing tool for a single operator: an approved pool with stable identities, one stored order, a draw that survives interruption, and an honest stopping point when the questions run out. Everything here is a design, not a transcript. No code below has been run against a database, and the walkthrough at the end is a hand trace of the state machine, not output from a session. Where I describe what a crash does, I mean what the design is built to do, and what you should test before trusting.

Validate the pool and generate one stored order

Start with identity. Every approved question gets an identifier that does not change: Q-101, Q-102, or a slug, or a UUID if you like, but it is assigned once and never reassigned. The prompt text can be rewritten between rehearsals; a prompt is content, not identity. If you identify questions by their text, then editing a typo in question six creates a question the trial has never seen, and your no-repeat guarantee quietly leaks.

That gives you the first validation, and it happens before any trial exists. Two rows with the same identifier are a duplicate identity, and you reject the import rather than picking one. In SQLite a PRIMARY KEY on question_id refuses the second row for you; in a spreadsheet or a JSON file, check len(set(ids)) == len(ids) explicitly. Whichever route you take, make the failure loud and make it happen at import time, when someone can still fix the sheet.

Next, record what version of the pool you approved. Compute a short fingerprint over the sorted identifiers and prompts — twelve hex characters of a SHA-256 over id \x00 prompt \x00 lines is plenty for this — and store it alongside the trial. The fingerprint isn't a security check; it's a drift detector. Its only job is to answer one question later: is this the same pool the trial was built from?

Then build the trial. You need a new trial_id, the pool revision you just computed, a status, and a position. Position means the index of the next question to draw, starting at zero.

Now the shuffle. Python's random module documentation describes shuffle as reordering a sequence in place, and sample as drawing without replacement; a full-length sample is a permutation, so either gives you one. What matters is that you do it once and then save the result:

import random

rng = random.Random(20260913)          # only if you want a repeatable controlled example
order = [q["question_id"] for q in approved_pool]
rng.shuffle(order)
# write order[0], order[1], order[2] ... into draw_order with their positions

The seed deserves a caution, because it's easy to over-trust. The same documentation notes that reproducibility depends on the saved method and state — the promise is narrower than "any seed reproduces any function forever." So treat the seed as a convenience for re-running a controlled example, and treat the saved ordered IDs as the actual rehearsal record. The order is data. It's what you re-read on Tuesday when someone asks why question four came before question seven.

Two things not to do. Don't reshuffle the pool on every press; that's sampling with replacement wearing a stored order as a costume. And don't reach for secrets and assume you've solved anything — the documentation is explicit that the random module is not suitable for security purposes, and swapping in a cryptographic generator still leaves you with a draw order that anyone holding the file can read. If this rehearsal is going to be a real competition with real stakes, that's a different build.

Make one draw a recoverable operation

Here's the shape of the operation, and it's worth saying as a sentence before it becomes code: validate the state, choose the next identifier, save the advanced position and the request together, and only then show the question to the operator. Display is the last step, not the first.

The first piece is request identity. The operator console generates an identifier for each requested draw — R-1, R-2, and so on — and that identifier is scoped to the trial. A request that has already been completed returns its stored question instead of advancing. This handles the double-click and the retry loop, which are the two ways a single operator accidentally asks for two questions when they meant one.

The second piece is the transaction, and SQLite is a reasonable local choice for it. Three tables carry the whole thing:

CREATE TABLE question (
  question_id  TEXT PRIMARY KEY,
  prompt       TEXT NOT NULL
);

CREATE TABLE trial (
  trial_id      TEXT PRIMARY KEY,
  pool_revision TEXT NOT NULL,
  status        TEXT NOT NULL CHECK (status IN ('active', 'exhausted')),
  position      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE draw_order (
  trial_id    TEXT NOT NULL,
  position    INTEGER NOT NULL,
  question_id TEXT NOT NULL,
  PRIMARY KEY (trial_id, position),
  FOREIGN KEY (question_id) REFERENCES question(question_id)
);

CREATE TABLE draw_request (
  trial_id      TEXT NOT NULL,
  request_id    TEXT NOT NULL,
  question_id   TEXT NOT NULL,
  position_used INTEGER NOT NULL,
  committed_at  TEXT NOT NULL,
  PRIMARY KEY (trial_id, request_id),
  UNIQUE (trial_id, question_id)
);

That UNIQUE (trial_id, question_id) is doing quiet work: the schema itself refuses to serve the same question twice inside one trial, even if your application logic has a bad day. You want guarantees in two places when one of them is a beginner's first draw loop.

The draw itself:

def draw(conn, trial_id, request_id):
    conn.execute("BEGIN IMMEDIATE")          # one writer, from the very start
    try:
        prior = conn.execute(
            "SELECT question_id FROM draw_request "
            "WHERE trial_id = ? AND request_id = ?",
            (trial_id, request_id)).fetchone()
        if prior is not None:
            conn.commit()
            return {"status": "confirmed", "question_id": prior[0], "replayed": True}

        row = conn.execute(
            "SELECT status, position, pool_revision FROM trial WHERE trial_id = ?",
            (trial_id,)).fetchone()
        if row is None:
            raise Refuse("no such trial")
        status, position, revision = row
        if status != "active":
            conn.commit()
            return {"status": status}

        if pool_revision_of(conn) != revision:
            raise Refuse("pool no longer matches this trial")

        total = conn.execute(
            "SELECT COUNT(*) FROM draw_order WHERE trial_id = ?", (trial_id,)).fetchone()[0]
        nxt = conn.execute(
            "SELECT question_id FROM draw_order WHERE trial_id = ? AND position = ?",
            (trial_id, position)).fetchone()
        if nxt is None:                        # defensive; should not normally happen
            conn.execute("UPDATE trial SET status = 'exhausted' WHERE trial_id = ?",
                         (trial_id,))
            conn.commit()
            return {"status": "exhausted"}

        question_id = nxt[0]
        conn.execute(
            "INSERT INTO draw_request "
            "(trial_id, request_id, question_id, position_used, committed_at) "
            "VALUES (?, ?, ?, ?, ?)",
            (trial_id, request_id, question_id, position, utcnow()))
        conn.execute(
            "UPDATE trial SET position = ?, status = ? WHERE trial_id = ?",
            (position + 1,
             "exhausted" if position + 1 >= total else "active",
             trial_id))
        conn.commit()
        return {"status": "confirmed", "question_id": question_id, "replayed": False}
    except Exception:
        conn.rollback()
        raise

A word about the database configuration, because this is where people assume more than they get. Python's connection context manager commits on a clean exit and rolls back on an exception, and that's genuinely useful — but it doesn't open the connection, it doesn't close it, and it doesn't necessarily begin the transaction where you assume it did. In the legacy isolation mode, a transaction starts at the first statement that needs one, which means the SELECT reading your position can run outside the transaction you thought you were in. So set the connection explicitly, use BEGIN IMMEDIATE so the write lock is taken at the start rather than at the first write, and test that behavior against your Python version instead of reading about it. The same caution applies to durability settings: the journal mode and the synchronous pragma decide what a crash can cost you, and if a "confirmed" draw needs to survive a power cut rather than just a process death, check what your file is actually configured to do.

Resume the trial without losing the last confirmed question

Reopening is three loads and three checks. Load the trial row, the stored order, and the position. Load the last confirmed request. Then verify: the pool revision matches, the position equals the number of draw_request rows for this trial, the order has as many rows as the pool has questions, and no identifier appears twice. Write that as a check_state function returning a list of problems, and call it when the tool opens and before each draw.

If a check fails, refuse to draw and print what disagreed. Do not reset, do not quietly create a fresh order, and above all do not shuffle a new one and carry on. A changed pool is the most likely failure: somebody added a tenth question mid-rehearsal. The correct response is a message naming both revisions, and someone deciding what to do — which usually means finishing this trial or starting a new one, not pretending the old order still covers the new pool.

The two crash cases are different, and the difference is the whole point of committing before display.

A crash before the commit leaves nothing behind. The rollback discards the pending insert and the position update together, so the request is unconfirmed and retryable. Retrying it with the same request identity draws the same next question, because nothing advanced.

A crash after the commit but before the operator ever saw the question is the harder case. The record exists. The position moved. The trial honestly cannot pretend the draw didn't happen, so the tool needs a "redisplay last confirmed" action that reads the stored answer and shows it again without consuming anything. That action is not a draw, it's a lookup, and it deserves its own button so that nobody under pressure reaches for "next" to recover a question that was already taken.

That's also where the design has a real cost, and it's worth naming. A question consumed but never read aloud stays consumed. The ordinary repair is the redisplay button. If the operator judges the item unusable for some reason, the honest fallback is to stop and start a new trial deliberately, with the previous trial's history intact — not to invent a mechanism that quietly rewinds the position. Adding a "skip" that returns an item to the pool mid-trial is exactly how used IDs start circulating under the old trial name.

Treat exhaustion and reset as different states

Exhaustion is a status value, not a flag on the screen. When the final draw commits, set status = 'exhausted' in the same transaction that advances the position, so the invariant holds: a trial is exhausted exactly when its position equals the length of its order. Reopening the file later shows an exhausted trial, and reopening it again a week after that shows the same thing. Exhaustion survives restarts because it's stored, not because something is still running.

A request against an exhausted trial returns the exhausted state. It does not wrap to the beginning, and it does not pick a used question for variety. If you want to know what the last drawn item was, that's the redisplay action, which still works fine on an exhausted trial.

Starting again is a separate, explicit operation. new_trial() mints a new trial ID, fingerprints the current pool, shuffles a fresh order, and writes new rows. The old trial's rows stay exactly where they were. Two rules keep this clean. Never reuse an old trial ID with a new order — that makes the record incoherent and any later reader will misread it. And never recycle used identifiers under the old trial name; a second pass through the same questions is a new trial with a new order, and it should say so.

The interface that serves all of this is one small screen: the trial ID, the pool revision, "position 4 of 9," the last confirmed question, and three actions — redisplay last, draw next, new trial. Resist adding more. Every extra control is another place where the stored state and what the operator believes can drift apart, and drift is the failure this whole build exists to prevent.

Walk a five-question pool by hand

Suppose an approved pool of five invented questions, identified Q-101 through Q-105, with placeholder slugs in this order: ferry timetable, house loaf, lighthouse steps, marimba keys, tunnel opening. The pool revision is a41f7c9b20de. A new trial, T-1, shuffles them and stores this order, which I'm stipulating rather than reporting:

Now walk it.

R-1 arrives. No prior request with that identity, the trial is active, the revision matches. The tool reads position 0, gets Q-103, inserts the request, sets position to 1, commits, and only then displays. The operator reads the lighthouse question.

R-1 again — the operator's finger slipped, or the console retried. The lookup finds the completed request and returns Q-103 with replayed = true. Position stays at 1. No second question is consumed.

R-2 draws Q-101. Position 2.

R-3 is attempted and the power goes out mid-transaction. The rollback discards the insert and the position update. On restart, check_state reports position 2, two request rows, order length 5, no duplicate identifiers. Retrying R-3 draws Q-105. Position 3.

R-4 commits and the display never renders — the console freezes, the operator sees nothing. Reopening, check_state reports position 4 with four request rows, and the last confirmed request is R-4 with Q-102. The operator presses redisplay and sees the house-loaf question. They do not press next. Position remains 4.

R-5 commits Q-104 and, in the same transaction, sets status to exhausted because 5 is not less than 5.

Reopen the file. Status is exhausted. Press redisplay: Q-104 again. Press draw next — there is no next — and the tool returns exhausted, in the same state it was in before. Reopen it next week and nothing has moved.

Now the two rejections. Introduce a duplicate: a second row claiming Q-102 in the pool. The import fails before any trial exists, and if the duplicate somehow lands in the pool table, the fingerprint changes and every trial built against the old revision refuses to draw. That's the drift detector doing its job — a mismatch is reported, not smoothed over.

Last, the reset. Somebody wants a second rehearsal. new_trial() creates T-2 with a fresh order over all five questions, possibly in a different sequence, possibly not. T-1's order, position, and request rows are untouched. A draw request labelled R-1 under T-2 is a different request from R-1 under T-1, because request identity is scoped to the trial. Serve it and the old trial's record doesn't move.

What passing means, and what it doesn't

The behavior worth testing is bounded and specific. Draw to exhaustion and confirm the last draw sets the exhausted status in the same transaction as the position advance. Retry a completed request identity and confirm the same question comes back with no advance. Interrupt before the commit and confirm the retry works and consumes exactly one question. Interrupt after the commit and confirm the stored answer is recoverable without drawing another. Reopen an exhausted trial and confirm it's still exhausted. Feed it a duplicate identifier and a changed pool revision and confirm both are refused rather than absorbed.

Passing those means the stored-state sequence works. It says nothing about whether your questions are good, whether the order is fair, or whether anyone should trust this for a prize. The tool is a single local file operated by one person in one process. If a second console opens against the same database, SQLite will serialize the writes but your request identity scheme won't know about the other window, and you'll get a duplicate that no invariant catches. Randomness from the random module is not a security mechanism, the order is sitting in a readable file, and no part of this is a certified draw. Editing, adjudicating, and scoring questions lives in a different job entirely — as does the scoreboard you'll want next, which is a separate build with its own state to keep straight.

Build the stored sequence first and the interface last. A tool that can name its next unused item, hand back its last confirmed one, and stop at exhaustion is useful on the second rehearsal. A prettier tool that reshuffles on every click is useful on none of them.

Frequently asked questions

Why does a random picker produce repeats in a small trial, and what is the fix?

A single call to a random picker samples with replacement, so repeats are supposed to happen. With nine approved questions and nine draws, the chance of at least one repeat is high enough that the repetition looks like a bug but is not one. The fix is less randomness: build one finite ordered list once, consume it from the front, and write down where you are. At that point no-repeats becomes a property of stored state, which can be inspected, resumed, and defended.

What must be true of question identity and pool version before a trial?

Every approved question gets an identifier that does not change, such as Q-101, a slug, or a UUID, assigned once and never reassigned. Prompt text can be rewritten; a prompt is content, not identity. If questions are identified by text, editing a typo creates a question the trial has never seen. Reject duplicate identifiers at import time, using a PRIMARY KEY in SQLite or an explicit check in a spreadsheet or JSON file. Also record a short fingerprint over the sorted identifiers and prompts, such as twelve hex characters of a SHA-256 over the sorted identifier and prompt lines, stored alongside the trial. The fingerprint is not a security check; it is a drift detector to answer whether this is the same pool the trial was built from.

How should a draw be committed so crashes and retries do not lose or duplicate a question?

Validate the state, choose the next identifier, save the advanced position and the request together, and only then show the question to the operator. Display is the last step, not the first. The operator console generates a request identity such as R-1, R-2, scoped to the trial; a completed request returns its stored question instead of advancing, which handles a double-click or retry. In SQLite, use BEGIN IMMEDIATE so the write lock is taken at the start, and rely on the UNIQUE constraint on trial and question to refuse serving the same question twice inside one trial. A crash before the commit rolls back the pending insert and position update together, so retrying draws the same next question. A crash after the commit but before the operator saw the question is harder: the record exists and the position moved, so the tool needs a redisplay last confirmed action that reads the stored answer without consuming anything. A question consumed but never read aloud stays consumed; if it is judged unusable, the honest fallback is to stop and start a new trial deliberately, not to rewind the position or add a skip that returns the item to the pool.

What should resume and exhaustion checks enforce?

On reopening, load the trial row, the stored order, the position, and the last confirmed request. Verify that the pool revision matches, that the position equals the number of draw_request rows for the trial, that the order has as many rows as the pool has questions, and that no identifier appears twice. Write this as a check_state function that returns a list of problems and call it when the tool opens and before each draw. If a check fails, refuse to draw and print what disagreed; do not reset, create a fresh order, or shuffle a new one and carry on. Exhaustion is a stored status, not a flag on the screen. When the final draw commits, set status to exhausted in the same transaction that advances the position, so a trial is exhausted exactly when its position equals the length of its order. A request against an exhausted trial returns exhausted; it does not wrap to the beginning or pick a used question for variety. Starting again is a separate operation: new_trial mints a new trial ID, fingerprints the current pool, shuffles a fresh order, and leaves the old trial’s rows untouched.

What does passing the draw tests not prove?

Passing the bounded tests means the stored-state sequence works: the last draw sets exhausted status in the same transaction as the position advance, a completed request identity returns the same question without advancing, an interruption before commit retries and consumes exactly one question, an interruption after commit recovers the stored answer without drawing another, an exhausted trial reopens still exhausted, and duplicate identifiers or a changed pool revision are refused rather than absorbed. It says nothing about whether the questions are good, whether the order is fair, or whether anyone should trust the tool for a prize. The tool is a single local file operated by one person in one process. If a second console opens against the same database, SQLite may serialize the writes, but the request identity scheme will not know about the other window, and a duplicate can slip past the invariants. Randomness from the random module is not a security mechanism, the order is sitting in a readable file, and no part of this is a certified draw. Editing, adjudicating, scoring, and a scoreboard are separate jobs with their own state.

Further reading

• Show How Questions Will Be Written for Your Quiz Format — Read when the approved pool needs real questions: it describes the writing, boundary and renewal method that sit behind a vetted answer note.
• Build a Two-Player Buzzer Lockout for a Small TV-Format Trial — A companion build if the trial also needs a buzzer: it defines admission as naming the player and states plainly what the controller cannot measure.