Outbox Pattern for ERP Reliability

An ERP system is usually the system of record for the transaction, and increasingly it also needs to tell the rest of the business — a warehouse system, a notification service, a data warehouse — that something happened: an order was created, a shipment was confirmed, an invoice was posted. The naive way to do that is to write the record and publish the event in the same request handler. It works in every demo and fails the first time the publish call times out after the database commit already succeeded, leaving downstream systems with no idea the order exists. This is the dual-write problem, and the outbox pattern is the standard fix.

The dual-write problem

Writing to a database and publishing to a message broker (or calling a webhook) are two separate operations against two separate systems, and there's no way to make them atomic without a distributed transaction coordinator. If the DB write commits and the publish fails, you've created an order with no downstream notification — an invisible order until someone reconciles it. If you publish first and the DB write then fails or rolls back, you've told the world about an order that doesn't exist. Neither ordering is safe on its own, and retrying blindly risks either losing the event or duplicating it.

The outbox table

The fix is to make the event write part of the same local database transaction as the business data — instead of calling out to a message broker, you insert a row into an "outbox" table in the same database, same transaction, same commit. If the transaction commits, both the order and its outbox event exist; if it rolls back, neither does. A separate relay process then reads unpublished rows from the outbox table and forwards them to the actual message broker, marking them as sent once the broker acknowledges. The relay can be a simple polling worker, or it can use change data capture (CDC) — tools like Debezium tail the database's write-ahead log directly and stream new outbox rows to Kafka without a polling loop at all.

BEGIN TRANSACTION;

INSERT INTO SalesOrder (OrderNbr, CustomerID, OrderTotal, Status)
VALUES ('SO-10432', 'CUST001', 4200.00, 'Open');

INSERT INTO OutboxEvent (EventId, AggregateType, AggregateId, EventType, Payload, CreatedAt, PublishedAt)
VALUES (
  NEWID(), 'SalesOrder', 'SO-10432', 'OrderCreated',
  '{"orderNbr":"SO-10432","customerId":"CUST001","total":4200.00}',
  GETUTCDATE(), NULL
);

COMMIT TRANSACTION;
-- A relay process polls WHERE PublishedAt IS NULL, publishes to the
-- broker, then sets PublishedAt — or a CDC tool reads the WAL directly.

Idempotent consumers are not optional

The outbox pattern guarantees at-least-once delivery, not exactly-once — the relay can crash after publishing but before marking the row as sent, and will republish it on restart. That means every consumer of these events has to be idempotent: processing the same "OrderCreated" event twice must produce the same end state as processing it once, not a duplicate shipment or a duplicate notification. In practice this means consumers track which event IDs they've already processed (a dedup table keyed on the event ID, checked before acting on a new one) rather than assuming delivery is clean.

-- Consumer side: check-then-act, inside one transaction
BEGIN TRANSACTION;

IF NOT EXISTS (SELECT 1 FROM ProcessedEvents WHERE EventId = @EventId)
BEGIN
    INSERT INTO ProcessedEvents (EventId, ProcessedAt) VALUES (@EventId, GETUTCDATE());
    -- do the actual work: create shipment record, send notification, etc.
    INSERT INTO Shipment (OrderNbr, Status) VALUES (@OrderNbr, 'Pending');
END

COMMIT TRANSACTION;

How this compares to distributed transactions

The alternative most people reach for first is a two-phase commit (2PC) across the database and the message broker, so both either commit or roll back together. In practice this is rarely used for this problem: most message brokers (Kafka included) don't support XA/2PC well or at all, 2PC coordinators are a single point of failure and add latency to every transaction, and 2PC doesn't compose well across more than two participants. The outbox pattern sidesteps all of that by never requiring atomicity across two different systems — it only needs atomicity within the ERP's own database, which every relational database already gives you for free, and it accepts eventual consistency (the event might be published a few seconds after the commit) as an explicit tradeoff instead of paying for a much more fragile form of strict consistency.

Wrapping up

Publishing events reliably out of an ERP comes down to refusing to treat "write to the database" and "notify the world" as one atomic-in-spirit operation when they're really two separate systems that can fail independently. Write the event to an outbox table in the same transaction as the business data, relay it asynchronously via polling or CDC, and build every consumer to be idempotent since at-least-once delivery is the guarantee you actually get — not exactly-once. It's a less elegant guarantee than a distributed transaction promises on paper, but it's the one that holds up when a broker call times out at 2am.