Safe2Go — Task Queue

Estimates are working time, not guarantees. Progress of each phase will show in the daily journey test (example trips from the TO-BE document are added as each phase starts).

Phase	Scope	Estimate	Status
0	Repairs: running-days reload (378 + 161 feeds, Switzerland), RAPTOR rebuild, Istanbul, USA search index, transfers tables, database safeguards	1 Oct	running
Traffic A	Store official open live-traffic data every few minutes and build per-road, per-hour profiles: Netherlands (NDW, every minute), Finland (Digitraffic, 5 min), Germany (Autobahn API warnings/closures; licence check first), then UK, France, Norway, Denmark, US 511	first version ~1–2 weeks	sources verified 1 Oct; collectors next
Traffic B	"Typical traffic" everywhere: road class + speed limits + time-of-day factors calibrated from A; always labelled typical, never live	~1 week after A starts	queued
1	Transfer graph (operators' transfers/pathways), airport graph (connecting flights through hubs; times "check airline" until a paid schedule source exists), ferry graph	3–5 working days	transfers tables in progress
2	Global node/place model, connector graph, cross-border stitching of the regional road graphs, one time-dependent search across all modes	2–4 weeks	queued
3	Remove external search/routing APIs (Photon, Nominatim, Mappls runtime, Overpass, Geoapify/Foursquare, public OSRM, ipwho.is), each only after a latency/accuracy test	1–2 weeks	queued
Traffic C	Safe2Go users' own GPS history (needs the owner's consent wording first)	months to mature	waiting for owner

Limits outside the code: real flight times need a paid schedule source; most ferry schedules need operator agreements; Nepal (the TO-BE example Sahibabad → Kathmandu) has no road/address region or bus timetable yet; TomTom stays the live traffic layer until our own coverage is enough (its data is not stored — licence).

Found from the owner's video (Mannheim Käfertal → Almere showed no train or bus). These feeds had been loaded with real data and were then removed by two automated server jobs:

• The nightly gtfs-freshness deleted a feed's old timetable before reloading it, so a failed reload left the feed empty. This was fixed on 28 Sep.
• Then the weekly gtfs-refresh start-up cleanup deleted the empty feed's record altogether.
• The 28 Sep repair only restored feeds that still had a record, so these 7 were missed.

Full evidence: Safe2Go-Evidence/2026-10-01_deletion_audit/AUDIT_REPORT.md. Of 2,998 feed IDs ever seen, 2,934 are present and 64 records are missing. 57 of the missing ones show no data when lost (see Feeds Monitor → "Lost feeds").

• OVapi (Netherlands) — RESTORED 05:10 UTC: 1,042,567 trips, 20,262,245 stop_times (3 Sep load: 16,378,225).
• Running, one at a time at low priority: HSL Helsinki → Mexico City → Fluo Grand Est → SIMUR Bogotá → AMT Italy, then De Lijn (Flanders, 11.2M on 3 Sep). Logs: /var/log/gtfs-restore-20261001*.log.

• Database deletion guard (infra/hetzner-gtfs-pipeline/gtfs_guard.sql):
  • A feed can't disappear and its timetable can't be emptied unless the owner switches the guard off. Every switch-off is recorded in gtfs_guard_override.
  • Direct deletes or TRUNCATE on timetable tables are rejected.
  • Normal reloads (delete + reinsert in one transaction) still work.
  • Tested on a scratch database with the real reload code: 10/10 pass (server/scripts/test_gtfs_guard.js).
  • Installed on the live database 05:37:43 UTC (attempt 71, /var/log/gtfs_guard_apply.log): 16 rules, all enabled; a live test delete was rejected.
  • Limit: a database superuser can still switch triggers off, so the inventory below counts the real rows independently.
• Daily feed inventory on the owner's PC: Windows task "Safe2Go feed inventory", 07:45 daily.
  • Saves real per-feed trip and stop_times counts to D:\Safe2Go-Backups\feed-inventory\, copied to E: when connected.
  • Writes CHANGES_<date>.txt listing every feed that disappeared, emptied or shrank by more than 10%, plus the guard's state.
• Weekly job fixed: the gtfs_ingest_all.js start-up cleanup now only reports unfinished feeds and retries them. It no longer deletes them. Deployed; md5 matches git.

• Cross-border trains not found (Käfertal → Almere: 0 RAPTOR journeys at 23:12 and at 08:00). There are two causes:
  • No Dutch timetable. Fixed by the OVapi restore above.
  • RAPTOR only uses feeds with stops within 1–2 km of either end, or within ~30 km of both, and only allows walking at the ends (max 2 km).
  Owner approved: hub-based feed selection and taxi/car for the first and last stretch. Queued next.
• 39 feeds that are active in the catalog were already empty when lost (route-connections build rows=0, 8–12 Sep), mostly small US demand-response operators. A further 8 active feeds were lost with no build record: Edmonton, PID Prague, Odesa, Alexandria, Reus, Zaratán, mdb-2046 and mdb-2434. Reload decision pending with the owner.
• 5 feeds are smaller than their 30 Sep snapshot: tdg-84037, mdb-787, mdb-1918, mdb-1847 and jbda-gan-wu-shiokazeline. The cause is not determined; it may be a newer, smaller timetable from the operator.
• gtfs-reingest.service is in "failed" state. Its 26 Sep run was killed by the automatic Postgres restart on 27 Sep. Next run 3 Oct.
• Destination labels show "europe" instead of the city or country (seen in the owner's video). Fix: hierarchical index (below).
• RAPTOR snapshot ca-l'inter_des_laurentides: apostrophe fix (ed722b5) confirmed, rebuilt 1 Oct with 113 stops and 1,901 stop_times (= database). It is a duplicate of tld-5722.
• Scratch database gtfs_guard_test was left "invalid" by a cancelled drop on 1 Oct. It is empty and touches nothing live; it will be dropped when the imports finish. gtfs_guard_test2 will be dropped then too.

Split the shared world_osm_addresses/localities tables into one physical table per country (unified by a view, transparent to the app). Structurally eliminates the cross-border ID-collision bug — no country's import can touch another country's table anymore, at all. Verified: all 6 unaffected countries migrated with exact matching row counts.

Restored the small border-overlap losses (Argentina lost rows to Brazil, China lost rows to Russia) by re-importing both fresh into their new, isolated tables. Argentina: 2,356,348 addresses. China: 181,324 addresses. Both backed up.

Finished 2026-09-29 07:37 UTC: 106,747,407 addresses, 10,628,636 place names, 0 duplicates, 0 missing geometries; backed up and md5-verified on the server, D: and the external drive. Not yet searchable in the app — see "Search index split" below.

6,457,741 addresses, 94,811 place names; backed up (country_canada_175121.dump). Not yet searchable in the app — included in the search index split below.

1,598,211,317 nodes done. Ways: 155.2M of 161.95M at 1 Oct 04:57 UTC, ~82,000 per minute, so finish expected ~06:20 UTC 1 Oct. That is well before the osm-transit-reimport on 5 Oct 05:00 UTC, which uses the same staging tables. No errors in this run; the 8 ERROR lines in the log date from 22–27 Sep, before it started. Owner decision: let it run, no stop or restart. (Correction: a 04:20 UTC status misread the log's thousands as single ways and said "weeks".)

On 29 Sep the single search index grew 27M → 145M rows with Europe and every app search timed out (>6 s), so the old 27M index was restored. Fix being built next to the live index (live search untouched):

• place_search_places — 7,320,935 place names (cities, localities, post offices, POIs), duplicates removed, Canada included. Live.
• place_search_addresses — 135,846,517 street addresses, street-first accent-folded key ("Hauptstrasse 5" = "Hauptstraße 5"), 0.5° grid cell for "near me". Live.
• Every query is an index range read with a hard row limit; typo tolerance only over place names.
• Gate before go-live: server/scripts/test_split_search_latency.js — 28 real queries (DE, IN, FR, IT, CA, PL, typo), p95 ≤ 1 s and every query must find the right place. Tables are switched in only if it passes. Result: p50 6–11 ms, p95 90–100 ms, 0 wrong of 28 (first run found 4 ranking bugs — fixed before go-live). Live app: typing suggestions 0.2–0.7 s end to end (was 0.6–6.2 s).
• Search box speed (fixed 2026-09-30, a9ced3a): /api/geocode/search still takes ~2.5–5 s (it waits for Mappls + building snap), so the search box now shows Safe2Go's own results first (/suggest, ~0.25 s; ~0.4 s after the last keystroke) and merges the full search in when it lands; Plan-without-picking uses the full answer if it comes within 1.5 s. /suggest has its own rate limit (600 per 5 min; /search stays 120). Verified in Chrome on the live site ("Kurfürstendamm 21": own results at 251 ms, full search 4.9 s later). Open: Place labels show "europe"/"canada" instead of the city/region — fixed properly by the hierarchical index below. Backed up by the nightly gtfs_core set from 30 Sep 21:00 UTC. The old 145M place_search_index_full (52 GB) and 27M place_search_index (10 GB) are kept until the owner OKs removal.

Build script: infra/hetzner-osm-pipeline/build_split_search_index.sql, log /var/log/build_split_search_index.log.

Owner's rule 2026-09-30: "I DO NOT NEED ANY FABRICATION."

• Done (e5f33be): routes with estimated bus/metro/tram/train/ferry legs are dropped (invented "every 5 min" metros, ferries "every 120 min", Berlin → Munich "Ferry + Train from Genoa"). Flight routes: airline route real, times shown as ≈ estimates. Master-timetable sample time no longer shown as the next departure; 29:05 → 05:05. Closed airports (Tegel) excluded everywhere (0a1e6dc). Deploy-gating tests: 14/14.
• Root cause found: the old per-request timetable SQL timed out (6 s) in every dense city although the data is there (Paris 11.1M rows, TTC 4.4M, DB long-distance).
• RAPTOR live (9bae65b): round-based timetable search over per-feed snapshots (infra/raptor), up to 6 vehicles, walking changes across operators. Service safe2go-raptor on Hetzner, nginx :8091/raptor/. Live results: Paris GdN → Eiffel RER B + RER C / Bus 32; Berlin → Munich ICE 29 + S2; Toronto Line 1 (day) / Bus 320 (night); Mannheim → Heidelberg IC 55. Search 0.1–0.8 s.
• Building: snapshots for all 2,807 distinct feeds (build_all.sh, log /var/log/raptor_build.log) — 801 done by 09:20 UTC, 0 failures; nationwide Germany feed (38M rows, local trams/buses incl. Mannheim) in progress.
• Status 1 Oct: 2,804 of 2,807 snapshots built. 3 large feeds (mdb-2027, mdb-3412, gb-bods-all 72M) are deferred until the USA import finishes. The service has been up since 30 Sep 09:50 with 0 restarts.
• Next (owner-approved 1 Oct): pick feeds through major hubs so long cross-border trips (e.g. Mannheim → Almere) can be connected · taxi/car for the first and last stretch, not only walking · planner total time (4–30 s, not RAPTOR) · fares are still distance-formula estimates (GTFS fare data not ingested) · rebuild snapshots automatically after each feed re-ingest · connecting flights + a current flight schedule source (OpenFlights is stale) · ferry schedules.

Owner's direction: search should be organised as a hierarchy — Country → Region/State → City/District → Locality → Street → House number (how Nominatim / Pelias / Photon work) — for accuracy, speed and easy narrowing.

• Accuracy: "Hauptstraße 5" exists in hundreds of towns; each result carries its full parent chain ("…, Heidelberg, Baden-Württemberg, Germany") and "Hauptstraße 5 Heidelberg" matches the right one.
• Speed/size: one row per street per city instead of one per house number (estimated ~10–15M streets instead of ~290M address rows incl. USA — to be measured); house numbers looked up inside the matched street.
• Duplicate names: "Frankfurt am Main" vs "Frankfurt (Oder)" resolved by region; ranking by city importance, not only distance.

Steps: (1) check whether OSM admin boundaries (country/state/district polygons) were kept by the import, import them if not; (2) measure real street counts; (3) build step assigning every place/address to its parents by location; (4) query parser (street / house number / city parts) matched level by level; (5) same latency + accuracy gate as the split index before go-live. Reuses the split index's street-first keys and grid cells.

1. Before the index phase: review data-disk space (~27–30 GB/day used during the import) and free safe items if needed (tile files not yet wired in, old dumps) — with the owner's OK.
2. Verify Europe: real row counts, rebuild place_search_index, test real addresses and routes.
3. Back up Europe (final pg_dump to _osm_backups/) and stop the 2-hourly partial backup loop.
4. Re-enable autovacuum on planet_osm_nodes (switched off for the Europe run).
5. Clean up: the Europe extract file and partial snapshots, once the final backup is verified.
6. Import disk priority: later imports run with ionice + nice (card below).
7. USA & Canada imports (with low priority), each backed up right after.
8. Phase 2 change times: station platform/parent/level details + operators' transfers/pathways.
9. Connecting-route logic (flights first, as the airport graph), then MobilityDatabase discovery and the Transport Source Registry.
10. India Place & Address Master, Phase 4–6.

Awaiting the owner's decision: "street names" search pass (named streets without addressed buildings aren't searchable outside India) · Twilio account for SMS/calls (done 2026-09-30, live app reports SMS & calls enabled; real-phone test pending) ·lock-screen safety notification (native app update).

Found 2026-09-26: while the Europe import runs, the data disk is ~80–90% busy and the app's real-timetable searches (6 s limit) time out, so users intermittently get only taxi/estimated routes instead of real trams and buses (seen live: Mannheim Hbf → Käfertal). The same would happen during USA/Canada.
Fix (queued per instruction, not applied to the running Europe import): start every later import (USA, Canada, …) with low disk and CPU priority (ionice -c2 -n7 + nice) in import_world_addresses.sh, so live user queries always go first. Then check that timetable routes still come back during the import.

Phase 1 is live (2026-09-26): realistic change times from walking routes, crossings, level changes, platform numbers, station size and rush hour, stored in the new station_profile / transfer_time tables. Phase 2: re-import stations' platform / parent-station / level details (dropped at the original import) and the operators' own transfers.txt / pathways.txt where published, then backfill transfer_time worldwide. Needs feed re-downloads, so it waits for the Europe import (disk load).

Both already have Valhalla routing tiles from earlier work, but were never part of this address-search pipeline at all until now. Added with auto-download support, explicitly sequenced after Europe per direct instruction.

Country	Size	Taken	Covers
Brazil	80MB	2026-09-25 03:58 UTC	world_osm_addresses_brazil + world_osm_localities_brazil
Argentina	57MB	2026-09-25 04:16 UTC	world_osm_addresses_argentina + world_osm_localities_argentina
China	22MB	2026-09-25 09:55 UTC	world_osm_addresses_china + world_osm_localities_china
Mexico	38MB	2026-09-25 11:30 UTC	world_osm_addresses_mexico + world_osm_localities_mexico
Malaysia/Singapore/Brunei	9.5MB	2026-09-25 11:30 UTC	world_osm_addresses_malaysia_singapore_brunei + localities
Japan	15MB	2026-09-25 11:30 UTC	world_osm_addresses_japan + world_osm_localities_japan
Russia	307MB	2026-09-25 11:31 UTC	world_osm_addresses_russia + world_osm_localities_russia
Africa	101MB	2026-09-25 11:32 UTC	world_osm_addresses_africa + world_osm_localities_africa
Pre-restructure snapshot	625MB	2026-09-25 01:01 UTC	Full world_osm_addresses/localities (shared table), taken right before splitting into per-country tables

All 8 currently-imported countries now have a post-restructure backup. Europe, Canada, and USA will each get one automatically right after they finish, per standing practice.

Currently only a single direct city-pair is checked against real schedule/route data. Real one-stop connections (e.g. Mannheim → Dar es Salaam via a verified hub) fail even though the underlying real legs exist. Fix: general on-demand logic — works for any city pair worldwide by chaining verified legs through a real hub, never fabricating a route.

Check this open GTFS/GTFS-RT/GBFS catalog (6,000+ feeds, 99+ countries) for real transit feeds not yet ingested beyond the ~2,800 already in the system.

API access token obtained and verified working (2026-09-24) — real OAuth2 exchange tested server-side, ready to use when this phase starts.

A tracked table/doc recording, per country + mode + operator: source type (API/GTFS/GTFS-RT/timetable/commercial), URL, license, coverage, last-successful-fetch, confidence — so every data source's provenance is answerable in one query.

No official UPSRTC or Haryana Roadways GTFS/API found. Independent lead found: "Haryana Bus Info" (GitHub org haryanabusinfo, 154,000+ verified schedules across 100+ Haryana cities) — real feed URL not yet confirmed. UP-side (Mathura) still has no identified open source. RedBus/MakeMyTrip are commercial platforms requiring real API/partner access, not a scrapeable public feed.

Mappls (MapmyIndia) selective enrichment, government open-address datasets, user-correction feedback loop. Deferred pending a real cost/scoping decision — Mappls is a paid API, unlike OSM.