SQL Academy · Lesson

Rebuilding State from Events

Fold events into current state.

What Does Rebuilding State Mean?

In event sourcing, data is stored as an immutable log of events rather than as mutable rows. To know the current state of anything, you must replay those events and fold them into a single result.

This is called rebuilding state from events. Think of a bank account: instead of storing the balance, you store every deposit and withdrawal. The balance is always the sum of all those events.

A Simple Events Table

Let us start by creating a minimal event log for a bank account system. Each row represents something that happened — a deposit or a withdrawal — with the amount and timestamp.

This table never gets updated or deleted. New facts are always appended as new rows.

CREATE TABLE account_events (
  event_id   SERIAL PRIMARY KEY,
  account_id INT NOT NULL,
  event_type VARCHAR(20) NOT NULL,  -- 'deposit' or 'withdrawal'
  amount     NUMERIC(12, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO account_events (account_id, event_type, amount, created_at) VALUES
  (1, 'deposit',    1000.00, '2024-01-01 09:00:00+00'),
  (1, 'deposit',     500.00, '2024-01-03 14:00:00+00'),
  (1, 'withdrawal',  200.00, '2024-01-05 10:00:00+00'),
  (1, 'deposit',     300.00, '2024-01-07 11:00:00+00'),
  (1, 'withdrawal',  150.00, '2024-01-09 16:00:00+00');

All lessons in this course

← Back to SQL Academy