Section 11: Projects

💬 Real-Time Chat Application Project

Build a Scalable MongoDB-Powered Messaging Platform

🎯 Project Overview

Welcome to the Real-Time Chat Application project! In this comprehensive hands-on tutorial, you'll build a production-ready messaging system similar to Slack, WhatsApp, or Discord. You'll implement user authentication, one-on-one messaging, group chats, file sharing, typing indicators, read receipts, and powerful search functionality.

💡 What Makes This Project Unique?

This project teaches you how to handle real-time messaging data, implement efficient message pagination, manage user presence states, and build complex aggregation pipelines for chat analytics. You'll learn patterns used by billion-user messaging platforms.

Collections

25+

Features

70+

Queries

Real-Time

Messaging

📖 Meet ChatFlow: Your Messaging Platform

The Challenge: Alex is building ChatFlow, a modern messaging platform for teams and communities. The system needs to support instant messaging between users, create and manage group conversations, handle file attachments, provide message search across conversations, track read receipts and typing indicators, and scale to millions of messages without performance degradation.

Your Mission: As the database architect, you'll design a MongoDB backend that supports:

✅ User registration and profile management
✅ One-on-one direct messaging
✅ Group chat creation and administration
✅ Message reactions and threading
✅ File attachments and media sharing
✅ Message search with full-text indexes
✅ Read receipts and delivery status
✅ User presence and typing indicators

🎓 Learning Objectives

By completing this project, you will master:

💬 Message Modeling

Design efficient schemas for scalable message storage and retrieval

🔍 Text Search

Implement full-text search across messages with weighted scoring

📄 Pagination

Master cursor-based pagination for infinite scroll chat history

📊 Aggregation Pipelines

Build complex queries for chat analytics and user insights

⚡ Query Optimization

Create compound indexes for fast message retrieval

🔐 Access Control

Implement user permissions for private and group chats

📐 Database Schema Design

Our chat application consists of 6 interconnected collections:

👥 users - User Profiles

Store user information, authentication, and presence data

_id ObjectId
username String (unique, indexed)
email String (unique, indexed)
displayName String
avatar String (URL)
status String (online/away/busy/offline)
lastSeen Date (indexed)
bio String
passwordHash String
createdAt Date
isActive Boolean

💬 conversations - Chat Conversations

Manage both direct messages and group chats

_id ObjectId
type String (direct/group) (indexed)
name String (for groups)
description String (for groups)
avatar String (for groups)
participants Array [ObjectId] (indexed)
createdBy ObjectId (reference)
admins Array [ObjectId] (for groups)
lastMessage Object {text, senderId, timestamp}
createdAt Date
isActive Boolean

✉️ messages - Chat Messages

Store all messages with delivery tracking

_id ObjectId
conversationId ObjectId (reference, indexed)
senderId ObjectId (reference, indexed)
content String (text indexed)
messageType String (text/image/file/video)
attachments Array [{url, filename, size, type}]
replyTo ObjectId (reference to message)
reactions Array [{userId, emoji}]
readBy Array [{userId, readAt}]
deliveredTo Array [{userId, deliveredAt}]
isEdited Boolean
editedAt Date
isDeleted Boolean
createdAt Date (indexed)

📁 files - Shared Files & Media

Track file uploads and metadata

_id ObjectId
filename String
originalName String
mimeType String
size Number (bytes)
url String
uploadedBy ObjectId (reference)
conversationId ObjectId (reference, indexed)
messageId ObjectId (reference)
uploadedAt Date

⌨️ typingIndicators - Real-Time Typing Status

Track who is currently typing in conversations

_id ObjectId
conversationId ObjectId (reference, indexed)
userId ObjectId (reference)
isTyping Boolean
lastUpdated Date (TTL indexed)

🔔 notifications - User Notifications

Manage unread message notifications

_id ObjectId
userId ObjectId (reference, indexed)
conversationId ObjectId (reference)
messageId ObjectId (reference)
type String (message/mention/invite)
isRead Boolean (indexed)
createdAt Date

🎯 Key Design Decisions:

Conversation-centric: Both DMs and groups use same conversation collection
Embedded receipts: Read/delivered status embedded in messages for atomic updates
Text indexing: Full-text search on message content for powerful search
TTL for typing: Auto-cleanup of stale typing indicators using TTL index
Separate files: File metadata tracked separately for analytics and quotas

🔧 Initial Setup

1Create the Database

Initialize ChatFlow Database

use chatFlow

// Verify creation
db.getName()
// Output: "chatFlow"

2Create Collections

db.createCollection("users")
db.createCollection("conversations")
db.createCollection("messages")
db.createCollection("files")
db.createCollection("typingIndicators")
db.createCollection("notifications")

3Create Text Search Indexes

Critical: Full-text search for messages

// Message content search (most important!)
db.messages.createIndex({ content: "text" })

// User search by name and username
db.users.createIndex({ 
  username: "text", 
  displayName: "text" 
})

4Create Performance Indexes

// Users
db.users.createIndex({ username: 1 }, { unique: true })
db.users.createIndex({ email: 1 }, { unique: true })
db.users.createIndex({ lastSeen: -1 })
db.users.createIndex({ status: 1 })

// Conversations
db.conversations.createIndex({ participants: 1 })
db.conversations.createIndex({ type: 1 })
db.conversations.createIndex({ "lastMessage.timestamp": -1 })

// Messages (compound index for efficient queries)
db.messages.createIndex({ conversationId: 1, createdAt: -1 })
db.messages.createIndex({ senderId: 1, createdAt: -1 })
db.messages.createIndex({ conversationId: 1, isDeleted: 1, createdAt: -1 })

// Files
db.files.createIndex({ conversationId: 1 })
db.files.createIndex({ uploadedBy: 1 })
db.files.createIndex({ uploadedAt: -1 })

// Typing indicators (TTL index - auto-delete after 10 seconds)
db.typingIndicators.createIndex({ conversationId: 1 })
db.typingIndicators.createIndex({ lastUpdated: 1 }, { expireAfterSeconds: 10 })

// Notifications
db.notifications.createIndex({ userId: 1, isRead: 1 })
db.notifications.createIndex({ userId: 1, createdAt: -1 })

✅ Setup Complete!

Your ChatFlow database is now ready with all collections, text search indexes, and performance optimizations configured!

👥 Phase 1: User Management

Let's create users who will interact in our chat system.

Register Users

Create User Accounts

db.users.insertMany([
  {
    username: "alice_dev",
    email: "alice@chatflow.com",
    displayName: "Alice Johnson",
    avatar: "https://i.pravatar.cc/150?img=1",
    status: "online",
    lastSeen: new Date(),
    bio: "Software engineer passionate about building scalable systems",
    passwordHash: "$2b$10$abcdefghijklmnopqrstuvwxyz123456",  // Hashed password
    createdAt: new Date(),
    isActive: true
  },
  {
    username: "bob_designer",
    email: "bob@chatflow.com",
    displayName: "Bob Smith",
    avatar: "https://i.pravatar.cc/150?img=2",
    status: "online",
    lastSeen: new Date(),
    bio: "UI/UX designer creating beautiful experiences",
    passwordHash: "$2b$10$zyxwvutsrqponmlkjihgfedcba654321",
    createdAt: new Date(),
    isActive: true
  },
  {
    username: "carol_pm",
    email: "carol@chatflow.com",
    displayName: "Carol Williams",
    avatar: "https://i.pravatar.cc/150?img=3",
    status: "away",
    lastSeen: new Date(Date.now() - 15 * 60000),  // 15 minutes ago
    bio: "Product manager driving innovation",
    passwordHash: "$2b$10$1234567890abcdefghijklmnopqrstuvw",
    createdAt: new Date(),
    isActive: true
  },
  {
    username: "david_ops",
    email: "david@chatflow.com",
    displayName: "David Brown",
    avatar: "https://i.pravatar.cc/150?img=4",
    status: "busy",
    lastSeen: new Date(),
    bio: "DevOps engineer keeping systems running smoothly",
    passwordHash: "$2b$10$wxyz9876543210fedcbaabcdefghijklm",
    createdAt: new Date(),
    isActive: true
  },
  {
    username: "emma_data",
    email: "emma@chatflow.com",
    displayName: "Emma Davis",
    avatar: "https://i.pravatar.cc/150?img=5",
    status: "offline",
    lastSeen: new Date(Date.now() - 2 * 60 * 60000),  // 2 hours ago
    bio: "Data scientist uncovering insights",
    passwordHash: "$2b$10$nopqrstuvwxyz123456789abcdefghij",
    createdAt: new Date(),
    isActive: true
  }
])

Search for Users

// Find user by username
db.users.findOne({ username: "alice_dev" })

// Search users by name
db.users.find({
  $text: { $search: "alice" }
})

// Get online users
db.users.find({ 
  status: "online",
  isActive: true 
})

Update User Status and Presence

// Update user status to away
db.users.updateOne(
  { username: "alice_dev" },
  { 
    $set: { 
      status: "away",
      lastSeen: new Date()
    } 
  }
)

// User goes offline
db.users.updateOne(
  { username: "bob_designer" },
  { 
    $set: { 
      status: "offline",
      lastSeen: new Date()
    } 
  }
)

// Update user profile
db.users.updateOne(
  { username: "alice_dev" },
  { 
    $set: { 
      bio: "Full-stack developer building amazing apps",
      avatar: "https://i.pravatar.cc/150?img=10"
    } 
  }
)

Get Recently Active Users

// Users active in last 30 minutes
var thirtyMinsAgo = new Date(Date.now() - 30 * 60000)

db.users.find({
  lastSeen: { $gte: thirtyMinsAgo }
}).sort({ lastSeen: -1 })

💬 Phase 2: Direct Messaging

Implement one-on-one conversations between users.

Create Direct Message Conversation

Start DM Between Two Users

var alice = db.users.findOne({ username: "alice_dev" })
var bob = db.users.findOne({ username: "bob_designer" })

db.conversations.insertOne({
  type: "direct",
  participants: [alice._id, bob._id],
  createdBy: alice._id,
  lastMessage: null,
  createdAt: new Date(),
  isActive: true
})

var conversation = db.conversations.findOne({ 
  type: "direct",
  participants: { $all: [alice._id, bob._id] }
})

Send Messages

Exchange Messages in DM

// Alice sends first message
db.messages.insertOne({
  conversationId: conversation._id,
  senderId: alice._id,
  content: "Hey Bob! How's the new design coming along?",
  messageType: "text",
  attachments: [],
  replyTo: null,
  reactions: [],
  readBy: [{ userId: alice._id, readAt: new Date() }],
  deliveredTo: [{ userId: bob._id, deliveredAt: new Date() }],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

var msg1 = db.messages.findOne({ content: /new design/ })

// Update conversation with last message
db.conversations.updateOne(
  { _id: conversation._id },
  {
    $set: {
      lastMessage: {
        text: "Hey Bob! How's the new design coming along?",
        senderId: alice._id,
        timestamp: new Date()
      }
    }
  }
)

// Bob replies
db.messages.insertOne({
  conversationId: conversation._id,
  senderId: bob._id,
  content: "It's going great! I'm working on the final mockups. Want to see?",
  messageType: "text",
  attachments: [],
  replyTo: msg1._id,  // Reply to Alice's message
  reactions: [],
  readBy: [{ userId: bob._id, readAt: new Date() }],
  deliveredTo: [{ userId: alice._id, deliveredAt: new Date() }],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

// Update last message
db.conversations.updateOne(
  { _id: conversation._id },
  {
    $set: {
      lastMessage: {
        text: "It's going great! I'm working on the final mockups. Want to see?",
        senderId: bob._id,
        timestamp: new Date()
      }
    }
  }
)

Mark Messages as Read

// Alice reads Bob's message
db.messages.updateOne(
  { 
    conversationId: conversation._id,
    senderId: bob._id,
    "readBy.userId": { $ne: alice._id }
  },
  {
    $push: {
      readBy: {
        userId: alice._id,
        readAt: new Date()
      }
    }
  }
)

Add Message Reactions

// Alice reacts with thumbs up
db.messages.updateOne(
  { _id: msg1._id },
  {
    $push: {
      reactions: {
        userId: bob._id,
        emoji: "👍"
      }
    }
  }
)

// Add multiple reactions
db.messages.updateOne(
  { _id: msg1._id },
  {
    $push: {
      reactions: {
        $each: [
          { userId: alice._id, emoji: "❤️" },
          { userId: bob._id, emoji: "🎉" }
        ]
      }
    }
  }
)

Edit and Delete Messages

// Edit a message
db.messages.updateOne(
  { 
    _id: msg1._id,
    senderId: alice._id  // Only sender can edit
  },
  {
    $set: {
      content: "Hey Bob! How's the new design project coming along?",
      isEdited: true,
      editedAt: new Date()
    }
  }
)

// Soft delete a message
db.messages.updateOne(
  { 
    _id: msg1._id,
    senderId: alice._id  // Only sender can delete
  },
  {
    $set: {
      isDeleted: true,
      content: "This message was deleted"
    }
  }
)

Get Conversation Messages with Pagination

Load Chat History (Most Recent First)

// Get last 20 messages
db.messages.find({
  conversationId: conversation._id,
  isDeleted: false
})
.sort({ createdAt: -1 })
.limit(20)

// Cursor-based pagination - load older messages
var lastMessageId = ObjectId("...")  // ID of oldest message shown

db.messages.find({
  conversationId: conversation._id,
  isDeleted: false,
  _id: { $lt: lastMessageId }
})
.sort({ createdAt: -1 })
.limit(20)

👥 Phase 3: Group Chats

Create and manage group conversations with multiple participants.

Create Group Conversation

Start a Group Chat

var alice = db.users.findOne({ username: "alice_dev" })
var bob = db.users.findOne({ username: "bob_designer" })
var carol = db.users.findOne({ username: "carol_pm" })
var david = db.users.findOne({ username: "david_ops" })

db.conversations.insertOne({
  type: "group",
  name: "Project Alpha Team",
  description: "Main chat for Project Alpha development team",
  avatar: "https://i.pravatar.cc/150?img=group1",
  participants: [alice._id, bob._id, carol._id, david._id],
  createdBy: alice._id,
  admins: [alice._id, carol._id],  // Alice and Carol are admins
  lastMessage: null,
  createdAt: new Date(),
  isActive: true
})

var groupChat = db.conversations.findOne({ name: "Project Alpha Team" })

Send Messages in Group

// Carol sends announcement
db.messages.insertOne({
  conversationId: groupChat._id,
  senderId: carol._id,
  content: "Team! We have our kick-off meeting tomorrow at 10 AM. Please review the project brief.",
  messageType: "text",
  attachments: [],
  replyTo: null,
  reactions: [],
  readBy: [{ userId: carol._id, readAt: new Date() }],
  deliveredTo: [
    { userId: alice._id, deliveredAt: new Date() },
    { userId: bob._id, deliveredAt: new Date() },
    { userId: david._id, deliveredAt: new Date() }
  ],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

// Update last message
db.conversations.updateOne(
  { _id: groupChat._id },
  {
    $set: {
      lastMessage: {
        text: "Team! We have our kick-off meeting tomorrow at 10 AM...",
        senderId: carol._id,
        timestamp: new Date()
      }
    }
  }
)

// Bob responds
db.messages.insertOne({
  conversationId: groupChat._id,
  senderId: bob._id,
  content: "Sounds good! I'll have the design mockups ready to present.",
  messageType: "text",
  attachments: [],
  replyTo: null,
  reactions: [],
  readBy: [{ userId: bob._id, readAt: new Date() }],
  deliveredTo: [
    { userId: alice._id, deliveredAt: new Date() },
    { userId: carol._id, deliveredAt: new Date() },
    { userId: david._id, deliveredAt: new Date() }
  ],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

Add Members to Group

var emma = db.users.findOne({ username: "emma_data" })

// Admin adds new member
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    admins: alice._id  // Verify admin permission
  },
  {
    $push: { participants: emma._id }
  }
)

// Send system message about new member
db.messages.insertOne({
  conversationId: groupChat._id,
  senderId: alice._id,
  content: "Emma Davis joined the group",
  messageType: "system",
  attachments: [],
  replyTo: null,
  reactions: [],
  readBy: [],
  deliveredTo: [],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

Remove Members from Group

// Admin removes member
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    admins: alice._id
  },
  {
    $pull: { participants: david._id }
  }
)

// Also remove from admins if they were admin
db.conversations.updateOne(
  { _id: groupChat._id },
  {
    $pull: { admins: david._id }
  }
)

Promote User to Admin

// Promote Bob to admin
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    admins: alice._id,  // Only admins can promote
    participants: bob._id  // Must be participant
  },
  {
    $addToSet: { admins: bob._id }
  }
)

Update Group Details

// Update group name and description
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    admins: alice._id
  },
  {
    $set: {
      name: "Project Alpha - Core Team",
      description: "Main development chat for Project Alpha (Core team members only)"
    }
  }
)

Leave Group

// User leaves group
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    participants: emma._id
  },
  {
    $pull: { 
      participants: emma._id,
      admins: emma._id  // Also remove from admins if admin
    }
  }
)

✨ Phase 4: Advanced Message Features

Implement typing indicators, message search, and threading.

Typing Indicators

Track Who's Typing

// User starts typing
db.typingIndicators.updateOne(
  { 
    conversationId: conversation._id,
    userId: alice._id
  },
  {
    $set: {
      isTyping: true,
      lastUpdated: new Date()
    }
  },
  { upsert: true }
)

// User stops typing
db.typingIndicators.updateOne(
  { 
    conversationId: conversation._id,
    userId: alice._id
  },
  {
    $set: {
      isTyping: false,
      lastUpdated: new Date()
    }
  }
)

// Get who's currently typing (excluding current user)
db.typingIndicators.aggregate([
  {
    $match: {
      conversationId: conversation._id,
      userId: { $ne: alice._id },
      isTyping: true
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "userId",
      foreignField: "_id",
      as: "user"
    }
  },
  { $unwind: "$user" },
  {
    $project: {
      _id: 0,
      username: "$user.username",
      displayName: "$user.displayName"
    }
  }
])

Search Messages

Full-Text Message Search

// Search all messages for keywords
db.messages.find({
  $text: { $search: "design mockups" },
  isDeleted: false
})
.sort({ createdAt: -1 })
.limit(20)

// Search within specific conversation
db.messages.find({
  conversationId: groupChat._id,
  $text: { $search: "meeting" },
  isDeleted: false
})

// Search with relevance score
db.messages.find(
  {
    $text: { $search: "project alpha" },
    isDeleted: false
  },
  {
    score: { $meta: "textScore" }
  }
)
.sort({ score: { $meta: "textScore" } })
.limit(10)

Get Unread Message Count

// Count unread messages for a user in a conversation
db.messages.countDocuments({
  conversationId: conversation._id,
  senderId: { $ne: alice._id },  // Not sent by user
  "readBy.userId": { $ne: alice._id },  // Not read by user
  isDeleted: false
})

// Get unread count per conversation
db.messages.aggregate([
  {
    $match: {
      senderId: { $ne: alice._id },
      "readBy.userId": { $ne: alice._id },
      isDeleted: false
    }
  },
  {
    $group: {
      _id: "$conversationId",
      unreadCount: { $sum: 1 }
    }
  },
  {
    $lookup: {
      from: "conversations",
      localField: "_id",
      foreignField: "_id",
      as: "conversation"
    }
  },
  { $unwind: "$conversation" },
  {
    $project: {
      _id: 0,
      conversationId: "$_id",
      conversationName: "$conversation.name",
      unreadCount: 1
    }
  }
])

Message Threading

Get Thread Replies

var parentMessage = db.messages.findOne({ 
  content: /kick-off meeting/
})

// Get all replies to a message
db.messages.find({
  replyTo: parentMessage._id,
  isDeleted: false
})
.sort({ createdAt: 1 })

// Get message with reply count
db.messages.aggregate([
  { $match: { _id: parentMessage._id } },
  {
    $lookup: {
      from: "messages",
      let: { msgId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$replyTo", "$$msgId"] },
            isDeleted: false
          }
        }
      ],
      as: "replies"
    }
  },
  {
    $project: {
      content: 1,
      senderId: 1,
      createdAt: 1,
      replyCount: { $size: "$replies" }
    }
  }
])

Pin Important Messages

// Add pinned messages to conversation
db.conversations.updateOne(
  { 
    _id: groupChat._id,
    admins: alice._id  // Only admins can pin
  },
  {
    $addToSet: {
      pinnedMessages: {
        messageId: parentMessage._id,
        pinnedBy: alice._id,
        pinnedAt: new Date()
      }
    }
  }
)

// Get pinned messages for conversation
db.conversations.aggregate([
  { $match: { _id: groupChat._id } },
  { $unwind: "$pinnedMessages" },
  {
    $lookup: {
      from: "messages",
      localField: "pinnedMessages.messageId",
      foreignField: "_id",
      as: "message"
    }
  },
  { $unwind: "$message" },
  {
    $project: {
      _id: 0,
      messageContent: "$message.content",
      pinnedBy: "$pinnedMessages.pinnedBy",
      pinnedAt: "$pinnedMessages.pinnedAt"
    }
  }
])

📁 Phase 5: File Sharing

Handle file attachments and media in messages.

Upload File and Send Message

Share File in Chat

// Store file metadata
db.files.insertOne({
  filename: "design_mockups_v3.pdf",
  originalName: "Design Mockups - Final Version.pdf",
  mimeType: "application/pdf",
  size: 2457600,  // bytes
  url: "https://storage.chatflow.com/files/abc123/design_mockups_v3.pdf",
  uploadedBy: bob._id,
  conversationId: groupChat._id,
  uploadedAt: new Date()
})

var file = db.files.findOne({ filename: "design_mockups_v3.pdf" })

// Send message with file attachment
db.messages.insertOne({
  conversationId: groupChat._id,
  senderId: bob._id,
  content: "Here are the final design mockups for review!",
  messageType: "file",
  attachments: [
    {
      fileId: file._id,
      url: file.url,
      filename: file.originalName,
      size: file.size,
      type: file.mimeType
    }
  ],
  replyTo: null,
  reactions: [],
  readBy: [{ userId: bob._id, readAt: new Date() }],
  deliveredTo: [
    { userId: alice._id, deliveredAt: new Date() },
    { userId: carol._id, deliveredAt: new Date() },
    { userId: emma._id, deliveredAt: new Date() }
  ],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

var fileMessage = db.messages.findOne({ "attachments.filename": file.originalName })

// Link message to file
db.files.updateOne(
  { _id: file._id },
  { $set: { messageId: fileMessage._id } }
)

Share Image with Preview

// Upload image
db.files.insertOne({
  filename: "screenshot_bug_12345.png",
  originalName: "Bug Screenshot.png",
  mimeType: "image/png",
  size: 856432,
  url: "https://storage.chatflow.com/images/xyz789/screenshot.png",
  uploadedBy: david._id,
  conversationId: groupChat._id,
  uploadedAt: new Date()
})

var imageFile = db.files.findOne({ filename: "screenshot_bug_12345.png" })

// Send image message
db.messages.insertOne({
  conversationId: groupChat._id,
  senderId: david._id,
  content: "Found a UI bug in the navigation menu. See screenshot:",
  messageType: "image",
  attachments: [
    {
      fileId: imageFile._id,
      url: imageFile.url,
      filename: imageFile.originalName,
      size: imageFile.size,
      type: imageFile.mimeType
    }
  ],
  replyTo: null,
  reactions: [],
  readBy: [{ userId: david._id, readAt: new Date() }],
  deliveredTo: [
    { userId: alice._id, deliveredAt: new Date() },
    { userId: bob._id, deliveredAt: new Date() },
    { userId: carol._id, deliveredAt: new Date() }
  ],
  isEdited: false,
  isDeleted: false,
  createdAt: new Date()
})

Get All Files in Conversation

// Get all files shared in conversation
db.files.find({
  conversationId: groupChat._id
})
.sort({ uploadedAt: -1 })

// Get files with uploader info
db.files.aggregate([
  { $match: { conversationId: groupChat._id } },
  {
    $lookup: {
      from: "users",
      localField: "uploadedBy",
      foreignField: "_id",
      as: "uploader"
    }
  },
  { $unwind: "$uploader" },
  {
    $project: {
      filename: "$originalName",
      size: 1,
      mimeType: 1,
      url: 1,
      uploadedBy: "$uploader.displayName",
      uploadedAt: 1
    }
  },
  { $sort: { uploadedAt: -1 } }
])

Calculate Storage Usage

// Total storage per user
db.files.aggregate([
  {
    $group: {
      _id: "$uploadedBy",
      totalFiles: { $sum: 1 },
      totalSize: { $sum: "$size" }
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user"
    }
  },
  { $unwind: "$user" },
  {
    $project: {
      _id: 0,
      username: "$user.username",
      totalFiles: 1,
      totalSizeMB: { $round: [{ $divide: ["$totalSize", 1048576] }, 2] }
    }
  },
  { $sort: { totalSizeMB: -1 } }
])

// Files by type
db.files.aggregate([
  {
    $group: {
      _id: "$mimeType",
      count: { $sum: 1 },
      totalSize: { $sum: "$size" }
    }
  },
  {
    $project: {
      _id: 0,
      fileType: "$_id",
      count: 1,
      totalSizeMB: { $round: [{ $divide: ["$totalSize", 1048576] }, 2] }
    }
  },
  { $sort: { totalSizeMB: -1 } }
])

📊 Phase 6: Analytics & Insights

Extract valuable insights from chat data.

Most Active Users

User Activity Analysis

db.messages.aggregate([
  { $match: { isDeleted: false } },
  {
    $group: {
      _id: "$senderId",
      messageCount: { $sum: 1 },
      reactionsReceived: { 
        $sum: { $size: { $ifNull: ["$reactions", []] } }
      }
    }
  },
  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user"
    }
  },
  { $unwind: "$user" },
  {
    $project: {
      _id: 0,
      username: "$user.username",
      displayName: "$user.displayName",
      messageCount: 1,
      reactionsReceived: 1
    }
  },
  { $sort: { messageCount: -1 } },
  { $limit: 10 }
])

Peak Activity Hours

db.messages.aggregate([
  {
    $group: {
      _id: { $hour: "$createdAt" },
      messageCount: { $sum: 1 }
    }
  },
  {
    $project: {
      _id: 0,
      hour: "$_id",
      messageCount: 1
    }
  },
  { $sort: { hour: 1 } }
])

Conversation Activity

Most Active Conversations

db.messages.aggregate([
  { $match: { isDeleted: false } },
  {
    $group: {
      _id: "$conversationId",
      messageCount: { $sum: 1 },
      uniqueSenders: { $addToSet: "$senderId" },
      lastActivity: { $max: "$createdAt" }
    }
  },
  {
    $lookup: {
      from: "conversations",
      localField: "_id",
      foreignField: "_id",
      as: "conversation"
    }
  },
  { $unwind: "$conversation" },
  {
    $project: {
      _id: 0,
      conversationName: {
        $ifNull: ["$conversation.name", "Direct Message"]
      },
      type: "$conversation.type",
      messageCount: 1,
      activeUsers: { $size: "$uniqueSenders" },
      lastActivity: 1
    }
  },
  { $sort: { messageCount: -1 } }
])

Response Time Analysis

// Average time between messages in conversations
db.messages.aggregate([
  { $match: { conversationId: groupChat._id, isDeleted: false } },
  { $sort: { createdAt: 1 } },
  {
    $setWindowFields: {
      partitionBy: "$conversationId",
      sortBy: { createdAt: 1 },
      output: {
        prevTimestamp: {
          $shift: {
            output: "$createdAt",
            by: -1
          }
        }
      }
    }
  },
  {
    $match: { prevTimestamp: { $ne: null } }
  },
  {
    $project: {
      timeDiff: {
        $divide: [
          { $subtract: ["$createdAt", "$prevTimestamp"] },
          60000  // Convert to minutes
        ]
      }
    }
  },
  {
    $group: {
      _id: null,
      avgResponseTime: { $avg: "$timeDiff" },
      medianResponseTime: { $median: { input: "$timeDiff", method: "approximate" } }
    }
  },
  {
    $project: {
      _id: 0,
      avgResponseMinutes: { $round: ["$avgResponseTime", 1] },
      medianResponseMinutes: { $round: ["$medianResponseTime", 1] }
    }
  }
])

Popular Reactions

db.messages.aggregate([
  { $match: { reactions: { $exists: true, $ne: [] } } },
  { $unwind: "$reactions" },
  {
    $group: {
      _id: "$reactions.emoji",
      count: { $sum: 1 }
    }
  },
  {
    $project: {
      _id: 0,
      emoji: "$_id",
      count: 1
    }
  },
  { $sort: { count: -1 } },
  { $limit: 10 }
])

Daily Message Volume

db.messages.aggregate([
  { $match: { isDeleted: false } },
  {
    $group: {
      _id: {
        year: { $year: "$createdAt" },
        month: { $month: "$createdAt" },
        day: { $dayOfMonth: "$createdAt" }
      },
      messageCount: { $sum: 1 },
      uniqueUsers: { $addToSet: "$senderId" }
    }
  },
  {
    $project: {
      _id: 0,
      date: {
        $dateFromParts: {
          year: "$_id.year",
          month: "$_id.month",
          day: "$_id.day"
        }
      },
      messageCount: 1,
      activeUsers: { $size: "$uniqueUsers" }
    }
  },
  { $sort: { date: -1 } },
  { $limit: 30 }  // Last 30 days
])

User Engagement Score

Calculate User Activity Score

db.users.aggregate([
  {
    $lookup: {
      from: "messages",
      let: { userId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$senderId", "$$userId"] },
            isDeleted: false
          }
        },
        {
          $group: {
            _id: null,
            messageCount: { $sum: 1 },
            reactionCount: { $sum: { $size: "$reactions" } }
          }
        }
      ],
      as: "activity"
    }
  },
  { $unwind: { path: "$activity", preserveNullAndEmptyArrays: true } },
  {
    $project: {
      username: 1,
      displayName: 1,
      messageCount: { $ifNull: ["$activity.messageCount", 0] },
      reactionsReceived: { $ifNull: ["$activity.reactionCount", 0] },
      engagementScore: {
        $add: [
          { $multiply: [{ $ifNull: ["$activity.messageCount", 0] }, 1] },
          { $multiply: [{ $ifNull: ["$activity.reactionCount", 0] }, 2] }
        ]
      }
    }
  },
  { $sort: { engagementScore: -1 } }
])

⚡ Performance Optimization

📇 Compound Indexes

{conversationId: 1, createdAt: -1} for efficient message queries

🔍 Text Search

Full-text indexes enable fast message search across millions of messages

⏱️ TTL Indexes

Auto-cleanup of typing indicators prevents collection bloat

📄 Pagination

Cursor-based pagination for infinite scroll without performance degradation

Query Performance Analysis

// Check if compound index is used
db.messages.find({
  conversationId: conversation._id,
  isDeleted: false
})
.sort({ createdAt: -1 })
.limit(20)
.explain("executionStats")

// Look for "stage": "IXSCAN" indicating index usage

Optimize Message Count Query

// Create compound index for unread count
db.messages.createIndex({ 
  conversationId: 1, 
  "readBy.userId": 1, 
  isDeleted: 1 
})

// This makes unread queries much faster
db.messages.countDocuments({
  conversationId: conversation._id,
  "readBy.userId": { $ne: alice._id },
  isDeleted: false
})

⚠️ Performance Best Practices:

Always use compound indexes for frequently queried field combinations
Implement cursor-based pagination instead of skip/limit for large datasets
Use projections to limit returned fields in list views
Consider read replicas for analytics queries on high-traffic systems
Archive old messages to a separate collection after 1-2 years
Cache conversation lists in Redis for active users

Message Archiving Strategy

Archive Old Messages

// Create archive collection
db.createCollection("messages_archive")

// Move messages older than 1 year
var oneYearAgo = new Date()
oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1)

// Copy to archive
db.messages.find({
  createdAt: { $lt: oneYearAgo }
}).forEach(function(msg) {
  db.messages_archive.insertOne(msg)
})

// Delete from main collection
db.messages.deleteMany({
  createdAt: { $lt: oneYearAgo }
})

🏆 Advanced Challenges

Challenge 1: @Mentions

Implement @username mentions that notify tagged users and are searchable

Challenge 2: Message Forwarding

Allow users to forward messages to other conversations while preserving original metadata

Challenge 3: Voice Messages

Add support for voice message attachments with duration tracking

Challenge 4: Message Scheduling

Schedule messages to be sent at a future time using timestamps

Challenge 5: Auto-Moderation

Flag messages containing profanity or spam patterns for review

Challenge 6: Chat Export

Generate conversation exports in JSON/PDF format with all metadata

💡 Challenge Hints:

@Mentions: Parse message content for @username, store array of mentioned user IDs, create notifications for mentioned users
Forwarding: Add forwardedFrom field to messages, preserve original senderId and timestamp, update conversation stats
Voice Messages: Store audio file in files collection, add duration field, create waveform visualization data
Scheduling: Add scheduledFor field, create background job to move scheduled messages at specified time
Auto-Moderation: Use regex patterns or text analysis, add flagged boolean, create moderation review queue collection
Chat Export: Use aggregation to join messages with user data, format as JSON, optionally convert to PDF using external library