PostgreSQL Idempotency Schema for Payment Webhook Events

Webhook retries are normal. Concurrent delivery is also possible when a timeout, worker retry, or network failure makes the sender uncertain about acknowledgement. Application code must therefore make repeated authentic events produce one durable business effect.

An in-memory set, cache-only lock, or SELECT followed by INSERT cannot provide this guarantee across processes. Put event identity and state transitions behind PostgreSQL constraints and transactions.

Model Event Identity

A minimal receipt table can look like this:

create table payment_event_receipts (
  tenant_id uuid not null,
  event_id text not null,
  order_id uuid not null,
  event_type text not null,
  payload_sha256 text not null,
  received_at timestamptz not null default now(),
  processed_at timestamptz,
  primary key (tenant_id, event_id)
);

The payload digest supports investigation without duplicating complete payment data in operational tables. It is not a replacement for HMAC verification. Authenticate the exact raw request and timestamp first, then parse the event and enter the database transaction.

Keep the local order reference unique inside the tenant too. Every query must include the trusted tenant derived from the merchant API key or connection—not a tenant value accepted from the webhook body.

Apply One Transaction

The consumer transaction should:

1. Insert the event receipt with ON CONFLICT DO NOTHING or handle a unique violation.
2. Stop business processing when the receipt already exists.
3. Lock the mapped local order using SELECT ... FOR UPDATE.
4. Compare merchant reference, gateway order ID, expected amount, and allowed current state.
5. Apply one forward state transition.
6. Insert a durable fulfilment or outbox record.
7. Mark the receipt processed and commit.

Do not mark an order paid when amount or identity differs. Store the exception and return the response appropriate to your contract; an authentic but invalid business event needs operational visibility.

The detailed flow is also available in the duplicate webhook fulfilment guide.

Queue Fulfilment Durably

Calling email, inventory, shipping, or account provisioning inside the database transaction creates a dangerous gap. The external call can succeed and the database can later roll back, or the database can commit and the process can crash before the call.

Use an outbox row written in the same transaction:

create table payment_outbox (
  id uuid primary key,
  tenant_id uuid not null,
  order_id uuid not null,
  event_id text not null,
  action text not null,
  created_at timestamptz not null default now(),
  delivered_at timestamptz,
  unique (tenant_id, event_id, action)
);

A worker claims pending rows, performs the side effect with its own idempotency key, and records completion. The unique action key prevents two workers from scheduling the same fulfilment for one event.

Handle Duplicates

If an authentic duplicate maps to an already processed receipt, return 2xx without repeating the order transition. If the same (tenant_id, event_id) arrives with a different payload digest, do not silently accept it as equivalent. Record a security anomaly with safe metadata and investigate the contract or sender.

Distinguish duplicates from multiple legitimate payment attempts. Two different event IDs may represent two verified payments connected to one purchase; that is an operations exception, not something to discard based only on order ID.

Test Concurrency

Send 20 concurrent copies of one signed fixture to multiple application instances. Assert that:

• One event receipt exists.
• One order transition exists.
• One outbox action exists.
• Every duplicate receives a controlled response.
• No deadlock or unbounded retry occurs.
• An amount mismatch produces no fulfilment.

Then simulate a crash after commit but before acknowledgement. The next delivery should find the existing receipt and return success without creating another effect. This is the failure idempotency is designed to survive.

Operate the schema as a financial control. Alert on payload-digest conflicts, authentic events that cannot map to an order, repeated database serialization failures, and outbox rows that remain undelivered beyond the normal service window. These are not ordinary duplicates and should not disappear behind a successful acknowledgement.

Define retention and archival by evidence need, merchant agreement, and applicable policy. Deleting receipt identity too early can allow a very late delivery to create a second effect; retaining complete payloads indefinitely creates unnecessary data exposure. Keep the minimum identifiers and audit fields needed to prove event handling, and protect any richer evidence separately.

Schema migrations also need concurrency tests. Adding or rebuilding a uniqueness rule on a live table can block event consumers or reveal historical duplicates. Rehearse the migration with production-like volume, decide how conflicting historical rows will be classified, and monitor event latency during rollout.