Event Sourcing with Databases: Design, Patterns & Production Pitfalls

Most applications are built around a simple mental model: save the current state, overwrite it when it changes, query it when you need it. That model works until it doesn't — and the moment it stops working, you feel it hard. You can't answer 'what did this record look like three weeks ago?' You can't audit who changed what and why. You can't replay business logic against historical data. You've lost information the moment you hit UPDATE.

Event sourcing flips this model on its head. Instead of storing current state, you store an immutable, append-only log of every state transition — every event that caused the world to change. The current state becomes a derived view, computed on demand by replaying that log. This gives you a complete audit trail, time-travel debugging, the ability to rebuild any projection from scratch, and a natural fit for event-driven architectures. The database stops being a snapshot of 'now' and becomes a ledger of 'everything that ever happened'.

By the end of this article you'll know how to design an event store schema from scratch, implement snapshot strategies to keep replay times sane at scale, wire up CQRS read models as SQL projections, handle schema evolution without breaking old events, and avoid the production mistakes that bite teams six months after they ship. This is the article I wish existed when I first built an event-sourced system in production.

What is Event Sourcing with Databases?

The core idea is simple: instead of updating a row in-place to reflect the latest state, you append an event to an append-only log. Each event captures what happened, when, and why. The current state of any entity — an account, an order, a document — is computed by replaying all its events in order.

This is fundamentally different from CRUD. In CRUD, an UPDATE erases the previous state. You lose history. Event sourcing preserves every fact forever. That's hugely powerful for auditing, debugging, and building multiple read models without fighting the write schema.

But it's also a trade-off. Replaying events on every read is slow — that's where snapshots come in. Storing immutable events makes schema evolution harder — you have to handle old event formats gracefully. And because you're appending to a single table at high concurrency, your database needs to handle write throughput, not just reads.

The snippet below shows a minimal Java example. This is not what you'd use in production, but it illustrates the append-replay loop.

package io.thecodeforge.eventsourcing;

import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

// Minimal in-memory event store — NOT production-ready
public final class BasicEventStore {
    private final Map<String, List<Event>> store = new ConcurrentHashMap<>();
    
    public void append(String aggregateId, Event event) {
        store.computeIfAbsent(aggregateId, k -> new ArrayList<>()).add(event);
    }
    
    public List<Event> readEvents(String aggregateId) {
        return store.getOrDefault(aggregateId, List.of());
    }
    
    // Replay all events to compute current state
    public AccountState getAccountState(String accountId) {
        AccountState state = new AccountState(accountId);
        for (Event e : readEvents(accountId)) {
            state.apply(e);
        }
        return state;
    }
    
    public record Event(String type, String data, long timestamp) {}
    
    public static class AccountState {
        private final String id;
        private double balance = 0.0;
        
        AccountState(String id) { this.id = id; }
        
        void apply(Event e) {
            switch (e.type()) {
                case "AccountOpened" -> { /* init */ }
                case "Deposited" -> balance += Double.parseDouble(e.data());
                case "Withdrawn" -> balance -= Double.parseDouble(e.data());
            }
        }
        
        public double getBalance() { return balance; }
    }
}

Event Store Schema Design

Your event store schema is the foundation of your entire system. Get it wrong and you'll fight ordering issues, slow queries, and painful migrations.

The essential columns: aggregate_id (string/ UUID), sequence_number (integer), event_type (string), event_data (JSON/JSONB), timestamp (transaction time). The primary key MUST be (aggregate_id, sequence_number). This enforces ordering per aggregate and allows fast replay.

You also need indexes for specific query patterns: projectors that need to scan events by type or by timestamp range. Consider a composite index on (event_type, timestamp) for subscriptions. But remember: you trade write throughput for read performance.

Avoid using high-latency sequences like UUIDs for sequence_number unless you can guarantee monotonic ordering. Database sequences (like SERIAL in PostgreSQL) work well for single-writer per aggregate. If you have concurrent writers for the same aggregate, you need optimistic locking with a version field.

Below is a PostgreSQL DDL for a production-grade event store table.

-- Production event store schema for PostgreSQL
CREATE TABLE io_thecodeforge.event_store (
    aggregate_id   VARCHAR(64) NOT NULL,
    sequence_number INTEGER NOT NULL,
    event_type     VARCHAR(128) NOT NULL,
    event_data     JSONB NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Ensure ordering and uniqueness per aggregate
    PRIMARY KEY (aggregate_id, sequence_number)
);

-- For projectors that scan by event type
CREATE INDEX idx_event_store_event_type 
    ON io_thecodeforge.event_store (event_type, created_at);

-- For querying events by time range (auditing)
CREATE INDEX idx_event_store_created_at 
    ON io_thecodeforge.event_store (created_at);

-- For snapshots (separate table)
CREATE TABLE io_thecodeforge.snapshots (
    aggregate_id    VARCHAR(64) NOT NULL,
    sequence_number INTEGER NOT NULL,
    state_type      VARCHAR(128) NOT NULL,
    state_data      JSONB NOT NULL,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (aggregate_id, sequence_number)
);

Snapshot Strategies for Efficient Replay

Replaying every event from the beginning of time on every read gets expensive fast. If an aggregate has 100k events, a single read takes 100k SQL queries or a huge IN clause. Snapshots are the answer.

A snapshot captures the aggregate state at a specific point (sequence number). On read, you load the latest snapshot and replay only events after that snapshot. This keeps replay cost constant relative to the window between snapshots, not total history.

Decide when to take snapshots: after every N events, or after a time interval. Use a background process (e.g., cron job or scheduled task) that reads events since the last snapshot and writes the aggregated state. Ensure snapshot creation is idempotent: if two snapshot jobs overlap, the later one wins.

Store snapshots in a separate table with the same primary key structure but with the state data. Projections also need snapshots if they're rebuilt often.

Here's a Java snippet for snapshot creation logic.

package io.thecodeforge.eventsourcing;

import java.time.Instant;
import java.util.List;

public class SnapshotService {
    private final EventStoreRepository eventStore;
    private final SnapshotRepository snapshotRepo;
    private final int snapshotFrequency = 100; // snapshot every 100 events
    
    public SnapshotService(EventStoreRepository eventStore, SnapshotRepository snapshotRepo) {
        this.eventStore = eventStore;
        this.snapshotRepo = snapshotRepo;
    }
    
    public void createSnapshot(String aggregateId) {
        // Load latest snapshot (if any)
        Snapshot latest = snapshotRepo.findLatest(aggregateId).orElse(null);
        int fromSequence = (latest != null) ? latest.sequenceNumber() + 1 : 1;
        
        // Get current state by replaying from snapshot
        AggregateState state = (latest != null) 
            ? latest.restoreState()
            : AggregateState.initial(aggregateId);
        
        List<Event> newEvents = eventStore.readEventsSince(aggregateId, fromSequence);
        for (Event e : newEvents) {
            state.apply(e);
        }
        
        // If enough events have passed, persist snapshot
        int totalEvents = (latest != null ? latest.sequenceNumber() : 0) + newEvents.size();
        if (totalEvents - (latest != null ? latest.sequenceNumber() : 0) >= snapshotFrequency) {
            Snapshot snapshot = new Snapshot(
                aggregateId,
                totalEvents,
                state.getClass().getName(),
                state.toJson(),
                Instant.now()
            );
            snapshotRepo.save(snapshot);
        }
    }
}

Projections and CQRS Integration

Event sourcing naturally pairs with CQRS. The write side appends events, the read side builds projections — denormalised tables optimised for query patterns. Projections are updated asynchronously by subscribing to the event stream.

This decoupling lets you build multiple read models without touching the write schema. You can have a relational projection for reporting, a cache projection for APIs, and a search projection for Elasticsearch — all fed from the same event stream.

But projections introduce complexity: eventual consistency, idempotency, and rebuild capability. When a projection fails, you need to rebuild it from scratch by replaying all events. Idempotency ensures that replaying events multiple times doesn't double-count. Your projection handlers must be deterministic and handle duplicate events gracefully.

Use a dedicated projection table that tracks the last processed position (often the event store's transaction ID or sequence number). This allows the projection to resume after a crash without missing events.

Here's a simple projector example for reading account balances.

package io.thecodeforge.eventsourcing.projectors;

import io.thecodeforge.eventsourcing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class AccountBalanceProjector {
    private final Connection conn;
    private final EventStore eventStore;
    private long lastProcessedPosition = 0;
    
    public AccountBalanceProjector(Connection conn, EventStore eventStore) {
        this.conn = conn;
        this.eventStore = eventStore;
    }
    
    public void processNextBatch() {
        List<Event> events = eventStore.readEventsAfter(lastProcessedPosition, 100);
        for (Event e : events) {
            switch (e.type()) {
                case "Deposited" -> applyDeposit(e);
                case "Withdrawn" -> applyWithdrawal(e);
                // ignore other event types
            }
            lastProcessedPosition = e.globalPosition();
        }
        // Save checkpoint (normally in a transaction)
        saveCheckpoint(lastProcessedPosition);
    }
    
    private void applyDeposit(Event e) {
        String accountId = e.aggregateId();
        double amount = Double.parseDouble(e.data());
        try (PreparedStatement stmt = conn.prepareStatement(
            "UPDATE account_balances SET balance = balance + ? WHERE account_id = ?")) {
            stmt.setDouble(1, amount);
            stmt.setString(2, accountId);
            stmt.executeUpdate();
        } catch (SQLException ex) { throw new RuntimeException(ex); }
    }
    // ... withdrawal method similar
}

Schema Evolution: Handling Event Versioning

Events are immutable, but your understanding of the domain changes. You'll need to add fields, rename them, or change structure. The challenge: you have millions of events in the old format. You can't go back and update them — they're immutable.

Two strategies: versioned event types and upcasters.

Versioned event types: each event type carries a version number (e.g., OrderPlacedV1, OrderPlacedV2). New events are written in the new version. On replay, you check the version and apply the appropriate handler. Old events stay as-is.

Upcasters: conversion functions that transform an old event into the new format on-the-fly during replay. This centralizes the transformation and keeps application code dealing only with the latest version. Upcasters run after deserialization but before handler application.

Choose upcasters over versioned types if the number of event types grows large — you avoid handler proliferation. Choose versioned types if different handlers need different logic per version.

Real-world tools like EventStoreDB or Axon Framework have built-in upcaster support. For custom databases, you implement upcasters in your application layer.

Here's an upcaster example in Java.

package io.thecodeforge.eventsourcing.upcaster;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;

// Converts OrderPlacedV1 to OrderPlacedV2 (adds currency field)
public class OrderPlacedUpcaster {
    private static final ObjectMapper mapper = new ObjectMapper();
    
    public static JsonNode upcast(JsonNode oldEvent) {
        if (!"OrderPlacedV1".equals(oldEvent.get("eventType").asText()))
            return oldEvent; // not for us
        
        ObjectNode v2 = oldEvent.deepCopy();
        v2.put("eventType", "OrderPlacedV2");
        v2.put("currency", "USD"); // default for old events
        // Optionally add a field to mark it was upcasted
        v2.put("_upcasted", true);
        return v2;
    }
}

Production Pitfalls: Real-World Mistakes and Fixes

After running event sourcing in production for months, teams hit predictable but painful problems. Here are the three that hurt most.

1. Missing per-aggregate ordering. As covered in the incident report, without a correct sequence number and single-writer per aggregate, events get reordered. The fix: enforce that all events for an aggregate are appended by the same thread/process, or use optimistic locking with a version field in the aggregate.
2. Snapshot corruption due to concurrent writes. If your snapshot job reads events while a new event is being written, it may snap an incomplete state. The snapshot appears valid but when replaying after it, you get duplicate or missing events. Solution: run snapshots on a read replica with a known lag, or use snapshot isolation level.
3. Projection rebuild timeout. When a projection fails and needs a full rebuild, replaying millions of events can exceed HTTP timeout for APIs or exhaust memory. Test rebuild times regularly. Consider rebuilding via a background job with progress tracking, and serve stale read models until rebuild completes.

Below is a checklist for avoiding these pitfalls.

-- Pitfall prevention checklist (run after deployment)

-- 1. Check for ordering gaps per aggregate
SELECT aggregate_id, count(*) as event_count,
       max(sequence_number) - min(sequence_number) + 1 - count(*) as gaps
FROM io_thecodeforge.event_store
GROUP BY aggregate_id
HAVING gaps > 0;

-- 2. Verify latest snapshot sequence_number <= latest event sequence_number
SELECT s.aggregate_id, s.sequence_number as snapshot_seq, 
       max(e.sequence_number) as latest_event_seq
FROM io_thecodeforge.snapshots s
JOIN io_thecodeforge.event_store e ON s.aggregate_id = e.aggregate_id
GROUP BY s.aggregate_id, s.sequence_number
HAVING s.sequence_number > max(e.sequence_number);

-- 3. Find projection lag: difference between event store max sequence and projection checkpoint
-- (Requires a projection_checkpoint table)
SELECT projector_name, 
       (SELECT max(sequence_number) FROM io_thecodeforge.event_store) - checkpoint_seq AS lag
FROM io_thecodeforge.projection_checkpoints;