Low Level Design: Quiz Engine – Tech Interview Dot Org

Quiz Engine Data Model

A quiz engine supports timed assessments, randomization, auto-grading, leaderboards, and anti-cheat mechanisms.

Core Tables

Quiz (id, title, time_limit_seconds INT, randomize_questions BOOL,
      randomize_options BOOL, pass_score INT, max_attempts INT,
      show_answers_after: attempt|pass|never)

Question (id, quiz_id, text,
          type: single|multiple|true_false|fill_blank,
          options JSONB, correct_answers JSONB,
          explanation TEXT, points INT)

QuizAttempt (id, quiz_id, user_id,
             status: in_progress|submitted|graded,
             started_at, submitted_at, score INT, passed BOOL,
             question_order JSONB, answers JSONB)

Time Enforcement

Each attempt has a hard deadline: started_at + time_limit_seconds. The server auto-submits an in-progress attempt when the deadline passes — either via a background job or by checking expiry on the next request.

-- Background job
UPDATE quiz_attempts SET status = 'submitted', submitted_at = NOW()
WHERE status = 'in_progress'
  AND started_at + INTERVAL '1 second' * (
        SELECT time_limit_seconds FROM quizzes WHERE id = quiz_id
      ) < NOW();

Randomization

When an attempt is created, question IDs are shuffled (if randomize_questions) and option arrays are shuffled per question (if randomize_options). The resulting order is stored in question_order JSONB so grading always compares against the same arrangement.

Auto-Grading

-- For each objective question, compare submitted answer to correct_answers
score = SUM(q.points) WHERE submitted_answer = correct_answer

-- Mark attempt
passed = (score >= quiz.pass_score)

Fill-in-the-blank answers are normalized (trimmed, lowercased) before comparison. Open-ended questions are flagged for manual review.

Leaderboard

SELECT user_id, score,
       EXTRACT(EPOCH FROM (submitted_at - started_at)) AS duration_seconds,
       RANK() OVER (ORDER BY score DESC,
                    (submitted_at - started_at) ASC) AS rank
FROM quiz_attempts
WHERE quiz_id = ? AND status = 'graded'
ORDER BY rank;

At equal scores, faster completion ranks higher.

Anti-Cheat Measures

Tab-switch detection: Page Visibility API fires an event when the browser tab loses focus. The client logs each event and sends it to the server.
Suspicious event flags: Attempts accumulate a flags JSONB column recording event type, timestamp, and count.
Rate limiting: Enforce max_attempts per user per quiz, and optionally cap attempts per day via a counter in Redis.
IP tracking: Log IP per attempt for anomaly detection (multiple accounts from same IP).

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you enforce a time limit in a quiz engine?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Record started_at when the attempt begins. The deadline is started_at + time_limit_seconds. A background job auto-submits any in_progress attempt past its deadline. The client also sends a submit request when a local countdown reaches zero, with the server authoritative on timing.”
}
},
{
“@type”: “Question”,
“name”: “How do you randomize questions and options without breaking grading?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “On attempt creation, shuffle question IDs and option arrays per question. Persist the resulting order in question_order JSONB on the QuizAttempt row. Grading always references this stored order to map submitted answers back to the original correct_answers.”
}
},
{
“@type”: “Question”,
“name”: “How does the quiz leaderboard handle ties?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Rank by score DESC first, then by (submitted_at – started_at) ASC for time taken. This means two users with equal scores are separated by who completed the quiz faster. Use RANK() OVER (ORDER BY score DESC, duration ASC) in SQL.”
}
},
{
“@type”: “Question”,
“name”: “What anti-cheat measures can a quiz engine implement?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Tab-switch detection via the Page Visibility API logs focus-loss events to a flags JSONB column on the attempt. Rate limiting caps attempts per user per day via Redis counters. IP logging across attempts flags multiple accounts from the same source. Max attempts per quiz is enforced at attempt creation.”
}
}
]
}