Data model
PostgreSQL owns all arena state. SQL migrations in server/src/db/migrations/ apply in filename order, each in its own transaction, tracked in schema_migrations.
Migrations
| File | What it creates |
|---|---|
| 001_initial.sql | models · matchups · responses · preferences · index preferences_winner_idx |
| 002_conversations_and_turns.sql | conversations · turns · index turns_conversation_idx; adds matchups.harness_version and the six response-metrics columns; backfills one conversation + turn 0 per pre-existing matchup |
| 003_model_ratings.sql | model_ratings |
| 004_style_ratings.sql | model_style_ratings · style_control_coefficients |
| 005_rating_history.sql | model_rating_history · index model_rating_history_computed_idx |
| 006_arena_mode.sql | matchups.mode (blind | shadow, default blind) |
| 007_matchup_steers.sql | matchups.steers JSONB array of mid-stream operator instructions |
| 008_matchup_provider_models.sql | matchups.slot_a/b_provider_model_id — snapshot of each slot's models.provider_model_id at insert time; backfills existing rows from the current roster |
Renamed after shipping. Migrations 002–004 once had phase-era filenames, so the runner first rewrites the legacy rows (002_phase_one.sql, 003_phase_two.sql, 004_phase_three.sql) to the current names — an existing database does not re-run them. Never edit an applied migration; add the next NNN_description.sql.
Who writes what
| Writer | Tables |
|---|---|
| Chat route POST /api/arena/chat | conversations · matchups · turns · responses; appends to matchups.steers on mid-stream steer |
| Vote route POST /api/arena/vote | preferences |
| Model seed db:seed / db:seed:mock | models |
| Rating worker, default pass | model_ratings |
| Rating worker, --style pass | model_style_ratings · style_control_coefficients |
| Demo-data seeder db:seed:demo | everything above except models — see Setup → Demo data |
The default worker pass upserts model_ratings and appends the same rows to model_rating_history in one transaction. The split is strict both ways: the server only reads the four worker-owned rating tables (via LEFT JOIN), and the worker only reads the request-path tables. The demo seeder is the single exception — it writes both sides, which is why it is opt-in and never automatic.
Tables at a glance
models
catalog of comparable models; only enabled ones enter matchmaking
conversations
linear chat container + anonymous session ownership
turns
ordered matchup; parent points only to the prior winning response
matchups
blind or shadow head-to-head; randomized slots, provider-model snapshots, token hash, harness version, mode
responses
content + TTFT, duration, tokens, Markdown density, model version
preferences
one vote per matchup (unique constraint), winner nullable
model_ratings
worker-written Bradley-Terry rating, CI, component id, games
model_style_ratings
style-controlled rating, CI, component id — heavier periodic pass
style_control_coefficients
fitted style confounder coefficients, one row per feature
model_rating_history
append-only rating snapshot per model per refit — the rating-over-time trail
schema_migrations
migration bookkeeping: name + applied_at
Key columns
| Table | Column | Why it matters |
|---|---|---|
| models | provider + provider_model_id | Unique pair; provider is google, openai, ollama, vllm, host-proxy, or mock (demos/e2e). Every column is NOT NULL; enabled defaults to TRUE and gates matchmaking, the roster route, and the leaderboard. |
| models | id | UUID minted per deployment by the seed (randomUUID()). ARENA_DEFAULT_MODEL still accepts this id, and also resolves human identifiers (provider_model_id, display_name, provider:provider_model_id) to it at boot. |
| conversations | anonymous_session_id | Nullable; when set, only that browser session may continue the conversation. Inserted only on turn 0, in the same transaction as the matchup and turn. |
| turns | parent_response_id | Null only at turn 0; unique thereafter, preventing two follow-ups from branching from one winner. A table check enforces the pairing: turn_index = 0 ⇔ parent IS NULL. |
| turns | conversation_id + turn_index | Unique ordered position (turn_index >= 0); prompt persisted as received. A mismatch or unique violation surfaces as conversation_conflict 409. |
| matchups | slot_a/b_model_id | Randomized display assignment, distinct from the raw pair columns; checks forbid self-matches. A non-votable single round writes no row here at all. Shadow sets A = incumbent, B = challenger. |
| matchups | slot_a/b_provider_model_id | Snapshot of each slot's models.provider_model_id at insert time so historical rows keep what ran if a roster alias is later repointed. Migration 008_matchup_provider_models.sql. |
| matchups | matchup_token_hash | SHA-256 of the signed token — the token itself is never stored. |
| matchups | harness_version | Prompt/orchestration version captured for temporal drift analysis. Defaults to 'v1'; demo-seeded rows carry 'demo'. |
| matchups | mode | blind (default, human-votable) or shadow (persisted, not votable). Migration 006_arena_mode.sql. Vote endpoint rejects shadow rows with 403. |
| matchups | steers | JSONB array (default []) of {instruction, at} objects appended by mid-stream steer so later analysis can control for operator interventions. Migration 007_matchup_steers.sql. |
| responses | ttft_ms + stream_duration_ms | First-token and total latency, both >= 0; latency_ms remains the compatibility field. ttft_ms, model_version, and error are the only nullable columns. |
| responses | output_token_count + token_count_source | Provider usage when available, deterministic lexical estimate otherwise (check-constrained to provider / estimated). |
| responses | markdown_density + model_version | Marker density checked to 0–1, and provider-reported checkpoint identity. Written ON CONFLICT (matchup_id, slot) DO NOTHING, so a replayed slot_done cannot duplicate a row. |
| preferences | winner_model_id | Null for ties and skips; derived server-side from the voted slot, never taken from the client. |
| preferences | position_bias_meta | JSONB NOT NULL DEFAULT '{}', currently { selectedSlot } — the style/bias analytics hook. |
| preferences | anonymous_session_id | Carried from the matchup token claims; the unit the worker's anomaly screen operates on. |
| model_ratings | rating + ci_lower/ci_upper | Elo-like rating 1000 + (400/ln10)·r with 95% Fisher-information CI. Worker-written (migration 003_model_ratings.sql). |
| model_ratings | component_id | Connected-component id; ratings only comparable within a component. |
| model_ratings | games + computed_at | Non-skip votes involving the model and the refit timestamp; upserted per model. |
| model_style_ratings | style_controlled_rating + style_ci_lower/upper | Elo-like rating with verbosity/formatting/latency/position regressed out, with 95% CI. Style-pass-written (migration 004_style_ratings.sql). |
| style_control_coefficients | feature + coefficient | One row per confounder (position, verbosity, formatting, latency_ttft, latency_duration); upserted each style pass. |
| model_rating_history | (model_id, computed_at) PK | Append-only: same rating/stderr/CI/component/games columns as model_ratings, inserted in the same transaction as each upsert (migration 005_rating_history.sql). Indexed on computed_at; feeds /api/arena/analytics/rating-history and the rating-over-time chart. |
Persistence: prompts and responses are stored as received; OmniArena does not transform or redact content.
Indexes
Only what the read paths need; everything else is served by primary keys and unique constraints.
| Index | Table | Purpose |
|---|---|---|
| preferences_winner_idx | preferences (winner_model_id) | Win/loss aggregation on the leaderboard |
| turns_conversation_idx | turns (conversation_id, turn_index) | Reconstructing a conversation's history in turn order |
| model_rating_history_computed_idx | model_rating_history (computed_at) | ?since= range scans for the rating-over-time chart |
Vote semantics
left → slot A wins
right → slot B wins
both_good → tie
both_bad → tie
skip → excluded from winRate
Leaderboard: winRate = wins / (wins + losses + ties), computed by SQL aggregation in PostgresRepository.getLeaderboard() over enabled models only; 0 when a model has no votes. A LEFT JOIN on model_ratings adds rating, ratingStdError, confidenceInterval, and componentId (null until the worker runs); a LEFT JOIN on model_style_ratings adds styleControlledRating, styleControlledStdError, and styleControlledConfidenceInterval (null until the style pass runs). Sorted rating DESC NULLS LAST, then wins DESC, total_votes DESC, display_name — an unrated model always sorts below a rated one, and a fresh install is ordered purely by its win-rate record.
A missing model_ratings row means the worker has not rated that model — either it has not run, or the model has no comparisons to rate. A non-votable single round persists no matchups row, so it produces nothing pairwise; see Rating methodology → what the engine cannot rate.
Reads beyond the leaderboard
| Port method | Reads | Notes |
|---|---|---|
| getRatingContext() | model_ratings · style_control_coefficients | The two qualifiers before comparing ratings: connected-component count + per-component head-counts (null before the worker runs), and the fitted style coefficients restated on the display scale (log-odds, rating points, and per interpretable unit where the deltas carry enough spread). |
| getSummary() | matchups · preferences · models · model_ratings | Arena-wide totals, slot-A/slot-B decisive split, pairs sampled vs possible, rating components. |
| getHeadToHead() | preferences ⋈ matchups | Canonical (unordered) pair records. |
| getModelMetrics() | responses · preferences ⋈ matchups | p50/p90 TTFT and duration, mean tokens and Markdown density, per-slot record. Skips failed slots (WHERE error IS NULL). |
| getActivity(bucket) | preferences ⋈ matchups | Vote volume by outcome and cumulative games, bucketed by day or hour. |
| getStyleControl() | style_control_coefficients · model_ratings · model_style_ratings | Coefficients plus the models rated in both passes. |
| getRatingHistory(since) | model_rating_history | The only reader of the append-only trail. |
Percentiles and time bucketing are computed in TypeScript, not SQL, so every analytics query stays runnable under pg-mem in tests.