Stop Dual-Write Bugs: Master the Transactional Outbox Pattern

In modern backend systems, publishing domain events after updating a database is extremely common. However, doing this naively creates the infamous dual-write problem, which silently causes data inconsistencies across services.

In this article, we will explore why naive event publishing fails in production and how to implement the Transactional Outbox Pattern to achieve bulletproof reliability.

The Infamous "Dual-Write" Nightmare

Imagine you are building an e-commerce checkout service. When a customer places an order, your application needs to perform two distinct actions:

1. Save the new order record into your PostgreSQL database.
2. Publish an order.created event to RabbitMQ or Kafka so the inventory and notification services can process it.

Here is how a typical naive implementation looks in Node.js or TypeScript:

async function createOrder(orderData: OrderPayload) {
  // Step 1: Save to Database
  const order = await db.transaction(async (tx) => {
    return await tx.order.create({ data: orderData });
  });

  // Step 2: Publish Event to Message Broker
  await messageBroker.publish('order.created', { orderId: order.id });
}

At first glance, this code seems clean and straightforward. But in production, it contains a critical flaw known as the Dual-Write Problem.

Because your database and your message broker are two completely separate distributed systems, you cannot wrap them both inside a single atomic ACID transaction. This opens the door to two dangerous edge cases:

• Database succeeds, Broker fails: The database transaction commits successfully. But right before the app publishes to RabbitMQ, the network drops or the app pod crashes. The order exists in your database, but inventory is never reserved and no confirmation email is sent.
• Broker succeeds, Database rolls back: If you move messageBroker.publish inside the transaction block, the event might reach RabbitMQ before the database transaction completes. If the database transaction subsequently rolls back due to a constraint violation, external services will act on an order that technically never existed!

Relying on simple try/catch blocks or background Promise executions only masks the issue; it never guarantees consistency.

Enter the Transactional Outbox Pattern

The Transactional Outbox Pattern solves the dual-write problem by leveraging the database's native ACID capabilities to handle event publishing.

Instead of publishing messages directly to an external broker during the HTTP request, your application writes the event into a dedicated table (called the Outbox) inside the exact same database transaction that saves your business entities.

Since both the business record and the outbox record are created within the same database transaction, either both are committed or both are rolled back. Data consistency within the database is guaranteed to be 100% atomic.

Implementing the Outbox Pattern Step-by-Step

Let's look at how to build this in practice using SQL and TypeScript.

Step 1: Create the Outbox Table

First, define a simple outbox schema in your relational database:

CREATE TABLE outbox (
    id UUID PRIMARY KEY DEFAULT gen_random_policy(),
    aggregate_type VARCHAR(255) NOT NULL,
    aggregate_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

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

Step 2: Write Entities and Outbox Messages Atomically

Now, update your transaction logic. Whenever an order is created, insert a record into the outbox table within the same transaction:

async function createOrderWithOutbox(orderData: OrderPayload) {
  return await db.transaction(async (tx) => {
    // 1. Create the business entity
    const order = await tx.order.create({ data: orderData });

    // 2. Create the outbox record in the SAME transaction
    await tx.outbox.create({
      data: {
        aggregateType: 'Order',
        aggregateId: order.id,
        eventType: 'order.created',
        payload: { orderId: order.id, amount: order.totalAmount },
        status: 'PENDING',
      },
    });

    return order;
  });
}

Now, if the database transaction fails for any reason, the outbox record is rolled back automatically. No phantom events will ever escape to your message queue!

Step 3: Publish via a Background Worker (Relay)

With events safely persisted in the database, a separate background worker (or a lightweight cron process) periodically fetches pending outbox messages and dispatches them to your message broker:

async function processOutboxMessages() {
  const pendingMessages = await db.outbox.findMany({
    where: { status: 'PENDING' },
    take: 50,
    orderBy: { createdAt: 'asc' },
  });

  for (const message of pendingMessages) {
    try {
      // Send to RabbitMQ/Kafka
      await messageBroker.publish(message.eventType, message.payload);

      // Mark as PROCESSED
      await db.outbox.update({
        where: { id: message.id },
        data: { status: 'PROCESSED' },
      });
    } catch (error) {
      console.error(`Failed to dispatch event ${message.id}:`, error);
      // Optional: Update retry count or mark as FAILED after max retries
    }
  }
}

For high-throughput systems, instead of polling the database table, you can use Change Data Capture (CDC) tools like Debezium to read the database transaction log (e.g., PostgreSQL WAL) directly and stream outbox events into Kafka with zero DB polling overhead.

Crucial Rule: Prepare for At-Least-Once Delivery

Because network glitches can happen when the worker updates the outbox status from PENDING to PROCESSED, your worker might occasionally publish the same message twice.

Therefore, the Outbox Pattern provides at-least-once delivery. Your downstream microservices must design their event handlers to be idempotent—meaning processing the same event twice yields the exact same outcome as processing it once.

Summary

The Transactional Outbox Pattern is an essential architectural pattern for modern distributed applications. By substituting direct network calls inside HTTP handlers with atomic database writes, you eliminate dual-write bugs, prevent phantom events, and build a system that remains reliable even when external brokers experience downtime.