Chat: Conversations, Messages, Delivery and Read State

In one line

Chat stores conversations, participants and messages ordered per conversation, plus per-user delivery and read markers (not per-message read rows). Messages are append-heavy and read by conversation and recency, which suits partitioned tables or wide-column stores keyed by conversation and time bucket. Offline delivery uses per-user inboxes, and retention policies bound storage.

0/8 · 0%

Think of it like this

A group's shared notebook. Each page (conversation) collects notes in order; instead of ticking every note as read, each member keeps a bookmark at the last note they've read.

Key ideas

01
Entities: user, conversation (direct or group), participant(conversation_id, user_id, role, joined_at, last_read_message_id, muted), message(conversation_id, message_id, sender_id, body, attachments, created_at), attachment metadata pointing to object storage.
02
Ordering: per-conversation sequence numbers (assigned by the conversation's owner shard, or Snowflake IDs) define a total order within a conversation; clients sort and de-duplicate by message ID.
03
Read state: store last_read_message_id per participant; unread count = messages after it (or a maintained counter). Per-message read receipts only for small groups.
04
Delivery: messages are persisted first, then pushed over WebSockets; offline users get them on reconnect via "messages after my last seen ID per conversation"; push notifications for mobile.
05
Idempotent sends: a client-generated message ID (UUID) with a unique constraint prevents duplicates when a send is retried after a timeout.

Code & diagrams

chat.sqlsql

CREATE TABLE conversation (id bigint PRIMARY KEY, kind text NOT NULL CHECK (kind IN ('direct','group')),
                           created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE participant (
  conversation_id bigint NOT NULL REFERENCES conversation(id),
  user_id bigint NOT NULL,
  last_read_seq bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (conversation_id, user_id)
);
CREATE INDEX ON participant (user_id);           -- "my conversations"

CREATE TABLE message (
  conversation_id bigint NOT NULL,
  seq bigint NOT NULL,                            -- per-conversation order
  client_msg_id uuid NOT NULL,                    -- idempotent retries
  sender_id bigint NOT NULL,
  body text,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (conversation_id, seq),
  UNIQUE (conversation_id, client_msg_id)
) PARTITION BY HASH (conversation_id);

-- newest page, then older pages by keyset
SELECT seq, sender_id, body FROM message
WHERE conversation_id = 77 AND seq < 10450 ORDER BY seq DESC LIMIT 50;

-- unread count for a user in a conversation
SELECT count(*) FROM message m JOIN participant p USING (conversation_id)
WHERE p.conversation_id = 77 AND p.user_id = 42 AND m.seq > p.last_read_seq;

Interview problem

The problem

WhatsApp-scale message storage

2B users, 100B messages/day, groups up to 1,024 members, messages delivered to offline users when they return, and optional server-side history. Design the storage, ordering and delivery state.

When it breaks

Per-message read-receipt rows for large groups

What you see

A 1,000-member group generates 1,000 rows per message; storage and write load explode.

Fix & prevent

Per-participant last-read markers; compute receipts from markers.

Explain it without notes

Why store last_read per participant instead of a read flag per message?

Practice

Make message sends idempotent when the mobile client retries after a timeout.

Trade-offs

↔
Server-side history enables multi-device sync and search but multiplies storage; delivery-only storage is cheaper and more private.

Done when you can

I can design chat storage with ordering, idempotent sends, read markers and retention.

Command Palette