samreshu_docs/database/schema.md

Схема базы данных

Описание таблиц и связей. Фактическая Drizzle-схема создаётся в samreshu-backend, здесь — справочник.

Диаграмма связей

erDiagram
    users ||--o{ subscriptions : has
    users ||--o{ sessions : has
    users ||--o{ email_verification_codes : has
    users ||--o{ password_reset_tokens : has
    users ||--o{ oauth_accounts : has
    users ||--o| totp_secrets : has
    users ||--o{ tests : takes
    users ||--o{ user_stats : has
    users ||--o{ user_achievements : earns
    users ||--o{ user_question_log : tracks

    tests ||--o{ test_questions : contains

    question_bank ||--o{ question_cache_meta : has
    question_bank ||--o{ question_reports : receives

Таблицы

users

Основная таблица пользователей.

Колонка	Тип	Описание
id	uuid, PK
email	varchar, unique
password_hash	varchar	bcrypt/argon2
nickname	varchar	Отображаемое имя
avatar_url	varchar, nullable
country	varchar, nullable
city	varchar, nullable
self_level	enum, nullable	jun / mid / sen
is_public	boolean, default true	Публичный профиль
role	enum, default 'free'	guest / free / pro / admin
email_verified_at	timestamptz, nullable
created_at	timestamptz
updated_at	timestamptz

subscriptions

Подписки пользователей. Существует с первого дня (даже для Free — с plan='free').

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
plan	enum	free / pro
status	enum	active / trialing / cancelled / expired
started_at	timestamptz
expires_at	timestamptz, nullable
cancelled_at	timestamptz, nullable
payment_provider	varchar, nullable	yukassa / cloudpayments
external_id	varchar, nullable	ID подписки у провайдера

sessions

Активные сессии пользователя (устройства).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
refresh_token_hash	varchar
user_agent	varchar
ip_address	varchar
last_active_at	timestamptz
expires_at	timestamptz
created_at	timestamptz

email_verification_codes

Коды подтверждения email (для регистрации).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
code	varchar	Код из письма (обычно 6 цифр)
expires_at	timestamptz	Срок действия кода
created_at	timestamptz

password_reset_tokens

Токены для сброса пароля.

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
token_hash	varchar	Хеш токена из письма
expires_at	timestamptz	Срок действия
created_at	timestamptz

oauth_accounts

Привязанные OAuth-провайдеры (Phase 2).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
provider	enum	github / google
provider_user_id	varchar
created_at	timestamptz

totp_secrets

2FA через TOTP (Phase 2).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users, unique
secret	varchar	Зашифрованный TOTP-секрет
enabled	boolean, default false
created_at	timestamptz

tests

Пройденные тесты.

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
stack	enum	html / css / js / ts / react / vue / nodejs / git / web_basics
level	enum	basic / beginner / intermediate / advanced / expert
question_count	integer	10 / 20 / 30
mode	enum	fixed / infinite / marathon
status	enum	in_progress / completed / abandoned
score	integer, nullable	Количество правильных
started_at	timestamptz
finished_at	timestamptz, nullable
time_limit_seconds	integer, nullable

test_questions

Снепшот вопросов для конкретного теста. При старте теста вопросы копируются сюда из question_bank или генерируются LLM.

Колонка	Тип	Описание
id	uuid, PK
test_id	uuid, FK → tests
question_bank_id	uuid, FK → question_bank, nullable	Если из банка
order_number	integer	Порядок в тесте
type	enum	single_choice / multiple_select / true_false / short_text
question_text	text
options	jsonb, nullable	Варианты ответов
correct_answer	jsonb	Правильный ответ
explanation	text	Объяснение
user_answer	jsonb, nullable	Ответ пользователя
is_correct	boolean, nullable
answered_at	timestamptz, nullable

question_bank

Провалидированные вопросы для переиспользования.

Колонка	Тип	Описание
id	uuid, PK
stack	enum
level	enum
type	enum
question_text	text
options	jsonb, nullable
correct_answer	jsonb
explanation	text
status	enum	pending / approved / rejected
source	enum	llm_generated / manual
usage_count	integer, default 0	Сколько раз использован
created_at	timestamptz
approved_at	timestamptz, nullable

question_cache_meta

Метаданные генерации вопросов через LLM.

Колонка	Тип	Описание
id	uuid, PK
question_bank_id	uuid, FK → question_bank
llm_model	varchar	Модель, сгенерировавшая вопрос
prompt_hash	varchar	Хеш промпта
generation_time_ms	integer	Время генерации в мс
valid	boolean	Прошёл ли валидацию с первого раза
retry_count	integer	Количество retry при ошибках
questions_generated	integer	Сколько вопросов сгенерировано
raw_response	text, nullable	Сырой ответ LLM (опционально, для отладки)
created_at	timestamptz

question_reports

Жалобы пользователей на вопросы.

Колонка	Тип	Описание
id	uuid, PK
question_bank_id	uuid, FK → question_bank
user_id	uuid, FK → users
reason	text
status	enum	open / resolved / dismissed
created_at	timestamptz

user_stats

Агрегированная статистика по темам (обновляется после каждого теста).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
stack	enum
level	enum
total_questions	integer
correct_answers	integer
tests_taken	integer
last_test_at	timestamptz

Unique constraint: (user_id, stack, level)

user_achievements

Бейджи и достижения (Phase 3).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
achievement_code	varchar	Код достижения
earned_at	timestamptz

user_question_log

Лог: какие вопросы пользователь видел (для дедупликации).

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
question_bank_id	uuid, FK → question_bank
seen_at	timestamptz

payments

Платежи.

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
subscription_id	uuid, FK → subscriptions
amount	decimal
currency	varchar, default 'RUB'
status	enum	pending / succeeded / failed / refunded
provider	enum	yukassa / cloudpayments
external_id	varchar	ID у провайдера
created_at	timestamptz

payment_events

Лог webhook-событий от платёжных систем.

Колонка	Тип	Описание
id	uuid, PK
payment_id	uuid, FK → payments, nullable
provider	enum
event_type	varchar
payload	jsonb	Полный JSON от провайдера
processed	boolean, default false
created_at	timestamptz

audit_logs

Действия админов.

Колонка	Тип	Описание
id	uuid, PK
admin_id	uuid, FK → users
action	varchar	ban_user / approve_question / ...
target_type	varchar	user / question / promo_code
target_id	uuid
details	jsonb, nullable
created_at	timestamptz

notifications_log

История отправленных уведомлений.

Колонка	Тип	Описание
id	uuid, PK
user_id	uuid, FK → users
channel	enum	email / in_app / telegram / push
template	varchar	verify_email / reset_password / trial_ending / ...
status	enum	sent / failed
created_at	timestamptz

promo_codes

Промокоды.

Колонка	Тип	Описание
id	uuid, PK
code	varchar, unique
discount_percent	integer
max_uses	integer, nullable
used_count	integer, default 0
valid_from	timestamptz
valid_until	timestamptz
created_at	timestamptz

Индексы (ключевые)

• users.email — unique
• sessions.user_id — для списка устройств
• tests.user_id + tests.created_at — для истории
• question_bank.stack + question_bank.level + question_bank.status — для выборки вопросов
• user_question_log.user_id + user_question_log.question_bank_id — для дедупликации
• payments.user_id — для истории платежей

Примечания

• Все PK — UUID v7 (сортируемые по времени)
• Все timestamps — timestamptz (UTC)
• JSONB используется для вариантов ответов и webhook payload
• Enum-значения для stack/level определяются один раз и используются во всех таблицах