lekss361/gendesign

Fork 0

feat(tradein): SQL migrations 030-032 — Yandex schema (listings/houses/history) #453

Merged

lekss361 merged 1 commit from feat/tradein-yandex-migrations into main

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

lekss361 commented

2026-05-23 12:54:10 +00:00

Owner

Motivation

Foundation DDL for YandexRealtyScraper v1 (Stage 1 of 9). Adds Yandex-specific columns to listings and houses, and registers source='yandex_valuation' on the existing house_placement_history table. Stages 2–9 (parsers, matchers, enrichment) follow in subsequent PRs.

Numbered 030–032 because 029 was already consumed by PR #448 (matching/valuation/dynamics extensions merged earlier today).

Files

File	Content
`030_listings_alter_yandex.sql`	7 columns + 2 partial indexes on `listings`
`031_houses_alter_yandex.sql`	9 columns + 2 partial indexes on `houses`
`032_yandex_history.sql`	COMMENT update + 2 indexes on `house_placement_history`

Column inventory

030 — listings

yandex_offer_id text — numeric string offer ID kept as text
views_total_yandex int — parallel to Cian’s views_total; separate column to preserve source attribution
publish_date_relative text — raw "цасов назад" / "вчера" string
sale_type_text text — raw RU phrase (свободная продажа / альтернативная)
agency_name text, agency_founded_year int, agency_objects_count int — OfferCardAuthorInfo block

031 — houses

yandex_jk_id text, yandex_jk_slug text — Yandex newbuilding (ЖК) identifiers
commission_year int, commission_month text — commissioning date (raw RU month)
total_area_ha numeric(6,2), corpus_count int — ЖК footprint/corpus metadata
yandex_validated_at timestamptz, yandex_total_listings int, has_panorama boolean — Valuation tool enrichment fields

032 — house_placement_history

No schema change. Updates COMMENT ON COLUMN source to document yandex_valuation value.
Adds hph_source_idx (source) and hph_house_source_idx (house_id, source, last_price_date DESC WHERE house_id IS NOT NULL).

Smoke verification

The MCP tunnel (localhost:15432) connects to the main gendesign DB which does not contain the tradein schema (listings, houses, house_placement_history). Smoke verification is deferred to deploy via the existing apply_sql_migrations step in deploy.yml. All migrations are idempotent (ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) — safe for repeated application.

Reviewer note

Reviewer: deep-code-reviewer — only merge after APPROVE.

## Motivation Foundation DDL for YandexRealtyScraper v1 (Stage 1 of 9). Adds Yandex-specific columns to `listings` and `houses`, and registers `source='yandex_valuation'` on the existing `house_placement_history` table. Stages 2–9 (parsers, matchers, enrichment) follow in subsequent PRs. Numbered 030–032 because 029 was already consumed by PR #448 (matching/valuation/dynamics extensions merged earlier today). ## Files | File | Content | |---|---| | `030_listings_alter_yandex.sql` | 7 columns + 2 partial indexes on `listings` | | `031_houses_alter_yandex.sql` | 9 columns + 2 partial indexes on `houses` | | `032_yandex_history.sql` | COMMENT update + 2 indexes on `house_placement_history` | ## Column inventory **030 — listings** - `yandex_offer_id text` — numeric string offer ID kept as text - `views_total_yandex int` — parallel to Cian’s `views_total`; separate column to preserve source attribution - `publish_date_relative text` — raw "цасов назад" / "вчера" string - `sale_type_text text` — raw RU phrase (свободная продажа / альтернативная) - `agency_name text`, `agency_founded_year int`, `agency_objects_count int` — OfferCardAuthorInfo block **031 — houses** - `yandex_jk_id text`, `yandex_jk_slug text` — Yandex newbuilding (ЖК) identifiers - `commission_year int`, `commission_month text` — commissioning date (raw RU month) - `total_area_ha numeric(6,2)`, `corpus_count int` — ЖК footprint/corpus metadata - `yandex_validated_at timestamptz`, `yandex_total_listings int`, `has_panorama boolean` — Valuation tool enrichment fields **032 — house_placement_history** - No schema change. Updates `COMMENT ON COLUMN source` to document `yandex_valuation` value. - Adds `hph_source_idx` (source) and `hph_house_source_idx` (house_id, source, last_price_date DESC WHERE house_id IS NOT NULL). ## Smoke verification The MCP tunnel (`localhost:15432`) connects to the main gendesign DB which does not contain the tradein schema (`listings`, `houses`, `house_placement_history`). Smoke verification is **deferred to deploy** via the existing `apply_sql_migrations` step in `deploy.yml`. All migrations are idempotent (`ADD COLUMN IF NOT EXISTS`, `CREATE INDEX IF NOT EXISTS`) — safe for repeated application. ## Reviewer note Reviewer: deep-code-reviewer — only merge after APPROVE.

lekss361 added 1 commit 2026-05-23 12:54:11 +00:00

feat(tradein): SQL migrations 030-032 — Yandex schema extensions 65c2824507

- 030_listings_alter_yandex.sql: 7 cols (yandex_offer_id, views_total_yandex,
  publish_date_relative, sale_type_text, agency_name/founded_year/objects_count)
  + 2 partial indexes (yandex_offer_id, agency_name)
- 031_houses_alter_yandex.sql: 9 cols (yandex_jk_id/slug, commission_year/month,
  total_area_ha, corpus_count, yandex_validated_at, yandex_total_listings,
  has_panorama) + 2 partial indexes (yandex_jk_id, commission_year)
- 032_yandex_history.sql: enables source='yandex_valuation' on existing
  house_placement_history + 2 indexes (hph_source_idx, hph_house_source_idx)

Foundation for YandexRealtyScraper v1 (Stages 2-9 follow on subsequent PRs).
Note: numbered 030-032 because 029 was already used by PR #448 (matching/
valuation/dynamics extensions). All BEGIN/COMMIT, ADD COLUMN IF NOT EXISTS,
nullable, idempotent.

lekss361 merged commit f452b132c9 into main

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

lekss361 referenced this pull request from a commit

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

feat(tradein): SQL migrations 030-032 — Yandex schema (listings/houses/history) (#453)

lekss361 commented

2026-05-23 13:01:20 +00:00

Author

Owner

Merged via deep-code-reviewer — verdict APPROVE (squash, commit f452b13).

Review summary

Files: 3 (all P0 — data/sql/**), +117/-0
Idempotency: all ADD COLUMN IF NOT EXISTS + CREATE INDEX IF NOT EXISTS, BEGIN/COMMIT wrapped, COMMENT overwrites — safe re-run
Numbering: 030-032 free on forgejo/main (last was 029 from PR #448). No collision.
house_placement_history.source: verified no CHECK constraint in 017 — new value yandex_valuation accepted without DDL change
Column collisions: views_total_yandex vs existing views_total (Cian) → parallel cols preserve source attribution ✅. sale_type_text vs existing sale_type enum → no collision ✅
Vault match: decisions/YandexRealtyScraper_v1_Implementation_Plan.md Stage 1 spec satisfied (sec 11.6 listings, sec 23.3 houses, sec 13.2 history)
Deploy paths: tradein-mvp/backend/** matches .forgejo/workflows/deploy-tradein.yml filter → triggers; apply loop ls -1 backend/data/sql/*.sql | sort → 030→031→032 lexicographic order

Numbering impact for PR #452 (IMV cache_key UNIQUE)

PR #452 (030_avito_imv_cache_key_unique.sql on WIP branch) must be renumbered to 033_avito_imv_cache_key_unique.sql before merge — slot 030 is now occupied by Yandex listings migration.

Lexicographic apply order after both merge: 029 → 030 (Yandex listings) → 031 (Yandex houses) → 032 (Yandex history) → 033 (IMV UNIQUE). The IMV UNIQUE migration has no dependency on Yandex DDL — apply order doesn't matter functionally.

Post-merge

Deploy workflow deploy-tradein.yml should fire on push to main. Expected log lines:

→ Migration: 030_listings_alter_yandex.sql
→ Migration: 031_houses_alter_yandex.sql
→ Migration: 032_yandex_history.sql

Smoke verification (\d listings / \d houses) after Stage 2+ scraper parsers land.

Merged via deep-code-reviewer — verdict **APPROVE** (squash, commit `f452b13`). ## Review summary - **Files**: 3 (all P0 — `data/sql/**`), +117/-0 - **Idempotency**: all `ADD COLUMN IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS`, BEGIN/COMMIT wrapped, COMMENT overwrites — safe re-run - **Numbering**: 030-032 free on `forgejo/main` (last was 029 from PR #448). No collision. - **`house_placement_history.source`**: verified no CHECK constraint in 017 — new value `yandex_valuation` accepted without DDL change - **Column collisions**: `views_total_yandex` vs existing `views_total` (Cian) → parallel cols preserve source attribution ✅. `sale_type_text` vs existing `sale_type` enum → no collision ✅ - **Vault match**: `decisions/YandexRealtyScraper_v1_Implementation_Plan.md` Stage 1 spec satisfied (sec 11.6 listings, sec 23.3 houses, sec 13.2 history) - **Deploy paths**: `tradein-mvp/backend/**` matches `.forgejo/workflows/deploy-tradein.yml` filter → triggers; apply loop `ls -1 backend/data/sql/*.sql | sort` → 030→031→032 lexicographic order ## Numbering impact for PR #452 (IMV cache_key UNIQUE) PR #452 (`030_avito_imv_cache_key_unique.sql` on WIP branch) must be **renumbered to `033_avito_imv_cache_key_unique.sql`** before merge — slot 030 is now occupied by Yandex listings migration. Lexicographic apply order after both merge: 029 → 030 (Yandex listings) → 031 (Yandex houses) → 032 (Yandex history) → 033 (IMV UNIQUE). The IMV UNIQUE migration has no dependency on Yandex DDL — apply order doesn't matter functionally. ## Post-merge Deploy workflow `deploy-tradein.yml` should fire on push to main. Expected log lines: ``` → Migration: 030_listings_alter_yandex.sql → Migration: 031_houses_alter_yandex.sql → Migration: 032_yandex_history.sql ``` Smoke verification (`\d listings` / `\d houses`) after Stage 2+ scraper parsers land.

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 referenced this pull request

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

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

lekss361 referenced this pull request

2026-05-23 13:09:01 +00:00

feat(tradein): yandex_helpers.py — JSON-LD + regex + date + NLP + money helpers #456

lekss361 referenced this pull request

2026-05-23 13:57:06 +00:00

feat(tradein): conflict_resolution — register Yandex sources in priority dicts #471