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

Send a notification the instant you detect its condition, and the short gap between detection and delivery will bite you. Stock runs out, a price reverts, a user removes a watch — tens of seconds to minutes after detection, the alert may no longer apply.

I built and shipped a price watcher called Yasugoro on Cloudflare Workers + D1. It notifies you when a staple gets cheaper, so sending a “no longer cheaper” alert is the worst thing it can do. So I split detection from sending: detection only appends to an outbox, and a separate step re-checks right before it sends. This post is about how that outbox is built as a state machine on D1 — idempotent enqueue, a pre-send re-check, and retries — not about what gets detected (the buy-timing decision).

Detection only enqueues

Detection’s whole job is one INSERT into the outbox when a condition holds. Actually sending is a separate step (a per-minute dispatch). That decouples detection from delivery in time.

Make the enqueue idempotent. 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 absorbs it.

-- migration: an expression index that collapses duplicates to one
CREATE UNIQUE INDEX uq_outbox_dedup
  ON outbox (watch_id, trigger, date(detected_at), channel);

// enqueue is idempotent; returns false when ON CONFLICT ignored it
export async function enqueueOutbox(db: D1Database, input: EnqueueInput): Promise<boolean> {
  const res = await db.prepare(
    `INSERT INTO outbox
       (watch_id, user_id, channel, trigger, status, detected_at, detected_payload_json, retry_count, created_at)
     VALUES (?, ?, ?, ?, 'pending', ?, ?, 0, ?)
     ON CONFLICT (watch_id, trigger, date(detected_at), channel) DO NOTHING`
  ).bind(/* ... */).run();
  return (res.meta?.changes ?? 0) > 0; // changes=1 on real insert, 0 when dedup ignored it
}

Keying on date(detected_at) is the trick: it folds “the same detection on the same day” into one row while still letting the next day’s detection through as a new one. Detection can run many times over without the outbox ballooning with duplicates.

Five states, one direction

Each outbox row moves one way through five states. An enqueued pending becomes sending → sent if it passes the pre-send re-check, or dropped if it broke. A transient send failure goes to retry, and to failed at the cap.

flowchart LR
  pending -->|re-check holds| sending
  pending -->|condition broke| dropped
  sending -->|delivered| sent
  sending -->|transient fail| retry
  retry -->|back to re-check| pending
  retry -->|cap reached| failed

sent, dropped, and failed are terminal. dropped isn’t an error — it’s a normal terminal state meaning “we learned we shouldn’t send, so we didn’t.” Keeping it distinct from errors lets you later count how many you correctly withheld.

Re-check right before sending, and drop what broke

The dispatch handles one row at a time: re-fetch → re-check whether the detection condition still holds → send only the ones that do. This is the core that stops stale alerts.

// processing one outbox row (skeleton)
async function processOutbox(o, now, deps) {
  // (0) pre-guards: watch removed / target paused -> dropped
  if (watchRemoved || listingPaused) { await markDropped(db, o.id, ...); return 'dropped'; }

  // (1) re-fetch the current value right before sending (split drop vs retry by error kind)
  const r = await adapter.getOne(itemCode);
  if (!r.ok) {
    if (r.error.kind === 'empty') { await markDropped(db, o.id, 'out_of_stock'); return 'dropped'; }
    await scheduleRetry(db, o, `refetch_${r.error.kind}`); return 'retry';
  }

  // (2) re-check that the detection condition still holds (the body is domain-specific)
  const recheck = recheckTrigger(/* current value */);
  if (!recheck.ok) { await markDropped(db, o.id, 'condition_lost'); return 'dropped'; }

  // (3) only for the ones that hold: build the body from the latest value and deliver
  await markSending(db, o.id, o.channel);
  const result = await deps.deliver(o.userId, buildPayload(/* re-fetched latest value */));
  // ... sent / retry / dropped
}

What the re-check contains (which trigger still “holds”) depends on the service. What’s universal: don’t send from the value you captured at detection — assert from the value you re-fetched at send time. Align the number in the message with the basis on which you decided to send. Let them drift and you ship “the body says one price, reality is another.”

Advance “notified” only on sent

The other half of idempotency is a rule: update last_notified (the baseline for the last alert sent to this watch) only on sent. Never on dropped or retry.

If a drop or retry moved the baseline, the next legitimate detection would be wrongly rejected as “already notified.” Don’t stamp “sent” on something you didn’t send; only advance when you actually delivered. That’s how “send exactly once, but things you couldn’t send still flow to the next round” both hold.

Retry transient failures with exponential backoff, fail at the cap

Rate limits on the re-fetch, network errors, and transient delivery failures aren’t discarded — they drop to retry. Bump retry_count, wait with exponential backoff, and terminate at failed at the cap.

export function backoffMs(retryCount: number): number {
  return Math.min(2 ** retryCount * 30_000, 30 * 60_000); // 30s, 60s, 120s … capped at 30 min
}

One implementation compromise here. Rather than add next_attempt_at / last_error columns, the retry schedule rides inside the payload JSON under a _retry key — the detection snapshot (listingId, etc.) stays immutable while the schedule piggybacks on it. The fetch queue coarsely pulls pending/retry in SQL and filters out any whose _retry.nextAttemptAt hasn’t arrived, app-side. Backoff is respected without growing the schema.

export async function pendingOutbox(db, limit, now) {
  const rows = await db.prepare(
    `SELECT * FROM outbox WHERE status IN ('pending','retry') ORDER BY created_at ASC LIMIT ?`
  ).bind(limit * 2).all();
  const ready = rows.results.filter((r) => {
    if (r.status === 'pending') return true;
    const meta = parseDetectedPayload(r.detected_payload_json)._retry;
    return !meta?.nextAttemptAt || meta.nextAttemptAt <= now; // exclude not-yet-due backoff
  });
  return ready.slice(0, limit);
}

Wrapping up

The more you want a notification to be correct, the more it paid off to stop “sending on detection” and put an outbox in between. Enqueue idempotently, send only what passed a pre-send re-check, and advance “notified” only when you actually delivered. Collapsing the states into a one-way pending → sending → sent / dropped / retry → failed makes “shouldn’t send, so didn’t (dropped)” and “want to send, haven’t yet (retry)” distinct in both code and operations.

How this outbox gets driven every minute is in running many jobs on one Workers cron, and the delivery channel (login-less Web Push) is in delivering Web Push by anonymous device token. The running app is Yasugoro.

FAQ

Why enqueue into an outbox instead of sending on detection?

Because the situation changes between detection and delivery, and the alert goes stale. You detect that a condition holds, but in the tens of seconds to minutes before you actually send, stock can vanish or the condition can revert. Send anyway and you deliver a notification that no longer applies. Letting detection only append one outbox row, then re-fetching and re-checking right before sending, lets you drop the stale ones instead of sending them.

What stops the same notification from firing twice?

The enqueue is idempotent. It runs INSERT … ON CONFLICT DO NOTHING against a UNIQUE(watch_id, trigger, date(detected_at), channel) expression index, so duplicates for the same day, watch, trigger, and channel collapse to one row. On top of that, the notified baseline (last_notified) advances only on sent, so a dropped or retried row never blocks the next legitimate detection.

What happens if the condition breaks right before sending?

It’s dropped (a normal terminal state). If the pre-send re-fetch shows stock gone or the detection condition no longer holding, the row is dropped as condition_lost and the notified baseline is left untouched. The message body is built from the freshly re-fetched values, not the ones captured at detection, so the number you assert and the basis for sending are the same current value.

How are transient send failures handled?

As retry. Rate limits on the re-fetch, network errors, and transient delivery failures bump retry_count and wait with exponential backoff (30s → 60s → … capped at 30 min), terminating at failed once it hits OUTBOX_MAX_RETRY. Rather than add a next_attempt_at column, the retry schedule rides inside the payload JSON under a _retry key, and the fetch queue filters out retries whose scheduled time hasn’t arrived.