feat(parcels): competitors endpoint (Forgejo #112 Phase 1 backend) #191

Merged

lekss361 merged 1 commit from feat/112-competitors-backend into main

2026-05-15 22:21:06 +00:00

lekss361 commented

2026-05-15 22:16:34 +00:00

Owner

Summary

Active competitors analysis для site-finder (priority/high от Макса).

Endpoint

POST /api/v1/parcels/{cad_num}/competitors

Body: {radius_km (0.1-1.5), time_window (month/quarter/year), obj_class_filter, exclude_obj_ids}

Returns list of competitors с distance_m, velocity_per_month, obj_class, lat/lng, is_active flag.

Schema correctness (4 critical fixed via review)

snapshot_date (NOT snapshot_at) в domrf_kn_objects
flat_count (NOT flats_total/flats_sold) — sold_pct return null
Velocity мигрирован domrf_kn_sale_graph (stale до 2026-01) → objective_corpus_room_month через objective_complex_mapping JOIN, паттерн как в velocity.py (coverage ~2.5%)
domrf_kn_flats.status (NOT deal_status)

Files

schemas/parcel.py: 4 new Pydantic models, radius_km ge=0.1
services/site_finder/competitors.py NEW (~150 lines): get_competitors() + SQL CTEs (latest_obj, velocity, distances, avg_price)
api/v1/parcels.py: POST endpoint, no exc leak in 500
tests/api/v1/test_parcel_competitors.py NEW: 9 mock tests

Test plan

9/9 tests pass
ruff clean
After merge + deploy → curl на cad с известными окружающими ЖК

Depends on: nothing (independent of #115).
Blocks: #113 (best layouts needs competitor obj_ids).

Closes part of #112 (Phase 1 backend). Frontend separate PR.

## Summary Active competitors analysis для site-finder (priority/high от Макса). ## Endpoint `POST /api/v1/parcels/{cad_num}/competitors` Body: `{radius_km (0.1-1.5), time_window (month/quarter/year), obj_class_filter, exclude_obj_ids}` Returns list of competitors с distance_m, velocity_per_month, obj_class, lat/lng, is_active flag. ## Schema correctness (4 critical fixed via review) - `snapshot_date` (NOT `snapshot_at`) в `domrf_kn_objects` - `flat_count` (NOT `flats_total`/`flats_sold`) — sold_pct return null - **Velocity мигрирован** `domrf_kn_sale_graph` (stale до 2026-01) → `objective_corpus_room_month` через `objective_complex_mapping` JOIN, паттерн как в `velocity.py` (coverage ~2.5%) - `domrf_kn_flats.status` (NOT `deal_status`) ## Files - `schemas/parcel.py`: 4 new Pydantic models, radius_km `ge=0.1` - `services/site_finder/competitors.py` NEW (~150 lines): `get_competitors()` + SQL CTEs (latest_obj, velocity, distances, avg_price) - `api/v1/parcels.py`: POST endpoint, no exc leak in 500 - `tests/api/v1/test_parcel_competitors.py` NEW: 9 mock tests ## Test plan - [x] 9/9 tests pass - [x] ruff clean - [ ] After merge + deploy → curl на cad с известными окружающими ЖК Depends on: nothing (independent of #115). Blocks: #113 (best layouts needs competitor obj_ids). Closes part of #112 (Phase 1 backend). Frontend separate PR.

lekss361 added 1 commit 2026-05-15 22:16:35 +00:00

feat(parcels): POST /{cad_num}/competitors — active competitors in radius (Forgejo #112 )

CI / backend (pull_request) Successful in 1m12s

Details

CI / frontend (pull_request) Successful in 2m14s

Details

f3d85f4d7a

Active competitor analysis для site-finder. Радиус 0.1-1.5km, time_window
month/quarter/year, фильтр obj_class, exclude_obj_ids.

Schema correctness (review fixed 4 critical):
- snapshot_date (not snapshot_at)
- flat_count (not flats_total/flats_sold) — flats_sold/sold_pct null
- Velocity migrated domrf_kn_sale_graph -> objective_corpus_room_month
- domrf_kn_flats.status (not deal_status)

9 mock tests pass. Closes part of #112 backend.

lekss361 commented

2026-05-15 22:21:04 +00:00

Author

Owner

Auto-review — verdict: approve ✅

CI ✅ (backend 1m12s + frontend 2m14s).

Schema verification: ALL 4 claims CORRECT

snapshot_date в latest_obj CTE ✅
flat_count → mapped flats_total, sold/pct=null ✅
Velocity migration objective_corpus_room_month + objective_complex_mapping JOIN ✅ (паттерн как velocity.py)
status = 'sold' в _AVG_PRICE_SQL ✅

Security: clean

cad_num, obj_class_filter, radius_m — all через bound params
exclude_obj_ids фильтруется в Python (set), не SQL
window_interval строится из hardcoded dict (time_window_months: float), не user input
No injection vectors

Minor (не блокирующие, fix later)

competitors.py:244 — distances CTE computes ST_Distance для ВСЕХ rows в latest_obj, фильтрует WHERE distance_m <= :radius_m в outer SELECT. Без ST_DWithin pre-filter — full-table scan на каждый запрос. Phase 1 acceptable (dataset bounded), но для Phase 2/scaling → заменить distances CTE на ST_DWithin(geog, ref_point::geography, :radius_m) — index-assisted.
competitors.py:408 — поле weighted_avg_velocity это арифметическое mean (total / n), не weighted. Misleading name. Rename → avg_velocity_per_month в follow-up.
parcel.py:97 — velocity_per_month: float non-nullable, в сервисе COALESCE(v.velocity_per_month, 0.0). Consistent но добавь короткий comment про COALESCE guarantee.

Positive

All SQL через text() с bound params
try/except Exception wraps service call в endpoint → no exc leak в 500
Graceful degradation на avg_price failure (warning log, continue)
Velocity coverage 2.5% documented в docstring + COALESCE → 0.0 для unmapped objects
9 mock tests cover 404, empty, exclude, is_active, sold_pct=null
No Any в public response models

Recommended action

Merge approved. Follow-up issue: ST_DWithin radius pre-filter before scaling.

## Auto-review — verdict: approve ✅ CI ✅ (backend 1m12s + frontend 2m14s). ### Schema verification: ALL 4 claims CORRECT - `snapshot_date` в `latest_obj` CTE ✅ - `flat_count` → mapped `flats_total`, sold/pct=null ✅ - Velocity migration `objective_corpus_room_month` + `objective_complex_mapping` JOIN ✅ (паттерн как `velocity.py`) - `status = 'sold'` в `_AVG_PRICE_SQL` ✅ ### Security: clean - `cad_num`, `obj_class_filter`, `radius_m` — all через bound params - `exclude_obj_ids` фильтруется в Python (set), не SQL - `window_interval` строится из hardcoded dict (`time_window_months: float`), не user input - No injection vectors ### Minor (не блокирующие, fix later) 1. **`competitors.py:244`** — `distances` CTE computes ST_Distance для ВСЕХ rows в `latest_obj`, фильтрует `WHERE distance_m <= :radius_m` в outer SELECT. Без `ST_DWithin` pre-filter — full-table scan на каждый запрос. Phase 1 acceptable (dataset bounded), но для Phase 2/scaling → заменить distances CTE на `ST_DWithin(geog, ref_point::geography, :radius_m)` — index-assisted. 2. **`competitors.py:408`** — поле `weighted_avg_velocity` это арифметическое mean (total / n), не weighted. Misleading name. Rename → `avg_velocity_per_month` в follow-up. 3. **`parcel.py:97`** — `velocity_per_month: float` non-nullable, в сервисе `COALESCE(v.velocity_per_month, 0.0)`. Consistent но добавь короткий comment про COALESCE guarantee. ### Positive - All SQL через `text()` с bound params - `try/except Exception` wraps service call в endpoint → no exc leak в 500 - Graceful degradation на avg_price failure (warning log, continue) - Velocity coverage 2.5% documented в docstring + COALESCE → 0.0 для unmapped objects - 9 mock tests cover 404, empty, exclude, is_active, sold_pct=null - No `Any` в public response models ### Recommended action **Merge approved**. Follow-up issue: `ST_DWithin` radius pre-filter before scaling. ---