lekss361/gendesign

Fork 0

feat(analytics): mv_quarter_price_per_m2 MV + refresh helper (#33 D1 PR A) #209

Merged

lekss361 merged 1 commit from feat/33-mv-quarter-price into main

2026-05-16 11:12:01 +00:00

lekss361 commented

2026-05-16 11:07:27 +00:00

Owner

Summary

Issue #33 D1 PR A — materialized view per-cad_quarter price aggregation из 6.83M rosreestr_deals. Заменяет district median (9 районов) на quarter granularity (~9700 ячеек для ЕКБ из 52 492 all-Russia).

Schema findings (real column names — НЕ как в issue spec)

Issue spec	Реальное имя
`cad_quarter`	`quarter_cad_number`
`price_per_m2`	`price_per_sqm` (99.9% filled)
`deal_date`	`period_start_date`
`realestate_type`	`realestate_type_code`

Fallback deal_price / area для ~6K rows с NULL price_per_sqm.

SQL (`data/sql/95_mv_quarter_price.sql`, 124 LOC)

Window: rolling 24 months
Filter: realestate_type_code='002001003000' (новостройки) + outlier 30K..800K руб/м²
HAVING COUNT(*) >= 3 (stat значимость)
Percentile breakdown: p25/median/p75/mean + temporal slices (median_6m/12m/24m)
WITH NO DATA + REFRESH after INDEX (per PR #194 pattern)
UNIQUE INDEX (quarter_cad_number) → CONCURRENTLY refresh capable
Expected MV size: ~52 492 quarters all-Russia (~9700 ЕКБ subset)

Python helper (`backend/app/services/site_finder/quarter_price_refresh.py`, 65 LOC)

refresh_quarter_price(db, concurrently=True) — pattern reuse из PR #194
try/except OperationalError fallback на non-concurrent при не-populated MV

Что НЕ в PR (Phase B follow-up)

Analyze integration — /analyze response должен возвращать demand.market_price: {p25, median, p75, deals_count, freshness} вместо district median. Отдельный PR #33 PR B (~50 LOC backend в parcels.py).
Celery beat refresh — weekly schedule для refresh_quarter_price (отдельный issue, после Phase B).

Test plan

Schema verified live (postgres MCP): quarter_cad_number, price_per_sqm, period_start_date, realestate_type_code
Pre-aggregation row estimate ~52K quarters
Migration auto-applied через deploy.yml + tracking _schema_migrations
pre-push: no test file (smoke verifies after deploy через qa-tester)
qa-tester post-deploy:
- SELECT COUNT(*) FROM mv_quarter_price_per_m2 ≥ 50 000
- SELECT * FROM mv_quarter_price_per_m2 WHERE quarter_cad_number = '66:41:0204016' (Академический ЕКБ) → median > 150K руб/м²
- EXPLAIN ANALYZE на refresh < 60s

Closes part of #33 (full close after PR B analyze integration).

Related: #194 (mv_layout_velocity — same pattern).

## Summary **Issue #33 D1 PR A** — materialized view per-cad_quarter price aggregation из **6.83M rosreestr_deals**. Заменяет district median (9 районов) на quarter granularity (~9700 ячеек для ЕКБ из 52 492 all-Russia). ### Schema findings (real column names — НЕ как в issue spec) | Issue spec | Реальное имя | |---|---| | `cad_quarter` | `quarter_cad_number` | | `price_per_m2` | `price_per_sqm` (99.9% filled) | | `deal_date` | `period_start_date` | | `realestate_type` | `realestate_type_code` | Fallback `deal_price / area` для ~6K rows с NULL `price_per_sqm`. ### SQL (`data/sql/95_mv_quarter_price.sql`, 124 LOC) - Window: rolling 24 months - Filter: `realestate_type_code='002001003000'` (новостройки) + outlier `30K..800K руб/м²` - HAVING COUNT(*) >= 3 (stat значимость) - Percentile breakdown: `p25/median/p75/mean` + temporal slices (`median_6m/12m/24m`) - WITH NO DATA + REFRESH after INDEX (per PR #194 pattern) - UNIQUE INDEX `(quarter_cad_number)` → CONCURRENTLY refresh capable - Expected MV size: **~52 492** quarters all-Russia (~9700 ЕКБ subset) ### Python helper (`backend/app/services/site_finder/quarter_price_refresh.py`, 65 LOC) - `refresh_quarter_price(db, concurrently=True)` — pattern reuse из PR #194 - try/except `OperationalError` fallback на non-concurrent при не-populated MV ### Что НЕ в PR (Phase B follow-up) - **Analyze integration** — `/analyze` response должен возвращать `demand.market_price: {p25, median, p75, deals_count, freshness}` вместо district median. Отдельный PR #33 PR B (~50 LOC backend в `parcels.py`). - **Celery beat refresh** — weekly schedule для `refresh_quarter_price` (отдельный issue, после Phase B). ## Test plan - [x] Schema verified live (postgres MCP): `quarter_cad_number`, `price_per_sqm`, `period_start_date`, `realestate_type_code` - [x] Pre-aggregation row estimate ~52K quarters - [x] Migration auto-applied через deploy.yml + tracking `_schema_migrations` - [x] pre-push: no test file (smoke verifies after deploy через qa-tester) - [ ] **qa-tester post-deploy**: - `SELECT COUNT(*) FROM mv_quarter_price_per_m2` ≥ 50 000 - `SELECT * FROM mv_quarter_price_per_m2 WHERE quarter_cad_number = '66:41:0204016'` (Академический ЕКБ) → median > 150K руб/м² - EXPLAIN ANALYZE на refresh < 60s Closes part of #33 (full close after PR B analyze integration). Related: #194 (mv_layout_velocity — same pattern).

lekss361 added 1 commit 2026-05-16 11:07:28 +00:00

feat(analytics): mv_quarter_price_per_m2 MV + refresh helper (#33 D1 PR A) e1a2a779e4

Per-cad_quarter price aggregation из 6.83M rosreestr_deals.

Source verification (postgres MCP):
- rosreestr_deals total: 6 830 001
- price_per_sqm filled 99.9% (fallback deal_price/area)
- quarter_cad_number (NOT cad_quarter), period_start_date, realestate_type_code

SQL (95_mv_quarter_price.sql, ~124 LOC):
- Filter realestate_type_code='002001003000' (новостройки) + outlier 30K..800K
- Percentile p25/median/p75 + mean + temporal slices (6m/12m/24m)
- 24-month window, HAVING COUNT(*)>=3
- WITH NO DATA + REFRESH after INDEX (#194 pattern)
- UNIQUE INDEX (quarter_cad_number) для CONCURRENTLY
- ~52 492 quarters в MV (all-Russia; EKB subset ~9700)

Python helper (quarter_price_refresh.py, ~65 LOC):
- refresh_quarter_price(db, concurrently=True) с try/except fallback

Phase B (analyze integration — quarter median вместо district) — отдельный PR.

lekss361 commented

2026-05-16 11:11:04 +00:00

Author

Owner

Deep Code Review — verdict

Summary

Status: ✅ APPROVE
Files reviewed: 2 (P0: 1 SQL migration, P1: 1 backend helper)
Lines: +187 / -0
PR: #209 · head SHA e1a2a77 · base 092976f6 · mergeable: true

🔴 Critical / 🟠 High / 🟡 Medium

None.

🟢 Low / nits (не блокирующее, можно отдельным PR или next iteration)

data/sql/95_mv_quarter_price.sql:88 — median_24m AS m.median алиас "for API symmetry" — fine, но slight redundancy в выборке. Inline comment покрывает.
backend/app/services/site_finder/quarter_price_refresh.py — placed under site_finder/ (consistent with layout_velocity_refresh.py из #194); arguably services/analytics/ точнее по domain, но pattern уже established — оставить.
data/sql/95_mv_quarter_price.sql:50-65 — два отдельных GROUP BY на filtered CTE (agg_main + agg_windows) дают 2× full sort на 1.89M rows (видно в EXPLAIN). Можно объединить через FILTER через subquery, но PG16 не поддерживает FILTER на PERCENTILE_CONT → текущий CASE-NULL workaround корректен. Тред-офф acceptable для weekly batch.

Verified live via postgres MCP

Schema: все 6 колонок (quarter_cad_number varchar, price_per_sqm numeric, deal_price numeric, area numeric, period_start_date date, realestate_type_code text) match diff 1-to-1 — PR body claim верный, issue spec был устаревшим.
Partitioning: rosreestr_deals partitioned by RANGE (period_start_date), 9 partitions (2024q1..2026q1). WHERE period_start_date >= NOW() - INTERVAL '24 months' корректно prune'ит _2024q1 (out of window) — EXPLAIN подтвердил.
Existing index rosreestr_deals_realtype_idx on realestate_type_code используется → Parallel Bitmap Heap Scan по 8 партициям, не Seq Scan. Дополнительный composite index НЕ нужен.
Row counts: total 6 830 001 · novostroyki 3 143 004 · in 24mo window 2 355 819 → after outlier filter ~1 889K → after HAVING ≥3 = 52 492 rows in MV (matches PR body claim точно).
EXPLAIN ANALYZE на full MV body: 12.67s execution на 6.83M parent table. Acceptable для weekly batch refresh. CONCURRENTLY будет slower но non-blocking.

Cross-file impact analysis

No existing callers of mv_quarter_price_per_m2 — этот PR его вводит. Zero breakage risk.
Legacy district median path (backend/app/services/analytics_queries.py:1842-2493 использует ekb_districts.median_price_per_m2) — НЕ тронут. Phase B заменит — корректное scope discipline.
backend/app/api/v1/parcels.py использует rosreestr_deals для cadastre comparables — независимый path, no interference.
analytics_refresh.py / workers/tasks/refresh_analytics.py существуют для будущей beat schedule integration (Issue #33 PR C) — корректно не тронуты в этом PR.

Pattern consistency vs merged PR #194 (`mv_layout_velocity`)

1-to-1 reuse всех fix'ов из #194:

WITH NO DATA + UNIQUE INDEX → REFRESH after — ✅
UNIQUE INDEX mv_quarter_price_pk (quarter_cad_number) для CONCURRENTLY — ✅
DROP ... IF EXISTS CASCADE + WARN comment про DR re-apply — ✅
COMMENT ON MATERIALIZED VIEW для observability — ✅
Python helper: db.commit() после REFRESH, literal SQL strings (no f-string), try/except OperationalError с fallback на non-concurrent — ✅
from __future__ import annotations, type hints, logger.* — ✅

Conventions check

BEGIN; ... COMMIT; обёртка — ✅
File numbering 95_* — next free после 94_mv_layout_velocity.sql ✅
Idempotency через DROP IF EXISTS CASCADE + _schema_migrations tracking — ✅
psycopg v3 / CAST(:x AS type) rule — N/A (no bind params в helper)
ruff line length 100 — longest line ~85 chars ✅
No print, no requests, no import psycopg2, no except: pass ✅

Vault cross-check

✅ domains/rosreestr/MV_Quarter_Price_Per_M2_May16.md — entry создана в той же сессии (per CLAUDE.md rule #6: "Vault обновляется вместе с кодом").
✅ limitations/Limit_District_Median_Null.md — этот MV is the documented replacement, no conflict.
✅ domains/api/endpoints/Endpoint_Parcel_BestLayouts.md — references PR #194 as same pattern; этот PR — consistent extension.

Performance findings

Initial REFRESH: ~13s (verified EXPLAIN ANALYZE на проде).
CONCURRENTLY refresh estimate: ~25-40s (двойная работа: build + diff against existing MV).
MV size estimate: ~52 492 rows × ~100 bytes ≈ ~5 MB + index ~2 MB. Negligible storage impact.
No additional indexes needed на rosreestr_deals — existing rosreestr_deals_realtype_idx доминирует план.

Positive observations

✅ Schema verified pre-PR через postgres MCP (исправил issue spec на real columns: quarter_cad_number vs cad_quarter, price_per_sqm vs price_per_m2, etc.)
✅ Inline comment в SQL объясняет PG16 PERCENTILE_CONT FILTER limitation + CASE-NULL workaround
✅ Comment про median_24m alias предупреждает readers о намеренной избыточности
✅ DR safety WARN в header SQL про non-concurrent first refresh после re-apply
✅ Fallback deal_price / area для ~6K rows с NULL price_per_sqm — корректно через CASE WHEN ... area > 0

Recommended next steps

Merge — main session human approval (scope blocked для auto-merge per policy: data/sql/** + backend/app/services/**).
Post-deploy qa-tester проверки (per PR test plan):
- SELECT COUNT(*) FROM mv_quarter_price_per_m2 ≥ 50K
- Sanity check on ЕКБ quarter (e.g. Академический 66:41:0204016) median > 150K руб/м²
Phase B PR: integrate в /analyze response → demand.market_price: {p25, median, p75, deals_count, freshness} (planned, ~50 LOC в parcels.py).
Phase C PR: Celery beat weekly schedule для refresh_quarter_price.

Complexity / blast radius score

Risk: Low — additive only (new MV + new helper), no existing consumers, no schema changes на source table, no breaking API changes.
Reversibility: Easy revert — DROP MATERIALIZED VIEW mv_quarter_price_per_m2 CASCADE; + delete entry из _schema_migrations. No data loss (MV is derived).
Recommended merge window: anytime — auto-deploy через deploy.yml применит миграцию + initial REFRESH (~13s) во время первого containers update. Single MV creation не блокирует existing reads.

Auto-merge policy: ❌ BLOCKED for auto-merge — touches data/sql/** + backend/app/services/** per scope policy. ✅ Verdict APPROVE — clean для human-initiated merge.

## Deep Code Review — verdict  ### Summary - **Status**: ✅ APPROVE - **Files reviewed**: 2 (P0: 1 SQL migration, P1: 1 backend helper) - **Lines**: +187 / -0 - **PR**: #209 · head SHA `e1a2a77` · base `092976f6` · mergeable: true ### 🔴 Critical / 🟠 High / 🟡 Medium None. ### 🟢 Low / nits (не блокирующее, можно отдельным PR или next iteration) - `data/sql/95_mv_quarter_price.sql:88` — `median_24m AS m.median` алиас "for API symmetry" — fine, но slight redundancy в выборке. Inline comment покрывает. - `backend/app/services/site_finder/quarter_price_refresh.py` — placed under `site_finder/` (consistent with `layout_velocity_refresh.py` из #194); arguably `services/analytics/` точнее по domain, но pattern уже established — оставить. - `data/sql/95_mv_quarter_price.sql:50-65` — два отдельных GROUP BY на `filtered` CTE (`agg_main` + `agg_windows`) дают 2× full sort на 1.89M rows (видно в EXPLAIN). Можно объединить через `FILTER` через subquery, но PG16 не поддерживает FILTER на `PERCENTILE_CONT` → текущий CASE-NULL workaround корректен. Тред-офф acceptable для weekly batch. ### Verified live via postgres MCP - **Schema**: все 6 колонок (`quarter_cad_number varchar`, `price_per_sqm numeric`, `deal_price numeric`, `area numeric`, `period_start_date date`, `realestate_type_code text`) match diff 1-to-1 — PR body claim верный, issue spec был устаревшим. - **Partitioning**: `rosreestr_deals` partitioned by `RANGE (period_start_date)`, 9 partitions (2024q1..2026q1). `WHERE period_start_date >= NOW() - INTERVAL '24 months'` корректно prune'ит `_2024q1` (out of window) — EXPLAIN подтвердил. - **Existing index `rosreestr_deals_realtype_idx` on `realestate_type_code`** используется → Parallel Bitmap Heap Scan по 8 партициям, не Seq Scan. Дополнительный composite index НЕ нужен. - **Row counts**: total 6 830 001 · novostroyki 3 143 004 · in 24mo window 2 355 819 → after outlier filter ~1 889K → after HAVING ≥3 = **52 492 rows in MV** (matches PR body claim точно). - **EXPLAIN ANALYZE на full MV body**: **12.67s execution** на 6.83M parent table. Acceptable для weekly batch refresh. CONCURRENTLY будет slower но non-blocking. ### Cross-file impact analysis - **No existing callers** of `mv_quarter_price_per_m2` — этот PR его вводит. Zero breakage risk. - **Legacy district median path** (`backend/app/services/analytics_queries.py:1842-2493` использует `ekb_districts.median_price_per_m2`) — НЕ тронут. Phase B заменит — корректное scope discipline. - `backend/app/api/v1/parcels.py` использует `rosreestr_deals` для cadastre comparables — независимый path, no interference. - `analytics_refresh.py` / `workers/tasks/refresh_analytics.py` существуют для будущей beat schedule integration (Issue #33 PR C) — корректно не тронуты в этом PR. ### Pattern consistency vs merged PR #194 (`mv_layout_velocity`) 1-to-1 reuse всех fix'ов из #194: - `WITH NO DATA` + UNIQUE INDEX → REFRESH after — ✅ - UNIQUE INDEX `mv_quarter_price_pk (quarter_cad_number)` для CONCURRENTLY — ✅ - `DROP ... IF EXISTS CASCADE` + WARN comment про DR re-apply — ✅ - `COMMENT ON MATERIALIZED VIEW` для observability — ✅ - Python helper: `db.commit()` после REFRESH, literal SQL strings (no f-string), try/except `OperationalError` с fallback на non-concurrent — ✅ - `from __future__ import annotations`, type hints, `logger.*` — ✅ ### Conventions check - `BEGIN; ... COMMIT;` обёртка — ✅ - File numbering `95_*` — next free после `94_mv_layout_velocity.sql` ✅ - Idempotency через `DROP IF EXISTS CASCADE` + `_schema_migrations` tracking — ✅ - psycopg v3 / `CAST(:x AS type)` rule — N/A (no bind params в helper) - ruff line length 100 — longest line ~85 chars ✅ - No `print`, no `requests`, no `import psycopg2`, no `except: pass` ✅ ### Vault cross-check - ✅ `domains/rosreestr/MV_Quarter_Price_Per_M2_May16.md` — entry создана в той же сессии (per CLAUDE.md rule #6: "Vault обновляется вместе с кодом"). - ✅ `limitations/Limit_District_Median_Null.md` — этот MV is the documented replacement, no conflict. - ✅ `domains/api/endpoints/Endpoint_Parcel_BestLayouts.md` — references PR #194 as same pattern; этот PR — consistent extension. ### Performance findings - Initial REFRESH: ~13s (verified EXPLAIN ANALYZE на проде). - CONCURRENTLY refresh estimate: ~25-40s (двойная работа: build + diff against existing MV). - MV size estimate: ~52 492 rows × ~100 bytes ≈ ~5 MB + index ~2 MB. Negligible storage impact. - No additional indexes needed на `rosreestr_deals` — existing `rosreestr_deals_realtype_idx` доминирует план. ### Positive observations - ✅ Schema verified pre-PR через postgres MCP (исправил issue spec на real columns: `quarter_cad_number` vs `cad_quarter`, `price_per_sqm` vs `price_per_m2`, etc.) - ✅ Inline comment в SQL объясняет PG16 PERCENTILE_CONT FILTER limitation + CASE-NULL workaround - ✅ Comment про `median_24m` alias предупреждает readers о намеренной избыточности - ✅ DR safety WARN в header SQL про non-concurrent first refresh после re-apply - ✅ Fallback `deal_price / area` для ~6K rows с NULL `price_per_sqm` — корректно через `CASE WHEN ... area > 0` ### Recommended next steps 1. Merge — main session human approval (scope blocked для auto-merge per policy: `data/sql/**` + `backend/app/services/**`). 2. Post-deploy qa-tester проверки (per PR test plan): - `SELECT COUNT(*) FROM mv_quarter_price_per_m2` ≥ 50K - Sanity check on ЕКБ quarter (e.g. Академический `66:41:0204016`) median > 150K руб/м² 3. Phase B PR: integrate в `/analyze` response → `demand.market_price: {p25, median, p75, deals_count, freshness}` (planned, ~50 LOC в `parcels.py`). 4. Phase C PR: Celery beat weekly schedule для `refresh_quarter_price`. ### Complexity / blast radius score - **Risk**: Low — additive only (new MV + new helper), no existing consumers, no schema changes на source table, no breaking API changes. - **Reversibility**: Easy revert — `DROP MATERIALIZED VIEW mv_quarter_price_per_m2 CASCADE;` + delete entry из `_schema_migrations`. No data loss (MV is derived). - **Recommended merge window**: anytime — auto-deploy через deploy.yml применит миграцию + initial REFRESH (~13s) во время первого containers update. Single MV creation не блокирует existing reads. --- **Auto-merge policy**: ❌ BLOCKED for auto-merge — touches `data/sql/**` + `backend/app/services/**` per scope policy. ✅ Verdict APPROVE — clean для human-initiated merge.