# SPHF Stage Bottlenecks Report (Condition-wise)

Date: 2026-06-01
Route: /sphf-stage-bottlenecks
Tenant used for counts: 20 (database: sphf28)
Data source for route matrix/drill: sphf_normalized_kpi (pending side)

## 1) Route-level active filter
All stage bottleneck rows are evaluated from pending records only:

- p_valid = true
- UPPER(TRIM(p_stage)) != 'COMPLETED'

In normalized KPI build, rows are inserted only when:

- bottleneck_stage IS NOT NULL
- p_days IS NOT NULL
- threshold_days IS NOT NULL
- p_days >= threshold_days

So each row is threshold-qualified before it reaches the route.

## 2) Stage creation logic (cache build time)
Implemented in SphfAgeingService pending SQL.

Base date aliases used in logic:

- rel1 = first_date
- with1 = plinth_submitted_at
- rel2 = second_date
- with2 = lintel_submitted_at
- rel3 = third_date
- with3 = roof_submitted_at
- rel4 = fourth_date

A) FIRST_INSTALLMENT_RELEASED -> p_pair_key = tranche_1_to_plinth

Condition:
- rel1 IS NOT NULL
- with1 IS NULL
- rel2 IS NULL
- with2 IS NULL
- rel3 IS NULL
- with3 IS NULL
- rel4 IS NULL

Days/threshold:
- p_days = CURRENT_DATE - rel1
- threshold_days = 45
- include only if p_days >= 45

B) PLINTH_COMPLETED -> p_pair_key = plinth_to_tranche_2

Condition:
- with1 IS NOT NULL
- rel2 IS NULL
- with2 IS NULL
- rel3 IS NULL
- with3 IS NULL
- rel4 IS NULL

Days/threshold:
- p_days = CURRENT_DATE - with1
- threshold_days = 15
- include only if p_days >= 15

C) SECOND_INSTALLMENT_RELEASED -> p_pair_key = tranche_2_to_lintel

Condition:
- rel2 IS NOT NULL
- with2 IS NULL
- rel3 IS NULL
- with3 IS NULL
- rel4 IS NULL

Days/threshold:
- p_days = CURRENT_DATE - rel2
- threshold_days = 45
- include only if p_days >= 45

D) LINTEL_COMPLETED -> p_pair_key = lintel_to_tranche_3

Condition:
- with2 IS NOT NULL
- rel3 IS NULL
- with3 IS NULL
- rel4 IS NULL

Days/threshold:
- p_days = CURRENT_DATE - with2
- threshold_days = 15
- include only if p_days >= 15

E) ROOF_COMPLETED -> p_pair_key = roof_to_tranche_4

Condition:
- with3 IS NOT NULL
- rel4 IS NULL

Days/threshold:
- p_days = CURRENT_DATE - with3
- threshold_days = 45
- include only if p_days >= 45

## 3) Alarm banding logic
Release/roof type stages:
- tranche_1_to_plinth
- tranche_2_to_lintel
- roof_to_tranche_4

Alarm by p_days:
- green <= 45
- yellow <= 60
- orange <= 90
- red > 90

Submitted/completed type stages:
- plinth_to_tranche_2
- lintel_to_tranche_3

Alarm by p_days:
- green <= 15
- yellow <= 30
- orange <= 60
- red > 60

## 4) Current stage counts (tenant 20)
Query basis:
- table: sphf_normalized_kpi
- where p_valid = true and p_stage != COMPLETED
- grouped by p_stage and p_pair_key

1) 1ST INSTALLMENT RELEASED (tranche_1_to_plinth)
- total: 272,529
- green: 0
- yellow: 0
- orange: 19,186
- red: 253,343

2) PLINTH COMPLETED (plinth_to_tranche_2)
- total: 21,070
- green: 547
- yellow: 15,830
- orange: 2,683
- red: 2,010

3) 2ND INSTALLMENT RELEASED (tranche_2_to_lintel)
- total: 123,649
- green: 0
- yellow: 0
- orange: 29,088
- red: 94,561

4) LINTEL COMPLETED (lintel_to_tranche_3)
- total: 25,505
- green: 433
- yellow: 18,398
- orange: 5,376
- red: 1,298

5) ROOF COMPLETED (roof_to_tranche_4)
- total: 1,582
- green: 35
- yellow: 431
- orange: 254
- red: 862

Grand total (all pending bottleneck rows): 444,335

## 5) UI matrix stage definition alignment
Route/service stage definitions map by p_pair_key:

- First Installment Released -> p_pair_key = tranche_1_to_plinth
- Plinth Completed -> p_pair_key = plinth_to_tranche_2
- Second Installment Released -> p_pair_key = tranche_2_to_lintel
- Lintel Completed -> p_pair_key = lintel_to_tranche_3
- Roof Completed -> p_pair_key = roof_to_tranche_4

This is implemented in SphfStageBottlenecksService::kpiStageDefinitions().