feat/rate-price

fetchPerPeriodDemandForOrg: missing DISTINCT ON → duplicate demand rows → false positive findings

domains/utility/src/finding/finding.queries.ts:694

The `base` CTE has no `DISTINCT ON (uc.id, b.year_month)`, unlike `fetchBillFactRowsForOrg` which uses `DISTINCT ON (uc.contract_number, b.year_month)`. Multiple XML bills for the same (contract_id, year_month) are a valid DB state (re-processing, duplicate folios with same unique_index). Both would match the historicData entry for that yearMonth, both pass `ym_rank <= 24` (DENSE_RANK assigns equal rank to equal year_month). Downstream: `detectPuntaManagement` sees inflated `punta.length`, satisfying `PUNTA_MIN_MONTHS=6` on as few as 3 real summer months; `detectPerPeriodLoadFactor` has weighted-toward-multi-bill-months util average. Fix: add `DISTINCT ON (uc.id, b.year_month)` with `ORDER BY uc.id, b.year_month, b.period_end DESC` to the base CTE, mirroring `bill_facts_base`.

windowed CTE partitions by rpu not contractId — terminated + active contracts share the 36-month cap

domains/utility/src/consumption-series/consumption-series.queries.ts:113

`DENSE_RANK() OVER (PARTITION BY rpu ORDER BY year_month DESC)` groups bills across terminated and active contracts for the same RPU. Old bills from a terminated contract consume rank slots, potentially excluding recent bills from the active contract's older months when combined history exceeds 36 months. Fix: partition by `utility_contract_id` instead of `rpu`.

detectPuntaManagement: × 12 annualization of summer-only punta signal may over-estimate ~2×

domains/utility/src/finding/finding.decisions.ts:1080

`avoidableAnnualMxn = avoidableKw * capacityRate * 12`. For GDMTH, punta demand only applies in summer (typically May–Oct). The capacity charge in winter may be set by base/intermedio demand unaffected by punta management behaviour. Multiplying by 12 assumes punta is the billing determinant all year. The `puntaMonths` count is present in details but not used to pro-rate. Impact: `estimatedImpact` shown to users can be 2× the real savings potential.

toNumberOrNull("") returns 0, not null — latent risk at future call sites

domains/utility/src/consumption-series/consumption-series.queries.ts:41

`Number('') === 0` is finite → returns 0. Current call sites use `NULLIF(..., '')::numeric` in SQL so empty strings never reach TypeScript. Latent risk if a future caller passes a raw text column without a NULLIF guard — would silently treat empty as zero consumption.

LEFT JOIN LATERAL hist::jsonb throws on non-array historicData — no type guard

domains/utility/src/finding/finding.queries.ts:734

If `base.hist` is a valid JSON non-array (e.g. an object `{...}` from a corrupted or reprocessed bill), `jsonb_array_elements` raises 'cannot call jsonb_array_elements on a non-array', aborting the entire org's detector run. The guard `length(base.hist) > 10` blocks NULLs/empty but not malformed objects. Adding `AND jsonb_typeof(base.hist::jsonb) = 'array'` to the ON clause would make it safe.

cohortKey + cohortSize in finding details leaks cross-org aggregate signal

domains/utility/src/finding/finding.decisions.ts:983

`peer_benchmark_consumption` and `overnight_load_factor` findings write `cohortKey` (e.g. `"GDMTH::Jalisco"`) and `cohortSize` (count of contracts in the pool) into the `details` JSONB, which is passed verbatim to the API. The cohort is built from ALL Batu-managed contracts across all orgs. An entitled user can infer how many Batu customers share their tariff+region profile — a cross-org count disclosure. Consider omitting `cohortSize` from the API projection or replacing it with a bucketed label (`"small cohort" / "medium cohort" / "large cohort"`).

Unbounded LATERAL expansion of hist JSON — large historicData arrays could cause query fan-out

domains/utility/src/finding/finding.queries.ts:734

`jsonb_array_elements(base.hist::jsonb)` has no element count cap (unlike the scalar subquery on line 706 which uses `LIMIT 1`). Data is Batu-owned pipeline output, not user input, so no injection risk. A malformed or oversized `historicData` array from a buggy ingestion run could cause severe row fan-out. A `WHERE jsonb_array_length(base.hist::jsonb) < 500` guard in the ON clause would bound the exposure.

fetchPerPeriodDemandForOrg extracts ESTIMACION by raw XML key, not conceptId

domains/utility/src/finding/finding.queries.ts:711

Uses `e->>'key' = 'ESTIMACION'` (raw XML tag name) rather than a `bill_concepts_catalog` JOIN on `concept_name = 'estimationFlag'` (the approach used in `consumption-series.queries.ts` and documented in CLAUDE.md § Dual key space). Both approaches work today because `source='xml'` is filtered, but the inconsistency means a CFE format change or tag rename would silently drop the classification for per-period demand while the consumption-series path still works. Align with the catalog-join approach.

consumptionSeriesQueries export is unused — dead surface in the FCIS namespace

domains/utility/src/consumption-series/index.ts:17

The only caller of `fetchConsumptionBillInputsForOrg` imports it by name from `'../consumption-series'`. The `consumptionSeriesQueries` object is exported but has no callers. Per canonical-form.md, the FCIS namespace is the public API; exporting an unused raw-query object invites future direct call-site usage that bypasses shells.

throw in insert query is pre-existing ADR-016 deviation (reformatted, not introduced here)

domains/utility/src/finding/finding.queries.ts:362

`if (!row) throw FindingErrors.databaseError('insert')` appears in the diff due to quote-style normalization but was present before this branch. This is a pre-existing ADR-016 deviation (queries should return null, not throw). Not introduced by this PR — tracking separately.

No mapper/shells/type-check for ConsumptionSeries — intentional for derived read-only entity

domains/utility/src/consumption-series/

canonical-form.md: 'Not every entity needs every file. Read-only lookup entities may lack decisions and shells.' ConsumptionSeries is derived (no stored Drizzle table), so no mapper, type-check, or shell is needed. Intentional.

punta_management@2 as first DB version — correct rule-change bump

domains/utility/src/finding/finding.shells.ts:71

Commit `fef1896a` introduced `punta_management@1` (flat placeholder rate); this commit (`364ea621`) replaces it with real zone rates. The @2 bump is correct — any change to `estimatedImpact` computation must bump the version so `decideUpsertFinding` overwrites stale findings via the update path.

Recency tiebreak (same-class overlap) completely untested

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts

The day-ownership model breaks ties between two bills of the same class using the later `periodEndMs` (production: `newPr === CLASS_PRIORITY[cur.billClass] && p.endMs > cur.periodEndMs`). A bimonthly measured bill partially overlapping a monthly measured bill is the canonical real-world case. No test exercises this path — any future refactor of priority logic could silently break the tiebreak.

Mixed per-period bands (some covered days with bands, some without) → perPeriod suppressed — untested

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts

The guard at `buildConsumptionSeries` line 239 only emits `perPeriod` when `daysWithPerPeriod === daysCovered`. If any owning day came from a bill without band data, the whole month loses its TOU breakdown. No test exercises this: e.g., a GDMTH bill with bands covering Jan 1–15 + a measured bill without bands covering Jan 16–31 should produce `perPeriod: undefined` for January. The demand detectors depend on this field's presence.

detectPeerBenchmark missing warning-severity test (30–50pp deviation gap)

domains/utility/src/finding/__tests__/finding.detectors.test.ts

Three outcomes: no-find (≤30pp), warning (30–50pp), critical (>50pp). Tests cover critical (+55pp) and the no-find boundary — but there is no test for the warning path. `PEER_WARN_DEVIATION = 0.3` and `PEER_CRIT_DEVIATION = 0.5` are independent branches that need independent coverage.

detectConsumptionYoySpike 'ignores low-confidence' test uses value coincidence — doesn't prove the filter

domains/utility/src/finding/__tests__/finding.detectors.test.ts:243

All middle months share `kwhPerDay=100`, so the test passes whether or not the low-confidence month is filtered. Setting the trusted prior-year to a distinct value (e.g. 80) and the low-confidence month to an implausibly high value (e.g. 200) would prove the filter independently of the value coincidence.

Partial month: kwh total and kwhPerDay never asserted

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts:269

The null-kwh-bill test asserts `coverage.source === 'partial'` and `billIds` but never checks `kwh` or `kwhPerDay` on the partial month. `kwhPerDay` divides by `daysCovered` (covered days), not by calendar days — a wrong denominator would silently produce inflated daily rates for partial months that slip through confidence filtering.

detectPerPeriodLoadFactor: null tariff/region exclusion untested

domains/utility/src/finding/__tests__/finding.detectors.test.ts

Production line 1163: `if (c.tariffCode === null || c.region === null) continue` drops contracts from cohort grouping. All test fixtures use non-null values; the guard is never exercised. Without a test, removing the guard accidentally would create a `null::null` phantom cohort that could reach 15 members and generate spurious findings.

detectPuntaManagement: tipKw=null months (missing data, not winter zero) untested

domains/utility/src/finding/__tests__/finding.detectors.test.ts

The filter is `m.tipKw !== null && m.tipKw > 0`. The test covers `tipKw=0` (winter) but not `tipKw=null` (missing data). A scenario with 5 real punta months + 3 null-tipKw months should not fire (5 < `PUNTA_MIN_MONTHS=6`). No test covers this.

All-null-kwh bill list → ok([]) not tested

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts

When every bill has `kwh: null`, `prepared` is empty and the function returns `ok([])`. This is distinct from empty input (which is tested). Happens in practice for contracts with only inferred or payment-check stubs.

Impossible date (e.g. Feb 30) → InvalidBillPeriod not tested

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts

`parseYmdUtc` validates impossible dates via a UTC round-trip check (Feb 30 → Mar 2, fails). Returns null → `err(InvalidBillPeriod)`. Only the inverted-period case is tested; impossible dates are a real CFE data quality scenario and exercise a different code path.

detectPuntaManagement: `period` and `rpu` on findings never asserted

domains/utility/src/finding/__tests__/finding.detectors.test.ts

`period = latest.month` via `punta.reduce((a, b) => a.month > b.month ? a : b)`. This non-trivial reduce is never verified — neither `f.period` nor `f.rpu` is asserted in any punta_management test. If `reduce` were replaced with `[0]` no test would catch it.

Adjustment-span test asserts `source !== 'measured'` — doesn't verify it's actually 'adjustment'

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts:113

The anti-+79%-jump test checks `source !== 'measured'` instead of `source === 'adjustment'`. Would also pass if source were 'partial', which would indicate incorrect coverage counting. Strengthen to `toBe('adjustment')` on fully-covered months.

Bimonthly split test doesn't assert coverage.daysCovered

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts:74

`coverage.daysCovered` (expected 31 for January, 28 for February) is never asserted on the bimonthly split test. This field drives partial-coverage determination and if the day-iteration were off-by-one the test would catch the kwh error but lose the daysCovered diagnostic signal.

Partial+low-confidence combination untested

domains/utility/src/consumption-series/__tests__/consumption-series.decisions.test.ts

`source` and `confidence` are computed independently. A month that is both partially covered AND covered by estimated bills (`source='partial'`, `confidence='low'`) is never exercised. The YoY detector filters on both fields independently.

Five independent DB fetches in shell run sequentially — ~4× unnecessary latency

domains/utility/src/finding/finding.shells.ts:164

The five fetches (`fetchBillFactRowsForOrg`, `fetchOrgConsumptionSeries`, `fetchContractRegionsForOrg`, `fetchPerPeriodDemandForOrg`, `fetchContractCapacityRatesForOrg`) are all read-only and mutually independent. Sequential `await` serialises five round-trips. `Promise.all([...])` would cut FETCH-phase wall-time by ~4×. The pattern is already established in `listByOrg` (list + count in parallel).

log.info for InvalidBillPeriod should be log.warn — buries a data integrity signal

domains/utility/src/finding/finding.shells.ts:112

An `InvalidBillPeriod` reaching this branch means a bill with `period_end < period_start` survived the SQL pre-filter — a data integrity anomaly, not routine output. `log.info` buries it in standard-operation noise; `log.warn` would surface it in production dashboards.

toNumberOrNull duplicated in consumption-series.queries.ts and finding.queries.ts

domains/utility/src/consumption-series/consumption-series.queries.ts:41

Identical file-private `toNumberOrNull` one-liner in both query files. Extract once to `domains/utility/src/lib/` alongside `ulid.ts`. Avoids silent divergence if NaN/Infinity handling changes.

GDMTH hardcoded in two SQL queries — extract if a second horaria tariff is added

domains/utility/src/finding/finding.queries.ts:725

'GDMTH' appears independently in `fetchPerPeriodDemandForOrg` and `fetchContractCapacityRatesForOrg`. A shared constant or `IN (...)` list would make a future tariff extension a one-place change.

org_scoped_sites → org_contract_ids CTE duplicated across three queries

domains/utility/src/finding/finding.queries.ts:493

The org scoping block is copy-pasted into `fetchBillFactRowsForOrg`, `fetchPerPeriodDemandForOrg`, `fetchContractCapacityRatesForOrg`, and `fetchConsumptionBillInputsForOrg`. If the scoping logic changes (e.g. `deleted_at` check on `site_utility_contracts`), four locations need updating. No Drizzle-level sharing is possible with raw SQL, but documenting this as a known four-copy pattern reduces the risk of partial updates.