gendesign/data/sql/auth/001_identity_schema.sql

-- auth/001: users + sessions — единое хранилище доступов для «Меры» и «Птицы».
--
-- WHY (почему отдельная БД и почему таблицы называются нейтрально):
--   Владелец продукта решил (2026-07-31) свести вход в «Меру» (trade-in, /trade-in) и
--   «Птицу» (раздел Site Finder, /site-finder/analysis/[cad]/ptica) к ОДНОЙ нейтральной
--   форме входа, вместо браузерного popup'а Caddy basic_auth. Значит, у хранилища доступов
--   два потребителя, и оно не должно принадлежать ни одному из них: живёт в отдельной БД
--   `auth` на платформенном сервере gendesign-postgres (тот же кластер, отдельная база —
--   новый контейнер не заводим; оба бэкенда сидят в сети gendesign_shared и TCP-достают
--   до gendesign-postgres-1:5432, проверено на проде 2026-07-31).
--   Отсюда имена без префикса продукта: `users`, а не `tradein_users`. Префикс продукта в
--   нейтральном хранилище означал бы, что вторая система — гость в чужой таблице, и через
--   полгода никто бы не помнил, кто владелец схемы.
--
--   Здесь НЕТ колонки `role` — сознательно. Идентичность («кто это, какой у него пароль,
--   активен ли доступ») общая для двух продуктов; полномочия внутри продукта (admin/manager/
--   employee в «Мере», админ-роуты в «Птице») — это знание продукта, оно остаётся в
--   продуктовых БД (tradein_users.role) и не переезжает сюда. Иначе `auth` пришлось бы
--   менять каждый раз, когда в одном из продуктов появляется новая роль.
--
-- WHAT:
--   1. users — identity. password_hash NULL допустим (см. комментарий к колонке): пароли
--      НИКОГДА не попадают в git, ни plaintext, ни bcrypt-хешем — конвенция репо, прецедент
--      tradein-mvp/backend/data/sql/193_tradein_users_seed.sql. Сид (003) вставляет строки
--      с password_hash = NULL, хеши проставляются на проде отдельно.
--   2. sessions — токен-based сессии, ON DELETE CASCADE от users (удалили пользователя —
--      его сессии теряют смысл). last_seen_at отдельно от created_at — для idle-timeout,
--      иначе «сессия жива 30 дней» и «человек не заходил 30 дней» неразличимы.
--   3. ASCII-CHECK на username — обязателен ДО появления прод-данных (см. ниже).
--
-- IDEMPOTENCY:
--   CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS; CHECK-констрейнты объявлены
--   inline в CREATE TABLE, а не через ALTER — при повторном прогоне CREATE TABLE не
--   выполняется вообще, значит констрейнт физически не может задублироваться (паттерн из
--   192_tradein_users_auth.sql).
--
-- Тип id: `bigint GENERATED ALWAYS AS IDENTITY` — стандартный (SQL-standard) эквивалент
--   bigserial: та же bigint-колонка на той же последовательности, но sequence принадлежит
--   таблице жёстко и не переживает DROP COLUMN сиротой, а прямой INSERT в id запрещён
--   (случайная вставка «своего» id, ломающая счётчик, невозможна). Ровно так объявлен
--   tradein_users.id в 192 — держим один тип на обе таблицы, чтобы будущий код, читающий
--   обе, не спотыкался о разницу.
--
-- Dependencies: нет (пустая БД `auth`, создаётся bootstrap-шагом деплоя,
--   см. ops/db-bootstrap/create_auth_db.sql).
-- Deploy order: Foundation. Роль приложения + гранты — 002, сид — 003. Python-код логина,
--   логин-страница и снятие Caddy basic_auth — отдельные PR'ы ПОСЛЕ этого
--   (SQL-схема первой, см. .claude/rules/sql.md «Migration order»).

BEGIN;

CREATE TABLE IF NOT EXISTS users (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username       text NOT NULL UNIQUE,
    password_hash  text NULL,
    display_name   text NULL,
    org_name       text NULL,
    email          text NULL,
    is_active      boolean NOT NULL DEFAULT true,
    created_at     timestamptz NOT NULL DEFAULT now(),
    updated_at     timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT users_username_ascii_ck CHECK (username ~ '^[A-Za-z0-9._-]{3,64}$')
);

COMMENT ON TABLE users IS
    'Единое хранилище доступов для «Меры» (trade-in) и «Птицы» (Site Finder) — только '
    'идентичность. Полномочия внутри продукта (роли) остаются в продуктовых БД: иначе эту '
    'таблицу пришлось бы менять при каждом изменении ролевой модели любого из продуктов.';

COMMENT ON COLUMN users.password_hash IS
    'NULL = пароль ещё не проставлен, вход по паролю для этой строки невозможен. Хеши '
    'НИКОГДА не хранятся в git (ни в сидах, ни в фикстурах) — их проставляют на проде '
    'отдельно от миграции; иначе один утёкший коммит открывает вход всем аккаунтам сразу.';

COMMENT ON COLUMN users.is_active IS
    'false = доступ закрыт владельцем продукта. Отдельная колонка, а не удаление строки: '
    'удаление каскадом снесло бы сессии и историю, а закрытие доступа обратимо и его надо '
    'уметь отличать от «такого пользователя никогда не было».';

COMMENT ON COLUMN users.org_name IS
    'Организация пользователя. NULL, пока реальные данные не подтверждены владельцем '
    'продукта — выдуманное название хуже пустого, оно выглядит достоверным.';

COMMENT ON CONSTRAINT users_username_ascii_ck ON users IS
    'Fail-closed запрет не-ASCII логинов (перенесено из tradein м.193, deep-review #2561): '
    'downstream-код кодирует username сессии через encode("latin-1","replace"), поэтому два '
    'кириллических логина ОДИНАКОВОЙ длины схлопываются в одну и ту же byte-строку из «?» — '
    'разные люди получают общую идентичность, общую квоту и взаимный IDOR (один видит данные '
    'другого). Констрейнт на уровне схемы, а не проверка в UI/API: проверку в коде однажды '
    'забудут добавить в новый путь создания пользователя, схему обойти нельзя.';

CREATE TABLE IF NOT EXISTS sessions (
    token         text PRIMARY KEY,
    user_id       bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at    timestamptz NOT NULL DEFAULT now(),
    expires_at    timestamptz NOT NULL,
    last_seen_at  timestamptz NOT NULL DEFAULT now(),
    ip_address    inet NULL,
    user_agent    text NULL
);

COMMENT ON TABLE sessions IS
    'Активные сессии единой формы входа (общие для «Меры» и «Птицы»). ON DELETE CASCADE от '
    'users: оставшаяся сессия удалённого пользователя — это действующий доступ без владельца.';

COMMENT ON COLUMN sessions.last_seen_at IS
    'Обновляется на каждом запросе — нужен для idle-timeout: без него «сессия не истекла» и '
    '«человек ещё работает» неразличимы, и забытая открытая вкладка живёт до expires_at.';

COMMENT ON COLUMN sessions.ip_address IS
    'IP на момент выдачи токена — для разбора инцидентов («откуда зашли под этим логином»), '
    'не для авторизации: привязка к IP ломает мобильных пользователей при смене сети.';

-- Индексы — как в tradein м.192: уборка протухших сессий по expires_at и выборка/отзыв
-- всех сессий одного пользователя по user_id (FK сам по себе индекс не создаёт, а без него
-- ON DELETE CASCADE на users делает seq scan по всей таблице сессий).
CREATE INDEX IF NOT EXISTS sessions_expires_at_idx
    ON sessions (expires_at);

CREATE INDEX IF NOT EXISTS sessions_user_id_idx
    ON sessions (user_id);

COMMIT;