What comes out of BODS, and what it turns into

Status: field-level reference, written 2026-09-08. Two feeds go in — live positions and a timetable — and a handful of tables come out. This says what every field is, which ones are actually populated, and where each one ends up. Coverage figures are measured, and say what they were measured on.

The store section is generated rather than written: 07-schema.R prints the real column list from the database itself, and what is below was pasted from its output on 2026-09-08. The full column-by-column listing, every table, is the store, table by table. A schema written from memory is wrong within a month — this one already was, in two places, before it was first published.

Input 1: live positions (SIRI-VM)

One HTTP request every 20 seconds returns every vehicle currently reporting in the area. The XML is kept exactly as it arrived, gzipped, because the feed only ever serves now and a minute not captured cannot be asked for again.

Each VehicleActivity carries these. Coverage is out of 851 vehicles in one region, measured 2026-09-07:

Field	Coverage	What it is, and what it is good for
RecordedAtTime	100%	When the vehicle reported. Not when you fetched it — see the stale tail below.
ValidUntilTime	100%	Publisher's expiry. Not used.
VehicleRef	100%	The operator's own vehicle number. Collides between operators — 1,433 refs nationally are used by more than one — so it is never a key on its own.
OperatorRef	100%	A NOC (FSYO, SYRK). Not the GTFS agency_id, which is OP153-style.
LineRef / PublishedLineName	100%	The service number. Most operators publish it bare (X2); some publish a scheme-qualified reference, so both are read.
DirectionRef	100%	inbound / outbound.
OriginRef	100%	ATCO code of the first stop. Joins straight to GTFS stops.stop_id.
DestinationRef	100%	ATCO code of the last stop.
OriginAimedDepartureTime	95%	Scheduled departure from the origin. The third part of the journey key, and the field nine operators omit entirely.
DestinationAimedArrivalTime	not measured	Scheduled arrival. Not used.
VehicleLocation/Latitude,/Longitude	100%	WGS84. Everything measured here is derived from these.
Bearing	78%	Degrees. -1 is used as a "no bearing" sentinel by some operators.
BlockRef	68%	The operator's number for a day's work — the chain of journeys one bus runs. Not used yet, and the most promising thing in the feed.
DatedVehicleJourneyRef	most	Looks like a trip id and is not one. Values are 4, 0945, 1040: it equals the operator's own TicketMachine/JourneyCode. Joining it to GTFS trip_id gives 0%.
DriverRef	15% (national)	Published by some operators. Not used, and not something to put on a page.
Occupancy	0.2%	Coarse words, not counts. SIRI 2.0 cannot express a passenger count at all.

Fields that do not exist in this feed at all, which is the part that determines what can be built:

• Delay — nothing ever says a bus is late. Every punctuality figure here is derived by comparing positions against the timetable.
• MonitoredCall / OnwardCalls — the feed never names the next stop or gives a prediction. Departure predictions are ours to make.
• Passenger counts — absent GB-wide, in both this feed and GTFS-RT.
• Accessibility — the fields exist in the standard and are unpopulated.

Two traps worth repeating, because both are silent:

• ~14% of the national feed is more than an hour stale, 7.3% more than twelve hours. Records are not dropped when a vehicle stops reporting, so an unfiltered feed reports buses that parked up this morning. Filter on RecordedAtTime.
• Key a vehicle on OperatorRef + VehicleRef, never the ref alone.

Input 2: the timetable (GTFS)

A bulk export, refreshed weekly. Loaded all-varchar: ids look numeric, and a stop_id of 0345 read as an integer silently drops every join it takes part in. Every column of every file below survives into the store as text.

File	Columns that matter here
agency.txt	agency_id (OP153-style), agency_name, agency_noc — the only field that joins the timetable to the live feed's OperatorRef. Populated 58/58.
routes.txt	route_id, route_short_name (the line as printed on the bus), agency_id.
trips.txt	trip_id (VJ + 40 hex), route_id, service_id, direction_id, shape_id.
stop_times.txt	trip_id, stop_id, stop_sequence, arrival_time, departure_time, timepoint.
stops.txt	stop_id (ATCO), stop_name, stop_lat, stop_lon.
calendar.txt	service_id, the seven weekday flags, start_date, end_date.
calendar_dates.txt	service_id, date, exception_type (1 adds a day, 2 removes one).
shapes.txt	shape_id, shape_pt_lat, shape_pt_lon, shape_pt_sequence — the road the bus drives. Present for 83.2% of trips.
feed_info.txt	feed_version and the validity dates. Empty in this export, which is why 06-stamp.R exists.

Four things about this timetable that cost time to learn:

• Times are local service time, and the live feed is offset-stamped UTC. Compare them without converting and every summer journey is an hour out.
• 24:15:00 is legal and means quarter past midnight on the following morning. So is H:MM:SS, unpadded.
• stop_sequence is text, so ordering by it puts stop 10 before stop 2 and a distance chain doubles back on itself — 2.38x the true distance on a 14-stop test trip. Cast before ordering, every time.
• timepoint is populated on 13.3% of stop times (552k of 4.16M). Those are the stops the timetable actually commits to; the rest are interpolated.

Input 3: fares (NeTEx), read separately

Parsed on demand rather than stored: 302 XML files in one region's zip give 40,393 fare pairs, £1.60–£3.00. The £3.00 that is both the maximum and the third quartile is the national fare cap showing up in the data.

What ends up in DuckDB

Generated by 07-schema.R on 2026-09-08, against a store holding two days of capture and one timetable export. One DuckDB file, bods.duckdb, with the parquet archive beside it. The database is in-process — no service, no port, nothing to install.

The positions are not in the database

The poller writes one parquet file per poll into a dated folder, and every query globs parquet\*\*.parquet. That keeps the single writer out of the readers' way, and it is why 04-compact.R can fold a finished day into one file without anything else changing. 1.07M rows over two days.

column	type	note
recorded	TIMESTAMP	The vehicle's own RecordedAtTime
pulled_at	TIMESTAMP	When we fetched it. The pair is what makes the stale tail visible — a row where these are hours apart is a bus that stopped reporting, not a bus standing still
operator, vehicle, vehicle_key	VARCHAR	vehicle_key is the operator-plus-vehicle composite, because VehicleRef alone collides
line_ref, line	VARCHAR	As published, and as printed on the bus
direction	VARCHAR
journey_code	VARCHAR	The operator's own code. Not a GTFS trip_id
block_ref	VARCHAR	The day's work this bus is on. Captured, not yet used
origin_ref, origin_name, dest_ref, dest_name	VARCHAR	ATCO codes and names
origin_dep, origin_dep_local	TIMESTAMP / VARCHAR	Both forms kept: the local string is what joins to the timetable
dest_arr	TIMESTAMP
lat, lon, bearing	DOUBLE	bearing uses -1 as a no-value sentinel
monitored	VARCHAR	The feed's own claim that the vehicle is being tracked

Occupancy is not captured. It is present on 0.2% of vehicles and coarse where it exists, so nothing here would be able to use it; the raw XML still has it if that ever changes.

The tables

Table	Rows	One row per	Notes
agency	58	operator	agency_noc is the only bridge to the feed's OperatorRef
routes	1,226	route	route_short_name is the line as printed
trips	90,551	trip	Carries block_id and vehicle_journey_code as well as shape_id — see below
stop_times	4,161,549	trip × stop	timepoint, and a shape_dist_traveled column
stops	32,462	stop	Includes wheelchair_boarding, which the export leaves empty
calendar	209	service pattern
calendar_dates	16,822	service exception
shapes	3,966,315	shape point	Plus shape_dist_traveled
sched	90,551	trip, not stop	The journey-level table: trip_id, service_id, direction_id, line, route_type, agency_id, agency_name, origin_ref, origin_dep_local. Those last three are the journey key the live feed is matched on. Stop-level work joins stop_times directly
sched_today	28,772	trip today	sched filtered to one service day, with sched_today_services holding the 117 service_ids that ran
hist_sched, hist_sched_services	28,496 / 122	trip on a scored day	The same, for the day being scored rather than today
active_services	117	service_id
timetable_stamp	1	the loaded export	Label, source file, and the fingerprint that detects a reload
hist_stop_visits	22,752	bus × stop × day	One observation per bus per stop, with delay_min, band, and the three counters
hist_journeys	12,594	journey × day	Status, ran, measurable, miles, miles_basis
hist_days	1	day	The headline figures, miles_basis, timetable, computed_at

There is no feed_info table: the export does not provide one, which is what 06-stamp.R and timetable_stamp exist to replace.

Three leads, tested 2026-09-08

08-leads.R scored all three against journeys matched the way everything else matches them. Two are dead and one is the lever.

trips.vehicle_journey_code is not the feed's journey code. Ruled out. Populated on 100% of trips, present on both sides for 100% of matched journeys, and matching on 0.0% of them — before and after stripping leading zeros. The values explain why: the feed publishes 1018, 1042, 0935B — the operator's ticket-machine code — while the timetable holds vj_2, VJ1519, VJ4343, which are TransXChange's own VehicleJourney identifiers. Same column name, different namespaces, exactly like agency_id against a NOC. There is still no shared journey id, and the three-column key stays the only way across.

shape_dist_traveled is empty. Ruled out. 0.0% populated on both shapes and stop_times. Distance has to be computed from the geometry, which is what trip_miles already does.

Blocks work — through the timetable, not the feed. block_id is populated on 74.7% of trips and block_ref on 71.9% of matched journeys, but they agree on 0.0%: the feed sends the operator's block number (25123, 36210) and the timetable holds a 40-hex hash. Another pair of namespaces, and another join that cannot be made.

It does not need to be. 81.1% of journeys that have a block have an earlier journey on the same block, and that chain is entirely inside the timetable. The bus that will run the 14:20 is the bus running the 13:45 on the same block, so a departure 25 minutes out can inherit the delay of a vehicle that is live now — matched on its current journey by the ordinary key, with no block_ref involved at all. Roughly 60% of all trips have such a predecessor (74.7% × 81.1%), against the 27% of departures that get a live prediction today.

And what leaves it

File	One row per	Read by
positions.csv	vehicle, now	The map
observations.csv	bus × stop today	Punctuality
delivery.csv	scheduled journey today	Did-it-run and lost mileage, with miles_basis on every row
departures.csv	scheduled departure in the next 30 min	The departure board
history_days.csv, history_operators.csv, history_periods.csv	day / day × operator / reporting period	Trends, and anything asked about last month

Every one of them is rewritten on a timer and none is worth editing or backing up: they are all derived from the raw capture, which is the only thing here that cannot be made again.