User Story
As a backend engineer, I need station_star_systems and station_orbits populated from UEX API data so that all planetary bodies, stations, and jump points have valid parent FK references in the location hierarchy.
Definition of Done
Acceptance Criteria
- After ETL:
SELECT count(*) FROM station_star_systems matches UEX star systems count
- After ETL: all
station_orbits rows have a non-null star_system_id FK
- An orbit with an unknown
id_star_system emits a warning row and is not inserted
- Re-running ETL does not duplicate rows
Technical Elaboration
UEX Endpoints
GET /star_systems — { id, name, code, is_available, is_available_live, is_default }GET /orbits — { id, name, code, id_star_system, is_available, is_available_live, ... }GET /orbit_distances — { id_orbit_origin, id_orbit_destination, distance_km, travel_time_s, ... }
Target Schema
station_star_systems: uex_id INT UNIQUE, name VARCHAR(255), code VARCHAR(50), is_available BOOLEAN, is_available_live BOOLEAN, is_default BOOLEAN
station_orbits: uex_id INT UNIQUE, name VARCHAR(255), code VARCHAR(50), star_system_id UUID FK → station_star_systems, is_available BOOLEAN, is_available_live BOOLEAN
station_orbit_distances: origin_orbit_id UUID FK → station_orbits, destination_orbit_id UUID FK → station_orbits, distance_km DECIMAL(15,2), travel_time_s INTEGER, PRIMARY KEY (origin_orbit_id, destination_orbit_id)
FK Resolution Pattern
Before inserting orbits, build an in-memory map: Map<uexId: number, uuid: string> from the just-inserted station_star_systems. Resolve id_star_system against this map. If not found, emit warning and skip.
For station_orbit_distances, both FK sides must resolve. Skip and warn if either is missing.
Step Order Dependency
OrbitsSyncStep must run after StarSystemsSyncStep completes. If StarSystemsSyncStep failed, OrbitsSyncStep should detect an empty station_star_systems table and emit a top-level warning rather than silently inserting orphan rows.
Design Elaboration
station_orbits is the central node of the entire location hierarchy — every physical body (planet, moon, city, station, outpost) references an orbit. Getting this table fully populated and FK-correct before running any downstream steps is critical. The in-memory UUID map pattern avoids N+1 queries during bulk upsert and is fast enough for the expected record counts (~200 orbits across all star systems).
Orbit distances are supplementary data used for route planning features (future). They are populated in this step because they are logically part of the orbit data, but a failure here should produce severity='warn' warnings only — not block the rest of the ETL.
Depends on: #188, #189
User Story
As a backend engineer, I need
station_star_systemsandstation_orbitspopulated from UEX API data so that all planetary bodies, stations, and jump points have valid parent FK references in the location hierarchy.Definition of Done
StarSystemsSyncStepETL step created and registered at tier-2 positionOrbitsSyncStepETL step created and registered at tier-3 positionstation_star_systemspopulated with idempotent upsert byuex_idstation_orbitspopulated with idempotent upsert byuex_id, with FKstar_system_idresolved fromstation_star_systemsstation_orbit_distancespopulated from the orbit distances endpointstation_etl_warningsand skippedpnpm testpassesAcceptance Criteria
SELECT count(*) FROM station_star_systemsmatches UEX star systems countstation_orbitsrows have a non-nullstar_system_idFKid_star_systememits a warning row and is not insertedTechnical Elaboration
UEX Endpoints
GET /star_systems—{ id, name, code, is_available, is_available_live, is_default }GET /orbits—{ id, name, code, id_star_system, is_available, is_available_live, ... }GET /orbit_distances—{ id_orbit_origin, id_orbit_destination, distance_km, travel_time_s, ... }Target Schema
station_star_systems:uex_id INT UNIQUE,name VARCHAR(255),code VARCHAR(50),is_available BOOLEAN,is_available_live BOOLEAN,is_default BOOLEANstation_orbits:uex_id INT UNIQUE,name VARCHAR(255),code VARCHAR(50),star_system_id UUID FK → station_star_systems,is_available BOOLEAN,is_available_live BOOLEANstation_orbit_distances:origin_orbit_id UUID FK → station_orbits,destination_orbit_id UUID FK → station_orbits,distance_km DECIMAL(15,2),travel_time_s INTEGER, PRIMARY KEY(origin_orbit_id, destination_orbit_id)FK Resolution Pattern
Before inserting orbits, build an in-memory map:
Map<uexId: number, uuid: string>from the just-insertedstation_star_systems. Resolveid_star_systemagainst this map. If not found, emit warning and skip.For
station_orbit_distances, both FK sides must resolve. Skip and warn if either is missing.Step Order Dependency
OrbitsSyncStepmust run afterStarSystemsSyncStepcompletes. IfStarSystemsSyncStepfailed,OrbitsSyncStepshould detect an emptystation_star_systemstable and emit a top-level warning rather than silently inserting orphan rows.Design Elaboration
station_orbitsis the central node of the entire location hierarchy — every physical body (planet, moon, city, station, outpost) references an orbit. Getting this table fully populated and FK-correct before running any downstream steps is critical. The in-memory UUID map pattern avoids N+1 queries during bulk upsert and is fast enough for the expected record counts (~200 orbits across all star systems).Orbit distances are supplementary data used for route planning features (future). They are populated in this step because they are logically part of the orbit data, but a failure here should produce
severity='warn'warnings only — not block the rest of the ETL.Depends on: #188, #189