Перейти к содержимому
30 мин чтения

Проектирование схемы базы данных: паттерны, которые стареют достойно

Правила нормализации, соглашения об именовании, мягкое удаление, аудит-трейлы, паттерны мультитенантности, стратегии версионирования и решения по схеме, о которых я пожалел. Фокус на PostgreSQL.

databasepostgresqlarchitecturebackendsql
Поделиться:X / TwitterLinkedIn

Схема — это контракт с твоим будущим «я». Каждая колонка, которую ты добавляешь, каждое ограничение, которое пропускаешь, каждое «мы потом почистим» — всё это накапливается. Я работал с системами, где одно плохое решение по схеме, принятое три года назад, обходится команде в полный спринт каждый квартал на костыли и обходные пути.

База данных переживает всё. Твой фронтенд-фреймворк поменяется. API-слой перепишут. Стратегия деплоя эволюционирует. Но данные? Данные остаются. И форма, которую ты им придал в первый день, преследует тебя вечно, потому что мигрировать таблицу с 200 миллионами строк — это не то же самое, что отрефакторить React-компонент.

Вот что я узнал о принятии решений по схеме, которые не преследуют тебя потом. Фокус на PostgreSQL, потому что я его использую и доверяю ему, но большинство этих паттернов применимы к любой реляционной базе данных.

Соглашения об именовании: скучная штука, которая важнее всего#

Я видел больше споров о соглашениях об именовании, чем о реальных архитектурных решениях. Вот к чему я пришёл после лет переключения между проектами:

Snake_case для всего. Таблицы, колонки, индексы, ограничения. Никакого camelCase, никакого PascalCase. PostgreSQL всё равно приводит идентификаторы без кавычек к нижнему регистру, так что createdAt превращается в createdat, если не заключать его в двойные кавычки повсюду. Не борись с базой данных.

Множественное число для таблиц. Таблица хранит много строк. users, а не user. orders, а не order. order_items, а не order_item. Это естественно читается в запросах: SELECT * FROM users WHERE ... — ты выбираешь из коллекции.

Суффикс _id для внешних ключей. user_id, order_id, tenant_id. Первичный ключ — просто id. Это однозначно. Когда джойнишь таблицы, users.id = orders.user_id читается как обычное предложение.

Суффикс _at для временных меток. created_at, updated_at, deleted_at, published_at, expires_at. Всегда понятно, что это момент времени.

Префикс is_ для булевых значений. is_active, is_verified, is_published. Некоторые используют has_ для булевых значений владения (has_mfa_enabled), но я придерживаюсь простоты и использую is_ для всего.

Суффикс _count для денормализованных счётчиков. comment_count, follower_count. Сразу понятно, что это кэшированное число, а не вычисление в реальном времени.

Вот в чём дело: последовательность важнее совершенства. Я видел команды, которые тратили недели на обсуждение, как назвать — email_address, email или email_addr. Просто выбери один паттерн и применяй его везде. Худшее соглашение об именовании — то, которое применяется непоследовательно.

sql
-- Good: consistent, readable, no surprises
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    login_count     INTEGER NOT NULL DEFAULT 0,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Bad: inconsistent casing, mixed conventions, ambiguous names
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- is this 0/1? a verification level?
    lastLogin       TIMESTAMP,       -- camelCase in a snake_case world
    created         TIMESTAMP        -- created what? when?
);

И ещё одно: никогда не используй зарезервированные слова в качестве имён колонок. type, order, user, group, table — они все работают, если обернуть в кавычки, но создадут проблемы в ORM, построителях запросов и любых инструментах динамической генерации SQL. Используй kind вместо type, sort_order вместо order. Будущий ты скажет спасибо.

Стандартные колонки: что получает каждая таблица#

Каждая таблица в моих схемах начинается с одного и того же скелета. Без исключений.

sql
CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... domain columns ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Дебаты UUID vs BIGSERIAL#

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

BIGSERIAL (автоинкремент):

  • 8 байт. Компактный. Быстрый для индексов и джойнов.
  • Упорядоченный — можно сортировать по id, чтобы получить порядок вставки.
  • Предсказуемый — пользователь может угадать другие ID, просто прибавив единицу.
  • Плохо работает в распределённых системах (нужна координация).

UUID v4 (случайный):

  • 16 байт. Большие индексы, медленнее джойны (но редко бывает узким местом).
  • Непредсказуемый — нет утечки информации.
  • Работает в распределённых системах без координации.
  • Ужасная локальность индексов — случайные UUID фрагментируют B-tree индексы.

UUID v7 (отсортированный по времени, RFC 9562):

  • 16 байт, но отсортированный по времени, так что локальность B-tree отличная.
  • Достаточно непредсказуемый для внешнего использования.
  • Относительно новый, но PostgreSQL 17+ имеет gen_random_uuid(), и можно использовать uuid_generate_v7() с расширениями.

Моя текущая позиция: BIGSERIAL для внутренних таблиц, UUID v7 для всего, что выставлено наружу. Если ID когда-либо появляется в URL, ответе API или webhook-пейлоаде — используй UUID. Если это чистая таблица связей, которую пользователи никогда не видят — BIGSERIAL вполне подходит.

sql
-- For an API-facing resource
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- For an internal mapping table
CREATE TABLE project_members (
    id          BIGSERIAL PRIMARY KEY,
    project_id  UUID NOT NULL REFERENCES projects(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL DEFAULT 'member',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, user_id)
);

Временные метки: всегда TIMESTAMPTZ#

Используй TIMESTAMPTZ, а не TIMESTAMP. Версия с «TZ» хранит значение в UTC и конвертирует при чтении на основе часового пояса сессии. Версия без TZ хранит то, что ты ей дал, без контекста часового пояса — а это значит, что если два сервера в разных часовых поясах пишут в одну таблицу, ты получаешь тихое повреждение данных.

sql
-- Always this
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Never this
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

Триггер updated_at#

У PostgreSQL нет аналога MySQL ON UPDATE CURRENT_TIMESTAMP. Нужен триггер:

sql
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Apply to every table that has updated_at
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Да, нужен один триггер на таблицу. Да, это утомительно. Напиши хелпер для миграций, который создаёт триггер автоматически при добавлении таблицы. Оно того стоит, потому что альтернатива — помнить, что нужно ставить updated_at = NOW() в каждом UPDATE-запросе по всему приложению — и ты точно забудешь.

Нормализация: когда нарушать правила#

Каждый курс информатики учит нормализации до 3НФ (Третья нормальная форма). Правила такие:

  • 1НФ: Каждая колонка хранит одно атомарное значение. Никаких массивов, никаких списков через запятую.
  • 2НФ: Каждая не ключевая колонка зависит от всего первичного ключа (актуально для составных ключей).
  • 3НФ: Нет транзитивных зависимостей. Если колонка A определяет колонку B, а B определяет C, то C не должна быть в одной таблице с A.

На практике 3НФ — это оптимум для транзакционных таблиц. Начинай с неё и отклоняйся только тогда, когда у тебя есть конкретная, измеримая причина.

Вот правильно нормализованная система заказов:

sql
CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Обрати внимание на unit_price_cents в order_items. Это намеренная денормализация. Мы фиксируем цену на момент заказа, потому что цена товара может измениться позже. Это одна из самых распространённых и правильных причин для денормализации.

Когда денормализация — правильный выбор#

Таблицы для отчётов. Если твоему аналитическому дашборду нужно джойнить 8 таблиц для отрисовки, создай денормализованную таблицу для отчётов и наполняй её фоновой задачей. Транзакционная схема остаётся чистой, а запросы для отчётов — быстрыми.

sql
-- Denormalized reporting table, populated by a cron job
CREATE TABLE order_reports (
    id                  BIGSERIAL PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    customer_email      TEXT NOT NULL,
    customer_name       TEXT NOT NULL,
    product_names       TEXT[] NOT NULL,        -- array is fine here
    item_count          INTEGER NOT NULL,
    total_cents         INTEGER NOT NULL,
    order_status        TEXT NOT NULL,
    ordered_at          TIMESTAMPTZ NOT NULL,
    report_generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Материализованные представления. MATERIALIZED VIEW в PostgreSQL — недооценённая фича. Это денормализованный снимок, который обновляешь по требованию. Идеально для дашбордов.

sql
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;
 
-- Refresh it nightly
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

JSON-колонки для динамических атрибутов. Когда разные строки нуждаются в разных полях — варианты товаров, отправки форм, пользовательские настройки — JSONB-колонка зачастую лучше, чем кошмарная EAV (Entity-Attribute-Value) таблица. Подробнее об этом позже.

Одно правило, которое я никогда не нарушаю#

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

Внешние ключи и ограничения: лучший код, который ты никогда не напишешь#

Я слышал все отговорки для пропуска внешних ключей. «Они замедляют запись.» «Мы проверяем это в приложении.» «Нам нужна гибкость.»

Всё это неправда.

Внешние ключи — это самая эффективная вещь, которую можно добавить в схему. Они предотвращают целые категории багов, которые никакой код приложения не поймает — гонки состояний, частичные сбои, осиротевшие строки от неудачных транзакций. Внешний ключ — это гарантия от самого движка базы данных, применяемая на уровне хранилища. Хук beforeDelete в твоём ORM — это лишь пожелание.

sql
-- Always do this
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- The "we'll handle it in code" alternative:
-- Hope. Hope is not a strategy.

Стратегии ON DELETE#

Здесь начинаются нюансы. Что происходит при удалении родительской строки?

RESTRICT (по умолчанию): Удаление не проходит, если существуют дочерние строки. Используй для большинства связей. Нельзя удалить клиента, у которого есть заказы — это бизнес-логика, закодированная в схеме.

sql
-- Customer can't be deleted while they have orders
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Дочерние строки автоматически удаляются. Используй осторожно и осознанно. Подходит для связей «является частью», где дочерний элемент не имеет смысла без родителя.

sql
-- Deleting an order deletes its line items — they're meaningless alone
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Deleting a project deletes its memberships
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: Колонка внешнего ключа устанавливается в NULL. Используй, когда связь необязательна и дочерняя строка имеет смысл сама по себе.

sql
-- If a manager leaves, their reports still exist — just unassigned
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Моё правило: по умолчанию RESTRICT, CASCADE для связей композиции, SET NULL для необязательных ассоциаций. Если сомневаешься — RESTRICT всегда безопасный выбор. Ослабить ограничение проще, чем восстановить удалённые данные.

Check-ограничения: дешёвая страховка#

Check-ограничения почти ничего не стоят при записи и навсегда предотвращают попадание мусорных данных в систему:

sql
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    discount_pct    NUMERIC(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    weight_kg       NUMERIC(10,3) CHECK (weight_kg > 0),
    status          TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Каждое добавленное check-ограничение — это один баг валидации, который тебе никогда не придётся дебажить в продакшене. База данных — последняя линия обороны. Используй её.

Уникальные ограничения и частичные уникальные индексы#

Уникальные ограничения просты для обычных случаев:

sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Но они становятся интересными с частичными индексами — уникальными ограничениями, которые действуют только для определённых строк:

sql
-- Only one active subscription per user (but they can have many cancelled ones)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Only one primary address per user
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Это одна из убойных фич PostgreSQL. Используй её.

Мягкое удаление: паттерн, который все любят ненавидеть#

Мягкое удаление простое по концепции: вместо DELETE FROM users WHERE id = 42 делаешь UPDATE users SET deleted_at = NOW() WHERE id = 42. Строка остаётся в базе данных, но отфильтровывается из обычных запросов.

sql
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- The partial index: filter out deleted rows efficiently
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Теперь каждый запрос в приложении должен содержать WHERE deleted_at IS NULL. Каждый. Единственный. Запрос. ORM помогают с этим (Prisma имеет middleware, Drizzle имеет .where(isNull(deletedAt))), но это налог на каждую операцию чтения. Забудешь один раз — и показываешь «удалённые» данные пользователям.

Проблема уникальных ограничений#

Вот где мягкое удаление становится неприятным. Если у тебя UNIQUE (email) и пользователь мягко удалил свой аккаунт, его email всё ещё в таблице. Он не может перерегистрироваться с тем же email. Новый пользователь с этим email тоже не может зарегистрироваться.

Решение — частичный уникальный индекс:

sql
-- Email must be unique, but only among non-deleted users
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Это работает, но теперь нужно помнить этот паттерн для каждой уникальной колонки в каждой таблице с мягким удалением. Это управляемо с дисциплиной, но это сложность, которую ты сам выбрал.

Проблема внешних ключей#

Мягкое удаление плохо взаимодействует с внешними ключами. Если orders.user_id ссылается на users.id с ON DELETE RESTRICT, и ты мягко удаляешь пользователя... ничего не происходит. FK не срабатывает, потому что ты на самом деле не удалил строку. Пользователь «ушёл» с точки зрения приложения, но всё ещё очень даже присутствует в базе данных.

Это значит, что код приложения должен обрабатывать случай, когда связанная сущность мягко удалена. Каждый join, каждый поиск, каждый раз, когда следуешь по внешнему ключу — нужно проверять deleted_at IS NULL и в связанной таблице тоже. Или не проверяешь, и приложение показывает «Заказ от [удалённый пользователь]», что является либо багом, либо фичей — зависит от того, кого спросить.

Мой подход#

Я использую мягкое удаление только для пользовательских сущностей, где восстановление — бизнес-требование — аккаунты пользователей, проекты, документы. Вещи, где специалисту поддержки может понадобиться восстановить удалённое. Для всего остального я использую жёсткое удаление с аудит-трейлом (подробнее дальше).

sql
-- Soft delete: user-facing, recoverable
CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_documents_active ON documents (owner_id) WHERE deleted_at IS NULL;
 
-- Hard delete: internal, not recoverable from UI (but audited)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash    TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- When deleted, it's gone. The audit_log records that it existed.

Аудит-трейлы: знай, что изменилось и кто это сделал#

Каждому нетривиальному приложению рано или поздно нужен ответ на вопрос «что произошло с этой записью?». Аудит-трейлы — это способ дать этот ответ, не копаясь в логах приложения.

Паттерн отдельной таблицы аудита#

Самый простой подход: единая таблица audit_log, которая записывает каждое изменение.

sql
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text to handle both UUID and BIGINT PKs
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL for INSERT
    new_values      JSONB,                  -- NULL for DELETE
    changed_fields  TEXT[],                 -- which columns changed (for UPDATE)
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      INET,
    user_agent      TEXT
);
 
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_by ON audit_log (changed_by);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Проблема — наполнение. Можно делать это в коде приложения (явно, но легко забыть) или с триггерами (автоматически, но сложнее передать контекст типа changed_by).

Подход на основе триггеров#

Триггеры перехватывают каждое изменение автоматически, даже из сырого SQL или операций администратора БД:

sql
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    changed  TEXT[];
    col      TEXT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_at)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', old_data, NOW());
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
        -- Find changed fields
        FOR col IN SELECT key FROM jsonb_each(new_data)
        LOOP
            IF old_data->col IS DISTINCT FROM new_data->col THEN
                changed := array_append(changed, col);
            END IF;
        END LOOP;
        -- Only log if something actually changed
        IF array_length(changed, 1) > 0 THEN
            INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, changed_at)
            VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE', old_data, new_data, changed, NOW());
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_at)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', new_data, NOW());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Apply to tables you want audited
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
 
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Недостаток: триггеры не знают, какой пользователь приложения внёс изменение. Можно обойти это с помощью сессионных переменных:

sql
-- In your application, before the query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- In the trigger function:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Это работает, но ощущается хрупко. На практике я использую гибридный подход: триггеры для захвата данных и код приложения для установки сессионного контекста.

Паттерн таблицы истории#

Для таблиц, где нужна полная история версий (не только «что изменилось», а «каково было состояние в момент T»), отдельная таблица истории будет чище:

sql
CREATE TABLE documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL DEFAULT 1,
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE document_history (
    id              BIGSERIAL PRIMARY KEY,
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL,
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, version)
);

Перед каждым обновлением documents копируй текущее состояние в document_history и увеличивай версию. Теперь можно восстановить документ на любой момент времени, показать диффы между версиями и даже откатиться к старым версиям.

Компромисс — хранилище. Если колонка content большая и часто меняется, таблица истории может расти быстро. Для большинства приложений это нормально — хранилище дешёвое, и можно архивировать старые версии в холодное хранилище при необходимости.

Мультитенантность: три подхода, выбери свою боль#

Мультитенантность — одна из тех вещей, которые легко добавить в начале и почти невозможно добавить позже. Если есть хоть малейший шанс, что приложение будет обслуживать несколько организаций — закладывай это с первого дня.

Строковый уровень: tenant_id в каждой таблице#

Самый распространённый подход. Каждая таблица имеет колонку tenant_id, и каждый запрос фильтрует по ней.

sql
CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Every index should include tenant_id for query performance
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

Риск — утечка данных. Один пропущенный WHERE tenant_id = ... — и ты показываешь данные Тенанта A Тенанту B. Row-Level Security (RLS) в PostgreSQL устраняет этот класс багов:

sql
-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Create a policy based on a session variable
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Force RLS even for table owners
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Теперь, даже если код приложения забудет WHERE tenant_id = ..., PostgreSQL добавит это автоматически. Это защита в глубину, и один из сильнейших аргументов в пользу PostgreSQL в мультитенантных системах.

sql
-- In your application's connection middleware:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Now all queries on RLS-enabled tables are automatically filtered
SELECT * FROM projects;
-- PostgreSQL internally adds: WHERE tenant_id = 'tenant-uuid-here'

Плюсы: Одна база данных, простое администрирование, эффективное использование ресурсов, лёгкие кросс-тенантные запросы для админки. Минусы: Требует дисциплины (или RLS), каждый запрос затрагивает tenant_id, сложнее предоставить тенантам собственный бэкап/восстановление.

Схема на тенанта#

Каждый тенант получает свою собственную PostgreSQL-схему. Все схемы разделяют одну базу данных, но таблицы изолированы пространством имён.

sql
-- Create a schema for each tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tables live in the tenant's schema
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Set the search_path for the current tenant
SET search_path TO tenant_acme, public;
 
-- Now unqualified queries hit the tenant's schema
SELECT * FROM projects;  -- queries tenant_acme.projects

Плюсы: Сильная изоляция, нет риска утечки данных между тенантами, лёгкий бэкап/восстановление на уровне тенанта, тенанты могут иметь вариации схемы. Минусы: Сложность миграции схемы (нужно мигрировать N схем), управление пулом соединений, у PostgreSQL есть практические ограничения около ~10 000 схем.

База данных на тенанта#

Каждый тенант получает собственную базу данных. Максимальная изоляция.

Плюсы: Полная изоляция, независимое масштабирование, лёгкий бэкап/восстановление, можно разместить крупных тенантов на выделенном оборудовании. Минусы: Кошмар управления соединениями, кросс-тенантные запросы невозможны, миграция должна выполняться N раз, значительные операционные накладные расходы.

Что выбрать?#

Для большинства SaaS-приложений: начни со строкового уровня + RLS. Это самое простое в эксплуатации, и RLS даёт достаточно сильную изоляцию для подавляющего большинства случаев. Переходи на схему-на-тенанта, только если есть контрактные требования к изоляции (корпоративные клиенты, регулируемые отрасли). База-на-тенанта — для случаев, когда абсолютно необходимо гарантировать физическую изоляцию — и даже тогда рассмотри управляемые базы данных, где операционная нагрузка берётся за тебя.

JSON/JSONB-колонки: запасной выход#

JSONB в PostgreSQL великолепен. Он даёт тебе гибкость документоориентированной базы данных внутри реляционной системы. Но как и любой мощный инструмент, его легко использовать во вред.

Когда JSONB — правильный выбор#

Динамические пользовательские атрибуты. E-commerce платформа, где у каждой категории товаров разные атрибуты — у обуви есть size и color, у электроники — voltage и wattage. Вместо EAV-таблицы или колонки для каждого возможного атрибута:

sql
CREATE TABLE products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES categories(id),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Example data:
-- Shoe: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Настройки приложения и конфигурация. Пользовательские предпочтения, фиче-флаги, настройки уведомлений — вещи, которые часто меняют форму и не нуждаются в реляционной целостности.

sql
CREATE TABLE user_settings (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{
        "theme": "system",
        "notifications": {"email": true, "push": false},
        "locale": "en",
        "timezone": "UTC"
    }',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Пейлоады вебхуков, ответы API, данные событий. Всё, где схема контролируется внешней системой и может измениться без предупреждения.

GIN-индексы на JSONB#

Без индексов запросы внутри JSONB требуют полного сканирования таблицы. GIN-индексы делают их быстрыми:

sql
-- Index the entire JSONB column (good for @> containment queries)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: find all products with color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: find products with any of these attributes
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Для конкретных путей, по которым часто делаешь запросы, таргетированный индекс эффективнее:

sql
-- Index a specific path
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Now this is a regular B-tree lookup
SELECT * FROM products
WHERE attributes->>'color' = 'black';

Ловушка миграции#

Вот где JSONB кусается: нельзя просто добавить NOT NULL ограничения или значения по умолчанию для полей внутри JSON. Если добавляешь новое обязательное поле в атрибуты товара, нужно бэкфилить каждую существующую строку. С обычной колонкой миграция делает это атомарно. С JSONB ты пишешь UPDATE, который затрагивает каждую строку, и надеешься, что код приложения корректно обрабатывает отсутствующие поля, пока бэкфил не завершится.

sql
-- Adding a new column: clean, atomic, one statement
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Adding a new JSONB field: messy, requires a backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Plus update application code to handle missing weight_kg
-- Plus update validation logic
-- Plus update every API response that includes attributes

Моё правило: если ты запрашиваешь JSONB-поле в WHERE-клаузе чаще двух раз в неделю, ему, вероятно, стоит быть колонкой. JSONB — отличный запасной выход. Но ужасный вариант по умолчанию.

Миграции схемы: не ломай продакшен в 3 часа ночи#

Миграции схемы — это место, где теория встречается с реальностью. Схема выглядит отлично на бумаге, но теперь нужно изменить таблицу с 50 миллионами строк в рабочее время без какого-либо даунтайма.

Инструменты миграции#

Я перепробовал большинство из них. Краткие заметки:

Drizzle (TypeScript): Мой текущий фаворит. Схема как код, типобезопасные запросы, сгенерированные из схемы, чистый SQL миграций. Команда push для разработки работает быстро.

Prisma (TypeScript): Отличный DX для простых схем. Плохо справляется с продвинутыми фичами PostgreSQL (частичные индексы, кастомные типы, RLS). Движок миграций может принимать неожиданные решения.

Flyway (Java/CLI): Надёжный, проверенный временем, SQL-first. Если пишешь сырые SQL-миграции, Flyway отслеживает их надёжно. Никакой магии, никаких сюрпризов.

golang-migrate (Go/CLI): Похож на Flyway, но легче. Отлично подходит для Go-проектов или когда нужен простой запускатель up/down миграций.

Проблема нулевого даунтайма#

Самые опасные изменения схемы — те, которые блокируют таблицу. В PostgreSQL ALTER TABLE ... ADD COLUMN со значением по умолчанию раньше блокировал всю таблицу на время перезаписи. Начиная с PostgreSQL 11, простые значения по умолчанию (константы) — это только метаданные и мгновенные. Но другие операции всё ещё блокируют:

sql
-- SAFE: metadata-only, instant (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- DANGEROUS: rewrites the entire table, full table lock
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- DANGEROUS: scans the entire table to validate
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Безопасное добавление NOT NULL колонок#

Нельзя просто добавить NOT NULL к существующей таблице с данными — операция провалится, потому что у существующих строк нет значения. Наивный подход:

sql
-- This locks the table and rewrites it. Don't do this on a big table.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Начиная с PostgreSQL 11, это на самом деле безопасно для константных значений по умолчанию — это только метаданные. Но если значение по умолчанию — функция или нужно бэкфилить вычисленными значениями, используй паттерн expand-contract.

Паттерн Expand-Contract#

Это золотой стандарт для изменений схемы без даунтайма. Три фазы:

Фаза 1: Expand — Добавь новую колонку как nullable. Задеплой код приложения, который пишет и в старую, и в новую колонку.

sql
-- Migration 1: Add the nullable column
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Фаза 2: Migrate — Бэкфиль существующих строк батчами. Приложение уже пишет в новую колонку для новых данных.

sql
-- Migration 2: Backfill in batches (don't do this in one statement for large tables)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Repeat until all rows are backfilled

Фаза 3: Contract — Когда все строки бэкфильнуты, добавь NOT NULL ограничение и удали старую колонку (если применимо).

sql
-- Migration 3: Add constraint (use NOT VALID to avoid full table scan, then validate separately)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migration 4: Validate the constraint (takes a ShareUpdateExclusiveLock, not an AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

Трюк с NOT VALID + VALIDATE CONSTRAINT критически важен. Добавление check-ограничения обычно сканирует всю таблицу, держа тяжёлую блокировку. NOT VALID добавляет ограничение без сканирования (оно применяется только к новым записям), а VALIDATE CONSTRAINT сканирует с более лёгкой блокировкой, которая не блокирует чтения и записи.

Создание индексов#

Создание индексов на больших таблицах по умолчанию блокирует запись. Всегда используй CONCURRENTLY:

sql
-- BLOCKS WRITES: don't do this on a live table
CREATE INDEX idx_users_email ON users (email);
 
-- NON-BLOCKING: use this instead
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY занимает больше времени и не может выполняться внутри транзакции, но не блокирует таблицу. Этот компромисс всегда оправдан в продакшене.

Решения, о которых я пожалел#

У каждой схемы есть свои сожаления. Вот мои.

Использование SERIAL вместо UUID для внешних ID#

На раннем этапе проекта я использовал SERIAL первичные ключи и выставлял их прямо в URL: /users/42, /orders/1337. Это утечка информации (конкуренты могли угадать количество наших пользователей), пагинация становилась предсказуемой, и всё сломалось, когда потребовалось объединить базы данных из двух регионов. Переход на UUID для внешних ID потребовал многомесячной миграции.

Урок: используй UUID для всего, что выставлено за пределы базы данных. Используй SERIAL/BIGSERIAL для внутренних таблиц связей, если хочешь, но никогда не допускай, чтобы автоинкрементное число появлялось в URL.

Без ограничений («Мы добавим потом»)#

Мы запустили таблицу без check-ограничений, потому что «мы двигаемся быстро и добавим потом». За две недели данные обросли отрицательными ценами, пустыми именами и значением order_status равным "oof", которое кто-то ввёл во время тестирования и никогда не вычистил.

Добавление ограничений задним числом потребовало:

  1. Найти все невалидные данные
  2. Решить, что с ними делать (исправить, удалить или оставить как есть)
  3. Написать миграцию, которая исправляет данные И добавляет ограничение

Это заняло больше времени, чем добавление ограничения в первый день. Начинай с ограничений. Ослабляй их, если необходимо. Никогда наоборот.

Строки через запятую вместо массивов или таблиц связей#

Однажды я хранил теги строкой через запятую: "javascript,react,nextjs". Запросы превратились в кошмар:

sql
-- This is how you query comma-separated values. Don't do this.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- This also matches "react-native" and "preact"
 
-- What I should have done:
CREATE TABLE post_tags (
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Или как минимум использовать нативный тип массивов PostgreSQL:

sql
CREATE TABLE posts (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL DEFAULT '{}',
    -- ...
);
 
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
 
-- Clean queries
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Массивы допустимы для простых списков, которым не нужны собственные атрибуты. Как только понадобятся метаданные связи (вроде «кто добавил этот тег» или «когда он был добавлен»), нужна таблица связей.

Использование «type» как имени колонки#

sql
-- Seemed fine at the time
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Then you try to query it in literally any ORM:
-- notification.type  ← conflicts with every language's type system
-- "type" is a reserved word in most SQL dialects
-- You end up quoting it everywhere: SELECT "type" FROM notifications

Используй kind, category или notification_type. Что угодно, только не type. Аналогично, избегай order (используй sort_order или position), user (используй account или добавляй префикс) и group (используй team или group_name).

Отсутствие created_at с самого начала#

Одна таблица не получила created_at, потому что «нам это не нужно». Через три месяца потребовалось отладить проблему, и не было никакой информации о том, когда были созданы записи. Добавление задним числом означало, что все существующие строки получили одну и ту же временную метку (время миграции), что сделало исторические данные бесполезными.

Каждая таблица получает created_at. Без исключений. Стоимость — одна колонка. Стоимость отсутствия — непознаваема до момента, когда оно понадобится.

Размещение бизнес-логики в представлениях БД#

Однажды я создал цепочку представлений — active_users фильтровала users, premium_active_users фильтровала active_users, а отчётное представление джойнило их все. Работало отлично, пока кто-то не изменил таблицу users и все три представления тихо сломались. Планировщик запросов тоже с трудом оптимизировал через несколько слоёв представлений.

Представления отлично подходят для удобства и паттернов только для чтения. Они ужасны как место для кодирования бизнес-логики, которая меняется. Держи бизнес-правила в коде приложения, где они версионируются, тестируются и деплоятся. Используй представления для удобных шорткатов в отчётности, а не как архитектурные строительные блоки.

Собираем всё вместе#

Вот как выглядит хорошо спроектированная схема, когда комбинируешь эти паттерны. Упрощённая система управления проектами:

sql
-- Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- for gen_random_uuid()
 
-- Updated_at trigger function (reusable)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenants
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    slug            TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    plan            TEXT NOT NULL DEFAULT 'free'
                    CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Users
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL CHECK (LENGTH(display_name) > 0),
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    last_login_at   TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE UNIQUE INDEX uq_users_email_per_tenant
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_tenant ON users (tenant_id) WHERE deleted_at IS NULL;
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projects
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'archived', 'deleted')),
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_projects_owner ON projects (owner_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Tasks
CREATE TABLE tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title           TEXT NOT NULL CHECK (LENGTH(title) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'todo'
                    CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority        INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4),
    assignee_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_status ON tasks (tenant_id, status) WHERE status != 'done';
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Task comments
CREATE TABLE task_comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL CHECK (LENGTH(body) > 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_task_comments_task ON task_comments (task_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON task_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON task_comments
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Audit log (no RLS — admin-only table)
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_log_tenant_table ON audit_log (tenant_id, table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Обрати внимание на паттерны:

  • Каждая таблица имеет tenant_id и включённый RLS (кроме tenants и audit_log).
  • Каждая таблица имеет created_at и updated_at с триггерами.
  • UUID для всех первичных ключей (внешне доступные сущности).
  • Check-ограничения на перечисления статусов, длины и диапазоны.
  • Внешние ключи с соответствующим поведением ON DELETE.
  • Индексы, спроектированные для паттернов доступа (tenant + project, tenant + status).
  • Мягкое удаление только для users (где нужно восстановление аккаунта), архивация по статусу для projects.

Заключение#

Проектирование схемы — не гламурное занятие. Никто никогда не выступал на конференции с докладом «Я добавил check-ограничения в каждую таблицу, и это сэкономило нам шесть месяцев отладки». Но именно это и делает хорошее проектирование схемы — оно предотвращает проблемы настолько тихо, что ты даже не узнаешь, что они могли бы существовать.

Паттерны из этой статьи не новы. Это результат лет написания миграций, дебага повреждённых данных и рефакторинга схем под продакшен-нагрузкой. Каждый из них существует, потому что я или кто-то, с кем я работал, сначала сделал по-другому и заплатил за это.

Начинай с ограничений. Используй внешние ключи. Добавляй created_at ко всему. Выбери соглашение об именовании и применяй его неукоснительно. Используй RLS для мультитенантности. Будь осторожен с JSONB. Тестируй миграции на данных продакшенного размера перед деплоем.

База данных — это фундамент. Сделай его правильно, и всё, что строится поверх, станет проще. Сделай его неправильно, и никакой объём хитрого кода приложения тебя не спасёт.

Похожие записи

Лучшие практики безопасности API: чек-лист, который я проверяю на каждом проекте

Аутентификация, авторизация, валидация входных данных, ограничение частоты запросов, CORS, управление секретами и OWASP API Top 10. Что я проверяю перед каждым продакшн-деплоем.

securityapibackendnode.jsweb-development
Читать далее