Outbox Pattern for Reliable Event Publishing from ERP Transactions

TL;DR

• Bottom line: Write events to an outbox table in the same database transaction as business data, then use a separate relay process (polling or CDC) to publish events to a message broker. This solves the dual-write problem -- either both the data change AND the event are committed, or neither is. [src1]
• Key limit: At-least-once delivery only, not exactly-once. Consumers must be idempotent -- use event IDs and deduplication to handle inevitable duplicates. [src4]
• Watch out for: Dual-writing directly to both database and message broker (without outbox) is the #1 anti-pattern. If the broker write fails after DB commit, the event is lost forever. [src1]
• Best for: Any ERP integration requiring guaranteed event delivery from database transactions -- order creation, invoice posting, inventory adjustments, master data changes. [src3]
• Authentication: N/A for the pattern itself. Each ERP and message broker authenticates independently. The relay process needs credentials for both source database and target broker.

System Profile

The transactional outbox pattern is an architecture pattern that applies to any system using a relational database for business data that needs to reliably publish events to external consumers. It is the canonical solution for the dual-write problem in distributed systems. This card covers the general pattern, ERP-specific adaptations for Salesforce/SAP/NetSuite/D365, relay mechanisms (polling and CDC), and a decision framework comparing outbox vs CDC vs dual-write vs event sourcing.

API Surfaces & Capabilities

The outbox pattern does not define a specific API surface -- it defines how a database transaction atomically produces both business data and an event record. The relay mechanism bridges the outbox table to the message broker. [src1, src3]

Rate Limits & Quotas

Per-System Event Limits

Outbox Table Performance Limits

Authentication

Authentication Gotchas

• Debezium requires a dedicated PostgreSQL replication slot -- if the slot is dropped while Debezium is down, events are lost permanently. Monitor replication slot lag. [src2]
• Salesforce Platform Event subscriptions use CometD replay IDs -- if the subscriber falls behind by >3 days (72h retention), events are lost. Use high-volume platform events for 72h guaranteed retention. [src6]
• SAP Event Mesh requires BTP entitlement -- not included in base S/4HANA Cloud license. Check your BTP subscription before architecting around Event Mesh.

Constraints

• At-least-once delivery only -- exactly-once requires idempotent consumers or transactional message consumption on the consumer side
• Outbox table must be in the SAME database as business data -- cross-database transactions defeat the pattern's purpose
• CDC relay (Debezium) requires database support: PostgreSQL (logical replication), MySQL (binlog), SQL Server (CT/CDC), Oracle (LogMiner)
• Event ordering is per-aggregate (partition key) only -- no global ordering guarantee across different aggregates
• Polling relay has a latency floor equal to the poll interval -- cannot achieve sub-second latency with polling
• ERP-native event systems (Salesforce Platform Events, SAP Event Mesh) are NOT true outbox implementations -- they have platform-specific retention limits and volume caps

Integration Pattern Decision Tree

START -- Need reliable event publishing from ERP transactions?
|
+-- Do you control the database schema?
|   |
|   +-- YES (custom DB, self-managed ERP)
|   |   +-- Need sub-second latency?
|   |   |   +-- YES --> Debezium CDC relay with outbox table
|   |   |   +-- NO  --> Polling publisher (simpler, fewer moving parts)
|   |   +-- Already running Kafka?
|   |       +-- YES --> Debezium outbox event router (purpose-built)
|   |       +-- NO  --> Polling publisher to SQS/RabbitMQ/Service Bus
|   |
|   +-- NO (SaaS ERP: Salesforce, NetSuite, D365)
|       +-- Salesforce --> Platform Events + CDC (native outbox equivalent)
|       +-- SAP S/4HANA Cloud --> Event Mesh + Business Event Handling
|       +-- NetSuite --> SuiteScript afterSubmit + custom outbox record
|       +-- D365 F&O --> Business Events + Azure Service Bus
|       +-- D365 CE/Dataverse --> Business Events + Power Automate/Webhooks
|
+-- Pattern comparison:
    +-- Need domain-specific events? --> Outbox pattern
    +-- Need all row changes captured? --> CDC (without outbox)
    +-- Cannot modify application code? --> CDC on existing tables
    +-- Need audit trail of all state? --> Event sourcing

Quick Reference

Outbox Table Schema (PostgreSQL)

Debezium Outbox Event Router Configuration

Step-by-Step Integration Guide

1. Create the outbox table

Create the outbox table in the same database as your business data. Use BIGSERIAL for reliable ordering and a partial index on unpublished rows for efficient polling. [src4]

CREATE TABLE outbox_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sequence_id     BIGSERIAL NOT NULL,
    aggregate_type  VARCHAR(255) NOT NULL,
    aggregate_id    UUID NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    payload         JSONB NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at    TIMESTAMPTZ
);

-- Partial index: relay queries ONLY unpublished rows
CREATE INDEX idx_outbox_unpublished
    ON outbox_events (sequence_id)
    WHERE published_at IS NULL;

-- Cleanup job index
CREATE INDEX idx_outbox_published_at
    ON outbox_events (published_at)
    WHERE published_at IS NOT NULL;

Verify: SELECT COUNT(*) FROM outbox_events WHERE published_at IS NULL; --> expected: 0

2. Write business data and outbox event in same transaction

The critical guarantee: business data and outbox event are committed atomically. If either fails, both roll back. [src1]

import uuid, json
from datetime import datetime

def create_order_with_event(conn, order_data):
    """Write order + outbox event in single transaction."""
    order_id = uuid.uuid4()
    event_id = uuid.uuid4()

    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO sales_orders (id, customer_id, total, status)
            VALUES (%s, %s, %s, 'created')
        """, (str(order_id), order_data['customer_id'], order_data['total']))

        cur.execute("""
            INSERT INTO outbox_events (id, aggregate_type, aggregate_id,
                                       event_type, payload)
            VALUES (%s, %s, %s, %s, %s)
        """, (str(event_id), 'SalesOrder', str(order_id), 'OrderCreated',
              json.dumps({
                  'order_id': str(order_id),
                  'customer_id': order_data['customer_id'],
                  'total': str(order_data['total']),
                  'timestamp': datetime.utcnow().isoformat()
              })))
    conn.commit()
    return order_id

Verify: SELECT COUNT(*) FROM outbox_events WHERE published_at IS NULL; --> expected: 1

3. Implement the polling publisher relay

Background process polls outbox, publishes to broker, marks as published. Use FOR UPDATE SKIP LOCKED for safe concurrent relay instances. [src4]

from confluent_kafka import Producer
import json, time

def poll_and_publish(conn, producer, batch_size=100, poll_interval=2.0):
    while True:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT id, aggregate_type, aggregate_id, event_type, payload
                FROM outbox_events WHERE published_at IS NULL
                ORDER BY sequence_id ASC LIMIT %s
                FOR UPDATE SKIP LOCKED
            """, (batch_size,))
            rows = cur.fetchall()
            if not rows:
                time.sleep(poll_interval)
                continue

            published_ids = []
            for row in rows:
                event_id, agg_type, agg_id, event_type, payload = row
                producer.produce(
                    topic=f"outbox.event.{agg_type}",
                    key=str(agg_id),
                    value=json.dumps(payload),
                    headers={'event_id': str(event_id), 'event_type': event_type}
                )
                published_ids.append(str(event_id))
            producer.flush()
            cur.execute("""
                UPDATE outbox_events SET published_at = NOW()
                WHERE id = ANY(%s)
            """, (published_ids,))
        conn.commit()
        time.sleep(poll_interval)

Verify: SELECT COUNT(*) FROM outbox_events WHERE published_at IS NULL; --> expected: 0

4. Configure Debezium CDC relay (alternative to polling)

Use Debezium's outbox event router for sub-second relay without polling overhead. [src2]

{
  "name": "outbox-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "erp-db.example.com",
    "database.port": "5432",
    "database.user": "debezium_replication",
    "database.dbname": "erp_production",
    "topic.prefix": "erp",
    "table.include.list": "public.outbox_events",
    "transforms": "outbox",
    "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter",
    "transforms.outbox.route.by.field": "aggregate_type",
    "transforms.outbox.route.topic.replacement": "outbox.event.${routedByValue}",
    "transforms.outbox.table.expand.json.payload": "true",
    "plugin.name": "pgoutput",
    "slot.name": "outbox_slot"
  }
}

Verify: curl http://kafka-connect:8083/connectors/outbox-connector/status --> expected: {"connector":{"state":"RUNNING"}}

5. Implement outbox table cleanup

Published events must be cleaned up to prevent unbounded table growth. [src4]

-- Delete published events older than 7 days
DELETE FROM outbox_events
WHERE published_at IS NOT NULL
  AND published_at < NOW() - INTERVAL '7 days';

Verify: SELECT pg_size_pretty(pg_total_relation_size('outbox_events')); --> size should remain stable

Code Examples

Python: Idempotent consumer with deduplication

# Input:  Kafka message from outbox relay
# Output: Processed event with duplicate detection

def process_event(conn, message):
    """Idempotent event consumer -- deduplicates by event_id."""
    event_id = dict(message.headers()).get('event_id', b'').decode()
    payload = json.loads(message.value())

    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM processed_events WHERE event_id = %s", (event_id,))
        if cur.fetchone():
            return  # Skip duplicate
        handle_order_created(payload)
        cur.execute(
            "INSERT INTO processed_events (event_id, processed_at) VALUES (%s, NOW())",
            (event_id,))
    conn.commit()

JavaScript/Node.js: Outbox write with Knex.js

// Input:  Order data + Knex transaction
// Output: Order + outbox event committed atomically

const { v4: uuidv4 } = require('uuid'); // [email protected]

async function createOrderWithOutbox(knex, orderData) {
  return knex.transaction(async (trx) => {
    const orderId = uuidv4();
    await trx('sales_orders').insert({
      id: orderId, customer_id: orderData.customerId,
      total: orderData.total, status: 'created'
    });
    await trx('outbox_events').insert({
      id: uuidv4(), aggregate_type: 'SalesOrder',
      aggregate_id: orderId, event_type: 'OrderCreated',
      payload: JSON.stringify({
        order_id: orderId, customer_id: orderData.customerId,
        total: orderData.total
      })
    });
    return orderId;
  });
}

Salesforce Apex: Platform Events as outbox equivalent

// Platform Events participate in trigger transaction context
// If the transaction rolls back, events are NOT published

trigger OpportunityClosedWon on Opportunity (after update) {
    List<Order_Event__e> events = new List<Order_Event__e>();
    for (Opportunity opp : Trigger.new) {
        Opportunity oldOpp = Trigger.oldMap.get(opp.Id);
        if (opp.StageName == 'Closed Won' && oldOpp.StageName != 'Closed Won') {
            events.add(new Order_Event__e(
                Order_Id__c = opp.Id, Account_Id__c = opp.AccountId,
                Amount__c = opp.Amount, Event_Type__c = 'OpportunityClosedWon'
            ));
        }
    }
    if (!events.isEmpty()) {
        List<Database.SaveResult> results = EventBus.publish(events);
    }
}

Data Mapping

Outbox Event Schema Mapping Across ERPs

Data Type Gotchas

• Salesforce datetime is always UTC; SAP timestamps depend on user timezone preference in SU01. Always normalize to UTC in outbox payload. [src6]
• SAP BAPI amounts return strings with leading zeros (e.g., 0000001234.56) -- parse and normalize before inserting into outbox payload.
• D365 OData dates use ISO 8601 but may include timezone offset. Kafka consumers expecting UTC will misparse 2026-03-07T14:30:00+01:00.

Error Handling & Failure Points

Common Error Scenarios

Failure Points in Production

• Replication slot lag (Debezium): If Debezium falls behind, PostgreSQL retains WAL segments, eventually filling disk. Fix: monitor pg_replication_slots.active and pg_wal_lsn_diff(); alert when lag exceeds 100MB. [src2]
• Polling relay contention: Multiple relay instances without FOR UPDATE SKIP LOCKED process same events, causing duplicates. Fix: always use FOR UPDATE SKIP LOCKED. [src4]
• Outbox table vacuum bloat: High insert+delete rate causes autovacuum to fall behind. Fix: set autovacuum_vacuum_scale_factor = 0.01 on the outbox table. [src4]
• Salesforce Platform Event publish failure: EventBus.publish() silently fails if daily event limit reached. Fix: check Database.SaveResult errors and implement fallback logging. [src6]
• Consumer offset loss (Kafka): Consumer group rebalance causes offset reset, reprocessing all events. Fix: idempotent consumer with processed_events table. [src3]

Anti-Patterns

Wrong: Dual-write -- publish event directly after DB commit

# BAD -- event can be lost if publish fails after commit
def create_order_bad(conn, producer, order_data):
    order_id = save_order_to_db(conn, order_data)  # DB commit
    producer.produce('orders', json.dumps({'order_id': order_id}))
    # If this fails (network error, broker down), event is LOST

Correct: Outbox -- event written in same transaction

# GOOD -- event is guaranteed to be published eventually
def create_order_good(conn, order_data):
    with conn.cursor() as cur:
        order_id = uuid.uuid4()
        cur.execute("INSERT INTO sales_orders ...", (order_id, ...))
        cur.execute("INSERT INTO outbox_events ...", (order_id, 'OrderCreated', ...))
    conn.commit()  # Atomic -- both or neither

Wrong: Publishing inside the transaction (phantom events)

# BAD -- if publish succeeds but transaction rolls back,
# consumers act on phantom events
def create_order_phantom(conn, producer, order_data):
    conn.begin()
    order_id = insert_order(conn, order_data)
    producer.produce('orders', json.dumps({'order_id': order_id}))
    conn.commit()  # If this fails, event was already published!

Correct: Publish only from the relay, never from the application

# GOOD -- application writes to outbox only
def create_order_correct(conn, order_data):
    conn.begin()
    order_id = insert_order(conn, order_data)
    insert_outbox_event(conn, 'OrderCreated', order_id, order_data)
    conn.commit()
    # Relay process handles broker publishing separately

Wrong: Using timestamps for relay ordering

-- BAD -- concurrent transactions produce out-of-order timestamps
SELECT * FROM outbox_events WHERE published_at IS NULL
ORDER BY created_at ASC;

Correct: Using monotonic sequence for ordering

-- GOOD -- BIGSERIAL guarantees monotonic ordering
SELECT * FROM outbox_events WHERE published_at IS NULL
ORDER BY sequence_id ASC
FOR UPDATE SKIP LOCKED;

Common Pitfalls

• Not implementing consumer idempotency: Outbox guarantees at-least-once, meaning duplicates WILL happen. Fix: every consumer must track processed event_ids and skip duplicates. [src1]
• Polling without SKIP LOCKED: Multiple relay instances process the same batch, causing massive duplication. Fix: FOR UPDATE SKIP LOCKED in every poll query. [src4]
• Outbox table on a different database: Cross-database transactions defeat the pattern's atomicity guarantee. Fix: outbox table MUST be in the same database as business data. [src1]
• Ignoring Debezium replication slot health: A dropped or lagging slot silently loses events. Fix: monitor pg_replication_slots, alert on inactive slots and WAL lag > 100MB. [src2]
• Publishing full entity state in every event: Payload bloat slows Kafka and increases storage cost. Fix: publish only changed fields + entity ID; let consumers fetch full state if needed. [src4]
• No cleanup job for published events: Outbox table grows to millions of rows, degrading performance. Fix: scheduled DELETE of published events older than retention window (7 days). [src4]

Diagnostic Commands

# Check outbox depth (unpublished events) -- should be near 0
psql -c "SELECT COUNT(*) as pending, MIN(created_at) as oldest_pending FROM outbox_events WHERE published_at IS NULL;"

# Check outbox table size
psql -c "SELECT pg_size_pretty(pg_total_relation_size('outbox_events'));"

# Check Debezium connector status
curl -s http://kafka-connect:8083/connectors/outbox-connector/status | jq '.connector.state'

# Check PostgreSQL replication slot lag
psql -c "SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) as lag_bytes FROM pg_replication_slots;"

# Check Kafka consumer group lag
kafka-consumer-groups.sh --bootstrap-server kafka:9092 --group outbox-consumer --describe

# Check Salesforce Platform Event usage
curl -H "Authorization: Bearer $SF_TOKEN" \
  "https://yourorg.my.salesforce.com/services/data/v62.0/limits" | jq '.DailyStandardVolumePlatformEvents'

# Monitor relay throughput (events/minute)
psql -c "SELECT date_trunc('minute', published_at) as minute, COUNT(*) FROM outbox_events WHERE published_at > NOW() - INTERVAL '1 hour' GROUP BY 1 ORDER BY 1;"

Version History & Compatibility

When to Use / When Not to Use

Cross-System Comparison

Important Caveats

• The outbox pattern provides at-least-once delivery, NOT exactly-once. Any architecture claiming exactly-once from outbox has pushed complexity to the consumer side (transactional message consumption).
• ERP-native event systems (Salesforce Platform Events, SAP Event Mesh, D365 Business Events) are NOT true outbox implementations -- they approximate the pattern but have platform-specific retention limits, volume caps, and delivery semantics.
• CDC-based relay (Debezium) requires careful operational management -- replication slot monitoring, WAL retention policies, and connector health checks are production-critical.
• Outbox table cleanup is not optional. Without a retention policy, the table grows unbounded, causing query degradation and PostgreSQL autovacuum pressure.
• This card covers the pattern at architecture level. See related units for Salesforce Platform Events, SAP Event Mesh, NetSuite SuiteScript, and D365 Business Events for system-specific guidance.