Database Migrations

Система миграций PostgreSQL. 400+ файлов, нумерация 001-400+. Любое изменение схемы = новая миграция. Idempotent по умолчанию.

205+

Миграций

Таблиц

Views

Schemas

Связи и зависимости

Предыдущие шаги: Runtime Policy

Следующие шаги: Quick Start, Docker Services

Необходимые навыки: SQL DDL, PostgreSQL, Bash

Философия миграций

Idempotent

Каждая миграция может запускаться многократно без ошибок. IF NOT EXISTS / IF EXISTS везде.

Ordered

Алфавитный порядок файлов определяет порядок применения. Нумерация критична.

Immutable

Применённые миграции не изменяются. Новые изменения = новая миграция.

Структура папки db/init/

db/init/
├── 001_extensions.sql        -- pgvector, uuid-ossp, pgcrypto
├── 002_schemas.sql           -- clowbot, mart schemas
├── 010_core_tables.sql       -- users, chats, messages
├── 020_functions.sql         -- Trigger functions, helpers
├── 030_seed.sql              -- Initial data (templates, processes)
├── 040_sessions_settings.sql -- Session management
├── ...
├── 230_runtime_policy.sql    -- Runtime policy system
├── 274_task_core.sql         -- Task management tables
├── 359_agent_profiles.sql    -- Agent platform tables
├── 388_truth_reconciliation.sql
├── 394_operator_plane_core.sql
├── 400_operator_modes.sql
└── 4XX_*.sql                 -- Latest migrations

Текущее количество: 205+ миграций

Нумерация и назначение диапазонов

Диапазон	Назначение	Примеры
001-009	Extensions, schemas	pgvector, uuid-ossp, clowbot schema
010-099	Core schema, seed data	users, chats, messages, processes
100-199	Features, integrations	telegram, reminders, documents
200-299	Process system, policies	process_runs, runtime_policy
300-399	Agent platform	agent_profile, agent_methods
400+	Operator plane	operator_runs, recurring_tasks

DDL Migrations

Изменения структуры БД: CREATE TABLE, ALTER TABLE, CREATE INDEX.

-- 401_add_notifications.sql
CREATE TABLE IF NOT EXISTS clowbot.notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES clowbot.users(id),
  title text NOT NULL,
  body text NOT NULL,
  read_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_notifications_user
  ON clowbot.notifications (user_id, created_at DESC);

Data Migrations

Изменения данных: INSERT, UPDATE, DELETE seed данных.

-- 402_seed_notification_templates.sql
INSERT INTO clowbot.templates (key, content)
VALUES 
  ('welcome', 'Добро пожаловать, {name}!'),
  ('reminder', 'Напоминание: {task}')
ON CONFLICT (key) DO NOTHING;

-- Обновление существующих данных
UPDATE clowbot.agent_profiles
SET preferred_model = 'phi4-mini'
WHERE role = 'assistant';

Применение миграций

# Docker startup применяет миграции автоматически
docker-compose up -d postgres

# Миграции выполняются в алфавитном порядке
# 001_extensions.sql → 010_schema.sql → 020_functions.sql → ...

# Ручной запуск всех миграций
psql -h localhost -p 25432 -U clowbot -d clowbot -f db/init/*.sql

# Запуск конкретной миграции
psql -h localhost -p 25432 -U clowbot -d clowbot -f db/init/359_agent_profiles.sql

# Проверка что миграция прошла
psql -c "SELECT tablename FROM pg_tables WHERE schemaname = 'clowbot';"

Создание новой миграции — пошагово

Определить номер

# Найти последний номер
ls db/init/*.sql | tail -5
# Output: 398_... 399_... 400_... 401_... 402_...

# Следующий номер: 403

Создать файл

# Формат: NNN_description.sql
touch db/init/403_add_user_preferences.sql

Написать миграцию

-- 403_add_user_preferences.sql
-- Description: Add user preferences table for personalization

CREATE TABLE IF NOT EXISTS clowbot.user_preferences (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES clowbot.users(id) ON DELETE CASCADE,
  preference_key text NOT NULL,
  preference_value jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_user_preference UNIQUE (user_id, preference_key)
);

CREATE INDEX IF NOT EXISTS idx_user_preferences_user
  ON clowbot.user_preferences (user_id);

CREATE TRIGGER trg_user_preferences_updated_at
  BEFORE UPDATE ON clowbot.user_preferences
  FOR EACH ROW EXECUTE FUNCTION clowbot.touch_updated_at();

-- Seed default preferences
INSERT INTO clowbot.user_preferences (user_id, preference_key, preference_value)
SELECT id, 'language', '{"value": "ru"}'::jsonb
FROM clowbot.users
ON CONFLICT (user_id, preference_key) DO NOTHING;

Протестировать

# На пустой БД (docker-compose down -v && docker-compose up -d)
docker-compose down -v
docker-compose up -d postgres

# Проверить что таблица создана
psql -c "\d clowbot.user_preferences"

# Проверить индексы
psql -c "\di clowbot.idx_user_preferences_*"

DO

✓IF NOT EXISTS / IF EXISTS для idempotency
✓ON CONFLICT DO NOTHING для seed данных
✓Явные типы (text, jsonb, uuid, timestamptz)
✓Indexes для часто запрашиваемых полей
✓updated_at trigger для таблиц с изменениями
✓Комментарии к таблицам и колонкам
✓FOREIGN KEY constraints с ON DELETE

DON'T

✗DROP TABLE без IF EXISTS
✗ALTER TABLE без проверки существования
✗Hardcoded IDs в foreign keys
✗Изменение уже применённых миграций
✗Миграции без отката (в production)
✗Блокирующие операции в пик нагрузки
✗INSERT без ON CONFLICT для seed

Типичные ошибки и решения

relation already exists

Таблица/индекс уже существует в БД.

-- Решение: добавить IF NOT EXISTS
CREATE TABLE IF NOT EXISTS clowbot.my_table (...);

duplicate key value violates unique constraint

Попытка вставить дубликат в UNIQUE поле.

-- Решение: добавить ON CONFLICT DO NOTHING
INSERT INTO clowbot.templates (key, content)
VALUES ('welcome', '...')
ON CONFLICT (key) DO NOTHING;

function does not exist

Функция не создана к моменту вызова.

-- Решение: проверить порядок миграций
-- Функция должна быть в файле с меньшим номером
020_functions.sql → CREATE FUNCTION touch_updated_at()
403_add_table.sql → использует touch_updated_at()

cannot drop table because other objects depend on it

Есть foreign keys или зависимости.

-- Решение: сначала DROP зависимые объекты
DROP TABLE IF EXISTS clowbot.child_table;
DROP TABLE IF EXISTS clowbot.parent_table;

-- Или CASCADE (осторожно!)
DROP TABLE IF EXISTS clowbot.parent_table CASCADE;

Продвинутые темы

Add column with default

-- Без блокировки (PostgreSQL 11+)
ALTER TABLE clowbot.users
ADD COLUMN IF NOT EXISTS timezone text
DEFAULT 'Asia/Bangkok';

-- С UPDATE для существующих строк
ALTER TABLE clowbot.users
ADD COLUMN IF NOT EXISTS timezone text;

UPDATE clowbot.users
SET timezone = 'Asia/Bangkok'
WHERE timezone IS NULL;

Create index concurrently

-- Без блокировки writes (production-safe)
CREATE INDEX CONCURRENTLY IF NOT EXISTS
  idx_messages_created_at
ON clowbot.messages (created_at DESC);

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

Safe column drop

-- 1. Сначала убрать использование в коде
-- 2. Потом drop в миграции
ALTER TABLE clowbot.users
DROP COLUMN IF EXISTS old_field;

Rename safely

-- Переименование таблицы
ALTER TABLE IF EXISTS clowbot.old_name
RENAME TO new_name;

-- Переименование колонки
ALTER TABLE clowbot.users
RENAME COLUMN old_col TO new_col;

Rollback стратегии

В идеале каждая миграция должна иметь парный rollback файл. В clowbot мы используем idempotent подход вместо rollback.

Idempotent подход

Миграция безопасно перезапускается. IF NOT EXISTS / IF EXISTS / ON CONFLICT обеспечивают повторяемость.

Forward-only подход

Новая миграция исправляет проблему. Не изменяем историю, создаём новую миграцию с исправлением.

Проверка статуса миграций

# Список всех миграций
ls db/init/*.sql | wc -l
# Output: 205+

# Последние 10 миграций
ls db/init/*.sql | tail -10

# Проверить что таблица существует
psql -c "\dt clowbot.*" | grep agent_profile

# Проверить структуру таблицы
psql -c "\d clowbot.agent_profile"

# Список всех таблиц
psql -c "SELECT tablename FROM pg_tables WHERE schemaname = 'clowbot' ORDER BY tablename;"

# Размер БД
psql -c "SELECT pg_size_pretty(pg_database_size('clowbot'));"

Что делать дальше