Таблицы организаций, членства и проектов
Каркас API уже знает, как открыть соединение. Теперь база должна отражать модель из первого урока: один человек может работать в нескольких компаниях, а каждый проект принадлежит ровно одной компании. Рассмотрим схему до маршрутов, потому что удобный HTTP-адрес не исправляет неверную связь в таблицах.
Сквозные данные остаются прежними: Анна владеет «Севером» и наблюдает за «Югом», Борис владеет «Югом». В каждой компании есть проект с одинаковым названием «План сезона». Это намеренное совпадение поможет увидеть, что название не определяет владельца. SQL урока входит в sql/01-schema.sql и sql/02-seed.sql полного примера; выполнение схемы не проводилось.
Почему организация хранится в самой строке
Для списка проектов нужна быстрая и однозначная проверка принадлежности. Если узнавать компанию только через цепочку промежуточных таблиц, каждый новый обработчик обязан правильно пройти всю цепочку. Явное поле organization_id даёт одинаковый контракт чтению, обновлению, аудиту и экспорту. Оно также позволяет построить ограничения, которые проверяют обе стороны связи.
Ниже приведён фрагмент схемы. Определения пользователей и членства завершены; таблица организаций в архиве дополнительно содержит состояние жизненного цикла. Применяйте весь SQL-файл, если готовите полный проект: фрагмент служит для разбора ключей.
CREATE TABLE users (
subject text PRIMARY KEY,
normalized_email text NOT NULL UNIQUE
);
CREATE TABLE memberships (
organization_id uuid NOT NULL REFERENCES organizations(id),
subject text NOT NULL REFERENCES users(subject),
role text NOT NULL CHECK (role IN ('owner','admin','editor','viewer')),
PRIMARY KEY (organization_id, subject)
);
CREATE TABLE projects (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL,
name text NOT NULL CHECK (length(name) BETWEEN 1 AND 160),
version bigint NOT NULL DEFAULT 1,
deleted_at timestamptz,
PRIMARY KEY (organization_id, id)
);Составной первичный ключ членства предотвращает повторное добавление того же человека в одну компанию. Он не запрещает Анне иметь другую строку для второй компании. CHECK на роли ограничивает словарь допустимых значений. Без него опечатка owener могла бы попасть в базу и затем неожиданно не соответствовать ни одному правилу API.
Для проекта ключ также включает организацию. Даже если сервис генерирует практически уникальные UUID, составной ключ выражает модель на уровне базы. Он пригодится задачам, которые должны ссылаться на проект в той же компании. Случайное совпадение UUID не является основным риском; основной риск — программный код, который переносит ссылку через границу компании.
Исходные данные как часть объяснения
В учебном проекте идентификаторы фиксированы, чтобы запросы из разных уроков относились к тем же объектам. UUID «Севера» начинается с единиц, UUID «Юга» — с двоек. Так проще заметить неверный параметр в адресе или SQL. В настоящем сервисе новые организации получают случайные идентификаторы; читаемость учебных значений не превращается в требование к генератору.
Файл seed создаёт пользователей с адресами в домене .test. Эти адреса используются для сопоставления приглашения с текущей личностью, а не для отправки писем. Учебный имитатор входа выбирает уже существующего пользователя и не позволяет заголовку самостоятельно назначить ему новый email.
INSERT INTO users(subject, normalized_email) VALUES
('anna','anna@example.test'),
('boris','boris@example.test'),
('vera','vera@example.test');
INSERT INTO memberships(organization_id,subject,role) VALUES
('11111111-1111-1111-1111-111111111111','anna','owner'),
('22222222-2222-2222-2222-222222222222','anna','viewer'),
('22222222-2222-2222-2222-222222222222','boris','owner');Вера пока не состоит ни в одной компании. Позже она примет приглашение в «Север». Благодаря этому приглашение будет добавлять связь между существующей личностью и организацией. Если бы мы каждый раз создавали нового пользователя из текста приглашения, нельзя было бы надёжно отличить подтверждённый адрес человека от адреса, который ввёл администратор.
Ожидаемый результат запроса по членствам Анны — две строки с разными ролями. Это ещё не ответ API: прямой SQL владельца схемы видит данные иначе, чем приложение. Сейчас мы проверяем смысл модели. В дальнейшем одни и те же строки будут читаться для построения доверенного контекста, но проекты останутся доступны только через область конкретного запроса.
Ограничения дополняют проверки приложения
Проверка имени проекта в API нужна для понятного сообщения человеку. Ограничение в базе нужно для сохранения инварианта независимо от точки записи. Фоновая задача, импорт или будущая команда администратора могут обходить форму создания проекта, но не должны сохранять пустое имя. Эти два уровня решают разные задачи и потому не дублируют друг друга бессмысленно.
Документация PostgreSQL 18 о constraints описывает первичные, уникальные и внешние ключи. Для нашего проекта из этого следует практическое правило: важная принадлежность объекта должна быть проверяема самой базой. Однако база не знает, действительно ли HTTP-пользователь Борис прошёл вход. Доказательство личности остаётся обязанностью сервера.
Не пытайтесь выразить все права сложным CHECK, читающим таблицу членства. Проверка ограничения относится к сохраняемой строке, а права меняются со временем и зависят от запроса. После удаления членства старый проект не должен стать недопустимой строкой. Для контекста доступа мы используем отдельную проверку и RLS, а для связей данных — ключи.
Уникальность названия тоже требует предметного решения. В нашем контракте два проекта одной компании могут называться одинаково: человек различает их по идентификатору и содержимому. Добавление UNIQUE(name) запретило бы одинаковые названия сразу во всех компаниях. Даже вариант UNIQUE(organization_id,name) нельзя вводить автоматически только ради удобства поиска: он меняет разрешённое поведение продукта.
Порядок изменения схемы
SQL-файлы архива предназначены для новой локальной базы. Повторное применение CREATE TABLE к существующей схеме не является механизмом миграций. При дальнейшем развитии проекта сохраняйте отдельные изменения: добавление столбца, заполнение исходных значений и только затем новое ограничение. Так вы сможете объяснить, что происходит с уже сохранёнными строками.
Обратите внимание на поля времени. Для момента удаления используется timestamptz, поскольку это событие на общей временной шкале. Отображаемый местный час относится к интерфейсу. Если хранить удаление как текст «вчера вечером», фоновой задаче будет трудно сравнить сроки, а экспортам — сохранять одинаковый смысл в разных часовых поясах.
Индекс начинается с области
Составной ключ проектов начинается с organization_id. Это соответствует наиболее частой операции: отобрать строки одной компании, а затем найти объект внутри неё. Если позднее появится поиск по имени, его индекс также нужно выбирать по реальному условию области и порядку списка. Индекс не меняет права доступа и не исправляет отсутствующий WHERE. Он сокращает работу базы для уже правильного запроса. Похожее имя компании не должно влиять ни на ключи, ни на маршрут сохранения задачи.
Теперь в базе есть место для пользователей, организаций и проектов. Следующий урок превратит сочетание личности, адреса и членства в доверенный контекст компании. До этого любые прямые выборки владельца схемы следует рассматривать как административные действия. Они подтверждают наличие строк, но не демонстрируют изоляцию HTTP-клиентов и не заменяют будущие ограничения доступа.