feat(tradein): recurring house-dedup merge (schedule dormant) (#1772) #1933

Merged

bot-backend merged 2 commits from feat/1772-recurring-house-merge into main

2026-06-26 21:39:31 +00:00

bot-backend commented

2026-06-26 21:24:43 +00:00

Collaborator

Recurring house-dedup merge (#1772)

One physical building splits into multiple houses rows → same-building comps lost. Migration 108_merge_duplicate_houses.sql did a ONE-TIME merge, but duplicates recur (the matching pipeline keeps creating them: Tier-3 geo-jitter + per-source ext_id INSERTs). A migration can't fix a steady-state inflow → this is a RECURRING, idempotent, collision-safe merge.

Reuses 108's proven collision-safe pattern

cluster → canonical keeper → re-point children with UNIQUE-collision handling → delete losers → backfill sources/aliases, all in ONE transaction.

Cluster key: EXACT houses.address — cadastral_number is 100% NULL on prod (migration 040), so address is the real building key.
Keeper rule: geom NOT NULL → most linked listings → most-populated fields → min(id).
ALL FK children handled (the migration-133 lesson — re-audited REFERENCES houses against the live schema; same 11 children as 108, none added after):
- listings.house_id_fk, house_placement_history, house_reviews, house_reliability_checks, external_valuations → plain re-point (no blocking UNIQUE)
- house_sources UNIQUE(ext_source,ext_id) → delete colliding losers, re-point rest
- house_address_aliases UNIQUE(normalized_address) → delete colliding, re-point rest
- houses_price_dynamics → dedup by the LIVE 6-col UNIQUE (house_id, source, room_count, prices_type, period, month_date) — migration 029 replaced 108's stale 3-col key; deduping on the old key would still violate on re-point
- house_imv_evaluations UNIQUE(house_id) → dedup, re-point
- house_suggestions UNIQUE(house_id,ext_item_id) → dedup, re-point
- address_mismatch_audit UNIQUE(house_id,audit_batch) → dedup, re-point
- All dedups partition on the TARGET keeper so dup-vs-dup collisions in clusters >2 are caught.

Schedule DISABLED by default (deploy neutral)

Migration 135 seeds scrape_schedules house_dedup_merge enabled=false (DORMANT). This is an auto-recurring DESTRUCTIVE merge — deploy stays neutral. The orchestrator dry-runs + does one validated manual run, then enables deliberately.

dry_run + idempotent

dry_run=True computes counts then ROLLBACK (no writes).
Idempotent: clean table → empty mapping → every statement is a 0-row no-op.
Audit-logs every loser→keeper move with its address.

Verify

uv run pytest -k "dedup or merge or house" -q → 25 passed, 1 skipped (real-DB test self-skips).
Full suite (deselects per spec) → 2444 passed, 2 skipped, 2 deselected, 0 failed.
uv run ruff check clean on all changed files.

Files

app/services/house_dedup_merge.py (service + run-lifecycle wrapper)
app/services/scheduler.py (trigger + dispatch + docstring)
data/sql/135_scrape_schedules_seed_house_dedup_merge.sql (seed, enabled=false)
tests/test_house_dedup_merge.py

## Recurring house-dedup merge (#1772) One physical building splits into multiple `houses` rows → same-building comps lost. Migration `108_merge_duplicate_houses.sql` did a ONE-TIME merge, but duplicates **recur** (the matching pipeline keeps creating them: Tier-3 geo-jitter + per-source ext_id INSERTs). A migration can't fix a steady-state inflow → this is a **RECURRING, idempotent, collision-safe** merge. ### Reuses 108's proven collision-safe pattern `cluster → canonical keeper → re-point children with UNIQUE-collision handling → delete losers → backfill sources/aliases`, all in ONE transaction. - **Cluster key:** EXACT `houses.address` — cadastral_number is 100% NULL on prod (migration 040), so address is the real building key. - **Keeper rule:** geom NOT NULL → most linked listings → most-populated fields → min(id). - **ALL FK children handled** (the migration-133 lesson — re-audited `REFERENCES houses` against the live schema; same 11 children as 108, none added after): - `listings.house_id_fk`, `house_placement_history`, `house_reviews`, `house_reliability_checks`, `external_valuations` → plain re-point (no blocking UNIQUE) - `house_sources` UNIQUE(ext_source,ext_id) → delete colliding losers, re-point rest - `house_address_aliases` UNIQUE(normalized_address) → delete colliding, re-point rest - `houses_price_dynamics` → dedup by the **LIVE 6-col** UNIQUE (house_id, source, room_count, prices_type, period, month_date) — **migration 029 replaced 108's stale 3-col key**; deduping on the old key would still violate on re-point - `house_imv_evaluations` UNIQUE(house_id) → dedup, re-point - `house_suggestions` UNIQUE(house_id,ext_item_id) → dedup, re-point - `address_mismatch_audit` UNIQUE(house_id,audit_batch) → dedup, re-point - All dedups partition on the TARGET keeper so dup-vs-dup collisions in clusters >2 are caught. ### Schedule DISABLED by default (deploy neutral) Migration `135` seeds `scrape_schedules` `house_dedup_merge` **enabled=false** (DORMANT). This is an auto-recurring DESTRUCTIVE merge — deploy stays neutral. The orchestrator dry-runs + does one validated manual run, then enables deliberately. ### dry_run + idempotent - `dry_run=True` computes counts then **ROLLBACK** (no writes). - Idempotent: clean table → empty mapping → every statement is a 0-row no-op. - Audit-logs every loser→keeper move with its address. ### Verify - `uv run pytest -k "dedup or merge or house" -q` → 25 passed, 1 skipped (real-DB test self-skips). - **Full suite** (deselects per spec) → **2444 passed, 2 skipped, 2 deselected, 0 failed**. - `uv run ruff check` clean on all changed files. ### Files - `app/services/house_dedup_merge.py` (service + run-lifecycle wrapper) - `app/services/scheduler.py` (trigger + dispatch + docstring) - `data/sql/135_scrape_schedules_seed_house_dedup_merge.sql` (seed, enabled=false) - `tests/test_house_dedup_merge.py`

bot-backend added 1 commit 2026-06-26 21:24:44 +00:00

feat(tradein): recurring house-dedup merge (schedule dormant) (#1772 )

CI / changes (pull_request) Successful in 6s

Details

CI / backend-tests (pull_request) Has been skipped

Details

CI / frontend-tests (pull_request) Has been skipped

Details

CI / openapi-codegen-check (pull_request) Has been skipped

Details

fa5e5d83f1

Reuses migration 108's proven collision-safe pattern
(cluster → canonical keeper → re-point children with UNIQUE-collision
handling → delete losers → backfill sources/aliases) as a RECURRING,
idempotent scheduled job — dups recur because the matching pipeline keeps
creating them, so a one-time migration cannot fix the steady-state inflow.

- Service app/services/house_dedup_merge.py: merge_duplicate_houses(db, *,
  dry_run=False). Clusters houses by EXACT address (cadastral 100% NULL →
  address is the real key). Keeper: geom NOT NULL → most listings →
  most-populated → min(id). Handles ALL 11 FK children incl. live 6-col
  houses_price_dynamics UNIQUE (migration 029 replaced 108's stale 3-col).
  One transaction; dry_run computes counts then ROLLBACK; idempotent
  (clean table → empty mapping → 0-row no-op). Audit-logs every merge.
- Scheduler: trigger_house_dedup_merge_run (run_in_executor, sync DB-only) +
  dispatch branch + run_house_dedup_merge lifecycle wrapper.
- Migration 135: seeds scrape_schedules house_dedup_merge enabled=FALSE
  (DORMANT — deploy neutral; enable deliberately after dry-run + manual run),
  weekly window, ON CONFLICT DO NOTHING.
- Tests: static SQL-shape (every FK child + collision handling, migration-133
  guard), fake-db dry_run/idempotent, scheduler wiring, optional real-DB
  end-to-end merge test (self-skips without a DB).

Light1YT referenced this pull request from a commit

2026-06-26 21:38:19 +00:00

test(tradein): fix real-DB merge test schema (#1772, #1933 review)

Light1YT added 1 commit 2026-06-26 21:38:20 +00:00

test(tradein): fix real-DB merge test schema (#1772 , #1933 review)

CI / changes (pull_request) Successful in 7s

Details

CI / backend-tests (pull_request) Has been skipped

Details

CI / frontend-tests (pull_request) Has been skipped

Details

CI / openapi-codegen-check (pull_request) Has been skipped

Details

70ca65b62b

The optional real-DB end-to-end test self-skipped without a DB but would
have errored WITH one (the safety fixture never actually ran). Fixed two
schema bugs + one impossible-collision setup, validated against a live
PostGIS 16 container (test now passes end-to-end, not just collected):

- houses_price_dynamics insert used a non-existent `avg_price` column →
  `price_per_sqm` (the real NOT NULL value column, migration 024). The two
  rows share all 5 dim fields with differing house_id → a genuine LIVE
  6-col UNIQUE collision the dedup step must resolve. Also assert the
  surviving row is the keeper's own (price_per_sqm=100000, not 999999).
- listings insert omitted NOT NULL no-default columns → added source_url,
  dedup_hash (also UNIQUE → distinct per row), price_rub (migration 002).
- house_sources UNIQUE(ext_source,ext_id) is GLOBAL, so the original
  same-key-on-both-houses setup violated at INSERT time (before the merge).
  Replaced with distinct loser/keeper source rows + assert the loser's row
  re-points onto the keeper and none dangle on the deleted loser.

Still self-skips in CI (no Postgres) — correct; now valid against a
tunnel/local DB. Full suite 2444 passed / 0 failed; ruff clean.