feat(webhook): Postgres-backed WebhookDeliverySupervisor for multi-worker durability

[bokelley]

Summary

Multi-worker deployments need durable circuit-breaker state and sequence numbers across processes. Reference SQLAlchemy ORM implementation of the supervisor seam shipped in PR #348.

Design

Tables

• webhook_circuit_state — per-endpoint state (open/half-open/closed, failure counts, next attempt timestamp)
• webhook_delivery_queue — in-flight retry queue, polled with FOR UPDATE SKIP LOCKED
• webhook_delivery_log — audit history

Pattern

Cross-process sequence-number durability via DB primary key. Pattern proves out the broader "Postgres-as-queue" approach used by graphile-worker and river. The FOR UPDATE SKIP LOCKED polling is the canonical multi-worker leasing primitive.

Adoption story

Salesagent's migration plan flags multi-worker durability as a real blocker at scale — this lands the reference adopters can fork.

Cross-references

• Webhook supervisor seam (merged): PR #348
• RFC: docs/proposals/v3-identity-bundle-design.md

🤖 Generated with Claude Code

[bokelley]

Triage complete — draft PR opened: #360

This is a well-scoped, actionable feature request that maps cleanly onto the WebhookDeliverySupervisor Protocol from #348 and the existing PgReplayStore Postgres pattern. Implemented as PgWebhookDeliverySupervisor in src/adcp/webhook_supervisor_pg.py.

What was built:

• Three Postgres tables (adcp_webhook_circuit_state, adcp_webhook_delivery_queue, adcp_webhook_delivery_log) with create_schema() for idempotent migration
• FOR UPDATE SKIP LOCKED worker loop — connection held open through HTTP send for crash-safe lease rollback
• Circuit breaker state (CLOSED/OPEN/HALF_OPEN) stored in Postgres; atomic half-open transition via INSERT … ON CONFLICT DO UPDATE … RETURNING
• Spec-compliant retry: sender.resend(prev) replays stored sent_body+idempotency_key (spec: "Publishers MUST reuse same idempotency key on retry")
• SQL injection protection via ASCII-only table-name validation at construction time
• 28 unit tests with fully-mocked psycopg3 (no real database needed to run tests)
• Protocol gap fix: WebhookDeliverySupervisor.send_mcp() was missing breaker_key/notification_type kwargs

Outcome: Execute PR — all triage criteria met (CONTRIBUTOR author, bounded scope, fits established pattern, no spec ambiguity, build gate green).

---

Generated by Claude Code