5. Database

The project uses a single PostgreSQL database (import_db) for imported stop data, GTFS identity resolution, route comparison data, and problem rows.

The schema is disposable by design:

migrations run in the migrator container
importer truncates the selected table scope
current pipeline output is bulk-inserted

This keeps DB contents aligned with the latest output from matching_and_import_db/.

Database	Env Var	Behavior	Contains
Import DB (`import_db`)	`DATABASE_URI`	migration + scoped refresh on each import run	stop/identity tables, route source anchors, normalized route tables, route matches, and problems

Active Schema

The active ORM schema ([backend/models.py](https://github.com/openTdataCH/stop_sync_osm_atlas/blob/main/backend/models.py)) is organized into five groups.

1. Stop and Identity

atlas_operators
atlas_stops
gtfs_stops_raw
gtfs_stop_identity_resolution
osm_nodes
osm_stops
osm_stop_members
stops_matched
problems

2. Route Source Anchors

atlas_line_families
osm_route_relations

These are the only raw/source route tables still persisted. Legacy raw route detail tables are no longer part of the ORM schema.

3. Normalized Route Model

line_families
itineraries
stop_calls

These tables are the route model consumed by the web application.

4. Route Match Results

line_family_matches
itinerary_matches

5. Relationship Summary

erDiagram atlas_operators ||--o{ atlas_stops : atlas_business_org_abbr gtfs_stops_raw ||--o{ gtfs_stop_identity_resolution : stop_id atlas_stops ||--o{ gtfs_stop_identity_resolution : resolved_sloid osm_nodes ||--o{ osm_stops : representative_node_id osm_stops ||--o{ osm_stop_members : osm_stop_id osm_nodes ||--o{ osm_stop_members : node_id stops_matched ||--o{ problems : stop_id atlas_line_families ||--o{ line_families : atlas_line_id osm_route_relations ||--o{ line_families : representative_relation_id line_families ||--o{ itineraries : line_family_id itineraries ||--o{ stop_calls : itinerary_id atlas_stops ||--o{ stop_calls : source_sloid osm_nodes ||--o{ stop_calls : source_node_id line_families ||--o{ line_family_matches : atlas_line_family_id line_families ||--o{ line_family_matches : osm_line_family_id line_family_matches ||--o{ itinerary_matches : line_family_match_id itineraries ||--o{ itinerary_matches : atlas_itinerary_id itineraries ||--o{ itinerary_matches : osm_itinerary_id

Refresh Modes

Importer refresh scope comes from matching_and_import_db/database/importer.py.

`complete`

Truncates and rewrites all import tables.

`atlas_cached_bootstrap`

Used when ATLAS and GTFS preprocessing artifacts are reusable but the required static import tables are missing or empty.

truncates and rewrites the full import schema, like complete
repopulates the static ATLAS/GTFS tables so later atlas_cached runs can reuse them

`atlas_cached`

Reuses ATLAS/GTFS static tables and rewrites dynamic tables.

Required static tables:

atlas_stops
gtfs_stops_raw
gtfs_stop_identity_resolution
atlas_line_families

Rewritten dynamic tables:

osm_nodes
osm_stops
osm_stop_members
stops_matched
problems
osm_route_relations
line_families
itineraries
stop_calls
line_family_matches
itinerary_matches

See 7.4 Atlas-Cached Import Optimization for operational behavior.

Generating Report

Documentation