Zuev/magistr
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
Redesign
magistr/docs/DATABASE.md
dipatrik10 7e29c53ce9 комит
2026-07-27 00:33:16 +03:00

50 KiB
Raw Permalink Blame History

🗄 База данных

Общая информация

  • СУБД: PostgreSQL (локально postgres:16.3-alpine с закреплённым digest, продакшн — managed PostgreSQL)
  • Управление схемой: Flyway (программный запуск)
  • Hibernate DDL: Отключён (ddl-auto=none)
  • Расширения: pgcrypto (bcrypt-хеширование паролей), btree_gist (exclusion constraint временных слотов)
  • Мультитенантность: Каждый тенант = отдельная БД
  • Абсолютное время: TIMESTAMPTZ, Hibernate читает и записывает значения как UTC Instant
  • Календарные бизнес-даты: DATE, интерпретируются в зоне Europe/Moscow

ER-диаграмма

erDiagram
    departments {
        BIGSERIAL id PK
        VARCHAR name
        BIGINT code UK
    }

    specialties {
        BIGSERIAL id PK
        VARCHAR name
        VARCHAR specialty_code UK
    }

    specialty_profiles {
        BIGSERIAL id PK
        BIGINT specialty_id FK
        VARCHAR name
        TEXT description
    }
    
    users {
        BIGSERIAL id PK
        VARCHAR username UK
        VARCHAR password
        VARCHAR role
        VARCHAR full_name
        VARCHAR job_title
        BIGINT department_id FK
        VARCHAR status
        DATE active_from
        DATE active_to
        TIMESTAMPTZ archived_at
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    auth_refresh_tokens {
        BIGSERIAL id PK
        BIGINT user_id FK
        VARCHAR tenant
        VARCHAR token_hash UK
        TIMESTAMPTZ issued_at
        TIMESTAMPTZ expires_at
        TIMESTAMPTZ revoked_at
        VARCHAR rotated_to_token_hash
    }

    auth_login_rate_limits {
        BIGSERIAL id PK
        VARCHAR tenant UK
        VARCHAR username_normalized UK
        VARCHAR client_ip UK
        INTEGER failure_count
        TIMESTAMPTZ window_started_at
        TIMESTAMPTZ last_failure_at
        TIMESTAMPTZ blocked_until
        TIMESTAMPTZ updated_at
    }

    auth_login_attempt_audit {
        BIGSERIAL id PK
        VARCHAR tenant
        VARCHAR username_normalized
        VARCHAR client_ip
        VARCHAR outcome
        TIMESTAMPTZ occurred_at
        INTEGER retry_after_seconds
    }
    
    education_forms {
        BIGSERIAL id PK
        VARCHAR name UK
        TEXT description
        TIMESTAMPTZ created_at
    }
    
    student_groups {
        BIGSERIAL id PK
        VARCHAR name
        BIGINT group_size
        BIGINT education_form_id FK
        BIGINT department_id FK
        BIGINT specialty_id FK
        BIGINT specialty_profile_id FK
        BIGINT year_start_study
        TIMESTAMPTZ created_at
    }
    
    subgroups {
        BIGSERIAL id PK
        BIGINT group_id FK
        VARCHAR name
        INT student_capacity
    }
    
    subjects {
        BIGSERIAL id PK
        VARCHAR name UK
        VARCHAR code
        BIGINT department_id FK
        TEXT description
        TIMESTAMPTZ created_at
    }
    
    lesson_types {
        BIGSERIAL id PK
        VARCHAR name UK
        VARCHAR color_code
        INT duration_minutes
    }
    
    equipments {
        BIGSERIAL id PK
        VARCHAR name UK
        TEXT description
        VARCHAR inventory_number
    }
    
    classrooms {
        BIGSERIAL id PK
        VARCHAR name UK
        INT capacity
        VARCHAR building
        INT floor
        BOOLEAN is_available
        VARCHAR status
        DATE active_from
        DATE active_to
        TIMESTAMPTZ archived_at
        TEXT description
        TIMESTAMPTZ created_at
    }
    
    classroom_equipments {
        BIGINT classroom_id FK,PK
        BIGINT equipment_id FK,PK
        INT quantity
        TEXT notes
    }
    
    teacher_subjects {
        BIGINT user_id FK,PK
        BIGINT subject_id FK,PK
        VARCHAR qualification_level
        INT experience_years
    }

    teacher_department_assignments {
        BIGSERIAL id PK
        BIGINT teacher_id FK
        BIGINT department_id FK
        DATE valid_from
        DATE valid_to
        BOOLEAN is_primary
        TEXT comment
    }

    teacher_creation_requests {
        BIGSERIAL id PK
        BIGINT department_id FK
        VARCHAR username
        VARCHAR full_name
        VARCHAR job_title
        TEXT comment
        VARCHAR status
        BIGINT requested_by FK
        BIGINT reviewed_by FK
        TIMESTAMPTZ reviewed_at
        TEXT review_comment
        BIGINT created_teacher_id FK
    }

    subject_comments {
        BIGSERIAL id PK
        BIGINT subject_id FK
        BIGINT author_id FK
        TEXT comment
        TIMESTAMPTZ created_at
    }
    
    teacher_lesson_types {
        BIGINT user_id FK,PK
        BIGINT subject_id FK,PK
        BIGINT lesson_type_id FK,PK
    }
    
    time_slot_scopes {
        BIGSERIAL id PK
        VARCHAR code UK
        VARCHAR name
        VARCHAR apply_mode
        INT day_of_week
        BOOLEAN system_scope
        INT display_order
    }

    time_slots {
        BIGSERIAL id PK
        BIGINT time_slot_scope_id FK
        INT order_number
        TIME start_time
        TIME end_time
        INT duration_minutes
    }

    time_slot_date_assignments {
        BIGSERIAL id PK
        DATE assignment_date UK
        BIGINT time_slot_scope_id FK
    }

    academic_years {
        BIGSERIAL id PK
        VARCHAR title UK
        DATE start_date
        DATE end_date
    }

    semesters {
        BIGSERIAL id PK
        BIGINT academic_year_id FK
        VARCHAR semester_type
        DATE start_date
        DATE end_date
    }

    academic_calendar_activity_types {
        BIGSERIAL id PK
        VARCHAR code UK
        VARCHAR name
        BOOLEAN allow_schedule
        VARCHAR color_code
        INT display_order
    }

    academic_calendars {
        BIGSERIAL id PK
        VARCHAR title
        BIGINT academic_year_id FK
        BIGINT specialty_id FK
        BIGINT specialty_profile_id FK
        BIGINT study_form_id FK
        INT course_count
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    academic_calendar_days {
        BIGSERIAL id PK
        BIGINT calendar_id FK
        INT course_number
        DATE date
        INT week_number
        INT day_of_week
        BIGINT activity_type_id FK
    }

    academic_calendar_subjects {
        BIGSERIAL id PK
        BIGINT calendar_id FK
        INT semester_number
        BIGINT subject_id FK
        TIMESTAMPTZ created_at
    }

    student_group_calendar_assignments {
        BIGSERIAL id PK
        BIGINT group_id FK
        BIGINT academic_year_id FK
        BIGINT calendar_id FK
    }

    schedule_rules {
        BIGSERIAL id PK
        BIGINT subject_id FK
        BIGINT semester_id FK
        VARCHAR status
        DATE valid_from
        DATE valid_to
        INT lecture_academic_hours
        INT laboratory_academic_hours
        INT practice_academic_hours
        INT lecture_start_week
        INT laboratory_start_week
        INT practice_start_week
    }

    schedule_rule_groups {
        BIGINT schedule_rule_id FK,PK
        BIGINT group_id FK,PK
    }

    schedule_rule_slots {
        BIGSERIAL id PK
        BIGINT schedule_rule_id FK
        INT day_of_week
        VARCHAR parity
        BIGINT time_slot_id FK
        BIGINT teacher_id FK
        BIGINT classroom_id FK
        BIGINT lesson_type_id FK
        VARCHAR lesson_format
        BOOLEAN time_locked
        BOOLEAN classroom_locked
        BOOLEAN teacher_locked
    }

    schedule_overrides {
        BIGSERIAL id PK
        BIGINT base_rule_slot_id FK
        DATE lesson_date
        DATE target_lesson_date
        VARCHAR action
        BIGINT new_time_slot_id FK
        BIGINT new_classroom_id FK
        BIGINT new_teacher_id FK
        VARCHAR new_lesson_format
        TEXT comment
    }

    schedule_rule_slot_subgroups {
        BIGINT schedule_rule_slot_id FK,PK
        BIGINT subgroup_id FK,PK
    }
    
    departments ||--o{ users : "department_id"
    departments ||--o{ student_groups : "department_id"
    departments ||--o{ subjects : "department_id"
    education_forms ||--o{ student_groups : "education_form_id"
    specialties ||--o{ specialty_profiles : "specialty_id"
    specialties ||--o{ student_groups : "specialty_id"
    specialty_profiles ||--o{ student_groups : "specialty_profile_id"
    student_groups ||--o{ subgroups : "group_id"
    student_groups ||--o{ schedule_rule_groups : "group_id"
    student_groups ||--o{ student_group_calendar_assignments : "group_id"
    users ||--o{ teacher_subjects : "user_id"
    users ||--o{ auth_refresh_tokens : "user_id"
    users ||--o{ teacher_department_assignments : "teacher_id"
    departments ||--o{ teacher_department_assignments : "department_id"
    departments ||--o{ teacher_creation_requests : "department_id"
    users ||--o{ teacher_creation_requests : "requested_by/reviewed_by/created_teacher_id"
    users ||--o{ teacher_lesson_types : "user_id"
    users ||--o{ subject_comments : "author_id"
    subjects ||--o{ teacher_subjects : "subject_id"
    subjects ||--o{ subject_comments : "subject_id"
    subjects ||--o{ teacher_lesson_types : "subject_id"
    subjects ||--o{ academic_calendar_subjects : "subject_id"
    subjects ||--o{ schedule_rules : "subject_id"
    lesson_types ||--o{ teacher_lesson_types : "lesson_type_id"
    lesson_types ||--o{ schedule_rule_slots : "lesson_type_id"
    classrooms ||--o{ schedule_rule_slots : "classroom_id"
    classrooms ||--o{ classroom_equipments : "classroom_id"
    equipments ||--o{ classroom_equipments : "equipment_id"
    academic_years ||--o{ semesters : "academic_year_id"
    academic_years ||--o{ academic_calendars : "academic_year_id"
    academic_years ||--o{ student_group_calendar_assignments : "academic_year_id"
    semesters ||--o{ schedule_rules : "semester_id"
    specialties ||--o{ academic_calendars : "specialty_id"
    specialty_profiles ||--o{ academic_calendars : "specialty_profile_id"
    education_forms ||--o{ academic_calendars : "study_form_id"
    academic_calendars ||--o{ academic_calendar_days : "calendar_id"
    academic_calendars ||--o{ academic_calendar_subjects : "calendar_id"
    academic_calendars ||--o{ student_group_calendar_assignments : "calendar_id"
    academic_calendar_activity_types ||--o{ academic_calendar_days : "activity_type_id"
    schedule_rules ||--o{ schedule_rule_groups : "schedule_rule_id"
    schedule_rules ||--o{ schedule_rule_slots : "schedule_rule_id"
    schedule_rule_slots ||--o{ schedule_rule_slot_subgroups : "schedule_rule_slot_id"
    schedule_rule_slots ||--o{ schedule_overrides : "base_rule_slot_id"
    time_slots ||--o{ schedule_overrides : "new_time_slot_id"
    classrooms ||--o{ schedule_overrides : "new_classroom_id"
    users ||--o{ schedule_overrides : "new_teacher_id"
    time_slot_scopes ||--o{ time_slots : "time_slot_scope_id"
    time_slot_scopes ||--o{ time_slot_date_assignments : "time_slot_scope_id"
    time_slots ||--o{ schedule_rule_slots : "time_slot_id"
    subgroups ||--o{ schedule_rule_slot_subgroups : "subgroup_id"
    users ||--o{ schedule_rule_slots : "teacher_id"

Описание таблиц

Справочники высшего уровня

departments — Кафедры

Колонка Тип Описание
id BIGSERIAL PK ID кафедры
name VARCHAR(255) Название кафедры
code BIGINT UNIQUE Код кафедры

specialties — Специальности

Колонка Тип Описание
id BIGSERIAL PK ID специальности
name VARCHAR(255) Название специальности
specialty_code VARCHAR(255) UNIQUE Код ФГОС (напр. 10.03.01)

specialty_profiles — Профили обучения

Колонка Тип Описание
id BIGSERIAL PK ID профиля
specialty_id BIGINT FK → specialties Специальность, внутри которой существует профиль
name VARCHAR(255) Название профиля, уникально внутри специальности
description TEXT Описание профиля

Пользователи

users — Пользователи системы

Колонка Тип Описание
id BIGSERIAL PK ID пользователя
username VARCHAR(50) UNIQUE Логин
password VARCHAR(255) bcrypt-хеш пароля
role VARCHAR(20) ADMIN, TEACHER, STUDENT
full_name VARCHAR(255) ФИО
job_title VARCHAR(255) Должность
department_id BIGINT FK → departments Кафедра
status VARCHAR(20) ACTIVE или ARCHIVED; архивный пользователь не может войти
active_from DATE Дата начала действия записи
active_to DATE Дата окончания действия записи
archived_at TIMESTAMPTZ Когда пользователь архивирован
archive_reason TEXT Причина архивирования
created_at TIMESTAMPTZ Дата создания
updated_at TIMESTAMPTZ Дата обновления (авто-триггер)

Триггер: update_users_updated_at автоматически обновляет updated_at при любом UPDATE.

auth_refresh_tokens — Refresh-сессии JWT

Колонка Тип Описание
id BIGSERIAL PK ID refresh-сессии
user_id BIGINT FK → users (CASCADE) Пользователь
tenant VARCHAR(100) Тенант, для которого выдан refresh-токен
token_hash VARCHAR(64) UNIQUE SHA-256 хэш refresh-токена
issued_at TIMESTAMPTZ Дата выдачи
expires_at TIMESTAMPTZ Дата истечения
revoked_at TIMESTAMPTZ Дата отзыва, NULL для активной сессии
rotated_to_token_hash VARCHAR(64) Хэш следующего refresh-токена после ротации
user_agent VARCHAR(512) User-Agent клиента
ip_address VARCHAR(64) IP-адрес клиента

Сырой refresh-токен никогда не хранится в БД. При каждом POST /api/auth/refresh старый refresh-токен отзывается, а клиент получает новый refresh-cookie. Фоновая tenant-aware очистка удаляет только строки, чьи expires_at или revoked_at старше настраиваемого срока audit retention (по умолчанию 30 дней). Индексы idx_auth_refresh_tokens_cleanup_expires и idx_auth_refresh_tokens_cleanup_revoked обслуживают ограниченные batch-delete из V1.

auth_login_rate_limits — Общие счётчики попыток входа

Колонка Тип Описание
id BIGSERIAL PK ID состояния rate limit
tenant VARCHAR(100) Тенант запроса
username_normalized VARCHAR(100) NFKC-нормализованное имя в нижнем регистре
client_ip VARCHAR(64) Проверенный IP клиента
failure_count INTEGER Число отказов в текущем окне, не меньше нуля
window_started_at TIMESTAMPTZ Начало окна учёта попыток
last_failure_at TIMESTAMPTZ Время последнего отказа
blocked_until TIMESTAMPTZ Окончание временной блокировки либо NULL
created_at TIMESTAMPTZ Время создания состояния
updated_at TIMESTAMPTZ Последнее изменение состояния

Комбинация (tenant, username_normalized, client_ip) уникальна. Перед проверкой пароля backend создаёт строку через INSERT ... ON CONFLICT DO NOTHING, затем захватывает её FOR UPDATE; одна tenant-БД поэтому является общим атомарным хранилищем для всех pod. Индексы по blocked_until и updated_at обслуживают проверку и очистку.

auth_login_attempt_audit — Аудит неудачных входов

Колонка Тип Описание
id BIGSERIAL PK ID события
tenant VARCHAR(100) Тенант запроса
username_normalized VARCHAR(100) Нормализованное имя из запроса
client_ip VARCHAR(64) Проверенный IP клиента
outcome VARCHAR(20) FAILURE или BLOCKED
occurred_at TIMESTAMPTZ Время события
retry_after_seconds INTEGER Срок Retry-After для блокировки либо NULL

Таблица принципиально не содержит пароль, его хэш из запроса или признак существования пользователя. Записи старше настраиваемого срока (по умолчанию 90 дней), а также неактивные счётчики удаляются tenant-aware задачей ограниченными SKIP LOCKED пачками.

Учебный процесс

education_forms — Формы обучения

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(100) UNIQUE Название (Бакалавриат, Магистратура, Специалитет)
description TEXT Описание

Эта таблица используется и группами (student_groups.education_form_id), и календарными учебными графиками (academic_calendars.study_form_id). Отдельного справочника форм обучения для календарей нет.

student_groups — Учебные группы

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(100) Название группы (напр. ИВТ-21-1), не уникальное
group_size BIGINT CHECK (> 0) Количество студентов
education_form_id BIGINT FK → education_forms Форма обучения
department_id BIGINT FK → departments Кафедра
specialty_id BIGINT FK → specialties Специальность
specialty_profile_id BIGINT FK → specialty_profiles Профиль обучения группы
year_start_study BIGINT CHECK (> 0) Год начала обучения, используется для вычисления текущего курса
status VARCHAR(20) Жизненный цикл группы: ACTIVE или ARCHIVED
active_from DATE Дата начала действия группы
active_to DATE Дата окончания действия группы для исторических расчётов
archived_at TIMESTAMPTZ Дата и время архивирования
archive_reason TEXT Причина архивирования

subgroups — Подгруппы

Колонка Тип Описание
id BIGSERIAL PK ID
group_id BIGINT FK → student_groups (CASCADE) Родительская группа
name VARCHAR(100) Название подгруппы
student_capacity INT NOT NULL CHECK (> 0) Количество студентов

Уникальность активных записей задаётся парой (group_id, lower(name)): в разных группах могут быть подгруппы с одинаковым названием, а архивные подгруппы не блокируют повторное создание подгруппы с тем же именем. Подгруппы применяются только для лабораторных занятий.

CHECK-ограничения требуют положительные student_groups.group_size, student_groups.year_start_study и subgroups.student_capacity. Триггеры validate_student_group_subgroup_capacity и validate_subgroup_student_capacity блокируют родительскую группу и не допускают, чтобы сумма численностей активных подгрупп превышала group_size. Инвариант действует и при прямой записи в БД, смене статуса/родительской группы и конкурентных транзакциях.

subjects — Дисциплины

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(200) NOT NULL Название
code VARCHAR(20) Код предмета
department_id BIGINT FK → departments Кафедра
description TEXT Описание

Уникальный функциональный индекс uq_subjects_name_ci на lower(name) гарантирует глобальную уникальность названия без учёта регистра и защищает владение дисциплиной при конкурентном импорте разных кафедр. Индекс входит в единую baseline-миграцию V1.

Аудиторный фонд

classrooms — Аудитории

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(50) UNIQUE Название (напр. 101 Ленинская)
capacity INT CHECK(> 0) Вместимость
building VARCHAR(50) Корпус
floor INT Этаж
is_available BOOLEAN Доступна для назначения пар
status VARCHAR(20) ACTIVE или ARCHIVED; архивные аудитории не выбираются в новых назначениях
active_from DATE Дата начала действия записи
active_to DATE Дата окончания действия записи
archived_at TIMESTAMPTZ Когда аудитория выведена из эксплуатации
archive_reason TEXT Причина архивирования
description TEXT Описание

equipments — Оборудование

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(50) UNIQUE Название
description TEXT Описание
inventory_number VARCHAR(50) Инвентарный номер

classroom_equipments — Привязка оборудования к аудиториям

Колонка Тип Описание
classroom_id BIGINT PK, FK → classrooms (CASCADE) Аудитория
equipment_id BIGINT PK, FK → equipments (CASCADE) Оборудование
quantity INT CHECK(> 0) Количество единиц
notes TEXT Примечания

Расписание

lesson_types — Типы занятий (справочник)

Колонка Тип Описание
id BIGSERIAL PK ID
name VARCHAR(50) UNIQUE Название типа
color_code VARCHAR(7) HEX-цвет для UI (напр. #FF6B6B)
duration_minutes INT Длительность (по умолчанию 90)

Связи «Преподаватель ↔ Дисциплина»

teacher_subjects — Квалификация преподавателей

Колонка Тип Описание
user_id BIGINT PK, FK → users (CASCADE) Преподаватель
subject_id BIGINT PK, FK → subjects (CASCADE) Дисциплина
qualification_level VARCHAR(50) Уровень квалификации
experience_years INT Стаж

teacher_lesson_types — Типы занятий преподавателя

Колонка Тип Описание
user_id BIGINT PK, FK → users (CASCADE) Преподаватель
subject_id BIGINT PK, FK → subjects (CASCADE) Дисциплина
lesson_type_id BIGINT PK, FK → lesson_types (CASCADE) Тип занятия

teacher_department_assignments — История кафедр преподавателя

Колонка Тип Описание
id BIGSERIAL PK ID исторической записи
teacher_id BIGINT FK → users Преподаватель
department_id BIGINT FK → departments Кафедра
valid_from DATE Дата начала принадлежности
valid_to DATE Дата окончания принадлежности, NULL для текущей кафедры
is_primary BOOLEAN Основная кафедра преподавателя
comment TEXT Комментарий к переводу
created_at TIMESTAMPTZ Дата создания записи
created_by BIGINT FK → users Кто оформил перевод

Индекс uq_teacher_department_open_primary гарантирует не больше одной открытой основной кафедры у преподавателя. Ограничение ex_teacher_primary_department_no_overlap запрещает пересечение любых закрытых или открытых периодов основной кафедры одного преподавателя. Индекс uq_teacher_department_open_pair запрещает две открытые связи одного преподавателя с одной кафедрой, но позволяет иметь несколько открытых неосновных кафедр. Все эти объекты входят в единую baseline-миграцию V1__init.sql.

teacher_creation_requests — Заявки кафедр на создание преподавателей

Колонка Тип Описание
id BIGSERIAL PK ID заявки
department_id BIGINT FK → departments Кафедра, которая запрашивает преподавателя
username VARCHAR(50) Предлагаемый логин
full_name VARCHAR(255) ФИО преподавателя
job_title VARCHAR(255) Должность
comment TEXT Комментарий кафедры
status VARCHAR(20) PENDING, APPROVED или REJECTED
requested_by BIGINT FK → users Пользователь, создавший заявку
reviewed_by BIGINT FK → users Администратор, рассмотревший заявку
reviewed_at TIMESTAMPTZ Дата рассмотрения
review_comment TEXT Комментарий администратора
created_teacher_id BIGINT FK → users Созданный преподаватель после одобрения
created_at TIMESTAMPTZ Дата создания заявки
updated_at TIMESTAMPTZ Дата последнего изменения

Заявка не хранит пароль. Пароль задаётся администратором только при одобрении, после чего создаётся пользователь с ролью TEACHER и основная запись в teacher_department_assignments. Частичный уникальный индекс uq_teacher_creation_requests_pending_username запрещает две открытые заявки с одним логином.

subject_comments — Комментарии к дисциплинам

Колонка Тип Описание
id BIGSERIAL PK ID комментария
subject_id BIGINT FK → subjects Дисциплина
author_id BIGINT FK → users Автор комментария
comment TEXT Текст комментария
created_at TIMESTAMPTZ Дата создания

time_slot_scopes — Сетки времени

Колонка Тип Описание
id BIGSERIAL PK ID
code VARCHAR(50) UNIQUE Системный код сетки
name VARCHAR(120) Название в интерфейсе
apply_mode VARCHAR(20) DEFAULT, WEEKDAY, MANUAL
day_of_week INT NULL День недели для автоматической сетки
system_scope BOOLEAN Защищает базовую и субботнюю сетки от удаления
display_order INT Порядок в списках

Seed создаёт Базовая сетка (DEFAULT) и Субботняя сетка (WEEKDAY, day_of_week = 6). Дополнительные сетки создаются как MANUAL и применяются только через ручные назначения дат.

time_slots — Временные слоты занятий

Колонка Тип Описание
id BIGSERIAL PK ID
time_slot_scope_id BIGINT FK → time_slot_scopes Сетка времени
order_number INT Номер пары в дне
start_time TIME Время начала
end_time TIME Время окончания
duration_minutes INT Длительность в минутах

Уникальность задаётся индексом (time_slot_scope_id, order_number): в одной сетке может быть только один слот с номером пары. Базовая схема V1 дополнительно требует точного равенства duration_minutes разнице end_time - start_time в полных минутах и запрещает пересекающиеся интервалы одной сетки через GiST exclusion constraint ex_time_slots_scope_no_overlap. Интервалы трактуются как полуоткрытые [start, end), поэтому соседние пары разрешены.

Триггеры V1 сохраняют связь правил с базовой сеткой: schedule_rule_slots принимает только слот области DEFAULT, используемый слот нельзя перенести в небазовую область, а область с используемыми слотами нельзя сделать небазовой. Блокировки строк слота и области закрывают гонку между созданием правила и изменением сетки.

time_slot_date_assignments — Ручные назначения сеток времени

Колонка Тип Описание
id BIGSERIAL PK ID
assignment_date DATE UNIQUE Дата ручного применения
time_slot_scope_id BIGINT FK → time_slot_scopes Ручная сетка времени

academic_years — Учебные годы

Колонка Тип Описание
id BIGSERIAL PK ID
title VARCHAR(20) UNIQUE Название, например 2025-2026
start_date DATE Дата начала
end_date DATE Дата окончания

V1 создаёт GiST exclusion constraint ex_academic_years_no_overlap для включительных диапазонов дат. Поэтому два учебных года не могут содержать одну и ту же календарную дату; следующий год может начаться на следующий день после окончания предыдущего.

semesters — Семестры

Колонка Тип Описание
id BIGSERIAL PK ID
academic_year_id BIGINT FK → academic_years Учебный год
semester_type VARCHAR(20) autumn или spring
start_date DATE Дата начала, от неё считается неделя 1
end_date DATE Дата окончания

Пара (academic_year_id, semester_type) уникальна. V1 дополнительно запрещает пересечение включительных диапазонов семестров одного года через ex_semesters_year_no_overlap. Триггеры требуют полного вхождения семестра в границы родительского года и запрещают сужать учебный год так, чтобы существующий семестр оказался снаружи. Блокировка строки года в триггере сериализует эти взаимные проверки с конкурентными insert/update.

academic_calendar_activity_types — Коды активностей графика

Колонка Тип Описание
id BIGSERIAL PK ID
code VARCHAR(10) UNIQUE Код из Excel-графика: Т, Э, К, У, П, Пд, Н, Г, Д, ПА, С, *, =
name VARCHAR(100) Расшифровка кода
allow_schedule BOOLEAN Разрешает генерацию обычных пар
color_code VARCHAR(7) HEX-цвет для редактора
display_order INT Порядок вывода в UI
description TEXT Дополнительное описание

academic_calendars — Календарные учебные графики

Колонка Тип Описание
id BIGSERIAL PK ID
title VARCHAR(255) Название графика
academic_year_id BIGINT FK → academic_years Учебный год
specialty_id BIGINT FK → specialties Специальность
specialty_profile_id BIGINT FK → specialty_profiles Профиль обучения
study_form_id BIGINT FK → education_forms Форма обучения из общего справочника
course_count INT CHECK(1–8) Количество курсов в сетке
created_at TIMESTAMPTZ Дата создания
updated_at TIMESTAMPTZ Дата обновления

Триггер trg_academic_calendars_protect_dependencies не позволяет изменить учебный год, специальность, профиль, форму обучения или количество курсов так, чтобы уже назначенная группа стала несовместимой. Уменьшение course_count также запрещается, если в сетке остаются строки старших курсов или дисциплины старших семестров.

academic_calendar_days — Дневная сетка календарного графика

Колонка Тип Описание
id BIGSERIAL PK ID
calendar_id BIGINT FK → academic_calendars (CASCADE) Календарный график
course_number INT Номер курса
date DATE Дата учебного года
week_number INT Номер периода понедельник–воскресенье; неделя 1 содержит начало учебного года
day_of_week INT CHECK(1–7) День недели ISO
activity_type_id BIGINT FK → academic_calendar_activity_types Код активности

Триггер trg_calendar_days_dimensions требует, чтобы course_number не превышал academic_calendars.course_count, а дата находилась внутри учебного года графика.

academic_calendar_subjects — Дисциплины календарного графика

Колонка Тип Описание
id BIGSERIAL PK ID
calendar_id BIGINT FK → academic_calendars (CASCADE) Календарный график
semester_number INT CHECK(> 0) Номер учебного семестра внутри графика: 1, 2, 3 ...
subject_id BIGINT FK → subjects Дисциплина из справочника
created_at TIMESTAMPTZ Дата создания привязки

Уникальность задаётся по calendar_id + semester_number + subject_id, поэтому одну дисциплину нельзя дважды добавить в один семестр одного графика. Верхняя граница номера семестра проверяется backend и триггером trg_calendar_subjects_dimensions по academic_calendars.course_count * 2.

student_group_calendar_assignments — Назначения графиков группам

Колонка Тип Описание
id BIGSERIAL PK ID
group_id BIGINT FK → student_groups (CASCADE) Учебная группа
academic_year_id BIGINT FK → academic_years (CASCADE) Учебный год
calendar_id BIGINT FK → academic_calendars (CASCADE) Назначенный график

Назначение уникально для пары «группа + учебный год». Триггер trg_calendar_assignments_compatible проверяет совпадение года, специальности, профиля и формы обучения, а также попадание вычисленного курса группы в 1..course_count. Обратные триггеры защищают назначение при изменении группы, графика и границ учебного года; блокировки ссылочных строк закрывают конкурентные записи между несколькими backend-pod.

schedule_rules — Правила расписания

Колонка Тип Описание
id BIGSERIAL PK ID
subject_id BIGINT FK → subjects Дисциплина
semester_id BIGINT FK → semesters Семестр
lecture_academic_hours INT Лимит академических часов лекций
laboratory_academic_hours INT Лимит академических часов лабораторных работ
practice_academic_hours INT Лимит академических часов практик
lecture_start_week INT Неделя семестра, с которой начинаются лекции
laboratory_start_week INT Неделя семестра, с которой начинаются лабораторные
practice_start_week INT Неделя семестра, с которой начинаются практики
status VARCHAR(20) ACTIVE или ARCHIVED
valid_from DATE Начало действия версии правила
valid_to DATE Окончание действия версии правила
version_group_id BIGINT Группа версий одного правила
change_reason TEXT Причина изменения

ScheduleRule использует собственные поля жизненного цикла status, valid_from и valid_to: архивированное правило или правило вне периода действия не участвует в генерации расписания. В отличие от справочников на LifecycleEntity, таблица не содержит active_from/active_to, поэтому состояние правила проверяется по valid_*.

Базовая схема V1 требует, чтобы лимиты лекций, лабораторных и практик были кратны двум. Неотрицательность каждого лимита и положительная сумма уже закреплены ограничениями V1; нули допустимы только как лимиты неиспользуемых типов занятия.

schedule_rule_groups — Группы правила

Колонка Тип Описание
schedule_rule_id BIGINT PK, FK → schedule_rules Правило
group_id BIGINT PK, FK → student_groups Группа

schedule_rule_slots — Слоты правила

Колонка Тип Описание
id BIGSERIAL PK ID
schedule_rule_id BIGINT FK → schedule_rules Правило
day_of_week INT CHECK(1–7) День недели: 1 — понедельник
parity VARCHAR(10) BOTH, EVEN, ODD
time_slot_id BIGINT FK → time_slots Базовый временной слот
teacher_id BIGINT FK → users Преподаватель
classroom_id BIGINT FK → classrooms Аудитория
lesson_type_id BIGINT FK → lesson_types Тип занятия
lesson_format VARCHAR(30) Очно или Онлайн
time_locked BOOLEAN Время закреплено учебным отделом
classroom_locked BOOLEAN Аудитория закреплена учебным отделом
teacher_locked BOOLEAN Преподаватель закреплён учебным отделом
locked_by BIGINT FK → users Кто выполнил закрепление
locked_at TIMESTAMPTZ Когда выполнено закрепление
lock_comment TEXT Комментарий к закреплению

V1 добавляет uq_schedule_rule_slots_exact_payload: в одном правиле нельзя повторить одинаковые день, чётность, базовый временной слот, преподавателя, аудиторию, тип и формат занятия. Более широкие ресурсные пересечения и семантика подгрупп проверяются сервисом, поскольку зависят от нескольких таблиц и фактических активных недель.

schedule_rule_slot_subgroups — Подгруппы лабораторного слота

Колонка Тип Описание
schedule_rule_slot_id BIGINT PK, FK → schedule_rule_slots (CASCADE) Слот правила
subgroup_id BIGINT PK, FK → subgroups (CASCADE) Подгруппа

Связь заполняется только для лабораторных слотов. Для потоковой лабораторной можно выбрать разные подгруппы разных групп в одном слоте. Лекции и практики не делятся на подгруппы и не имеют записей в этой таблице. Триггер validate_schedule_rule_slot_subgroups проверяет тип занятия, принадлежность подгруппы к группам правила и запрет на две подгруппы одной группы в одном слоте; для быстрых выборок есть индекс idx_schedule_rule_slot_subgroups_subgroup.

schedule_overrides — Точечные изменения пар

Колонка Тип Описание
id BIGSERIAL PK ID изменения
base_rule_slot_id BIGINT FK → schedule_rule_slots Базовый слот правила
lesson_date DATE Исходная дата конкретной пары из базового правила
target_lesson_date DATE NULL Новая дата единственного занятия при MOVE
action VARCHAR(20) MOVE, CANCEL, REPLACE
new_time_slot_id BIGINT FK → time_slots Новый временной слот
new_classroom_id BIGINT FK → classrooms Новая аудитория
new_teacher_id BIGINT FK → users Новый преподаватель
new_lesson_format VARCHAR(30) Новый формат занятия
comment TEXT Причина изменения
created_by BIGINT FK → users Автор изменения
created_at TIMESTAMPTZ Дата создания

Ограничение uq_schedule_overrides_slot_date не позволяет создать две разные правки для одной и той же пары. Базовая схема V1 добавляет структурные инварианты:

  • CANCEL не содержит новых ресурсов;
  • MOVE содержит новый временной слот;
  • REPLACE не содержит нового времени и содержит нового преподавателя, аудиторию или формат;
  • new_lesson_format равен Очно, Онлайн либо NULL.
  • target_lesson_date отличается от lesson_date, разрешена только для MOVE и требует new_time_slot_id.

Частичный индекс idx_schedule_overrides_target_date ускоряет выборку входящих переносов, а индекс исходной даты idx_schedule_overrides_date обслуживает исходящие изменения и отмены. Диапазонный реестр и поиск используют обе даты.

Фактическое существование пары, тот же семестр, учебный календарь затронутых групп, lifecycle ресурсов, эффективная сетка целевого дня, реальность изменения и ресурсные конфликты проверяются транзакционным сервисом, а не SQL CHECK.

Flyway миграции

Правила работы

  1. Все миграции находятся в backend/src/main/resources/db/migration/
  2. Формат имени: V{номер}__{описание}.sql (напр. V1__init.sql, V2__add_departments.sql)
  3. ЗАПРЕЩЕНО изменять уже закоммиченные файлы миграций — это сломает контрольные суммы Flyway. Исключение допускается только по прямой просьбе пользователя и при полном сбросе tenant-БД.
  4. Flyway запускается программно при первом обращении к БД тенанта (TenantConfigWatcher.initDatabaseForTenant())
  5. Настройка baselineOnMigrate=true — если в БД уже есть данные, Flyway начнёт с baseline

Текущие миграции

Файл Описание
V1__init.sql Полная baseline-схема: справочники, роли, refresh-сессии JWT, PostgreSQL rate limit и аудит входа, lifecycle-поля, история кафедр, календарные графики, динамическое расписание, точечные изменения с переносом даты, seed, CHECK/UNIQUE/GiST-ограничения, конкурентно безопасные триггеры и комментарии
V2__align_academic_calendar_weeks_to_monday.sql Перенумерация сохранённых дней календарного графика по периодам понедельник–воскресенье, чтобы неполная первая неделя не заполнялась датами следующей недели

Этап разработки

По прямому решению владельца проекта прежние разработческие миграции V2–V7 были объединены в baseline V1. Новая миграция V2__align_academic_calendar_weeks_to_monday.sql создана после фиксации baseline и накатывается поверх существующих tenant-БД без изменения контрольной суммы V1.

Полный сброс БД (локально)

docker compose down -v    # Удаляет volumes (данные)
docker compose up -d      # Пересоздаёт БД с нуля
Reference in New Issue View Git Blame Copy Permalink