# 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.

---

## Three findings up front

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.

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

### 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 and wire the
  already-present rich data (floor-plan rent, `rental_yield_detail`, `quarterly_sale_psf`,
  listing arrays) into the corridor export.
- **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.**