// ============================================ // WhatsApp Data Model — Complete DBML Schema // 19 Entities | FAANG Interview Level // ============================================

// ---- ENUMS ----

Enum last_seen_visibility { everyone my_contacts my_contacts_except nobody }

Enum chat_type { private group }

Enum member_role { super_admin admin member }

Enum message_type { text image video audio document contact location poll sticker system }

Enum delivery_status { sent delivered read }

Enum call_type { voice video }

Enum call_status { ringing answered missed rejected busy failed }

Enum call_participant_status { joined missed rejected left }

Enum status_type { text image video }

Enum status_privacy { all_contacts selected_contacts except_contacts }

Enum disappearing_timer { off "24_hours" "7_days" "90_days" }

Enum device_type { primary_phone web desktop_windows desktop_mac tablet }

Enum file_type { image video audio document sticker }

Enum group_message_permission { all_members admins_only }

// ---- TABLES ----

Table users { user_id uuid [pk, note: 'Internal UUID — NOT the phone number'] phone_number varchar(20) [unique, not null, note: 'E.164 format (e.g. +919876543210)'] country_code varchar(5) [not null] display_name varchar(25) [not null] about varchar(139) [default: 'Hey there! I am using WhatsApp.'] profile_photo_url varchar(500) last_seen_at timestamp is_online boolean [default: false] created_at timestamp [default: now()] updated_at timestamp is_active boolean [default: true]

Note: 'Core user identity. Phone number is login credential but UUID is the internal PK to handle number changes.' }

Table user_settings { setting_id int [pk, increment] user_id uuid [ref: - users.user_id, unique, not null] last_seen_visibility last_seen_visibility [default: 'everyone'] profile_photo_visibility last_seen_visibility [default: 'everyone'] about_visibility last_seen_visibility [default: 'everyone'] status_visibility status_privacy [default: 'all_contacts'] read_receipts_enabled boolean [default: true] default_disappearing_timer disappearing_timer [default: 'off'] chat_backup_enabled boolean [default: false] notification_tone varchar(100) [default: 'default']

Note: 'One row per user. Controls privacy and notification preferences.' }

Table user_contacts { contact_id bigint [pk, increment] owner_user_id uuid [ref: > users.user_id, not null] contact_user_id uuid [ref: > users.user_id, not null] contact_name varchar(100) synced_at timestamp [default: now()]

indexes { (owner_user_id, contact_user_id) [unique] }

Note: 'Address book sync. Asymmetric — A having B saved does not mean B has A saved.' }

Table user_blocks { block_id int [pk, increment] blocker_user_id uuid [ref: > users.user_id, not null] blocked_user_id uuid [ref: > users.user_id, not null] blocked_at timestamp [default: now()]

indexes { (blocker_user_id, blocked_user_id) [unique] }

Note: 'Unidirectional blocks. Blocked user sees single grey tick forever.' }

Table device_sessions { session_id uuid [pk] user_id uuid [ref: > users.user_id, not null] device_type device_type [not null] device_name varchar(100) identity_key text [not null, note: 'Signal Protocol identity public key'] signed_pre_key text [not null, note: 'Signed pre-key for X3DH handshake'] registration_id int [not null] last_active_at timestamp linked_at timestamp [default: now()] is_active boolean [default: true]

Note: 'Multi-device E2EE support. Each device has its own key pair. Max 5 devices per user.' }

Table chats { chat_id bigint [pk, increment] chat_type chat_type [not null] group_name varchar(100) [note: 'NULL for private chats'] group_description text group_icon_url varchar(500) created_by uuid [ref: > users.user_id] created_at timestamp [default: now()]

Note: 'Unified conversation container for both 1:1 and group chats.' }

Table group_settings { setting_id int [pk, increment] chat_id bigint [ref: - chats.chat_id, unique, not null] who_can_send_messages group_message_permission [default: 'all_members'] who_can_edit_group_info group_message_permission [default: 'all_members'] approval_required_to_join boolean [default: false] disappearing_messages_timer disappearing_timer [default: 'off'] is_community_group boolean [default: false] community_id bigint

Note: 'Configuration for group chats only. 1:1 with chats table.' }

Table group_members { membership_id bigint [pk, increment] chat_id bigint [ref: > chats.chat_id, not null] user_id uuid [ref: > users.user_id, not null] role member_role [default: 'member'] joined_at timestamp [default: now()] added_by uuid [ref: > users.user_id] is_muted boolean [default: false] muted_until timestamp

indexes { (chat_id, user_id) [unique] }

Note: 'Group membership with roles. Users only see messages after their joined_at.' }

Table group_invite_links { invite_id int [pk, increment] chat_id bigint [ref: > chats.chat_id, not null] invite_code varchar(50) [unique, not null] created_by uuid [ref: > users.user_id, not null] created_at timestamp [default: now()] expires_at timestamp max_uses int use_count int [default: 0] is_revoked boolean [default: false]

Note: 'Shareable group join links with optional expiry and usage limits.' }

Table messages { message_id bigint [pk, increment] chat_id bigint [ref: > chats.chat_id, not null] sender_id uuid [ref: > users.user_id, not null] message_type message_type [not null] encrypted_content text [note: 'E2EE payload — server cannot read'] reply_to_message_id bigint [ref: > messages.message_id] forwarded_count int [default: 0] is_edited boolean [default: false] edited_at timestamp is_deleted boolean [default: false] is_view_once boolean [default: false] expires_at timestamp [note: 'For disappearing messages'] sent_at timestamp [default: now()]

indexes { (chat_id, sent_at) }

Note: 'Core message record. Self-referencing for reply threading.' }

Table message_status { status_id bigint [pk, increment] message_id bigint [ref: > messages.message_id, not null] recipient_id uuid [ref: > users.user_id, not null] status delivery_status [default: 'sent'] delivered_at timestamp read_at timestamp

indexes { (message_id, recipient_id) [unique] (recipient_id, status) }

Note: 'Per-recipient delivery tracking. Highest-volume table — 1 row per recipient per message.' }

Table message_reactions { reaction_id bigint [pk, increment] message_id bigint [ref: > messages.message_id, not null] user_id uuid [ref: > users.user_id, not null] emoji varchar(10) [not null] reacted_at timestamp [default: now()]

indexes { (message_id, user_id) [unique] }

Note: 'One reaction per user per message. Updating changes the emoji.' }

Table media_attachments { attachment_id bigint [pk, increment] message_id bigint [ref: > messages.message_id, not null] file_url varchar(500) [not null] thumbnail_url varchar(500) file_type file_type [not null] mime_type varchar(50) file_size_bytes bigint duration_seconds int [note: 'For audio/video only'] width int height int file_name varchar(255) [note: 'Original filename for documents'] caption text uploaded_at timestamp [default: now()]

Note: 'Media files attached to messages. Stored encrypted on CDN.' }

Table calls { call_id bigint [pk, increment] chat_id bigint [ref: > chats.chat_id, not null] caller_id uuid [ref: > users.user_id, not null] call_type call_type [not null] is_group_call boolean [default: false] status call_status [not null] started_at timestamp [default: now()] answered_at timestamp ended_at timestamp duration_seconds int [default: 0]

Note: 'Voice and video call sessions. Group calls support up to 32 participants.' }

Table call_participants { participant_id bigint [pk, increment] call_id bigint [ref: > calls.call_id, not null] user_id uuid [ref: > users.user_id, not null] joined_at timestamp left_at timestamp status call_participant_status [not null]

Note: 'Per-participant tracking for calls. 2 rows for 1:1, up to 32 for group calls.' }

Table status_updates { status_id bigint [pk, increment] user_id uuid [ref: > users.user_id, not null] status_type status_type [not null] content text media_url varchar(500) background_color varchar(7) [note: 'Hex color for text statuses'] font_style varchar(20) privacy_setting status_privacy [default: 'all_contacts'] posted_at timestamp [default: now()] expires_at timestamp [not null, note: 'posted_at + 24 hours'] view_count int [default: 0]

indexes { (user_id, posted_at) (expires_at) }

Note: '24-hour ephemeral Stories. Auto-expire based on expires_at.' }

Table status_viewers { viewer_id bigint [pk, increment] status_id bigint [ref: > status_updates.status_id, not null] viewer_user_id uuid [ref: > users.user_id, not null] viewed_at timestamp [default: now()]

indexes { (status_id, viewer_user_id) [unique] }

Note: 'Track who viewed each status. Only visible to the poster.' }

Table broadcast_lists { broadcast_id bigint [pk, increment] owner_id uuid [ref: > users.user_id, not null] name varchar(100) [not null] created_at timestamp [default: now()] updated_at timestamp

Note: 'One-to-many broadcast. Recipients see individual 1:1 messages.' }

Table broadcast_recipients { recipient_id bigint [pk, increment] broadcast_id bigint [ref: > broadcast_lists.broadcast_id, not null] user_id uuid [ref: > users.user_id, not null] added_at timestamp [default: now()]

indexes { (broadcast_id, user_id) [unique] }

Note: 'Max 256 recipients per list. Only delivers if recipient has sender saved.' }

// ---- TABLE GROUPS ----

TableGroup user_domain { users user_settings user_contacts user_blocks device_sessions }

TableGroup messaging_domain { chats group_settings group_members group_invite_links messages message_status message_reactions media_attachments }

TableGroup calling_domain { calls call_participants }

TableGroup ephemeral_domain { status_updates status_viewers broadcast_lists broadcast_recipients }

This content is reserved for Premium Members.