Database Schema

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()
• Lap times stored as INTEGER milliseconds — never float
• Feature scores stored as NUMERIC(6,5) — range 0.00000 to 1.00000
• 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)
        └── 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)
  └── team_season_stats    (FK → teams)

circuits      (static, seeded once)
data_quality_runs      (FK → races, race_id nullable for season aggregates)
  └── data_quality_issues  (FK → runs)

Tables

seasons

One row per calendar year.

circuits

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

teams

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

drivers

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

races

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

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.

race_results

One row per driver per grand prix.

race_control_messages

One row per flag/safety-car/incident event per race, sourced from OpenF1 — 2023+ only (OpenF1 has no coverage before that), and only ingested once a race is completed and past OpenF1’s live-data window.

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 build_driver_number_map), since both sides of the exchange are needed for a usable UI.

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.

lap_times

One row per lap per driver — 2018+ only.

sprint_results

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

sprint_lap_times

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

fp2_long_run_times

FP2 long-run stint data per driver per race. Populated by ingest_fp2. 2018+ only.

driver_season_stats

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

team_season_stats

Aggregated after each race. Drives car_performance_score in predictions.

driver_prediction_features

One row per driver per grand prix race. Written by compute_features, updated by compute_predictions.

race_predictions

One row per grand prix — the single predicted winner.

driver_sprint_features

One row per driver per sprint weekend. Written by compute_sprint_features, updated by compute_sprint_predictions.

sprint_predictions

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

data_quality_runs

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

data_quality_issues

One row per flagged gap. Written by data_quality_audit.