Idempotent notifications with an outbox: separate detection from sending, re-check before send

Send a notification the instant you detect its condition, and the short gap before delivery bites you. Stock runs out, a price reverts, a watch is removed — tens of seconds to minutes later, the alert may no longer apply.

I solved this with the outbox pattern in a price watcher I built (Cloudflare Workers + D1): detection only enqueues, and a separate step re-checks right before sending. Here are the essentials — idempotent enqueue, a pre-send re-check, and retries — as a state machine on D1.

Detection only enqueues (idempotent)

Detection's whole job is one INSERT into the outbox; sending is a separate step. However many times the same watch's same trigger fires in a day, the notification should collapse to one — a SQLite expression index plus ON CONFLICT DO NOTHING handles it.

CREATE UNIQUE INDEX uq_outbox_dedup