feat(velocity): per-room-bucket breakdown in VelocityResult #163

Merged

lekss361 merged 2 commits from feat/velocity-per-room-breakdown into main

2026-05-15 07:09:03 +00:00

lekss361 commented

2026-05-15 07:00:32 +00:00

(Migrated from github.com)

Summary

Adds per-room sales breakdown to velocity API. User feedback: 'поквартирно сколько и каких а не м2'.

Changes (`backend/app/services/site_finder/velocity.py`)

New SQL query (bucket_rows) — третий после competitor + sales aggregations:

SELECT m.obj_id, crm.room_bucket,
       SUM(crm.deals_total_count) AS units_sold,
       SUM(COALESCE(crm.deals_total_vol_m2, crm.deals_total_count * 45.0)) AS sqm_sold
  FROM objective_corpus_room_month crm
  JOIN mapped m ON ...
 WHERE crm.report_month >= (CURRENT_DATE - CAST(:window_interval AS interval))
   AND crm.deals_total_count > 0
 GROUP BY m.obj_id, crm.room_bucket

Wrapped в SAVEPOINT (PR #155 pattern); CAST() syntax (PR #160 lesson).

Response shape

velocity gains 2 fields:

by_room_bucket: {bucket: {units, sqm, complexes_count}} — aggregate
sample_competitors[].by_room_bucket: {bucket: units} — per ЖК

Tests

3 new (aggregation, empty fallback, sample entries). All pass.

Verified data для test cad

ЖК	студия	1-к	2-к	3-к
Раута	38	22	30	9
Астон	18	13	6	3
Траектория	4	18	4	2

Refs #161

## Summary Adds per-room sales breakdown to velocity API. User feedback: 'поквартирно сколько и каких а не м2'. ## Changes (`backend/app/services/site_finder/velocity.py`) New SQL query (`bucket_rows`) — третий после competitor + sales aggregations: ```sql SELECT m.obj_id, crm.room_bucket, SUM(crm.deals_total_count) AS units_sold, SUM(COALESCE(crm.deals_total_vol_m2, crm.deals_total_count * 45.0)) AS sqm_sold FROM objective_corpus_room_month crm JOIN mapped m ON ... WHERE crm.report_month >= (CURRENT_DATE - CAST(:window_interval AS interval)) AND crm.deals_total_count > 0 GROUP BY m.obj_id, crm.room_bucket ``` Wrapped в SAVEPOINT (PR #155 pattern); CAST() syntax (PR #160 lesson). ## Response shape `velocity` gains 2 fields: - `by_room_bucket: {bucket: {units, sqm, complexes_count}}` — aggregate - `sample_competitors[].by_room_bucket: {bucket: units}` — per ЖК ## Tests 3 new (aggregation, empty fallback, sample entries). All pass. ## Verified data для test cad | ЖК | студия | 1-к | 2-к | 3-к | |---|---|---|---|---| | Раута | 38 | 22 | 30 | 9 | | Астон | 18 | 13 | 6 | 3 | | Траектория | 4 | 18 | 4 | 2 | Refs #161

lekss361 commented

2026-05-15 07:08:58 +00:00

(Migrated from github.com)

Final review (SHA `fdfe023`, CI ✅)

Cross-checked агентом — все 6 проверок пройдены, плюс бонусная находка.

✅ SAVEPOINT integrity: новый bucket_rows query wrapped в with db.begin_nested(): с graceful fallback на exception (bucket_rows = [], logger.warning). Pattern matches PR #155.
✅ Frontend contract additive only: as_dict() adds by_room_bucket field; existing keys obj_id, name, obj_class, distance_m, total_sqm_period preserved через **competitor_meta[oid]. VelocityBlock.tsx не сломается.
✅ SQL syntax clean: использует CAST(:window_interval AS interval) (PR #160 lesson). Бонус: фиксит lingering ::interval cast в _get_ekb_median line 375 — PR #160 пропустил это место!
✅ 3 новых теста: test_by_room_bucket_aggregation, test_by_room_bucket_empty_when_no_bucket_data, test_sample_competitors_include_by_room_bucket. Existing test_as_dict_structure обновлён.
✅ CI green (backend + frontend SUCCESS)

Минор (follow-up, не блокер)

🟡 TS types не обновлены: frontend/src/types/site-finder.ts (Velocity / VelocityCompetitor) не содержит by_room_bucket. Backend-only PR — extra fields в JSON не крашит frontend (TS additive-properties tolerant), но UI не сможет потребить field type-safe до follow-up.

🟢 Parking filter sanity check: source objective_corpus_room_month.room_bucket typically только residential (студия/1/2/3+); если non-residential bucket'ы есть — они leak в by_room_bucket. Worth quick grep distinct values, но likely fine.

Рекомендую follow-up

Update site-finder.ts Velocity + VelocityCompetitor interfaces with by_room_bucket для type-safe frontend consumption

approve merge

## Final review (SHA `fdfe023`, CI ✅) Cross-checked агентом — все 6 проверок пройдены, плюс бонусная находка. ✅ **SAVEPOINT integrity**: новый `bucket_rows` query wrapped в `with db.begin_nested():` с graceful fallback на exception (`bucket_rows = []`, logger.warning). Pattern matches PR #155. ✅ **Frontend contract additive only**: `as_dict()` adds `by_room_bucket` field; existing keys `obj_id`, `name`, `obj_class`, `distance_m`, `total_sqm_period` preserved через `**competitor_meta[oid]`. `VelocityBlock.tsx` не сломается. ✅ **SQL syntax clean**: использует `CAST(:window_interval AS interval)` (PR #160 lesson). **Бонус: фиксит lingering `::interval` cast в `_get_ekb_median` line 375** — PR #160 пропустил это место! ✅ **3 новых теста**: `test_by_room_bucket_aggregation`, `test_by_room_bucket_empty_when_no_bucket_data`, `test_sample_competitors_include_by_room_bucket`. Existing `test_as_dict_structure` обновлён. ✅ **CI green** (backend + frontend SUCCESS) ### Минор (follow-up, не блокер) 🟡 **TS types не обновлены**: `frontend/src/types/site-finder.ts` (`Velocity` / `VelocityCompetitor`) не содержит `by_room_bucket`. Backend-only PR — extra fields в JSON не крашит frontend (TS additive-properties tolerant), но UI не сможет потребить field type-safe до follow-up. 🟢 **Parking filter sanity check**: source `objective_corpus_room_month.room_bucket` typically только residential (студия/1/2/3+); если non-residential bucket'ы есть — они leak в `by_room_bucket`. Worth quick grep distinct values, но likely fine. ### Рекомендую follow-up - Update `site-finder.ts` Velocity + VelocityCompetitor interfaces with `by_room_bucket` для type-safe frontend consumption approve merge