central/sql/migrations
malice 1d5548c24c
v0.10.0: ITD 511 official API adapter (events + advisories + cameras) (#85)
First official-state-DOT-API pattern landing. Two adapters in one PR:

- itd_511 (event-class): polls Events (60s) + Advisories (300s) from
  https://511.idaho.gov/api/v2/get/{event,alerts}. Decodes EncodedPolyline
  to LineString via the polyline lib (bookend LineString or Point fallback);
  ITD Severity string mapped None->1 / Minor->2 / Major->3 with
  IsFullClosure=true forcing 3 regardless; RecurrenceSchedules /
  Restrictions / DetourPolyline pass through unmodified. Advisories ship
  as structural pass-through under data.advisory since the upstream
  /alerts endpoint currently returns []; per-record try/except keeps a
  surprise shape from sinking the cycle when ITD posts its first one.

- itd_511_cameras (telemetry-class): polls Cameras (600s). One event per
  camera per UTC day; image URL passes straight through to <img src>.
  Region uniform US-ID with data.source_jurisdiction preserving the raw
  upstream Source field for the ~1.2% cross-DOT border-region mirrors
  (UDOT / ODOT / WYDOT / WSDOT / NDot / MTD / DriveBC / Lemhi County).

Subject convention (v0.9.20 forward): central.traffic.<event_type>.us.id
and central.traffic_cameras.us.id.<camera_id>. Castle Rock state_511_atis
keeps its bare-state subject; consumers stay on central.traffic.>
wildcards during the A/B comparison window.

Retry predicate tightened from the Castle Rock / TomTom precedent: 5xx +
connection / timeout retry; 4xx other than 429 skip-with-warn (don't
burn quota on permanent errors); 429 honors Retry-After once then
retries. API key (alias 'idaho_511') travels in the ?key= query string,
so every error log path runs through self._redact() to scrub the URL.

Both adapters ship disabled; operator enables via GUI after registering
the API key with 'python -m set_api_key idaho_511'. Reuses existing
CENTRAL_TRAFFIC and CENTRAL_TRAFFIC_CAMERAS streams -- no archive
restart needed.

Scope-cap exception: this PR is ~1.5k lines vs. the standard 500-line
cap, authorized as a one-time exception for the first
official-state-DOT-API pattern landing. Two adapters + their tests +
real-API fixtures naturally exceed the v0.9.x adapter-cap budget.

Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
2026-06-03 22:36:26 -06:00
..
001_create_config_schema.sql
002_add_updated_at_trigger_and_index.sql
003_add_streams_table.sql
004_nws_states_to_bbox.sql
005_add_firms_adapter.sql
006_add_usgs_quake_adapter.sql
007_add_config_system.sql
008_add_operators.sql
009_add_sessions.sql
010_add_audit_log.sql
011_events_add_adapter_column.sql
013_add_session_csrf_token.sql
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