Compliance Monitoring Dashboard
Document ID: CODITECT-BIO-COMP-DASH-001 Version: 1.0.0 Effective Date: 2026-02-16 Classification: Internal - Restricted Owner: Chief Compliance Officer (CCO) / VP Quality Assurance
Document Control
Approval History
| Role | Name | Signature | Date |
|---|---|---|---|
| Chief Compliance Officer | [Pending] | [Digital Signature] | YYYY-MM-DD |
| VP Quality Assurance | [Pending] | [Digital Signature] | YYYY-MM-DD |
| VP Engineering | [Pending] | [Digital Signature] | YYYY-MM-DD |
| Chief Information Security Officer | [Pending] | [Digital Signature] | YYYY-MM-DD |
| Chief Technology Officer | [Pending] | [Digital Signature] | YYYY-MM-DD |
Revision History
| Version | Date | Author | Changes | Approval Status |
|---|---|---|---|---|
| 1.0.0 | 2026-02-16 | Compliance Team | Initial release | Draft |
Distribution List
- Executive Leadership Team
- Quality Assurance Team
- Compliance Team
- Regulatory Affairs
- Information Security Team
- Internal Audit
- External SOC 2 Auditor (during audit engagement)
- External FDA Auditor (during inspection)
- Engineering Leadership
- Department Heads
Review Schedule
| Review Type | Frequency | Next Review Date | Responsible Party |
|---|---|---|---|
| Annual Review | 12 months | 2027-02-16 | CCO |
| Quarterly Metrics Review | 3 months | 2026-05-16 | VP Quality |
| Post-Audit Review | After each external audit | N/A | CCO + VP Quality |
| KPI Threshold Review | 6 months | 2026-08-16 | Compliance Team |
| Technology Stack Review | 12 months | 2027-02-16 | VP Engineering |
Table of Contents
- Executive Summary
- Regulatory Context
- Dashboard Architecture
- Key Metrics
- KPI Trends
- Alert System
- Multi-Framework View
- Role-Based Views
- Technical Implementation
- Database Schema
- React Components
- WebSocket Events
- Export Capabilities
- Maintenance & Operations
1. Executive Summary
Purpose
The BIO-QMS Compliance Monitoring Dashboard provides real-time visibility into regulatory compliance status across FDA 21 CFR Part 11, HIPAA, and SOC 2 frameworks for a multi-tenant biomedical/pharmaceutical QMS SaaS platform. This dashboard serves as the central command center for quality management, compliance officers, and executive leadership to:
- Monitor ongoing compliance health in real-time
- Identify and escalate compliance gaps before they become regulatory findings
- Track CAPA effectiveness and deviation trends
- Ensure training compliance across all departments and roles
- Maintain audit readiness through continuous monitoring
- Provide evidence for external audits and regulatory inspections
Key Features
- Real-Time Monitoring: WebSocket-based live updates for critical metrics
- Multi-Framework Coverage: Unified view across FDA, HIPAA, and SOC 2 requirements
- Role-Based Access: Customized dashboards for 5 stakeholder personas
- Automated Alerting: Threshold-based alerts with escalation chains
- Trend Analysis: Historical KPI tracking over 30/60/90/180/365 day periods
- Export Capabilities: PDF compliance reports and CSV data exports
- Audit Trail: Complete activity log for dashboard access and alert acknowledgments
Regulatory Alignment
| Framework | Primary Controls | Dashboard Coverage |
|---|---|---|
| FDA 21 CFR Part 11 | §11.10(e) Audit Trail, §11.10(g) Authority Checks | CAPA tracking, E-signature compliance, Validation status |
| HIPAA | §164.312(b) Audit Controls, §164.308(a)(8) Evaluation | Access log monitoring, Training compliance, Incident tracking |
| SOC 2 | CC7.2 System Monitoring, CC7.3 Anomaly Evaluation | Control effectiveness, Security metrics, Availability KPIs |
2. Regulatory Context
FDA 21 CFR Part 11 Requirements
§11.10(e) - Audit Trail Documentation
The dashboard provides real-time visibility into system audit trails, ensuring:
- All CAPA actions are documented with timestamps and user IDs
- E-signature events are tracked with meaning, date, and signer
- Validation activities (IQ/OQ/PQ) have complete evidence chains
- Document lifecycle events are captured and reportable
§11.10(g) - Authority Checks
Compliance metrics demonstrate:
- Approval workflows are followed per established procedures
- Only authorized personnel perform critical quality actions
- Role-based access controls are enforced and monitored
- Exceptions are logged and reviewed
§11.200 - Electronic Signature Components
Dashboard tracks:
- E-signature usage rates across document types
- Signature binding integrity (cryptographic validation status)
- Re-authentication compliance for critical actions
- Signature manifestation completeness (name, date, meaning)
HIPAA Requirements
§164.312(b) - Audit Controls
Dashboard provides:
- Access log aggregation and anomaly detection
- ePHI access patterns by user and department
- Failed access attempts and security violations
- Audit log retention compliance status
§164.308(a)(5) - Security Awareness Training
Training compliance metrics include:
- Completion rates per department and role
- Overdue training assignments with escalation
- Training effectiveness tracking (quiz scores, re-training triggers)
- Annual security training compliance percentage
§164.308(a)(8) - Evaluation
Dashboard demonstrates:
- Continuous monitoring of HIPAA control effectiveness
- Regular security posture assessments
- Incident response metrics (detection time, resolution time)
- Risk management KPIs
SOC 2 Trust Service Criteria
CC7.2 - System Monitoring
Dashboard monitors:
- System availability and performance metrics
- Security control effectiveness scores
- Incident detection and response times
- Change management compliance rates
CC7.3 - Anomaly Evaluation
Alert system provides:
- Real-time detection of control failures
- Threshold-based alerts for KPI deviations
- Escalation workflows for critical findings
- Root cause analysis linkage
CC9.1 - Risk Management
Risk metrics include:
- Open risk assessments by severity
- Mitigation plan implementation status
- Risk trend analysis (increasing/decreasing risk exposure)
- Third-party risk monitoring
3. Dashboard Architecture
System Context
┌─────────────────────────────────────────────────────────────────┐
│ Compliance Monitoring Dashboard │
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Quality │ │ Compliance │ │ Executive │ │
│ │ Manager │ │ Officer │ │ Leadership │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │ │ │
│ └─────────────────┴─────────────────┘ │
│ │ │
│ ┌────────────▼────────────┐ │
│ │ React Dashboard Layer │ │
│ │ - Role-Based Views │ │
│ │ - Real-Time Updates │ │
│ │ - Interactive Charts │ │
│ └────────────┬────────────┘ │
│ │ │
│ ┌────────────▼────────────┐ │
│ │ WebSocket Gateway │ │
│ │ - Event Streaming │ │
│ │ - Alert Delivery │ │
│ └────────────┬────────────┘ │
│ │ │
│ ┌─────────────────┴─────────────────┐ │
│ │ │ │
│ ┌──────▼───────┐ ┌────────▼────────┐ │
│ │ Metrics API │ │ Alert Service │ │
│ │ - KPI Calc │ │ - Thresholds │ │
│ │ - Trends │ │ - Escalation │ │
│ └──────┬───────┘ └────────┬────────┘ │
│ │ │ │
│ └─────────────────┬─────────────────┘ │
│ │ │
│ ┌────────────▼────────────┐ │
│ │ PostgreSQL Database │ │
│ │ - Materialized Views │ │
│ │ - Partitioned Tables │ │
│ │ - Redis Cache Layer │ │
│ └─────────────────────────┘ │
│ │
│ Data Sources: │
│ - CAPA System │
│ - Deviation Management │
│ - Training LMS │
│ - Audit Management │
│ - Document Control │
│ - Validation System │
└─────────────────────────────────────────────────────────────────┘
Technology Stack
| Layer | Technology | Purpose |
|---|---|---|
| Frontend | React 18.2+ with TypeScript | Dashboard UI components |
| State Management | Redux Toolkit + RTK Query | State + real-time data fetching |
| Charting | Recharts 2.5+ | KPI visualization |
| WebSocket | Socket.io 4.6+ | Real-time event streaming |
| Backend API | Django REST Framework | Metrics API endpoints |
| Background Workers | Celery + Redis | Metric computation jobs |
| Database | PostgreSQL 15+ | Compliance data storage |
| Cache | Redis 7+ | Dashboard data caching (5 min TTL) |
| Export | ReportLab (PDF), Pandas (CSV) | Report generation |
High-Level Architecture
┌─────────────────────────────────────────────────────────────────┐
│ Frontend Layer │
│ │
│ React Dashboard Components │
│ - ComplianceOverview │
│ - CapaTracker │
│ - DeviationMonitor │
│ - TrainingComplianceView │
│ - AuditFindingsView │
│ - MultiFrameworkHeatmap │
│ - ExecutiveScorecard │
│ │
└────────────────────────┬────────────────────────────────────────┘
│
│ HTTPS + WebSocket
│
┌────────────────────────▼────────────────────────────────────────┐
│ API Gateway │
│ │
│ REST API Endpoints: │
│ - /api/compliance/metrics/ │
│ - /api/compliance/trends/ │
│ - /api/compliance/alerts/ │
│ - /api/compliance/export/ │
│ │
│ WebSocket Events: │
│ - metric:update │
│ - alert:new │
│ - alert:acknowledged │
│ - kpi:threshold_breach │
│ │
└────────────────────────┬────────────────────────────────────────┘
│
┌───────────────┴───────────────┐
│ │
┌────────▼─────────┐ ┌──────────▼──────────┐
│ Metrics Service │ │ Alert Service │
│ │ │ │
│ - KPI Calculator│ │ - Threshold Check │
│ - Trend Analysis│ │ - Escalation Logic │
│ - Aggregation │ │ - Notification │
└────────┬─────────┘ └──────────┬──────────┘
│ │
└───────────────┬───────────────┘
│
┌────────────────────────▼────────────────────────────────────────┐
│ PostgreSQL Database │
│ │
│ Tables: Materialized Views: │
│ - compliance_capa - mv_compliance_summary │
│ - compliance_deviation - mv_capa_aging │
│ - compliance_training - mv_deviation_trends │
│ - compliance_audit_finding - mv_training_compliance │
│ - compliance_alert - mv_audit_findings_summary │
│ - compliance_kpi_history - mv_framework_compliance │
│ │
│ Partitioning: │
│ - By tenant_id (RLS isolation) │
│ - By date range (performance) │
│ │
└────────────────────────┬────────────────────────────────────────┘
│
│ Refresh Jobs
│
┌────────────────────────▼────────────────────────────────────────┐
│ Background Workers │
│ │
│ Celery Tasks: │
│ - refresh_compliance_metrics() Every 5 minutes │
│ - calculate_trend_data() Every 15 minutes │
│ - check_alert_thresholds() Every 1 minute │
│ - generate_daily_snapshot() Daily at 00:00 UTC │
│ - archive_old_metrics() Weekly │
│ │
└─────────────────────────────────────────────────────────────────┘
Data Flow
-
Metric Collection:
- Background workers query source systems (CAPA, Deviation, Training, etc.)
- Raw data is aggregated and normalized
- Results stored in database tables + Redis cache
-
Real-Time Updates:
- Database triggers detect changes to compliance tables
- Notifications sent to WebSocket gateway via Redis pub/sub
- Connected clients receive updates via Socket.io
-
Alert Generation:
- Threshold check worker runs every minute
- Compares current metrics against configured thresholds
- Creates alert records and triggers notification cascade
-
Dashboard Rendering:
- React components subscribe to WebSocket events
- Initial data loaded from REST API (cached via Redis)
- Live updates applied via Redux state management
4. Key Metrics
4.1 CAPA Metrics
4.1.1 Open CAPAs with Aging
Purpose: Track all open Corrective and Preventive Actions with visibility into how long each has been open.
Calculation:
-- Materialized view: mv_capa_aging
SELECT
tenant_id,
capa_id,
capa_number,
title,
severity,
assigned_to,
status,
created_at,
due_date,
CURRENT_DATE - created_at::date AS age_days,
CASE
WHEN CURRENT_DATE - created_at::date > 90 THEN 'red'
WHEN CURRENT_DATE - created_at::date > 30 THEN 'yellow'
ELSE 'green'
END AS aging_status
FROM compliance_capa
WHERE status IN ('open', 'in_progress', 'under_review')
AND tenant_id = %(tenant_id)s
ORDER BY age_days DESC;
Display:
- Total Count: Badge with red/yellow/green color based on aging
- Aging Distribution: Bar chart (0-30 days, 31-60 days, 61-90 days, >90 days)
- Top 10 Oldest: Table with CAPA number, title, assigned to, age, severity
Alert Thresholds:
- Yellow: Any CAPA open > 30 days
- Red: Any CAPA open > 60 days
- Critical: Critical severity CAPA > 14 days
4.1.2 CAPA Cycle Time Trends
Purpose: Monitor CAPA resolution efficiency over time.
Calculation:
-- Average CAPA cycle time by month
SELECT
tenant_id,
DATE_TRUNC('month', closed_at) AS month,
AVG(closed_at - created_at) AS avg_cycle_time,
COUNT(*) AS capas_closed,
AVG(CASE WHEN severity = 'critical' THEN closed_at - created_at END) AS avg_critical_cycle_time
FROM compliance_capa
WHERE status = 'closed'
AND closed_at >= CURRENT_DATE - INTERVAL '12 months'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, month
ORDER BY month DESC;
Display:
- Line Chart: Avg cycle time per month (last 12 months)
- Target Line: Overlay target cycle time (configurable per tenant)
- Severity Breakdown: Separate lines for critical/major/minor
Alert Thresholds:
- Yellow: Monthly avg exceeds target by 20%
- Red: Monthly avg exceeds target by 50%
4.2 Deviation Metrics
4.2.1 Overdue Deviations Count and Severity Breakdown
Purpose: Track deviations past their due date for investigation or CAPA linkage.
Calculation:
-- Overdue deviations summary
SELECT
tenant_id,
severity,
COUNT(*) AS count,
AVG(CURRENT_DATE - due_date::date) AS avg_overdue_days
FROM compliance_deviation
WHERE status IN ('open', 'under_investigation')
AND due_date < CURRENT_DATE
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, severity
ORDER BY
CASE severity
WHEN 'critical' THEN 1
WHEN 'major' THEN 2
WHEN 'minor' THEN 3
END;
Display:
- Severity Breakdown: Donut chart (Critical, Major, Minor)
- Overdue Days: Heatmap showing deviation ID vs days overdue
- Recent Additions: List of deviations that became overdue in last 7 days
Alert Thresholds:
- Yellow: Any major deviation overdue > 7 days
- Red: Any critical deviation overdue > 3 days
- Critical: Critical deviation overdue > 7 days (escalate to VP Quality)
4.2.2 Deviation Recurrence Rates
Purpose: Identify systemic issues through recurring deviations.
Calculation:
-- Deviations grouped by root cause category
WITH deviation_groups AS (
SELECT
tenant_id,
root_cause_category,
COUNT(*) AS occurrences,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '90 days') AS recent_occurrences,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '180 days'
AND created_at < CURRENT_DATE - INTERVAL '90 days') AS prior_occurrences
FROM compliance_deviation
WHERE status IN ('closed', 'resolved')
AND root_cause_category IS NOT NULL
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, root_cause_category
)
SELECT
tenant_id,
root_cause_category,
occurrences,
recent_occurrences,
prior_occurrences,
CASE
WHEN prior_occurrences > 0
THEN ((recent_occurrences - prior_occurrences)::float / prior_occurrences * 100)
ELSE NULL
END AS recurrence_rate_change_pct
FROM deviation_groups
WHERE occurrences >= 3 -- Only show categories with 3+ occurrences
ORDER BY recent_occurrences DESC;
Display:
- Top Recurring Causes: Bar chart (top 10 root cause categories)
- Trend Indicator: Arrow showing increase/decrease vs prior period
- Recurrence Rate: Percentage badge (green < 5%, yellow 5-15%, red > 15%)
Alert Thresholds:
- Yellow: Same root cause appears 3+ times in 90 days
- Red: Same root cause appears 5+ times in 90 days
4.3 Training Compliance Metrics
4.3.1 Training Compliance % by Department and Role
Purpose: Ensure all personnel have current training for their roles.
Calculation:
-- Training compliance by department and role
SELECT
tenant_id,
department,
role,
COUNT(*) AS total_employees,
COUNT(*) FILTER (WHERE training_current = TRUE) AS compliant_employees,
(COUNT(*) FILTER (WHERE training_current = TRUE)::float / COUNT(*) * 100) AS compliance_pct,
COUNT(*) FILTER (WHERE training_overdue_days > 0) AS overdue_count,
AVG(CASE WHEN training_overdue_days > 0 THEN training_overdue_days END) AS avg_overdue_days
FROM compliance_training_status
WHERE tenant_id = %(tenant_id)s
AND employee_status = 'active'
GROUP BY tenant_id, department, role
ORDER BY compliance_pct ASC;
Display:
- Heatmap: Department (Y-axis) vs Role (X-axis), color by compliance %
- Target Line: 100% compliance target
- Drill-Down: Click cell to see individual employees with overdue training
Alert Thresholds:
- Yellow: Department/role drops below 95% compliance
- Red: Department/role drops below 90% compliance
- Critical: Any individual overdue > 60 days on required training
4.3.2 Training Completion Velocity
Purpose: Monitor training assignment completion rates to identify bottlenecks.
Calculation:
-- Training completion velocity (assignments completed per week)
SELECT
tenant_id,
DATE_TRUNC('week', completed_at) AS week,
COUNT(*) AS completions,
COUNT(*) FILTER (WHERE completed_at <= due_date) AS on_time_completions,
(COUNT(*) FILTER (WHERE completed_at <= due_date)::float / COUNT(*) * 100) AS on_time_pct
FROM compliance_training_assignment
WHERE status = 'completed'
AND completed_at >= CURRENT_DATE - INTERVAL '26 weeks'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, week
ORDER BY week DESC;
Display:
- Line Chart: Completions per week (last 26 weeks)
- On-Time Rate: Overlay line showing % completed by due date
- Forecast: Projected completions for next 4 weeks based on trend
Alert Thresholds:
- Yellow: Weekly completions < 80% of rolling average
- Red: Weekly completions < 60% of rolling average
4.4 Audit Findings Metrics
4.4.1 Open Audit Findings by Severity
Purpose: Track external and internal audit findings through closure.
Calculation:
-- Audit findings summary
SELECT
tenant_id,
audit_type, -- 'internal', 'external_fda', 'external_soc2', 'external_hipaa'
severity, -- 'critical', 'major', 'minor', 'observation'
status, -- 'open', 'in_progress', 'under_review', 'closed'
COUNT(*) AS count,
AVG(CURRENT_DATE - finding_date::date) AS avg_age_days
FROM compliance_audit_finding
WHERE status IN ('open', 'in_progress', 'under_review')
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, audit_type, severity, status
ORDER BY
CASE audit_type
WHEN 'external_fda' THEN 1
WHEN 'external_hipaa' THEN 2
WHEN 'external_soc2' THEN 3
WHEN 'internal' THEN 4
END,
CASE severity
WHEN 'critical' THEN 1
WHEN 'major' THEN 2
WHEN 'minor' THEN 3
WHEN 'observation' THEN 4
END;
Display:
- Stacked Bar Chart: Audit type (X-axis), count (Y-axis), stacked by severity
- Status Breakdown: Donut chart showing open/in-progress/under-review
- Aging Grid: Matrix of finding ID vs days open, colored by severity
Alert Thresholds:
- Yellow: Critical finding > 7 days open
- Red: Critical finding > 14 days open
- Critical: FDA critical finding > 30 days open (immediate escalation)
4.4.2 Audit Finding Resolution Rates
Purpose: Measure effectiveness of corrective action implementation.
Calculation:
-- Audit finding resolution rates by quarter
SELECT
tenant_id,
DATE_TRUNC('quarter', closed_at) AS quarter,
audit_type,
COUNT(*) AS findings_closed,
AVG(closed_at - finding_date) AS avg_resolution_time,
COUNT(*) FILTER (WHERE closed_at <= target_closure_date) AS closed_on_time,
(COUNT(*) FILTER (WHERE closed_at <= target_closure_date)::float / COUNT(*) * 100) AS on_time_pct
FROM compliance_audit_finding
WHERE status = 'closed'
AND closed_at >= CURRENT_DATE - INTERVAL '24 months'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, quarter, audit_type
ORDER BY quarter DESC, audit_type;
Display:
- Trend Chart: Quarterly resolution time (line per audit type)
- On-Time Rate: Bar chart showing % closed by target date
- Comparison: Current quarter vs same quarter prior year
Alert Thresholds:
- Yellow: Quarterly on-time rate < 85%
- Red: Quarterly on-time rate < 70%
4.5 Document Review Metrics
4.5.1 Document Review Cycle Times
Purpose: Monitor document approval workflow efficiency.
Calculation:
-- Document review cycle times by document type
SELECT
tenant_id,
document_type,
COUNT(*) AS documents_reviewed,
AVG(approved_at - submitted_for_review_at) AS avg_review_time,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY approved_at - submitted_for_review_at) AS median_review_time,
MAX(approved_at - submitted_for_review_at) AS max_review_time
FROM compliance_document_review
WHERE status = 'approved'
AND approved_at >= CURRENT_DATE - INTERVAL '90 days'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, document_type
ORDER BY avg_review_time DESC;
Display:
- Bar Chart: Document type (X-axis), avg review time in days (Y-axis)
- Target Overlay: Configurable target review time per document type
- Outliers: List of documents exceeding 2x median review time
Alert Thresholds:
- Yellow: Critical document review > 5 days
- Red: Any document review > 14 days
4.6 E-Signature Compliance Metrics
4.6.1 E-Signature Compliance Rate
Purpose: Ensure all required electronic signatures meet 21 CFR Part 11 requirements.
Calculation:
-- E-signature compliance assessment
SELECT
tenant_id,
DATE_TRUNC('month', signed_at) AS month,
COUNT(*) AS total_signatures,
COUNT(*) FILTER (WHERE has_signer_name = TRUE
AND has_signed_at = TRUE
AND has_meaning = TRUE) AS compliant_signatures,
COUNT(*) FILTER (WHERE cryptographic_binding_verified = TRUE) AS cryptographically_bound,
COUNT(*) FILTER (WHERE re_auth_performed = TRUE) AS re_auth_signatures,
(COUNT(*) FILTER (WHERE has_signer_name = TRUE
AND has_signed_at = TRUE
AND has_meaning = TRUE)::float / COUNT(*) * 100) AS compliance_rate
FROM compliance_electronic_signature
WHERE signed_at >= CURRENT_DATE - INTERVAL '12 months'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, month
ORDER BY month DESC;
Display:
- Compliance Rate Trend: Line chart showing monthly compliance %
- Component Breakdown: Stacked bar showing presence of name/date/meaning
- Re-Auth Rate: Separate line for critical action re-authentication
Alert Thresholds:
- Yellow: Monthly compliance rate < 98%
- Red: Any signature missing required component (immediate remediation)
4.7 System Validation Status
4.7.1 Validation Status (IQ/OQ/PQ)
Purpose: Track system validation lifecycle for FDA compliance.
Calculation:
-- System validation status summary
SELECT
tenant_id,
system_name,
validation_phase, -- 'IQ', 'OQ', 'PQ', 'revalidation'
status, -- 'not_started', 'in_progress', 'completed', 'approved'
scheduled_date,
completed_date,
next_revalidation_date,
CASE
WHEN status = 'approved' AND next_revalidation_date < CURRENT_DATE + INTERVAL '90 days' THEN 'due_soon'
WHEN status = 'approved' AND next_revalidation_date < CURRENT_DATE THEN 'overdue'
WHEN status IN ('not_started', 'in_progress') AND scheduled_date < CURRENT_DATE THEN 'delayed'
ELSE 'on_track'
END AS validation_health
FROM compliance_system_validation
WHERE tenant_id = %(tenant_id)s
ORDER BY
CASE validation_health
WHEN 'overdue' THEN 1
WHEN 'due_soon' THEN 2
WHEN 'delayed' THEN 3
WHEN 'on_track' THEN 4
END,
next_revalidation_date ASC;
Display:
- System Grid: Table with system name, phase, status, health indicator
- Revalidation Timeline: Gantt chart showing upcoming revalidations
- Validation Coverage: Pie chart (IQ/OQ/PQ complete vs in-progress vs not started)
Alert Thresholds:
- Yellow: Revalidation due in < 60 days
- Red: Revalidation overdue by any amount
- Critical: Production system running without approved validation
5. KPI Trends
5.1 Trend Period Configuration
All KPI trend charts support the following time periods (configurable via UI):
- 30 Days: Daily data points
- 60 Days: Daily data points
- 90 Days: Weekly aggregation
- 180 Days: Weekly aggregation
- 365 Days: Monthly aggregation
5.2 Trend Metrics
5.2.1 CAPA Cycle Time Trends
SQL Query:
-- CAPA cycle time trend with period flexibility
WITH capa_cycle_times AS (
SELECT
tenant_id,
closed_at,
severity,
(closed_at - created_at) AS cycle_time
FROM compliance_capa
WHERE status = 'closed'
AND closed_at >= CURRENT_DATE - INTERVAL '%(days)s days'
AND tenant_id = %(tenant_id)s
)
SELECT
DATE_TRUNC(%(period)s, closed_at) AS period_start, -- 'day', 'week', 'month'
severity,
COUNT(*) AS capas_closed,
AVG(cycle_time) AS avg_cycle_time,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_time) AS median_cycle_time
FROM capa_cycle_times
GROUP BY period_start, severity
ORDER BY period_start DESC, severity;
Chart Type: Multi-line chart
- X-Axis: Time period
- Y-Axis: Average cycle time (days)
- Lines: One per severity level (Critical, Major, Minor)
- Target Band: Shaded area showing acceptable range
5.2.2 Deviation Recurrence Rate Trends
SQL Query:
-- Deviation recurrence rate by root cause over time
WITH deviation_occurrences AS (
SELECT
tenant_id,
DATE_TRUNC(%(period)s, created_at) AS period_start,
root_cause_category,
COUNT(*) AS occurrences
FROM compliance_deviation
WHERE created_at >= CURRENT_DATE - INTERVAL '%(days)s days'
AND tenant_id = %(tenant_id)s
AND root_cause_category IS NOT NULL
GROUP BY tenant_id, period_start, root_cause_category
)
SELECT
period_start,
root_cause_category,
occurrences,
SUM(occurrences) OVER (
PARTITION BY root_cause_category
ORDER BY period_start
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS rolling_4_period_sum
FROM deviation_occurrences
WHERE root_cause_category IN (
-- Top 5 root cause categories
SELECT root_cause_category
FROM compliance_deviation
WHERE tenant_id = %(tenant_id)s
AND root_cause_category IS NOT NULL
GROUP BY root_cause_category
ORDER BY COUNT(*) DESC
LIMIT 5
)
ORDER BY period_start DESC, occurrences DESC;
Chart Type: Area chart with rolling average
- X-Axis: Time period
- Y-Axis: Occurrence count
- Areas: Stacked by top 5 root cause categories
- Overlay Line: Rolling 4-period average for each category
5.2.3 Training Completion Velocity Trends
SQL Query:
-- Training completion velocity with forecast
WITH completion_velocity AS (
SELECT
tenant_id,
DATE_TRUNC('week', completed_at) AS week,
COUNT(*) AS completions,
COUNT(*) FILTER (WHERE completed_at <= due_date) AS on_time_completions
FROM compliance_training_assignment
WHERE status = 'completed'
AND completed_at >= CURRENT_DATE - INTERVAL '%(days)s days'
AND tenant_id = %(tenant_id)s
GROUP BY tenant_id, week
)
SELECT
week,
completions,
on_time_completions,
(on_time_completions::float / completions * 100) AS on_time_pct,
AVG(completions) OVER (
ORDER BY week
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
) AS rolling_4_week_avg
FROM completion_velocity
ORDER BY week DESC;
Chart Type: Combination chart
- Bar Chart: Weekly completions
- Line Chart: On-time completion %
- Line Chart: Rolling 4-week average (forecast indicator)
5.2.4 Audit Finding Resolution Rate Trends
SQL Query:
-- Audit finding resolution rate by audit type over time
SELECT
DATE_TRUNC(%(period)s, closed_at) AS period_start,
audit_type,
COUNT(*) AS findings_closed,
AVG(closed_at - finding_date) AS avg_resolution_days,
(COUNT(*) FILTER (WHERE closed_at <= target_closure_date)::float / COUNT(*) * 100) AS on_time_pct
FROM compliance_audit_finding
WHERE status = 'closed'
AND closed_at >= CURRENT_DATE - INTERVAL '%(days)s days'
AND tenant_id = %(tenant_id)s
GROUP BY period_start, audit_type
ORDER BY period_start DESC, audit_type;
Chart Type: Multi-line chart with dual Y-axis
- Primary Y-Axis: Average resolution days
- Secondary Y-Axis: On-time percentage
- Lines: One per audit type (Internal, FDA, HIPAA, SOC 2)
5.2.5 Regulatory Inspection Readiness Score Trends
Purpose: Composite score indicating overall audit readiness.
Calculation:
-- Regulatory inspection readiness score (weighted composite)
WITH component_scores AS (
SELECT
tenant_id,
DATE_TRUNC('week', calculation_date) AS week,
-- Component 1: CAPA Health (30% weight)
(100 - (COUNT(*) FILTER (WHERE capa_status IN ('open', 'in_progress')
AND CURRENT_DATE - capa_created_at::date > 60)::float
/ NULLIF(COUNT(*) FILTER (WHERE capa_status IN ('open', 'in_progress')), 0) * 100)) * 0.30 AS capa_score,
-- Component 2: Training Compliance (25% weight)
(SELECT AVG(compliance_pct) FROM mv_training_compliance WHERE tenant_id = %(tenant_id)s) * 0.25 AS training_score,
-- Component 3: Audit Findings (25% weight)
(100 - (COUNT(*) FILTER (WHERE audit_finding_status IN ('open', 'in_progress')
AND audit_finding_severity IN ('critical', 'major'))::float
/ NULLIF(COUNT(*) FILTER (WHERE audit_finding_status IN ('open', 'in_progress')), 0) * 100)) * 0.25 AS audit_score,
-- Component 4: Document/Validation Current (20% weight)
(SELECT AVG(CASE WHEN next_revalidation_date > CURRENT_DATE THEN 100 ELSE 0 END)
FROM compliance_system_validation WHERE tenant_id = %(tenant_id)s) * 0.20 AS validation_score
FROM (
SELECT tenant_id, CURRENT_DATE AS calculation_date FROM tenants WHERE tenant_id = %(tenant_id)s
) AS tenant_dates
LEFT JOIN compliance_capa ON compliance_capa.tenant_id = tenant_dates.tenant_id
LEFT JOIN compliance_audit_finding ON compliance_audit_finding.tenant_id = tenant_dates.tenant_id
GROUP BY tenant_dates.tenant_id, calculation_date
)
SELECT
week,
capa_score + training_score + audit_score + validation_score AS readiness_score,
capa_score,
training_score,
audit_score,
validation_score
FROM component_scores
ORDER BY week DESC;
Chart Type: Stacked area chart
- X-Axis: Week
- Y-Axis: Score (0-100)
- Areas: Stacked by component (CAPA, Training, Audit, Validation)
- Target Line: 85 (minimum acceptable readiness)
6. Alert System
6.1 Alert Severity Levels
| Severity | Color | Response Time | Escalation |
|---|---|---|---|
| Info | Blue | None required | None |
| Warning | Yellow | Review within 24 hours | Team Lead (if not acknowledged) |
| Error | Orange | Review within 4 hours | Quality Manager (if not acknowledged) |
| Critical | Red | Immediate review | Compliance Officer + VP Quality (immediate escalation) |
6.2 Alert Configuration Schema
interface AlertThreshold {
id: string;
tenantId: string;
metric: string; // e.g., 'capa_aging', 'training_compliance'
condition: 'gt' | 'lt' | 'eq'; // greater than, less than, equal to
threshold: number;
severity: 'info' | 'warning' | 'error' | 'critical';
enabled: boolean;
notificationChannels: ('in_app' | 'email' | 'slack' | 'teams')[];
escalationChainId?: string;
cooldownMinutes: number; // Prevent alert spam
metadata: Record<string, any>;
}
interface Alert {
id: string;
tenantId: string;
thresholdId: string;
metric: string;
severity: 'info' | 'warning' | 'error' | 'critical';
title: string;
message: string;
currentValue: number;
thresholdValue: number;
status: 'active' | 'acknowledged' | 'resolved';
createdAt: Date;
acknowledgedAt?: Date;
acknowledgedBy?: string;
resolvedAt?: Date;
escalatedAt?: Date;
escalatedTo?: string[];
metadata: Record<string, any>;
}
6.3 Alert Definitions
6.3.1 CAPA Aging Alerts
const capaAlerts: AlertThreshold[] = [
{
id: 'capa-aging-30',
metric: 'capa_aging_days',
condition: 'gt',
threshold: 30,
severity: 'warning',
notificationChannels: ['in_app', 'email'],
escalationChainId: 'quality-team-lead',
cooldownMinutes: 1440, // Once per day
metadata: {
title: 'CAPA Aging: 30+ Days',
messageTemplate: 'CAPA {capa_number} has been open for {age_days} days. Assigned to: {assigned_to}',
}
},
{
id: 'capa-aging-60',
metric: 'capa_aging_days',
condition: 'gt',
threshold: 60,
severity: 'error',
notificationChannels: ['in_app', 'email', 'slack'],
escalationChainId: 'quality-manager',
cooldownMinutes: 720, // Twice per day
metadata: {
title: 'CAPA Aging: 60+ Days - ESCALATED',
messageTemplate: 'CRITICAL: CAPA {capa_number} has been open for {age_days} days without resolution. Immediate action required.',
}
},
{
id: 'capa-critical-aging-14',
metric: 'capa_critical_aging_days',
condition: 'gt',
threshold: 14,
severity: 'critical',
notificationChannels: ['in_app', 'email', 'slack'],
escalationChainId: 'vp-quality',
cooldownMinutes: 360, // Every 6 hours
metadata: {
title: 'CRITICAL CAPA OVERDUE',
messageTemplate: 'CRITICAL SEVERITY CAPA {capa_number} has exceeded maximum allowed duration ({age_days} days). VP Quality notified.',
}
}
];
6.3.2 Deviation Alerts
const deviationAlerts: AlertThreshold[] = [
{
id: 'deviation-major-overdue-7',
metric: 'deviation_major_overdue_days',
condition: 'gt',
threshold: 7,
severity: 'warning',
notificationChannels: ['in_app', 'email'],
escalationChainId: 'quality-team-lead',
cooldownMinutes: 1440,
metadata: {
title: 'Major Deviation Overdue',
messageTemplate: 'Major deviation {deviation_number} is {overdue_days} days overdue for investigation closure.',
}
},
{
id: 'deviation-critical-overdue-3',
metric: 'deviation_critical_overdue_days',
condition: 'gt',
threshold: 3,
severity: 'error',
notificationChannels: ['in_app', 'email', 'slack'],
escalationChainId: 'quality-manager',
cooldownMinutes: 720,
metadata: {
title: 'Critical Deviation Overdue - ESCALATED',
messageTemplate: 'CRITICAL: Deviation {deviation_number} is {overdue_days} days overdue. Escalated to Quality Manager.',
}
},
{
id: 'deviation-recurrence-threshold',
metric: 'deviation_recurrence_count_90d',
condition: 'gt',
threshold: 3,
severity: 'warning',
notificationChannels: ['in_app', 'email'],
escalationChainId: 'quality-manager',
cooldownMinutes: 10080, // Once per week
metadata: {
title: 'Recurring Deviation Pattern Detected',
messageTemplate: 'Root cause "{root_cause_category}" has occurred {count} times in 90 days. CAPA may be required.',
}
}
];
6.3.3 Training Compliance Alerts
const trainingAlerts: AlertThreshold[] = [
{
id: 'training-dept-below-95',
metric: 'training_compliance_pct_dept',
condition: 'lt',
threshold: 95,
severity: 'warning',
notificationChannels: ['in_app', 'email'],
escalationChainId: 'dept-head',
cooldownMinutes: 1440,
metadata: {
title: 'Department Training Compliance Below Target',
messageTemplate: 'Department {department} training compliance is {compliance_pct}% (target: 100%). {overdue_count} employees overdue.',
}
},
{
id: 'training-individual-overdue-60',
metric: 'training_overdue_days_individual',
condition: 'gt',
threshold: 60,
severity: 'critical',
notificationChannels: ['in_app', 'email', 'slack'],
escalationChainId: 'compliance-officer',
cooldownMinutes: 1440,
metadata: {
title: 'CRITICAL: Employee Training 60+ Days Overdue',
messageTemplate: 'Employee {employee_name} has required training {training_name} overdue by {overdue_days} days. Compliance violation risk.',
}
}
];
6.3.4 Audit Finding Alerts
const auditAlerts: AlertThreshold[] = [
{
id: 'audit-critical-aging-14',
metric: 'audit_finding_critical_age_days',
condition: 'gt',
threshold: 14,
severity: 'error',
notificationChannels: ['in_app', 'email', 'slack'],
escalationChainId: 'compliance-officer',
cooldownMinutes: 720,
metadata: {
title: 'Critical Audit Finding Overdue',
messageTemplate: 'Critical {audit_type} finding {finding_id} has been open for {age_days} days. Target closure: {target_date}.',
}
},
{
id: 'audit-fda-critical-aging-30',
metric: 'audit_finding_fda_critical_age_days',
condition: 'gt',
threshold: 30,
severity: 'critical',
notificationChannels: ['in_app', 'email', 'slack', 'teams'],
escalationChainId: 'executive-escalation',
cooldownMinutes: 360,
metadata: {
title: 'FDA CRITICAL FINDING OVERDUE - EXECUTIVE ESCALATION',
messageTemplate: 'FDA Critical finding {finding_id} has been open for {age_days} days. Immediate executive attention required.',
}
}
];
6.4 Escalation Chains
interface EscalationChain {
id: string;
name: string;
stages: EscalationStage[];
}
interface EscalationStage {
order: number;
delayMinutes: number; // Wait time before escalating to this stage
recipients: {
userId?: string;
roleId?: string; // e.g., 'quality_manager', 'vp_quality'
email?: string;
}[];
notificationChannels: ('in_app' | 'email' | 'slack' | 'teams' | 'sms')[];
requireAcknowledgment: boolean;
}
const escalationChains: EscalationChain[] = [
{
id: 'quality-team-lead',
name: 'Quality Team Lead Escalation',
stages: [
{
order: 1,
delayMinutes: 0,
recipients: [{ roleId: 'quality_team_lead' }],
notificationChannels: ['in_app', 'email'],
requireAcknowledgment: true
},
{
order: 2,
delayMinutes: 1440, // 24 hours
recipients: [{ roleId: 'quality_manager' }],
notificationChannels: ['in_app', 'email', 'slack'],
requireAcknowledgment: true
}
]
},
{
id: 'vp-quality',
name: 'VP Quality Immediate Escalation',
stages: [
{
order: 1,
delayMinutes: 0,
recipients: [
{ roleId: 'quality_manager' },
{ roleId: 'vp_quality' }
],
notificationChannels: ['in_app', 'email', 'slack'],
requireAcknowledgment: true
},
{
order: 2,
delayMinutes: 240, // 4 hours
recipients: [{ roleId: 'compliance_officer' }],
notificationChannels: ['in_app', 'email', 'slack', 'sms'],
requireAcknowledgment: true
}
]
},
{
id: 'executive-escalation',
name: 'Executive Escalation Chain',
stages: [
{
order: 1,
delayMinutes: 0,
recipients: [
{ roleId: 'vp_quality' },
{ roleId: 'compliance_officer' }
],
notificationChannels: ['in_app', 'email', 'slack', 'sms'],
requireAcknowledgment: true
},
{
order: 2,
delayMinutes: 120, // 2 hours
recipients: [
{ roleId: 'ceo' },
{ roleId: 'cto' }
],
notificationChannels: ['email', 'sms'],
requireAcknowledgment: false
}
]
}
];
6.5 Alert Delivery Mechanisms
6.5.1 In-App Notifications
Implementation: Redux state + WebSocket push
// Redux slice: alertsSlice.ts
interface AlertState {
alerts: Alert[];
unreadCount: number;
filter: 'all' | 'active' | 'acknowledged';
sortBy: 'severity' | 'created_at';
}
const alertsSlice = createSlice({
name: 'alerts',
initialState: {
alerts: [],
unreadCount: 0,
filter: 'all',
sortBy: 'severity'
},
reducers: {
addAlert: (state, action: PayloadAction<Alert>) => {
state.alerts.unshift(action.payload);
if (action.payload.status === 'active') {
state.unreadCount += 1;
}
},
acknowledgeAlert: (state, action: PayloadAction<string>) => {
const alert = state.alerts.find(a => a.id === action.payload);
if (alert && alert.status === 'active') {
alert.status = 'acknowledged';
alert.acknowledgedAt = new Date();
state.unreadCount -= 1;
}
}
}
});
// WebSocket listener
socket.on('alert:new', (alert: Alert) => {
dispatch(alertsSlice.actions.addAlert(alert));
// Show toast notification
if (alert.severity === 'critical' || alert.severity === 'error') {
toast.error(alert.title, {
description: alert.message,
duration: Infinity, // Requires manual dismiss
action: {
label: 'Acknowledge',
onClick: () => dispatch(acknowledgeAlert(alert.id))
}
});
}
});
6.5.2 Email Notifications
Template: SendGrid dynamic templates
<!-- email-alert-template.html -->
<!DOCTYPE html>
<html>
<head>
<style>
.alert-critical { border-left: 4px solid #dc2626; background: #fee2e2; }
.alert-error { border-left: 4px solid #ea580c; background: #ffedd5; }
.alert-warning { border-left: 4px solid #ca8a04; background: #fef9c3; }
</style>
</head>
<body>
<div class="alert-{{severity}}">
<h2>{{title}}</h2>
<p>{{message}}</p>
<table>
<tr><td><strong>Severity:</strong></td><td>{{severity}}</td></tr>
<tr><td><strong>Metric:</strong></td><td>{{metric}}</td></tr>
<tr><td><strong>Current Value:</strong></td><td>{{currentValue}}</td></tr>
<tr><td><strong>Threshold:</strong></td><td>{{thresholdValue}}</td></tr>
<tr><td><strong>Created:</strong></td><td>{{createdAt}}</td></tr>
</table>
<a href="{{dashboardUrl}}/alerts/{{alertId}}" style="background: #2563eb; color: white; padding: 10px 20px; text-decoration: none; display: inline-block; margin-top: 20px;">
Acknowledge Alert
</a>
</div>
</body>
</html>
6.5.3 Slack/Teams Webhooks
Slack Block Kit Example:
async function sendSlackAlert(alert: Alert, webhookUrl: string) {
const severityColor = {
critical: '#dc2626',
error: '#ea580c',
warning: '#ca8a04',
info: '#3b82f6'
};
const blocks = [
{
type: 'header',
text: {
type: 'plain_text',
text: `🚨 ${alert.title}`,
emoji: true
}
},
{
type: 'section',
fields: [
{ type: 'mrkdwn', text: `*Severity:*\n${alert.severity.toUpperCase()}` },
{ type: 'mrkdwn', text: `*Metric:*\n${alert.metric}` },
{ type: 'mrkdwn', text: `*Current Value:*\n${alert.currentValue}` },
{ type: 'mrkdwn', text: `*Threshold:*\n${alert.thresholdValue}` }
]
},
{
type: 'section',
text: {
type: 'mrkdwn',
text: alert.message
}
},
{
type: 'actions',
elements: [
{
type: 'button',
text: { type: 'plain_text', text: 'View Dashboard' },
url: `${process.env.DASHBOARD_URL}/compliance`,
style: 'primary'
},
{
type: 'button',
text: { type: 'plain_text', text: 'Acknowledge' },
url: `${process.env.DASHBOARD_URL}/alerts/${alert.id}/acknowledge`,
style: 'danger'
}
]
}
];
await fetch(webhookUrl, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
attachments: [{
color: severityColor[alert.severity],
blocks
}]
})
});
}
6.6 Alert Acknowledgment Tracking
Database Schema:
CREATE TABLE compliance_alert_acknowledgment (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
alert_id UUID NOT NULL REFERENCES compliance_alert(id),
acknowledged_by UUID NOT NULL REFERENCES users(id),
acknowledged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
acknowledgment_notes TEXT,
acknowledgment_method VARCHAR(50) NOT NULL, -- 'in_app', 'email_link', 'slack', 'api'
sla_breach BOOLEAN DEFAULT FALSE,
sla_response_time_minutes INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_alert_ack_tenant ON compliance_alert_acknowledgment(tenant_id);
CREATE INDEX idx_alert_ack_alert ON compliance_alert_acknowledgment(alert_id);
CREATE INDEX idx_alert_ack_user ON compliance_alert_acknowledgment(acknowledged_by);
SLA Tracking:
interface AlertSLA {
severity: 'info' | 'warning' | 'error' | 'critical';
responseTimeMinutes: number;
resolutionTimeMinutes: number;
}
const alertSLAs: AlertSLA[] = [
{ severity: 'info', responseTimeMinutes: 10080, resolutionTimeMinutes: 20160 }, // 7 days / 14 days
{ severity: 'warning', responseTimeMinutes: 1440, resolutionTimeMinutes: 4320 }, // 24 hours / 3 days
{ severity: 'error', responseTimeMinutes: 240, resolutionTimeMinutes: 1440 }, // 4 hours / 24 hours
{ severity: 'critical', responseTimeMinutes: 60, resolutionTimeMinutes: 240 } // 1 hour / 4 hours
];
async function checkAlertSLA(alert: Alert) {
const sla = alertSLAs.find(s => s.severity === alert.severity);
if (!sla) return;
const responseTime = alert.acknowledgedAt
? (alert.acknowledgedAt.getTime() - alert.createdAt.getTime()) / 60000
: (new Date().getTime() - alert.createdAt.getTime()) / 60000;
if (responseTime > sla.responseTimeMinutes && !alert.acknowledgedAt) {
// SLA breach - escalate
await escalateAlert(alert, 'SLA response time exceeded');
}
}
7. Multi-Framework View
7.1 Compliance Heatmap
Purpose: Visual overview of compliance status across all frameworks at a glance.
Heatmap Structure:
┌─────────────────────────────────────────────────────────────────┐
│ Compliance Framework Heatmap │
├─────────────┬──────────────┬──────────────┬──────────────┬──────┤
│ Framework │ Access Ctrl │ Audit Trail │ Validation │ Score│
├─────────────┼──────────────┼──────────────┼──────────────┼──────┤
│ FDA Part 11 │ 🟢 98% │ 🟢 100% │ 🟡 85% │ 94% │
│ HIPAA │ 🟢 97% │ 🟢 99% │ 🟢 100% │ 99% │
│ SOC 2 │ 🟢 95% │ 🟢 96% │ 🟢 92% │ 94% │
├─────────────┼──────────────┼──────────────┼──────────────┼──────┤
│ Overall │ 🟢 97% │ 🟢 98% │ 🟡 92% │ 96% │
└─────────────┴──────────────┴──────────────┴──────────────┴──────┘
Legend: 🟢 ≥90% 🟡 70-89% 🔴 <70%
SQL Query:
-- Multi-framework compliance scores
WITH framework_controls AS (
SELECT
tenant_id,
framework, -- 'FDA_21_CFR_PART_11', 'HIPAA', 'SOC2'
control_category,
control_id,
control_status, -- 'compliant', 'partial', 'non_compliant', 'not_applicable'
last_assessment_date,
next_assessment_due,
evidence_count
FROM compliance_framework_control
WHERE tenant_id = %(tenant_id)s
AND control_status != 'not_applicable'
)
SELECT
framework,
control_category,
COUNT(*) AS total_controls,
COUNT(*) FILTER (WHERE control_status = 'compliant') AS compliant_controls,
COUNT(*) FILTER (WHERE control_status = 'partial') AS partial_controls,
COUNT(*) FILTER (WHERE control_status = 'non_compliant') AS non_compliant_controls,
(COUNT(*) FILTER (WHERE control_status = 'compliant')::float / COUNT(*) * 100) AS compliance_pct,
MIN(next_assessment_due) AS earliest_due_date
FROM framework_controls
GROUP BY framework, control_category
ORDER BY framework, control_category;
7.2 FDA 21 CFR Part 11 Compliance Score
Control-by-Control Breakdown:
interface FDAControl {
section: string; // e.g., '11.10(a)', '11.10(e)', '11.50(a)'
requirement: string;
status: 'compliant' | 'partial' | 'non_compliant' | 'not_applicable';
evidence: {
type: 'document' | 'test_result' | 'screenshot' | 'audit_log';
id: string;
name: string;
url: string;
}[];
lastAssessed: Date;
assessedBy: string;
notes: string;
}
const fdaControls: FDAControl[] = [
{
section: '11.10(a)',
requirement: 'Validation of systems to ensure accuracy, reliability, consistent intended performance',
status: 'compliant',
evidence: [
{ type: 'document', id: 'VAL-001', name: 'System Validation Protocol', url: '/docs/VAL-001' },
{ type: 'test_result', id: 'IQ-001', name: 'Installation Qualification', url: '/test-results/IQ-001' }
],
lastAssessed: new Date('2026-01-15'),
assessedBy: 'Jane Smith, Quality Manager',
notes: 'IQ/OQ/PQ complete. Next revalidation: 2027-01-15'
},
{
section: '11.10(c)',
requirement: 'Protection of records to enable accurate and ready retrieval',
status: 'compliant',
evidence: [
{ type: 'document', id: 'RET-001', name: 'Record Retention Policy', url: '/docs/RET-001' },
{ type: 'audit_log', id: 'AUDIT-2026-01', name: 'January 2026 Audit Log Sample', url: '/audit/AUDIT-2026-01' }
],
lastAssessed: new Date('2026-02-01'),
assessedBy: 'John Doe, Compliance Officer',
notes: 'Append-only audit trail verified. Partitioning strategy in place.'
}
// ... all 21 CFR Part 11 controls
];
Dashboard Display:
┌─────────────────────────────────────────────────────────────────┐
│ FDA 21 CFR Part 11 Compliance Status │
│ │
│ Overall Score: 94% │
│ ████████████████████▒▒▒ (32/34 controls compliant) │
│ │
│ ┌──────────────┬──────────┬───────────┬────────────┐ │
│ │ Control Area │ Total │ Compliant │ Status │ │
│ ├──────────────┼──────────┼───────────┼────────────┤ │
│ │ §11.10 │ 10 │ 9 │ 🟡 90% │ │
│ │ §11.50 │ 4 │ 4 │ 🟢 100% │ │
│ │ §11.70 │ 2 │ 1 │ 🟡 50% │ │
│ │ §11.100 │ 3 │ 3 │ 🟢 100% │ │
│ │ §11.200 │ 9 │ 9 │ 🟢 100% │ │
│ │ §11.300 │ 6 │ 6 │ 🟢 100% │ │
│ └──────────────┴──────────┴───────────┴────────────┘ │
│ │
│ ⚠️ Gap: §11.70 Cryptographic Binding (Estimated fix: 2 days) │
│ │
└─────────────────────────────────────────────────────────────────┘
7.3 HIPAA Security Rule Compliance Matrix
Control Mapping:
-- HIPAA compliance by safeguard type
SELECT
safeguard_type, -- 'administrative', 'physical', 'technical'
standard, -- e.g., '164.308(a)(1)', '164.312(a)(1)'
implementation_spec,
required, -- TRUE/FALSE (required vs addressable)
status, -- 'implemented', 'not_implemented', 'not_applicable'
COUNT(*) OVER (PARTITION BY safeguard_type) AS total_in_category,
COUNT(*) FILTER (WHERE status = 'implemented') OVER (PARTITION BY safeguard_type) AS implemented_in_category
FROM compliance_hipaa_control
WHERE tenant_id = %(tenant_id)s
ORDER BY safeguard_type, standard;
Dashboard Display:
┌─────────────────────────────────────────────────────────────────┐
│ HIPAA Security Rule Compliance Status │
│ │
│ Overall Score: 99% │
│ ██████████████████████████████ (47/48 controls implemented) │
│ │
│ ┌──────────────────────┬──────────┬─────────────┬────────────┐ │
│ │ Safeguard Type │ Total │ Implemented │ Status │ │
│ ├──────────────────────┼──────────┼─────────────┼────────────┤ │
│ │ Administrative │ 20 │ 20 │ 🟢 100% │ │
│ │ Physical │ 8 │ 8 │ 🟢 100% │ │
│ │ Technical │ 20 │ 19 │ 🟡 95% │ │
│ └──────────────────────┴──────────┴─────────────┴────────────┘ │
│ │
│ ⚠️ Gap: §164.312(a)(2)(ii) Emergency Access (Design phase) │
│ │
└─────────────────────────────────────────────────────────────────┘
7.4 SOC 2 Trust Service Criteria Status
TSC Category Mapping:
interface SOC2Control {
category: 'CC' | 'A' | 'C' | 'PI' | 'P'; // Common Criteria, Availability, Confidentiality, Processing Integrity, Privacy
controlId: string; // e.g., 'CC7.2', 'A1.2'
controlObjective: string;
controlActivity: string;
frequency: 'continuous' | 'daily' | 'weekly' | 'monthly' | 'quarterly' | 'annual';
automationLevel: 'fully_automated' | 'partially_automated' | 'manual';
status: 'operating_effectively' | 'deviation' | 'not_tested';
testDate?: Date;
testResults?: string;
deviations?: {
description: string;
remediationPlan: string;
targetDate: Date;
}[];
}
SQL Query:
-- SOC 2 control effectiveness by category
SELECT
category,
COUNT(*) AS total_controls,
COUNT(*) FILTER (WHERE status = 'operating_effectively') AS effective_controls,
COUNT(*) FILTER (WHERE status = 'deviation') AS deviations,
COUNT(*) FILTER (WHERE status = 'not_tested') AS not_tested,
(COUNT(*) FILTER (WHERE status = 'operating_effectively')::float /
NULLIF(COUNT(*) FILTER (WHERE status != 'not_tested'), 0) * 100) AS effectiveness_pct
FROM compliance_soc2_control
WHERE tenant_id = %(tenant_id)s
GROUP BY category
ORDER BY category;
Dashboard Display:
┌─────────────────────────────────────────────────────────────────┐
│ SOC 2 Trust Service Criteria Effectiveness │
│ │
│ Overall Score: 94% │
│ ████████████████████▒▒▒ (128/136 controls effective) │
│ │
│ ┌──────────────┬──────────┬───────────┬────────────┬─────────┐ │
│ │ Category │ Total │ Effective │ Deviations │ Status │ │
│ ├──────────────┼──────────┼───────────┼────────────┼─────────┤ │
│ │ CC (Common) │ 60 │ 58 │ 2 │ 🟡 97% │ │
│ │ A (Avail.) │ 20 │ 20 │ 0 │ 🟢 100% │ │
│ │ C (Confid.) │ 25 │ 22 │ 3 │ 🟡 88% │ │
│ │ PI (Proc.) │ 18 │ 17 │ 1 │ 🟡 94% │ │
│ │ P (Privacy) │ 13 │ 11 │ 2 │ 🟡 85% │ │
│ └──────────────┴──────────┴───────────┴────────────┴─────────┘ │
│ │
│ ⚠️ 8 Total Deviations - All with active remediation plans │
│ │
└─────────────────────────────────────────────────────────────────┘
7.5 Combined Compliance Heatmap
Interactive React Component:
import React from 'react';
import { Tooltip } from '@/components/ui/tooltip';
interface ComplianceCell {
framework: string;
category: string;
score: number;
status: 'compliant' | 'partial' | 'non_compliant';
details: string;
}
const ComplianceHeatmap: React.FC<{ data: ComplianceCell[] }> = ({ data }) => {
const getColor = (score: number) => {
if (score >= 90) return 'bg-green-500';
if (score >= 70) return 'bg-yellow-500';
return 'bg-red-500';
};
const frameworks = ['FDA 21 CFR Part 11', 'HIPAA', 'SOC 2'];
const categories = ['Access Control', 'Audit Trail', 'Validation', 'Encryption', 'Training'];
return (
<div className="compliance-heatmap">
<table className="w-full border-collapse">
<thead>
<tr>
<th className="border p-2">Framework</th>
{categories.map(cat => (
<th key={cat} className="border p-2">{cat}</th>
))}
<th className="border p-2">Overall</th>
</tr>
</thead>
<tbody>
{frameworks.map(framework => {
const frameworkData = data.filter(d => d.framework === framework);
const overallScore = frameworkData.reduce((sum, d) => sum + d.score, 0) / frameworkData.length;
return (
<tr key={framework}>
<td className="border p-2 font-semibold">{framework}</td>
{categories.map(category => {
const cell = frameworkData.find(d => d.category === category);
const score = cell?.score ?? 0;
return (
<Tooltip key={category} content={cell?.details ?? 'No data'}>
<td className={`border p-2 text-center ${getColor(score)}`}>
{score}%
</td>
</Tooltip>
);
})}
<td className={`border p-2 text-center font-bold ${getColor(overallScore)}`}>
{Math.round(overallScore)}%
</td>
</tr>
);
})}
</tbody>
</table>
</div>
);
};
7.6 Gap Analysis Visualization
Purpose: Visual representation of compliance gaps across frameworks with prioritization.
interface ComplianceGap {
framework: string;
control: string;
currentState: string;
targetState: string;
priority: 'critical' | 'high' | 'medium' | 'low';
estimatedEffort: string; // e.g., '2 days', '1 week'
assignedTo?: string;
targetDate?: Date;
dependencies?: string[];
}
const GapVisualization: React.FC<{ gaps: ComplianceGap[] }> = ({ gaps }) => {
const criticalGaps = gaps.filter(g => g.priority === 'critical');
const highGaps = gaps.filter(g => g.priority === 'high');
return (
<div className="gap-analysis">
<h3>Compliance Gap Analysis</h3>
<div className="gap-summary">
<div className="gap-card critical">
<span className="count">{criticalGaps.length}</span>
<span className="label">Critical Gaps</span>
</div>
<div className="gap-card high">
<span className="count">{highGaps.length}</span>
<span className="label">High Priority Gaps</span>
</div>
</div>
<table className="gap-table">
<thead>
<tr>
<th>Priority</th>
<th>Framework</th>
<th>Control</th>
<th>Current State</th>
<th>Effort</th>
<th>Assigned To</th>
<th>Target Date</th>
</tr>
</thead>
<tbody>
{gaps.map((gap, idx) => (
<tr key={idx} className={`priority-${gap.priority}`}>
<td><span className={`badge ${gap.priority}`}>{gap.priority}</span></td>
<td>{gap.framework}</td>
<td>{gap.control}</td>
<td>{gap.currentState}</td>
<td>{gap.estimatedEffort}</td>
<td>{gap.assignedTo ?? 'Unassigned'}</td>
<td>{gap.targetDate ? gap.targetDate.toLocaleDateString() : '-'}</td>
</tr>
))}
</tbody>
</table>
</div>
);
};
8. Role-Based Views
8.1 Role Definitions
| Role | Access Level | Dashboard Scope | Export Permissions |
|---|---|---|---|
| Quality Manager | Full | All metrics, all departments, all frameworks | PDF, CSV, Excel |
| Department Head | Department-scoped | Department-specific metrics only | CSV for own dept |
| Compliance Officer | Full (read-only for ops) | Regulatory focus, audit readiness, framework status | PDF, CSV, Excel |
| Executive | Summary | High-level scorecard, trends, risk indicators | PDF executive summary |
| Auditor | Read-only (time-bound) | Evidence access, audit trail, control status | View-only (no export) |
8.2 Quality Manager View
Purpose: Comprehensive operational dashboard for day-to-day quality management.
Layout:
┌─────────────────────────────────────────────────────────────────┐
│ Quality Manager Dashboard 👤 Jane Smith, QM │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Quick Status Overview │ │
│ │ CAPA: 12 Open (3 Aging) │ Deviations: 5 Overdue │ │
│ │ Training: 94% Compliant │ Audits: 2 Critical Open │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ CAPA Aging Chart │ │ Deviation Severity │ │
│ │ [Bar Chart: 0-30, │ │ [Donut: Critical/Major/ │ │
│ │ 31-60, 61-90, 90+] │ │ Minor distribution] │ │
│ └──────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Training Compliance Heatmap (Dept x Role) │ │
│ │ [Heatmap with drill-down to individual employees] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Audit Findings by Type │ │ Document Review Backlog │ │
│ │ [Stacked Bar Chart] │ │ [List with aging] │ │
│ └──────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Recent Alerts (Last 7 Days) │ │
│ │ [Table: Alert | Severity | Status | Acknowledged By] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ Actions: [Export Full Report] [Schedule Review] [Settings] │
└─────────────────────────────────────────────────────────────────┘
React Component:
// QualityManagerDashboard.tsx
import React, { useEffect } from 'react';
import { useSelector, useDispatch } from 'react-redux';
import { CapaAgingChart } from './components/CapaAgingChart';
import { TrainingHeatmap } from './components/TrainingHeatmap';
import { AlertsTable } from './components/AlertsTable';
export const QualityManagerDashboard: React.FC = () => {
const dispatch = useDispatch();
const metrics = useSelector(state => state.compliance.metrics);
const alerts = useSelector(state => state.alerts.alerts);
useEffect(() => {
// Subscribe to real-time updates
socket.emit('subscribe', { channel: 'compliance:all' });
return () => {
socket.emit('unsubscribe', { channel: 'compliance:all' });
};
}, []);
return (
<div className="quality-manager-dashboard">
<header>
<h1>Quality Manager Dashboard</h1>
<div className="user-info">👤 {currentUser.name}, {currentUser.role}</div>
</header>
<section className="quick-status">
<StatusCard
label="CAPA"
value={metrics.capa.open}
subtitle={`${metrics.capa.aging} aging`}
alert={metrics.capa.aging > 0}
/>
<StatusCard
label="Deviations"
value={metrics.deviation.overdue}
subtitle="overdue"
alert={metrics.deviation.overdue > 0}
/>
<StatusCard
label="Training"
value={`${metrics.training.compliancePct}%`}
subtitle="compliant"
alert={metrics.training.compliancePct < 95}
/>
<StatusCard
label="Audits"
value={metrics.audit.criticalOpen}
subtitle="critical open"
alert={metrics.audit.criticalOpen > 0}
/>
</section>
<div className="charts-grid">
<CapaAgingChart data={metrics.capa.agingDistribution} />
<DeviationSeverityChart data={metrics.deviation.severityBreakdown} />
<TrainingHeatmap data={metrics.training.heatmapData} />
<AuditFindingsChart data={metrics.audit.findingsByType} />
</div>
<AlertsTable
alerts={alerts.filter(a => a.status === 'active')}
onAcknowledge={(id) => dispatch(acknowledgeAlert(id))}
/>
<div className="actions">
<button onClick={() => exportReport('full')}>Export Full Report</button>
<button onClick={() => scheduleReview()}>Schedule Review</button>
</div>
</div>
);
};
8.3 Department Head View
Purpose: Department-scoped view for operational managers to track their team's compliance.
Scope Restrictions:
-- Row-level security for department-scoped access
CREATE POLICY dept_head_view ON compliance_training_status
FOR SELECT
USING (
tenant_id = current_setting('app.tenant_id')::uuid
AND department = current_setting('app.user_department')::text
);
CREATE POLICY dept_head_view_capa ON compliance_capa
FOR SELECT
USING (
tenant_id = current_setting('app.tenant_id')::uuid
AND (assigned_department = current_setting('app.user_department')::text
OR originating_department = current_setting('app.user_department')::text)
);
Layout:
┌─────────────────────────────────────────────────────────────────┐
│ Department Dashboard: Manufacturing 👤 Bob Jones, Mgr │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Department Health Score: 92% │ │
│ │ ████████████████████▒▒▒ │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Training Compliance │ │ Open CAPAs (Dept) │ │
│ │ 28/30 Employees (93%) │ │ 4 Open, 1 Aging │ │
│ │ [List of 2 overdue] │ │ [List with assignees] │ │
│ └──────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Training Compliance by Role │ │
│ │ Operator: 95% | Supervisor: 100% | Technician: 90% │ │
│ │ [Bar chart] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Department Deviations (Last 90 Days) │ │
│ │ [Timeline showing deviation occurrences by category] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ Actions: [Export Dept Report (CSV)] [Request Training] │
└─────────────────────────────────────────────────────────────────┘
8.4 Compliance Officer View
Purpose: Regulatory-focused view emphasizing audit readiness and framework compliance.
Layout:
┌─────────────────────────────────────────────────────────────────┐
│ Compliance Officer Dashboard 👤 Sarah Chen, CCO │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Regulatory Inspection Readiness Score: 91% │ │
│ │ ███████████████████████▒▒▒ │ │
│ │ Target: 85% | Status: 🟢 READY │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Multi-Framework Compliance Heatmap │ │
│ │ [Interactive heatmap: Framework x Control Category] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Critical Audit Findings │ │ Upcoming Revalidations │ │
│ │ FDA: 0 | HIPAA: 0 │ │ [Timeline: Next 6 months]│ │
│ │ SOC 2: 2 (in progress) │ │ │ │
│ └──────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Gap Analysis Summary │ │
│ │ Critical: 0 | High: 2 | Medium: 5 | Low: 8 │ │
│ │ [Gantt chart showing remediation timeline] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Framework Trend Analysis (12 Months) │ │
│ │ [Multi-line chart: FDA/HIPAA/SOC2 scores over time] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ Actions: [Generate Audit Package] [Request Evidence] │
│ [Schedule External Audit] [Export Compliance Report] │
└─────────────────────────────────────────────────────────────────┘
8.5 Executive View
Purpose: High-level scorecard for C-suite and board visibility.
Layout:
┌─────────────────────────────────────────────────────────────────┐
│ Executive Compliance Scorecard 👤 Alex Martinez, CEO │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ Overall Compliance Health │ │
│ │ 96% │ │
│ │ ████████████████████████▒ │ │
│ │ Status: 🟢 HEALTHY │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────┬─────────────┬─────────────┬─────────────┐ │
│ │ FDA Part 11│ HIPAA │ SOC 2 │ ISO 13485 │ │
│ │ 94% │ 99% │ 94% │ N/A │ │
│ │ 🟢 OK │ 🟢 OK │ 🟢 OK │ Planned │ │
│ └─────────────┴─────────────┴─────────────┴─────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ Compliance Trend (12 Months) │ │
│ │ [Simplified line chart showing overall score trend] │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ Key Risk Indicators │ │
│ │ • Open Critical Findings: 0 │ │
│ │ • Overdue CAPAs (>60 days): 1 │ │
│ │ • Training Compliance: 94% (Target: 100%) │ │
│ │ • Next External Audit: SOC 2 (March 2026) │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ Recent Regulatory Activity │ │
│ │ • FDA Audit: Passed (Jan 2026) │ │
│ │ • HIPAA Assessment: Compliant (Dec 2025) │ │
│ │ • SOC 2 Type II: In Progress │ │
│ └──────────────────────────────────────────────────────────┘ │
│ │
│ Actions: [Download Executive Summary PDF] [Schedule Briefing] │
└─────────────────────────────────────────────────────────────────┘
Simplified Metrics:
// Executive metrics focus on high-level aggregates
interface ExecutiveMetrics {
overallScore: number;
frameworkScores: {
fda: number;
hipaa: number;
soc2: number;
};
riskIndicators: {
criticalFindings: number;
overdueCAPAs: number;
trainingCompliance: number;
};
upcomingAudits: {
framework: string;
date: Date;
status: 'scheduled' | 'in_progress' | 'completed';
}[];
recentActivity: {
event: string;
date: Date;
outcome: string;
}[];
}
8.6 Auditor View (Read-Only, Time-Bound)
Purpose: Provide external auditors with controlled, read-only access to compliance evidence.
Access Control:
interface AuditorAccess {
userId: string;
tenantId: string;
auditorType: 'internal' | 'external_fda' | 'external_soc2' | 'external_hipaa';
accessStartDate: Date;
accessEndDate: Date;
scopedControls: string[]; // List of control IDs they can view
allowedActions: ('view' | 'export_view_only')[];
auditTrailEnabled: boolean; // Log all their access
}
// Middleware to enforce auditor restrictions
function enforceAuditorRestrictions(req: Request, res: Response, next: NextFunction) {
if (req.user.role === 'auditor') {
const now = new Date();
const access = req.user.auditorAccess;
if (now < access.accessStartDate || now > access.accessEndDate) {
return res.status(403).json({ error: 'Auditor access period expired' });
}
// Log all auditor actions
auditLog.create({
userId: req.user.id,
action: req.method + ' ' + req.path,
timestamp: now,
ipAddress: req.ip
});
}
next();
}
Layout:
┌─────────────────────────────────────────────────────────────────┐
│ Auditor View: SOC 2 Type II Audit 👤 External Auditor │
│ Access Period: 2026-02-01 to 2026-02-28 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ⚠️ READ-ONLY ACCESS - All actions are logged │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ In-Scope Controls (SOC 2 TSC) │ │
│ │ [Expandable tree: CC | A | C | PI | P categories] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────┐ ┌──────────────────────────┐ │
│ │ Control Effectiveness │ │ Evidence Repository │ │
│ │ [Table: Control | Status]│ │ [Document list with │ │
│ │ │ │ view-only links] │ │
│ └──────────────────────────┘ └──────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Audit Trail Sample (90 Days) │ │
│ │ [Paginated table with search and filter] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ System Validation Evidence │ │
│ │ • IQ Protocol: [View] │ │
│ │ • OQ Test Results: [View] │ │
│ │ • PQ Report: [View] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ Actions: [Request Additional Evidence] [Submit Finding] │
│ Note: Export functionality disabled for external auditors. │
└─────────────────────────────────────────────────────────────────┘
9. Technical Implementation
9.1 Backend API Structure
Django REST Framework Endpoints:
# urls.py
from django.urls import path, include
from rest_framework.routers import DefaultRouter
from .views import (
ComplianceMetricsViewSet,
AlertViewSet,
TrendDataViewSet,
ExportViewSet,
FrameworkComplianceViewSet
)
router = DefaultRouter()
router.register(r'metrics', ComplianceMetricsViewSet, basename='compliance-metrics')
router.register(r'alerts', AlertViewSet, basename='compliance-alerts')
router.register(r'trends', TrendDataViewSet, basename='compliance-trends')
router.register(r'export', ExportViewSet, basename='compliance-export')
router.register(r'frameworks', FrameworkComplianceViewSet, basename='compliance-frameworks')
urlpatterns = [
path('api/compliance/', include(router.urls)),
]
ViewSet Example:
# views.py
from rest_framework import viewsets, status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.permissions import IsAuthenticated
from django.utils import timezone
from django.core.cache import cache
from .models import ComplianceMetric, Alert
from .serializers import ComplianceMetricSerializer, AlertSerializer
from .permissions import HasComplianceAccess, HasDepartmentScope
class ComplianceMetricsViewSet(viewsets.ReadOnlyModelViewSet):
"""
API endpoint for compliance metrics.
Supports role-based filtering and real-time data.
"""
serializer_class = ComplianceMetricSerializer
permission_classes = [IsAuthenticated, HasComplianceAccess]
def get_queryset(self):
user = self.request.user
tenant_id = self.request.tenant.id
# Check cache first (5 minute TTL)
cache_key = f'compliance_metrics_{tenant_id}_{user.role}'
cached_data = cache.get(cache_key)
if cached_data:
return cached_data
# Base queryset with tenant isolation
queryset = ComplianceMetric.objects.filter(tenant_id=tenant_id)
# Apply role-based filtering
if user.role == 'department_head':
queryset = queryset.filter(department=user.department)
elif user.role == 'auditor':
# Auditors only see in-scope controls
queryset = queryset.filter(
control_id__in=user.auditor_access.scoped_controls
)
# Cache the queryset
cache.set(cache_key, queryset, 300) # 5 minutes
return queryset
@action(detail=False, methods=['get'])
def summary(self, request):
"""
Return aggregated compliance summary for dashboard overview.
"""
tenant_id = request.tenant.id
cache_key = f'compliance_summary_{tenant_id}'
summary = cache.get(cache_key)
if not summary:
from .services import ComplianceMetricsService
service = ComplianceMetricsService(tenant_id)
summary = service.calculate_summary()
cache.set(cache_key, summary, 300)
return Response(summary)
@action(detail=False, methods=['get'])
def capa_aging(self, request):
"""
Return CAPA aging distribution.
"""
from .services import CAPAService
service = CAPAService(request.tenant.id)
aging_data = service.get_aging_distribution()
return Response(aging_data)
class AlertViewSet(viewsets.ModelViewSet):
"""
API endpoint for compliance alerts.
Supports create, acknowledge, resolve, and escalate actions.
"""
serializer_class = AlertSerializer
permission_classes = [IsAuthenticated, HasComplianceAccess]
def get_queryset(self):
user = self.request.user
tenant_id = self.request.tenant.id
queryset = Alert.objects.filter(tenant_id=tenant_id)
# Filter by status if provided
status_filter = self.request.query_params.get('status', None)
if status_filter:
queryset = queryset.filter(status=status_filter)
# Filter by severity if provided
severity_filter = self.request.query_params.get('severity', None)
if severity_filter:
queryset = queryset.filter(severity=severity_filter)
return queryset.order_by('-created_at')
@action(detail=True, methods=['post'])
def acknowledge(self, request, pk=None):
"""
Acknowledge an alert.
"""
alert = self.get_object()
if alert.status != 'active':
return Response(
{'error': 'Alert is not in active state'},
status=status.HTTP_400_BAD_REQUEST
)
alert.status = 'acknowledged'
alert.acknowledged_at = timezone.now()
alert.acknowledged_by = request.user.id
alert.save()
# Record acknowledgment
from .models import AlertAcknowledgment
AlertAcknowledgment.objects.create(
tenant_id=request.tenant.id,
alert=alert,
acknowledged_by=request.user,
acknowledgment_notes=request.data.get('notes', ''),
acknowledgment_method='in_app'
)
# Send WebSocket notification
from .websocket import send_alert_update
send_alert_update(alert)
return Response(AlertSerializer(alert).data)
@action(detail=True, methods=['post'])
def escalate(self, request, pk=None):
"""
Manually escalate an alert.
"""
alert = self.get_object()
from .services import AlertEscalationService
service = AlertEscalationService(request.tenant.id)
service.escalate_alert(alert, reason=request.data.get('reason', 'Manual escalation'))
return Response({'status': 'escalated'})
9.2 Background Worker Tasks
Celery Task Definitions:
# tasks.py
from celery import shared_task
from django.utils import timezone
from datetime import timedelta
import logging
logger = logging.getLogger(__name__)
@shared_task(name='compliance.refresh_metrics')
def refresh_compliance_metrics(tenant_id=None):
"""
Refresh all compliance metrics for tenant(s).
Runs every 5 minutes via Celery Beat.
"""
from .services import ComplianceMetricsService
from apps.tenants.models import Tenant
tenants = [Tenant.objects.get(id=tenant_id)] if tenant_id else Tenant.objects.filter(is_active=True)
for tenant in tenants:
try:
service = ComplianceMetricsService(tenant.id)
service.refresh_all_metrics()
logger.info(f'Refreshed compliance metrics for tenant {tenant.id}')
except Exception as e:
logger.error(f'Error refreshing metrics for tenant {tenant.id}: {e}')
@shared_task(name='compliance.check_alert_thresholds')
def check_alert_thresholds(tenant_id=None):
"""
Check all alert thresholds and create alerts if breached.
Runs every 1 minute via Celery Beat.
"""
from .services import AlertThresholdService
from apps.tenants.models import Tenant
tenants = [Tenant.objects.get(id=tenant_id)] if tenant_id else Tenant.objects.filter(is_active=True)
for tenant in tenants:
try:
service = AlertThresholdService(tenant.id)
service.check_all_thresholds()
logger.info(f'Checked alert thresholds for tenant {tenant.id}')
except Exception as e:
logger.error(f'Error checking thresholds for tenant {tenant.id}: {e}')
@shared_task(name='compliance.process_alert_escalations')
def process_alert_escalations():
"""
Process alert escalation chains for unacknowledged alerts.
Runs every 5 minutes via Celery Beat.
"""
from .services import AlertEscalationService
from .models import Alert
# Find alerts needing escalation
alerts_to_escalate = Alert.objects.filter(
status='active',
escalation_chain_id__isnull=False
).select_related('threshold', 'tenant')
for alert in alerts_to_escalate:
try:
service = AlertEscalationService(alert.tenant_id)
service.process_escalation(alert)
except Exception as e:
logger.error(f'Error processing escalation for alert {alert.id}: {e}')
@shared_task(name='compliance.calculate_trend_data')
def calculate_trend_data(tenant_id=None):
"""
Calculate historical trend data for KPIs.
Runs every 15 minutes via Celery Beat.
"""
from .services import TrendCalculationService
from apps.tenants.models import Tenant
tenants = [Tenant.objects.get(id=tenant_id)] if tenant_id else Tenant.objects.filter(is_active=True)
for tenant in tenants:
try:
service = TrendCalculationService(tenant.id)
service.calculate_all_trends()
logger.info(f'Calculated trend data for tenant {tenant.id}')
except Exception as e:
logger.error(f'Error calculating trends for tenant {tenant.id}: {e}')
@shared_task(name='compliance.generate_daily_snapshot')
def generate_daily_snapshot():
"""
Generate daily compliance snapshot for historical tracking.
Runs daily at 00:00 UTC via Celery Beat.
"""
from .services import SnapshotService
from apps.tenants.models import Tenant
for tenant in Tenant.objects.filter(is_active=True):
try:
service = SnapshotService(tenant.id)
service.create_daily_snapshot()
logger.info(f'Created daily snapshot for tenant {tenant.id}')
except Exception as e:
logger.error(f'Error creating snapshot for tenant {tenant.id}: {e}')
# Celery Beat schedule
from celery.schedules import crontab
CELERY_BEAT_SCHEDULE = {
'refresh-compliance-metrics': {
'task': 'compliance.refresh_metrics',
'schedule': timedelta(minutes=5),
},
'check-alert-thresholds': {
'task': 'compliance.check_alert_thresholds',
'schedule': timedelta(minutes=1),
},
'process-alert-escalations': {
'task': 'compliance.process_alert_escalations',
'schedule': timedelta(minutes=5),
},
'calculate-trend-data': {
'task': 'compliance.calculate_trend_data',
'schedule': timedelta(minutes=15),
},
'generate-daily-snapshot': {
'task': 'compliance.generate_daily_snapshot',
'schedule': crontab(hour=0, minute=0), # Daily at midnight UTC
},
}
9.3 Service Layer
Metrics Calculation Service:
# services/metrics_service.py
from django.db import connection
from django.core.cache import cache
from typing import Dict, Any
import logging
logger = logging.getLogger(__name__)
class ComplianceMetricsService:
"""
Service for calculating and caching compliance metrics.
"""
def __init__(self, tenant_id: str):
self.tenant_id = tenant_id
def calculate_summary(self) -> Dict[str, Any]:
"""
Calculate high-level compliance summary.
"""
return {
'capa': self._calculate_capa_metrics(),
'deviation': self._calculate_deviation_metrics(),
'training': self._calculate_training_metrics(),
'audit': self._calculate_audit_metrics(),
'document': self._calculate_document_metrics(),
'validation': self._calculate_validation_metrics(),
}
def _calculate_capa_metrics(self) -> Dict[str, Any]:
"""Calculate CAPA-related metrics."""
with connection.cursor() as cursor:
cursor.execute("""
SELECT
COUNT(*) FILTER (WHERE status IN ('open', 'in_progress')) AS open,
COUNT(*) FILTER (WHERE status IN ('open', 'in_progress')
AND CURRENT_DATE - created_at::date > 30) AS aging,
COUNT(*) FILTER (WHERE status IN ('open', 'in_progress')
AND CURRENT_DATE - created_at::date > 60) AS critical_aging,
AVG(CASE WHEN status = 'closed'
THEN EXTRACT(EPOCH FROM (closed_at - created_at))/86400
END) AS avg_cycle_time_days
FROM compliance_capa
WHERE tenant_id = %s
""", [self.tenant_id])
row = cursor.fetchone()
return {
'open': row[0] or 0,
'aging': row[1] or 0,
'critical_aging': row[2] or 0,
'avg_cycle_time_days': round(row[3] or 0, 1)
}
def _calculate_deviation_metrics(self) -> Dict[str, Any]:
"""Calculate deviation-related metrics."""
with connection.cursor() as cursor:
cursor.execute("""
SELECT
COUNT(*) FILTER (WHERE status IN ('open', 'under_investigation')
AND due_date < CURRENT_DATE) AS overdue,
COUNT(*) FILTER (WHERE severity = 'critical'
AND status IN ('open', 'under_investigation')) AS critical_open,
json_object_agg(
severity,
count
) AS severity_breakdown
FROM (
SELECT severity, COUNT(*) as count
FROM compliance_deviation
WHERE tenant_id = %s
AND status IN ('open', 'under_investigation')
GROUP BY severity
) AS subq,
compliance_deviation
WHERE compliance_deviation.tenant_id = %s
GROUP BY 1
""", [self.tenant_id, self.tenant_id])
row = cursor.fetchone()
return {
'overdue': row[0] or 0,
'critical_open': row[1] or 0,
'severity_breakdown': row[2] or {}
}
def refresh_all_metrics(self):
"""
Refresh all metrics and update cache.
"""
summary = self.calculate_summary()
cache_key = f'compliance_summary_{self.tenant_id}'
cache.set(cache_key, summary, 300) # 5 minutes
logger.info(f'Refreshed metrics for tenant {self.tenant_id}')
return summary
10. Database Schema
10.1 Core Compliance Tables
-- compliance_capa table
CREATE TABLE compliance_capa (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
capa_number VARCHAR(50) NOT NULL UNIQUE,
title VARCHAR(500) NOT NULL,
description TEXT,
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'major', 'minor')),
status VARCHAR(50) NOT NULL CHECK (status IN ('open', 'in_progress', 'under_review', 'closed', 'cancelled')),
root_cause TEXT,
corrective_action TEXT,
preventive_action TEXT,
assigned_to UUID REFERENCES users(id),
assigned_department VARCHAR(100),
originating_department VARCHAR(100),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
created_by UUID NOT NULL REFERENCES users(id),
due_date DATE,
closed_at TIMESTAMP WITH TIME ZONE,
closed_by UUID REFERENCES users(id),
verification_status VARCHAR(50),
effectiveness_check_date DATE,
metadata JSONB DEFAULT '{}',
CONSTRAINT capa_tenant_number_unique UNIQUE (tenant_id, capa_number)
);
CREATE INDEX idx_capa_tenant ON compliance_capa(tenant_id);
CREATE INDEX idx_capa_status ON compliance_capa(status);
CREATE INDEX idx_capa_assigned ON compliance_capa(assigned_to);
CREATE INDEX idx_capa_aging ON compliance_capa(tenant_id, status, created_at) WHERE status IN ('open', 'in_progress');
-- compliance_deviation table
CREATE TABLE compliance_deviation (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
deviation_number VARCHAR(50) NOT NULL UNIQUE,
title VARCHAR(500) NOT NULL,
description TEXT,
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'major', 'minor')),
status VARCHAR(50) NOT NULL CHECK (status IN ('open', 'under_investigation', 'resolved', 'closed')),
root_cause_category VARCHAR(100),
root_cause_detail TEXT,
impact_assessment TEXT,
assigned_to UUID REFERENCES users(id),
department VARCHAR(100),
occurred_at TIMESTAMP WITH TIME ZONE,
detected_at TIMESTAMP WITH TIME ZONE,
reported_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
reported_by UUID NOT NULL REFERENCES users(id),
due_date DATE,
closed_at TIMESTAMP WITH TIME ZONE,
linked_capa_id UUID REFERENCES compliance_capa(id),
metadata JSONB DEFAULT '{}',
CONSTRAINT deviation_tenant_number_unique UNIQUE (tenant_id, deviation_number)
);
CREATE INDEX idx_deviation_tenant ON compliance_deviation(tenant_id);
CREATE INDEX idx_deviation_status ON compliance_deviation(status);
CREATE INDEX idx_deviation_root_cause ON compliance_deviation(root_cause_category);
CREATE INDEX idx_deviation_overdue ON compliance_deviation(tenant_id, status, due_date) WHERE status IN ('open', 'under_investigation') AND due_date < CURRENT_DATE;
-- compliance_training_status table
CREATE TABLE compliance_training_status (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
employee_id UUID NOT NULL REFERENCES users(id),
employee_name VARCHAR(200),
employee_status VARCHAR(20) DEFAULT 'active',
department VARCHAR(100),
role VARCHAR(100),
training_current BOOLEAN DEFAULT FALSE,
required_training_ids UUID[],
completed_training_ids UUID[],
overdue_training_ids UUID[],
training_overdue_days INTEGER DEFAULT 0,
last_training_completed_at TIMESTAMP WITH TIME ZONE,
next_training_due DATE,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
CONSTRAINT training_status_tenant_employee_unique UNIQUE (tenant_id, employee_id)
);
CREATE INDEX idx_training_status_tenant ON compliance_training_status(tenant_id);
CREATE INDEX idx_training_status_dept ON compliance_training_status(department);
CREATE INDEX idx_training_status_overdue ON compliance_training_status(tenant_id, training_current, training_overdue_days);
-- compliance_training_assignment table
CREATE TABLE compliance_training_assignment (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
training_id UUID NOT NULL,
training_name VARCHAR(200),
assigned_to UUID NOT NULL REFERENCES users(id),
assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
due_date DATE,
status VARCHAR(50) NOT NULL CHECK (status IN ('assigned', 'in_progress', 'completed', 'overdue')),
completed_at TIMESTAMP WITH TIME ZONE,
score INTEGER,
metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_training_assignment_tenant ON compliance_training_assignment(tenant_id);
CREATE INDEX idx_training_assignment_user ON compliance_training_assignment(assigned_to);
CREATE INDEX idx_training_assignment_completion ON compliance_training_assignment(status, completed_at);
-- compliance_audit_finding table
CREATE TABLE compliance_audit_finding (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
finding_number VARCHAR(50) NOT NULL UNIQUE,
audit_type VARCHAR(50) NOT NULL CHECK (audit_type IN ('internal', 'external_fda', 'external_soc2', 'external_hipaa', 'external_other')),
audit_id UUID,
audit_name VARCHAR(200),
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'major', 'minor', 'observation')),
status VARCHAR(50) NOT NULL CHECK (status IN ('open', 'in_progress', 'under_review', 'closed')),
finding_date DATE NOT NULL,
finding_description TEXT,
requirement_reference VARCHAR(200),
corrective_action_plan TEXT,
assigned_to UUID REFERENCES users(id),
target_closure_date DATE,
closed_at TIMESTAMP WITH TIME ZONE,
linked_capa_id UUID REFERENCES compliance_capa(id),
evidence_ids UUID[],
metadata JSONB DEFAULT '{}',
CONSTRAINT audit_finding_tenant_number_unique UNIQUE (tenant_id, finding_number)
);
CREATE INDEX idx_audit_finding_tenant ON compliance_audit_finding(tenant_id);
CREATE INDEX idx_audit_finding_type ON compliance_audit_finding(audit_type);
CREATE INDEX idx_audit_finding_status ON compliance_audit_finding(status);
CREATE INDEX idx_audit_finding_aging ON compliance_audit_finding(tenant_id, status, severity, finding_date) WHERE status IN ('open', 'in_progress');
-- compliance_document_review table
CREATE TABLE compliance_document_review (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
document_id UUID NOT NULL,
document_type VARCHAR(100),
document_title VARCHAR(500),
submitted_for_review_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
submitted_by UUID NOT NULL REFERENCES users(id),
assigned_to UUID REFERENCES users(id),
status VARCHAR(50) NOT NULL CHECK (status IN ('pending', 'in_review', 'approved', 'rejected', 'revision_required')),
approved_at TIMESTAMP WITH TIME ZONE,
approved_by UUID REFERENCES users(id),
review_notes TEXT,
metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_doc_review_tenant ON compliance_document_review(tenant_id);
CREATE INDEX idx_doc_review_status ON compliance_document_review(status);
CREATE INDEX idx_doc_review_cycle ON compliance_document_review(tenant_id, status, submitted_for_review_at, approved_at);
-- compliance_electronic_signature table
CREATE TABLE compliance_electronic_signature (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
entity_type VARCHAR(100) NOT NULL,
entity_id UUID NOT NULL,
signer_id UUID NOT NULL REFERENCES users(id),
signed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
meaning TEXT NOT NULL,
has_signer_name BOOLEAN DEFAULT TRUE,
has_signed_at BOOLEAN DEFAULT TRUE,
has_meaning BOOLEAN DEFAULT TRUE,
cryptographic_binding_hash VARCHAR(64),
cryptographic_binding_verified BOOLEAN DEFAULT FALSE,
re_auth_performed BOOLEAN DEFAULT FALSE,
metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_esig_tenant ON compliance_electronic_signature(tenant_id);
CREATE INDEX idx_esig_entity ON compliance_electronic_signature(entity_type, entity_id);
CREATE INDEX idx_esig_signer ON compliance_electronic_signature(signer_id);
CREATE INDEX idx_esig_compliance ON compliance_electronic_signature(tenant_id, signed_at, has_signer_name, has_signed_at, has_meaning);
-- compliance_system_validation table
CREATE TABLE compliance_system_validation (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
system_name VARCHAR(200) NOT NULL,
system_id VARCHAR(100),
validation_phase VARCHAR(20) NOT NULL CHECK (validation_phase IN ('IQ', 'OQ', 'PQ', 'revalidation')),
status VARCHAR(50) NOT NULL CHECK (status IN ('not_started', 'in_progress', 'completed', 'approved')),
scheduled_date DATE,
completed_date DATE,
approved_date DATE,
approved_by UUID REFERENCES users(id),
next_revalidation_date DATE,
protocol_document_id UUID,
report_document_id UUID,
metadata JSONB DEFAULT '{}',
CONSTRAINT validation_tenant_system_phase_unique UNIQUE (tenant_id, system_name, validation_phase)
);
CREATE INDEX idx_validation_tenant ON compliance_system_validation(tenant_id);
CREATE INDEX idx_validation_status ON compliance_system_validation(status);
CREATE INDEX idx_validation_revalidation ON compliance_system_validation(tenant_id, status, next_revalidation_date);
-- compliance_alert table
CREATE TABLE compliance_alert (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
threshold_id UUID REFERENCES compliance_alert_threshold(id),
metric VARCHAR(100) NOT NULL,
severity VARCHAR(20) NOT NULL CHECK (severity IN ('info', 'warning', 'error', 'critical')),
title VARCHAR(500) NOT NULL,
message TEXT NOT NULL,
current_value NUMERIC,
threshold_value NUMERIC,
status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'acknowledged', 'resolved')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
acknowledged_at TIMESTAMP WITH TIME ZONE,
acknowledged_by UUID REFERENCES users(id),
resolved_at TIMESTAMP WITH TIME ZONE,
escalated_at TIMESTAMP WITH TIME ZONE,
escalated_to UUID[],
metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_alert_tenant ON compliance_alert(tenant_id);
CREATE INDEX idx_alert_status ON compliance_alert(status);
CREATE INDEX idx_alert_severity ON compliance_alert(severity);
CREATE INDEX idx_alert_created ON compliance_alert(created_at DESC);
-- compliance_alert_threshold table
CREATE TABLE compliance_alert_threshold (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
name VARCHAR(200) NOT NULL,
metric VARCHAR(100) NOT NULL,
condition VARCHAR(10) NOT NULL CHECK (condition IN ('gt', 'lt', 'eq')),
threshold NUMERIC NOT NULL,
severity VARCHAR(20) NOT NULL CHECK (severity IN ('info', 'warning', 'error', 'critical')),
enabled BOOLEAN DEFAULT TRUE,
notification_channels VARCHAR(50)[],
escalation_chain_id UUID,
cooldown_minutes INTEGER DEFAULT 60,
metadata JSONB DEFAULT '{}',
CONSTRAINT threshold_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_threshold_tenant ON compliance_alert_threshold(tenant_id);
CREATE INDEX idx_threshold_enabled ON compliance_alert_threshold(enabled);
-- compliance_framework_control table
CREATE TABLE compliance_framework_control (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
framework VARCHAR(50) NOT NULL CHECK (framework IN ('FDA_21_CFR_PART_11', 'HIPAA', 'SOC2', 'ISO_13485', 'ISO_27001')),
control_category VARCHAR(100),
control_id VARCHAR(50) NOT NULL,
control_description TEXT,
control_status VARCHAR(50) NOT NULL CHECK (control_status IN ('compliant', 'partial', 'non_compliant', 'not_applicable')),
last_assessment_date DATE,
assessed_by UUID REFERENCES users(id),
next_assessment_due DATE,
evidence_count INTEGER DEFAULT 0,
evidence_ids UUID[],
notes TEXT,
metadata JSONB DEFAULT '{}',
CONSTRAINT framework_control_tenant_framework_id_unique UNIQUE (tenant_id, framework, control_id)
);
CREATE INDEX idx_framework_control_tenant ON compliance_framework_control(tenant_id);
CREATE INDEX idx_framework_control_framework ON compliance_framework_control(framework);
CREATE INDEX idx_framework_control_status ON compliance_framework_control(control_status);
-- compliance_kpi_history table
CREATE TABLE compliance_kpi_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
metric_name VARCHAR(100) NOT NULL,
metric_value NUMERIC,
metric_metadata JSONB DEFAULT '{}',
recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
period_type VARCHAR(20) CHECK (period_type IN ('daily', 'weekly', 'monthly', 'quarterly')),
period_start DATE,
period_end DATE
);
CREATE INDEX idx_kpi_history_tenant ON compliance_kpi_history(tenant_id);
CREATE INDEX idx_kpi_history_metric ON compliance_kpi_history(metric_name);
CREATE INDEX idx_kpi_history_recorded ON compliance_kpi_history(recorded_at DESC);
CREATE INDEX idx_kpi_history_period ON compliance_kpi_history(tenant_id, metric_name, period_type, period_start);
10.2 Materialized Views
-- Materialized view: CAPA aging summary
CREATE MATERIALIZED VIEW mv_capa_aging AS
SELECT
tenant_id,
capa_id AS id,
capa_number,
title,
severity,
assigned_to,
status,
created_at,
due_date,
CURRENT_DATE - created_at::date AS age_days,
CASE
WHEN CURRENT_DATE - created_at::date > 90 THEN 'red'
WHEN CURRENT_DATE - created_at::date > 30 THEN 'yellow'
ELSE 'green'
END AS aging_status
FROM compliance_capa
WHERE status IN ('open', 'in_progress', 'under_review');
CREATE UNIQUE INDEX idx_mv_capa_aging_id ON mv_capa_aging(id);
CREATE INDEX idx_mv_capa_aging_tenant ON mv_capa_aging(tenant_id);
CREATE INDEX idx_mv_capa_aging_status ON mv_capa_aging(aging_status);
-- Refresh policy: Every 5 minutes via background job
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_capa_aging;
-- Materialized view: Training compliance summary
CREATE MATERIALIZED VIEW mv_training_compliance AS
SELECT
tenant_id,
department,
role,
COUNT(*) AS total_employees,
COUNT(*) FILTER (WHERE training_current = TRUE) AS compliant_employees,
(COUNT(*) FILTER (WHERE training_current = TRUE)::float / COUNT(*) * 100) AS compliance_pct,
COUNT(*) FILTER (WHERE training_overdue_days > 0) AS overdue_count,
AVG(CASE WHEN training_overdue_days > 0 THEN training_overdue_days END) AS avg_overdue_days
FROM compliance_training_status
WHERE employee_status = 'active'
GROUP BY tenant_id, department, role;
CREATE INDEX idx_mv_training_comp_tenant ON mv_training_compliance(tenant_id);
CREATE INDEX idx_mv_training_comp_dept ON mv_training_compliance(department);
-- Materialized view: Deviation trends
CREATE MATERIALIZED VIEW mv_deviation_trends AS
WITH deviation_groups AS (
SELECT
tenant_id,
root_cause_category,
COUNT(*) AS total_occurrences,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '90 days') AS recent_occurrences,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '180 days'
AND created_at < CURRENT_DATE - INTERVAL '90 days') AS prior_occurrences
FROM compliance_deviation
WHERE status IN ('closed', 'resolved')
AND root_cause_category IS NOT NULL
GROUP BY tenant_id, root_cause_category
)
SELECT
tenant_id,
root_cause_category,
total_occurrences,
recent_occurrences,
prior_occurrences,
CASE
WHEN prior_occurrences > 0
THEN ((recent_occurrences - prior_occurrences)::float / prior_occurrences * 100)
ELSE NULL
END AS recurrence_rate_change_pct
FROM deviation_groups
WHERE total_occurrences >= 3;
CREATE INDEX idx_mv_deviation_trends_tenant ON mv_deviation_trends(tenant_id);
CREATE INDEX idx_mv_deviation_trends_recent ON mv_deviation_trends(recent_occurrences DESC);
-- Materialized view: Audit findings summary
CREATE MATERIALIZED VIEW mv_audit_findings_summary AS
SELECT
tenant_id,
audit_type,
severity,
status,
COUNT(*) AS count,
AVG(CURRENT_DATE - finding_date::date) AS avg_age_days
FROM compliance_audit_finding
WHERE status IN ('open', 'in_progress', 'under_review')
GROUP BY tenant_id, audit_type, severity, status;
CREATE INDEX idx_mv_audit_summary_tenant ON mv_audit_findings_summary(tenant_id);
CREATE INDEX idx_mv_audit_summary_type ON mv_audit_findings_summary(audit_type);
-- Materialized view: Framework compliance scores
CREATE MATERIALIZED VIEW mv_framework_compliance AS
SELECT
tenant_id,
framework,
control_category,
COUNT(*) AS total_controls,
COUNT(*) FILTER (WHERE control_status = 'compliant') AS compliant_controls,
COUNT(*) FILTER (WHERE control_status = 'partial') AS partial_controls,
COUNT(*) FILTER (WHERE control_status = 'non_compliant') AS non_compliant_controls,
(COUNT(*) FILTER (WHERE control_status = 'compliant')::float / COUNT(*) * 100) AS compliance_pct
FROM compliance_framework_control
WHERE control_status != 'not_applicable'
GROUP BY tenant_id, framework, control_category;
CREATE INDEX idx_mv_framework_comp_tenant ON mv_framework_compliance(tenant_id);
CREATE INDEX idx_mv_framework_comp_framework ON mv_framework_compliance(framework);
-- Materialized view: Compliance summary (overall dashboard metrics)
CREATE MATERIALIZED VIEW mv_compliance_summary AS
SELECT
t.id AS tenant_id,
-- CAPA metrics
(SELECT COUNT(*) FROM compliance_capa WHERE tenant_id = t.id AND status IN ('open', 'in_progress')) AS capa_open,
(SELECT COUNT(*) FROM compliance_capa WHERE tenant_id = t.id AND status IN ('open', 'in_progress') AND CURRENT_DATE - created_at::date > 30) AS capa_aging,
-- Deviation metrics
(SELECT COUNT(*) FROM compliance_deviation WHERE tenant_id = t.id AND status IN ('open', 'under_investigation') AND due_date < CURRENT_DATE) AS deviation_overdue,
(SELECT COUNT(*) FROM compliance_deviation WHERE tenant_id = t.id AND severity = 'critical' AND status IN ('open', 'under_investigation')) AS deviation_critical,
-- Training metrics
(SELECT AVG(compliance_pct) FROM mv_training_compliance WHERE tenant_id = t.id) AS training_compliance_pct,
(SELECT SUM(overdue_count) FROM mv_training_compliance WHERE tenant_id = t.id) AS training_overdue_count,
-- Audit metrics
(SELECT COUNT(*) FROM compliance_audit_finding WHERE tenant_id = t.id AND severity = 'critical' AND status IN ('open', 'in_progress')) AS audit_critical_open,
(SELECT COUNT(*) FROM compliance_audit_finding WHERE tenant_id = t.id AND status IN ('open', 'in_progress')) AS audit_total_open,
-- Validation metrics
(SELECT COUNT(*) FROM compliance_system_validation WHERE tenant_id = t.id AND status = 'approved' AND next_revalidation_date < CURRENT_DATE) AS validation_overdue,
-- Framework scores
(SELECT AVG(compliance_pct) FROM mv_framework_compliance WHERE tenant_id = t.id AND framework = 'FDA_21_CFR_PART_11') AS fda_score,
(SELECT AVG(compliance_pct) FROM mv_framework_compliance WHERE tenant_id = t.id AND framework = 'HIPAA') AS hipaa_score,
(SELECT AVG(compliance_pct) FROM mv_framework_compliance WHERE tenant_id = t.id AND framework = 'SOC2') AS soc2_score,
NOW() AS last_refreshed
FROM tenants t
WHERE t.is_active = TRUE;
CREATE UNIQUE INDEX idx_mv_compliance_summary_tenant ON mv_compliance_summary(tenant_id);
10.3 Partitioning Strategy
-- Partition compliance_kpi_history by month for performance
CREATE TABLE compliance_kpi_history_2026_02 PARTITION OF compliance_kpi_history
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE compliance_kpi_history_2026_03 PARTITION OF compliance_kpi_history
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- Automatic partition creation via maintenance job
CREATE OR REPLACE FUNCTION create_kpi_history_partitions()
RETURNS void AS $$
DECLARE
start_date DATE;
end_date DATE;
partition_name TEXT;
BEGIN
FOR i IN 0..3 LOOP -- Create partitions for next 3 months
start_date := DATE_TRUNC('month', CURRENT_DATE + (i || ' months')::INTERVAL);
end_date := start_date + INTERVAL '1 month';
partition_name := 'compliance_kpi_history_' || TO_CHAR(start_date, 'YYYY_MM');
IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = partition_name) THEN
EXECUTE format('CREATE TABLE %I PARTITION OF compliance_kpi_history FOR VALUES FROM (%L) TO (%L)',
partition_name, start_date, end_date);
RAISE NOTICE 'Created partition %', partition_name;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Run monthly via cron/scheduler
-- SELECT create_kpi_history_partitions();
11. React Components
11.1 Dashboard Layout Component
// ComplianceDashboard.tsx
import React, { useEffect, useState } from 'react';
import { useSelector, useDispatch } from 'react-redux';
import { socket } from '@/lib/socket';
import { RootState } from '@/store';
import { setMetrics, updateMetric } from '@/store/complianceSlice';
import { addAlert } from '@/store/alertsSlice';
import { ComplianceOverview } from './components/ComplianceOverview';
import { CapaTracker } from './components/CapaTracker';
import { DeviationMonitor } from './components/DeviationMonitor';
import { TrainingComplianceView } from './components/TrainingComplianceView';
import { AuditFindingsView } from './components/AuditFindingsView';
import { FrameworkHeatmap } from './components/FrameworkHeatmap';
import { AlertsPanel } from './components/AlertsPanel';
import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card';
import { Tabs, TabsContent, TabsList, TabsTrigger } from '@/components/ui/tabs';
export const ComplianceDashboard: React.FC = () => {
const dispatch = useDispatch();
const currentUser = useSelector((state: RootState) => state.auth.user);
const metrics = useSelector((state: RootState) => state.compliance.metrics);
const [activeTab, setActiveTab] = useState('overview');
useEffect(() => {
// Subscribe to real-time compliance updates
socket.emit('subscribe', { channel: `compliance:${currentUser.tenantId}` });
socket.on('metric:update', (data) => {
dispatch(updateMetric(data));
});
socket.on('alert:new', (alert) => {
dispatch(addAlert(alert));
});
// Load initial data
fetchInitialMetrics();
return () => {
socket.emit('unsubscribe', { channel: `compliance:${currentUser.tenantId}` });
socket.off('metric:update');
socket.off('alert:new');
};
}, [currentUser.tenantId]);
const fetchInitialMetrics = async () => {
try {
const response = await fetch('/api/compliance/metrics/summary/', {
headers: { 'Authorization': `Bearer ${currentUser.token}` }
});
const data = await response.json();
dispatch(setMetrics(data));
} catch (error) {
console.error('Failed to fetch compliance metrics:', error);
}
};
// Role-based view determination
const renderDashboardByRole = () => {
switch (currentUser.role) {
case 'quality_manager':
return <QualityManagerView metrics={metrics} />;
case 'department_head':
return <DepartmentHeadView metrics={metrics} department={currentUser.department} />;
case 'compliance_officer':
return <ComplianceOfficerView metrics={metrics} />;
case 'executive':
return <ExecutiveView metrics={metrics} />;
case 'auditor':
return <AuditorView metrics={metrics} access={currentUser.auditorAccess} />;
default:
return <ComplianceOverview metrics={metrics} />;
}
};
return (
<div className="compliance-dashboard p-6">
<header className="mb-6">
<h1 className="text-3xl font-bold">Compliance Monitoring Dashboard</h1>
<p className="text-gray-600">
Real-time regulatory compliance oversight | {currentUser.name} ({currentUser.role})
</p>
</header>
<AlertsPanel />
<Tabs value={activeTab} onValueChange={setActiveTab} className="mt-6">
<TabsList>
<TabsTrigger value="overview">Overview</TabsTrigger>
<TabsTrigger value="capa">CAPA</TabsTrigger>
<TabsTrigger value="deviations">Deviations</TabsTrigger>
<TabsTrigger value="training">Training</TabsTrigger>
<TabsTrigger value="audits">Audits</TabsTrigger>
<TabsTrigger value="frameworks">Frameworks</TabsTrigger>
</TabsList>
<TabsContent value="overview">
{renderDashboardByRole()}
</TabsContent>
<TabsContent value="capa">
<CapaTracker />
</TabsContent>
<TabsContent value="deviations">
<DeviationMonitor />
</TabsContent>
<TabsContent value="training">
<TrainingComplianceView />
</TabsContent>
<TabsContent value="audits">
<AuditFindingsView />
</TabsContent>
<TabsContent value="frameworks">
<FrameworkHeatmap />
</TabsContent>
</Tabs>
</div>
);
};
11.2 CAPA Aging Chart Component
// components/CapaAgingChart.tsx
import React from 'react';
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip, Legend, ResponsiveContainer, Cell } from 'recharts';
import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card';
interface CapaAgingData {
ageRange: string;
count: number;
severity: 'critical' | 'major' | 'minor';
}
interface Props {
data: CapaAgingData[];
}
export const CapaAgingChart: React.FC<Props> = ({ data }) => {
const COLORS = {
'0-30': '#22c55e',
'31-60': '#eab308',
'61-90': '#f97316',
'90+': '#ef4444'
};
const aggregatedData = [
{ range: '0-30 days', count: data.filter(d => d.ageRange === '0-30').length },
{ range: '31-60 days', count: data.filter(d => d.ageRange === '31-60').length },
{ range: '61-90 days', count: data.filter(d => d.ageRange === '61-90').length },
{ range: '90+ days', count: data.filter(d => d.ageRange === '90+').length }
];
return (
<Card>
<CardHeader>
<CardTitle>CAPA Aging Distribution</CardTitle>
</CardHeader>
<CardContent>
<ResponsiveContainer width="100%" height={300}>
<BarChart data={aggregatedData}>
<CartesianGrid strokeDasharray="3 3" />
<XAxis dataKey="range" />
<YAxis />
<Tooltip />
<Legend />
<Bar dataKey="count" fill="#8884d8">
{aggregatedData.map((entry, index) => (
<Cell key={`cell-${index}`} fill={Object.values(COLORS)[index]} />
))}
</Bar>
</BarChart>
</ResponsiveContainer>
<div className="mt-4">
<h4 className="font-semibold mb-2">Top 10 Oldest CAPAs</h4>
<table className="w-full text-sm">
<thead>
<tr className="border-b">
<th className="text-left p-2">CAPA #</th>
<th className="text-left p-2">Title</th>
<th className="text-left p-2">Age (days)</th>
<th className="text-left p-2">Severity</th>
<th className="text-left p-2">Assigned To</th>
</tr>
</thead>
<tbody>
{data.slice(0, 10).map((capa) => (
<tr key={capa.id} className="border-b hover:bg-gray-50">
<td className="p-2">{capa.capaNumber}</td>
<td className="p-2">{capa.title}</td>
<td className="p-2 font-semibold">{capa.ageDays}</td>
<td className="p-2">
<span className={`badge ${capa.severity}`}>{capa.severity}</span>
</td>
<td className="p-2">{capa.assignedTo}</td>
</tr>
))}
</tbody>
</table>
</div>
</CardContent>
</Card>
);
};
11.3 Training Compliance Heatmap
// components/TrainingHeatmap.tsx
import React from 'react';
import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card';
import { Tooltip } from '@/components/ui/tooltip';
interface TrainingData {
department: string;
role: string;
compliancePct: number;
overdueCount: number;
}
interface Props {
data: TrainingData[];
}
export const TrainingHeatmap: React.FC<Props> = ({ data }) => {
const departments = [...new Set(data.map(d => d.department))];
const roles = [...new Set(data.map(d => d.role))];
const getColor = (pct: number) => {
if (pct >= 95) return 'bg-green-500';
if (pct >= 90) return 'bg-yellow-500';
if (pct >= 70) return 'bg-orange-500';
return 'bg-red-500';
};
const getCellData = (dept: string, role: string) => {
return data.find(d => d.department === dept && d.role === role);
};
return (
<Card>
<CardHeader>
<CardTitle>Training Compliance by Department & Role</CardTitle>
</CardHeader>
<CardContent>
<div className="overflow-x-auto">
<table className="w-full border-collapse">
<thead>
<tr>
<th className="border p-2 bg-gray-100">Department</th>
{roles.map(role => (
<th key={role} className="border p-2 bg-gray-100">{role}</th>
))}
</tr>
</thead>
<tbody>
{departments.map(dept => (
<tr key={dept}>
<td className="border p-2 font-semibold">{dept}</td>
{roles.map(role => {
const cellData = getCellData(dept, role);
if (!cellData) {
return <td key={role} className="border p-2 bg-gray-200">-</td>;
}
return (
<Tooltip
key={role}
content={`${cellData.compliancePct}% compliant\n${cellData.overdueCount} overdue`}
>
<td
className={`border p-2 text-center cursor-pointer ${getColor(cellData.compliancePct)} text-white`}
onClick={() => handleCellClick(dept, role)}
>
{cellData.compliancePct}%
</td>
</Tooltip>
);
})}
</tr>
))}
</tbody>
</table>
</div>
<div className="mt-4 flex items-center gap-4 text-sm">
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-green-500"></div>
≥95% (Target)
</span>
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-yellow-500"></div>
90-94%
</span>
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-orange-500"></div>
70-89%
</span>
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-red-500"></div>
<70%
</span>
</div>
</CardContent>
</Card>
);
function handleCellClick(dept: string, role: string) {
// Navigate to detailed employee list for this dept/role
window.location.href = `/compliance/training/details?dept=${dept}&role=${role}`;
}
};
11.4 Framework Compliance Heatmap
// components/FrameworkHeatmap.tsx
import React from 'react';
import { Card, CardHeader, CardTitle, CardContent } from '@/components/ui/card';
import { Tooltip } from '@/components/ui/tooltip';
interface ComplianceCell {
framework: string;
category: string;
score: number;
status: 'compliant' | 'partial' | 'non_compliant';
details: string;
}
interface Props {
data: ComplianceCell[];
}
export const FrameworkHeatmap: React.FC<Props> = ({ data }) => {
const frameworks = ['FDA 21 CFR Part 11', 'HIPAA', 'SOC 2'];
const categories = ['Access Control', 'Audit Trail', 'Validation', 'Encryption', 'Training'];
const getColor = (score: number) => {
if (score >= 90) return 'bg-green-500 text-white';
if (score >= 70) return 'bg-yellow-500 text-black';
return 'bg-red-500 text-white';
};
const getCellData = (framework: string, category: string) => {
return data.find(d => d.framework === framework && d.category === category);
};
return (
<Card>
<CardHeader>
<CardTitle>Multi-Framework Compliance Heatmap</CardTitle>
</CardHeader>
<CardContent>
<div className="overflow-x-auto">
<table className="w-full border-collapse">
<thead>
<tr>
<th className="border p-3 bg-gray-100">Framework</th>
{categories.map(cat => (
<th key={cat} className="border p-3 bg-gray-100">{cat}</th>
))}
<th className="border p-3 bg-gray-100">Overall</th>
</tr>
</thead>
<tbody>
{frameworks.map(framework => {
const frameworkData = data.filter(d => d.framework === framework);
const overallScore = frameworkData.reduce((sum, d) => sum + d.score, 0) / frameworkData.length;
return (
<tr key={framework}>
<td className="border p-3 font-semibold">{framework}</td>
{categories.map(category => {
const cell = getCellData(framework, category);
const score = cell?.score ?? 0;
return (
<Tooltip key={category} content={cell?.details ?? 'No data'}>
<td className={`border p-3 text-center cursor-pointer ${getColor(score)}`}>
{score}%
</td>
</Tooltip>
);
})}
<td className={`border p-3 text-center font-bold ${getColor(overallScore)}`}>
{Math.round(overallScore)}%
</td>
</tr>
);
})}
</tbody>
</table>
</div>
<div className="mt-6">
<h4 className="font-semibold mb-2">Legend</h4>
<div className="flex gap-4 text-sm">
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-green-500"></div>
≥90% (Compliant)
</span>
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-yellow-500"></div>
70-89% (Partial)
</span>
<span className="flex items-center gap-2">
<div className="w-4 h-4 bg-red-500"></div>
<70% (Non-Compliant)
</span>
</div>
</div>
</CardContent>
</Card>
);
};
11.5 Alerts Panel Component
// components/AlertsPanel.tsx
import React, { useState } from 'react';
import { useSelector, useDispatch } from 'react-redux';
import { RootState } from '@/store';
import { acknowledgeAlert } from '@/store/alertsSlice';
import { Alert, AlertDescription, AlertTitle } from '@/components/ui/alert';
import { Button } from '@/components/ui/button';
import { Badge } from '@/components/ui/badge';
import { Bell, X, CheckCircle } from 'lucide-react';
export const AlertsPanel: React.FC = () => {
const dispatch = useDispatch();
const alerts = useSelector((state: RootState) => state.alerts.alerts);
const unreadCount = useSelector((state: RootState) => state.alerts.unreadCount);
const [isOpen, setIsOpen] = useState(false);
const activeAlerts = alerts.filter(a => a.status === 'active');
const getSeverityColor = (severity: string) => {
switch (severity) {
case 'critical': return 'destructive';
case 'error': return 'destructive';
case 'warning': return 'default';
case 'info': return 'secondary';
default: return 'default';
}
};
const handleAcknowledge = async (alertId: string) => {
try {
await fetch(`/api/compliance/alerts/${alertId}/acknowledge/`, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': `Bearer ${localStorage.getItem('token')}`
}
});
dispatch(acknowledgeAlert(alertId));
} catch (error) {
console.error('Failed to acknowledge alert:', error);
}
};
return (
<div className="alerts-panel fixed top-4 right-4 z-50">
<Button
variant="outline"
size="icon"
onClick={() => setIsOpen(!isOpen)}
className="relative"
>
<Bell className="h-5 w-5" />
{unreadCount > 0 && (
<Badge variant="destructive" className="absolute -top-2 -right-2 h-5 w-5 flex items-center justify-center p-0">
{unreadCount}
</Badge>
)}
</Button>
{isOpen && (
<div className="absolute right-0 mt-2 w-96 max-h-96 overflow-y-auto bg-white rounded-lg shadow-lg border">
<div className="p-4 border-b flex items-center justify-between">
<h3 className="font-semibold">Active Alerts ({activeAlerts.length})</h3>
<Button variant="ghost" size="icon" onClick={() => setIsOpen(false)}>
<X className="h-4 w-4" />
</Button>
</div>
<div className="divide-y">
{activeAlerts.length === 0 ? (
<div className="p-4 text-center text-gray-500">
No active alerts
</div>
) : (
activeAlerts.map(alert => (
<Alert key={alert.id} variant={getSeverityColor(alert.severity)} className="m-2">
<AlertTitle className="flex items-center justify-between">
<span>{alert.title}</span>
<Badge variant={getSeverityColor(alert.severity)}>
{alert.severity.toUpperCase()}
</Badge>
</AlertTitle>
<AlertDescription className="mt-2">
{alert.message}
</AlertDescription>
<div className="mt-4 flex items-center justify-between text-sm">
<span className="text-gray-500">
{new Date(alert.createdAt).toLocaleString()}
</span>
<Button
size="sm"
variant="outline"
onClick={() => handleAcknowledge(alert.id)}
>
<CheckCircle className="h-4 w-4 mr-1" />
Acknowledge
</Button>
</div>
</Alert>
))
)}
</div>
</div>
)}
</div>
);
};
12. WebSocket Events
12.1 Event Types
// types/websocket.ts
export enum WebSocketEvent {
// Metric updates
METRIC_UPDATE = 'metric:update',
METRICS_REFRESH = 'metrics:refresh',
// Alert events
ALERT_NEW = 'alert:new',
ALERT_ACKNOWLEDGED = 'alert:acknowledged',
ALERT_RESOLVED = 'alert:resolved',
ALERT_ESCALATED = 'alert:escalated',
// KPI threshold breaches
KPI_THRESHOLD_BREACH = 'kpi:threshold_breach',
// Real-time compliance updates
CAPA_CREATED = 'capa:created',
CAPA_UPDATED = 'capa:updated',
CAPA_CLOSED = 'capa:closed',
DEVIATION_CREATED = 'deviation:created',
DEVIATION_UPDATED = 'deviation:updated',
DEVIATION_CLOSED = 'deviation:closed',
TRAINING_COMPLETED = 'training:completed',
TRAINING_OVERDUE = 'training:overdue',
AUDIT_FINDING_CREATED = 'audit_finding:created',
AUDIT_FINDING_CLOSED = 'audit_finding:closed',
// Subscription management
SUBSCRIBE = 'subscribe',
UNSUBSCRIBE = 'unsubscribe',
}
export interface MetricUpdateEvent {
metric: string;
value: number;
previousValue?: number;
metadata?: Record<string, any>;
timestamp: string;
}
export interface AlertEvent {
alert: Alert;
action: 'created' | 'acknowledged' | 'resolved' | 'escalated';
performedBy?: string;
timestamp: string;
}
export interface CapaEvent {
capa: {
id: string;
capaNumber: string;
title: string;
severity: string;
status: string;
};
action: 'created' | 'updated' | 'closed';
performedBy: string;
timestamp: string;
}
12.2 WebSocket Server (Socket.io)
# websocket/compliance_events.py
from socketio import AsyncServer, ASGIApp
from typing import Dict, Any
import logging
logger = logging.getLogger(__name__)
# Initialize Socket.io server
sio = AsyncServer(async_mode='asgi', cors_allowed_origins='*')
app = ASGIApp(sio)
# Store connected clients by tenant and user
connected_clients: Dict[str, Dict[str, Any]] = {}
@sio.event
async def connect(sid, environ):
"""Handle client connection."""
logger.info(f'Client connected: {sid}')
@sio.event
async def disconnect(sid):
"""Handle client disconnection."""
# Remove from connected_clients
for tenant_id, clients in connected_clients.items():
if sid in clients:
del clients[sid]
logger.info(f'Client disconnected: {sid} from tenant {tenant_id}')
break
@sio.event
async def subscribe(sid, data):
"""Subscribe client to a specific channel."""
channel = data.get('channel')
tenant_id = data.get('tenantId')
user_id = data.get('userId')
if not all([channel, tenant_id, user_id]):
await sio.emit('error', {'message': 'Missing required subscription data'}, room=sid)
return
# Store client subscription
if tenant_id not in connected_clients:
connected_clients[tenant_id] = {}
connected_clients[tenant_id][sid] = {
'channel': channel,
'user_id': user_id,
'connected_at': timezone.now()
}
# Join the room
await sio.enter_room(sid, channel)
logger.info(f'Client {sid} subscribed to {channel}')
await sio.emit('subscribed', {'channel': channel}, room=sid)
@sio.event
async def unsubscribe(sid, data):
"""Unsubscribe client from a channel."""
channel = data.get('channel')
await sio.leave_room(sid, channel)
logger.info(f'Client {sid} unsubscribed from {channel}')
# Event emission functions
async def send_metric_update(tenant_id: str, metric: str, value: float, metadata: Dict = None):
"""Send metric update to all subscribed clients."""
channel = f'compliance:{tenant_id}'
await sio.emit('metric:update', {
'metric': metric,
'value': value,
'metadata': metadata or {},
'timestamp': timezone.now().isoformat()
}, room=channel)
async def send_alert_update(alert: Alert):
"""Send alert notification to subscribed clients."""
channel = f'compliance:{alert.tenant_id}'
await sio.emit('alert:new', {
'alert': {
'id': str(alert.id),
'severity': alert.severity,
'title': alert.title,
'message': alert.message,
'metric': alert.metric,
'currentValue': float(alert.current_value),
'thresholdValue': float(alert.threshold_value),
'createdAt': alert.created_at.isoformat()
},
'timestamp': timezone.now().isoformat()
}, room=channel)
async def send_capa_event(capa, action: str, performed_by: str):
"""Send CAPA lifecycle event."""
channel = f'compliance:{capa.tenant_id}'
await sio.emit('capa:' + action, {
'capa': {
'id': str(capa.id),
'capaNumber': capa.capa_number,
'title': capa.title,
'severity': capa.severity,
'status': capa.status
},
'action': action,
'performedBy': performed_by,
'timestamp': timezone.now().isoformat()
}, room=channel)
12.3 WebSocket Client (React)
// lib/socket.ts
import { io, Socket } from 'socket.io-client';
import { store } from '@/store';
import { updateMetric } from '@/store/complianceSlice';
import { addAlert } from '@/store/alertsSlice';
class WebSocketClient {
private socket: Socket | null = null;
private reconnectAttempts = 0;
private maxReconnectAttempts = 5;
connect(token: string, tenantId: string, userId: string) {
if (this.socket?.connected) {
console.log('WebSocket already connected');
return;
}
this.socket = io(process.env.NEXT_PUBLIC_WEBSOCKET_URL || 'ws://localhost:8000', {
auth: { token },
transports: ['websocket', 'polling'],
reconnection: true,
reconnectionDelay: 1000,
reconnectionDelayMax: 5000,
reconnectionAttempts: this.maxReconnectAttempts
});
this.socket.on('connect', () => {
console.log('WebSocket connected');
this.reconnectAttempts = 0;
// Subscribe to compliance channel
this.socket?.emit('subscribe', {
channel: `compliance:${tenantId}`,
tenantId,
userId
});
});
this.socket.on('disconnect', (reason) => {
console.log('WebSocket disconnected:', reason);
if (reason === 'io server disconnect') {
// Server initiated disconnect, reconnect manually
this.socket?.connect();
}
});
this.socket.on('connect_error', (error) => {
console.error('WebSocket connection error:', error);
this.reconnectAttempts++;
if (this.reconnectAttempts >= this.maxReconnectAttempts) {
console.error('Max reconnection attempts reached');
// Show user notification
store.dispatch(addAlert({
id: 'websocket-error',
severity: 'error',
title: 'Connection Lost',
message: 'Real-time updates unavailable. Please refresh the page.',
status: 'active',
createdAt: new Date()
}));
}
});
// Register event handlers
this.registerEventHandlers();
}
private registerEventHandlers() {
if (!this.socket) return;
this.socket.on('metric:update', (data) => {
store.dispatch(updateMetric({
metric: data.metric,
value: data.value,
metadata: data.metadata
}));
});
this.socket.on('alert:new', (data) => {
store.dispatch(addAlert(data.alert));
// Show toast notification for critical/error alerts
if (data.alert.severity === 'critical' || data.alert.severity === 'error') {
// Assuming you have a toast notification library
import('@/lib/toast').then(({ toast }) => {
toast.error(data.alert.title, {
description: data.alert.message,
duration: Infinity
});
});
}
});
this.socket.on('capa:created', (data) => {
console.log('New CAPA created:', data.capa);
// Trigger metrics refresh
store.dispatch({ type: 'compliance/triggerRefresh' });
});
// ... other event handlers
}
disconnect() {
if (this.socket) {
this.socket.disconnect();
this.socket = null;
}
}
subscribe(channel: string) {
this.socket?.emit('subscribe', { channel });
}
unsubscribe(channel: string) {
this.socket?.emit('unsubscribe', { channel });
}
}
export const socket = new WebSocketClient();
13. Export Capabilities
13.1 PDF Compliance Report
# services/export_service.py
from reportlab.lib import colors
from reportlab.lib.pagesizes import letter, A4
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak, Image
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
from django.utils import timezone
from io import BytesIO
import logging
logger = logging.getLogger(__name__)
class ComplianceReportExporter:
"""
Service for generating PDF compliance reports.
"""
def __init__(self, tenant_id: str, user):
self.tenant_id = tenant_id
self.user = user
self.styles = getSampleStyleSheet()
self._setup_custom_styles()
def _setup_custom_styles(self):
"""Setup custom paragraph styles."""
self.styles.add(ParagraphStyle(
name='CustomTitle',
parent=self.styles['Heading1'],
fontSize=24,
textColor=colors.HexColor('#1e40af'),
spaceAfter=30,
alignment=TA_CENTER
))
self.styles.add(ParagraphStyle(
name='SectionHeader',
parent=self.styles['Heading2'],
fontSize=16,
textColor=colors.HexColor('#1e40af'),
spaceAfter=12,
spaceBefore=12
))
def generate_executive_summary(self) -> BytesIO:
"""Generate executive summary PDF."""
buffer = BytesIO()
doc = SimpleDocTemplate(buffer, pagesize=letter)
elements = []
# Title
elements.append(Paragraph('Compliance Executive Summary', self.styles['CustomTitle']))
elements.append(Paragraph(
f'Generated: {timezone.now().strftime("%B %d, %Y %H:%M UTC")}',
self.styles['Normal']
))
elements.append(Spacer(1, 0.5 * inch))
# Overall Compliance Score
from .metrics_service import ComplianceMetricsService
service = ComplianceMetricsService(self.tenant_id)
summary = service.calculate_summary()
# Overall score calculation
overall_score = self._calculate_overall_score(summary)
elements.append(Paragraph('Overall Compliance Health', self.styles['SectionHeader']))
score_table = Table([
['Overall Score', f'{overall_score}%'],
['Status', self._get_status_text(overall_score)]
], colWidths=[3*inch, 2*inch])
score_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, -1), colors.HexColor('#f3f4f6')),
('TEXTCOLOR', (0, 0), (-1, -1), colors.black),
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('FONTNAME', (0, 0), (-1, -1), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, -1), 14),
('BOTTOMPADDING', (0, 0), (-1, -1), 12),
('GRID', (0, 0), (-1, -1), 1, colors.grey)
]))
elements.append(score_table)
elements.append(Spacer(1, 0.3 * inch))
# Framework Scores
elements.append(Paragraph('Framework Compliance Scores', self.styles['SectionHeader']))
framework_data = [
['Framework', 'Score', 'Status'],
['FDA 21 CFR Part 11', f'{summary.get("fda_score", 0):.1f}%', self._get_status_icon(summary.get("fda_score", 0))],
['HIPAA', f'{summary.get("hipaa_score", 0):.1f}%', self._get_status_icon(summary.get("hipaa_score", 0))],
['SOC 2', f'{summary.get("soc2_score", 0):.1f}%', self._get_status_icon(summary.get("soc2_score", 0))],
]
framework_table = Table(framework_data, colWidths=[2.5*inch, 1.5*inch, 1*inch])
framework_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e40af')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 12),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.beige),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(framework_table)
elements.append(Spacer(1, 0.3 * inch))
# Key Risk Indicators
elements.append(Paragraph('Key Risk Indicators', self.styles['SectionHeader']))
risk_data = [
['Indicator', 'Current', 'Target', 'Status'],
['Open Critical Findings', str(summary['audit']['critical_open']), '0', self._get_status_icon(0 if summary['audit']['critical_open'] == 0 else 50)],
['Overdue CAPAs (>60 days)', str(summary['capa']['critical_aging']), '0', self._get_status_icon(0 if summary['capa']['critical_aging'] == 0 else 50)],
['Training Compliance', f'{summary["training"]["compliancePct"]:.1f}%', '100%', self._get_status_icon(summary['training']['compliancePct'])],
]
risk_table = Table(risk_data, colWidths=[2*inch, 1.25*inch, 1.25*inch, 0.5*inch])
risk_table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e40af')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 11),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
elements.append(risk_table)
# Build PDF
doc.build(elements)
buffer.seek(0)
return buffer
def generate_full_report(self) -> BytesIO:
"""Generate comprehensive compliance report with all metrics."""
buffer = BytesIO()
doc = SimpleDocTemplate(buffer, pagesize=letter)
elements = []
# Cover page
elements.extend(self._build_cover_page())
elements.append(PageBreak())
# Executive summary section
elements.extend(self._build_executive_summary_section())
elements.append(PageBreak())
# CAPA section
elements.extend(self._build_capa_section())
elements.append(PageBreak())
# Deviation section
elements.extend(self._build_deviation_section())
elements.append(PageBreak())
# Training section
elements.extend(self._build_training_section())
elements.append(PageBreak())
# Audit findings section
elements.extend(self._build_audit_section())
elements.append(PageBreak())
# Framework compliance section
elements.extend(self._build_framework_section())
doc.build(elements)
buffer.seek(0)
return buffer
def _calculate_overall_score(self, summary: dict) -> float:
"""Calculate weighted overall compliance score."""
weights = {
'capa': 0.25,
'training': 0.25,
'audit': 0.30,
'validation': 0.20
}
scores = {
'capa': 100 - (summary['capa']['aging'] / max(summary['capa']['open'], 1) * 100),
'training': summary['training']['compliancePct'],
'audit': 100 - (summary['audit']['critical_open'] / max(summary['audit']['total_open'], 1) * 100),
'validation': 100 - (summary.get('validation_overdue', 0) * 10) # Each overdue validation -10%
}
overall = sum(scores[key] * weights[key] for key in weights.keys())
return round(overall, 1)
def _get_status_icon(self, score: float) -> str:
"""Return status icon based on score."""
if score >= 90:
return '✓ OK'
elif score >= 70:
return '⚠ Warning'
else:
return '✗ Critical'
def _get_status_text(self, score: float) -> str:
"""Return status text based on score."""
if score >= 90:
return 'Healthy'
elif score >= 70:
return 'Needs Attention'
else:
return 'Critical'
# ... additional helper methods for building report sections
13.2 CSV Data Export
# services/export_service.py (continued)
import csv
from typing import List, Dict
class ComplianceCSVExporter:
"""
Service for generating CSV exports of compliance data.
"""
def __init__(self, tenant_id: str, user):
self.tenant_id = tenant_id
self.user = user
def export_capa_data(self) -> BytesIO:
"""Export CAPA data to CSV."""
from .models import ComplianceCAPA
buffer = BytesIO()
writer = csv.writer(buffer)
# Header row
writer.writerow([
'CAPA Number', 'Title', 'Severity', 'Status', 'Assigned To',
'Created Date', 'Due Date', 'Age (days)', 'Department',
'Root Cause', 'Corrective Action', 'Preventive Action'
])
# Data rows
capas = ComplianceCAPA.objects.filter(
tenant_id=self.tenant_id,
status__in=['open', 'in_progress', 'under_review']
).select_related('assigned_to')
for capa in capas:
age_days = (timezone.now().date() - capa.created_at.date()).days
writer.writerow([
capa.capa_number,
capa.title,
capa.severity,
capa.status,
capa.assigned_to.full_name if capa.assigned_to else 'Unassigned',
capa.created_at.strftime('%Y-%m-%d'),
capa.due_date.strftime('%Y-%m-%d') if capa.due_date else '',
age_days,
capa.assigned_department or '',
capa.root_cause or '',
capa.corrective_action or '',
capa.preventive_action or ''
])
buffer.seek(0)
return buffer
def export_training_data(self) -> BytesIO:
"""Export training compliance data to CSV."""
from .models import ComplianceTrainingStatus
buffer = BytesIO()
writer = csv.writer(buffer)
writer.writerow([
'Employee Name', 'Department', 'Role', 'Training Current',
'Overdue Days', 'Required Training Count', 'Completed Training Count',
'Last Training Date', 'Next Training Due'
])
training_status = ComplianceTrainingStatus.objects.filter(
tenant_id=self.tenant_id,
employee_status='active'
).order_by('department', 'employee_name')
for status in training_status:
writer.writerow([
status.employee_name,
status.department,
status.role,
'Yes' if status.training_current else 'No',
status.training_overdue_days,
len(status.required_training_ids),
len(status.completed_training_ids),
status.last_training_completed_at.strftime('%Y-%m-%d') if status.last_training_completed_at else '',
status.next_training_due.strftime('%Y-%m-%d') if status.next_training_due else ''
])
buffer.seek(0)
return buffer
def export_framework_compliance(self) -> BytesIO:
"""Export framework compliance matrix to CSV."""
from .models import ComplianceFrameworkControl
buffer = BytesIO()
writer = csv.writer(buffer)
writer.writerow([
'Framework', 'Control Category', 'Control ID', 'Control Description',
'Status', 'Last Assessment Date', 'Assessed By', 'Next Assessment Due',
'Evidence Count', 'Notes'
])
controls = ComplianceFrameworkControl.objects.filter(
tenant_id=self.tenant_id
).select_related('assessed_by').order_by('framework', 'control_category', 'control_id')
for control in controls:
writer.writerow([
control.framework,
control.control_category,
control.control_id,
control.control_description,
control.control_status,
control.last_assessment_date.strftime('%Y-%m-%d') if control.last_assessment_date else '',
control.assessed_by.full_name if control.assessed_by else '',
control.next_assessment_due.strftime('%Y-%m-%d') if control.next_assessment_due else '',
control.evidence_count,
control.notes or ''
])
buffer.seek(0)
return buffer
13.3 Export API Endpoints
# views/export_views.py
from rest_framework import viewsets, status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.permissions import IsAuthenticated
from django.http import FileResponse
from .services.export_service import ComplianceReportExporter, ComplianceCSVExporter
from .permissions import HasComplianceExportAccess
import logging
logger = logging.getLogger(__name__)
class ExportViewSet(viewsets.ViewSet):
"""
API endpoints for exporting compliance data.
"""
permission_classes = [IsAuthenticated, HasComplianceExportAccess]
@action(detail=False, methods=['get'])
def executive_summary_pdf(self, request):
"""
Export executive summary as PDF.
"""
try:
exporter = ComplianceReportExporter(request.tenant.id, request.user)
buffer = exporter.generate_executive_summary()
return FileResponse(
buffer,
as_attachment=True,
filename=f'compliance_executive_summary_{timezone.now().strftime("%Y%m%d")}.pdf',
content_type='application/pdf'
)
except Exception as e:
logger.error(f'Failed to export executive summary: {e}')
return Response(
{'error': 'Failed to generate report'},
status=status.HTTP_500_INTERNAL_SERVER_ERROR
)
@action(detail=False, methods=['get'])
def full_report_pdf(self, request):
"""
Export full compliance report as PDF.
"""
try:
exporter = ComplianceReportExporter(request.tenant.id, request.user)
buffer = exporter.generate_full_report()
return FileResponse(
buffer,
as_attachment=True,
filename=f'compliance_full_report_{timezone.now().strftime("%Y%m%d")}.pdf',
content_type='application/pdf'
)
except Exception as e:
logger.error(f'Failed to export full report: {e}')
return Response(
{'error': 'Failed to generate report'},
status=status.HTTP_500_INTERNAL_SERVER_ERROR
)
@action(detail=False, methods=['get'])
def capa_csv(self, request):
"""
Export CAPA data as CSV.
"""
try:
exporter = ComplianceCSVExporter(request.tenant.id, request.user)
buffer = exporter.export_capa_data()
return FileResponse(
buffer,
as_attachment=True,
filename=f'capa_data_{timezone.now().strftime("%Y%m%d")}.csv',
content_type='text/csv'
)
except Exception as e:
logger.error(f'Failed to export CAPA CSV: {e}')
return Response(
{'error': 'Failed to export data'},
status=status.HTTP_500_INTERNAL_SERVER_ERROR
)
@action(detail=False, methods=['get'])
def training_csv(self, request):
"""
Export training compliance data as CSV.
"""
try:
exporter = ComplianceCSVExporter(request.tenant.id, request.user)
buffer = exporter.export_training_data()
return FileResponse(
buffer,
as_attachment=True,
filename=f'training_compliance_{timezone.now().strftime("%Y%m%d")}.csv',
content_type='text/csv'
)
except Exception as e:
logger.error(f'Failed to export training CSV: {e}')
return Response(
{'error': 'Failed to export data'},
status=status.HTTP_500_INTERNAL_SERVER_ERROR
)
@action(detail=False, methods=['get'])
def framework_compliance_csv(self, request):
"""
Export framework compliance matrix as CSV.
"""
try:
exporter = ComplianceCSVExporter(request.tenant.id, request.user)
buffer = exporter.export_framework_compliance()
return FileResponse(
buffer,
as_attachment=True,
filename=f'framework_compliance_{timezone.now().strftime("%Y%m%d")}.csv',
content_type='text/csv'
)
except Exception as e:
logger.error(f'Failed to export framework CSV: {e}')
return Response(
{'error': 'Failed to export data'},
status=status.HTTP_500_INTERNAL_SERVER_ERROR
)
14. Maintenance & Operations
14.1 Monitoring & Health Checks
# monitoring/health_checks.py
from django.core.management.base import BaseCommand
from django.core.cache import cache
from django.db import connection
import logging
logger = logging.getLogger(__name__)
class ComplianceDashboardHealthCheck:
"""
Health check service for compliance dashboard components.
"""
def check_database(self) -> Dict[str, Any]:
"""Check database connectivity and materialized view freshness."""
try:
with connection.cursor() as cursor:
cursor.execute("SELECT 1")
# Check materialized view freshness
cursor.execute("""
SELECT
schemaname || '.' || matviewname AS view_name,
EXTRACT(EPOCH FROM (NOW() - last_refresh)) / 60 AS minutes_since_refresh
FROM pg_matviews
WHERE schemaname = 'public'
AND matviewname LIKE 'mv_compliance%'
""")
stale_views = []
for row in cursor.fetchall():
if row[1] > 10: # More than 10 minutes stale
stale_views.append({'view': row[0], 'minutes_stale': row[1]})
return {
'status': 'healthy' if not stale_views else 'degraded',
'stale_views': stale_views
}
except Exception as e:
logger.error(f'Database health check failed: {e}')
return {'status': 'unhealthy', 'error': str(e)}
def check_cache(self) -> Dict[str, Any]:
"""Check Redis cache connectivity."""
try:
cache.set('health_check', 'ok', 10)
value = cache.get('health_check')
return {
'status': 'healthy' if value == 'ok' else 'unhealthy',
'cache_hit': value == 'ok'
}
except Exception as e:
logger.error(f'Cache health check failed: {e}')
return {'status': 'unhealthy', 'error': str(e)}
def check_websocket(self) -> Dict[str, Any]:
"""Check WebSocket server connectivity."""
try:
# Attempt to connect to WebSocket server
import socketio
sio = socketio.SimpleClient()
sio.connect(f'http://localhost:8000')
sio.disconnect()
return {'status': 'healthy'}
except Exception as e:
logger.error(f'WebSocket health check failed: {e}')
return {'status': 'unhealthy', 'error': str(e)}
def check_background_workers(self) -> Dict[str, Any]:
"""Check Celery worker status."""
try:
from celery import current_app
inspect = current_app.control.inspect()
stats = inspect.stats()
if not stats:
return {'status': 'unhealthy', 'error': 'No workers available'}
return {
'status': 'healthy',
'workers': len(stats),
'worker_names': list(stats.keys())
}
except Exception as e:
logger.error(f'Worker health check failed: {e}')
return {'status': 'unhealthy', 'error': str(e)}
def run_full_check(self) -> Dict[str, Any]:
"""Run all health checks."""
return {
'database': self.check_database(),
'cache': self.check_cache(),
'websocket': self.check_websocket(),
'background_workers': self.check_background_workers(),
'timestamp': timezone.now().isoformat()
}
14.2 Materialized View Refresh Schedule
# management/commands/refresh_compliance_views.py
from django.core.management.base import BaseCommand
from django.db import connection
import logging
logger = logging.getLogger(__name__)
class Command(BaseCommand):
help = 'Refresh compliance materialized views'
def add_arguments(self, parser):
parser.add_argument(
'--view',
type=str,
help='Specific view to refresh (omit to refresh all)',
)
def handle(self, *args, **options):
views = [
'mv_capa_aging',
'mv_training_compliance',
'mv_deviation_trends',
'mv_audit_findings_summary',
'mv_framework_compliance',
'mv_compliance_summary'
]
if options['view']:
views = [options['view']]
with connection.cursor() as cursor:
for view in views:
try:
self.stdout.write(f'Refreshing {view}...')
cursor.execute(f'REFRESH MATERIALIZED VIEW CONCURRENTLY {view}')
self.stdout.write(self.style.SUCCESS(f'✓ {view} refreshed'))
except Exception as e:
self.stdout.write(self.style.ERROR(f'✗ Failed to refresh {view}: {e}'))
logger.error(f'Failed to refresh {view}: {e}')
self.stdout.write(self.style.SUCCESS('Materialized view refresh complete'))
14.3 Alert Threshold Management
# management/commands/manage_alert_thresholds.py
from django.core.management.base import BaseCommand
from ...models import ComplianceAlertThreshold
import json
class Command(BaseCommand):
help = 'Manage compliance alert thresholds'
def add_arguments(self, parser):
parser.add_argument(
'--import',
type=str,
dest='import_file',
help='Import thresholds from JSON file',
)
parser.add_argument(
'--export',
type=str,
dest='export_file',
help='Export thresholds to JSON file',
)
parser.add_argument(
'--tenant',
type=str,
help='Tenant ID for import/export',
)
def handle(self, *args, **options):
if options['import_file']:
self.import_thresholds(options['import_file'], options['tenant'])
elif options['export_file']:
self.export_thresholds(options['export_file'], options['tenant'])
else:
self.stdout.write(self.style.ERROR('Must specify --import or --export'))
def import_thresholds(self, filename, tenant_id):
"""Import alert thresholds from JSON file."""
try:
with open(filename, 'r') as f:
thresholds = json.load(f)
for threshold_data in thresholds:
threshold_data['tenant_id'] = tenant_id
ComplianceAlertThreshold.objects.update_or_create(
tenant_id=tenant_id,
name=threshold_data['name'],
defaults=threshold_data
)
self.stdout.write(self.style.SUCCESS(f'Imported {len(thresholds)} thresholds'))
except Exception as e:
self.stdout.write(self.style.ERROR(f'Import failed: {e}'))
def export_thresholds(self, filename, tenant_id):
"""Export alert thresholds to JSON file."""
try:
thresholds = ComplianceAlertThreshold.objects.filter(tenant_id=tenant_id).values()
thresholds_list = list(thresholds)
with open(filename, 'w') as f:
json.dump(thresholds_list, f, indent=2, default=str)
self.stdout.write(self.style.SUCCESS(f'Exported {len(thresholds_list)} thresholds to {filename}'))
except Exception as e:
self.stdout.write(self.style.ERROR(f'Export failed: {e}'))
15. Conclusion
This comprehensive Compliance Monitoring Dashboard specification provides a production-ready system for real-time regulatory oversight across FDA 21 CFR Part 11, HIPAA, and SOC 2 frameworks in a multi-tenant biomedical/pharmaceutical QMS SaaS environment.
Key Deliverables
- Comprehensive Metrics: Real-time tracking of CAPAs, deviations, training, audits, validation status, e-signatures
- Trend Analysis: Historical KPI tracking over multiple time periods with forecasting
- Automated Alerting: Multi-level alert system with SLA tracking and escalation chains
- Multi-Framework View: Unified compliance heatmap spanning all regulatory frameworks
- Role-Based Access: Five distinct dashboard views tailored to stakeholder needs
- Technical Architecture: Complete Django + React + WebSocket implementation
- Export Capabilities: PDF reports and CSV exports for audit evidence
- Production-Ready: Includes monitoring, health checks, and operational procedures
Next Steps
- Implementation: Deploy database schema, backend services, and frontend components
- Configuration: Set up alert thresholds and escalation chains per tenant
- Training: Conduct user training for each role-based view
- Validation: Perform IQ/OQ/PQ testing per FDA requirements
- Go-Live: Enable dashboard for production use with monitoring
Compliance Assurance
This dashboard directly supports regulatory compliance by:
- Providing continuous oversight of quality management activities
- Enabling proactive identification and remediation of compliance gaps
- Maintaining audit readiness through real-time metrics and evidence tracking
- Supporting regulatory inspections with comprehensive reporting capabilities
- Demonstrating management oversight and commitment to compliance
Document Status: Ready for Review Implementation Priority: High (Critical Path for Regulatory Readiness) Estimated Effort: 6-8 weeks (backend 3 weeks, frontend 3 weeks, testing 2 weeks)