gendesign/tradein-mvp/backend/data/sql/274_yandex_session_cookies.sql

-- 274_yandex_session_cookies.sql
-- Purpose: Encrypted storage for Yandex.Nedvizhimost browser session cookies.
--          Замер на проде (сайдкар, две карточки, вперемежку с/без кук, 2026-08-28):
--          author.phoneNumbers присутствует ТОЛЬКО в авторизованном режиме
--          (0 без кук → 2 с куками, одинаково на обеих карточках), плюс три
--          номера в author.phones сверху. Это контакт продавца с тегом канала
--          и redirectId.
--          NB: mainPhone НЕ собирать — он лежит в паспортном блоке рядом с
--          login/passportHost/passportPhones, то есть это номер НАШЕЙ учётки,
--          а не продавца. Первая версия этого замера приняла его за данные
--          объявления; счётчики по конкретным полям это опровергли.
--          encryptedPhones одинаков в обоих режимах (54) — зашифрованные токены
--          отдаются всегда, различие не в них.
--          Uses pgcrypto pgp_sym_encrypt for AES encryption at rest (зеркалит
--          027_cian_session_cookies.sql / 174_domclick_session_cookies.sql).
-- Dependencies:
--   - pgcrypto extension (installed here via CREATE EXTENSION IF NOT EXISTS)
-- Deploy order: Apply after 273.
--
-- Security notes:
--   - cookies_encrypted stores AES-encrypted JSON blob via pgp_sym_encrypt.
--   - Encryption key lives in .env.runtime (COOKIE_ENCRYPTION_KEY), never in DB.
--   - Access restricted to admin-token-gated API endpoint only.
--
-- Schema note: ключ записи — account_label (text), НЕ числовой id как у Cian
-- (account_user_id) / DomClick (account_cas_id) — у Яндекса нет внутреннего
-- числового id аккаунта, доступного из cookie-дампа; используем произвольную
-- текстовую метку аккаунта, которую задаёт оператор при заливке дампа.
--
-- Sources: issue #3192 (Yandex session cookies — author.phoneNumbers reveal)

BEGIN;

-- #2752: без lock_timeout CREATE INDEX встанет в очередь за чужой долгой
-- сессией и уведёт за собой запросы приложения — таблица новая и пустая, но
-- очередь блокировок этого не знает. Гейт check-migration-lock-timeout.py
-- держит правило на каждом PR.
SET LOCAL lock_timeout = '5s';

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS yandex_session_cookies (
    account_label        text        PRIMARY KEY,      -- произвольная метка аккаунта (нет числового id)
    cookies_encrypted    bytea       NOT NULL,          -- pgp_sym_encrypt(json_cookies, key)
    expires_at_estimate  timestamptz NOT NULL,          -- estimated expiry (~30 days from upload)
    uploaded_at           timestamptz NOT NULL DEFAULT NOW(),
    last_used_at          timestamptz,                   -- set on each successful authenticated fetch
    last_invalid_at       timestamptz,                   -- set when session no longer authenticated
    notes                 text                           -- e.g. 'Account: <label / email>'
);

-- Most recent upload first (admin dashboard ordering)
CREATE INDEX IF NOT EXISTS yandex_cookies_uploaded_idx
  ON yandex_session_cookies (uploaded_at DESC);

-- Active session lookup (non-expired and not invalidated)
CREATE INDEX IF NOT EXISTS yandex_cookies_active_idx
  ON yandex_session_cookies (expires_at_estimate)
  WHERE last_invalid_at IS NULL;

COMMIT;