Light1YT
0f194de069
Migration 127: durable, versioned, re-openable persistence for Site Finder analysis runs (the §22 API result + forecast/recommendation/report snapshot). One row = one completed analysis of a parcel (frozen result jsonb = SiteFinderReport.as_dict() #987, schema_version for versioned re-open). Append-mostly (history/versioning — never overwrites prior runs → TABLE not MV). Replaces the last_analysis_date=None placeholder (parcels.py:1026,1092): "date of last analysis" + "current analysis of parcel" now come from v_analysis_runs_latest (DISTINCT ON cad_num, max created_at, deterministic id-DESC tiebreak — mirrors v_supply_layers_latest m.125). created_by = X-Authenticated-User identity, nullable TEXT, NO FK (no users table; precedent m.90/m.119; background/Celery runs have no author). result/ params/segment as JSONB (report shape evolves); district/confidence/status denormalized out for filter/sort. status + confidence CHECK-guarded. Idempotent (CREATE TABLE/INDEX IF NOT EXISTS + CREATE OR REPLACE VIEW). Dry-run-verified vs prod catalog (DDL applies clean in rolled-back txn; DISTINCT ON latest-wins confirmed). Foundation for #994 persist + #992 contract. Closes #993. Refs #961.
146 lines
14 KiB
PL/PgSQL
146 lines
14 KiB
PL/PgSQL
-- 127_analysis_runs.sql
|
||
-- #993 (961-C2, Site Finder v2 / «GG-форсайт» ТЗ §22, EPIC 17 «Анализ-раны / история») — PR: durable analysis-run store.
|
||
--
|
||
-- Создаёт долговременную, версионированную, ПЕРЕ-открываемую таблицу analysis_runs —
|
||
-- frozen-снапшот одного завершённого анализа участка (результат §22 API + прогноз/
|
||
-- рекомендация/отчёт), плюс read-view v_analysis_runs_latest (последний ран на участок).
|
||
-- Это фундамент: #994 строит persist-логику поверх неё, #992 — контракт; UI/чат/экспорт
|
||
-- ПЕРЕ-открывают раны отсюда БЕЗ пересчёта (история / версионирование / re-open).
|
||
--
|
||
-- Что это ЗАМЕНЯЕТ:
|
||
-- parcel.py:387 / parcels.py:1026,1092 — заглушку last_analysis_date=None
|
||
-- (placeholder «real after B2 auth #307»). Теперь «дата последнего анализа» и «текущий
|
||
-- анализ участка» берутся из v_analysis_runs_latest (max(created_at) на cad_num).
|
||
--
|
||
-- Что хранит строка (grain: ОДНА строка = ОДИН ран анализа участка):
|
||
-- frozen-снапшот завершённого анализа — чтобы history/versioning/re-open/export
|
||
-- работали БЕЗ повторного вычисления. result jsonb = SiteFinderReport.as_dict()
|
||
-- (backend/app/services/forecasting/report.py, #987) ЛИБО dict результата analyze
|
||
-- (POST /parcels/{cad_num}/analyze). schema_version = report._SCHEMA_VERSION ("1.0") —
|
||
-- для версионированного re-open (бамп при несовместимом изменении формы as_dict()).
|
||
-- advisory ВСЕГДА TRUE — весь форсайт-стек советующий (зеркало report._ADVISORY).
|
||
--
|
||
-- ПОЧЕМУ TABLE (append-mostly), а не materialized view:
|
||
-- раны — это ИСТОРИЯ. Каждый запуск analyze добавляет НОВУЮ строку (никогда не затирает
|
||
-- прошлую) → версионирование / re-open прежних ранов / аудит. MV (выводимый запросом)
|
||
-- тут невозможен: result/params/segment — это снапшот вычисленного выхода, не агрегат
|
||
-- источников. id BIGSERIAL — суррогатный PK; «последний ран на участок» — по индексу
|
||
-- (cad_num, created_at DESC), а не по UNIQUE-ключу (намеренно НЕТ dedup: два анализа
|
||
-- одного участка в один день — две легитимные версии, обе сохраняются).
|
||
--
|
||
-- created_by — БЕЗ FK (намеренно): в системе НЕТ таблицы users. Идентичность приходит
|
||
-- из Caddy basic_auth → заголовок X-Authenticated-User → RBAC (auth/roles.yaml,
|
||
-- backend/app/core/auth.py). Прецедент в репо — user-scoped таблицы хранят identity
|
||
-- плоским TEXT без FK: user_weight_profiles.user_id (м.90), parcel_user_status.user_id
|
||
-- (м.119). created_by NULLABLE TEXT: фоновый/системный ран (Celery) или header-less
|
||
-- путь не обязан иметь идентичность — re-open не должен падать на NULL автора.
|
||
--
|
||
-- result/params/segment — JSONB (а не jsonb-в-text / отдельные колонки): форма отчёта
|
||
-- §13/§22 эволюционирует (8 секций, под-сервисные as_dict()), нормализовать её в
|
||
-- реляционные колонки преждевременно и хрупко. Денормализуем НАРУЖУ лишь то, по чему
|
||
-- фильтруют/сортируют без JSON-разбора: district, confidence, status, advisory.
|
||
--
|
||
-- Deploy: auto-applied by deploy.yml через _schema_migrations tracking (ровно один раз по NN).
|
||
-- Idempotent (acceptance #1 — должен ре-applied чисто ДВАЖДЫ без ошибки): CREATE TABLE/INDEX
|
||
-- IF NOT EXISTS + CREATE OR REPLACE VIEW. CHECK'и объявлены ИНЛАЙН в CREATE TABLE: на уже
|
||
-- существующей таблице CREATE TABLE IF NOT EXISTS пропускает весь statement целиком, поэтому
|
||
-- инлайн-CHECK'и НЕ передобавляются (re-apply safe; не нужен guarded DO-блок, как в м.125,
|
||
-- т.к. здесь НЕТ отдельно добавляемых UNIQUE/FK — всё в инлайн CREATE TABLE).
|
||
-- Migration order: эта SQL-миграция применяется ПЕРВОЙ; persist-логика #994 / контракт #992 /
|
||
-- UI/чат/экспорт — следом. НИКАКИХ строк здесь не вставляется (раны пишет #994 на analyze).
|
||
-- НИКАКИХ DROP/TRUNCATE.
|
||
|
||
BEGIN;
|
||
|
||
-- ── 1. Таблица analysis_runs ─────────────────────────────────────────────────────
|
||
-- PK и CHECK'и объявлены инлайн (re-apply пропускает весь CREATE TABLE на сущ. таблице).
|
||
-- Плохие данные writer'а (#994) должны падать ГРОМКО на write через CHECK, не молча.
|
||
CREATE TABLE IF NOT EXISTS analysis_runs (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
cad_num TEXT NOT NULL, -- проанализированный участок (кадастровый номер)
|
||
district TEXT, -- денормализовано из result.meta.district (фильтрация без JSON-разбора)
|
||
segment JSONB, -- спецификация сегмента (class/room/price), если задан; NULL = без сегмента
|
||
params JSONB NOT NULL DEFAULT '{}', -- параметры запроса (horizon_months, profile_id, веса POI, ...)
|
||
result JSONB NOT NULL, -- снапшот SiteFinderReport.as_dict() (#987) / dict результата analyze
|
||
schema_version TEXT NOT NULL, -- report._SCHEMA_VERSION ("1.0") — для версионированного re-open
|
||
advisory BOOLEAN NOT NULL DEFAULT TRUE, -- весь форсайт-стек советующий (зеркало report._ADVISORY); всегда TRUE
|
||
confidence TEXT, -- отчётный уровень high/medium/low (денормализовано из result.confidence)
|
||
status TEXT NOT NULL DEFAULT 'complete', -- complete | partial | error
|
||
created_by TEXT, -- идентичность X-Authenticated-User (NULLABLE; БЕЗ FK — нет таблицы users)
|
||
created_at timestamptz NOT NULL DEFAULT now(), -- момент сохранения рана (powers last_analysis_date + latest-view)
|
||
-- status ограничен 3 known-значениями (зеркало форсайт-стека complete|partial|error):
|
||
CONSTRAINT analysis_runs_status_chk
|
||
CHECK (status IN ('complete', 'partial', 'error')),
|
||
-- confidence NULLABLE; если задана — только high/medium/low (зеркало report ReportConfidenceLevel):
|
||
CONSTRAINT analysis_runs_confidence_chk
|
||
CHECK (confidence IS NULL OR confidence IN ('high', 'medium', 'low'))
|
||
);
|
||
|
||
COMMENT ON TABLE analysis_runs IS
|
||
'Durable версионированный store анализ-ранов Site Finder (#993, ТЗ §22, EPIC 17). '
|
||
'Grain: одна строка = один завершённый ран анализа участка (frozen-снапшот §22-результата '
|
||
'+ прогноз/рекомендация/отчёт). result jsonb = SiteFinderReport.as_dict() (#987) или dict '
|
||
'результата analyze; schema_version = report _SCHEMA_VERSION для версионированного re-open. '
|
||
'Append-mostly (история/версионирование — НЕ затираем прошлые раны, поэтому TABLE, не MV). '
|
||
'Заменяет заглушку last_analysis_date (parcels.py:1026,1092). created_by = X-Authenticated-User '
|
||
'без FK (нет таблицы users; прецедент — м.90/м.119). Последний ран на участок — v_analysis_runs_latest. '
|
||
'persist-логику пишет #994, контракт — #992, re-open — UI/чат/экспорт.';
|
||
|
||
COMMENT ON COLUMN analysis_runs.cad_num IS 'Кадастровый номер проанализированного участка (NOT NULL; не FK — cad-номера приходят и до загрузки участка в cad_parcels).';
|
||
COMMENT ON COLUMN analysis_runs.result IS 'Снапшот SiteFinderReport.as_dict() (#987, 8 секций §13) или dict результата POST /analyze. JSONB — форма §22-отчёта эволюционирует, не нормализуем.';
|
||
COMMENT ON COLUMN analysis_runs.schema_version IS 'report._SCHEMA_VERSION ("1.0") на момент рана — для версионированного re-open (бамп при несовместимом изменении формы as_dict()).';
|
||
COMMENT ON COLUMN analysis_runs.advisory IS 'Весь форсайт-стек советующий (зеркало report._ADVISORY). Всегда TRUE — ран НЕ основание для инвест-решения.';
|
||
COMMENT ON COLUMN analysis_runs.confidence IS 'Отчётный уровень high|medium|low, денормализован из result.confidence (CHECK-ограничено; NULL = не оценено).';
|
||
COMMENT ON COLUMN analysis_runs.status IS 'complete|partial|error (CHECK-ограничено). partial = graceful-частичный анализ (дух форсайт-стека); error = ран зафиксирован с ошибкой.';
|
||
COMMENT ON COLUMN analysis_runs.created_by IS 'Идентичность X-Authenticated-User (Caddy basic_auth → RBAC). NULLABLE, БЕЗ FK — нет таблицы users; фоновый/системный ран автора не имеет.';
|
||
COMMENT ON COLUMN analysis_runs.created_at IS 'Момент сохранения рана. Powers last_analysis_date (max на cad_num) + latest-view; tiebreak с id DESC.';
|
||
|
||
-- ── 2. Индексы ───────────────────────────────────────────────────────────────────
|
||
-- Primary read-path: «последний ран на участок» (powers last_analysis_date + re-open
|
||
-- текущего анализа + история по участку). Compound (cad_num, created_at DESC) — точечный
|
||
-- фильтр по участку + сортировка/«самый свежий» без отдельного сорта; используется
|
||
-- DISTINCT ON во v_analysis_runs_latest.
|
||
CREATE INDEX IF NOT EXISTS idx_analysis_runs_cad_created
|
||
ON analysis_runs (cad_num, created_at DESC);
|
||
-- Глобальная лента недавних ранов (дашборд «последние анализы» / housekeeping по дате).
|
||
CREATE INDEX IF NOT EXISTS idx_analysis_runs_created
|
||
ON analysis_runs (created_at DESC);
|
||
-- Фильтр по району (денормализованный district) + свежесть — для районных списков ранов.
|
||
-- Partial (district IS NOT NULL): region-wide/без-района раны индекс не раздувают.
|
||
CREATE INDEX IF NOT EXISTS idx_analysis_runs_district_created
|
||
ON analysis_runs (district, created_at DESC) WHERE district IS NOT NULL;
|
||
|
||
-- ── 3. Read-view: последний ран на участок ───────────────────────────────────────
|
||
-- Canonical read-path для last_analysis_date + «текущий анализ участка» (UI/чат/экспорт).
|
||
-- DISTINCT ON (cad_num) → ровно одна строка на участок — самая свежая по created_at.
|
||
-- Tiebreak ПОЛНОСТЬЮ детерминирован: при равном created_at берём бОльший id (последний
|
||
-- вставленный ран). DISTINCT ON требует, чтобы ведущие ORDER BY-выражения совпадали с
|
||
-- DISTINCT-ключом (cad_num), затем — порядок отбора «последнего» (created_at DESC, id DESC).
|
||
-- Зеркалит v_supply_layers_latest (м.125): DISTINCT ON + детерминир. tiebreak по id DESC.
|
||
CREATE OR REPLACE VIEW v_analysis_runs_latest AS
|
||
SELECT DISTINCT ON (cad_num)
|
||
id,
|
||
cad_num,
|
||
district,
|
||
segment,
|
||
params,
|
||
result,
|
||
schema_version,
|
||
advisory,
|
||
confidence,
|
||
status,
|
||
created_by,
|
||
created_at
|
||
FROM analysis_runs
|
||
ORDER BY
|
||
cad_num,
|
||
created_at DESC,
|
||
id DESC;
|
||
|
||
COMMENT ON VIEW v_analysis_runs_latest IS
|
||
'Последний ран analysis_runs на участок (DISTINCT ON cad_num, max(created_at), '
|
||
'детерминир. tiebreak id DESC). Canonical read-path для last_analysis_date '
|
||
'(parcels.py:1026,1092) + «текущий анализ участка» (UI/чат/экспорт, re-open). '
|
||
'Пустая таблица → 0 строк.';
|
||
|
||
COMMIT;
|