Database Design: Conversations Over Chat Rooms

While group chats are a common feature in messaging apps, the core of Facebook-style messaging revolves around direct conversations – private dialogues between two individuals. Our database schema, defined using Prisma, is designed to efficiently handle these direct conversations and prioritize:

• Efficient message retrieval: Quickly fetching messages for a given conversation.
• Read status tracking: Keeping track of which users have read which messages.
• Scalable participant management: Managing users participating in conversations in an efficient way.

Here is our Prisma schema definition:

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url       = env("DATABASE_URL")
}

// User model definition
model User {
  id             String                  @id @default(uuid()) 
  email          String                  @unique 
  username       String                  @unique 
  passwordHash   String 
  sessions       Session[] 
  conversations  ConversationParticipant[] 
  messages       Message[] 
  lastOnline     DateTime? 
  createdAt      DateTime                @default(now()) 

  @@index([email]) 
  @@index([username]) 
}

// Conversation model definition
model Conversation {
  id             String                  @id @default(uuid())
  participants   ConversationParticipant[] 
  messages       Message[] 
  createdAt      DateTime                @default(now()) 
  updatedAt      DateTime                @updatedAt automatically updated

  @@index([createdAt]) 
}

// ConversationParticipant model definition (Junction table for many-to-many relationship between User and Conversation)
model ConversationParticipant {
  id             String                  @id @default(uuid()) 
  user           User                    @relation(fields: [userId], references: [id]) 
  userId         String 
  conversation   Conversation            @relation(fields: [conversationId], references: [id]) 
  conversationId String 
  hasUnread      Boolean                 @default(false) 
  lastReadAt     DateTime? 
  createdAt      DateTime                @default(now()) 

  @@index([userId]) 
  @@index([conversationId]) 
  @@index([userId, conversationId], map: "user_conversation_unique_constraint", unique: true) 
}

// Message model definition
model Message {
  id             String                  @id @default(uuid())
  content        String
  sender         User                    @relation(fields: [senderId], references: [id])
  senderId       String
  conversation   Conversation            @relation(fields: [conversationId], references: [id])
  conversationId String
  readBy         User[]                  @relation("MessageReads")
  createdAt      DateTime                @default(now())
  updatedAt      DateTime                @updatedAt
  isEdited       Boolean                 @default(false)

  @@index([conversationId])
  @@index([senderId])
  @@index([createdAt])
}

Key Design Decisions

1. Conversation Participants

   • Junction Table ConversationParticipant: We use a separate model, ConversationParticipant, to act as a junction table. This model is crucial for managing the many-to-many relationship between User and Conversation. Each record in ConversationParticipant links a User to a Conversation and stores participant-specific data.

   • Tracking Read Status (lastReadAt) and Unread Counts (hasUnread): This table is the perfect place to store read status information on a per-participant basis. lastReadAt stores the timestamp of when a participant last read messages in the conversation, enabling "last seen" functionality. hasUnread is a boolean flag that efficiently indicates if a participant has unread messages, simplifying queries for unread message counts and notifications.

   • Efficient Conversation Queries: The ConversationParticipant model enables efficient querying of all conversations a user is involved in. By querying ConversationParticipant with WHERE userId = :userId, we can easily retrieve all conversations for a specific user, along with their participant-specific read statuses and unread flags. This is much more efficient than trying to query conversations and then filter based on participants each time.

   Example Scenario: Imagine User A and User B are in a conversation.

   • There will be one record in the Conversation table representing their conversation.
   • There will be two records in the ConversationParticipant table:
     • One linking User A to the Conversation, storing User A's lastReadAt and hasUnread status for this specific conversation.
     • Another linking User B to the same Conversation, storing User B's lastReadAt and hasUnread status for this specific conversation.

2. Message Read Tracking

   • Many-to-Many readBy Relation: The Message model has a many-to-many relationship with the User model, named readBy. This relation, defined using @relation("MessageReads"), tracks which users have read a specific message. This allows us to easily determine who has seen a particular message in group conversations (if we were to extend to that feature later) or in direct messages.

   • lastReadAt in ConversationParticipant for "Last Seen" Optimization: While readBy tracks read status per message, the lastReadAt field in ConversationParticipant is strategically used to optimize queries for "last seen" or "last message read" functionality in a conversation. Instead of needing to iterate through all messages to find the last read one, we can directly access lastReadAt from the ConversationParticipant record for a user in a specific conversation. This significantly speeds up these common types of queries.

Core Architecture

1. REST API Structure

We will use Express to define RESTful APIs for managing core messaging functionalities. REST APIs are ideal for operations that are not real-time and follow a request-response pattern, such as sending, retrieving, editing, and deleting messages, as well as managing conversations.

# Messaging Endpoints
POST     /api/messages                # Send a new message to a conversation
GET      /api/messages                # Retrieve a list of messages within a specific conversation (with pagination)
PATCH    /api/messages/:id            # Edit an existing message identified by its ID
DELETE   /api/messages/:id            # Delete a message identified by its ID

# Conversation Endpoints
GET      /api/conversations             # Retrieve a list of conversations for the currently logged-in user
POST     /api/conversations             # Start a new conversation with another user
GET      /api/conversations/:id         # Get metadata for a specific conversation, such as participants and timestamps

2. WebSocket Events

For real-time features like message delivery, read receipts, typing indicators, and online status, we will use WebSockets and Socket.io.

WebSockets provide persistent, bidirectional communication channels between the server and clients, allowing for instant updates in both directions.

interface ServerToClientEvents {
  'message:received': (message: Message) => void; // Event for when a new message is received by a client
  'message:edited': (payload: { messageId: string; newContent: string }) => void; // Event for when a message is edited
  'message:deleted': (messageId: string) => void; // Event for when a message is deleted
  'typing:start': (userId: string) => void; // Event to indicate a user has started typing
  'typing:stop': (userId: string) => void; // Event to indicate a user has stopped typing
  'presence:online': (userId: string) => void; // Event to indicate a user has come online
  'presence:offline': (userId: string) => void; // Event to indicate a user has gone offline
  'messages:read': (payload: { userId: string, messageIds: string[] }) => void; // Event to notify clients that messages have been read by a user
}

interface ClientToServerEvents {
  'message:send': (content: string, conversationId: string) => void;
  'message:edit': (messageId: string, newContent: string) => void;
  'message:delete': (messageId: string) => void;
  'typing:start': (conversationId: string) => void;
  'typing:stop': (conversationId: string) => void;
}

Implementation Deep Dive

Let's dive into the code implementation for some of the core features of our messaging backend.

1. Starting a Conversation

Goal: To create a new direct conversation between two users if one doesn't already exist.

API Endpoint: POST /api/conversations

// controllers/conversationController.ts
import { Request, Response } from 'express';
import { prisma } from '../prisma'; // Assuming you have Prisma client setup


export const createConversation = async (req: Request, res: Response) => {

  // Extract the ID of the other user from the request body.
  const { otherUserId } = req.body;
  // Extract the ID of the current user from the request object (assuming user authentication middleware is in place).
  const userId = req.user!.id; // 'req.user' is populated by your authentication middleware

  // -- Check for Existing Conversation --
  // Query the database to find if a conversation already exists between these two users.
  const existing = await prisma.conversation.findFirst({
    where: {
      participants: { // Filter participants in the conversation
        every: { userId: { in: [otherUserId, userId] } } // Check if both user IDs are present among participants
      }
    }
  });

  // If a conversation already exists, return a 409 Conflict status with an error message.
  if (existing) {
    return res.status(409).json({ error: 'Conversation already exists' });
  }

  // -- Create New Conversation --
  // If no existing conversation is found, create a new one.
  const conversation = await prisma.conversation.create({
    data: {
      participants: { // Create participant records linked to the new conversation
        createMany: { // Efficiently create multiple records
                      // You could use a transaction if you like
          data: [otherUserId, userId].map(id => ({ userId: id })) // Map both user IDs to participant data objects
        }
      }
    },
    include: { participants: true } // Include participant data in the response for confirmation
  });

  // Respond with a 201 Created status and the newly created conversation object.
  res.status(201).json(conversation);
};

2. Real-Time Message Delivery

Goal: To send messages in real-time to all participants of a conversation using WebSockets when a user sends a message.

WebSocket Handler:

// services/socketService.ts
import { Server, Socket } from 'socket.io';
import { prisma } from '../prisma'; // Assuming Prisma client setup
import Redis from 'ioredis';

// Initialize Redis client
const redisClient = new Redis(); // You might need to configure this with your Redis connection details

// Initialize Socket.IO server instance (assuming 'io' is defined elsewhere, e.g., in your main server file)
export const setupSocketService = (io: Server) => {



  // Handle new client connections to the WebSocket server
  io.on('connection', (socket: Socket) => {

    // **Authentication & User Identification**:
    // In a production-grade application, user_id should be securely obtained from the session
    // For simplicity, just pretend that 'socket.data.user.id' is populated correctly after authentication.
    const userId = socket.data.user.id;

    // **Join User-Specific Room**:
    socket.join(`user_${userId}`);


    // -- 'message:send' Event Handler --
    socket.on('message:send', async (content: string, conversationId: string) => {
      const isParticipant = await prisma.conversationParticipant.findFirst({
        where: { userId, conversationId }
      });

      if (!isParticipant) {
        return socket.emit('error', 'Failed to send the message');
      }

      const message = await prisma.message.create({
        data: {
          content: content,
          senderId: userId,
          conversationId,
          readBy: { connect: { id: userId } }
        },
        include: { sender: true }
      });

      const participants = await prisma.conversationParticipant.findMany({
        where: { conversationId, userId: { not: userId } }
      });

      participants.forEach(({ userId }) => {
        io.to(`user_${userId}`).emit('message:received', {
          ...message,
        });
      });
    });

    // -- Typing Indicator Handlers (Typing Start) --
    socket.on('typing:start', async (conversationId) => {
      const typingUsersKey = `typingUsers:conversation_${conversationId}`;

      // Check if the user is already typing (in Redis) to avoid resetting timer unnecessarily
      const isAlreadyTyping = await redisClient.sismember(typingUsersKey, userId);

      if (!isAlreadyTyping) {
        // Add user to typing users set in Redis
        await redisClient.sadd(typingUsersKey, userId);

        // Broadcast 'typing:start' event to all participants in the conversation room
        socket.to(`conversation_${conversationId}`).emit('typing:start', userId);
      }

      // Set an expiration for the user in the typing users set (e.g., 3 seconds - slightly more than debounce)
      await redisClient.expire(typingUsersKey, 3); // Reset expiration timer, effectively extending typing status

    });

    // -- Typing Indicator Handlers (Typing Stop) --
    socket.on('typing:stop', async (conversationId) => {
      const typingUsersKey = `typingUsers:conversation_${conversationId}`;

      // Remove user from typing users set in Redis
      await redisClient.srem(typingUsersKey, userId);

      socket.to(`conversation_${conversationId}`).emit('typing:stop', userId);
    });


    // -- 'conversation:open' Event Handler --
    socket.on('conversation:open', async (conversationId) => {
      const unreadMessages = await prisma.message.findMany({
        where: {
          conversationId,
          NOT: {
            readBy: {
              some: {
                id: userId
              }
            }
          }
        },
        select: { id: true }
      });

      const messageIds = unreadMessages.map(msg => msg.id);

      if (messageIds.length > 0) {
          await prisma.$transaction([
              prisma.message.updateMany({
                  where: { id: { in: messageIds } },
                  data: { readBy: { connect: { id: userId } } }
              }),
              prisma.conversationParticipant.update({
                  where: { userId_conversationId: { userId, conversationId } },
                  data: { lastReadAt: new Date(), hasUnread: false }
              })
          ]);

          socket.to(`conversation_${conversationId}`).emit('messages:read', {
              userId,
              messageIds
          });
      }
    });


    socket.on('disconnect', () => {
      console.log(`User disconnected: ${userId}`);
    });
  });
};

3. Read Receipts Implementation

Goal: To implement read receipts, informing message senders when their messages have been read by recipients, and updating the UI in real-time.

Marking Messages as Read:

// controllers/messageController.ts
import { Request, Response } from 'express';
import { prisma } from '../prisma'; // Assuming Prisma client setup


export const markAsRead = async (req: Request, res: Response) => {
  // Extract the array of message IDs from the request body.
  const { messageIds } = req.body;
  // Extract the current user's ID from the request (assuming authentication middleware).
  const userId = req.user!.id; // 'req.user' is populated by your authentication middleware

  // Use Prisma's $transaction to ensure atomicity of database operations.
  await prisma.$transaction([
    // -- Update Messages Read Status --
    // Update all messages with IDs in 'messageIds' to mark them as read by the current user.
    prisma.message.updateMany({
      where: { id: { in: messageIds } }, // Filter messages by IDs provided in the request
      data: { readBy: { connect: { id: userId } } } // Connect the current user to the 'readBy' relation, marking them as read
    }),

    // -- Update Conversation Participant Status --
    // Update the ConversationParticipant record for the current user in the relevant conversation.
    prisma.conversationParticipant.updateMany({
      where: { userId }, // Filter to find the participant record for the current user
      data: { lastReadAt: new Date(), hasUnread: false } // Update 'lastReadAt' to current time and set 'hasUnread' to false
    })
  ]);

  // Respond with a 204 No Content status to indicate successful processing with no response body needed.
  res.sendStatus(204);
};

Performance Optimization

To ensure our messaging backend can handle a large number of users and messages efficiently, we need to implement several performance optimization strategies.

1. Message Pagination with Cursors

Goal: To efficiently retrieve and display message history in chunks, especially for conversations with a large number of messages, preventing overwhelming the client and server.

// controllers/messageController.ts
import { Request, Response } from 'express';
import { prisma } from '../prisma'; // Assuming Prisma client setup

export const getMessages = async (req: Request, res: Response) => {

  // Extract conversationId and cursor from the request query parameters.
  // cursor will be explained in future articles
  const { conversationId, cursor } = req.query;
  // Define the limit for the number of messages to fetch per page.
  const LIMIT = 50;

  // -- Fetch Messages from Database with Pagination --
  // Use Prisma's 'findMany' to query messages with pagination.
  const messages = await prisma.message.findMany({
    where: { conversationId: conversationId as string }, // Filter messages by conversation ID
    take: LIMIT, // Limit the number of messages fetched to 'LIMIT'
    cursor: cursor ? { id: cursor as string } : undefined, // Use cursor for pagination, if provided; otherwise, start from the latest
    skip: cursor ? 1 : 0, // Skip 1 record when using cursor to avoid duplicates (cursor points to the last item of the previous page)
    orderBy: { createdAt: 'desc' }, // Order messages by creation time in descending order (newest first)
    include: { sender: true } // Include sender information in the message objects
  });

  // -- Determine Next Cursor --
  // Check if we fetched 'LIMIT' number of messages, which indicates there might be more pages available.
  const nextCursor = messages.length === LIMIT
    ? messages[messages.length - 1].id // If fetched limit, set 'nextCursor' to the ID of the last message, indicating next page start
    : null; // If fetched less than limit, no more pages available, set 'nextCursor' to null

  // Respond with a JSON object containing the message data and the 'nextCursor'.
  res.json({
    data: messages, // Array of message objects for the current page
    nextCursor: nextCursor // Cursor for fetching the next page of messages (or null if no more pages)
  });
};

2. Presence Tracking with Redis Sorted Sets

Goal: To efficiently track user online status and retrieve a list of online users using Redis, a fast in-memory data store, optimized for real-time operations.

// services/presenceService.ts
import Redis from 'ioredis'; // Import the ioredis library for Redis interaction

// Define the threshold in seconds to consider a user online (e.g., last activity within 5 minutes).
const ONLINE_THRESHOLD = 300; // 5 minutes in seconds

/**
 * Marks a user as online in Redis by adding/updating their user ID in a sorted set.
 * The score in the sorted set is the timestamp of their last online activity.
 */
export const markOnline = async (userId: string) => {
  // Create a new Redis client instance (ensure Redis connection URL is configured via environment variable).
  const redis = new Redis(process.env.REDIS_URL!);
  // Use Redis sorted set 'zadd' command to add/update the user ID with the current timestamp as the score.
  await redis.zadd('online_users', Date.now(), userId);
};

/**
 * Retrieves a list of user IDs who are currently online based on their last activity timestamp in Redis.
 * @returns An array of user IDs who are considered online.
 */
export const getOnlineUsers = async () => {
  // Create a new Redis client instance.
  const redis = new Redis(process.env.REDIS_URL!);
  // Get the current timestamp to calculate the online threshold.
  const now = Date.now();
  // Use Redis sorted set 'zrangebyscore' command to fetch user IDs whose last activity timestamp is within the online threshold.
  const users = await redis.zrangebyscore(
    'online_users', // Sorted set key for online users
    now - ONLINE_THRESHOLD * 1000, // Minimum score (timestamp): current time minus online threshold (milliseconds)
    now // Maximum score (timestamp): current time
  );
  return users; // Return an array of user IDs considered online.
};

Conclusion

Congratulations! You've successfully architected a robust and scalable messaging backend that incorporates key features of modern messaging applications like Facebook Messenger. This backend is capable of handling:

• Real-time bidirectional communication: Using WebSockets for instant message delivery, typing indicators, and read receipts.
• Efficient and end-to-end encrypted message storage: Persisting messages securely in a PostgreSQL database, designed for efficient retrieval and read status tracking.
• Scalable presence tracking: Utilizing Redis for fast and efficient tracking of user online status.
• Production-grade security: Implementing security best practices such as UUIDs for primary keys and considering production deployment checklists for database optimization, WebSocket scaling, monitoring, and CI/CD.

Next Steps: To further enhance this messaging backend and build upon this foundation, consider implementing the following advanced features:

1. Add Message Reactions: Extend message functionality by adding support for message reactions (like 👍, ❤️, 😂) using polymorphic relations in your database schema. Polymorphic relations allow a reaction to be associated with different types of entities (e.g., messages, comments) in a flexible way.

2. Implement File Attachments with S3 Presigned URLs: Enable users to send and receive files by integrating file upload and download functionality using S3 (Amazon Simple Storage Service) pre-signed URLs. Presigned URLs provide secure and temporary access to S3 objects, allowing clients to directly upload files to S3 and download them without passing through your server for data transfer, improving scalability and security.

3. Build Message Synchronization for Offline-First Experiences: Implement robust message synchronization mechanisms to support offline-first experiences. This involves strategies for handling message sending and receiving when users are offline and ensuring seamless synchronization when they come back online. Techniques like optimistic updates, conflict resolution, and reliable background synchronization processes would be key to building a true offline-first messaging experience.

Final Reminder: Building truly reliable and scalable systems is an iterative process that requires continuous learning, testing, and refinement. Always remember that true reliability comes from anticipating failure. Embrace chaos engineering to proactively identify weaknesses in your system, diligently monitor client-side and server-side metrics to gain insights into performance and user experience, and always design your system with the principles of handling network partitions and potential failures in mind. This proactive and failure-aware approach is essential for building robust, resilient, and user-friendly messaging applications.

This article was written by Ahmad Adel. Ahmad is a freelance writer and also a backend developer.