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

[harrymove-ctrl]

Context

Scheduled re-scrapes fire diff alerts + HMAC-signed webhooks (runSchedule/createAlertForSchedule/deliverWebhook, apps/api/src/worker.ts:3910-4024) on the */30 cron (wrangler.jsonc triggers). Delivery is fire-once: attempts is hardcoded to 1 on both success and failure (worker.ts:4010/4018), there is no retry/backoff, no dead-letter, and no way to list or redeliver a failed webhook. processDueSchedules also silently swallows per-schedule errors (.catch(() => undefined), worker.ts:3906) and only handles LIMIT 10 per tick.

Goal / user story

As a user relying on change alerts, I want webhook deliveries to retry with backoff and be inspectable/redeliverable, so a transient 500 on my endpoint doesn't permanently drop a "context changed" notification.

Acceptance criteria

• Migration adds next_attempt_at, max_attempts (default 5), and a payload-snapshot column to contextmem_webhook_deliveries; the status CHECK is extended with 'dead'.
• deliverWebhook increments attempts (not hardcoded 1) and, on failure (non-2xx or throw), schedules a retry with exponential backoff (e.g. 1m/5m/30m/2h/12h) until max_attempts, then marks 'dead'.
• A sweep in the existing */30 cron (or a dedicated Queue) re-attempts rows where status IN ('queued','failed') and next_attempt_at <= now.
• GET /api/webhook-deliveries?scheduleId= (owner-scoped) lists deliveries with status/attempts/last error; POST /api/webhook-deliveries/:id/redeliver re-queues one.
• Per-schedule failures in processDueSchedules are recorded (the contextmem_schedule_runs status='failed' write at worker.ts:3955 is preserved) rather than silently dropped at the loop .catch (worker.ts:3906).
• Tests: a 500 endpoint is retried then dead-lettered; a recovered endpoint succeeds on retry; redeliver re-sends with a valid x-contextmem-signature (signWebhookPayload, worker.ts:4026).

Implementation notes

Reuse the existing contextmem_webhook_deliveries table (migrations/0004_growth_loops.sql) and HMAC signer. Prefer the bound Queue pattern (CONTEXTMEM_EXTRACT_QUEUE) or add a dedicated contextmem-webhooks queue in wrangler.jsonc for retry fan-out; for alpha the */30 cron sweep alone is sufficient. Persist the exact request body so redelivery is byte-identical (keeps the signature valid). Keep the next_run_at scheduling math (worker.ts:3946) intact. Raising the LIMIT 10 / paginating processDueSchedules is a follow-up once schedule count grows.

Sui Overflow angle

Reliable, inspectable change-alerts are the visible, demo-able payoff of the freshness pipeline ("watch a page → ContextMeM re-extracts → your agent/Slack gets a signed webhook"), and a webhook can carry the on-chain attribution receipt id once receipts land — making delivery reliability part of the provenance story, not a throwaway notification.

Dependencies

Owner-scoped delivery endpoints depend on the accounts/owner-auth issue. None otherwise.

Part of the ContextMEM roadmap (#4) • Sui Overflow build.

[harrymove-ctrl]

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).