rbrooks/WeatherBot

Watch

Fork 0

Unified 13-month history horizon across all history tables #133

New issue

Closed

opened 2026-07-27 20:07:17 +00:00 by claude-bot · 1 comment

claude-bot commented

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

Contributor

Parent: #22.

Retention windows currently disagree, so an older event renders with holes:

Data	Window	Basis
`sent_alerts`	90 days	`cleared_at`
`nws_alert_snapshots`	180 days	`received_at`
`spc_outlook_pages` (+ impacts, transitions)	`retained_until` (= expires + 48h) + 30-day buffer	`retained_until`
`ai_summary_records` / `_attempts`	30 days	`created_at`
radar snapshot PNGs	transitive on `sent_alerts`	—

An event 100 days old keeps its alert row and its snapshot but has already lost its AI summary and its SPC context. History would visibly rot in inconsistent, confusing ways.

Decision taken: one unified 13-month horizon, so year-over-year comparison ("this July vs last July") works and every product family ages out together.

Tasks

Single HISTORY_HORIZON_DAYS (default ~400) from which per-table windows derive. Keep per-table overrides for anything that genuinely must differ — AI payloads are large, so a shorter override with a stated reason is fine, but it must be a decision rather than an accident.
Fix the sent_alerts basis. Pruning on cleared_at means a row that never gets cleared is never pruned. Prune on a coalesce of cleared_at / expires / sent_at.
Bound the currently unpruned tables — notification_deliveries, product_freshness_samples, sent_forecasts, and redirect_tokens all grow without limit today (retention.py:171-178).
Size the result: estimate rows and bytes at 13 months for a representative location count; post it here.
Revisit whether spc_outlook_pages.product_text (full narrative, stored per issuance) should be retained at full horizon or truncated — SPC reissues frequently, so this is likely the largest avoidable share of the growth.

Parent: #22. Retention windows currently disagree, so an older event renders with holes: | Data | Window | Basis | |---|---|---| | `sent_alerts` | 90 days | `cleared_at` | | `nws_alert_snapshots` | 180 days | `received_at` | | `spc_outlook_pages` (+ impacts, transitions) | `retained_until` (= expires + 48h) + 30-day buffer | `retained_until` | | `ai_summary_records` / `_attempts` | 30 days | `created_at` | | radar snapshot PNGs | transitive on `sent_alerts` | — | An event 100 days old keeps its alert row and its snapshot but has already lost its AI summary and its SPC context. History would visibly rot in inconsistent, confusing ways. **Decision taken: one unified 13-month horizon**, so year-over-year comparison ("this July vs last July") works and every product family ages out together. ## Tasks - [ ] Single `HISTORY_HORIZON_DAYS` (default ~400) from which per-table windows derive. Keep per-table overrides for anything that genuinely must differ — AI payloads are large, so a shorter override with a stated reason is fine, but it must be a decision rather than an accident. - [ ] Fix the `sent_alerts` basis. Pruning on `cleared_at` means a row that never gets cleared is never pruned. Prune on a coalesce of `cleared_at` / `expires` / `sent_at`. - [ ] Bound the currently unpruned tables — `notification_deliveries`, `product_freshness_samples`, `sent_forecasts`, and `redirect_tokens` all grow without limit today (`retention.py:171-178`). - [ ] Size the result: estimate rows and bytes at 13 months for a representative location count; post it here. - [ ] Revisit whether `spc_outlook_pages.product_text` (full narrative, stored per issuance) should be retained at full horizon or truncated — SPC reissues frequently, so this is likely the largest avoidable share of the growth.

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

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

claude-bot added the

type:maintenance

v2.0.0

labels

2026-07-27 20:07:17 +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-07-27 23:16:15 +00:00

Decouple historical alert-snapshot capture from public-page gating #129

claude-bot referenced this issue

2026-07-28 00:00:53 +00:00

Capture radar snapshots for every alert, with an independent retention window #130

claude-bot referenced this issue

2026-07-28 06:12:58 +00:00

Archive ENTLN lightning clusters (#132) #156

claude-bot referenced this issue

2026-07-28 14:26:25 +00:00

Lightning cluster archive #132

claude-bot commented

2026-07-28 14:34:37 +00:00

Author

Contributor

Measured production storage — 2026-07-28 14:30 UTC

Queried prod directly (12d47dd, migration 0032 applied) rather than reusing the arithmetic estimates on #130/#132. Total database: 101 MB.

Table	Rows	Heap	Index	Live data	Bytes/row	Current window
`product_freshness_samples`	155,672	32 MB	31 MB	24 MB	164 B	14 d (inline)
`sent_alerts`	1,708	21 MB	7.0 MB	1,023 kB	613 B	90 d on `cleared_at`
`spc_outlook_pages`	7	136 kB	160 kB	18 kB text	2,614 B text	`retained_until` + 30 d
`notification_deliveries`	32	112 kB	64 kB	—	—	none
`sent_forecasts`	93	56 kB	96 kB	—	—	pruned by `sent_date`
`nws_alert_snapshots`	3	32 kB	112 kB	—	—	180 d
`alert_radar_frames`	31	16 kB	80 kB	—	—	400 d
`lightning_clusters`	0	32 kB	—	—	—	90 d
`redirect_tokens`	0	48 kB	—	—	—	none

Disk: radar_cache 4.4 MB total, of which alert_snapshots is 216 kB across 31 PNGs → ~7.1 kB per radar frame.

Four findings that change this issue's task list

1. Two of the four "unpruned" tables are already pruned. The task says notification_deliveries, product_freshness_samples, sent_forecasts and redirect_tokens all grow without limit. Measured:

product_freshness_samples is pruned at 14 days, inline at observability.py:108-110 (SAMPLE_RETENTION_DAYS), not via retention.py. Its oldest row is exactly 14.0 days old — the prune is working.
sent_forecasts is pruned by sent_date at forecast_delivery.py:45.
Genuinely unbounded: notification_deliveries (32 rows) and redirect_tokens (0 rows) — both trivially small today.

2. Task 5's hypothesis is disproved. spc_outlook_pages.product_text averages 2,614 bytes per issuance — 18 kB across all 7 pages. It is not "the largest avoidable share of the growth"; it is negligible. The 296 kB table is index and heap overhead, not narrative text. Truncating it would save nothing. Recommend closing that task as measured-and-rejected.

3. The cleared_at basis bug is real and quantified. 225 of 1,708 rows (13.2%) have cleared_at IS NULL and are therefore immortal under the current basis. Under a coalesce(cleared_at, expires, sent_at) basis, 67 additional rows would be prunable at the current 90-day window today.

4. The largest table is not history, and sent_alerts is mostly bloat. Two things the estimates missed entirely:

product_freshness_samples is 62% of the database — and it is observability, not history. 11,119 rows/day, 78% of it spc_fetch (121,007 rows; then nws_alerts 20,149, notifier_delivery 5,937, media 5,187, nws_forecast 3,392). Its indexes cost as much as its data (31 MB vs 32 MB) — five indexes on a 164-byte row. Folding this into a 400-day horizon would take it from 63 MB to roughly 1.8 GB. It must stay excluded.
sent_alerts holds 1,023 kB of live data in a 21 MB heap — roughly 20× bloat, from UPDATE churn (cleared_at, escalation_count, digest_pending, acknowledged_at all update in place). Last autovacuum was 2026-07-22. This is an autovacuum/VACUUM FULL matter, not a retention one, and it is worth knowing before we raise this table's window.

Correcting the estimates on #130 and #132

#130 (~165 MB/location/year) is an overestimate, by roughly 1.5–2.3×. Measured inputs: 839 dashboard records over 92 days across 5 locations = 1.82 alert events/day/location ≈ 666/year; ~7.1 kB/frame; frames average 15.5 per event against the alert_radar_frame_max_per_event = 24 cap.

At the measured average: 666 × 15.5 × 7.1 kB ≈ 73 MB/location/year
At the 24-frame ceiling: 666 × 24 × 7.1 kB ≈ 113 MB/location/year

Both sit below 165 MB. Caveat: the frames-per-event average rests on n=2 events, since capture only ungated at 03:50 UTC today. The event rate (839 records / 92 days) is solid; the frames-per-event figure is not yet.

#132 (~25–50 MB/year/region) remains unmeasurable. lightning_clusters is at 0 rows — the archive deployed at 14:27 UTC today and poll_lightning_job is running and fetching successfully, but the feed currently returns no strikes near the single opted-in location. Only 1 of 5 locations has lightning_alert_radius_mi set, so archive coverage is one location until more opt in.

What this does and doesn't unblock

The capture tables have 3 snapshots, 31 frames and 0 lightning clusters — 10.6 hours of history, not a storm season. So the "size the result at 13 months" task cannot be completed today for the capture tables, and I am not going to manufacture a projection that looks measured but isn't. It needs a real convective season; I'd revisit in the autumn.

What is settled enough to act on now is the structural work, which does not depend on that sizing:

Single HISTORY_HORIZON_DAYS (default 400) from which per-table windows derive.
Fix the sent_alerts basis → coalesce(cleared_at, expires, sent_at). Confirmed real, 225 rows affected.
Bound notification_deliveries and redirect_tokens — the two genuinely unbounded tables.
Keep deliberate, documented overrides: product_freshness_samples at 14 d (observability, not history — 400 d would cost ~1.8 GB), lightning_clusters at 90 d (volume; unmeasured), public_tokens at 30 d (token hygiene, not history).

One caveat on raising sent_alerts 90 → 400 d: the table then stops pruning entirely for ~10 months (its oldest row is 92 days old, so stale_400 is currently 0) while continuing to accrue ~9.1 rows/day. That is the intended consequence of the 13-month decision, but combined with the 20× bloat above it means autovacuum behaviour on this table is worth watching after the change.

Also worth a look (not this issue)

sent_alerts.radar_snapshot_path is NULL on all 1,708 rows, while alert_radar_frames has 31. Most likely benign — both frame-bearing alerts were already active when capture ungated at 03:50, so the first-fire moment had passed and only periodic frames accrued. But if it is still 0 after a fresh alert fires post-deploy, that is a #85 regression. Flagging to re-check rather than asserting a bug.

## Measured production storage — 2026-07-28 14:30 UTC Queried prod directly (`12d47dd`, migration `0032` applied) rather than reusing the arithmetic estimates on #130/#132. **Total database: 101 MB.** | Table | Rows | Heap | Index | Live data | Bytes/row | Current window | |---|---:|---:|---:|---:|---:|---| | `product_freshness_samples` | 155,672 | 32 MB | 31 MB | 24 MB | 164 B | 14 d (inline) | | `sent_alerts` | 1,708 | 21 MB | 7.0 MB | 1,023 kB | 613 B | 90 d on `cleared_at` | | `spc_outlook_pages` | 7 | 136 kB | 160 kB | 18 kB text | 2,614 B text | `retained_until` + 30 d | | `notification_deliveries` | 32 | 112 kB | 64 kB | — | — | **none** | | `sent_forecasts` | 93 | 56 kB | 96 kB | — | — | pruned by `sent_date` | | `nws_alert_snapshots` | 3 | 32 kB | 112 kB | — | — | 180 d | | `alert_radar_frames` | 31 | 16 kB | 80 kB | — | — | 400 d | | `lightning_clusters` | 0 | 32 kB | — | — | — | 90 d | | `redirect_tokens` | 0 | 48 kB | — | — | — | **none** | Disk: `radar_cache` 4.4 MB total, of which `alert_snapshots` is 216 kB across 31 PNGs → **~7.1 kB per radar frame**. ## Four findings that change this issue's task list **1. Two of the four "unpruned" tables are already pruned.** The task says `notification_deliveries`, `product_freshness_samples`, `sent_forecasts` and `redirect_tokens` all grow without limit. Measured: - `product_freshness_samples` is pruned at **14 days**, inline at [observability.py:108-110](app/services/observability.py#L108-L110) (`SAMPLE_RETENTION_DAYS`), not via `retention.py`. Its oldest row is exactly 14.0 days old — the prune is working. - `sent_forecasts` is pruned by `sent_date` at [forecast_delivery.py:45](app/services/forecast_delivery.py#L45). - Genuinely unbounded: **`notification_deliveries` (32 rows) and `redirect_tokens` (0 rows)** — both trivially small today. **2. Task 5's hypothesis is disproved.** `spc_outlook_pages.product_text` averages **2,614 bytes per issuance** — 18 kB across all 7 pages. It is not "the largest avoidable share of the growth"; it is negligible. The 296 kB table is index and heap overhead, not narrative text. Truncating it would save nothing. Recommend closing that task as measured-and-rejected. **3. The `cleared_at` basis bug is real and quantified.** **225 of 1,708 rows (13.2%) have `cleared_at IS NULL`** and are therefore immortal under the current basis. Under a `coalesce(cleared_at, expires, sent_at)` basis, **67 additional rows** would be prunable at the current 90-day window today. **4. The largest table is not history, and `sent_alerts` is mostly bloat.** Two things the estimates missed entirely: - `product_freshness_samples` is **62% of the database** — and it is observability, not history. 11,119 rows/day, 78% of it `spc_fetch` (121,007 rows; then `nws_alerts` 20,149, `notifier_delivery` 5,937, `media` 5,187, `nws_forecast` 3,392). Its **indexes cost as much as its data** (31 MB vs 32 MB) — five indexes on a 164-byte row. Folding this into a 400-day horizon would take it from 63 MB to roughly **1.8 GB**. It must stay excluded. - `sent_alerts` holds **1,023 kB of live data in a 21 MB heap — roughly 20× bloat**, from UPDATE churn (`cleared_at`, `escalation_count`, `digest_pending`, `acknowledged_at` all update in place). Last autovacuum was 2026-07-22. This is an autovacuum/`VACUUM FULL` matter, not a retention one, and it is worth knowing before we raise this table's window. ## Correcting the estimates on #130 and #132 **#130 (~165 MB/location/year) is an overestimate, by roughly 1.5–2.3×.** Measured inputs: 839 dashboard records over 92 days across 5 locations = **1.82 alert events/day/location ≈ 666/year**; **~7.1 kB/frame**; frames average **15.5 per event** against the `alert_radar_frame_max_per_event = 24` cap. - At the measured average: 666 × 15.5 × 7.1 kB ≈ **73 MB/location/year** - At the 24-frame ceiling: 666 × 24 × 7.1 kB ≈ **113 MB/location/year** Both sit below 165 MB. Caveat: the frames-per-event average rests on **n=2 events**, since capture only ungated at 03:50 UTC today. The event *rate* (839 records / 92 days) is solid; the frames-per-event figure is not yet. **#132 (~25–50 MB/year/region) remains unmeasurable.** `lightning_clusters` is at 0 rows — the archive deployed at 14:27 UTC today and `poll_lightning_job` is running and fetching successfully, but the feed currently returns no strikes near the single opted-in location. Only **1 of 5 locations** has `lightning_alert_radius_mi` set, so archive coverage is one location until more opt in. ## What this does and doesn't unblock The capture tables have **3 snapshots, 31 frames and 0 lightning clusters** — 10.6 hours of history, not a storm season. So the "size the result at 13 months" task **cannot be completed today** for the capture tables, and I am not going to manufacture a projection that looks measured but isn't. It needs a real convective season; I'd revisit in the autumn. What *is* settled enough to act on now is the structural work, which does not depend on that sizing: - [ ] Single `HISTORY_HORIZON_DAYS` (default 400) from which per-table windows derive. - [ ] Fix the `sent_alerts` basis → `coalesce(cleared_at, expires, sent_at)`. Confirmed real, 225 rows affected. - [ ] Bound `notification_deliveries` and `redirect_tokens` — the two genuinely unbounded tables. - [ ] Keep deliberate, documented overrides: `product_freshness_samples` at 14 d (observability, not history — 400 d would cost ~1.8 GB), `lightning_clusters` at 90 d (volume; unmeasured), `public_tokens` at 30 d (token hygiene, not history). **One caveat on raising `sent_alerts` 90 → 400 d:** the table then stops pruning entirely for ~10 months (its oldest row is 92 days old, so `stale_400` is currently 0) while continuing to accrue ~9.1 rows/day. That is the intended consequence of the 13-month decision, but combined with the 20× bloat above it means autovacuum behaviour on this table is worth watching after the change. ## Also worth a look (not this issue) `sent_alerts.radar_snapshot_path` is **NULL on all 1,708 rows**, while `alert_radar_frames` has 31. Most likely benign — both frame-bearing alerts were already active when capture ungated at 03:50, so the first-fire moment had passed and only periodic frames accrued. But if it is still 0 after a *fresh* alert fires post-deploy, that is a #85 regression. Flagging to re-check rather than asserting a bug.

claude-bot referenced this issue

2026-07-28 14:34:58 +00:00

Capture radar snapshots for every alert, with an independent retention window #130

claude-bot referenced this issue

2026-07-28 14:35:09 +00:00

Lightning cluster archive #132