# Route Export Schema v2 — Data-Availability Audit

**Context:** the corridor-episode AI pipeline wants a richer, pre-computed JSON export
(Schema v2) so the AI only judges, never re-derives. This audits what data we
**actually hold** against the request, per item, with the fulfilment path. Verified
against the live `catalogue` DB, 2026-08-31.

**Bottom line:** ~70% is achievable from data we already have (much of it currently
unused). A handful of fields are genuinely missing and need a data/engine change.

---

## Status — read this before the audit below (2026-09-14)

Everything after this section is the **original 2026-08-31 audit**, kept as the record of
what the catalogue can and cannot supply. Two things about it have changed since, and
reading it without them is how the wrong work gets planned.

**1. The builder exists. Nothing calls it.**
[`src/AreaGuide/Support/RouteExportBuilder.php`](/src/AreaGuide/Support/RouteExportBuilder.php)
implements Steps 1 and 2 of the plan below, but `build()` has **no caller anywhere** — no
route, no job, no controller (its unused constructor injection has since been removed, so
the only reference outside the class is its own unit test). **The AI never sees a Schema v2
export.** The live pipeline ([`EpisodeDebate`](/src/AreaGuide/Support/EpisodeDebate.php)) is a
map-reduce: one analyze-property engine brief per stop, plus a compact corridor summary that
carries labels and driving order and no figures at all. `flags`, `basis` and `txn_by_year`
reach no model. The showrunner prompt now **declares those real inputs** (`<CORRIDOR>`,
`<STOP_BRIEFS>`, optional `<WEB_CONTEXT>` / `<FIELD_NOTES>`, `<EPISODE_SPEC>`); the
`<ROUTE_EXPORT>{{schema-v2 JSON}}` block it used to declare is gone, and so is the
contradiction where `reduceSystem` had to tell the model to ignore its own prompt. Step 0's
validation was re-expressed over what the model actually receives — the NO DATA / STALE-THIN /
MISSING CRITICAL / BASIS checks survive, the cross-field checks became cross-brief checks, and
a new TRACEABILITY rule drops any figure no brief states.

So: the builder is the Schema v2 **deliverable**, not the thing in production. Treat this
audit as the spec for wiring it in, not as a description of what the AI is fed today.

**2. The "never an estimate" rule now holds on the LIVE path too.**
The audit wrote the null policy for the *export*. The live pipeline was breaking it
elsewhere: `stopAnalysis()` fed the engine a synthetic unit (price 500 000, size 900,
bedrooms guessed 2 or 3) whenever the catalogue record was thin, and an invented building's
analysis reached the brief indistinguishable from a real one. It no longer does — a stop
with no transacted `price_median` / `psf_median` is **not analysed at all**; its brief is an
explicit INSUFFICIENT DATA instruction naming what is missing, bedrooms are passed as `null`,
and the Script tab badges the stop. See the module
[readMe](readMe.md) for the reader-facing version.

### What the builder now emits that this audit listed as missing

| Audit item | Now |
|---|---|
| Finding 1 — the `(int)` cast on the listing arrays | **Was never in committed code.** Presenter and builder both `count()`; the only commit touching them already did. Ignore it as open work. |
| #3 maintenance fee → "null + flag" | `maintenance_fee_psf` is null **with `MISSING_MAINTENANCE_FEE`**; `net_yield_pct` stays null because it cannot be computed without it. |
| #1 per-year counts → "count: null + flag" | `txn_by_year[].count` is null **with `NO_YEARLY_TXN_COUNTS`**. |
| #4 bonus — median asking psf + unit sizes from the listings | New per-project `asking` block: `median_asking_psf`, `median_unit_sqft`, `listings`, `basis`. |
| #12 `meta` — per-project `data_scraped_at` | Emitted per project. |
| #13 `implied_avg_sqft` from floor plans | Prefers the floor plans' mean sqft and reports `derived.implied_avg_sqft_basis` (`floor_plans` \| `price_over_psf`); the price ÷ psf fallback also raises `IMPLIED_SQFT_FROM_PRICE`. |
| #10 `bms_500m` | An amenity whose distance is missing or unparseable is **no longer counted** — "within 500 m" is a claim only a known distance supports. |
| — (not in the audit) | The floor-plan lookup runs **once per connection** the stop's projects live on, so a project this platform created itself has its plans read from the site database rather than the master. |

Flags are named constants on the class (`FLAG_*`, `SQFT_BASIS_*`), not literals.

### Still null, still open

`net_yield_pct` (needs a cleansed maintenance fee), per-year transaction **counts**, median
asking **price** from the listings, station `line` / `status`, `str_house_rule`,
`units_count` / `share_pct`. These are the same data gaps the audit named — nothing about
them has changed.

### Fix before wiring `build()` in

Its floor-plan result map is keyed by the **raw integer project id** while being filled from
two connections, so a master project and a platform-owned project that share an integer id
would overwrite each other's plans. Harmless while the class is dead code; it needs a
connection-qualified key first.

---

## Three findings up front *(2026-08-31, historical)*

1. **`asking_sale_listings` / `asking_rental_listings` were never capped at 1 — it was a
   presenter bug on our side.** They are full JSON **arrays** of live listings (The Scott
   Garden: 37 sale + 9 rent), each with `sqft / psf_rm / price_rm / bedrooms / furnished /
   agent`. The export code cast the array to `(int)` → `1`. Fixing this yields the real
   counts **and** per-listing size/asking distributions for free.
   › **Not open work.** No such cast exists in any committed code — the presenter and the
   builder both `count()`, and the only commit that ever touched those files already did.
   The finding describes a pre-commit state; the counts, and the size/asking distributions it
   promised, are in the builder today.

2. **Rich data sits in the DB but was never fed to the AI:**
   - `catalog_floor_plans` — per-layout `sqft / bedrooms / rent_median / asking_psf`
     (8,030 MY projects have rows). → `unit_mix`, real per-layout rent.
   - `rental_yield_detail` (JSON) — **transacted** `avg_monthly_rent` by size band +
     `transactions` count + `yield`. → real `median_monthly_rent` + `rental_txn`.
   - `quarterly_sale_psf` (JSON) — quarterly `{25th, median, 75th}` psf time series. →
     psf-by-year, YoY, staleness.

3. **Genuinely missing (no source in the DB):** a station reference table
   (`market_catalysts` is EMPTY), `title_type`, `str_house_rule`, per-layout `units_count`,
   a reliable `maintenance_fee`.

---

## Item-by-item *(2026-08-31, historical — see Status above for what has since been built)*

### P0

| # | Requested | Status | Fulfilment |
|---|---|---|---|
| 1 | `last_txn_date` + `txn_by_year[]` | **Partial** | `quarterly_sale_psf` → aggregate to `psf_by_year` (median) + last quarter → `stale_months`. `transaction_period` (`"Feb 2022 and May 2025"`) + `total_transactions` give the total & range. **No per-year transaction COUNT** (only the grand total) → per-year `count: null` + flag. |
| 2 | `total_units` | **Weak** | `non_landed_units` populated for some (Ren 1260, Oaka 350) but `0`/missing for many; `floor_plans.units_count` is **empty**. → give it only when `non_landed_units > 0`, else `null` + `MISSING_UNITS`. `liquidity` only when units present. |
| 3 | `maintenance_fee_psf` | **Missing** | `management_fee` exists but is mostly `"0"`/garbage. → `null` + flag; **`net_yield` not computable**. |
| 4 | asking listings real count | **✅ one-line fix** | Count the array: `active_sale_listings = count(asking_sale_listings)` (=37). Bonus: median asking price/psf + unit sizes from the listings. |
| 5 | `median_monthly_rent` + `rental_txn_12m` | **✅ mostly** | `rental_yield_detail` → transacted rent + txn count (`basis: transacted`); `floor_plans.rent_median` per layout; `asking_rental_listings` → asking rents (`basis: asking`). `rental_txn_12m` ≈ `rental_yield_detail.transactions` (cumulative, not strictly 12 m — noted). |
| 6 | `upcoming_supply[]` with `units` | **Partial** | `sale_status = 'New Launch'` separable; `units = non_landed_units` where present, else flagged. |
| 7 | `title_type` + `str_house_rule` | **No columns** | `title_type` proxied from `property_type` (`Serviced Apartment` → `commercial_serviced`, else `residential`). `str_house_rule` has **no source** → always `unknown`. |

### P1

| # | Requested | Status | Fulfilment |
|---|---|---|---|
| 8 | `nearest_stations[]` | **Partial** | From the project's `amenities.train` (name + straight-line distance; code like `KD03` embedded in name). **No line / operating-vs-planned status** (needs a station reference table we don't have). → name + `distance_m`; `line`/`status`: `null`. |
| 9 | `airbnb_rollup` per building | **✅ computable** | Airbnb points within 300 m of each project → median ADR/occ, `by_bedroom`, `top_quartile_occ`. Dedup: only 4/242 coords duplicate in OKR (small, but done anyway). |
| 10 | `bms_500m` per project | **✅ computable** | Bank / McDonald's / Starbucks within 500 m from the project's own amenities JSON. |
| 11 | `unit_mix[]` | **Partial** | `floor_plans` give `layout / sqft / bedrooms / rent` (8,030 projects), but `units_count` is **empty** → **no `share_pct`**. Give sizes + rent, omit share. |

### P2

- **12 `meta`** — ✅ `data_as_of` (export date), per-project `data_scraped_at`, source list.
- **13 `derived`** — Partial: `implied_avg_sqft` (floor plans) ✅, `psf_yoy_pct` / `stale_months` (quarterly) ✅; `liquidity_pct_12m` (needs units, often missing); `monthly_rent_from_yield` — **drop it, we now have real rent**.
- **14 `flags[]`** — ✅ all cheap: `STALE_DATA` (last quarter), `ZERO_TXN` (`total_transactions = 0`), `MISSING_UNITS`, `ASKING_ONLY` (asking but no transacted), `LOW_LIQUIDITY` (when computable).

### The three landing rules

- **null policy** — ✅ adopted: not found → `null` + flag, never an estimate.
- **basis tag** — ✅ and important: source disambiguates it — `quarterly_sale_psf` /
  `total_transactions` = `transacted`; `asking_*_listings` / `floor_plans.asking_psf` =
  `asking`. Directly fixes the asking-vs-transacted risk.
- **dedup** — ✅ cheap, done.

---

## What the export layer CAN'T produce — needs data/engine work

1. **Per-year transaction COUNTS** — the engine's transaction detail for MY is thin (~592
   rows); `quarterly_sale_psf` carries psf but no counts.
2. **`total_units` / per-layout `units_count`** — backfill needed.
3. **A station reference table** (name / line / status / coordinates) — the biggest gap;
   blocks `nearest_stations` line+status and any real transit analysis.
4. **`maintenance_fee`** cleansing (currently `"0"`).
5. **`str_house_rule`** — no source at all.

---

## Plan

- **Step 1 (immediate, low risk):** ~~fix the asking-listings cast bug~~ (never in committed
  code — see Finding 1) and wire the already-present rich data (floor-plan rent,
  `rental_yield_detail`, `quarterly_sale_psf`, listing arrays) into the corridor export.
  **DONE in `RouteExportBuilder`.**
- **Step 2:** re-shape the export to Schema v2 for everything achievable (psf-by-year,
  rental, `upcoming_supply`, `nearest_stations` from amenities, `airbnb_rollup`, `bms_500m`,
  `unit_mix`, `derived`, `flags`, `basis` tags, null policy, dedup). Missing fields ship as
  `null` + a flag — **never an estimate in the export.**
  **DONE in `RouteExportBuilder`**, including the four gaps the first pass left (per-project
  `data_scraped_at`, the listing-derived `asking` block, floor-plan-based `implied_avg_sqft`
  with its basis, and flags for the missing maintenance fee / missing per-year counts). The
  genuinely absent data listed above is still absent.
- **Step 3 (the only step actually left): wire it in.** `build()` has no caller, so none of
  the above reaches a model. That is a pipeline decision, not a data one — the episode run
  currently feeds per-stop engine briefs, and swapping or supplementing them with the export
  means changing what `resources/prompts/area_tutorial_script.md` SECTION 1 declares as well,
  since the prompt now describes the briefs. Fix the integer-keyed floor-plan map (see Status)
  before that lands.