database-design.md

Database

PostgreSQL (Neon in the demo), accessed through Exposed and HikariCP. Three tables. The submissions table is also the judging queue.

erDiagram
    PROBLEMS ||--o{ TEST_CASES : has
    PROBLEMS ||--o{ SUBMISSIONS : "judged against"

    PROBLEMS {
        int id PK
        varchar title
        text description
        int time_limit_ms
        int memory_limit_mb
    }
    TEST_CASES {
        int id PK
        int problem_id "no foreign-key constraint"
        text input
        text expected_output
    }
    SUBMISSIONS {
        int id PK
        int problem_id
        varchar language
        text code
        varchar verdict "QUEUED, RUNNING or a final verdict"
        text output
        text test_results "JSON array of per-test results"
        varchar created_at "UTC"
        bigint runtime_ms
        bigint memory_kb
        bigint claimed_at "lease: epoch ms of the last heartbeat"
        int attempts "claims so far"
    }

Loading

submissions as a queue

Column	Role
verdict	QUEUED → RUNNING → a final verdict. Only QUEUED rows can be claimed.
claimed_at	Set when a worker claims the row and refreshed every 10 s while it judges. NULL when idle.
attempts	Incremented on every claim. The reaper fails a row with an expired lease after 2 attempts.

Claiming one row, atomically and without two workers ever taking the same one:

UPDATE submissions
SET verdict = 'RUNNING', claimed_at = :now, attempts = attempts + 1
WHERE id = (
    SELECT id FROM submissions
    WHERE verdict = 'QUEUED'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING id;

The reaper, every 30 s: rows in RUNNING whose claimed_at is older than 60 s go back to QUEUED, or to INTERNAL_ERROR once attempts reaches 2. A worker only writes its result while the row is still RUNNING, so a row the reaper took away is never overwritten by a stale worker.

Per-test results

test_results holds a JSON array: test number, passed, expected and received output (received output is capped at 64 KB), status (OK, WA, TLE, MLE, OLE, RE), CPU time and peak memory. Rows from before these fields existed still decode, because every newer field has a default.

Migrations

Tables are created with SchemaUtils.create at start-up. Columns added later (claimed_at, attempts) are added with ALTER TABLE ... ADD COLUMN IF NOT EXISTS, so an existing database upgrades itself on the next deploy.

Times

created_at is written in UTC regardless of the server's time zone, and the API returns it in Indian Standard Time.