Complete database schema for a WhatsApp-like real-time messaging platform featuring user accounts, contact management, group messaging with member roles, private and group conversations, message delivery status tracking, emoji reactions, rich media attachments, ephemeral stories with view tracking, audio and video call logs, multi-device support, and user blocking functionality.
The identity is a phone number, and that single choice explains several tables. contacts maps an owner to another user with a private nickname, because the name you see is the one you saved, not the one they chose. devices exists because the same number signs in from a phone and a laptop at once, each with its own push token.
A group and its conversation are separate rows. groups holds the name, icon and invite link with group_members as the roster; conversations holds the thread, with type telling a direct chat from a group one and a nullable group_id linking the two. Renaming a group therefore touches nothing that messages depend on.
message_status is one row per message per recipient, which is how the tick marks work. Delivered and read are different timestamps on that row rather than two flags on the message, because in a group of fifty the message was delivered to some people and read by fewer — a single column on the message could not hold either answer.
is_delivered and is_read on messages would be free to store and wrong for every group. Status is per recipient, so it belongs on a table keyed by message and user. The cost is the fastest-growing table in the product — messages times participants — which is why receipts are usually aged out long before messages are.
The chat list shows the latest message for every thread, and deriving that is a top-one lookup per conversation on every open. Pointing at the message directly makes the list a join instead of many subqueries. It must be updated on each send and on deletion of the latest message, which is the write that keeps this column honest.
is_deleted keeps the row so replies that quote it, reactions on it and receipts for it still have a target. It also preserves the position in the thread, which is what lets a client show that something was removed rather than silently closing the gap. True erasure means blanking the body and removing the media row, not deleting the message.
A scheduled job that deletes expired stories would make visibility depend on the job having run. Storing the expiry means every read filters on it and a story is invisible the instant it lapses, whether or not cleanup has happened yet. The actual deletion then becomes a storage task with no correctness pressure on it.