Design Event Store Architectures

This skill provides a comprehensive guide to designing event stores for event-sourced applications, covering architecture, requirements, technology selection, and working implementation templates.

Its event store architecture models multiple per-aggregate streams (each an ordered sequence of immutable events) alongside a global position counter spanning all streams for cross-stream subscriptions. Its event store requirements are append-only (events are immutable, only appended), ordered (both per-stream and globally), versioned (optimistic concurrency control), subscribable (real-time notifications), and idempotent (safe handling of duplicate writes).

Its technology comparison weighs EventStoreDB (best for pure event sourcing, but single-purpose), PostgreSQL (fits an existing Postgres stack, but requires manual implementation), Kafka (high-throughput streaming, not ideal for per-stream queries), DynamoDB (serverless AWS-native, with query limitations), and Marten (.NET ecosystems, .NET-specific).

Its templates provide working code: a PostgreSQL event store schema (an events table with stream_id/stream_type/event_type/event_data/version/global_position and a unique constraint on stream_id+version for optimistic concurrency, indexed for stream queries/global subscription/event-type/time-based queries, plus snapshots and subscription_checkpoints tables); a Python event store implementation; EventStoreDB usage (connecting, appending events, reading a stream, subscribing to all events, and category projections via $ce-Category); and a DynamoDB event store with a CloudFormation/Terraform table definition.

Its best practices favor stream IDs that include the aggregate type (e.g. Order-{uuid}), including correlation/causation IDs for tracing, versioning events from day one to plan for schema evolution, implementing idempotency via event IDs for deduplication, and indexing appropriately for query patterns - while avoiding updating or deleting events (they're immutable facts), storing large payloads (keep events small), skipping optimistic concurrency (prevents data corruption), and ignoring backpressure from slow consumers. It references EventStoreDB, Marten Events, and the Azure Architecture Center's Event Sourcing pattern as external resources.

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)
);

CREATE INDEX idx_events_stream_id ON events(stream_id, version);
CREATE INDEX idx_events_global_position ON events(global_position);

CREATE TABLE snapshots (
    stream_id VARCHAR(255) PRIMARY KEY,
    stream_type VARCHAR(255) NOT NULL,
    snapshot_data JSONB NOT NULL,
    version BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Use this skill when designing an event store for an event-sourced application - choosing between EventStoreDB, PostgreSQL, Kafka, DynamoDB, or Marten, and planning for event store scaling.

Inputs: an event-sourced application needing a durable, ordered, versioned event store.

Outputs: a designed event store schema (PostgreSQL, EventStoreDB, or DynamoDB) meeting append-only/ordered/versioned/subscribable/idempotent requirements, with snapshotting and subscription checkpoint support.

PostgreSQL, EventStoreDB, Apache Kafka, DynamoDB, Marten (.NET); CloudFormation/Terraform for DynamoDB provisioning.

Architects and engineers designing event stores for event-sourced applications who need to choose the right technology and implement a correct, scalable schema.