Transactional Outbox Pattern for SaaS: Fix Dual-Write Failures

Most SaaS reliability bugs that look like “the queue dropped a message” are really dual-write bugs. Your API handler commits a row in PostgreSQL, then publishes an event to Redis, SQS, Kafka, or a webhook fan-out. Those are two systems with no shared transaction. A process crash, a deploy, a broker timeout, or an ORM that opens a second connection between the statements leaves you in one of two ugly states: the database moved and nobody downstream knows, or a ghost event announces a change that never committed.

The transactional outbox pattern removes that gap. You write the business change and an outbox row in the same database transaction, then a separate relay publishes committed rows to the broker. Delivery becomes at-least-once by design; consumers stay idempotent. This article is a practical guide for SaaS teams building event-driven APIs, background jobs, and integration pipelines—without invented case studies or fake uptime numbers.

The dual-write problem in plain terms

A dual write is any request path that must update durable state in one store and notify another system. Classic SaaS examples:

• Mark an invoice paid, then emit invoice.paid so billing webhooks and email workers run.
• Create a tenant, then enqueue provisioning jobs for storage, search indexes, and feature defaults.
• Accept a Shopify sync result into your DB, then notify an internal ERP connector.
• Update inventory ownership, then publish to a cache invalidation channel.

If you “write then publish,” you can lose the publish after commit. If you “publish then write,” you can publish a change that later rolls back. Wrapping the publish in try/except does not fix either case. Retries without an idempotency story make both worse. AWS’s prescriptive guidance on the transactional outbox frames the same failure modes for microservices that touch a database and a message broker together.

How the transactional outbox pattern works

Three moving parts:

1. Domain write — the business row your API already owns.
2. Outbox insert — a durable message record in the same database, same transaction.
3. Relay — a worker (or CDC connector) that reads committed outbox rows and publishes to the broker, then marks them published (or deletes them) only after the broker acknowledges.

Because the outbox row commits atomically with the domain change, there is no interleaving where the invoice is paid but the event never existed. The relay only ever sees committed work. If the relay publishes and crashes before marking the row, it republishes later—that is at-least-once delivery, not a defect. Consumers must treat duplicate event IDs as safe no-ops.

Minimal schema that holds up

Keep the outbox boring and indexable:

CREATE TABLE event_outbox (
  id              BIGSERIAL PRIMARY KEY,
  event_id        UUID NOT NULL UNIQUE,
  aggregate_type  TEXT NOT NULL,
  aggregate_id    TEXT NOT NULL,
  event_type      TEXT NOT NULL,
  payload         JSONB NOT NULL,
  headers         JSONB NOT NULL DEFAULT '{}',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at    TIMESTAMPTZ,
  attempts        INT NOT NULL DEFAULT 0,
  available_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_error      TEXT
);

CREATE INDEX event_outbox_unpublished_idx
  ON event_outbox (available_at, id)
  WHERE published_at IS NULL;

event_id is the stable identity consumers dedupe on. aggregate_id helps with ordering keys on FIFO queues. available_at supports backoff for poison messages without blocking the whole table.

Store enough in payload for the consumer to act without re-reading mutable domain state when possible, but do not dump secrets, raw card data, or PII you are not allowed to retain in a message store. Prefer stable identifiers plus the fields the consumer actually needs. Version the event type name (invoice.paid.v1) when the shape changes in breaking ways so old relays and new consumers can coexist during deploys.

Insert inside the real transaction

The insert must ride the same connection and transaction as the domain write. An after_commit hook that opens a new session quietly reintroduces the dual write. In Django, that means using the same atomic() block (or the transaction you already hold). In SQLAlchemy, pass the same Session. NestJS’s outbox docs make the same point: refuse the database handle that autocommits; require the active transaction.

from django.db import transaction
from uuid import uuid4

@transaction.atomic
def mark_invoice_paid(invoice_id: str) -> None:
    invoice = Invoice.objects.select_for_update().get(pk=invoice_id)
    invoice.status = "paid"
    invoice.save(update_fields=["status", "updated_at"])

    OutboxEvent.objects.create(
        event_id=uuid4(),
        aggregate_type="invoice",
        aggregate_id=str(invoice.id),
        event_type="invoice.paid",
        payload={
            "invoice_id": str(invoice.id),
            "tenant_id": str(invoice.tenant_id),
            "amount_cents": invoice.amount_cents,
        },
    )

No broker call happens here. The request path stays short, which also helps connection pooling pressure—see our notes on PostgreSQL connection pooling for SaaS APIs.

Polling relay vs change data capture

Two common relay styles:

Polling relay

A worker claims unpublished rows with SELECT … FOR UPDATE SKIP LOCKED, publishes a batch, then sets published_at. Skip locked keeps multiple relays from fighting. Only mark published after the broker ACK. If you mark first, a crash loses messages; if you never mark after ACK, you spam duplicates forever (consumers must still be idempotent, but you should not manufacture unnecessary load).

-- claim a batch
BEGIN;
SELECT id, event_id, event_type, payload
FROM event_outbox
WHERE published_at IS NULL
  AND available_at <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 100;
-- publish each row to the broker, then:
UPDATE event_outbox
SET published_at = now(), attempts = attempts + 1
WHERE id = ANY(:claimed_ids);
COMMIT;

Polling is simple to operate on Postgres and pairs well with existing job runners like Celery. Treat the relay as a first-class worker with its own concurrency and backoff—the same discipline you use for other Celery and Redis background jobs.

CDC / log-tailing relay

Debezium (or a managed equivalent) tails the WAL and publishes inserts from the outbox table. You avoid constant polling load and often get lower lag, at the cost of connector operations. Filter to the outbox table only so you do not turn every domain row into a public event by accident. CDC does not remove the need for consumer idempotency: connectors and brokers still deliver at least once.

Delivery semantics you must design for

The outbox does not give you exactly-once magic. It gives you a reliable producer contract:

• At-least-once publish from relay to broker.
• At-least-once delivery from broker to consumers (unless you use a strictly once FIFO + carefully designed consumer, which is rare across an entire SaaS estate).
• Effectively-once side effects only when consumers dedupe by event_id (or an inbox table) and apply business changes idempotently.

Order is another subtlety. Concurrent transactions can commit in an order that does not match your application’s logical sequence. If a consumer needs per-aggregate order, partition by aggregate_id on a FIFO queue or process with a per-key lock. Global total order across aggregates is usually the wrong goal.

This producer-side reliability complements the consumer-side patterns in our guides on SaaS webhook reliability and Shopify webhook HMAC, retries, and idempotency: verify and ack inbound events quickly, then process with the same dedupe discipline.

Multi-tenant SaaS details that bite

Outbox rows are tenant data. Put tenant_id in the payload and enforce it the way you enforce everything else in a shared schema—row-level security, mandatory tenant filters in queries, and no cross-tenant relay credentials. A shared relay that publishes events without tenant context is how cache poisoning and wrong-tenant webhooks happen. For isolation patterns beyond the outbox table, see multi-tenant SaaS architecture and tenant isolation.

Also decide what belongs in the outbox versus direct cache writes. Invalidating Redis from the request path is another dual write. Prefer emitting an event and letting a dedicated invalidator run, or accept brief staleness with explicit TTLs—covered in our Redis caching for SaaS APIs guide.

Operations: what fails on day two

Getting the schema right is the easy half. Production pain shows up later:

• Silent relay death — backlog grows, nobody pages. Track a relay heartbeat and the age of the oldest unpublished row.
• Poison messages — one bad payload blocks a naive single-threaded poller. Use attempts, exponential available_at, and a dead-letter / quarantine lane with an owner.
• Table churn — endless inserts and deletes bloat indexes. Archive or partition published rows; keep the hot unpublished set small.
• Direct publish creep — a “temporary” broker.publish() in a hotfix reopens the dual write. Code review for broker calls outside the relay.
• Lag SLO — define p99 commit-to-publish lag that product can live with (seconds for UX emails, tighter for ledger-adjacent flows).

Minimum metrics worth alerting on:

1. Oldest unpublished row age
2. Unpublished row count (backlog)
3. p99 commit-to-publish latency
4. Relay publish errors by class
5. Relay heartbeat
6. Quarantined / dead-letter count

Retention policy matters as much as indexing. Keep unpublished rows forever until published or quarantined. For published rows, retain enough history to debug a bad deploy (often days to a few weeks), then archive to cold storage or drop. If you delete aggressively, leave event_id in an inbox/dedupe table on the consumer side so late redeliveries from the broker still no-op safely.

Load tests should include relay lag under burst writes: create thousands of domain commits in a short window and confirm oldest-unpublished age stays inside your SLO while consumers stay idempotent. That exercise surfaces missing indexes, under-provisioned relay concurrency, and accidental synchronous work inside the publish path faster than a quiet staging environment ever will.

When not to use an outbox

Skip the pattern when there is no second system—pure CRUD with no downstream listeners. Skip it when a managed integration already owns delivery (for example, a platform’s native event bus with strong delivery guarantees you already trust end to end). Prefer a saga or orchestration when the “transaction” truly spans multiple independent databases; an outbox only atomicizes work inside one local database. And if your only need is “retry this HTTP call,” an idempotency key on the public API may be enough for that endpoint—outbox and idempotency keys solve adjacent problems (outbound event reliability vs inbound request dedupe).

A pragmatic rollout checklist

1. Inventory request paths that write to the DB and then talk to a broker, cache, or webhook dispatcher.
2. Add an outbox table with a unique event_id and an unpublished partial index.
3. Move publishes into the same transaction as the domain write; delete direct broker calls from handlers.
4. Ship a relay with SKIP LOCKED (or CDC), backoff, and dead-letter.
5. Make every consumer idempotent on event_id.
6. Alert on oldest unpublished age and relay heartbeat before you call the migration “done.”

If you are designing a new SaaS API, integration layer, or event-driven backend and want a second set of eyes on dual-write paths, CodeSapient builds and hardens these systems end to end—from schema and workers to observability. See our development services or contact the team.

Key takeaways

• Dual writes between a database and a broker are a consistency bug waiting for a crash, not an edge case.
• The transactional outbox pattern stores the event in the same local transaction as the domain change, then relays asynchronously.
• Expect at-least-once delivery; invest in consumer idempotency and per-aggregate ordering where it matters.
• Polling and CDC both work—operate the relay like production infrastructure, with lag and heartbeat alerts.
• In multi-tenant SaaS, treat outbox rows as tenant-scoped data and keep broker publishes out of the hot request path.