The scenario measures modal came up empty because both measures routes
gated on EXISTS against plan_recommendations — a retired join table with
no rows for new-approach plans (25.7M legacy rows, none for e.g.
portfolio 812's plans), so the query returned zero measures.
Read the denormalised model instead: drive from the latest plan per
property (default or scenario) and JOIN recommendation by the indexed
property_id, scoped to the plan via recommendation.plan_id. Portfolio 812
default now returns 5 measures (solar_pv 54 homes/£266k, …) where it
returned 0. Also removes the stale commented query block that referenced
the retired plan_recommendations / recommendation_materials tables.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The breakdown chart looped only over baseline bands (epcBands), which the
query returns only for bands that have properties. So a band reached ONLY
after the scenario — e.g. A/B when no property starts that high — had no
row, and its scenario bar never rendered (portfolio 812: scenario yields
7 A + 2 B, baseline has none). Iterate a canonical A–G+Unknown order over
baseline ∪ scenario instead, so post-scenario-only bands still appear.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The modelling writes a target-level post_sap (e.g. ~C) even for homes
already above the scenario target, so plans can carry cost while modelling
post_sap BELOW the effective baseline. That skewed three reporting
surfaces. Three TEMP (demo) guards, all keyed on the effective baseline
(ADR-0002); revert once the Model team fixes the sub-baseline plans:
1. EPC band chart: post-scenario SAP clamped to GREATEST(baseline, post)
so already-compliant properties aren't shown "improving" down a band
(portfolio 796: EPC B 4,244 -> 1,660 wrongly, now 4,479).
2. n_units_upgraded + cost: exclude plans whose post_sap is below the
effective baseline (not real upgrades) -- 796: 10,283 -> 9,765, -£1.28M.
3. total_sap_uplift / £-per-SAP: baseline is the effective SAP, not the
null-for-new-approach current_sap_points, and counts genuine gains
only -- uplift 0 -> 89,724, so £/SAP £0 -> £536.
Also fixes the no-plan branch to use the effective baseline instead of
the null current_sap_points (Unknown-band leakage).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
getPropertiesCount returns the portfolio's total property count for
pagination, but it dragged the whole read model through the COUNT: the
property_details_epc + property_baseline_performance + two epc_property
joins, plus a correlated default-plan LATERAL that ran once per property
(31k+ plan lookups for a large portfolio). None of those joins change a
COUNT (none multiply rows), so for an unfiltered load they were pure cost
— pushing the query to ~14.7s, past Vercel's 15s limit (intermittent
timeout on /api/properties).
Join only what an active filter references: no filters -> plain COUNT over
property; EPC/provenance filter -> add the epc-graph joins; Expected-EPC
filter -> add the plan LATERAL. Unfiltered count 14,667ms -> 93ms
(portfolio 796); provenance-filtered 156ms. getProperties is unchanged
(its LIMIT 1000 already bounds the LATERALs).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Theme 1 — EPC card provenance. New resolveProvenanceSignal (pure, unit-
tested) + resolveProvenance expose provenanceSignal ("estimated" /
"remodelled" / "none") and the lodged headline on property-meta. The
building-passport card shows a "Lodged EPC" badge on re-modelled
properties (even within-band), never on predicted ones; the existing
"estimated from nearby homes" banner already covers predicted.
Theme 2 — portfolio table. "Current EPC" + reporting now read the
effective baseline (effectiveSapSql/effectiveEpcBandSql); a dedicated
"Lodged EPC" column reads the source-gated lodged value (blank for
predicted); a "Predicted" pill marks estimated rows; a provenance filter
is added and the broken currentEpc/lodgedEpc filters (on null row
columns) point at the effective/lodged baselines. Expected EPC now uses
the default plan's post rating (expectedEpcRating, unit-tested) instead
of current + Σ rec SAP, which double-counted across plans (732385: A→C);
the same rec aggregate is scoped to the default plan, fixing the Cost
column's 2x double-count.
See ADR-0002. Pure logic covered by provenance.test.ts + expectedEpc.test.ts;
SQL/query/UI verified against live properties 732385 and 729529.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
ADR-0002: Effective performance is the canonical "current" for display and
reporting; lodged shown alongside (supersedes the mid-migration "reporting
stays lodged" note). CONTEXT.md gains EPC-provenance and provenance-signal
terms, and flags that the UI "Current EPC" surfaces show Effective.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Widen the lodged->effective switch from plan-only to all per-property
building-passport views (overview, assessment, plans). New-approach
properties' "current" SAP/EPC/CO2/primary-energy now come from the
effective (re-baselined) figure the modelling and plans were scored
against, so a property's current state agrees with the plan maths --
re-baselining (e.g. pre_sap10) otherwise made plans read as flat or as a
downgrade against the lodged gov-register EPC.
Done by flipping the shared object resolvers (resolvePropertyHeadline,
resolveDetailsEpcMeta, resolveNewConditionReport) to effective_*, which
removes the plan-only getPlanPropertyMeta/resolveEffectiveHeadline
indirection added in the previous commit. The set-based reporting
fragments (sapSql/epcBandSql/carbonSql/...) stay on lodged_* so portfolio
and reporting totals remain matched to the gov register.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Plans are scored against the effective baseline on
property_baseline_performance, which re-baselining can move away from the
lodged EPC shown everywhere else. Using lodged as a plan's "current"
misrepresented the uplift — e.g. lodged C/71 vs effective D/64 with a
post-retrofit C/69.6 read as a downgrade (71 → 69.6) instead of the real
D → C improvement.
Add resolveEffectiveHeadline + getPlanPropertyMeta and switch both plan
pages to it. Legacy properties and backfill gaps fall back to the
lodged/row values.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Local authority and constituency have no data source for new-approach
properties (not on epc_property; null on the property row). Replace them with
longitude/latitude from property_details_spatial (already fetched on the page),
keeping Tenure.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Tenure read the property-row tenure column, null for new-approach properties.
Backfill propertyMeta.tenure from epc_property.tenure (mostly text labels like
"Rented Social"; numeric SAP codes 1/2/3 mapped to the same labels).
Local authority / constituency have no source in the new pipeline (not on
epc_property, null on the property row, energy_assessments doesn't cover these
properties) — left as-is pending the pipeline writing them to the property row.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
On the overview page, Storeys read epc_property.number_of_storeys (null for some
properties) and Habitable rooms read the property-row number_of_rooms (null for
new-approach properties).
- Habitable rooms: backfill propertyMeta.numberOfRooms from
epc_property.habitable_rooms_count (always populated).
- Storeys: fall back to the main building part's floor-dimension count when
epc_property.number_of_storeys is null.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
epc_property stores property_type/built_form either as numeric RdSAP codes
(newer rows) or plain text ("House", "Detached" — most rows). The previous code
only mapped codes and fell through to dwelling_type for text rows, showing
"Semi-Detached house" instead of "House". Now: map numeric codes, pass text
through as-is, fall back to dwelling_type only when absent.
Also align AGE_BAND_YEARS with the backend ConstructionAgeBand enum: L is
2012–2022 (not "2012 onwards") and add M = 2023 onwards.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
For new-approach properties the property-row text columns (property_type,
built_form, year_built) are null, and the condition report had no floor height,
so the assessment page showed Unknown for several general features.
- property-meta: backfill property type / built form / age from epc_property
(+ epc_building_part construction age band), mapping RdSAP codes to labels
(property_type 2 -> Flat, built_form 3 -> End-Terrace, age band F -> 1976–1982),
falling back to the raw value for unmapped codes.
- condition report: source floor height from the main building part's
epc_floor_dimension.room_height_m.
- formatGeneralFeatures: use ?? instead of || so a real 0 (open fireplaces /
extensions) shows as 0 rather than "Unknown".
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
getProperties selected p.current_sap_points / p.current_epc_rating directly,
which are null for new-approach properties (headline columns unwritten on the
row). That made the Current EPC column blank and, since the Expected EPC column
is currentSapPoints + recommendation SAP, made Expected EPC show 0.
Use the sapSql / epcBandSql baseline helpers (lodged_sap_score / lodged_epc_band
for new-approach properties, property row for legacy), matching the reporting
page. Verified for properties that have a baseline row; the rest populate once
the baseline backfill completes.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Both were per-property LATERAL lookups against tables with no index on the
join column (property_targets.property_id, funding_package.plan_id), so each
seq-scanned 150k-180k rows once per property — ~3.4s of the load for a
291-property portfolio came from property_targets alone.
Neither value is actually used by the portfolio table: the "Expected EPC" column
derives from currentSapPoints + recommendation SAP, and fundingScheme is fetched
separately by the proposals table. Drop both reads (kept as NULL to preserve the
PropertyWithRelations shape) and trim the plan LATERAL to the columns used.
291-property portfolio: ~3.5s -> ~85ms.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
getPropertyMeta self-fetched process.env.URL + /api/property-meta. That URL can
resolve to a different deployment (e.g. production), so the building-passport
pages were reading property meta from OLD code — the backfilled headline SAP/EPC
never arrived and the current rating rendered blank, regardless of caching.
Extract resolvePropertyMeta() and call it directly from both the route and
getPropertyMeta, so the page runs identical in-process logic. JSON round-trip
through serializeBigInt preserves the previous shape (bigints/dates as strings).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
getPropertyMeta self-fetched /api/property-meta with revalidate:60 (despite a
comment saying it shouldn't cache). Vercel's Data Cache served a stale response
from before the headline backfill, so the current EPC rating / SAP rendered
blank on the plans and plan-detail pages even though the API now returns them.
Use cache:"no-store" so backfilled headline values propagate immediately.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
getRecommendations read the retired plan_recommendations join table, which is
empty for new-approach plans, so the plan detail page showed no recommendations.
Read the recommendation table directly. Because recommendation.plan_id is
unindexed (26M rows), key off the plan's property_id (indexed) and filter
plan_id in memory — fast for any plan, default or not.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The plans page computed the plan uplift as postSapPoints - currentSapPoints,
but current_sap_points is unwritten on the property row for new-approach
properties (null -> 0), so the uplift showed the full post score (e.g. +71.39)
and no current rating.
property-meta now backfills currentSapPoints / currentEpcRating from
property_baseline_performance (lodged_sap_score / lodged_epc_band) for
new-approach properties, fixing every propertyMeta consumer (plans page, hero
cards, building passport). e.g. 709634: current 60 (D), uplift now +11.39.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
/api/properties timed out (15s) on just 12 properties: joining the 26M-row
recommendation table on the unindexed plan_id forced a full-table hash, and the
multi-million-row plan/funding joins compounded it.
Rewrite getProperties/getPropertiesCount to read the denormalised model (no
plan_recommendations / recommendation_materials): one-row LATERAL lookups for
target/plan/funding and a recommendation aggregate keyed by the INDEXED
recommendation.property_id, dropping the GROUP BY entirely. ~1s -> instant for a
12-property portfolio.
Also fix the reporting page for new-approach properties (current_sap_points /
current_epc_rating on the property row are unwritten): source avg SAP and the
EPC band distribution from property_baseline_performance (lodged_sap_score /
lodged_epc_band) via new sapSql / epcBandSql helpers.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Per domain semantics: lodged_* on property_baseline_performance is the
gov-registry (actual) figure, effective_* is the modelling-adjusted baseline.
Use the lodged columns for the "current" carbon/primary-energy display and the
individual cost columns (summed) for bills. Bills return NULL when no baseline
row exists, so they're excluded from AVG/SUM rather than shown as £0.
(property_baseline_performance is still unwritten DB-wide; this makes the reads
correct for when the pipeline populates it.)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
For new-approach properties the modelling pipeline currently writes only
epc_property + epc_energy_element + plan/recommendation — property_baseline_
performance, epc_property_energy_performance metrics and the property-row
headline columns are not yet populated.
- layout: guard propertyMeta.detailsEpc?.estimated (detailsEpc can be absent)
- resolveConditionReport: degrade to an all-Unknown report instead of throwing
when the EPC graph/legacy row is missing (no more 500s)
- getProperties: join recommendation via recommendation.plan_id instead of the
retired plan_recommendations table, so Plan Cost / recommendation SAP populate
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The new modelling pipeline no longer writes property_details_epc. Route all
EPC reads through src/lib/services/epcSources.ts, which branches on the cutoff
(property.updated_at >= 2026-06-01): legacy rows keep reading
property_details_epc; new-approach rows read epc_property (+ children),
property_baseline_performance, and plan/recommendation.
- per-property: resolveConditionReport (building passport),
resolveDetailsEpcMeta (/api/property-meta)
- aggregates: shared carbon/bills/energy/estimated/expired/floor-area/
lodgement/mainfuel SQL fragments + newApproachJoins, wired into the
portfolio list + filters, reporting metrics, and both scenario metrics routes
estimated = predicted-only EPC. Gracefully omits gap fields for new
properties (mainfuel, energyTariff, floorHeight, installed-measures bill
adjustment, SAP-05 likely-downgrade).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Adds a `source` discriminator to `epc_property` so a property can hold a
lodged and a predicted EPC at the same time, per the EPC Prediction
production-wiring branch in the Model repo (docs/MIGRATION_NOTE_predicted_epc_source.md).
- `source` text NOT NULL default 'lodged' — backfills every existing row as
a real lodged EPC.
- Unique index becomes (property_id, portfolio_id, source) so lodged +
predicted rows can coexist for the same property/portfolio.
- New (property_id, source) index — lodged/predicted reads filter on both.
Allowed values: 'lodged' | 'predicted'.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
