WTT Train Timetable

Network Rail Working Timetable (WTT) train schedules loaded into the owntracks Postgres database on gravlax, alongside gazetteer and locations. Each train journey and its calling points are stored as relational rows, with calling points linked to gazetteer stations (CRS + lat/lon/geom). Purpose: give applications a machine-queryable source of scheduled train times to compare against actual GPS movement — most directly for Delay Repay Automation (was the timetable late?).

See also: location-db • usage • gazetteer • delayrepay • gwr-delay-repay.

Key facts

• Host / DB: gravlax.critchley.biz, database owntracks (same DB as gazetteer/locations).

• Tables (schema public): train_journey, train_calls, wtt_location.

• Read access: MCP pg_query tool (role owntracks_ro, SELECT) — same connector as the gazetteer/locations queries. See usage.

• Write/owner role: travel — a dedicated login role (scram password auth on the local socket / stunnel) with full access to the owntracks DB. Password lives only in ~/.pgpass on the working host (never in notes). owntracks_ro was granted SELECT on all tables + default privileges for future travel-created tables.

• Source: Network Rail Working Timetable, June–December 2026 period (valid 18/05/2026–12/12/2026), supplied as per-route .xlsx books (one column per train, rows are calling points with arr/dep/pass, platform, line, allowances).

• Volume currently loaded: 83,113 journeys, 1,494,642 calling points, and 4,172 distinct WTT location names. Of those locations, 2,449 are linked to a gazetteer station and 1,723 are retained as non-station or currently unmatched timing points.

Scope loaded — full national corpus

The complete supplied Network Rail June–December 2026 WTT corpus is loaded: 251 .xlsx workbooks across all route-book directories, destinations, operators, directions, passenger workings, empty-stock movements, and freight schedules. The former Paddington–Bristol Parkway / Swansea / Carmarthen slice is now part of this full dataset.

The load completed on 4 September 2026. Validation found zero journeys without calls, zero orphan calls, zero duplicate (journey_id, seq) positions, zero missing validity dates, and zero malformed nonblank operator codes. Six source sheets omit the Operator header; their 1,765 journeys therefore retain a blank operator while preserving valid UID, endpoints, dates, running days, and calls.

Coverage period in the source spans 17/05/2026–13/12/2026 (individual schedules carry their own validity dates).
Circular and depot workings preserve repeated visits to the same timing point; duplicate overlaps between route books are merged using location and booked-time agreement.
WTT names that do not resolve to gazetteer are retained, since many are junctions, loops, depots, signals, yards, freight terminals, or closed stations rather than data errors.

Schema

train_journey — one row per logical train, keyed by the schedule UID (the only reliable journey key; a through train is filed across many route books and both day-type/direction sheets, all sharing one UID).

Column	Meaning
journey_id	surrogate PK (bigserial)
uid	schedule UID, e.g. W34646 — UNIQUE
headcode	reporting number (TID), e.g. 1B31 — NOT unique; reused across days
operator	e.g. GW (Great Western)
origin_name / dest_name	true endpoints (uppercase WTT names, with dep/arr time)
origin_id / dest_id	gazetteer.id of origin/dest station (nullable)
running_days	e.g. SX (Sat excepted, approx Mon-Fri), SO, SUO, MO, FO — the authoritative day filter
valid_from / valid_to	schedule validity dates
timing_load, service_code, n_segments	stock/timing load, service code, number of books stitched

train_calls — one row per calling point, ordered along the journey by (day_offset, seq).

Column	Meaning
journey_id	FK -> train_journey
seq	order along the route
wtt_name	calling-point name (FK -> wtt_location)
station_id	gazetteer.id of the station (NULL for junctions / closed stations)
arr / dep / pass	booked times (time-of-day; half-minutes stored as :30 seconds)
is_pass	true = passing point (no stop); false = booked stop
day_offset	0, or 1 for post-midnight calls (journey crossed midnight)
platform, line, allowance, activities	platform, running line, engineering/pathing allowance, activity codes
raw_arr / raw_dep / raw_pass	original WTT cell text, e.g. 00TF43, 08/51 half

wtt_location — resolves each distinct calling-point name once to a gazetteer station (so names are matched in one place, not per-call): wtt_name (PK), norm_name, station_id -> gazetteer, crs, is_station, match_method, confidence. Matching is exact on a normalised (uppercase, non-alphanumerics stripped) name against gazetteer WHERE source='uk-railway-stations'; unmatched names are junctions/loops/tunnels or long-closed stations and correctly keep station_id = NULL.

How to query (via pg_query)

Scheduled arrival of a specific train at a station — the core delay-repay lookup (here the 19:18 SX Paddington->Swansea at Bristol Parkway):

SELECT j.headcode, wl.crs, tc.arr, tc.dep
FROM train_journey j
JOIN train_calls  tc ON tc.journey_id = j.journey_id
JOIN wtt_location wl ON wl.wtt_name   = tc.wtt_name
WHERE j.headcode = '1B31' AND j.running_days = 'SX'
  AND wl.crs = 'BPW';

Departure board — weekday trains leaving Paddington in a time window:

SELECT j.headcode, j.dest_name, pc.dep
FROM train_journey j
JOIN train_calls pc
  ON pc.journey_id = j.journey_id AND pc.wtt_name = 'LONDON PADDINGTON'
WHERE pc.dep >= time '19:00' AND pc.dep < time '19:30'
  AND j.running_days NOT IN ('SUO','SO')
ORDER BY pc.dep;

Full calling pattern with coordinates (join gazetteer for lat/lon; booked stops only):

SELECT tc.seq, tc.wtt_name, wl.crs,
       tc.arr, tc.dep, g.lat, g.lon
FROM train_journey j
JOIN train_calls  tc ON tc.journey_id = j.journey_id
JOIN wtt_location wl ON wl.wtt_name   = tc.wtt_name
LEFT JOIN gazetteer g ON g.id = tc.station_id
WHERE j.headcode = '1B31' AND j.running_days = 'SX'
  AND tc.is_pass = false
ORDER BY tc.day_offset, tc.seq;

Using it in delayrepay (and similar)

Delay Repay needs the booked arrival at the destination for the train the traveller was on, to compare against the actual arrival derived from GPS. Pattern:

Identify the train: by headcode if known, else by its booked Paddington departure time (the LONDON PADDINGTON call dep) for the right running_days.
Read its booked arrival at the destination station (join train_calls -> wtt_location on crs).
Read the actual arrival from locations — first GPS fix within ~500 m of the station geom after the booked time (ST_DWithin(geom::geography, ...)).
Delay = actual - booked; map to the GWR band (15-29 / 30-59 / 60+ min).

Concrete link: the delayrepay 27 Aug return leg was blocked for want of “the 19:18 train’s scheduled arrival vs the ~20:48 GPS actual” — that train is 1B31 (SX), booked Bristol Parkway 20:27. That figure is now a single pg_query away. (Note: WTT times are working times, typically ~half-to-one minute tighter than the public timetable; for a claim boundary, cross-check the public arrival.)

Gotchas

Always filter running_days — headcodes (and even a given departure minute) are reused across Mon-Fri / Sat / Sun for different trains.
Order calls by (day_offset, seq), not seq alone — post-midnight legs carry day_offset = 1.
Filter is_pass = false for the public stops view; passing points (junctions, tunnels) have is_pass = true.
Join to gazetteer with LEFT JOIN — junctions and closed stations have station_id = NULL.
Half-minute WTT times are stored with :30 seconds; raw_* columns keep the original cell text (e.g. 08/51 half, 00TF43).
Terminating and originating times are normalised: a terminus's single time is stored as arr (the source sometimes records it in the dep row with a TF code), an origin's as dep. So query arr at the destination and dep at the origin.
GWML Paddington <-> Bristol Parkway / Swansea / Carmarthen only (both directions) — see Scope above.

How it was built / provenance

Loader (Python + openpyxl + psycopg2) parses the WTT .xlsx books, then for each schedule UID stitches all its per-book segments into one journey: dedupe overlapping boundary points by name, order by booked time, and apply a midnight rollover anchored on the header origin-departure time. Calling-point names are resolved to gazetteer stations via the normalised exact match above. Scoped to the Western P* books (complete for these routes; a whole-corpus run just drops the scope + origin/dest filter).

Loader and schema are persisted in the Envoy repository as wtt_load.py and wtt_ddl.sql, with the operational copy on kelp:/home/john/tmp. The loader supports preview-slice, load-slice, preview-full, and load-full; full loads use dynamic header detection, overlap-aware stitching, batched inserts, and one atomic transaction. A pre-full-load table dump is retained on gravlax at /var/tmp/wtt_tables_before_full_20260904.sql. The travel role and owntracks_ro grants remain to be persisted in Ansible.

Todo

Persist the travel role + pg_hba scram line + owntracks_ro grants in the gravlax Ansible.
Review high-value unmatched WTT station aliases and add explicit gazetteer mappings where appropriate; do not map junctions, loops, depots, yards, or signals as stations.
Update the pg_query tool description in misc_mcp_server.py to mention the train tables (it currently documents only locations), so agents discover the schema.

Related: Public (Passenger) Timetable — CIF Load — plan + drafted loader to bring the passenger-facing PUBLIC arrival/departure times (from the Network Rail SCHEDULE CIF feed) into parallel pt_* tables, so a delay-repay claim can cross-check the public arrival that GWR uses (WTT working times run ~½–1 min tighter).