Data Engineering Path · Data Modelling
WHATSAPP CASE STUDY
Step 2: Entity Identification — WhatsApp
Detailed Entity Analysis
🔹 USERS — Registered phone-based accounts
Purpose: Store registered user accounts (phone-based identity)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| user_id | UUID (PK) | Internal unique identifier (not the phone number) |
| phone_number | VARCHAR(20), UNIQUE | E.164 format phone number (e.g. +919876543210) |
| country_code | VARCHAR(5) | Country dialing code (e.g. +91) |
| display_name | VARCHAR(25) | User-chosen display name |
| about | VARCHAR(139) | Bio / status text |
| profile_photo_url | VARCHAR(500) | S3/CDN URL for profile picture |
| last_seen_at | TIMESTAMP | Last active timestamp |
| is_online | BOOLEAN | Real-time presence flag |
| created_at | TIMESTAMP | Account registration time |
| updated_at | TIMESTAMP | Last profile update |
| is_active | BOOLEAN | Account active status (false = deactivated) |
Business Rules:
- Phone number is the login credential — must be globally unique
- Display name limited to 25 characters
- About text limited to 139 characters
user_id(UUID) is the true PK — phone numbers can change
🔹 USER_SETTINGS — Per-user privacy and notification preferences
Purpose: Per-user privacy controls and notification preferences
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| setting_id | INT (PK) | Auto-increment identifier |
| user_id | UUID (FK) | Owning user |
| last_seen_visibility | ENUM | everyone, my_contacts, my_contacts_except, nobody |
| profile_photo_visibility | ENUM | everyone, my_contacts, my_contacts_except, nobody |
| about_visibility | ENUM | everyone, my_contacts, my_contacts_except, nobody |
| status_visibility | ENUM | my_contacts, my_contacts_except, only_share_with |
| read_receipts_enabled | BOOLEAN | Show blue ticks to senders |
| default_disappearing_timer | ENUM | off, 24_hours, 7_days, 90_days |
| chat_backup_enabled | BOOLEAN | Auto cloud backup toggle |
| notification_tone | VARCHAR(100) | Custom notification sound name |
Business Rules:
- One settings row per user (1:1 relationship)
- Read receipts toggle applies globally (cannot be per-chat)
- Disappearing timer applies as default for new chats
🔹 USER_CONTACTS — Address book sync (who has whose number saved)
Purpose: Track whose phone number is saved in whose address book (determines who can see statuses, etc.)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| contact_id | BIGINT (PK) | Auto-increment identifier |
| owner_user_id | UUID (FK) | The user who has the contact saved |
| contact_user_id | UUID (FK) | The user whose number is saved |
| contact_name | VARCHAR(100) | Name as saved in address book |
| synced_at | TIMESTAMP | Last address book sync time |
Business Rules:
- Asymmetric relationship: A can have B's number saved without B having A's
- Used to enforce privacy settings ("My Contacts" visibility)
- Broadcast messages only delivered if recipient has sender's number saved
🔹 USER_BLOCKS — Block relationships
Purpose: Block relationships (prevents messages, calls, status views)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| block_id | INT (PK) | Auto-increment identifier |
| blocker_user_id | UUID (FK) | User who initiated the block |
| blocked_user_id | UUID (FK) | User who is blocked |
| blocked_at | TIMESTAMP | When the block was created |
Business Rules:
- Unidirectional: A blocks B, but B can still send messages (they just won't be delivered)
- Blocked users see single grey tick forever (cannot distinguish from "phone off")
- Blocked users cannot see last seen, profile photo, about, or status
- Unique constraint on
(blocker_user_id, blocked_user_id)
🔹 DEVICE_SESSIONS — Linked devices per user (multi-device support)
Purpose: Multi-device support — track linked companion devices
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| session_id | UUID (PK) | Unique device session identifier |
| user_id | UUID (FK) | Owning user |
| device_type | ENUM | primary_phone, web, desktop_windows, desktop_mac, tablet |
| device_name | VARCHAR(100) | Human-readable device label |
| identity_key | TEXT | Signal Protocol identity public key for this device |
| signed_pre_key | TEXT | Signed pre-key for X3DH handshake |
| registration_id | INT | Signal Protocol registration ID |
| last_active_at | TIMESTAMP | Last heartbeat from this device |
| linked_at | TIMESTAMP | When the device was linked |
| is_active | BOOLEAN | Whether session is still valid |
Business Rules:
- One primary phone + up to 4 companion devices
- Each device has its own encryption keys
- Inactive devices auto-unlinked after 14 days of no activity
🔹 CHATS — Conversation containers (1:1 or group)
Purpose: Conversation containers — represents either a 1:1 chat or a group chat
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| chat_id | BIGINT (PK) | Unique conversation identifier |
| chat_type | ENUM | private, group |
| group_name | VARCHAR(100) | Group name (NULL for private chats) |
| group_description | TEXT | Group description (NULL for private chats) |
| group_icon_url | VARCHAR(500) | Group profile icon URL |
| created_by | UUID (FK) | User who created the chat/group |
| created_at | TIMESTAMP | Chat creation timestamp |
| is_archived | BOOLEAN | Whether the chat is archived (per-user, but stored here for group metadata) |
Business Rules:
- For
privatechats: exactly 2 participants, no group name/icon - For
groupchats: 2-1024 participants, with name and optional icon - Private chats created lazily when first message is sent
created_byis the super-admin for group chats
🔹 GROUP_SETTINGS — Group-specific configuration
Purpose: Group-specific configuration (separate from chat metadata for clean separation)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| setting_id | INT (PK) | Auto-increment identifier |
| chat_id | BIGINT (FK) | The group chat this setting belongs to |
| who_can_send_messages | ENUM | all_members, admins_only |
| who_can_edit_group_info | ENUM | all_members, admins_only |
| approval_required_to_join | BOOLEAN | Whether admin approval is needed for invite-link joins |
| disappearing_messages_timer | ENUM | off, 24_hours, 7_days, 90_days |
| is_community_group | BOOLEAN | Whether this group belongs to a Community |
| community_id | BIGINT | Parent Community ID (NULL if standalone) |
Business Rules:
- 1:1 relationship with
chats(only forgrouptype) - Super-admin settings cannot be overridden by regular admins
- Community groups inherit some settings from the parent Community
🔹 GROUP_MEMBERS — Group membership with roles
Purpose: Track who is in which group, with roles
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| membership_id | BIGINT (PK) | Auto-increment identifier |
| chat_id | BIGINT (FK) | Group chat reference |
| user_id | UUID (FK) | Member user |
| role | ENUM | super_admin, admin, member |
| joined_at | TIMESTAMP | When the user joined the group |
| added_by | UUID (FK) | Who added this member (NULL if joined via link) |
| is_muted | BOOLEAN | Whether the member has muted this group |
| muted_until | TIMESTAMP | Mute expiry (NULL = muted indefinitely) |
Business Rules:
- Users can only see messages sent AFTER their
joined_attimestamp - Super-admin (group creator) cannot be removed by other admins
- Only admins can add/remove members (when configured)
- Unique constraint on
(chat_id, user_id)
🔹 GROUP_INVITE_LINKS — Shareable join links for groups
Purpose: Shareable URLs that allow outsiders to join a group
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| invite_id | INT (PK) | Auto-increment identifier |
| chat_id | BIGINT (FK) | Target group chat |
| invite_code | VARCHAR(50), UNIQUE | Unique token in the invite URL |
| created_by | UUID (FK) | Admin who generated the link |
| created_at | TIMESTAMP | Link creation time |
| expires_at | TIMESTAMP | Optional expiry timestamp |
| max_uses | INT | Maximum number of joins allowed (NULL = unlimited) |
| use_count | INT | Current number of joins via this link |
| is_revoked | BOOLEAN | Whether the link has been manually revoked |
Business Rules:
- Any admin can generate an invite link
- Any admin can revoke any invite link (generates a new one)
- Expired or fully-used links show "This invite link is no longer valid"
🔹 MESSAGES — Individual message records
Purpose: Individual message records within a chat
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| message_id | BIGINT (PK) | Global unique message identifier |
| chat_id | BIGINT (FK) | Conversation this message belongs to |
| sender_id | UUID (FK) | User who sent the message |
| message_type | ENUM | text, image, video, audio, document, contact, location, poll, sticker, system |
| encrypted_content | TEXT | E2EE encrypted message payload |
| reply_to_message_id | BIGINT (FK) | Quoted/replied message (NULL if not a reply) |
| forwarded_count | INT | Number of forward hops (0 = original, 5+ = "frequently forwarded") |
| is_edited | BOOLEAN | Whether the message has been edited |
| edited_at | TIMESTAMP | Timestamp of last edit |
| is_deleted | BOOLEAN | Whether "Delete for Everyone" was triggered |
| is_view_once | BOOLEAN | Whether media auto-deletes after first view |
| expires_at | TIMESTAMP | Auto-delete timestamp for disappearing messages (NULL if permanent) |
| sent_at | TIMESTAMP | Server-received timestamp |
| is_starred | BOOLEAN | Whether the sender has starred this message |
Business Rules:
- Messages can only be edited within 15 minutes of sending
- "Delete for Everyone" works within ~2 days of sending
- Forwarded messages retain the
forwarded_countchain - System messages (e.g. "Alice added Bob") have
message_type = 'system'
🔹 MESSAGE_STATUS — Per-recipient delivery tracking
Purpose: Per-recipient delivery and read tracking (the tick system)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| status_id | BIGINT (PK) | Auto-increment identifier |
| message_id | BIGINT (FK) | The message being tracked |
| recipient_id | UUID (FK) | The specific recipient |
| status | ENUM | sent, delivered, read |
| delivered_at | TIMESTAMP | When the message reached the recipient's device |
| read_at | TIMESTAMP | When the recipient opened the chat |
Business Rules:
- For 1:1 chats: exactly 1 status row per message
- For group chats: 1 status row per recipient per message
- The overall message status shown to sender = MIN(all recipients' statuses)
- If read receipts are disabled,
read_atstays NULL for that user's messages
🔹 MESSAGE_REACTIONS — Emoji reactions on messages
Purpose: Emoji reactions on messages (added in 2022)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| reaction_id | BIGINT (PK) | Auto-increment identifier |
| message_id | BIGINT (FK) | Message being reacted to |
| user_id | UUID (FK) | User who reacted |
| emoji | VARCHAR(10) | The reaction emoji (e.g. 👍, ❤️, 😂, 😮, 😢, 🙏) |
| reacted_at | TIMESTAMP | When the reaction was added |
Business Rules:
- One reaction per user per message (updating changes the emoji)
- Removing a reaction deletes the row
- Unique constraint on
(message_id, user_id)
🔹 MEDIA_ATTACHMENTS — Files, images, videos, audio linked to messages
Purpose: Files, images, videos, and audio linked to messages
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| attachment_id | BIGINT (PK) | Auto-increment identifier |
| message_id | BIGINT (FK) | Parent message |
| file_url | VARCHAR(500) | CDN/S3 URL for the encrypted file |
| thumbnail_url | VARCHAR(500) | Compressed preview thumbnail URL |
| file_type | ENUM | image, video, audio, document, sticker |
| mime_type | VARCHAR(50) | MIME type (e.g. image/jpeg, application/pdf) |
| file_size_bytes | BIGINT | File size in bytes |
| duration_seconds | INT | Duration for audio/video (NULL for images/docs) |
| width | INT | Image/video width in pixels |
| height | INT | Image/video height in pixels |
| file_name | VARCHAR(255) | Original file name for documents |
| caption | TEXT | Optional caption text |
| uploaded_at | TIMESTAMP | Upload timestamp |
Business Rules:
- Maximum file size: 2GB for documents, 16MB for images, 16MB for audio
- Videos up to 16MB (can be compressed further)
- Thumbnails are auto-generated server-side for images and videos
- View-once media is deleted from CDN after first download by recipient
🔹 CALLS — Voice/video call session records
Purpose: Voice and video call session records
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| call_id | BIGINT (PK) | Unique call session identifier |
| chat_id | BIGINT (FK) | Chat context where the call was initiated |
| caller_id | UUID (FK) | User who initiated the call |
| call_type | ENUM | voice, video |
| is_group_call | BOOLEAN | Whether this is a group call |
| status | ENUM | ringing, answered, missed, rejected, busy, failed |
| started_at | TIMESTAMP | Call initiation timestamp |
| answered_at | TIMESTAMP | When the call was picked up (NULL if missed) |
| ended_at | TIMESTAMP | Call termination timestamp |
| duration_seconds | INT | Call duration (0 if missed/rejected) |
Business Rules:
- Missed calls appear as a system message in the chat
- Group calls can have up to 32 participants
- Call duration calculated as
ended_at - answered_at
🔹 CALL_PARTICIPANTS — Per-participant call tracking
Purpose: Track individual participants in group calls
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| participant_id | BIGINT (PK) | Auto-increment identifier |
| call_id | BIGINT (FK) | Parent call session |
| user_id | UUID (FK) | Participant user |
| joined_at | TIMESTAMP | When the participant joined |
| left_at | TIMESTAMP | When the participant left (NULL if still in call) |
| status | ENUM | joined, missed, rejected, left |
Business Rules:
- For 1:1 calls: exactly 2 participants (caller + callee)
- For group calls: 2-32 participants
- Participants can leave and rejoin during a group call
🔹 STATUS_UPDATES — 24-hour ephemeral Stories
Purpose: 24-hour ephemeral Stories (text, image, or video)
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| status_id | BIGINT (PK) | Unique status identifier |
| user_id | UUID (FK) | User who posted the status |
| status_type | ENUM | text, image, video |
| content | TEXT | Text content or caption |
| media_url | VARCHAR(500) | CDN URL for image/video (NULL for text-only) |
| background_color | VARCHAR(7) | Hex color for text statuses (e.g. #1DA1F2) |
| font_style | VARCHAR(20) | Text font choice |
| privacy_setting | ENUM | all_contacts, selected_contacts, except_contacts |
| posted_at | TIMESTAMP | Creation timestamp |
| expires_at | TIMESTAMP | Auto-expiry (posted_at + 24 hours) |
| view_count | INT | Denormalized viewer count |
Business Rules:
- Statuses auto-expire after 24 hours (enforced by
expires_at) - Videos limited to 30 seconds
- Privacy setting controls who can view (cross-referenced with
user_contacts) - Only contacts who have the poster's number saved can view statuses
🔹 STATUS_VIEWERS — Who viewed each status
Purpose: Track who viewed each status update
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| viewer_id | BIGINT (PK) | Auto-increment identifier |
| status_id | BIGINT (FK) | The status that was viewed |
| viewer_user_id | UUID (FK) | The user who viewed it |
| viewed_at | TIMESTAMP | When the view occurred |
Business Rules:
- One row per viewer per status (no duplicate views tracked)
- Unique constraint on
(status_id, viewer_user_id) - Viewer list visible only to the status poster
- Muted contacts' statuses still generate view rows if opened manually
🔹 BROADCAST_LISTS — One-to-many broadcast containers
Purpose: One-to-many messaging where recipients see individual 1:1 messages
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| broadcast_id | BIGINT (PK) | Unique broadcast list identifier |
| owner_id | UUID (FK) | User who created the broadcast list |
| name | VARCHAR(100) | Broadcast list name (visible only to the owner) |
| created_at | TIMESTAMP | Creation timestamp |
| updated_at | TIMESTAMP | Last modification |
Business Rules:
- Only the owner can see the broadcast list — recipients see individual 1:1 messages
- Messages only delivered to recipients who have the sender's number saved
- Broadcast lists can have up to 256 recipients
🔹 BROADCAST_RECIPIENTS — Members of a broadcast list
Purpose: Members of a broadcast list
Attributes:
| Attribute | Data Type | Description |
|---|---|---|
| recipient_id | BIGINT (PK) | Auto-increment identifier |
| broadcast_id | BIGINT (FK) | Parent broadcast list |
| user_id | UUID (FK) | Recipient user |
| added_at | TIMESTAMP | When the contact was added to the list |
Business Rules:
- Unique constraint on
(broadcast_id, user_id) - Only contacts who have the sender's number saved will receive messages
- Removing a recipient does not delete past messages
Entity Summary
| Entity | Type | Purpose |
|---|---|---|
| users | Core | Phone-based user identity |
| user_settings | Supporting | Privacy and notification preferences |
| user_contacts | Supporting | Address book relationships |
| user_blocks | Supporting | Block enforcement |
| device_sessions | Supporting | Multi-device E2EE key management |
| chats | Core | Conversation containers (1:1 and group) |
| group_settings | Supporting | Group configuration |
| group_members | Junction | Group membership with roles |
| group_invite_links | Supporting | Shareable group join URLs |
| messages | Core | Individual message records |
| message_status | Junction | Per-recipient delivery tracking |
| message_reactions | Junction | Emoji reactions |
| media_attachments | Supporting | Files, images, videos |
| calls | Core | Voice/video call sessions |
| call_participants | Junction | Per-participant call tracking |
| status_updates | Core | 24-hour Stories |
| status_viewers | Junction | Status view tracking |
| broadcast_lists | Core | One-to-many broadcast containers |
| broadcast_recipients | Junction | Broadcast list members |
Key Design Decisions
1. UUID for user_id (not phone number)
Phone numbers can be changed or recycled by carriers. Using a UUID as the internal PK ensures referential integrity even when the phone number column is updated.
2. Separate user_settings table
Settings change frequently and have many columns. Separating from the users table keeps the hot users table lean for joins and lookups.
3. message_status as a junction table
This is the highest-volume table in the entire system. A 1000-member group message creates 1000 rows. Needs aggressive partitioning by message_id and TTL-based cleanup.
4. chats as a unified container
Both 1:1 and group conversations share the same chats table with a chat_type discriminator. This simplifies message queries — all messages belong to a chat_id regardless of type.
arrow_back Previous Topic
Problem Statement Analysis
Next Topic arrow_forward
Relationships Explained