# Claims Collection V2 — HR Dashboard API

**Created:** 2026-06-02  
**Updated:** 2026-06-03  
**Controller:** `App\Controllers\ClaimsCollectionV2DashboardController`  
**Model:** `App\Models\ClaimsCollectionV2DashboardModel`  
**Source queries:** `metabase_raw_queries.csv` → collection `Claims Collection V2` (31 original) + 5 KPIs added in-model (232–236)  
**Base app URL (local):** `https://localhost/PHP828APPS/ruc/nhance/index.php`  
**Total KPIs:** 36

---

## Overview

Each KPI is a separate PHP method on the model, registered in `KPI_MAP` (Metabase question id → slug) and `KPI_LABELS` (slug → display label).

The API exposes them in three ways:

| What | URL fragment | Use case |
|------|-------------|----------|
| **Single KPI** | `kpi/{slug or Metabase id}` | FE loads one card at a time |
| **All KPIs** | `all` | FE loads entire dashboard in one call (36 KPIs) |
| **Debug / preview** | `debug` / `preview` | Admin checks raw output in browser |

All endpoints require `client_policy` or `client_policy_id` as a query param  
(or route segment for `debug/{id}` / `preview/{id}`).

---

## Route stacks

### 1. Admin — `authMVC` (session login required)

Prefix: `util/claims-collection-v2`

| Method | Path | Handler | Purpose |
|--------|------|---------|---------|
| GET | `util/claims-collection-v2/preview` | `::preview` | UI debug grid in browser |
| GET | `util/claims-collection-v2/preview/{policy_id}` | `::preview` | Same, policy in URL |
| GET | `util/claims-collection-v2/debug` | `::debug` | Raw JSON dump (all KPIs) |
| GET | `util/claims-collection-v2/debug/{policy_id}` | `::debug` | Same, policy in URL |
| GET | `util/claims-collection-v2/all` | `::all` | JSON — all 36 KPIs |
| GET | `util/claims-collection-v2/kpi/{slug\|id}` | `::kpi` | JSON — single KPI |

**Filters applied:** `authMVC`, `AclFilter`, `HttpRequestLog`, `Cors`, `SecurityInputFilter`

---

### 2. Frontend / HR App — `authJWT` (Bearer token required)

Prefix: `employeeRest/claims-collection-v2`

| Method | Path | Handler | Purpose |
|--------|------|---------|---------|
| GET | `employeeRest/claims-collection-v2/all` | `::all` | JSON — all 36 KPIs |
| GET | `employeeRest/claims-collection-v2/kpi/{slug\|id}` | `::kpi` | JSON — single KPI |
| GET | `employeeRest/claims-collection-v2/preview` | `::preview` | UI grid (FE debug) |
| GET | `employeeRest/claims-collection-v2/preview/{policy_id}` | `::preview` | Same, policy in URL |
| GET | `employeeRest/claims-collection-v2/debug` | `::debug` | Raw JSON (FE debug) |
| GET | `employeeRest/claims-collection-v2/debug/{policy_id}` | `::debug` | Same, policy in URL |

**Filters applied:** `GlobalPostFileUploadGuard`, `ratelimit`, `appSignature`, `authJWT`

---

## Parameters

| Param | Location | Type | Required | Notes |
|-------|----------|------|----------|-------|
| `client_policy` | query string | int | **Yes** (API) | Policy ID |
| `client_policy_id` | query string | int | **Yes** (API) | Alias for above |
| `{policy_id}` | URL segment | int | No | Only on `preview/{id}` and `debug/{id}`; falls back to 4687 if omitted |
| `{slug\|id}` | URL segment | string/int | Yes (kpi only) | KPI method slug OR Metabase question id |

> **Default policy ID (4687)** is only the method-signature default for `preview` and `debug`.  
> Change it by passing the query param or URL segment — never hardcoded elsewhere.

---

## How to call — Admin (browser / Postman, session cookie)

Open in browser while logged in to admin:

```
# UI preview page (loads grid, click "Load all KPIs")
GET /index.php/util/claims-collection-v2/preview?client_policy=4687

# Preview with policy in URL
GET /index.php/util/claims-collection-v2/preview/4687

# Raw JSON — all 36 KPIs
GET /index.php/util/claims-collection-v2/debug?client_policy=4687

# Raw JSON — single KPI by slug
GET /index.php/util/claims-collection-v2/kpi/incurred_ratio?client_policy=4687

# Raw JSON — single KPI by Metabase question id
GET /index.php/util/claims-collection-v2/kpi/207?client_policy=4687

# Exposure KPIs (added 2026-06-03)
GET /index.php/util/claims-collection-v2/kpi/inception_emp_lives?client_policy=4687
GET /index.php/util/claims-collection-v2/kpi/current_emp_lives?client_policy=4687
GET /index.php/util/claims-collection-v2/kpi/claim_value_by_month?client_policy=4687
GET /index.php/util/claims-collection-v2/kpi/claim_amount_by_relationship?client_policy=4687
GET /index.php/util/claims-collection-v2/kpi/top_10_ailments_by_claim_count?client_policy=4687
```

---

## How to call — Frontend / HR App (JWT)

```http
GET /index.php/employeeRest/claims-collection-v2/all?client_policy=4687
Authorization: Bearer <jwt_token>
X-App-Signature: <app_signature>
Content-Type: application/json
```

```http
GET /index.php/employeeRest/claims-collection-v2/kpi/incurred_ratio?client_policy=4687
Authorization: Bearer <jwt_token>
X-App-Signature: <app_signature>
```

```http
GET /index.php/employeeRest/claims-collection-v2/kpi/inception_emp_lives?client_policy=4687
Authorization: Bearer <jwt_token>
X-App-Signature: <app_signature>
```

```http
GET /index.php/employeeRest/claims-collection-v2/kpi/claim_amount_by_relationship?client_policy=4687
Authorization: Bearer <jwt_token>
X-App-Signature: <app_signature>
```

```http
GET /index.php/employeeRest/claims-collection-v2/kpi/235?client_policy=4687
Authorization: Bearer <jwt_token>
X-App-Signature: <app_signature>
```

---

## Sample responses

### `kpi/{slug}` — single-row KPI

```json
{
  "status": true,
  "policy_id": 4687,
  "kpi_id": 207,
  "kpi": "incurred_ratio",
  "label": "Incurred Ratio",
  "rows": [
    { "incurred_ratio": "72.34%" }
  ]
}
```

### `kpi/inception_emp_lives` — exposure (single row)

```json
{
  "status": true,
  "policy_id": 4687,
  "kpi_id": 232,
  "kpi": "inception_emp_lives",
  "label": "Inception Employees & Lives",
  "rows": [
    { "inception_emp": "150", "inception_lives": "420" }
  ]
}
```

### `kpi/current_emp_lives` — exposure (single row)

```json
{
  "status": true,
  "policy_id": 4687,
  "kpi_id": 233,
  "kpi": "current_emp_lives",
  "label": "Current Employees & Lives",
  "rows": [
    { "current_emp": "145", "current_lives": "410", "avg_family_size": "2.83" }
  ]
}
```

### `kpi/claim_value_by_month` — time series (multi-row)

```json
{
  "status": true,
  "policy_id": 4687,
  "kpi_id": 234,
  "kpi": "claim_value_by_month",
  "label": "Claim Value by Month",
  "rows": [
    { "claim_month": "Jan 2024", "month_sort": "2024-01", "claim_value": "125000.00" },
    { "claim_month": "Feb 2024", "month_sort": "2024-02", "claim_value": "98000.50" }
  ]
}
```

> Sort charts by `month_sort` (ISO `YYYY-MM`), not `claim_month` (display label).

### `all`

```json
{
  "status": true,
  "policy_id": 4687,
  "data": {
    "policy_exposure_summary": {
      "id": 181,
      "label": "POLICY & EXPOSURE SUMMARY",
      "rows": [
        {
          "policy_start_date": "2024-01-01",
          "policy_end_date": "2024-12-31",
          "insurer_name": "HDFC Ergo",
          "tpa_name": "Medi Assist"
        }
      ]
    },
    "incurred_ratio": {
      "id": 207,
      "label": "Incurred Ratio",
      "rows": [{ "incurred_ratio": "72.34%" }]
    },
    "inception_emp_lives": {
      "id": 232,
      "label": "Inception Employees & Lives",
      "rows": [{ "inception_emp": "150", "inception_lives": "420" }]
    },
    "current_emp_lives": {
      "id": 233,
      "label": "Current Employees & Lives",
      "rows": [{ "current_emp": "145", "current_lives": "410", "avg_family_size": "2.83" }]
    },
    "claim_value_by_month": {
      "id": 234,
      "label": "Claim Value by Month",
      "rows": [
        { "claim_month": "Jan 2024", "month_sort": "2024-01", "claim_value": "125000.00" }
      ]
    }
  }
}
```

### Error — missing policy id

```json
{
  "status": false,
  "message": "client_policy or client_policy_id is required."
}
```
**HTTP 422**

### Error — unknown KPI

```json
{
  "status": false,
  "message": "Unknown KPI. Pass Metabase id or method slug.",
  "allowed": {
    "181": "policy_exposure_summary",
    "207": "incurred_ratio",
    "232": "inception_emp_lives",
    "233": "current_emp_lives",
    "234": "claim_value_by_month",
    "235": "claim_amount_by_relationship",
    "236": "top_10_ailments_by_claim_count"
  }
}
```
**HTTP 404** — `allowed` lists the full `KPI_MAP` (36 entries).

---

## All 36 KPIs

| Metabase ID | Method slug | Label | Rows |
|-------------|-------------|-------|------|
| 181 | `policy_exposure_summary` | POLICY & EXPOSURE SUMMARY | single |
| 185 | `premium_as_on_date` | PREMIUM AS ON DATE | single |
| 186 | `claims_experience_summary` | CLAIMS EXPERIENCE SUMMARY | single |
| 190 | `claim_amount_by_gender` | Claim Amount by Gender | multi |
| 191 | `age_band` | Age Band | multi |
| 194 | `top_5_hospitals_by_incurred_amount` | Top 5 Hospitals by Incurred amount | multi |
| 197 | `claims_incidence_rate` | Claims Incidence Rate | single |
| 198 | `policy_start_date` | Policy Start Date | single |
| 199 | `policy_end_date` | Policy End Date | single |
| 200 | `insurer` | Insurer | single |
| 201 | `tpa` | TPA | single |
| 203 | `earned_premium` | Earned Premium | single |
| 204 | `total_claims` | Total Claims | single |
| 206 | `incurred_amount` | Incurred Amount | single |
| 207 | `incurred_ratio` | Incurred Ratio | single |
| 208 | `projected_claims` | Projected Claims | single |
| 209 | `projected_ratio` | Projected Ratio | single |
| 213 | `total_reimbursement_amount` | Total Reimbursement Amount | single |
| 214 | `total_reimbursement_amt_pct` | Total Reimbursement Amt % | single |
| 215 | `cashless_claim_amt` | Cashless Claim Amt | single |
| 216 | `cashless_claim_amt_pct` | Cashless Claim Amt % | single |
| 217 | `total_incurred_by_city` | Total Incurred by city | multi |
| 219 | `claim_amount_by_claim_status` | Claim Amount by Claim Status | multi |
| 220 | `hospitals_in_detail` | Hospitals in detail | multi |
| 221 | `hospital_city_wise_si_limit_pregnancy` | Hospital city wise SI Limit - Pregnancy | multi |
| 224 | `s_pregnancy_normal_delivery_exceeded_amt` | S-PREGNANCY - NORMAL DELIVERY Exceeded Amt | multi |
| 225 | `s_pregnancy_c_sec_avg_exceeded_amt` | S-Pregnancy C-Sec avg exceeded amt | multi |
| 228 | `cataract_exceeded_claim_amount` | Cataract exceeded claim amount | multi |
| 229 | `cataract_avg_exceeded_amount` | Cataract avg exceeded amount | multi |
| 230 | `hospital_city_wise_si_limit_cataract` | Hospital city wise SI Limit - Cataract | multi |
| 231 | `total_incurred_by_cliam_status` | Total Incurred by Cliam Status | multi |
| 232 | `inception_emp_lives` | Inception Employees & Lives | single |
| 233 | `current_emp_lives` | Current Employees & Lives | single |
| 234 | `claim_value_by_month` | Claim Value by Month | multi |
| 235 | `claim_amount_by_relationship` | Claim Amount by Relationship | multi |
| 236 | `top_10_ailments_by_claim_count` | Top 10 Ailments by Claim Count | multi |

> **Note:** KPIs 214 and 216 were renamed from the auto-generated slug to avoid collision with 213 and 215.

> **Note:** IDs **232–236** were assigned in-app for KPIs added outside the original Metabase CSV export. Confirm real Metabase question IDs and update `KPI_MAP` if they differ.

---

## Output columns — KPIs 232–236

| Slug | Row fields | Description |
|------|------------|-------------|
| `inception_emp_lives` | `inception_emp`, `inception_lives` | Distinct Self employees and all lives at inception (`change_event` contains inception) |
| `current_emp_lives` | `current_emp`, `current_lives`, `avg_family_size` | Active employees/lives; `avg_family_size` = lives ÷ employees (2 dp) |
| `claim_value_by_month` | `claim_month`, `month_sort`, `claim_value` | Monthly sum of `claim_amount`; ordered by `month_sort` |
| `claim_amount_by_relationship` | `relationship`, `claim_count`, `count_pct`, `claim_value`, `value_pct` | Claims by `ticket_master.relationship`; `count_pct` / `value_pct` are numeric (not `%` strings) |
| `top_10_ailments_by_claim_count` | `ailment`, `claim_count`, `count_pct`, `claim_value`, `value_pct` | Top 10 `tpa_ailments` (non-null); `claim_value` formatted via `FORMAT()`; pct fields as `%` strings |

---

### `kpi/claim_amount_by_relationship` — breakdown (multi-row)

```json
{
  "status": true,
  "policy_id": 4687,
  "kpi_id": 235,
  "kpi": "claim_amount_by_relationship",
  "label": "Claim Amount by Relationship",
  "rows": [
    {
      "relationship": "Self",
      "claim_count": "42",
      "count_pct": "35.00",
      "claim_value": "850000.00",
      "value_pct": "40.50"
    },
    {
      "relationship": "Spouse",
      "claim_count": "28",
      "count_pct": "23.33",
      "claim_value": "520000.00",
      "value_pct": "24.80"
    }
  ]
}
```

> Not the same as `claim_amount_by_gender` (#190), which groups by employee `gender` via join — not `ticket_master.relationship`.

---

## Files

| File | Purpose |
|------|---------|
| `app/Models/ClaimsCollectionV2DashboardModel.php` | 36 KPI query methods, `KPI_MAP`, `KPI_LABELS`, `getAllKpis()`, `getKpi()` |
| `app/Controllers/ClaimsCollectionV2DashboardController.php` | `kpi()`, `all()`, `preview()`, `debug()` |
| `app/Views/claims_collection_v2_dashboard.php` | Admin/FE debug preview UI (KPI card grid) |
| `app/Config/Routes.php` | Both route groups (search `claims-collection-v2`) |
| `tests/smoke_claims_collection_v2.php` | CLI smoke test — run: `php tests/smoke_claims_collection_v2.php 4687` |
| `metabase_raw_queries.csv` | Source of truth for original Metabase SQL queries |
| `hr-dashboard.md` | This document |

---

## FE integration notes

- Call `all` once on dashboard mount; render each `data[method].rows` into its card (36 keys under `data`).
- Call `kpi/{slug}` for lazy/on-demand loading of individual cards.
- `policy_id` should come from the HR session / selected policy context — never hardcoded.
- All rows are raw arrays; formatting (currency, %, dates) is already applied inside the SQL where applicable (`FORMAT()`, `CONCAT()`, `DATE_FORMAT()`).
- `rows` may be empty `[]` if no data exists for that policy — handle gracefully in UI.
- **Single-row KPIs** (e.g. `incurred_ratio`, `inception_emp_lives`): use `rows[0]`.
- **Multi-row KPIs** (e.g. `claim_value_by_month`, `age_band`): iterate `rows`; for time series use `month_sort` for sort order.
- **Exposure block:** `inception_emp_lives` + `current_emp_lives` pair for inception vs current headcount.

---

## BE notes

- To add a new KPI: add an entry to `KPI_MAP` + `KPI_LABELS` in the model, write `public function my_kpi(int $policyId): array`, update this doc and bump the smoke test KPI count.
- All queries use named binding `:policy_id:` (CodeIgniter style, replaces Metabase `{{policy_id}}`).
- Literal `\t` / `\n` in CSV SQL is normalized in `runKpiQuery()` — safe to re-generate from CSV.
- Run `php tests/smoke_claims_collection_v2.php {policy_id}` after any model change (expects `KPI_MAP` count === 36).

---

## Related dashboards

- **Enrollment Collection V1** (10 KPIs): see `enrollment-dashboard.md` — prefix `enrollment-collection-v1`