In a chat app with 5,000-member groups, how would you store read state so unread counts and read receipts stay cheap?

## Two features, one data model **Unread counts** answer "how many messages in this chat have I not seen?" **Read receipts** answer "who has seen my message?" A naive design stores a row for every (message, reader) pair and serves both features from it. That works in a 1:1 chat and collapses in large groups. ## Why per-message receipts explode The row count grows as **messages x members**. As an illustration, assume a 5,000-member group that receives 200 messages a day: - 200 x 5,000 = **1,000,000** receipt rows a day for that single group; - every read by one member must write rows for each message they scrolled past; - if receipts are pushed live, every read triggers an update to up to 5,000 other members; - counting unread messages means counting rows, which gets slower as the backlog grows. Write volume, storage and fanout all grow with group size, while almost nobody reads a 5,000-person receipt list. ## The read cursor Messages already carry a **per-conversation sequence number**, so read state can be compressed into one number per member: ```sql CREATE TABLE read_cursor ( user_id BIGINT NOT NULL, conversation_id BIGINT NOT NULL, last_read_seq BIGINT NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY (user_id, conversation_id) ); -- advance only forward UPDATE read_cursor SET last_read_seq = GREATEST(last_read_seq, :seq), updated_at = :now WHERE user_id = :u AND conversation_id = :c; ``` - **One row per member per conversation**, however many messages exist. - **Forward-only updates** (`max(old, new)`): a phone and a laptop can report reads in any order without moving the cursor back. - **Own messages** advance the sender's cursor when sent, so they never count as unread. ## Deriving both features | Feature | Per-message rows | Read cursor | |---|---|---| | Unread count for one chat | count the unread rows | `latest_seq - last_read_seq` | | Total badge across chats | count over all chats | sum of per-chat differences, cached | | 1:1 "read" tick | look up one row | peer cursor >= seq | | Group "read by N" | count the rows | count members with cursor >= seq | | Write cost of one read | one row per message read | one cursor update | The unread count is an approximation when deleted messages leave tombstones in the sequence. Products either accept that or subtract a per-conversation deleted-count. ## Keeping receipts cheap in large groups 1. **Debounce on the client**: while the user scrolls, send only the highest sequence seen, at most every few seconds. 2. **Compute on demand**: "read by 1,243" is computed when someone opens message info, by counting cursors at or above that sequence, not maintained per message. 3. **Limit by size**: many products show full per-person receipts only in small groups, and aggregate counts or nothing in large ones. 4. **No fanout of every read**: in big groups, do not push each cursor move to all members. A sender who has the info screen open can subscribe to updates for that one message. ## Other details that matter - **Multi-device**: the cursor is per user, not per device, so reading on the laptop clears the phone's badge. Send a silent sync to the other devices. - **Badge totals**: a user in hundreds of chats should not trigger hundreds of subtractions per push. Cache a per-user unread total and adjust it when a cursor or a latest sequence changes, and rebuild it now and then to correct drift. - **Delivered and read**: "delivered" can use the same idea with a per-device delivered cursor, advanced by the device ack. - **Privacy**: if a user turns off read receipts, their cursor still drives their own unread count but is never shown to others. The core idea is that ordered sequence numbers turn "which messages did I read" into a single number, so storage grows with membership instead of message volume.