---
title: "Transactional Outbox: Dual-Write, Idempotent Consumers"
description: "Closing dual-write with an outbox, suppressing the at-least-once repeats it creates, and encrypting the personal data inside the event."
url: https://sade.dev/en/systems/transactional-outbox-dual-write-and-idempotent-consumption/
lang: en
author: "Muhammet Şafak"
published: 2026-06-22
updated: 2026-09-06
section: System
tags: ["architecture","messaging","idempotency","reliability","security"]
summary: "An outbox collapses dual-write into a single write but gives no free guarantee — it gives at-least-once. Polling vs CDC, multi-relay with SKIP LOCKED, ordering, pruning processed_messages, the exactly-once illusion, and field-level encryption."
---

# Transactional Outbox: Dual-Write, Idempotent Consumers

> An outbox collapses dual-write into a single write but gives no free guarantee — it gives at-least-once. Polling vs CDC, multi-relay with SKIP LOCKED, ordering, pruning processed_messages, the exactly-once illusion, and field-level encryption.

The symptom was small: one or two invoices a day sat in the database but the consumer never saw them. The user said "I issued it," and there was no trace on the other side. Always at night, always during a deploy or a network blip. Once I laid the picture out, the cause was clear — the `INSERT` into the database and the `publish` to the queue were two separate systems, with no guarantee between them.

This post is the systems side of the **transactional outbox** pattern that closed that gap, the **at-least-once** repeats it opened in return, and finally encrypting the personal data that flows through the event at the **field level**. I wrote the decision-chain side as [a log on muhammetsafak.com.tr](https://muhammetsafak.com.tr/en/blog/from-dual-write-to-outbox-idempotent-consumption-and-field-encryption); here I collect the decisions you have to make for the pattern to be durable in production.

## Dual-write: you can't stretch a transaction across two systems

The crux in one sentence: your local database transaction doesn't cover RabbitMQ. The classic code is dangerous *because it works most of the time*:

```php
DB::transaction(function () use ($invoice) {
    $invoice->save();                 // 1) write to MySQL
    $this->publishToRabbit($invoice); // 2) publish to the queue
});
```

There are two distinct failure modes:

- `save()` succeeds, `publish` drops on a network error → the invoice is in the DB, the event isn't. **Lost event.**
- `publish` succeeds, then the transaction `rollback`s for another reason → the event went out, no counterpart in the DB. **Phantom event.**

`DB::transaction` doesn't save you, because commit/rollback only wraps the MySQL side; the broker isn't part of that transaction. The practical way to hold two systems in one atomic step is to reduce the write to **a single system**.

## Outbox: write to one system first, let a separate process publish

The idea is plain: instead of pushing the message straight to the queue, write it as a row into an `outbox` table **inside the same transaction**. The invoice and the event either both exist or both don't, in the same commit — dual-write collapses into a single write.

```sql
CREATE TABLE outbox (
    id            BINARY(16)   NOT NULL,        -- event id = idempotency key
    aggregate_id  BIGINT       NOT NULL,        -- ordering and partition key
    topic         VARCHAR(120) NOT NULL,
    payload       JSON         NOT NULL,
    status        ENUM('pending','published') NOT NULL DEFAULT 'pending',
    created_at    DATETIME(6)  NOT NULL,
    published_at  DATETIME(6)  NULL,
    PRIMARY KEY (id),
    KEY idx_dispatch (status, created_at)        -- the relay scan goes through this index
);
```

The write is no longer the business code's concern:

```php
DB::transaction(function () use ($invoice) {
    $invoice->save();
    Outbox::write('invoice.issued', $invoice->id, $this->payload($invoice));
});
```

A separate **relay** does the queue push: it reads the `pending` rows, `publish`es them to RabbitMQ, and stamps them `published` on success. The critical fact here: the relay can fall into the "I published but crashed before stamping it `published`" state. So while the outbox solves dual-write, it gives you **no free guarantee** — it gives you **at-least-once**. The message isn't lost, but it can repeat. Everything else arranges itself around this fact.

## How do you feed the relay: polling or CDC?

The relay can see `pending` rows two ways.

**Polling** — the relay scans the table periodically:

```sql
SELECT id, topic, payload
FROM outbox
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;
```

Simple, works on every database, low operational overhead. The cost is two things: **latency** up to the scan interval (a 1s poll = ~1s of queue latency) and an idling query. Without the `idx_dispatch` index this scan gets expensive as the table grows; even with it, you trade poll frequency against DB load. At small-to-medium volume, polling is the **right** answer — tune it frequent enough to keep latency acceptable, sparse enough not to hammer the DB (in practice 200ms–1s).

**CDC** (change data capture) — the relay listens to the database's **WAL/binlog** rather than the table (Debezium is the typical tool). Every `INSERT` into `outbox` turns into an event almost instantly; polling latency and idle scanning disappear. The cost is operational: binlog access, a connector process, one more moving part. I turn CDC on when latency genuinely matters (sub-second) or when volume strains polling; otherwise I count polling's simplicity as an advantage.

> Rule: your latency budget and your operational budget conflict. CDC buys you latency and charges you an infrastructure piece in return. Start with polling; switch to CDC when there's a measured reason.

## Multiple relays: collision-free dispatch with `SKIP LOCKED`

A single relay is a bottleneck and a single point of failure. Running multiple relays over the same table creates a new risk: two of them grab the same row and publish the same message twice. The naive fix is to lock the table — which kills the parallelism.

The right tool is `SELECT ... FOR UPDATE SKIP LOCKED`. Each relay claims a batch of rows **by locking them**; instead of queuing behind rows another relay has locked, it **skips** them:

```sql
BEGIN;
  SELECT id, topic, payload
  FROM outbox
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 100
  FOR UPDATE SKIP LOCKED;     -- skip rows another relay holds, don't wait

  -- publish this batch, then:
  UPDATE outbox SET status = 'published', published_at = NOW(6)
  WHERE id IN (...);
COMMIT;
```

Without `SKIP LOCKED`, the relays queue for the same rows and the parallelism effectively runs serially. With it, each relay pulls a disjoint set and all of them move at once. PostgreSQL and MySQL 8+ support it.

A subtlety: if the relay crashes between `publish` and `UPDATE ... published`, the row stays `pending` and gets **republished**. That's acceptable — we're already at at-least-once; the fix is on the consumer side, below. The dangerous order is the reverse: stamping `published` first and then `publish`ing. A crash there produces a **lost event** — exactly what we ran from. So the order is **fixed**: `publish` first, stamp second.

## Ordering: what you can guarantee, what you can't

"Let events arrive in the order they were written" is an intuitive expectation, but global ordering is expensive and usually unnecessary. What you actually need is **per-aggregate** order: the same invoice's `created` event should arrive before its `updated`; the order of two different invoices relative to each other is nobody's concern.

You get this by partitioning on `aggregate_id`: events with the same `aggregate_id` go to the same queue partition (or, in RabbitMQ, the same queue via a consistent-hash exchange), and a **single consumer** processes that partition in order. Different aggregates flow in parallel.

```
outbox (in created_at order)
   │  hash(aggregate_id) % N
   ├── partition 0 ──► consumer-0   (events for aggregates A,D — ordered)
   ├── partition 1 ──► consumer-1   (events for aggregate B    — ordered)
   └── partition 2 ──► consumer-2   (events for aggregate C    — ordered)
```

Two things still break ordering, and the design has to be ready for them: multiple relays under `SKIP LOCKED` can publish rows in a different order, and at-least-once repeats can cut in. So rather than leaning on a global order, you write the consumer to be resilient to **out-of-order and duplicated** events. The practical shield: put a monotonic `version`/`sequence` on the event and drop the stale one (lower version) at the consumer. If you genuinely need strict global ordering you drop to a single partition + single consumer — which then ties throughput to that one consumer; most systems don't want to pay that.

## Once you have at-least-once: the consumer must be idempotent

Because the relay can republish, you have to assume every event arrives at least once, sometimes more. The fix isn't to make the event unique — it's to set up the **consumer** so that processing the same event twice has the same effect as once. Write the `id` of every processed event into a dedupe table, and do the work **inside the same transaction**:

```php
DB::transaction(function () use ($event) {
    $inserted = DB::table('processed_messages')->insertOrIgnore([
        'message_id'   => $event['id'],
        'processed_at' => now(),
    ]);

    if ($inserted === 0) {
        return; // it's a repeat; produce no side effects
    }

    $this->applyBusinessEffect($event); // the real work — exactly once
});
```

The **unique** constraint on `processed_messages.message_id` is the heart of it: a second arrival of the same `id` is silently dropped by `insertOrIgnore`. Putting the business effect in the same transaction is mandatory — otherwise the "I processed it but crashed before stamping" gap opens. Where the dedupe key comes from, and designing a dedupe that survives a crash, is a topic of its own; I covered it in [a separate note](/en/notes/idempotency-duplicate-delivery).

If the side effect is an external system (an e-invoice integrator, a payment API), dedupe alone isn't enough — because the side effect is outside the transaction. There you have to **carry the key to the external service**: pass the same `id` as an idempotency key to the integrator, or ask "does this already exist?" before sending. I worked through that boundary in detail in [the JIT reservation post](/en/systems/race-conditions-and-gaps-in-sequential-numbering), where sealing the number before the external call turns recovery into a lookup.

## `processed_messages` can't grow forever

The dedupe table accumulates a row per event; if you don't prune it, it becomes a performance problem of its own. The key observation: you don't need to keep an event `id` forever — only as long as the **window in which a repeat could arrive**. Relay retries and broker redeliveries are on the order of hours, not days.

In practice I pick a fixed **retention window** (say 7 days — comfortably above the longest possible redelivery) and prune older rows:

```sql
DELETE FROM processed_messages
WHERE processed_at < NOW() - INTERVAL 7 DAY
LIMIT 10000;   -- small batches at a time, keeping replica lag and locks in check
```

If `message_id` is a UUID the table and its index grow; pruning keeps that bounded. When choosing the window, frame the rule backwards: *how late could the latest redelivery arrive?* Make a safe upper bound of that your retention. Set the window too short and a late repeat misses the dedupe, so the side effect runs a second time.

## Why exactly-once is usually an illusion

In a distributed system "exactly-once delivery" is tempting but can't be guaranteed end to end. The reason is the same dual-write, at the broker boundary: if the consumer did the work but crashed before getting its `ack` to the broker, the broker redelivers the message — because it never saw the `ack`. "I processed it" and "I reported that I processed it" are two separate steps, and a crash can fall between them. This is the practical face of the Two Generals problem.

So the goal isn't exactly-once **delivery**, it's exactly-once **effect**. And you get that by combining two cheap guarantees:

```
at-least-once delivery  +  idempotent consumer  =  effectively-once
```

That is, you stop trying to deliver exactly once and instead make the **repeat harmless**. Under systems that market "exactly-once," this is usually exactly what's there: at-least-once plus a dedupe layer. Look for the guarantee in the effect, not in the delivery.

## Personal data flowing through the event: field-level encryption

Once the outbox settles in, a new surface appears: the events' payload carries personal data — customer name, tax ID, address — and that payload now sits in a **durable** table (`outbox`) and passes through the broker. If I leave the data as-is, I'm copying GDPR/KVKK-scoped fields, unencrypted, into more than one place.

I didn't want to encrypt the whole payload — I needed fields like `topic`, `aggregate_id`, and `invoice_id` in the clear for relay routing and observability. The need is **field-level** encryption: only the sensitive fields encrypted, the rest in the clear. I marked the sensitive fields in the schema with `x-gdpr-sensitive` and encrypted only those at serialization time:

```php
$schema = [
    'invoice_id'    => ['x-gdpr-sensitive' => false],
    'customer_name' => ['x-gdpr-sensitive' => true],
    'tax_id'        => ['x-gdpr-sensitive' => true],
    'total'         => ['x-gdpr-sensitive' => false],
];

$payload = collect($raw)->map(fn ($value, $field) =>
    ($schema[$field]['x-gdpr-sensitive'] ?? false)
        ? Crypt::encryptString((string) $value)  // only the sensitive field is encrypted
        : $value
)->all();
```

That the schema declares the sensitive field is no accident — combine it with schema validation at the edge of the queue and both questions ("which field is personal data, and is it the right type?") get answered [at the edge](/en/notes/validate-schema-at-the-edge). The real engineering decision isn't the encryption itself, it's the three questions around it:

**Key management and rotation.** Laravel's `Crypt` uses AES-256 keyed by `APP_KEY`. Bind yourself to a single key and rotation becomes a nightmare: the new key can't open outbox rows written with the old one. The fix is to store a **key version** (key id) alongside the encrypted value and keep a keyring that recognizes several keys at once — new writes use the current key, old values decrypt with their own version. In Laravel, `key` + `previous_keys` in `config/app.php` is exactly this; decryption tries the older keys in turn. That's why you can rotate without migrating data.

**Searching an encrypted field.** `Crypt::encryptString` produces different ciphertext on every call (a random IV), which is correct for security but makes `WHERE tax_id = ?` impossible. If you need equality search you add a **blind index**: keep a deterministic HMAC of the searchable field in a separate column and query that. The encrypted column carries confidentiality, the blind index carries searchability; they're separate columns because they do two separate jobs.

**Losing the key is losing the data.** Field encryption turns `APP_KEY` into an **availability** dependency: lose the key and the encrypted fields are permanent garbage. Key backup and access control are as much a part of the design as the encryption itself.

## When do you not build this pattern?

Outbox + idempotent consumption + field encryption isn't cheap: it brings a table, a relay process (or a CDC connector), a dedupe table and its pruning, plus key management. If you don't know what you're buying in return, don't pay for it. I don't build it when:

- **Event loss is tolerable.** If what you publish is a best-effort notification (cache invalidation, a "new content available" signal) and losing one or two doesn't matter, a direct `publish` is enough. You build the outbox because loss matters.
- **The write and the publish are in the same system.** If your target is the same database (there's no separate broker), there's no dual-write; you don't need an outbox.
- **Volume fits a single consumer and ordering is critical.** At low volume a single-consumer queue gives you both order and uniqueness simply; building the multi-relay-and-partition machinery is solving a problem you don't have.

The need for the pattern starts when three conditions arise **together**: event loss is unacceptable, production/consumption is parallel, and the data goes to a separate system (a broker). If all three aren't present, there's a plainer solution.

---

The three pieces come down to one discipline: **write the data to a single system, suppress repeats at the consumer, seal the sensitive field before it leaves.** The outbox makes trust in the queue unnecessary, idempotent consumption makes trust in delivery unnecessary, and field encryption makes trust in the payload unnecessary — each moves a guarantee out of the code and into the design.

## Frequently asked

**Should the relay be fed by polling or by CDC?**

Polling works on every database and costs almost nothing operationally, but it adds latency up to the scan interval — in practice a 200 ms to 1 s poll — plus an idling query. CDC reads the WAL or binlog instead and that latency nearly disappears, at the price of binlog access and one more moving part. Start with polling and switch when there is a measured reason.

**How long do rows have to stay in processed_messages?**

Only as long as a repeat could still arrive. Relay retries and broker redeliveries are on the order of hours, not days, so a fixed retention window comfortably above the longest possible redelivery — seven days, for example — is enough, pruned in small batches. Set the window too short and a late repeat misses the dedupe, so the side effect runs a second time.


## Sources

- [Pattern: Transactional outbox](https://microservices.io/patterns/data/transactional-outbox.html) — microservices.io
- [SELECT: the locking clause, FOR UPDATE and SKIP LOCKED](https://www.postgresql.org/docs/current/sql-select.html) — PostgreSQL
- [InnoDB locking reads: SKIP LOCKED](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) — MySQL
- [Encryption: gracefully rotating encryption keys](https://laravel.com/docs/12.x/encryption) — Laravel