A database transaction can make an order update and an outgoing event durable together. It cannot make a remote HTTP request commit atomically with that database transaction. That boundary is the reason an outbox worker needs both a safe claiming strategy and a deliberate approach to duplicate delivery.
This walkthrough uses PostgreSQL row locks and a persistent lease to explain the worker side. The SQL is illustrative and has not been executed as part of this article. It is a starting point for a test implementation, not a complete production queue.
Persist the intent with the business change
Write the business update and its outbox row in the same transaction. If that transaction rolls back, neither change survives. If it commits, a worker can discover the pending intent even if the application process stops immediately afterward.
A minimal outbox schema might look like this:
CREATE TABLE outbox (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
operation_key text NOT NULL UNIQUE,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending',
available_at timestamptz NOT NULL DEFAULT now(),
lease_until timestamptz,
claim_version bigint NOT NULL DEFAULT 0,
attempts integer NOT NULL DEFAULT 0
);
CREATE INDEX outbox_pending_idx
ON outbox (available_at, id)
WHERE status = 'pending';
The operation key identifies one intended business action. Decide its scope explicitly: sending a particular notification and issuing a refund are separate operations even when they refer to the same order. Reject an accidental reuse of a key with different semantics rather than silently treating the new request as equivalent.
PostgreSQL’s INSERT documentation explains conflict handling and the atomic insert-or-update behavior of ON CONFLICT DO UPDATE. That database guarantee does not extend to a downstream email or payment API.
Claim work in one short transaction
A read followed by an unrelated update lets competing workers select the same pending row. Instead, lock candidate rows and persist their claim in one statement:
WITH candidates AS (
SELECT id FROM outbox
WHERE status = 'pending' AND available_at <= now()
ORDER BY available_at, id
FOR UPDATE SKIP LOCKED
LIMIT 10
)
UPDATE outbox AS o
SET status = 'processing',
lease_until = now() + interval '2 minutes',
claim_version = o.claim_version + 1,
attempts = o.attempts + 1
FROM candidates AS c
WHERE o.id = c.id
RETURNING o.id, o.operation_key, o.payload, o.claim_version;
Commit this claim before making remote calls. The row lock protects selection during the transaction; the stored processing state and lease describe ownership after it ends. Keep batches small enough that work does not sit locally until its lease expires.
The SELECT locking documentation describes SKIP LOCKED as useful for competing queue consumers, while warning that skipping locked rows gives an inconsistent view. Use it for work selection, not a report that must include every eligible row. It also does not remove all possible database blocking.
Reject stale completion
Return the claim version to the worker. When recording success, require the same version and processing state:
UPDATE outbox
SET status = 'delivered', lease_until = NULL
WHERE id = $1
AND claim_version = $2
AND status = 'processing'
AND lease_until > now()
RETURNING id;
An empty result means the worker must not assume it still owns the local record. A recovery process can return expired processing rows to pending, with bounded attempts and an explicit exhausted state. Long tasks need lease renewal that checks the same claim version. Choose lease duration from actual execution behavior rather than copying the two-minute illustration.
A version check protects the local completion update. It cannot stop a stale worker from sending an HTTP request. Once leases can expire and work can be reclaimed, concurrent external execution remains possible.
Handle the external-write gap
Suppose the receiver accepts a request and the worker crashes before marking delivery. The outbox still appears incomplete. A later worker sends it again.
Use a receiver-supported idempotency key derived from the stable operation key where available, observing the receiver’s retention and parameter rules. Otherwise design receiver-side deduplication or reconciliation. Do not claim exactly-once external effects just because row claiming is atomic.
Test the failure boundaries
Before deployment, use synthetic data and two independent worker connections. Hold one claim transaction open and verify the second worker skips its locked candidates. Then test a worker crash after claiming, lease expiry during a slow request, recovery followed by stale completion, and receiver success with a lost response.
Measure both database state and actual receiver-side effects. Also review queue age, exhausted attempts, query plans, retention and table growth. This article provides the design and illustrative SQL; it reports no benchmark or successful runtime test.