Design Event Store Architectures

This skill designs event stores for event-sourced applications. It models the architecture as multiple per-aggregate streams, each an ordered sequence of events, sharing a single global position across all streams, and defines five requirements an event store must meet: append-only (events are immutable, only appended), ordered (per-stream and global), versioned (optimistic concurrency control), subscriptions (real-time notifications), and idempotent (safe handling of duplicate writes). It compares five technologies: EventStoreDB (pure event sourcing, single-purpose), PostgreSQL (fits an existing Postgres stack, needs manual implementation), Kafka (high-throughput streaming, not ideal for per-stream queries), DynamoDB (serverless, AWS-native, has query limitations), and Marten (.NET ecosystems, .NET-specific). It provides four implementation templates: a PostgreSQL schema (an events table with stream/version/global-position indexes, a snapshots table, and a subscription_checkpoints table), a Python EventStore class over asyncpg implementing append_events with optimistic-concurrency version checks, read_stream, read_all, and a polling subscribe method that persists checkpoints, an EventStoreDB client wrapper (esdbclient) for appending, reading, subscribing to all events, and reading a category via the $ce-Category system projection, and a DynamoDB event store using a STREAM#/VERSION# partition/sort key scheme with a GSI for global ordering.

Use it when designing event sourcing infrastructure, choosing between event store technologies, implementing a custom event store, optimizing event storage and retrieval, setting up event store schemas, or planning for event store scaling. It does not apply outside event store design - a different domain or tool is out of scope.

Inputs: the aggregates and event types the system needs to persist, and throughput/query requirements. Outputs: a chosen event-store technology, a schema (SQL tables or a DynamoDB key design), an append-events implementation with optimistic concurrency (raising ConcurrencyError on a version mismatch), stream and global read methods, and a checkpointed subscription mechanism for consumers.

PostgreSQL with asyncpg, EventStoreDB via the esdbclient Python client (including its $ce-Category system projection), DynamoDB via boto3, and Kafka and Marten named as alternative event-store technologies.

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    stream_id VARCHAR(255) NOT NULL,
    stream_type VARCHAR(255) NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    event_data JSONB NOT NULL,
    metadata JSONB DEFAULT '{}',
    version BIGINT NOT NULL,
    global_position BIGSERIAL,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT unique_stream_version UNIQUE (stream_id, version)
);

Backend engineers designing event-sourced systems who need to choose an event store technology, define an append-only schema with optimistic concurrency, and implement stream reads and subscriptions correctly the first time. Its best practices: prefix stream IDs with the aggregate type (Order-{uuid}), include correlation and causation IDs for tracing, version events from day one for schema evolution, implement idempotency via event IDs, and index for the actual query patterns - while never updating or deleting events (they are immutable facts), never storing large payloads, never skipping optimistic concurrency checks, and never ignoring backpressure from slow consumers.