Webhook Relay Service: API Data Model and Schema Design Patterns

Half of the integration outages we have triaged in the last eighteen months trace back to one missing field in a relay schema. Not a flaky downstream. Not a thundering herd. A column that should have been there from day one: an idempotency key, a delivery attempt counter, an event version, a parent event id. The team ships the relay, traffic looks fine for six weeks, then a partner replays a backlog and the database collapses under duplicate inserts that the schema cannot detect.

A webhook relay service is a deceptively small piece of infrastructure. From the outside it looks like a queue with HTTP on both sides. Inside, it is a state machine with at-least-once semantics, multiple failure modes per delivery, and a contract with both producers and consumers that you cannot break without forcing a migration on someone else's on-call rotation. The data model is the contract. Get the entities, the envelope and the retry semantics wrong and every dashboard, alert and replay tool you build on top is wrong too. Get them right once and the relay quietly handles five years of growth.

The Four Core Entities

Every production-grade relay we have audited converges on the same four entities. The names differ ("source" vs "tenant", "subscription" vs "endpoint") but the responsibilities do not. If your data model is missing one of these, you are pushing that concern into application code where it cannot be queried, indexed or alerted on.

The separation between delivery and attempt is the one teams skip most often. They store everything on a single "webhook" row, overwrite the status field on retries and lose the ability to answer the only question that matters at 2am: which response did the partner return on attempt three before we gave up? Keep them separate. A delivery has a logical outcome (pending, succeeded, failed, expired); an attempt has a physical outcome (status code, latency, response body hash, error class).

Source

Source is your producer-side abstraction. At minimum it holds the public identifier you put in the X-Webhook-Source header, an allow-list of event types it is permitted to publish, and the signing key version it currently uses. Add a signing_key_previous column from day one — when you rotate keys you will need a 24 to 72 hour overlap window where both signatures verify, and bolting that on later means a migration touching every subscriber.

Subscription

Subscription is the consumer's configuration. The destination URL, the shared secret, the filter expression (event type plus optional payload predicates), the retry policy overrides if you allow them, and a disabled_at timestamp instead of a boolean. Soft-disable lets you re-enable without losing the original creation context, and it lets you query "subscriptions disabled in the last 7 days" for churn analysis. A disabled flag throws that signal away.

Delivery

A delivery is created the moment an event matches a subscription. It is the durable record that this event owes this subscription a response. The schema needs: id, event_id, subscription_id, status, attempt_count, next_attempt_at, last_attempt_at, terminal_at, idempotency_key, and scheduled_at (when delivery first became eligible, distinct from event timestamp). Index (status, next_attempt_at) for the worker poll and (subscription_id, created_at desc) for the customer dashboard.

Attempt

Attempts are append-only. Every HTTP request gets a row: delivery_id, attempt_number, requested_at, responded_at, status_code, latency_ms, response_body_sha256, response_headers_json, error_class, worker_id. Storing the response body hash instead of the body itself keeps the table small while still letting you detect when a partner returned the same error 12 times in a row — strong signal that retrying again is pointless.

Event Envelope vs Payload Schema

The single most consequential design decision in a relay is whether the envelope and the payload are the same object. They are not. The envelope is what the relay owns and controls; the payload is what the producer controls. Conflate them and every payload schema change becomes a relay schema change.

A clean envelope looks like this in transit:

Notice the two timestamps. occurred_at answers "when did the thing happen"; relayed_at answers "when did we hear about it". The gap between them is one of the most valuable observability signals you will ever have, and you cannot reconstruct it after the fact. Add a third — attempt_started_at in the outbound headers — and you have end-to-end timing for any delivery without a single distributed trace.

Idempotency Keys and Deduplication

At-least-once delivery is not a bug, it is the contract. Anything stronger is a lie that breaks under partition or restart. Which means every consumer must dedupe, and the relay must make dedupe cheap. Two keys do two different jobs, and you need both.

• Producer idempotency key. Set by the producer when publishing. Used by the relay to collapse duplicate publishes of the same logical event. Scope: (source_id, idempotency_key), TTL 24 hours minimum.
• Delivery id. Set by the relay, unique per delivery. Used by the consumer to dedupe across relay retries. Scope: globally unique, stored by the consumer for the longest retry window you allow (often 72 hours).

The producer key prevents the relay from creating two events when a producer's own retry loop fires. The delivery id prevents the consumer from processing the same event twice when the relay retries after a network blip. Skip either one and you ship double-charges. We have seen one team conflate them into a single event_id field and then discover, six months in, that they cannot tell a producer retry from a relay retry — so they cannot tell whether the duplicate is the producer's fault or theirs. The fix was a nine-week migration. The cost of doing it right on day one was four extra columns.

Retry, Backoff, and Dead-Letter Design

Retry policy belongs in data, not in code. If the policy is hard-coded, you cannot tune per-subscription, you cannot replay with different parameters, and you cannot answer the auditor when they ask "what was the retry schedule on the day this delivery failed". Store the policy on the subscription, snapshot the effective policy on the delivery row at create time, and never mutate it.

A defensible default schedule for general-purpose relays looks like this:

Add jitter of plus or minus 20 percent to each delay. Without jitter you will eventually have a thousand deliveries scheduled for the exact same millisecond, and the worker pool will spike CPU then idle then spike again in a sawtooth that the autoscaler chases forever.

Dead-letter is not a separate table. It is a terminal status on the delivery row. Build a partial index on status = 'dead_letter' and a single endpoint that lets operators bulk-replay by subscription, by time window or by error class. Engineers who treat dead-letter as a separate table inevitably end up writing a second query layer to unify the two for dashboards. Same data, one table, one source of truth. For more on building integrations that survive an actual audit, see our piece on audit-ready integrations.

Observability Fields Nobody Adds Until Too Late

Here is the list teams wish they had added in week one. Every column below has paid for itself the first time an incident required it.

• worker_id on attempt. When one worker is silently misbehaving, you need to group failures by worker. Without this column you are reading container logs at 3am.
• response_body_sha256. Detects "same error N times in a row" without storing payloads. Lets you short-circuit retries when a destination is permanently broken in the same way.
• partner_request_id. Echo back whatever request id the consumer returns in X-Request-Id . The first time a partner says "we never got it", you will paste this id into their support form and end the argument in 90 seconds.
• scheduled_at separate from created_at. Delivery created at 10:00:00, eligible at 10:00:30 (because of subscription throttling). The gap is queue lag, and you cannot compute it without both timestamps.
• filter_match_ms. If your filters get expressive (JSONPath, CEL) some of them get slow. A millisecond column on the delivery row turns "our relay is slow today" into a one-line query.
• relay_region. Once you are multi-region, every delivery row needs to remember where it was processed. Cross-region replays without this field corrupt your latency dashboards forever.
• cause_event_id. When one event triggers another (an LLM tool call producing a webhook, say), link them. We covered this pattern more in our notes on tool use best practices — the same lineage requirement applies in plain integration code.

Schema Evolution Without Breaking Consumers

The relay schema will change. The producer payload schemas will change more often. The two evolutions must be independent or every payload tweak becomes a coordinated release across every consumer you have.

Three rules, applied without exception:

1. The envelope is additive only. New fields, never removed fields, never type changes. A consumer that ignored filter_match_ms last year will keep working when it appears this year.
2. Payload schema_version is a producer-owned integer. When the producer needs a breaking change, they bump schema_version from 3 to 4 and run both in parallel during the migration window. The relay does not interpret the payload; it just passes it through with the version stamp. We dug into the related design questions for AI-driven publishers in webhook design for AI.
3. Subscriptions pin to a major version. A subscription created against schema_version=3 keeps receiving v3 payloads until the consumer opts in to v4. The relay stores the pinned version on the subscription row and uses it to fan out, or to refuse delivery if the producer has retired that version.

That third rule is the one teams resist hardest and regret skipping fastest. It costs you one extra column on the subscription table. It saves you from the worst class of incident in this category: a producer ships what they think is a backward-compatible change, half the consumers handle it, the other half throw 500s for eight hours, and the on-call engineer cannot tell which consumers were on which version because the schema never tracked it.

What "Done" Looks Like

A webhook relay data model is done when you can answer all of these from SQL in under a second, without joining application logs:

• Which subscriptions have failed deliveries in the last hour, grouped by error class?
• For delivery X, what was every attempt, every status code and every response hash?
• What is the p95 gap between occurred_at and the first successful attempt, per source?
• Which subscriptions are on payload version 3 and need to migrate before v3 is retired?
• How many deliveries are currently in dead-letter, by subscription, by reason?

If even one of those queries requires a join into a log aggregator or a tail through Loki, the schema is not finished yet. The point of designing the data model deliberately is to make every operational question a SQL question. The point of getting it right on day one is that the migration to add the missing column always lands the week your highest-revenue customer is mid-incident. Pay the design cost up front. The relay will be the quietest service you own.