Design a Transactional Outbox & Event-Driven Ledger

1. Problem Statement & Scope Clarification

System Mission

Design a mission-critical, enterprise-grade Transactional Outbox & Event-Driven Ledger Platform engineered to guarantee atomic database state transitions and reliable cross-boundary asynchronous event propagation without distributed transactions (Two-Phase Commit / 2PC).

The platform must eliminate the notorious Dual-Write Problem, guarantee zero lost domain events, enforce double-spending prevention under high-concurrency balance updates, and scale outbox dispatching to tens of millions of daily events while avoiding catastrophic PostgreSQL MVCC table bloat and WAL (Write-Ahead Log) replication-slot disk exhaustion.

textCopy
+---------------------------------------------------------------------------------------------------------+
|                              TRANSACTIONAL OUTBOX & EVENT LEDGER AT A GLANCE                             |
+------------------------------------+------------------------------------+-------------------------------+
| Scale: 50M Events/Day              | Ingestion Peak: 5,000 TPS          | Event Latency: < 5ms (Reactive Wake-Up) |
| Read QPS: 25,000 Peak              | P99 Lock Wait: < 2ms (In-Memory)   | Durability: 100% Zero Loss    |
| Concurrency: Advisory Lock (SHM)   | Event Bus: Redpanda / Amazon MSK   | Primary DB: Aurora PostgreSQL |
+------------------------------------+------------------------------------+-------------------------------+

Functional Requirements

1. Atomic Dual-Write Prevention (Transactional Outbox): Ingest state mutations (financial transfers, coin purchases, order status updates) and atomically persist domain events within the same local relational database transaction.
2. Guaranteed At-Least-Once Delivery: Reliably extract, dispatch, and stream outbox events to a distributed message log (Redpanda / Amazon MSK / Kafka) even across process restarts, application crashes, and network partitions.
3. Ironclad Account Concurrency & Double-Spend Defense: Serialize balance checks, debits, and credits for a given account to prevent race conditions and overdrafts, ensuring zero lost updates.
4. Idempotent Downstream Consumption: Enable downstream consumers (billing, analytics, notification, audit ledgers) to deduplicate re-delivered messages using monotonic idempotency keys without duplicate side effects.
5. Pluggable Event Pipeline with Graceful Degradation: Maintain high availability on single-node deployments and developer environments (via in-process channel buses) while seamlessly scaling to partitioned distributed event brokers under high load.

Non-Functional Requirements (SLAs & SLOs)

• Data Consistency & Durability: $100\%$ zero data loss. Monetary mutations and outbox records must be bound by strict ACID guarantees (SERIALIZABLE or READ COMMITTED with explicit locking).
• Availability: $99.999\%$ uptime for transactional ingress. Background event dispatch must degrade gracefully without taking down API write availability if the external message broker is temporarily unreachable.
• Latency (P99):
  • Synchronous Transaction Commit: $P99 < 15\text{ ms}$.
  • Concurrency Lock Acquisition: $P99 < 2\text{ ms}$.
  • Outbox-to-Broker Dispatch Latency: $P99 < 5\text{ ms}$ (Reactive In-Memory Signal Sweeper) with $< 5.0\text{ s}$ fallback safety timeout, or $< 100\text{ ms}$ (Log-Based CDC).
• Storage Stability: Absolute immunity to unbounded storage bloat. The system must prevent PostgreSQL table bloat (dead tuples) and replication-slot WAL accumulation from exhausting disk capacity.

---

2. Capacity & Scale Estimation (Back-of-the-Envelope Math)

Transaction Volume & Event Throughput (TPS)

• Daily Domain Events: $50,000,000\text{ events/day}$ ($50\text{ Million}$).
• Average Event Generation TPS: $\text{TPS}_{\text{avg}} = \frac{50 \times 10^6\text{ events}}{86,400\text{ sec}} \approx \mathbf{578.7\text{ TPS}}$
• Peak Flash-Load TPS ($10\times$ burst during market open / peak hours): $\text{TPS}_{\text{peak}} = 578.7 \times 10 \approx \mathbf{5,787\text{ TPS}} \quad (\text{dimensioned for } \mathbf{6,000\text{ TPS}})$
• Read & Account Balance Inquiries ($5:1$ Read/Write Ratio): $\text{QPS}_{\text{read, peak}} = 6,000 \times 5 = \mathbf{30,000\text{ QPS}}$

Outbox Storage & WAL Generation Math

Each outbox record consists of:

• event_id (UUID): $16\text{ bytes}$
• event_type (VARCHAR): $48\text{ bytes}$
• aggregate_id (VARCHAR): $36\text{ bytes}$
• occurred_at (TIMESTAMPTZ): $8\text{ bytes}$
• payload (JSONB): $\approx 500\text{ bytes}$
• source_module (VARCHAR): $24\text{ bytes}$
• B-Tree Index & Row Header Overhead: $\approx 100\text{ bytes}$
• Total Record Size: $\approx \mathbf{732\text{ bytes}} \approx \mathbf{0.75\text{ KB}}$.

Storage Impact Under Different Outbox Architectures:

1. Naive Retain-All Outbox (Marking status = 'PROCESSED'): $\text{Daily Table Growth} = 50 \times 10^6 \times 750\text{ bytes} \approx \mathbf{37.5\text{ GB/day}}$ $\text{Annual Storage Bloat} = 37.5\text{ GB} \times 365 \approx \mathbf{13.7\text{ TB/year}}$ In addition, PostgreSQL MVCC creates a dead tuple for every update, doubling disk write amplification.
2. Immediate Delete-on-Success Architecture:
   • Under Naive 2-Second Polling: $\text{Active Rows at Any Moment} = \text{TPS}_{\text{peak}} \times \text{Sweep Interval} = 6,000\text{ TPS} \times 2\text{ sec} = \mathbf{12,000\text{ rows}}$ $\text{Steady-State Table Footprint} = 12,000 \times 750\text{ bytes} \approx \mathbf{9.0\text{ MB}}$
   • Under Reactive In-Memory Signal Wake-Up ($< 5\text{ms}$ dispatch): Events are drained immediately upon transaction commit. The active pending row queue depth stays bounded by the single batch size ($50\text{--}200\text{ rows} \approx \mathbf{150\text{ KB}}$), maintaining an active footprint well under $1\text{ MB}$ during normal operations and bounded to $< 15\text{ MB}$ during external broker retries. Disk usage remains permanently locked under 15 MB!

PostgreSQL Shared Memory Advisory Lock Sizing

PostgreSQL maintains advisory locks in shared memory (SHM), allocated at server startup:

$$\text{Max Lock Objects} = \text{max\_connections} \times \text{max\_locks\_per\_transaction} = 200 \times 64 = \mathbf{12,800\text{ lock entries}}$$

• Each lock entry occupies approximately $270\text{ bytes}$ of shared memory.
• Total Shared Memory Footprint: $12,800 \times 270\text{ bytes} \approx \mathbf{3.45\text{ MB RAM}}$.
• Disk I/O and WAL Overhead for Advisory Locks: Exactly 0 bytes/sec.

---

3. AWS-First High-Level Architecture

The architecture coordinates three specialized planes: Transactional Ingress & Ledger Persistence, Outbox Event Sweeping & Streaming, and Idempotent Consumer Materialization.

Synthesizing vector architecture diagram...

End-to-End Execution Walkthrough

---

4. API Interface Design & Data Contracts

1. Execute Ledger Transfer (POST /v1/ledger/transfers)

Executes a balance transfer between accounts with atomic outbox event staging.

Request Headers

httpCopy
POST /v1/ledger/transfers HTTP/1.1
Host: api.hispeeddesign.com
Authorization: Bearer <jwt_token>
Idempotency-Key: 7b8c2f1e-9a4d-4e6f-8b2c-1d3e5f7a9b1c
X-Correlation-ID: corr_01J8N6K3P4V9QZ2W8M1Y7R4X
Content-Type: application/json

Request Payload

jsonCopy
{
  "source_account_id": "00000000-0000-0000-0000-000000000001",
  "destination_account_id": "99999999-9999-9999-9999-999999999999",
  "amount": 250,
  "currency": "COINS",
  "reason": "SUBVARIANT_UNLOCK_FEE",
  "reference_id": "pv_binary_search_monotonic"
}

Response: 201 Created

jsonCopy
{
  "transfer_id": "xfer_882910492817",
  "source_account_id": "00000000-0000-0000-0000-000000000001",
  "amount": 250,
  "new_balance": 1750,
  "status": "COMMITTED",
  "outbox_event_id": "evt_44a2c890-3b1a-4c2d-9f0e-8a7b6c5d4e3f",
  "timestamp": "2026-09-19T22:30:00.000Z"
}

---

2. Standardized Outbox Event Envelope Specification (EventEnvelope)

All outbox events published to Redpanda / Amazon MSK adhere to a strict, decoupled JSON schema format that avoids CLR/JVM reflection dependencies:

jsonCopy
{
  "eventId": "44a2c890-3b1a-4c2d-9f0e-8a7b6c5d4e3f",
  "eventType": "economy.coins.transferred.v1",
  "version": 1,
  "occurredAt": "2026-09-19T22:30:00.124Z",
  "sourceModule": "Economy",
  "correlationId": "corr_01J8N6K3P4V9QZ2W8M1Y7R4X",
  "payload": {
    "transferId": "xfer_882910492817",
    "sourceAccountId": "00000000-0000-0000-0000-000000000001",
    "destinationAccountId": "99999999-9999-9999-9999-999999999999",
    "amount": 250,
    "reason": "SUBVARIANT_UNLOCK_FEE",
    "referenceId": "pv_binary_search_monotonic"
  }
}

---

5. Data Models & Storage Architecture

PostgreSQL DDL Schema

sqlCopy
-- 1. Immutable Financial Ledger Schema (Double-Entry Ledger)
CREATE TABLE economy.coin_ledger_entries (
    entry_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    student_account_id UUID NOT NULL,
    amount BIGINT NOT NULL,                  -- Positive = Credit, Negative = Debit
    entry_type VARCHAR(32) NOT NULL,         -- 'EARN', 'SPEND', 'TRANSFER', 'BONUS'
    reason VARCHAR(128) NOT NULL,
    source_event_id UUID UNIQUE,             -- Idempotency anchor for incoming events
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ledger_student_created 
    ON economy.coin_ledger_entries (student_account_id, created_at DESC);

-- 2. Fast Balance Snapshot Table (O(1) Balance Reads)
CREATE TABLE economy.student_balances (
    student_account_id UUID PRIMARY KEY,
    current_balance BIGINT NOT NULL CHECK (current_balance >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. High-Performance Transactional Outbox Table
CREATE TABLE economy.outbox_messages (
    id UUID PRIMARY KEY,
    event_type VARCHAR(128) NOT NULL,
    version INT NOT NULL DEFAULT 1,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload JSONB NOT NULL,
    source_module VARCHAR(64) NOT NULL
);

-- B-Tree Index optimized for FIFO sweeping
CREATE INDEX idx_outbox_occurred_at 
    ON economy.outbox_messages (occurred_at ASC);

-- 4. Downstream Idempotent Consumer Deduplication Store
CREATE TABLE economy.processed_events (
    event_id UUID PRIMARY KEY,
    event_type VARCHAR(128) NOT NULL,
    consumer_group VARCHAR(64) NOT NULL,
    processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-cleanup partition or index for 30-day deduplication window
CREATE INDEX idx_processed_events_time 
    ON economy.processed_events (processed_at) 
    WHERE processed_at < NOW() - INTERVAL '30 days';

---