fix(tradein): plain natural-key dedup_hash for rosreestr deals (#576) #638

Merged

Light1YT merged 1 commit from fix/576-dedup-hash-plain-key into main

2026-05-29 06:56:35 +00:00

Light1YT commented

2026-05-29 06:55:43 +00:00

Owner

Summary

Closes #576. rosreestr ДКП deals used dedup_hash = md5('ros:dkp:' || id) — a hash of an already-unique natural key: collision-prone (theoretically), not human-readable, and irreversible (couldn't recover the source id, which mattered because deals.source_id was never populated).

Replace with the plain natural key 'ros:dkp:' || id (injective, zero-collision, reversible) and populate deals.source_id going forward — in both the live scheduler import (import_rosreestr_dkp) and the legacy deploy/import-rosreestr.sh.

Migration 077 backfills the existing 49,791 rows: recovers the original id from FDW foreign table gendesign_rosreestr_deals by matching the old md5, then writes plain key + source_id. Idempotent; plain keys and md5 hex are format-disjoint → no transient UNIQUE violation on deals_dedup_hash_key. Runs exactly-once under the new _schema_migrations gate (PR #637).

Reviewed by code-reviewer: ✅ approve, no blockers (full cross-file impact check — nothing reads deals.dedup_hash as md5 or deals.source_id; estimator _fetch_deals filters by geo/rooms/area/date only, unaffected).

Test plan

pytest 13 passed (8 new static-SQL asserts + 5 scheduler) · ruff clean · py_compile clean · bash -n clean
Prod dry-run of migration 077 (ROLLBACK tx): UPDATE 49791 → md5_after=0, plain_after=49791, source_id_filled=49791, 3.85s with FDW join
Post-merge: deploy applies 077 once (tracked); verify SELECT count(*) FROM deals WHERE source='rosreestr' AND dedup_hash ~ '^[0-9a-f]{32}$' = 0; next rosreestr_dkp_import inserts plain-keyed without dupes

Closes #576

## Summary Closes #576. rosreestr ДКП deals used `dedup_hash = md5('ros:dkp:' || id)` — a hash of an already-unique natural key: collision-prone (theoretically), not human-readable, and irreversible (couldn't recover the source id, which mattered because `deals.source_id` was never populated). Replace with the **plain natural key** `'ros:dkp:' || id` (injective, zero-collision, reversible) and populate `deals.source_id` going forward — in both the live scheduler import (`import_rosreestr_dkp`) and the legacy `deploy/import-rosreestr.sh`. **Migration 077** backfills the existing 49,791 rows: recovers the original id from FDW foreign table `gendesign_rosreestr_deals` by matching the old md5, then writes plain key + source_id. Idempotent; plain keys and md5 hex are format-disjoint → no transient UNIQUE violation on `deals_dedup_hash_key`. Runs exactly-once under the new `_schema_migrations` gate (PR #637). Reviewed by code-reviewer: ✅ approve, no blockers (full cross-file impact check — nothing reads `deals.dedup_hash` as md5 or `deals.source_id`; estimator `_fetch_deals` filters by geo/rooms/area/date only, unaffected). ## Test plan - [x] pytest 13 passed (8 new static-SQL asserts + 5 scheduler) · ruff clean · py_compile clean · `bash -n` clean - [x] **Prod dry-run of migration 077 (ROLLBACK tx):** `UPDATE 49791` → `md5_after=0, plain_after=49791, source_id_filled=49791`, 3.85s with FDW join - [ ] Post-merge: deploy applies 077 once (tracked); verify `SELECT count(*) FROM deals WHERE source='rosreestr' AND dedup_hash ~ '^[0-9a-f]{32}$'` = 0; next rosreestr_dkp_import inserts plain-keyed without dupes Closes #576

Light1YT added 1 commit 2026-05-29 06:55:44 +00:00

fix(tradein): plain natural-key dedup_hash for rosreestr deals (#576 ) b51893f7c3

rosreestr ДКП deals used dedup_hash = md5('ros:dkp:' || id) — a hash of
an already-unique natural key. The md5 is collision-prone (theoretically),
not human-readable, and irreversible (can't recover the source id from it,
which mattered because deals.source_id was never populated).

Replace with the plain natural key 'ros:dkp:' || id (injective, zero
collision, reversible) and populate deals.source_id going forward, in both
the live scheduler import (import_rosreestr_dkp) and the legacy
deploy/import-rosreestr.sh.

Migration 077 backfills the existing 49,791 rows: recovers the original id
from FDW foreign table gendesign_rosreestr_deals by matching the old md5,
then writes the plain key + source_id. Idempotent; plain keys and md5 hex
are format-disjoint so no transient UNIQUE violation on deals_dedup_hash_key.
Verified via prod dry-run (ROLLBACK): UPDATE 49791, 0 md5 remaining, 3.85s.

Closes #576