0Pricing
SQL Academy · Lesson

Rebuilding State from Events

Fold events into current state.

Rebuilding State from Events is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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');

Folding Events Into a Balance

To rebuild the current balance, we aggregate all events. Deposits add to the balance and withdrawals subtract from it. A CASE expression lets us treat each event type with the correct sign before summing.

This single query gives us the present state derived entirely from the historical event log.

SELECT
  account_id,
  SUM(
    CASE event_type
      WHEN 'deposit'    THEN  amount
      WHEN 'withdrawal' THEN -amount
      ELSE 0
    END
  ) AS current_balance
FROM account_events
WHERE account_id = 1
GROUP BY account_id;

Point-in-Time State

One of the most powerful aspects of event sourcing is the ability to reconstruct state at any point in time. Simply add a WHERE created_at <= :target_time filter before aggregating.

This gives you a time-travel query with no extra schema changes required — the history is already in the event log.

-- What was the balance at the end of January 5th?
SELECT
  account_id,
  SUM(
    CASE event_type
      WHEN 'deposit'    THEN  amount
      WHEN 'withdrawal' THEN -amount
      ELSE 0
    END
  ) AS balance_at_snapshot
FROM account_events
WHERE account_id = 1
  AND created_at <= '2024-01-05 23:59:59+00'
GROUP BY account_id;

Running Balance with Window Functions

Instead of a single total, we can compute a running balance — the balance after every event. The SUM(...) OVER (ORDER BY ...) window function calculates the cumulative sum as events accumulate in chronological order.

This is extremely useful for audit trails and debugging state transitions.

SELECT
  event_id,
  created_at,
  event_type,
  amount,
  SUM(
    CASE event_type
      WHEN 'deposit'    THEN  amount
      WHEN 'withdrawal' THEN -amount
      ELSE 0
    END
  ) OVER (PARTITION BY account_id ORDER BY created_at, event_id)
    AS running_balance
FROM account_events
WHERE account_id = 1
ORDER BY created_at, event_id;

Materializing State into a Snapshot Table

Replaying all events on every query can become expensive as the log grows. A common optimization is to materialize the current state into a snapshot table and rebuild it periodically or on demand.

The snapshot stores the folded result; queries read from the snapshot instead of replaying the full log each time.

CREATE TABLE account_snapshots (
  account_id      INT PRIMARY KEY,
  current_balance NUMERIC(12, 2) NOT NULL,
  as_of_event_id  INT NOT NULL,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Populate / refresh the snapshot from the event log
INSERT INTO account_snapshots (account_id, current_balance, as_of_event_id, updated_at)
SELECT
  account_id,
  SUM(CASE event_type WHEN 'deposit' THEN amount WHEN 'withdrawal' THEN -amount ELSE 0 END),
  MAX(event_id),
  NOW()
FROM account_events
GROUP BY account_id
ON CONFLICT (account_id) DO UPDATE
  SET current_balance = EXCLUDED.current_balance,
      as_of_event_id  = EXCLUDED.as_of_event_id,
      updated_at      = EXCLUDED.updated_at;

Incremental Snapshot Updates

When new events arrive, you do not have to replay the entire history. If you recorded the last processed event_id in the snapshot, you can apply only the delta — events that arrived after the snapshot was taken.

This incremental pattern keeps snapshot refreshes fast even on large logs.

-- Apply only new events since the last snapshot
UPDATE account_snapshots AS snap
SET
  current_balance = snap.current_balance + delta.net,
  as_of_event_id  = delta.max_event_id,
  updated_at      = NOW()
FROM (
  SELECT
    ae.account_id,
    SUM(CASE ae.event_type WHEN 'deposit' THEN ae.amount WHEN 'withdrawal' THEN -ae.amount ELSE 0 END) AS net,
    MAX(ae.event_id) AS max_event_id
  FROM account_events ae
  JOIN account_snapshots s ON s.account_id = ae.account_id
  WHERE ae.event_id > s.as_of_event_id
  GROUP BY ae.account_id
) AS delta
WHERE snap.account_id = delta.account_id;

Temporal Tables and System Versioning

SQL:2011 introduced system-versioned temporal tables, which the database itself maintains. Every row automatically gets valid_from and valid_to columns managed by the engine.

PostgreSQL does not support this natively, but you can emulate it. Other databases like MariaDB and SQL Server support WITH SYSTEM VERSIONING directly.

-- Emulating a temporal table in PostgreSQL
CREATE TABLE account_state_history (
  account_id      INT NOT NULL,
  current_balance NUMERIC(12, 2) NOT NULL,
  valid_from      TIMESTAMPTZ NOT NULL,
  valid_to        TIMESTAMPTZ NOT NULL DEFAULT 'infinity'
);

-- Insert initial state
INSERT INTO account_state_history (account_id, current_balance, valid_from)
VALUES (1, 1000.00, '2024-01-01 09:00:00+00');

-- On update: close old row, insert new row
UPDATE account_state_history
  SET valid_to = '2024-01-03 14:00:00+00'
WHERE account_id = 1 AND valid_to = 'infinity';

INSERT INTO account_state_history (account_id, current_balance, valid_from)
VALUES (1, 1500.00, '2024-01-03 14:00:00+00');

Querying Temporal History

With the emulated temporal table in place, you can ask what the balance was at any past moment by filtering on the validity range. The row whose range contains the target timestamp is the state at that time.

This pattern decouples query logic from event replay — the state history table is pre-folded.

-- What was the account balance on January 4th?
SELECT
  account_id,
  current_balance,
  valid_from,
  valid_to
FROM account_state_history
WHERE account_id = 1
  AND valid_from <= '2024-01-04 00:00:00+00'
  AND valid_to   >  '2024-01-04 00:00:00+00';

Event Sourcing with Multiple Entities

Real systems track events for many entities at once. A shared event log with an entity_id and entity_type column lets you rebuild state for any object from a single table.

Here we track inventory movements across multiple products. Rebuilding the current stock for each product is again just a grouped aggregation.

CREATE TABLE inventory_events (
  event_id    SERIAL PRIMARY KEY,
  product_id  INT NOT NULL,
  event_type  VARCHAR(20) NOT NULL,  -- 'received', 'shipped', 'adjusted'
  quantity    INT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO inventory_events (product_id, event_type, quantity, created_at) VALUES
  (101, 'received',  200, '2024-03-01 08:00:00+00'),
  (101, 'shipped',    50, '2024-03-02 12:00:00+00'),
  (101, 'shipped',    30, '2024-03-04 15:00:00+00'),
  (102, 'received',  150, '2024-03-01 08:00:00+00'),
  (102, 'adjusted',  -10, '2024-03-03 09:00:00+00');

-- Rebuild current stock for all products
SELECT
  product_id,
  SUM(CASE event_type WHEN 'received' THEN quantity WHEN 'shipped' THEN -quantity ELSE quantity END) AS stock_on_hand
FROM inventory_events
GROUP BY product_id
ORDER BY product_id;

Using CTEs for Clarity

State-rebuild queries can grow complex. Wrapping the fold step in a CTE improves readability and lets you join the rebuilt state against other tables cleanly.

Here we rebuild account balances and then join them to an accounts reference table to include owner names in the output.

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  owner_name VARCHAR(100) NOT NULL
);

INSERT INTO accounts (account_id, owner_name) VALUES
  (1, 'Alice'),
  (2, 'Bob');

INSERT INTO account_events (account_id, event_type, amount, created_at) VALUES
  (2, 'deposit',   2000.00, '2024-01-02 10:00:00+00'),
  (2, 'withdrawal', 400.00, '2024-01-06 11:00:00+00');

WITH rebuilt_balances AS (
  SELECT
    account_id,
    SUM(CASE event_type WHEN 'deposit' THEN amount WHEN 'withdrawal' THEN -amount ELSE 0 END) AS balance
  FROM account_events
  GROUP BY account_id
)
SELECT
  a.account_id,
  a.owner_name,
  rb.balance
FROM accounts a
JOIN rebuilt_balances rb USING (account_id)
ORDER BY a.account_id;

Knowledge Check

Test your understanding of rebuilding state from events in SQL.

Lesson Recap

In this lesson you learned how to rebuild current and historical state from an immutable event log using SQL.

Key takeaways:

  • State is derived by folding (aggregating) events with a signed CASE expression inside SUM.
  • Adding a timestamp filter gives you point-in-time queries for free.
  • Window functions produce a running state after every event.
  • Snapshot tables materialize the folded result for performance; incremental updates apply only new events.
  • Emulated temporal tables store pre-folded state rows with validity ranges for fast historical lookups.
  • CTEs keep rebuild queries readable when you need to join the derived state against other tables.

These patterns are the foundation of event-sourced and audit-friendly database designs.

Frequently asked questions

Is the “Rebuilding State from Events” lesson free?

Yes — the full text of “Rebuilding State from Events” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Rebuilding State from Events”?

Fold events into current state. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Rebuilding State from Events” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Keep History
  2. Append-Only Event Tables
  3. Temporal and Versioned Rows
  4. Rebuilding State from Events
← Back to SQL Academy