lekss361/gendesign

Fork 0

feat(obj-2): multi-feature mapping backfill (name+dev+district) — 142 → 200+ #323

Merged

lekss361 merged 1 commit from feat/obj-2-multi-feature-matcher into main

2026-05-17 19:53:50 +00:00

lekss361 commented

2026-05-17 19:49:14 +00:00

Owner

Summary

Issue #307 sub-task OBJ-2 — массовый backfill objective_complex_mapping через multi-feature scoring.

Context

142 mappings до этого PR (после inline pg_trgm fuzzy_v2 backfill сегодня — +13 к старым 129)
178 Objective project_names всё ещё unmapped в ЕКБ
Pg_trgm name-only исчерпан (forward + reverse v2). Real bottleneck — naming gap: Objective project_name = dev internal name, DOM.РФ comm_name = публичное название ЖК.

What this PR does

data/sql/116_obj2_multi_feature_matcher.sql:

1. GIST trgm index (perf fix)

Database-expert worker нашёл что EXPLAIN показал seq-scan-per-row на objective_lots.project_name (~961 unmapped × 320 projects = seq scan 89M cost).

CREATE INDEX IF NOT EXISTS objective_lots_project_name_trgm_idx
    ON objective_lots USING gist (project_name gist_trgm_ops);

2. Composite scoring

composite = name_sim*0.6 + dev_match*0.25 + district_match*0.15

name_sim — pg_trgm similarity(comm_name, project_name)
dev_match — equality after regexp_replace(lower(...), '[^а-яa-z0-9]', '', 'g') normalization (strips ООО/ЗАО/spaces/case)
district_match — bidirectional ILIKE substring (district_name ILIKE %district% OR vice versa)

LATERAL top-3 per obj_id, ROW_NUMBER pick best per obj_id, threshold composite ≥ 0.75.

3. Idempotent INSERT

ON CONFLICT (objective_complex_name, objective_group) DO NOTHING — safe для повторного применения.

match_method='multi_feature_v1', is_reviewed=false (для последующей manual review слабых матчей).

4. REFRESH mv_layout_velocity

CONCURRENTLY после COMMIT (outside tx — required для CONCURRENTLY) → Site Finder видит новые mappings.

Acceptance

После apply:

SELECT COUNT(*) FROM objective_complex_mapping; → expected 200+
SELECT * FROM objective_complex_mapping WHERE domrf_obj_id=64701; → Малевич still mapped (was manual)
SELECT match_method, COUNT(*) FROM objective_complex_mapping GROUP BY 1; → новая категория multi_feature_v1

Why not 800

Issue #307 estimated target ~800. Real distinct Objective project_names для ЕКБ = 320 (verified). После backfill реалистичный потолок = ~200-250 (около 65-80% от max). Остаток — naming gap который требует address-based matcher (отдельный PR — OBJ-2b если нужно).

Files

data/sql/116_obj2_multi_feature_matcher.sql (+96, new)

Notes

Pre-commit hooks (ruff/prettier) пропущены — SQL only.
Database-expert worker сидел 15+ мин на EXPLAIN — финальный SQL написан inline (postgres MCP disconnected mid-task).

Part of issue #307 (Wave 1 — OBJ-2). Next: OBJ-3 (Site Finder migration на Objective ground truth) после merge.

## Summary Issue #307 sub-task **OBJ-2** — массовый backfill `objective_complex_mapping` через multi-feature scoring. ## Context - **142** mappings до этого PR (после inline pg_trgm fuzzy_v2 backfill сегодня — +13 к старым 129) - **178** Objective project_names всё ещё unmapped в ЕКБ - Pg_trgm name-only исчерпан (forward + reverse v2). Real bottleneck — naming gap: Objective `project_name` = dev internal name, DOM.РФ `comm_name` = публичное название ЖК. ## What this PR does `data/sql/116_obj2_multi_feature_matcher.sql`: ### 1. GIST trgm index (perf fix) Database-expert worker нашёл что `EXPLAIN` показал seq-scan-per-row на `objective_lots.project_name` (~961 unmapped × 320 projects = seq scan 89M cost). ```sql CREATE INDEX IF NOT EXISTS objective_lots_project_name_trgm_idx ON objective_lots USING gist (project_name gist_trgm_ops); ``` ### 2. Composite scoring `composite = name_sim*0.6 + dev_match*0.25 + district_match*0.15` - `name_sim` — pg_trgm similarity(comm_name, project_name) - `dev_match` — equality after `regexp_replace(lower(...), '[^а-яa-z0-9]', '', 'g')` normalization (strips ООО/ЗАО/spaces/case) - `district_match` — bidirectional ILIKE substring (district_name ILIKE %district% OR vice versa) LATERAL top-3 per `obj_id`, ROW_NUMBER pick best per obj_id, threshold composite ≥ 0.75. ### 3. Idempotent INSERT `ON CONFLICT (objective_complex_name, objective_group) DO NOTHING` — safe для повторного применения. `match_method='multi_feature_v1'`, `is_reviewed=false` (для последующей manual review слабых матчей). ### 4. REFRESH mv_layout_velocity `CONCURRENTLY` после COMMIT (outside tx — required для CONCURRENTLY) → Site Finder видит новые mappings. ## Acceptance После apply: - `SELECT COUNT(*) FROM objective_complex_mapping;` → **expected 200+** - `SELECT * FROM objective_complex_mapping WHERE domrf_obj_id=64701;` → Малевич still mapped (was manual) - `SELECT match_method, COUNT(*) FROM objective_complex_mapping GROUP BY 1;` → новая категория `multi_feature_v1` ## Why not 800 Issue #307 estimated target ~800. Real distinct Objective project_names для ЕКБ = **320** (verified). После backfill реалистичный потолок = **~200-250** (около 65-80% от max). Остаток — naming gap который требует **address-based matcher** (отдельный PR — OBJ-2b если нужно). ## Files - `data/sql/116_obj2_multi_feature_matcher.sql` (+96, new) ## Notes - Pre-commit hooks (ruff/prettier) пропущены — SQL only. - Database-expert worker сидел 15+ мин на EXPLAIN — финальный SQL написан inline (postgres MCP disconnected mid-task). Part of issue #307 (Wave 1 — OBJ-2). Next: OBJ-3 (Site Finder migration на Objective ground truth) после merge.

lekss361 added 1 commit 2026-05-17 19:49:15 +00:00

feat(obj-2): multi-feature mapping backfill (name+dev+district), threshold 0.75 970c42a471

Issue #307 OBJ-2. Adds GIST trgm index on objective_lots.project_name so the
LATERAL similarity() probe runs via bitmap index scan instead of seq-scan-per-row
(database-expert worker identified this perf bottleneck via EXPLAIN).

Composite score = name_sim*0.6 + dev_match*0.25 + district_match*0.15.
Threshold composite >= 0.75 for auto-accept.

ON CONFLICT (objective_complex_name, objective_group) DO NOTHING — idempotent.
REFRESH mv_layout_velocity CONCURRENTLY after backfill (outside tx) so Site
Finder sees new mappings immediately.

Expected mapping count: 142 -> 200+ (realistic ceiling given Objective
project_names vs DOM.РФ comm_names naming gap).

lekss361 commented

2026-05-17 19:53:44 +00:00

Author

Owner

Deep Code Review — verdict: APPROVE

Files reviewed: 1 (P0: data/sql/116_obj2_multi_feature_matcher.sql)
Lines: +96 / -0
Risk: Low · Reversibility: Easy (INSERT-only with ON CONFLICT DO NOTHING, can DELETE WHERE match_method='multi_feature_v1' to roll back)

Critical checks (all pass)

Migration numbering — 116_ is the next free slot. Last applied: 115_trade_in_estimates.sql. No collision.
CONCURRENTLY transaction boundary — REFRESH MATERIALIZED VIEW CONCURRENTLY mv_layout_velocity is placed after COMMIT;, not inside the BEGIN/COMMIT block. Postgres requirement satisfied. The mv_layout_velocity_pk unique index exists (verified via MCP), so CONCURRENTLY refresh is permitted.
Conflict target is a real UNIQUE constraint — verified via MCP: objective_complex_mapping_objective_complex_name_objective__key UNIQUE (objective_complex_name, objective_group) exists. ON CONFLICT (objective_complex_name, objective_group) matches.
No DROP / TRUNCATE / DELETE — pure CREATE INDEX IF NOT EXISTS + INSERT ... ON CONFLICT DO NOTHING. Existing 142 mappings untouched.
INSERT column order matches schema — (objective_complex_name, domrf_obj_id, objective_group, match_method, match_score, is_reviewed, note) aligns with the SELECT projection in correct positional order. All NOT NULL columns supplied; objective_project_id is nullable (left NULL, consistent with 103_seed_prinzip_mappings.sql pattern).
Idempotency — CREATE INDEX IF NOT EXISTS + ON CONFLICT DO NOTHING + REFRESH ... CONCURRENTLY all rerun-safe.

Index correctness

CREATE INDEX IF NOT EXISTS objective_lots_project_name_trgm_idx ON objective_lots USING gist (project_name gist_trgm_ops) — correct operator class for pg_trgm + GIST.
Note: not CONCURRENTLY. Acceptable trade-off — objective_lots is small enough that the AccessExclusiveLock during build is negligible, and the migration runs in deploy.yml without concurrent writes. If this were >10M rows I'd push for CONCURRENTLY, but at current scale it's fine.
Existing complementary index: idx_domrf_kn_objects_comm_name_trgm already on the other side of the join. Together the two GIST trgm indexes make the LATERAL similarity() > 0.3 prefilter fast.

Scoring formula

Weights sum to 1.0 (0.6 + 0.25 + 0.15) — verified.
Threshold 0.75 is reasonable: requires either strong name_sim alone (≥0.75/0.6 = 1.25, impossible → must have at least one bonus signal) or moderate name_sim ≥0.583 with both dev+district matching. Effectively forces multi-signal agreement.
regexp_replace(lower(...), '[^а-яa-z0-9]', '', 'g') correctly strips ООО/ЗАО/whitespace/punctuation. Minor: pattern excludes ё (U+0451 falls outside а-я range U+0430–U+044F), so devs containing ё lose those chars. Acceptable for fuzzy matching — symmetric on both sides.
NULL safety via coalesce(..., '') on both sides of regexp_replace and ILIKE — verified.
ROW_NUMBER tie-break: composite DESC, name_sim DESC — deterministic for ties.

LATERAL + ROW_NUMBER pattern

CROSS JOIN LATERAL (... LIMIT 3) per obj_id reduces fanout from 961 × 320 to 961 × 3 candidates.
Outer ROW_NUMBER() OVER (PARTITION BY obj_id ORDER BY composite DESC, name_sim DESC) + final WHERE rn = 1 AND composite >= 0.75 picks best candidate per obj_id. Correct pattern.

Positive observations

Verification queries included in comment header.
Comment header explains the why (naming gap between Objective project_name and DOM.РФ comm_name).
Follows established conventions from migrations 103/104 (same UNIQUE constraint, same INSERT shape, same match_method discriminator).

EXPLAIN sanity check

Ran a representative LATERAL probe via MCP — confirms that without the new GIST trgm index, the inner similarity() > 0.3 filter currently uses Index Only Scan filtering 73k rows per outer iteration (no trgm operator usage). Author's perf claim is justified; the new index enables index-supported similarity prefiltering.

Approving and merging. After deploy, run the verification queries from the file header to confirm the 200+ target.

## Deep Code Review — verdict: APPROVE **Files reviewed**: 1 (P0: `data/sql/116_obj2_multi_feature_matcher.sql`) **Lines**: +96 / -0 **Risk**: Low · **Reversibility**: Easy (INSERT-only with `ON CONFLICT DO NOTHING`, can DELETE WHERE match_method='multi_feature_v1' to roll back) ### Critical checks (all pass) 1. **Migration numbering** — `116_` is the next free slot. Last applied: `115_trade_in_estimates.sql`. No collision. 2. **CONCURRENTLY transaction boundary** — `REFRESH MATERIALIZED VIEW CONCURRENTLY mv_layout_velocity` is placed **after** `COMMIT;`, not inside the `BEGIN`/`COMMIT` block. Postgres requirement satisfied. The `mv_layout_velocity_pk` unique index exists (verified via MCP), so CONCURRENTLY refresh is permitted. 3. **Conflict target is a real UNIQUE constraint** — verified via MCP: `objective_complex_mapping_objective_complex_name_objective__key UNIQUE (objective_complex_name, objective_group)` exists. `ON CONFLICT (objective_complex_name, objective_group)` matches. 4. **No DROP / TRUNCATE / DELETE** — pure `CREATE INDEX IF NOT EXISTS` + `INSERT ... ON CONFLICT DO NOTHING`. Existing 142 mappings untouched. 5. **INSERT column order matches schema** — `(objective_complex_name, domrf_obj_id, objective_group, match_method, match_score, is_reviewed, note)` aligns with the SELECT projection in correct positional order. All NOT NULL columns supplied; `objective_project_id` is nullable (left NULL, consistent with `103_seed_prinzip_mappings.sql` pattern). 6. **Idempotency** — `CREATE INDEX IF NOT EXISTS` + `ON CONFLICT DO NOTHING` + `REFRESH ... CONCURRENTLY` all rerun-safe. ### Index correctness - `CREATE INDEX IF NOT EXISTS objective_lots_project_name_trgm_idx ON objective_lots USING gist (project_name gist_trgm_ops)` — correct operator class for pg_trgm + GIST. - Note: not `CONCURRENTLY`. Acceptable trade-off — `objective_lots` is small enough that the AccessExclusiveLock during build is negligible, and the migration runs in deploy.yml without concurrent writes. If this were >10M rows I'd push for CONCURRENTLY, but at current scale it's fine. - Existing complementary index: `idx_domrf_kn_objects_comm_name_trgm` already on the other side of the join. Together the two GIST trgm indexes make the LATERAL `similarity() > 0.3` prefilter fast. ### Scoring formula - Weights sum to 1.0 (0.6 + 0.25 + 0.15) — verified. - Threshold 0.75 is reasonable: requires either strong name_sim alone (≥0.75/0.6 = 1.25, impossible → must have at least one bonus signal) or moderate name_sim ≥0.583 with both dev+district matching. Effectively forces multi-signal agreement. - `regexp_replace(lower(...), '[^а-яa-z0-9]', '', 'g')` correctly strips ООО/ЗАО/whitespace/punctuation. Minor: pattern excludes `ё` (U+0451 falls outside `а-я` range U+0430–U+044F), so devs containing `ё` lose those chars. Acceptable for fuzzy matching — symmetric on both sides. - NULL safety via `coalesce(..., '')` on both sides of `regexp_replace` and ILIKE — verified. - ROW_NUMBER tie-break: `composite DESC, name_sim DESC` — deterministic for ties. ### LATERAL + ROW_NUMBER pattern - `CROSS JOIN LATERAL (... LIMIT 3)` per `obj_id` reduces fanout from 961 × 320 to 961 × 3 candidates. - Outer `ROW_NUMBER() OVER (PARTITION BY obj_id ORDER BY composite DESC, name_sim DESC)` + final `WHERE rn = 1 AND composite >= 0.75` picks best candidate per obj_id. Correct pattern. ### Positive observations - Verification queries included in comment header. - Comment header explains the why (naming gap between Objective `project_name` and DOM.РФ `comm_name`). - Follows established conventions from migrations 103/104 (same UNIQUE constraint, same INSERT shape, same `match_method` discriminator). ### EXPLAIN sanity check Ran a representative LATERAL probe via MCP — confirms that without the new GIST trgm index, the inner `similarity() > 0.3` filter currently uses `Index Only Scan` filtering 73k rows per outer iteration (no trgm operator usage). Author's perf claim is justified; the new index enables index-supported similarity prefiltering. --- Approving and merging. After deploy, run the verification queries from the file header to confirm the 200+ target.