ADR-004: Паттерны базы данных

Дата: 2026-01-04
Статус: Принято
Авторы: Backend Team

---

Контекст

Проект использует PostgreSQL 16.x с 21 таблицей согласно autoparts-contracts-v5.md. Необходимо определить единые паттерны для:

• Первичных ключей
• Timestamps и аудита
• Soft delete
• Денормализации и триггеров
• Обработки связей M:N
• Индексирования

---

Решения

1. Первичные ключи: UUID v7

Решение: Используем UUID v7 для всех первичных ключей.

Обоснование:

• Сортируемость по времени (в отличие от UUID v4)
• Отсутствие коллизий при горизонтальном масштабировании
• Безопасность: ID не раскрывает порядок создания
• Совместимость с Medusa.js

-- Генерация UUID v7 (PostgreSQL 17+ или расширение)
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  ...
);

Альтернатива для PostgreSQL 16:

-- Использование gen_random_uuid() с ручной сортировкой по created_at
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ...
);

CREATE INDEX idx_products_created ON products(created_at DESC);

---

2. Timestamps: created_at + updated_at

Решение: Все таблицы содержат created_at и updated_at.

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- ... поля таблицы
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Автоматическое обновление updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Применение триггера ко всем таблицам:

DO $$
DECLARE
  t text;
BEGIN
  FOR t IN 
    SELECT table_name FROM information_schema.tables 
    WHERE table_schema = 'public' 
    AND table_type = 'BASE TABLE'
    AND table_name NOT LIKE 'pg_%'
  LOOP
    EXECUTE format('
      CREATE TRIGGER trigger_%I_updated_at
      BEFORE UPDATE ON %I
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at()
    ', t, t);
  END LOOP;
END $$;

---

3. Soft Delete

Решение: Soft delete через deleted_at для ключевых сущностей.

Таблицы с soft delete:

• users — сохранение истории, возможность восстановления
• products — сохранение связей в отзывах/сообщениях
• conversations — архивация переписок

Таблицы БЕЗ soft delete (hard delete):

• verification_codes — временные данные
• user_sessions — инвалидированные сессии удаляются
• product_images — файлы удаляются физически
• messages — удаляются вместе с conversation

-- Пример для products
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE;

CREATE INDEX idx_products_active ON products(id) WHERE deleted_at IS NULL;

-- Обязательное условие во всех запросах
SELECT * FROM products WHERE deleted_at IS NULL;

Глобальный scope в MikroORM:

// src/modules/products/entities/product.entity.ts
@Entity()
@Filter({ name: 'notDeleted', cond: { deleted_at: null }, default: true })
export class Product {
  @Property({ nullable: true })
  deleted_at?: Date;
  
  // Метод для soft delete
  softDelete() {
    this.deleted_at = new Date();
  }
}

---

4. Денормализация и триггеры

Согласно autoparts-contracts-v5.md, используем денормализованные счётчики для производительности.

Схема денормализации:

Таблица	Поле	Обновляется при	Триггер
users	rating	INSERT/UPDATE/DELETE reviews	AVG(rating)
users	reviews_count	INSERT/DELETE reviews	COUNT(*)
users	products_count	INSERT/DELETE products	COUNT(*)
products	views_count	INSERT product_views	+1 (rate-limit)
products	favorites_count	INSERT/DELETE favorites	COUNT(*)
categories	products_count	INSERT/DELETE product_categories	COUNT(*)
conversations	buyer_unread_count	INSERT/UPDATE messages	COUNT(*)
conversations	seller_unread_count	INSERT/UPDATE messages	COUNT(*)
conversations	last_message_at	INSERT messages	MAX(created_at)

Пример триггера для рейтинга:

CREATE OR REPLACE FUNCTION update_seller_rating()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    UPDATE users SET 
      rating = COALESCE((SELECT AVG(rating)::DECIMAL(3,2) FROM reviews WHERE seller_id = OLD.seller_id), 0),
      reviews_count = (SELECT COUNT(*) FROM reviews WHERE seller_id = OLD.seller_id)
    WHERE id = OLD.seller_id;
    RETURN OLD;
  ELSE
    UPDATE users SET 
      rating = COALESCE((SELECT AVG(rating)::DECIMAL(3,2) FROM reviews WHERE seller_id = NEW.seller_id), 0),
      reviews_count = (SELECT COUNT(*) FROM reviews WHERE seller_id = NEW.seller_id)
    WHERE id = NEW.seller_id;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_seller_rating
  AFTER INSERT OR UPDATE OR DELETE ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION update_seller_rating();

Триггер для просмотров с rate-limit:

CREATE OR REPLACE FUNCTION increment_product_views()
RETURNS TRIGGER AS $$
BEGIN
  -- Проверка: не более 1 просмотра с одного IP в час
  IF NOT EXISTS (
    SELECT 1 FROM product_views 
    WHERE product_id = NEW.product_id 
    AND ip_address = NEW.ip_address 
    AND created_at > NOW() - INTERVAL '1 hour'
  ) THEN
    UPDATE products 
    SET views_count = views_count + 1 
    WHERE id = NEW.product_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_increment_views
  AFTER INSERT ON product_views
  FOR EACH ROW
  EXECUTE FUNCTION increment_product_views();

---

5. Связи M:N с метаданными

Согласно autoparts-contracts-v5.md, товар связан с категориями через M:N с дополнительными полями.

Паттерн: Junction table с метаданными

CREATE TABLE product_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id UUID NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  is_primary BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  UNIQUE(product_id, category_id)
);

-- Индексы для обоих направлений связи
CREATE INDEX idx_product_categories_product ON product_categories(product_id);
CREATE INDEX idx_product_categories_category ON product_categories(category_id);

-- Constraint: ровно одна primary категория типа 'part'
CREATE OR REPLACE FUNCTION check_primary_category()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.is_primary = true THEN
    -- Сбросить предыдущую primary
    UPDATE product_categories 
    SET is_primary = false 
    WHERE product_id = NEW.product_id 
    AND id != NEW.id 
    AND is_primary = true;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_primary_category
  BEFORE INSERT OR UPDATE ON product_categories
  FOR EACH ROW
  WHEN (NEW.is_primary = true)
  EXECUTE FUNCTION check_primary_category();

---

6. Типы категорий (Enum vs Lookup table)

Решение: PostgreSQL ENUM для статичных типов.

CREATE TYPE category_type AS ENUM ('part', 'condition', 'attribute');
CREATE TYPE product_status AS ENUM ('draft', 'pending', 'active', 'sold', 'archived');
CREATE TYPE account_type AS ENUM ('personal', 'business');
CREATE TYPE steering_type AS ENUM ('left', 'right', 'universal');

CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  category_type category_type NOT NULL,
  parent_id UUID REFERENCES categories(id),
  products_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Когда использовать lookup table вместо ENUM:

• Значения часто меняются
• Нужны метаданные (описание, иконка)
• Управление через админку

---

7. Иерархические данные (категории, регионы)

Решение: Adjacency List + Materialized Path для категорий.

CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL,
  category_type category_type NOT NULL,
  parent_id UUID REFERENCES categories(id),
  path LTREE,  -- Требует расширение ltree
  depth INTEGER DEFAULT 0,
  ...
);

CREATE EXTENSION IF NOT EXISTS ltree;

-- Индекс для поиска по дереву
CREATE INDEX idx_categories_path ON categories USING GIST(path);

-- Пример path: 'root.body.doors.front_left'
-- Найти всех потомков категории:
SELECT * FROM categories WHERE path <@ 'root.body';

-- Найти всех предков:
SELECT * FROM categories WHERE path @> 'root.body.doors.front_left';

Триггер для обновления path:

CREATE OR REPLACE FUNCTION update_category_path()
RETURNS TRIGGER AS $$
DECLARE
  parent_path LTREE;
BEGIN
  IF NEW.parent_id IS NULL THEN
    NEW.path = text2ltree(NEW.slug);
    NEW.depth = 0;
  ELSE
    SELECT path INTO parent_path FROM categories WHERE id = NEW.parent_id;
    NEW.path = parent_path || text2ltree(NEW.slug);
    NEW.depth = nlevel(NEW.path) - 1;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_category_path
  BEFORE INSERT OR UPDATE OF parent_id, slug ON categories
  FOR EACH ROW
  EXECUTE FUNCTION update_category_path();

---

8. Индексирование

Стратегия индексирования:

-- 1. Внешние ключи (автоматически для FK, но проверить)
CREATE INDEX idx_products_seller ON products(seller_id);
CREATE INDEX idx_products_city ON products(city_id);

-- 2. Фильтры поиска
CREATE INDEX idx_products_status ON products(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_price ON products(price) WHERE status = 'active';
CREATE INDEX idx_products_steering ON products(steering) WHERE status = 'active';
CREATE INDEX idx_products_created ON products(created_at DESC) WHERE status = 'active';

-- 3. Совместимость (часто используемый запрос)
CREATE INDEX idx_compatibility_make_model ON product_compatibility(make_id, model_id);
CREATE INDEX idx_compatibility_generation ON product_compatibility(generation_id);

-- 4. Полнотекстовый поиск
CREATE INDEX idx_products_title_fts ON products 
  USING GIN(to_tsvector('russian', title));

-- 5. OEM поиск (нормализованный)
CREATE INDEX idx_oem_numbers_normalized ON oem_numbers(
  UPPER(REPLACE(REPLACE(oem, '-', ''), ' ', ''))
);

-- 6. Геопоиск (PostGIS)
CREATE INDEX idx_cities_location ON cities USING GIST(location);

-- 7. Составные индексы для частых запросов
CREATE INDEX idx_products_category_status_created ON product_categories(category_id)
  INCLUDE (product_id)
  WHERE EXISTS (
    SELECT 1 FROM products p 
    WHERE p.id = product_id AND p.status = 'active' AND p.deleted_at IS NULL
  );

---

9. Constraints и валидация на уровне БД

-- Проверка цены
ALTER TABLE products ADD CONSTRAINT chk_products_price 
  CHECK (price >= 0);

-- Проверка рейтинга
ALTER TABLE reviews ADD CONSTRAINT chk_reviews_rating 
  CHECK (rating >= 1 AND rating <= 5);

-- Проверка годов
ALTER TABLE car_generations ADD CONSTRAINT chk_generations_years 
  CHECK (year_from <= COALESCE(year_to, year_from));

-- Уникальность email (case-insensitive)
CREATE UNIQUE INDEX idx_users_email_lower ON users(LOWER(email));

-- Уникальность телефона
CREATE UNIQUE INDEX idx_users_phone ON users(phone) WHERE phone IS NOT NULL;

-- Business profile только для business аккаунтов
-- (реализуется через триггер или application logic)

---

10. Партиционирование (для масштаба)

Решение: Отложено до >500K товаров.

Когда применять:

• products > 500K записей
• product_views > 10M записей
• search_logs > 10M записей

Пример для product_views:

CREATE TABLE product_views (
  id UUID DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL,
  user_id UUID,
  ip_address INET,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Партиции по месяцам
CREATE TABLE product_views_2026_01 PARTITION OF product_views
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
  
CREATE TABLE product_views_2026_02 PARTITION OF product_views
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Автоматизация через pg_partman (расширение)

---

Последствия

Положительные

• Консистентность: единые паттерны во всей БД
• Производительность: денормализация для частых запросов
• Целостность: constraints на уровне БД
• Масштабируемость: подготовка к партиционированию

Отрицательные

• Сложность триггеров: отладка при проблемах
• Денормализация: риск рассинхронизации
• Миграции: изменение ENUM требует миграции

Митигация

Риск	Решение
Рассинхрон счётчиков	CRON-задача для пересчёта раз в сутки
Медленные триггеры	Асинхронное обновление через очередь
Блокировки	Оптимистичные блокировки в ORM

---

Связанные решения

• ADR-001: ORM
• ADR-005: Кэширование
• ADR-007: Background Jobs