Every table that backs this site, where its data comes from, and its known quirks — the gaps and inconsistencies real data always has, stated plainly rather than smoothed over.
One row per competition. sport distinguishes soccer from the planned NFL/NBA expansion.
'EPL', 'SERIE_A', 'MLS', 'LA_LIGA', 'WC'
true only for World Cup
'soccer' | 'basketball' | 'football' — every league is 'soccer' today; the other two exist for the planned NFL/NBA build
Per-league season. Label format is NOT uniform across leagues — a real bug (fixed) once made these disagree between ingestion paths for the same real season.
Hyphenated 'YYYY-YY' for cross-year leagues (EPL, SERIE_A, LA_LIGA — e.g. '2026-27'); bare 'YYYY' for single-calendar-year competitions (MLS, WC — e.g. '2026'). Both ingestion paths (loader.py, api_football.py) must agree on this per league or a season silently duplicates.
Shared entity tables across every league and (eventually) every sport. External ids exist purely for de-duplicating the same real-world team/player across three independent data sources.
Nullable — a team only has an id for the sources that actually cover it. Name-based aliasing (src/ingestion/entities.py) resolves the same team across sources when ids don't overlap.
'GK'/'DF'/'MF'/'FW' — free text from the source, not an enum
One row per fixture, shared across all leagues/sports. home_score/away_score were renamed from home_goals/away_goals (generalizing off soccer terminology) — check you're on a build past that rename.
'scheduled' | 'live' | 'final' | 'postponed'
NULL until final
Renamed from went_to_et — extra time/overtime, applies to any sport
Soccer/knockout-tournament specific (penalty shootout) — deliberately NOT generalized, NFL/NBA have no equivalent and this column will simply stay false for them
e.g. 'api-football:12345', 'understat:67890' — the source event id, prefixed by source
Soccer-specific box-score stats, one row per team/player per match. Deliberately NOT shared with other sports (see team_match_stats_nfl below) — per-sport tables chosen over a shared core + JSONB column for full type safety and easy indexing.
Understat only — API-Football's tier doesn't include xG, so these are NULL for matches sourced only from API-Football (e.g. current-season EPL/SERIE_A fixtures pulled via the --primary stopgap path before FBref/Understat catch up)
Pressing intensity — Understat only
FBref/API-Football; coverage varies by league — see the World Cup exception below
Empty as of this writing — schema prep only, ahead of the actual NFL ingestion adapter. Column list is a best-effort standard box-score set matching nflverse/nfl_data_py's shape; expect adjustment once real data starts flowing.
total/passing/rushing yards, turnovers, sacks, penalties, first downs, third-down conversions, time of possession
position, passing/rushing/receiving stat lines, defensive tackles/sacks (sacks is NUMERIC(3,1) — half-sacks are real)
Event-level shot data (Understat only) — coordinates, situation, body part, Understat's own xG as a benchmark. Feeds a planned custom xG model (v3); also the reason MLS/API-Football-only leagues have no shot-level detail.
Understat's normalized pitch coordinates
Understat's xG for this shot — the benchmark a future in-house model would be compared against
One row per distinct (model, config), not per prediction run. This was broken for a while — version_tag used to be unique per fixture, which meant every slate-generation call minted a new throwaway row and the registry never actually deduplicated anything. Fixed: version_tag is now stable per league+code-version.
Real hyperparameters + feature lists as of the fix, not free-text notes
Real out-of-fold error metrics (e.g. MAE) where computed inline; not always populated for every model
The immutable ledger. INSERT-only (a trigger rejects UPDATE/DELETE outright); another trigger rejects any row locked at or after its match's kickoff.
CHECK-constrained: '1X2','BTTS','TOTAL_GOALS','CORNERS','SOT','PLAYER_GOALS','PLAYER_SAVES' (soccer, live) plus 'MONEYLINE','SPREAD','TOTAL_POINTS' (NFL/NBA, schema prep only — nothing writes these yet)
Semantics depend on market: 'home'/'draw'/'away' for 1X2, 'yes'/'no' for BTTS, 'over'/'under' for line markets, 'home'/'away' for SPREAD/MONEYLINE (which team the prediction concerns)
NULL where not applicable (1X2, BTTS, MONEYLINE); the threshold for O/U markets; the signed spread for SPREAD
CHECK-constrained to the open interval (0,1) — never exactly 0 or 1
Separate, append-only. The prediction row itself is never touched when grading happens.
'hit' | 'miss' | 'void' — void covers pushes (line lands exactly on the actual value) and postponed/abandoned matches
The observed number graded against — a signed margin for SPREAD, a count for line markets, goal difference for 1X2
Queue for entity-resolution ambiguities a human should check (e.g. a possible-duplicate player). Empty as of this writing — surfaced by scripts/review_queue.py, not shown anywhere in the UI.
Backing store for a future data-freshness indicator. The table exists but nothing writes to it yet — auto_slate.py/auto_grade.py/the ingestion scripts still need a small wrapper to record start/finish/status. Known gap, tracked in Todoist.