Change Data Capture (CDC) & Outbox Pattern

1. What It Is & Why It Exists

The Core Problem: The Dual-Write Desynchronization Trap

In distributed systems, microservices frequently need to update a primary database and simultaneously notify other systems (e.g. invalidating a Redis cache, updating an Elasticsearch index, or broadcasting an event to Apache Kafka):

Interactive Architecture Diagram
100%Fit100%
Synthesizing vector architecture diagram...

If the application attempts to execute two separate network write operations ("Dual-Writing"):

1. DB Succeeds, Broker Fails: Downstream consumers never receive the event (permanent data loss).
2. Broker Succeeds, DB Fails: Downstream consumers process ghost records that do not exist in the primary database.
3. Concurrent Races: Two concurrent updates execute in order $A \to B$ in SQL, but arrive in order $B \to A$ in Kafka, corrupting search indexes.

The First-Principles Solution: Transactional Outbox & Log-Based CDC

To guarantee atomicity without distributed locks, the event publication must be bounded inside the same local ACID database transaction as the business data mutation.

2. Core Mechanics: Transactional Outbox vs. Log-Based CDC

Interactive Architecture Diagram
100%Fit100%
Synthesizing vector architecture diagram...

Outbox Table Schema & DDL

sqlCopy
CREATE TABLE outbox_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(64) NOT NULL,    -- e.g. "ORDER"
    aggregate_id VARCHAR(64) NOT NULL,      -- e.g. "ord_10294"
    event_type VARCHAR(64) NOT NULL,        -- e.g. "ORDER_CREATED"
    payload JSONB NOT NULL,                 -- Full event payload
    headers JSONB,                          -- Trace ID, correlation ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Application Atomic Write Transaction
BEGIN;
INSERT INTO orders (order_id, user_id, amount_cents, status) 
VALUES ('ord_10294', 'usr_881', 4999, 'PAID');

INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload) 
VALUES ('ORDER', 'ord_10294', 'ORDER_CREATED', '{"order_id": "ord_10294", "amount_cents": 4999}');
COMMIT;