# Hong Kong Detail Page — Data Requirements
---

## Where things stand

The Hong Kong project detail page is largely built on petav3. Everything driven
by House730 data is done and verified against investhink.ai:

- **项目概览** — 项目资料, 相册 (grouped by album), 销售进度 timeline
- **户型 · 平面图** — the full per-unit price list (座 → 樓層 → 單位) with
  只看在售有价, the cheapest-unit verdict, 呎价(折)/价格(折), floor price ladder,
  单位户型图
- **位置 · 周边** + **跨境交通** — nearest MTR / hospitals / business cores /
  border crossings
- **VR360航拍** — the developer's show-flat tours (159 of 319 projects)
- **开发商** — other projects by the same developer
- 319 / 319 HK projects published, with 折實價 on the 80 that have one

What remains needs HK data petav3 does not hold. **Most of it reuses tables we
already have**, so the ask is smaller than it first looked.

---

## A. Reuses EXISTING petav3 tables — no new schema

| HK source | Goes into | Work needed |
|---|---|---|
| `ih_hk_estates` (13k) | `catalog_projects` (subsale segment) | **None — the adapter is already written and registered.** Just run `catalogue:sync centanet-estates` |
| `ih_hsbc_valuations`, `ih_centa_valuations`, `ih_estate_valuations` | `catalog_unit_valuations` | Table + `UnitValuationIngestionService` already exist; needs one adapter |
| per-unit data | `catalog_units` | Table already exists (floor / unit_number / saleable_area / gross_floor_area); needs one adapter |
| `ih_hk_listings` (21k) | `catalog_projects.asking_sale_listings` / `.asking_rental_listings` | JSON columns Malaysia already uses |
| transaction **aggregates** — median PSF, transaction counts, historical high/low | `catalog_projects.psf_25/median/75`, `price_25/median/75`, `total_transactions`, `transaction_period`, `historical_high_*`, `historical_low_*`, `rental_yield` | Columns already exist |

> **Important context: Malaysia has no transaction table at all.** It keeps
> per-project summary statistics in the columns above — 17,676 MY rows already
> carry `total_transactions`, 9,344 carry `psf_median`. If Hong Kong follows the
> same pattern, most of 投资分析 works without copying anything large.

---

## B. Needs NEW tables

Proposed names follow petav3 conventions: `catalog_*` for catalogue entities,
`market_*` for market reference data (as with the existing `market_airbnbs`,
`market_property_agents`), snake_case, plural, max 30 characters.

### B1. Tiny reference data — ~3,100 rows for the whole set

No decision needed on these. If we can read them, or get a dump, the sections
below can be finished quickly.

| HK source | rows | Proposed petav3 table | Feeds |
|---|---:|---|---|
| `ih_hk_schools` | 1,794 | `market_schools` | 校网 → 周边学校 |
| `ih_hk_featured_schools` | 468 | `market_featured_schools` | 校网 → 重点 4 所 |
| `ih_hk_education_scores` | 429 | `market_school_net_scores` | 教育指数 |
| `ih_hk_school_tags` | 137 | `market_school_tags` | school badges |
| `ih_hk_private_intl_schools` | 60 | `market_private_schools` | 12 年三种花法 |
| `ih_hk_school_net_scores` | 36 | `market_school_nets` | net tier + 首三志愿 |
| `ih_hk_district_pathways` | 18 | `market_school_pathways` | 升中 narrative |
| `ih_hk_feeder_links` | 11 | `market_school_feeders` | 一条龙 |
| `ih_hk_education_weights` | 4 | `market_school_weights` | 校网 scoring |
| `ih_hk_hma_profiles` | 174 | `market_area_profiles` | 片区画像 |
| `ih_gba_reference` | 20 | `market_area_benchmarks` | 大湾区对比 |
| `ih_hk_indices` | 16 | `market_price_indices` | 市场周期 (CCL) |

The nine school tables could collapse into about four if preferred — happy
either way.

### B2. Larger

| HK source | rows / size | Proposed petav3 table | Feeds |
|---|---:|---|---|
| `ih_hk_doc_pages` | 65,753 | `catalog_document_pages` | 官方文件 AI 问答 |
| `ih_hk_transactions` | 1.9M / **8.8 GB** | `market_transactions` | see section C |

---

## C. The one real decision — `ih_hk_transactions`

Only these sections need **individual** transaction rows. Everything else can be
served from the aggregates in section A.

| Tab / section | Why it needs per-transaction detail |
|---|---|
| 项目概览 → **五维画像** (4 of its 5 axes) | The page quotes counts — "周边业主持货 5-10 年实际中位升幅 +83%（281 宗真实转售）" |
| 投资分析 → **同区真实升值** | Resale pairs: `prevPrice`, `prevRegDate`, `gainPercent`, `heldDay` |
| 开发商 → **记分卡** + **JV 往绩** | Median 10-year realised growth per completed estate |
| 投资分析 → **深度市场分析** | Triangulated 预测 + 成交 + 银行 valuation |

Note the 校网 axis of 五维画像 is **not** transaction-based — it needs
`market_school_nets` from B1 only.

### Sizes, for context

| table | rows | size |
|---|---:|---:|
| `ih_hk_transactions` | 1.9M | **8,808 MB** |
| `ih_centa_valuations` | 1.1M | 351 MB |
| `ih_hk_listings` | 21k | 307 MB |
| `ih_hsbc_valuations` | 1.0M | 290 MB |
| `ih_hk_estates` | 13k | 72 MB |
| `ih_estate_valuations` | 14k | 8 MB |

### Options

1. **Aggregate into the existing `catalog_projects` columns** — the Malaysia
   pattern. Copies nothing, covers most figures, but loses the individual
   resale counts the page prints.
2. **Read `ih_hk_transactions` live over the read-only `reference` connection** —
   no copy, works immediately. This is what petav2 itself does, and petav3
   already reads shared market data this way. Trade-off: HK analysis pages
   depend on that database being reachable.
3. **Import into `market_transactions`** — self-contained and fastest to serve,
   but 8.8 GB to copy and keep in sync.

**Recommendation: 1 + 2.** Aggregate into the columns we already have for
anything that only needs a median or a count, and read live for the two
sections that genuinely quote individual resales. Nothing large gets
duplicated.

---

## D. Two side questions

1. **`hotness` is 0 on all 319 HK rows** in the `peta_wk_dev` copy we read
   (`MAX(hotness) = MIN(hotness) = 0`). investhink.ai orders its project list by
   this field, so we cannot reproduce its ordering. Does production carry real
   values, or is there another ordering signal?

2. **Every catalogue sync reports "upstream data is STALE."** When did the HK
   scrapers last run against the source we read?

---

## E. Summary of the ask

1. Approve running `catalogue:sync centanet-estates` — already built, adds ~13k
   subsale records to `catalog_projects`.
2. Access to (or a dump of) the ~3,100 rows of reference data in B1.
3. A decision on section C.
4. Answers to D.

Items 1 and 2 unblock 校网, 大湾区对比, 片区画像 and 市场周期 immediately.