Transactional Outbox Pattern with MySQL, PostgreSQL and Kafka

The dual-write failure

This looks correct at first:

await bookingRepository.save(booking);
await kafka.publish('BOOKING_CONFIRMED', event);

It is not atomic.

Possible failure:

database commit    SUCCESS
Kafka publish      FAILED

The booking exists, but notification, audit or downstream systems never receive the event.

The reverse order is also unsafe:

Kafka publish      SUCCESS
database commit    FAILED

Now consumers see an event for state that does not exist.

Outbox transaction

I write the business state and event intent into the same database transaction.

START TRANSACTION;

INSERT INTO bookings(
  id, status, customer_id
) VALUES (
  'B1001', 'CONFIRMED', 'C901'
);

INSERT INTO outbox_events(
  id,
  aggregate_type,
  aggregate_id,
  event_type,
  payload,
  status,
  created_at
) VALUES (
  UUID(),
  'BOOKING',
  'B1001',
  'BOOKING_CONFIRMED',
  JSON_OBJECT(
    'bookingId', 'B1001',
    'customerId', 'C901'
  ),
  'NEW',
  NOW()
);

COMMIT;

Now either both records exist or neither exists.

Outbox table

CREATE TABLE outbox_events (
  id              CHAR(36) NOT NULL,
  aggregate_type  VARCHAR(80) NOT NULL,
  aggregate_id    VARCHAR(100) NOT NULL,
  event_type      VARCHAR(120) NOT NULL,
  payload         JSON NOT NULL,
  status          VARCHAR(20) NOT NULL,
  attempts        INT NOT NULL DEFAULT 0,
  available_at    DATETIME NOT NULL,
  created_at      DATETIME NOT NULL,
  published_at    DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_outbox_publish (
    status,
    available_at,
    created_at
  )
);

Concurrent publishers

For MySQL 8/PostgreSQL-style worker claiming I can use SKIP LOCKED.

START TRANSACTION;

SELECT id, aggregate_id, event_type, payload
FROM outbox_events
WHERE status = 'NEW'
  AND available_at <= NOW()
ORDER BY created_at
LIMIT 100
FOR UPDATE SKIP LOCKED;

The worker then publishes.

One practical approach is to mark rows PROCESSING with a lease before committing the claim transaction. That prevents a database lock from being held while waiting on Kafka network I/O.

NestJS publisher

for (const event of batch) {
  try {
    await this.kafka.send({
      topic: this.topicFor(event.eventType),
      messages: [{
        key: event.aggregateId,
        value: JSON.stringify(event.payload),
        headers: {
          'event-id': event.id,
          'event-type': event.eventType,
        },
      }],
    });

    await this.outbox.markPublished(event.id);
  } catch (error) {
    await this.outbox.reschedule(
      event.id,
      nextBackoff(event.attempts),
    );
  }
}

Why consumers still need idempotency

There is another failure window:

Kafka publish      SUCCESS
mark outbox row    FAILED

The publisher retries and publishes the same event again.

That is acceptable when consumers are idempotent.

Outbox gives at-least-once reliable publication, not magic end-to-end exactly-once business semantics.

CDC alternative

Instead of polling:

database outbox
     |
transaction log
     |
Debezium / CDC
     |
Kafka

CDC can reduce polling load and latency, but adds connector infrastructure and operational responsibility.

I choose polling or CDC based on platform maturity and throughput requirements.

Backpressure

If Kafka is unavailable, the outbox grows.

That is a good failure mode up to a point: business transactions can continue while publication is delayed.

But the backlog must be observable:

oldest NEW event age
NEW event count
publish rate
failure rate
attempt distribution

If the backlog grows without limits, the database itself becomes the next failure.

Event ordering

If ordering matters per aggregate, use the aggregate ID as Kafka key.

Parallel outbox publishers can otherwise publish different aggregates concurrently while preserving per-key ordering inside Kafka.

Cleanup

Published events should not remain forever unless required for audit.

Options:

• delete after retention period;
• archive to cheaper storage;
• partition outbox table by date;
• separate audit history from transport outbox.

Failure scenarios

Poison event serialization

Do not let one row block the complete outbox. Track attempts and quarantine/alert.

Publisher crash after claiming

Use leases/timeouts so another worker can reclaim.

Database transaction becomes large

Outbox payloads should contain event data, not huge binary objects.

Kafka unavailable for hours

Monitor disk/table growth and oldest-event age. The outbox is a buffer, not infinite storage.

Production checklist

• Business state and outbox row in one transaction.
• Stable event ID.
• Aggregate/business key.
• Indexed publish query.
• Concurrent safe claiming.
• Bounded retry/backoff.
• Idempotent consumers.
• Backlog metrics.
• Poison-event handling.
• Published-row cleanup.
• Load-test broker outage.
• Document event schema/version.

FAQ

Does Outbox guarantee exactly once?

No. It reliably records the intent to publish. Duplicate publication is possible.

Polling or Debezium?

Both work. Polling is simpler; CDC is attractive at higher scale or where the platform already operates connectors.

Why not use a distributed transaction?

Most modern Kafka/database architectures avoid XA-style distributed transactions because of coupling and operational complexity. Outbox keeps local atomicity and accepts idempotent asynchronous delivery.

Related architecture guides

• Kafka Retry Topics and DLQ

• Backpressure in Kafka and NestJS

  Architect's final take

  Transactional Outbox is one of the patterns I use most confidently because it solves a very specific failure window without pretending the database and broker share one transaction.