Case Management KPIs & Reporting

KPI Summary

Note: Actual table/field names must align with 03-data-specifications for the case-management, billing-claims, and scheduling modules.

KPI Definitions

KPI-CSM-001: Average Length of Stay (ALOS) vs. Expected (ALOS Ratio)

Definition

Measures the ratio of actual inpatient length of stay (LOS) to expected LOS (e.g., DRG-based geometric mean LOS) per encounter. Indicates efficiency of resource utilization and effectiveness of case management in avoiding unnecessary days while maintaining quality.

Calculation Formula

Assumptions (align with data spec):

• encounters.encounter_type = 'inpatient'
• encounters.admit_datetime, encounters.discharge_datetime
• case_reviews.expected_discharge_date
• encounters.drg_code
• drg_expected_los is a reference/master table (owned by RCM analytics or coding) with drg_code, expected_los_days.

-- ALOS Ratio per period
SELECT
    DATE_TRUNC('month', e.discharge_datetime) AS period_month,
    AVG(
        EXTRACT(EPOCH FROM (e.discharge_datetime - e.admit_datetime)) / 86400.0
        / d.expected_los_days
    ) AS alos_ratio
FROM encounters e
JOIN case_reviews cr
    ON cr.encounter_id = e.encounter_id
LEFT JOIN drg_expected_los d
    ON d.drg_code = e.drg_code
WHERE
    e.encounter_type = 'inpatient'
    AND e.discharge_datetime IS NOT NULL
    AND e.discharge_datetime >= :start_date
    AND e.discharge_datetime < :end_date
    AND d.expected_los_days IS NOT NULL
GROUP BY
    DATE_TRUNC('month', e.discharge_datetime);

To compute actual LOS and expected LOS separately:

SELECT
    AVG(EXTRACT(EPOCH FROM (e.discharge_datetime - e.admit_datetime)) / 86400.0) AS avg_actual_los_days,
    AVG(d.expected_los_days) AS avg_expected_los_days,
    AVG(
        EXTRACT(EPOCH FROM (e.discharge_datetime - e.admit_datetime)) / 86400.0
        / d.expected_los_days
    ) AS alos_ratio
...

Target / Benchmark

Data Source

• encounters.encounter_id, patient_id, facility_id, department_id, admit_datetime, discharge_datetime, encounter_type, drg_code
• case_reviews.case_id, encounter_id, expected_discharge_date
• drg_expected_los.drg_code, expected_los_days

Dimensions / Filters

• Time: day, week, month, quarter, year (based on discharge_datetime)
• Facility: encounters.facility_id
• Department/service line: encounters.department_id, case_reviews.case_type
• Attending provider: encounters.attending_provider_id
• Payer / plan: encounters.payer_id, encounters.plan_id
• DRG / diagnosis group: encounters.drg_code, encounters.primary_diagnosis_code

Visualization

• Primary: Line chart of ALOS Ratio by month with target line at 1.0.
• Secondary: Bar chart comparing actual vs expected LOS by DRG or department.
• Drill-down: Table of outlier cases (ALOS Ratio > 1.5).

Alert Thresholds

• Warning: ALOS Ratio > 1.05 for 2 consecutive months (facility-level).
• Critical: ALOS Ratio > 1.10 in any month or > 1.0 for high-cost DRGs.
• Notification recipients: Case Management Director, RCM Director, Quality & Performance team.

KPI-CSM-002: Avoidable Day Rate

Definition

Percentage of total inpatient days classified as avoidable (e.g., delays in diagnostics, discharge planning, or payer authorization) based on UM review documentation. Reflects efficiency of throughput and coordination.

Calculation Formula

Assumptions:

• utilization_reviews.total_patient_days
• utilization_reviews.avoidable_days
• utilization_reviews.review_type IN ('admission','continued_stay','retrospective')

SELECT
    DATE_TRUNC('month', ur.review_date) AS period_month,
    SUM(ur.avoidable_days) * 100.0 / NULLIF(SUM(ur.total_patient_days), 0) AS avoidable_day_rate_pct
FROM utilization_reviews ur
JOIN case_reviews cr
    ON cr.case_id = ur.case_id
JOIN encounters e
    ON e.encounter_id = cr.encounter_id
WHERE
    e.encounter_type = 'inpatient'
    AND ur.review_date >= :start_date
    AND ur.review_date < :end_date
GROUP BY
    DATE_TRUNC('month', ur.review_date);

Target / Benchmark

Data Source

• utilization_reviews.review_id, case_id, review_date, review_type, avoidable_days, total_patient_days, reviewer_id
• case_reviews.case_id, encounter_id, case_manager_id
• encounters.encounter_id, facility_id, department_id, payer_id

Dimensions / Filters

• Time: review month/quarter (review_date)
• Facility, department
• Payer / plan
• Review type (admission vs continued stay vs retrospective)
• Case manager / UM nurse
• Level of care (from utilization_reviews.level_of_care)

Visualization

• Stacked bar chart: avoidable vs non-avoidable days by department.
• Line chart: avoidable day rate trend by month.
• Heatmap: avoidable day rate by payer × department.

Alert Thresholds

• Warning: Avoidable Day Rate > 3% for any department in a month.
• Critical: Avoidable Day Rate > 5% facility-wide or > 7% for any payer.
• Notification recipients: Case Management Director, Nursing Administration, Bed Management.

KPI-CSM-003: UM Review Timeliness

Definition

Percentage of scheduled utilization reviews completed on or before their scheduled date. Measures compliance with payer review schedules and internal UM policies.

Calculation Formula

Assumptions:

• utilization_reviews.next_review_date = scheduled date for next review.
• For timeliness, we compare the actual review_date to the scheduled date stored in the previous review or in a scheduling table.
• For simplicity, assume utilization_reviews.scheduled_review_date exists (populated from payer rules).

SELECT
    DATE_TRUNC('week', ur.scheduled_review_date) AS period_week,
    COUNT(CASE
        WHEN ur.review_date::date <= ur.scheduled_review_date::date
        THEN 1 END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS um_review_timeliness_pct
FROM utilization_reviews ur
JOIN case_reviews cr
    ON cr.case_id = ur.case_id
JOIN encounters e
    ON e.encounter_id = cr.encounter_id
WHERE
    ur.scheduled_review_date IS NOT NULL
    AND ur.scheduled_review_date >= :start_date
    AND ur.scheduled_review_date < :end_date
GROUP BY
    DATE_TRUNC('week', ur.scheduled_review_date);

Target / Benchmark

Data Source

• utilization_reviews.review_id, case_id, review_type, review_date, scheduled_review_date, reviewer_id, payer_notification_required
• case_reviews.case_id, encounter_id, case_manager_id
• encounters.facility_id, department_id, payer_id

Dimensions / Filters

• Time: week, month (scheduled_review_date)
• Facility, department
• Payer / plan
• Review type
• Reviewer (UM nurse / case manager)

Visualization

• Gauge: overall timeliness percentage vs 95% target.
• Bar chart: timeliness by payer or department.
• Trend line: weekly timeliness over last 12 weeks.

Alert Thresholds

• Warning: Timeliness < 95% for any payer or department in a week.
• Critical: Timeliness < 90% facility-wide or for a major payer (e.g., Daman, THIQA).
• Notification recipients: Case Management Director, UM Team Lead, Patient Access Manager (due to auth risk).

KPI-CSM-004: Continued-Stay Authorization Rate

Definition

Percentage of continued-stay authorization (CSA) requests approved by payers. Indicates effectiveness of UM documentation and payer communication.

Calculation Formula

Assumptions:

• continued_stay_authorizations.status IN ('approved','denied','partial','pending')
• CSA requests identified by request_type = 'continued_stay' or by context.

SELECT
    DATE_TRUNC('month', csa.request_date) AS period_month,
    COUNT(CASE
        WHEN csa.status = 'approved'
             OR csa.status = 'partial'  -- treat partial as approved for rate
        THEN 1 END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS continued_stay_auth_rate_pct
FROM continued_stay_authorizations csa
JOIN case_reviews cr
    ON cr.case_id = csa.case_id
JOIN encounters e
    ON e.encounter_id = cr.encounter_id
WHERE
    csa.request_date >= :start_date
    AND csa.request_date < :end_date
GROUP BY
    DATE_TRUNC('month', csa.request_date);

Target / Benchmark

Data Source

• continued_stay_authorizations.csa_id, case_id, payer_id, auth_number, requested_days, approved_days, request_date, response_date, status, denial_reason, appeal_status
• case_reviews.case_id, encounter_id
• encounters.facility_id, department_id, payer_id

Dimensions / Filters

• Time: month, quarter (request_date)
• Facility, department
• Payer / plan
• Denial reason category (medical necessity, auth expired, level of care)
• Case manager / UM nurse

Visualization

• Bar chart: CSA approval rate by payer.
• Line chart: monthly CSA approval rate trend.
• Table: top denial reasons with counts and rates.

Alert Thresholds

• Warning: CSA rate < 90% for any payer over a month.
• Critical: CSA rate < 80% or sudden drop > 10 percentage points from prior month.
• Notification recipients: Case Management Director, Contract Management, Patient Access, Billing Manager.

KPI-CSM-005: Discharge Plan Completion Rate

Definition

Percentage of discharge plans where all associated tasks are completed before the patient’s actual discharge. Reflects quality and completeness of discharge planning and transition-of-care processes.

Calculation Formula

Assumptions:

• discharge_plans.plan_status (e.g., 'open','in_progress','completed').
• discharge_plan_tasks.status (e.g., 'pending','in_progress','completed','cancelled').
• encounters.discharge_datetime.

-- Identify plans with all tasks completed before discharge
WITH plan_task_status AS (
    SELECT
        dp.plan_id,
        MIN(CASE
            WHEN dpt.status <> 'completed'
                 AND dpt.status <> 'cancelled'
            THEN 0 ELSE 1 END) AS all_tasks_completed_flag,
        MAX(dpt.completed_datetime) AS last_task_completed_datetime
    FROM discharge_plans dp
    LEFT JOIN discharge_plan_tasks dpt
        ON dpt.plan_id = dp.plan_id
    GROUP BY dp.plan_id
),
plans_with_discharge AS (
    SELECT
        dp.plan_id,
        cr.case_id,
        e.encounter_id,
        e.discharge_datetime,
        pts.all_tasks_completed_flag,
        pts.last_task_completed_datetime
    FROM discharge_plans dp
    JOIN case_reviews cr
        ON cr.case_id = dp.case_id
    JOIN encounters e
        ON e.encounter_id = cr.encounter_id
    JOIN plan_task_status pts
        ON pts.plan_id = dp.plan_id
    WHERE
        e.discharge_datetime IS NOT NULL
        AND e.discharge_datetime >= :start_date
        AND e.discharge_datetime < :end_date
)
SELECT
    DATE_TRUNC('month', discharge_datetime) AS period_month,
    COUNT(CASE
        WHEN all_tasks_completed_flag = 1
             AND (last_task_completed_datetime IS NULL
                  OR last_task_completed_datetime <= discharge_datetime)
        THEN 1 END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS discharge_plan_completion_rate_pct
FROM plans_with_discharge
GROUP BY
    DATE_TRUNC('month', discharge_datetime);

Target / Benchmark

Data Source

• discharge_plans.plan_id, case_id, patient_id, encounter_id, planned_disposition, barriers_identified, services_needed, target_discharge_date, plan_status, patient_family_agreed, created_by, created_datetime
• discharge_plan_tasks.task_id, plan_id, task_description, assigned_to, due_date, status, completed_datetime
• case_reviews.case_id, encounter_id
• encounters.discharge_datetime, facility_id, department_id

Dimensions / Filters

• Time: month (discharge_datetime)
• Facility, department
• Disposition (home, rehab, SNF, home health, etc.)
• Case manager / discharge planner
• Payer / plan
• Readmission risk level (from readmission_risk_assessments.risk_level)

Visualization

• Gauge: overall completion rate vs 90% target.
• Bar chart: completion rate by department or disposition.
• Table: list of incomplete plans for recently discharged patients.

Alert Thresholds

• Warning: Completion rate < 90% for any department in a month.
• Critical: Completion rate < 80% facility-wide or for high-risk patients.
• Notification recipients: Case Management Director, Nursing Unit Managers, Quality Department.

KPI-CSM-006: 30-Day Readmission Rate

Definition

Percentage of discharged patients who are readmitted to any facility within 30 days of discharge. Can be calculated for all-cause readmissions and same-diagnosis readmissions. Key outcome metric for case management and care coordination.

Calculation Formula

Assumptions:

• encounters.encounter_type = 'inpatient'
• Index admission: first inpatient encounter within period.
• Readmission: subsequent inpatient encounter for same patient within 30 days of index discharge.
• Same-diagnosis: primary_diagnosis_code matches.

WITH inpatient_encounters AS (
    SELECT
        e.encounter_id,
        e.patient_id,
        e.facility_id,
        e.department_id,
        e.admit_datetime,
        e.discharge_datetime,
        e.primary_diagnosis_code
    FROM encounters e
    WHERE
        e.encounter_type = 'inpatient'
        AND e.discharge_datetime IS NOT NULL
        AND e.discharge_datetime >= :start_date
        AND e.discharge_datetime < :end_date
),
index_admissions AS (
    SELECT
        ie.*,
        ROW_NUMBER() OVER (
            PARTITION BY ie.patient_id
            ORDER BY ie.admit_datetime
        ) AS rn
    FROM inpatient_encounters ie
),
readmissions AS (
    SELECT
        idx.encounter_id AS index_encounter_id,
        idx.patient_id,
        idx.discharge_datetime AS index_discharge_datetime,
        ra.encounter_id AS readmit_encounter_id,
        ra.admit_datetime AS readmit_admit_datetime,
        ra.primary_diagnosis_code AS readmit_diagnosis_code,
        CASE
            WHEN ra.primary_diagnosis_code = idx.primary_diagnosis_code
            THEN 1 ELSE 0 END AS same_diagnosis_flag
    FROM inpatient_encounters idx
    JOIN inpatient_encounters ra
        ON ra.patient_id = idx.patient_id
        AND ra.admit_datetime > idx.discharge_datetime
        AND ra.admit_datetime <= idx.discharge_datetime + INTERVAL '30 days'
)
SELECT
    DATE_TRUNC('month', idx.discharge_datetime) AS period_month,
    COUNT(DISTINCT r.index_encounter_id) * 100.0
        / NULLIF(COUNT(DISTINCT idx.encounter_id), 0) AS readmission_rate_all_cause_pct,
    COUNT(DISTINCT CASE WHEN r.same_diagnosis_flag = 1 THEN r.index_encounter_id END) * 100.0
        / NULLIF(COUNT(DISTINCT idx.encounter_id), 0) AS readmission_rate_same_dx_pct
FROM index_admissions idx
LEFT JOIN readmissions r
    ON r.index_encounter_id = idx.encounter_id
WHERE
    idx.discharge_datetime >= :start_date
    AND idx.discharge_datetime < :end_date
GROUP BY
    DATE_TRUNC('month', idx.discharge_datetime);

Target / Benchmark

Data Source

• encounters.encounter_id, patient_id, facility_id, department_id, encounter_type, admit_datetime, discharge_datetime, primary_diagnosis_code
• case_reviews.readmission_risk_score (for stratification)
• readmission_risk_assessments.risk_level

Dimensions / Filters

• Time: month, quarter (discharge_datetime)
• Facility, department
• Diagnosis group (ICD-10-AM chapter, DRG)
• Payer / plan
• Risk level (low/medium/high)
• Age group, nationality (if available in patient demographics)

Visualization

• Line chart: monthly readmission rates (all-cause and same-diagnosis) with targets.
• Bar chart: readmission rate by diagnosis group or department.
• Table: high-risk cohorts with highest readmission rates.

Alert Thresholds

• Warning: All-cause readmission rate > 10% or same-diagnosis > 5% in any quarter.
• Critical: Sudden increase > 2 percentage points quarter-on-quarter for key conditions (e.g., CHF, COPD).
• Notification recipients: Case Management Director, Chief Medical Officer, Quality & Patient Safety, Population Health team.

KPI-CSM-007: Discharge Before Noon Rate

Definition

Percentage of inpatient discharges occurring before 12:00 local time. Early discharges improve bed availability and patient flow.

Calculation Formula

Assumptions:

• encounters.discharge_datetime in local UAE time.
• Noon threshold = 12:00:00.

SELECT
    DATE_TRUNC('month', e.discharge_datetime) AS period_month,
    COUNT(CASE
        WHEN CAST(e.discharge_datetime::time AS time) < TIME '12:00:00'
        THEN 1 END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS discharge_before_noon_rate_pct
FROM encounters e
JOIN case_reviews cr
    ON cr.encounter_id = e.encounter_id
WHERE
    e.encounter_type = 'inpatient'
    AND e.discharge_datetime IS NOT NULL
    AND e.discharge_datetime >= :start_date
    AND e.discharge_datetime < :end_date
GROUP BY
    DATE_TRUNC('month', e.discharge_datetime);

Target / Benchmark

Data Source

• encounters.encounter_id, facility_id, department_id, encounter_type, discharge_datetime
• case_reviews.case_id, expected_discharge_date

Dimensions / Filters

• Time: day, week, month (discharge_datetime)
• Facility, department
• Disposition
• Payer / plan
• Readmission risk level

Visualization

• Line chart: discharge-before-noon rate trend.
• Bar chart: rate by department.
• Table: list of discharges after noon with reasons (if captured in discharge notes).

Alert Thresholds

• Warning: Rate < 40% for any department over a month.
• Critical: Rate < 30% facility-wide for 2 consecutive months.
• Notification recipients: Case Management Director, Bed Management, Nursing Unit Managers.

KPI-CSM-008: Case Manager Caseload

Definition

Average number of active cases per case manager. Ensures workload is balanced and within safe limits.

Calculation Formula

Assumptions:

• case_reviews.case_status IN ('open','active') indicates active cases.
• case_reviews.case_manager_id
• Snapshot-based daily calculation.

-- Snapshot as of a given date (:as_of_date)
SELECT
    :as_of_date::date AS snapshot_date,
    AVG(cm_case_count) AS avg_caseload_per_case_manager
FROM (
    SELECT
        cr.case_manager_id,
        COUNT(*) AS cm_case_count
    FROM case_reviews cr
    JOIN encounters e
        ON e.encounter_id = cr.encounter_id
    WHERE
        cr.case_status IN ('open','active')
        AND e.admit_datetime <= :as_of_date
        AND (e.discharge_datetime IS NULL OR e.discharge_datetime > :as_of_date)
    GROUP BY
        cr.case_manager_id
) cm;

To monitor distribution:

SELECT
    cr.case_manager_id,
    COUNT(*) AS active_cases
FROM case_reviews cr
JOIN encounters e
    ON e.encounter_id = cr.encounter_id
WHERE
    cr.case_status IN ('open','active')
    AND e.admit_datetime <= :as_of_date
    AND (e.discharge_datetime IS NULL OR e.discharge_datetime > :as_of_date)
GROUP BY
    cr.case_manager_id;

Target / Benchmark

Data Source

• case_reviews.case_id, encounter_id, case_manager_id, case_status, case_type, priority
• encounters.encounter_id, admit_datetime, discharge_datetime, facility_id, department_id
• case_management_assignments.case_manager_id, department_id, max_caseload, current_caseload, is_active

Dimensions / Filters

• Time: daily snapshot, weekly average.
• Facility, department/service line.
• Case type (UM, discharge planning, care coordination).
• Case manager.

Visualization

• Gauge: average caseload vs target.
• Bar chart: active cases per case manager.
• Heatmap: caseload by department × case manager.

Alert Thresholds

• Warning: Any case manager with active_cases > max_caseload or > 20.
• Critical: Average caseload > 22 or > max_caseload for a department.
• Notification recipients: Case Management Director, HR/Staffing Coordinator.

KPI-CSM-009: Denial Rate (UM-Related)

Definition

Percentage of claims denied for UM-related reasons (medical necessity, authorization expired, level of care issues) out of all submitted claims. Measures effectiveness of UM processes and payer compliance.

Calculation Formula

Assumptions:

• billing_claims.claim_id, encounter_id, payer_id, submission_date.
• claim_responses.claim_id, status, denial_reason_code, denial_category.
• UM-related denial categories: 'medical_necessity','auth_expired','level_of_care'.

SELECT
    DATE_TRUNC('month', bc.submission_date) AS period_month,
    COUNT(CASE
        WHEN cr.status = 'denied'
             AND cr.denial_category IN ('medical_necessity','auth_expired','level_of_care')
        THEN 1 END
    ) * 100.0
    / NULLIF(COUNT(*), 0) AS um_related_denial_rate_pct
FROM billing_claims bc
LEFT JOIN claim_responses cr
    ON cr.claim_id = bc.claim_id
WHERE
    bc.submission_date >= :start_date
    AND bc.submission_date < :end_date
GROUP BY
    DATE_TRUNC('month', bc.submission_date);

Target / Benchmark

Data Source

• billing_claims.claim_id, encounter_id, payer_id, plan_id, submission_date, total_amount
• claim_responses.claim_id, status, denial_reason_code, denial_category, response_date
• continued_stay_authorizations.auth_number, status, denial_reason (for root cause analysis)
• encounters.facility_id, department_id

Dimensions / Filters

• Time: month, quarter (submission_date)
• Facility, department
• Payer / plan
• Denial category and reason
• Case manager / UM nurse (via encounter → case_reviews)

Visualization

• Bar chart: UM-related denial rate by payer.
• Line chart: monthly UM-related denial rate trend.
• Pareto chart: top denial reasons contributing to UM-related denials.

Alert Thresholds

• Warning: UM-related denial rate > 3% for any payer in a month.
• Critical: UM-related denial rate > 5% facility-wide or > 7% for a major payer.
• Notification recipients: Case Management Director, Billing & Claims Manager, Policy & Contract Management.

Standard Reports

Dashboard Wireframe

Below is an HTML wireframe mockup for the Case Management Analytics dashboard (SCR-CSM-007), showing KPI cards, charts, and filter controls.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Case Management Analytics Dashboard</title>
</head>
<body style="font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f5f7fa;">

  <!-- Header and Filters -->
  <div style="background-color: #004b8d; color: #fff; padding: 16px 24px;">
    <div style="display: flex; justify-content: space-between; align-items: center;">
      <h1 style="margin: 0; font-size: 20px;">Case Management Analytics</h1>
      <div>
        <label style="margin-right: 8px; font-size: 12px;">Date Range:</label>
        <select style="padding: 4px 8px; font-size: 12px;">
          <option>Last 30 days</option>
          <option>Last 90 days</option>
          <option>Year to Date</option>
          <option>Custom...</option>
        </select>
        <label style="margin: 0 8px; font-size: 12px;">Facility:</label>
        <select style="padding: 4px 8px; font-size: 12px;">
          <option>All Facilities</option>
          <option>Dubai General Hospital</option>
          <option>Abu Dhabi City Hospital</option>
        </select>
        <label style="margin: 0 8px; font-size: 12px;">Payer:</label>
        <select style="padding: 4px 8px; font-size: 12px;">
          <option>All Payers</option>
          <option>Daman</option>
          <option>THIQA</option>
          <option>Oman Insurance</option>
        </select>
      </div>
    </div>
  </div>

  <!-- KPI Cards -->
  <div style="padding: 16px 24px;">
    <div style="display: flex; flex-wrap: wrap; gap: 12px;">
      <!-- ALOS Ratio -->
      <div style="flex: 1 1 180px; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
        <div style="font-size: 12px; color: #666;">ALOS vs Expected</div>
        <div style="font-size: 24px; font-weight: bold;">0.98</div>
        <div style="font-size: 11px; color: #0a8a0a;">Target ≤ 1.0</div>
        <div style="margin-top: 4px; font-size: 10px; color: #999;">Last month</div>
      </div>

      <!-- Avoidable Day Rate -->
      <div style="flex: 1 1 180px; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
        <div style="font-size: 12px; color: #666;">Avoidable Day Rate</div>
        <div style="font-size: 24px; font-weight: bold;">2.4%</div>
        <div style="font-size: 11px; color: #0a8a0a;">Target ≤ 3%</div>
        <div style="margin-top: 4px; font-size: 10px; color: #999;">Facility-wide</div>
      </div>

      <!-- UM Review Timeliness -->
      <div style="flex: 1 1 180px; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
        <div style="font-size: 12px; color: #666;">UM Review Timeliness</div>
        <div style="font-size: 24px; font-weight: bold;">93%</div>
        <div style="font-size: 11px; color: #d9534f;">Target ≥ 95%</div>
        <div style="margin-top: 4px; font-size: 10px; color: #999;">Last 4 weeks</div>
      </div>

      <!-- CSA Approval Rate -->
      <div style="flex: 1 1 180px; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
        <div style="font-size: 12px; color: #666;">CSA Approval Rate</div>
        <div style="font-size: 24px; font-weight: bold;">91%</div>
        <div style="font-size: 11px; color: #0a8a0a;">Target ≥ 90%</div>
        <div style="margin-top: 4px; font-size: 10px; color: #999;">All payers</div>
      </div>

      <!-- 30-Day Readmission Rate -->
      <div style="flex: 1 1 180px; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
        <div style="font-size: 12px; color: #666;">30-Day Readmission</div>
        <div style="font-size: 24px; font-weight: bold;">9.1%</div>
        <div style="font-size: 11px; color: #0a8a0a;">Target ≤ 10%</div>
        <div style="margin-top: 4px; font-size: 10px; color: #999;">All-cause</div>
      </div>
    </div>
  </div>

  <!-- Charts Row 1 -->
  <div style="padding: 0 24px 16px 24px; display: flex; gap: 16px;">
    <!-- ALOS Trend -->
    <div style="flex: 2; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size: 13px; font-weight: bold; margin-bottom: 8px;">ALOS vs Expected Trend</div>
      <div style="height: 200px; border: 1px dashed #ccc; font-size: 11px; color: #999; display: flex; align-items: center; justify-content: center;">
        Line chart placeholder (ALOS Ratio by month with target line)
      </div>
    </div>

    <!-- UM Review Timeliness by Payer -->
    <div style="flex: 1; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size: 13px; font-weight: bold; margin-bottom: 8px;">UM Timeliness by Payer</div>
      <div style="height: 200px; border: 1px dashed #ccc; font-size: 11px; color: #999; display: flex; align-items: center; justify-content: center;">
        Bar chart placeholder (Timeliness % by payer)
      </div>
    </div>
  </div>

  <!-- Charts Row 2 -->
  <div style="padding: 0 24px 16px 24px; display: flex; gap: 16px;">
    <!-- Readmission Rate by Department -->
    <div style="flex: 1; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size: 13px; font-weight: bold; margin-bottom: 8px;">30-Day Readmission by Department</div>
      <div style="height: 200px; border: 1px dashed #ccc; font-size: 11px; color: #999; display: flex; align-items: center; justify-content: center;">
        Bar chart placeholder (Readmission % by department)
      </div>
    </div>

    <!-- UM-Related Denials -->
    <div style="flex: 1; background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
      <div style="font-size: 13px; font-weight: bold; margin-bottom: 8px;">UM-Related Denial Rate</div>
      <div style="height: 200px; border: 1px dashed #ccc; font-size: 11px; color: #999; display: flex; align-items: center; justify-content: center;">
        Line/column chart placeholder (Denial rate trend and by payer)
      </div>
    </div>
  </div>

  <!-- Detail Table -->
  <div style="padding: 0 24px 24px 24px;">
    <div style="background-color: #fff; border-radius: 4px; padding: 12px; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
      <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 8px;">
        <div style="font-size: 13px; font-weight: bold;">High-Risk / Outlier Cases</div>
        <div>
          <button style="font-size: 11px; padding: 4px 8px; margin-right: 4px;">Export CSV</button>
          <button style="font-size: 11px; padding: 4px 8px;">Export Excel</button>
        </div>
      </div>
      <table style="width: 100%; border-collapse: collapse; font-size: 11px;">
        <thead>
          <tr style="background-color: #f0f0f0;">
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">Patient</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">MRN</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">Dept</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">ALOS Ratio</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">Risk Level</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">Payer</th>
            <th style="text-align: left; padding: 4px; border-bottom: 1px solid #ddd;">Flags</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Ahmed Al-Maktoum</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">00123456</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Cardiology</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee; color: #d9534f;">1.8</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">High</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Daman</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Long LOS, high readmission risk</td>
          </tr>
          <tr>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Fatima Al-Nahyan</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">00198765</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Medicine</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee; color: #d9534f;">1.6</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">Medium</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">THIQA</td>
            <td style="padding: 4px; border-bottom: 1px solid #eee;">UM denial, complex discharge</td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>

</body>
</html>

Regulatory Reports

This section focuses on UAE-specific regulatory and compliance reporting relevant to case management and utilization management.

MOH (Ministry of Health and Prevention)

1. Inpatient Utilisation & LOS Summary

• Content: Aggregated inpatient admissions, LOS by diagnosis group (ICD-10-AM), discharge disposition, and readmission rates.
• Source Tables: encounters, case_reviews, discharge_plans.
• Frequency: Quarterly or as requested.
• Format: MOH-specified template (typically Excel/CSV or XML).
• System Requirements:
  • Ability to generate anonymised, aggregated data (no direct identifiers) unless explicitly required.
  • Mapping of internal diagnosis/procedure codes to ICD-10-AM and CPT.
  • Support for federal disease surveillance indicators where case management is involved (e.g., prolonged LOS for notifiable conditions).

2. Utilisation Review & Bed Management Indicators

• Content: ALOS, bed occupancy, avoidable day rate, level-of-care utilization.
• Source Tables: encounters, utilization_reviews, case_reviews.
• Frequency: Semi-annual / ad-hoc.
• Format: Aggregated PDF/Excel.

DOH (Abu Dhabi) / Malaffi

1. DOH Utilisation & Quality Indicators

• Content: LOS, readmission rates, discharge disposition, utilisation of post-acute services for Abu Dhabi facilities.
• Source Tables: encounters, case_reviews, discharge_plans, readmission_risk_assessments.
• Frequency: Quarterly.
• Format: DOH templates (Excel/CSV); integration with Malaffi via HL7/FHIR handled by other modules, but case management must provide structured data.

2. Payer Performance & Denial Analytics

• Content: UM-related denial rates, CSA approval rates, timeliness of reviews for DOH-regulated payers (e.g., Daman, THIQA).
• Source Tables: continued_stay_authorizations, billing_claims, claim_responses, utilization_reviews.
• Frequency: Quarterly / contract review cycles.
• Format: PDF/Excel; supports DOH eClaims analytics.

DHA (Dubai) / NABIDH

1. DHA Utilisation & Readmission Metrics

• Content: LOS, avoidable days, readmission rates, discharge-before-noon for Dubai facilities.
• Source Tables: encounters, utilization_reviews, case_reviews.
• Frequency: Quarterly.
• Format: DHA reporting templates; aggregated, de-identified where required.

2. NABIDH Compliance Metrics (Case Management Contribution)

• Content: Completeness and timeliness of discharge summaries and care plans shared to NABIDH; proportion of discharges with structured discharge plans.
• Source Tables: discharge_plans, care_coordination_notes, encounters.
• Frequency: Monthly.
• Format: Internal dashboards; NABIDH integration via HL7/FHIR handled by EHR module, but case management must expose required fields.

UAE PDPL (Federal Decree-Law No. 45/2021) Audit Reporting

1. Access & Activity Logs for Case Management Data

• Content: Who accessed/modified case management records (case reviews, UM notes, discharge plans), when, and from where.
• Source Tables: audit_log (cross-module), referencing case_reviews, utilization_reviews, discharge_plans, continued_stay_authorizations.
• Frequency: On-demand (Data Protection Officer), quarterly summary.
• Format: PDF/CSV with filters by user, patient, date range.

2. Data Subject Rights Handling (Case Management Context)

• Content: Requests for access, rectification, restriction, or objection related to case management notes; response times and outcomes.
• Source Tables: privacy_requests (cross-module), linked to case management records.
• Frequency: Quarterly.
• Format: PDF/Excel.

3. Minimum Necessary & Role-Based Access Review

• Content: Role vs permission mapping for case management roles (Case Manager, UM Nurse, Social Worker, etc.), unusual access patterns.
• Source Tables: roles, permissions, users, audit_log.
• Frequency: Annual or as part of PDPL compliance audits.
• Format: PDF.

Ad-Hoc Reporting

Available Data Fields (Case Management Domain)

The ad-hoc reporting layer should expose, at minimum, the following field groups (subject to PDPL role-based access):

• Case Reviews (case_reviews)
• case_id
• patient_id (FK to patients)
• encounter_id (FK to encounters)
• case_manager_id (FK to providers/users)
• assigned_datetime
• case_status
• case_type (UM, discharge planning, care coordination)
• priority
• expected_discharge_date
• actual_discharge_date
• disposition
• readmission_risk_score

• closed_datetime

• Utilization Reviews (utilization_reviews)

• review_id
• case_id
• review_type (admission, continued_stay, retrospective)
• review_date
• reviewer_id
• criteria_tool (InterQual-equivalent, local)
• criteria_met (Y/N)
• clinical_justification
• level_of_care
• recommended_action
• next_review_date
• scheduled_review_date
• payer_notification_required
• total_patient_days

• avoidable_days

• UM Criteria Evaluations (um_criteria_evaluations)

• eval_id
• review_id
• criteria_category
• criteria_item
• met (Y/N)
• evidence

• notes

• Discharge Plans (discharge_plans)

• plan_id
• case_id
• patient_id
• encounter_id
• planned_disposition
• barriers_identified
• services_needed
• target_discharge_date
• plan_status
• patient_family_agreed
• created_by

• created_datetime

• Discharge Plan Tasks (discharge_plan_tasks)

• task_id
• plan_id
• task_description
• assigned_to
• due_date
• status
• completed_datetime

• notes

• Care Coordination Notes (care_coordination_notes)

• note_id
• case_id
• patient_id
• coordinator_id
• activity_type
• description
• contact_made_with
• outcome

• note_datetime

• Continued-Stay Authorizations (continued_stay_authorizations)

• csa_id
• case_id
• payer_id
• auth_number
• requested_days
• approved_days
• request_date
• response_date
• status
• denial_reason

• appeal_status

• Readmission Risk Assessments (readmission_risk_assessments)

• assessment_id
• patient_id
• encounter_id
• assessment_datetime
• risk_score
• risk_level
• score_components_json
• interventions_planned

• assessed_by

• Case Management Assignments (case_management_assignments)

• assignment_id
• case_manager_id
• department_id
• service_line
• max_caseload
• current_caseload

• is_active

• Shared Entities (via FKs)

• Patient demographics (from patients, patient_demographics)
• Encounter details (from encounters, encounter_details)
• Facility/department (from facilities, departments)
• Payer/plan (from payers, insurance_plans)

Export Formats

The reporting engine should support:

• CSV: For data analysis in external tools (Excel, BI platforms).
• Excel (XLSX): For operational and management reports with formatting.
• PDF: For official reports, regulatory submissions, and sign-off.
• JSON: For API-based data extraction (internal analytics services).

All exports must:

• Respect UAE PDPL requirements (role-based access, minimum necessary data).
• Support de-identification/anonymisation options (e.g., remove Emirates ID, names) for research/benchmarking.

Scheduled Report Delivery

Capabilities:

1. Scheduling

• Users with appropriate permissions (e.g., Case Management Director, RCM Analyst) can schedule reports:
  • Daily, weekly, monthly, quarterly.
  • Specific time of day (local UAE time).
• Stored in report_schedules (cross-module) with:
  • report_id, parameters_json (filters), frequency, next_run_datetime, recipients.

2. Delivery Channels

• Secure email with link to download (no PHI in email body).
• Internal portal notifications.
• SFTP drop for bulk regulatory exports (if required by MOH/DOH/DHA).

3. Access Control & Audit

• Only authorised roles can schedule or view specific reports.
• All report executions logged in report_audit_log with:
  • user_id, report_id, run_datetime, parameters, export_format.
• Supports PDPL audit requirements and TDRA/NESA cybersecurity guidelines.

4. Paperless Transformation

• Replaces manual Excel trackers and paper UM logs with automated, scheduled digital reports.
• Ensures consistent, reproducible metrics for internal governance and UAE regulatory submissions.