Inside the QuizRush Question Database Engineering

Introduction: Engineering a Real-Time Trivia Platform

Building a high-concurrency competitive trivia platform presents a set of unique backend engineering challenges. When thousands of players simultaneously request randomized 5-question trivia rounds, the system must deliver questions in sub-15 milliseconds while guaranteeing:

1. Zero Repeat Questions: Users should not encounter recently answered questions.
2. Category Equilibrium: Rounds must maintain a balanced distribution of topic domains (Science, Tech, History, Culture).
3. Cheat-Resistant Verification: Server-side timestamp validation must prevent automated submission tampering.
4. Zero-Lock Concurrency: Database locks must never block concurrent rounds during peak competitive tournament hours.

In this deep-dive technical article, we explore the architectural decisions, database index optimizations, and caching layers powering the QuizRush engine on PHP 8.3 and Laravel.

1. High-Performance Randomization Without ORDER BY RAND()

In relational database systems like MySQL and PostgreSQL, executing an ORDER BY RAND() LIMIT 5 query forces the database engine to perform a full table scan, generate random values for every record, sort the temporary table in memory, and discard all rows except the requested batch.

As a question bank grows from 5,000 to 100,000+ records, ORDER BY RAND() query execution times skyrocket from 2ms to over 800ms, causing database CPU spikes and thread exhaustion.

The Indexed Sampling Pipeline

To achieve consistent sub-10ms response times regardless of database size, QuizRush uses an Indexed Random Offset Sampling Algorithm:

Incoming Round Request ➔ Read Active ID Bitmaps from Redis ➔ Subtract User History Set ➔ Sample 5 Random IDs ➔ Bulk Hydrate via Primary Key

namespace App\Services;

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class QuestionSelector
{
    public function getRoundQuestions(int $userId, int $count = 5): array
    {
        // 1. Fetch cached active question IDs
        $allIds = Cache::remember('active_question_ids', 3600, function () {
            return DB::table('questions')
                ->where('is_active', true)
                ->pluck('id')
                ->all();
        });

        // 2. Fetch user recent question attempts within the suppression window
        $attemptedIds = DB::table('user_question_attempts')
            ->where('user_id', $userId)
            ->where('created_at', '>=', now()->subDays(10))
            ->pluck('question_id')
            ->all();

        // 3. Perform fast array diff in memory
        $eligibleIds = array_diff($allIds, $attemptedIds);

        // 4. Fallback if candidate pool is depleted
        if (count($eligibleIds) < $count) {
            $eligibleIds = $allIds;
        }

        // 5. Pick random keys in PHP memory (O(1) complexity)
        $selectedKeys = (array) array_rand($eligibleIds, min($count, count($eligibleIds)));
        $selectedIds = array_map(fn($key) => $eligibleIds[$key], $selectedKeys);

        // 6. Single indexed primary key lookup
        return DB::table('questions')
            ->whereIn('id', $selectedIds)
            ->get()
            ->shuffle()
            ->all();
    }
}

By decoupling the random selection step into memory and querying the database exclusively through indexed primary key lookups (WHERE id IN (...)), database load drops by over 94%.

2. Anti-Repeat Window Safeguards & Category Balancing

Leaderboard integrity hinges on testing genuine recall and analytical speed rather than memorization of repetitive question sequences.

The 10-Day Sliding Suppression Window

Multi-Category Round Balancing

• 2 General Knowledge / Science questions
• 1 Technology & Engineering question
• 1 History / Geography question
• 1 Pop Culture / Arts question

This guarantees fair difficulty curves across all player demographics.

"Intelligent filtering guarantees that leaderboard rankings reflect broad knowledge and cognitive speed, not simple repetitive memorization."

3. Real-Time Scoring & Anti-Cheat Validation

Because competitive leaderboards reward top performers, client-side score calculations must never be trusted. QuizRush utilizes an end-to-end server-verified scoring workflow:

Step 1: Encrypted Round Session Generation

• The round session ID
• The 5 chosen question IDs in exact sequence
• A Unix timestamp of round initialization (start_time)
• A cryptographic signature generated using the application key

Step 2: Answer Verification & Time Windowing

1. Signature Validity: Ensuring the payload originated from an authorized game server.
2. Speed Bounds: If an answer is received in under 400 milliseconds, it is analyzed for bot automation heuristics.
3. Timeout Enforcement: Answers received after the allotted question countdown plus a 1.5-second network buffer are rejected as expired.

public function validateAnswerSubmission(Request $request, Question $question): bool
{
    $startTime = $request->input('round_start_timestamp');
    $elapsedSeconds = microtime(true) - $startTime;

    // Reject answers submitted past maximum time limit + network grace period
    if ($elapsedSeconds > ($question->time_limit + 1.5)) {
        return false;
    }

    // Verify correct option hash
    return hash_equals(
        hash('sha256', (string) $question->correct_option_id),
        (string) $request->input('option_token')
    );
}

4. Flat-File CMS Integration for High-Throughput Content

While user profiles, quiz results, and leaderboard tallies reside in optimized MySQL tables, the editorial blog and knowledge base utilize a zero-database flat-file architecture.

Markdown files (.md) with YAML frontmatter headers are compiled directly into cached HTML structures by App\Services\BlogRepository. This architecture provides:

• Zero Database Load for Informational Content: Search engine crawlers and reading users do not consume database connection pool slots.
• Git Version Control: All editorial changes, code samples, and typography adjustments are versioned and audited via source control commits.
• Microsecond Response Times: Cached file parses deliver instantaneous HTML responses.

Frequently Asked Questions (FAQ)

What tech stack powers the QuizRush backend?

How does QuizRush prevent database bottlenecks during tournament spikes?

Can automated scripts or bots cheat the timer on QuizRush?

How often is the QuizRush question database updated?