177 lines
13 KiB
PL/PgSQL
177 lines
13 KiB
PL/PgSQL
-- 125_supply_layers.sql
|
||
-- #950 (Site Finder v2 / 3-layer склад предложения, EPIC 6, «GG-форсайт») — PR B: durable supply store.
|
||
--
|
||
-- Создаёт долговременную таблицу supply_layers — единый склад для 3 слоёв будущего/скрытого
|
||
-- предложения по districts (и опционально per-ЖК), плюс read-view v_supply_layers_latest
|
||
-- (свежий снапшот на логический ключ). Это фундамент future-supply-pressure (#950 Step 6).
|
||
--
|
||
-- layer=1 (open) — открытое предложение в текущей продаже (Objective / выставлено сейчас).
|
||
-- layer=2 (hidden) — скрытый запас застройщика: flat_count − free_flats по ПД (#950 PR A / м.124).
|
||
-- layer=3 (future) — будущее предложение, ещё НЕ на рынке: граддок-stubs (#956, позже),
|
||
-- multiphase-проекты ДОМ.РФ, ручные insider-строки.
|
||
--
|
||
-- Почему TABLE, а не materialized view:
|
||
-- Layer1/Layer2 ПЕРЕсчитываются worker'ом (#950 Step 5) из источников и UPSERT-ятся сюда.
|
||
-- НО Layer3 держит строки, КОТОРЫЕ НЕЛЬЗЯ вывести запросом — граддок-stubs (#956) и ручной
|
||
-- insider-ввод. MV каждый refresh затёр бы их. Поэтому это реальная таблица: worker UPSERT-ит
|
||
-- свои layer1/2 строки, layer3 живёт независимо (вставляется отдельно, переживает пересчёт).
|
||
-- Future-supply-pressure индекс считается НА ЛЕТУ в #950 Step 6 (future_supply.py) — здесь НЕ хранится.
|
||
--
|
||
-- Зерно (grain): primary — district_name; optional — complex_id (per-ЖК, в основном L3-stubs /
|
||
-- reviewed L2). NO per-flat строк (это склад агрегатов, не сделок). district_name/complex_id/
|
||
-- obj_class — NULLABLE (region-wide / класс-agnostic строки допустимы).
|
||
--
|
||
-- Зависимости:
|
||
-- • complexes(id) (м.73, BIGSERIAL PK) — FK complex_id → complexes(id) ON DELETE SET NULL
|
||
-- (complex_id — опциональное обогащение; удаление ЖК НЕ должно удалять supply-строку).
|
||
-- • PostgreSQL 16 (postgis/postgis:16-3.4, docker-compose.prod.yml) — нужен для
|
||
-- UNIQUE NULLS NOT DISTINCT (PG15+). Прецедент в репо: м.110 (uq_infra_dedupe).
|
||
--
|
||
-- Логический UPSERT-ключ (что таргетит ON CONFLICT в worker'е #950 Step 5):
|
||
-- (layer, district_name, complex_id, obj_class, source, snapshot_date)
|
||
-- КРИТИЧНО: district_name/complex_id/obj_class NULLABLE. По умолчанию Postgres в UNIQUE
|
||
-- считает NULL DISTINCT → (а) дубли region-wide строк (district_name IS NULL) проскочили бы,
|
||
-- (б) ON CONFLICT НЕ матчил бы их (NULL <> NULL). Поэтому ключ объявлен
|
||
-- UNIQUE NULLS NOT DISTINCT — NULL'ы сравниваются как РАВНЫЕ, дубли ловятся, ON CONFLICT
|
||
-- по этому column-list срабатывает. Constraint именованный (uq_supply_layers_logical) —
|
||
-- ON CONFLICT (<column list>) таргетит его по списку колонок (имя для diagnostics/idempotency).
|
||
--
|
||
-- Deploy: auto-applied by deploy.yml через _schema_migrations tracking (ровно один раз по NN).
|
||
-- Idempotent (acceptance #1 — должен ре-applied чисто ДВАЖДЫ без ошибки): CREATE TABLE/INDEX
|
||
-- IF NOT EXISTS + CREATE OR REPLACE VIEW. Ловушка идемпотентности — повторное добавление
|
||
-- UNIQUE и FK (их нельзя добавить дважды): обёрнуты в guarded DO-блоки с проверкой
|
||
-- pg_constraint (прецедент — м.40). Re-apply конвергентен даже если таблица была создана
|
||
-- руками без этих constraint'ов.
|
||
-- Migration order: эта SQL-миграция применяется ПЕРВОЙ; worker/reader (#950 Step 5/6) — следом.
|
||
-- НИКАКИХ строк здесь не вставляется (layer3-stubs приходят из #956; layer1/2 — из worker'а).
|
||
|
||
BEGIN;
|
||
|
||
-- ── 1. Таблица supply_layers ───────────────────────────────────────────────────
|
||
-- PK и CHECK-и объявлены инлайн: CREATE TABLE IF NOT EXISTS на существующей таблице
|
||
-- пропускает весь statement целиком, поэтому инлайн-CHECK'и НЕ передобавляются (re-apply safe).
|
||
-- UNIQUE и FK вынесены в DO-блоки ниже (их нельзя добавить дважды → guard по pg_constraint).
|
||
CREATE TABLE IF NOT EXISTS supply_layers (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
layer SMALLINT NOT NULL, -- 1=open | 2=hidden | 3=future
|
||
district_name TEXT, -- primary grain (NULLABLE: region-wide строки)
|
||
complex_id BIGINT, -- optional per-ЖК grain (L3-stubs / reviewed L2); FK complexes(id)
|
||
obj_class TEXT, -- эконом..премиум (NULLABLE: класс-agnostic)
|
||
units_estimate INTEGER, -- оценка кол-ва квартир (NULLABLE)
|
||
area_estimate NUMERIC, -- оценка площади, м² (NULLABLE)
|
||
expected_online_date DATE, -- когда предложение выйдет на рынок (L3); NULL для L1/L2
|
||
source TEXT NOT NULL, -- 'objective'|'domrf_declared'|'domrf_multiphase'|'graddoc_stub'|'insider_manual'
|
||
confidence TEXT NOT NULL, -- 'high'|'medium'|'low' (зеркало market_metrics.py)
|
||
method TEXT, -- короткая provenance-строка (как считалось/откуда)
|
||
snapshot_date DATE NOT NULL DEFAULT CURRENT_DATE, -- дата пересчёта/ввода; часть логического ключа
|
||
computed_at timestamptz NOT NULL DEFAULT now(), -- момент записи строки (tiebreak для latest-view)
|
||
-- CHECK'и: плохие данные worker'а должны падать ГРОМКО на write.
|
||
CONSTRAINT supply_layers_layer_chk
|
||
CHECK (layer IN (1, 2, 3)),
|
||
CONSTRAINT supply_layers_confidence_chk
|
||
CHECK (confidence IN ('high', 'medium', 'low')),
|
||
-- source ограничен 5 known-значениями — новый источник = осознанное расширение CHECK
|
||
-- отдельной миграцией (не молчаливый free-text). Если появится потребность в произвольном
|
||
-- источнике — этот CHECK снять отдельной ревизией с комментарием.
|
||
CONSTRAINT supply_layers_source_chk
|
||
CHECK (source IN ('objective', 'domrf_declared', 'domrf_multiphase',
|
||
'graddoc_stub', 'insider_manual'))
|
||
);
|
||
|
||
COMMENT ON TABLE supply_layers IS
|
||
'Durable 3-layer склад предложения (#950 EPIC 6, GG-форсайт). layer 1=open / 2=hidden / '
|
||
'3=future. Grain: district_name (+ optional complex_id per-ЖК). NO per-flat строк. '
|
||
'Layer1/2 UPSERT-ятся worker''ом (#950 Step5) по uq_supply_layers_logical; layer3 — '
|
||
'граддок-stubs (#956) + ручной insider-ввод (поэтому TABLE, не MV — refresh не должен '
|
||
'затирать невыводимые строки). Future-supply-pressure считается на лету (#950 Step6), тут НЕ '
|
||
'хранится. Читать свежий снапшот через v_supply_layers_latest.';
|
||
|
||
COMMENT ON COLUMN supply_layers.layer IS '1=open (в продаже) | 2=hidden (запас ПД) | 3=future (ещё не на рынке)';
|
||
COMMENT ON COLUMN supply_layers.expected_online_date IS 'Когда предложение выйдет на рынок (L3-прогноз); NULL для L1/L2 (уже/скрыто на рынке)';
|
||
COMMENT ON COLUMN supply_layers.source IS 'objective|domrf_declared|domrf_multiphase|graddoc_stub|insider_manual (CHECK-ограничено)';
|
||
COMMENT ON COLUMN supply_layers.confidence IS 'high|medium|low — зеркало market_metrics.py (CHECK-ограничено)';
|
||
COMMENT ON COLUMN supply_layers.snapshot_date IS 'Дата пересчёта/ввода; часть логического UPSERT-ключа. Latest-view берёт max(snapshot_date) на ключ.';
|
||
|
||
-- ── 2. Логический UNIQUE-ключ (NULLS NOT DISTINCT, PG15+) ──────────────────────
|
||
-- Цель ON CONFLICT для worker'а (#950 Step5). NULLS NOT DISTINCT — чтобы region-wide
|
||
-- строки (district_name/complex_id/obj_class IS NULL) НЕ дублировались и ON CONFLICT их матчил
|
||
-- (иначе NULL <> NULL → дубли + промах UPSERT). Guard по pg_constraint → idempotent re-apply
|
||
-- (constraint нельзя добавить дважды). Прецедент паттерна: м.110 (uq_infra_dedupe), м.40 (FK guard).
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'uq_supply_layers_logical'
|
||
) THEN
|
||
ALTER TABLE supply_layers
|
||
ADD CONSTRAINT uq_supply_layers_logical
|
||
UNIQUE NULLS NOT DISTINCT
|
||
(layer, district_name, complex_id, obj_class, source, snapshot_date);
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- ── 3. FK complex_id → complexes(id) (ON DELETE SET NULL) ──────────────────────
|
||
-- complex_id — опциональное per-ЖК обогащение; удаление ЖК НЕ должно удалять supply-строку,
|
||
-- поэтому SET NULL (не CASCADE). Guard по pg_constraint → re-apply не падает «constraint already
|
||
-- exists». DELETE CASCADE здесь был бы неверен: потеряли бы агрегат предложения из-за чистки ЖК.
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_constraint WHERE conname = 'fk_supply_layers_complex'
|
||
) THEN
|
||
ALTER TABLE supply_layers
|
||
ADD CONSTRAINT fk_supply_layers_complex
|
||
FOREIGN KEY (complex_id) REFERENCES complexes(id) ON DELETE SET NULL;
|
||
END IF;
|
||
END
|
||
$$;
|
||
|
||
-- ── 4. Индексы ─────────────────────────────────────────────────────────────────
|
||
-- Primary read-path: фильтр по слою + району + дате (consumer'ы Step6 / UI / forecast).
|
||
CREATE INDEX IF NOT EXISTS idx_supply_layers_layer_district_date
|
||
ON supply_layers (layer, district_name, snapshot_date DESC);
|
||
-- FK-lookups + per-ЖК выборки (и ускоряет ON DELETE SET NULL).
|
||
CREATE INDEX IF NOT EXISTS idx_supply_layers_complex
|
||
ON supply_layers (complex_id) WHERE complex_id IS NOT NULL;
|
||
-- snapshot_date в одиночку — для latest-view (отбор max-снапшота) и housekeeping по дате.
|
||
CREATE INDEX IF NOT EXISTS idx_supply_layers_snapshot_date
|
||
ON supply_layers (snapshot_date DESC);
|
||
|
||
-- ── 5. Read-view: свежий снапшот на логический ключ ────────────────────────────
|
||
-- Что читают #950 Step6 (future_supply.py) и любой UI/forecast-consumer. Возвращает по одной
|
||
-- строке на логический ключ БЕЗ snapshot_date (layer, district_name, complex_id, obj_class,
|
||
-- source) — самую свежую по snapshot_date. Tiebreak полностью детерминирован: при равной
|
||
-- snapshot_date берём бОльший computed_at, затем бОльший id (на случай двух записей в день).
|
||
-- DISTINCT ON требует, чтобы ведущие ORDER BY-выражения совпадали с DISTINCT-ключом.
|
||
CREATE OR REPLACE VIEW v_supply_layers_latest AS
|
||
SELECT DISTINCT ON (layer, district_name, complex_id, obj_class, source)
|
||
id,
|
||
layer,
|
||
district_name,
|
||
complex_id,
|
||
obj_class,
|
||
units_estimate,
|
||
area_estimate,
|
||
expected_online_date,
|
||
source,
|
||
confidence,
|
||
method,
|
||
snapshot_date,
|
||
computed_at
|
||
FROM supply_layers
|
||
ORDER BY
|
||
layer,
|
||
district_name,
|
||
complex_id,
|
||
obj_class,
|
||
source,
|
||
snapshot_date DESC,
|
||
computed_at DESC,
|
||
id DESC;
|
||
|
||
COMMENT ON VIEW v_supply_layers_latest IS
|
||
'Свежий снапшот supply_layers на логический ключ (layer, district_name, complex_id, '
|
||
'obj_class, source) — max(snapshot_date), детерминир. tiebreak computed_at→id DESC. '
|
||
'Это canonical read-path для future-supply (#950 Step6) и UI/forecast. Пустая таблица → 0 строк.';
|
||
|
||
COMMIT;
|