Harden schedule/diff-alert webhook delivery: retry, backoff, DLQ, and delivery API #26

Activity

1. harrymove-ctrl

   added this to the Sui Overflow milestone on Jun 16, 2026
2. harrymove-ctrl

   added

   P2Post-demo: distribution, polish, and reliabilityPost-demo: distribution, polish, and reliability

   featureUser- or agent-facing capabilityUser- or agent-facing capability

   platformBackend platform plumbing: Worker, D1, queues, secrets, meteringBackend platform plumbing: Worker, D1, queues, secrets, metering

   on Jun 16, 2026
3. harrymove-ctrl commented on Jun 19, 2026

   

   harrymove-ctrl

   on Jun 19, 2026

   OwnerAuthor

   More actions

   Implementation Plan

   Approach
   Make webhook delivery durable by persisting the exact request body + signature on the delivery row, replacing the hardcoded attempts = 1 with a real attempt counter + exponential-backoff scheduler, and re-attempting due rows from a sweep added to the existing */30 cron (no new Queue for alpha). Split the current monolithic deliverWebhook (worker.ts:3979-4024) into enqueueWebhookDelivery (build payload + signature, insert row, fire first attempt) and attemptWebhookDelivery (fetch, increment, decide sent/failed/dead) so the cron sweep and the manual redeliver endpoint reuse one code path. Owner-scoped list/redeliver endpoints join contextmem_webhook_deliveries → contextmem_alerts (deliveries carry no owner_id today; the alert does).

   Prerequisites & dependencies

   • No blocking issue for the core work. The "owner-scoped" requirement (AC4) nominally depends on the accounts/owner-auth issue, but this repo's existing /api/schedules + /api/alerts endpoints already use the in-place pattern: requireImportAuthorization (shared CONTEXTMEM_NAMESPACE_IMPORT_TOKEN bearer, worker.ts:4436) plus an ?ownerId= filter (listAlerts, worker.ts:1741). Reuse that exact pattern — do not block on real accounts.
   • Bindings/creds: none new. Cron already exists in apps/api/cloudflare/wrangler.jsonc:52-54 (["*/30 * * * *"]). CONTEXTMEM_WEBHOOK_SECRET already in WorkerEnv (worker.ts:39) as the HMAC fallback.
   • Migration must run against prod + staging D1 (contextmem / contextmem-staging) via wrangler d1 migrations apply — D1 is not auto-migrated on deploy.
   • Open roadmap decision (non-blocking): roadmap §6 lists "Webhook reliability" as P1 and asks "is fire-once OK for alpha, or is retry/DLQ required" (decisions q on webhook SLA). This issue answers it: cron-sweep DLQ now, dedicated contextmem-webhooks Queue deferred.

   Step-by-step

   1. Add migration apps/api/migrations/0007_webhook_retry.sql (next after 0006_namespace_build_sources.sql). SQLite cannot ALTER a CHECK constraint, and the existing table has a status IN ('queued','sent','failed') CHECK (0004_growth_loops.sql:77), so do a table rebuild: create contextmem_webhook_deliveries_new with all existing columns + next_attempt_at TEXT, max_attempts INTEGER NOT NULL DEFAULT 5, payload TEXT, signature TEXT, schedule_id TEXT, and status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','sent','failed','dead')); INSERT ... SELECT old rows (new cols default to NULL/5); DROP TABLE contextmem_webhook_deliveries; ALTER TABLE ..._new RENAME TO contextmem_webhook_deliveries; recreate contextmem_webhook_deliveries_alert_idx and add contextmem_webhook_deliveries_sweep_idx ON contextmem_webhook_deliveries(status, next_attempt_at). (No FK references this table, so the rebuild is safe.) Store schedule_id directly so the sweep/redeliver don't need an alert join for resend.
   2. Add backoff constant + types near the schedule helpers in worker.ts: const WEBHOOK_BACKOFF_MS = [60_000, 300_000, 1_800_000, 7_200_000, 43_200_000]; (1m/5m/30m/2h/12h) and a WebhookDeliveryRow type mirroring the new columns (model on ScheduleRow, worker.ts:185).
   3. Refactor deliverWebhook → enqueueWebhookDelivery (worker.ts:3979). Build body + signature (unchanged signer signWebhookPayload, worker.ts:4026) as today, but INSERT with attempts = 0, status = 'queued', next_attempt_at = now, max_attempts = 5, and persist payload = body, signature, schedule_id, webhook_url. Then call attemptWebhookDelivery(deliveryRow, env) for the immediate first try (preserves current fire-on-create UX). Update the caller createAlertForSchedule (worker.ts:3976) to call the renamed fn.
   4. Add attemptWebhookDelivery(row, env) (new fn in worker.ts). POST row.payload with header x-contextmem-signature: row.signature (verbatim → keeps the HMAC valid). Compute const attempts = Number(row.attempts) + 1; (replaces hardcoded 1 at worker.ts:4010/4018). On 2xx: status='sent', status_code, attempts, next_attempt_at=NULL. On non-2xx or throw: if attempts >= max_attempts → status='dead'; else status='failed', next_attempt_at = new Date(Date.now() + WEBHOOK_BACKOFF_MS[Math.min(attempts-1, WEBHOOK_BACKOFF_MS.length-1)]).toISOString(); always record status_code/error + attempts + updated_at. Never throw out of this fn (so the sweep loop never aborts mid-batch).
   5. Add sweepWebhookDeliveries(env) (new export async function, sibling of processDueSchedules at worker.ts:3893). SELECT * ... WHERE status IN ('queued','failed') AND next_attempt_at IS NOT NULL AND next_attempt_at <= ? ORDER BY next_attempt_at ASC LIMIT 50, then for each row await attemptWebhookDelivery(row, env). Wire it into the cron: in the scheduled() handler (worker.ts:540-544) run processDueSchedules(env, ctx) then sweepWebhookDeliveries(env) (both under ctx.waitUntil / awaited).
   6. Stop swallowing per-schedule failures — change the loop .catch(() => undefined) at worker.ts:3906 to .catch((err) => console.error("[schedule] run failed", schedule.id, err)). The contextmem_schedule_runs status='failed' write inside runSchedule's catch (worker.ts:3955) already persists and re-throws, so the failure row is preserved and now also logged (satisfies AC5) — keep the next_run_at math at worker.ts:3946 untouched.
   7. Add routes after the /api/alerts block (worker.ts:743-747):
      • GET /api/webhook-deliveries → requireImportAuthorization gate, then listWebhookDeliveries(request, env): read ?scheduleId= + ?ownerId=, run SELECT d.* FROM contextmem_webhook_deliveries d JOIN contextmem_alerts a ON a.id = d.alert_id WHERE a.owner_id = ? AND (? IS NULL OR a.schedule_id = ?) ORDER BY d.created_at DESC LIMIT 100, map through a new publicWebhookDelivery (id, status, attempts, maxAttempts, statusCode, lastError, nextAttemptAt, createdAt/updatedAt — model on publicSchedule, worker.ts:4116).
      • POST /api/webhook-deliveries/:id/redeliver → gate, load the row + verify owner via the same alert join, reset status='queued', next_attempt_at=now, then await attemptWebhookDelivery(row, env) and return the refreshed publicWebhookDelivery. 404 if not found/owner mismatch.
   8. No wrangler.jsonc change (cron sweep path). Add a one-line comment noting the dedicated contextmem-webhooks Queue is the documented follow-up if sweep volume grows.

   New / changed files

   • apps/api/migrations/0007_webhook_retry.sql (new)
   • apps/api/src/worker.ts (changed: scheduled handler ~540; processDueSchedules catch 3906; rename/split deliverWebhook 3979-4024 + createAlertForSchedule 3976; new attemptWebhookDelivery, sweepWebhookDeliveries, listWebhookDeliveries, redeliver handler, publicWebhookDelivery, WebhookDeliveryRow, WEBHOOK_BACKOFF_MS; new route entries ~747)
   • apps/api/src/worker.test.ts (changed: extend MemoryD1Database SQL parser + mockFetch; add 3 tests)

   Test plan (vitest, in apps/api/src/worker.test.ts; runs in CI via bun-check.yml)

   • Mock prep: extend MemoryD1Database (the prefix-matched parser at worker.test.ts:1070-1083) to handle the new insert columns (payload/signature/schedule_id/max_attempts/next_attempt_at), the attempts = N / 'dead' / next_attempt_at update branches, the sweep SELECT ... WHERE status IN ('queued','failed'), and the deliveries↔alerts join. Extend mockFetch (worker.test.ts:1092) to (a) accept { body, status } per-route (already supported) and (b) record each call's headers + body so tests can assert the signature.
   • Unit — backoff: WEBHOOK_BACKOFF_MS[min(attempts-1,4)] produces 1m/5m/30m/2h/12h for attempts 1-5.
   • Integration — 500 → dead (AC2/AC6): schedule with a webhook routed to { status: 500 }; run processDueSchedules; assert delivery status='failed', attempts=1, next_attempt_at set. Then loop: set next_attempt_at to a past time + call sweepWebhookDeliveries 4×; assert attempts climbs 2→5 and final status='dead'.
   • Integration — recovery (AC6): first attempt {status:500} → failed; flip the mock route to 200; backdate next_attempt_at; sweepWebhookDeliveries → status='sent', status_code=200.
   • Integration — redeliver + signature (AC4/AC6): after a delivery exists, POST /api/webhook-deliveries/:id/redeliver with the import bearer; assert the recorded outbound request body is byte-identical to the stored payload and x-contextmem-signature === signWebhookPayload(payload, secret) (import signWebhookPayload/export if needed). Assert owner-mismatch → 404.
   • Integration — list (AC4): GET /api/webhook-deliveries?scheduleId=&ownerId= returns the rows with status/attempts/lastError; wrong ownerId returns empty.
   • Regression: the existing "runs due schedules… records webhook delivery metadata" test (worker.test.ts:718) still passes — status='sent', status_code=200, but attempts is now 1 from the counter (not hardcoded). Update its assertion if it pins internals.
   • Manual / staging: point a schedule at a webhook.site URL returning 500, wait two */30 ticks (or invoke wrangler d1 execute to backdate next_attempt_at), confirm attempts increments and row flips to dead; then verify a 200 endpoint succeeds and GET /api/hosted/webhook-deliveries?scheduleId=... (the /api/hosted/* alias normalizes to /api/*, worker.ts:581) lists it.

   Risks & gotchas

   • CHECK-constraint rebuild: the only safe way to add 'dead' to the status CHECK in SQLite/D1 is the create-copy-drop-rename rebuild (step 1). Verify column order in the INSERT ... SELECT and recreate both indexes, or the sweep loses its index. Test the migration against a copy of prod data (CI migration-apply check, roadmap §6 CI item).
   • Worker subrequest/CPU limits: the sweep does up to N sequential fetches inside one cron invocation. Cap LIMIT 50 and rely on ctx.waitUntil; if delivery volume grows, move to the dedicated contextmem-webhooks Queue (issue's stated follow-up) which gives native per-message retry/DLQ.
   • Clock/idempotency: retries resend the identical persisted body+signature, so a receiver that already processed a delivery may see duplicates — document that x-contextmem-signature + alertId is the idempotency key for consumers.
   • Backoff vs cron granularity: next_attempt_at is honored only every 30 min, so a "1m" backoff effectively fires on the next tick — acceptable for alpha; note it in the delivery UI copy.
   • max_attempts default on backfilled rows: old rows get max_attempts=5 and next_attempt_at=NULL, so the sweep's next_attempt_at IS NOT NULL guard prevents re-attempting historical fire-once rows. Keep that guard.
   • Owner scoping caveat: auth is still the shared import token + ?ownerId= filter (not per-user identity) until the accounts issue lands — the join enforces ownerId at the data layer, matching today's /api/schedules security posture; flag for hardening when real accounts arrive.

   Acceptance-criteria mapping

   • Migration adds next_attempt_at, max_attempts (default 5), payload snapshot col; CHECK extended with 'dead' → Step 1.
   • deliverWebhook increments attempts (not hardcoded 1) + exponential backoff until max_attempts then 'dead' → Steps 3, 4 (+ constant in Step 2).
   • Sweep in */30 cron re-attempts status IN ('queued','failed') and next_attempt_at <= now → Step 5.
   • GET /api/webhook-deliveries?scheduleId= (owner-scoped) + POST /api/webhook-deliveries/:id/redeliver → Step 7.
   • Per-schedule failures recorded (schedule_runs failed write at worker.ts:3955 preserved) not silently dropped at the loop .catch → Step 6.
   • Tests: 500 retried then dead-lettered; recovered endpoint succeeds on retry; redeliver re-sends with valid x-contextmem-signature → Test plan (500→dead, recovery, redeliver+signature).

   Effort: M — ~2-3 person-days (1 migration + focused single-file backend change + mock-harness extension and 3 tests; the riskiest piece is the CHECK rebuild and extending the bespoke in-memory D1 mock).

   ---

   Implementation plan generated for execution (planning phase). Part of the roadmap (#4).