Stop the SPC day match crossing days, which failed every SPC poll on production (#214) #215

Merged

claude-bot merged 1 commit from fix/spc-day-match into main

2026-10-14 06:35:12 +00:00

claude-bot commented

2026-10-14 06:28:02 +00:00

Collaborator

Fixes #214. Production hotfix for v2.0.0.

What broke

Since the v2.0.0 upgrade at 05:50Z, production's poll_spc_job has raised on every run (UniqueViolation on uq_sent_alerts_dashboard_dedup), so SPC convective outlook notifications are not being sent. NWS warning alerts are unaffected.

Why

_upgrade_check selects a location's active SPC dashboard rows for one outlook day with nws_alert_id.contains(f"_{day}_"), an unescaped LIKE. In LIKE, _ matches any character. Product ids are timestamps, so on production's real ids the Day 2 pattern also matches the Day 1 and Day 3 rows. A same-risk reissue then renamed the wrong day's row onto an id another row already held, and the flush failed the whole poll.

The bug predates 2.0.0: production's rows show ids that moved between days. Why it fails on every cycle only since 2.0.0 is my inference: the query had no ORDER BY, and 2.0.0's new indexes likely changed which row comes first.

Change

contains(day_suffix, autoescape=True) in _upgrade_check and in app/api/alerts.py's per-day SPC history query, which had the same pattern.
_upgrade_check skips rows whose parsed day differs from the outlook's day, and orders rows by sent_at.

Verification

ruff: clean
Bulk tier: 1044 passed (1 new)
The new regression test seeds production's exact shape: one active MRGL row per day with timestamp product ids, Day 1 reissued. Without the fix it raises the same IntegrityError production logs; with the fix it passes, each row stays on its own day and nothing re-notifies.
Postgres tier: left to CI.

Not done here

Rows already moved between days on production are not repaired. Each day-id still maps to one active row, so continuations should settle as new products arrive; that gets checked on production after the hotfix.

🤖 Generated with Claude Code

Fixes #214. **Production hotfix** for v2.0.0. ## What broke Since the v2.0.0 upgrade at 05:50Z, production's `poll_spc_job` has raised on every run (`UniqueViolation` on `uq_sent_alerts_dashboard_dedup`), so SPC convective outlook notifications are not being sent. NWS warning alerts are unaffected. ## Why `_upgrade_check` selects a location's active SPC dashboard rows for one outlook day with `nws_alert_id.contains(f"_{day}_")`, an unescaped `LIKE`. In `LIKE`, `_` matches any character. Product ids are timestamps, so on production's real ids the Day 2 pattern also matches the Day 1 and Day 3 rows. A same-risk reissue then renamed the wrong day's row onto an id another row already held, and the flush failed the whole poll. The bug predates 2.0.0: production's rows show ids that moved between days. Why it fails on every cycle only since 2.0.0 is my inference: the query had no `ORDER BY`, and 2.0.0's new indexes likely changed which row comes first. ## Change - `contains(day_suffix, autoescape=True)` in `_upgrade_check` and in `app/api/alerts.py`'s per-day SPC history query, which had the same pattern. - `_upgrade_check` skips rows whose parsed day differs from the outlook's day, and orders rows by `sent_at`. ## Verification - ruff: clean - Bulk tier: **1044 passed** (1 new) - The new regression test seeds production's exact shape: one active MRGL row per day with timestamp product ids, Day 1 reissued. **Without the fix it raises the same `IntegrityError` production logs; with the fix it passes**, each row stays on its own day and nothing re-notifies. - Postgres tier: left to CI. ## Not done here Rows already moved between days on production are not repaired. Each day-id still maps to one active row, so continuations should settle as new products arrive; that gets checked on production after the hotfix. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

claude-bot added 1 commit

2026-10-14 06:28:03 +00:00

Stop the SPC day match crossing days, which failed every SPC poll on production (#214 )

CI / test (pull_request) Successful in 6m2s

Details

ad8184a933

After the 2.0.0 upgrade, poll_spc_job raised on every run with a
UniqueViolation on uq_sent_alerts_dashboard_dedup, so SPC outlook
notifications stopped. NWS warnings were unaffected.

_upgrade_check found a location's active SPC rows for one outlook day with
nws_alert_id.contains("_2_"), an unescaped LIKE. An underscore matches any
character, and product ids are timestamps, so the Day 2 pattern also matched
Day 1 and Day 3 ids. The first same-risk row was then "continued" by
renaming it to the new product's id; when it belonged to another day and
that day's row already held the id, the unique index rejected the flush and
the whole poll failed. The match predates 2.0.0 (production rows show ids
moved between days); the query had no ORDER BY, and 2.0.0's new indexes
plausibly changed which row came first.

- contains(..., autoescape=True), here and in the dashboard's per-day SPC
  history query, which had the same pattern.
- _upgrade_check skips any row whose parsed day differs from the outlook's,
  and orders rows by sent_at so the choice is deterministic.

The regression test seeds production's exact id shapes (one MRGL row per
day, timestamp product ids) and reproduces the production IntegrityError
without the fix. Existing tests used ids like "D1_P1", which never put a
digit beside an underscore.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

claude-bot merged commit ebca20a17d into main

2026-10-14 06:35:12 +00:00

claude-bot referenced this pull request from a commit

2026-10-14 06:35:14 +00:00

Merge pull request 'Stop the SPC day match crossing days, which failed every SPC poll on production (#214)' (#215) from fix/spc-day-match into main

claude-bot referenced this pull request

2026-10-14 06:35:23 +00:00

Add the v2.0.1 changelog entry #216

claude-bot referenced this pull request

2026-10-14 06:35:26 +00:00

Production: SPC outlook poll fails every cycle after 2.0.0 (unescaped LIKE in the SPC day match) #214