SQL Academy · Lesson

Append-Only Event Tables

Record what happened, never overwrite.

What Is an Append-Only Table?

An append-only event table is a table where rows are only ever inserted — never updated or deleted. Each row represents something that happened at a specific point in time.

This pattern is the foundation of event sourcing. Instead of storing the current state, you store every change as an immutable event, giving you a complete, auditable history.

Creating an Event Table

A well-designed event table captures who did what, to which resource, and when. The occurred_at column records the exact timestamp, and DEFAULT NOW() ensures it is always filled in automatically.

Notice there is no UPDATE or DELETE in this design — rows are permanent once written.

CREATE TABLE account_events (
  id           BIGSERIAL PRIMARY KEY,
  account_id   BIGINT      NOT NULL,
  event_type   TEXT        NOT NULL,
  payload      JSONB,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

All lessons in this course

← Back to SQL Academy