fix(tradein-sql): rename dup 030, add trade_in_estimates geom, drop dup indexes #500

Merged

lekss361 merged 1 commit from feat/sql-rename-030-and-schema-debt into main

2026-05-24 10:04:41 +00:00

lekss361 commented

2026-05-24 10:00:36 +00:00

Owner

Summary

Close 3 SQL schema findings from 2026-05-24 trade-in audit:

#2 (blocker) rename 030_listings_alter_yandex.sql -> 033_* to resolve duplicate prefix with 030_avito_imv_cache_key_unique.sql (undefined apply order under ls | sort).
#15 new 034_trade_in_estimates_geom.sql: PostGIS geom column + GIST + trigger (schema-consistency vs .claude/rules/sql.md).
#14 new 035_drop_duplicate_indexes.sql: drop redundant listing_sources_listing_idx2 (= 028 _listing_idx) and houses_geom_idx2 (= 009 geom_idx).

All migrations idempotent (IF NOT EXISTS / CREATE OR REPLACE). Apply order:

030_avito_imv_cache_key_unique -> 031_houses_alter_yandex -> 032_yandex_history -> 033_listings_alter_yandex -> 034 -> 035 -> 040_...

DDL inversion (031 before 033) safe: 031 touches houses, 033 touches listings, no cross-table dep.

Test plan

Deploy applies cleanly to tradein-postgres (idempotent re-run of renamed file is no-op)
psql shows trade_in_estimates.geom geometry(Point,4326) + trade_in_estimates_geom_idx GIST
listing_sources_listing_idx2 gone, listing_sources_listing_idx (028) remains
houses_geom_idx2 gone, houses_geom_idx (009) remains
INSERT into trade_in_estimates with lat/lon auto-populates geom via trigger

## Summary Close 3 SQL schema findings from 2026-05-24 trade-in audit: - #2 (blocker) rename 030_listings_alter_yandex.sql -> 033_* to resolve duplicate prefix with 030_avito_imv_cache_key_unique.sql (undefined apply order under ls | sort). - #15 new 034_trade_in_estimates_geom.sql: PostGIS geom column + GIST + trigger (schema-consistency vs .claude/rules/sql.md). - #14 new 035_drop_duplicate_indexes.sql: drop redundant listing_sources_listing_idx2 (= 028 _listing_idx) and houses_geom_idx2 (= 009 geom_idx). All migrations idempotent (IF NOT EXISTS / CREATE OR REPLACE). Apply order: 030_avito_imv_cache_key_unique -> 031_houses_alter_yandex -> 032_yandex_history -> 033_listings_alter_yandex -> 034 -> 035 -> 040_... DDL inversion (031 before 033) safe: 031 touches houses, 033 touches listings, no cross-table dep. ## Test plan - [ ] Deploy applies cleanly to tradein-postgres (idempotent re-run of renamed file is no-op) - [ ] psql shows trade_in_estimates.geom geometry(Point,4326) + trade_in_estimates_geom_idx GIST - [ ] listing_sources_listing_idx2 gone, listing_sources_listing_idx (028) remains - [ ] houses_geom_idx2 gone, houses_geom_idx (009) remains - [ ] INSERT into trade_in_estimates with lat/lon auto-populates geom via trigger

lekss361 added 1 commit 2026-05-24 10:00:36 +00:00

fix(tradein-sql): rename duplicate 030 prefix, add trade_in_estimates geom, drop dup indexes 2f2a89b08f

- Rename 030_listings_alter_yandex.sql -> 033_listings_alter_yandex.sql to
  resolve duplicate prefix with 030_avito_imv_cache_key_unique.sql (undefined
  apply order under ls|sort).
- Add 034_trade_in_estimates_geom.sql: PostGIS geom column + GIST + trigger
  (schema-consistency debt vs .claude/rules/sql.md).
- Add 035_drop_duplicate_indexes.sql: drop redundant listing_sources_listing_idx2
  (= 028's _listing_idx) and houses_geom_idx2 (= 009's geom_idx).

All migrations idempotent (IF NOT EXISTS / CREATE OR REPLACE).
Per 2026-05-24 schema audit (findings #2, #14, #15).

lekss361 merged commit 02b48b8d04 into main

2026-05-24 10:04:41 +00:00

lekss361 referenced this pull request from a commit

2026-05-24 10:04:42 +00:00

fix(tradein-sql): rename dup 030, add trade_in_estimates geom, drop dup indexes (#500)

lekss361 commented

2026-05-24 10:05:10 +00:00

Author

Owner

Merged via deep-code-reviewer — verdict APPROVE.

Cross-checks performed:

Verified deploy-tradein.yml migration mechanism: ls -1 *.sql | sort + ON_ERROR_STOP=on || echo "skipped" per file, no tracking table — relies entirely on idempotent SQL patterns. Re-run safe for ALL migrations on each deploy.
Verified rename safety: original 030_listings_alter_yandex.sql already applied → renamed 033_*.sql will no-op via ADD COLUMN IF NOT EXISTS.
Cross-referenced dropped indexes:
- listing_sources_listing_idx2 (050) = exact dup of listing_sources_listing_idx (028) — confirmed
- houses_geom_idx2 (050) = exact dup of houses_geom_idx (009) — confirmed (both GIST(geom) no filter)
- Neither dropped index referenced in app code (grep-verified)
DDL inversion 031 (houses) before 033 (listings) safe — no cross-table dep
Trigger pattern: DROP TRIGGER IF EXISTS ... CREATE TRIGGER — correct PG-portable idempotent pattern
BEGIN/COMMIT wrap correct, no CONCURRENTLY conflict (intentional — small tables make CONCURRENTLY unnecessary)
Backfill UPDATE ... WHERE geom IS NULL smartly preempts existing-rows edge case

Nits (non-blocking, ignore):

034 trigger could reuse listings_set_geom (009 pattern) for DRY, but separate ownership = independent evolution; acceptable trade-off
DROP INDEX (без CONCURRENTLY) — ACCESS EXCLUSIVE lock на ms-only, on small trade-in DB non-issue

Post-merge verification suggested:

Wait for deploy-tradein.yml finish on main push
\d trade_in_estimates shows geom geometry(Point,4326)
\di trade_in_estimates_geom_idx exists
\di listing_sources_listing_idx2 returns nothing
\di houses_geom_idx2 returns nothing
INSERT into trade_in_estimates с lat/lon → SELECT geom IS NOT NULL

Merged via deep-code-reviewer — verdict APPROVE. **Cross-checks performed:** - Verified deploy-tradein.yml migration mechanism: `ls -1 *.sql | sort` + `ON_ERROR_STOP=on || echo "skipped"` per file, **no tracking table** — relies entirely on idempotent SQL patterns. Re-run safe for ALL migrations on each deploy. - Verified rename safety: original `030_listings_alter_yandex.sql` already applied → renamed `033_*.sql` will no-op via `ADD COLUMN IF NOT EXISTS`. - Cross-referenced dropped indexes: - `listing_sources_listing_idx2` (050) = exact dup of `listing_sources_listing_idx` (028) — confirmed - `houses_geom_idx2` (050) = exact dup of `houses_geom_idx` (009) — confirmed (both `GIST(geom)` no filter) - Neither dropped index referenced in app code (grep-verified) - DDL inversion 031 (houses) before 033 (listings) safe — no cross-table dep - Trigger pattern: `DROP TRIGGER IF EXISTS ... CREATE TRIGGER` — correct PG-portable idempotent pattern - BEGIN/COMMIT wrap correct, no `CONCURRENTLY` conflict (intentional — small tables make CONCURRENTLY unnecessary) - Backfill `UPDATE ... WHERE geom IS NULL` smartly preempts existing-rows edge case **Nits (non-blocking, ignore):** - 034 trigger could `reuse listings_set_geom` (009 pattern) for DRY, but separate ownership = independent evolution; acceptable trade-off - `DROP INDEX` (без CONCURRENTLY) — ACCESS EXCLUSIVE lock на ms-only, on small trade-in DB non-issue **Post-merge verification suggested:** 1. Wait for `deploy-tradein.yml` finish on `main` push 2. `\d trade_in_estimates` shows `geom geometry(Point,4326)` 3. `\di trade_in_estimates_geom_idx` exists 4. `\di listing_sources_listing_idx2` returns nothing 5. `\di houses_geom_idx2` returns nothing 6. INSERT into trade_in_estimates с lat/lon → SELECT geom IS NOT NULL

lekss361 referenced this pull request from a commit

2026-05-24 12:12:36 +00:00

fix(tradein-sql): remove duplicate CREATE INDEX from 050 (regression from PR #500)

lekss361 referenced this pull request

2026-05-24 12:13:15 +00:00

fix(tradein-sql): remove duplicate CREATE INDEX from 050 (regression from PR #500) #515

lekss361 referenced this pull request

2026-05-24 12:16:21 +00:00

fix(tradein-sql): remove duplicate CREATE INDEX from 050 (regression from PR #500) #515

lekss361 referenced this pull request from a commit

2026-05-24 12:16:30 +00:00

fix(tradein-sql): remove duplicate CREATE INDEX from 050 (regression from PR #500) (#515)