Outboxes and database-backed jobs

A transactional outbox is a table of messages to deliver, written in the same database transaction as the change that calls for them. A shop can store an order and its acknowledgement intention together, then let a background worker deliver the acknowledgement. If the application stops after the commit, the intention is still there for another worker to find.

That closes one gap and leaves another. The receiver can accept a message before the worker records that acceptance. If the worker stops between the two, recovery sends the message again. An outbox makes the work durable; safe repetition depends on the receiver as well.

Every example follows Mina’s order 103: one blue mug at 20.00 and two green teas at 4.50, total 29.00. The order is pending and has no payment. The new work, order-103-ack, says that the shop received the order. It does not say that Mina has paid.

We will first follow that acknowledgement across the database boundary, then give several workers a way to claim and recover it. The SQL uses PostgreSQL. The failure paths and clock times are illustrative; they are not recordings of a running mail service.

The gap between a commit and a send

Suppose the purchase handler calls an email provider as part of placing the order. It must choose whether to send before or after its database commit. Neither order makes the two systems atomic.

Calling the provider between BEGIN and COMMIT does not enlarge the database transaction to include email. The provider may accept the message and the next SQL statement may fail. The database can roll back its rows, but it cannot undo the provider’s action.

Sending after the commit avoids announcing a rolled-back order. It leaves the other path: the order exists, but the application stops before sending. A retry of the purchase request must recover its existing result rather than create another order. Retries and uncertain commit outcomes develops that request protocol. The acknowledgement needs its own durable record of unfinished work.

The transactional outbox

Instead of calling the provider, the purchase transaction inserts a row into outbox. It commits that row with the order, its lines and the stock decrements. A separate process, the relay, later reads committed work and makes the external call.

The order and its delivery intention now share the database’s commit outcome. The later provider call remains outside that transaction.

If the transaction aborts, there is no committed outbox row to deliver. If it commits, the relay can find the intention even if the API never receives the commit reply. This assumes the order and intention have the durability the application requires; a table whose rows disappear after a crash cannot hold the promise.

An outbox describes messages to deliver. A database-backed job describes work to execute, such as generating an invoice or sending a notification. The same notification row can serve both roles. Neither name says that a worker has run, or that its external effect happened once.

CREATE TABLE outbox (
  work_id text PRIMARY KEY,
  kind text NOT NULL,
  payload jsonb NOT NULL,
  state text NOT NULL DEFAULT 'ready'
    CHECK (state IN ('ready', 'running', 'done', 'failed', 'cancelled')),
  available_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  claim_generation bigint NOT NULL DEFAULT 0,
  claimed_by text,
  lease_until timestamptz,
  completed_at timestamptz,
  CHECK ((state = 'running') =
         (claimed_by IS NOT NULL AND lease_until IS NOT NULL))
);

The purchase transaction inserts the following row before its existing COMMIT. An insertion failure aborts the purchase too; it must not be caught and ignored while the order is committed.

INSERT INTO outbox (work_id, kind, payload)
VALUES (
  'order-103-ack',
  'order_received',
  '{"order_id":103,"customer_id":1,"total":"29.00",
    "message":"Order received; payment still pending"}'
);

Give the work a stable identity

The purchase request token, purchase-103, identifies the caller’s logical purchase. The resulting order number is 103. The work ID order-103-ack identifies this one acknowledgement. A worker may claim that work several times without making it a new acknowledgement.

Keep the ID and intended payload fixed through those attempts. If the total is reconstructed from mutable catalogue prices a day later, the “same” work could say 31.00 instead of 29.00. Here the row captures the agreed total and the meaning of the message when the order is created. A real notification also needs a deliberate policy for recipient details and template version: capture what must remain historical, and identify what should be resolved at delivery time.

The primary key rejects a second row with the same work ID. It does not check that an existing row has the same payload. If recovering a repeated purchase encounters the existing acknowledgement, recover and compare the stored intention through the request protocol; do not silently accept a changed payload with ON CONFLICT DO NOTHING.

Why delivery can repeat

The relay has three jobs: read unfinished work, make the call, and record the result. A crash can fall between any two. Suppose the receiver accepts order-103-ack, then the worker stops before marking the row done.

The shop’s row still looks unfinished after the receiver accepts. Recovery has to try again, because it would look unfinished after a crash before the call too. A missing reply has the same problem: it supplies no proof that the receiver did nothing.

Marking the row done before making the call reverses the risk. A crash between the mark and the call leaves finished work that was never delivered. Recording completion afterwards preserves recovery, but leaves a window in which delivery can repeat.

This is why “exactly once” needs a named boundary. The outbox transaction can create one durable intention. The relay can attempt delivery repeatedly. Neither fact proves one external business effect. Nor does a row by itself promise eventual success: a running recovery process, a usable receiver and a policy for failures must keep moving it forwards.

Make repeated delivery safe

Suppose the receiver offers this explicit contract: it atomically records the work ID, checks the payload, performs its effect and saves the result. A repeat with the same ID and payload returns that result. A repeat with a different payload is rejected.

The decision belongs where the effect happens. If the receiving service changes its own database, it can insert a processed-message record and make the domain change in one transaction there. A unique key on the message ID selects the first application; a duplicate recovers the prior result. If it then sends another remote message, that next boundary needs its own recovery protocol.

For an external API, use the API’s documented idempotency contract, including key scope, concurrent requests, failures and retention. Stripe’s API v1 contract, for example, compares parameters and may prune keys once they are at least 24 hours old. Reusing a pruned key starts a new request. That is a useful concrete contract to inspect, not a guarantee supplied by every provider.

An ordinary email send is not assumed to provide such deduplication. A local sent flag cannot make it do so: the flag is the very write that can be missing after acceptance. Where the receiver offers no suitable protection, choose explicitly between possible duplicates, possible omission and reconciliation using other outcome evidence. Changing the retry ID does not resolve uncertainty.

Know where the guarantee ends

A provider’s acceptance means what its contract says. It may mean “queued for sending”, not “arrived in Mina’s inbox”. A broker’s acknowledgement means the broker accepted the message under its durability rules, not that a consumer completed the business work. Track those milestones separately when the service promise depends on them.

Keep deduplication evidence for at least as long as a delivery may be retried or deliberately redriven. If a week-old failed job is replayed after its receiver has forgotten the ID, the old protection has ended. Reconcile the unknown outcome or obtain a service-specific recovery path before repeating an effect that must not happen twice. Deleting a finished row and recreating it under a new ID also discards that connection to the earlier outcome.

Claiming work with several workers

A single relay can be replaced after failure, and several can share a queue. They need a way to decide who will work on each row. A plain SELECT followed later by an UPDATE leaves a race: A and B can both read “ready” before either records a claim.

A claim records temporary authority to work on a row. A lease gives that authority a deadline, so a lost worker cannot hold the row forever. Each successful claim increments a generation, making the new authority distinguishable from every earlier claim of this work.

The PostgreSQL statement below locks a bounded set of eligible rows and records their claims before releasing those locks. $1 is the worker ID. Keep this transaction short and commit it before doing any external work.

BEGIN;
WITH candidates AS (
  SELECT work_id
  FROM outbox
  WHERE state IN ('ready', 'running')
    AND available_at <= clock_timestamp()
    AND (lease_until IS NULL OR lease_until <= clock_timestamp())
  ORDER BY available_at, work_id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE outbox AS j
SET state = 'running',
    claimed_by = $1,
    claim_generation = j.claim_generation + 1,
    lease_until = clock_timestamp() + interval '1 minute'
FROM candidates AS c
WHERE j.work_id = c.work_id
RETURNING j.work_id, j.kind, j.payload,
          j.claim_generation, j.lease_until;
COMMIT;

FOR UPDATE holds each selected row against a competing claimant. SKIP LOCKED lets another worker pass rows it cannot immediately lock. The update changes the selected rows while the locks are still held. Only start work after the claim commit succeeds; if that commit’s result is uncertain, recover authority from the stored row before proceeding.

The row lock ends at the claim transaction’s commit, before the remote call begins. The stored lease then keeps the row ineligible for another worker until its deadline. A concurrent poll can skip a locked row, so receiving zero rows does not establish that the queue is empty. Holding the transaction open across the remote call would keep a connection occupied throughout an unpredictable wait, yet still permit duplicates if the call succeeded before rollback.

ORDER BY chooses a polling preference, not a completion order. A worker can skip the first row, another can take longer, and a row repeatedly locked by other work can be delayed. Neither fairness nor strict business order follows from this query. General lock scope, waits and deadlocks belong in Locks, contention and deadlocks.

Recovering a lost worker

The lease is a recovery rule, not proof that a process is alive. Suppose A claims generation 7 at 10:16 UTC on 20 October, with expiry at 10:17. A pauses past that deadline, and B takes generation 8. When A resumes, it is an old worker even if its original call succeeded.

A completion update must name the current claim, as well as the work. The following transaction first acquires the row lock; its next statement samples the database clock after that possible wait. $1 is the work ID, $2 the worker ID and $3 the generation returned by claiming.

BEGIN;
-- Acquire the row before sampling time for the decision below.
SELECT work_id FROM outbox WHERE work_id = $1 FOR UPDATE;

UPDATE outbox
SET state = 'done', completed_at = clock_timestamp(),
    claimed_by = NULL, lease_until = NULL
WHERE work_id = $1 AND state = 'running'
  AND claimed_by = $2 AND claim_generation = $3
  AND lease_until > clock_timestamp()
RETURNING work_id;
COMMIT;

Require one returned row. Zero means that this worker cannot record completion: its claim may have expired, another generation may have replaced it, or the job may already be terminal. Stop using that claim and inspect the durable state. A successful external call does not entitle an old worker to overwrite the new owner.

Renewal uses the same lock-then-guard protocol, setting lease_until to a new deadline only while the current claim remains unexpired. Retry scheduling and failure recording also use the owner and generation guard. Otherwise an old worker’s late error could put B’s active job back into the ready queue.

Expiry does not stop the old worker

A can pause between checking its lease and making a call. Its network request can remain in flight while B reclaims the row. Even a worker that checks perfectly cannot recall that request once sent. The guard in the shop database protects the job record; receiver deduplication protects a repeated acknowledgement.

The SQL uses the database’s wall clock, clock_timestamp(), for eligibility and deadlines. Worker clocks do not decide claims. PostgreSQL’s now() stays at transaction start, which is unsuitable for a transaction that has waited before checking expiry. Keep statements and transactions bounded, and account for wall-clock adjustments: a clock jump can shorten a lease or delay recovery. No timing choice makes external safety dependably follow from prompt expiry.

Longer leases give a slow worker more room but delay recovery after loss. Shorter leases recover sooner but increase overlapping attempts when healthy work runs late. Heartbeats extend a healthy claim, subject to the same guard. They reduce unnecessary takeovers; a missed heartbeat cannot tell a dead process from a paused one.

Fencing stale work

Some jobs modify an external resource whose owner can change. Such a receiver may support fencing: it remembers the highest authority generation it has accepted and rejects lower generations. If B’s generation 8 is accepted first, a delayed generation-7 operation from A is refused. Repeating generation 8 is not lower, so the fence alone does not stop B repeating an effect; work-ID deduplication still has a separate job.

The token order must belong to the protected resource. This article’s per-job generations 7 and 8 can fence successive claims of this one job if the resource is exclusive to it. They cannot order unrelated jobs touching the same resource: another job might also have generation 7. Allocate authority in the resource’s own scope and preserve that order through recovery.

The receiver must check the token and perform the operation atomically. Checking a fence in the shop and then calling a provider leaves another gap. A lower generation can also arrive before the higher one has reached the receiver; a fence rejects it only after the receiver has learnt the higher authority. Fencing prevents that later stale operation, while deduplication prevents repeating one intention. They answer different questions.

When the order of work matters

Independent acknowledgements can often finish in either order. Changes to one order may need more care. An “order received” event records something that happened. A “dispatch this order” command asks for an action whose validity can change before a worker runs it. A cancellation does not erase the earlier event, but it may make the dispatch command invalid.

If changes for one order must be applied in sequence, assign a per-order sequence under that order’s transaction protocol and carry it to the receiver. Then choose the rule there. A history consumer that needs every event may hold sequence 3 until sequence 2 arrives. A projection of current state may accept a newer version and reject older ones. Those are different business promises.

A timestamp, an increasing message ID or the claim query’s ORDER BY does not implement either rule. Several workers can overtake each other after claiming. Define the unit of ordering, serialise or gate delivery for it where needed, and have the receiver detect gaps or stale versions. Keep the stable work ID as well: a sequence establishes position; the work ID identifies a repeat.

Retrying and retiring work

A failed attempt needs an outcome decision. A temporary provider failure can be scheduled later through available_at. An invalid payload needs diagnosis. A timed-out call may already have succeeded, so it needs safe repetition or reconciliation. None becomes a known non-effect merely because the worker labels the attempt “failed”.

Use bounded backoff with jitter for retryable failures, and coordinate it with any retries in the provider’s client library. Otherwise many recovered workers can hammer the same failing receiver, or layers of retries can multiply the calls. Cap attempts or elapsed retry time according to the service promise, then retain exhausted work in a failed state with enough evidence for a deliberate redrive.

Cancellation and deadlines need a worker protocol too: coordinate their database changes with claims, recheck validity at the point the business requires, and define what to do about in-flight work. A receiver-enforced deadline or cancellation contract may be necessary when a late action is unacceptable. A compensating action, when available, is new work with its own identity and failure cases.

Keep recovery observable

A queue with a hundred unfinished rows might be healthy; a queue with one three-day-old acknowledgement might be broken. Track the age of the oldest eligible work, time from creation to completion, expired and repeated claims, and failures by reason. Keep receiver references and uncertain outcomes so an operator can distinguish “never attempted” from “possibly accepted”.

A redrive should preserve the work ID and fixed payload when it continues the same intention, and record who restarted it and why. Check the receiver’s deduplication horizon first. A corrected intention may need a new identity and an explicit relationship to the failed one; merely changing the payload behind the old ID breaks the recovery contract.

Bound poll frequency, batch size and worker concurrency so recovery does not overwhelm the database or receiver. Index the eligible subset as the table grows, and clean up completed work only under a policy that preserves the outcome evidence still needed. A broker may earn its place through fan-out, delivery features or throughput. The handover from the shop database to that broker still has the same commit-and-send gap.

A row-change stream can transport committed outbox rows without this polling query. It does not decide which application intention a row means, how the consumer deduplicates it, or whether the final action succeeded. Native stream setup and its recovery obligations belong in PostgreSQL logical replication and change feeds.

Checking the examples

The SQL and retained checks use owned disposable databases. The purchase-and-outbox transaction, rollback counterexample and ordinary claim updates were checked in PGlite 0.5.8, a WebAssembly build of PostgreSQL 18.3. Separate native PostgreSQL 18.6 sessions check competing claims, skipped locks, expiry and stale completion. The fixed UTC times, receiver deduplication and fencing are models of stated contracts, not observations of those services.

The SQL experiment creates its own temporary tables and checks the purchase boundary and claim guards in one session. It does not simulate concurrent clients or call an external provider. Run it with psql -v ON_ERROR_STOP=1 -f outbox-experiment.sql on a disposable PostgreSQL database.