The PostgreSQL schemas behind the pipeline, how migrations are managed and how to apply them safely.

Reference Value
Subject and boundary Schema layout, the Alembic baseline and lifecycle commands. Gold relation details are on ‣; reader roles on ‣.
Verified source 0093_alert_publication_provenance.py, 0094_worker_starts.py, 0095_trip_service_span.py, 0096_provider_live_policy.py, migration_guard.py, ci.yml at main 9733eb26d92533f56cdc8bc46b81b93fbb5987c8, checked November 10, 2026.
Support state Implemented schema source. Which revision a given database runs is a property of that database, checked with alembic current against it. The production database reported 0096_provider_live_policy to Worker Start Probe run 42841680539 on November 10, 2026.

Schemas

Schema Holds Examples
raw Capture lineage and Bronze object metadata ingestion_runs, ingestion_objects, realtime_snapshot_index, i3_alert_snapshots, alert observations
core Providers, endpoints, dataset versions and publication state providers, feed_endpoints, dataset_versions, snapshot_publish_state, snapshot_historic_receipts, snapshot_historic_gc_marks, worker_starts, route_live_observations
silver Normalized GTFS, GTFS-RT, GIS and i3 alerts routes, trips, stop_times, rt_feed_snapshots, rt_trip_updates, rt_vehicle_positions, gis_*, i3_alerts
gold Dimensions, facts, serving state, summaries and daily rollups fact_trip_delay_snapshot, trip_delay_summary_5m, route_delay_hourly, route_delay_spine, warm_rollup_periods, realtime_serving_state, trip_service_span
ops Reserved The baseline creates the schema without tables

Tables are owned by the module that writes them: ingestion for raw, silver for silver, gold for gold, snapshots for publication state in core, worker_starts.py for core.worker_starts.

Migrations

The repository carries one squashed baseline, 0093_alert_publication_provenance, and every new revision extends its single head. The head is 0096_provider_live_policy. Each revision after the baseline sets a 2-second lock timeout, grants transit_app the writes it needs and transit-db select when those roles exist, and revokes public access.

Revision Changes
0094_worker_starts Creates core.worker_starts: one row per provider with start_count and first and last start times. 15-second statement limit
0095_trip_service_span Creates gold.trip_service_span: first and last scheduled seconds per trip for each static dataset version, cascading with the version. Backfills it from current stop times (10-minute statement limit) and rewrites gold.scheduled_running_trips_at to read it. The static load refreshes the spans in its transaction
0096_provider_live_policy Adds core.providers.lines_without_live_data (a JSON array, default empty), creates core.route_live_observations (last live sighting per provider and declared route) and gold.routes_without_live_data(provider_id), seeds the STM metro and OC Transpo O-Train declarations, and rewrites gold.scheduled_running_trips_at to skip declared routes with no sighting in the last 15 minutes
Rule How it is enforced
One Alembic head ci.yml job alembic-single-head-work
The full chain applies to an empty database CI runs alembic upgrade head on a disposable PostGIS service before the real-database suite
Applied revisions are immutable Schema changes use a separate revision
Remote targets need explicit intent assert_explicit_remote_url refuses a remote URL that came only from .env; set DATABASE_URL in the process environment
Tests never touch production target_safety.py accepts only the approved disposable role and database pairs 
cd apps/db
uv run alembic heads
DATABASE_URL='<explicit target>' uv run transit-ops init-db     # upgrade to head
DATABASE_URL='<same target>' uv run transit-ops seed-core       # providers and endpoints

seed-core records every manifest, including inactive ones, so provider state stays explicit. The Daily Static and Warm workflows run init-db before their work, so a merged migration is applied by the next scheduled run. Upgrade schema and writers together.

Some constraints are added NOT VALID so retained rows are not rechecked during install. After the bounded recover-delay-sums run described on Recovery, backups and retention, validate ck_trip_delay_5m_usable_sum, ck_route_delay_hourly_usable_count and ck_route_delay_hourly_usable_sum with ALTER TABLE ... VALIDATE CONSTRAINT.

Provenance carried into the contract

Each published value keeps enough context to be interpreted: provider, source window, observation counts, freshness, methodology version where relevant, capability state and an explicit reason when no answer exists. The public contract is built from Gold and Silver by the snapshot builders; it is never a table dump.