event-store-design

Create an events table with stream_id, event_type, JSONB event_data, version, and a BIGSERIAL global_position, plus a unique constraint on (stream_id, version) for optimistic concurrency. Add indexes on stream_id, global_position, event_type, and created_at for your query patterns.

EventStoreDB is purpose-built for event sourcing with native streams, projections, and subscriptions, while PostgreSQL fits teams already running Postgres who accept manual implementation of concurrency checks and subscriptions. Kafka suits high-throughput streaming but is weak for per-stream queries.

Pass an expected_version when appending events and compare it against the stream's current maximum version inside a transaction, raising a concurrency error on mismatch. In PostgreSQL, the unique constraint on (stream_id, version) provides a second layer of protection.

Yes, model streams with a partition key like STREAM#{stream_id} and a sort key of zero-padded version numbers, using a GSI for global ordering. Conditional writes enforce concurrency, though cross-stream global ordering and query flexibility are limited compared to dedicated stores.

A subscription polls events by global_position in batches, invokes a handler per event, then persists the last processed position in a subscription_checkpoints table. On restart it resumes from the stored checkpoint, giving at-least-once delivery that handlers must make idempotent.

Avoid updating or deleting events, storing oversized payloads, and skipping optimistic concurrency checks, since these break immutability and corrupt state. Also include correlation and causation IDs from day one and plan for backpressure from slow subscription consumers.