aaa5d44e77
backend/app/services/scrapers/objective.py: ObjectiveClient — GetToken (Bearer cached в Redis 25 min TTL) + GetReport v2 с UseDdu/UseDkp. 4 высокоуровневых метода: report_corpuses_summary, report_lots_summary, report_corpuses_per_flat, report_lots_per_flat. Retry 401/429/5xx, rate-limit 500ms, brotli. backend/app/workers/tasks/scrape_objective.py: Celery task sync_objective_group — еженедельно тянет 4 канон. отчёта по группе, raw payload в objective_raw_reports. celery_app.py: +include + beat «0 5 * * mon». data/sql/68_schema_objective.sql: 6 таблиц — runs, raw_reports (jsonb), oks (готовые ТЭП + escrow/debt), lots, lots_history (per-flat per-day), complex_mapping (Objective ↔ domrf_kn_objects, is_reviewed workflow). data/sql/69_objective_smoke.py: stand-alone GetToken + 4 отчёта → data/raw/objective_smoke/<ts>/. Используется один раз чтобы понять реальную схему payload перед написанием parser-слоя. config: OBJECTIVE_API_KEY, OBJECTIVE_DEFAULT_GROUP=Екатеринбург, OBJECTIVE_SYNC_CRON='0 5 * * mon'.
333 lines
20 KiB
SQL
333 lines
20 KiB
SQL
-- Storage для данных API Объектива (api.objctv.ru).
|
||
--
|
||
-- Схема ВЫРОВНЕНА по реальной форме payload, наблюдаемой на пробных вызовах
|
||
-- (data/raw/objective_test/20260507_*/), а не по гипотезам.
|
||
--
|
||
-- Реальная форма данных:
|
||
-- 1. /v2/Report/GetReport?ReportType=Сводные&ReportName=Корпуса
|
||
-- → {"result": [ {Месяц, Проект, Девелопер, Район, Класс, Корпус,
|
||
-- "Количество комнат (Данные Объектива)",
|
||
-- Лотов по ПД шт, Площадь по ПД м2,
|
||
-- Сделки ДДУ/ДКП/всего × {count, count_priced, vol, vol_priced,
|
||
-- sum_priced_млн, avg_price_per_m2_тыс,
|
||
-- avg_area},
|
||
-- Объем предложения шт/м2, Сумм. стоимость в продаже млн,
|
||
-- Средняя цена м2 в продаже тыс
|
||
-- }, ...] }
|
||
-- → long-формат: одна строка = (месяц × корпус × room_bucket) per-row.
|
||
-- → corp_sum.json для одного ЖК Парк Культуры за 7 дней = 8 строк (4 bucket × 2 мес).
|
||
--
|
||
-- 2. /v2/Report/GetReport?ReportType=Поквартирные&ReportName=Лоты
|
||
-- → {"result": [ {Id, "Id проекта", Проект, Корпус, Адрес, Класс, Секция, Этаж,
|
||
-- "Номер лота", "Вид помещения", Статус, Продано,
|
||
-- "Количество комнат(Сайт девелопера/ПД/Данные объектива)",
|
||
-- "Площадь, м2(Сайт девелопера/ПД)",
|
||
-- "Расчетный бюджет лота, Р", "Цена за м2, Р",
|
||
-- "Цена предложения, Р", "Дельта цена, Р/%",
|
||
-- "Дата договора", "Дата регистрации", "Тип сделки",
|
||
-- "Тип покупателя", "Номер регистрации",
|
||
-- "Тип обременения", Банк, "Дата начала обременения",
|
||
-- "Дата актуальности данных из ЕГРН"}, ...] }
|
||
-- → per-flat snapshot. `Id` (Objective lot_id) — stable, можно использовать как UPSERT-ключ.
|
||
--
|
||
-- 3. /v2/Report/GetReport?ReportType=Сводные&ReportName=Лоты — HTTP 500 на нашем тарифе
|
||
-- 4. /v2/Report/GetReport?ReportType=Поквартирные&ReportName=Корпуса — HTTP 500 на нашем тарифе
|
||
-- Эти 2 отчёта недоступны / не реализованы у нас. Если откроют — добавим.
|
||
--
|
||
-- Стратегия storage:
|
||
-- - RAW: objective_raw_reports (jsonb payload) — страховка от ломки схемы.
|
||
-- - NORMALIZED:
|
||
-- - objective_corpus_room_month (long-формат из corp_sum)
|
||
-- - objective_lots (текущая запись per-flat, UPSERT по Objective.Id)
|
||
-- - objective_lots_history (append-only снапшоты per-flat per snapshot_date)
|
||
-- - MAPPING: objective_complex_mapping связывает Objective.ComplexName с domrf_kn_objects.obj_id.
|
||
--
|
||
-- Идемпотентно через IF NOT EXISTS.
|
||
|
||
CREATE EXTENSION IF NOT EXISTS postgis;
|
||
|
||
-- ── 0. Runs (журнал sync) ────────────────────────────────────────────────────
|
||
|
||
CREATE TABLE IF NOT EXISTS objective_scrape_runs (
|
||
run_id BIGSERIAL PRIMARY KEY,
|
||
started_at timestamptz NOT NULL DEFAULT NOW(),
|
||
finished_at timestamptz,
|
||
heartbeat_at timestamptz,
|
||
group_name TEXT NOT NULL,
|
||
reports_ok INT NOT NULL DEFAULT 0,
|
||
reports_failed INT NOT NULL DEFAULT 0,
|
||
rows_corpus_room INT NOT NULL DEFAULT 0,
|
||
rows_lots INT NOT NULL DEFAULT 0,
|
||
rows_history INT NOT NULL DEFAULT 0,
|
||
requests_count INT NOT NULL DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT 'running', -- running | done | failed | zombie
|
||
error TEXT,
|
||
triggered_by TEXT NOT NULL DEFAULT 'beat' -- beat | manual | resume
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_runs_status_idx
|
||
ON objective_scrape_runs(status, started_at DESC);
|
||
|
||
COMMENT ON TABLE objective_scrape_runs IS
|
||
'Журнал запусков sync с api.objctv.ru. Один прогон = один (group_name) пакет отчётов.';
|
||
|
||
-- ── 1. RAW payloads ──────────────────────────────────────────────────────────
|
||
|
||
CREATE TABLE IF NOT EXISTS objective_raw_reports (
|
||
raw_id BIGSERIAL PRIMARY KEY,
|
||
run_id BIGINT REFERENCES objective_scrape_runs(run_id) ON DELETE SET NULL,
|
||
fetched_at timestamptz NOT NULL DEFAULT NOW(),
|
||
page TEXT NOT NULL,
|
||
report_section TEXT NOT NULL,
|
||
report_type TEXT NOT NULL,
|
||
report_name TEXT NOT NULL,
|
||
group_name TEXT NOT NULL,
|
||
complex_name TEXT,
|
||
start_date DATE,
|
||
end_date DATE,
|
||
use_ddu BOOLEAN,
|
||
use_dkp BOOLEAN,
|
||
api_version TEXT NOT NULL DEFAULT 'v2',
|
||
payload jsonb NOT NULL,
|
||
payload_size INT NOT NULL,
|
||
rows_extracted INT -- сколько rows распарсено в normalized слой
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_raw_combo_idx
|
||
ON objective_raw_reports(report_section, report_type, report_name,
|
||
group_name, fetched_at DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_raw_dates_idx
|
||
ON objective_raw_reports(start_date, end_date) WHERE start_date IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_raw_run_idx
|
||
ON objective_raw_reports(run_id);
|
||
|
||
COMMENT ON TABLE objective_raw_reports IS
|
||
'Сырой JSON-payload каждого GetReport-вызова. Источник для re-parse при '
|
||
'изменениях схемы Objective или переноса normalized-слоя на новую версию.';
|
||
|
||
-- ── 2. NORMALIZED — Сводные/Корпуса (long: month × corpus × room_bucket) ────
|
||
|
||
-- Заменяет старую objective_oks (была спроектирована под per-corpus с
|
||
-- rooms[N]_count колонками, не подходит под реальный long-формат).
|
||
CREATE TABLE IF NOT EXISTS objective_corpus_room_month (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
-- Ключ записи (month × corpus × room_bucket)
|
||
report_month DATE NOT NULL, -- 1-е число месяца из "Месяц": "апрель-2026"
|
||
group_name TEXT NOT NULL,
|
||
project_name TEXT NOT NULL, -- "Парк Культуры"
|
||
developer TEXT, -- "PRINZIP"
|
||
district TEXT, -- "Автовокзал"
|
||
class TEXT, -- "Комфорт" / "Бизнес" / "Стандарт" / ...
|
||
corpus_name TEXT NOT NULL, -- "1.1"
|
||
room_bucket TEXT NOT NULL, -- "студия" | "1" | "2" | "3" | "4" | "5+"
|
||
rooms_int SMALLINT, -- нормализованный 0=студия, 1, 2, 3, 4, 5
|
||
-- Динамика стройки (повторяется в каждой строке, but stable per-corpus per snapshot)
|
||
sales_start_date DATE,
|
||
plan_completion_date DATE, -- "Планируемая дата ввода"
|
||
actual_completion_date DATE, -- "Фактическая дата ввода"
|
||
months_in_realization INT,
|
||
-- Capacity по ПД
|
||
lots_pd_count INT, -- "Лотов по ПД, шт."
|
||
lots_pd_area NUMERIC, -- "Площадь по ПД, м2."
|
||
-- Сделки ДДУ
|
||
deals_ddu_count INT,
|
||
deals_ddu_count_priced INT, -- из них с известной ценой
|
||
deals_ddu_vol_m2 NUMERIC,
|
||
deals_ddu_vol_m2_priced NUMERIC,
|
||
deals_ddu_sum_mln_rub NUMERIC, -- "млн.Р"
|
||
deals_ddu_avg_price_thousand_rub_per_m2 NUMERIC, -- "тыс.Р/м2"
|
||
deals_ddu_avg_area_m2 NUMERIC,
|
||
-- Сделки ДКП
|
||
deals_dkp_count INT,
|
||
deals_dkp_count_priced INT,
|
||
deals_dkp_vol_m2 NUMERIC,
|
||
deals_dkp_vol_m2_priced NUMERIC,
|
||
deals_dkp_sum_mln_rub NUMERIC,
|
||
deals_dkp_avg_price_thousand_rub_per_m2 NUMERIC,
|
||
deals_dkp_avg_area_m2 NUMERIC,
|
||
-- Сделки всего (= ДДУ + ДКП)
|
||
deals_total_count INT,
|
||
deals_total_count_priced INT,
|
||
deals_total_vol_m2 NUMERIC,
|
||
deals_total_vol_m2_priced NUMERIC,
|
||
deals_total_sum_mln_rub NUMERIC,
|
||
deals_total_avg_price_thousand_rub_per_m2 NUMERIC,
|
||
deals_total_avg_area_m2 NUMERIC,
|
||
-- Текущее предложение (stock на конец месяца)
|
||
offer_count INT, -- "Объем предложения, шт."
|
||
offer_area_m2 NUMERIC, -- "Объем предложения, м2."
|
||
offer_sum_mln_rub NUMERIC, -- "Суммарная стоимость лотов в продаже, млн.Р"
|
||
offer_avg_price_thousand_rub_per_m2 NUMERIC, -- "Средняя цена м2 лота в продаже, тыс.Р/м2"
|
||
-- Audit
|
||
raw_id BIGINT REFERENCES objective_raw_reports(raw_id) ON DELETE SET NULL,
|
||
raw_props jsonb,
|
||
fetched_at timestamptz NOT NULL DEFAULT NOW(),
|
||
|
||
-- Один (месяц, корпус, room_bucket, group) — одна запись.
|
||
UNIQUE (report_month, group_name, project_name, corpus_name, room_bucket)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_crm_project_month_idx
|
||
ON objective_corpus_room_month(project_name, report_month DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_crm_district_class_idx
|
||
ON objective_corpus_room_month(district, class, report_month DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_crm_developer_idx
|
||
ON objective_corpus_room_month(developer, report_month DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_crm_rooms_idx
|
||
ON objective_corpus_room_month(rooms_int, report_month DESC);
|
||
|
||
COMMENT ON TABLE objective_corpus_room_month IS
|
||
'Сводные/Корпуса в реальном long-формате: (месяц × корпус × количество комнат). '
|
||
'Главное преимущество над domrf_kn — готовая monthly history с разбивкой по mix '
|
||
'(studio/1/2/3/4) с явными метриками: остатки, средняя цена, средняя площадь.';
|
||
|
||
-- ── 3. NORMALIZED — Поквартирные/Лоты ────────────────────────────────────────
|
||
|
||
CREATE TABLE IF NOT EXISTS objective_lots (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
objective_lot_id BIGINT NOT NULL UNIQUE, -- "Id" в payload — stable Objective ID
|
||
objective_project_id BIGINT, -- "Id проекта"
|
||
-- Identification
|
||
project_name TEXT NOT NULL,
|
||
developer TEXT,
|
||
city TEXT,
|
||
district TEXT,
|
||
corpus_name TEXT,
|
||
address TEXT,
|
||
class TEXT, -- "комфорт" в lots_pf (lowercase!) vs "Комфорт" в corp_sum
|
||
section TEXT,
|
||
floor INT,
|
||
lot_number TEXT, -- "Номер лота" — может быть "1а" etc, поэтому TEXT
|
||
premise_kind TEXT, -- "квартира" | "нежилое" | "паркинг" | "кладовая"
|
||
-- Stage
|
||
sales_start_date DATE,
|
||
plan_completion_date DATE,
|
||
actual_completion_date DATE,
|
||
readiness_pct SMALLINT, -- "17%" → 17
|
||
construction_stage TEXT, -- "строящийся" | "сдан" | ...
|
||
finishing TEXT, -- "Отделка по корпусу" — "под чистовую" | "с отделкой" | ...
|
||
-- Status
|
||
status TEXT, -- "резерв" | "свободна" | "продан" | "бронь"
|
||
is_sold BOOLEAN, -- "Продано": "да"/"нет" → true/false
|
||
-- Mix (3 источника от Объектива)
|
||
rooms_dev_site TEXT, -- "Количество комнат(Сайт девелопера)"
|
||
rooms_pd TEXT, -- "Количество комнат(ПД)"
|
||
rooms_objective TEXT, -- "Количество комнат(Данные объектива)" — самое нормализованное
|
||
rooms_int SMALLINT, -- нормализуем objective: студия→0, "1"→1, ...
|
||
-- Areas
|
||
area_dev_site NUMERIC,
|
||
area_pd NUMERIC,
|
||
-- Pricing
|
||
price_calculated_total_rub NUMERIC, -- "Расчетный бюджет лота, Р"
|
||
price_per_m2_rub NUMERIC, -- "Цена за м2, Р"
|
||
price_offer_total_rub NUMERIC, -- "Цена предложения, Р"
|
||
price_delta_rub NUMERIC, -- "Дельта цена, Р"
|
||
price_delta_pct NUMERIC, -- "Дельта цена, %"
|
||
pricing_method TEXT, -- "Способ определения цены"
|
||
price_set_date DATE, -- "Дата установки цены"
|
||
price_actual_date DATE, -- "Дата актуальности цены"
|
||
-- Deal
|
||
contract_date DATE, -- "Дата договора"
|
||
registration_date DATE, -- "Дата регистрации"
|
||
contract_type TEXT, -- "Тип сделки": ДДУ | ДКП | ...
|
||
buyer_type TEXT, -- "Тип покупателя"
|
||
registration_number TEXT, -- "Номер регистрации"
|
||
-- Encumbrance / mortgage
|
||
encumbrance_type TEXT, -- "Тип обременения"
|
||
bank_name TEXT, -- "Банк"
|
||
encumbrance_start_date DATE, -- "Дата начала обременения"
|
||
egrn_actual_date DATE, -- "Дата актуальности данных из ЕГРН"
|
||
-- Audit
|
||
raw_id BIGINT REFERENCES objective_raw_reports(raw_id) ON DELETE SET NULL,
|
||
raw_props jsonb,
|
||
snapshot_date DATE NOT NULL, -- дата снимка (= когда мы синкнули)
|
||
fetched_at timestamptz NOT NULL DEFAULT NOW(),
|
||
updated_at timestamptz NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_lots_project_idx
|
||
ON objective_lots(project_name, snapshot_date DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_lots_status_idx
|
||
ON objective_lots(status) WHERE status IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_lots_rooms_area_idx
|
||
ON objective_lots(rooms_int, area_pd) WHERE area_pd IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_lots_district_class_idx
|
||
ON objective_lots(district, class) WHERE district IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_lots_bank_idx
|
||
ON objective_lots(bank_name) WHERE bank_name IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_lots_reg_date_idx
|
||
ON objective_lots(registration_date) WHERE registration_date IS NOT NULL;
|
||
|
||
COMMENT ON TABLE objective_lots IS
|
||
'Per-flat snapshot из Объектива (Поквартирные/Лоты). UPSERT по objective_lot_id. '
|
||
'Текущее (самое последнее) состояние каждой квартиры. Полная history — '
|
||
'в objective_lots_history.';
|
||
|
||
-- ── 4. NORMALIZED — История квартир (append-only snapshots) ─────────────────
|
||
|
||
-- Объектив НЕ отдаёт per-day price/status timeline в одном вызове.
|
||
-- Зато при еженедельном sync мы можем собирать сами: при каждом вызове
|
||
-- делаем INSERT в _history для каждого изменившегося лота. Это даст нам
|
||
-- weekly-resolution timeline price/status/area per flat — достаточно для
|
||
-- elasticity и velocity-anomaly детектора.
|
||
|
||
CREATE TABLE IF NOT EXISTS objective_lots_history (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
objective_lot_id BIGINT NOT NULL, -- ссылка на objective_lots.objective_lot_id
|
||
snapshot_date DATE NOT NULL, -- день sync
|
||
status TEXT,
|
||
is_sold BOOLEAN,
|
||
price_calculated_total_rub NUMERIC,
|
||
price_per_m2_rub NUMERIC,
|
||
price_offer_total_rub NUMERIC,
|
||
price_delta_pct NUMERIC,
|
||
area_pd NUMERIC,
|
||
contract_date DATE,
|
||
registration_date DATE,
|
||
contract_type TEXT,
|
||
bank_name TEXT,
|
||
raw_id BIGINT REFERENCES objective_raw_reports(raw_id) ON DELETE SET NULL,
|
||
fetched_at timestamptz NOT NULL DEFAULT NOW(),
|
||
-- На каждый (lot, snapshot_date) — ровно одна запись.
|
||
UNIQUE (objective_lot_id, snapshot_date)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_lots_history_lot_idx
|
||
ON objective_lots_history(objective_lot_id, snapshot_date DESC);
|
||
CREATE INDEX IF NOT EXISTS objective_lots_history_date_idx
|
||
ON objective_lots_history(snapshot_date);
|
||
CREATE INDEX IF NOT EXISTS objective_lots_history_reg_idx
|
||
ON objective_lots_history(registration_date)
|
||
WHERE registration_date IS NOT NULL;
|
||
|
||
COMMENT ON TABLE objective_lots_history IS
|
||
'Append-only weekly snapshots каждого лота. Главное преимущество для '
|
||
'velocity-anomaly detection и для elasticity (точность регрессии × лет).';
|
||
|
||
-- ── 5. MAPPING — Objective ↔ domrf_kn_objects ────────────────────────────────
|
||
|
||
CREATE TABLE IF NOT EXISTS objective_complex_mapping (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
objective_complex_name TEXT NOT NULL, -- "Парк Культуры"
|
||
objective_project_id BIGINT, -- из payload "Id проекта"
|
||
objective_group TEXT NOT NULL,
|
||
domrf_obj_id BIGINT, -- domrf_kn_objects.obj_id; NULL = не сматчили
|
||
is_reviewed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
match_method TEXT, -- 'exact_name' | 'fuzzy_name' | 'manual' | 'spatial'
|
||
match_score NUMERIC, -- 0..1 для fuzzy
|
||
note TEXT,
|
||
created_at timestamptz NOT NULL DEFAULT NOW(),
|
||
updated_at timestamptz NOT NULL DEFAULT NOW(),
|
||
UNIQUE (objective_complex_name, objective_group)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS objective_mapping_domrf_idx
|
||
ON objective_complex_mapping(domrf_obj_id) WHERE domrf_obj_id IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS objective_mapping_unreviewed_idx
|
||
ON objective_complex_mapping(is_reviewed) WHERE is_reviewed = FALSE;
|
||
CREATE INDEX IF NOT EXISTS objective_mapping_project_id_idx
|
||
ON objective_complex_mapping(objective_project_id) WHERE objective_project_id IS NOT NULL;
|
||
|
||
COMMENT ON TABLE objective_complex_mapping IS
|
||
'Маппинг Objective.ComplexName ↔ domrf_kn_objects.obj_id. is_reviewed=TRUE — '
|
||
'человек подтвердил соответствие. Auto-matching пишет с is_reviewed=FALSE.';
|