Transactional Outbox Pattern for Workflow Event Delivery

The Dual-Write Problem in Event-Driven Automation

When building systems that trigger downstream automated workflows—such as provisioning infrastructure, notifying external APIs, or kicking off an n8n pipeline after a user registration—engineers frequently encounter the dual-write problem.

A dual-write occurs when a system attempts to update a local database and publish an event to an external service (or webhooks engine) within the same logical operation. Consider this common application pattern:

1. Begin database transaction.
2. Insert a new customer record into the users table.
3. Make an HTTP POST call to an n8n webhook endpoint to trigger customer onboarding.
4. Commit the database transaction.

This pattern contains inherent race conditions and failure modes. If the database transaction fails and rolls back after the HTTP call completes, the downstream workflow executes for data that never existed in the database. Conversely, if the HTTP request times out or fails due to network degradation, the application might roll back a valid database mutation or commit without notifying the workflow engine. In either scenario, database state and workflow state diverge.

Wrapping the HTTP call inside the database transaction also introduces latency bottlenecks. Holding database connections open while waiting for external network responses starves connection pools and drastically degrades system throughput under high concurrency.

To achieve reliable execution without distributed transactions, backend architectures must decouple the database operation from the event dispatch step using the Transactional Outbox Pattern.

Architecture of the Transactional Outbox Pattern

The Transactional Outbox Pattern guarantees at-least-once event delivery by persisting outbound event payloads directly into the local database as part of the same atomic database transaction that updates business state.

Instead of making an outbound API call inside the business logic, the application writes an event record to an outbox table in the same relational transaction. Because both writes occur within the same local database boundary, they either both succeed or both roll back. Atomic guarantees are provided entirely by the database engine.

A separate asynchronous background worker (an outbox processor or Change Data Capture daemon) periodically reads unpublished events from the outbox table, delivers them to the workflow engine or message broker, and marks them as processed upon receiving a successful HTTP response.

+-----------------------------------------------------------------+
|                      Application Boundary                       |
|                                                                 |
|  +------------------+     1. Begin Transaction                  |
|  | Business Service |--------------------------------+          |
|  +------------------+                                |          |
|           |                                          v          |
|           | 2. Write Data               +--------------------+  |
|           +---------------------------->|  User Data Table   |  |
|           |                             +--------------------+  |
|           | 3. Write Event Payload                   ^          |
|           +---------------------------->+--------------------+  |
|           | 4. Commit Transaction       |    Outbox Table    |  |
|                                         +--------------------+  |
+---------------------------------------------------|-------------+
                                                    |             
                                                    | 5. Poll / Stream
                                                    v             
                                          +--------------------+  |
                                          |  Outbox Processor  |  |
                                          +--------------------+  |
                                                    |             
                                                    | 6. HTTP Post
                                                    v             
                                          +--------------------+  |
                                          |  Workflow Engine   |  |
                                          |    (e.g., n8n)     |  |
                                          +--------------------+  

Designing the Outbox Table and Worker

To implement the pattern cleanly in PostgreSQL, establish a dedicated schema for outbox operations. The outbox table needs enough metadata to track event state, payload structure, and delivery attempts.

CREATE TABLE outbox_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(64) NOT NULL,
    aggregate_id VARCHAR(64) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    retry_count INT NOT NULL DEFAULT 0,
    last_error TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at TIMESTAMPTZ
);

CREATE INDEX idx_outbox_pending 
ON outbox_events (created_at) 
WHERE status = 'PENDING';

When a business action occurs—for example, upgrading a customer subscription—the application inserts into outbox_events within the current transaction block:

BEGIN;

UPDATE subscriptions 
SET plan_id = 'enterprise_monthly', updated_at = NOW() 
WHERE customer_id = 'cust_90124';

INSERT INTO outbox_events (
    aggregate_type, 
    aggregate_id, 
    event_type, 
    payload
) VALUES (
    'subscription', 
    'cust_90124', 
    'subscription.upgraded', 
    '{"customer_id": "cust_90124", "new_plan": "enterprise_monthly", "effective_timestamp": 1711929600}'::jsonb
);

COMMIT;

Polling the Outbox Efficiently

A common issue with database-backed queues is lock contention between worker processes. PostgreSQL supports FOR UPDATE SKIP LOCKED, which allows multiple concurrent worker instances to fetch and lock non-overlapping rows without blocking each other.

The outbox processor queries pending events using a lock query:

WITH selected_events AS (
    SELECT id 
    FROM outbox_events
    WHERE status = 'PENDING'
    ORDER BY created_at ASC
    LIMIT 50
    FOR UPDATE SKIP LOCKED
)
UPDATE outbox_events
SET status = 'PROCESSING'
FROM selected_events
WHERE outbox_events.id = selected_events.id
RETURNING outbox_events.id, outbox_events.event_type, outbox_events.payload;

Once locked and fetched, the application dispatching code sends each payload to the target workflow webhook.

Integrating Outbox Processors with Workflow Engines

Workflow engines like n8n, Temporal, or custom webhook consumers must expect at-least-once event delivery. Network failures during response transmission might cause an outbox processor to resend an event that the workflow engine already accepted and processed.

To make event processing idempotent at the destination layer:

1. Pass the Outbox UUID: Include the outbox event id in the HTTP header or payload as an idempotency key (e.g., X-Event-ID: 7b34f6e1-9538-4e89-8d1a-4638 standard).
2. Deduplicate at the Workflow Endpoint: Maintain an execution key log inside your workflow system or cache layer (e.g., Redis). Check whether the X-Event-ID has been handled in the last 24–72 hours before executing state-changing workflow nodes.

Here is an example NodeJS polling handler operating against the outbox query:

async function processOutboxBatch() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    const { rows: events } = await client.query(`
      WITH selected AS (
        SELECT id FROM outbox_events
        WHERE status = 'PENDING'
        ORDER BY created_at ASC
        LIMIT 25
        FOR UPDATE SKIP LOCKED
      )
      UPDATE outbox_events
      SET status = 'PROCESSING'
      FROM selected
      WHERE outbox_events.id = selected.id
      RETURNING outbox_events.id, outbox_events.event_type, outbox_events.payload;
    `);

    await client.query('COMMIT');

    for (const event of events) {
      try {
        await axios.post('https://automation.internal/webhook/subscription-events', event.payload, {
          headers: {
            'X-Event-ID': event.id,
            'X-Event-Type': event.event_type,
            'Content-Type': 'application/json'
          },
          timeout: 5000
        });

        await pool.query(`
          UPDATE outbox_events 
          SET status = 'COMPLETED', processed_at = NOW() 
          WHERE id = $1
        `, [event.id]);
      } catch (err) {
        await pool.query(`
          UPDATE outbox_events 
          SET status = 'PENDING', 
              retry_count = retry_count + 1, 
              last_error = $2
          WHERE id = $1
        `, [event.id, err.message]);
      }
    }
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Outbox worker transaction error:', err);
  } finally {
    client.release();
  }
}

Polling vs. Change Data Capture (CDC)

While SQL-based polling with FOR UPDATE SKIP LOCKED works reliably for moderate workloads (hundreds of events per second), polling directly impacts database IOPS and transaction log size at high volume. Two architectural options exist for scaling outbox workers:

1. Polling-Based Outbox

• Pros: Simple to write, zero external infrastructure requirements, works directly on existing PostgreSQL connections.
• Cons: Adds query load to primary database, higher latency (polling frequency dependent).
• Best For: Microservice-to-workflow automation with under 100 events/sec.

2. Change Data Capture (CDC) with Logical Replication

• Pros: Zero polling query load on target application tables, real-time event streaming latency (sub-second).
• Cons: Requires configuring PostgreSQL logical replication slots and running tools like Debezium or custom WAL decoders.
• Best For: High-throughput distributed pipelines operating at high event volumes.

With CDC, PostgreSQL writes changes in the outbox_events table directly to the write-ahead log (WAL). The CDC tool reads the WAL stream asynchronously, converts outbox inserts into Kafka or HTTP events, and dispatches them without issuing SELECT or UPDATE queries against the primary database.

Outbox Maintenance and Table Pruning

Unchecked growth of the outbox_events table will eventually cause index bloat and degrade performance. A production implementation requires a deterministic retention strategy for historical events.

Instead of issuing costly DELETE statements on hot production tables continuously, implement range-based table partitioning or a scheduled pruning cron job targeting historical timestamps:

-- Scheduled maintenance query running during off-peak hours
DELETE FROM outbox_events
WHERE status = 'COMPLETED'
  AND processed_at < NOW() - INTERVAL '7 days';

For high-scale setups, partition outbox_events by date (RANGE (created_at)), dropping old partition tables directly via DROP TABLE to instantly reclaim disk space without generating table bloat.

Summary

Directly calling external automation webhooks from inside application transactions introduces point-of-failure risks that lead to inconsistent state across your architecture. Implementing the Transactional Outbox Pattern ensures that business state writes and downstream workflow dispatches remain atomic and resilient.

By leveraging PostgreSQL's FOR UPDATE SKIP LOCKED primitive or streaming the write-ahead log via CDC, backend engineers can construct event pipelines that provide strict execution guarantees without compromising performance or stability.