Slack

Same startup as the URL Shortener journey. Same engineer #4. Different Monday.

Your CTO drops by your desk with coffee. "Marketing loves the URL shortener you shipped. Now they want team chat inside the platform. Persistent channels, DMs, threads, presence indicators, file uploads. Like Slack. Ship an MVP in 12 weeks. Enterprise customers want compliance. Oh — and it needs to feel snappy, sub-100ms message delivery."

She walks away.

Now — same discipline, different problem shape. URL Shortener was read-heavy (100 redirects per URL created). Slack is write-heavy with real-time delivery (every keystroke fires a presence event; every message fans out to all channel members instantly). The architecture that worked for URL Shortener will not work here. Understanding why is the whole game.

Here's the shape you're going to design across the next 12 chapters:

The whole journey at a glance

Every 10× in team scale forces a fundamentally different architecture:

text
    ═══════════ SLACK ARCHITECTURE ACROSS 4 SCALES ═══════════

    L4 (10K users)     L5 (100K users)   L6 (1M users)         L7 (1B users)
    100 teams          10K teams         100K teams            enterprise-federated
    12 weeks · $500/mo 6 months · $8K/mo 18 months · $200K/mo  ongoing · $10M/yr

    ┌────────┐         ┌────────┐        ┌───────── CDN ─────┐   ┌─── Own CDN ────┐
    │  Web   │         │  Web   │        │  (files + assets) │   │  files + edge  │
    │ Mobile │         │ Mobile │        └───┬──┬───────┬────┘   │  message relay │
    └───┬────┘         └───┬────┘            │  │       │        └──┬──┬──┬──────┘
        │                  │                 │  │       │           │  │  │
     ┌──▼───┐           ┌──▼──┐           ┌──▼──▼──┬────▼──┐    ┌───▼┐ ▼  ▼
     │ ALB  │           │ ALB │           │us-east │eu-w-1 │    │us  │eu  ap
     │ WSS  │           │ WSS │           │ ALB WSS│ ALB   │    │reg │reg │reg
     └──┬───┘           └──┬──┘           └───┬────┴───┬───┘    └─┬──┘┬─┘┬┘
        │                  │                  │        │           │   │  │
     ┌──▼──────┐       ┌───▼─────────┐   ┌────▼────┐ ┌─▼─────┐ ┌──▼─┐ ▼  ▼
     │ 1 WS    │       │ 4 WS nodes  │   │ 32 WS   │ │ 32 WS │ │50 WS + edge│
     │ server  │       │ sticky-team │   │ nodes   │ │       │ │ presence   │
     │ 100 tm  │       │ affinity    │   │ team-   │ │       │ │ enterprise │
     └──┬──────┘       │ routing     │   │ affinity│ │       │ │ isolation  │
        │              └───┬─────────┘   │ shard by│ │       │ └──┬─────────┘
        │                  │             │ team_id │ │       │    │
        │              ┌───▼──┐          └───┬─────┘ └───────┘ ┌──▼─────────┐
        │              │Redis │              │                  │ Regional   │
        │              │ pres │          ┌───▼─────────┐        │ Kafka +    │
        │              │ +typ │          │  Regional   │        │ Flink for  │
        │              └───┬──┘          │  Kafka      │        │ fanout +   │
        │                  │             │  fanout     │        │ history    │
     ┌──▼───┐          ┌───▼─┐           │  by team    │        └──┬─────────┘
     │MySQL         │Post │           └───┬─────────┘           │
     │(msgs +          │gres │               │                  ┌──▼──────────┐
     │ teams)          │+ 2  │           ┌───▼──────┐           │Sharded PG   │
     │Multi-           │repl │           │ Sharded  │           │(CockroachDB │
     │AZ               │icas │           │ MySQL │           │ or per-     │
     └──────┘          └─────┘           │ 8 shards │           │ enterprise) │
                                         │ by team  │           └─────────────┘
                                         └──────────┘

    ↑                  ↑                 ↑                       ↑
   Boring         Add cache + shard   Add sharded WS +         Federated per
   works.         WS by team.         team-affinity +          enterprise.
                  MySQL holds all  Kafka fanout.            Cost = business
                  messages.                                    strategy.

   Chapter 5      Chapters 6+6.5      Chapter 7+7.5            Chapter 8
   walks          walks through       walks through            walks through
   through        team-affinity       sharding by team_id,     enterprise-
   L4 in          routing + Redis     Kafka fanout for         federated
   full           presence            channel messages         architecture

  Key insight: EVERY message must be delivered to N channel members
  in real time. That's the difference from URL Shortener - reads and
  writes are BOTH high volume, and both require sub-100ms latency.

The same 4 tiers as clean architecture diagrams

The ASCII shows the whole story compressed side-by-side. Here are the same four tiers as clean Mermaid flowcharts — the shapes an interviewer expects you to sketch on a whiteboard:

L4 · 10K users · 100 teams · $500/mo · 12 weeks:

L5 · 100K users · 10K teams · $8K/mo · 6 months:

L6 · 1M users · 100K teams · $200K/mo · 18 months:

L7 · 1B users · enterprise-federated · $10M/yr:

Read the four in order: each tier adds exactly the components its bottleneck forced. L4 → L5 adds sticky team-affinity routing + Redis presence + read replicas because ONE WS node maxes out around 10-20K persistent connections. L5 → L6 adds CDN + multi-region + Kafka fanout + MySQL sharding because 100K → 1M users means a single Kafka topic (or single PG primary) becomes the bottleneck. L6 → L7 adds an owned CDN + per-region compliance stacks + per-enterprise data isolation because at 1B users the choice becomes strategy (build vs buy CDN, own network vs commercial) and regulatory (data residency per region).

Read this chart before diving in. Notice the differences from URL Shortener:

• WebSockets everywhere. Slack is a persistent-connection product, not a request-response product. A single user holds an open connection for hours. That changes load balancer choices, changes memory profile per server, changes failover semantics.
• Team-affinity routing. All members of Team A should ideally land on the same WebSocket server so message fanout is a single-server broadcast, not a cross-server RPC. That's a hard routing problem.
• Sharding by team, not by user. Team is the unit of everything: messages, channels, presence, permissions. A user in 3 teams belongs to 3 shards.
• Message fanout dominates the cost. Every message posted to a 50-person channel produces 50 delivery events. At scale, fanout math dominates architecture.

What's different from URL Shortener

The takeaways apply to any real-time messaging system: Slack, Discord, WhatsApp, Teams, iMessage. If you understand these 4 scale tiers for Slack, you understand ~80% of the design surface for any real-time messaging system.

Where the rest of this journey goes

Chapters 1-4 — same discipline as URL Shortener:
- Ch 1 Requirements — the 6 clarifying questions specific to messaging
- Ch 2 Estimation — the fanout math: how much traffic does a 50-person channel actually create?
- Ch 3 API — REST endpoints for CRUD + WebSocket protocol for real-time
- Ch 4 Data model — messages, channels, teams, memberships (way more entities than URL Shortener)

Chapters 5-8 — the scale evolution shown above.

Chapters 9-12 — failures, trade-offs, masterclass, defense (mirror of URL Shortener structure).

Deliberate pace note. This journey follows the URL Shortener template. If you completed URL Shortener, you know the shape: each chapter builds on the previous. Don't skip ahead. The trade-offs at L6 only make sense once you've felt the pain of L5.

If you haven't done URL Shortener yet, do it first — it's the simpler shape and gives you the vocabulary for this one.

Chapters queued (session note)

Chapters 1-12 of this journey are queued for follow-on authoring sessions. This Chapter 0 exists so you can see the whole arc and decide if this journey is right for your current preparation. Come back as the remaining chapters ship.

References for what's coming:

• Slack engineering blog: slack.engineering — real production architecture and scale content directly from the team
• Nystrom & Warnock (2019) — "How Slack Built Shared Channels": slack.engineering/how-slack-built-shared-channels — reference for cross-workspace routing at L7
• Slack's Nebula overlay network: github.com/slackhq/nebula — how Slack routes traffic between data centers
• Discord engineering blog: discord.com/blog/engineering — parallel product, published architecture at 100M+ concurrent connections
• WhatsApp: 900M users on ~50 servers (2015 talk by Rick Reed / Erlang factory conference) — how radical simplicity + Erlang scaled a messaging platform
• Chapter 5.5 of URL Shortener journey — database decision framework applies here too (spoiler: MySQL by default, Cassandra at L7 for write-heavy fanout)

Same discipline as URL Shortener Ch 1 — ask the questions FIRST, design SECOND. But because Slack is fundamentally different (persistent connections, real-time, presence, files, compliance), the 6 questions are different too.

I'm going to walk you through the 6 clarifying questions I would ask if I were interviewing for a Staff role at any FAANG right now. Each comes with the "why" — how the answer changes the architecture. Learn the "why" and you'll spot the same pattern in any real-time system.

Q1. What's the message-delivery guarantee — at-most-once, at-least-once, or exactly-once?

This is THE question. Get it wrong and every downstream decision is off.

At-most-once — send a message; if the recipient's WebSocket is disconnected, it's LOST. Simplest architecture: fire-and-forget WebSocket write. Used by: presence indicators ("Alice is typing"), volatile UI notifications.

At-least-once — guarantee delivery; recipient may see the same message twice on reconnect. Requires: message-ID uniqueness on client side, idempotency on backend. Used by: Slack, WhatsApp, Discord — the industry standard.

Exactly-once — guarantee delivery AND no duplicates. Requires: distributed consensus (Paxos/Raft), 2-phase commit, or client-side ACK protocol. Used by: financial transactions, NOT chat. Adding this to a chat app 5×'s the infra cost for no user-visible benefit.

The interview soundbite: "I'd design at-least-once with client-side dedup by message_id — the industry standard. Exactly-once is a red herring for chat."

Q2. What's the persistence duration — session-only, days, or forever?

Determines your storage tier + your compliance headache.

Slack's answer: forever, with enterprise-configurable retention. That's why they run petabyte-scale Solr + Elasticsearch for search.

Q3. What's the maximum team size?

Team size drives the fanout math. Every message fires N recipient events where N = channel member count.

• 50 members (small startup team) — fanout is trivial. Single WebSocket server can handle 1000+ teams.
• 500 members (mid-size company channel) — 10K events per message on a busy channel. Needs regional routing.
• 10K members ("all-hands" or #general in a giant company) — 100K events per message. Needs sharding + fan-out via Kafka.
• 100K members (Slack Connect, Discord public server) — 1M events per message. Needs a message relay tier + probabilistic delivery for typing indicators.

Slack's answer: 500K max per channel (Enterprise Grid), but 90% of channels are <50 members. Design for the common case, escape hatch for the rare huge channel.

Q4. Does search need to be real-time or eventual?

Search complexity multiplies with real-time requirements.

• Eventual (5-min lag) — batch indexer runs every 5 min against MySQL. Simple. Cheap. Users don't notice until they DO.
• Near-real-time (5-sec lag) — Kafka → Elasticsearch pipeline. 10× more complex, 5× cost.
• True real-time — write to MySQL AND Elasticsearch in same request. Doubles write latency. Elasticsearch outages block writes.

Slack picked eventual (~5-30 sec). It's the sane default. If your interviewer pushes for real-time search, ask "what's the user-visible SLO?" — 5 seconds is almost always fine.

Q5. Files — how big and how many per user?

File uploads dominate cost at Slack scale.

• Text-only — no upload path. Skip S3, skip antivirus, skip CDN for files. Cheap.
• Images + docs (max 10 MB) — S3 + CloudFront. Simple. $0.02/GB storage.
• Video calls / large files (max 1 GB) — needs pre-signed URLs, multipart upload, virus scan, transcoding pipeline. $0.30/GB egress.
• Slack's answer: 5 GB per file, unlimited uploads, kept forever. Files are ~40% of Slack's total infra cost.

Q6. What compliance regime — none, SOC 2, HIPAA, or FedRAMP?

Compliance changes your ENTIRE architecture, not just a checkbox.

• None (consumer app) — pick any cloud, any region, any encryption. Move fast.
• SOC 2 — audit trail on every access, encryption at rest, encryption in transit, SSO required. +30% eng time. Table-stakes for enterprise sales.
• HIPAA (US healthcare) — BAA with cloud provider, per-workspace encryption keys, dedicated infrastructure available. Slack HIPAA-eligible plan starts at $30K/yr per customer.
• FedRAMP (US government) — AWS GovCloud only, US citizen operators only, security clearances, air-gapped networks. Slack FedRAMP took 3 YEARS to certify. Six-figure floor per customer.

The interview soundbite: "I'd default to SOC 2 as table-stakes; HIPAA and FedRAMP are separate SKUs with dedicated cells — same code, different deployment."

The requirements summary

For our design, here's what we're going to assume (matches Slack Free/Standard tier — most common):

These 6 answers unlock the next 11 chapters. Chapter 2 (estimation) turns these requirements into RPS + storage math. Chapter 3 (API) codifies the delivery guarantee + search semantics into REST + WebSocket contracts. Chapters 5-9 progressively evolve the architecture across L4-L7.

Newbie insight: the 6 questions aren't universal — they're specific to real-time messaging. When you interview for a different problem (feed generation, video streaming, ride matching), you have to derive the 6 questions from scratch based on the problem's SHAPE. The skill is knowing to ASK, not knowing which questions.

Same funnel discipline as URL Shortener Ch 2. Different quantities.

Slack estimation has FIVE funnels, not one:
1. Concurrent WebSocket connections — the big number that scares junior engineers
2. Messages sent per second — what most people think Slack is about
3. Presence events per second — the hidden 100× multiplier
4. File bytes per day — the cost driver
5. Storage across tiers — the compliance driver

Get each funnel right and the architecture derives itself.

Assumptions we'll use

Let me anchor to Slack's published-ish numbers so this feels real. From their 2019 engineering blog + IPO S-1:
- ~12 million daily active users (DAU)
- Average DAU is connected ~10 hours per day
- Average DAU sends ~50 messages/day, receives ~500
- 30% of DAU are on mobile (~lower connection quality)

We'll design for 20M DAU to give ourselves 65% growth headroom.

Funnel 1 — Concurrent WebSocket connections

This is the number that dictates your entire connection tier.

```
20M DAU × (10 hrs connected / 24 hrs) = 8.3M concurrent connections (average)
× 1.5 peak factor
= 12.5M peak concurrent
```

Why this matters: each WebSocket connection holds a TCP socket + a small amount of server memory (~30KB with Erlang, ~50KB with Node.js, ~500KB with naive Java).

Boxes needed at 100K connections per WS server (Node.js production benchmark): 12.5M / 100K = 125 WebSocket servers minimum, plus HA headroom → ~180 boxes. Erlang at 2M connections/box gets you to ~6 boxes, which is why WhatsApp famously ran 900M users on ~50 servers.

Funnel 2 — Messages sent per second

```
20M DAU × 50 messages/day × 1/86400 = 11,574 messages/sec average
× 3× peak factor (12pm-2pm)
= ~35K messages/sec peak
```

That's the write rate. Now the FANOUT:

```
35K msgs/sec × ~10 recipients per message (avg channel size) = 350K delivery events/sec
× 3× typing/read/reaction ratio
= ~1M realtime events/sec
```

Every message is amplified 30× by the time it hits every recipient's screen with all its reactions and read receipts. This is why fanout dominates the architecture.

Funnel 3 — Presence events per second (the hidden multiplier)

Presence is the silent killer of naive designs.

• Users typing → send "X is typing" event every 500ms while active
• Users going online/offline → 4-6 events per DAU per day
• Users reading channels → mark-as-read event per channel per user

```
20M DAU × 100 typing events/day = 2B events/day
20M DAU × 5 status changes/day = 100M events/day
20M DAU × 20 channel-reads/day = 400M events/day
Total: ~2.5B presence events/day = ~29K presence events/sec (average)
× 3× peak = ~90K/sec
```

That's ~3× your message rate. And presence is USELESS if it's not real-time. This is where you need Redis + probabilistic dropping.

Funnel 4 — File bytes per day

The cost bomb. Slack's files are 40% of infra cost.

```
20M DAU × 20% upload files daily × avg 2 MB per file = 8 TB/day uploaded
× 365 days = 2.9 PB/year of NEW files
+ kept forever (compliance)
= 30 PB storage after 10 years
```

At S3 Standard ($0.023/GB/mo): $690K/mo just for file storage at 10-year mark. That's the number that drives you to tiered storage (S3 IA, Glacier).

Funnel 5 — Storage across tiers

Text messages (small), files (big), search indexes (bigger).

Interview soundbite: "At 20M DAU we're looking at 12.5M peak WebSockets, 35K msgs/sec with 30× fanout amplification, 90K presence events/sec, 8 TB/day file uploads. The presence tier alone justifies Redis Cluster; file storage tiering is where the CFO gets involved."

From estimation to architecture (preview)

The 5 funnels above force each tier of the architecture:

Cost bottom line (L6 at 20M DAU)

• Connection tier (180 WS servers × m5.large): ~$12K/mo
• Message tier (Kafka + Cassandra): ~$40K/mo
• Presence (Redis Cluster ×8): ~$3K/mo
• File storage (year 1): ~$50K/mo growing to ~$700K/mo in 10 years
• Search (Elasticsearch × 50 c5.2xlarge): ~$25K/mo
• Bandwidth egress: ~$60K/mo
• Total year 1: ~$190K/mo (~$2.3M/year infra)

At 12M paying DAU × $10/mo (average) = $120M ARR. Infra is 2% of revenue. Sustainable.

Newbie insight: the 5-funnel model is universal for messaging systems. WhatsApp, Discord, Teams, Signal all do this exact calculation. What differs is which funnel dominates: WhatsApp = messages, Discord = presence, Slack = files. Which funnel dominates decides your engineering priorities.

Slack has two protocols working together. Get this split right and everything else falls into place. Get it wrong and you'll be paying WebSocket costs for things HTTP would do better.

The two-protocol rule

REST/HTTP for:
- Authentication + session establishment
- Fetching history (messages before you connected)
- Uploading files (multipart, resumable)
- Search queries
- Any request that has an answer and returns

WebSocket for:
- Receiving real-time messages
- Sending new messages
- Presence + typing indicators
- Read receipts
- Reactions

The interview soundbite: "HTTP is for state-transfer; WebSocket is for state-changes. If it can be answered with a single response, use REST. If it's an event stream, use WebSocket."

The REST API surface (6 endpoints, that's it)

Slack's public API has hundreds of endpoints. The MVP needs 6:

1. POST /v1/auth/login Body: { email, password | oauth_token } Returns: { user_id, session_token, ws_url, initial_state }

The ws_url is the client's next stop. initial_state contains the user's team list + unread counts so the client can render before opening the WebSocket. This is the "TL;DR everything I need to know" endpoint.

2. GET /v1/channels/{channel_id}/history?before={message_id}&limit=50 Fetches historical messages. Cursor-based pagination via before message_id.

Why cursor and not offset? Because messages get inserted concurrently — an offset-based query at 50-99 will miss messages inserted during your scroll. Cursor-based (before/after specific message_id) is idempotent even under concurrent writes.

3. POST /v1/channels/{channel_id}/messages Body: { client_message_id, text, thread_parent_id? } Returns: { server_message_id, timestamp }

The critical detail: the client generates client_message_id (UUID) at compose time. Server accepts idempotently — if two requests arrive with same client_message_id, server returns the same server_message_id both times. This is how the client survives retry-storms on flaky mobile networks WITHOUT sending duplicate messages.

4. POST /v1/files/upload Multipart upload. Returns pre-signed S3 URL for large files (>10 MB) so the browser uploads directly to S3, not through our API tier.

5. GET /v1/search?q={query}&channel={channel_id}&limit=20 Full-text search backed by Elasticsearch. Returns highlighted snippets.

6. GET /v1/users/{user_id}/presence Explicit presence check. Rarely used — presence usually comes via WebSocket. This endpoint exists for "is X online?" polls in unusual UI states.

That's it. 6 endpoints for the MVP. Every other endpoint in Slack's real API is a specialization of one of these 6.

The WebSocket protocol

The WebSocket carries an event stream — a bidirectional flow of typed messages. Slack's actual production protocol (documented at api.slack.com/rtm) is a good model.

Frame format

Every WebSocket frame is a JSON blob with a type field:

json
{ "type": "message", "channel_id": "C123", "user_id": "U456", "text": "hi", "ts": "1234567890.001", "client_message_id": "uuid" }
{ "type": "presence", "user_id": "U456", "status": "online" }
{ "type": "typing", "channel_id": "C123", "user_id": "U456" }
{ "type": "reaction_added", "message_ts": "1234567890.001", "reaction": "thumbsup", "user_id": "U456" }
{ "type": "ack", "client_message_id": "uuid", "server_message_id": "M789" }
{ "type": "ping", "id": 42 }
{ "type": "pong", "id": 42 }

The 4-step message send

Every outgoing message flows through 4 states on the client:

1. compose — user hits Enter. Client generates client_message_id, adds message to UI as "sending" (grey).
2. send — client emits WebSocket { type: "message", client_message_id, text, channel_id } frame.
3. ack — server responds { type: "ack", client_message_id, server_message_id, timestamp }. Client updates the local message record with server_message_id + turns UI from grey to normal.
4. broadcast — server independently pushes { type: "message", server_message_id, ... } to ALL other connected users in that channel. Their clients render new message.

Why the ack/broadcast separation? The sending client already has the message in its UI from step 1. It just needs the server_message_id and timestamp. Other clients need the whole message. Separating these two events halves the bytes on the wire for busy channels.

Reconnect + catch-up

WebSockets are lossy — flaky WiFi, mobile network handoff, laptop lid closing. The client MUST expect disconnect and reconnect. The protocol handles this:

1. Client detects disconnect (missing ping/pong).
2. Client stores last_ack_timestamp (server timestamp of last message it received).
3. Client reconnects via new WebSocket.
4. First frame client sends: { type: "reconnect", last_ack_timestamp: "1234567890.500" }
5. Server replays all messages between last_ack_timestamp and now — as normal broadcast frames.
6. Client dedups by server_message_id.

This is why at-least-once + client-side dedup by server_message_id is the industry standard. Exactly-once would require distributed consensus over reconnects — 10× more complexity for no user-visible benefit.

Heartbeat + backpressure

Every 30 seconds the client sends { type: "ping", id: N }. Server responds with { type: "pong", id: N }. Missing 2 pongs → client tears down + reconnects.

When the server's send buffer for a client fills (slow client on bad network), server MUST drop the client. Slack's approach: drop the connection cleanly with a "reconnect_url" hint. Client reconnects to a less-loaded WebSocket server.

API decisions summary (with rationale)

The interview soundbite (60-sec version)

"REST for state-transfer, WebSocket for state-changes. 6 REST endpoints: auth, history, send-message, upload, search, presence. WebSocket protocol with typed frames — message, presence, typing, ack, ping, pong. Ack + broadcast separation reduces bandwidth on busy channels. Client generates message_id for idempotency; server dedups. Reconnect with last_ack_timestamp for catch-up. At-least-once delivery with client-side dedup is the industry standard — exactly-once is a red herring."

That's the whole contract in 90 seconds. Now Chapter 4 designs the data model that backs it.

The data model is where amateurs make Slack architecturally impossible. There are 5 core entities. Get the relationships right and Chapters 5-9 write themselves. Get them wrong and you spend 18 months migrating.

The 5 core entities

```
Team ─┬─ Users (members)
├─ Channels ──┬─ Messages ──┬─ Reactions
│ │ ├─ Threads (as messages with thread_parent_id)
│ │ └─ Files (message attachments)
│ └─ Membership (users in channel)
└─ Presence (user online/offline)
```

Table 1: teams

The Slack "workspace" — the top-level scoping entity.

sql
CREATE TABLE teams (
  team_id       UUID PRIMARY KEY,
  name          TEXT NOT NULL,
  domain        TEXT UNIQUE NOT NULL,       -- acme.slack.com
  plan          TEXT NOT NULL,              -- free / standard / plus / enterprise
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  region        TEXT NOT NULL,              -- us-east-1, eu-west-1, ap-south-1
  data_residency TEXT DEFAULT 'flexible',   -- flexible / us-only / eu-only (GDPR)
  retention_days INT DEFAULT NULL,          -- NULL = forever
  encryption_key_id UUID                    -- customer-managed key for HIPAA
);
CREATE INDEX idx_teams_domain ON teams (domain);

The one schema decision that matters most: region is a physical column, not a computed value. This is the sharding key at L6+. Every downstream table has a team_id FK and is sharded by the team's region. Cross-region queries are RARE and require going through a coordinator.

Table 2: users

sql
CREATE TABLE users (
  user_id       UUID PRIMARY KEY,
  team_id       UUID NOT NULL REFERENCES teams,
  email         TEXT NOT NULL,
  display_name  TEXT NOT NULL,
  status        TEXT DEFAULT 'active',       -- active / deactivated / deleted
  role          TEXT DEFAULT 'member',       -- owner / admin / member / guest
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  last_seen_at  TIMESTAMPTZ,
  UNIQUE (team_id, email)
);
CREATE INDEX idx_users_team ON users (team_id);

Note: user_id is globally unique, not per-team. Why? Because Slack Connect (cross-team DMs) needs a single global user identity. If user_id were team-scoped, cross-team messaging would need a translation layer forever.

Slack's actual gotcha: a user can be in multiple workspaces with the same email. That means the "primary key" for identity is (email × workspace_domain), NOT email alone. Slack Connect resolves this by upgrading email → global user_id at first sign-in and mapping the two workspaces to that global identity.

Table 3: channels

sql
CREATE TABLE channels (
  channel_id    UUID PRIMARY KEY,
  team_id       UUID NOT NULL REFERENCES teams,
  name          TEXT NOT NULL,
  channel_type  TEXT NOT NULL,               -- public / private / dm / mpdm
  topic         TEXT,
  purpose       TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  created_by    UUID REFERENCES users,
  archived      BOOLEAN DEFAULT FALSE,
  is_shared     BOOLEAN DEFAULT FALSE,       -- Slack Connect flag
  UNIQUE (team_id, name)
);
CREATE INDEX idx_channels_team ON channels (team_id) WHERE NOT archived;

Design decision — one table or four?

Public channels, private channels, DMs, group DMs are all "channels" architecturally — they hold messages, have members, get searched. Slack uses ONE table with a discriminator column. Trade-off: some code paths need to filter by channel_type; benefit: message-write path is identical for all four, simplifying the WebSocket protocol.

Table 4: memberships (the many-to-many)

sql
CREATE TABLE memberships (
  channel_id    UUID NOT NULL REFERENCES channels,
  user_id       UUID NOT NULL REFERENCES users,
  joined_at     TIMESTAMPTZ DEFAULT NOW(),
  last_read_ts  TIMESTAMPTZ,                 -- for unread badges
  notification_pref TEXT DEFAULT 'all',      -- all / mentions / muted
  PRIMARY KEY (channel_id, user_id)
);
CREATE INDEX idx_membership_user ON memberships (user_id);       -- "channels I'm in"
CREATE INDEX idx_membership_channel ON memberships (channel_id); -- "who's in this channel"

Two indexes on the same table — is that OK? For a read-heavy table like this, YES. Membership is read on every message send (to fan out) AND on every user login (to load channels). Missing either index is a 100× slowdown.

last_read_ts is a hot column — it updates on EVERY channel view. At scale you split it out into a channel_reads table or into Redis. But at L4/L5 keeping it inline saves a join.

Table 5: messages — where scale lives

sql
CREATE TABLE messages (
  message_id            UUID PRIMARY KEY,
  channel_id            UUID NOT NULL REFERENCES channels,
  team_id               UUID NOT NULL,            -- denormalized for sharding
  user_id               UUID NOT NULL REFERENCES users,
  client_message_id     UUID NOT NULL,            -- for idempotency dedup (Ch 3)
  text                  TEXT NOT NULL,
  ts                    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  edited_at             TIMESTAMPTZ,
  thread_parent_id      UUID REFERENCES messages, -- NULL = top-level, non-NULL = reply
  attachments_json      JSONB,                    -- files, links, formatted blocks
  UNIQUE (channel_id, client_message_id)
);

-- The critical index: messages by channel, ordered by time
CREATE INDEX idx_messages_channel_ts ON messages (channel_id, ts DESC);

-- Thread-lookup index
CREATE INDEX idx_messages_thread ON messages (thread_parent_id) WHERE thread_parent_id IS NOT NULL;

Every decision here matters:

1. `team_id` denormalized — the channel already has team_id. Why duplicate it in messages? Because at L6+ we shard by team_id, and joining channels every query is a shard-hop away. Denormalization = fewer shard hops.

1. `client_message_id` UNIQUE per channel — enforces idempotency at the DB level. Even if the app tier's dedup breaks, the DB refuses duplicate inserts.

1. `thread_parent_id` self-reference — threads are messages. Slack's schema treats a threaded reply as a message with a parent pointer. UI renders them differently, but the storage layer treats them identically.

1. `attachments_json` denormalized — files are looked up as-attached-to-messages 100× more often than as-standalone-files. JSON blob avoids the join.

1. The (channel_id, ts DESC) index is the WORKHORSE — it powers channel history load, unread scanning, search-in-channel. Get this wrong and every user complains that Slack is slow.

The reactions table (bonus)

sql
CREATE TABLE reactions (
  message_id    UUID NOT NULL REFERENCES messages,
  user_id       UUID NOT NULL REFERENCES users,
  reaction      TEXT NOT NULL,               -- ":thumbsup:", ":heart:", custom
  ts            TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (message_id, user_id, reaction)
);
CREATE INDEX idx_reactions_message ON reactions (message_id);

Denormalization opportunity: at L6+ you cache reaction counts on the message itself (reactions_summary_json) so the fan-out doesn't need a JOIN for every WebSocket broadcast.

Scale trade-offs across the 5 tables

The interview soundbite

"5 core entities: teams, users, channels, memberships, messages. Users are globally unique for Slack Connect. Channels are one table with a type discriminator. Memberships is the M:N junction with two indexes because both directions get queried on every message. Messages denormalize team_id for shard routing, threads are self-referential, reactions have a lightweight (message_id, user_id, reaction) primary key. At L6+ shard by team_id; hot vs. cold storage tier at L7."

Newbie insight: the temptation is to normalize everything (3NF or higher) at design time and denormalize later "when you need to." Wrong. You'll never migrate the schema at scale — DDL locks tables for hours. Design for denormalization from Day 1 in the columns you know will be hot. team_id in messages is the canonical example: never joinable in production because you'd hop shards.