Event store schema

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  stream_id   UUID NOT NULL,        -- e.g., order-{order_id}
  stream_type VARCHAR(100) NOT NULL, -- Order, Customer, etc.
  event_type  VARCHAR(100) NOT NULL, -- OrderPlaced, OrderShipped, etc.
  version     INT NOT NULL,          -- per-stream sequence number
  payload     JSONB NOT NULL,
  metadata    JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (stream_id, version)
);

Applying events to build state

def load_order(order_id):
    events = db.query("SELECT * FROM events WHERE stream_id=? ORDER BY version", order_id)
    state = {}
    for event in events:
        state = apply(state, event)
    return state

Projections

A projection is a read model built by consuming events. Keep projections in separate tables. When you need a new query shape, build a new projection by replaying all events — no schema migration needed.

Operational realities

  • Snapshots — for streams with thousands of events, store periodic snapshots to avoid replaying from the beginning.
  • Event schema evolution — events are immutable. Upcasting transforms old events to the current schema at read time.