Table of Contents

Схема БД

Группы таблиц (всего 27)

Auth & Users (3)
Schedule (3)
Projects (7)
Workshops (8)
Files v2.0 (6)
Notifications (1)

Ключевые связи (FK)
Миграции

Как делать новую миграцию
Откат

Бэкап

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

Схема БД

PostgreSQL 15, база morozovka_portal, владелец morozovka. Все таблицы — SQLModel, миграции — Alembic.

Группы таблиц (всего 27)

Auth & Users (3)

user — пользователи

id, gitea_id (unique), username (unique), password_hash, full_name, email (unique), organization, role, is_active, is_verified, created_at

usersession — активные сессии

id, session_token (unique), user_id → user, ip_address, user_agent, created_at

useronboarding — прогресс тура

id, user_id (unique) → user, 6 флагов visited_*, completed_at, created_at

Schedule (3)

event — события

id, title, event_type, start_time, end_time, location, description, status, is_public, google_event_id (unique), manually_edited, created_at, audience, reason

globalstatus — текущий статус (singleton, всегда 1 запись)

id, status, delay_minutes, message, updated_at

eventsubscriber — подписки на email-уведомления

id, email (index), user_id (nullable) → user, unsubscribe_token (unique), is_confirmed, confirm_token (unique), notify_status, notify_events, created_at

Projects (7)

project

id, title, description, status, visibility, gitea_url, gitea_repo, owner_id → user, tags, start_date, end_date, contact_person, created_at

projectmember — участники + granular-права

id, project_id, user_id, role, 7 флагов, joined_at

projecttask — задачи (канбан)

id, project_id, title, description, status, priority, assignee_id (nullable), created_by, due_date, position, created_at, updated_at

projectpost

id, project_id, author_id, title, body, is_pinned, created_at, updated_at

projectpostattachment

id, post_id → projectpost, file_id → portalfile

projectsubscription

id, project_id, user_id, notify_email, notify_inapp, created_at
UNIQUE(project_id, user_id)

Workshops (8)

workshop

id, title, description, program, lead_id, category, difficulty, partner, status, visibility, location, materials_required, outcome, has_certificate, recurrence, max_participants, cover_url, tags, created_at

workshopassistant

id, workshop_id → workshop, user_id → user, created_at
UNIQUE(workshop_id, user_id)

workshopsession

id, workshop_id → workshop, start_time, end_time, location_override, note

workshopregistration

id, workshop_id → workshop, user_id → user, status (registered/attended/cancelled/no_show), notes, registered_at
UNIQUE(workshop_id, user_id)

workshopmaterial

id, workshop_id → workshop, title, description, file_id (nullable) → portalfile, external_url, visible_to, created_at

workshoppost

id, workshop_id → workshop, author_id → user, title, body, is_pinned, created_at, updated_at

workshoppostattachment

id, post_id → workshoppost, file_id → portalfile

workshopfeedback

id, workshop_id → workshop, user_id → user, rating, comment, created_at
UNIQUE(workshop_id, user_id)

workshopsubscription

id, workshop_id → workshop, user_id → user, notify_email, notify_inapp, created_at
UNIQUE(workshop_id, user_id)

Files v2.0 (6)

filespace — пространства

id, name, slug (unique), description, owner_id → user, project_id (nullable) → project, visibility, created_at

filefolder — папки (рекурсивные через parent_id)

id, space_id → filespace, parent_id (nullable) → filefolder, name, description, created_by → user, created_at

filespaceacl — права на пространство

id, space_id → filespace, user_id → user, role (viewer/editor/manager), created_at
UNIQUE(space_id, user_id)

filefolderacl — права на папку

id, folder_id → filefolder, user_id → user, can_view, can_upload, can_delete, can_manage, created_at
UNIQUE(folder_id, user_id)

portalfile — файлы

id, original_name, stored_name, size_bytes, uploaded_by → user, project_id (nullable) → project, uploaded_at
v2.0: space_id (nullable) → filespace, folder_id (nullable) → filefolder, description, tags, version (default 1), current_version_id (nullable → fileversion)

fileversion — версии файлов

id, file_id → portalfile, version_num, stored_name, size_bytes, uploaded_by → user, uploaded_at, comment

Notifications (1)

notification

id, user_id → user, title, body, link_url, kind, is_read, created_at

Ключевые связи (FK)

user
  ├── usersession (CASCADE)
  ├── useronboarding (CASCADE)
  ├── project.owner_id
  ├── projectmember.user_id
  ├── projecttask.assignee_id (SET NULL), created_by
  ├── projectpost.author_id
  ├── workshop.lead_id
  ├── workshopassistant.user_id (CASCADE)
  ├── workshopregistration.user_id (CASCADE)
  ├── workshoppost.author_id
  ├── notification.user_id (CASCADE)
  ├── filespace.owner_id
  ├── filefolder.created_by
  ├── filespaceacl.user_id
  ├── filefolderacl.user_id
  ├── fileversion.uploaded_by
  └── portalfile.uploaded_by

filespace
  ├── filefolder.space_id
  ├── filespaceacl.space_id
  └── portalfile.space_id (SET NULL)

filefolder
  ├── filefolder.parent_id
  ├── filefolderacl.folder_id
  └── portalfile.folder_id (SET NULL)

portalfile
  ├── fileversion.file_id
  ├── projectpostattachment.file_id (CASCADE)
  ├── workshoppostattachment.file_id (CASCADE)
  └── workshopmaterial.file_id (SET NULL)

Миграции

Текущее состояние:

alembic current
# → 0aab74aaa3fc (head) или новее

Ревизии:

efda7d432855 — baseline (no-op, stamp)
9be3c5145b6c — UserOnboarding
0aab74aaa3fc — FileSpace, FileFolder, ACLs, FileVersion + extend PortalFile
Event audience/reason

Как делать новую миграцию

cd /opt/morozovka-portal
export PATH=/opt/morozovka-portal/venv/bin:$PATH

# 1. Меняешь models.py
# 2. Генеришь
alembic revision --autogenerate -m "краткое описание"

# 3. Проверяешь alembic/versions/XXX_*.py
#    • нет drop_* для существующих таблиц (защита include_object)
#    • нет sqlmodel.sql.sqltypes.* (защита render_item)
#    • NOT NULL колонки имеют server_default
#    • FK на существующие таблицы — добавляй вручную

# 4. Применяешь
alembic upgrade head

# 5. Тестовая autogenerate — пустая
alembic revision --autogenerate -m "test: empty"
ls -t alembic/versions/*.py | head -1 | xargs cat | head -20
ls -t alembic/versions/*.py | head -1 | xargs rm -f

Откат

alembic downgrade -1
alembic downgrade <revision_hash>

Бэкап

PGPASSWORD='rf-slf-1945' pg_dump -Fc -h 127.0.0.1 -U morozovka morozovka_portal > backup.dump
PGPASSWORD='rf-slf-1945' pg_restore -h 127.0.0.1 -U morozovka -d morozovka_portal --clean backup.dump

Автоматический бэкап — TODO, см. Security:

pg_dump в 3:00
rsync на внешний сервер
Retention 30 дней