Event sourcing database schema

Table aggregate {
  id   uuid pk
  type text not null   // order, account, ...
  created_at timestamptz default `now()`
}

Table event_log {
  stream_id    uuid [ref: > aggregate.id, not null]
  version      int not null
  event_type   text not null
  payload      jsonb not null
  recorded_at  timestamptz default `now()`
  pk (stream_id, version)
}

Table snapshot {
  stream_id    uuid [ref: > aggregate.id]
  version      int not null
  state        jsonb not null
  taken_at     timestamptz default `now()`
  pk (stream_id, version)
}

Table projection_offset {
  projection_name text pk
  last_position   bigint not null
  updated_at      timestamptz default `now()`
}