# The EPC reporting cards count certificates, not predictions

## Status

accepted

## Context

The portfolio reporting page shows two EPC-quality cards: **Homes Without an EPC**
and **Expired EPCs**. Both were derived from `estimatedSql`, which asked *"is this
property's EPC picture a gap-fill?"*:

```sql
-- "Homes Without an EPC"
SUM(CASE WHEN estimatedSql = true THEN 1 ELSE 0 END)
-- "Expired EPCs"
SUM(CASE WHEN isExpiredSql = true AND estimatedSql = false THEN 1 ELSE 0 END)
```

That conflates two genuinely different questions:

- **Provenance** — was this picture *modelled*, or read off a real certificate?
- **Coverage** — does a certificate *exist* for this dwelling at all?

They used to give the same answer, because `epc_property.source` had two values and
a gap-fill implied no certificate. That stopped being true.

The gov EPC API only serves certificates registered since **1 January 2012**. A
dwelling whose only certificate predates that looks EPC-less to ingestion, so its
picture gets gap-filled — even though a real, if stale, certificate exists for it in
the historic register. Model ADR-0054 introduced a third `source` value, `expired`,
for exactly this case: a prediction *conditioned on the dwelling's own expired
certificate*. `predicted` and `expired` share one slot (`PREDICTED_SLOT_SOURCES`).

Measured on portfolio 824 (5,276 homes): **403** homes were gap-filled, and **375**
of them have a real pre-2012 certificate in the historic backup. So the card claimed
403 homes had no EPC when 375 of them did — they just had an out-of-date one, which
is a different and far more actionable problem. It is also the *wrong* problem to
solve: you do not commission a new EPC survey for a home you believe has never been
certified in the same way you chase a lapsed one.

Two further defects fell out of the same conflation:

- `epc_property.source` is plain `text` with **no enum and no CHECK constraint**, and
  every query matched `source = 'predicted'` literally. An `expired` row matches
  neither the lodged nor the predicted alias, so the home would have read as though it
  held a *valid current certificate* — silently absent from **both** cards, with no
  Estimated badge. Nothing in the database would have caught this.
- `isExpiredSql` read `epl.registration_date` alone, but 714 of 824's 4,873 lodged
  certificates carry only an `inspection_date`. All 714 were treated as current; 170
  of them are in fact over ten years old. (`lodgementDateSql` already coalesced the
  two — the expiry predicate simply didn't.)

## Decision

**The two cards partition on coverage, not provenance.** A prediction is not a
certificate; an expired certificate is still a certificate.

- `withoutEpcSql` — *no lodgement record and no historical record*:
  `epl.id IS NULL AND epx.id IS NULL`. A blind `predicted` row is not evidence of an
  EPC, so it is not consulted. A property with no `epc_property` rows at all also
  lands here, correctly.
- `isExpiredSql` — *we found a certificate, of either kind, and it is out of date*:
  an `expired` row (>10 years old by construction, since its conditioning certificate
  predates 2012), **or** a lodged certificate older than ten years, dating it by
  `COALESCE(registration_date, inspection_date)`.
- `estimatedSql` keeps its own, separate meaning — *was this modelled?* — and now
  spans the whole predicted slot, so an `expired` home still carries the **Estimated**
  provenance badge. It is estimated **and** has an EPC. Both are true.

The `AND estimated = false` guard is removed from the Expired card and must stay
removed: an `expired` home *is* estimated, so the guard would exclude precisely the
homes the card exists to count. The rule it was really enforcing — *a gap-fill has no
certificate to expire* — now lives inside `isExpiredSql`, where a caller cannot forget
it.

**Every query addresses the predicted slot (`epp` OR `epx`), never one member.**

## Consequences

Legacy properties (`updated_at` before the 2026-06-01 cutoff) are **unaffected** —
they have no `epc_property` rows and keep reading the flat `property_details_epc`
flags. Portfolio 632 reports 8 / 1,662 before and after.

For new-approach properties the numbers move, in two steps. The `inspection_date`
fallback lands immediately and is a straight bug fix: 824's Expired count goes
**913 → 1,083**. Then, once Model starts writing `expired` rows, the cards settle at
**403 → 25** without an EPC and **1,083 → 1,461** expired. The portfolio has not
changed; we were miscounting it.

This repo is safe to deploy **before** Model starts writing `expired` rows — the
predicates simply find none. The reverse is not true: had Model shipped first, every
`expired` home would have gone quietly missing from both cards.

Two things we accept:

- **The three-way reality is still squashed into booleans per card.** We do not
  surface "has an expired certificate" and "has nothing at all" as distinct states
  anywhere except by the two cards being disjoint. If a third state ever needs its own
  treatment, this is where to add it.
- **Nothing enforces the slot at the database level.** `source` remains `text`. A
  future query that matches `source = 'predicted'` alone would reintroduce the bug in
  silence. `src/lib/services/epcSlot.test.ts` pins the invariant structurally; a CHECK
  constraint or a Postgres enum would be stronger, and is worth doing when the schema
  is next touched.
