f1 intelligence
./docs / database-schema

Database Schema

The F1 Intelligence Platform’s database.

Provider: Neon PostgreSQL
ORM: Drizzle
Schema source: apps/api/src/db/schema/
Migrations: apps/api/drizzle/migrations/ (generated SQL files)


Conventions

  • All primary keys are SERIAL (auto-increment integer)
  • All timestamps are TIMESTAMPTZ DEFAULT now(); most tables carry a created_at (stats tables updated_at, prediction/odds tables computed_at), left out of the column tables below
  • Lap times stored as INTEGER milliseconds: never float
  • Sprint feature scores stored as NUMERIC(6,5): range 0.00000 to 1.00000; GP features as one features JSONB
  • All natural keys have UNIQUE constraints

Table Overview

seasons
  └── teams          (season-scoped)
  └── drivers        (season-scoped, FK → teams)
  └── races          (FK → circuits)
        └── qualifying_results      (FK → drivers)
        └── race_results            (FK → drivers)
        └── race_control_messages   (no driver FK: driver_number is a raw OpenF1 car number)
        └── race_overtakes          (two FKs → drivers: overtaking_driver_id, overtaken_driver_id)
        └── team_radio_clips        (FK → drivers)
        └── race_stints             (FK → drivers)  ← 2018+ (OpenF1 2023+, FastF1 before), per session
        └── race_pit_stops          (FK → drivers)  ← 2018+ (OpenF1 2023+, FastF1 before), per session
        └── race_lap_traces         (FK → drivers)  ← 2018+ (OpenF1 live, FastF1 before), int[] per lap
        └── race_battles            (2 FKs → drivers)
        └── race_weather_traces     (no driver FK)  ← per-minute weather arrays
        └── qualifying_lap_telemetry (FK → drivers) ← fastest Q/SQ lap, JSONB samples
        └── fp2_long_run_times      (FK → drivers)
        └── lap_times               (FK → drivers)
        └── driver_prediction_features (FK → drivers)
        └── race_predictions        (FK → drivers)
        └── sprint_results          (FK → drivers)  ← sprint weekends only
        └── sprint_lap_times        (FK → drivers)  ← sprint weekends only
        └── driver_sprint_features  (FK → drivers)  ← sprint weekends only
        └── sprint_predictions      (FK → drivers)  ← sprint weekends only
  └── driver_season_stats  (FK → drivers)
  └── title_odds_drivers   (FK → drivers): one row per driver per round
  └── title_odds_teams     (FK → teams)  : one row per team per round
  └── team_season_stats    (FK → teams)

circuits      (static, seeded once)
history_drivers / history_constructors / history_constructor_seasons / history_constructor_lineage / f1db_imports
              (F1DB all-time history: no FKs, joined by match_key / f1db id)
data_quality_runs      (FK → races, race_id nullable for season aggregates)
  └── data_quality_issues  (FK → runs)

Tables

seasons

One row per calendar year.

ColumnTypeNotes
idserial PK
yearinteger UNIQUEe.g. 2026

circuits

Static track data, seeded once. Not season-scoped.

ColumnTypeNotes
idserial PK
circuit_keyvarchar(50) UNIQUEe.g. monza, silverstone
namevarchar(100)
countryvarchar(50)
cityvarchar(50)
lap_countinteger
track_length_kmnumeric(5,3)
overtake_ratenumeric(4,3)0.0–1.0; used in prediction model
number_of_cornersintegernull for pre-DRS-era circuits
drs_zonesintegernull for pre-DRS-era circuits
sc_probabilitynumeric(4,3)Historical SC deployment rate (completed races); null for circuits with no completed races
image_urlvarchar(512)Public R2 URL or custom path for track layout image
track_categoryvarchar(30)street | high_speed | high_downforce | balanced: drives car_performance_at_circuit blend

teams

Season-scoped: a team row exists per year it competed.

ColumnTypeNotes
idserial PK
season_idFK → seasons
team_keyvarchar(50)snake_case, e.g. red_bull
namevarchar(100)Display name
nationalityvarchar(50)
UNIQUE(season_id, team_key)

drivers

Season-scoped: a driver row exists per year they raced.

ColumnTypeNotes
idserial PK
season_idFK → seasons
team_idFK → teams
driver_numberinteger
codechar(3)e.g. VER, HAM
first_namevarchar(50)
last_namevarchar(50)
nationalityvarchar(50)
headshot_urlvarchar(255)From OpenF1 (FastF1 for seasons ingested before Oct 2026), 2019+
UNIQUE(season_id, first_name, last_name)One row per person per season (migration 0028, ADR 0009)
INDEX(season_id, driver_number)Not unique (0027): before 2014 a stand-in shared the regular driver’s car number

races

One row per race event. Sprint weekends have one race row (covering both the sprint and grand prix).

ColumnTypeNotes
idserial PK
season_idFK → seasons
circuit_idFK → circuits
round_numberinteger
namevarchar(100)e.g. Monaco Grand Prix
race_datedateGrand Prix date
race_date_utctimestamptzGrand Prix start time; nullable
qualifying_datetimestamptzSaturday qualifying session time
sprint_datetimestamptzSprint race session time (sprint weekends only)
sprint_qualifying_datetimestamptzSQ session time (sprint weekends only)
event_formatvarchar(30)conventional (default) | sprint | sprint_qualifying | sprint_shootout
statusrace_status enumDefault scheduled; see status flow below
weathervarchar(30)dry | wet | mixed: main race weather
safety_car_lapsinteger2018+ only (default 0)
vsc_lapsinteger2018+ only (default 0)
air_temp_avgnumeric(4,1)2018+ only
track_temp_avgnumeric(4,1)2018+ only
humidity_avgnumeric(4,1)2018+ only
sprint_weathervarchar(30)Sprint-specific weather: sprint weekends only
sprint_safety_car_lapsintegerSprint SC laps
sprint_vsc_lapsintegerSprint VSC laps
sprint_air_temp_avgnumeric(4,1)Sprint session air temp
sprint_track_temp_avgnumeric(4,1)Sprint session track temp
sprint_humidity_avgnumeric(4,1)Sprint session humidity
openf1_race_session_key / openf1_sprint_session_keyintegerCached OpenF1 session keys, so later jobs skip the /sessions lookup
UNIQUE(season_id, round_number)

Race Status Flow

conventional weekend:
  scheduled → qualifying_done → completed

sprint weekend:
  scheduled → sprint_qualifying_done → sprint_done → qualifying_done → completed
                      ↓                     ↓                ↓
               sprint features      ingest sprint       main qualifying
               sprint predictions   sprint season stats features + predictions

qualifying_results

One row per driver per main qualifying session.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
grid_positionintegerFinal starting grid position
q1_time_msintegerNull if knocked out before Q2
q2_time_msintegerNull if knocked out before Q3
q3_time_msintegerNull if not in Q3
sector1_msintegerBest sector times: 2018+ only
sector2_msinteger
sector3_msinteger
speed_stnumeric(5,1)Speed trap km/h: 2018+ only
UNIQUE(race_id, driver_id)

race_results

One row per driver per grand prix.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
finish_positionintegerNull = DNF/DSQ
grid_positioninteger
pointsnumeric(4,1)Championship points scored
statusvarchar(30)Finishers: Finished, Lapped, +1 Lap, +2 Laps; DNFs: Retired, Accident, Engine, Collision, Disqualified, Did not start, etc.
total_race_time_msbigintFor winner only
fastest_lapboolean
UNIQUE(race_id, driver_id)

race_control_messages

One row per flag/safety-car/incident event per race, 2018+. From 2023 it comes from OpenF1, ingested once a race is completed and past OpenF1’s live-data window; 2018–2022 was backfilled from FastF1’s race control feed (scripts/backfill_fastf1_enrichment.py).

ColumnTypeNotes
idserial PK
race_idFK → races
datetimestamptzMessage timestamp (OpenF1, or FastF1 pre-2023)
categoryvarchar(30)Flag, SafetyCar, Drs, SessionStatus, CarEvent, Other
flagvarchar(20)e.g. YELLOW, DOUBLE YELLOW, RED, GREEN; null for non-flag messages
lap_numberintegerNullable
driver_numberintegerRaw OpenF1 car number, not a drivers FK: no reliable per-season mapping stored here
scopevarchar(20)e.g. Track, Sector, Driver; nullable
sectorintegerNullable
messagetextRaw race-control message text
session_typevarchar(10)race (default) or sprint: both sessions of a sprint weekend share race_id
UNIQUE(race_id, date, message)

race_overtakes

One row per on-track position exchange per race, sourced from OpenF1: same 2023+/completed/ past-live-window coverage as race_control_messages. Unlike that table, OpenF1’s driver numbers here are resolved to drivers.id at ingest time (via driver_registry.season_drivers), since both sides of the exchange are needed for a usable UI.

ColumnTypeNotes
idserial PK
race_idFK → races
datetimestamptzOvertake timestamp, from OpenF1
overtaking_driver_idFK → driversThe driver who gained the position
overtaken_driver_idFK → driversThe driver who lost the position
positionintegerTrack position after the overtake
session_typevarchar(10)race (default) or sprint: both sessions of a sprint weekend share race_id
UNIQUE(race_id, date, overtaking_driver_id, overtaken_driver_id)

team_radio_clips

One row per team radio clip per race, sourced from OpenF1: same 2023+/completed/ past-live-window coverage as the other OpenF1 tables. F1 doesn’t release radio for every session, so sparse or missing coverage for a given race is expected, not a data gap.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → driversResolved from OpenF1’s car number via season_drivers
datetimestamptzClip timestamp, from OpenF1
recording_urltextDirect link to F1’s own audio CDN
session_typevarchar(10)race (default) or sprint: both sessions of a sprint weekend share race_id
UNIQUE(race_id, driver_id, date)

race_stints

One row per tyre stint per driver per session, 2018+: OpenF1 /stints from 2023, FastF1 laps for 2018–2022 (scripts/backfill_fastf1_enrichment.py). A race with no rows falls back to stints rebuilt on the fly from lap_times by the API (see /api/races/:id/strategy).

ColumnTypeNotes
idserial PK
race_idFK → races
session_typevarchar(10)race or sprint
driver_idFK → driversResolved from OpenF1’s car number (FastF1 driver code pre-2023)
stint_numberinteger1-based
compoundvarchar(20)SOFT/MEDIUM/HARD/INTERMEDIATE/WET; 2018 keeps Pirelli’s named compounds (HYPERSOFT/ULTRASOFT/SUPERSOFT/SUPERHARD too); nullable
lap_start / lap_endintegerInclusive lap range
tyre_age_at_startintegerLaps already on the set (> 0 = used set)
UNIQUE(race_id, session_type, driver_id, stint_number)

race_pit_stops

One row per pit stop per driver per session, 2018+: OpenF1 /pit from 2023, FastF1 in/out-lap times for 2018–2022 (pit-lane time only; lane visits over 120 s, red-flag waits, are dropped).

ColumnTypeNotes
idserial PK
race_idFK → races
session_typevarchar(10)race or sprint
driver_idFK → driversResolved from OpenF1’s car number (FastF1 driver code pre-2023)
lap_numberintegerLap the stop happened on
pit_lane_msintegerTotal pit-lane time, ms
stationary_msintegerTime stopped in the box, ms: only from the 2024 US GP onward (FastF1 has none)
datetimestamptz
UNIQUE(race_id, session_type, driver_id, lap_number)

race_lap_traces

One row per driver per session holding per-lap arrays (index 0 = lap 1), built by race_story.build_lap_traces from the session’s full laps (OpenF1 live, FastF1 before; unlike lap_times, which only keeps accurate laps). ~20 rows and ~18 KB per race: row-per-lap would be ~1,200 rows.

ColumnTypeNotes
race_id, session_type, driver_idUNIQUE together
positionsinteger[]Position at the end of each lap
gaps_msinteger[]Gap to that lap’s leader; NULL while lapped
intervals_msinteger[]Gap to the car directly ahead; NULL for the leader
laps_downinteger[]Laps behind the leader

race_battles

3+ consecutive laps within 1.0 s of the same car ahead (race_story.find_battles). Columns: race_id, session_type, chaser_driver_id, leader_driver_id, lap_start, lap_end, min_gap_ms. UNIQUE (race_id, session_type, chaser_driver_id, lap_start).

race_weather_traces

One row per session: minute_offsets integer[], air_temp_tenths / track_temp_tenths integer[] (tenths of °C), rainfall boolean[]. UNIQUE (race_id, session_type).


qualifying_lap_telemetry

Each driver’s fastest qualifying (Q) / sprint-qualifying (SQ) lap from OpenF1 /laps + /car_data

  • /location (2023+), or FastF1 car/position data for 2018–2022 Q laps. samples is JSONB of int arrays on a 10 m distance grid: distanceM, timeMs, speed, throttle, brake (0/100), gear, x, y. ~11 KB per row after TOAST. UNIQUE (race_id, session_type, driver_id); also lap_number, lap_time_ms.

History tables (F1DB, CC-BY-4.0)

Imported by import_f1db; separate from the season-scoped core tables that feed the model.

  • history_drivers: one row per F1DB driver: f1db_id (UNIQUE), match_key (normalised name, indexed: the join to core drivers), bio (abbreviation, date_of_birth, date_of_death, nationality), career totals (championships, race_starts, race_wins, podiums, pole_positions, fastest_laps, career_points, sprint_wins, grand_slams) and first_{race,podium,pole,win}_{date,name}.
  • history_constructors: f1db_id (UNIQUE), name, full_name, country, career totals.
  • history_constructor_seasons: (constructor_f1db_id, year) UNIQUE; chassis, engine display names.
  • history_constructor_lineage: F1DB chronology: (parent_f1db_id, position) UNIQUE, member constructor_f1db_id, year_from/year_to.
  • f1db_imports: imported release version (UNIQUE) + imported_at, so a release is never imported twice.

lap_times

One row per lap per driver: 2018+ only.

ColumnTypeNotes
idbigserial PKLarge table: bigserial not serial
race_idFK → races
driver_idFK → drivers
lap_numberinteger
lap_time_msintegerNullable (untimed laps)
sector1_msinteger
sector2_msinteger
sector3_msinteger
speed_stnumeric(5,1)Speed trap km/h
compoundvarchar(20)SOFT, MEDIUM, HARD, INTER, WET
tyre_lifeintegerLaps on current tyre
fresh_tyreboolean
is_pit_lapbooleanExcluded from pace calculations
stint_numberintegerStint index within the race
UNIQUE(race_id, driver_id, lap_number)

sprint_results

One row per driver per sprint race. Also stores sprint qualifying (SQ) session times.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
finish_positionintegerNull = DNF/DSQ
grid_positionintegerSet from SQ result
pointsnumeric(4,1)Sprint points scored
statusvarchar(30)e.g. Finished, +1 Lap
total_sprint_time_msbigintFor sprint winner only
fastest_lapboolean
sq1_time_msintegerSQ1 lap time: null if eliminated in SQ1
sq2_time_msintegerSQ2 lap time: null if not in SQ2
sq3_time_msintegerSQ3 lap time: null if not in SQ3
sq_sector1_msintegerBest S1 from SQ session
sq_sector2_msintegerBest S2 from SQ session
sq_sector3_msintegerBest S3 from SQ session
sq_speed_stnumeric(5,1)Max speed trap from SQ session
UNIQUE(race_id, driver_id)

sprint_lap_times

Per-lap data for sprint races: mirrors lap_times structure. 2018+ only.

ColumnTypeNotes
idbigserial PK
race_idFK → races
driver_idFK → drivers
lap_numberinteger
lap_time_msinteger
sector1_msinteger
sector2_msinteger
sector3_msinteger
speed_stnumeric(5,1)
compoundvarchar(20)
tyre_lifeinteger
fresh_tyreboolean
is_pit_lapboolean
stint_numberintegerStint index within the sprint
UNIQUE(race_id, driver_id, lap_number)

fp2_long_run_times

FP2 long-run stint data per driver per race. Populated by ingest_fp2. 2018+ only. Served by GET /api/races/:id/long-runs for the race page; not a weighted-v4 feature.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
compoundvarchar(20)SOFT, MEDIUM, HARD
session_typevarchar(4)FP2 (primary) | FP1 (sprint-weekend fallback: those weekends have no FP2)
median_lap_msintegerMEDIUM-normalised median stint lap time (Soft +500ms, Hard −400ms)
stint_lengthintegerNumber of laps in the long-run stint (≥5)
fp2_best_lap_msintegerDriver’s single fastest raw lap in FP2
UNIQUE(race_id, driver_id, compound)Best (shortest) stint per compound kept

driver_season_stats

Aggregated after each race via compute_season_stats. Includes sprint-specific aggregates.

ColumnTypeNotes
idserial PK
season_idFK → seasons
driver_idFK → drivers
races_enteredintegerGrand prix count
races_finishedinteger
winsintegerGrand prix wins
podiumsinteger
polesintegerGrid position = 1
total_pointsnumeric(6,1)Grand prix points
championship_positionintegerRanked by total_points + sprint_total_points (the championship counts sprints)
avg_finish_positionnumeric(4,2)
win_ratenumeric(5,4)Bayesian smoothed
avg_position_gainnumeric(4,2)grid_position − finish_position avg
dnf_countinteger
dnf_ratenumeric(4,3)dnf_count / races_entered
avg_sector1/2/3_msintegerMedian: 2018+ only
top_speed_avgnumeric(5,1)
teammate_quali_deltanumeric(6,4)Mean delta vs teammate across season
sprint_races_enteredintegerSprint race count
sprint_winsinteger
sprint_podiumsinteger
sprint_total_pointsnumeric(6,1)Sprint points
sprint_avg_finish_positionnumeric(4,2)
sprint_win_ratenumeric(5,4)Bayesian smoothed sprint win rate
UNIQUE(season_id, driver_id)

team_season_stats

Aggregated after each race. Drives car_performance_score in sprint predictions.

ColumnTypeNotes
idserial PK
season_idFK → seasons
team_idFK → teams
races_completedinteger
wins / podiumsintegerGrand prix
total_pointsnumeric(6,1)Grand prix points
avg_finish_positionnumeric(4,2)
dnf_countinteger
sprint_wins / sprint_podiumsinteger
sprint_total_pointsnumeric(6,1)Sprint points
car_performance_scorenumeric(5,4)Blend of robust race pace (median finish) + qualifying pace (avg grid): 0.6·minmax(21−median) + 0.4·minmax(21−avg_grid)
reliability_scorenumeric(5,4)Normalized from 1 − dnf_rate
championship_positionintegerRanked by GP + sprint points
UNIQUE(season_id, team_id)

title_odds_drivers / title_odds_teams

Monte Carlo championship odds written by compute_title_odds after every qualifying, sprint and race. One row per contender per after_round (the last completed round when computed, 0 before the opener), so the odds can be charted across the season: about 720 rows a season in total. scripts/backfill_title_odds.py replays past rounds (run(year, after_round)), so 2018–2026 has full per-round history.

ColumnTypeNotes
idserial PK
season_idFK → seasons
after_roundintegerLast completed round at compute time, or the replayed round
driver_id / team_idFK → drivers / teams
title_probabilitynumeric(6,5)Share of simulated seasons won (ties split)
expected_pointsnumeric(6,1)Mean final points, GP + sprint
points_p10 / points_p90numeric(6,1)10th / 90th percentile final points
simulationsinteger10,000
model_versionvarchar(30)title-odds-v1
UNIQUE(season_id, after_round, driver_id) / (season_id, after_round, team_id)

driver_prediction_features

One row per driver per grand prix race (weighted-v4). Written by compute_features together with the race_predictions row, in one transaction; drivers no longer in the field are deleted.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
featuresjsonbFeature → {value, contribution} for the 7 fitted features (grid, quali_pace, team_quali_pace, driver_points, teammate_edge, grid_processional, team_race_pace). A refit with other features needs no migration
raw_weighted_scorenumeric(8,6)Sum of the contributions: the driver’s log-odds score
win_probabilitynumeric(6,5)Softmax (T = 1) over the field: sums to 1.0 per race
predicted_positioninteger1 = predicted winner
podium_probabilitynumeric(6,5)P(top 3) implied by the win probabilities (utils/finishing_order.py); null on rows from before migration 0031 until backfilled
UNIQUE(race_id, driver_id)

The weighted-v3 score columns and long_run_used_fp were dropped by migrations 0029/0030.


race_predictions

One row per grand prix: the single predicted winner.

ColumnTypeNotes
race_idFK → races UNIQUE
predicted_winner_idFK → drivers
computed_attimestamptz
model_versionvarchar(20)weighted-v4 (default)
notestextNullable

driver_sprint_features

One row per driver per sprint weekend. Written by compute_sprint_features (sprint-v3, ADR 0014) together with the sprint_predictions row, in one transaction.

ColumnTypeNotes
idserial PK
race_idFK → races
driver_idFK → drivers
featuresjsonbweighted-v4’s 7 features on the sprint grid, feature → {value, contribution}, as in driver_prediction_features. Null only on rows from before migration 0032 until backfilled
raw_weighted_scorenumeric(8,6)Sum of the contributions
win_probabilitynumeric(6,5)Softmax (T = 1) over the field
predicted_positioninteger
car_performance_score … sq_qualifying_delta_scorenumeric(6,5)sprint-v2’s ten feature-score columns: legacy, nullable since 0032, never written by sprint-v3; dropped by a later migration
UNIQUE(race_id, driver_id)

sprint_predictions

One row per sprint weekend: the single predicted sprint winner.

ColumnTypeNotes
race_idFK → races UNIQUE
predicted_winner_idFK → drivers
computed_attimestamptz
model_versionvarchar(20)sprint-v3 (rows written before it say sprint-v2)

pre_quali_predictions

A GP’s forecast before qualifying (pre-quali-v1, ADR 0015): one row per driver of the lineup of the season’s previous race, written by compute_pre_quali once that race is ingested. Kept after qualifying, so the GP prediction can show how each driver’s chance moved. Migration 0033.

ColumnTypeNotes
idserial PK
race_idFK → racesThe race forecast
driver_idFK → drivers
featuresjsonbFeature → {value, contribution} for the 4 pre-qualifying features (driver_points, team_race_pace, team_quali_pace, teammate_edge)
raw_weighted_scorenumeric(8,6)Sum of the contributions
win_probabilitynumeric(6,5)Softmax over the lineup: sums to 1.0 per race
predicted_positioninteger1 = favourite
model_versionvarchar(20)pre-quali-v1
computed_attimestamptz
UNIQUE(race_id, driver_id)

data_quality_runs

One row per audit pass (season aggregate) or per-race audit. Written by data_quality_audit.

ColumnTypeNotes
idserial PK
yearintegerSeason audited (0 when an all-years scan ran)
race_idFK → races, nullableNull = season aggregate pass
generated_attimestamptz
health_scorenumeric(5,2)0–100, per-pass average
summaryjsonbraces_audited, issue_count, fixable_count, by_severity, by_table
UNIQUE(year) WHERE race_id IS NULLOne season-aggregate run per year (migration 0013)

data_quality_issues

One row per flagged gap. Written by data_quality_audit.

ColumnTypeNotes
idserial PK
run_idFK → data_quality_runs
race_idFK → races, nullable
round_numberinteger
yearinteger
table_namevarchar(40)Source table the issue belongs to
check_namevarchar(60)e.g. driver_coverage, lap_coverage, row_count
severityvarchar(10)high | medium | low
detailvarchar(500)Human-readable summary
fixablebooleanWhether re-ingest/recompute can close it
is_sprintbooleanSprint-weekend issue
resolvedbooleanManual triage flag (default false)
created_attimestamptz