How apps/db turns feed bytes into normalized rows and analytical tables, and the invariants each stage keeps.

Stages

flowchart LR
  F[Provider feed] -->|download + SHA-256| B[(Bronze object<br/>raw.ingestion_runs)]
  B --> S[Silver<br/>normalized rows]
  S --> G[Gold facts<br/>and serving state]
  G --> R[5-minute and hourly<br/>delay summaries]
  R --> D[Daily warm rollups]
  G --> L[Live publication]
  D --> H[Historic publication]

Static pipeline (daily)

run-static-pipeline <provider> downloads the static GTFS (and STM GIS), records the bytes in Bronze, loads Silver and refreshes Gold.

• Capture records source bytes without promoting them; dataset_version_id stays null until Silver loads.
• Identical bytes retry only unfinished work: Silver and Gold after a failed Silver load, Gold alone after a Gold failure. A completed unchanged dataset skips both.
• Empty route data cannot replace a usable dataset. Silver loading pins its source object in the caller's transaction, so pruning cannot remove it mid-load; size and checksum are verified before any Silver write.
• The daily workflow publishes the static tier only if every active provider succeeded. A pipeline failure prevents static publication and preserves the stored snapshots; a failure during upload can leave mutable files from different generations. GIS failures are best-effort.

Realtime cycle (per-endpoint cadence)

run-realtime-worker <provider> first records its start in core.worker_starts through a recorder subprocess bounded to five seconds; a recorder failure logs one warning and the loop starts anyway, and a shutdown signal during that wait exits before the first cycle. It then runs the realtime cycle in process (orchestration.py) on each worker tick; run-realtime-cycle is the matching one-shot CLI command. One worker process runs per provider.

Cadence sources, updated by #746: stm.yaml, octranspo.yaml, realtime_schedule.py. The OC worker was rolled out on November 10, 2026 8:53 PM; production evidence is in ‣.

realtime_schedule.py owns the gating:

• worker_tick_seconds is the greatest common divisor of REALTIME_POLL_SECONDS, every enabled realtime feed's interval, and any declared phase. Static/GIS and disabled feeds do not shorten the tick. The current configuration gives a 10 s tick for both STM and OC Transpo. A provider with a phased feed sleeps to tick boundaries aligned to the UTC epoch; unphased OC keeps a start-to-start cadence and has no artificial two-second source-phase capture window.
• Successful unphased endpoint eligibility uses cycle timestamps, so earlier trip processing cannot push a 20 s vehicle schedule onto 30 s slots. Actual request/capture timestamps remain in the payloads. Failed attempts retain actual-time backoff; source-phased feeds still check actual attempt time. Successful attempt history is cleared only after capture history is recorded. Source: orchestration.py at e2779026, #748.
• endpoint_due skips an endpoint until its interval minus a 2-second tolerance (CAPTURE_CADENCE_TOLERANCE_SECONDS) has passed since its last capture or attempt. A phased endpoint also runs only within 2 seconds after its slot and once per slot; a missed slot waits for the next one.
• Phased feeds are captured first in each cycle, so other downloads cannot delay them.