A comprehensive messaging and chat database schema for building applications like Slack, WhatsApp, or Discord. Supports one-on-one and group conversations with member management, real-time message delivery with read receipts, and file attachments. Includes conversation types, member roles, message status tracking, and media sharing capabilities.
A direct message and a group use the same tables. conversations has a type column and a nullable name, and both kinds hold their participants in conversation_members — so there is no separate one-to-one path to maintain, and promoting a DM into a group is a change of type plus an inserted member.
Unread state is answered twice because it is two different questions. conversation_members.last_read_at is one timestamp per person per conversation, which is all a badge count needs. read_receipts stores a row per message per reader, which is what shows who has seen a specific message — far more rows, so it exists beside the cheap answer rather than replacing it.
Messages are never physically removed. is_deleted and is_edited are flags on the row, so a deleted message leaves its id intact for every reply_to_id, reaction and pin that points at it. Deleting the row instead would break threads and silently drop reactions that no longer have a target.
Separate tables would make a direct message cheaper — two user ids and no membership rows. They would also duplicate messages, attachments, reactions and pins across two paths forever, and every feature would have to be built twice. One table with a type column costs two member rows for a DM and makes everything downstream singular.
A receipt per message per reader is the complete answer and grows as members times messages. A badge count does not need that: comparing last_read_at to the message timestamps answers it from one row. Keeping both means the cheap question stays cheap and the expensive one is still possible, with the cost that two places record reading and can disagree.
is_deleted keeps the row so replies, reactions and pins keep a valid target, and so a moderator can still see what was removed. The consequence is that every read path must filter it, and a message that is truly gone — a legal erasure — needs the content column blanked rather than the row dropped.
The conversation list sorts by recent activity, and deriving that from a max over messages for every conversation on every open is the slowest query in a chat app. The column is updated on each send. It can drift if a write fails midway, which is why it is treated as a sort key rather than as the record of when the last message actually arrived.