Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
Ask any chat product’s support team for their most common complaint and it will be some variant of “the badge is wrong”. The reason is almost always that someone modelled read state with one cursor when the domain has two, and the two failure modes are mirror images of each other. Store read state per user only, and every device tries to be the authority on what has been delivered: the laptop that was asleep during lunch reconnects, believes it is up to date because the phone advanced the shared cursor, and silently never renders forty messages. Store it per device only, and the badge never clears: you read a channel on your phone, and the laptop, having no idea, keeps a red dot on it until you open it there too — which is exactly the behaviour users describe as “the app is broken”. The correct model separates a semantic fact from a transport fact. Whether a human has read a channel is a property of the human, merged across all their devices, monotonic, and durable. Which messages have reached a particular installation is a property of that installation, unrelated to reading, and used for exactly one purpose: working out what to send when it reconnects. Once those are two columns in two tables, both bugs become impossible rather than merely rare.
Two tables and one arithmetic identity.
The badge cursor, last_read_seq, is per (user, channel) and is updated with a GREATEST — never a plain assignment. This detail matters more than it looks. Two devices acknowledge reads independently and their updates arrive in arbitrary order over unreliable networks; a plain assignment lets a delayed update from the phone drag the badge backwards and resurrect messages the user already read. Monotonic merge makes the operation commutative, so the order the updates arrive in stops mattering at all.
The catch-up cursor, last_delivered_seq, is per (device, channel) and is what a reconnecting client sends up. The server replies with the diff — or, when the gap is too large to stream, refuses and orders a resync, which is a bounded read of the newest page plus the badge count rather than an unbounded backfill of nine thousand messages the user will never scroll to.
And the unread count itself is a subtraction, not a count. channel_head.high_water_seq minus last_read_seq. No rows scanned, no per-user fan-out at write time, correct for a channel with four members and a channel with fifty thousand. This is the payoff of the sequence-number decision from challenge 3, and it is why the offline majority in challenge 5 costs nothing: their badge is computed at read time out of two integers that already exist.
One honest caveat: because the sequence is strictly increasing but not dense, the subtraction is an upper bound, not an exact count. For a badge that shows a number up to some cap and “99+” above it, that is fine and is what real products ship. If you need an exact count, count rows for the small case and cap the large one — do not try to make seq dense.
-- PER PERSON. Semantic. Drives the badge. Merged across devices.
CREATE TABLE channel_read_state (
user_id bigint NOT NULL,
channel_id bigint NOT NULL,
last_read_seq bigint NOT NULL DEFAULT 0,
muted boolean NOT NULL DEFAULT false,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, channel_id)
);
-- PER DEVICE. Transport. Drives reconnect catch-up. Never the badge.
CREATE TABLE device_sync_state (
device_id text NOT NULL,
user_id bigint NOT NULL,
channel_id bigint NOT NULL,
last_delivered_seq bigint NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (device_id, channel_id)
);
-- THE update. GREATEST, not "=". Two devices ack out of order over
-- unreliable networks; a plain assignment lets a late update drag the
-- badge backwards and resurrect messages the user already read.
INSERT INTO channel_read_state (user_id, channel_id, last_read_seq, updated_at)
VALUES ($1, $2, $3, now())
ON CONFLICT (user_id, channel_id) DO UPDATE
SET last_read_seq = GREATEST(channel_read_state.last_read_seq, EXCLUDED.last_read_seq),
updated_at = now();
-- THE badge. A subtraction over two integers. No rows scanned, no
-- per-user write at send time, identical cost for a 4-member channel and
-- a 50,000-member one.
SELECT c.channel_id,
GREATEST(h.high_water_seq - COALESCE(r.last_read_seq, 0), 0) AS unread_upper_bound
FROM channel_membership c
JOIN channel_head h USING (channel_id)
LEFT JOIN channel_read_state r
ON r.channel_id = c.channel_id AND r.user_id = c.user_id
WHERE c.user_id = $1 AND NOT COALESCE(r.muted, false);
-- Deliberately NOT a table: per-message, per-device delivery receipts.
-- One row per (message, device) is 8,000 rows per message in a busy
-- channel, to answer a question a cursor already answers.