# Transactional Outbox

> Write the outgoing message in the same transaction as the business change; a relay publishes and marks it sent - the commit and the message become one fact, not two hopes.

- kind: pattern
- category: data
- aliases: Outbox
- canonical: https://aipatterns.dev/design-patterns/transactional-outbox
- inspired-by: https://microservices.io/patterns/data/transactional-outbox.html

## Intent

A service must change its database and tell the world - publish an event, send an email, call a webhook. Doing both as separate operations is the dual-write bug: send mid-transaction and you may announce data that never committed; send after commit and a crash in between loses the message forever. The fix is to write the message into an outbox table inside the same transaction as the business change, and let a separate relay publish it afterwards.

## Forces

- **Atomicity** vs **two systems**: the database commits atomically; the message broker does not participate, so the message must become database data first.
- **Deliver once** vs **deliver at least once**: the relay cannot atomically publish and mark-sent, so it chooses between risking duplicates or risking loss - and duplicates are the only survivable choice.
- **Immediate publish** vs **orderly publish**: polling adds latency; reacting to the transaction log is fast but is infrastructure you now operate.
- **Service simplicity** vs **relay machinery**: the pattern removes the bug from business code and concentrates it in one relay everyone can inspect.

## Therefore

**In the same transaction, write the business row and an outbox row; afterwards, a relay reads unmarked outbox rows, publishes each, and marks it sent - and every consumer tolerates redelivery.**

The outbox row is the message: its insertion commits with the business fact, so "database committed" and "message exists" become one event. The relay publishes and then marks - if it dies between the two, the message is published again on restart. That makes publishing at-least-once by construction, which is why this pattern drags in its sibling: consumers must be idempotent ([Idempotent Consumer](https://aipatterns.dev/design-patterns/idempotent-consumer.md)).

## Structure

```mermaid
flowchart TD
  A[Business transaction] --> B[Write business row]
  A --> C[Write outbox row]
  B --> D[Commit]
  C --> D
  D --> E[Relay polls unmarked rows]
  E --> F[Publish message]
  F --> G[Mark row sent]
  F -- relay dies here --> E
```

### Participants

- **Service transaction** (`placeOrder`): writes the order and the outbox row, commits once. Never calls the broker directly.
- **Outbox** (`outbox` table): rows with the message payload, the destination, and a `sent` marker. Read by the relay, written only inside business transactions.
- **Relay** (`Relay`): polls unmarked rows in order, publishes each, marks it sent. Small, stateless apart from its position, and the only component that talks to the broker.
- **Consumer**: receives messages at least once and deduplicates on the message id.

## Use it when

- A state change must reliably produce a message: `user.created` after the insert, `order.shipped` after the status update, a webhook after a subscription change.
- The alternative is sending inside or right after the transaction - the first announces uncommitted data, the second loses messages on crash.
- You own the writing service and can add a table; the fix is one table and one loop, not new infrastructure.
- Consumers can deduplicate, or the message can carry an idempotency key ([Idempotent Consumer](https://aipatterns.dev/design-patterns/idempotent-consumer.md)).

## Do not use it when

- The write and the notification live in the same database transaction - a trigger or an in-transaction listener already gives you atomicity; a relay would be latency for nothing.
- The message must go out within milliseconds, synchronously with the commit. Polling lag is part of the contract; if that is unacceptable, buy or operate log-tailing CDC and say so explicitly.
- There is no message: if "notify" just means updating another table in the same database, do it in the same transaction and skip the pattern.
- The business change is itself a saga step - the step's result message should go through this pattern, but the saga owns the ordering decisions ([Saga](https://aipatterns.dev/design-patterns/saga.md)).

## Application grill

**Inspect first:** the dual-write sites (database commit plus publish or external call) and today's loss window between them; the database's capabilities (Postgres and MySQL support SKIP LOCKED; DynamoDB has streams); existing queues and any CDC tooling (Debezium) already running in the organization.

**Missing pieces:** an outbox table in the existing database - no new infrastructure on the write side. A relay to publish: cron polling with SELECT ... FOR UPDATE SKIP LOCKED is the smallest reliable relay; a queue service receives the events; Debezium or streams is the high-throughput option with a Kafka-grade operational bill. Consumers receive at-least-once and must deduplicate (idempotent-consumer).

**Ask before you build:**

- **Scale:** what event rate and what publish lag is acceptable - is a one-second poll enough, or does CDC earn its complexity?
- **Performance:** what does relay polling cost the primary database at peak?
- **Reliability:** the relay crashes mid-publish - is at-least-once downstream accepted, and is per-aggregate ordering preserved?
- **Budget:** cron plus queue versus a CDC platform - whose team operates each?
- **Implementation:** who guarantees consumer idempotency, and where do poison messages land (dead letters)?

## Implementation steps

1. Add the outbox table: id, destination, payload, `created_at`, `sent` marker (or a `sent_at` null). One table per service, not one shared outbox.
2. In every code path that changes business state and must notify, write the outbox row inside the same transaction. This is the whole pattern - one code review rule: no broker calls from business transactions.
3. Write the message payload at write time, not "at send time" - the message must reflect exactly what committed, or the relay becomes a second brain re-deriving facts.
4. Implement the relay: select unmarked rows in `created_at` order (batch, e.g. 100), publish each, mark sent. One relay instance at a time per destination, or partition rows so two relays cannot race.
5. Accept at-least-once: if the relay dies after publishing but before marking, the row publishes again. Put the message id in every payload and require consumers to deduplicate.
6. Order within reason: per-entity ordering only. Global ordering across all entities is a queueing system, not an outbox.
7. Monitor the outbox age - oldest unsent row - not just the relay's liveness. A stuck relay is silent until someone asks why the downstream never heard about yesterday's orders.

## Example

Placing an order writes the order and its `order.placed` outbox row in one commit. The relay then publishes and marks. The demo also shows the failure this exists for: a crash between commit and publish loses nothing, because the message is already a committed row.

```ts title="orders.ts"
type OutboxRow = { id: number; event: string; payload: string; sent: boolean };

// The database: orders and outbox commit together or not at all.
// begin()/commit() stand in for a real transaction's BEGIN/COMMIT.
class Database {
  orders: string[] = [];
  outbox: OutboxRow[] = [];
  private nextId = 1;

  begin() {}
  commit() {}
}

class OrderService {
  constructor(private readonly db: Database) {}

  placeOrder(sku: string): string {
    this.db.begin();
    const orderId = `o-${this.db.orders.length + 1}`;
    this.db.orders.push(orderId);
    // Same transaction: the message is data, not a side effect.
    this.db.outbox.push({
      id: this.db.nextId++,
      event: "order.placed",
      payload: JSON.stringify({ orderId, sku }),
      sent: false,
    });
    this.db.commit();
    return orderId;
  }
}

const published: string[] = [];

class Relay {
  constructor(private readonly db: Database) {}

  tick(): void {
    for (const row of this.db.outbox) {
      if (row.sent) continue;
      published.push(`${row.event}: ${row.payload}`);
      row.sent = true; // die here and it publishes again - consumers dedupe
    }
  }
}

const db = new Database();
const orders = new OrderService(db);
const relay = new Relay(db);

const id = orders.placeOrder("tea-1");
console.log(`placed ${id}; outbox rows: ${db.outbox.length}`); // 1 - committed together

relay.tick();
console.log(`published: ${published}`); // ["order.placed: {\"orderId\":\"o-1\",...}"]
relay.tick(); // marked rows are skipped - no duplicate from a healthy relay

// The bug this pattern removes: a crash after commit, before any publish.
const crashed = new Database();
const crashedOrders = new OrderService(crashed);
crashedOrders.placeOrder("tea-2"); // committed, relay never ran
new Relay(crashed).tick(); // restart: the message is still there, and goes out
console.log(`recovered publish count: ${published.length + 1}`); // nothing lost
```

```python title="orders.py"
import json
from dataclasses import dataclass, field

@dataclass
class OutboxRow:
    id: int
    event: str
    payload: str
    sent: bool = False

@dataclass
class Database:
    """Orders and outbox commit together or not at all."""

    orders: list[str] = field(default_factory=list)
    outbox: list[OutboxRow] = field(default_factory=list)
    _next_id: int = 1
    _tx_rows: list[OutboxRow] = field(default_factory=list)

class OrderService:
    def __init__(self, db: Database) -> None:
        self._db = db

    def place_order(self, sku: str) -> str:
        order_id = f"o-{len(self._db.orders) + 1}"
        self._db.orders.append(order_id)
        # Same transaction: the message is data, not a side effect.
        self._db.outbox.append(
            OutboxRow(
                id=self._db._next_id,
                event="order.placed",
                payload=json.dumps({"orderId": order_id, "sku": sku}),
            )
        )
        self._db._next_id += 1
        return order_id

published: list[str] = []

class Relay:
    def __init__(self, db: Database) -> None:
        self._db = db

    def tick(self) -> None:
        for row in self._db.outbox:
            if row.sent:
                continue
            published.append(f"{row.event}: {row.payload}")
            row.sent = True  # die here and it publishes again - consumers dedupe

db = Database()
orders = OrderService(db)
relay = Relay(db)

order_id = orders.place_order("tea-1")
print(f"placed {order_id}; outbox rows: {len(db.outbox)}")  # 1 - committed together

relay.tick()
print(f"published: {published}")  # ['order.placed: {"orderId": "o-1", ...}']
relay.tick()  # marked rows are skipped - no duplicate from a healthy relay

# The bug this pattern removes: a crash after commit, before any publish.
crashed_db = Database()
crashed_orders = OrderService(crashed_db)
crashed_orders.place_order("tea-2")  # committed, relay never ran
Relay(crashed_db).tick()  # restart: the message is still there, and goes out
print(f"recovered publish count: {len(published) + 1}")  # nothing lost
```

```go title="orders.go"
package main

import (
	"fmt"
)

type OutboxRow struct {
	ID      int
	Event   string
	Payload string
	Sent    bool
}

// Database: orders and outbox commit together or not at all.
type Database struct {
	Orders []string
	Outbox []OutboxRow
	nextID int
}

type OrderService struct{ db *Database }

func (s OrderService) PlaceOrder(sku string) string {
	id := fmt.Sprintf("o-%d", len(s.db.Orders)+1)
	s.db.Orders = append(s.db.Orders, id)
	// Same transaction: the message is data, not a side effect.
	s.db.nextID++
	s.db.Outbox = append(s.db.Outbox, OutboxRow{
		ID:      s.db.nextID,
		Event:   "order.placed",
		Payload: fmt.Sprintf(`{"orderId":%q,"sku":%q}`, id, sku),
	})
	return id
}

var published []string

type Relay struct{ db *Database }

func (r Relay) Tick() {
	for i := range r.db.Outbox {
		row := &r.db.Outbox[i]
		if row.Sent {
			continue
		}
		published = append(published, row.Event+": "+row.Payload)
		row.Sent = true // die here and it publishes again - consumers dedupe
	}
}

func main() {
	db := &Database{}
	orders := OrderService{db}
	relay := Relay{db}

	id := orders.PlaceOrder("tea-1")
	fmt.Printf("placed %s; outbox rows: %d\n", id, len(db.Outbox)) // 1 - committed together

	relay.Tick()
	fmt.Printf("published: %v\n", published)
	relay.Tick() // marked rows are skipped - no duplicate from a healthy relay

	// The bug this pattern removes: a crash after commit, before any publish.
	crashed := &Database{}
	crashedOrders := OrderService{crashed}
	crashedOrders.PlaceOrder("tea-2") // committed, relay never ran
	Relay{crashed}.Tick()             // restart: the message is still there, and goes out
	fmt.Printf("recovered publish count: %d\n", len(published)+1) // nothing lost
}
```

```java title="Orders.java"
import java.util.ArrayList;
import java.util.List;

public class Orders {
    record OutboxRow(int id, String event, String payload, boolean sent) {}

    // The database: orders and outbox commit together or not at all.
    static final class Database {
        final List<String> orders = new ArrayList<>();
        final List<OutboxRow> outbox = new ArrayList<>();
        private int nextId = 1;
    }

    record OrderService(Database db) {
        String placeOrder(String sku) {
            String orderId = "o-" + (db.orders.size() + 1);
            db.orders.add(orderId);
            // Same transaction: the message is data, not a side effect.
            db.outbox.add(new OutboxRow(db.nextId++, "order.placed",
                    "{\"orderId\":\"" + orderId + "\",\"sku\":\"" + sku + "\"}", false));
            return orderId;
        }
    }

    static final List<String> published = new ArrayList<>();

    record Relay(Database db) {
        void tick() {
            for (int i = 0; i < db.outbox.size(); i++) {
                OutboxRow row = db.outbox.get(i);
                if (row.sent()) continue;
                published.add(row.event() + ": " + row.payload());
                db.outbox.set(i, new OutboxRow(row.id(), row.event(), row.payload(), true));
                // die here and it publishes again - consumers dedupe
            }
        }
    }

    public static void main(String[] args) {
        Database db = new Database();
        OrderService orders = new OrderService(db);
        Relay relay = new Relay(db);

        String id = orders.placeOrder("tea-1");
        System.out.printf("placed %s; outbox rows: %d%n", id, db.outbox.size()); // 1

        relay.tick();
        System.out.println("published: " + published);
        relay.tick(); // marked rows are skipped - no duplicate from a healthy relay

        // The bug this pattern removes: a crash after commit, before any publish.
        Database crashed = new Database();
        new OrderService(crashed).placeOrder("tea-2"); // committed, relay never ran
        new Relay(crashed).tick(); // restart: the message is still there, and goes out
        System.out.println("recovered publish count: " + (published.size() + 1));
    }
}
```

The demo database collapses the transaction into "rows are there or they are not"; in a real store the order insert and outbox insert sit inside one `BEGIN … COMMIT`, which is what makes the guarantee real.

## Trade-offs

**Gains**
- The database commit and the message become one atomic fact - the dual-write bug is gone by construction.
- Business code never touches the broker; failure to publish is one inspectable relay, not scattered `try/except`.
- The outbox is an audit log for free: every message the service owes the world is a row.

**Costs**
- Delivery is at-least-once; every consumer must deduplicate or every message must carry an idempotency key.
- Polling adds latency between commit and publish; log-tailing CDC removes it at the price of operating CDC.
- The relay needs its own discipline: one instance or partitioned rows, ordered reads, age monitoring.
- Message payloads are written at business time - schema evolution means old outbox rows with old payloads.

## Relations

- [Idempotent Consumer](https://aipatterns.dev/design-patterns/idempotent-consumer.md): the mandatory sibling - the relay's at-least-once delivery is only safe onto consumers that deduplicate.
- [Saga](https://aipatterns.dev/design-patterns/saga.md): saga steps announce their results through this pattern, so a step's state change and its message commit together.
- [Retry](https://aipatterns.dev/design-patterns/retry.md): the relay is a retry loop with a persistent cursor; transient publish failures re-publish the same row later, which is exactly the contract.
- [Command](https://aipatterns.dev/design-patterns/command.md): the outbox row is a command to the outside world - a persisted unit of work someone else will execute.

## Agent checklist

- [ ] The outbox row is written inside the same transaction as the business change - no broker calls from business code.
- [ ] The payload is computed at write time from committed data.
- [ ] The relay publishes then marks, tolerates its own crash, and runs single-instance or partitioned.
- [ ] Every message carries an id, and consumers are known to deduplicate.
- [ ] Outbox age (oldest unsent row) is monitored, not just relay liveness.