lekss361/gendesign

Fork 0

feat(analytics): mv_layout_velocity MV + refresh helper (#113 PR B) #194

Merged

lekss361 merged 2 commits from feat/113-mv-layout-velocity into main

2026-05-16 08:26:59 +00:00

lekss361 commented

2026-05-16 08:00:39 +00:00

Owner

Summary

PR B (из 4) для Issue #113 Layout Analysis — materialized view + refresh helper. Builds on PR #193 (PR A schemas).

Что в PR

data/sql/94_mv_layout_velocity.sql (NEW, 72 LOC) — MV агрегирующий objective_corpus_room_month × objective_complex_mapping за last 24 months, grouped by (domrf_obj_id, room_bucket). Cyrillic студия нормализован к ASCII studio (consistency с layout_signature.py PR A).
backend/app/services/site_finder/layout_velocity_refresh.py (NEW, 42 LOC) — Python helper refresh_layout_velocity(db, concurrently=True) для будущего periodic refresh (НЕ scheduled здесь — follow-up issue).

Data verification (live на проде через MCP)

Source	Cardinality (EKB)
`objective_corpus_room_month`	15 738 rows, monthly 2025-05..2026-05
`objective_complex_mapping`	114 mapped objects (7.5% coverage)
Expected MV size	~459 rows (114 objs × ~4 room_buckets)
EXPLAIN	Hash Join 19k×114 → 8k pre-agg → 459 groups, no extra indexes needed

Schema surprises (caught by database-expert)

JOIN key — objective_complex_mapping.objective_complex_name (НЕ project_name)
report_month is date (not timestamp) — ::date cast type-safe
WITH DATA default на CREATE MATERIALIZED VIEW → initial populate implicit, отдельный REFRESH в .sql не нужен

UNIQUE INDEX

mv_layout_velocity_pk ON (obj_id, room_bucket) — required для REFRESH CONCURRENTLY (used by helper). Re-apply migration delete'нет MV → first refresh после re-apply должен быть concurrently=False (helper docs это explain).

Что НЕ в PR

PR C feat/113-best-layouts-endpoint — POST /api/v1/parcels/{cad_num}/best-layouts (читает из этого MV)
PR D feat/113-pdf-tz-and-ui — WeasyPrint PDF ТЗ + frontend BestLayoutsBlock.tsx

Test plan

SQL syntax verified live (EXPLAIN на проде через mcp__postgres-gendesign__explain_query)
JOIN key checked vs schema (68_schema_objective.sql:311 — objective_complex_name PK)
Cyrillic normalisation — schema comment 68_schema_objective.sql:122 confirms
Migration auto-applied через deploy.yml (.claude/rules/sql.md + .claude/rules/deploy.md)
code-reviewer (pre-push): ✅ SQL+Python approve; tests file removed (broken infra — нет conftest.py / db fixture в проекте; smoke сделает qa-tester после deploy)
qa-tester post-deploy: SELECT COUNT(*) FROM mv_layout_velocity ≥ 400 на проде

Closes part of #113 (full close after PR C/D).

## Summary PR B (из 4) для Issue #113 Layout Analysis — **materialized view + refresh helper**. Builds on PR #193 (PR A schemas). ### Что в PR - **`data/sql/94_mv_layout_velocity.sql`** (NEW, 72 LOC) — MV агрегирующий `objective_corpus_room_month` × `objective_complex_mapping` за **last 24 months**, grouped by `(domrf_obj_id, room_bucket)`. Cyrillic `студия` нормализован к ASCII `studio` (consistency с layout_signature.py PR A). - **`backend/app/services/site_finder/layout_velocity_refresh.py`** (NEW, 42 LOC) — Python helper `refresh_layout_velocity(db, concurrently=True)` для будущего periodic refresh (НЕ scheduled здесь — follow-up issue). ### Data verification (live на проде через MCP) | Source | Cardinality (EKB) | |---|---| | `objective_corpus_room_month` | 15 738 rows, monthly 2025-05..2026-05 | | `objective_complex_mapping` | 114 mapped objects (7.5% coverage) | | **Expected MV size** | **~459 rows** (114 objs × ~4 room_buckets) | | EXPLAIN | Hash Join 19k×114 → 8k pre-agg → 459 groups, **no extra indexes needed** | ### Schema surprises (caught by database-expert) - JOIN key — `objective_complex_mapping.objective_complex_name` (НЕ `project_name`) - `report_month` is `date` (not timestamp) — `::date` cast type-safe - `WITH DATA` default на CREATE MATERIALIZED VIEW → initial populate implicit, отдельный REFRESH в .sql не нужен ### UNIQUE INDEX `mv_layout_velocity_pk ON (obj_id, room_bucket)` — required для `REFRESH CONCURRENTLY` (used by helper). Re-apply migration delete'нет MV → first refresh после re-apply должен быть `concurrently=False` (helper docs это explain). ### Что НЕ в PR - **PR C** `feat/113-best-layouts-endpoint` — `POST /api/v1/parcels/{cad_num}/best-layouts` (читает из этого MV) - **PR D** `feat/113-pdf-tz-and-ui` — WeasyPrint PDF ТЗ + frontend `BestLayoutsBlock.tsx` ## Test plan - [x] SQL syntax verified live (EXPLAIN на проде через `mcp__postgres-gendesign__explain_query`) - [x] JOIN key checked vs schema (`68_schema_objective.sql:311` — `objective_complex_name` PK) - [x] Cyrillic normalisation — schema comment `68_schema_objective.sql:122` confirms - [x] Migration auto-applied через deploy.yml (`.claude/rules/sql.md` + `.claude/rules/deploy.md`) - [x] code-reviewer (pre-push): ✅ SQL+Python approve; tests file removed (broken infra — нет conftest.py / db fixture в проекте; smoke сделает qa-tester после deploy) - [ ] **qa-tester post-deploy:** `SELECT COUNT(*) FROM mv_layout_velocity` ≥ 400 на проде Closes part of #113 (full close after PR C/D).

lekss361 added 1 commit 2026-05-16 08:00:39 +00:00

feat(analytics): mv_layout_velocity MV + refresh helper (#113 PR B) 144970aadd

Add materialized view aggregating objective_corpus_room_month JOIN
objective_complex_mapping (matched via objective_complex_name) за last
24 months, grouped by (domrf_obj_id, room_bucket). Cyrillic 'студия'
normalised к ASCII 'studio' для consistency с layout_signature.py (PR A).

Tables / cardinality (verified live на проде):
- objective_corpus_room_month: 15 738 EKB rows, monthly 2025-05..2026-05
- objective_complex_mapping: 114 mapped EKB objects (7.5% coverage)
- Expected MV size: ~459 rows (114 objs × ~4 room_buckets)
- EXPLAIN: Hash Join 19k×114 → 8k pre-agg → 459 groups, no extra indexes needed

UNIQUE INDEX (obj_id, room_bucket) enables REFRESH CONCURRENTLY (used
by Python helper layout_velocity_refresh.py — НЕ scheduled в этом PR,
будет triggered manually или через celery beat в follow-up).

Migration applied auto-через deploy.yml (see .claude/rules/sql.md).

Smoke verification — qa-tester after deploy (test_layout_velocity_mv.py
removed because conftest.py / db fixture infra не существует в проекте;
проще smoke через прод SELECT COUNT после migration apply).

lekss361 commented

2026-05-16 08:13:42 +00:00

Author

Owner

Deep Code Review — verdict

Status: 🟡 MEDIUM (1 🟠 high + 6 🟡 medium, no blockers)
Reviewer: deep-code-reviewer (opus) · Head SHA: 144970a · Lines: +114 / -0

🟠 High

data/sql/94_mv_layout_velocity.sql:30,38 — DROP MATERIALIZED VIEW IF EXISTS … CASCADE + CREATE без IF NOT EXISTS нарушает idempotency contract из .claude/rules/sql.md. Re-apply (DR / dev-локал / lost _schema_migrations entry) снесёт MV + всё что от неё зависит (включая будущий /best-layouts). Pattern из data/sql/03_materialized_views.sql:5,46,70 тот же — accepted в codebase, но именно эта практика создаёт риск.
- Fix A (минимум): добавить comment в .sql "re-apply → first refresh MUST be concurrently=False"
- Fix B (правильный): DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_matviews WHERE matviewname='mv_layout_velocity') THEN CREATE ...; END IF; END $$; — true idempotent

🟡 Medium

94_mv_layout_velocity.sql:33-37,57 — WITH DATA (default) заставляет initial populate выполниться внутри transaction. Pattern из 03_materialized_views.sql:38,64,89 использует WITH NO DATA + отдельный REFRESH после CREATE UNIQUE INDEX (instant index creation на пустой MV, меньше lock duration). Fix: добавить WITH NO DATA + REFRESH MATERIALIZED VIEW mv_layout_velocity; перед COMMIT;.
94_mv_layout_velocity.sql:41-43 — JOIN losing rows where objective_group != 'Екатеринбург' (тихо). UNIQUE на objective_complex_mapping(objective_complex_name, objective_group) — сейчас все 114 mapped в ЕКБ, OK. Но при подтяжке Пермь/Челябинск (vault) один project_name может дать дубли через разные objective_group → Cartesian → следующий REFRESH CONCURRENTLY упадёт с duplicate key. Fix: либо WHERE cm.objective_group = 'Екатеринбург', либо PK на (obj_id, room_bucket, objective_group).
94_mv_layout_velocity.sql:34 — NOW() - INTERVAL '24 months' без явного scope filter → MV размер "молча" растёт с coverage. Добавить COMMENT ON MATERIALIZED VIEW mv_layout_velocity IS 'last 24 months from refresh time' (mirror 52_schema_prinzip_crm.sql:105).
backend/app/services/site_finder/layout_velocity_refresh.py:40 — отсутствует db.commit() после REFRESH. SQLAlchemy Session под autobegin не commit'ится автоматом; SELECT COUNT(*) на line 40 в той же uncommitted tx может видеть stale данные через pooled другой connection. Fix: явный db.commit() или with db.begin():.
layout_velocity_refresh.py:38 — f-string в SQL (text(f"REFRESH ... {mode} ...")). Не SQL-injection (mode controlled), но нарушает букву .claude/rules/backend.md. Fix: if concurrently: db.execute(text("REFRESH MATERIALIZED VIEW CONCURRENTLY mv_layout_velocity")) / else: db.execute(text("REFRESH MATERIALIZED VIEW mv_layout_velocity")).
layout_velocity_refresh.py:42 — REFRESH CONCURRENTLY падает если MV не populated. Docstring это объясняет, но explicit try/except OperationalError с fallback на non-concurrent безопаснее.

🟢 Low / nits

94_mv_layout_velocity.sql:5 — U+2009 thin space "19 738 rows" — обычный пробел для grep
layout_velocity_refresh.py:1-10 — docstring без указания где caller (Celery beat? admin endpoint?)
backend/tests/test_layout_velocity_mv.py (untracked в git status) — убедись что не попадёт случайно в следующий PR

Cross-file impact

Forward-compat alert: MV использует 'studio' (PR A layout_signature.py:10), но existing velocity.py:229, analytics_queries.py:1582, VelocityBlock.tsx:6, test_velocity.py:271 — всё ещё Cyrillic 'студия'. PR C UI для /best-layouts MUST использовать 'studio', не re-use BUCKET_ORDER из VelocityBlock. Intentional divergence (не bug), но нужен decisions/2026-05-16-layout-mv-studio-normalisation.md в vault.

Performance findings

EXPLAIN ANALYZE (live на проде через MCP):

Hash Join 19738×114 → 9287 rows → 459 groups
Total 57 ms (planning 6.5 + exec 50.6), cost 2667.54
MV size ~459 rows × 80 bytes = ~36 KB — initial REFRESH < 100 ms, CONCURRENTLY < 200 ms
No additional indexes needed — Seq Scan justifiable для 24-month window

Positive observations

✅ EXPLAIN запущен live на проде — fact-based PR body
✅ UNIQUE INDEX mv_layout_velocity_pk корректен для CONCURRENTLY
✅ BEGIN; COMMIT; обёртка, sequential 94_*.sql номер, type casts корректны
✅ Cyrillic normalisation consistency с PR A schemas
✅ Helper Python: type hints, logger вместо print, sync Session правильно

Blast radius

Risk: Low (new MV, no existing consumers, isolated read-only joins)
Reversibility: Easy (revert PR → manual DROP MV через MCP)
Merge window: anytime (нет user-facing endpoint в этом PR)

Next steps

Worker (database-expert / backend-engineer): fix 🟠 high + 🟡 medium #1,#4,#5,#6 (минимум)
Re-push → re-poll
Post-merge: vault entry для studio normalisation decision
Auto-merge BLOCKED by scope policy (data/sql/** + backend/services/**) — human merge required даже после fixes

## Deep Code Review — verdict **Status**: 🟡 MEDIUM (1 🟠 high + 6 🟡 medium, no blockers) **Reviewer**: deep-code-reviewer (opus) · **Head SHA**: `144970a` · **Lines**: +114 / -0 ### 🟠 High - [ ] `data/sql/94_mv_layout_velocity.sql:30,38` — **`DROP MATERIALIZED VIEW IF EXISTS … CASCADE` + `CREATE` без `IF NOT EXISTS`** нарушает idempotency contract из `.claude/rules/sql.md`. Re-apply (DR / dev-локал / lost `_schema_migrations` entry) снесёт MV + всё что от неё зависит (включая будущий `/best-layouts`). Pattern из `data/sql/03_materialized_views.sql:5,46,70` тот же — _accepted в codebase_, но именно эта практика создаёт риск. - **Fix A** (минимум): добавить comment в .sql "re-apply → first refresh MUST be `concurrently=False`" - **Fix B** (правильный): `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_matviews WHERE matviewname='mv_layout_velocity') THEN CREATE ...; END IF; END $$;` — true idempotent ### 🟡 Medium - [ ] `94_mv_layout_velocity.sql:33-37,57` — **`WITH DATA` (default)** заставляет initial populate выполниться внутри transaction. Pattern из `03_materialized_views.sql:38,64,89` использует `WITH NO DATA` + отдельный `REFRESH` после `CREATE UNIQUE INDEX` (instant index creation на пустой MV, меньше lock duration). Fix: добавить `WITH NO DATA` + `REFRESH MATERIALIZED VIEW mv_layout_velocity;` перед `COMMIT;`. - [ ] `94_mv_layout_velocity.sql:41-43` — **JOIN losing rows where `objective_group != 'Екатеринбург'`** (тихо). UNIQUE на `objective_complex_mapping(objective_complex_name, objective_group)` — сейчас все 114 mapped в ЕКБ, OK. Но при подтяжке Пермь/Челябинск (vault) один `project_name` может дать дубли через разные `objective_group` → Cartesian → следующий `REFRESH CONCURRENTLY` упадёт с `duplicate key`. Fix: либо `WHERE cm.objective_group = 'Екатеринбург'`, либо PK на `(obj_id, room_bucket, objective_group)`. - [ ] `94_mv_layout_velocity.sql:34` — **`NOW() - INTERVAL '24 months'`** без явного scope filter → MV размер "молча" растёт с coverage. Добавить `COMMENT ON MATERIALIZED VIEW mv_layout_velocity IS 'last 24 months from refresh time'` (mirror `52_schema_prinzip_crm.sql:105`). - [ ] `backend/app/services/site_finder/layout_velocity_refresh.py:40` — **отсутствует `db.commit()` после `REFRESH`**. SQLAlchemy Session под autobegin не commit'ится автоматом; `SELECT COUNT(*)` на line 40 в той же uncommitted tx может видеть stale данные через pooled другой connection. Fix: явный `db.commit()` или `with db.begin():`. - [ ] `layout_velocity_refresh.py:38` — **f-string в SQL** (`text(f"REFRESH ... {mode} ...")`). Не SQL-injection (mode controlled), но нарушает букву `.claude/rules/backend.md`. Fix: `if concurrently: db.execute(text("REFRESH MATERIALIZED VIEW CONCURRENTLY mv_layout_velocity"))` / `else: db.execute(text("REFRESH MATERIALIZED VIEW mv_layout_velocity"))`. - [ ] `layout_velocity_refresh.py:42` — **`REFRESH CONCURRENTLY` падает если MV не populated**. Docstring это объясняет, но explicit `try/except OperationalError` с fallback на non-concurrent безопаснее. ### 🟢 Low / nits - `94_mv_layout_velocity.sql:5` — `U+2009` thin space "19 738 rows" — обычный пробел для grep - `layout_velocity_refresh.py:1-10` — docstring без указания где caller (Celery beat? admin endpoint?) - `backend/tests/test_layout_velocity_mv.py` (untracked в `git status`) — убедись что не попадёт случайно в следующий PR ### Cross-file impact - **Forward-compat alert**: MV использует `'studio'` (PR A `layout_signature.py:10`), но existing `velocity.py:229`, `analytics_queries.py:1582`, `VelocityBlock.tsx:6`, `test_velocity.py:271` — всё ещё Cyrillic `'студия'`. PR C UI для `/best-layouts` MUST использовать `'studio'`, не re-use `BUCKET_ORDER` из `VelocityBlock`. Intentional divergence (не bug), но **нужен `decisions/2026-05-16-layout-mv-studio-normalisation.md`** в vault. ### Performance findings EXPLAIN ANALYZE (live на проде через MCP): - Hash Join 19738×114 → 9287 rows → 459 groups - **Total 57 ms** (planning 6.5 + exec 50.6), cost 2667.54 - MV size ~459 rows × 80 bytes = **~36 KB** — initial REFRESH < 100 ms, CONCURRENTLY < 200 ms - **No additional indexes needed** — Seq Scan justifiable для 24-month window ### Positive observations - ✅ EXPLAIN запущен live на проде — fact-based PR body - ✅ UNIQUE INDEX `mv_layout_velocity_pk` корректен для CONCURRENTLY - ✅ `BEGIN; COMMIT;` обёртка, sequential `94_*.sql` номер, type casts корректны - ✅ Cyrillic normalisation consistency с PR A schemas - ✅ Helper Python: type hints, logger вместо print, sync Session правильно ### Blast radius - **Risk**: Low (new MV, no existing consumers, isolated read-only joins) - **Reversibility**: Easy (revert PR → manual `DROP MV` через MCP) - **Merge window**: anytime (нет user-facing endpoint в этом PR) ### Next steps 1. Worker (database-expert / backend-engineer): fix 🟠 high + 🟡 medium #1,#4,#5,#6 (минимум) 2. Re-push → re-poll 3. Post-merge: vault entry для studio normalisation decision 4. **Auto-merge BLOCKED** by scope policy (`data/sql/**` + `backend/services/**`) — human merge required даже после fixes

lekss361 added 1 commit 2026-05-16 08:18:36 +00:00

fix(analytics): address review-bot PR B fixes (#113 ) 46c2a84ea4

SQL (data/sql/94_mv_layout_velocity.sql):
- 🟠 idempotency WARN comment перед DROP CASCADE
- 🟡 WITH NO DATA + REFRESH after CREATE INDEX (меньше lock duration)
- 🟡 WHERE cm.objective_group = 'Екатеринбург' (cross-region Cartesian защита)
- 🟡 COMMENT ON MATERIALIZED VIEW с источниками + refresh механизмом

Python (services/site_finder/layout_velocity_refresh.py):
- 🟡 db.commit() после REFRESH (visibility через connection pool)
- 🟡 убрать f-string в SQL — две явных text() ветки
- 🟡 try/except OperationalError fallback на non-concurrent

lekss361 commented

2026-05-16 08:22:54 +00:00

Author

Owner

Deep Code Review — re-review verdict (head `46c2a84`)

Summary

Status: ✅ APPROVE
Previous verdict: 🟡 MEDIUM at 144970a (1 🟠 + 6 🟡)
Files reviewed: 2 (P0: data/sql/94_mv_layout_velocity.sql, P1: backend/app/services/site_finder/layout_velocity_refresh.py)
Lines: +147 / -0 (fixup +33 vs previous head)
Auto-merge: ❌ BLOCKED by scope policy — touches data/sql/** + backend/app/services/**

Fix verification vs previous review

#	Severity	Item	Fix at `46c2a84`	Status
🟠	high	DROP CASCADE без safety guardrail	`94_mv_layout_velocity.sql:23-29` — explicit `WARN:` comment block: re-apply risk + `_schema_migrations` mitigation + reminder про concurrently=False для first refresh после re-apply	✅ fixed
🟡 #1	WITH DATA → WITH NO DATA + explicit REFRESH	`94_mv_layout_velocity.sql:71` — `WITH NO DATA`, indexes created на пустой MV (instant), `REFRESH MATERIALIZED VIEW` после индексов	✅ fixed
🟡 #2	JOIN losing rows для non-ЕКБ objective_group	`94_mv_layout_velocity.sql:67` — `AND cm.objective_group = 'Екатеринбург'` + комментарий "защита от cross-region Cartesian при future multi-city"	✅ fixed
🟡 #3	COMMENT ON MATERIALIZED VIEW	`94_mv_layout_velocity.sql:86-89` — sources + refresh механизм	✅ fixed
🟡 #4	db.commit() в helper	`layout_velocity_refresh.py:42, 51` — `db.commit()` после обеих REFRESH веток + `db.rollback()` перед fallback	✅ fixed
🟡 #5	f-string → if/else literal SQL	`layout_velocity_refresh.py:39-42` — explicit `if concurrently:` / `else:` branches, две статичных `text()` literal	✅ fixed
🟡 #6	try/except для not-populated CONCURRENTLY	`layout_velocity_refresh.py:43-52` — `except OperationalError` + substring match `"cannot refresh materialized view"` + warning log + fallback на non-concurrent	✅ fixed

All 7 prior items addressed. No new issues introduced.

Live EXPLAIN (re-verified на проде)

Plan with new WHERE cm.objective_group = 'Екатеринбург':

Aggregate (cost 2374..2605, rows 459) [actual 35.9 ms]
  → Hash Join (cost 5.85..1818.62) [actual 23.15 ms, 9287 rows]
    → Seq Scan objective_corpus_room_month [19738 rows, Filter: report_month >= ...]
    → Hash → Seq Scan objective_complex_mapping [114 rows, Filter: domrf_obj_id IS NOT NULL AND objective_group = 'Екатеринбург']

Execution: 36ms — no regression vs previous review baseline.
New objective_group filter — no-op today (1 group exists, 114 EKB rows, 0 nulls) — pure future-proofing.

Positive observations

WITH NO DATA + post-index REFRESH pattern correctly minimises lock duration during initial CREATE.
Helper's fallback path correctly does db.rollback() before retry — no transaction state leak.
OperationalError substring match on "cannot refresh materialized view" — slightly fragile (locale-dependent), but Postgres emits this in English regardless of lc_messages for SQLSTATE 55000. Acceptable.
'Екатеринбург' hardcoded literal — defensible scope-limited choice for MVP (EKB-only), matches existing data.

Action

✅ APPROVE verdict, но auto-merge BLOCKED per scope policy (touches data/sql/** + backend/app/services/**).

@lekss361 — approved, needs human merge per scope policy. После merge: deploy.yml auto-apply migration → qa-tester smoke: SELECT COUNT(*) FROM mv_layout_velocity ≥ 400.

Risk / blast radius

Risk: Low — new MV, no existing dependents, idempotent re-apply blocked by _schema_migrations (с safety comment как fallback documentation).
Reversibility: Easy — DROP MATERIALIZED VIEW IF EXISTS mv_layout_velocity CASCADE; (no data loss, source tables untouched).
Recommended merge window: anytime.

## Deep Code Review — re-review verdict (head `46c2a84`) ### Summary - **Status**: ✅ APPROVE - **Previous verdict**: 🟡 MEDIUM at `144970a` (1 🟠 + 6 🟡) - **Files reviewed**: 2 (P0: `data/sql/94_mv_layout_velocity.sql`, P1: `backend/app/services/site_finder/layout_velocity_refresh.py`) - **Lines**: +147 / -0 (fixup +33 vs previous head) - **Auto-merge**: ❌ BLOCKED by scope policy — touches `data/sql/**` + `backend/app/services/**` ### Fix verification vs previous review | # | Severity | Item | Fix at `46c2a84` | Status | |---|---|---|---|---| | 🟠 | high | DROP CASCADE без safety guardrail | `94_mv_layout_velocity.sql:23-29` — explicit `WARN:` comment block: re-apply risk + `_schema_migrations` mitigation + reminder про concurrently=False для first refresh после re-apply | ✅ fixed | | 🟡 #1 | WITH DATA → WITH NO DATA + explicit REFRESH | `94_mv_layout_velocity.sql:71` — `WITH NO DATA`, indexes created на пустой MV (instant), `REFRESH MATERIALIZED VIEW` после индексов | ✅ fixed | | 🟡 #2 | JOIN losing rows для non-ЕКБ objective_group | `94_mv_layout_velocity.sql:67` — `AND cm.objective_group = 'Екатеринбург'` + комментарий "защита от cross-region Cartesian при future multi-city" | ✅ fixed | | 🟡 #3 | COMMENT ON MATERIALIZED VIEW | `94_mv_layout_velocity.sql:86-89` — sources + refresh механизм | ✅ fixed | | 🟡 #4 | db.commit() в helper | `layout_velocity_refresh.py:42, 51` — `db.commit()` после обеих REFRESH веток + `db.rollback()` перед fallback | ✅ fixed | | 🟡 #5 | f-string → if/else literal SQL | `layout_velocity_refresh.py:39-42` — explicit `if concurrently:` / `else:` branches, две статичных `text()` literal | ✅ fixed | | 🟡 #6 | try/except для not-populated CONCURRENTLY | `layout_velocity_refresh.py:43-52` — `except OperationalError` + substring match `"cannot refresh materialized view"` + warning log + fallback на non-concurrent | ✅ fixed | **All 7 prior items addressed. No new issues introduced.** ### Live EXPLAIN (re-verified на проде) Plan with new `WHERE cm.objective_group = 'Екатеринбург'`: ``` Aggregate (cost 2374..2605, rows 459) [actual 35.9 ms] → Hash Join (cost 5.85..1818.62) [actual 23.15 ms, 9287 rows] → Seq Scan objective_corpus_room_month [19738 rows, Filter: report_month >= ...] → Hash → Seq Scan objective_complex_mapping [114 rows, Filter: domrf_obj_id IS NOT NULL AND objective_group = 'Екатеринбург'] ``` - **Execution: 36ms** — no regression vs previous review baseline. - New `objective_group` filter — no-op today (1 group exists, 114 EKB rows, 0 nulls) — pure future-proofing. ### Positive observations - `WITH NO DATA` + post-index `REFRESH` pattern correctly minimises lock duration during initial CREATE. - Helper's fallback path correctly does `db.rollback()` before retry — no transaction state leak. - `OperationalError` substring match on `"cannot refresh materialized view"` — slightly fragile (locale-dependent), but Postgres emits this in English regardless of `lc_messages` for SQLSTATE 55000. Acceptable. - `'Екатеринбург'` hardcoded literal — defensible scope-limited choice for MVP (EKB-only), matches existing data. ### Action ✅ **APPROVE** verdict, но **auto-merge BLOCKED per scope policy** (touches `data/sql/**` + `backend/app/services/**`). @lekss361 — approved, needs human merge per scope policy. После merge: deploy.yml auto-apply migration → qa-tester smoke: `SELECT COUNT(*) FROM mv_layout_velocity` ≥ 400. ### Risk / blast radius - **Risk**: Low — new MV, no existing dependents, idempotent re-apply blocked by `_schema_migrations` (с safety comment как fallback documentation). - **Reversibility**: Easy — `DROP MATERIALIZED VIEW IF EXISTS mv_layout_velocity CASCADE;` (no data loss, source tables untouched). - **Recommended merge window**: anytime.