dashboard.js

Go to the documentation of this file.

/**
 * @file routes/dashboard.js
 * @module routes/dashboard
 * @description Dashboard statistics and metrics API for MSP overview. Provides real-time agent
 * health metrics, business KPIs, and aggregated statistics across ticket, billing, monitoring,
 * and alerting systems.
 * 
 * **Dashboard Components:**
 * - Agent health: Real-time CPU, memory, disk, uptime from RMM agents
 * - Ticket metrics: Active tickets, closed this month
 * - Alerting: Unread critical alerts
 * - Monitoring: Online agent count, device health
 * - Financial: Invoices and payments for current month
 * - Incidents: Recent critical incidents/alerts
 * 
 * **Agent Metrics Collection:**
 * Retrieves most recent metrics for each agent using PostgreSQL DISTINCT ON for efficiency.
 * Merges agent metadata (hostname, OS, customer) with latest metric snapshot.
 * 
 * **Time-Based Filtering:**
 * - Active tickets: status IN ('open', 'in_progress')
 * - Online agents: last_seen within 5 minutes
 * - Monthly stats: DATE_TRUNC('month') for current month aggregations
 * - Paid invoices: status = 'paid' within current month
 * 
 * **Multi-Tenant Isolation:**
 * All queries filtered by authenticated user's tenant scope. Root MSP users see all tenants'
 * data. Regular users see only their tenant's dashboard.
 * 
 * **Performance Considerations:**
 * - Uses COUNT(*)::int for efficient counting
 * - DISTINCT ON for latest metric per agent (avoids window functions)
 * - Separate queries for each metric (parallelizable by client)
 * - Limited incident list (5 most recent)
 * 
 * **Use Cases:**
 * - Homepage dashboard overview
 * - Real-time monitoring dashboards
 * - Executive reporting
 * - Health check status boards
 * - MSP portal home page
 * @requires express
 * @requires ../services/db
 * @requires ../middleware/auth
 * @requires ../middleware/tenant
 * @author IBG MSP Development Team
 * @date 2024-03-11
 * @since 1.0.0
 * @see {@link module:routes/agent} for agent management
 * @see {@link module:routes/tickets} for ticketing system
 * @see {@link module:routes/invoices} for billing
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
 
 
/**
 * @api {get} /dashboard/agent-metrics Get Agent Metrics
 * @apiName GetAgentMetrics
 * @apiGroup Dashboard
 * @apiDescription Retrieves latest system metrics for all RMM agents in tenant. Combines agent
 * metadata (hostname, OS, customer) with most recent metric snapshot (CPU, memory, disk, uptime).
 * Used for real-time monitoring dashboards and health overviews.
 * 
 * **Metrics Included:**
 * - CPU: CPU usage percentage (0-100)
 * - Memory: Memory usage percentage (0-100)
 * - Disk: Disk usage percentage (0-100)
 * - Uptime: System uptime in seconds
 * - Raw: Complete raw metrics JSON from agent
 * 
 * **Data Freshness:**
 * Returns most recent metric for each agent using PostgreSQL DISTINCT ON optimization.
 * Metric age depends on agent heartbeat interval (typically 1-5 minutes).
 * 
 * **Agent States:**
 * - metrics: non-null - Agent reporting metrics
 * - metrics: null - Agent never reported metrics or data purged
 * 
 * **Tenant Filtering:**
 * Only returns agents belonging to authenticated user's tenant. Root MSP users see all
 * tenants' agents.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiSuccess {object[]} agents Array of agents with latest metrics
 * @apiSuccess {number} agents.agent_id Agent ID
 * @apiSuccess {string} agents.agent_uuid Agent UUID
 * @apiSuccess {string} agents.hostname Device hostname
 * @apiSuccess {string} agents.os Operating system (Windows, Linux, macOS)
 * @apiSuccess {number} agents.customer_id Associated customer ID
 * @apiSuccess {object} agents.metrics Latest metric snapshot (or null)
 * @apiSuccess {number} agents.metrics.agent_id Agent ID
 * @apiSuccess {string} agents.metrics.ts Metric timestamp
 * @apiSuccess {number} agents.metrics.cpu CPU usage percentage
 * @apiSuccess {number} agents.metrics.memory Memory usage percentage
 * @apiSuccess {number} agents.metrics.disk Disk usage percentage
 * @apiSuccess {number} agents.metrics.uptime System uptime in seconds
 * @apiSuccess {Object} agents.metrics.raw Raw metrics JSON from agent
 * @apiError {number} 500 Failed to fetch agent metrics
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/dashboard/agent-metrics"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "agents": [
 *       {
 *         "agent_id": 142,
 *         "agent_uuid": "550e8400-e29b-41d4-a716-446655440000",
 *         "hostname": "ACME-DESKTOP-01",
 *         "os": "Windows 11 Pro",
 *         "customer_id": 45,
 *         "metrics": {
 *           "agent_id": 142,
 *           "ts": "2024-03-11T17:58:00.000Z",
 *           "cpu": 24.5,
 *           "memory": 62.3,
 *           "disk": 78.1,
 *           "uptime": 432000,
 *           "raw": {"cpu_cores": 8, "memory_total_gb": 32, "disk_total_gb": 512}
 *         }
 *       },
 *       {
 *         "agent_id": 89,
 *         "agent_uuid": "7c9e6679-7425-40de-944b-e07fc1f90ae7",
 *         "hostname": "ACME-SERVER-01",
 *         "os": "Ubuntu 22.04 LTS",
 *         "customer_id": 45,
 *         "metrics": null
 *       }
 *     ]
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/agent} for agent management
 * @see {@link module:routes/dashboard.getStats} for dashboard statistics
 */
router.get('/agent-metrics', async (req, res) => {
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req);
    const whereClause = tenantClause ? `WHERE ${tenantClause}` : '';
    // Get all agents for tenant
    const agentsResult = await pool.query(
      `SELECT agent_id, agent_uuid, hostname, os, customer_id FROM agents ${whereClause}`,
      tenantParams
    );
    const agents = agentsResult.rows;
    // For each agent, get latest metric
    const metricsResult = await pool.query(
      `SELECT DISTINCT ON (agent_id) agent_id, ts, cpu, memory, disk, uptime, raw
       FROM agent_metrics
       WHERE agent_id IN (${agents.map(a => a.agent_id).join(',') || 'NULL'})
       ORDER BY agent_id, ts DESC`
    );
    const metricsByAgent = {};
    metricsResult.rows.forEach(m => { metricsByAgent[m.agent_id] = m; });
    // Merge agent info and metrics
    const result = agents.map(agent => ({
      agent_id: agent.agent_id,
      agent_uuid: agent.agent_uuid,
      hostname: agent.hostname,
      os: agent.os,
      customer_id: agent.customer_id,
      metrics: metricsByAgent[agent.agent_id] || null
    }));
    res.json({ agents: result });
  } catch (err) {
    console.error('Error fetching agent metrics:', err);
    res.status(500).json({ error: 'Failed to fetch agent metrics' });
  }
});
 
/**
 * @api {get} /dashboard/stats Get Dashboard Statistics
 * @apiName GetDashboardStats
 * @apiGroup Dashboard
 * @apiDescription Retrieves comprehensive dashboard statistics and KPIs for MSP overview.
 * Aggregates data across ticketing, monitoring, billing, and alerting systems. All metrics
 * filtered by tenant scope.
 * 
 * **Statistics Provided:**
 * - activeTickets: Open or in-progress tickets
 * - criticalAlerts: Unread alert-type notifications
 * - ticketsClosedThisMonth: Tickets closed in current month
 * - onlineAgents: Agents with heartbeat within 5 minutes
 * - invoicedThisMonth: Total invoice amount for current month (excluding void)
 * - paymentsThisMonth: Total payments received in current month
 * - recentIncidents: Last 5 alert/warning notifications with formatted time
 * 
 * **Time Calculations:**
 * - "This month": DATE_TRUNC('month', CURRENT_DATE) comparison
 * - "Online": last_seen > NOW() - INTERVAL '5 minutes'
 * - Time ago formatting: Minutes (m), hours (h), or days (d)
 * 
 * **Financial Metrics:**
 * - invoicedThisMonth: Sum of issued_date within current month, status != 'void'
 * - paymentsThisMonth: Sum of invoices with status='paid' updated this month
 * - Note: Payment tracking approximation (updated_at used as proxy for payment date)
 * 
 * **Incident Formatting:**
 * Recent incidents include:
 * - id: notification_id
 * - message: Notification title
 * - type: alert or warning
 * - time: Human-readable relative time ("15m ago", "3h ago", "2d ago")
 * 
 * **Performance:**
 * Uses COUNT(*)::int and SUM()::numeric casts for type consistency. All queries use
 * tenant filter for security. Parallel execution possible via multiple connections.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiSuccess {number} activeTickets Count of open and in-progress tickets
 * @apiSuccess {number} criticalAlerts Count of unread critical alert notifications
 * @apiSuccess {number} ticketsClosedThisMonth Tickets closed in current calendar month
 * @apiSuccess {number} onlineAgents Count of agents with heartbeat within 5 minutes
 * @apiSuccess {number} invoicedThisMonth Total invoice amount for current month (excluding void)
 * @apiSuccess {number} paymentsThisMonth Total payments received in current month
 * @apiSuccess {object[]} recentIncidents Last 5 alert/warning incidents
 * @apiSuccess {number} recentIncidents.id Notification ID
 * @apiSuccess {string} recentIncidents.message Incident message/title
 * @apiSuccess {string} recentIncidents.type Incident type (alert, warning)
 * @apiSuccess {string} recentIncidents.time Human-readable time ago ("15m ago", "3h ago")
 * @apiError {number} 500 Failed to fetch dashboard stats
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/dashboard/stats"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "activeTickets": 23,
 *     "criticalAlerts": 5,
 *     "ticketsClosedThisMonth": 147,
 *     "onlineAgents": 432,
 *     "invoicedThisMonth": 147250.50,
 *     "paymentsThisMonth": 132440.00,
 *     "recentIncidents": [
 *       {
 *         "id": 891,
 *         "message": "Server CPU above 90% for 15 minutes",
 *         "type": "alert",
 *         "time": "15m ago"
 *       },
 *       {
 *         "id": 884,
 *         "message": "Backup failed on ACME-SERVER-01",
 *         "type": "warning",
 *         "time": "3h ago"
 *       },
 *       {
 *         "id": 875,
 *         "message": "Disk space critical: 95% used",
 *         "type": "alert",
 *         "time": "1d ago"
 *       }
 *     ]
 *   }
 * @since 1.0.0
 * @see {@link module:routes/dashboard.getAgentMetrics} for agent health metrics
 * @see {@link module:routes/tickets} for ticketing system
 * @see {@link module:routes/invoices} for billing
 */
router.get('/stats', async (req, res) => {
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req);
    const whereClause = tenantClause ? `WHERE ${tenantClause}` : '';
    
    // Get active tickets count
    const activeTicketsResult = await pool.query(
      `SELECT COUNT(*)::int AS count FROM tickets ${whereClause ? whereClause + ' AND' : 'WHERE'} status IN ('open', 'in_progress')`,
      tenantParams
    );
    const activeTickets = activeTicketsResult.rows[0]?.count || 0;
 
    // Get critical alerts count
    const criticalAlertsResult = await pool.query(
      `SELECT COUNT(*)::int AS count FROM notifications ${whereClause ? whereClause + ' AND' : 'WHERE'} type = 'alert' AND is_read = false`,
      tenantParams
    );
    const criticalAlerts = criticalAlertsResult.rows[0]?.count || 0;
 
    // Get tickets closed this month
    const ticketsClosedThisMonthResult = await pool.query(
      `SELECT COUNT(*)::int AS count FROM tickets 
       ${whereClause ? whereClause + ' AND' : 'WHERE'} status = 'closed' 
       AND DATE_TRUNC('month', updated_at) = DATE_TRUNC('month', CURRENT_DATE)`,
      tenantParams
    );
    const ticketsClosedThisMonth = ticketsClosedThisMonthResult.rows[0]?.count || 0;
 
    // Get online agents count
    const onlineAgentsResult = await pool.query(
      `SELECT COUNT(*)::int AS count FROM agents ${whereClause ? whereClause + ' AND' : 'WHERE'} last_seen > NOW() - INTERVAL '5 minutes'`,
      tenantParams
    );
    const onlineAgents = onlineAgentsResult.rows[0]?.count || 0;
 
    // Get invoices this month
    const invoicedThisMonthResult = await pool.query(
      `SELECT COALESCE(SUM(amount), 0)::numeric AS total FROM invoices 
       ${whereClause ? whereClause + ' AND' : 'WHERE'} DATE_TRUNC('month', issued_date) = DATE_TRUNC('month', CURRENT_DATE)
       AND status != 'void'`,
      tenantParams
    );
    const invoicedThisMonth = parseFloat(invoicedThisMonthResult.rows[0]?.total || 0);
 
    // Get payments received this month (approximation)
    const paymentsThisMonthResult = await pool.query(
      `SELECT COALESCE(SUM(amount), 0)::numeric AS total FROM invoices 
       ${whereClause ? whereClause + ' AND' : 'WHERE'} status = 'paid' 
       AND DATE_TRUNC('month', updated_at) = DATE_TRUNC('month', CURRENT_DATE)`,
      tenantParams
    );
    const paymentsThisMonth = parseFloat(paymentsThisMonthResult.rows[0]?.total || 0);
 
    // Get recent incidents
    const recentIncidentsResult = await pool.query(
      `SELECT notification_id as id, title as message, type, created_at 
       FROM notifications 
       ${whereClause ? whereClause + ' AND' : 'WHERE'} type IN ('alert', 'warning') 
       ORDER BY created_at DESC 
       LIMIT 5`,
      tenantParams
    );
    
    const recentIncidents = recentIncidentsResult.rows.map(incident => ({
      id: incident.id,
      message: incident.message,
      type: incident.type,
      time: formatTimeAgo(incident.created_at)
    }));
 
    res.json({
      activeTickets,
      criticalAlerts,
      ticketsClosedThisMonth,
      onlineAgents,
      invoicedThisMonth,
      paymentsThisMonth,
      recentIncidents
    });
  } catch (err) {
    console.error('Error fetching dashboard stats:', err);
    res.status(500).json({ error: 'Failed to fetch dashboard stats' });
  }
});
 
// Helper function to format time ago
/**
 *
 * @param date
 */
function formatTimeAgo(date) {
  const now = new Date();
  const diffMs = now - new Date(date);
  const diffMins = Math.floor(diffMs / 60000);
  const diffHours = Math.floor(diffMs / 3600000);
  const diffDays = Math.floor(diffMs / 86400000);
 
  if (diffMins < 60) return `${diffMins}m ago`;
  if (diffHours < 24) return `${diffHours}h ago`;
  return `${diffDays}d ago`;
}
 
module.exports = router;