DEV Community

Aulvem
Aulvem

Posted on

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

#typescript #cloudflare #sqlite #architecture

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
  ON outbox (watch_id, trigger, date(detected_at), channel);
Enter fullscreen mode Exit fullscreen mode 
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; // 1 on real insert, 0 when dedup ignored it
}
Enter fullscreen mode Exit fullscreen mode

Keying on date(detected_at) folds "the same detection on the same day" into one row while letting the next day's through as new.

Five states, one direction

Each row moves one way: pending → sending → sent (holds), pending → dropped (broke), sending → retry → failed (transient → 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
Enter fullscreen mode Exit fullscreen mode

dropped isn't an error — it's a normal terminal state ("we learned we shouldn't send, so we didn't"). Keeping it distinct lets you later count how many you correctly withheld.

Re-check right before sending, drop what broke

The send step handles one row at a time: re-fetch → re-check whether the detection condition still holds → send only the ones that do.

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
  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) does the detection condition still hold? (body is domain-specific)
  if (!recheckTrigger(/* current value */).ok) { await markDropped(db, o.id, 'condition_lost'); return 'dropped'; }
  // (3) only 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
}
Enter fullscreen mode Exit fullscreen mode

What the re-check contains depends on the service. What's universal: don't send from the detection-time value — assert from the value you re-fetched at send time. Align the number in the message with the basis for sending, or you ship "the body says one price, reality is another."

Advance "notified" only on sent

The other half of idempotency: update last_notified (the last-alerted baseline) only on sent — never on dropped or retry. Move it on a drop or retry and the next legitimate detection gets wrongly rejected as "already notified." Advance only when you actually delivered, and "send exactly once, but un-sent things still flow onward" both hold.

Retry transient failures with backoff

Rate limits on the re-fetch, network errors, and transient delivery failures 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
}
Enter fullscreen mode Exit fullscreen mode

The compromise of riding the retry schedule inside the payload JSON instead of adding columns, how this outbox is driven every minute (one Workers cron), and the delivery channel (login-less Web Push) — the full notes are on Aulvem → Aulvem | Idempotent notifications with an outbox. The running app is Yasugoro.

Top comments (0)