Construction Project Management - MongoDB Aggregation & Analytics Dashboard

A production-grade MongoDB aggregation pipeline and Express/Node.js API integration for a Construction Project Management Application's Project Dashboard.

---

📋 Table of Contents

• Overview
• Project Document Schema
• Dashboard Requirements
• Production Aggregation Pipeline
• Stage-by-Stage Pipeline Breakdown
• Indexing & Performance Realities
• Sample Input & Expected Output
• Node.js & Express API Integration
• Setup & Usage Instructions

---

🔍 Overview

This repository contains an optimized, single-query MongoDB aggregation pipeline designed to feed a real-time construction project management dashboard. It calculates summary metrics, financial health, average durations, overdue statuses, and per-status breakdowns in a single database round-trip using MongoDB's $facet stage.

---

🗄️ Project Document Schema

The pipeline operates on the projects collection in MongoDB. Each project document adheres to the following structure:

{
  "_id": "ObjectId",
  "name": "String",
  "status": "planning | active | completed | on-hold",
  "startDate": "Date",
  "endDate": "Date",
  "budget": "Number",
  "spentAmount": "Number",
  "createdAt": "Date",
  "updatedAt": "Date"
}

---

🎯 Dashboard Requirements

1. Project Breakdown by Status: Calculate total count and total financial stats grouped by status (planning, active, completed, on-hold).
2. Average Project Duration: Compute average duration in days based on startDate and endDate.
3. Overdue Project Identification: Flag projects as overdue if status !== "completed" and endDate < CURRENT_DATE.
4. Budget Utilization Percentage: Calculate (spentAmount / budget) * 100 safely with zero-division guard and rounding to 2 decimal places.

---

🚀 Production Aggregation Pipeline

const getDashboardPipeline = () => [
  // ---------------------------------------------------------------------------
  // Stage 1: $match (Scope pre-filtering)
  // Filters documents at the collection entry point to allow index utilization.
  // ---------------------------------------------------------------------------
  {
    $match: {
      status: { $in: ["planning", "active", "completed", "on-hold"] }
    }
  },

  // ---------------------------------------------------------------------------
  // Stage 2: $addFields (Type-Safe Computation & Defensive Conversion)
  // Converts date strings/types safely using $convert to prevent $subtract runtime errors.
  // ---------------------------------------------------------------------------
  {
    $addFields: {
      parsedStartDate: {
        $convert: {
          input: "$startDate",
          to: "date",
          onError: null,
          onNull: null
        }
      },
      parsedEndDate: {
        $convert: {
          input: "$endDate",
          to: "date",
          onError: null,
          onNull: null
        }
      }
    }
  },
  {
    $addFields: {
      // Calculate project duration in days: (parsedEndDate - parsedStartDate) / msPerDay
      durationInDays: {
        $cond: {
          if: {
            $and: [
              { $ne: ["$parsedStartDate", null] },
              { $ne: ["$parsedEndDate", null] },
              { $gte: ["$parsedEndDate", "$parsedStartDate"] }
            ]
          },
          then: {
            $divide: [
              { $subtract: ["$parsedEndDate", "$parsedStartDate"] },
              1000 * 60 * 60 * 24 // 86,400,000 milliseconds in a day
            ]
          },
          else: null
        }
      },

      // Determine overdue status: not completed AND parsedEndDate is in the past
      isOverdue: {
        $and: [
          { $ne: ["$status", "completed"] },
          { $ne: ["$parsedEndDate", null] },
          { $lt: ["$parsedEndDate", "$$NOW"] } // Uses MongoDB system variable $$NOW
        ]
      },

      // Safe budget utilization % calculation with zero-division guard
      budgetUtilizationPct: {
        $cond: {
          if: {
            $or: [
              { $eq: ["$budget", null] },
              { $lte: ["$budget", 0] },
              { $eq: [{ $type: "$budget" }, "missing"] }
            ]
          },
          then: 0,
          else: {
            $round: [
              {
                $multiply: [
                  { $divide: [{ $ifNull: ["$spentAmount", 0] }, "$budget"] },
                  100
                ]
              },
              2 // Round to 2 decimal places
            ]
          }
        }
      }
    }
  },

  // ---------------------------------------------------------------------------
  // Stage 3: $facet (Multi-perspective aggregated analysis in 1 query)
  // ---------------------------------------------------------------------------
  {
    $facet: {
      // 1. Group total project count & financial totals by status
      statusBreakdown: [
        {
          $group: {
            _id: "$status",
            count: { $sum: 1 },
            totalBudget: { $sum: "$budget" },
            totalSpent: { $sum: "$spentAmount" }
          }
        },
        {
          $project: {
            _id: 0,
            status: "$_id",
            count: 1,
            totalBudget: 1,
            totalSpent: 1
          }
        }
      ],

      // 2. Global KPIs (Average Duration in days & Overdue count)
      overallMetrics: [
        {
          $group: {
            _id: null,
            totalProjects: { $sum: 1 },
            avgDurationDays: { $avg: "$durationInDays" },
            totalOverdueCount: {
              $sum: { $cond: ["$isOverdue", 1, 0] }
            }
          }
        },
        {
          $project: {
            _id: 0,
            totalProjects: 1,
            avgDurationDays: { $round: ["$avgDurationDays", 1] },
            totalOverdueCount: 1
          }
        }
      ],

      // 3. Detailed list of overdue projects
      overdueProjects: [
        { $match: { isOverdue: true } },
        {
          $project: {
            _id: 1,
            name: 1,
            status: 1,
            startDate: 1,
            endDate: 1,
            budget: 1,
            spentAmount: 1,
            budgetUtilizationPct: 1,
            durationInDays: 1
          }
        }
      ],

      // 4. All projects with calculated metrics for frontend data grid
      projectList: [
        {
          $project: {
            _id: 1,
            name: 1,
            status: 1,
            startDate: 1,
            endDate: 1,
            budget: 1,
            spentAmount: 1,
            budgetUtilizationPct: 1,
            durationInDays: 1,
            isOverdue: 1
          }
        }
      ]
    }
  },

  // ---------------------------------------------------------------------------
  // Stage 4: $project (Normalize output structure)
  // ---------------------------------------------------------------------------
  {
    $project: {
      statusBreakdown: 1,
      overallMetrics: {
        $ifNull: [
          { $arrayElemAt: ["$overallMetrics", 0] },
          { totalProjects: 0, avgDurationDays: 0, totalOverdueCount: 0 }
        ]
      },
      overdueProjects: 1,
      projectList: 1
    }
  }
];

---

🛠️ Stage-by-Stage Pipeline Breakdown

Stage	Name	Purpose & Rationale
Stage 1	$match	Pre-filters documents at collection scan time. This is the only stage in this pipeline that can leverage database indexes to narrow down the document set.
Stage 2	$addFields	Type-safe computation stage using $convert (onError: null, onNull: null). Guarantees that invalid date formats/strings, nulls, missing fields, or zero budgets do not break arithmetic operations ($subtract, $divide).
Stage 3	$facet	Executes parallel aggregate streams in memory over the pipeline documents. Fetches status breakdowns, summary metrics, overdue project details, and the main data grid in a single database round-trip.
Stage 4	$project	Cleans up and reshapes the final response. Extracts single object metrics out of $facet arrays using $arrayElemAt and provides fallback default values if no projects match.

---

⚡ Indexing & Performance Realities

Indexing Scope & Pipeline Execution

MongoDB indexes are only used by the initial $match stage at the start of the aggregation pipeline. Once documents pass into $addFields and $facet, all operations are performed in memory on the working document set.

1. Compound Index for Initial Scope Filtering

db.projects.createIndex(
  { status: 1, endDate: 1 },
  { name: "idx_status_endDate" }
);

• Purpose: Helps MongoDB efficiently locate relevant project documents during Stage 1 $match if filtering by status or date ranges.

2. Partial Index Syntax (Valid MongoDB Specification)

Note: MongoDB partialFilterExpression does not support the $ne operator. To create a partial index for uncompleted projects, explicit status values must be passed using $in:

db.projects.createIndex(
  { endDate: 1, status: 1 },
  {
    name: "idx_uncompleted_projects_endDate",
    partialFilterExpression: {
      status: { $in: ["planning", "active", "on-hold"] }
    }
  }
);

• Purpose: Useful if you run direct queries (db.projects.find(...)) specifically querying uncompleted projects by endDate. (Note: This index will only benefit aggregation pipelines if an explicit status $match matching the partial filter expression is used as the very first stage).

---

📥 Sample Input & Expected Output

Sample Input Documents (projects collection)

[
  {
    "_id": { "$oid": "66b1a1111111111111111111" },
    "name": "Commercial Plaza Tower A",
    "status": "active",
    "startDate": { "$date": "2026-01-01T00:00:00Z" },
    "endDate": { "$date": "2026-06-01T00:00:00Z" },
    "budget": 500000,
    "spentAmount": 350000
  },
  {
    "_id": { "$oid": "66b1a2222222222222222222" },
    "name": "Highway Expansion Sector 4",
    "status": "planning",
    "startDate": { "$date": "2026-09-01T00:00:00Z" },
    "endDate": { "$date": "2026-12-31T00:00:00Z" },
    "budget": 2000000,
    "spentAmount": 0
  },
  {
    "_id": { "$oid": "66b1a3333333333333333333" },
    "name": "City Bridge Renovation",
    "status": "completed",
    "startDate": { "$date": "2025-01-01T00:00:00Z" },
    "endDate": { "$date": "2025-12-31T00:00:00Z" },
    "budget": 800000,
    "spentAmount": 780000
  },
  {
    "_id": { "$oid": "66b1a4444444444444444444" },
    "name": "Community Park & Recreation",
    "status": "on-hold",
    "startDate": { "$date": "2026-01-01T00:00:00Z" },
    "endDate": { "$date": "2026-05-01T00:00:00Z" },
    "budget": 0,
    "spentAmount": 0
  }
]

Verified Sample Calculations

• Commercial Plaza Tower A: Duration = 151 days (2026-01-01 to 2026-06-01). Budget Utilization = (350,000 / 500,000) * 100 = 70%.
• Highway Expansion Sector 4: Duration = 121 days (2026-09-01 to 2026-12-31). Budget Utilization = 0%.
• City Bridge Renovation: Duration = 364 days (2025-01-01 to 2025-12-31). Budget Utilization = (780,000 / 800,000) * 100 = 97.5%.
• Community Park & Recreation: Duration = 120 days (2026-01-01 to 2026-05-01). Budget Utilization = 0% (Guard against budget = 0).
• Average Duration Calculation: (151 + 121 + 364 + 120) / 4 = 756 / 4 = 189.0 days.

Expected Aggregation Output JSON

{
  "statusBreakdown": [
    {
      "status": "active",
      "count": 1,
      "totalBudget": 500000,
      "totalSpent": 350000
    },
    {
      "status": "planning",
      "count": 1,
      "totalBudget": 2000000,
      "totalSpent": 0
    },
    {
      "status": "completed",
      "count": 1,
      "totalBudget": 800000,
      "totalSpent": 780000
    },
    {
      "status": "on-hold",
      "count": 1,
      "totalBudget": 0,
      "totalSpent": 0
    }
  ],
  "overallMetrics": {
    "totalProjects": 4,
    "avgDurationDays": 189.0,
    "totalOverdueCount": 2
  },
  "overdueProjects": [
    {
      "_id": "66b1a1111111111111111111",
      "name": "Commercial Plaza Tower A",
      "status": "active",
      "startDate": "2026-01-01T00:00:00.000Z",
      "endDate": "2026-06-01T00:00:00.000Z",
      "budget": 500000,
      "spentAmount": 350000,
      "budgetUtilizationPct": 70,
      "durationInDays": 151
    },
    {
      "_id": "66b1a4444444444444444444",
      "name": "Community Park & Recreation",
      "status": "on-hold",
      "startDate": "2026-01-01T00:00:00.000Z",
      "endDate": "2026-05-01T00:00:00.000Z",
      "budget": 0,
      "spentAmount": 0,
      "budgetUtilizationPct": 0,
      "durationInDays": 120
    }
  ],
  "projectList": [
    {
      "_id": "66b1a1111111111111111111",
      "name": "Commercial Plaza Tower A",
      "status": "active",
      "startDate": "2026-01-01T00:00:00.000Z",
      "endDate": "2026-06-01T00:00:00.000Z",
      "budget": 500000,
      "spentAmount": 350000,
      "budgetUtilizationPct": 70,
      "durationInDays": 151,
      "isOverdue": true
    },
    {
      "_id": "66b1a2222222222222222222",
      "name": "Highway Expansion Sector 4",
      "status": "planning",
      "startDate": "2026-09-01T00:00:00.000Z",
      "endDate": "2026-12-31T00:00:00.000Z",
      "budget": 2000000,
      "spentAmount": 0,
      "budgetUtilizationPct": 0,
      "durationInDays": 121,
      "isOverdue": false
    },
    {
      "_id": "66b1a3333333333333333333",
      "name": "City Bridge Renovation",
      "status": "completed",
      "startDate": "2025-01-01T00:00:00.000Z",
      "endDate": "2025-12-31T00:00:00.000Z",
      "budget": 800000,
      "spentAmount": 780000,
      "budgetUtilizationPct": 97.5,
      "durationInDays": 364,
      "isOverdue": false
    },
    {
      "_id": "66b1a4444444444444444444",
      "name": "Community Park & Recreation",
      "status": "on-hold",
      "startDate": "2026-01-01T00:00:00.000Z",
      "endDate": "2026-05-01T00:00:00.000Z",
      "budget": 0,
      "spentAmount": 0,
      "budgetUtilizationPct": 0,
      "durationInDays": 120,
      "isOverdue": true
    }
  ]
}

---

🌐 Node.js & Express API Integration

import express from 'express';
import mongoose from 'mongoose';

const router = express.Router();

/**
 * @route   GET /api/v1/projects/dashboard
 * @desc    Get aggregated project dashboard metrics
 * @access  Private / Authenticated
 */
router.get('/dashboard', async (req, res, next) => {
  try {
    const Project = mongoose.model('Project');

    // Run pipeline with allowDiskUse for memory safety on larger collections
    const [dashboardData] = await Project.aggregate(getDashboardPipeline())
      .allowDiskUse(true)
      .exec();

    // Fallback response if collection is empty
    const responsePayload = dashboardData || {
      statusBreakdown: [],
      overallMetrics: { totalProjects: 0, avgDurationDays: 0, totalOverdueCount: 0 },
      overdueProjects: [],
      projectList: []
    };

    return res.status(200).json({
      success: true,
      message: 'Dashboard metrics retrieved successfully',
      data: responsePayload
    });
  } catch (error) {
    next(error);
  }
});

// Centralized Error Handling Middleware
router.use((err, req, res, next) => {
  console.error('[Dashboard Aggregation Error]:', err);
  return res.status(500).json({
    success: false,
    message: 'Internal server error processing dashboard analytics',
    error: process.env.NODE_ENV === 'development' ? err.message : undefined
  });
});

export default router;

---

💻 Setup & Usage Instructions

1. Prerequisites: Node.js (v18+), MongoDB (v6.0+), Mongoose (v7+).
2. Apply Database Indexes: Run the index creation commands in your MongoDB shell or migration scripts.
3. Integrate Pipeline: Copy getDashboardPipeline and Express route into your backend project.
4. Test Output: Execute a GET request to /api/v1/projects/dashboard.