PostgreSQL 17. The schema is owned by Flyway
(backend/src/main/resources/db/migration); Hibernate only validates it
(ddl-auto=validate). Never edit an applied migration: add V2__...sql.
| Table | Purpose | Key constraints |
|---|---|---|
users |
Accounts | unique e-mail (stored lower-case, checked), unique username case-insensitively (lower(username) index), role USER/ADMIN, username pattern, country ^[A-Z]{2}$ |
auth_tokens |
E-mail verification and password reset | only the SHA-256 hash is stored (unique); purpose check; single use (used_at) and expiry |
contests |
Contest metadata | ends_at > starts_at, at most 30 days; published flag |
contest_registrations |
Participants | PK (contest_id, user_id) |
problems |
Statements and limits | unique (contest_id, position) (deferrable), position 1-26, time 250-10000 ms, memory 32-512 MB, points 1-10000, difficulty check |
test_cases |
Judge data | unique (problem_id, position), ≤ 1 MiB input and output each, sample flag |
submissions |
Source, verdict and judge-queue state | unique (user_id, client_request_id) for idempotency; language and status checks; source ≤ 64 KiB |
submission_results |
Per-test verdict, time, sample output | PK (submission_id, position) |
comments |
Problem discussions | self-reference for replies (one level enforced in the API), non-blank body ≤ 2000 |
spring_session, spring_session_attributes |
HTTP sessions | Spring Session JDBC schema |
All foreign keys cascade on delete except contests.created_by (set null), so
deleting a user removes their submissions, registrations and comments, and
deleting a contest removes its problems, tests and submissions.
| Index | Query |
|---|---|
idx_submissions_queue (available_at, id) WHERE status = 'QUEUED' |
Judge worker claim (FOR UPDATE SKIP LOCKED) and queue gauge |
idx_submissions_user_created (user_id, created_at DESC, id DESC) |
"My submissions", newest first |
idx_submissions_contest_standings (contest_id, user_id, problem_id, created_at) WHERE in_contest |
Standings |
idx_submissions_accepted (user_id, problem_id) WHERE status = 'ACCEPTED' |
Solved flags, profile stats, leaderboard |
idx_submissions_problem (problem_id) |
Rejudge, cascades |
idx_contests_published_starts, _ends |
Contest list by status |
idx_comments_problem_top (problem_id, created_at DESC) WHERE parent_id IS NULL |
Discussion page |
idx_comments_parent (parent_id, created_at) |
Replies |
spring_session_ix3 (principal_name) |
Revoke all sessions of a user |
EXPLAIN ANALYZE of the hot queries on seeded data is in
performance.md.
Standings and the leaderboard are computed by SQL at read time from
submissions (see StandingsService and UserQueries). There are no
counters to keep in sync; a rejudge changes standings immediately.
docker compose --profile seed run --rm seed loads demo users, three contests
(ended, ongoing, upcoming) and eight problems with generated tests. It does
nothing if admin@contester.dev already exists. Demo password:
SEED_PASSWORD (default Demo-password-1). Demo submissions are inserted as
already judged so that standings have content without running the judge; they
carry no run time.
scripts/backup.sh (pg_dump custom format, verified with pg_restore --list,
retention) and scripts/restore.sh. Procedure and drills:
disaster-recovery.md.