Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
Every design in this module so far rests on one assumption: a message, once written, never changes. That assumption bought immutable rows, safe cold-storage tiering, stable keyset cursors, and a cache that never needs invalidating. Then the product ships an edit button, and it is worth being precise about what that button does to the design, because the naive implementation is a single UPDATE statement and it quietly breaks five things at once. It invalidates every cache holding that message, including ones on devices you cannot reach. It leaves the search index stale. It makes cold segments un-freezable, because any row might change. It gives replicas reading a snapshot a different answer from the primary. And it destroys the audit trail in a product where “what did they actually say before they edited it” is sometimes a legal question. Deletes are worse, because there are two entirely different features wearing the same word: the delete where a message disappears from a channel, and the delete where a regulator requires the bytes to be gone. Threads are worse still, because a thread is not a feature on a message — it is a second ordering domain grafted onto the first, and every count, cursor and badge in the system now exists twice. This challenge is the one that separates designs that survive their own roadmap from designs that get rewritten in year two.
The move that makes all three tractable is the same move: express the change as another append.
An edit is a new event in the channel log, at a new sequence number, that references the sequence number it modifies. This preserves everything. The log stays append-only, so cursors and cold segments stay valid. Connected clients receive the edit through the ordinary fan-out path, with no new delivery mechanism. The audit trail is the log itself. And the original row is untouched, so a replica reading a snapshot is never inconsistent — it just has fewer revisions applied.
The cost lands on the read path, and it must be bounded or it eats the design. Resolving a page of fifty messages cannot mean scanning the channel’s edit history; it means one extra keyset-bounded query against a small side table, keyed by the message being targeted, returning the latest revision of anything in the page’s sequence range. Two queries per page, both bounded, both prefix scans.
A delete is a revision whose kind is a tombstone. The row stays, the sequence number stays, the body goes. Never renumber and never physically remove, because both would shift every cursor held by every client. The regulatory delete is a different mechanism entirely: either a scheduled physical purge that accepts the cursor holes it creates, or per-message encryption keys that get destroyed — crypto-shredding — which makes the bytes unrecoverable while leaving the row, the sequence and every cursor perfectly intact.
A thread is the expensive one, because it is a second sequence domain. The clean design gives the thread its own log, keyed by the root message, with its own sequence numbers and its own head — which means the read cursor from challenge 6 now has to exist per thread as well as per channel, and the badge is a sum over an unbounded set of thread cursors rather than a single subtraction. The mitigation used by real products is a subscription model: you only carry a cursor for threads you participate in, so the sum is over a handful, not over thousands. That is a product decision doing load-bearing work in the architecture, which is what makes it worth noticing.
-- Messages stay immutable. Every change is a row in a small side table
-- keyed by the message it targets, applied at read time.
CREATE TABLE message_revision (
channel_id bigint NOT NULL,
target_seq bigint NOT NULL, -- the message being changed
rev int NOT NULL, -- 1, 2, 3 ...
kind text NOT NULL, -- 'edit' | 'tombstone'
body text, -- NULL for a tombstone
edit_seq bigint NOT NULL, -- seq of the edit EVENT in the channel log
actor_id bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (channel_id, target_seq, rev)
);
-- Page resolution: ONE extra query per page, bounded by the page's own
-- sequence range. Never a scan of the channel's edit history.
SELECT DISTINCT ON (target_seq) target_seq, kind, body, actor_id, created_at
FROM message_revision
WHERE channel_id = $1 AND target_seq BETWEEN $2 AND $3 -- the page range
ORDER BY target_seq, rev DESC;
-- Threads: a second ordering domain, with its own head.
CREATE TABLE thread_head (
channel_id bigint NOT NULL,
root_seq bigint NOT NULL, -- the message the thread hangs off
high_water_seq bigint NOT NULL, -- thread-local sequence, independent
reply_count int NOT NULL DEFAULT 0,
PRIMARY KEY (channel_id, root_seq)
);
-- ...which means the read cursor from challenge 6 now exists twice.
CREATE TABLE thread_read_state (
user_id bigint NOT NULL,
channel_id bigint NOT NULL,
root_seq bigint NOT NULL,
last_read_seq bigint NOT NULL DEFAULT 0,
subscribed boolean NOT NULL DEFAULT true, -- the load-bearing column:
PRIMARY KEY (user_id, channel_id, root_seq) -- badges sum over SUBSCRIBED
); -- threads only, a handful,
-- not over thousands
-- Regulatory erasure, the version that does not break cursors. Bodies
-- are stored encrypted per message; destroying the key destroys the
-- plaintext while the row, its seq and every cursor stay intact.
CREATE TABLE message_key (
channel_id bigint NOT NULL,
seq bigint NOT NULL,
wrapped_key bytea, -- SET NULL to shred; row remains
PRIMARY KEY (channel_id, seq)
);