Схема PostgreSQL и ограничения данных

В предыдущем уроке контроллеры DeskQueue обращались к хранилищу заявок через небольшой набор функций. Пока данные жили в памяти процесса: перезапуск возвращал начальное состояние, а два процесса не разделяли изменения. Теперь сохраним прежний HTTP-контракт, но зададим постоянное хранилище. Эта глава является самостоятельным дополнением по PostgreSQL 18. Переход от объектов JavaScript к SQL нельзя объяснить простой заменой названия драйвера: у реляционной базы есть собственные правила идентичности, связей и согласованности.

Наш сервис принимает заявки на обслуживание учебного оборудования. У заявки есть заголовок, состояние и автор. Позже сотрудник будет менять состояние, а история сохранит, кто выполнил переход. Сначала определим, какие значения допустимы независимо от конкретного маршрута. Это важнее выбора красивых имён столбцов: одинаковая схема должна защищать данные при обращении из API, служебной программы и ручной SQL-сессии.

Строка, ключ и связь

Таблица users описывает пользователей, tickets — заявки, ticket_events — события их обработки. Строка имеет первичный ключ. Мы выберем integer GENERATED ALWAYS AS IDENTITY: база назначает целое число, а API использует его как непрозрачный идентификатор. Номер не выражает порядковый номер заявки в интерфейсе и не обязан идти без пропусков. Неудачная вставка или откат транзакции могут оставить промежуток; это обычное свойство генератора идентификаторов.

Полный файл migrations/001-core.sql из состояния stages/10 создаёт начальную схему. Перед применением читателю понадобится отдельная пустая учебная база. Команды подключения и создание роли относятся к подготовке окружения; здесь мы не подключаемся к действующему серверу. Сохраните файл целиком, поскольку последующие запросы рассчитывают на эти имена и ограничения.

BEGIN;
CREATE TABLE users (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  password_hash text NOT NULL,
  role text NOT NULL DEFAULT 'requester'
    CHECK (role IN ('requester', 'agent')),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE tickets (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL CHECK (length(title) BETWEEN 1 AND 120),
  status text NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'working', 'closed')),
  author_id integer REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE ticket_events (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ticket_id integer NOT NULL REFERENCES tickets(id),
  actor_id integer REFERENCES users(id),
  from_status text NOT NULL,
  to_status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
COMMIT;

NOT NULL запрещает отсутствие значения, а CHECK проверяет условие для строки. Это разные задачи. Условие с NULL может получить неизвестный логический результат, поэтому проверка длины сама по себе не заменяет NOT NULL. Для заголовка требуются оба ограничения. Мы проверяем длину текста в базе, но пока не запрещаем строку из пробелов. На границе API позже появится нормализация trim; база остаётся последней защитой длины и допустимого состояния. Правила подробно описаны в документации ограничений PostgreSQL.

Внешний ключ author_id связывает заявку с существующим пользователем. На этом этапе столбец допускает NULL, потому что первые SQL-уроки ещё не добавили регистрацию. Это явный переходный контракт. В главе об аутентификации новые заявки начнут получать автора из проверенного токена. Мы не будем незаметно менять старые строки или подставлять выдуманного пользователя. Внешний ключ не копирует адрес электронной почты: изменение профиля не требует массового исправления каждой заявки.

Ограничение как часть поведения

Проверим схему мысленно на двух вставках. Первая создаёт корректную заявку без автора. Вторая пытается сохранить неизвестное состояние. Это задания для читательского стенда, а не выполненная здесь сессия.

INSERT INTO tickets (title) VALUES ('Заменить кабель проектора')
RETURNING id, title, status;
INSERT INTO tickets (title, status) VALUES ('Проверить микрофон', 'done');

Ожидаемый результат первой операции — одна строка с назначенным id, исходным заголовком и состоянием open. Вторая должна завершиться ошибкой ограничения, поскольку done отсутствует в согласованном словаре. Число идентификатора не фиксируем: оно зависит от предшествующих действий в конкретной базе. Контроллер позднее переведёт известную ошибку в подходящий HTTP-ответ, но сама база не знает ни HTTP, ни формы браузера.

Обратите внимание на отличие значения по умолчанию от проверки. DEFAULT 'open' используется, когда столбец не указан. Если клиент прямо передаёт NULL, значение по умолчанию его не исправляет. Такое разделение помогает обнаружить ошибку вызывающего кода. Слишком «помогающая» нормализация в каждом слое способна превратить неправильный запрос в неожиданную операцию и затруднить диагностику.

Тип timestamptz хранит момент времени. Представление в SQL-сессии зависит от её часового пояса; это не означает, что база хранит разные моменты для разных пользователей. HTTP API будет отдавать дату в едином формате, а интерфейс сможет показывать местное время. Не заменяйте время создания строкой в произвольном формате: сортировка и сравнение тогда начнут зависеть от написания даты.

Изменения схемы во времени

Миграция — сохранённый переход между версиями схемы. Файл 001-core.sql применяется один раз к пустому стенду. Он намеренно не содержит IF NOT EXISTS: случайное повторение должно обнаружиться, а не скрыть частично несовпадающую схему. Если таблица уже существует с другим набором столбцов, молчаливый пропуск создания не делает её совместимой с приложением. Сначала выясняют состояние базы и историю применённых изменений.

Весь файл обёрнут в транзакцию. Если создание третьей таблицы не удалось, первые изменения не следует считать завершённым выпуском схемы. Позже мы будем добавлять таблицы refresh-токенов и вложений отдельными файлами. Старую применённую миграцию не переписывают ради нового требования: иначе две учебные базы с одинаковым именем версии получат разные определения. Для курса важен такой же устойчивый путь, как для настоящего приложения.

История событий пока содержит состояния как текст. В основном хранилище они проверяются ограничением, а в истории будут записываться только после чтения текущей строки в транзакции. Само наличие таблицы событий ещё не обещает достоверный аудит: значение actor_id необходимо получать из проверенного контекста, а не доверять телу запроса. Здесь мы заложили структуру; механизм согласованной записи появится в отдельном уроке.

После применения этой миграции читатель получает три связанные таблицы с явными правилами. Приложение ещё не использует их автоматически. Следующий шаг соединит процесс Node.js с базой, объяснит пул подключений и покажет, как SQL-параметры отделяют данные заявки от текста команды.

Внешние ключи не создают автоматически все возможные индексы на ссылающихся столбцах. Для частого чтения событий по ticket_id может понадобиться отдельный индекс, а его необходимость зависит от объёма и запроса. Сейчас схема фиксирует целостность, а не обещает максимальную скорость. Мы сначала определим операции, затем рассмотрим подходящие пути чтения. Это предотвращает механическое добавление большого числа индексов до появления задачи.

Имена ограничений тоже могут быть частью сопровождения. При сложной схеме полезно назначать понятные имена, чтобы сообщение базы указывало на конкретное правило. Но публичный клиент всё равно должен получать устойчивый код приложения, а не зависящее от SQL имя. В нашем маленьком примере автоматических имён достаточно для учебной диагностики; общий формат ошибок появится позднее.

Сохраните миграцию отдельно от исходного JSON. JSON описывал две конкретные заявки, а SQL задаёт правила для множества будущих строк. Применение схемы не импортирует старые данные само по себе. Поэтому новая учебная база начинается пустой, а последующие операции создают строки через API.

Оглавление курса

Полные файлы учебного проекта. Примеры при подготовке текста не запускались; ответы и вывод в уроке описывают ожидаемый результат читателя.