Event correlation layer (derived, rebuildable weather_events)

claude-bot commented

2026-07-27 20:07:44 +00:00

Contributor

Parent: #22.

Key finding: the correlation spine already exists

SentAlert.nws_alert_id stores the VTEC-derived lifecycle id, not the raw NWS feature URN — alert_processor.py:910 assigns nws_alert_id=lifecycle_id, where alert_lifecycle_id (app/services/alert_identity.py:9-27) builds NWS_{office}_{phenomenon}_{significance}_{etn} from the P-VTEC.

That same string is already:

nws_alert_snapshots.lifecycle_id
public_alert_pages.lifecycle_id
half the radar snapshot filename ({lifecycle_id}__{location_id}.png)
the AI summary join key (ai_summary_records.source_id = f"{product_family}:{lifecycle_id}")

So the Explorer does not need a correlation layer built from nothing. It needs a grouping layer over keys that already line up. This is the single biggest de-risking of v2.0.0.

Available identifiers per product family

Family	Stable identifier	Quality
NWS VTEC alerts	lifecycle id above	Stable across CON/EXT/CAN updates
NWS non-VTEC	raw feature id fallback	Unstable across updates
SPC outlooks	4-tuple `(product_family, product_id, day, risk_label)` (`SPCOutlookPage` unique constraint)	`product_id` is timestamp-derived, so it changes every reissue — no native cross-issuance id exists
SPC MCDs	`SPCDiscussion.product_id` = the real SPC-assigned MCD number (`spc.py:408`)	Cleanest cross-family join key available
SPC lineage	`spc_outlook_risk_transitions`	Explicit from→to edges already recorded
Lightning	none	Correlate by location + time window only

Design constraint from #22

Keep raw snapshots separate from derived groupings. The grouping tables must be derived and rebuildable, never the source of truth, so the correlation algorithm can be improved and re-run without data loss.

Tasks

weather_events + weather_event_members (polymorphic member reference: kind + id), with a derivation_version column so a stale derivation can be detected and forced to rebuild.
Correlation pass grouping by location, time overlap, and product-family relationships — per the rules settled in the ambiguous-grouping decision issue.
Idempotent rebuild job that can re-derive any date range from the raw tables.
Reuse visible_location_filter(user) and can_view_location_with_db(db, user, location) from app/api/permissions.py exactly; do not reimplement the three-tier global/personal/shared check.
Backfill over existing retained history on first run.

Parent: #22. ## Key finding: the correlation spine already exists `SentAlert.nws_alert_id` stores the **VTEC-derived lifecycle id**, not the raw NWS feature URN — `alert_processor.py:910` assigns `nws_alert_id=lifecycle_id`, where `alert_lifecycle_id` (`app/services/alert_identity.py:9-27`) builds `NWS_{office}_{phenomenon}_{significance}_{etn}` from the P-VTEC. That same string is already: - `nws_alert_snapshots.lifecycle_id` - `public_alert_pages.lifecycle_id` - half the radar snapshot filename (`{lifecycle_id}__{location_id}.png`) - the AI summary join key (`ai_summary_records.source_id = f"{product_family}:{lifecycle_id}"`) So the Explorer does **not** need a correlation layer built from nothing. It needs a grouping layer over keys that already line up. This is the single biggest de-risking of v2.0.0. ## Available identifiers per product family | Family | Stable identifier | Quality | |---|---|---| | NWS VTEC alerts | lifecycle id above | Stable across CON/EXT/CAN updates | | NWS non-VTEC | raw feature id fallback | Unstable across updates | | SPC outlooks | 4-tuple `(product_family, product_id, day, risk_label)` (`SPCOutlookPage` unique constraint) | `product_id` is timestamp-derived, so it changes every reissue — no native cross-issuance id exists | | SPC MCDs | `SPCDiscussion.product_id` = the real SPC-assigned MCD number (`spc.py:408`) | Cleanest cross-family join key available | | SPC lineage | `spc_outlook_risk_transitions` | Explicit from→to edges already recorded | | Lightning | none | Correlate by location + time window only | ## Design constraint from #22 Keep raw snapshots separate from derived groupings. The grouping tables must be **derived and rebuildable**, never the source of truth, so the correlation algorithm can be improved and re-run without data loss. ## Tasks - [ ] `weather_events` + `weather_event_members` (polymorphic member reference: kind + id), with a `derivation_version` column so a stale derivation can be detected and forced to rebuild. - [ ] Correlation pass grouping by location, time overlap, and product-family relationships — per the rules settled in the ambiguous-grouping decision issue. - [ ] Idempotent rebuild job that can re-derive any date range from the raw tables. - [ ] Reuse `visible_location_filter(user)` and `can_view_location_with_db(db, user, location)` from `app/api/permissions.py` **exactly**; do not reimplement the three-tier global/personal/shared check. - [ ] Backfill over existing retained history on first run.

claude-bot added this to the v2.0.0 - Historical Weather Event Explorer milestone

2026-07-27 20:07:44 +00:00

claude-bot added the

v2.0.0

type:feature

labels

2026-07-27 20:07:44 +00:00

claude-bot referenced this issue

2026-07-27 20:09:21 +00:00

Historical Weather Event Explorer #22

claude-bot referenced this issue

2026-08-01 03:00:30 +00:00

Decide correlation rules for ambiguous product groupings #136

claude-bot referenced this issue

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

Manual merge/split of derived weather events (deferred from #136) #167

claude-bot referenced this issue

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

Decide correlation rules for ambiguous product groupings #136

claude-bot commented

2026-08-01 03:16:05 +00:00

Author

Contributor

Picking this up — unblocked by the ratified decisions on #136 (2026-08-01): cores-vs-context model, EVENT_GAP_MINUTES = 90 over W/Y/S-significance members with transitive closure and upgrade_from always-merge; watches/MCDs attach as context and never chain; watch-only and lightning-only events with event_kind; SPC outlooks stay a day-band (no membership); per-location events; manual merge/split deferred to #167. Interval = coalesce(onset, sent_at) → coalesce(cleared_at, expires, sent_at + 6h).

Implementation shape per the task list: weather_events + weather_event_members(member_kind, member_id, role ∈ {core, context}) with derivation_version = 1, a pure derivation pass, an idempotent ranged rebuild, a periodic trailing-window refresh job plus first-run backfill, and correlation-logic tests. PR to follow.

Picking this up — unblocked by the ratified decisions on #136 (2026-08-01): cores-vs-context model, `EVENT_GAP_MINUTES = 90` over W/Y/S-significance members with transitive closure and `upgrade_from` always-merge; watches/MCDs attach as `context` and never chain; watch-only and lightning-only events with `event_kind`; SPC outlooks stay a day-band (no membership); per-location events; manual merge/split deferred to #167. Interval = `coalesce(onset, sent_at)` → `coalesce(cleared_at, expires, sent_at + 6h)`. Implementation shape per the task list: `weather_events` + `weather_event_members(member_kind, member_id, role ∈ {core, context})` with `derivation_version = 1`, a pure derivation pass, an idempotent ranged rebuild, a periodic trailing-window refresh job plus first-run backfill, and correlation-logic tests. PR to follow.