A reliable sender stores a business change and its outgoing event in one local transaction, then delivers through a recoverable relay. The guarantee is that a committed change has a corresponding event, not that transport never duplicates it or every external action happens once. For enterprise integration and data-platform teams, this article traces an order approval into an analytics projection, defining atomic boundaries, relay choices and reproducible failure checks.
Define the layer at which success occurs
A transactional outbox persists outgoing events inside the business database. AWS describes the pattern as addressing separate commits for database changes and notifications, while requiring consumers to handle duplicates.[1] Here, the business table and outbox occupy one PostgreSQL 18 database, while the message system is separate. Two databases or an external API are not assumed to share that local transaction.
Writing the business change before sending leaves a failure window after commit and before delivery. Sending first can make downstream systems act on a change later rolled back. Retrying both calls changes failure probability without eliminating their separate outcomes. Persist the obligation to notify so that it survives or rolls back with the business change.
PostgreSQL documents transactions as grouping changes into an all-or-nothing operation whose unfinished intermediate states are not exposed to other transactions.[2] The order and event therefore need the same connection and transaction boundary. An application framework that opens another connection for the event, or inserts it in an after-commit callback, retains the original gap.
Distinguish business commit, message-system acceptance and consumer-effect commit. These may occur at different times and should be represented separately in interfaces. The example's successful command response promises the first stage and supplies a queryable event identity. Completion of the analytics update requires consumption evidence; a successful send call cannot establish it.
The semantic baseline is PostgreSQL 18 transactions, sequences and locking. The alternative relay references Debezium 3.3 Outbox Event Router, and delivery guarantees reference Kafka 4.1. The data structures and procedures are illustrative; the complete database-and-messaging system has not been run here. No throughput measurement is claimed.
Give commands, events and object versions separate identities
Construct order O42 for enterprise A at business version 1, status draft, quantity ten and unit price CNY 100, totaling CNY 1,000. Command K-approve-42 moves it to approved at version 2. Later, K-correct-42 changes quantity to nine and amount to CNY 900 at version 3. Analytics maintains a current approved-amount projection, not accounting revenue, and does not dispatch goods.
A command key identifies one business intention and survives request retries. An event identifier identifies one immutable message produced by that intention and survives relay retries. An object version orders O42's states. Do not generate a new event identity for each network send or substitute retry count for a business version. Approval produces E2 and correction E3; these display identifiers can be UUIDs in the actual table.
The business table needs tenant_id, order_id, version, status, quantity, unit_price and amount, with a defined decimal monetary type. A command table is unique by enterprise and command key and retains input digest, resulting version and event identity. The outbox retains event_id, enterprise and object identifiers, aggregate_version, event type, payload_schema_version, immutable payload and creation time.
This example produces one OrderStateChanged event per object version, so add uniqueness across enterprise, object, version and event type. If a real change produces several event types or multiple events of one type, include the relevant type or sequence in identity instead of copying a single-event rule. All business-key and command-key components are mandatory; unknown identities must not enter deduplication.
A polling implementation can add sent_at, attempts, next_attempt_at and the last error class, or keep a separate delivery-state table. Retries alter delivery metadata, never the business payload. Resubmitting a command key with quantity eleven instead of ten must return a conflict and preserve its first outcome, not silently rewrite E2.
Commit the order state and delivery obligation together
The approval transaction follows a fixed sequence: begin; insert or acquire the K-approve-42 command record; lock O42 and validate enterprise, current version and state; update it to approved version 2; insert complete event E2; save the command result; commit. Failure at any step rolls back everything. An event is permitted only when the business update actually matches one row satisfying the preconditions.
E2 expresses complete projection state: enterprise A, order O42, version 2, approved, quantity ten, unit price 100, amount 1,000 and schema version 1. E3 describes version 3, quantity nine and amount 900. Generate the payload from confirmed values inside the business transaction. Requerying the current order at relay time could package E3's state under E2's identity.
The command uniqueness constraint and transaction arbitrate concurrent copies of the same approval. A waiting caller reads the committed outcome and compares the input digest instead of creating another event. Different commands competing for O42 are governed by its row lock and version precondition. After deadlock or transaction conflict, inspect and retry the original command within bounds; a new key must not bypass an existing business fact.
If commit succeeds but the response is lost, the outcome is unknown to the client. Query enterprise A's K-approve-42. A matching committed record yields version 2 and E2; an unfinished original transaction requires waiting for a definite result. Reexecute under the same key only after the database can establish that the original did not commit. Absence of an in-memory success flag is not absence of a database write.
Preserve two invariants: each confirmed state change has exactly one corresponding logical business event, and uncommitted changes are not relayed. Several network deliveries do not increase the outbox's logical-event count. Acceptance inspects orders, command results and the event set together rather than only counting broker receipts.
Make polling recoverable before increasing concurrency
Path one polls records without confirmed delivery. A straightforward baseline uses small batches and controlled concurrency. Within a bounded transaction, select and lock a pending event, send its original content, record sent_at after the agreed durable-acceptance receipt, then commit. Without an unambiguous receipt, do not mark success; retain the event for inspection or retransmission.
If the send succeeds but the worker exits before committing sent_at, the next worker sends E2 again. This duplicate window is expected. Marking sent_at first instead creates permanent loss if the worker exits before sending. Holding a lock across a network call has a cost: slow communication lengthens transactions and lock occupancy. Bound send time and batch size and measure the effect on the business database.
Workers can divide queue candidates with FOR UPDATE SKIP LOCKED; PostgreSQL explicitly distinguishes this queue-like use from ordinary consistent reading.[3] Skipping locked E2 and finding E3 does not authorize out-of-order processing of one order. Row locking prevents simultaneous ownership of one row; it does not establish business-object ordering.
Where consumers require every version, select only each object's earliest unresolved event and block its successors when it fails. Stable object-based shards with serial relays, or object-level scheduling state, can enforce this. A quarantined predecessor remains unresolved. Removing it from the ordinary pending queue must not silently release later events.
A higher-throughput design can separate claiming from sending with leases, avoiding long-held locks. Persist a claim generation and allow completion updates only for the current generation. That prevents an expired worker overwriting delivery state, but cannot recall a message it already sent. Consumers still deduplicate. Restrict concurrency until the protocol is demonstrated; more workers do not inherently improve reliability.
An increasing identifier is not a commit watermark
A dangerous optimization saves last_id and reads only events with larger IDs. Transaction T1 obtains 101 but remains uncommitted. T2 obtains 102 and commits first. The relay reads 102 and advances last_id to 102. When T1 later commits, event 101 is newly visible but permanently excluded by id > 102.
PostgreSQL sequences define concurrent allocation, not transaction commit order; aborted transactions do not restore a gap-free series.[4] The timeline above is a constructed counterexample derived from those semantics. Identifier continuity cannot establish which business transactions succeeded, failed or remain open.
Polling should discover work through each event's unresolved state and an index suited to that predicate. IDs may assist fair ordering without proving completeness. Cleanup of sent events must preserve the agreed audit and recovery horizon. A log-based consumer should use its mechanism's position and recovery protocol, rather than presenting the business table's maximum key as a log position.
Object versions need their own generation boundary too. Here O42 is locked before its version increases, giving its valid changes a defined order. Different orders need no total order. Creation timestamps, UUIDs and partition offsets cannot simply replace that business contract. Sources in separate databases require an explicit scope within which versions are comparable.
Log capture trades polling for position and retention responsibilities
Path two still commits the order and outbox together, then relays through change data capture, or CDC. It avoids repeated scans of the pending set and suits environments with operational experience managing continuous log capture. It does not transform arbitrary table updates into well-defined business events: the explicit outbox payload retains that responsibility.
Debezium 3.3 Outbox Event Router uses id as event identity and aggregateid as the message key, with routing by aggregate type. Its normal input consists of inserts; updates are not expected outbox operations and deletes are filtered.[5] A polling table whose delivery state is continually updated cannot therefore be attached unchanged while claiming identical behavior.
Keep business events immutable on this route, placing operational delivery state in separate structures or connector evidence. Specify how additional object and schema versions enter the message. An object-based message key helps partition ordering, but partition changes, relay settings and consumer parallelism still require checks. Setting one field does not establish global order.
PostgreSQL 18 documents possible redelivery after logical-slot crash recovery and resource retention by unconsumed slots.[6] Monitor position progress and retained space and define reconstruction after required logs are lost. Restarting a connector is not sufficient recovery. Establish the missing interval, snapshot boundary and consumer deduplication evidence.
Choose from actual latency needs, database impact, recovery capability and ownership. Polling avoids a log-infrastructure dependency but adds scanning, cleanup and locking tradeoffs. CDC requires replication permissions, connector upgrades, position management and log-capacity planning. A promise of greater freshness is insufficient reason to migrate without the operational capability.
Converge repeated events on one correct consumer projection
Each subscription keeps receipt identity scoped by enterprise and event_id. Commit its deduplication marker and projection update in the same target-database transaction. E2 changes O42's current approved contribution from zero to 1,000. E3 replaces 1,000 with 900, adjusting the aggregate by negative 100. This is replacement of a current contribution, not an extra sale for every message.
After E2 then E3, redelivering either must leave 900. A previously processed event returns its known result. The same event_id with different content is quarantined as a conflict without overwriting initial evidence. Even a new event identity must not change the projection again when object version and state are identical. Different states for the same version require source reconciliation.
If E3 arrives first, this article's explicit complete-current-state contract permits recording version 3 and amount 900 immediately and treating later version 2 as stale. A consumer performing an independent version-2 approval action, shipment or incremental ledger entry cannot skip it. It must wait for history or follow a defined compensation process. A larger version is not permission to ignore intermediate business actions.
A consumer exiting after projection commit but before acknowledgement receives the event again and must be protected by committed deduplication evidence. Saving the marker first and updating the projection separately can label unfinished work complete. Updating the projection first and saving the marker separately can duplicate effects. The transaction must cover the actual result, not just its receipt log.
Kafka 4.1 distinguishes its internal transactional processing from coordination required for an external destination.[7] A target-database transaction does not cover sending email, operating machinery or calling another service. Such effects require remote operation keys, queryable receipts or explicit treatment of unknown outcomes. Do not generalize projection convergence into an end-to-end exactly-once claim for every business action.
Six failure tests inspect durable evidence at each boundary
Test one forces approval failure before E2 is inserted. Expect O42 to remain draft at version 1, with neither a successful command result nor E2, and no corresponding broker event. Inspect all three database records and relay evidence after rollback. If an orphan event exists, repair connection or transaction scope and repeat with successful approval as a control.
Test two disconnects after approval commit but before its response. Querying K-approve-42 must return the same version 2 and E2, and another request must not add an event. Retain command digest, object version and event count. If another event appears, repair command uniqueness and unknown-outcome recovery, then repeat concurrent calls with the same key.
Test three stops the relay after broker acceptance of E2 but before sent_at commits. Two deliveries may occur, while the projection remains 1,000; E3 subsequently changes it to 900. Retain both attempts, the common event identity and the consumer transaction. Repair atomic deduplication and replay. Absence of transport duplicates is not the sole passing condition.
Test four uses two database sessions to keep 101 uncommitted while 102 commits, then commits 101. Both events must eventually be discovered and sent. Seeing only 102 exposes the watermark defect. Inspect pending state, the query predicate and send records, repair per-event discovery or log recovery, and retest including a rollback that leaves an identifier gap.
Test five delivers E3 before E2 and adds a conflicting message with E3's identity but amount 950. The current-state projection must remain version 3 and 900 while retaining a conflict. A stepwise-action consumer instead pauses for the missing predecessor. Inspect the selected semantic contract, repair version and content handling, and include a valid newer state as a control against an implementation that drops everything.
Test six interrupts the message service while authorized orders continue. Every committed change must retain its outgoing event, unconfirmed events must survive cleanup, and recovery must preserve original identities. Inspect oldest pending age, event counts and final object sets as backlog drains. If logs or events have exceeded retention, enter explicit reconstruction; a restored connection alone does not establish complete delivery.
Plan capacity, cleanup and handoff around the oldest unfinished fact
Consider a separate capacity assumption: twenty new events per second during a thirty-minute messaging outage produce 20 × 1,800 = 36,000 pending events. If restored relay capacity is sixty per second while twenty per second continue arriving, net drain is forty per second. The theoretical catch-up time is 900 seconds, or fifteen minutes. Retries, slow objects and throttling are excluded; this is not a performance promise.
At an average payload size of 2 KiB, the backlog contains about 70.31 MiB of payload before row overhead, indexes, logs and replicas. Measure real peaks and unusually large messages. High average throughput does not release an object blocked behind a persistently failing predecessor. Observe backlog count, oldest age, retry distribution, quarantined objects and actual consumer completions together.
Clean up according to confirmed delivery stage and the agreed recovery horizon, not simply creation more than a day ago. Consumer deduplication retention must cover allowed replay history, and archived events must retain identity and schema version. Before replay, establish that the destination still has corresponding deduplication or version evidence. If it does not, arrange controlled reconstruction instead of treating forgotten old facts as new actions.
For a rollout, fix the example's business, command and event records and fault points on authorized test data, then compare old and new relay output sets. Avoid incompatible event identities during transition and do not assign new keys to already completed events. Rollback should stop the new route while preserving unfinished work, rather than deleting a queue to produce an apparently clean state.
The handoff records database and messaging versions, command and event identity, object-ordering scope, send-acceptance conditions, consumer transaction boundaries, unknown-outcome ownership, retention and all six fault inputs and retests. From CDC to trusted data products extends downstream semantic checks; webhook authentication and event ledgers covers trust at the receiving boundary. Reliable sending, authenticated receipt and correct business numbers each require their own evidence.
References
- [1] AWS Prescriptive Guidance — Transactional outbox pattern (accessed 2026-11-16)
- [2] PostgreSQL 18 — Transactions
- [3] PostgreSQL 18 — SELECT, Locking Clause / SKIP LOCKED
- [4] PostgreSQL 18 — Sequence Manipulation Functions
- [5] Debezium 3.3 — Outbox Event Router
- [6] PostgreSQL 18 — Logical Decoding Concepts
- [7] Apache Kafka 4.1 — Design, Message Delivery Semantics
