Event correlation layer: derived, rebuildable weather_events (#135) #168

Merged

claude-bot merged 3 commits from feat/event-correlation-layer into main

2026-08-01 03:55:40 +00:00

claude-bot commented

2026-08-01 03:39:15 +00:00

Contributor

Closes #135. Implements the correlation rules ratified on #136 (2026-08-01).

What this adds

The first Explorer building block: weather_events + weather_event_members — derived, rebuildable tables (never source of truth) that group raw per-product rows into per-location timeline events.

Rules implemented exactly as decided on #136:

Cores vs. context: warnings/advisories/statements (VTEC significance W/Y/S, plus non-VTEC fallback ids) are core members; watches (A) and lightning attach as context and never chain or extend event bounds.
EVENT_GAP_MINUTES = 90 interval-merge sweep with transitive closure; upgrade_from lineage merges regardless of gap (union-find pass).
Watch overlapping zero hazard events → watch_only event; lightning episodes (same gap rule over cluster timestamps) attach to any overlapping event or form lightning_only events.
Interval = coalesce(onset, sent_at) → coalesce(cleared_at, expires, sent_at + 6h).
Title/peak-severity from highest-severity core member; derivation_version = 1.

Pieces: pure derivation logic (unit-testable, no DB) + rebuild_events() DB layer; migration 0034; a 30-minute scheduler job rebuilding the trailing 48 h per ready location, with automatic full-history backfill on first run (empty table). No API/UI — that's #137–#139.

Notable decisions (recorded here for review)

MCD attachment deferred: MCD SentAlert rows are channel-only (no dashboard record), so no per-location MCD association exists to read. Comment in the module; picked up with the day view (#138).
Lightning↔location uses the existing proximity definition (lightning_alert_radius_mi, haversine) — the only precedent in the codebase for "this location's lightning". No radius ⇒ no lightning-derived events.
Member deletes are explicit rather than relying on FK cascade, because the SQLite bulk tier doesn't enforce FK actions — keeps rebuild behavior identical across tiers.

Review pass (two fixes over the initial implementation)

Ranged rebuilds widen to existing event bounds. No fixed padding can bound a ≤90-min-gap chain — a multi-day flood succession would have been progressively truncated by the trailing-window job (deleted as range-intersecting, re-derived from a window missing its older members). The rebuild now extends the range to the bounds of previously derived events intersecting it before fetching/deleting, making the trailing-window job exact for incrementally growing events. Pinned by a test where the padded floor alone would drop the oldest chain member.
Lightning attaches to watch_only events too (per the #136 rule "any overlapping event") — a storm under a watch with no warnings corroborates the watch event instead of spawning a parallel lightning-only one.

Verification

Full bulk suite on the dev server: 886 passed (30 new correlation tests: gap boundaries at 89/91 min, transitive chaining, upgrade merges, watch non-chaining, watch-only, lightning radius/episodes/attachment, interval caps, titles, rebuild idempotency, ranged-rebuild padding + chain preservation).
ruff clean; alembic heads resolves to 0034 as sole head. CI's Postgres tier exercises the migration against real Postgres.
Migration adds two empty tables only (no changes to existing tables), so the empty-DB CI migration run is representative per [migration-real-db-verify].

🤖 Generated with Claude Code

Closes #135. Implements the correlation rules ratified on #136 (2026-08-01). ## What this adds The first Explorer building block: `weather_events` + `weather_event_members` — **derived, rebuildable tables** (never source of truth) that group raw per-product rows into per-location timeline events. **Rules implemented exactly as decided on #136:** - Cores vs. context: warnings/advisories/statements (VTEC significance W/Y/S, plus non-VTEC fallback ids) are *core* members; watches (A) and lightning attach as *context* and never chain or extend event bounds. - `EVENT_GAP_MINUTES = 90` interval-merge sweep with transitive closure; `upgrade_from` lineage merges regardless of gap (union-find pass). - Watch overlapping zero hazard events → `watch_only` event; lightning episodes (same gap rule over cluster timestamps) attach to any overlapping event or form `lightning_only` events. - Interval = `coalesce(onset, sent_at)` → `coalesce(cleared_at, expires, sent_at + 6h)`. - Title/peak-severity from highest-severity core member; `derivation_version = 1`. **Pieces:** pure derivation logic (unit-testable, no DB) + `rebuild_events()` DB layer; migration `0034`; a 30-minute scheduler job rebuilding the trailing 48 h per ready location, with automatic full-history backfill on first run (empty table). No API/UI — that's #137–#139. ## Notable decisions (recorded here for review) - **MCD attachment deferred**: MCD `SentAlert` rows are channel-only (no dashboard record), so no per-location MCD association exists to read. Comment in the module; picked up with the day view (#138). - **Lightning↔location** uses the existing proximity definition (`lightning_alert_radius_mi`, haversine) — the only precedent in the codebase for "this location's lightning". No radius ⇒ no lightning-derived events. - **Member deletes are explicit** rather than relying on FK cascade, because the SQLite bulk tier doesn't enforce FK actions — keeps rebuild behavior identical across tiers. ## Review pass (two fixes over the initial implementation) 1. **Ranged rebuilds widen to existing event bounds.** No fixed padding can bound a ≤90-min-gap *chain* — a multi-day flood succession would have been progressively truncated by the trailing-window job (deleted as range-intersecting, re-derived from a window missing its older members). The rebuild now extends the range to the bounds of previously derived events intersecting it before fetching/deleting, making the trailing-window job exact for incrementally growing events. Pinned by a test where the padded floor alone would drop the oldest chain member. 2. **Lightning attaches to `watch_only` events too** (per the #136 rule "any overlapping event") — a storm under a watch with no warnings corroborates the watch event instead of spawning a parallel lightning-only one. ## Verification - Full bulk suite on the dev server: **886 passed** (30 new correlation tests: gap boundaries at 89/91 min, transitive chaining, upgrade merges, watch non-chaining, watch-only, lightning radius/episodes/attachment, interval caps, titles, rebuild idempotency, ranged-rebuild padding + chain preservation). - `ruff` clean; `alembic heads` resolves to `0034` as sole head. CI's Postgres tier exercises the migration against real Postgres. - Migration adds two empty tables only (no changes to existing tables), so the empty-DB CI migration run is representative per [migration-real-db-verify]. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

claude-bot added 3 commits

2026-08-01 03:39:15 +00:00

Add event correlation layer: weather_events/weather_event_members (#135 ) 3146b3d75d

Derives correlated timeline events from raw dashboard SentAlert rows and
lightning clusters: gap/overlap merging of core hazards (with transitive
closure and upgrade_from links across a >90min gap), watch attachment as
context without extending event bounds, lightning episode grouping, and
watch-only/lightning-only standalone events. weather_events and
weather_event_members are derived/rebuildable tables (never source of
truth), rebuilt idempotently per-location by rebuild_events() with padded
raw-row queries so ranged rebuilds match a full rebuild at the boundaries.
Wired as a new 30-minute scheduler job (derive_weather_events_job) with a
full-history backfill on first run. MCD attachment is deferred (#138) since
no per-location MCD association exists yet -- MCD SentAlert rows are
channel-only with no dashboard record.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>

Review fixes: widen ranged rebuilds to existing event bounds; lightning attaches to watch_only events 8394e31119

Two corrections to the initial implementation:

- A ranged rebuild deleted any event intersecting the range and re-derived
  it from a fixed-padding window — but no fixed padding can bound a
  ≤90-min-gap chain, so the trailing-48h job would progressively truncate
  a multi-day event's older members. The rebuild now first widens the
  range to the bounds of existing derived events that intersect it, so
  the raw query, delete, and keep-filter all cover the whole chain.
  Exact for incrementally-growing events; cold ranged rebuilds should use
  a full rebuild (as the job's first-run backfill does).

- Lightning episodes attached only to hazard events; the #136 rule says
  they attach to any overlapping event, which includes watch_only — a
  storm under a watch that never produced a warning now corroborates the
  watch event instead of spawning a parallel lightning_only one.

Both behaviors are pinned by new tests.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

Normalize naive SQLite datetime read-backs in ranged-rebuild tests

CI / test (pull_request) Successful in 3m29s

Details

17c66ac2dc

DateTime(timezone=True) columns come back tz-naive on the SQLite bulk
tier; comparing them against aware expectations failed both ranged-
rebuild tests on real infrastructure while the derived instants were
correct.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

claude-bot merged commit 951173afbc into main

2026-08-01 03:55:40 +00:00

claude-bot deleted branch feat/event-correlation-layer

2026-08-01 03:55:40 +00:00

claude-bot referenced this pull request from a commit

2026-08-01 03:55:41 +00:00

Merge pull request 'Event correlation layer: derived, rebuildable weather_events (#135)' (#168) from feat/event-correlation-layer into main

claude-bot referenced this pull request

2026-08-01 07:25:10 +00:00

Historical Weather Event Explorer #22