1.2 + zhkh_year, propagate to listings (#2013) #2476

Merged

bot-backend merged 1 commit from feat/2013-domrf-year-backfill into main

2026-07-12 13:12:08 +00:00

bot-backend commented

2026-07-12 13:10:33 +00:00

Collaborator

Problem

houses.year_built is only 38% filled and houses.material_walls is 0% filled. The existing ГИС-ЖКХ loader (app/services/zhkh_flats_loader.py, migr. 146/149) already populates houses.zhkh_year (70% = 5002/7128) and houses.zhkh_floors (70%), but never copies them into houses.year_built/total_floors — that is the core gap. The estimator's cohort filter (app/services/estimator.py, year_built BETWEEN ...) directly depends on real years, so this is the real fix for #2013 (supersedes the parked cohort-exclusion PR #2474, which only worked around the symptom on avito).

What this adds

Loader app/services/domrf_kapremont_loader.py + CLI task app/tasks/domrf_kapremont_load.py — downloads ДОМ.РФ капремонт open data (free, no auth) for region 66:
- КР1.1 house registry (export/190) — mkd_code, houseguid (ФИАС GUID), address, commission_year, number_floors_max, total_sq.
- КР1.2 constructive elements (export/275) — long-format (row per construction element per mkd_code); wall_material is populated only on the фасад (facade) element row.
- httpx verify=False (домрф.рф serves an RU cert not verified by the default trust store — same situation as sber_index.py).
- UPSERT into a new staging table domrf_kapremont (idempotent, IS DISTINCT FROM gate, chunked SAVEPOINTs mirroring zhkh_flats_loader.py).
Migration data/sql/176_domrf_kapremont.sql — creates the domrf_kapremont staging table (schema only, no DML on houses/listings).
Backfill houses (backfill_houses_from_domrf) — two-step UPDATE:
- Step 1: houses matched to domrf_kapremont via COALESCE(gar_house_guid, house_fias_id, zhkh_house_guid) = houseguid get year_built = COALESCE(year_built, domrf.commission_year, zhkh_year), material_walls = COALESCE(material_walls, domrf.wall_material), total_floors = COALESCE(total_floors, domrf.number_floors_max, zhkh_floors).
- Step 2: any house (including those with no ДОМ.РФ match at all) gets year_built/total_floors folded in from the already-loaded zhkh_year/zhkh_floors if still NULL — so houses without a ДОМ.РФ match still benefit from the ГИС-ЖКХ data already on hand.
- Only fills NULLs (COALESCE) — never overwrites existing non-null year_built.
Propagate to listings (propagate_listings_year_from_houses) — UPDATE listings SET year_built = houses.year_built WHERE house_id_fk links and listings.year_built IS NULL. This is what actually lifts avito's estimator-visible year coverage (avito is 52.5% linked to houses via house_id_fk but has 0% year_built of its own).

Coverage impact (expected, prod)

houses.year_built: 38% → higher (ДОМ.РФ region-66 registry + fold-in of the 70%-filled zhkh_year).
houses.material_walls: 0% → non-zero (first source ever for this column).
listings.year_built on avito: 0% → up to ~36-52% (bounded by avito's 52.5% house_id_fk link rate × however many of those linked houses gain a year_built).

Tests

tests/test_domrf_kapremont_loader.py (23 tests, all green):

CSV parsing against fixtures built from the real downloaded region-66 files (two real houses, incl. a real КР1.2 фасад row with wall_material=кирпич).
fetch_domrf_csvs download+unzip path with HTTP mocked via httpx.MockTransport (no live network).
Static SQL assertions: psycopg v3 CAST(:x AS type) discipline, ON CONFLICT ... IS DISTINCT FROM idempotency, COALESCE ordering (year_built, domrf.commission_year, zhkh_year), only-fills-NULL guards, listings propagation predicate.
backfill_houses_from_domrf / propagate_listings_year_from_houses dry-run vs real-run branching (MagicMock db).

Also ran (all green, unrelated pre-existing failure confirmed out of scope — tests/test_search_api.py::test_search_cache_hit, RBAC/auth fixture ordering issue reproducible on forgejo/main before this branch, not touched by this PR):

tests/test_backtest_regression_gate.py
tests/test_estimator*.py (450 tests)
tests/test_migrations_manifest.py
ruff + ruff-format (pre-commit hooks) clean.

Deliberately skipped

Wiring the propagation into the listing save/link flow: grepped for an obvious _link_listing_to_house hook (as hinted in the issue) and found none — house_id_fk linking happens via app/services/matching/listings.py and standalone backfill scripts, not a single save-time hook. Per the issue's own guidance, shipping the standalone backfill script (propagate_listings_year_from_houses, wired into the CLI task) rather than forcing an integration point that doesn't cleanly exist.
Address-normalization fallback for the staging→houses match: kept to guid-only COALESCE join (gar_house_guid → house_fias_id → zhkh_house_guid) per the issue's "keep it simple/safe" guidance — no fuzzy address matching.

## Problem `houses.year_built` is only 38% filled and `houses.material_walls` is 0% filled. The existing ГИС-ЖКХ loader (`app/services/zhkh_flats_loader.py`, migr. 146/149) already populates `houses.zhkh_year` (70% = 5002/7128) and `houses.zhkh_floors` (70%), but **never copies them into `houses.year_built`/`total_floors`** — that is the core gap. The estimator's cohort filter (`app/services/estimator.py`, `year_built BETWEEN ...`) directly depends on real years, so this is the real fix for #2013 (supersedes the parked cohort-exclusion PR #2474, which only worked around the symptom on avito). ## What this adds 1. **Loader** `app/services/domrf_kapremont_loader.py` + CLI task `app/tasks/domrf_kapremont_load.py` — downloads ДОМ.РФ капремонт open data (free, no auth) for region 66: - КР1.1 house registry (`export/190`) — `mkd_code, houseguid (ФИАС GUID), address, commission_year, number_floors_max, total_sq`. - КР1.2 constructive elements (`export/275`) — long-format (row per construction element per `mkd_code`); `wall_material` is populated only on the `фасад` (facade) element row. - httpx `verify=False` (домрф.рф serves an RU cert not verified by the default trust store — same situation as `sber_index.py`). - UPSERT into a new staging table `domrf_kapremont` (idempotent, `IS DISTINCT FROM` gate, chunked SAVEPOINTs mirroring `zhkh_flats_loader.py`). 2. **Migration** `data/sql/176_domrf_kapremont.sql` — creates the `domrf_kapremont` staging table (schema only, no DML on `houses`/`listings`). 3. **Backfill `houses`** (`backfill_houses_from_domrf`) — two-step UPDATE: - Step 1: houses matched to `domrf_kapremont` via `COALESCE(gar_house_guid, house_fias_id, zhkh_house_guid) = houseguid` get `year_built = COALESCE(year_built, domrf.commission_year, zhkh_year)`, `material_walls = COALESCE(material_walls, domrf.wall_material)`, `total_floors = COALESCE(total_floors, domrf.number_floors_max, zhkh_floors)`. - Step 2: **any** house (including those with no ДОМ.РФ match at all) gets `year_built`/`total_floors` folded in from the already-loaded `zhkh_year`/`zhkh_floors` if still NULL — so houses without a ДОМ.РФ match still benefit from the ГИС-ЖКХ data already on hand. - Only fills NULLs (COALESCE) — never overwrites existing non-null `year_built`. 4. **Propagate to `listings`** (`propagate_listings_year_from_houses`) — `UPDATE listings SET year_built = houses.year_built WHERE house_id_fk` links and `listings.year_built IS NULL`. This is what actually lifts avito's estimator-visible year coverage (avito is 52.5% linked to houses via `house_id_fk` but has 0% `year_built` of its own). ## Coverage impact (expected, prod) - `houses.year_built`: 38% → higher (ДОМ.РФ region-66 registry + fold-in of the 70%-filled `zhkh_year`). - `houses.material_walls`: 0% → non-zero (first source ever for this column). - `listings.year_built` on avito: 0% → up to ~36-52% (bounded by avito's 52.5% `house_id_fk` link rate × however many of those linked houses gain a `year_built`). ## Tests `tests/test_domrf_kapremont_loader.py` (23 tests, all green): - CSV parsing against fixtures built from the **real** downloaded region-66 files (two real houses, incl. a real КР1.2 `фасад` row with `wall_material=кирпич`). - `fetch_domrf_csvs` download+unzip path with HTTP mocked via `httpx.MockTransport` (no live network). - Static SQL assertions: psycopg v3 `CAST(:x AS type)` discipline, `ON CONFLICT ... IS DISTINCT FROM` idempotency, COALESCE ordering (`year_built, domrf.commission_year, zhkh_year`), only-fills-NULL guards, listings propagation predicate. - `backfill_houses_from_domrf` / `propagate_listings_year_from_houses` dry-run vs real-run branching (MagicMock db). Also ran (all green, unrelated pre-existing failure confirmed out of scope — `tests/test_search_api.py::test_search_cache_hit`, RBAC/auth fixture ordering issue reproducible on `forgejo/main` before this branch, not touched by this PR): - `tests/test_backtest_regression_gate.py` - `tests/test_estimator*.py` (450 tests) - `tests/test_migrations_manifest.py` - ruff + ruff-format (pre-commit hooks) clean. ## Deliberately skipped - Wiring the propagation into the listing save/link flow: grepped for an obvious `_link_listing_to_house` hook (as hinted in the issue) and found none — `house_id_fk` linking happens via `app/services/matching/listings.py` and standalone backfill scripts, not a single save-time hook. Per the issue's own guidance, shipping the standalone backfill script (`propagate_listings_year_from_houses`, wired into the CLI task) rather than forcing an integration point that doesn't cleanly exist. - Address-normalization fallback for the staging→houses match: kept to guid-only COALESCE join (`gar_house_guid → house_fias_id → zhkh_house_guid`) per the issue's "keep it simple/safe" guidance — no fuzzy address matching.

bot-backend added 1 commit 2026-07-12 13:10:33 +00:00

feat(tradein): fill houses.year_built/material_walls from ДОМ.РФ КР1.1/1.2 + zhkh_year, propagate to listings (#2013 )

CI / changes (pull_request) Successful in 7s

Details

CI Trade-In / changes (pull_request) Successful in 8s

Details

CI Trade-In / frontend-checks (pull_request) Has been skipped

Details

CI / backend-tests (pull_request) Has been skipped

Details

CI / frontend-tests (pull_request) Has been skipped

Details

CI / openapi-codegen-check (pull_request) Has been skipped

Details

CI Trade-In / backend-tests (pull_request) Successful in 51s

Details

30195c30ce

houses.year_built is only 38% filled and material_walls 0%. The existing ГИС-ЖКХ
loader already populates houses.zhkh_year (70%) but never copies it into
year_built — that's the core gap. Adds a ДОМ.РФ капремонт open-data loader
(КР1.1 house registry + КР1.2 constructive elements, region 66) into a new
domrf_kapremont staging table, then backfills houses.year_built/material_walls/
total_floors via COALESCE(existing, domrf, zhkh_year) — folding in the
already-loaded ЖКХ year so houses without a ДОМ.РФ match still benefit — and
propagates houses.year_built into listings.year_built for still-NULL rows so
the estimator's year_built cohort filter runs on real years across all sources
(avito 0%→~36-52% once linked houses gain a year). Supersedes the parked
cohort-exclusion PR #2474.