Chat / Messaging
Design the schema for a chat product that supports 1:1 and group conversations, persistent message history, per-user read receipts, and presence. Optimize for the dominant read pattern: 'load the last N messages in a conversation, newest first.'
Clarifying questions
- Are groups bounded (Slack-style channels of 10k) or capped small (WhatsApp 256)? It changes the membership table cost.
- Do we need server-side full-text search, or is search delegated to a separate index?
- How long do we retain messages — forever, or with a per-conversation TTL (Time-To-Live)?
- Is presence authoritative in the DB or pushed through a separate pub/sub layer?
- Do read receipts need to be per-message or just a high-water mark per user per conversation?
Core requirements
- Store users, conversations (1:1 and group), and messages with ordering guarantees.
- Look up the last N messages in a conversation in O(log n) keyed on conversation_id.
- Track per-user last-read position so unread counts are cheap.
- Support group membership add/remove without rewriting history.
- Soft-delete messages so 'deleted for me' and 'deleted for everyone' both work.
- Scale messages table to billions of rows with predictable query latency.
Canonical schema (PostgreSQL)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
handle TEXT UNIQUE NOT NULL,
display_name TEXT NOT NULL,
last_seen_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE conversations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
kind TEXT NOT NULL CHECK (kind IN ('direct', 'group')),
title TEXT,
created_by UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_message_at TIMESTAMPTZ
);
CREATE TABLE conversation_members (
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
last_read_message_id BIGINT,
muted_until TIMESTAMPTZ,
PRIMARY KEY (conversation_id, user_id)
);
CREATE TABLE messages (
id BIGSERIAL PRIMARY KEY,
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
sender_id UUID NOT NULL REFERENCES users(id),
body TEXT,
attachments JSONB,
reply_to_id BIGINT REFERENCES messages(id),
deleted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
CREATE TABLE message_receipts (
message_id BIGINT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
delivered_at TIMESTAMPTZ,
read_at TIMESTAMPTZ,
PRIMARY KEY (message_id, user_id)
);Key indexes
CREATE INDEX idx_messages_conv_created ON messages(conversation_id, created_at DESC, id DESC)
The hot query is 'last N messages in a conversation' — this serves it as an index-only range scan.
CREATE INDEX idx_conv_members_user ON conversation_members(user_id, conversation_id)
Inbox view: 'list all conversations for this user' joins back to conversations on last_message_at.
CREATE INDEX idx_conversations_recent ON conversations(last_message_at DESC) WHERE last_message_at IS NOT NULL
Partial index for global recency feeds and admin tooling — empty conversations stay out.
CREATE INDEX idx_messages_sender ON messages(sender_id, created_at DESC) WHERE deleted_at IS NULL
Supports 'show messages I sent' and moderation lookups while ignoring tombstones.
CREATE INDEX idx_receipts_unread ON message_receipts(user_id, message_id) WHERE read_at IS NULL
Partial index keeps unread-count queries small even as the receipts table grows unbounded.
Key decisions to defend
- Shard / partition messages by created_at range — time-bounded queries align with retention, and old partitions can be moved to cheap storage.
- BIGSERIAL on messages.id for monotonic ordering and cheap keyset pagination; UUIDs elsewhere where global uniqueness matters more than locality.
- Track read state as last_read_message_id on conversation_members for the unread-count fast path; message_receipts only exists when per-message receipts are required.
- Soft-delete messages (deleted_at) instead of DROP — preserves reply threads and moderation history without leaving orphan references.
- Presence lives outside Postgres (Redis or a pub/sub layer); last_seen_at is a denormalized cache, not the source of truth.
Likely follow-ups
- · Scale to 10M concurrent users — what changes about the shard key?
- · Add end-to-end encryption — what columns disappear and what gets opaque?
- · Support message reactions without exploding the receipts table.
- · A celebrity broadcasts to a 1M-member channel — how do you avoid the fan-out write storm?
Dive deeper
This tests whether you recognize the access pattern (newest-first within a conversation) and let it drive every choice: partition key, composite index direction, and the decision to track read state as a high-water mark instead of per-message rows. Strong candidates separate hot small-state (last_read) from cold large-state (receipts) and call out partitioning before the interviewer has to ask.