Outbox Pattern POC with PostgreSQL Triggers

A production-ready implementation of the transactional outbox pattern in Go, featuring both writer-based and trigger-based immediate publishing for sidecar architectures.

🚀 Features

• Dual Publishing Modes: Traditional writer-based + PostgreSQL trigger-based
• Sidecar Architecture: Language-agnostic outbox publishing
• Immediate Publishing: Microsecond latency via database triggers
• Reliable Fallback: Background polling for failure recovery
• Extensible Publishers: HTTP APIs, gRPC, message brokers
• Production Ready: Retry logic, DLQ, error handling
• Zero HTTP Coupling: Direct database-driven publishing

🏗️ Architecture

Traditional Mode

App → writer.Write() → OptimisticPublisher → Immediate Publish
                    ↓
                 Outbox Table → Background Reader → Retry/DLQ

Sidecar Mode

Python/Any App → Outbox Table → PostgreSQL Trigger → NOTIFY
                                                    ↓
Go Sidecar ← LISTEN ← PostgreSQL ← pg_notify() ← Trigger
    ↓
Immediate Publish → HTTP/gRPC/MessageBroker

📋 Prerequisites

• Go 1.24+
• PostgreSQL 12+
• Docker & Docker Compose (optional)

🛠️ Quick Start

1. Clone & Setup

git clone https://github.com/AmanGIT07/outbox-go-poc.git
cd outbox-go-poc
go mod tidy

2. Start PostgreSQL

docker run --name postgres-outbox \
  -e POSTGRES_USER=user \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=outboxdb \
  -p 5432:5432 \
  -d postgres:15

3. Run Traditional Mode

export ENABLE_TRIGGERS=false
export RUN_HTTP_SERVER=true
go run .

Test with HTTP API:

curl -X POST http://localhost:8080/trigger \
  -H "Content-Type: application/json" \
  -d '{
    "name": "test-org",
    "user_id": 123
  }'

4. Run Sidecar Mode

export ENABLE_TRIGGERS=true
export RUN_HTTP_SERVER=false
go run .

Test with direct database insert:

psql postgres://user:password@localhost:5432/outboxdb

-- Simulate Python app creating organization + outbox message in same transaction
BEGIN;
INSERT INTO organization (id, name, user_id, created_at) 
VALUES (gen_random_uuid(), 'manual-org', 999, NOW());

INSERT INTO outbox (id, payload, metadata, created_at, scheduled_at, times_attempted) 
VALUES (
    gen_random_uuid(),
    '{"event_type": "org.created", "data": {"name": "manual-org", "user_id": 999}}'::bytea,
    '{"trace_id": "manual-trace"}'::bytea,
    NOW(), NOW(), 0
);
COMMIT;

🐳 Docker Compose

version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: outboxdb
    ports:
      - "5432:5432"

  outbox-sidecar:
    build: .
    environment:
      - DB_DSN=postgres://user:password@postgres:5432/outboxdb?sslmode=disable
      - ENABLE_TRIGGERS=true
      - RUN_HTTP_SERVER=false
    depends_on:
      - postgres

  outbox-api:
    build: .
    environment:
      - DB_DSN=postgres://user:password@postgres:5432/outboxdb?sslmode=disable
      - ENABLE_TRIGGERS=false
      - RUN_HTTP_SERVER=true
    ports:
      - "8080:8080"
    depends_on:
      - postgres

Run: docker-compose up -d

🐍 Python Integration

For sidecar architecture with Python applications:

# models.py
from sqlalchemy import Column, DateTime, Integer, JSON, String
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.declarative import declarative_base
import uuid
from datetime import datetime

Base = declarative_base()

class OutboxEvent(Base):
    __tablename__ = 'outbox'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    payload = Column(JSON, nullable=False)
    metadata = Column(JSON, default=dict)
    created_at = Column(DateTime, default=datetime.utcnow)
    scheduled_at = Column(DateTime, default=datetime.utcnow)
    times_attempted = Column(Integer, default=0)

# service.py - Business logic with outbox events
def create_user_with_event(db_session, email: str, name: str):
    with db_session.begin():
        # 1. Business logic
        user_id = create_user_in_db(email, name)
        
        # 2. Write to outbox (triggers immediate publishing via PostgreSQL trigger)
        event = OutboxEvent(
            payload={
                "event_type": "user.created",
                "data": {"user_id": user_id, "email": email, "name": name}
            }
        )
        db_session.add(event)
        # Commit triggers PostgreSQL notification → Go sidecar publishes immediately

⚙️ Configuration

Variable	Default	Description
DB_DSN	postgres://user:password@localhost:5432/outboxdb?sslmode=disable	PostgreSQL connection
ENABLE_TRIGGERS	false	Enable PostgreSQL triggers
RUN_HTTP_SERVER	true	Run HTTP server

🧪 Testing

Expected logs for trigger-based publishing:

{"level":"INFO","msg":"Received outbox notification","msg_id":"..."}
{"level":"INFO","msg":"Publishing to mock audit API","payload":{"event_type":"user.registered",...}}
{"level":"INFO","msg":"Successfully published message via notification","msg_id":"..."}