central/sql/migrations
malice 3f1fec9846
v0.13.0: sat_orbits adapter (forward-orbit-track per satellite) + antimeridian splitter
## Matt's "each sat's path" framing

After enabling the satellite family in v0.12.1, the `/events` map showed overlapping orange visibility-footprint circles from satpass_predict + a polar-orbit ground track wrapping the wrong way across the antimeridian (the v0.11.2 documented limitation). Matt's ask:

> honestly i just want each sats path.

Interpreted as: one continuous orbital track per satellite, color-coded, no observer-specific clutter, no visibility-footprint overlays. Six tracked sats = six distinguishable lines on the map.

## Family placement — global line counterpart to global points

| Adapter | What it publishes | Geometry | Cadence |
|---|---|---|---|
| satpass_predict (v0.11.1) | Observer-anchored pass alerts | LineString ground-track + Polygon footprint per pass | 1h |
| sat_positions (v0.12.0) | Current sub-sat POINT per sat | Point centroid only | 60s |
| **sat_orbits (this PR)** | Forward-orbit LINE per sat | LineString / MultiLineString, 90min horizon | 5min |

Each answers a different question; they complement.

## Antimeridian splitter — shared sat_common primitive

`split_antimeridian(coords)` lives in `sat_common.py` next to `gmst_rad` / `eci_to_ecef` / `subsatellite_point`. Returns `None` for <2 vertices, a `LineString` dict for the common no-crossing case, or a `MultiLineString` dict when one or more ±180° crossings exist. Each crossing closes the current segment at `sign(prev_lon)*180` with a linearly-interpolated latitude and starts the next at `sign(cur_lon)*180` with the same lat (sub-0.1° error at LEO orbital speeds, well below Leaflet rendering precision).

**Sibling concern fixed:** `satpass_predict._build_pass_geometry` now routes its `ground_track` through `split_antimeridian` too. This was the v0.11.2 documented limitation ("polar-orbit crossings near ±180° will produce a polygon that visually wraps the wrong way"). Sat_orbits and satpass_predict share the helper because the antimeridian problem is identical for both — and **44/44 existing satpass_predict tests still pass** because the splitter returns a LineString identical in shape to the prior inline construction when there's no crossing (which is the case for every CONUS-observer ISS-fixture test).

New test specifically for the splitter inside `_build_pass_geometry`: synthesized polar-orbit `ground_track` produces a `GeometryCollection` whose linear-geometry component is a `MultiLineString` with 2 segments (first ends at +180, second starts at -180).

## GUI per-NORAD-ID color helper

20-line addition to `events_list.html`:

```js
function orbitColorForNoradId(norad) {
    var hue = (norad * 137.508) % 360;  // golden-angle hue distribution
    return "hsl(" + hue.toFixed(1) + ", 70%, 50%)";
}
function getRowColor(adapter, row) {
    if (adapter === "tomtom_flow") return flowColor(row.dataset.severity);
    if (adapter === "sat_orbits") {
        var norad = parseInt((row.dataset.eventId || "").split(":")[0], 10);
        if (!isNaN(norad)) return orbitColorForNoradId(norad);
    }
    return getAdapterColor(adapter);
}
```

`event_id` shape is `<norad_id>:<iso>` (same as sat_positions), so JS reads the first colon-token. **Additive**: tomtom_flow keeps its severity-based color, every other adapter keeps its per-adapter palette color, sat_orbits gets per-satellite distinguishable lines.

## Phase A sanity (per spec)

```
vertices = 91                                      ✓ (90min @ 60s + 1 endpoint)
first vertex = (170.66°, -17.15°, 417.4km)        ✓ matches v0.11.1 ISS pin
last vertex  = (140.52°, -8.60°, 415.9km)         ✓ geographically distinct
antimeridian crossings in 90min track = 1
geometry type = MultiLineString, 2 segments        ✓ splitter integrates
```

## Diff size

**+838 / −9 = +829 net** across 15 files. Spec budget was ≤800 lines. **29 over** — much tighter than v0.12.0 (894) or v0.12.1 (848). Adapter LoC 275 (well under 350 cap). sat_common splitter 51 LoC (~budget).

Test breakdown: 285 (sat_orbits) + 60 (sat_common splitter) + 26 (satpass regression) + 12 (events_feed) + 4 (telemetry-separation) = 387 LoC tests. Production: 275 + 51 + 37 (migration) + 41 (doc) + 16 (partials) + 21 (JS) + 15 (satpass refactor) + 2 (wiring) = 458 LoC.

## Test plan

- [x] `pytest tests/test_sat_orbits.py` — 19 new tests, all pass.
- [x] `pytest tests/test_sat_common.py` — 7 new splitter tests, 16 total pass.
- [x] `pytest tests/test_satpass_predict.py` — **45/45 pass** (44 existing regression-guard + 1 new polar-orbit splitter integration test). The `_build_pass_geometry` rewire is byte-identical for non-crossing tracks.
- [x] `pytest tests/test_events_feed_frontend.py` — 125/125 pass (sat_orbits sample + expected subject extended).
- [x] `pytest tests/test_telemetry_separation.py` — 9/9 pass (`_TELEMETRY` pin extended with `sat_orbits`).
- [x] `pytest tests/test_consumer_doc.py` — 6/6 pass (new `### sat_orbits` subsection accepted).
- [x] Full sweep `pytest tests/` (excluding postgres-dep files): **1274 passed, 1 skipped, 0 failures**.
- [x] Ruff: clean on all new + touched satellite-family code.

## Deploy plan

1. Squash-merge PR #N → tag v0.13.0 at merge SHA → push tag.
2. `ssh central`, `git pull` on `/opt/central`. **No `uv sync`** (no new dep).
3. Apply migration 041 manually via psql (per option C):
   `sudo -u postgres psql central -f /opt/central/sql/migrations/041_add_sat_orbits_adapter.sql`
4. `sudo systemctl restart central-supervisor` (picks up new adapter + STREAM_CATEGORY_DOMAINS extension) + `sudo systemctl restart central-gui` (picks up new partials + ADAPTER_GROUPS extension + JS color helper).
5. **No** `central-archive` restart (CENTRAL_SAT pre-existed; only the category-domain tuple grew, archive already covers `central.sat.>`).
6. Verify: `config.adapters` has `sat_orbits` row with `enabled=false`; supervisor log shows discovery; no polling until Matt flips it.
7. Matt enables via `/adapters/sat_orbits/edit` when ready. First poll happens within 5min; orbit-track LineStrings surface at `/telemetry` filtered by adapter=sat_orbits, color-coded per NORAD ID.

## Halt acknowledgment

Per spec acceptance bar #6: **squash-merge NOT authorized**. Branch + PR open. Halting for line-by-line review.

🤖 Generated with [Claude Code](https://claude.com/claude-code)
2026-06-09 18:50:47 -06:00
..
001_create_config_schema.sql chore: normalize line endings to LF 2026-05-16 22:26:12 +00:00
002_add_updated_at_trigger_and_index.sql feat(db): add migration 002 for updated_at trigger and enabled index 2026-05-16 01:36:30 +00:00
003_add_streams_table.sql chore: normalize line endings to LF 2026-05-16 22:26:12 +00:00
004_nws_states_to_bbox.sql chore: normalize line endings to LF 2026-05-16 22:26:12 +00:00
005_add_firms_adapter.sql feat(schema): add FIRMS adapter and CENTRAL_FIRE stream 2026-05-16 19:58:20 +00:00
006_add_usgs_quake_adapter.sql feat(schema): add USGS quake adapter and CENTRAL_QUAKE stream 2026-05-16 20:51:28 +00:00
007_add_config_system.sql feat(gui): add auth core, setup gate, and first-run operator creation 2026-05-17 05:30:49 +00:00
008_add_operators.sql feat(gui): add auth core, setup gate, and first-run operator creation 2026-05-17 05:30:49 +00:00
009_add_sessions.sql feat(gui): add auth core, setup gate, and first-run operator creation 2026-05-17 05:30:49 +00:00
010_add_audit_log.sql feat(gui): add auth core, setup gate, and first-run operator creation 2026-05-17 05:30:49 +00:00
011_events_add_adapter_column.sql feat(schema): add adapter column to events, drop source 2026-05-17 16:09:59 +00:00
013_add_session_csrf_token.sql feat(gui): implement first-run setup wizard (1b-8) (#24) 2026-05-17 22:06:22 -06:00
014_events_time_id_index.sql feat(api): add paginated events feed JSON endpoint (#25) 2026-05-17 22:31:00 -06:00
015_add_adapters_last_error.sql refactor(gui): clean up flagged issues before merge 2026-05-18 23:55:34 +00:00
016_add_wfigs_adapters.sql feat(2-B): add NIFC WFIGS adapters for incidents and perimeters 2026-05-19 02:47:26 +00:00
017_add_inciweb_adapter.sql feat(2-C): add NIFC InciWeb wildfire narrative adapter 2026-05-19 03:19:25 +00:00
018_add_swpc_adapters.sql feat(2-D): add NOAA SWPC space weather adapters (alerts, kindex, protons) 2026-05-19 05:55:29 +00:00
019_add_central_space_stream.sql feat(2-D): add NOAA SWPC space weather adapters (alerts, kindex, protons) 2026-05-19 05:55:29 +00:00
020_add_gdacs_adapter.sql feat(2-E): GDACS disaster adapter 2026-05-19 06:58:52 +00:00
021_add_central_disaster_stream.sql feat(2-E): GDACS disaster adapter 2026-05-19 06:58:52 +00:00
022_add_eonet_adapter.sql feat(2-F): NASA EONET disaster adapter 2026-05-19 15:35:25 +00:00
023_add_nwis_adapter_and_hydro_stream.sql feat(2-G): USGS NWIS adapter (OGC API) + CENTRAL_HYDRO stream 2026-05-19 16:50:21 +00:00
024_add_config_enrichment.sql feat(3-K.5): operator-settable EnrichmentConfig (config plumbing) 2026-05-20 18:52:22 +00:00
025_add_wzdx_adapter_and_traffic_stream.sql feat(wzdx): WZDx adapter + CENTRAL_TRAFFIC family bootstrap (v0.9.0) 2026-05-25 20:35:08 +00:00
026_add_state_511_atis_adapter.sql feat(state_511_atis): Castle Rock 511 adapter — Idaho incidents/closures/road work (v0.9.2) 2026-05-25 22:01:11 +00:00
027_add_tomtom_flow_adapter_and_flow_stream.sql feat(tomtom_flow): TomTom Orbis vector flow-tile telemetry adapter + CENTRAL_TRAFFIC_FLOW (v0.9.3) 2026-05-25 23:25:44 +00:00
028_add_tomtom_incidents_adapter.sql feat(tomtom_incidents): TomTom real-time traffic incidents adapter (v0.9.5) 2026-05-26 00:25:27 +00:00
029_add_state_511_atis_cameras_adapter.sql feat(state_511_atis_cameras): Castle Rock 511 traffic cameras telemetry (v0.9.6) 2026-05-26 01:33:21 +00:00
030_add_monitoring_area.sql v0.9.12: archive-level monitoring-area bbox filter 2026-05-26 23:40:17 +00:00
031_add_itd_511_adapters.sql v0.10.0: ITD 511 official API adapter (events + advisories + cameras) (#85) 2026-06-03 22:36:26 -06:00
032_remove_state_511_atis_adapters.sql v0.10.3.1: soft-disable state_511_atis* adapters instead of DELETE (FK blocked v0.10.3 migration) (#90) 2026-06-06 18:39:33 -06:00
033_soft_disable_state_511_atis_adapters.sql v0.10.3.1: soft-disable state_511_atis* adapters instead of DELETE (FK blocked v0.10.3 migration) (#90) 2026-06-06 18:39:33 -06:00
034_widen_monitoring_area_default_to_full_idaho.sql v0.10.9: widen monitoring-area default to cover all of Idaho (49.0N) (#97) 2026-06-08 01:42:59 -06:00
035_add_central_avy_stream.sql v0.10.10: new avalanche_org adapter — backcountry avalanche advisories (#98) 2026-06-08 21:57:56 -06:00
037_add_celestrak_tle_adapter.sql v0.11.0: new celestrak_tle adapter + CENTRAL_SAT satellite-tracking stream (#100) 2026-06-09 00:54:19 -06:00
038_add_satpass_predict_adapter.sql v0.11.1: satpass_predict adapter (server-side pass alerts for fixed observers) (#101) 2026-06-09 01:16:43 -06:00
039_add_sat_positions_adapter.sql v0.12.0: sat_positions adapter (live global satellite positions) + sat_common refactor 2026-06-09 15:23:32 -06:00
040_add_n2yo_visualpasses_adapter.sql v0.12.1: n2yo_visualpasses adapter (server-side visible-pass alerts) 2026-06-09 16:00:55 -06:00
041_add_sat_orbits_adapter.sql v0.13.0: sat_orbits adapter (forward-orbit-track per satellite) + antimeridian splitter 2026-06-09 18:50:47 -06:00