fix(tradein): 030 — UNIQUE constraint on avito_imv_evaluations.cache_key (unblocks PR #452) #454

Merged

lekss361 merged 1 commit from fix/tradein-imv-cache-key-unique into main

2026-05-23 13:04:22 +00:00

lekss361 commented

2026-05-23 12:59:28 +00:00

Owner

Summary

Hotfix миграции 018 (Stage 1 of Avito Scraper v2). Закрывает 🔴 blocker от deep-code-review на PR #452 (#452 (comment)).

Problem

Миграция 018_avito_imv_evaluations.sql создала только non-unique imv_cache_key_idx. Но save_imv_evaluation (avito_imv.py:481) использует:

ON CONFLICT (cache_key) DO UPDATE SET ...

Это требует UNIQUE/exclusion constraint. Иначе runtime error:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Live impact: первый POST /api/v1/trade-in/estimate с IMV-compatible payload → save_imv_evaluation fails → caught by graceful except Exception в _get_or_fetch_imv_cached → cache never populates → каждый subsequent call re-fetches от Avito (rate-limit risk + 2-5s latency).

Fix

Новая миграция 030_avito_imv_cache_key_unique.sql:

BEGIN;
-- Защитная очистка дублей (на свежей БД no-op)
DELETE FROM avito_imv_evaluations a
USING avito_imv_evaluations b
WHERE a.id < b.id AND a.cache_key = b.cache_key;

CREATE UNIQUE INDEX IF NOT EXISTS imv_cache_key_uniq_idx
    ON avito_imv_evaluations (cache_key);
COMMIT;

Идемпотентно (IF NOT EXISTS)
Без CONCURRENTLY — таблица свежая (cache_key никогда не записывался до PR #452)
DELETE дублей защитно (на свежей БД пусто)

Test plan

Migration syntactically valid (idempotent ALTER pattern)
Deploy auto-apply через GHA → verify pg_indexes содержит imv_cache_key_uniq_idx
После merge этого PR → unblock PR #452 (Stage 3 IMV estimator)
После merge #452 → live smoke: POST /api/v1/trade-in/estimate для ЕКБ address → verify logs imv: fresh recommended=... (first call) и imv: cache HIT (re-call)

Refs

Reviewer comment: PR #452 issuecomment-2517 (deep-code-reviewer)
Original migration: 018_avito_imv_evaluations.sql (Stage 1, PR #440 merged)
Blocked PR: #452 (Stage 3 — будет re-mergeable после этого fix)

## Summary Hotfix миграции 018 (Stage 1 of Avito Scraper v2). Закрывает 🔴 **blocker** от deep-code-review на PR #452 (https://git.gendsgn.ru/lekss361/gendesign/pulls/452#issuecomment-2517). ## Problem Миграция `018_avito_imv_evaluations.sql` создала **только non-unique** `imv_cache_key_idx`. Но `save_imv_evaluation` (`avito_imv.py:481`) использует: ```sql ON CONFLICT (cache_key) DO UPDATE SET ... ``` Это требует UNIQUE/exclusion constraint. Иначе runtime error: ``` ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ``` **Live impact**: первый `POST /api/v1/trade-in/estimate` с IMV-compatible payload → `save_imv_evaluation` fails → caught by graceful `except Exception` в `_get_or_fetch_imv_cached` → cache never populates → каждый subsequent call re-fetches от Avito (rate-limit risk + 2-5s latency). ## Fix Новая миграция `030_avito_imv_cache_key_unique.sql`: ```sql BEGIN; -- Защитная очистка дублей (на свежей БД no-op) DELETE FROM avito_imv_evaluations a USING avito_imv_evaluations b WHERE a.id < b.id AND a.cache_key = b.cache_key; CREATE UNIQUE INDEX IF NOT EXISTS imv_cache_key_uniq_idx ON avito_imv_evaluations (cache_key); COMMIT; ``` - Идемпотентно (`IF NOT EXISTS`) - Без `CONCURRENTLY` — таблица свежая (cache_key никогда не записывался до PR #452) - DELETE дублей защитно (на свежей БД пусто) ## Test plan - [x] Migration syntactically valid (idempotent ALTER pattern) - [ ] Deploy auto-apply через GHA → verify `pg_indexes` содержит `imv_cache_key_uniq_idx` - [ ] После merge этого PR → unblock PR #452 (Stage 3 IMV estimator) - [ ] После merge #452 → live smoke: POST /api/v1/trade-in/estimate для ЕКБ address → verify logs `imv: fresh recommended=...` (first call) и `imv: cache HIT` (re-call) ## Refs - Reviewer comment: PR #452 issuecomment-2517 (deep-code-reviewer) - Original migration: `018_avito_imv_evaluations.sql` (Stage 1, PR #440 merged) - Blocked PR: #452 (Stage 3 — будет re-mergeable после этого fix)

lekss361 added 1 commit 2026-05-23 12:59:29 +00:00

fix(tradein): 030 — UNIQUE constraint on avito_imv_evaluations.cache_key 1b6461eb15

Hotfix для unblock PR #452 (Stage 3 IMV estimator integration).

Миграция 018 (Stage 1) создала только non-unique imv_cache_key_idx —
но save_imv_evaluation использует ON CONFLICT (cache_key) DO UPDATE,
что требует UNIQUE/exclusion constraint, иначе:
  ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Этот PR:
- DELETE дублей по cache_key (защитно, на свежей БД no-op)
- CREATE UNIQUE INDEX IF NOT EXISTS imv_cache_key_uniq_idx ON avito_imv_evaluations(cache_key)

Idempotent: повторный apply — NOTICE, без ошибок.
Без CONCURRENTLY — таблица свежая (cache_key никогда не writev до PR #452).

Reviewer: deep-code-reviewer on PR #452.

lekss361 merged commit 0db71033b4 into main

2026-05-23 13:04:22 +00:00

lekss361 referenced this pull request from a commit

2026-05-23 13:04:23 +00:00

fix(tradein): 030 — UNIQUE constraint on avito_imv_evaluations.cache_key (unblocks PR #452) (#454)

lekss361 commented

2026-05-23 13:04:42 +00:00

Author

Owner

Merged via deep-code-reviewer — verdict APPROVE.

Verification summary:

DELETE/USING dedup correct (keeps MAX(id), no-op on empty table)
BEGIN/COMMIT atomic, IF NOT EXISTS idempotent
Numbering coexists with PR #453's 030-032 Yandex (different filenames, different tables, safe lexicographic apply order: 030_avito_imv_* runs before 030_listings_*)
Both imv_cache_key_idx (composite, lookup) and new imv_cache_key_uniq_idx (UNIQUE for ON CONFLICT) are complementary — keep both
avito_imv.py:498 ON CONFLICT (cache_key) now resolvable

Post-merge unblock chain for user:

deploy-tradein.yml triggers automatically (path tradein-mvp/** matches)
Apply log should show: → Migration: 030_avito_imv_cache_key_unique.sql (runs before 030_listings_alter_yandex.sql lexicographically)
Verify: SELECT indexname FROM pg_indexes WHERE tablename='avito_imv_evaluations' → imv_cache_key_uniq_idx present
Re-trigger PR #452 CI → merge once green (mergeable: true confirmed)
After #452 merge: live smoke POST /api/v1/trade-in/estimate twice within 24h → second call logs imv: cache HIT

Merged via deep-code-reviewer — verdict APPROVE. **Verification summary:** - DELETE/USING dedup correct (keeps MAX(id), no-op on empty table) - BEGIN/COMMIT atomic, IF NOT EXISTS idempotent - Numbering coexists with PR #453's 030-032 Yandex (different filenames, different tables, safe lexicographic apply order: `030_avito_imv_*` runs before `030_listings_*`) - Both `imv_cache_key_idx` (composite, lookup) and new `imv_cache_key_uniq_idx` (UNIQUE for ON CONFLICT) are complementary — keep both - `avito_imv.py:498` ON CONFLICT (cache_key) now resolvable **Post-merge unblock chain for user:** 1. `deploy-tradein.yml` triggers automatically (path `tradein-mvp/**` matches) 2. Apply log should show: `→ Migration: 030_avito_imv_cache_key_unique.sql` (runs before 030_listings_alter_yandex.sql lexicographically) 3. Verify: `SELECT indexname FROM pg_indexes WHERE tablename='avito_imv_evaluations'` → `imv_cache_key_uniq_idx` present 4. Re-trigger PR #452 CI → merge once green (mergeable: true confirmed) 5. After #452 merge: live smoke `POST /api/v1/trade-in/estimate` twice within 24h → second call logs `imv: cache HIT`

lekss361 referenced this pull request

2026-05-23 13:08:48 +00:00

feat(tradein): integrate Avito IMV as 5th evaluation source (on-demand cached) #452