lekss361/gendesign

Fork 0

fix(scrapers): deactivate pre-#753 ghost duplicate listings (dedup correctness) #1557

Merged

lekss361 merged 1 commit from fix/deactivate-ghost-duplicate-listings into main

2026-06-16 12:59:47 +00:00

lekss361 commented

2026-06-16 12:41:15 +00:00

Owner

Summary

One-shot data-correctness migration 113_deactivate_ghost_duplicate_listings.sql.

Before #753, dedup_hash included the price, so each price change spawned a new row with a different hash. The current scraper upserts the canonical row (ON CONFLICT (dedup_hash), hash = sha256(source||'|'||source_id)), but the old "ghost" rows keep their price-inclusive hash, are never matched again, have frozen last_seen_at, and stay is_active=true forever — inflating active-listing counts (estimator/coverage/asking-to-sold denominators).

This migration deactivates, within each (source, source_id) group that has >1 rows AND at least one canonical-hash row, the active rows whose hash is not canonical. Groups with no canonical row (35 tiny all-ghost groups) are excluded via the HAVING filter — deactivating them would wipe the whole group.

Verification (prod, rolled-back transaction, 2026-06-16)

BEGIN
UPDATE 167          -- yandex ~164, cian ~1, n1 ~2, avito 0
ROLLBACK

avito ghosts are already inactive (deactivate_stale_avito); cian ghosts already mostly inactive; yandex has no deactivation yet so its ghosts are the bulk. dedup_hash has a UNIQUE constraint, so at most one canonical row per group.

Why safe

BEGIN/COMMIT atomic; idempotent (AND is_active=true → re-run is a no-op).
The current scraper computes the canonical hash, so re-scrapes hit the canonical row, never the deactivated ghost → ghosts will not be revived by the is_active=true ON CONFLICT clause.
merged_into deliberately not set (column is dead — no code writer).
No application code changes.

Test plan

Prod dry-run (ROLLBACK) → UPDATE 167, matches estimate
Post-deploy: confirm yandex active count drops by ~164 and no canonical rows lost

Refs #753

## Summary One-shot data-correctness migration `113_deactivate_ghost_duplicate_listings.sql`. Before #753, `dedup_hash` included the price, so each price change spawned a **new** row with a different hash. The current scraper upserts the canonical row (`ON CONFLICT (dedup_hash)`, hash = `sha256(source||'|'||source_id)`), but the old "ghost" rows keep their price-inclusive hash, are never matched again, have frozen `last_seen_at`, and stay `is_active=true` forever — inflating active-listing counts (estimator/coverage/asking-to-sold denominators). This migration deactivates, within each `(source, source_id)` group that has **>1 rows AND at least one canonical-hash row**, the active rows whose hash is **not** canonical. Groups with **no** canonical row (35 tiny all-ghost groups) are excluded via the `HAVING` filter — deactivating them would wipe the whole group. ## Verification (prod, rolled-back transaction, 2026-06-16) ``` BEGIN UPDATE 167 -- yandex ~164, cian ~1, n1 ~2, avito 0 ROLLBACK ``` avito ghosts are already inactive (`deactivate_stale_avito`); cian ghosts already mostly inactive; yandex has no deactivation yet so its ghosts are the bulk. `dedup_hash` has a UNIQUE constraint, so at most one canonical row per group. ## Why safe - `BEGIN/COMMIT` atomic; idempotent (`AND is_active=true` → re-run is a no-op). - The current scraper computes the **canonical** hash, so re-scrapes hit the canonical row, never the deactivated ghost → ghosts will **not** be revived by the `is_active=true` ON CONFLICT clause. - `merged_into` deliberately not set (column is dead — no code writer). - No application code changes. ## Test plan - [x] Prod dry-run (ROLLBACK) → UPDATE 167, matches estimate - [ ] Post-deploy: confirm yandex active count drops by ~164 and no canonical rows lost Refs #753

lekss361 added 1 commit 2026-06-16 12:41:16 +00:00

fix(db): deactivate pre-#753 ghost duplicate listings (167 rows)

CI / changes (push) Successful in 8s

Details

CI / backend-tests (push) Has been skipped

Details

CI / frontend-tests (push) Has been skipped

Details

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

Details

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

6b7391ba93

Before #753 dedup_hash included price, so each price change created a new
active row. The canonical rows are now upserted correctly; ghost rows sit
frozen with stale hashes. Deactivates only active non-canonical rows in
groups that already have a canonical twin, excluding all-ghost groups.

Dry-run on prod (2026-06-16): UPDATE 167 rows as expected (yandex ~164,
cian ~1, n1 ~2, avito 0).

Refs #753

lekss361 added the

bug

scrapers

labels 2026-06-16 12:41:25 +00:00

lekss361 merged commit 496af12c9c into main

2026-06-16 12:59:47 +00:00

lekss361 deleted branch fix/deactivate-ghost-duplicate-listings

2026-06-16 12:59:47 +00:00

lekss361 referenced this pull request from a commit

2026-06-16 12:59:49 +00:00

Merge pull request 'fix(scrapers): deactivate pre-#753 ghost duplicate listings (dedup correctness)' (#1557) from fix/deactivate-ghost-duplicate-listings into main

lekss361 commented

2026-06-16 16:04:29 +00:00

Author

Owner

Deep review (post-hoc — PR was already squash-merged by lekss361 at 12:59Z; not re-merged).

Verdict: APPROVE. Migration is correct, idempotent, atomic, and verified clean on prod.

Checks against the landed file (merge commit 496af12c) and live prod DB:

Idempotency: re-ran the UPDATE in a rolled-back tx → 0 rows (already applied 13:02:22Z; AND is_active=true guard → re-run is a no-op). Confirmed.
HAVING filter: 35 all-ghost groups (no canonical row) correctly untouched; 0 active non-canonical rows remain inside canonical groups. Whole-group-wipe footgun correctly avoided.
113_ prefix collision with 113_yandex_detail_backfill.sql: NOT a problem — _schema_migrations keys on full filename (PK), both applied independently (11:20Z / 13:02Z). Same accepted pattern as the existing two 108_* files. Minor convention smell only.
No revival risk: migration's canonical formula sha256(source||'|'||source_id) is scoped via WHERE source_id IS NOT NULL — exactly the subset where it equals the scraper's compute_dedup_hash (key=source_id when present). Re-scrapes upsert the canonical row via ON CONFLICT, never the deactivated ghost. source_id-NULL (Yandex URL-fallback) rows untouched. Correct.
::bytea casts fine in raw SQL migration context.
Strict-gate deploy succeeded; _schema_migrations row present.

No blockers.

Deep review (post-hoc — PR was already squash-merged by lekss361 at 12:59Z; not re-merged). **Verdict: APPROVE.** Migration is correct, idempotent, atomic, and verified clean on prod. Checks against the landed file (merge commit 496af12c) and live prod DB: - **Idempotency**: re-ran the UPDATE in a rolled-back tx → 0 rows (already applied 13:02:22Z; `AND is_active=true` guard → re-run is a no-op). Confirmed. - **HAVING filter**: 35 all-ghost groups (no canonical row) correctly untouched; 0 active non-canonical rows remain inside canonical groups. Whole-group-wipe footgun correctly avoided. - **113_ prefix collision** with `113_yandex_detail_backfill.sql`: NOT a problem — `_schema_migrations` keys on full filename (PK), both applied independently (11:20Z / 13:02Z). Same accepted pattern as the existing two `108_*` files. Minor convention smell only. - **No revival risk**: migration's canonical formula `sha256(source||'|'||source_id)` is scoped via `WHERE source_id IS NOT NULL` — exactly the subset where it equals the scraper's `compute_dedup_hash` (key=source_id when present). Re-scrapes upsert the canonical row via ON CONFLICT, never the deactivated ghost. source_id-NULL (Yandex URL-fallback) rows untouched. Correct. - `::bytea` casts fine in raw SQL migration context. - Strict-gate deploy succeeded; `_schema_migrations` row present. No blockers.