data-model: gwptd_kernel — снапшот-факты состояния

Path note (S3 Next, 2026-07-13): field/table semantics in this snapshot remain reference material, but any collector.py or collectors/*.py source path below is historical. The active source is the matching specs/products/*.yaml plus shared spec_runtime; use docs/backend/generated/INGESTION-LINEAGE.md for current lineage.

Снапшот DDL: 2026-07-03, s3 live · Код: master @ c33c623 (учтены фикс-волны 7a77140, 0d90d26 — ../DEFECT-LEDGER.md)

Четыре таблицы-«снимка состояния» (state snapshots): что лежит на складах, почём продаётся, что есть у поставщика и сколько стоит хранение. В отличие от событийных фактов (fact_orders, fact_returns) здесь строка = срез на дату, а не событие: перезапуск ETL в тот же день перезаписывает срез (idempotent upsert по UNIQUE-ключу), история не событийная, а «один снимок в день».

Таблица	Гранулярность (UNIQUE)	Писатель (kernel ETL)
fact_stocks_daily	model_id × mp_id × warehouse_canon × snapshot_date	kernel/etl/build_fact_stocks.py
v_fact_stocks_current	VIEW: последний срез на model × mp × warehouse_canon	— (view над fact_stocks_daily)
fact_prices_daily	model_id × mp_id × price_date	kernel/etl/build_fact_prices.py
fact_supplier_stock_price	model_id × snapshot_date	kernel/etl/build_fact_supplier_stock.py
fact_storage_costs	model_id × mp_id × cost_date × warehouse_canon × cost_type	kernel/etl/build_fact_storage.py

Все пути к коду — от marketplace-collector-v3/. Второй (исторический) писатель всех таблиц, кроме fact_supplier_stock_price, — разовый бэкфилл из legacy lamoda_reports.mp_*: kernel/etl/backfill_facts.py (stocks :73, prices :88, storage :130) — он не перечисляет source_payload_id (NULL = строка из бэкфилла).

Связанные правила: BR-004-rating (cost из price_buy_usd, exposure из стоков), formulas.yaml (константы 3.0 / 1.47, формула cost_price).

---

fact_stocks_daily

Назначение. Ежедневные остатки товара по маркетплейсам и складам — единый источник для exposure рейтинга (дни с остатком, DNR-003), avg_stock оборачиваемости, текущих остатков в отгрузке (/new) и legacy-зеркала mp_stocks_daily.

Писатель — kernel/etl/build_fact_stocks.py

Пять источников из gwptd_intake, каждый — отдельный INSERT … SELECT c ON DUPLICATE KEY UPDATE (идемпотентно). Мерж источников — не конкурентный: каждый источник пишет свои mp_id, пересечений по UNIQUE-ключу между источниками нет (кроме режима Ozon, см. ниже). Порядок прогона: SOURCES (build_fact_stocks.py:213), оркестрация build() :230.

Источник	SQL (file:line)	intake-таблица	mp_id	Резолв в model_id
WB FBO	WB_SQL :36	wb_stocks	1 (из w.mp_id)	nm_id → dim_product_identifier type=nmID
WB FBS	WB_FBS_SQL :56	wb_stocks_fbs	2 (из w.mp_id)	nm_id → nmID
Ozon агрегат	OZON_SQL :79	ozon_stocks (stocks_json через JSON_TABLE)	3/4/5 по type fbs/rfbs/fbo	offer_id → offer_id
Ozon FBS/rFBS (per-wh режим)	OZON_FBS_RFBS_SQL :116	ozon_stocks	только 3/4	offer_id → offer_id
Ozon FBO per-warehouse	OZON_FBO_WH_SQL :154	ozon_stock_on_warehouses	5	offer_id → offer_id
YM	YM_SQL :180	ym_stocks	из y.mp_id (6 FBS / 8 FBY)	offer_id → shopSku
Lamoda	LAMODA_SQL :199	lamoda_inventory	9 (из l.mp_id)	sku → dim_product.artikul_upper напрямую

Особенности мержа:

• Ozon — переключаемый режим (_ozon_warehouses_available() :216): если intake.ozon_stock_on_warehouses существует и не пуст, mp=5 (FBO) наполняется поскладски из OZON_FBO_WH_SQL (реальные РФЦ-склады + резерв), а OZON_SQL заменяется на OZON_FBS_RFBS_SQL (только mp 3/4) — чтобы не задвоить FBO. Иначе backward-compatible fallback: v4-агрегат по всем трём типам, warehouse_name=''.
• WB FBS может быть пропущен, если intake-таблицы wb_stocks_fbs нет (build() :232–240).
• Несрезолвленные идентификаторы (нет строки в dim_product_identifier / dim_product) не пишутся — INNER JOIN просто отбрасывает (skip).
• Внутри дня берётся последний прогон коллектора: WB FBS / Ozon / YM / storage используют подзапрос MAX(id) per ключ+день; WB FBO и Lamoda — MAX(...) по значениям в GROUP BY дня.

Семантика stock-полей по источникам

snapshot_date = DATE(collected_at) — дата снимка сбора, не дата события на МП.

Источник (mp)	stock_available	stock_reserved	stock_total	stock_in_way_to	stock_in_way_from	warehouse
WB FBO (1)	quantity (доступно к продаже)	NULL (WB не отдаёт)	quantity_full	in_way_to_client	in_way_from_client	реальный склад WB
WB FBS (2)	amount (= total)	NULL	amount (= available)	NULL	NULL	склад продавца (warehouse_id заполнен)
Ozon fbs/rfbs/fbo агрегат (3/4/5)	GREATEST(present − reserved, 0)	reserved	present	NULL	NULL	'' (v4 без складов)
Ozon FBO per-wh (5)	free_to_sell	reserved	free_to_sell + reserved	NULL	NULL	реальный РФЦ-склад
YM (6/8)	available_count	freeze_count	available_count + freeze_count	NULL	NULL	склад YM (warehouse_id заполнен)
Lamoda (9)	quantity (= total)	NULL	quantity (= available)	NULL	NULL	'' (API не отдаёт склад)

Инварианты (проверяются в kernel/etl/validate_invariants.py:49,64): stock_in_way_* заполнены только у WB FBO; stock_reserved — только Ozon и YM. У WB FBS и Lamoda available≡total (единственное число из API).

warehouse_canon — generated-колонка (правило нормализации)

warehouse_canon GENERATED ALWAYS AS (
  lower(trim(regexp_replace(regexp_replace(warehouse_name,
       '[[:space:]]+', ' '),          -- 1) любые пробельные последовательности → один пробел
       '[[:space:]]*/[[:space:]]*', '/'))) -- 2) пробелы вокруг «/» убрать
) STORED                              -- 3) trim + lower

• Считает сама БД (STORED), INSERT её не перечисляет — нормализация работает даже при прямой записи мимо ETL.
• Входит в UNIQUE uq_model_mp_whcanon_date: варианты написания одного склада («Софьино », «Быково / Софьино», «Быково/Софьино») схлопываются в одну строку ⇒ SUM по складам не задваивает. Коллизия по canon при GROUP BY по сырому warehouse_name резолвится через ON DUPLICATE KEY UPDATE.
• Та же формула продублирована на Python: kernel/etl/canon.py (canon_warehouse).
• Семантические алиасы (разные имена = физически один склад) нормализацией не лечатся — это dim_warehouse + gate Слоя 3 (комментарий build_fact_stocks.py:13–21).

Читатели

kernel ETL:

• kernel/etl/build_fact_rating.py:236–244 — exposure_days = COUNT(DISTINCT snapshot_date) где stock_total > 0 за месяц по группе mp_id IN (группа продаж); двойной сток (Ozon FBS+FBO в один день) день не удваивает (DNR-003, epic-05/04).
• kernel/etl/build_fact_turnover.py:128–135 (universe моделей), :158–163 — avg_stock_28d = AVG(дневной SUM(stock_available)) за окно 28 дней, days_with_stock; :169–181 — in_stock (последний срез ≤ calc_date).
• kernel/etl/validate_invariants.py:49,64 — CI-инварианты.

data_api (Python, /new):

• data_api/routers/facts.py:54 — GET /facts/stocks-daily (сырой срез).
• data_api/routers/reports/stocks.py:66,76,139,170 — отчёт «Остатки» пер-МП.
• data_api/routers/reports/shipment/_helpers.py:92 — текущий сток отгрузки через v_fact_stocks_current (SUM(stock_available) по модели); :158–170 — exposure-дни для прогноза (stock_total>0).
• data_api/routers/reports/shipment/corrective.py:41 (view), :99,115; .../shipment/ozon_fbo.py:68,111,142 (view + история).
• data_api/routers/reports/shipment_distribution.py:43–46 — дельты остатков.
• data_api/routers/reports/ozon_sales.py:180–185 — «Остаток ФБО» (последний срез mp=5).

Обратная синхронизация в legacy: scripts/refresh_legacy_from_kernel.py:245 (mp_stocks_daily ←), :639 (model_exposure_month — bitmap дней с остатком).

Поля fact_stocks_daily (DDL gwptd_kernel.sql:426)

Поле	Тип	Что это	Откуда
id	bigint unsigned PK AI	суррогатный ключ	БД
model_id	int unsigned NOT NULL, FK dim_product	модель товара	резолв идентификатора (см. таблицу источников)
mp_id	tinyint unsigned NOT NULL, FK dim_marketplace	площадка (1 WB FBO, 2 WB FBS, 3/4/5 Ozon, 6/8 YM, 9 Lamoda)	intake mp_id / CASE по типу Ozon
warehouse_id	bigint NULL	ID склада из API (заполнен только WB FBS и YM)	wb_stocks_fbs.warehouse_id, ym_stocks.warehouse_id
warehouse_name	varchar(255) NOT NULL DEFAULT ''	сырое имя склада ('' если источник без складов)	intake warehouse_name / ''
warehouse_canon	varchar(255) GENERATED STORED	нормализованное имя склада (правило выше), часть UNIQUE	БД, из warehouse_name
snapshot_date	date NOT NULL	дата снимка (день сбора коллектором, не событие)	DATE(collected_at) intake
stock_available	int NULL	доступно к продаже (семантика per-источник, см. таблицу)	intake, per-источник
stock_reserved	int NULL	резерв (только Ozon/YM, иначе NULL)	intake, per-источник
stock_total	int NULL	всего (семантика per-источник)	intake, per-источник
stock_in_way_to	int NULL	в пути к клиенту (только WB FBO)	wb_stocks.in_way_to_client
stock_in_way_from	int NULL	в пути от клиента (только WB FBO)	wb_stocks.in_way_from_client
source_payload_id	bigint unsigned NULL	ссылка на сырой payload intake (NULL = бэкфилл из legacy)	MAX(payload_id) intake
collected_at	timestamp NULL, auto-update	когда строка записана/обновлена ETL	БД

Ключи: PK(id); UNIQUE uq_model_mp_whcanon_date (model_id, mp_id, warehouse_canon, snapshot_date); KEY idx_model(model_id), idx_mp_date(mp_id, snapshot_date).

---

v_fact_stocks_current

Назначение. «Текущие остатки»: последний доступный срез fact_stocks_daily на каждую комбинацию (model_id, mp_id, warehouse_canon) — чтобы читателям не писать одинаковый MAX(snapshot_date)-подзапрос.

Определение (DDL gwptd_kernel.sql:738–747): self-join fact_stocks_daily с latest = GROUP BY model_id, mp_id, warehouse_canon → MAX(snapshot_date) и отсечкой свежести 7 дней (WHERE snapshot_date >= CURDATE() - INTERVAL 7 DAY). Отдаёт те же stock-колонки + warehouse_name/warehouse_canon/snapshot_date.

Отсечка — фикс DEFECT-LEDGER F-11 (7a77140, применён на s3 2026-07-03): раньше последний срез брался БЕЗ ограничения по дате, и склад, умерший в выдаче API, навсегда оставался в «текущих» остатках (live-факт до фикса: 22 328 стале-строк двоили сток mp3/6/8/9 в 2–4×, view 38 311→12 811 строк после фикса; строки fact_stocks_daily не удалялись). Теперь склад, пропавший из выдачи >7 дней, из view исчезает. Остаточная семантика (by design): внутри 7-дневного окна «последние даты» разных складов одной модели всё ещё могут различаться. Читатели shipment суммируют view по модели без собственных проверок даты (_helpers.py:88–99) — после F-11 это корректно.

Читатели: data_api/routers/reports/shipment/_helpers.py:92, shipment/corrective.py:41, shipment/ozon_fbo.py:68,142; контроль строк после ETL — build_fact_stocks.build() :257.

---

fact_prices_daily

Назначение. Ежедневный снимок цен каталога по маркетплейсам — «почём товар стоит на витрине» (не транзакционная цена продажи — та в fact_orders.price).

Писатель — kernel/etl/build_fact_prices.py

Одна строка на (model_id, mp_id, день); внутри дня — последний прогон (MAX(id) per идентификатор+день у WB/YM), по модели — MAX(...) над её идентификаторами. Цены WB едины для FBO/FBS → снимок хранится только под mp=1.

Источник	SQL (file:line)	intake-таблица	mp_id	Резолв
Ozon	OZON_SQL :21	ozon_prices	3	offer_id → offer_id
WB	WB_SQL :40	wb_prices	1	nm_id → nmID
YM	YM_SQL :64	ym_prices	6	offer_id → shopSku

Lamoda цен не имеет (API не отдаёт, probe confirmed). WB/YM пропускаются, если intake-таблиц нет (build() :90–108).

Маппинг полей per-МП

Поле	Ozon (mp=3) ← ozon_prices	WB (mp=1) ← wb_prices	YM (mp=6) ← ym_prices
price	price	price (базовая, до скидки)	price (актуальная цена)
price_discounted	marketing_price	discounted_price (со скидкой продавца)	NULL
price_final	marketing_seller_price	club_discounted_price (цена с WB-клубом)	NULL
price_min	min_price	NULL	NULL
price_old	old_price (зачёркнутая)	NULL	discount_base (зачёркнутая базовая)

• Семантика Ozon-полей — по актуальной схеме API /v5/product/info/prices (сверено со swagger Ozon Seller API 2026-07-03): price — цена с учётом скидок, отображается на карточке товара; old_price — цена до скидок (зачёркнутая); min_price — минимальная цена со ВСЕМИ применёнными акциями; marketing_seller_price — цена с учётом акций ПРОДАВЦА. ⚠️ Поля marketing_price в актуальном v5-ответе НЕТ (Ozon его убрал): в intake ozon_prices.marketing_price NULL во всех строках (live s3 2026-07-03) ⇒ fact_prices_daily.price_discounted для mp=3 всегда NULL — колонка для Ozon мёртвая, живёт только у WB (discounted_price).
• Копейки ÷100 — НЕ здесь. WB discounts-prices-api v2 отдаёт price/discountedPrice/clubDiscountedPrice в рублях; legacy делил на 100 и портил цены (~220 ₽ вместо ~22 000 ₽) — v3 НЕ делит (collectors/wb_prices.py:1–11). Деление на 100 в v3 живёт только у WB FBS заказов (collectors/wb_orders_fbs.py:5), к fact_prices_daily не относится.
• price_date = DATE(collected_at) — дата снимка каталога, не изменение цены.

Читатели

• scripts/refresh_legacy_from_kernel.py:357 — зеркало в legacy mp_prices_daily (для старого /admin).
• В kernel ETL и data_api-роутерах прямых читателей нет (grep по репо @ b1d760b) — таблица пока «на вырост» (аналитика цен/акций).

Поля fact_prices_daily (DDL gwptd_kernel.sql:306)

Поле	Тип	Что это	Откуда
id	bigint unsigned PK AI	суррогатный ключ	БД
model_id	int unsigned NOT NULL, FK dim_product	модель	резолв идентификатора
mp_id	tinyint unsigned NOT NULL, FK dim_marketplace	площадка (1 WB, 3 Ozon, 6 YM)	константа per-источник
price_date	date NOT NULL	дата снимка цен	DATE(collected_at) intake
price	decimal(14,2) NULL	базовая/актуальная цена (см. маппинг)	intake, per-МП
price_discounted	decimal(14,2) NULL	цена со скидкой (Ozon marketing / WB seller discount)	intake, per-МП
price_final	decimal(14,2) NULL	«итоговая» (Ozon marketing_seller / WB club)	intake, per-МП
price_min	decimal(14,2) NULL	минимальная цена (только Ozon)	ozon_prices.min_price
price_old	decimal(14,2) NULL	зачёркнутая цена (Ozon old_price / YM discount_base)	intake, per-МП
source_payload_id	bigint unsigned NULL	ссылка на payload intake (NULL = бэкфилл)	MAX(payload_id)
collected_at	timestamp NULL, auto-update	запись/обновление строки ETL	БД

Ключи: PK(id); UNIQUE uq_model_mp_date (model_id, mp_id, price_date); KEY idx_model, mp_id.

---

fact_supplier_stock_price

Назначение. Дневной срез каталога поставщика из 1С (наш закуп): остаток у поставщика и закупочные цены. price_buy_usd — корень себестоимости всей аналитики: cost_price_rub = ROUND((price_buy_usd + 3.0) × 1.47 × usd_rub_rate, 2) (BR-004-rating, formulas.yaml § cost_price; константы — build_fact_rating.py:76–77).

Писатель — kernel/etl/build_fact_supplier_stock.py

Один запрос UPSERT_SQL :19: intake.onec_supplier_catalog → JOIN dim_product по artikul_upper = UPPER(TRIM(artikul)) (наши данные, прямое совпадение артикула — без dim_product_identifier). Один срез на (model_id, DATE(collected_at)), внутри дня MAX(...). Единственная state-таблица без mp_id — данные поставщика не привязаны к площадке. Бэкфилла из legacy нет.

Читатели

• kernel/etl/build_fact_rating.py:246–258 — cost: последний срез price_buy_usd на модель (MAX(snapshot_date) при price_buy_usd > 0); INNER JOIN ⇒ модели без закупочной цены в рейтинг не попадают (skip_row_if в formulas.yaml).
• data_api/routers/reports/shipment/_helpers.py:207–214 (supplier_stock_and_cost) — остаток поставщика (qty>0) как лимит отгрузки и себестоимость; shipment/corrective.py:56–59 — то же для корректирующей.
• data_api/routers/reports/supplier_stock.py:66,75,126,152 — отчёт «Остатки поставщика» в /new.

Поля fact_supplier_stock_price (DDL gwptd_kernel.sql:484)

Поле	Тип	Что это	Откуда
id	bigint unsigned PK AI	суррогатный ключ	БД
model_id	int unsigned NOT NULL, FK dim_product	модель	dim_product.artikul_upper = UPPER(TRIM(artikul))
snapshot_date	date NOT NULL	дата среза каталога 1С	DATE(collected_at) onec_supplier_catalog
qty	int NULL	остаток у поставщика, шт (лимит отгрузки)	onec_supplier_catalog.quantity
price_buy_usd	decimal(12,4) NULL	закупочная цена, USD — вход формулы cost (BR-004)	onec_supplier_catalog.price_buy_usd (1С)
price_buy_rub	decimal(12,2) NULL	закупочная цена, руб (из 1С)	onec_supplier_catalog.price_buy_rub
price_retail	decimal(12,2) NULL	розничная цена (из 1С)	onec_supplier_catalog.price_retail
source_payload_id	bigint unsigned NULL	ссылка на payload intake	MAX(payload_id)
collected_at	timestamp NULL, auto-update	запись/обновление строки ETL	БД

⚠️ Вопрос владельцу (сторона 1С): по какому курсу/правилу 1С считает price_buy_rub и price_retail и зачем они рядом с price_buy_usd — из кода репо это не подтверждаемо (kernel их только копирует из 1С); ни рейтинг, ни отгрузка их не читают (cost всегда через price_buy_usd), поэтому блокером не является. Пункта в DEFECT-LEDGER нет.

Ключи: PK(id); UNIQUE uq_model_date (model_id, snapshot_date); KEY idx_model, idx_date.

---

fact_storage_costs

Назначение. Расходы на хранение по товару/складу/дню — вход storage_cost_28d оборачиваемости (справочная метрика стоимости запаса).

Писатель — kernel/etl/build_fact_storage.py

Единственный kernel-источник — WB paid_storage (WB_SQL :31): intake.wb_paid_storage → mp_id=1, cost_type='storage', резолв nm_id → nmID. WB пересобирает окно 7 дней ежедневно ⇒ берётся последний прогон (MAX(id) per nm_id+warehouse+cost_date), несколько баркодов/размеров одной модели суммируются (SUM quantity/amount/volume). Здесь cost_date — дата из отчёта WB (день, за который начислено хранение), в отличие от snapshot/price_date других таблиц.

Ozon сознательно НЕ кладётся (docstring :11–14): хранение Ozon (OperationMarketplaceServiceStorage в intake.ozon_finance_transactions) — дневной агрегат без разбивки по товарам (items_json=[]), а model_id NOT NULL; при необходимости брать прямо из intake. YM/Lamoda источников в kernel нет.

warehouse_canon — та же generated STORED колонка с тем же правилом нормализации, что в fact_stocks_daily (см. выше), входит в UNIQUE.

Значения cost_type

• 'storage' — единственное значение, которое пишет kernel ETL (build_fact_storage.py:35).
• Колонка varchar(50) без ENUM/CHECK; бэкфилл backfill_facts.py:130 копировал cost_type из legacy mp_storage_costs как есть, но других значений фактически НЕ занёс: live s3 2026-07-03 — во всей таблице ровно одно значение 'storage' (185 565 строк). Приёмка/штрафы из legacy в kernel не попали; при появлении нового источника значения появятся без схемного предупреждения (ENUM нет).

Читатели

• kernel/etl/build_fact_turnover.py:183–190 — storage_cost_28d = SUM(amount) за окно 28 дней по группе площадок.
• В data_api-роутерах прямых читателей нет (grep по репо @ b1d760b).

Поля fact_storage_costs (DDL gwptd_kernel.sql:456)

Поле	Тип	Что это	Откуда
id	bigint unsigned PK AI	суррогатный ключ	БД
model_id	int unsigned NOT NULL, FK dim_product	модель	nm_id → dim_product_identifier nmID
mp_id	tinyint unsigned NOT NULL, FK dim_marketplace	площадка (kernel пишет только 1 = WB FBO)	константа в SQL
cost_date	date NOT NULL	дата начисления хранения (из отчёта WB)	wb_paid_storage.cost_date
warehouse_name	varchar(255) NOT NULL DEFAULT ''	сырое имя склада WB	wb_paid_storage.warehouse
warehouse_canon	varchar(255) GENERATED STORED	нормализованное имя склада (правило как у стоков), часть UNIQUE	БД, из warehouse_name
cost_type	varchar(50) NOT NULL DEFAULT ''	тип расхода; live-факт: только 'storage' (см. выше)	константа kernel / legacy-бэкфилл
quantity	int NULL	штук на хранении (SUM баркодов модели)	SUM(wb_paid_storage.barcodes_count)
amount	decimal(12,2) NULL	стоимость хранения за день, руб	SUM(wb_paid_storage.warehouse_price)
volume	decimal(8,2) NULL	объём, л	SUM(wb_paid_storage.volume)
source_payload_id	bigint unsigned NULL	ссылка на payload intake (NULL = бэкфилл)	MAX(payload_id)
collected_at	timestamp NULL, auto-update	запись/обновление строки ETL	БД

Ключи: PK(id); UNIQUE uq_model_mp_date_whcanon_type (model_id, mp_id, cost_date, warehouse_canon, cost_type); KEY idx_model, mp_id.

---

Сводка ⚠️ и известные ограничения

1. v_fact_stocks_current: отсечка свежести 7 дней встроена в само view — фикс F-11, применён на s3 2026-07-03; внутри 7-дневного окна даты разных складов одной модели могут различаться (by design).
2. fact_prices_daily / Ozon: семантика полей сверена со swagger Ozon API (см. маппинг выше); ⚠️ marketing_price из v5-ответа удалён Ozon'ом ⇒ price_discounted для mp=3 всегда NULL (живёт только у WB).
3. fact_supplier_stock_price.price_buy_rub / price_retail: ⚠️ вопрос владельцу — правило расчёта на стороне 1С; в kernel нигде не используются (cost — только через price_buy_usd), не блокер.
4. fact_storage_costs.cost_type: live-факт — только 'storage' (185 565 строк, 2026-07-03); ENUM/CHECK нет — новые значения появятся без схемного предупреждения.