Breakdown.md

Unit-Scale Mismatch — Remediation Plan

Date: 2026-04-26 Owner: Raven team Related: docs/site-057-distribution-loss-audit-2026-04-26.md

Problem recap

Every row in meter_readings whose meter is of type bulk or energy_center was ingested with energy_heat_cum_kwh values that are literally one thousand times too small — the READy CSV supplied MWh but the historical parser stored the number verbatim. End-meter rows are unaffected.

Quantified on the local dev DB (mirrors production feed):

Meter type	Rows affected	Meters affected	Median (Δcum/hr) ÷ (avg power_kw)
energy_center	8,559	12	0.00141 ← 1000× too small
bulk	57,000	79	0.00105 ← 1000× too small
end_meter (reference)	—	4,246	0.56 (normal)

All affected rows were ingested between 2026-03-28 17:40 and 2026-04-22 08:10.

Worked example — one real row showing the 1000× mismatch

Meter 85185497 (Gothmog, a bulk meter on site 057). Two consecutive readings from meter_readings:

reading_timestamp	energy_heat_cum_kwh	power_kw
2026-03-19 00:01	4093.500	246.000
2026-03-19 01:01	4093.700	182.000

The values imply Δcum = 0.200 over 1.00 hours. Compare that against the same row's instantaneous power_kw = ~214 kW (avg of the two):

• If the value were in kWh (as the column label claims): Δcum/hr = 0.200 kWh/hr. That is 1000× smaller than the measured 214 kW flow. Physically impossible — the meter would need to be reporting 214 kW of instantaneous load while accumulating essentially no energy over the same hour.
• If the value is actually in MWh (the real situation): Δcum = 0.200 MWh = 200 kWh, giving Δcum/hr = 200 kWh/hr. That matches the 214 kW instantaneous power within ~7% — exactly what we'd expect from an hourly-aggregated reading with a small demand variation inside the hour.

The ratio (Δcum/hr) ÷ power_kw ≈ 0.0009 for this row is the fingerprint of the bug. It repeats across every bulk and energy-centre meter:

Meter type	Rows	Median ratio	Expected if kWh	Expected if MWh-stored-as-kWh
end_meter	4,246 meters	0.56	≈ 1.0 (matches)	—
production	12 meters	0.98	≈ 1.0 (matches)	—
bulk	79 meters	0.00105	—	≈ 0.001 (matches)
energy_center	12 meters	0.00141	—	≈ 0.001 (matches)

There is no band between 0.01 and 0.5 — the two populations are cleanly separated by three orders of magnitude, which is why we can use the ratio as a machine-decidable filter for the backfill.

Where the bug lives in the code

Two early-return branches inside apps/backend/src/meter-readers/common/parsing-utils.ts trust the incoming unit label (or its absence) without cross-checking the magnitude of the value. Both branches return { ok: true, value } unchanged.

export function convertToCanonicalUnit(
  value: number,
  rawUnit: string | null | undefined,
  canonicalUnit: 'kWh' | 'm³' | '°C',
): UnitConversionResult {
  if (!Number.isFinite(value)) {
    return { ok: false, reason: `Non-finite numeric value (${value})` };
  }

  const normalized = normalizeUnit(rawUnit);

  if (!normalized) {
    return { ok: true, value };      // Failure 1 — missing/empty Unit column
  }

  if (normalized === canonicalUnit) {
    return { ok: true, value };      // Failure 2 — label says "kWh" but the number is actually MWh
  }

Failure 1 — missing / empty Unit column (line 118-120)

normalizeUnit(undefined) returns undefined, the if (!normalized) branch fires, and the raw number is passed through. The function comment (lines 97-99) even documents this as intentional "to preserve the ingestion behaviour for historical READy files that did not emit a Unit column". That assumption is exactly what bites us on files where the Unit column is blank but the meter's firmware is emitting MWh.

Failure 2 — label says kWh but the meter is lying (line 122-124)

After normalizeUnit() maps "kwh", "KWH", "Kwh" all to canonical "kWh", the check normalized === canonicalUnit is true and we return { ok: true, value } without scaling. This is the case that produces ~74 of the 90 broken post-fix bulk / energy-center meters we observed on dev.

The caller doesn't compensate

apps/backend/src/meter-readers/readers/ready/ready.service.ts at lines 297-325 reads the adjacent Unit column, calls convertToCanonicalUnit, and stores whatever it returns:

          // Pick up the adjacent `Unit` column (standard READy layout:
          // every canonical metric is immediately followed by its unit).
          // Missing / empty unit is treated as "assume canonical" by
          // `convertToCanonicalUnit` to preserve ingestion of historical
          // files that predate the Unit column.
          const nextColName = header[colIdx + 1]?.trim().toLowerCase();
          const rawUnit = nextColName === 'unit' ? row[colIdx + 1]?.trim() : undefined;
          const canonicalUnit = CANONICAL_METRIC_UNITS[matchedMetric.key];

          const conversion = convertToCanonicalUnit(numValue, rawUnit, canonicalUnit);

          if (conversion.ok === false) {
            const reason = conversion.reason;
            errorCollector.add(
              rowNumber,
              ImportErrorType.INVALID_NUMBER,
              `Unit mismatch in "${header[colIdx]}" (got "${rawUnit ?? 'none'}", need ${canonicalUnit}): ${reason}`,
              {
                columnName: header[colIdx],
                rawValue: rawValue?.substring(0, 100),
                meterSerial,
              },
            );
            parseWarnings.push(
              `[${filename}] row ${rowNumber} meter ${meterSerial}: ${reason}`,
            );
            unitConversionSkipped++;
            continue;
          }

          if (conversion.convertedFrom) {
            unitConversionApplied++;
          }
          metrics[matchedMetric.key] = conversion.value;
        } else {

The caller has the adjacent data needed to catch the bug — it is already iterating every other column of the same row, including power_kw as an aux metric — but it never cross-references the magnitude of the cumulative value against that signal.

What the code does today vs. what it should do

flowchart LR
  csv["CSV row<br/>cum=5.2, unit=kWh, power_kw=4200"]
  parser["convertToCanonicalUnit"]
  current["Current: return 5.2 as-is<br/>(1000x too small)"]
  fixed["Fixed: ratio 5.2/4200 = 0.0012<br/>below 0.01 threshold<br/>scale -> 5200 kWh"]

  csv --> parser
  parser -->|"label matches<br/>early-return line 123"| current
  parser -.->|"proposed: magnitude check<br/>against power_kw"| fixed

Loading

In one sentence: the bug is two early-returns that assume a matching (or absent) unit label means the value is correct, when in practice the CSV can silently lie about the unit while keeping a valid-looking label. Track 0 below adds the missing second gate.

What is already fixed — and what is NOT

Commit 55875aa feat(meter-readers): canonical unit conversion in READy ingestion (merged 2026-04-25 18:38) correctly:

• Detects the adjacent Unit column for every canonical metric (apps/backend/src/meter-readers/readers/ready/ready.service.ts lines 297-325).
• Converts MWh → kWh by multiplying by 1,000 via convertToCanonicalUnit (apps/backend/src/meter-readers/common/parsing-utils.ts).
• Logs unit conversion: applied=N, skipped=M per file.
• Is covered by parsing-utils.spec.ts — the MWh→kWh test passes deterministically.

But — verified 2026-04-26 against the dev database: the fix is only partial

Of the 1,030 post-fix rows ingested on dev between 2026-04-25 18:38 and 2026-04-26, only 17% of bulk / energy-center meters are actually being stored in kWh.

Ratio band	Meters	What this means
0.5 .. 2.0 (correct kWh)	15	CSV's Unit column explicitly said MWh; parser multiplied by 1000
< 0.01 (MWh-as-kWh, still broken)	74	CSV's Unit column was blank or said kWh, but the value was actually MWh. Parser trusted the label, stored verbatim.
> 2.0 (standby / intermittent)	1	Normal data-sparsity noise

This is the "label lies" failure mode explicitly called out in the top-of-file comment in parsing-utils.ts:

"Incompatible kinds … return ok: false and MUST be surfaced by the caller; silently accepting them is what produced the Site-001 MWh-as-kWh corruption."

convertToCanonicalUnit only surfaces an error when the unit kinds are incompatible (e.g., kW supplied for a cumulative kWh field). It cannot detect a meter whose CSV says kWh but whose values are magnitude-wrong — there's no syntactic signal to catch. It needs a semantic sanity check against power_kw to close this loophole.

Breakdown by source file prefix (dev, post-fix rows):

• BROKEN (74 meters): banddexport (36), TestData_Site1 (9), TestData_Site2D (5), TestData_Site4A (4), TestData_Site4B (3), TestData_Site16 (3), and 10 other sites.
• OK (15 meters): TestData_Site3 (5), TestData_Site55 (4), TestData_Site7 (3), TestData_Site10 (2), TestData_Site2A (1).

Note TestData_Site2A appears in both bands — this is per-meter, not per-site. The CSV file generator seems to emit the correct MWh unit label for some meters and an incorrect kWh (or blank) label for others even within the same file drop.

Gaps the fix leaves behind

1. No audit trail on converted rows. After ingestion we keep metrics.energy_heat_cum_kwh but discard the convertedFrom flag. We can't look at a row in the DB and tell whether its value was scaled from MWh or already kWh.

2. Pre-fix rows are still wrong. On dev: 162,050 bulk + 35,681 energy_center = 197,731 rows (~3× what we initially measured on local). On local: 57,000 + 8,559 = 65,559.

3. Post-fix rows for ~82% of meters are ALSO still wrong. The parser only fires when the CSV's Unit column literally says MWh. For meters whose files lie about units (or omit them) the magnitude bug still ships to the DB.

4. No runtime sanity guard. If the parser regresses further, the daily MV silently produces 0% losses again. We caught this only because a human noticed the KPIs looked off.

Remediation — four tracks, in the order they should ship

Track 0 — Stop the bleeding (URGENT, new track added 2026-04-26)

The parser must stop trusting a kWh label on a value whose magnitude is obviously MWh. Add a magnitude sanity check at parse time, running alongside the existing unit-label check:

// Pseudocode - add to ready.service.ts parseMetrics loop
const hourlyDelta = computeHourlyDelta(meterSerial, numValue, rowTimestamp, previousRow);
const power    = numericValueOfAuxMetric('power_kw') ?? rowPowerKw;
if (power > 0.1 && hourlyDelta !== null) {
  const ratio = hourlyDelta / power;
  if (ratio > 0 && ratio < 0.01) {
    // Value is clearly in MWh even though the unit label disagrees.
    metrics.energy_heat_cum_kwh = numValue * 1000;
    unitConversionApplied++;
    row.additional_data.unitConversion = { metric: 'energy_heat_cum_kwh', from: 'MWh (magnitude)', factor: 1000, reason: 'label_disagrees' };
    continue;
  }
}

Practical complications to solve in the real PR (not here):

• The parser processes rows in isolation; to get previousRow we need a running per-meter cache within a single parse pass, or a lookup into the existing DB for the last persisted row.
• The check should be skipped for the very first row of a meter (no delta available).
• Threshold constants (0.1 kW, 0.01) should live in a named config object so ops can tune them without redeploying.

Ship this before Track B, otherwise the backfill gets re-corrupted on the very next ingest.

Track A — Preserve an audit trail (small code change)

1. In ready.service.ts, when conversion.convertedFrom is set, write additional_data.unitConversion = { metric: 'energy_heat_cum_kwh', from: 'MWh', factor: 1000 } onto the row persisted.
2. Update parsing-utils.spec.ts to assert this field.
3. Ship to main.

This costs ~15 LOC and unlocks deterministic queries like WHERE additional_data->'unitConversion' IS NULL to identify pre-fix rows unambiguously — useful for Track B.

Track B — Backfill the broken rows (~197,731 on dev / ~65,559 on local)

The backfill cannot rely on meter_type ∈ {bulk, energy_center} alone anymore — we now know some meters of those types are stored correctly (15 of the 90 sampled post-fix). Instead, use the per-meter magnitude ratio as the filter: scale only meters whose median (Δcum/hr) ÷ power_kw falls below 0.01.

Two implementation options; we recommend Option B.1 (SQL migration) because:

• The SFTP archive isn't guaranteed to be complete (files are archived after READy processing).
• power_kw gives us an independent, per-meter sanity signal that cleanly partitions the affected meters from the already-correct ones.
• Running the same check post-fix is our acceptance test.

Option B.1 — TypeORM migration (recommended)

The migration works in two passes: (1) compute the per-meter median ratio, (2) scale rows only for meters whose ratio says they're in MWh. The sanity check at the end is the acceptance test.

-- apps/backend/src/database/migrations/177xxxxxxxxxx-BackfillMwhLabelLieRows.ts

BEGIN;

-- 1. Identify every meter whose median hourly delta vs power_kw says "stored in MWh"
CREATE TEMP TABLE suspect_meters AS
WITH hourly AS (
  SELECT meter_serial, reading_timestamp, power_kw, energy_heat_cum_kwh,
         LAG(energy_heat_cum_kwh) OVER w AS prev_cum,
         LAG(reading_timestamp)   OVER w AS prev_ts
  FROM meter_readings
  WINDOW w AS (PARTITION BY meter_serial ORDER BY reading_timestamp)
),
deltas AS (
  SELECT meter_serial,
         (energy_heat_cum_kwh - prev_cum) /
           NULLIF(EXTRACT(EPOCH FROM (reading_timestamp - prev_ts))/3600 * power_kw, 0) AS ratio
  FROM hourly
  WHERE prev_cum IS NOT NULL
    AND reading_timestamp - prev_ts < interval '3 hours'
    AND power_kw > 0.1
)
SELECT meter_serial
FROM deltas
GROUP BY meter_serial
HAVING PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ratio) < 0.01
   AND COUNT(*) >= 10;  -- require a minimum sample so standby meters don't get falsely scaled

-- 2. Scale the cumulative field by 1000 for every row of a suspect meter
UPDATE meter_readings mr
SET energy_heat_cum_kwh = mr.energy_heat_cum_kwh * 1000,
    additional_data = COALESCE(mr.additional_data, '{}'::jsonb)
                    || jsonb_build_object(
                         'unitConversion',
                         jsonb_build_object(
                           'metric','energy_heat_cum_kwh',
                           'from','MWh (magnitude)',
                           'factor',1000,
                           'backfilledAt', to_jsonb(NOW()),
                           'source','BackfillMwhLabelLieRows'
                         )
                       )
WHERE mr.meter_serial IN (SELECT meter_serial FROM suspect_meters)
  AND (mr.additional_data->'unitConversion') IS NULL;  -- idempotent

-- 3. Refresh the continuous aggregate so the fix propagates immediately
CALL refresh_continuous_aggregate('meter_readings_daily', NULL, NULL);

-- 4. Verification: after the update, no bulk / energy_center meter should still be suspect
DO $$
DECLARE remaining INT;
BEGIN
  SELECT COUNT(DISTINCT s.meter_serial) INTO remaining
  FROM suspect_meters s
  JOIN meter_readings mr ON mr.meter_serial = s.meter_serial
  WHERE mr.energy_heat_cum_kwh > 0 AND mr.power_kw > 0.1
    AND (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (
           ORDER BY (mr.energy_heat_cum_kwh - LAG(mr.energy_heat_cum_kwh) OVER w)
                  / NULLIF(EXTRACT(EPOCH FROM (mr.reading_timestamp - LAG(mr.reading_timestamp) OVER w))/3600 * mr.power_kw, 0)
         ) FROM meter_readings WHERE meter_serial = s.meter_serial
           WINDOW w AS (ORDER BY reading_timestamp)) < 0.01;

  IF remaining > 0 THEN
    RAISE EXCEPTION 'Backfill sanity check failed: % meter(s) still show MWh-magnitude pattern', remaining;
  END IF;
END $$;

COMMIT;

Rollback: the unitConversion marker lets the migration's down() divide by 1000 only for rows it touched, making the operation safe to revert.

Option B.2 — Re-ingest (fallback)

If Track A adds the audit trail in time and stakeholders prefer it, we can:

1. List SFTP archive files covering 2026-03-28 → 2026-04-22.
2. Delete the corresponding meter_readings rows.
3. Re-run ReadyService.processFile() in a controlled batch.
4. Refresh the MV.

Downside: slower, depends on archive completeness. Use only as fallback.

Track C — Runtime sanity guard

Add a nightly check (or bolt onto the MV refresh) that per meter:

// Pseudocode for a new method in MeterReadingAggregationService
async detectUnitRegression(meterSerial: string): Promise<UnitSuspicion | null> {
  // Median ratio of (Δcum/hr) to (avg power_kw) over the last 7 days
  // If outside [0.5, 2.0] AND the meter has > 50 readings → flag
}

Flagged meters bubble up to the new Data Health panel with unit_suspect=true and their group badges render — instead of 0.0% Good. Same mechanism handles future regressions in other fields (volume, cooling energy).

Suggested sequencing

Day	Action	Outcome
T+0	Ship Track 0 (magnitude sanity check in parser) + Track A (audit trail)	All new ingests correctly tagged; no new MWh-as-kWh rows
T+0.5	Author Track B migration, test against local dev DB, verify sanity check passes	Ready for review
T+1	Merge + run migration in dev, re-check site 057 numbers	C1, D4, D5, Khaza-dum move from 0.0% to real values
T+2	Run migration in prod inside a maintenance window	Portfolio numbers correct
T+3 onwards	Ship Track C + Data Health panel	Regressions become visible, not silent

Track 0 is gating — without it, we'd run Track B and then watch dev's nightly ingest re-corrupt 82% of the bulk meters we just fixed.

Acceptance criteria

A successful remediation means, on site 057 for the same Apr 19–25 window:

• D3 still returns ≈ 2.18% (unchanged — it was already correct).
• D6 still returns ≈ 13.71% (unchanged — already correct).
• C1 moves from 0.0% to approximately 7%.
• D4, D5, Khaza-dum move from 0.0% to plausible low-double-digit percentages.
• D1 / D2 / Mirkwood / 5th C1 parent still show — (not 0%) because their parent meter has no readings at all — the separate "missing data" track, not this one.