База данных для приложения с ИИ: как спроектировать таблицы до генерации кода

Начинай схему базы не со списка слов, а с вопросов, на которые приложение должно отвечать. Для учебного приложения домашней библиотеки нужны три операции: показать книги, выдать конкретный экземпляр читателю и найти просроченные возвраты. Эти действия задают связи надёжнее, чем генератор, который сразу предложит десяток таблиц.

Из запросов — к сущностям

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

loans нужен как отдельная сущность, потому что отношения «читатель ↔ экземпляр» имеют собственные поля: даты выдачи и возврата. Если записать читателя прямо в copies, история прошлых выдач потеряется при следующем займе.

Типы, ключи и ограничения

Пусть books.id, copies.id, members.id и loans.id — первичные ключи; copies.book_id, loans.copy_id и loans.member_id — внешние ключи. isbn может быть nullable, потому что не каждое старое издание имеет известное значение; но если заполнен, его нельзя дублировать. Для due_on выбери календарный тип date, если важен день возврата без времени суток; время выдачи, если нужно, хранится отдельно с часовым поясом. Не называй поле status вместо фактов returned_at, если статус можно вычислить из возвращённой даты и дедлайна.

PostgreSQL constraints описывает PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL и CHECK: ограничения помогают не сохранить ссылку на несуществующую книгу или отрицательное число копий. Например, CHECK (due_on >= loaned_on) выражает правило внутри одной строки. Межстрочное правило «на экземпляре одновременно только одна активная выдача» требует отдельной модели/ограничения; не рассчитывай, что обычный CHECK прочитает другую строку. В PostgreSQL это можно выразить partial unique index, например CREATE UNIQUE INDEX one_active_loan_per_copy ON loans (copy_id) WHERE returned_at IS NULL;. Индекс не позволит двум параллельным запросам закрепить один экземпляр за разными читателями. Документация PostgreSQL описывает partial indexes как индекс только для строк, удовлетворяющих предикату (Partial Indexes). При гонке второй INSERT должен получить конфликт уникальности: backend переводит его в понятный ответ «экземпляр уже выдан», перечитывает доступность и не показывает успешную выдачу. Проверь двумя конкурентными транзакциями, что сохраняется ровно один активный займ; сам SQL здесь приведён как иллюстрация и не запускался.

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

CREATE TABLE books (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  isbn text UNIQUE,
  title text NOT NULL
);

CREATE TABLE copies (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint NOT NULL REFERENCES books(id),
  inventory_code text NOT NULL UNIQUE
);

CREATE TABLE members (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name text NOT NULL
);

CREATE TABLE loans (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  copy_id bigint NOT NULL REFERENCES copies(id),
  member_id bigint NOT NULL REFERENCES members(id),
  loaned_on date NOT NULL,
  due_on date NOT NULL,
  returned_at timestamptz,
  CHECK (due_on >= loaned_on)
);

Проверь модель теми же вопросами

Запрос «какие выдачи просрочены?» должен соединить loans, copies, books и members, отобрать returned_at IS NULL и due_on < CURRENT_DATE. «Сколько экземпляров сейчас доступно?» нельзя считать просто как общее число copies: вычти экземпляры с активным займом. Подготовь маленький seed набор: книга с двумя экземплярами, один активный займ, один закрытый. В ожидаемом результате первая книга доступна в одном экземпляре, а просроченной считается только активная запись с прошлой датой. Сравнивай результаты после каждого изменения модели, а не только отсутствие ошибок в генераторе.

Попроси ИИ составить для каждой таблицы словарь полей: смысл, тип, nullability, unique/FK/check и пример допустимой строки. Затем дай два-три реальных запроса, которые должен обслужить экран, и попроси показать joins. Если генератор не может связать поле с вопросом продукта, отложи его, а не создавай «на будущее».

Миграция и восстановление — тоже часть схемы

Схема будет меняться. В PostgreSQL ALTER TABLE добавляет/удаляет колонки, ограничения и изменяет типы. Для непустой таблицы опасно сразу добавить новый NOT NULL столбец без плана заполнения. Более управляемая последовательность для email_normalized: сначала добавить nullable-колонку; обновить приложение, чтобы записывать её; заполнить существующие строки; проверить пропуски и коллизии; добавить NOT NULL и UNIQUE; только затем удалить старое поле после проверки всех потребителей. Сначала проверяй последовательность на копии или тестовой базе и сохраняй SQL как версионированную миграцию.

Документация PostgreSQL по SQL dump описывает pg_dump как способ получить файл, из которого можно восстановить состояние базы. Наличие файла резервной копии ещё не означает, что восстановление прошло: раз в проверку включай восстановление в отдельную пустую тестовую базу и сравнение количества записей и связей. Эта учебная схема не определяет политику хранения данных, шифрования и доступа для реального сервиса; такие требования зависят от характера данных и инфраструктуры.

Проверяй отдельно: вставка займа с неизвестным copy_id отклоняется; дублирующий инвентарный код запрещён; due_on раньше loaned_on не сохраняется; удаление книги с выдачами имеет явно выбранное поведение; добавление миграции сохраняет старые строки; backup можно восстановить. Если в приложении есть несколько организаций, добавь tenant в модель и ограничения доступа — отдельная тема авторизации и ролей, а не декоративная колонка owner.