feat(tradein): bulk sweep CLI for Yandex valuation house-history (EKB) #533

Merged

lekss361 merged 1 commit from feat/tradein-yandex-valuation-bulk-sweep into main

2026-05-24 14:37:25 +00:00

lekss361 commented

2026-05-24 14:33:26 +00:00

Owner

Summary

New tradein-mvp/scripts/sweep-yandex-valuation-ekb.py
Iterates DISTINCT addresses from listings (source='yandex' AND region_code=66 AND is_active)
Calls existing estimator helpers: page=1 uses 24h cache via _get_or_fetch_yandex_valuation_cached; pages 2..N go direct via YandexValuationScraper.fetch_house_history
Idempotent — skips addresses with external_valuations.fetched_at < N days

CLI

--limit            cap addresses (smoke testing)
--max-pages 12     Yandex pagination cap per address
--delay 5          sec between page requests within one address
--address-delay 15 sec between addresses
--skip-recent-days 1   skip already-cached recently (0 = force re-scrape)
--address "..."    single-address mode

Why now

With PRs #526 (parser fixes — removed_date, total_floors, NBSP) and #531 (house_id linking via match_or_create_house) merged, one sweep will populate house_placement_history with properly linked + complete rows. The current 160 orphan rows on prod (house_id=NULL, removed_date=NULL, total_floors=NULL) will be superseded as fresh runs hit those addresses again.

Smoke validated

ruff lint + format clean
--help parses, all CLI args wire correctly
Imports resolve (weasyprint stub + sys.path injection mirrors local-sweep-ekb-yandex.py)

How to run (operator step, separate from this PR)

ssh -f -N -L 15433:172.20.0.2:5432 gendesign
cd tradein-mvp/backend
export DATABASE_URL='postgresql+psycopg://tradein:<PASS>@localhost:15433/tradein'
uv run python ../scripts/sweep-yandex-valuation-ekb.py --limit 5   # smoke
uv run python ../scripts/sweep-yandex-valuation-ekb.py             # full

## Summary - New `tradein-mvp/scripts/sweep-yandex-valuation-ekb.py` - Iterates DISTINCT addresses from `listings` (source='yandex' AND region_code=66 AND is_active) - Calls existing estimator helpers: page=1 uses 24h cache via `_get_or_fetch_yandex_valuation_cached`; pages 2..N go direct via `YandexValuationScraper.fetch_house_history` - Idempotent — skips addresses with `external_valuations.fetched_at < N days` ## CLI ``` --limit cap addresses (smoke testing) --max-pages 12 Yandex pagination cap per address --delay 5 sec between page requests within one address --address-delay 15 sec between addresses --skip-recent-days 1 skip already-cached recently (0 = force re-scrape) --address "..." single-address mode ``` ## Why now With PRs #526 (parser fixes — removed_date, total_floors, NBSP) and #531 (house_id linking via `match_or_create_house`) merged, one sweep will populate `house_placement_history` with properly linked + complete rows. The current 160 orphan rows on prod (`house_id=NULL`, `removed_date=NULL`, `total_floors=NULL`) will be superseded as fresh runs hit those addresses again. ## Smoke validated - ruff lint + format clean - `--help` parses, all CLI args wire correctly - Imports resolve (weasyprint stub + sys.path injection mirrors `local-sweep-ekb-yandex.py`) ## How to run (operator step, separate from this PR) ```bash ssh -f -N -L 15433:172.20.0.2:5432 gendesign cd tradein-mvp/backend export DATABASE_URL='postgresql+psycopg://tradein:<PASS>@localhost:15433/tradein' uv run python ../scripts/sweep-yandex-valuation-ekb.py --limit 5 # smoke uv run python ../scripts/sweep-yandex-valuation-ekb.py # full ```

lekss361 added 1 commit 2026-05-24 14:33:27 +00:00

feat(tradein): bulk sweep CLI for Yandex valuation house-history (EKB) 4f26720dc8

- New tradein-mvp/scripts/sweep-yandex-valuation-ekb.py
- Iterates DISTINCT addresses from listings (source='yandex' AND region_code=66)
- Calls existing estimator helpers (_get_or_fetch_yandex_valuation_cached +
  _save_yandex_history_items) — page=1 uses 24h cache; pages 2..N go direct
- Idempotent — skips addresses with external_valuations.fetched_at < N days
- CLI: --limit / --max-pages / --delay / --address-delay / --skip-recent-days /
  --address (single-address mode)
- Backfills the 160 orphan rows now that PR #526 (parser) + #531 (house_id link)
  are wired

lekss361 commented

2026-05-24 14:37:14 +00:00

Author

Owner

Deep Code Review — verdict ✅ APPROVE

Summary

Status: ✅ APPROVE
Files: 1 new (P2 — operator script, no service-code impact)
Lines: +258 / -0 · Diff scope: contained
Time to verdict: ~6 min

Cross-file impact verified

app.services.estimator._get_or_fetch_yandex_valuation_cached — signature matches script call (db, kw address, offer_category, offer_type). Function commits internally + writes external_valuations.address ✓ so the skip-recent-days query is sound.
app.services.estimator._save_yandex_history_items — idempotent via UNIQUE (source, ext_item_id), ON CONFLICT DO NOTHING. Re-runs are safe.
app.services.scrapers.yandex_valuation.YandexValuationScraper.fetch_house_history(address, offer_category, offer_type, page) — matches script's page>=2 path.
match_or_create_house already serializes via pg_advisory_xact_lock(42, hashtext(fp)) — concurrent sweeps for the SAME address won't dup-create houses.
external_valuations schema (sql/026): address column + partial idx ev_address_idx covers the NOT EXISTS correlated subquery in _addresses_to_sweep.

🟢 Strengths

Idempotent end-to-end (UNIQUE + skip-recent-days at address-selection level).
Graceful per-address try/except — one failure won't abort the run.
Random jitter ×0.85-1.3 on delays — respectful of Yandex throttling.
Reuses cached path for page=1 (cost saving) and direct scraper for page 2+.
CLI surface is well-scoped: --limit, --address, --skip-recent-days 0 for force-reseed, --max-pages cap.
Resumability is implicit: rerun with default --skip-recent-days 1 skips already-processed addresses.

🟡 Minor (informational, won't block)

tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:106 — _get_or_fetch_yandex_valuation_cached is _underscore (module-private). Re-using it from an operator script is fine but creates a soft coupling; consider promoting to public name in a follow-up if these helpers grow more callers.
tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:127 — early-break heuristic len(result.history_items) < 13 hardcodes Yandex page size. If Yandex changes page size, the sweep may stop early or burn extra page requests. Not a correctness bug; consider moving the magic number to a constant in yandex_valuation.py later.
--skip-recent-days 0 only bypasses the address-selection skip; page=1 still goes through _get_or_fetch_yandex_valuation_cached which honors the 24h cache TTL (expires_at > NOW()). So 0 does NOT force a Yandex fresh fetch on page 1 — it just re-saves already-cached items to house_placement_history. Behavior is fine + safe, but the help text could be clearer ("0 = no address-level skip; cache TTL still applies to page=1 fetch").
tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:122 — broad except Exception: break on per-page failure means a transient 429 on page 1 abandons the address entirely. Could be improved with one-shot retry, but operator can re-run with --skip-recent-days 1 and the address will be retried. Acceptable for a manual script.

🔒 Security: clean

All SQL uses parameterized text() bindparams.
No credentials in file; setup block uses <PASS> placeholder.
No new public endpoints.

⚡ Performance: fine

Sequential single-process design; ~75-90 s/address × ~hundreds = 2-3 h total. Operator-acceptable.
SELECT DISTINCT address ... NOT EXISTS ev covered by ev_address_idx partial index.

🧪 Tests

No tests added. Consistent with sibling scripts/local-sweep-ekb-*.py — operator scripts are smoke-validated manually. Not blocking.

Conventions

psycopg v3 + SQLAlchemy text() + named bindparams ✓
from __future__ import annotations ✓
Lives next to peers in tradein-mvp/scripts/ ✓
Reuses weasyprint-stub + sys.path injection pattern from local-sweep-ekb-yandex.py ✓

Recommended next steps (after merge)

Operator runs --limit 5 smoke on prod (per PR body).
After full sweep: spot-check house_placement_history for the 160 orphan rows → expect new rows with house_id filled.
Schedule via systemd-timer / cron later if recurring sweeps prove valuable.

Complexity / blast radius

Risk: very low (standalone script, no API/migration/cron wiring)
Reversibility: trivially revertable
Merge window: any

## Deep Code Review — verdict ✅ APPROVE ### Summary - Status: ✅ APPROVE - Files: 1 new (P2 — operator script, no service-code impact) - Lines: +258 / -0 · Diff scope: contained - Time to verdict: ~6 min ### Cross-file impact verified - `app.services.estimator._get_or_fetch_yandex_valuation_cached` — signature matches script call (`db`, kw `address`, `offer_category`, `offer_type`). Function commits internally + writes `external_valuations.address` ✓ so the `skip-recent-days` query is sound. - `app.services.estimator._save_yandex_history_items` — idempotent via `UNIQUE (source, ext_item_id)`, `ON CONFLICT DO NOTHING`. Re-runs are safe. - `app.services.scrapers.yandex_valuation.YandexValuationScraper.fetch_house_history(address, offer_category, offer_type, page)` — matches script's page>=2 path. - `match_or_create_house` already serializes via `pg_advisory_xact_lock(42, hashtext(fp))` — concurrent sweeps for the SAME address won't dup-create houses. - `external_valuations` schema (sql/026): `address` column + partial idx `ev_address_idx` covers the `NOT EXISTS` correlated subquery in `_addresses_to_sweep`. ### 🟢 Strengths - Idempotent end-to-end (UNIQUE + skip-recent-days at address-selection level). - Graceful per-address `try/except` — one failure won't abort the run. - Random jitter ×0.85-1.3 on delays — respectful of Yandex throttling. - Reuses cached path for page=1 (cost saving) and direct scraper for page 2+. - CLI surface is well-scoped: `--limit`, `--address`, `--skip-recent-days 0` for force-reseed, `--max-pages` cap. - Resumability is implicit: rerun with default `--skip-recent-days 1` skips already-processed addresses. ### 🟡 Minor (informational, won't block) 1. `tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:106` — `_get_or_fetch_yandex_valuation_cached` is `_underscore` (module-private). Re-using it from an operator script is fine but creates a soft coupling; consider promoting to public name in a follow-up if these helpers grow more callers. 2. `tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:127` — early-break heuristic `len(result.history_items) < 13` hardcodes Yandex page size. If Yandex changes page size, the sweep may stop early or burn extra page requests. Not a correctness bug; consider moving the magic number to a constant in `yandex_valuation.py` later. 3. `--skip-recent-days 0` only bypasses the address-selection skip; page=1 still goes through `_get_or_fetch_yandex_valuation_cached` which honors the 24h cache TTL (`expires_at > NOW()`). So `0` does NOT force a Yandex fresh fetch on page 1 — it just re-saves already-cached items to `house_placement_history`. Behavior is fine + safe, but the help text could be clearer ("0 = no address-level skip; cache TTL still applies to page=1 fetch"). 4. `tradein-mvp/scripts/sweep-yandex-valuation-ekb.py:122` — broad `except Exception: break` on per-page failure means a transient 429 on page 1 abandons the address entirely. Could be improved with one-shot retry, but operator can re-run with `--skip-recent-days 1` and the address will be retried. Acceptable for a manual script. ### 🔒 Security: clean - All SQL uses parameterized `text()` bindparams. - No credentials in file; setup block uses `<PASS>` placeholder. - No new public endpoints. ### ⚡ Performance: fine - Sequential single-process design; ~75-90 s/address × ~hundreds = 2-3 h total. Operator-acceptable. - `SELECT DISTINCT address ... NOT EXISTS ev` covered by `ev_address_idx` partial index. ### 🧪 Tests No tests added. Consistent with sibling `scripts/local-sweep-ekb-*.py` — operator scripts are smoke-validated manually. Not blocking. ### Conventions - psycopg v3 + SQLAlchemy `text()` + named bindparams ✓ - `from __future__ import annotations` ✓ - Lives next to peers in `tradein-mvp/scripts/` ✓ - Reuses `weasyprint`-stub + `sys.path` injection pattern from `local-sweep-ekb-yandex.py` ✓ ### Recommended next steps (after merge) 1. Operator runs `--limit 5` smoke on prod (per PR body). 2. After full sweep: spot-check `house_placement_history` for the 160 orphan rows → expect new rows with `house_id` filled. 3. Schedule via systemd-timer / cron later if recurring sweeps prove valuable. ### Complexity / blast radius - Risk: very low (standalone script, no API/migration/cron wiring) - Reversibility: trivially revertable - Merge window: any