WhatsApp Clone
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.
messagingchatrealtimewhatsapp
MySQL10 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique user identifier
Unique international phone number used for registration and login
User display name shown in chats and contact lists
Short status message visible to contacts
URL to the user profile picture
Timestamp of the user last online activity
Whether the user is currently online and connected
Whether the account is active and not deactivated
Timestamp when the account was registered
Timestamp when the account was last updated
IXUQCK|💬
MySQL5 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique contact record identifier
FK to users: the user who saved this contact
FK to users: the user saved as a contact
Custom name given to this contact by the owner
Timestamp when the contact was saved
IXUQCK|💬
MySQL8 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique group identifier
Display name of the group shown to all members
Optional group description visible to all members
URL to the group icon image
FK to users: the user who created the group
Unique shareable link that allows joining the group without admin approval
Timestamp when the group was created
Timestamp when the group info was last modified
IXUQCK|💬
MySQL5 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique group membership record identifier
FK to groups: the group this user belongs to
FK to users: the group member
Whether the member has admin privileges to manage the group
Timestamp when the user joined the group
IXUQCK|💬
MySQL6 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique conversation identifier
Type of conversation: private (1-to-1) or group
FK to groups: linked group chat, null for private conversations
ID of the most recent message for conversation list preview
Timestamp when the conversation was first created
Timestamp of the most recent activity in the conversation
IXUQCK|💬
MySQL6 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique participant record identifier
FK to conversations: the conversation this user participates in
FK to users: the participant user
Whether notifications from this conversation are muted
Timestamp of the last message this user read in the conversation
Timestamp when the user joined the conversation
IXUQCK|💬
MySQL9 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique message identifier
FK to conversations: the conversation this message belongs to
FK to users: the user who sent the message
FK to messages: the message being replied to for threaded context
Type of message content: text, image, video, audio, document, or location
Text content of the message, nullable for media-only messages
Whether the sender has deleted this message for everyone
Whether the message was forwarded from another conversation
Timestamp when the message was sent by the user
IXUQCK|💬
MySQL6 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique message status record identifier
FK to messages: the message this status belongs to
FK to users: the recipient user
Delivery status: sent (server received), delivered (device received), or read
Timestamp when the message was delivered to the recipient device
Timestamp when the recipient opened and read the message
IXUQCK|💬
MySQL5 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique message reaction record identifier
FK to messages: the message that received the reaction
FK to users: the user who reacted to the message
Unicode emoji character used as the reaction
Timestamp when the reaction was added to the message
IXUQCK|💬
MySQL9 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique media attachment identifier
FK to messages: the message this media is attached to
Type of media: image, video, audio, document, sticker, or gif
URL to the media file stored in object storage
MIME type of the media file for client rendering decisions
File size in bytes for display and quota enforcement
Duration in seconds for audio and video files
URL to a preview thumbnail for video and document files
Timestamp when the media was uploaded
IXUQCK|💬
MySQL8 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique story identifier
FK to users: the user who posted this story
URL to the story image or video content
Type of story media: image or video
Optional text caption displayed over the story
Denormalized count of unique viewers who watched the story
Timestamp when the story expires and is no longer visible (24h after creation)
Timestamp when the story was posted
IXUQCK|💬
MySQL4 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique story view record identifier
FK to stories: the story that was viewed
FK to users: the contact who viewed the story
Timestamp when the story was viewed by the contact
IXUQCK|💬
MySQL4 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique block record identifier
FK to users: the user who initiated the block
FK to users: the user who has been blocked
Timestamp when the user was blocked
IXUQCK|💬
MySQL8 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, globally unique call log identifier
FK to users: the user who initiated the call
Type of call: voice or video
Call outcome status: completed, missed, declined, or failed
Total duration of the call in seconds
Timestamp when the call was answered and began
Timestamp when the call ended
Timestamp when the call was initiated
IXUQCK|💬
MySQL5 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique call participation record identifier
FK to call_logs: the call this participant is linked to
FK to users: the user who was a participant in the call
Participation status: joined, declined, or missed
Timestamp when the participant joined the call
IXUQCK|💬
MySQL7 cols
Num
PK
AI
FK
Null
Physical Name
Type
Default
Comment
Primary key, unique device registration identifier
FK to users: the user this device belongs to
Platform of the device: ios, android, or web
Human-readable device model name for the linked devices list
Push notification token for delivering messages to this device
Timestamp when the user last used the app on this device
Timestamp when the device was registered
IXUQCK|💬
Press enter or space to select a node. You can then use the arrow keys to move the node around. Press delete to remove it and escape to cancel.
Press enter or space to select an edge. You can then press delete to remove it or escape to cancel.
Created with ERD Studio
Tables in this template
usersUsers
10 colscontactsContacts
5 colsgroupsGroups
8 colsgroup_membersGroup Members
5 colsconversationsConversations
6 colsconversation_participantsConversation Participants
6 colsmessagesMessages
9 colsmessage_statusMessage Status
6 colsmessage_reactionsMessage Reactions
5 colsmessage_mediaMessage Media
9 colsstoriesStories
8 colsstory_viewsStory Views
4 colsblocked_usersBlocked Users
4 colscall_logsCall Logs
8 colscall_participantsCall Participants
5 colsdevicesDevices
7 colsRelated Templates
View all templates →GitHub
CloneCode hosting platform with repositories, issues, pull requests, and CI/CD
PostgreSQL16 tables
developer-toolsgitcollaboration+2
Web App
Netflix
CloneVideo streaming platform with content catalog, profiles, subscriptions, and recommendations
PostgreSQL15 tables
streamingvideosubscription+2
Web App
Spotify
CloneMusic streaming platform with tracks, artists, albums, playlists, and social features
PostgreSQL18 tables
musicstreamingplaylist+2
Web App
Discord
CloneCommunity platform with servers, channels, roles, voice, and bots
PostgreSQL15 tables
gamingcommunityvoice+2
Web App