Webhooks connect payment gateways, identity providers and SaaS platforms to the systems that act on their events. As enterprise adoption grows, two requirements pull against each other: data sovereignty and operational orchestration.
Customers increasingly want sensitive payloads (PII, financial records, health data) to stay inside infrastructure they control. Webhook relays and automation platforms still need to coordinate delivery, track retries and report health. This article describes a Bring-Your-Own (BYO) database pattern that handles both. Raw webhook bodies are persisted only in a customer-controlled PostgreSQL database. An orchestration layer sees operational metadata only.
Along the way, it covers where the pattern’s guarantees stop. A pattern like this is easy to oversell, so the limits are stated plainly.
1. The Architectural Dilemma: Sovereignty vs. Orchestration
The conventional approach is a hosted webhook router. It receives provider requests, stores the bodies in a shared multi-tenant datastore, and runs processing and retries centrally. This is operationally simple, but it has costs:
- Compliance scope: If a vendor stores or processes regulated data on your behalf, it becomes part of your compliance footprint. HIPAA generally requires a business associate agreement with such a vendor, and GDPR requires a data processing agreement. PCI DSS applies to any system that stores, processes or transmits cardholder data. Keeping payloads out of a third party’s storage can shrink that footprint, but it does not remove your own obligations.
- Trust: Many security teams are reluctant to send raw event bodies to a multi-tenant system, or to share database credentials with one.
The BYO Infrastructure Paradigm
The pattern splits payload persistence from job orchestration:
- Data plane (customer infrastructure): A PostgreSQL database holds the raw event store, headers and business processing tables. Workers run here too.
- Orchestration plane (relay or control plane): It manages routing rules, delivery status, retry policy, alerting and dashboards. It works from metadata such as job IDs, status codes, timings and error classes, not payload contents.
One constraint shapes the whole design. Providers sign the exact raw bytes of the request body. Stripe, for example, signs the raw payload and sends the signature in a Stripe-Signature header with a timestamp, and its libraries reject timestamps outside a default five-minute tolerance. Verification must therefore happen wherever the raw body is available. There are two ways to honor “the orchestrator never stores the payload”:
- Customer-side ingress: The provider’s webhook points at an endpoint you host. It verifies the signature, writes the event to your database and reports metadata to the control plane.
- Pass-through relay: A relay receives the request, verifies it, and forwards it to your ingress without persisting the body. The relay still sees the bytes in memory in transit, so this is a weaker guarantee than “never possesses the payload.” Describe it accurately to customers.
┌───────────────────────────────┐
│ ORCHESTRATION PLANE │
│ - Routing & retry policy │
│ - Delivery status & metrics │
│ - Alerting & dashboards │
└──────────────▲────────────────┘
│ metadata only
│ (job_id, status, timings, error class)
┌──────────┐ signed webhook ┌──┴────────────────────────────────┐
│ Provider │ ───────────────► │ CUSTOMER INFRASTRUCTURE │
│ (Stripe, │ │ - Ingress: verify signature │
│ etc.) │ │ - PostgreSQL: events + jobs │
└──────────┘ │ - Workers: lease & process jobs │
└───────────────────────────────────┘
2. Designing the Minimal-Privilege PostgreSQL Schema
The schema below stores events and jobs in a dedicated schema. It uses gen_random_uuid(), which is built into core PostgreSQL since version 13. The uuid-ossp extension is therefore unnecessary for random UUIDs. pgcrypto is only needed if you use its functions.
Two design choices differ from a naive implementation:
-
Raw body is stored as
bytea. Signature verification needs the original bytes, andJSONBdoes not preserve whitespace, key order or duplicate keys. Keep the original body for audit and replay. Add aJSONBcolumn if you want to query fields. -
Retries are scheduled in the row. A
next_attempt_atcolumn supports backoff, and a boundedmax_attemptsstops infinite retry loops.
CREATE SCHEMA IF NOT EXISTS webhook_ingress;
-- 1. Events: raw payloads live only in the customer's database
CREATE TABLE webhook_ingress.events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
provider VARCHAR(64) NOT NULL,
external_event_id VARCHAR(255) NOT NULL,
event_type VARCHAR(128) NOT NULL,
raw_body BYTEA NOT NULL, -- exact bytes that were signed
payload JSONB, -- parsed copy for querying
headers JSONB NOT NULL,
signature_verified BOOLEAN NOT NULL DEFAULT FALSE,
received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT uk_provider_external_id UNIQUE (provider, external_event_id)
);
CREATE INDEX idx_events_received_at ON webhook_ingress.events (received_at DESC);
-- 2. Jobs: lease-based work queue
CREATE TABLE webhook_ingress.jobs (
job_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_id UUID NOT NULL REFERENCES webhook_ingress.events(id) ON DELETE CASCADE,
status VARCHAR(16) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','leased','completed','failed','dead_letter')),
lease_holder_id VARCHAR(128),
lease_expires_at TIMESTAMPTZ,
next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
attempt_count INT NOT NULL DEFAULT 0,
max_attempts INT NOT NULL DEFAULT 5,
last_error TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Partial index keeps the polling set small as completed jobs accumulate
CREATE INDEX idx_jobs_claimable
ON webhook_ingress.jobs (next_attempt_at)
WHERE status IN ('pending', 'leased');
CREATE INDEX idx_jobs_event_id ON webhook_ingress.jobs (event_id);
Enforcing Least Privilege with Roles
Use separate roles for ingress and workers. PostgreSQL supports column-level UPDATE grants, so workers can be limited to the lease-related columns.
CREATE ROLE webhook_ingress_writer LOGIN;
CREATE ROLE webhook_worker LOGIN;
REVOKE ALL ON SCHEMA webhook_ingress FROM PUBLIC;
GRANT USAGE ON SCHEMA webhook_ingress TO webhook_ingress_writer, webhook_worker;
-- Ingress: insert events and enqueue jobs; no reads, updates or deletes
GRANT INSERT ON webhook_ingress.events, webhook_ingress.jobs TO webhook_ingress_writer;
-- Workers: read events, and update only lease and result columns on jobs
GRANT SELECT ON webhook_ingress.events TO webhook_worker;
GRANT SELECT ON webhook_ingress.jobs TO webhook_worker;
GRANT UPDATE (status, lease_holder_id, lease_expires_at, next_attempt_at,
attempt_count, last_error, updated_at)
ON webhook_ingress.jobs TO webhook_worker;
TRUNCATE, DELETE, DROP and DDL are separate privileges that none of these grants include. A role that is not the table owner cannot alter the schema. Note that the ingress role needs INSERT only. If you use INSERT ... ON CONFLICT DO NOTHING, PostgreSQL also requires SELECT on the columns in the conflict target, so grant column-level SELECT (provider, external_event_id) as well.
3. Job Leasing with FOR UPDATE SKIP LOCKED
Workers claim jobs directly from the customer database, with no separate broker such as Redis.
PostgreSQL added SKIP LOCKED in version 9.5. With it, rows that cannot be locked immediately are skipped instead of waited on. The PostgreSQL documentation warns that this yields an inconsistent view of the data, which makes it unsuitable for general-purpose queries. It is intended for queue-like tables, where each consumer should receive a different subset of available rows.
-- Claim up to 5 jobs: pending and due, or leased with an expired lease
WITH candidates AS (
SELECT job_id
FROM webhook_ingress.jobs
WHERE attempt_count < max_attempts
AND (
(status = 'pending' AND next_attempt_at <= now())
OR (status = 'leased' AND lease_expires_at < now())
)
ORDER BY next_attempt_at
LIMIT 5
FOR UPDATE SKIP LOCKED
)
UPDATE webhook_ingress.jobs j
SET status = 'leased',
lease_holder_id = $1, -- worker identity
lease_expires_at = now() + interval '5 minutes',
attempt_count = j.attempt_count + 1,
updated_at = now()
FROM candidates c
WHERE j.job_id = c.job_id
RETURNING j.job_id, j.event_id, j.attempt_count;
The worker then reads the event body by event_id. Keeping the claim and the payload read separate means a claim never copies large payloads through the queue path.
Run the claim as a single statement, as above, or inside one transaction. If the row lock is taken in one transaction and the update happens in another, the lock is released at the first commit and another worker can claim the same row.
Completing, Failing and Dead-Lettering
Completion must verify the lease. Otherwise a slow worker whose lease expired could overwrite the result of the worker that took over.
-- Success
UPDATE webhook_ingress.jobs
SET status = 'completed', lease_holder_id = NULL, lease_expires_at = NULL, updated_at = now()
WHERE job_id = $1 AND lease_holder_id = $2 AND status = 'leased';
-- Failure: retry with backoff, or dead-letter when attempts are exhausted
UPDATE webhook_ingress.jobs
SET status = CASE WHEN attempt_count >= max_attempts THEN 'dead_letter' ELSE 'pending' END,
next_attempt_at = now() + (interval '30 seconds' * power(2, attempt_count)),
lease_holder_id = NULL,
lease_expires_at = NULL,
last_error = $3,
updated_at = now()
WHERE job_id = $1 AND lease_holder_id = $2 AND status = 'leased';
Because the claim query skips jobs with attempt_count >= max_attempts, a job whose worker crashed on its final attempt would stay leased forever. Run a periodic reaper that moves such rows to dead_letter:
UPDATE webhook_ingress.jobs
SET status = 'dead_letter', updated_at = now()
WHERE status = 'leased' AND lease_expires_at < now() AND attempt_count >= max_attempts;
What SKIP LOCKED Does and Doesn’t Give You
- Less lock waiting: Workers move on to unlocked rows instead of blocking behind a competitor.
- Crash recovery: If a worker dies, its lease expires and another worker can reclaim the job.
- At-least-once delivery, not exactly-once: A worker that is slow but alive can still be processing when its lease expires. Handlers must be idempotent. Set the lease longer than your worst-case processing time, or extend it with a heartbeat.
- Throughput limits: A Postgres-backed queue is a good fit for moderate volumes and for teams that already run Postgres. Very high volumes may call for table partitioning, aggressive vacuuming of the jobs table, or a dedicated broker. Benchmark with your own workload.
4. Metadata Synchronization and State Reporting
The orchestration layer still needs to know whether deliveries are succeeding. After each job finishes, the customer-side worker or an outbox publisher sends a small status record to the control plane over HTTPS:
{
"instance_id": "cust-cluster-us-east-01",
"job_id": "a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d",
"status": "completed",
"attempt_count": 1,
"duration_ms": 142,
"error_class": null,
"processed_at": "2026-11-05T07:43:00Z"
}
Keep the record minimal and make the contract explicit:
- Send: job ID, status, attempt count, duration, a coarse error class and timestamps.
- Never send: payload bodies, headers, customer identifiers inside error messages, or stack traces that might echo payload fields.
-
Treat some metadata as sensitive. Fields like
providerandevent_typecan reveal business activity. Under GDPR, an identifier that links back to a person can itself count as personal data. If that matters to the customer, use opaque job IDs and let them opt out of event-type reporting.
To keep this reporting reliable, write the status change and a pending-sync row in the same database transaction (a transactional outbox). A publisher then ships outbox rows to the control plane and retries on failure, so a network blip never loses a status update.
5. Provider Behavior Your Schema Must Absorb
Your ingress design has to cope with how providers actually deliver. Using Stripe as an example:
- Retries: In live mode, Stripe retries failed deliveries with exponential backoff for up to three days. In test and sandbox mode, it makes three attempts over a few hours.
-
Duplicates and ordering: The same event can arrive more than once, and events are not guaranteed to arrive in order. This is why
UNIQUE (provider, external_event_id)matters. Insert withON CONFLICT DO NOTHING, return2xxfor duplicates, and avoid assuming the latest-arriving event reflects the latest state. -
Acknowledge quickly: Stripe’s exact timeout is not publicly documented, but handlers should acknowledge fast. Persist the event and enqueue the job, then return
2xxand do the heavy work asynchronously. This is the main reason for the events/jobs split.
INSERT INTO webhook_ingress.events
(provider, external_event_id, event_type, raw_body, payload, headers, signature_verified)
VALUES ($1, $2, $3, $4, $5, $6, TRUE)
ON CONFLICT (provider, external_event_id) DO NOTHING
RETURNING id;
-- If a row is returned, insert the matching job in the same transaction.
6. Security Hardening
-
Network isolation and transport security: Place the database in private subnets. Require TLS on all connections, set
ssl_min_protocol_versionto at least TLS 1.2 (TLS 1.3 where your clients support it), and have clients connect withsslmode=verify-full. For service-to-service access, use client certificates (mTLS). Certificate pinning is rarely practical for database connections, so prefer a private CA with verification. - Encryption at rest: Use volume or storage encryption from your cloud provider or filesystem layer as the baseline. For field-level protection, prefer application-side or envelope encryption with keys from a KMS.
-
Be careful with
pgcryptocolumn encryption:pgp_sym_encrypt()returnsbytea, so it cannot be inserted into aJSONBcolumn. It needs abyteacolumn. Also, pgcrypto runs inside the database server, so plaintext and keys reach the server, and a key written as a literal in SQL can end up in statement logs. Disable statement logging for those queries, or encrypt in the application before the data reaches Postgres. -
Verify before you store as trusted: Verify the provider’s signature on the raw body, with its timestamp tolerance, before setting
signature_verified = TRUE. Reject anything outside the tolerance window. -
Retention and deletion: Add a scheduled job to purge old events and completed jobs. Because
jobs.event_idcascades on delete, deleting expired events removes their jobs as well. Retention limits are also a common compliance requirement. -
Audit access: Log connections and role usage (for example with
log_connections, and thepgauditextension where available) so customers can see exactly what the orchestration layer touched.
Summary: Centralized vs. BYO Architecture
| Dimension | Centralized webhook storage | BYO database pattern |
|---|---|---|
| Payload storage | Vendor-managed, multi-tenant | Customer-controlled PostgreSQL |
| Compliance scope | Vendor holds regulated data; agreements such as a BAA or DPA are typically required | Smaller vendor footprint; customer keeps full responsibility for the data |
| Job coordination | Vendor-operated queues or brokers | Database-native leasing with FOR UPDATE SKIP LOCKED
|
| Metadata visibility | Full event visibility for the vendor | Operational status only, by design |
| Operational burden | Low for the customer | Higher: the customer runs the database, workers and backups |
| Debugging | Vendor can inspect payloads | Needs customer-side tooling, since the vendor cannot see payloads |
The BYO pattern shifts risk and responsibility rather than eliminating them. Customers get stronger control over payloads and a smaller vendor footprint. In exchange, they run more infrastructure and must build idempotent handlers. Combining least-privilege roles, lease-verified job updates and a deliberately small metadata contract gives you reliable delivery orchestration without a third party storing the raw bodies.
References
- PostgreSQL documentation:
SELECTlocking clause (FOR UPDATE,NOWAIT,SKIP LOCKED), https://www.postgresql.org/docs/current/sql-select.html - PostgreSQL documentation: UUID type and
gen_random_uuid()(core since PostgreSQL 13), https://www.postgresql.org/docs/current/datatype-uuid.html - PostgreSQL documentation:
pgcrypto, https://www.postgresql.org/docs/current/pgcrypto.html - Stripe documentation: webhooks, signatures, retries, https://docs.stripe.com/webhooks
- Hookdeck, Guide to Stripe Webhooks: Features and Best Practices, https://hookdeck.com/webhooks/platforms/guide-to-stripe-webhooks-features-and-best-practices
Originally published at https://instawebhook.com/blog/architecting-a-bring-your-own-byo-database-schema-for-webhook-ingress-balancing
Top comments (0)