Immutable Audit Trails with Postgres JSONB & Logical Replication

In modern event-sourced architectures, data integrity and traceability are paramount. Traditional relational databases often struggle with the high write throughput and schema flexibility required by event sourcing. Fortunately, PostgreSQL offers a powerful combination of features—specifically the JSONB data type and Logical Decoding—that allows engineers to build immutable, append-only audit trails with exceptional performance and flexibility.

This post explores how to leverage these tools to create a robust audit system that captures every state change without locking the database or sacrificing performance.

The Core Problem: Auditing in Event Sourcing

Event sourcing dictates that the state of an application is derived from a sequence of events. To meet compliance and debugging requirements, we must ensure these events are immutable. Once written, an event cannot be altered or deleted. This "append-only" pattern is ideal for JSONB columns, which can store complex, unstructured payload data efficiently within a relational framework.

However, simply storing JSON in a table isn't enough. We need a mechanism to capture these writes reliably, potentially for downstream consumers like data warehouses or real-time dashboards, without impacting the primary transaction's latency.

Schema Design for Immutable Events

First, let's define a schema that enforces immutability. We use a unique constraint on the event ID and timestamp to prevent duplicates, and we omit update triggers to ensure the record is truly append-only.

CREATE TABLE event_store (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_id UUID NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Optional: Index for querying by aggregate history
CREATE INDEX idx_event_store_aggregate 
ON event_store (aggregate_id, created_at);

By making payload a JSONB type, we gain the ability to query specific fields within the event without deserializing the entire blob, which is crucial for debugging or filtering audit logs.

Leveraging Logical Replication for Decoupling

While storing the events is step one, sharing this data with other systems is where logical replication shines. Unlike physical replication, which copies the entire database, logical replication allows you to subscribe to specific tables and stream changes in a readable format (often JSON or protocol buffers).

To enable this, you must configure your postgresql.conf:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Once enabled, you can create a publication that streams events to downstream consumers:

PUBLICATION event_pub FOR TABLE event_store;

Benefits of this Approach

• Performance: Downstream consumers read from the WAL (Write-Ahead Log) asynchronously. This adds minimal overhead to the primary write path.
• Fault Tolerance: If the consumer goes down, it can replay the WAL from the last consumed position, ensuring no events are lost.
• Decoupling: You can have multiple consumers (e.g., an Elasticsearch indexer, a Kafka producer, a data warehouse loader) without impacting each other.

Practical Implementation: Reading the WAL

Developers can use libraries like pgoutput in Java/Go or psycopg2 with logical decoding plugins in Python to consume these streams. The data arrives in a format that maps directly to your schema, making deserialization trivial.

For example, a consumer might receive a message like this:

{
  "operation": "INSERT",
  "table": "event_store",
  "tuple": {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "aggregate_id": "b2c1e9f0-...",
    "event_type": "OrderPlaced",
    "payload": {"item": "Widget", "qty": 1},
    "created_at": "2023-10-27T10:00:00Z"
  }
}

Conclusion

Combining PostgreSQL's JSONB flexibility with Logical Replication provides a battle-tested foundation for immutable audit trails in event-sourced systems. It allows you to maintain strong consistency in your primary data while effortlessly streaming changes to secondary systems. This architecture is scalable, resilient, and leverages the mature ecosystem of PostgreSQL to handle the complexities of modern data engineering.

Start small by implementing the schema, then integrate a logical replication slot to feed your first consumer. The result is a system that is both easy to maintain and robust enough to handle production-scale audit requirements.