The store, table by table

Status: generated, not written. Every table and column below came out of 07-schema.R run against the store on 2026-09-08, holding two days of capture and one timetable export. Row counts move; the shapes do not. Regenerate it rather than editing it by hand.

What each field means, and which are populated, is in what comes out of BODS. This is the column list.

Captured

Written by the poller, one parquet file per poll into a dated folder. Not a table: every query globs the folder, which is what keeps the single writer out of the readers' way.

parquet · positions — 1,068,693 rows

One row per vehicle per poll

column	type
recorded	TIMESTAMP	The vehicle's own RecordedAtTime
pulled_at	TIMESTAMP	When we fetched it. The gap between these two is the stale tail
vehicle_key	VARCHAR	operator + vehicle, because VehicleRef alone collides
operator	VARCHAR	NOC
vehicle	VARCHAR
line	VARCHAR	Journey key
line_ref	VARCHAR	As published
direction	VARCHAR
journey_code	VARCHAR	Operator's ticket-machine code. Joins nothing
block_ref	VARCHAR	Operator's block number. Joins nothing — see below
origin_ref	VARCHAR	Journey key. ATCO, joins stops.stop_id
origin_name	VARCHAR
dest_ref	VARCHAR
dest_name	VARCHAR
origin_dep	TIMESTAMP
origin_dep_local	VARCHAR	Journey key
dest_arr	TIMESTAMP
lat	DOUBLE
lon	DOUBLE
bearing	DOUBLE	-1 is a no-value sentinel
monitored	VARCHAR

The timetable, as loaded

One GTFS export, read straight in and left alone. Every column is text: a stop_id of 0345 read as a number silently drops every join it takes part in.

agency — 58 rows

One row per operator

column	type
agency_id	VARCHAR	OP153-style
agency_name	VARCHAR
agency_url	VARCHAR
agency_timezone	VARCHAR
agency_lang	VARCHAR
agency_phone	VARCHAR
agency_noc	VARCHAR	The only bridge to the feed's OperatorRef

routes — 1,226 rows

One row per route

column	type
route_id	VARCHAR
agency_id	VARCHAR
route_short_name	VARCHAR	The line as printed on the bus
route_long_name	VARCHAR
route_type	VARCHAR

trips — 90,551 rows

One row per trip

column	type
trip_id	VARCHAR	VJ + 40 hex
route_id	VARCHAR
service_id	VARCHAR
trip_headsign	VARCHAR
direction_id	VARCHAR
block_id	VARCHAR	74.7% populated. The chain that predicts departures
shape_id	VARCHAR	83.2% of trips
wheelchair_accessible	VARCHAR	Present, empty
vehicle_journey_code	VARCHAR	TransXChange's id. Not the feed's journey_code

stop_times — 4,161,549 rows

One row per trip and stop

column	type
trip_id	VARCHAR
stop_id	VARCHAR
stop_sequence	VARCHAR	Text — cast before ordering
arrival_time	VARCHAR	Local service time; 24:15:00 is legal
departure_time	VARCHAR
stop_headsign	VARCHAR
pickup_type	VARCHAR
drop_off_type	VARCHAR
shape_dist_traveled	VARCHAR	Empty
timepoint	VARCHAR	13.3% — the stops the timetable commits to

stops — 32,462 rows

One row per stop

column	type
stop_id	VARCHAR	ATCO
stop_code	VARCHAR
stop_name	VARCHAR
stop_lat	VARCHAR
stop_lon	VARCHAR
wheelchair_boarding	VARCHAR	Present, empty
location_type	VARCHAR
parent_station	VARCHAR
platform_code	VARCHAR

calendar — 209 rows

One row per service pattern

column	type
service_id	VARCHAR
monday … sunday	VARCHAR	Seven flag columns
start_date	VARCHAR
end_date	VARCHAR

calendar_dates — 16,822 rows

One row per service exception

column	type
service_id	VARCHAR
date	VARCHAR
exception_type	VARCHAR	1 adds a day, 2 removes one

shapes — 3,966,315 rows

One row per shape point

column	type
shape_id	VARCHAR
shape_pt_lat	VARCHAR
shape_pt_lon	VARCHAR
shape_pt_sequence	VARCHAR	Text — cast before ordering
shape_dist_traveled	VARCHAR	Empty

Derived for measurement

Built from the timetable, not from the feed. sched is the journey-level table — one row per trip, carrying the three columns the live feed is matched on.

sched — 90,551 rows

One row per trip — not per stop

column	type
trip_id	VARCHAR
service_id	VARCHAR
direction_id	VARCHAR
line	VARCHAR	Journey key
route_type	VARCHAR
agency_id	VARCHAR
agency_name	VARCHAR
origin_ref	VARCHAR	Journey key
origin_dep_local	VARCHAR	Journey key

sched_today — 28,772 rows

One row per trip running today

column	type
(same nine columns assched)		Filtered to today's service_ids

hist_sched — 28,496 rows

One row per trip on the day being scored

column	type
(same nine columns assched)		Filtered to that day's service_ids

sched_today_services / active_services / hist_sched_services — 117 / 117 / 122 rows

One row per service running that day

column	type
service_id	VARCHAR	The calendar filter, resolved once

timetable_stamp — 1 rows

One row: which export is loaded

column	type
label	VARCHAR
source_file	VARCHAR
file_bytes	BIGINT
file_modified	VARCHAR
stamped_at	VARCHAR
fingerprint	VARCHAR	Trips / stop_times / calendar range. Detects a reload without a re-stamp

Scored history

Written only for finished days. Today is never stored: a part-day filed as a whole one is exactly the number that gets quoted back at you six months later.

hist_stop_visits — 22,752 rows

One row per bus, stop and day

column	type
date	VARCHAR
vehicle	VARCHAR
noc	VARCHAR
operator	VARCHAR
line	VARCHAR
stop_name	VARCHAR
scheduled	VARCHAR
actual	VARCHAR
delay_min	DOUBLE
band	VARCHAR
on_time	INTEGER
early	INTEGER
late_over_5	INTEGER

hist_journeys — 12,594 rows

One row per scheduled journey and day

column	type
line	VARCHAR
operator	VARCHAR
noc	VARCHAR
direction	VARCHAR
origin	VARCHAR
scheduled	VARCHAR
is_first	INTEGER
is_last	INTEGER
status	VARCHAR	One of six
ran	INTEGER
measurable	INTEGER	Only Ran and Not seen
miles	DOUBLE
miles_basis	VARCHAR	Per journey, because it varies per journey
date	VARCHAR

hist_days — 1 rows

One row per finished day

column	type
date	VARCHAR
weekday	VARCHAR
watched_from	VARCHAR
watched_to	VARCHAR
watched_hours	DOUBLE
pings	DOUBLE
vehicles	DOUBLE
stop_visits	INTEGER
on_time	INTEGER
early	INTEGER
late_over_5	INTEGER
on_time_pct	DOUBLE
early_pct	DOUBLE
late_pct	DOUBLE
journeys	INTEGER
measurable	INTEGER
ran	INTEGER
delivery_pct	DOUBLE
miles_operated	DOUBLE
miles_lost	DOUBLE
miles_not_judged	DOUBLE
lost_pct	DOUBLE
miles_basis	VARCHAR
timetable	VARCHAR	Which export scored it
computed_at	VARCHAR