# CRT / RCIA Platform — Technical Audit and Upgrade Plan

Prepared for: Climate Risk Tool (CRT) / RCIA Platform (`C:\ms4w\Apache\htdocs\crt` + `C:\ms4w\Apache\htdocs\climate_analysis_tool`)
Scope: Hazard/Exposure/Vulnerability/Capacity/Risk formulas, BMD projection methodology, new exposure/vulnerability layers (drought, soil type, population density), interface redesign, general robustness review.
Date: 30 August 2026

This document is a full read-through of the actual code (Node/Express app, PHP endpoints, Python models, PostGIS schema, and the Bootstrap/Leaflet front end), not a generic template. Section 3 documents exactly what the system does today; Section 4 lists concrete defects; Sections 5–7 give the redesign; Section 8 maps recommendations to the delivered code package.

---

## 1. Executive summary

The platform already implements a recognisable IPCC AR6 risk chain — `Risk = f(Hazard, Exposure, Vulnerability, Capacity)` — and already has two real climate-projection pathways (a BMD-trained LightGBM statistical model, and a CMIP6 multi-GCM SSP ensemble sampled from WorldClim-format rasters). That is a stronger starting point than most tools at this stage. The three biggest problems are not "wrong technology," they are:

1. **Exposure and Vulnerability are hazard-blind.** The same "Combined Exposure Score" (fixed weights: river 30%, flood 30%, storm surge 25%, salinity 15%) is used no matter which hazard the Risk Engine is evaluating. A drought risk calculation is currently diluted by flood/storm/salinity numbers that have nothing to do with drought. This is the main structural fix needed before adding new layers, and it is also exactly what makes the new drought/soil/population layers slot in cleanly (Section 5).
2. **The BMD "projection" model extrapolates a tree ensemble past its training data using a hand-written trend-slope + clamp routine.** That is a defensible safety patch, but it means the actual future signal for anything beyond ~2018–2024 is not coming from LightGBM at all — it is coming from an ad-hoc linear extrapolation. Meanwhile the platform already has a proper tool for future climate signal (the CMIP6/SSP ensemble) sitting next to it, combined with the BMD output through a second, inconsistent, ad hoc blending rule. Section 6 gives a concrete fix that keeps LightGBM but uses it for what it is good at.
3. **Two new physical layers you want to add (drought, population density) already have most of what they need on disk** — `shapefile/drought.shp` is a real BARC/DAE-style drought-proneness polygon layer keyed on the same `GEOCODE` used everywhere else in the schema, and there is already an `output/cdrought.gpkg` cleaned version. Soil type has no data file yet; population density has no data file yet. Section 5.2 gives ingestion + scoring designs for all three so you can drop in the actual files when ready.

Section 9 gives a phased rollout so nothing here has to go in as a single risky release.

---

## 2. Architecture as found

- **Front end**: one long `index.html` (Bootstrap 5 + Leaflet), ~15 stacked cards, ~10 vanilla-JS modules communicating through `window.*` globals and `CustomEvent`s (`crt:dripLoaded`, `crt:exposureUpdated`, `crt:capacityUpdated`, `crt:sspUpdated`, …). No build step, no framework — this is good for a government tool (no dependency rot) but means every new layer must follow the same event-bus convention or the Risk Engine (`final_risk.js`) will not see it.
- **Node/Express** (`server.js`, `app.js` routes, `routes/*.js`): thin API layer over PostGIS (`climate.js`, `bmd.js`, `bmdyearly.js`) and over the LGED ArcGIS FeatureServer for infrastructure geometry (`admin.js`, `infrastructure.js`).
- **PHP layer** (`php/*.php`): a second, parallel API surface (river/raster exposure, road profile, DRIP hazards, SSP climate, report generation) that talks to the same PostGIS database directly via `pg_query_params`/PDO, and shells out to Python for raster sampling and ML inference.
- **Python** (`climate_analysis_tool/*.py`): `bmd_lightgbm.py` (older monthly model), `bmd_yearly_model.py` (current yearly model, "safe_v2"), `raster_exposure.py` (flood/storm-surge/salinity raster sampling via `rasterio`), `ssp_climate.py` (CMIP6/SSP ensemble sampling + hazard blending).
- **PostGIS** (`crt` database): `bmdyearlydb`, `bmdmondb`, `dripdb`, `rivers`, `lgedcode` (BBS↔LGED code crosswalk) confirmed from the route/PHP code.
- **Static rasters**: `tif/flood_88_geo.tif`, `tif/storm_cc_geo.tif`, `tif/salinity_geo.tif` (national coverage, sampled per point). `ssp/` holds 32 WorldClim-format `.tif`s: 2 variables (prec, tmax) × 2 models (MPI‑ESM1‑2‑HR, EC‑Earth3‑Veg) × 2 scenarios (SSP245, SSP585) × 4 periods (2021‑2040 … 2081‑2100).
- **Vector layer already on disk but not wired in**: `shapefile/drought.shp` — polygon, fields `GEOCODE, THANANAME, DROUGHT_CL, DROU_CLASS, AREA_SQKM`. Confirmed values: `Very Severe(1), Severe(2), Moderate(3), Slight(4), No Drought(5)`, plus non-agricultural classes `9/10/11` (rivers/forests/hills, non-T.Aman areas). `GEOCODE` values (e.g. `57790` = Tentulia) are the same 5-digit BBS upazila code used as `BBS_Geocode` in `bmdyearlydb` and `bbsuzid` throughout the app — this joins cleanly to everything else with no crosswalk needed. A cleaned copy already exists at `output/cdrought.gpkg` (built by `correctdroughtnewnewnew.py` — geometry repair + gap-filling from neighbours).

---

## 3. Current formulas, exactly as implemented

### 3.1 Hazard (H)
- **Baseline**: `dripdb` per upazila (`Code`/`BBS_Geocode`), 11 hazards (Cyclone, Drought Kharif, Drought Pre‑kharif, Earthquake, River Erosion, Flash Flood, Flood, Land Slide, Salinity, Sea Level Rise, Storm Surge), each on a fixed 5-point scale {10, 30, 50, 70, 90} = {Very low…Very high}. User can override per-hazard in the UI.
- **Aggregate DRIP hazard** (`drip.js: computeAggregateHazard`): take the *highest occupied category* across all 11 hazards and average the scores within it (e.g. if any hazards are "Very high", average only those; otherwise fall back to "High", etc.). This is a priority/maximax rule, not a weighted composite.
- **BMD climate modifier blend** (`final_risk.js: blendHazardWithBMD`): `0.7 × DRIP + 0.3 × BMD_modifier`, only used when the SSP ensemble hasn't produced a `futureHazardScore`.
- **DRIP+SSP future hazard** (`ssp_climate.py: build_future_hazard`): `FutureHazard = DRIP + (CombinedClimateModifier − 50) × 0.5`, where `CombinedClimateModifier` is itself `year-banded weight × BMD + (1−weight) × SSP` (0.6/0.4 up to 2040, 0.4/0.6 to 2060, 0.3/0.7 beyond). **This is a second, different hazard-blending formula from the one in 3.1** — see Finding F2.

### 3.2 Exposure (E)
`raster_exposure.js: combinedExposure()` — fixed weights regardless of hazard:
```
E = 0.30·river_score + 0.30·flood_score + 0.25·storm_surge_score + 0.15·salinity_score
```
- River score: distance to nearest major/minor river (PostGIS `ST_Distance` on geography), classed `≤100m→90, ≤300m→70, ≤1000m→50, ≤3000m→30, else 10`.
- Flood/storm-surge: raster depth (m) sampled at the point, classed on fixed 0.3/0.6/0.9/1.2 m breakpoints → 10/30/50/70/90.
- Salinity: raster ppt, classed at 0.5/2/5/15 ppt (FAO salinity classes) → 10/30/50/70/90.
- No population, no settlement density, no drought, no soil — and critically, **all four components are always combined even when the hazard being assessed is, say, Drought or Earthquake**, for which river distance and salinity are not meaningful exposure drivers.

### 3.3 Vulnerability (V)
Two independent methods (radio-toggle), only one active at a time:
- **Road profile** (`road_profile.js`): `V_road = weighted-avg(Surface 35%, Condition 20%, Drainage Weakness 15%, Embankment 30%)`, N/A inputs excluded with weight renormalisation. Surface vulnerability is a fixed lookup (RCC 10 → ERT 90). Embankment vulnerability compares embankment height to the *max of sampled flood depth and storm-surge depth* at that point (freeboard logic): `freeboard ≤0.1m→70, ≤0.2m→50, ≤0.3m→30, >0.3m→10`, and `emb < hazard depth → 90`.
- **Field assessment** (`vulnerability_field.js`): for each DRIP hazard at "High"/"Very High" only, average three manually-scored damage dimensions (Physical Infrastructure, Construction Materials, Site Condition) against narrative guidance pulled from `hazard_description.php`; combined score = average of hazard averages.
- No soil/subgrade term in either method today.

### 3.4 Capacity (C)
`weighted-avg(Drainage Capacity 40%, Protective Works 30%, Network Redundancy 30%)`, yes/no/NA fields, NA excluded with renormalisation.

### 3.5 Final Risk
`final_risk.js: calcRiskStandard` — normalise H, E, V, C to [0,1], then:
```
Risk_standard = H · E · V · (1 − C)                      (IPCC AR6 multiplicative form)
Risk_policy   = (0.5·(H·E·V) + 0.5·mean(H,E,V)) · (1 − C)  ("LGED policy adjusted" toggle)
```
computed at Hazard P10/P50/P90 to carry the SSP ensemble's uncertainty band through to the final number. This part of the design is sound and standard (IPCC AR6 WGII Ch.16 risk framing; same multiplicative logic underlies INFORM Risk and most WFP/UNDRR composite risk indices) — the redesign in Section 5 keeps this formula and only makes H, E, V hazard-specific.

### 3.6 BMD "projection" model (`bmd_yearly_model.py`, `MODEL_VERSION = safe_v2_20260324`)
- Features: `Year, BBS_Geocode (categorical), LON, LAT` only — no other meteorological or physical covariates.
- LightGBM regression, `log1p` transform for rainfall, monotonic constraint (non-decreasing in Year) for temperature, early stopping, L1/L2 regularisation — competently built as a *spatio-temporal interpolator of historical station data*.
- **For any requested year ≤ the last training year**, this is a normal, reasonable ML prediction.
- **For any requested year beyond the last training year** (which is exactly the case this endpoint exists for — asset lifetime projections to 2040–2058), the code does *not* extrapolate the tree model. It freezes the model input at `train_year_max`, takes that prediction as a base, then adds `blended_slope × years_ahead`, where `blended_slope` is `0.7×local 10-year linear trend + 0.3×national linear trend`, hard-clamped to ±3%/yr (rain) or +0.08/−0.02 °C/yr (temp), and finally clamps the whole result to `[0.7×hist_min, max(1.8×hist_max, 2.5×local_mean)]` (rain) or `[hist_min−1, hist_max+3]` (temp).
- In other words: **the "LightGBM projection" for every year that matters for this tool (project design life, 5–30+ years out) is actually a guarded linear-trend extrapolation**, not a gradient-boosted prediction. The guardrails are sensible engineering (they stop the classic tree-extrapolation failure mode of a flat prediction, or worse, a wild one under `linear_tree`), but the *label* "LightGBM model" overstates what is producing the future number, and the scenario (SSP245 vs SSP585) has no way to enter this pathway at all — Year and geocode cannot represent radiative forcing.

### 3.7 SSP / CMIP6 ensemble (`ssp_climate.py`)
This is the part of the codebase that is actually doing scenario-aware climate projection correctly: it samples monthly precipitation/Tmax rasters per GCM/scenario/period at the point, computes `rain_ratio` and `temp_delta` against the BMD historical baseline, converts to 0–100 scores with hazard-specific rain/temp weights (`climate_weights_for_hazard`), and reports the full ensemble as P10/P50/P90 across the two available GCMs. This is good practice (multi-model ensemble uncertainty), it is just under-used — see Section 6.

---

## 4. Findings

| # | Finding | Severity | Type |
|---|---|---|---|
| F1 | Exposure and Vulnerability are computed once, hazard-blind, and reused for every hazard in the Risk Engine dropdown. Drought/earthquake risk is currently contaminated by flood/river/salinity/storm numbers. | **Critical** | Methodology |
| F2 | Two different, disconnected hazard/BMD blending formulas exist (`0.7·DRIP+0.3·BMD` in `final_risk.js` vs. the year-banded `w·BMD+(1−w)·SSP` then `DRIP+(CM−50)·0.5` in `ssp_climate.py`). Whichever runs last silently wins; results are not reproducible from the UI alone. | High | Consistency |
| F3 | The BMD "projection" beyond the training window is a linear-trend extrapolation dressed as a LightGBM output, and cannot represent SSP scenario divergence. | High | Scientific credibility |
| F4 | No exposure layer for drought or population/settlement density; no vulnerability layer for soil/subgrade — this is the user's explicit ask, addressed in Section 5. | High | Feature gap |
| F5 | Score breakpoints (flood 0.3/0.6/0.9/1.2 m, salinity 0.5/2/5/15 ppt, river 100/300/1000/3000 m, etc.) are round numbers, not derived from the empirical distribution of each raster/vector over Bangladesh. Two independent hazard modules (`raster_exposure.php` flood classes and `raster_exposure.js`/DRIP colour scale) also use *different* colour-to-score conventions at the same numeric boundaries — cosmetic but confusing in review. | Medium | Rigor |
| F6 | Plaintext PostgreSQL and ArcGIS credentials (`Zazabar65_`, `crelic$123`) are hard-coded and duplicated across `php/config.php`, `bmd_lightgbm.py`, `bmd_yearly_model.py`, `raster_exposure.py`, `ssp_climate.py`. | **High** | Security |
| F7 | Every raster/ML request (`raster_exposure.php`, `ssp_climate.php`, BMD routes) does `shell_exec()`/`spawn()` to start a fresh Python interpreter and re-import `rasterio`/`lightgbm`/`sqlalchemy` per HTTP call. This is slow (hundreds of ms–seconds of pure interpreter/import overhead per click) and has no queueing, so concurrent users can pile up OS processes. | Medium-High | Performance/robustness |
| F8 | Surface-type normalisation, lifespan table, and surface-vulnerability lookup are hand-copied in three languages (`climate_proj.js`, `road_profile.js`/`drip.js` in JS; `road_profile.php`; `raster_exposure.py`/`ssp_climate.py` in Python). They already agree today, but any future edit (e.g. adding a new surface type) has to be made in 5 places or the layers silently disagree. | Medium | Maintainability |
| F9 | No authentication/authorization or rate limiting visible on any `php/*.php` or `/api/*` endpoint, several of which spawn a Python process per call (F7) or run raw SQL. On a public-facing government tool this is a real availability and data-integrity risk, independent of F6. | Medium-High | Security |
| F10 | `php/road_profile.php` builds its ArcGIS `where` clause by string concatenation with manual quote-escaping rather than a parameterised query (contrast with `river_exposure.php`, which correctly uses `pg_query_params`). | Low-Medium | Injection hygiene |
| F11 | UI is a single ~640-line scroll of ~15 stacked cards; the Risk Engine result (the thing every user actually wants) is the second-to-last card, reachable only after scrolling past unrelated sections; there is no persistent indication of overall progress or current risk while filling the form. | Medium | UX |

---

## 5. Recommended formula redesign

### 5.1 Make Exposure and Vulnerability hazard-conditional (the core fix)

This is the same pattern the codebase *already* uses for climate weighting (`climate_weights_for_hazard()` in `ssp_climate.py`) — it just needs to be applied one level up, to E and V themselves. Each hazard gets its own exposure recipe and vulnerability recipe, drawn from the same underlying physical layers, so the physical layers (river, flood, storm surge, salinity, **drought, soil, settlement/population**) are inputs to a matrix rather than one flat blend.

> **REVISION (2026-08-30 — supersedes the original weight matrices below):** per the user's explicit direction, **population/settlement is a VULNERABILITY factor, not an Exposure factor**, in this deployment. `drought` is a real, populated PostGIS layer (`public.drought`, pre-computed `risk`/`score`) and stays in the Exposure matrix. `settlement` (population, `public.settlement`) moves to the Vulnerability matrix, scored as a socio-vulnerability/consequence factor — how many people are affected if a road fails — which is the WorldRiskIndex/SoVI convention rather than the strict IPCC "people in the hazard footprint" convention. Both conventions are defensible in the literature; what matters is using exactly one of them so population is never counted in both E and V at once (that would double-count it in `Risk = H·E·V·(1−C)`). The tables below reflect this final design; the code (`js/hazard_layer_profiles.js`) is the source of truth if this document and the code ever drift.

**Exposure weight matrix** (rows sum to 1.0 per hazard; starting values below, calibrate per §5.4):

| Hazard | River | Flood | Storm surge | Salinity | Drought zone |
|---|---|---|---|---|---|
| Flood | 0.40 | 0.55 | 0.05 | 0.00 | 0.00 |
| Flash Flood | 0.55 | 0.45 | 0.00 | 0.00 | 0.00 |
| River Erosion | 1.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| Storm Surge | 0.05 | 0.15 | 0.65 | 0.15 | 0.00 |
| Cyclone | 0.05 | 0.15 | 0.70 | 0.10 | 0.00 |
| Sea Level Rise | 0.05 | 0.05 | 0.45 | 0.45 | 0.00 |
| Salinity | 0.00 | 0.00 | 0.20 | 0.80 | 0.00 |
| Drought (Kharif / Pre‑kharif) | 0.00 | 0.00 | 0.00 | 0.00 | 1.00 |
| Earthquake *(placeholder — see below)* | 0.25 | 0.25 | 0.20 | 0.10 | 0.20 |
| Land Slide *(placeholder — see below)* | 0.10 | 0.50 | 0.20 | 0.00 | 0.20 |
| **Overall DRIP (no single hazard selected)** | 0.25 | 0.25 | 0.20 | 0.10 | 0.20 |

Rationale: with population removed, each row's weight is redistributed proportionally across that hazard's genuinely relevant physical layers, so e.g. Flood is now driven almost entirely by river + flood-depth, and Salinity by salinity + storm surge (saline intrusion is storm-surge-linked). **Known limitation — Earthquake and Land Slide:** neither hazard has any genuinely relevant layer among river/flood/storm/salinity/drought (earthquakes are geophysical; landslides are triggered by slope + rainfall intensity, not modelled here). Previously `population` filled this gap as an exposure proxy; with it moved to Vulnerability, these two rows fall back to the `__overall__` blend (Land Slide weighted slightly toward flood, since heavy rainfall is the usual trigger) purely so Exposure is never null. Treat their Exposure scores as low-confidence until a real seismic (PGA/shaking-intensity) or slope/rainfall-intensity layer is added — this is a genuine data gap, flagged rather than hidden.

**Vulnerability weight matrix** (road-profile method; field-assessment method is unaffected since it is already hazard-specific by construction). `Settlement` is the new socio-vulnerability component (§5.2), added at a uniform first-draft weight of 0.15 across every hazard — cross-cutting rather than hazard-specific, unlike the physical road factors — with the other four components scaled down proportionally so each row still sums to 1.0:

| Hazard | Surface | Condition | Drainage weakness | Embankment | Soil (subgrade/drainage class) | Settlement (population) |
|---|---|---|---|---|---|---|
| Flood / Flash Flood | 0.21 | 0.13 | 0.13 | 0.21 | 0.17 | 0.15 |
| Storm Surge / Cyclone / SLR | 0.21 | 0.13 | 0.09 | 0.29 | 0.13 | 0.15 |
| River Erosion | 0.17 | 0.09 | 0.04 | 0.13 | 0.42 *(bank-material erodibility)* | 0.15 |
| Drought | 0.17 | 0.09 | 0.00 | 0.00 | 0.59 *(shrink–swell / low water-holding soils crack pavements and subgrade)* | 0.15 |
| Salinity | 0.26 | 0.08 | 0.04 | 0.04 | 0.43 *(saline-soil corrosion/base weakening)* | 0.15 |
| Earthquake / Land Slide | 0.26 | 0.25 | 0.00 | 0.00 | 0.34 *(liquefaction/slope-stability proxy once available)* | 0.15 |
| **Overall / no hazard selected** | 0.25 | 0.17 | 0.09 | 0.17 | 0.17 | 0.15 |

Capacity (C) stays hazard-generic (drainage capacity, protective works, redundancy are genuinely cross-cutting adaptive-capacity measures) — no change needed there.

Final risk formula is **unchanged**: `Risk = H_hazard · E_hazard(matrix) · V_hazard(matrix) · (1 − C)`, still evaluated at H's P10/P50/P90. The only change is that E and V are now looked up per selected hazard instead of being one fixed blend — this is a strictly additive change to `final_risk.js`'s data flow, not a rewrite of its interpretation logic.

### 5.2 New layers — data design

All three new layers are now real, imported PostGIS tables (imported by the user via `ogr2ogr` on their VPS) rather than design proposals — see `sql/001_new_layers.sql` for the exact schemas and import commands used.

**Drought exposure** — `public.drought` (gid, geocode, thananame, `drou_class` numeric code, `drought_cl` text label, `risk`, `score`, geom). Populated and confirmed working. `risk`/`score` are pre-computed on the table (90/70/50/30/10 for Very High…Very Low), so `php/drought_exposure.php` reads them directly with a point-in-polygon (`ST_Contains`) query — no re-derivation. Classes 9/10/11 (rivers/forest/hills/non‑T.Aman) are treated as **excluded, not zero** (a non-agricultural polygon isn't "very low drought exposure," it's "not applicable").

**Settlement / population vulnerability** — `public.settlement` (gid, div, dist, thana, unionname, mauza, `area` (assumed m², verify per `sql/001_new_layers.sql`), com_un, `set_pop`, geom). Populated and confirmed working. Two scoring modes, chosen automatically by `js/settlement_vulnerability.js` based on context:

- **Road-corridor buffer mode** (`php/road_settlement_vulnerability.php`) — the authoritative mode whenever Infrastructure = Road and a road is selected, per explicit user design. Fetches the selected ROADCODE's full line geometry (all sections merged) from the same ArcGIS layer `php/road_profile.php` already queries, buffers it (default 500 m, adjustable 250/500/1000 m in the UI), and sums population from every settlement polygon overlapping the buffer — full `set_pop` for polygons entirely inside, `set_pop × (intersection area / polygon's own area)` for partial overlaps. The total is classified into 5 bands (below). **Caveat, flagged in the code and worth your attention:** classifying on the *raw* population total makes the score sensitive to the selected road's length — a long road sweeps a bigger corridor than a short one at the same settlement density. The endpoint also returns `population_per_km_road` and `avg_density_per_km2` (both length-independent) alongside the raw total specifically so you can sanity-check this against your real road inventory; flip `$USE_LENGTH_NORMALIZED` in that file to `true` to classify on the per-km figure instead if the raw total turns out to favor long roads regardless of density.
- **Point / nearest mode** (`php/settlement_vulnerability.php`, the original design) — used for any non-road assessment, or before a road finishes loading. Computes density = `set_pop / area` at query time and scores it against quantile breakpoints computed from the table's own distribution (self-calibrating). Falls back to the nearest settlement within 500 m with linear distance decay when no polygon directly contains the point.

**Road-corridor buffer classification bands** (first draft, per the user's explicit numbers — recalibrate once run against your real road inventory, same status as every other weight/breakpoint in this document):

| Total affected population in buffer | Score | Label |
|---|---|---|
| < 200 | 10 | Very Low |
| 200 – 399 | 30 | Low |
| 400 – 699 | 50 | Medium |
| 700 – 999 | 70 | High |
| ≥ 1000 | 90 | Very High |

**Soil vulnerability** — `public.soil` (gid, `gst_code`, `gst_label`, `risk`, `score`, geom) — same pre-computed-score pattern as `drought`. **Now populated and confirmed** (25 rows; the numeric-field-overflow fix — `-lco PRECISION=NO` — worked here too). `php/soil_vulnerability.php` has been rewritten to read `gst_code`/`gst_label`/`risk`/`score` directly, the same simplification already applied to `drought_exposure.php`.

**Resolved: the hazard-conditional inversion originally planned for this layer is NOT applied, and that is now confirmed correct rather than a placeholder.** With the real 25-class table visible, `score` reads as a general geotechnical/structural subgrade-suitability rating, not a simple flood-drainage proxy: Peat and Acid Sulphate Soils score Very High (90) because they are poor bearing-capacity, corrosive materials that are bad for a road foundation under ANY hazard including drought (bad subgrade still cracks and settles when dry); Deep Red-Brown Terrace Soils score Very Low (10) as stable, well-drained material in any condition. Inverting per hazard, as originally planned before the real data existed, would have incorrectly told the system a peat bog becomes low-vulnerability in a drought. The same score for every hazard is therefore the right design, not a fallback.

**Still open, needs your judgement (not changed):** 4 of the 25 classes aren't really "soil types" for road engineering — codes 34/35/39/40 (Sundarban, Waterbodies, Reserved Forest, Kaptai Lake) are land-cover/exclusion zones, all pre-scored Very High. Unlike drought's classes 9/10/11 (confirmed "not an agricultural zone — exclude, don't zero"), it isn't obviously wrong for these four to score high at face value — a road near Sundarban or a reservoir margin plausibly does carry elevated structural risk. Flagged in `soil_vulnerability.php`'s header comment rather than changed; tell me if any of these four should instead be treated as "not applicable" the way drought's non-agricultural classes are.

### 5.3 Worked example
Upazila with Flood selected as the risk hazard, H_p50 = 70 (DRIP "High"), river score 50, flood-depth score 70, settlement/population vulnerability score 90 (dense riverine settlement), surface = ERT (90), embankment freeboard 0.15 m (score 50), soil = poorly-drained clay under flood weighting (score 70), capacity C = 40 (some protective works present):

```
E = 0.40·50 + 0.55·70 + 0.05·(storm n/a → excluded, renormalised) ≈ 58.4
V = 0.21·90 + 0.13·(condition, say 50) + 0.13·(drainage weak, say 90) + 0.21·50 + 0.17·70 + 0.15·90 ≈ 73.7
Risk_p50 = (0.70)(0.584)(0.737)·(1 − 0.40) × 100 ≈ 18.1   → "Low-Medium" on the 0–100 scale
```
compare against the *old* blanket formula, which would have pulled in an irrelevant salinity score and diluted E even for this clearly flood-dominated case.

### 5.4 On weight calibration — be honest about precision
The matrices above are a defensible starting point (consistent with how DRIP itself and INFORM Risk assign hazard-specific indicator weights), **not a claim of measured precision**. Before these numbers go into a report that LGED/BMD will rely on, run a short AHP (Analytic Hierarchy Process) pairwise-comparison workshop with 4–6 domain experts (LGED engineers, a BMD climatologist, a BBS/DRIP analyst, an SRDI soil scientist) per hazard row, and check the consistency ratio (<0.10). This is the standard, defensible way composite risk indices set weights (it is literally how DRIP and INFORM Risk were built) — a couple of hours of expert time buys real methodological legitimacy that no amount of hand-tuned numbers here can.

---

## 6. BMD projection model — recommendation

**Keep LightGBM, but stop asking it to do something it structurally cannot do (extrapolate scenario-divergent climate signal from Year alone), and stop discarding the uncertainty information you already compute elsewhere.**

1. **Split responsibilities cleanly.**
   - `bmd_yearly_model.py` (historical LightGBM) → used **only** for years within/near the observed record: baseline climatology, spatial interpolation between BBS geocode centroids, gap-filling missing station-years. This is genuinely what gradient boosting is good at here.
   - `ssp_climate.py` (CMIP6/SSP ensemble, already built) → the **sole source of future signal**, anchored to the BMD historical baseline via the `rain_ratio`/`temp_delta` mechanism that already exists. This is the scientifically correct tool for "what will rainfall/temperature look like in 2050 under SSP245 vs SSP585" — it already produces P10/P50/P90 across GCMs, which the ad hoc trend-extrapolation cannot honestly claim to do (its "uncertainty" is a manually clamped range, not a modelled one).
   - Concretely: in `routes/bmdyearly.js`, once `requested_year > train_year_max`, stop calling `bmd_yearly_model.py`'s guarded extrapolation and instead return the SSP-ensemble-derived value for that geocode/period (or a clear "use `/api/climate/ssp` for this year" response). This removes `apply_future_guardrails` from the critical path entirely rather than deleting it outright — keep it only as an emergency fallback for periods/locations where the SSP rasters don't have coverage.
   - Unify Finding F2: one hazard-blend formula, not two. Recommend keeping the year-banded `w·BMD+(1−w)·SSP` logic (it is the more defensible of the two, since it deliberately shifts weight toward the SSP ensemble the further out you project) and retiring the separate `0.7·DRIP+0.3·BMD` shortcut in `final_risk.js`.

2. **Upgrade LightGBM itself to native quantile regression** for the baseline period, instead of a single point estimate: train three models per target (`objective="quantile"`, `alpha ∈ {0.1, 0.5, 0.9}`), so the historical/baseline branch reports a real P10/P50/P90 on the same footing as the SSP ensemble, rather than a point value that only gets a spread once the SSP branch takes over. This is a small, mechanical change to `bmd_yearly_model.py` (same features, same monotonic-constraint machinery, just three trainings instead of one) — provided as `bmd_yearly_quantile_model.py` in the delivered package.

3. **Models considered and why LightGBM-quantile is the pragmatic choice**: Quantile Regression Forests and CatBoost-with-quantile-loss are essentially the same family and would perform comparably — not worth a technology change for a marginal gain. A Generalized Additive Model (smooth year trend + geocode random effect) would be more transparent to a non-ML climate reviewer and is worth prototyping if BMD wants something they can audit by hand, but LightGBM-quantile keeps the existing training pipeline, model-versioning, and guardrail infrastructure you've already built and tested, and is the change with the best effort-to-credibility ratio right now.

4. Do **not** attempt to make the historical LightGBM model scenario-aware by adding an SSP/RCP forcing feature unless you also have a physically-informed relationship between that feature and the target (a tree model will happily "learn" a coefficient on a categorical scenario ID that has zero physical meaning, which is worse than being honest that Year-only regression can't do scenario projection).

---

## 7. Interface redesign

The underlying event-driven architecture (each module dispatches a `crt:*` event, `final_risk.js` recalculates on any of them) is actually well-suited to a better UI — it just needs an information-architecture pass, not a rewrite. Current layout is one long scroll of 15 cards with the actual answer (Final Risk Engine) second from the bottom.

**Redesign, delivered as a non-destructive `index_v2.html` + `redesign.css`** (every existing element ID, script, and event is untouched — the new files only add a shell around them):

- **Stepper navigation** across the top: `1 Location & Feature → 2 Hazard → 3 Exposure → 4 Vulnerability & Capacity → 5 Climate Projection → 6 Risk & Report`. Each step is the existing cards, just grouped and shown one group at a time (pure CSS/JS show-hide — no functional change to any card's contents).
- **Sticky risk-summary sidebar** (desktop) / collapsible bottom sheet (mobile): live H/E/V/C mini-readout and a colour-coded risk gauge, updated on the same `crt:*` events `final_risk.js` already listens for, visible from step 1 onward instead of only after scrolling to the bottom.
- **Risk gauge** replacing the three plain P10/P50/P90 boxes: a single arc/needle gauge coloured by the IPCC 5-class scale, with the three percentile boxes shown underneath as detail — keeps all existing markup (`finalRiskP10Box` etc.) as-is, gauge is purely decorative/additional.
- **New cards added in the same visual language as existing ones**: Drought Exposure, Population Exposure, Soil Vulnerability — placed under the Exposure and Vulnerability steps respectively.
- **Hazard-conditional badge**: next to the Exposure/Vulnerability card headers, a small "computed for: Flood" tag that updates when the user changes the Risk Engine's hazard dropdown, making the F1 fix visible to the user rather than implicit.

---

## 8. Delivered package contents

```
crt/upgrade_2026-08/
  AUDIT_AND_UPGRADE_PLAN.md        (this document)
  README.md                        (apply / rollback / test checklist)
  sql/001_new_layers.sql           (indexes + ogr2ogr notes for the real drought/settlement/soil tables)
  php/drought_exposure.php         (point-in-polygon drought EXPOSURE score, reads public.drought directly)
  php/settlement_vulnerability.php (point-in-polygon + nearest-fallback settlement/population VULNERABILITY score — non-road fallback mode)
  php/road_settlement_vulnerability.php (road-corridor buffer settlement/population VULNERABILITY score — authoritative for road assessments, §5.2)
  php/soil_vulnerability.php       (soil VULNERABILITY score, reads public.soil's gst_code/gst_label/risk/score directly)
  js/hazard_layer_profiles.js      (single source of truth: exposure/vulnerability weight matrices, §5.1)
  js/drought_exposure.js           (front-end module, mirrors raster_exposure.js pattern)
  js/soil_vulnerability.js         (front-end module)
  js/settlement_vulnerability.js   (front-end module — picks road-buffer vs point mode automatically; replaces the superseded population_exposure.js/.php, moved to _to_delete)
  js/exposure_v2.js                (hazard-conditional Combined Exposure — supersedes raster_exposure.js's combinedExposure())
  js/vulnerability_v2.js           (hazard-conditional Vulnerability — extends road_profile.js's V_road, now includes settlement)
  css/redesign.css                 (stepper, sticky summary, gauge)
  index_v2.html                    (redesigned shell, all original IDs/scripts intact; at crt/ root, not inside this subfolder — see README correction note)
  landing.html                     (marketing + dashboard landing page; also at crt/ root)

climate_analysis_tool/upgrade_2026-08/
  python/bmd_yearly_quantile_model.py  (LightGBM quantile regression p10/p50/p90 upgrade)
```

Nothing in the existing, live files is modified. Everything above is new, in a clearly named subfolder, so you can review, test, and merge on your own schedule — see `README.md` for the step-by-step apply/rollback procedure and a test checklist.

---

## 9. Suggested rollout order

1. **Phase 1 (additive, zero risk to production)**: load the SQL, drop the new PHP/Python/JS files in place, add the new cards to a *copy* of `index.html`; verify the three new layers return sane values for a handful of known points before touching the Risk Engine.
2. **Phase 2 (formula fix)**: switch `final_risk.js` to hazard-conditional E/V via `hazard_layer_profiles.js`; run the worked example in §5.3 and a few real cases side-by-side against the current production values so LGED/BMD reviewers can see exactly what changed and why.
3. **Phase 3 (expert calibration)**: run the AHP weighting workshop (§5.4); replace the starting-point matrices with the calibrated ones.
4. **Phase 4 (BMD/SSP unification)**: apply Section 6's routing change and quantile-regression upgrade; retire the duplicate hazard-blend formula.
5. **Phase 5 (hardening)**: rotate the exposed credentials into environment variables/secrets, add basic auth or an API key + rate limit in front of the `php/*.php` and Python-spawning endpoints, and — only once traffic justifies it — replace per-request `shell_exec`/`spawn` with a small persistent FastAPI/Flask microservice for raster sampling and inference.
6. **Phase 6 (UI)**: roll out `index_v2.html` once Phases 1–2 are validated, so the new layers and the new gauge appear together rather than in two separate visual changes.

---

## References
- IPCC AR6 WGII (2022), Chapter 16 — risk framing `Risk = f(Hazard, Exposure, Vulnerability)` with adaptive capacity as a risk-reducing term.
- UNDRR/INFORM Risk Index methodology — hazard-specific indicator weighting and AHP-based calibration practice.
- WorldPop (worldpop.org) — gridded population density products for Bangladesh.
- BBS Population & Housing Census 2022 — authoritative national population counts by administrative/mouza unit.
- SRDI (Soil Resources Development Institute, Bangladesh) — Agro-Ecological Zone and soil texture/drainage mapping.
- FAO Harmonized World Soil Database (HWSD v2) — international fallback soil raster.
- WorldClim v2.1 / CMIP6 downscaled GCM projections — source format of the `ssp/*.tif` files already in use.
- LightGBM documentation — `objective="quantile"` for native prediction intervals.