event-store-design

Create an append-only events table with stream_id, version, event_type, JSONB payload, and a global_position bigserial column, enforcing a unique constraint on (stream_id, version). Use optimistic concurrency by checking the expected version inside a transaction before inserting new events.

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 and subscriptions. Kafka suits high-throughput streaming but is weak for per-stream queries.

Pass an expected version when appending events and verify it matches the stream's current max version inside the same transaction. If the versions differ, raise a concurrency error and let the caller reload the stream and retry.

Yes, using a single-table design with PK as STREAM#{id} and SK as a zero-padded VERSION# number, plus a GSI for global ordering. Conditional writes provide concurrency control, though cross-stream queries and global ordering are more limited than relational stores.

Events are immutable facts that form the audit trail and the source of truth for rebuilding state. Updating or deleting them breaks replayability, corrupts projections, and destroys the ability to reconstruct historical aggregate state.