Full stack Developer

Глава 9. PostgreSQL: схема данных

Эта глава – про то, как спроектировать схему в PostgreSQL так, чтобы:

– API работало быстро и предсказуемо,

– данные не превращались в кашу через месяц,

– изменения можно было вносить без паники.

Мы будем держаться принципа: половина теории, половина практики. То есть: сначала понятные правила, затем – как это выглядит в схеме и что с этим делать руками.

9.1. Почему именно PostgreSQL (и что от него ожидать)

PostgreSQL – это “швейцарский нож” для бэкенда:

– умеет транзакции и строгую целостность;

– богатый SQL (CTE, оконные функции, JSON, полнотекст);

– отличные индексы и оптимизатор;

– работает одинаково хорошо и на ноутбуке, и на сервере.

Если вы планируете разрабатывать и учиться – Postgres благодарный. Он не делает вид, что всё в порядке, когда данные уже сломаны. Он честный: либо можно, либо нельзя.

Что установить для работы (по желанию):

– PostgreSQL (локально или в Docker)

– любой GUI-клиент: DBeaver, DataGrip, pgAdmin (что удобнее)

– psql (терминальный клиент; полезен как минимум для “быстро проверить”)

– Docker + docker-compose (чтобы одинаково запускать окружение)

9.2. Модель данных TaskFlow: от домена к таблицам

Напомним домен:

– User

– Workspace + участники (membership)

– Project

– Task

– Comment

– Label + связь task-label (many-to-many)

– Audit log

– Idempotency keys (для безопасного повтора POST)

Сразу полезное правило: таблица – это не “модель в коде”, а “факт в мире”.

Например, “участник workspace” – это факт, значит это отдельная таблица membership.

9.3. Нормализация: что это вообще и зачем вам это сегодня

Нормализация – это не ритуал. Это способ уменьшить вероятность того, что:

– вы храните одно и то же в пяти местах,

– оно расходится,

– и потом вы неделями чините отчёты и поиск.

9.3.1. Практическое правило №1: “Один факт – одно место”

Если роль пользователя в workspace хранится и в users, и в workspace_members, и в каком-то JSON – однажды вы поймаете ситуацию: “в одном месте admin, в другом member”. И вы проиграете спор с реальностью.

Роль в рамках workspace – это факт membership. Значит:

– workspace_members.role – единственный источник.

9.3.2. Практическое правило №2: “Массивы и списки – осторожно”

Например, хочется хранить метки как массив строк прямо в tasks.labels.

Это удобно первые два дня. Потом вы захотите:

– переименовать метку,

– вывести список всех меток workspace,

– посчитать статистику по меткам,

– не допускать дубликаты,

– индексировать фильтр по метке.

И внезапно вы понимаете, что метка – сущность, и у неё должна быть таблица.

Вывод: метки – отдельная таблица labels, а связь – task_labels.

9.3.3. Практическое правило №3: “Денормализация допустима, но осознанно”

Мы можем хранить workspace_id в tasks, даже если его можно вычислить через project. Это “снятие лишнего JOIN” для производительности и удобства фильтров.

Но тогда нужен механизм, который гарантирует консистентность (например, constraint, триггер, или правило в коде + тесты). В учебной системе можно держать workspace_id в tasks и контролировать его в приложении, а в идеале – усилить constraint’ами.

9.4. Схема данных: таблицы и ключевые поля

Ниже – списками, без таблиц (как вы просили). Названия – в стиле snake_case.

9.4.1. users

– id (uuid или bigserial; для современных систем uuid очень удобен)

– email (unique, not null)

– password_hash (not null)

– name (nullable)

– created_at (not null, default now())

– updated_at (not null, default now())

– deleted_at (nullable) – если хотите soft delete для пользователей (часто не надо; чаще “deactivated”)

Индексы:

– уникальный индекс на email

Constraints:

– email не пустой (можно простое CHECK (email <> ''))

– лучше хранить email в lower-case или нормализовать на входе

9.4.2. workspaces

– id

– name (not null)

– owner_user_id (not null, FK → users.id)

– created_at, updated_at

– deleted_at (nullable, если soft delete workspace)

Индексы:

– индекс на owner_user_id

Constraints:

– FK на owner

– CHECK (name <> '')

9.4.3. workspace_members

Это центральная таблица для прав.

– workspace_id (FK → workspaces.id)

– user_id (FK → users.id)

– role (not null; например: owner/admin/member)

– created_at

Ключи:

– составной PK (workspace_id, user_id) – так проще запретить дубль участия.

Constraints:

– role ограничить через CHECK (role IN ('owner','admin','member'))

или через PostgreSQL ENUM (ENUM удобен, но миграции могут быть чуть менее приятными; CHECK проще менять).

Индексы:

– индекс на user_id (быстрый поиск “где состоит пользователь”)

Практический нюанс:

– owner можно хранить и в workspaces.owner_user_id, и как запись membership с ролью owner.

Это полезно: membership даёт единый механизм прав, а поле owner – быстрый доступ и “один владелец”.

Тогда важно синхронизировать (обычно в коде + ограничениями).

9.4.4. projects

– id

– workspace_id (FK → workspaces.id)

– name

– description (nullable)

– status (active/archived)

– created_at, updated_at

– deleted_at (если soft delete проектов)

Индексы:

– (workspace_id, status) – список проектов по workspace

– возможно (workspace_id, name) если хотите искать по имени

Constraints:

– CHECK (status IN ('active','archived'))

9.4.5. tasks

– id

– workspace_id (FK → workspaces.id) – можно держать для ускорения фильтров

– project_id (FK → projects.id)

– title (not null)

– description (nullable)

– status (todo/in_progress/done/canceled)

– priority (low/medium/high, nullable)

– assignee_user_id (nullable, FK → users.id)

– reporter_user_id (not null, FK → users.id)

– due_date (nullable, date or timestamptz – зависит от требований)

– created_at, updated_at

– deleted_at (nullable; soft delete задач часто полезен)

Индексы (важное):

– (project_id, created_at desc, id desc) – для списков задач в проекте

– (workspace_id, status, created_at desc) – для фильтра по статусу

– (workspace_id, assignee_user_id, status) – “мои задачи”

– индекс на deleted_at не нужен, но частичный индекс полезен (см. ниже)

Partial index (практика):

– индекс только по “живым” задачам:

например индекс по (project_id, created_at desc) WHERE deleted_at IS NULL.

Это часто сильно помогает, потому что приложение почти всегда работает с живыми задачами.

Constraints:

– CHECK на статус и priority

– CHECK (title <> '')

Нюанс консистентности:

– если tasks.workspace_id хранится отдельно от projects.workspace_id, вы должны гарантировать, что они совпадают.

В простом варианте – правило в коде (при создании задачи берем workspace_id из проекта).

В строгом варианте – триггер или сложный constraint (в Postgres это решаемо, но для учебной книги можно оставить на уровне кода + тестов).

9.4.6. comments

– id

– task_id (FK → tasks.id)

– author_user_id (FK → users.id)

– body (not null)

– created_at, updated_at

– deleted_at (nullable)

Индексы:

– (task_id, created_at, id) – список комментариев к задаче

Constraints:

– CHECK (body <> '')

9.4.7. labels

– id

– workspace_id (FK → workspaces.id)

– name (not null)

– color (nullable)

– created_at, updated_at

– deleted_at (nullable)

Индексы:

– уникальность имени метки в workspace: unique (workspace_id, lower(name))

(обычно “Bug” и “bug” – одна и та же метка для людей)

9.4.8. task_labels

many-to-many связь:

– task_id (FK → tasks.id)

– label_id (FK → labels.id)

– created_at

Ключи:

– составной PK (task_id, label_id)

Индексы:

– индекс на label_id (чтобы быстро находить задачи по метке)

9.4.9. audit_events

– id

– workspace_id (nullable, FK → workspaces.id)

– actor_user_id (nullable, FK → users.id) – иногда события системные

– action (not null) – строка типа task.created

– entity_type (nullable) – task, project, …

– entity_id (nullable)

– ip (nullable)

– user_agent (nullable)

– metadata (jsonb, nullable)

– created_at

Индексы:

– (workspace_id, created_at desc)

– (actor_user_id, created_at desc)

– возможно (entity_type, entity_id)

9.4.10. idempotency_keys

Чтобы POST можно было безопасно повторять:

– id (uuid)

– user_id (FK → users.id)

– key (not null) – значение Idempotency-Key

– method (not null) – POST

– path (not null) – например /api/v1/projects/{id}/tasks

– request_hash (nullable) – чтобы ловить конфликт payload’ов

– response_code (not null)

– response_body (jsonb или text, not null)

– created_at

– expires_at

Индексы:

– unique (user_id, key, method, path)

– индекс на expires_at (чтобы чистить)

9.5. Индексы: как думать, чтобы не сделать “индекс на всё”

Индекс – это ускорение чтения ценой:

– места на диске,

– замедления записи,

– более сложного планирования.

9.5.1. Практическое правило: “индексируем то, чем фильтруем и сортируем”

Если ваш endpoint “список задач” почти всегда делает:

– WHERE workspace_id = ?

– AND status = ?

– ORDER BY created_at desc

То индекс должен соответствовать этой форме. Обычно порядок полей в индексе:

1) равенства (workspace_id, status)

2) сортировка (created_at, id)

9.5.2. “id в конце” для стабильной сортировки

Если вы делаете cursor pagination, вам нужна уникальная сортировка.

То есть сортируем по (created_at, id), а не только по created_at.

9.5.3. Partial indexes для soft delete

Если в таблице много удалённых записей, но вы почти всегда выбираете живые:

– делайте индекс с условием WHERE deleted_at IS NULL.

Это дешевле и эффективнее, чем индексировать всё подряд.

9.6. Constraints и foreign keys: когда база “воспитывает” приложение

Constraints – это ваша страховка от “случайно сохранили мусор”.

Что стоит делать всегда:

– NOT NULL там, где без значения сущность теряет смысл

– CHECK для ограниченных наборов значений (status, role)

– UNIQUE там, где нельзя дублировать (email, membership, label name)

– FOREIGN KEY почти везде, где есть ссылки

9.6.1. Foreign key и поведение при удалении

Варианты:

– ON DELETE RESTRICT – не даём удалить родителя, пока есть дети (часто лучший вариант)

– ON DELETE CASCADE – удаляем детей автоматически (опасно, но иногда удобно)

– ON DELETE SET NULL – “ссылка исчезла, но запись живёт”

Для TaskFlow здравый баланс:

– workspace → проекты: RESTRICT (или soft delete)

– project → tasks: RESTRICT/soft delete

– task → comments: RESTRICT/soft delete

– users в assignee: SET NULL (если пользователь деактивирован, задача остаётся, но без исполнителя)

9.6.2. Почему каскад иногда опасен

CASCADE легко превращается в кнопку “удалить всё случайно”.

Кто-то удалил workspace – исчезли проекты, задачи, комментарии, аудит (если вы не думали), и у вас остались только воспоминания.

9.7. Soft delete vs hard delete: как выбрать без философских войн

Hard delete – физическое удаление строки (DELETE FROM …).

Soft delete – отметка “удалено” (например deleted_at), но строка остаётся.

9.7.1. Когда soft delete – хороший выбор

– для задач и комментариев: полезно восстановление, аудит, разбор конфликтов

– для проектов: “архив” и “удаление” часто путают, лучше мягко

– для labels: чтобы не ломать историю и фильтры

9.7.2. Когда hard delete проще и честнее

– временные таблицы типа idempotency_keys (там вообще TTL)

– логины/сессии/refresh tokens (если вы их храните)

– технические данные, которые не нужны для истории

9.7.3. Цена soft delete

– каждый SELECT должен помнить deleted_at IS NULL

– индексы и запросы усложняются

– нужен механизм “чистки навсегда” (например, удалять soft-deleted старше N дней)

Практика: сделайте “живые данные” дефолтом:

– все основные запросы выбирают только deleted_at IS NULL

– отдельные admin-ручки могут показывать удалённые

9.8. Миграции: почему без них нельзя (и чем отличаются инструменты)

Миграции – это способ изменять схему базы управляемо, а не “я на проде руками поправлю”.

Есть несколько популярных инструментов. Мы сравним их по простым критериям: удобство, контроль, гибкость, и как они чувствуют себя в многоязычной книге.

9.8.1. Flyway

Подход:

– миграции – SQL-файлы с версиями (V1__init.sql, V2__add_tasks.sql)

Плюсы:

– очень простой ментально: SQL – значит SQL

– легко ревьюить

– хорошо подходит для PostgreSQL

Минусы:

– сложнее делать “условные” миграции, но чаще это и не нужно