feat(tradein): ppm²-tier segmentation of asking→sold ratio (#928, flag OFF) #934

Merged

bot-reviewer merged 2 commits from feat/tradein-ppm2-tier-ratio into main

2026-05-31 21:22:40 +00:00

lekss361 commented

2026-05-31 21:14:58 +00:00

Owner

Closes #928 (implementation; flag stays OFF until post-demo QA).

What

Segments the asking→sold correction ratio by ppm²-tier (terciles of active-listing ₽/m² per rooms bucket), so the cheap econom-panel submarket no longer shares a ratio with monolith new-builds. Targets the expected_sold under-valuation found in golden-сверка #794 vs Brusnika EKB-2485 (expected_sold 3.16M was −9% below the ~3.47M buyout). Headline median is NOT touched (same-building anchor + IQR/MAD unchanged).

Behind a flag — ships dark

tier_aware_ratio_enabled: bool = False (config.py). With the flag OFF the estimator path is byte-identical to current behavior. Do NOT enable on prod until after the 2026-06-01 demo + manual QA + the held-out backtest (see "Gate before flag-flip" below).

Design decisions

Separate tables, not ADD COLUMN. New asking_to_sold_ratios_tiered + asking_to_sold_tier_bounds; the existing asking_to_sold_ratios is untouched. Rationale: the flag-OFF estimator does SELECT ... WHERE rooms_bucket=B AND district='' → fetchone(); adding tier rows to that table with a new PK would return multiple rows and fetchone() would pick arbitrarily → silent prod regression. Parallel table keeps the live path provably unchanged.
Migration data/sql/098_asking_to_sold_ratios_tiered.sql — strictly additive (CREATE TABLE IF NOT EXISTS + ON CONFLICT seed), no DROP/ALTER of existing objects, BEGIN/COMMIT, idempotent. Safe for the strict prod auto-apply.
Tier ratio = median(sold in tier)/median(asking in tier) with empirical-Bayes shrinkage toward the rooms-aggregate ratio: w = n_deals/(n_deals + tier_ratio_shrink_k), k=150. On current data all 15 cells are dense (n_deals ≥169) so shrinkage is near-no-op; it's insurance for future thin cells.
Fallback ladder: tier (B,'',T) → rooms-aggregate (B,'','all') → global (-1,'','all') → (None,None). Graceful try/except + rollback on un-migrated DB.
Refresh task (tasks/asking_to_sold_ratio.py) extended to re-derive the tiered tables in the same atomic transaction as the legacy table (byte-consistent derivation).

Step-1 distribution gate (validated read-only on prod)

All 15 cells (5 rooms × 3 tiers) clear ≥30 deals AND ≥30 listings (min: 169 deals / 583 listings). Example: rb=2 t33=129167 t66=163870; low ratio 0.911, mid 0.978, high 0.991 vs current global 0.803. Brusnika EKB-2485 (anchor ppm² ≈92k → low tier) → ratio 0.911 → expected_sold ≈3.57M (error −9% → +3%).

Review fixes applied (code-reviewer ⚠️ MINOR, no blockers)

M1 cache-key collision: flag-OFF path now keys (bucket,"_legacy"), distinct from flag-ON (bucket,None) — prevents serving the old-table ratio for ~TTL after a flag flip.
M2 band-drift guard: comment in migration 098 + test_migration_098_band_matches_settings_default asserting the seed literals [30000,1200000] match _PPM2_MIN / asking_ratio_ppm2_max defaults.
M3 (doc only): tier placement currently uses the pre-anchor radius median median_ppm2, not the same-building Tukey anchor #928 ideally specifies (anchor is computed post-call). Robust for the golden case; flagged to revisit before the flag-flip.

Gate before flag-flip (post-demo, NOT in this PR)

Step 5 backtest: Brusnika expected_sold ≥3.35M (flag on) and all 53 golden cases' headline median byte-identical pre/post (regression guard, golden set = research/TradeIn_Valuation_Golden_Dataset_May29 §6); plus held-out ДКП check for mid/high tiers (those tiers' sold-side is untested by the asking-priced golden set) and the M3 anchor-placement revisit.

Tests

1677 passed; 2 failures are pre-existing on main (test_cian_valuation::…cache_hit, test_search_api::test_search_cache_hit) and unrelated. New: 8 tier-lookup unit tests + 2 guard tests. ruff clean.

Closes #928 (implementation; flag stays OFF until post-demo QA). ## What Segments the asking→sold correction ratio by **ppm²-tier** (terciles of active-listing ₽/m² per rooms bucket), so the cheap econom-panel submarket no longer shares a ratio with monolith new-builds. Targets the `expected_sold` under-valuation found in golden-сверка #794 vs Brusnika EKB-2485 (expected_sold 3.16M was −9% below the ~3.47M buyout). **Headline median is NOT touched** (same-building anchor + IQR/MAD unchanged). ## Behind a flag — ships dark `tier_aware_ratio_enabled: bool = False` (config.py). With the flag OFF the estimator path is **byte-identical** to current behavior. Do NOT enable on prod until after the 2026-06-01 demo + manual QA + the held-out backtest (see "Gate before flag-flip" below). ## Design decisions - **Separate tables, not ADD COLUMN.** New `asking_to_sold_ratios_tiered` + `asking_to_sold_tier_bounds`; the existing `asking_to_sold_ratios` is untouched. Rationale: the flag-OFF estimator does `SELECT ... WHERE rooms_bucket=B AND district='' → fetchone()`; adding tier rows to that table with a new PK would return multiple rows and `fetchone()` would pick arbitrarily → silent prod regression. Parallel table keeps the live path provably unchanged. - **Migration `data/sql/098_asking_to_sold_ratios_tiered.sql`** — strictly additive (CREATE TABLE IF NOT EXISTS + ON CONFLICT seed), no DROP/ALTER of existing objects, BEGIN/COMMIT, idempotent. Safe for the strict prod auto-apply. - **Tier ratio** = `median(sold in tier)/median(asking in tier)` with **empirical-Bayes shrinkage** toward the rooms-aggregate ratio: `w = n_deals/(n_deals + tier_ratio_shrink_k)`, `k=150`. On current data all 15 cells are dense (n_deals ≥169) so shrinkage is near-no-op; it's insurance for future thin cells. - **Fallback ladder:** tier `(B,'',T)` → rooms-aggregate `(B,'','all')` → global `(-1,'','all')` → `(None,None)`. Graceful try/except + rollback on un-migrated DB. - Refresh task (`tasks/asking_to_sold_ratio.py`) extended to re-derive the tiered tables in the **same atomic transaction** as the legacy table (byte-consistent derivation). ## Step-1 distribution gate (validated read-only on prod) All 15 cells (5 rooms × 3 tiers) clear ≥30 deals AND ≥30 listings (min: 169 deals / 583 listings). Example: rb=2 t33=129167 t66=163870; low ratio 0.911, mid 0.978, high 0.991 vs current global 0.803. Brusnika EKB-2485 (anchor ppm² ≈92k → **low** tier) → ratio 0.911 → expected_sold ≈3.57M (error −9% → +3%). ## Review fixes applied (code-reviewer ⚠️ MINOR, no blockers) - **M1** cache-key collision: flag-OFF path now keys `(bucket,"_legacy")`, distinct from flag-ON `(bucket,None)` — prevents serving the old-table ratio for ~TTL after a flag flip. - **M2** band-drift guard: comment in migration 098 + `test_migration_098_band_matches_settings_default` asserting the seed literals `[30000,1200000]` match `_PPM2_MIN` / `asking_ratio_ppm2_max` defaults. - **M3** (doc only): tier placement currently uses the pre-anchor radius median `median_ppm2`, not the same-building Tukey anchor #928 ideally specifies (anchor is computed post-call). Robust for the golden case; flagged to revisit before the flag-flip. ## Gate before flag-flip (post-demo, NOT in this PR) Step 5 backtest: Brusnika expected_sold ≥3.35M (flag on) **and** all 53 golden cases' headline median byte-identical pre/post (regression guard, golden set = `research/TradeIn_Valuation_Golden_Dataset_May29` §6); plus held-out ДКП check for mid/high tiers (those tiers' sold-side is untested by the asking-priced golden set) and the M3 anchor-placement revisit. ## Tests 1677 passed; 2 failures are pre-existing on main (`test_cian_valuation::…cache_hit`, `test_search_api::test_search_cache_hit`) and unrelated. New: 8 tier-lookup unit tests + 2 guard tests. ruff clean.

lekss361 added 2 commits 2026-05-31 21:14:59 +00:00

feat(tradein): ppm2-tier segmentation of asking->sold ratio pool, flag-gated (#928 ) 46db7ebcba

Add ppm2-tier (low/mid/high) asking->sold ratio lookup behind
tier_aware_ratio_enabled feature flag (default OFF). Flag-OFF path
remains byte-identical to pre-#928 behavior.

Migration 098 (additive, idempotent):
- asking_to_sold_tier_bounds: 33.33/66.67 pct ppm2 bounds per rooms_bucket
- asking_to_sold_ratios_tiered: per-tier ratio + 'all' aggregates + global -1
  Parallel table to avoid fetchone() regression on asking_to_sold_ratios.

Task (asking_to_sold_ratio.py):
- Extends recompute_asking_to_sold_ratios with tiered DELETE + re-derive
  in same transaction (atomic with old table refresh).
- Two new counters: tiered_rows_written, tier_bounds_written.

Config (config.py):
- tier_aware_ratio_enabled: bool = False  (feature flag, ENV: TIER_AWARE_RATIO_ENABLED)
- tier_ratio_shrink_k: int = 150  (empirical-Bayes shrink K, ENV: TIER_RATIO_SHRINK_K)

Estimator (estimator.py):
- _get_asking_sold_ratio gains anchor_ppm2 param (default None, backward-compat).
- Cache key changed from int to tuple[int, str|None] (bucket, tier).
- Flag-ON path: bounds lookup -> tier assign -> shrunk ratio w/ Bayes shrink.
- Fallback chain: tier -> 'all' -> global_all -> (None, None).

Tests:
- tests/services/test_asking_sold_tier_ratio.py: 8 new unit tests.
- tests/test_asking_to_sold_ratio.py: updated FakeDB (8 execute calls) + counter assertions.

Refs #928

fix(tradein): #928 cache-key collision, migration-098 band guard, tier-signal note 4b0fd10cb0

M1 (bug): flag-OFF path now caches under (bucket, '_legacy') instead of
(bucket, None), preventing stale old-table ratio being served when
tier_aware_ratio_enabled is flipped ON without process restart.

M2 (guard): add band-drift comment to migration 098 seed + regression test
asserting _PPM2_MIN==30000 and asking_ratio_ppm2_max default==1_200_000.

M3 (doc): append NOTE(#928) to estimator call-site comment explaining that
tier placement uses the pre-anchor radius median, not the same-building Tukey
anchor; flags the revisit needed before enabling the flag in production.

lekss361 referenced this pull request

2026-05-31 21:15:35 +00:00

[tradein][backend] Сегментация аналогов/ratio по ppm²-тиру — убрать смешение подрынков (expected_sold недооценка) #928

bot-reviewer approved these changes 2026-05-31 21:22:27 +00:00

bot-reviewer left a comment

Collaborator

✅ APPROVE — корректный dark-ship (flag OFF), миграция safe для strict prod-auto-apply, demo-safe. Мержу.

Verified — два критичных риска (прод DB + valuation)

Migration 098 — strictly additive + idempotent (проверил файл напрямую): BEGIN/COMMIT, CREATE TABLE IF NOT EXISTS ×2 (новые параллельные таблицы), INSERT … ON CONFLICT DO UPDATE (idempotent seed). DROP/ALTER/DELETE встречаются только в -- комментах (описание refresh-task), реальных destructive DDL/DML нет. Существующая asking_to_sold_ratios НЕ тронута → safe для strict exit-1 auto-apply (см. project_tradein_migrations_auto_apply), повторный re-apply идемпотентен.
Flag-OFF estimator — byte-identical: use_tier_path = settings.tier_aware_ratio_enabled and anchor_ppm2 is not None; flag OFF → legacy-ветка тот же SELECT ratio, basis FROM asking_to_sold_ratios (старая таблица) с отдельным cache-key (bucket,"_legacy") (M1 fix — нет collision с flag-ON 'all' после флипа). Tier-путь активен ТОЛЬКО flag-ON + anchor задан. Дефолт tier_aware_ratio_enabled=False → прод/демо без изменений.

Sound

Parallel-tables (не ADD COLUMN) — правильное решение: flag-OFF fetchone() по старой таблице не получит multiple rows. ✓
Refresh task ре-деривит tiered-таблицы в той же атомарной транзакции. Fallback ladder tier→rooms→global→(None) + graceful rollback на un-migrated DB.
M1 (cache-key collision) / M2 (band-drift guard test) исправлены; M3 (anchor-placement = pre-anchor median) задокументирован к ревизии перед flag-flip.
Тесты: 8 tier-lookup + 2 guard, 1677 passed (2 fail pre-existing на main, unrelated). ruff clean.

🟡 Follow-up (завожу issue — gate перед flag-flip)

НЕ включать tier_aware_ratio_enabled на проде до Step-5 backtest: Brusnika expected_sold ≥3.35M (flag on) + все 53 golden headline median byte-identical pre/post + held-out ДКП для mid/high тиров + M3 anchor-placement ревизия. Завожу tracking-issue.

Squash-merge + delete branch. Closes #928.

✅ **APPROVE** — корректный dark-ship (flag OFF), миграция safe для strict prod-auto-apply, demo-safe. Мержу. ## Verified — два критичных риска (прод DB + valuation) 1. **Migration `098` — strictly additive + idempotent** (проверил файл напрямую): `BEGIN/COMMIT`, `CREATE TABLE IF NOT EXISTS` ×2 (новые параллельные таблицы), `INSERT … ON CONFLICT DO UPDATE` (idempotent seed). `DROP/ALTER/DELETE` встречаются **только в `--` комментах** (описание refresh-task), реальных destructive DDL/DML нет. Существующая `asking_to_sold_ratios` НЕ тронута → safe для strict exit-1 auto-apply (см. `project_tradein_migrations_auto_apply`), повторный re-apply идемпотентен. 2. **Flag-OFF estimator — byte-identical**: `use_tier_path = settings.tier_aware_ratio_enabled and anchor_ppm2 is not None`; flag OFF → legacy-ветка тот же `SELECT ratio, basis FROM asking_to_sold_ratios` (старая таблица) с отдельным cache-key `(bucket,"_legacy")` (M1 fix — нет collision с flag-ON 'all' после флипа). Tier-путь активен ТОЛЬКО flag-ON + anchor задан. Дефолт `tier_aware_ratio_enabled=False` → прод/демо без изменений. ## Sound - Parallel-tables (не ADD COLUMN) — правильное решение: flag-OFF `fetchone()` по старой таблице не получит multiple rows. ✓ - Refresh task ре-деривит tiered-таблицы в той же атомарной транзакции. Fallback ladder tier→rooms→global→(None) + graceful rollback на un-migrated DB. - M1 (cache-key collision) / M2 (band-drift guard test) исправлены; M3 (anchor-placement = pre-anchor median) задокументирован к ревизии перед flag-flip. - Тесты: 8 tier-lookup + 2 guard, 1677 passed (2 fail pre-existing на main, unrelated). ruff clean. ## 🟡 Follow-up (завожу issue — gate перед flag-flip) НЕ включать `tier_aware_ratio_enabled` на проде до Step-5 backtest: Brusnika expected_sold ≥3.35M (flag on) + все 53 golden headline median byte-identical pre/post + held-out ДКП для mid/high тиров + M3 anchor-placement ревизия. Завожу tracking-issue. Squash-merge + delete branch. `Closes #928`.