✏️ Explanatory Question

Design a schema for a WhatsApp-style chat system (messages, conversations, read receipts)

👁 8 Views
📘 Detailed Answer
🟢 Easy
← Previous Design the database schema for a URL shortener like bit.ly
💡

Answer with Explanation

110

Design a schema for a WhatsApp-style chat system that scales to billions of rows

Level: Hard — Tests many-to-many modeling, high-write design, and the read-receipt fan-out problem.

Scenario: Design the schema for a chat app supporting 1-on-1 and group chats, message history, and read receipts ("seen by"). It must handle billions of messages and answer "give me the latest 50 messages in this conversation" instantly. Walk through your tables and the key indexes.

Requirements to Clarify First

  • 1-on-1 only, or groups too? → Both (model uniformly as "conversations").
  • Read receipts per user? → Yes (who has seen each message).
  • Access pattern? → "Latest N messages per conversation" (paginated, newest first).
  • Scale? → Write-heavy, billions of rows → partitioning/sharding needed.
Key design decision: Model everything as a "conversation" — a 1-on-1 chat is just a conversation with 2 members, a group is one with many. This avoids separate tables/logic for DMs vs groups.

The Core Schema

-- A conversation (thread) — works for both 1-on-1 and group
CREATE TABLE conversations (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    type        ENUM('direct','group') NOT NULL,
    title       VARCHAR(255),               -- group name (NULL for direct)
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Membership (many-to-many: users <-> conversations)
CREATE TABLE conversation_members (
    conversation_id BIGINT NOT NULL,
    user_id         BIGINT NOT NULL,
    joined_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_read_message_id BIGINT,            -- pointer for unread counts
    PRIMARY KEY (conversation_id, user_id), -- a user is in a convo once
    INDEX idx_user (user_id)                -- "list my conversations"
) ENGINE=InnoDB;

-- Messages (the huge, write-heavy table)
CREATE TABLE messages (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT NOT NULL,
    sender_id       BIGINT NOT NULL,
    body            TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- THE critical index: latest messages per conversation
    INDEX idx_convo_time (conversation_id, id)
) ENGINE=InnoDB;
The most important index: (conversation_id, id) on messages. Since id is auto-increment (time-ordered), "latest 50 messages" becomes a single indexed range scan — no filesort, no full scan.

The Hot Query — Latest 50 Messages

-- Uses the (conversation_id, id) index directly; keyset pagination for scroll
SELECT id, sender_id, body, created_at
FROM messages
WHERE conversation_id = 42
  AND id < 9000000            -- last seen id (keyset, for "load older")
ORDER BY id DESC
LIMIT 50;

The Read-Receipt Challenge

"Seen by" is a fan-out problem: in a 100-member group, one message can generate 100 receipt rows. Two common designs:

Per-Message Receipts (accurate)

  • Row per (message, user) seen
  • Exact "seen by X, Y, Z"
  • Huge write volume in big groups

Last-Read Pointer (scalable)

  • One last_read_message_id per member
  • Cheap: a message is "read" if its id ≤ pointer
  • Great for unread counts

Read Receipts — The Scalable Pointer Approach

-- Mark conversation read up to a message (one cheap UPDATE per user)
UPDATE conversation_members
SET last_read_message_id = 9000123
WHERE conversation_id = 42 AND user_id = 7;

-- Unread count for a user in a conversation
SELECT COUNT(*) AS unread
FROM messages m
JOIN conversation_members cm
     ON cm.conversation_id = m.conversation_id AND cm.user_id = 7
WHERE m.conversation_id = 42
  AND m.id > COALESCE(cm.last_read_message_id, 0);

-- For exact "seen by" in groups, an explicit receipts table (partitioned)
CREATE TABLE message_reads (
    message_id BIGINT NOT NULL,
    user_id    BIGINT NOT NULL,
    read_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (message_id, user_id)
) ENGINE=InnoDB;

Scaling to Billions

  • Shard messages by conversation_id — all of a chat's messages live together.
  • Partition by time for cheap archival of old messages.
  • Cache recent messages and unread counts in Redis.
  • Consider BIGINT time-sortable IDs (Snowflake) instead of auto-increment for sharded uniqueness.
Interviewer follow-up: "For a 1-on-1 chat, how do you find the existing conversation between two users without duplicates?" → Enforce a canonical rule: store a direct_key like LEAST(u1,u2) || '-' || GREATEST(u1,u2) with a UNIQUE constraint. That guarantees exactly one direct conversation per pair, regardless of who starts it — and makes "find or create" a single indexed lookup.
← Previous Design the database schema for a URL shortener like bit.ly