-- ===================================================================== -- Lap Ledger — relational schema -- Version 2.0 (2026-09-04) -- -- Provenance model -- ---------------- -- Every fact table carries: -- confidence 'verified' | 'high' | 'reference' | 'medium' | 'unverified' -- | 'catalogued' -- source URL or citation where the fact was checked -- source_id the source_registry entry `source` resolves to. Not written -- out below: the build's last stage adds it to every table -- carrying `source` and fills it by the resolution described -- at source_patterns, so the answer to "which source, under -- what licence" is a join rather than ten regular expressions -- (DA-03). A table given `source` gets it with no edit here. -- -- verified = checked this session against fia.com / formula1.com -- high = well-established record, stable across decades of -- official publication; safe to rely on -- reference = a published secondary record: F1DB for almost all of -- it, Wikipedia for most of the rest. Compared on load with -- a second source where one is held - not every table has -- one. Usable, but not official under this database's -- policy. The `provenance` row names each source, and -- verify.py holds the names to the rows (DA-05). -- medium = correct in substance, detail (exact figure, exact -- date) worth confirming before publication -- unverified = placeholder / disputed / known-incomplete -- catalogued = below unverified: a third party's filing, not a check - -- a photograph a Commons editor filed under a category -- named for the chassis or the race (article_images, routes -- 'category' and 'race') -- -- Rule inherited from v1: never promote a fact to 'verified' without -- an official source. See the `provenance` table for the ladder. -- ===================================================================== PRAGMA foreign_keys = ON; -- ---------------------------------------------------------------- meta CREATE TABLE meta ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE provenance ( confidence TEXT PRIMARY KEY, rank INTEGER NOT NULL, definition TEXT NOT NULL, may_publish INTEGER NOT NULL -- 1 = safe to state as fact ); CREATE TABLE source_registry ( id INTEGER PRIMARY KEY, priority INTEGER NOT NULL, source TEXT NOT NULL, url TEXT, use TEXT, -- official | reference | authored | forbidden -- -- 'authored' was added in v2.16 and names what ATTRIBUTION.md already -- said in prose: some of this database was written for the project from -- general knowledge rather than taken from anywhere. That is a real -- provenance and it deserves a word. It is NOT a lesser kind of -- reference source - it has no external source at all - and nothing -- carrying it may sit above 'medium'. See docs/DERIVED-CONFIDENCE.md. authority TEXT NOT NULL DEFAULT 'official' CHECK (authority IN ('official', 'reference', 'authored', 'forbidden')), -- A source is judged on these three, not on how much data it has. The -- largest dataset in the sport is worth nothing here if its values -- cannot be checked against something held independently. licence TEXT, -- what you may actually do with the data cadence TEXT, -- how often it is updated, and by whom checkability TEXT, -- what in this database can contradict it -- `licence` above is prose, written for a person. These four are the -- same judgement in a form the BUILD can read, so that "may this row be -- published?" is a query rather than a memory. Without them the licence -- of a row is knowable only by reading a paragraph and recognising which -- of sixteen sources a URL belongs to, which is how a CC BY-NC citation -- survived seven versions in the committed database. -- -- yes may be redistributed, on the terms in the columns below -- facts-only the FACTS may be restated - they are not copyrightable - -- but nothing of the source's own expression may be copied, -- and no substantial extraction of its database made -- no may not be redistributed at all. A row citing one of -- these must not be in the committed database, and -- verify.py fails if one is. redistributable TEXT NOT NULL DEFAULT 'facts-only' CHECK (redistributable IN ('yes', 'facts-only', 'no')), share_alike INTEGER NOT NULL DEFAULT 0, -- reuse must carry the same licence attribution_required INTEGER NOT NULL DEFAULT 1, -- How a row's `source` is recognised as belonging to this entry: a -- comma-separated list of hostnames and of the bare tokens the loaders -- write ('f1db', 'fastf1', 'jolpica'). NULL where no row ever cites the -- source - the fan-site entry exists to record that it is forbidden, not -- to be pointed at. Several entries may share a host, and where they do -- the build requires them to agree on the three columns above. domains TEXT ); -- How a row's free-text `source` resolves to a registry entry. -- -- source_registry.url is ONE EXAMPLE PAGE, not a namespace, which is why -- 4,691 rows - 4.9% - previously resolved to no registry entry at all: -- .../List_of_Formula_One_polesitters does not prefix-match -- .../2024_Formula_One_World_Championship though both are the same source. -- A source needs several patterns (Wikipedia needs three), so this is a -- child table rather than a column. CREATE TABLE source_patterns ( id INTEGER PRIMARY KEY, source_id INTEGER NOT NULL REFERENCES source_registry(id), pattern TEXT NOT NULL, -- Python re, anchored at the start note TEXT ); -- Provenance for a table that has no `source` column of its own. -- -- Fifteen tables carry `confidence` and no `source`. Thirteen of them are -- authored; two are sourced and simply never got the column. Either way the -- provenance existed only in ATTRIBUTION.md, where nothing could read it. -- A row here says, for the whole table, where its content came from. CREATE TABLE table_provenance ( tbl TEXT PRIMARY KEY, source_id INTEGER NOT NULL REFERENCES source_registry(id), -- 1 where nothing in this database can constrain the claim the table -- makes, whatever the source's standing. A well-run source does not -- make a row checkable: article_images comes from the MediaWiki API and -- records which file an article carries, and NOTHING here constrains -- what the photograph shows. See known_gaps #11. Such a table is floored -- at 'unverified' rather than taking its source's tier. unconstrained INTEGER NOT NULL DEFAULT 0, note TEXT ); -- Which source gave the value in one column of one row (PM-14). -- -- `source` is row-grain and sourcing is not. A driver's external career -- figures came from three places - figures typed into data/drivers.py, -- formula1.com's driver pages, Wikipedia articles - and one -- `external_source` string described them per row, so it dated four typed -- fastest-lap totals to a formula1.com fetch that never gave them, and -- credited formula1.com with Russell's 11 poles, which are Wikipedia's -- figure after formula1.com's 12 was corrected; the typed totals beside -- them had no source at all. A chassis is an F1DB row -- carrying three figures off a Wikipedia article, and its source_id can say -- only the first. Five places encoded "another source holds this value" -- five different ways; this is the one shape for it. -- -- A claim is the value, as text, that `source` gave for column `field` of -- the row of `tbl` whose key is `row_key`. So the source of a single value -- - and so the terms it may be reused on - is a lookup, and agreement -- between sources is a comparison. The columns stay where they were, so -- nothing reading them changes; verify.py holds each one to be exactly its -- claims, in both directions, which makes it a view of them rather than a -- second record - bar the driver figures no source is named for, which -- have none and are counted. data/current.py CLAIM_FIELDS names every column -- a claim may back, and the build refuses any other. -- -- drivers.*_external one claim per figure a source is named for, -- citing THAT source: formula1.com's dated fetch, -- the two Wikipedia fastest-lap totals, the -- Wikipedia figure a correction took. A correction -- replaces the claim, and the old value stays in -- `discrepancies`. A figure typed into the data -- modules from reference records nobody named cites -- F1DB's published career total where it equals it, -- a second named source where F1DB differs, and is -- removed where no named source gives it (PM-57, -- #624). -- chassis.published_* the article's figures, citing the article. As -- the column is, the career of the article's -- subject: for a family article, the family's. -- car_seasons.other_chassis -- what F1DB's entry lists name for the car's -- constructor that season beyond the chassis the -- car covers. NULL: they name none, which is what -- `corroborated` = 1 means. -- -- race_entries.note why a classified finisher inside the paid -- places scored nothing - a shared drive, a -- Formula Two car, a second car not entered for -- the championship, a penalty - citing the -- race's own article (DA-37). The note is this -- project's wording of what the article states. -- -- Two of the five encodings docs/DERIVED-CONFIDENCE.md names are NOT here, -- on purpose. circuit_geometry's measured_km is OpenStreetMap's, and f1.db -- carries no OpenStreetMap data (ATTRIBUTION.md; that table is empty in this -- file). article_images.name_matches is a string test of a row's own file -- name against its own chassis, not a second source. -- -- `row_key` is the row's primary key where that is not a bare integer id, -- or else the natural key data/current.py ID_STABILITY publishes for the -- table, as `discrepancies` spells it: its columns joined by '|' in key -- order: 'fangio', 'mclaren-m23|1976', a race entry's '|'. -- A bare integer id is never one: those are the build's to renumber. CREATE TABLE claims ( tbl TEXT NOT NULL, row_key TEXT NOT NULL, field TEXT NOT NULL, value_given TEXT, -- as the source gave it; NULL = it names none as_of TEXT, -- when the source said it, where the build knows source TEXT NOT NULL, -- Resolved by the build's last stage, like every other table's; declared -- here so the constraint below can name it. Two URLs resolving to the -- same entry would be one source counted twice. source_id INTEGER REFERENCES source_registry(id), PRIMARY KEY (tbl, row_key, field, source), UNIQUE (tbl, row_key, field, source_id) ); -- Where each column of this database has its values from (DA-30). -- -- source_id says where a ROW came from, and a row is not the grain of its -- columns: a chassis is an F1DB row whose specification is the car's -- Wikipedia article, named in spec_source; a circuit's notes are prose the -- prose pass measures against Wikipedia, whatever its row cites; a car's -- race count is counted by build.py; a race's date is F1DB's even on a row -- citing a season article. Derived from source_id alone, as first proposed, -- this table would have stated a false origin for about fifty columns -- (measured 2026-09-26). It is derived instead from tools/facts_artefact.py's -- declaration of the facts artefact (PD-53), column by column, and states no -- origin that declaration has not established (the ruling of 2026-10-06). -- A column it does not reach has `origin` NULL - not established - whatever -- its row cites; it does not fall back to the row's source_id or the -- table's table_provenance row. -- -- One row per column of every table here, this one included. build.py -- writes it in its last stage and verify.py derives it again and compares -- the whole table. -- -- It records ORIGINS. It states no licence on which this project releases -- anything and grants nothing: the terms a source's values come under are -- source_registry's, reached through each row's own source_id. CREATE TABLE column_origin ( tbl TEXT NOT NULL, col TEXT NOT NULL, -- span a column of the same row names its source: `via` -- claims a claims row names the source, value by value -- prose this project's text, labelled value by value against -- Wikipedia by the prose pass, in docs/prose_pass.tsv -- rather than here -- project this project's own record: a citation, a confidence tier, -- an identifier, a state, a classification, a link, its own -- writing outside the prose pass, or its account of itself -- derived built by build.py from other rows of this database: a -- count, or a record build.py defines and finds in them -- NULL not established origin TEXT CHECK (origin IN ('span', 'claims', 'prose', 'project', 'derived')), -- Where the source is recorded, for `span` and `claims`: the column of -- the same row ('spec_source'), or 'claims'. via TEXT, -- Which part of the declaration settled it, by the name -- tools/facts_artefact.py origins() gives it, so a reader can find the -- reason: 'APPARATUS', 'citation', 'SPANS', 'UNGRANTED', 'CLAIM_FIELDS', -- 'prose pass', 'UNSOURCED', 'RECOUNTED', or 'unreached' (none of it -- reaches the column). rule TEXT NOT NULL CHECK (rule IN ('APPARATUS', 'citation', 'SPANS', 'UNGRANTED', 'CLAIM_FIELDS', 'prose pass', 'UNSOURCED', 'RECOUNTED', 'unreached')), PRIMARY KEY (tbl, col), CHECK ((origin IS NOT NULL AND origin IN ('span', 'claims')) = (via IS NOT NULL)), CHECK ((origin IS 'claims') = (via IS 'claims')), CHECK ((rule = 'unreached') <= (origin IS NULL)) ); -- ------------------------------------------------------------- people CREATE TABLE drivers ( id TEXT PRIMARY KEY, -- slug, e.g. 'juan-manuel-fangio' full_name TEXT NOT NULL, nationality TEXT, nationality_code TEXT, born TEXT, -- ISO date where known died TEXT, -- What F1DB publishes about a person, which the harvest used to read and -- drop (PD-17). All five come from one CC BY 4.0 register and none is -- derived here. -- -- f1db_id is the reconciliation key: the id this driver resolves to in -- F1DB, stored rather than recomputed from a name match every time -- somebody wants to line the two registers up. UNIQUE because the match -- is one-to-one and has to stay that way - two entries claiming one F1DB -- driver is how Nelson Piquet Jr was once given his father's 23 wins, and -- a constraint states that in the schema rather than in a function. f1db_id TEXT UNIQUE, -- VER, HAM. Not unique - three letters are reused across eras - and not -- an identifier the sport issued: F1DB gives one to every driver it -- holds, so Fangio carries FAN for a championship that never used codes. abbreviation TEXT, -- The number the driver races under, where the era has one: 28 of F1DB's -- 917 drivers, all distinct. NOT the number on the car in a given season, -- which is season_entries.car_number - the reigning champion may carry 1 -- instead, and build.py holds the two to exactly that rule. permanent_number INTEGER, place_of_birth TEXT, -- the town, as F1DB writes it -- The country, in the one country vocabulary: normalise_countries() holds -- this column to the F1DB registry the same way it holds nationality. country_of_birth TEXT, -- The career block below mixes figures counted here with figures stored -- as a source gave them, and the two read alike (DA-16). Which is which: -- DERIVED on every build from the race records, which cover every -- championship race 1950-2026, so always consistent and current: -- wins, poles, fastest_laps, podiums (derivable since v2.7, when -- second and third place were harvested for every race), and -- practice_only. -- TYPED AND CHECKED: titles and title_years are typed in -- data/drivers.py, and verify.py holds both to the champions in -- `seasons`, so neither can disagree with them. -- STORED as typed: entries and career_points for the champions in -- data/drivers.py and the seven current drivers whose formula1.com -- pages were read on the date stats_as_of gives; starts for the -- champions among the typed rows only - those pages give no starts, -- so the four champions among the seven hold NULL - and NULL for -- everyone else. They are not recounted from race_entries and need -- not equal a count of it: a points total is everything the driver -- scored, but Fangio's and Senna's are championship totals, net of -- the scores the best-results rule dropped; a source may count an -- entry at a race the records hold no row for; and a current -- driver's figure has moved on since the date. Every difference from -- the race records is a 'stored-total' row in `discrepancies`, and a -- typed figure no source gave was corrected, its 'correction' row -- keeping the typed value (DA-42). -- MIXED: first_season and last_season are typed for the authored -- drivers and taken from the race records - or, for a driver who only -- ran in practice, the practice sheets - for the rest. first_season INTEGER, last_season INTEGER, entries INTEGER, starts INTEGER, wins INTEGER, podiums INTEGER, poles INTEGER, fastest_laps INTEGER, career_points REAL, titles INTEGER DEFAULT 0, title_years TEXT, -- comma-separated status TEXT CHECK (status IN ('active', 'retired', 'deceased')), -- 1 for a driver who ran in practice or sprint qualifying and never -- entered a championship race: a Friday reserve (LV-03). Derived by the -- build from those tables, never typed, and no page counts one among -- the drivers who raced. practice_only INTEGER NOT NULL DEFAULT 0 CHECK (practice_only IN (0, 1)), stats_as_of TEXT, -- when the career figures were true -- The *_external columns hold the separately sourced figure for the same -- statistic, so the two can be compared. Where they disagree, the -- difference is recorded in the discrepancies table rather than hidden. wins_external INTEGER, poles_external INTEGER, fastest_laps_external INTEGER, podiums_external INTEGER, external_source TEXT, notes TEXT, -- about the driver; the page's lede -- How the row entered the register, where that was a harvest rather than -- the authored list. Kept apart from notes because notes is read as the -- page lede and the meta description, and 111 of them opened with it. provenance TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence), source TEXT ); -- Where a driver's note was checked, where that is not the row's own source -- (LV-08). A Friday driver's row is F1DB's - name, dates, nationality - and -- F1DB says nothing about who they were, so the line that says it is checked -- against the driver's own article and cites it here rather than in the -- row's `source`, which would re-attribute F1DB's facts. One citation per -- note. The build gives this table `source_id` like every table carrying -- `source`, so its licence is a join and verify.py's licence checks read it; -- the driver page's sources list reads it too. CREATE TABLE driver_note_sources ( driver_id TEXT PRIMARY KEY REFERENCES drivers(id), source TEXT NOT NULL ); -- `role` is one or more roles from one list, joined by ' / ' in the order -- the person held or is known for them: "founder / designer". The CHECK -- strips each listed role, separators and all, and requires nothing to be -- left - a CHECK cannot hold a subquery, so this is how one column is held -- to a list without splitting it into a table. A role not on the list, a -- different separator and an empty part are refused; a repeated role only -- when the two are side by side, since replace() consumes one separator. -- Adding a role means adding it here (DA-13). CREATE TABLE personnel ( id TEXT PRIMARY KEY, full_name TEXT NOT NULL, nationality TEXT, role TEXT CHECK (role IS NULL OR replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(' / ' || role || ' / ', ' / team principal / ', ' / '), ' / technical director / ', ' / '), ' / sporting director / ', ' / '), ' / race director / ', ' / '), ' / designer / ', ' / '), ' / engineer / ', ' / '), ' / founder / ', ' / '), ' / co-founder / ', ' / '), ' / team owner / ', ' / '), ' / commercial rights holder / ', ' / '), ' / executive / ', ' / '), ' / official / ', ' / '), ' / neurosurgeon / ', ' / '), ' / driver / ', ' / '), ' / advisor / ', ' / ') = ' / '), active_from INTEGER, active_to INTEGER, associated_with TEXT, significance TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence) ); -- ------------------------------------------------------- constructors CREATE TABLE constructors ( id TEXT PRIMARY KEY, name TEXT NOT NULL, full_name TEXT, country TEXT, base TEXT, first_entry INTEGER, -- NULL = still competing, and verify.py holds it to that: an inactive -- constructor with none takes the last season it has a race entry in. last_entry INTEGER, entries INTEGER, wins INTEGER, poles INTEGER, constructors_titles INTEGER DEFAULT 0, drivers_titles INTEGER DEFAULT 0, title_years TEXT, -- The chain this NAME most recently belonged to: the chain of the latest -- constructor_lineage period whose constructor_id is this row, NULL when -- no period names it. build.py derives it where data/teams.py leaves it -- out and refuses a typed one that disagrees; verify.py re-derives it. -- It is NOT a join from a race entry to a chain. A name in this sport is -- reused by unrelated operations, so `renault` carries 'enstone' for -- 1977-85 as well, when Enstone was Toleman and Renault was somewhere -- else. To place an entry in a chain, join constructor_lineage on -- constructor_id and the race's year (see there). lineage_chain TEXT, active INTEGER NOT NULL DEFAULT 0, notes TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence), source TEXT ); -- A single continuous racing operation that changed names/owners over -- time, e.g. Enstone: Toleman > Benetton > Renault > Lotus > Renault > Alpine -- -- ONE ROW IS A PERIOD: a name the operation raced under, for a span of years, -- and the constructors row its race entries are recorded under. The period, -- not the constructors row, is what places a race entry in a chain: -- -- SELECT l.chain_id, e.* FROM race_entries e -- JOIN races r ON r.id = e.race_id -- JOIN constructor_lineage l ON l.constructor_id = e.constructor_id -- AND r.year BETWEEN l.from_year AND COALESCE(l.to_year, r.year) -- -- verify.py holds that join to exactly one period for every entry under a -- constructor any period names, so it neither drops nor doubles a row; and -- every period to at least one entry of its constructor (where it has any), -- so a constructor_id that points at the wrong team of the same name fails. CREATE TABLE constructor_lineage ( id INTEGER PRIMARY KEY, chain_id TEXT NOT NULL, chain_name TEXT NOT NULL, sequence INTEGER NOT NULL, entity_name TEXT NOT NULL, -- the name as raced, for reading -- The id, which entity_name is not: 'Jordan Grand Prix' is `jordan`, and -- Alfa Romeo 2019-23 and Kick Sauber are both `sauber`. constructor_id TEXT NOT NULL REFERENCES constructors(id), from_year INTEGER, to_year INTEGER, note TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); -- --------------------------------------------------- engines / power units CREATE TABLE engine_manufacturers ( id TEXT PRIMARY KEY, name TEXT NOT NULL, country TEXT, first_year INTEGER, last_year INTEGER, wins INTEGER, constructors_titles INTEGER DEFAULT 0, drivers_titles INTEGER DEFAULT 0, notes TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence) ); CREATE TABLE engine_eras ( id INTEGER PRIMARY KEY, from_year INTEGER NOT NULL, to_year INTEGER, era_name TEXT NOT NULL, formula TEXT NOT NULL, -- what the era's cars actually raced, not what the formula allowed -- (the `formula` column says that): 1966-76 permitted forced induction -- and nobody ran it. 'both' is an era where the two routes raced. aspiration TEXT CHECK (aspiration IN ('naturally aspirated', 'supercharged', 'turbocharged', 'turbocharged hybrid', 'both')), typical_config TEXT, approx_power_bhp TEXT, rev_limit TEXT, notes TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); -- ------------------------------------------------------------ seasons CREATE TABLE seasons ( year INTEGER PRIMARY KEY, rounds INTEGER, drivers_champion TEXT REFERENCES drivers(id), champion_team TEXT REFERENCES constructors(id), champion_points REAL, champion_wins INTEGER, runner_up TEXT REFERENCES drivers(id), runner_up_points REAL, margin REAL, constructors_champion TEXT REFERENCES constructors(id), constructors_points REAL, engine_formula TEXT, tyre_suppliers TEXT, notes TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence), source TEXT ); -- Championship entries: who drove what, in which year CREATE TABLE season_entries ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL REFERENCES seasons(year), constructor_id TEXT REFERENCES constructors(id), driver_id TEXT REFERENCES drivers(id), car TEXT, power_unit TEXT, car_number INTEGER, role TEXT DEFAULT 'race', -- race | reserve | substitute note TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); CREATE TABLE standings ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL, table_type TEXT NOT NULL CHECK (table_type IN ('drivers', 'constructors')), -- NULL where the entry has no championship position. That is not a gap: -- Michael Schumacher scored 78 points in 1997 and was EXCLUDED from the -- classification after Jerez, so he has points and no position. Storing -- a 0 there put him first in every query that ordered by position. position INTEGER, position_text TEXT, -- "2", or DSQ / EX -- The register's name for the entrant - drivers.full_name or -- constructors.name - on every row of every source, so one id stands -- under one name in a season's tables (DA-29). The name raced under that -- season is the lineage's entity_name, not this. entity TEXT NOT NULL, -- The entrant, in the namespace table_type names: a drivers.id on a -- drivers' row and a constructors.id on a constructors' one, exactly one -- of the two set. Up to v2.24 one `entity_id` column held both, and four -- ids are both - brabham, fittipaldi, amon, modena - so a join on it -- without table_type turned 517 constructors' rows into drivers' ones and -- said nothing (DA-10). It was retired after v2.24 (DA-31): these two -- declare the key that one could not. driver_id TEXT REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), -- The constructors' championship is contested by a CHASSIS-ENGINE -- combination, not by a chassis maker. Cooper-Climax won 1960 with 48 -- points; Cooper-Maserati and Cooper-Castellotti tied for fifth on 3. -- Without this column those are one row and Cooper's total is whichever -- happened to be written last. NULL on driver rows. -- The values are F1DB's engine-manufacturer ids - the ones -- engines.f1db_manufacturer_id holds, 'ferrari', 'red-bull-ford' - and not -- this database's engine_manufacturers.id, which spells them -- 'ferrari-eng'. A join on engine_manufacturers matches 'climax' and -- 'cosworth' by coincidence and drops the other nine rows in ten without -- an error (DA-11); join engines on f1db_manufacturer_id instead. engine_id TEXT, team TEXT, points REAL, -- The round these standings stood after, on every row (DA-01). An -- end-of-season row stands after the season's last round, and `basis` -- says which of the two tables after that round it belongs to. after_round INTEGER NOT NULL, -- 'running' is the total after after_round, as it stood that weekend. -- 'final' is the END-OF-SEASON classification, which is not the same -- fact as the running total after the last round: before 1991 the -- championship counted only a driver's best N results, and an exclusion -- takes the position away (Schumacher 1997), so the two tables after one -- round genuinely differ. A season still being run has no 'final' row; -- the table as it stands is each source's running table after the last -- round that source has counted, which is what v_standings_final reads. basis TEXT NOT NULL CHECK (basis IN ('running', 'final')), -- The day the source published this table, where the source dates it: -- formula1.com's snapshot of the season being run. NULL on every other -- row, which is not a gap - F1DB's tables are dated by the round they -- stand after, which is after_round. This is the one thing the retired -- `as_of` ('final' | 'round 7' | '2026-09-04 (after round 12)') said -- that after_round and basis do not (DA-31); verify.py holds it inside -- the days between the round it stands after and the next one. snapshot_date TEXT CHECK (snapshot_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'), confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence), source TEXT, CHECK ((table_type = 'drivers') = (driver_id IS NOT NULL)), CHECK ((table_type = 'constructors') = (constructor_id IS NOT NULL)) ); -- The key. The table used to declare UNIQUE (year, table_type, after_round, -- entity_id, engine_id, as_of), which SQLite made inert for 69% of the rows - -- it treats NULLs as distinct, and after_round was NULL on every end-of-season -- row and engine_id is NULL on every driver row - so a byte-for-byte -- duplicate of a final driver row was accepted. Once DA-01 filled after_round -- it bit instead, and refused the 2018 Force India pair below; it is gone, -- and this index is the one statement of the key, with the NULLs pinned. -- position_text is in it on purpose: it is the column that tells the two -- 2018 Force India constructor rows apart - EX on 0 points and P7 on 52, one -- entrant excluded and its successor scoring under the same id - which are -- two facts and not a loader running twice. `source` is in it so an -- official snapshot and F1DB's table after the same round can coexist -- without one silently overwriting the other, and `basis` so a season's -- final classification and its running table after the last round can; the -- retired `as_of` did both until DA-31 swapped it for these two. CREATE UNIQUE INDEX ux_standings_identity ON standings( year, table_type, after_round, basis, COALESCE(driver_id, ''), COALESCE(constructor_id, ''), COALESCE(engine_id, ''), COALESCE(source, ''), COALESCE(position_text, '')); -- ------------------------------------------------- circuits and events CREATE TABLE circuits ( id TEXT PRIMARY KEY, name TEXT NOT NULL, official_name TEXT, locality TEXT, country TEXT, circuit_type TEXT CHECK (circuit_type IN ('permanent', 'street', 'hybrid', 'oval', 'road')), first_gp INTEGER, last_gp INTEGER, gp_count INTEGER, length_km REAL, turns INTEGER, -- One spelling. Jacarepagua carried 'anticlockwise' for nine releases and -- a filter on direction missed it; the vocabulary rule that already -- governs countries and confidence tiers now governs this. direction TEXT CHECK (direction IN ('clockwise', 'anti-clockwise')), characteristics TEXT, notes TEXT, -- The Wikipedia article the List of Formula One circuits links for this -- circuit (VD-47, VD-61), from harvest/circuit_articles.txt, matched on -- country, seasons and races held and never on the name. Each value is a -- claim citing the list's revision. Not UNIQUE: the Nordschleife and the -- GP-Strecke are one article. NULL: no list maps it - the Sudschleife, -- known_gaps 'circuit-article-sudschleife'. article TEXT, -- Where the list links a SECTION of a larger article rather than one -- about the circuit - Fair Park, the Bugatti Circuit, Zeltweg Air Base - -- the section's name. NULL with an article: the whole article. article_section TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence), source TEXT ); CREATE TABLE circuit_layouts ( id INTEGER PRIMARY KEY, circuit_id TEXT NOT NULL REFERENCES circuits(id), layout_key TEXT NOT NULL, -- stable id, unique per circuit layout_name TEXT, from_year INTEGER, to_year INTEGER, -- 1: this layout is what was raced at this circuit for every season in -- [from_year, to_year], and such rows form a complete non-overlapping -- timeline. 0: a one-off used for particular races only, reachable -- solely through races.layout_key. A season that ran two different -- layouts - Bahrain in 2020 - needs the second one flagged this way. by_year INTEGER NOT NULL DEFAULT 1, length_km REAL, turns INTEGER, change_reason TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence), -- The F1DB outline that draws this row, DERIVED in build.py: set where -- every race in the row's span ran one F1DB layout, NULL where the span -- crosses two (F1DB splits Monza's chicane era where this timeline does -- not) or no race here ran it. A race page never needs it - races carry -- their own f1db_layout_id - so a NULL loses a reader nothing. f1db_layout_id TEXT REFERENCES circuit_outlines(f1db_layout_id), UNIQUE (circuit_id, layout_key) ); -- The outline of every layout the championship has raced on, from F1DB. -- -- A DRAWING, not a measurement, and a different fact from a row in -- circuit_geometry. The trace there is OpenStreetMap's: geo-referenced, -- measured against the published length, and possible only for a layout -- that is on the ground today, which caps it at 25 circuits. The outline is -- F1DB's, drawn by Jules Roy in a 500x500 box with no scale, no position and -- no direction of travel, and exists for all 160 layouts of every circuit - -- seven Monzas and eight Silverstones among them. The rule the site prints -- wherever a shape appears: the outline is F1DB's, for every layout; the -- trace is OpenStreetMap's, where it exists. Nothing checks one against the -- other, because they do not claim the same thing. -- -- CC BY 4.0 like the rest of F1DB, so unlike the ODbL trace it may live in -- this file. The key is F1DB's own layout id. circuit_id is DERIVED in -- build.py from the races that ran the layout, never read from F1DB: its -- circuit ids differ from this register's for ten venues, and its one -- `nurburgring` is three circuits here. f1db_circuit_id keeps what it said. CREATE TABLE circuit_outlines ( f1db_layout_id TEXT PRIMARY KEY, -- F1DB's id: monza-7 circuit_id TEXT NOT NULL REFERENCES circuits(id), f1db_circuit_id TEXT NOT NULL, length_km REAL, -- F1DB's figures for the layout, not this register's turns INTEGER, path TEXT NOT NULL, -- SVG path data; viewBox 0 0 500 500 confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT ); -- The centreline of a circuit as OpenStreetMap maps it, checked against the -- length this database already held. -- -- ODbL 1.0, which is share-alike AND carries a database right. That is a -- different obligation from anything else here and it is confined to this -- one table on purpose - see ATTRIBUTION.md. Nothing else in the database -- derives from OpenStreetMap. -- -- The trace can only ever be the CURRENT layout: OSM maps what is on the -- ground. Historic configurations - Spa's 14.1 km road course, Monza's -- banking - have no geometry source anywhere, so they have no row here, and -- layout_key names the configuration a trace actually corresponds to rather -- than letting a modern shape stand in for a 1955 one. -- -- measured_km, published_km and delta_pct are all stored so the check is -- visible in the data and not only in the loader. A naive sum of a circuit -- relation's member ways includes the pit lane and puts Monaco 12% long; -- what rejects that is published_km, which came from somewhere else. CREATE TABLE circuit_geometry ( circuit_id TEXT NOT NULL REFERENCES circuits(id), -- NULL where the circuit has no layout timeline at all. Never a historic -- layout: a trace is of the shape that exists now. layout_key TEXT, wikidata_id TEXT NOT NULL, -- Q171400; CC0, brokers the id osm_relation INTEGER NOT NULL, -- 148194 centreline TEXT NOT NULL, -- GeoJSON MultiLineString, lon/lat measured_km REAL NOT NULL, -- summed from centreline published_km REAL NOT NULL, -- what it was checked against delta_pct REAL NOT NULL, -- signed, and small by construction node_count INTEGER, -- Topology, measured when the row is admitted. An OSM relation's members -- are an unordered bag of ways; whether they form a lap is a separate -- question from whether they measure the right length, and a trace can be -- short by one way and still measure plausibly. See build.py. segment_count INTEGER, -- ways in the MultiLineString loose_ends INTEGER, -- way ends meeting nothing closes INTEGER, -- 1 = every way stitches into one closed ring osm_timestamp TEXT, -- the relation version measured licence TEXT NOT NULL DEFAULT 'ODbL-1.0', confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), UNIQUE (circuit_id, layout_key) ); -- ------------------------------------------------------------------ cars -- One row per car model. A car is a chassis design, not a season: the Lotus -- 79 raced in 1978 and 1979 and is one row. Where a design was substantially -- revised and given a new designation - 72C, 72D, 72E - each designation is -- its own row, chained through supersedes_id, because that is how results -- were published and how the cars are talked about. -- -- The spec columns are patchy ON PURPOSE. A famous car has published figures; -- a 1954 privateer entry does not. A NULL here means "not established", never -- "zero", and spec_confidence records how good the figures that ARE here are. CREATE TABLE cars ( id TEXT PRIMARY KEY, -- lotus-79, ferrari-312t constructor_id TEXT NOT NULL REFERENCES constructors(id), designation TEXT NOT NULL, -- "79", "312T", "MP4/4" full_name TEXT NOT NULL, -- "Lotus 79" from_year INTEGER, to_year INTEGER, supersedes_id TEXT REFERENCES cars(id), -- the design this evolved from designers TEXT, -- comma-separated engine_id TEXT REFERENCES engine_manufacturers(id), engine_name TEXT, -- "Ford Cosworth DFV" as published tyres TEXT, -- power unit engine_config TEXT, -- V8, V12, flat-12, turbo I4 capacity_cc INTEGER, -- one vocabulary with chassis and engines (DA-13) aspiration TEXT CHECK (aspiration IN ('naturally aspirated', 'supercharged', 'turbocharged', 'turbocharged hybrid', 'gas turbine')), power_bhp INTEGER, -- peak race power as published power_note TEXT, -- qualifying boost, era caveats rev_limit_rpm INTEGER, -- chassis and running gear chassis_type TEXT, -- spaceframe | aluminium monocoque -- | carbon-fibre composite gearbox TEXT, suspension TEXT, brakes TEXT, weight_kg REAL, -- as raced, to the era's rules wheelbase_mm INTEGER, track_front_mm INTEGER, track_rear_mm INTEGER, fuel_capacity_l INTEGER, -- what the car was for, and what it changed concept TEXT, -- the design idea in one line innovations TEXT, -- what it introduced story TEXT, -- the deep dive, where warranted outcome TEXT, -- what it actually achieved -- derived at build time from race_entries, never stored by hand -- races with a RECORDED entry, not races entered: race_entries holds -- only winners, pole-sitters and fastest-lap setters, so this is always -- a lower bound on the car's true race count. races INTEGER DEFAULT 0, wins INTEGER DEFAULT 0, poles INTEGER DEFAULT 0, fastest_laps INTEGER DEFAULT 0, -- authored, not derived: a title season is often shared between two -- chassis (Lotus ran the 78 and the 79 through 1978) and there is no -- defensible rule for splitting a championship between them. These are -- the titles the car was the team's primary chassis for. drivers_titles INTEGER DEFAULT 0, constructors_titles INTEGER DEFAULT 0, landmark INTEGER NOT NULL DEFAULT 0, -- 1 = in the deep-dive set confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence), spec_confidence TEXT REFERENCES provenance(confidence), source TEXT, UNIQUE (constructor_id, designation) ); -- ------------------------------------------------- the chassis register -- -- `cars` above is a curated set: a design, its story and what it changed, -- researched one at a time. `chassis` is the opposite - every chassis that -- has raced, 1,153 of them, loaded from F1DB by tools/f1db_fetch.py without -- a person in the middle. -- -- The two are different things and are deliberately not merged. A `cars` row -- is a design family (one row covers the Ferrari 312T through 312T5, because -- that is how Wikipedia, and the sport, treat it). A `chassis` row is one -- machine as entered. The link between them runs through `chassis.car_id`, -- and where both hold the same figure the build compares them instead of -- picking one: a specification derived twice by different routes is the -- strongest evidence this database has, and a disagreement goes to -- `discrepancies` rather than being resolved silently. CREATE TABLE chassis ( id TEXT PRIMARY KEY, -- f1db id: ferrari-312t2 constructor_id TEXT REFERENCES constructors(id), -- NULL where this -- database has no constructor f1db_constructor_id TEXT NOT NULL, -- always present name TEXT NOT NULL, -- "312T2" full_name TEXT NOT NULL, -- "Ferrari 312T2" car_id TEXT REFERENCES cars(id), -- the design family, if curated first_year INTEGER, -- first season entered last_year INTEGER, -- last season entered seasons INTEGER NOT NULL DEFAULT 0, -- specifications harvested from the chassis's own Wikipedia article. -- NULL is "not established" throughout, and is never a zero or a guess. -- A figure that is only the season's regulation limit is NOT stored here -- - it lives in regulation_limits, where a rule belongs. article TEXT, -- the article the specs came from designers TEXT, chassis_type TEXT, susp_front TEXT, susp_rear TEXT, engine_name TEXT, engine_config TEXT, -- the infobox's turbo/na field, normalised by build.py's -- _aspiration(): 'N/A' and a bare rev limit are NULL, not a guess aspiration TEXT CHECK (aspiration IN ('naturally aspirated', 'supercharged', 'turbocharged', 'turbocharged hybrid', 'gas turbine')), engine_position TEXT, gearbox TEXT, gears TEXT, brakes TEXT, fuel TEXT, tyres TEXT, capacity_cc INTEGER, power_bhp INTEGER, power_note TEXT, weight_kg REAL, wheelbase_mm INTEGER, track_front_mm INTEGER, track_rear_mm INTEGER, fuel_capacity_l INTEGER, predecessor TEXT, successor TEXT, -- published career figures off the same article. Not specifications: -- these are the numbers the database derives from its own race records, -- and comparing the two is what proves a linkage rather than assuming it. published_races INTEGER, published_wins INTEGER, published_poles INTEGER, -- derived at build time from race_entries, never stored by hand races INTEGER NOT NULL DEFAULT 0, wins INTEGER NOT NULL DEFAULT 0, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), spec_source TEXT, source TEXT NOT NULL ); -- The record of which CAR_SEASONS claims the entry lists corroborate. -- -- data/cars.py asserts, per (car, season), that every race the constructor -- won, took pole for or set fastest lap in that year was in this car. That is -- an authored claim, and this table is where it stops being trusted and -- starts being checked: `corroborated` is 1 only where the season's entry -- lists name no chassis outside the ones the car covers. -- -- Where it is 0, `other_chassis` says what else the constructor ran, and the -- blanket link is NOT made - an entry that season gets a car only if the -- entry lists resolved its chassis outright. The claim was safe for wins by -- luck rather than by construction, and the moment poles could be attributed -- it showed: McLaren ran the M23 and M26 through 1976-77, and the blanket -- gave the M23 sixteen poles against a published career fourteen. -- The photograph of each car article, and the attribution needed to show it. -- -- NO IMAGE IS STORED. This is a reference and its credit: which file an -- article carries (its lead image, or a body image that names the car), who -- took it, under what licence. The pixels are fetched from Wikimedia's -- media servers by whatever renders the page, under Wikimedia's terms; -- this database redistributes nothing and f1.db does not grow. -- -- The claim is checkable and it is deliberately narrow: "the article already -- proved to describe this chassis carries this file". The article passed -- the three checks in tools/wikispec_fetch.py before it got here, so the row -- is not an image found by searching for a car's name. -- -- What CANNOT be checked is whether the photograph shows the car. Nothing in -- this database constrains the content of an image and there is no second -- source to disagree. Testing whether the file name mentions the chassis -- finds under half the correct images - most are filed under the driver - -- so name_matches is RECORDED AND ENFORCED NOWHERE. These rows are -- 'unverified' because that is what they are. -- -- A second route (AF-42) reaches the chassis no article describes: a -- Wikimedia Commons category named for the chassis. Its claim is weaker - -- "Commons editors filed this file under a category named for this -- chassis" - and a category admits replicas and show cars, so its rows are -- keyed on the chassis, not an article, and sit at 'catalogued', one rung -- below 'unverified'. `route` keeps the two separable whatever a person -- later does to `confidence`. -- -- A third route (VD-61) is a circuit's: an aerial or satellite photograph in -- the body of the article circuits.article names, whose file name names the -- circuit (data/harvest.py CIRCUIT_PHOTOGRAPH). Keyed on the circuit, not -- the article, because two circuits share one. The claim is the article -- route's - "the article mapped to this circuit carries this file" - and so -- is the rung, 'unverified'. A circuit whose article is a section of a -- larger one, or not about the circuit, takes none. -- -- A fourth route (PD-64) is a race's: photographs filed under the Commons -- category named for the race, `Category: ` -- (data/harvest.py race_category), filed as that season's Formula One. The -- category route's claim and rung, 'catalogued'. The only route that keys -- several rows on one subject - a race keeps up to RACE_PHOTOGRAPHS_KEPT - -- so `race_id` is not UNIQUE and the pair with the file is. CREATE TABLE article_images ( route TEXT NOT NULL DEFAULT 'article' CHECK (route IN ('article', 'category', 'circuit', 'race')), article TEXT UNIQUE, -- joins chassis.article; the article route's key chassis_id TEXT UNIQUE REFERENCES chassis(id), -- the category route's key circuit_id TEXT UNIQUE REFERENCES circuits(id), -- the circuit route's key race_id INTEGER REFERENCES races(id), -- the race route's key; several rows each category TEXT, -- 'Category:...' on Commons, category and race routes file_name TEXT NOT NULL, -- 'File:...' as Commons spells it -- 'shared' on the article and circuit routes. A file hosted locally on -- en.wikipedia.org is local BECAUSE it is non-free; linking one would be -- a licence violation. -- -- 'commons' on the category and race routes, and this is a RESTATEMENT of that -- check, not a weakening of it. Asked about its own file, Commons answers -- 'local'; 'shared' is only what en.wikipedia.org says of a file it does -- not host. The harvest instead requires that the answer came from -- commons.wikimedia.org for a page in namespace 6, which is where a -- Commons file lives and where a non-free en.wikipedia.org upload cannot -- be. See tools/wikimedia_images.py. repository TEXT NOT NULL CHECK (repository IN ('shared', 'commons')), -- Every file carries its own. Sixteen distinct licence strings appear -- across these rows, so there is no blanket credit line for them. licence TEXT NOT NULL, licence_url TEXT, artist TEXT, -- plain text; the API returns HTML credit TEXT, description_url TEXT NOT NULL, -- the Commons file page -- The pixels' own address, as the API gave it when asked for 800 px -- (VD-23): the 960px thumbnail, since Commons rounds up to the widths it -- serves, or the original where that is narrower. One request where -- Special:FilePath takes three, two of them redirects. -- NULL where the harvest could not fetch it; a page then builds the -- Special:FilePath address. verify.py checks it names this row's file -- in Commons' own path, on upload.wikimedia.org or thumb.wikimedia.org. thumb_url TEXT, width INTEGER, height INTEGER, name_matches INTEGER NOT NULL DEFAULT 0, confidence TEXT NOT NULL DEFAULT 'unverified' REFERENCES provenance(confidence), -- Exactly one key per route, and each route's repository with it. CHECK ((route = 'article' AND article IS NOT NULL AND chassis_id IS NULL AND circuit_id IS NULL AND race_id IS NULL AND category IS NULL AND repository = 'shared') OR (route = 'category' AND article IS NULL AND chassis_id IS NOT NULL AND circuit_id IS NULL AND race_id IS NULL AND category IS NOT NULL AND repository = 'commons') OR (route = 'circuit' AND article IS NULL AND chassis_id IS NULL AND circuit_id IS NOT NULL AND race_id IS NULL AND category IS NULL AND repository = 'shared') OR (route = 'race' AND article IS NULL AND chassis_id IS NULL AND circuit_id IS NULL AND race_id IS NOT NULL AND category IS NOT NULL AND repository = 'commons')), -- A race's photographs, each once. NULL race_id on the other routes is -- distinct under UNIQUE, so this binds the race route alone. UNIQUE (race_id, file_name) ); CREATE TABLE car_seasons ( car_id TEXT NOT NULL REFERENCES cars(id), year INTEGER NOT NULL, corroborated INTEGER NOT NULL, other_chassis TEXT, -- '+'-separated, when not PRIMARY KEY (car_id, year) ); -- Who entered what, per season: the mapping that lets a race be tied to a -- chassis at all. One row per (season, entrant, constructor). -- -- The `+`-separated lists are the point of this table rather than an -- awkwardness in it. F1DB records which chassis a constructor ran in a -- season; it does NOT record which chassis ran in which round. Where a team -- ran two designs both are listed, and splitting them into separate rows -- would invent an attribution the source never made. `chassis_count` = 1 is -- exactly the case where the season constrains the chassis, and it is the -- only case build.py will link a race entry from. CREATE TABLE season_entrants ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL REFERENCES seasons(year), entrant_id TEXT NOT NULL, -- f1db entrant id f1db_constructor_id TEXT NOT NULL, constructor_id TEXT REFERENCES constructors(id), -- part of the grain, not decoration: Team Lotus in 1966 is one entrant -- with two constructor blocks, both Lotus, one on Climax and one on BRM. engine_manufacturer_id TEXT, chassis_ids TEXT, -- '+'-separated chassis_count INTEGER NOT NULL DEFAULT 0, engine_ids TEXT, -- '+'-separated tyre_ids TEXT, -- '+'-separated -- The names a reader is shown in place of the ids (UR-14), off F1DB's own -- registers rather than title-cased from a slug. `entrant_name` is the -- register's one name for the entrant, not that season's: F1DB keeps no -- per-season name. `tyre_names` is tyre_ids' list in the same order, -- which build.py can promise because it refuses a name holding a '+'. -- The chassis and engines need no such column: `chassis.full_name` and -- `engines.full_name` name every id in those two lists already. entrant_name TEXT NOT NULL, tyre_names TEXT, -- '+'-separated, as tyre_ids confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT NOT NULL, UNIQUE (year, entrant_id, f1db_constructor_id, engine_manufacturer_id) ); -- Every engine that has raced, with the three specifications F1DB carries. CREATE TABLE engines ( id TEXT PRIMARY KEY, manufacturer_id TEXT REFERENCES engine_manufacturers(id), f1db_manufacturer_id TEXT NOT NULL, name TEXT NOT NULL, full_name TEXT NOT NULL, capacity_l REAL, configuration TEXT, -- V10, F12, L6 ... -- F1DB's NATURALLY_ASPIRATED etc., lower-cased with spaces aspiration TEXT CHECK (aspiration IN ('naturally aspirated', 'supercharged', 'turbocharged', 'turbocharged hybrid', 'gas turbine')), confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT NOT NULL ); -- Numeric limits the regulations put on every car in a season. -- -- Here rather than on a car because that is what they are. Most "weight" -- quoted for a modern car is the season's minimum, and the 2026 figures in -- circulation - 768 kg, 3,400 mm, 1,900 mm - are rules, not measurements of -- anything. A per-car field holding one of them would be inference presented -- as fact. -- -- The series has holes on purpose: a row covers from_year..to_year, and -- to_year is set only where a source records the next change. Carrying a -- value across an unrecorded change would invent a limit. CREATE TABLE regulation_limits ( id INTEGER PRIMARY KEY, from_year INTEGER NOT NULL, to_year INTEGER NOT NULL, field TEXT NOT NULL, -- minimum_weight_kg, maximum_width_mm value REAL NOT NULL, unit TEXT NOT NULL, note TEXT, confidence TEXT NOT NULL REFERENCES provenance(confidence), source TEXT NOT NULL, UNIQUE (from_year, field) ); CREATE TABLE grands_prix ( id TEXT PRIMARY KEY, name TEXT NOT NULL, country TEXT, first_held INTEGER, last_held INTEGER, editions INTEGER, circuits_used TEXT, aliases TEXT, -- other names this event has carried notes TEXT, confidence TEXT NOT NULL DEFAULT 'medium' REFERENCES provenance(confidence) ); -- ===================================================================== -- The race model (v2.4) -- -- `races` is one row per championship event. -- `race_entries` is one row per driver per race. -- -- Every per-driver fact about a race - who was on pole, who won, who set -- the fastest lap - is an attribute of an ENTRY, not of the race. Before -- v2.4 those three facts were columns on the race row plus a separate -- credits table, which meant a shared win and a shared fastest lap were -- modelled two different ways and neither could be extended to a full -- finishing order without changing shape again. -- -- Deriving from entries: -- pole pole = 1 -- win finish_position = 1 -- fastest lap fastest_lap = 1 -- Adding the rest of the finishing order is then pure INSERT. -- -- WHAT 'POLE' MEANS HERE. Three different facts sit near the front of a grid -- and this schema holds each in its own place: -- -- pole = 1 the driver CREDITED with pole position, as the -- season record credits it (the pole harvest) -- grid = 1 the car that STARTED from the front of the grid -- (F1DB's grid) -- qualifying.position = 1 the driver who was QUICKEST in qualifying -- -- They usually name one driver and sometimes do not. A grid penalty moves the -- fastest qualifier back and pole stays with whoever starts first; through -- 2021 a sprint set the grid and pole went to the sprint winner; from 2022 a -- sprint weekend credits the fastest qualifier even where the sprint winner -- starts first (2022 round 21: Magnussen has pole, Russell grid 1). And a -- pole-sitter who never starts leaves grid 1 empty (1996 round 9, 2021 round -- 5). None of these is a disagreement between sources - each column is true -- of the thing it describes - so none is recorded in `discrepancies`; -- verify.py pins the counts instead, so a new case arrives as a question. -- -- Through v2.20 `grid = 1` carried both of the first two meanings, which made -- 2022 round 21 a row with grid = 1 and grid_text = '8'. -- ===================================================================== CREATE TABLE races ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL REFERENCES seasons(year), round INTEGER NOT NULL, gp_id TEXT NOT NULL REFERENCES grands_prix(id), name_used TEXT NOT NULL, -- the name carried that year circuit_id TEXT REFERENCES circuits(id), layout_key TEXT, -- overrides the year lookup -- The F1DB layout this race ran, from the round's own race.yml: the key -- into circuit_outlines, so every race can be drawn whether or not its -- circuit has a layout timeline here. f1db_layout_id TEXT REFERENCES circuit_outlines(f1db_layout_id), -- THE RACE DAY, AND THE WEEKEND AROUND IT WHERE ONE IS STATED. -- date_iso is the day the race itself was held, YYYY-MM-DD, on every -- row: F1DB's date for every round it holds, and the last -- day of the announced weekend - the LOCAL day - for a -- round it has not reached yet. The two readings differ -- only where a race runs past midnight UTC, so Las Vegas -- 2027's date_iso moves a day when F1DB lists it. -- date_from the first and last day of the weekend, YYYY-MM-DD, where -- date_to a source states one - today the calendars formula1.com -- announces, so the current season and the next. NULL on -- every other row, because nothing here says which day -- practice began at Silverstone in 1950; a renderer shows -- the race day there instead. -- Until DA-15 this was one `dates` column that held an ISO day on 98% of -- rows and a display range - "27-29 Mar 2026", "30 Oct-01 Nov 2026" - on -- the rest, so a renderer printed raw ISO most of the time and no check -- could read the range. Both halves are now dates, and verify.py holds -- them to each other: date_from <= date_to, and the race day is date_to. -- The one exception: with a timetable in `sessions`, date_to is the -- race's local day and date_iso its UTC day, which is F1DB's reading - -- Las Vegas races on a Saturday evening that is Sunday in UTC. That is -- measured where a timetable is held and declared where none is yet -- (data/current.py RACE_DAY_AFTER_WEEKEND). The `calendar` view still -- offers the old display string as `dates`, derived from these three. date_iso TEXT, -- YYYY-MM-DD, the race day date_from TEXT, -- YYYY-MM-DD, the weekend's first day, or NULL date_to TEXT, -- YYYY-MM-DD, its last day, or NULL sprint INTEGER NOT NULL DEFAULT 0, -- completed | scheduled. A cancelled round has no row rather than a -- third value: the calendar holds what was and will be run, and the -- front end knows only these two. A third state fails loudly here first. status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('completed', 'scheduled')), -- WHETHER F1DB'S CALENDAR LISTS THE ROUND (SD-37). 1 where F1DB lists a -- race in this season on date_iso; 0 where F1DB holds the season's -- calendar and lists no race that day - a round cancelled or moved after -- it was typed into data/current.py; NULL where F1DB holds no calendar -- for the season at all (2027, today), which is a season the harvest has -- not reached rather than one of cancellations. Matched on the day and -- not the round number, because F1DB renumbers the rounds after one it -- drops. It is the half of the late-results rule (decided on #786) that -- refresh.yml's health check reads from harvest/race_dates.txt, carried -- here so the site can apply the rule with the reader's own date: a -- round is late when it is more than three days past date_iso, holds no -- result, and this is not 0. verify.py recomputes it from the harvest. on_f1db_calendar INTEGER CHECK (on_f1db_calendar IN (0, 1)), note TEXT, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, UNIQUE (year, round) ); -- The weekend timetable of the current season (LV-02): one row per session, -- the start in UTC and the circuit's IANA zone beside it, so a page can show -- both the circuit's clock and the reader's, and count down to the next -- session in the browser. Rows exist only for the season in progress; the -- source is formula1.com's race page (a start time is a fact and may be -- re-stated), and the FIA's per-event timetable is what it will be checked -- against. A sprint weekend is fp1, sprint_qualifying, sprint, qualifying, -- race; any other is fp1, fp2, fp3, qualifying, race - verify.py holds each -- weekend to the set races.sprint implies, and holds the race's local day to -- races.date_to and its UTC day to races.date_iso. Las Vegas is why the zone -- travels with the row: its Saturday-evening race is Sunday in UTC. CREATE TABLE sessions ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), kind TEXT NOT NULL CHECK (kind IN ('fp1', 'fp2', 'fp3', 'sprint_qualifying', 'sprint', 'qualifying', 'race')), name TEXT NOT NULL, -- "Practice 1", "Sprint qualifying" start_utc TEXT NOT NULL, -- YYYY-MM-DDTHH:MMZ; the Z is what makes a browser read it as UTC zone TEXT NOT NULL, -- IANA tz database name of the circuit confidence TEXT NOT NULL DEFAULT 'verified' REFERENCES provenance(confidence), source TEXT NOT NULL, UNIQUE (race_id, kind) ); CREATE TABLE race_entries ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT NOT NULL REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), car_id TEXT REFERENCES cars(id), -- the curated design family -- The chassis as entered. Filled only where the season's entry list -- names exactly one chassis for the constructor, which is the only case -- in which the season constrains it; NULL everywhere else, including -- every season a team ran two designs. chassis_id TEXT REFERENCES chassis(id), entrant TEXT, -- chassis-engine as published grid INTEGER, -- the slot the car started from -- Credited with pole position. Distinct from grid = 1 on purpose: see -- WHAT 'POLE' MEANS HERE, above. Credited from harvest/poles.txt - the -- Wikipedia season tables - whatever `source` says: `source` names who -- established the row's FINISHING POSITION, and the pole and fastest-lap -- flags are the one thing on a row that can come from elsewhere, bar -- `note`, which `claims` cites. Field-grain sourcing is what `claims` -- would carry for the flags too (docs/DERIVED-CONFIDENCE.md); until then -- this is where the exception is declared. pole INTEGER NOT NULL DEFAULT 0, -- The grid slot as the source states it. Almost always the same number -- as `grid`, but 236 entries started from the PIT LANE, which is not a -- grid slot at all. NULLing those would say "we do not know where they -- started", which is the opposite of the truth. grid_text TEXT, finish_position INTEGER, -- 1 = race win; NULL if unclassified -- The result as the source states it, losslessly: "1", or one of NC, -- DNF, DNQ, DNPQ, DNP, DNS, DSQ, EX. finish_position is an integer so it -- can be ordered and counted; this keeps what actually happened, because -- "did not qualify" and "retired on lap 3" are different facts and -- collapsing them loses the late-1980s entirely - 1,041 failures to -- qualify and 338 failures to PRE-qualify. position_text TEXT, shared_drive INTEGER NOT NULL DEFAULT 0, fastest_lap INTEGER NOT NULL DEFAULT 0, -- credited as `pole` is, see above fastest_lap_shared INTEGER, -- how many drivers shared it -- Reserved for the full finishing order. Declared now so that adding it -- is pure INSERT and no consumer of this schema has to change. classified INTEGER, -- 1 = classified finisher status TEXT, -- Finished | +1 Lap | Engine | Accident ... laps_completed INTEGER, -- 0 for a classified finisher below the last place the season's points -- system paid (points_systems.scale, DA-08), and for an entry that was -- not classified - retired, disqualified, did not start, did not qualify -- - in a race that has a classification (DA-36): the rule paid them -- nothing, which is established, so it is not a blank. A point such an -- entry did earn, a 1950s fastest lap, keeps its value. A finisher -- inside the paid places whom the race's own rules did not pay - a -- shared drive, a Formula Two car, a second car not entered for the -- championship, a penalty - holds 0 too, and `note` says why, citing the -- race's article in `claims` (DA-37). NULL is left only in a race -- nobody has yet been classified in. points REAL, -- Why the row is not what its place would suggest: today, only why a -- finisher inside the paid places scored nothing (DA-37). Every value -- is a claim (CLAIM_FIELDS), so its source is a lookup. note TEXT, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, -- One row per driver per race (known_gaps #2). A driver F1DB enters -- twice in one race - a shared drive, a car taken over, a second car -- entered - keeps the first row's car; the build merges the rest into -- it, and refuses an undeclared one whose car was another constructor's -- (data/harvest.py SECOND_ENTRIES, CR-62). UNIQUE (race_id, driver_id) ); CREATE INDEX idx_races_year ON races(year, round); CREATE INDEX idx_races_gp ON races(gp_id); CREATE INDEX idx_races_circuit ON races(circuit_id); -- ---------------------------------------------------------------- sprints -- -- A sprint is a SEPARATE RACE held on a grand prix weekend, with its own -- grid, its own classification and its own points — and those points count -- towards the championship. It is not a session of the grand prix, so it is -- not a column on race_entries: it is its own rows, keyed on the round that -- held it. -- -- Sprints began at Silverstone in 2021. Before that this table is empty, and -- that emptiness is a fact rather than a gap: there were none. -- -- No shared_drive column, unlike race_entries. Sharing a car mid-race ended -- in 1964 and no sprint has ever had one, so a column for it would only ever -- hold zero. CREATE TABLE sprint_results ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT NOT NULL REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), grid INTEGER, -- the sprint grid, not the GP grid finish_position INTEGER, -- 1 = sprint win; NULL if unclassified -- As race_entries.position_text: "1", or NC, DNF, DNS, DSQ. Kept because -- "retired" and "did not start" are different facts. position_text TEXT, status TEXT, -- reason retired, where stated laps_completed INTEGER, time TEXT, -- winner's time; gap for the rest gap TEXT, points REAL, -- counts towards the championship; -- 0 below the paid places and for an -- entry not classified, as race_entries note TEXT, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, UNIQUE (race_id, driver_id) ); CREATE INDEX idx_sprint_race ON sprint_results(race_id); CREATE INDEX idx_sprint_driver ON sprint_results(driver_id); CREATE INDEX idx_sprint_cons ON sprint_results(constructor_id); CREATE INDEX idx_sprint_pos ON sprint_results(race_id, finish_position); CREATE INDEX idx_entries_race ON race_entries(race_id); CREATE INDEX idx_entries_driver ON race_entries(driver_id); CREATE INDEX idx_entries_cons ON race_entries(constructor_id); CREATE INDEX idx_entries_win ON race_entries(finish_position); CREATE INDEX idx_entries_grid ON race_entries(grid); CREATE INDEX idx_entries_fl ON race_entries(fastest_lap); -- composite indexes so the per-race lookups in the compatibility views and -- in any "who won / who was on pole" query are index-only CREATE INDEX idx_entries_race_pos ON race_entries(race_id, finish_position); CREATE INDEX idx_entries_race_grid ON race_entries(race_id, grid); CREATE INDEX idx_entries_race_fl ON race_entries(race_id, fastest_lap); CREATE INDEX idx_entries_driver_pos ON race_entries(driver_id, finish_position); CREATE INDEX idx_entries_car ON race_entries(car_id); CREATE INDEX idx_entries_chassis ON race_entries(chassis_id); CREATE INDEX idx_chassis_cons ON chassis(constructor_id); CREATE INDEX idx_chassis_car ON chassis(car_id); CREATE INDEX idx_chassis_years ON chassis(first_year, last_year); CREATE INDEX idx_entrants_year ON season_entrants(year, constructor_id); CREATE INDEX idx_cars_constructor ON cars(constructor_id); CREATE INDEX idx_cars_years ON cars(from_year, to_year); -- ------------------------------------------- rules, tech and safety -- Qualifying, which is a session and not a property of the race. -- -- This is deliberately NOT a column on race_entries. race_entries.grid is the -- STARTING GRID, which penalties, engine changes and pit-lane starts move -- away from the qualifying order; F1DB publishes the two separately for the -- same reason. A driver can also qualify and never start, and 1,041 drivers -- failed to qualify at all - none of which fits on a row about a race. -- -- Pre-1996 qualifying is a single time. The knockout era has three segments -- and no single time, so both shapes are stored and neither is back-filled -- from the other: q1/q2/q3 are NULL for 1950, and `time` is NULL for 2021. CREATE TABLE qualifying ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT NOT NULL REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), position INTEGER, -- NULL where not classified position_text TEXT, -- "1", DNQ, DNPQ, DNS ... driver_number INTEGER, time TEXT, -- as published: "1:50.800" q1 TEXT, q2 TEXT, q3 TEXT, gap TEXT, interval TEXT, laps INTEGER, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, UNIQUE (race_id, driver_id) ); -- Practice, one row per driver per session (LV-03). The session's -- classification as F1DB publishes it: the order of best laps, the lap, the -- gap and interval to it, and laps run. One lap per driver - the fastest - -- so this is a classification, not the lap timing that stays FOM's (the four -- empty tables, docs/TIMING-ARCHITECTURE.md). `session` is the `sessions` -- table's own word, so a 2026 row is held to that weekend's timetable; fp4 -- is the fourth session of 2004-2005 and has no timetable row, and so have -- pre_qualifying (1977-1992) and warm_up (1984-2003), the sessions a weekend -- no longer runs. A driver who set no time has an empty `time` and the -- place F1DB gives them. CREATE TABLE practice ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), session TEXT NOT NULL CHECK (session IN ('pre_qualifying', 'fp1', 'fp2', 'fp3', 'fp4', 'warm_up')), driver_id TEXT NOT NULL REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), position INTEGER, -- NULL where not classified position_text TEXT, -- "1", or NC driver_number INTEGER, time TEXT, -- best lap, as published: "1:45.387" gap TEXT, interval TEXT, laps INTEGER, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, UNIQUE (race_id, session, driver_id) ); CREATE INDEX idx_practice_race ON practice(race_id, session); CREATE INDEX idx_practice_driver ON practice(driver_id); -- The session that sets a sprint's grid (LV-03): the sprint shootout in 2023, -- sprint qualifying since. Knockout-shaped like qualifying - three segments -- and no single time - and kept apart from it for the reason qualifying is -- kept apart from the race: it is its own session with its own order. CREATE TABLE sprint_qualifying ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT NOT NULL REFERENCES drivers(id), constructor_id TEXT REFERENCES constructors(id), position INTEGER, position_text TEXT, driver_number INTEGER, q1 TEXT, q2 TEXT, q3 TEXT, gap TEXT, interval TEXT, laps INTEGER, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT, UNIQUE (race_id, driver_id) ); CREATE TABLE regulation_changes ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL, category TEXT NOT NULL CHECK (category IN ('technical', 'sporting', 'safety', 'financial', 'format')), title TEXT NOT NULL, detail TEXT, impact TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence), source TEXT ); CREATE TABLE technical_innovations ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL, innovation TEXT NOT NULL, originator TEXT, description TEXT, legacy TEXT, banned_year INTEGER, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); CREATE TABLE safety_milestones ( id INTEGER PRIMARY KEY, year INTEGER NOT NULL, milestone TEXT NOT NULL, trigger_event TEXT, description TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); CREATE TABLE tyre_suppliers ( id INTEGER PRIMARY KEY, supplier TEXT NOT NULL, from_year INTEGER, to_year INTEGER, exclusive INTEGER DEFAULT 0, notes TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); -- `scoring` is the rule as a sentence; `win_points` and `fastest_lap_points` -- are the two figures of it a query can add up. known_gaps #12 records that -- the clinching round and the title permutations are computable from -- `standings` and this table "once the per-season maximum-points rule is -- written down" - these two columns are that rule, and the season page's -- "who can still win" is the first thing to read them. They are authored in -- data/technical.py beside the prose, not parsed out of it at read time, and -- verify.py holds each to the sentence it sits next to so the two cannot -- disagree. A sprint row carries its own win value here too. -- -- `scale` is the whole of `scoring`'s rule as a JSON array - the points for -- first, second, third, down to the last place paid (DA-08). Its length is -- how far down the order the system paid, and the build reads it to write 0 -- into race_entries.points and sprint_results.points for a classified -- finisher below that line. -- -- Two period tables share these rows, and `session` says which a row is in -- (DA-18): 'race' for the Grand Prix, 'sprint' for the sprint. Within one -- session no two periods overlap, which verify.py checks season by season; -- across the two they do, so a query for one season's system names the -- session it wants. Until DA-18 the only mark of a sprint row was a -- 'SPRINT: ' prefix inside `scoring`. -- -- `fastest_lap` and `dropped_scores` are never NULL: where a system had no -- such rule they say so, as 'No point' and 'Every result counts', because -- that there was none is established and NULL here means not established. CREATE TABLE points_systems ( id INTEGER PRIMARY KEY, session TEXT NOT NULL CHECK (session IN ('race', 'sprint')), from_year INTEGER NOT NULL, to_year INTEGER, scoring TEXT NOT NULL, scale TEXT NOT NULL, -- JSON array, e.g. [8,6,4,3,2] win_points INTEGER NOT NULL, -- points for a win, e.g. 25 fastest_lap TEXT NOT NULL, -- the rule, or 'No point' fastest_lap_points INTEGER NOT NULL DEFAULT 0, dropped_scores TEXT NOT NULL, -- the rule, or 'Every result counts' notes TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence), UNIQUE (session, from_year) ); -- How each grid was set, period by period (WK-01), in the shape DA-18 gave -- points_systems: `session` is whose grid - 'race' for the Grand Prix, -- 'sprint' for the sprint - and within one session no two periods overlap. -- The format changed mid-season twice, so a period begins at `from_round` of -- `from_year` and runs to the end of `to_year` or to the next period's start; -- NULL `to_year` is the period still running. `sessions` counts the timed -- sessions whose times went into the grid, not the sessions run (a knockout -- is one session in three parts). `rule_107` is 1 where the FIA Sporting -- Regulations set a 107% limit on the first part's times for that grid and 0 -- where the issue read sets none; what the limit cost a driver changed in -- 2023, when the bar on starting the race went and an unclassified driver's -- participation was left to the stewards, and regulation_limits holds that. -- NULL is not established: the -- column is read only from the FIA's text (known_gaps #19). Nothing before -- 1996: known_gaps #18 holds that span. CREATE TABLE qualifying_formats ( id INTEGER PRIMARY KEY, session TEXT NOT NULL CHECK (session IN ('race', 'sprint')), from_year INTEGER NOT NULL, from_round INTEGER NOT NULL DEFAULT 1 CHECK (from_round >= 1), to_year INTEGER, format TEXT NOT NULL CHECK (format IN ('single session', 'single lap', 'single lap, aggregate', 'knockout', 'knockout, elimination')), sessions INTEGER NOT NULL CHECK (sessions >= 1), rule_107 INTEGER CHECK (rule_107 IN (0, 1)), note TEXT NOT NULL, confidence TEXT NOT NULL REFERENCES provenance(confidence), source TEXT NOT NULL, UNIQUE (session, from_year, from_round) ); -- DERIVED, not authored. Until v2.23 this held thirty rows typed from general -- knowledge, with twenty-four spellings of `as_of` and a Hamilton win count -- one behind the `drivers.wins` the same database computed. Every row is now -- one query in build.py (derive_records) over the tables the site's -- leaderboards read, so the two cannot disagree, and verify.py recomputes a -- sample by a different route. `detail` says how each was derived: which -- tables, the rule, what was excluded and why. A tie holds every holder in -- `holder`, comma-separated, with `holder_id` NULL - one is never picked. CREATE TABLE records ( id INTEGER PRIMARY KEY, key TEXT NOT NULL UNIQUE, -- stable slug, e.g. 'most-wins' category TEXT NOT NULL, -- drivers | constructors | races -- The statistic a record is a form of, which is what /records groups it -- under (WK-08): 'Wins' holds a career's wins, a season's and a win rate -- alike. Not who holds it - that is `category`. Declared per key in -- build.py (RECORD_FAMILIES), which refuses a record in no family. family TEXT NOT NULL, -- 1 for the few records /records leads with (RECORD_HEADLINES); every -- other record is shown under its family instead. headline INTEGER NOT NULL DEFAULT 0 CHECK (headline IN (0, 1)), record TEXT NOT NULL, holder TEXT NOT NULL, -- name(s), for display -- The row `holder_id` joins to. `records` is the one place a holder can -- be a driver, a team, a race or a circuit, so the table has to be said. holder_table TEXT NOT NULL CHECK (holder_table IN ('drivers', 'constructors', 'races', 'circuits')), holder_id TEXT, -- NULL when the record is shared value TEXT NOT NULL, -- for display: '18 years, 228 days, ...' value_num REAL NOT NULL, -- the figure, comparable: 6802 unit TEXT NOT NULL, -- what value_num counts: 'days' detail TEXT NOT NULL, as_of TEXT NOT NULL, -- ISO date of the last completed race -- 'reference' because that is what the race records the figures are -- computed from carry; a derivation cannot outrank its inputs. confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence) ); CREATE TABLE eras ( id INTEGER PRIMARY KEY, from_year INTEGER NOT NULL, to_year INTEGER, era_name TEXT NOT NULL, summary TEXT, dominant_teams TEXT, defining_features TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); CREATE TABLE glossary ( term TEXT PRIMARY KEY, category TEXT, definition TEXT NOT NULL, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); CREATE TABLE governance ( id INTEGER PRIMARY KEY, year INTEGER, event TEXT NOT NULL, detail TEXT, significance TEXT, confidence TEXT NOT NULL DEFAULT 'high' REFERENCES provenance(confidence) ); -- --------------------------------------------------------- indexes CREATE INDEX idx_seasons_champion ON seasons(drivers_champion); CREATE INDEX idx_seasons_cchampion ON seasons(constructors_champion); CREATE INDEX idx_sentries_year ON season_entries(year); CREATE INDEX idx_sentries_driver ON season_entries(driver_id); CREATE INDEX idx_sentries_constructor ON season_entries(constructor_id); CREATE INDEX idx_standings_year ON standings(year, table_type); CREATE INDEX idx_regs_year ON regulation_changes(year); CREATE INDEX idx_regs_category ON regulation_changes(category); CREATE INDEX idx_innov_year ON technical_innovations(year); CREATE INDEX idx_safety_year ON safety_milestones(year); CREATE INDEX idx_layouts_circuit ON circuit_layouts(circuit_id); CREATE INDEX idx_lineage_chain ON constructor_lineage(chain_id, sequence); CREATE INDEX idx_drivers_titles ON drivers(titles); CREATE INDEX idx_drivers_wins ON drivers(wins); -- ----------------------------------------------------------- views CREATE VIEW v_champions AS SELECT s.year, d.full_name AS champion, d.nationality AS nationality, c.name AS team, s.champion_points AS points, s.champion_wins AS wins, ru.full_name AS runner_up, s.runner_up_points AS runner_up_points, s.margin AS margin, cc.name AS constructors_champion, s.rounds AS rounds FROM seasons s LEFT JOIN drivers d ON d.id = s.drivers_champion LEFT JOIN drivers ru ON ru.id = s.runner_up LEFT JOIN constructors c ON c.id = s.champion_team LEFT JOIN constructors cc ON cc.id = s.constructors_champion ORDER BY s.year; CREATE VIEW v_title_count AS -- d.id leads, as v_wins_by_constructor's c.id does: a row of this view is a -- driver, and without the key nothing reading it can link the row - joining -- back on full_name is not safe, since names are not unique (PD-27). SELECT d.id, d.full_name, d.nationality, d.titles, d.title_years, d.wins, d.poles FROM drivers d WHERE d.titles > 0 ORDER BY d.titles DESC, d.wins DESC; CREATE VIEW v_constructor_titles AS SELECT name, country, constructors_titles, drivers_titles, wins, first_entry, last_entry, title_years FROM constructors WHERE constructors_titles > 0 OR drivers_titles > 0 ORDER BY constructors_titles DESC, wins DESC; -- "Current" is read, not typed (CR-07). The anchor is meta.current_season, -- which build.py writes from the one constant in data/current.py and verify.py -- holds to the season the race records actually show. NOT MAX(season_entries -- .year): that is the same value today only because next season's entry list -- has never been carried, and the register already carries next season's -- calendar, so the day a 2027 grid is announced MAX() would move to it while -- the season in progress had not. CREATE VIEW v_current_grid AS SELECT e.car_number, d.full_name AS driver, d.nationality_code, c.name AS team, e.car, e.power_unit, e.role FROM season_entries e LEFT JOIN drivers d ON d.id = e.driver_id LEFT JOIN constructors c ON c.id = e.constructor_id WHERE e.year = (SELECT CAST(value AS INTEGER) FROM meta WHERE key = 'current_season') ORDER BY e.role, c.name, e.car_number; CREATE VIEW v_season_timeline AS SELECT s.year, s.rounds, d.full_name AS champion, cc.name AS constructors_champion, s.engine_formula, s.tyre_suppliers, s.notes FROM seasons s LEFT JOIN drivers d ON d.id = s.drivers_champion LEFT JOIN constructors cc ON cc.id = s.constructors_champion ORDER BY s.year; CREATE VIEW v_unverified AS SELECT 'drivers' AS tbl, id AS key, full_name AS label, confidence FROM drivers WHERE confidence IN ('medium','unverified') UNION ALL SELECT 'constructors', id, name, confidence FROM constructors WHERE confidence IN ('medium','unverified') UNION ALL SELECT 'circuits', id, name, confidence FROM circuits WHERE confidence IN ('medium','unverified') UNION ALL SELECT 'seasons', CAST(year AS TEXT), CAST(year AS TEXT), confidence FROM seasons WHERE confidence IN ('medium','unverified'); -- ------------------------------------------- race-result views (v2.1) -- Known gaps in the harvest, and open discrepancies between a hand-entered -- career figure and the figure derived from the race records. Recorded -- rather than silently reconciled: where two sources disagree and neither -- can be checked against an official source, the disagreement IS the fact. -- --------------------------------------------------------------- timing -- Per-race timing, one row per race. Times are stored as text exactly as -- published ("1:24.303") and as seconds for arithmetic, because the published -- form is the citable one and the numeric form is the useful one. Both are -- written from the same harvested string so they cannot disagree. CREATE TABLE race_timing ( race_id INTEGER PRIMARY KEY REFERENCES races(id), pole_time TEXT, pole_seconds REAL, fastest_lap_time TEXT, fastest_lap_seconds REAL, fastest_lap_number INTEGER, winner_time TEXT, -- total race time winner_seconds REAL, margin TEXT, -- gap to second, as published margin_seconds REAL, -- NULL when the gap is in laps margin_laps INTEGER, -- set instead when lapped laps INTEGER, -- race distance in laps distance_km REAL, confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT ); -- Per-lap data. Empty in the distributed database: F1 does not publish lap -- times before 2018 in any retrievable form, and the 2018- data comes from -- the live timing API through FastF1, which needs network access this build -- environment does not have. tools/fastf1_load.py populates these four -- tables; the schema is here so that a database built with them and one -- built without are the same shape. CREATE TABLE laps ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT REFERENCES drivers(id), driver_code TEXT, -- VER, HAM where the source has one -- What the SOURCE keys this lap on, and the reason the two can coexist. -- FastF1 identifies a driver by three-letter code and may not resolve a -- register id; Jolpica resolves to a register id and mostly has no -- abbreviation before the 2000s. Neither column is reliably present for -- both, so each loader writes its own key here and the uniqueness is -- (race, source, key, lap) - which lets both sources hold the same race -- side by side, and verify.py compare them. driver_key TEXT NOT NULL, lap_number INTEGER NOT NULL, position INTEGER, lap_seconds REAL, sector1_seconds REAL, sector2_seconds REAL, sector3_seconds REAL, speed_trap_kph REAL, compound TEXT, -- SOFT | MEDIUM | HARD | INTER | WET tyre_life INTEGER, -- laps on this set fresh_tyre INTEGER, stint INTEGER, is_personal_best INTEGER, deleted INTEGER, -- lap time deleted by the stewards deleted_reason TEXT, track_status TEXT, -- yellow, SC, VSC, red as flagged is_fastest_lap INTEGER, -- the driver's quickest of the race source TEXT NOT NULL DEFAULT 'fastf1', UNIQUE (race_id, source, driver_key, lap_number) ); CREATE TABLE stints ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT REFERENCES drivers(id), driver_code TEXT, stint INTEGER NOT NULL, compound TEXT, lap_start INTEGER, lap_end INTEGER, laps_run INTEGER, source TEXT NOT NULL DEFAULT 'fastf1', UNIQUE (race_id, driver_code, stint) ); CREATE TABLE pit_stops ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), driver_id TEXT REFERENCES drivers(id), driver_code TEXT, driver_key TEXT NOT NULL, -- as laps.driver_key stop_number INTEGER, lap_number INTEGER, -- The timing sources publish PIT LANE time - entry to exit, around -- 20-30 s - not the two or three seconds the car is stationary. Storing -- the one we have in the column that names it, and leaving the other -- NULL, is the difference between a figure and a wrong figure. In the -- DISTRIBUTED database both are NULL on every row: the only source that -- may be passed on, F1DB, publishes the lap and the stop order and no -- duration at all. Strategy is derivable from it; duration is not. stationary_seconds REAL, pit_lane_seconds REAL, source TEXT NOT NULL DEFAULT 'fastf1', UNIQUE (race_id, source, driver_key, stop_number) ); -- ------------------------------------------------------ radio and control -- Race control messages are published as text and are fully structured: -- flags, safety cars, investigations, penalties, deleted lap times. CREATE TABLE race_control_messages ( id INTEGER PRIMARY KEY, race_id INTEGER NOT NULL REFERENCES races(id), session TEXT NOT NULL DEFAULT 'race', -- race | qualifying | sprint utc_time TEXT, lap_number INTEGER, category TEXT, -- Flag | SafetyCar | Drs | Other flag TEXT, -- GREEN | YELLOW | DOUBLE YELLOW | RED ... scope TEXT, -- Track | Sector | Driver sector INTEGER, driver_number INTEGER, message TEXT NOT NULL, source TEXT NOT NULL DEFAULT 'fastf1', UNIQUE (race_id, session, utc_time, message) ); -- Team radio. F1 publishes the AUDIO, not transcripts, so `transcript` is -- filled only where one has been made: tools/fastf1_load.py can transcribe -- with a local Whisper model if one is installed, and otherwise records the -- clip and its URL with transcript NULL. `notable` marks the curated set of -- historically significant exchanges, which are transcribed from broadcast -- and are the only rows that exist for seasons before 2018. CREATE TABLE team_radio ( id INTEGER PRIMARY KEY, race_id INTEGER REFERENCES races(id), driver_id TEXT REFERENCES drivers(id), driver_code TEXT, utc_time TEXT, lap_number INTEGER, -- who spoke and to whom, as the source attributes it: "Rob Smedley -- (race engineer) to Felipe Massa". Prose, not a vocabulary - the -- controlled part is `channel`. Up to v2.25 the channel was appended to -- this column in brackets (DA-13). speaker TEXT, channel TEXT CHECK (channel IN ('pit-to-car', 'car-to-pit', 'team to race director', 'race director to team')), transcript TEXT, audio_url TEXT, notable INTEGER NOT NULL DEFAULT 0, context TEXT, -- why this one matters confidence TEXT NOT NULL DEFAULT 'reference' REFERENCES provenance(confidence), source TEXT NOT NULL DEFAULT 'fastf1' ); CREATE INDEX idx_laps_race ON laps(race_id, lap_number); CREATE INDEX idx_laps_driver ON laps(race_id, driver_code); CREATE INDEX idx_stints_race ON stints(race_id); CREATE INDEX idx_pits_race ON pit_stops(race_id); CREATE INDEX idx_rcm_race ON race_control_messages(race_id); CREATE INDEX idx_radio_race ON team_radio(race_id); CREATE INDEX idx_radio_notable ON team_radio(notable); -- What the database does not hold, on the record. A row is never deleted: -- a gap that closes is marked closed and its resolution says when and how, -- so the register keeps its history. `state` separates three things that -- used to share one list and one count - -- open a fact nobody holds yet; these are the gaps the site counts -- closed filled since, and kept so the closure is on record -- position a deliberate absence (no lap timing, no historic centrelines, -- a race in which no lap was set) - the right state, not a gap -- `reader` is the one-paragraph version a reader of the site is shown; -- `description` and `resolution` are the maintainer's note, kept whole. -- `key` is the gap's name to cite it by, written beside it in -- data/harvest.py and never reused (DA-24): `id` is numbered by hand and -- has held, but the identifier policy promises no integer id outside the -- tables it names, and a correction that names a gap needs something the -- policy does promise. It is the natural key data/current.py ID_STABILITY -- publishes, so verify.py holds it to one row. CREATE TABLE known_gaps ( id INTEGER PRIMARY KEY, key TEXT NOT NULL UNIQUE, -- e.g. 'cost-cap-indexation' field TEXT NOT NULL, -- which column is incomplete area TEXT NOT NULL, state TEXT NOT NULL CHECK (state IN ('open', 'closed', 'position')), reader TEXT NOT NULL, -- what a reader is shown description TEXT NOT NULL, -- the maintainer's note -- How many races the gap touches, MEASURED: build.py runs the gap's query -- in data/harvest.py GAP_RACES and verify.py re-runs it. NULL where the -- gap is not counted in races, or no query here can yet say which races -- it touches - never 0 for "not counted", which reads as "none" (DA-16). races_affected INTEGER, resolution TEXT -- what would close it, or what did ); -- The gaps the site counts. The homepage, /data and the README figure all -- read this view, so they cannot disagree with each other or with the table. CREATE VIEW v_open_gaps AS SELECT * FROM known_gaps WHERE state = 'open' ORDER BY id; -- Where two sources disagree, both readings, on the record (DA-09). -- -- `tbl`, `row_key` and `field` say which value the disagreement is about: -- column `field` of the row of `tbl` whose key is `row_key`, spelt the way -- `claims` spells it - the primary key where that is not a bare integer id, -- or else the natural key data/current.py ID_STABILITY publishes, its -- columns joined by '|' in key order: 'mclaren-m23', 'ferrari-500|1952'. -- A leading part of a key names every row it begins - '57' is every entry -- in race 57, '2026|constructors|14' the constructors' table after round 14 -- - because some disagreements are about a race's classification rather -- than one driver's line of it. NULL names no single row: a figure a whole -- grid quoted, withdrawn from every chassis that quoted it, which `subject` -- describes. verify.py holds `field` to a column of `tbl` and a `row_key` -- to at least one of its rows. -- -- `kind` says which two readings are compared, one word per way the build -- files a row, because one value can carry two disagreements: a driver's -- corrected figure and, later, the lag of the external figure behind the -- races run since. -- -- `key` is the row's name to cite it by, `tbl.field[row_key]:kind` - or, -- where `row_key` is NULL, `tbl.field[=stored_value]:kind` - so it follows -- from the fact and not from the order the build filed it in. It is the -- natural key ID_STABILITY publishes; the columns cannot be, because their -- NULL would make one key of every grid-wide row. -- -- `subject` is the display name, and is what a race, driver or constructor -- page finds its rows by. `status` is one of three words: open (nothing has -- settled it), resolved (settled - the record was changed or the claim not -- applied) or explained (settled without either side being wrong). How is -- `status_note`, a short phrase: 'withdrawn', 'external figure is older'. -- A value that is absent is NULL, never the string 'NULL'. CREATE TABLE discrepancies ( id INTEGER PRIMARY KEY, key TEXT NOT NULL UNIQUE, kind TEXT NOT NULL CHECK (kind IN ( 'regulation', -- a figure that is the rules, withdrawn 'shared-fastest-lap', -- a share the season table dropped 'f1db-fastest-lap', -- the harvest's setter against F1DB's 'reclassification', -- a race's order, the cause of a points split 'running-table', -- formula1.com's standings against F1DB's 'final-table', -- the same, for a final table 'repeated-round', -- an F1DB table that did not move 'correction', -- an external figure found wrong 'external-figure', -- an external figure against the records 'f1db-career-total', -- a typed career figure against F1DB's total 'stored-total', -- a stored entries, starts or points total against the records 'career-span', -- the register's span against the records' 'car-season', -- CAR_SEASONS against the entry lists 'car-chassis', -- a car's figure against its one chassis's 'session-sheet', -- F1DB's race result against its own session sheets 'race-classification', -- F1DB's race result against another source's 'jolpica-result')), -- tools/ergast_load.py, local only subject TEXT NOT NULL, tbl TEXT NOT NULL, row_key TEXT, field TEXT NOT NULL, -- a column of `tbl` stored_value TEXT, derived_value TEXT, assessment TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'resolved', 'explained')), status_note TEXT ); -- Shared poles and fastest laps: one row per driver credited. Used because -- several early races had two or more drivers set identical times, and the -- official record credits every one of them. -- --------------------------------------- pole / fastest lap views (v2.2) -- Pole + win + fastest lap in the same race -- ===================================================================== -- Race views (v2.4) -- -- The first two reproduce the pre-v2.4 `race_results` and `race_credits` -- tables exactly, so anything written against them keeps working. New -- queries should go to races / race_entries. -- ===================================================================== CREATE VIEW race_results AS SELECT r.id, r.year, r.round, r.name_used AS gp_name, r.gp_id, r.circuit_id, w.driver_id AS winner_id, wd.full_name AS winner, cw.driver_id AS co_winner_id, w.constructor_id AS constructor_id, cn.name AS constructor, w.entrant AS entrant, pd.full_name AS pole, p.driver_id AS pole_id, fd.full_name AS fastest_lap, f.driver_id AS fastest_lap_id, r.note, r.confidence, r.source FROM races r LEFT JOIN race_entries w ON w.race_id = r.id AND w.finish_position = 1 AND w.id = (SELECT MIN(id) FROM race_entries WHERE race_id = r.id AND finish_position = 1) LEFT JOIN race_entries cw ON cw.race_id = r.id AND cw.finish_position = 1 AND cw.id != w.id LEFT JOIN race_entries p ON p.race_id = r.id AND p.pole = 1 LEFT JOIN race_entries f ON f.race_id = r.id AND f.fastest_lap = 1 AND f.id = (SELECT MIN(id) FROM race_entries WHERE race_id = r.id AND fastest_lap = 1) LEFT JOIN drivers wd ON wd.id = w.driver_id LEFT JOIN drivers pd ON pd.id = p.driver_id LEFT JOIN drivers fd ON fd.id = f.driver_id LEFT JOIN constructors cn ON cn.id = w.constructor_id; CREATE VIEW race_credits AS SELECT r.year, r.round, 'pole' AS credit_type, e.driver_id, 1 AS shared_with FROM races r JOIN race_entries e ON e.race_id = r.id WHERE e.pole = 1 UNION ALL SELECT r.year, r.round, 'fastest_lap', e.driver_id, COALESCE(e.fastest_lap_shared, 1) FROM races r JOIN race_entries e ON e.race_id = r.id WHERE e.fastest_lap = 1; CREATE VIEW v_race_winners AS SELECT r.year, r.round, r.name_used AS gp_name, g.name AS grand_prix, ci.name AS circuit, d.full_name AS winner, d2.full_name AS co_winner, c.name AS constructor, e.entrant, r.confidence FROM races r JOIN race_entries e ON e.race_id = r.id AND e.finish_position = 1 AND e.id = (SELECT MIN(id) FROM race_entries WHERE race_id = r.id AND finish_position = 1) LEFT JOIN race_entries e2 ON e2.race_id = r.id AND e2.finish_position = 1 AND e2.id != e.id LEFT JOIN drivers d ON d.id = e.driver_id LEFT JOIN drivers d2 ON d2.id = e2.driver_id LEFT JOIN constructors c ON c.id = e.constructor_id LEFT JOIN grands_prix g ON g.id = r.gp_id LEFT JOIN circuits ci ON ci.id = r.circuit_id ORDER BY r.year, r.round; CREATE VIEW v_wins_by_driver AS SELECT d.full_name, d.nationality, COUNT(*) AS wins, MIN(r.year) AS first_win, MAX(r.year) AS last_win, d.titles FROM race_entries e JOIN races r ON r.id = e.race_id JOIN drivers d ON d.id = e.driver_id WHERE e.finish_position = 1 GROUP BY d.id ORDER BY wins DESC, first_win; CREATE VIEW v_wins_by_constructor AS -- c.id leads, as v_circuits' does and for the same reason: a row of this -- view is a constructor, and without its key nothing reading the view can -- link the row or colour it - joining back on the name is not safe, two -- teams have shared one. SELECT c.id, c.name, c.country, COUNT(DISTINCT e.race_id) AS wins, MIN(r.year) AS first_win, MAX(r.year) AS last_win, c.constructors_titles FROM race_entries e JOIN races r ON r.id = e.race_id JOIN constructors c ON c.id = e.constructor_id WHERE e.finish_position = 1 GROUP BY c.id ORDER BY wins DESC; CREATE VIEW v_wins_by_decade AS -- A row is a decade and a driver, so the driver's key is driver_id, as the -- other views keyed on something other than their own row name it (PD-27). SELECT (r.year/10)*10 AS decade, d.id AS driver_id, d.full_name, COUNT(*) AS wins FROM race_entries e JOIN races r ON r.id = e.race_id JOIN drivers d ON d.id = e.driver_id WHERE e.finish_position = 1 GROUP BY decade, d.id ORDER BY decade, wins DESC; CREATE VIEW v_poles_by_driver AS SELECT d.full_name, d.nationality, COUNT(*) AS poles, MIN(r.year) AS first_pole, MAX(r.year) AS last_pole FROM race_entries e JOIN races r ON r.id = e.race_id JOIN drivers d ON d.id = e.driver_id WHERE e.pole = 1 GROUP BY d.id ORDER BY poles DESC, first_pole; CREATE VIEW v_fastest_laps_by_driver AS SELECT d.full_name, d.nationality, COUNT(*) AS fastest_laps, MIN(r.year) AS first, MAX(r.year) AS last, SUM(CASE WHEN e.fastest_lap_shared > 1 THEN 1 ELSE 0 END) AS shared FROM race_entries e JOIN races r ON r.id = e.race_id JOIN drivers d ON d.id = e.driver_id WHERE e.fastest_lap = 1 GROUP BY d.id ORDER BY fastest_laps DESC; CREATE VIEW v_grand_slams AS SELECT r.year, r.round, r.name_used AS gp_name, d.full_name AS driver, c.name AS constructor FROM races r JOIN race_entries e ON e.race_id = r.id JOIN drivers d ON d.id = e.driver_id LEFT JOIN constructors c ON c.id = e.constructor_id WHERE e.pole = 1 AND e.finish_position = 1 AND e.fastest_lap = 1 ORDER BY r.year, r.round; CREATE VIEW v_pole_to_win AS SELECT r.year, COUNT(*) AS races, SUM(CASE WHEN EXISTS (SELECT 1 FROM race_entries e WHERE e.race_id = r.id AND e.pole = 1 AND e.finish_position = 1) THEN 1 ELSE 0 END) AS pole_converted FROM races r WHERE r.status = 'completed' GROUP BY r.year ORDER BY r.year; -- One row per entity in a season's FINAL table - the question everyone asks -- of standings, and the one the raw table answers wrongly. -- -- A season's table is its basis = 'final' rows once it has finished, and -- until then each source's running table after the last round that source -- has counted. Neither is one row per entity, for two different reasons and -- only one of them is a duplicate: -- -- Two sources describing one season. 2026 carries a formula1.com snapshot -- (team, no engine) and F1DB's running table (engine, position, more -- rounds) for every driver and team. Rendered naively, everyone appears -- twice, and the compat export shipped exactly that for seven releases. -- -- One source asserting two entries. Force India was excluded from the 2018 -- constructors' championship on 0 points and its successor scored 52 under -- the same id; Cooper contested 1960 with three engines. Those rows all -- belong in the table, and collapsing them would delete a fact. -- -- The source tells them apart, by construction: two rows from DIFFERENT -- sources are two descriptions of one thing, two rows from ONE source are two -- things. So per entity the view keeps every row from one source - the one -- whose table has counted the most rounds; formula1.com where both stand -- after the same round - and fills position, position_text, engine_id and -- team from the other source where the kept row lacks them. Same columns as -- the table, so a consumer swaps the name and nothing else. For a finished -- season the sources agree and this is the identity. -- -- The freshest row, not the largest. The rule was "the higher total", on the -- reasoning that points only accumulate - which holds only while both sources -- stand after the same round. In 2026 the official snapshot stood after -- round 12 and F1DB after round 14, and the larger figure kept Gasly and -- Alpine on the round-12 table beside every other row at round 14 (AF-35). -- How many rounds a row has counted is its after_round, which every row -- carries (DA-01); it used to be read out of the retired as_of's prose. -- -- The fill reads only an unambiguous row. Where the other source asserts two -- entries for the entity - 2018 Force India's shape, arriving in a season -- with two sources - there is no telling which of the two a kept row's blank -- belongs to, and the lowest id would have given both kept rows the first -- entry's position and engine, so one entry would read as the other. The -- blank stays blank: NULL is "not established", which is true. -- -- Written for the two sources that exist: formula1.com and F1DB. The tie-break -- names one of them, and the fill takes the one row of "the other", so a -- third source would need this revisited - verify.py's checks on which row -- survives and what it was filled from are what would say so. CREATE VIEW v_standings_final AS WITH season AS ( SELECT year, table_type, MAX(basis = 'final') AS finished FROM standings GROUP BY year, table_type), latest AS ( SELECT year, table_type, source, MAX(after_round) AS after_round FROM standings WHERE basis = 'running' GROUP BY year, table_type, source), final AS ( SELECT s.* FROM standings s JOIN season n ON n.year = s.year AND n.table_type = s.table_type LEFT JOIN latest l ON l.year = s.year AND l.table_type = s.table_type AND l.source = s.source WHERE CASE WHEN n.finished THEN s.basis = 'final' ELSE s.after_round = l.after_round END), ranked AS ( SELECT year, table_type, driver_id, constructor_id, source, ROW_NUMBER() OVER ( PARTITION BY year, table_type, driver_id, constructor_id ORDER BY MAX(after_round) DESC, CASE WHEN source LIKE '%formula1.com%' THEN 0 ELSE 1 END) AS rank FROM final GROUP BY year, table_type, driver_id, constructor_id, source) SELECT f.id, f.year, f.table_type, COALESCE(f.position, o.position) AS position, COALESCE(f.position_text, o.position_text) AS position_text, f.entity, f.driver_id, f.constructor_id, COALESCE(f.engine_id, o.engine_id) AS engine_id, COALESCE(f.team, o.team) AS team, f.points, f.after_round, f.basis, f.snapshot_date, f.confidence, f.source FROM final f JOIN ranked k ON k.year = f.year AND k.table_type = f.table_type AND k.driver_id IS f.driver_id AND k.constructor_id IS f.constructor_id AND k.source = f.source AND k.rank = 1 LEFT JOIN final o ON o.id = ( SELECT CASE WHEN COUNT(*) = 1 THEN MIN(x.id) END FROM final x WHERE x.year = f.year AND x.table_type = f.table_type AND x.driver_id IS f.driver_id AND x.constructor_id IS f.constructor_id AND x.source <> f.source); -- One row per race entry, with the rest of the driver's weekend beside it: -- where they qualified, where they started, where they finished in the -- sprint, where they finished in the Grand Prix, and what each paid. The -- first query a stranger writes of this database, which until DA-14 had to -- be written as a four-way join by somebody who knew the four tables exist. -- -- The grain is race_entries', exactly: qualifying and sprint_results are -- each one row per driver per race, so joining them on (race_id, driver_id) -- adds columns and never rows, and verify.py checks that it stays so. What -- the grain costs, said plainly: -- -- A qualifying row with no race entry is not here. Two exist - the HRTs -- that missed the 107 per cent limit at Melbourne in 2011, which F1DB's -- qualifying holds and its classification omits (known_gaps) - and a car -- that did not take the start of the race is not in its classification. -- -- A driver who drove two cars in one Grand Prix keeps one result, because -- race_entries does (known_gaps: one row per driver per race, normal -- before 1965, the 1955 Argentine Grand Prix in particular). -- -- The three positions are three different facts and none is derived from -- another: qualifying_position is who was quickest, grid is where the car -- started and pole is who was credited with pole - see WHAT 'POLE' MEANS -- HERE, in schema.sql above races. sprint_position is NULL for a driver with -- no sprint result, which is every entry before 2021. -- -- weekend_points is the Grand Prix's points plus the sprint's, NULL where -- either is: NULL is "not established", and a total that quietly read it as -- nought would establish it. No sprint result adds nothing, because none -- was paid; a sprint result whose points are NULL is not established. -- -- The order is the race page's: classified finishers in order, then the -- rest by laps completed. CREATE VIEW v_race_classification AS SELECT r.id AS race_id, r.year, r.round, r.name_used AS gp_name, r.circuit_id, e.id AS entry_id, e.driver_id, d.full_name AS driver, e.constructor_id, c.name AS constructor, e.entrant, e.car_id, q.position AS qualifying_position, q.position_text AS qualifying_position_text, e.grid, e.grid_text, e.pole, s.finish_position AS sprint_position, s.position_text AS sprint_position_text, s.points AS sprint_points, e.finish_position, e.position_text, e.status, e.laps_completed, e.classified, e.shared_drive, e.fastest_lap, e.fastest_lap_shared, e.points, e.points + CASE WHEN s.id IS NULL THEN 0 ELSE s.points END AS weekend_points, e.confidence, e.source FROM race_entries e JOIN races r ON r.id = e.race_id LEFT JOIN drivers d ON d.id = e.driver_id LEFT JOIN constructors c ON c.id = e.constructor_id LEFT JOIN qualifying q ON q.race_id = e.race_id AND q.driver_id = e.driver_id LEFT JOIN sprint_results s ON s.race_id = e.race_id AND s.driver_id = e.driver_id ORDER BY r.year, r.round, e.finish_position IS NULL, e.finish_position, e.laps_completed IS NULL, e.laps_completed DESC, e.id; -- A driver's season in points, from the results and from the championship, -- side by side - because they are two figures and a season's points live in -- two tables. Summing race_entries alone gives Verstappen 2023 530; the -- sprints paid him 45 more, in sprint_results, and the championship says -- 575 (DA-17). -- -- race_points the Grand Prix points his entries scored -- sprint_points the sprint points his sprint results scored; 0, not NULL, -- where he held none, because none was paid - which is true -- of every season before 2021 -- points the two together: what the results say he scored -- championship_* his row in v_standings_final's drivers' table -- -- points and championship_points are equal wherever every result counted -- (points_systems.dropped_scores), and verify.py checks it for every finished -- season. Before 1991 they are not, and the difference is not an error: -- only a driver's best results counted, so the championship is the smaller. -- In the season being run the championship stands after -- championship_after_round, and points counts every round the results hold, -- so the two part for as long as one source is a round behind the other. -- -- An entry whose points are NULL - not established - adds nothing to the -- sum, in either table: the championship is the sum of the established -- points (the ineligible entries of the 1950s and 1980s are the NULLs, and -- they scored nothing). A season with no established points at all is NULL, -- not 0, in race_points or sprint_points, and so in points. CREATE VIEW v_driver_season_points AS WITH race AS ( SELECT r.year, e.driver_id, COUNT(*) AS entries, SUM(e.points) AS points FROM race_entries e JOIN races r ON r.id = e.race_id GROUP BY r.year, e.driver_id), sprint AS ( SELECT r.year, s.driver_id, COUNT(*) AS results, SUM(s.points) AS points FROM sprint_results s JOIN races r ON r.id = s.race_id GROUP BY r.year, s.driver_id) SELECT g.year, g.driver_id, d.full_name AS driver, g.entries, g.points AS race_points, CASE WHEN p.results IS NULL THEN 0 ELSE p.points END AS sprint_points, g.points + CASE WHEN p.results IS NULL THEN 0 ELSE p.points END AS points, f.position AS championship_position, f.position_text AS championship_position_text, f.points AS championship_points, f.after_round AS championship_after_round FROM race g LEFT JOIN sprint p ON p.year = g.year AND p.driver_id = g.driver_id LEFT JOIN drivers d ON d.id = g.driver_id LEFT JOIN v_standings_final f ON f.year = g.year AND f.table_type = 'drivers' AND f.driver_id = g.driver_id ORDER BY g.year, f.position IS NULL, f.position, g.points + CASE WHEN p.results IS NULL THEN 0 ELSE p.points END DESC, g.driver_id; CREATE VIEW v_stat_reconciliation AS SELECT d.full_name, d.wins AS derived_wins, d.wins_external, d.poles AS derived_poles, d.poles_external, d.fastest_laps AS derived_fl, d.fastest_laps_external, d.last_season, d.confidence FROM drivers d WHERE d.wins_external IS NOT NULL OR d.poles_external IS NOT NULL ORDER BY d.wins DESC; -- Every event, with how many times it has been held and where CREATE VIEW v_grands_prix AS SELECT g.id, g.name, g.country, COUNT(r.id) AS editions, MIN(r.year) AS first_held, MAX(r.year) AS last_held, COUNT(DISTINCT r.circuit_id) AS circuits_recorded, COUNT(r.circuit_id) || '/' || COUNT(r.id) AS circuit_coverage FROM grands_prix g LEFT JOIN races r ON r.gp_id = g.id GROUP BY g.id ORDER BY editions DESC; -- The calendar is the race table seen as a schedule. It was a separate table -- until v2.4, which meant the 2026 season existed in two places. -- -- `dates` is the display string races.dates held until DA-15, derived now -- from the three dates it stood for, so a reader of this view and -- f1_compat.json sees what it always saw: the weekend where one is stated - -- "27-29 Mar 2026", "30 Oct-01 Nov 2026" - and the ISO race day otherwise. -- It is kept as it was on purpose, for anyone reading the compat export. It -- is no longer the site's string: web/src/lib/format.js raceDates() writes -- the house format (CD-57) - "27–29 Mar 2026", "30 Oct–1 Nov 2026", "9 Mar -- 1997" - and the two are not meant to agree. CREATE VIEW calendar AS SELECT r.id, r.year, r.round, r.name_used AS gp_name, r.gp_id, g.country, ci.locality AS city, r.circuit_id, ci.name AS circuit_name, CASE WHEN r.date_from IS NULL OR r.date_to IS NULL THEN r.date_iso WHEN substr(r.date_from, 1, 7) = substr(r.date_to, 1, 7) THEN substr(r.date_from, 9, 2) || '-' || substr(r.date_to, 9, 2) || ' ' || substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3 * CAST(substr(r.date_to, 6, 2) AS INTEGER) - 2, 3) || ' ' || substr(r.date_to, 1, 4) WHEN substr(r.date_from, 1, 4) = substr(r.date_to, 1, 4) THEN substr(r.date_from, 9, 2) || ' ' || substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3 * CAST(substr(r.date_from, 6, 2) AS INTEGER) - 2, 3) || '-' || substr(r.date_to, 9, 2) || ' ' || substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3 * CAST(substr(r.date_to, 6, 2) AS INTEGER) - 2, 3) || ' ' || substr(r.date_to, 1, 4) ELSE substr(r.date_from, 9, 2) || ' ' || substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3 * CAST(substr(r.date_from, 6, 2) AS INTEGER) - 2, 3) || ' ' || substr(r.date_from, 1, 4) || '-' || substr(r.date_to, 9, 2) || ' ' || substr('JanFebMarAprMayJunJulAugSepOctNovDec', 3 * CAST(substr(r.date_to, 6, 2) AS INTEGER) - 2, 3) || ' ' || substr(r.date_to, 1, 4) END AS dates, r.date_iso, r.date_from, r.date_to, r.sprint, r.status, r.confidence, r.source FROM races r LEFT JOIN grands_prix g ON g.id = r.gp_id LEFT JOIN circuits ci ON ci.id = r.circuit_id ORDER BY r.year, r.round; -- ------------------------------------------------------------- circuits -- Every circuit with its championship record, in one row. CREATE VIEW v_circuits AS SELECT c.id, c.name, c.country, c.locality, c.circuit_type, COUNT(CASE WHEN r.status='completed' THEN 1 END) AS races, COUNT(CASE WHEN r.status!='completed' THEN 1 END) AS scheduled, MIN(CASE WHEN r.status='completed' THEN r.year END) AS first_gp, MAX(CASE WHEN r.status='completed' THEN r.year END) AS last_gp, COUNT(DISTINCT CASE WHEN r.status='completed' THEN r.year END) AS seasons_used, COUNT(DISTINCT CASE WHEN r.status='completed' THEN r.gp_id END) AS events_hosted, (SELECT COUNT(*) FROM circuit_layouts l WHERE l.circuit_id = c.id) AS layouts, c.length_km, c.turns, c.direction FROM circuits c LEFT JOIN races r ON r.circuit_id = c.id GROUP BY c.id ORDER BY races DESC, c.name; -- ------------------------------------------------------------- seasons -- The grid of a season, counted rather than written: who was entered (from -- the race entries - entered, not started: a DNQ is an entry, and no source -- here says who started), which constructors entered, whose engines. The -- Wikipedia infobox states these three for the current season by hand; here -- they hold for every season. Constructors are counted by the F1DB key, which -- every entrant row carries: the curated constructor_id is NULL for the -- Indianapolis 500 builders of 1950-1960, and counting it read 1950 as eight -- constructors when twenty-three entered - the review of #73 caught it. CREATE VIEW v_season_grid AS SELECT s.year, -- NULLIF, not the bare count: a season whose calendar has been -- announced and whose entries nobody has published yet has no grid -- ESTABLISHED, which is not the same claim as a grid of nobody. Every -- season that has run has a non-zero count, so this only ever speaks -- for one that has not. NULLIF((SELECT COUNT(DISTINCT e.driver_id) FROM race_entries e JOIN races r ON r.id = e.race_id WHERE r.year = s.year), 0) AS drivers, NULLIF((SELECT COUNT(DISTINCT se.f1db_constructor_id) FROM season_entrants se WHERE se.year = s.year), 0) AS constructors, NULLIF((SELECT COUNT(DISTINCT se.engine_manufacturer_id) FROM season_entrants se WHERE se.year = s.year AND se.engine_manufacturer_id IS NOT NULL), 0) AS engine_manufacturers, (SELECT COUNT(*) FROM races r WHERE r.year = s.year AND r.status = 'completed') AS races_run FROM seasons s; -- Who has won most often at each circuit. CREATE VIEW v_circuit_winners AS SELECT r.circuit_id, c.name AS circuit, e.driver_id, d.full_name AS driver, COUNT(DISTINCT r.id) AS wins, MIN(r.year) AS first_win, MAX(r.year) AS last_win FROM races r JOIN race_entries e ON e.race_id = r.id AND e.finish_position = 1 JOIN circuits c ON c.id = r.circuit_id JOIN drivers d ON d.id = e.driver_id GROUP BY r.circuit_id, e.driver_id ORDER BY r.circuit_id, wins DESC, driver; -- The same for constructors. CREATE VIEW v_circuit_constructors AS SELECT r.circuit_id, c.name AS circuit, e.constructor_id, t.name AS constructor, COUNT(DISTINCT r.id) AS wins, MIN(r.year) AS first_win, MAX(r.year) AS last_win FROM races r JOIN race_entries e ON e.race_id = r.id AND e.finish_position = 1 JOIN circuits c ON c.id = r.circuit_id JOIN constructors t ON t.id = e.constructor_id GROUP BY r.circuit_id, e.constructor_id ORDER BY r.circuit_id, wins DESC, constructor; -- Countries ranked by how much championship racing they have held. -- Circuits with a traced centreline, and how far the trace sits from the -- length that admitted it. Sorted worst-first: the interesting row is always -- the one closest to the tolerance, not the one that matched exactly. CREATE VIEW v_circuit_geometry AS SELECT g.circuit_id, c.name AS circuit, c.country, COALESCE(g.layout_key, '-') AS layout, g.measured_km, g.published_km, g.delta_pct, g.node_count, g.osm_relation, g.osm_timestamp, g.licence, -- The coordinates themselves. Without them this view describes a -- centreline it cannot draw, which is what it did for one build. g.centreline FROM circuit_geometry g JOIN circuits c ON c.id = g.circuit_id ORDER BY ABS(g.delta_pct) DESC; -- What has a trace and what does not, by whether the circuit is still in use. -- The shape of this is the point: OSM maps the present, so a circuit that -- stopped hosting Grands Prix in 1976 is not missing its geometry, it has -- none to have. CREATE VIEW v_geometry_coverage AS SELECT CASE WHEN c.last_gp IS NULL THEN 'in use' ELSE 'former' END AS status, COUNT(*) AS circuits, SUM(CASE WHEN g.circuit_id IS NOT NULL THEN 1 ELSE 0 END) AS traced, ROUND(100.0 * SUM(CASE WHEN g.circuit_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct FROM circuits c LEFT JOIN circuit_geometry g ON g.circuit_id = c.id GROUP BY status ORDER BY status; -- Every car that can be illustrated, with the credit that must appear beside -- it. A row here is a licence obligation, not a decoration: where -- attribution_required is 1 the artist line is not optional. CREATE VIEW v_car_images AS SELECT DISTINCT ch.car_id, c.full_name AS car, i.article, i.file_name, i.licence, i.licence_url, i.artist, i.credit, i.description_url, i.thumb_url, i.width, i.height, i.name_matches FROM article_images i JOIN chassis ch ON ch.article = i.article JOIN cars c ON c.id = ch.car_id WHERE ch.car_id IS NOT NULL AND i.route = 'article'; -- The images whose file name does not mention the car. NOT a list of wrong -- images - most are correct and simply filed under the driver - but it is -- where a wrong one will be, and it is the only handle there is. The -- article route only: every category-route row needs a person anyway. CREATE VIEW v_images_to_check AS SELECT i.article, i.file_name, i.licence, i.description_url, (SELECT COUNT(*) FROM chassis ch WHERE ch.article = i.article) AS chassis FROM article_images i WHERE i.name_matches = 0 AND i.route = 'article' ORDER BY chassis DESC, i.article; CREATE VIEW v_circuits_by_country AS SELECT c.country, COUNT(DISTINCT c.id) AS circuits, COUNT(r.id) AS races, MIN(r.year) AS first_gp, MAX(r.year) AS last_gp, GROUP_CONCAT(DISTINCT c.name) AS venues FROM circuits c LEFT JOIN races r ON r.circuit_id = c.id AND r.status = 'completed' GROUP BY c.country ORDER BY races DESC; -- Each race with the circuit and the exact configuration in use that year. CREATE VIEW v_race_venues AS SELECT r.id AS race_id, r.year, r.round, r.name_used AS gp_name, r.gp_id, r.circuit_id, c.name AS circuit, c.country, c.locality, l.layout_name, COALESCE(l.length_km, c.length_km) AS length_km, COALESCE(l.turns, c.turns) AS turns, -- Where the circuit has a researched layout timeline the figures are -- those actually raced that year. Otherwise they are the circuit's -- current figures, which may not be what was raced. CASE WHEN l.id IS NOT NULL THEN 'as raced' ELSE 'current layout' END AS figures FROM races r LEFT JOIN circuits c ON c.id = r.circuit_id LEFT JOIN circuit_layouts l ON l.circuit_id = r.circuit_id AND ( (r.layout_key IS NOT NULL AND l.layout_key = r.layout_key) OR (r.layout_key IS NULL AND l.by_year = 1 AND r.year >= l.from_year AND (l.to_year IS NULL OR r.year <= l.to_year))) ORDER BY r.year, r.round; -- Venues no longer in use, most recently dropped first. -- -- Both the count and the cutoff hang off the season in progress rather than a -- typed year (CR-07): "lost" is not on this year's calendar and was not on -- last year's, and "years since" is counted back from this year. The anchor is -- meta.current_season, as v_current_grid uses, and NOT MAX(races.year), which -- becomes the next season's calendar as soon as one is announced. CREATE VIEW v_lost_circuits AS SELECT c.id, c.name, c.country, COUNT(r.id) AS races, MIN(r.year) AS first_gp, MAX(r.year) AS last_gp, (SELECT CAST(value AS INTEGER) FROM meta WHERE key = 'current_season') - MAX(r.year) AS years_since FROM circuits c JOIN races r ON r.circuit_id = c.id WHERE NOT EXISTS (SELECT 1 FROM races s WHERE s.circuit_id = c.id AND s.year >= (SELECT CAST(value AS INTEGER) FROM meta WHERE key = 'current_season') - 1) GROUP BY c.id ORDER BY last_gp DESC; -- ---------------------------------------------------------------- cars -- The register with its derived competition record. CREATE VIEW v_cars AS SELECT c.id, c.full_name AS car, t.name AS constructor, c.from_year, c.to_year, c.engine_name, c.engine_config, c.capacity_cc, c.aspiration, c.power_bhp, c.chassis_type, c.weight_kg, c.races AS recorded_races, c.wins, c.poles, c.fastest_laps, c.drivers_titles, c.constructors_titles, c.landmark, c.designers, c.concept, c.confidence, c.spec_confidence FROM cars c JOIN constructors t ON t.id = c.constructor_id ORDER BY c.wins DESC, c.from_year; -- Every race a car is known to have won, taken pole for or set fastest lap in. CREATE VIEW v_car_races AS SELECT c.id AS car_id, c.full_name AS car, r.year, r.round, r.name_used AS gp_name, ci.name AS circuit, d.full_name AS driver, CASE WHEN e.finish_position = 1 THEN 1 ELSE 0 END AS won, e.pole, e.fastest_lap FROM race_entries e JOIN cars c ON c.id = e.car_id JOIN races r ON r.id = e.race_id JOIN drivers d ON d.id = e.driver_id LEFT JOIN circuits ci ON ci.id = r.circuit_id ORDER BY r.year, r.round; -- How the technology moved: one row per car, in the order the cars appeared. CREATE VIEW v_car_evolution AS SELECT c.from_year, c.full_name AS car, t.name AS constructor, c.chassis_type, c.engine_config, c.aspiration, c.capacity_cc, c.power_bhp, c.weight_kg, c.innovations FROM cars c JOIN constructors t ON t.id = c.constructor_id WHERE c.landmark = 1 ORDER BY c.from_year, c.full_name; -- Design lineages: a car and everything descended from it. CREATE VIEW v_car_lineage AS WITH RECURSIVE chain(root, id, full_name, from_year, depth) AS ( SELECT id, id, full_name, from_year, 0 FROM cars WHERE supersedes_id IS NULL UNION ALL SELECT ch.root, c.id, c.full_name, c.from_year, ch.depth + 1 FROM cars c JOIN chain ch ON c.supersedes_id = ch.id ) SELECT root, id, full_name, from_year, depth FROM chain ORDER BY root, depth; -- What per-lap data exists, by season, and which source it came from. -- Empty in the distributed build: both loaders need network access, and both -- sources are licensed for local use rather than redistribution. CREATE VIEW v_lap_coverage AS -- Counted in subqueries rather than by joining both tables to `races` at -- once. Joining laps AND pit_stops in one query multiplies them: ten laps -- and two stops in a race renders as twenty laps, because every lap row is -- paired with every stop row. COUNT(DISTINCT ...) hides it for the ids and -- not for anything else, which is worse than failing outright. SELECT r.year, COUNT(*) AS races, (SELECT COUNT(DISTINCT l.race_id) FROM laps l JOIN races x ON x.id = l.race_id WHERE x.year = r.year) AS races_with_laps, (SELECT COUNT(*) FROM laps l JOIN races x ON x.id = l.race_id WHERE x.year = r.year) AS laps, (SELECT COUNT(DISTINCT p.race_id) FROM pit_stops p JOIN races x ON x.id = p.race_id WHERE x.year = r.year) AS races_with_stops, (SELECT COUNT(*) FROM pit_stops p JOIN races x ON x.id = p.race_id WHERE x.year = r.year) AS pit_stops, (SELECT GROUP_CONCAT(DISTINCT l.source) FROM laps l JOIN races x ON x.id = l.race_id WHERE x.year = r.year) AS sources FROM races r GROUP BY r.year ORDER BY r.year; -- ------------------------------------------------ the chassis register -- -- Every chassis that has raced, with whatever specification has been -- established for it. `specs` says where that came from: 'harvested' where -- the car's own article was read and accepted, 'register only' where the -- chassis is known to exist and nothing more. CREATE VIEW v_chassis AS SELECT ch.id, ch.full_name AS chassis, COALESCE(t.name, ch.f1db_constructor_id) AS constructor, ch.first_year, ch.last_year, ch.seasons, ch.races AS recorded_races, ch.wins, ch.published_wins, ch.engine_name, ch.engine_config, ch.capacity_cc, ch.aspiration, ch.power_bhp, ch.chassis_type, ch.weight_kg, ch.wheelbase_mm, ch.car_id, ch.article, CASE WHEN ch.article IS NULL THEN 'register only' ELSE 'harvested' END AS specs FROM chassis ch LEFT JOIN constructors t ON t.id = ch.constructor_id ORDER BY ch.first_year, ch.full_name; -- What the chassis register actually covers, by decade. The shape of this is -- the finding, not an accident: a modern team runs one car all season and the -- entry list settles it, while a 1960s "constructor" was a name several -- privateers entered several different chassis under, and the season settles -- nothing. CREATE VIEW v_chassis_coverage AS SELECT (r.year / 10) * 10 AS decade, COUNT(*) AS race_entries, SUM(CASE WHEN e.chassis_id IS NOT NULL THEN 1 ELSE 0 END) AS with_chassis, ROUND(100.0 * SUM(CASE WHEN e.chassis_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct FROM race_entries e JOIN races r ON r.id = e.race_id GROUP BY decade ORDER BY decade; -- Every constructor-season the entry lists cannot resolve to one chassis, -- with the chassis it was choosing between. This is the open half of -- known_gaps #3, listed rather than described. CREATE VIEW v_ambiguous_seasons AS SELECT se.year, COALESCE(t.name, se.f1db_constructor_id) AS constructor, GROUP_CONCAT(DISTINCT se.chassis_ids) AS chassis, (SELECT COUNT(*) FROM race_entries e JOIN races r ON r.id = e.race_id WHERE r.year = se.year AND e.constructor_id = se.constructor_id) AS unlinked_entries FROM season_entrants se LEFT JOIN constructors t ON t.id = se.constructor_id WHERE se.chassis_ids IS NOT NULL GROUP BY se.year, se.f1db_constructor_id HAVING COUNT(DISTINCT se.chassis_ids) > 1 OR MAX(se.chassis_count) > 1 ORDER BY unlinked_entries DESC, se.year; -- What each season's rules capped or required, next to nothing else. CREATE VIEW v_regulation_limits AS SELECT field, from_year, to_year, value, unit, note, confidence, source FROM regulation_limits ORDER BY field, from_year;