Design an Online Judge System (LeetCode)

CREATE TABLE problems (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    difficulty VARCHAR(20) NOT NULL CHECK (difficulty IN ('EASY', 'MEDIUM', 'HARD')),
    current_version INT NOT NULL DEFAULT 1
);

CREATE TABLE problem_versions (
    problem_id INT NOT NULL REFERENCES problems(id),
    version INT NOT NULL,
    statement_s3_key TEXT NOT NULL,
    time_limit_ms INT NOT NULL DEFAULT 2000,
    memory_limit_kb INT NOT NULL DEFAULT 256000,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (problem_id, version)
);

CREATE TABLE test_suites (
    problem_id INT NOT NULL REFERENCES problems(id),
    version INT NOT NULL,
    manifest_sha256 CHAR(64) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (problem_id, version),
    FOREIGN KEY (problem_id, version) REFERENCES problem_versions(problem_id, version)
);

CREATE TABLE submissions (
    id VARCHAR(50) PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    contest_id UUID,
    problem_id INT NOT NULL REFERENCES problems(id),
    problem_version INT NOT NULL,
    test_suite_version INT NOT NULL,
    language VARCHAR(20) NOT NULL,
    language_version VARCHAR(50) NOT NULL,
    runtime_image_digest VARCHAR(128) NOT NULL,
    code_s3_url TEXT NOT NULL, -- Raw source is private in S3
    code_sha256 CHAR(64) NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('QUEUED', 'COMPILING', 'RUNNING', 'ACCEPTED', 'WRONG_ANSWER', 'TLE', 'MLE', 'COMPILE_ERROR', 'RUNTIME_ERROR', 'SYSTEM_ERROR', 'CANCELED')),
    result_sequence INT NOT NULL DEFAULT 0,
    runtime_ms INT,
    memory_kb INT,
    idempotency_key VARCHAR(128) NOT NULL,
    request_sha256 CHAR(64) NOT NULL, -- Hash of the normalized request for idempotency conflict detection
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    UNIQUE (user_id, idempotency_key),
    FOREIGN KEY (problem_id, problem_version) REFERENCES problem_versions(problem_id, version),
    FOREIGN KEY (problem_id, test_suite_version) REFERENCES test_suites(problem_id, version)
);

CREATE INDEX idx_submissions_user_created ON submissions(user_id, created_at DESC);
CREATE INDEX idx_submissions_problem_created ON submissions(problem_id, created_at DESC);
CREATE INDEX idx_submissions_contest_created ON submissions(contest_id, created_at DESC);

CREATE TABLE submission_dispatch_outbox (
    id UUID PRIMARY KEY,
    submission_id VARCHAR(50) NOT NULL UNIQUE REFERENCES submissions(id),
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMPTZ
);

CREATE INDEX idx_submission_dispatch_pending
    ON submission_dispatch_outbox(created_at)
    WHERE published_at IS NULL;

CREATE TABLE test_cases (
    problem_id INT NOT NULL,
    suite_version INT NOT NULL,
    test_case_id INT NOT NULL,
    input_s3_key TEXT NOT NULL, -- Hidden test data stays private
    output_s3_key TEXT NOT NULL, -- Expected output stays outside the untrusted process
    is_hidden BOOLEAN NOT NULL,
    PRIMARY KEY (problem_id, suite_version, test_case_id),
    FOREIGN KEY (problem_id, suite_version) REFERENCES test_suites(problem_id, version)
);