Complete database schema for a Slack-like team communication platform featuring workspaces, channels, direct messages, threads, emoji reactions, file attachments, apps and integrations, user groups, and notification preferences.
Identity here is per workspace, not per account. users holds the login; workspace_members holds who you are inside one workspace — display_name_override and title live there, so the same person can be 'Kim' in one team and 'Dr. Kim, Platform' in another without a second account.
A thread is not a separate thing. messages.thread_id points at another message in the same table, so a reply is a message whose parent happens to be set. reply_count sits on the parent because a channel view needs to say '12 replies' without counting them.
Read state is a column on membership, not a table of receipts. channel_members.last_read_at and dm_members.last_read_at each hold one timestamp, which is all the unread badge needs — and it means marking a channel read is one update rather than a row per message.
A reply has everything a message has — an author, reactions, attachments, edits — so a separate table would duplicate all of it and then need a union on every channel read. One nullable thread_id costs a column and makes 'show this thread' an index lookup. The price is that queries must remember to filter thread_id is null for the channel view, which is exactly the bug people hit once and then never again.
message_reactions.workspace_id is redundant — it can be reached through the message and its channel. It is stored because reactions are the highest-volume table here and every workspace-scoped count or cleanup would otherwise join twice to find out where a row belongs. This is the kind of denormalisation worth making deliberately and writing down, because the copy can drift if a channel ever moves.
Storing a row per user per message answers 'who has read this' and costs a row per pair, which on a busy channel is the largest table in the system. A single last_read_at answers 'how many are unread', which is the question the interface actually asks. Slack-style products that later add receipts add them for DMs only, where the volume is bounded.
display_name_override and title on workspace_members exist because the same account appears in several workspaces and should not have to pick one name. Putting them on users would make a client's workspace see your employer's title. The cost is that rendering a message joins to membership, not just to users.