Database design

The features that are kept in mind while making this database design are:

1. One-to-one messaging
2. Group messaging
3. Channels in group: A group will have multiple channels where messages can be sent. Users can use these separate channels to categorize their group chats.
4. Read receipts: Sent, delivered, and read.
5. Contacts: Users should be able to add each other in their contact list.
6. Text content: Currently only text content is handled.
7. Archive/unarchive, pin/unpin, clear and delete chats.

Note: Authentication is handled by a separate auth microservice. This document focuses on the messaging-system database schema for storing chat data and user profiles.

        
    erDiagram
        USER_PROFILES {
            int id
            string globalName
            string dp
            text bio
        }
        CONTACTS {
            int id
            int user_id
            int contact_user_id
            string alias
        }
        CHATS {
            int sender_id
            int receiver_id
            date cleared_at
            boolean archived
            boolean pinned
        }
        MESSAGES {
            int id
            string content
            int sender_id
            int channel_id
            date created_at
            date updated_at
        }
        MESSAGE_RECIPIENTS {
            int id
            int message_id
            string status
            int receiver_id
            date created_at
            date updated_at
        }
        GROUPS {
            int id
            string name
            int founder_id
        }
        USER_GROUP {
            int id
            int user_id
            int group_id
        }
        CHANNELS {
            int id
            string name
            int group_id
        }
        USER_PROFILES ||--o{ CONTACTS : has
        CONTACTS ||--|| USER_PROFILES : "contact_user_id"
        USER_PROFILES ||--o{ CHATS : sends_to
        USER_PROFILES ||--o{ CHATS : receives_from
        USER_PROFILES ||--o{ MESSAGES : sends
        MESSAGES ||--o{ MESSAGE_RECIPIENTS : contains
        MESSAGE_RECIPIENTS ||--|| MESSAGES : "message_id"
        MESSAGE_RECIPIENTS ||--|| USER_PROFILES : "receiver_id"
        USER_PROFILES ||--o{ GROUPS : creates
        GROUPS ||--o{ USER_GROUP : has_members
        USER_GROUP ||--|| USER_PROFILES : "user_id"
        USER_GROUP ||--|| GROUPS : "group_id"
        GROUPS ||--o{ CHANNELS : has_channels
        CHANNELS ||--|| GROUPS : "group_id"
        MESSAGES ||--|| CHANNELS : "channel_id"

      

• When a new user is created after sign-up in the auth-service, they are stored in the auth database. Once email verification is complete, the auth-service marks them as verified.
• Users can save other users to their contacts. For this we have the contacts table.
• Users can have "chats" with other users. To store which user has a "chat" with some other user, we have the chats table.
• Users will send and receive messages from other users. For this we have the messages and message_recipients tables.

The chats table in detail

The chats table is for managing one-to-one conversations with per-user settings:

interface Chat {
  sender_id: number // User initiating the chat
  receiver_id: number // User receiving the chat
  cleared_at: Date | null // When this user cleared the chat
  archived: boolean // User archived the chat
  pinned: boolean // User pinned the chat
}

We need two entries per conversation. This is because User A's settings for chat with User B are independent from User B's settings for chat with User A. User A might archive while User B views it actively.

The cleared_at Column: This timestamp is key for the "clear chat" feature. When a user clears their chat history, instead of deleting messages (which would affect the other user), we record the timestamp. Messages created after cleared_at are shown; messages before are hidden from this user only.

The message_recipients table in detail

interface MessageRecipient {
  id: number
  message_id: number // Foreign key to message
  receiver_id: number // User who should receive this
  status: 'sent' | 'delivered' | 'read' // Read receipt status
  created_at: Date // When the message was created
  updated_at: Date // When status was last updated
}

For a group message sent to 5 members, 5 message_recipient records are created - one per member. This allows:

• Tracking individual read receipts
• Storing when each user received the message
• Managing delivery status per recipient

Relationships Summary

The entity relationships for a normalized data design:

• Users create multiple Chats (as sender or receiver)
• Users can have multiple Contacts (with aliases)
• Users create Groups (as founder)
• Users join Groups via UserGroup junction table
• Groups contain multiple Channels
• Users send Messages to Channels
• Messages have multiple MessageRecipients (one per recipient)
• MessageRecipients track delivery status