Re_Backend/docs/KPI_REPORTING_SYSTEM.md

KPI Reporting System - Complete Guide

Overview

This document describes the complete KPI (Key Performance Indicator) reporting system for the Royal Enfield Workflow Management System, including database schema, views, and query examples.

---

📊 Database Schema

1. TAT Alerts Table (tat_alerts)

Purpose: Store all TAT notification records for display and KPI analysis

CREATE TABLE tat_alerts (
  alert_id UUID PRIMARY KEY,
  request_id UUID REFERENCES workflow_requests(request_id),
  level_id UUID REFERENCES approval_levels(level_id),
  approver_id UUID REFERENCES users(user_id),
  alert_type ENUM('TAT_50', 'TAT_75', 'TAT_100'),
  threshold_percentage INTEGER, -- 50, 75, or 100
  tat_hours_allocated DECIMAL(10,2),
  tat_hours_elapsed DECIMAL(10,2),
  tat_hours_remaining DECIMAL(10,2),
  level_start_time TIMESTAMP,
  alert_sent_at TIMESTAMP DEFAULT NOW(),
  expected_completion_time TIMESTAMP,
  alert_message TEXT,
  notification_sent BOOLEAN DEFAULT true,
  notification_channels TEXT[], -- ['push', 'email', 'sms']
  is_breached BOOLEAN DEFAULT false,
  was_completed_on_time BOOLEAN, -- Set when level completed
  completion_time TIMESTAMP,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

Key Features:

• ✅ Tracks every TAT notification sent (50%, 75%, 100%)
• ✅ Records timing information for KPI calculation
• ✅ Stores completion status for compliance reporting
• ✅ Metadata includes request title, approver name, priority

---

🎯 KPI Categories & Metrics

Category 1: Request Volume & Status

KPI Name	Description	SQL View	Primary Users
Total Requests Created	Count of all workflow requests	vw_request_volume_summary	All
Open Requests	Requests currently in progress with age	vw_workflow_aging	All
Approved Requests	Fully approved and closed	vw_request_volume_summary	All
Rejected Requests	Rejected at any stage	vw_request_volume_summary	All

Query Examples:

-- Total requests created this month
SELECT COUNT(*) as total_requests
FROM vw_request_volume_summary
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE);

-- Open requests with age
SELECT request_number, title, status, age_hours, status_category
FROM vw_request_volume_summary
WHERE status_category = 'IN_PROGRESS'
ORDER BY age_hours DESC;

-- Approved vs Rejected (last 30 days)
SELECT 
  status,
  COUNT(*) as count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM vw_request_volume_summary
WHERE closure_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status IN ('APPROVED', 'REJECTED')
GROUP BY status;

---

Category 2: TAT Efficiency

KPI Name	Description	SQL View	Primary Users
Average TAT Compliance %	% of workflows completed within TAT	vw_tat_compliance	All
Avg Approval Cycle Time	Average time from creation to closure	vw_request_volume_summary	All
Delayed Workflows	Requests currently breaching TAT	vw_tat_compliance	All

Query Examples:

-- Overall TAT compliance rate
SELECT 
  COUNT(CASE WHEN completed_within_tat = true THEN 1 END) * 100.0 / 
  NULLIF(COUNT(CASE WHEN completed_within_tat IS NOT NULL THEN 1 END), 0) as compliance_rate,
  COUNT(CASE WHEN completed_within_tat = true THEN 1 END) as on_time_count,
  COUNT(CASE WHEN completed_within_tat = false THEN 1 END) as breached_count
FROM vw_tat_compliance;

-- Average cycle time by priority
SELECT 
  priority,
  ROUND(AVG(cycle_time_hours), 2) as avg_hours,
  ROUND(AVG(cycle_time_hours) / 24, 2) as avg_days,
  COUNT(*) as total_requests
FROM vw_request_volume_summary
WHERE closure_date IS NOT NULL
GROUP BY priority;

-- Currently delayed workflows
SELECT 
  request_number,
  approver_name,
  level_number,
  tat_status,
  tat_percentage_used,
  remaining_hours
FROM vw_tat_compliance
WHERE tat_status IN ('CRITICAL', 'BREACHED')
  AND level_status IN ('PENDING', 'IN_PROGRESS')
ORDER BY tat_percentage_used DESC;

---

Category 3: Approver Load

KPI Name	Description	SQL View	Primary Users
Pending Actions (My Queue)	Requests awaiting user approval	vw_approver_performance	Approvers
Approvals Completed	Count of actions in timeframe	vw_approver_performance	Approvers

Query Examples:

-- My pending queue (for specific approver)
SELECT 
  pending_count,
  in_progress_count,
  oldest_pending_hours
FROM vw_approver_performance
WHERE approver_id = 'USER_ID_HERE';

-- Approvals completed today
SELECT 
  approver_name,
  COUNT(*) as approvals_today
FROM approval_levels
WHERE action_date >= CURRENT_DATE
  AND status IN ('APPROVED', 'REJECTED')
GROUP BY approver_name
ORDER BY approvals_today DESC;

-- Approvals completed this week
SELECT 
  approver_name,
  approved_count,
  rejected_count,
  (approved_count + rejected_count) as total_actions
FROM vw_approver_performance
ORDER BY total_actions DESC;

---

Category 4: Engagement & Quality

KPI Name	Description	SQL View	Primary Users
Comments/Work Notes Added	Collaboration activity	vw_engagement_metrics	All
Attachments Uploaded	Documents added	vw_engagement_metrics	All

Query Examples:

-- Engagement metrics summary
SELECT 
  engagement_level,
  COUNT(*) as requests_count,
  AVG(work_notes_count) as avg_comments,
  AVG(documents_count) as avg_documents
FROM vw_engagement_metrics
GROUP BY engagement_level;

-- Most active requests (by comments)
SELECT 
  request_number,
  title,
  work_notes_count,
  documents_count,
  spectators_count
FROM vw_engagement_metrics
ORDER BY work_notes_count DESC
LIMIT 10;

-- Document upload trends (last 7 days)
SELECT 
  DATE(uploaded_at) as date,
  COUNT(*) as documents_uploaded
FROM documents
WHERE uploaded_at >= CURRENT_DATE - INTERVAL '7 days'
  AND is_deleted = false
GROUP BY DATE(uploaded_at)
ORDER BY date DESC;

---

📈 Analytical Reports

1. Request Lifecycle Report

Purpose: End-to-end status with timeline, approvers, and TAT compliance

SELECT 
  w.request_number,
  w.title,
  w.status,
  w.priority,
  w.submission_date,
  w.closure_date,
  w.cycle_time_hours / 24 as cycle_days,
  al.level_number,
  al.approver_name,
  al.status as level_status,
  al.completed_within_tat,
  al.elapsed_hours,
  al.tat_hours as allocated_hours,
  ta.threshold_percentage as last_alert_threshold,
  ta.alert_sent_at as last_alert_time
FROM vw_request_volume_summary w
LEFT JOIN vw_tat_compliance al ON w.request_id = al.request_id
LEFT JOIN vw_tat_alerts_summary ta ON al.level_id = ta.level_id
WHERE w.request_number = 'REQ-YYYY-NNNNN'
ORDER BY al.level_number;

Export: Can be exported as CSV using \copy or application-level export

---

2. Approver Performance Report

Purpose: Track response time, pending count, TAT compliance by approver

SELECT 
  ap.approver_name,
  ap.department,
  ap.pending_count,
  ap.approved_count,
  ap.rejected_count,
  ROUND(ap.avg_response_time_hours, 2) as avg_response_hours,
  ROUND(ap.tat_compliance_percentage, 2) as compliance_percent,
  ap.breaches_count,
  ROUND(ap.oldest_pending_hours, 2) as oldest_pending_hours
FROM vw_approver_performance ap
WHERE ap.total_assignments > 0
ORDER BY ap.tat_compliance_percentage DESC;

Visualization: Bar chart or leaderboard

---

3. Department-wise Workflow Summary

Purpose: Compare requests by department

SELECT 
  department,
  total_requests,
  open_requests,
  approved_requests,
  rejected_requests,
  ROUND(approved_requests * 100.0 / NULLIF(total_requests, 0), 2) as approval_rate,
  ROUND(avg_cycle_time_hours / 24, 2) as avg_cycle_days,
  express_priority_count,
  standard_priority_count
FROM vw_department_summary
WHERE department IS NOT NULL
ORDER BY total_requests DESC;

Visualization: Pie chart or stacked bar chart

---

4. TAT Breach Report

Purpose: List all requests that breached TAT with reasons

SELECT 
  ta.request_number,
  ta.request_title,
  ta.priority,
  ta.level_number,
  u.display_name as approver_name,
  ta.threshold_percentage,
  ta.alert_sent_at,
  ta.expected_completion_time,
  ta.completion_time,
  ta.was_completed_on_time,
  CASE
    WHEN ta.completion_time IS NULL THEN 'Still Pending'
    WHEN ta.was_completed_on_time = false THEN 'Completed Late'
    ELSE 'Completed On Time'
  END as status,
  ta.response_time_after_alert_hours
FROM vw_tat_alerts_summary ta
LEFT JOIN users u ON ta.approver_id = u.user_id
WHERE ta.is_breached = true
ORDER BY ta.alert_sent_at DESC;

Visualization: Table with filters

---

5. Priority Distribution Report

Purpose: Express vs Standard workflows and cycle times

SELECT 
  priority,
  COUNT(*) as total_requests,
  COUNT(CASE WHEN status_category = 'IN_PROGRESS' THEN 1 END) as open_requests,
  COUNT(CASE WHEN status_category = 'COMPLETED' THEN 1 END) as completed_requests,
  ROUND(AVG(CASE WHEN closure_date IS NOT NULL THEN cycle_time_hours END), 2) as avg_cycle_hours,
  ROUND(AVG(CASE WHEN closure_date IS NOT NULL THEN cycle_time_hours / 24 END), 2) as avg_cycle_days
FROM vw_request_volume_summary
GROUP BY priority;

Visualization: Pie chart + KPI cards

---

6. Workflow Aging Report

Purpose: Workflows open beyond threshold

SELECT 
  request_number,
  title,
  age_days,
  age_category,
  current_approver,
  current_level_age_hours,
  current_level_tat_hours,
  current_level_tat_used
FROM vw_workflow_aging
WHERE age_category IN ('AGING', 'CRITICAL')
ORDER BY age_days DESC;

Visualization: Table with age color-coding

---

7. Daily/Weekly Trends

Purpose: Track volume and performance trends

-- Daily KPIs for last 30 days
SELECT 
  date,
  requests_created,
  requests_submitted,
  requests_closed,
  requests_approved,
  requests_rejected,
  ROUND(avg_completion_time_hours, 2) as avg_completion_hours
FROM vw_daily_kpi_metrics
WHERE date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY date DESC;

-- Weekly aggregation
SELECT 
  DATE_TRUNC('week', date) as week_start,
  SUM(requests_created) as weekly_created,
  SUM(requests_closed) as weekly_closed,
  ROUND(AVG(avg_completion_time_hours), 2) as avg_completion_hours
FROM vw_daily_kpi_metrics
WHERE date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE_TRUNC('week', date)
ORDER BY week_start DESC;

Visualization: Line chart or area chart

---

🔍 TAT Alerts - Display in UI

Get TAT Alerts for a Request

-- For displaying in Request Detail screen (like the image shared)
SELECT 
  ta.alert_type,
  ta.threshold_percentage,
  ta.alert_sent_at,
  ta.alert_message,
  ta.tat_hours_elapsed,
  ta.tat_hours_remaining,
  ta.notification_sent,
  CASE
    WHEN ta.alert_type = 'TAT_50' THEN '⏳ 50% of TAT elapsed'
    WHEN ta.alert_type = 'TAT_75' THEN '⚠️ 75% of TAT elapsed - Escalation warning'
    WHEN ta.alert_type = 'TAT_100' THEN '⏰ TAT breached - Immediate action required'
  END as alert_title
FROM tat_alerts ta
WHERE ta.request_id = 'REQUEST_ID_HERE'
  AND ta.level_id = 'LEVEL_ID_HERE'
ORDER BY ta.created_at ASC;

Display Format (like image):

Reminder 1
⏳ 50% of SLA breach reminder have been sent
Reminder sent by system automatically
Sent at: Oct 6 at 2:30 PM

---

📊 KPI Dashboard Queries

Executive Dashboard

-- Overall KPIs for dashboard cards
SELECT 
  (SELECT COUNT(*) FROM vw_request_volume_summary WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)) as requests_this_month,
  (SELECT COUNT(*) FROM vw_request_volume_summary WHERE status_category = 'IN_PROGRESS') as open_requests,
  (SELECT ROUND(AVG(cycle_time_hours / 24), 2) FROM vw_request_volume_summary WHERE closure_date IS NOT NULL) as avg_cycle_days,
  (SELECT ROUND(COUNT(CASE WHEN completed_within_tat = true THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) FROM vw_tat_compliance WHERE completed_within_tat IS NOT NULL) as tat_compliance_percent;

---

🚀 API Endpoint Examples

Example Service Method (TypeScript)

// services/kpi.service.ts

export class KPIService {
  /**
   * Get Request Volume Summary
   */
  async getRequestVolumeSummary(startDate: string, endDate: string) {
    const query = `
      SELECT 
        status_category,
        COUNT(*) as count
      FROM vw_request_volume_summary
      WHERE created_at BETWEEN :startDate AND :endDate
      GROUP BY status_category
    `;
    
    return await sequelize.query(query, {
      replacements: { startDate, endDate },
      type: QueryTypes.SELECT
    });
  }

  /**
   * Get TAT Compliance Rate
   */
  async getTATComplianceRate(period: 'daily' | 'weekly' | 'monthly') {
    const query = `
      SELECT 
        COUNT(CASE WHEN completed_within_tat = true THEN 1 END) * 100.0 / 
        NULLIF(COUNT(*), 0) as compliance_rate
      FROM vw_tat_compliance
      WHERE action_date >= NOW() - INTERVAL '1 ${period}'
    `;
    
    return await sequelize.query(query, { type: QueryTypes.SELECT });
  }

  /**
   * Get TAT Alerts for Request
   */
  async getTATAlertsForRequest(requestId: string) {
    return await TatAlert.findAll({
      where: { requestId },
      order: [['alertSentAt', 'ASC']],
      include: [
        { model: ApprovalLevel, as: 'level' },
        { model: User, as: 'approver' }
      ]
    });
  }
}

---

📋 Maintenance & Performance

Indexes

All views use indexed columns for optimal performance:

• request_id, level_id, approver_id
• status, created_at, alert_sent_at
• is_deleted (for soft deletes)

Refresh Materialized Views (if needed)

If you convert views to materialized views for better performance:

-- Refresh all materialized views
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_request_volume_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_tat_compliance;
-- etc.

---

📖 Related Documentation

• TAT Notification System: TAT_NOTIFICATION_SYSTEM.md
• Database Structure: backend_structure.txt
• API Documentation: API_DOCUMENTATION.md

---

Last Updated: November 4, 2025
Version: 1.0.0
Maintained By: Royal Enfield Workflow Team