send-alerts cron: TOCTOU race can send duplicate emails on overlapping runs

[NewCoder3294]

Where

apps/web/app/api/cron/send-alerts/route.ts lines ~72–126

Problem

The dedup logic reads alert_sends once into an in-memory Set at the top of the run, then iterates subscriptions and:

1. calls sendAlertEmail(...) (line ~110)
2. inserts into alert_sends (line ~118)

If two runs of this cron overlap (Vercel cron retries, manual trigger during a scheduled run, or two regions firing), both invocations read the same empty/stale sentSet, both send the email, and both insert. The email has already left the queue before the row exists, so even adding a UNIQUE(subscription_id, live_incident_id) constraint after-the-fact wouldn't prevent the duplicate send — it would just turn the second insert into an error.

Why it's real

• The 5-minute cron window + Vercel's at-least-once delivery makes overlap plausible, not theoretical.
• sentSet is built from a single SELECT before any sends, never refreshed mid-loop.
• No advisory lock, no row-level claim, no upsert-then-send ordering.

Suggested fix

Flip the order: insert a "claim" row first (with a unique constraint), then send only if the insert succeeded. Pattern:

const { error: claimErr } = await supabase
  .from("alert_sends")
  .insert({ subscription_id, live_incident_id, sent_at: new Date().toISOString(), status: "pending" });
if (claimErr) continue; // another runner already owns this pair
await sendAlertEmail(...);
await supabase.from("alert_sends").update({ status: "sent" }).eq(...);

This makes the DB the source of truth and lets concurrent crons coexist safely.

Severity

High — user-visible (duplicate alert emails erode trust in the product).