Append-Only Event Tables
Record what happened, never overwrite.
Append-Only Event Tables is a free SQL Academy lesson on CoddyKit — lesson 2 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 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()
);Inserting Events
Every action by a user — logging in, depositing money, changing an email — becomes a new row. You never go back and edit a previous event. If something needs to be corrected, you insert a compensating event instead.
This preserves the full sequence of what happened, in order.
INSERT INTO account_events (account_id, event_type, payload)
VALUES
(42, 'account_opened', '{"plan": "free"}'),
(42, 'email_verified', '{"email": "alice@example.com"}'),
(42, 'plan_upgraded', '{"from": "free", "to": "pro"}');Reading the Full History
Because every state change is stored as a row, querying the full history for an account is a simple SELECT ordered by time. You can replay the entire life of a record from the very first event to the latest one.
SELECT
id,
event_type,
payload,
occurred_at
FROM account_events
WHERE account_id = 42
ORDER BY occurred_at ASC;Deriving Current State
With an append-only table you do not store current state directly — you derive it by reading the latest relevant event. Here, the current plan for account 42 is whatever the most recent plan_upgraded or account_opened event says.
Using ORDER BY occurred_at DESC LIMIT 1 efficiently fetches the most recent snapshot.
SELECT payload->>'to' AS current_plan
FROM account_events
WHERE account_id = 42
AND event_type IN ('account_opened', 'plan_upgraded')
ORDER BY occurred_at DESC
LIMIT 1;Enforcing Immutability with Rules
The append-only guarantee can be enforced at the database level using a RULE that silently ignores any UPDATE or DELETE on the table. This prevents accidental mutations from any application that has write access.
A trigger that raises an exception is an even stronger alternative, as it actively rejects the operation with an error.
CREATE RULE no_update_events AS
ON UPDATE TO account_events
DO INSTEAD NOTHING;
CREATE RULE no_delete_events AS
ON DELETE TO account_events
DO INSTEAD NOTHING;Immutability via Trigger
A trigger that raises an exception is stricter than a silent rule — the application receives an error immediately if it tries to mutate a past event. This makes bugs visible rather than silently swallowing them.
CREATE OR REPLACE FUNCTION deny_event_mutation()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'Event table is append-only: % is not allowed', TG_OP;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_deny_event_mutation
BEFORE UPDATE OR DELETE ON account_events
FOR EACH ROW EXECUTE FUNCTION deny_event_mutation();Counting Events Over Time
Append-only tables make time-based analytics straightforward. Since every event has a timestamp you can group by day, week, or month without any extra columns. The example below counts how many events of each type occurred per day.
SELECT
DATE_TRUNC('day', occurred_at) AS day,
event_type,
COUNT(*) AS total
FROM account_events
GROUP BY 1, 2
ORDER BY 1, 2;Point-in-Time Reconstruction
One of the most powerful properties of an event log is the ability to reconstruct the state of any record as it existed at a past moment. Simply filter events up to the desired timestamp — no time-travel extension required.
This is invaluable for debugging, audits, and regulatory compliance.
-- What plan was account 42 on at the end of last month?
SELECT payload->>'to' AS plan_at_snapshot
FROM account_events
WHERE account_id = 42
AND event_type IN ('account_opened', 'plan_upgraded')
AND occurred_at <= DATE_TRUNC('month', NOW()) - INTERVAL '1 second'
ORDER BY occurred_at DESC
LIMIT 1;Compensating Events Instead of Corrections
When a mistake is discovered — say, an incorrect charge — you do not delete the bad event. Instead, you insert a compensating event that cancels or reverses it. Both events remain visible in the log, showing exactly what happened and when the correction was made.
This keeps the audit trail complete and tamper-evident.
-- A charge was applied by mistake; record a reversal
INSERT INTO account_events (account_id, event_type, payload)
VALUES (
42,
'charge_reversed',
'{"reason": "billing_error", "reverses_event_id": 17}'
);Partitioning Large Event Tables
Event tables grow quickly. Partitioning by a time range keeps individual partitions small, speeds up range queries, and allows old partitions to be archived or dropped without touching recent data.
PostgreSQL's declarative partitioning makes this straightforward: define a RANGE partition on occurred_at and let the database route inserts automatically.
CREATE TABLE account_events_2025
PARTITION OF account_events
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE account_events_2026
PARTITION OF account_events
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');Append-Only Tables Knowledge Check
Test your understanding of append-only event table design.
Recap: Append-Only Event Tables
In this lesson you learned how to design and use append-only event tables:
- Immutability — rows are inserted once and never modified. Past events are facts.
- Complete history — every state change is preserved, enabling full audit trails and point-in-time queries.
- Compensating events — mistakes are corrected by adding a new reversal event, not by deleting the old one.
- Enforcement — rules or triggers at the database level prevent accidental mutations.
- Scalability — range partitioning keeps large event logs performant over time.
Append-only tables are the backbone of event sourcing, CQRS architectures, and any system where auditability and historical accuracy are critical.
Frequently asked questions
Is the “Append-Only Event Tables” lesson free?
Yes — the full text of “Append-Only Event Tables” 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 “Append-Only Event Tables”?
Record what happened, never overwrite. 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 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Append-Only Event Tables” 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
- Why Keep History
- Append-Only Event Tables
- Temporal and Versioned Rows
- Rebuilding State from Events