play-life/play-life-backend/migrations

Database Migrations

Этот каталог содержит SQL миграции для создания структуры базы данных проекта play-life.

Использование

Создание базы данных с нуля

Выполните миграцию для создания всех таблиц и представлений:

psql -U your_user -d your_database -f 001_create_schema.sql

Или через docker-compose:

docker-compose exec db psql -U playeng -d playeng -f /migrations/001_create_schema.sql

Структура базы данных

Таблицы

1. projects - Проекты

   • id (SERIAL PRIMARY KEY)
   • name (VARCHAR(255) NOT NULL, UNIQUE)
   • priority (SMALLINT)

2. entries - Записи с текстом и датами создания

   • id (SERIAL PRIMARY KEY)
   • text (TEXT NOT NULL)
   • created_date (TIMESTAMP WITH TIME ZONE NOT NULL, DEFAULT CURRENT_TIMESTAMP)

3. nodes - Узлы, связывающие проекты и записи

   • id (SERIAL PRIMARY KEY)
   • project_id (INTEGER NOT NULL, FK -> projects.id ON DELETE CASCADE)
   • entry_id (INTEGER NOT NULL, FK -> entries.id ON DELETE CASCADE)
   • score (NUMERIC(8,4))

4. weekly_goals - Недельные цели для проектов

   • id (SERIAL PRIMARY KEY)
   • project_id (INTEGER NOT NULL, FK -> projects.id ON DELETE CASCADE)
   • goal_year (INTEGER NOT NULL)
   • goal_week (INTEGER NOT NULL)
   • min_goal_score (NUMERIC(10,4) NOT NULL, DEFAULT 0)
   • max_goal_score (NUMERIC(10,4))
   • priority (SMALLINT)
   • UNIQUE CONSTRAINT: (project_id, goal_year, goal_week)

Materialized View

• weekly_report_mv - Агрегированные данные по неделям для каждого проекта
  • project_id (INTEGER)
  • report_year (INTEGER)
  • report_week (INTEGER)
  • total_score (NUMERIC)
  • normalized_total_score (NUMERIC) — ограничение total_score по max_goal_score (миграция 000020 удалила колонку max_score, normalized считается по max_goal_score)

Миграции

Порядок применения миграций

1. 001_create_schema.sql - Создание базовой структуры (таблицы, индексы, materialized view)
2. 002_add_dictionaries.sql - Добавление таблиц для словарей
3. 003_remove_words_unique_constraint.sql - Удаление уникального ограничения на words.name
4. 004_add_config_dictionaries.sql - Добавление связи между конфигурациями и словарями
5. 005_fix_weekly_report_mv.sql - Исправление использования ISOYEAR вместо YEAR для корректной работы на границе года
6. 006_fix_weekly_report_mv_structure.sql - Исправление структуры view (добавление LEFT JOIN для включения всех проектов)
7. 026_weekly_goals_max_score.sql - Добавление snapshot поля weekly_goals.max_score и удаление неиспользуемого actual_score
8. 027_add_normalized_total_score_to_weekly_report_mv.sql - Добавление normalized_total_score в weekly_report_mv (ограничение total_score по max_score)

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

Для существующей базы данных применяйте миграции последовательно:

psql -U playeng -d playeng -f migrations/005_fix_weekly_report_mv.sql
psql -U playeng -d playeng -f migrations/006_fix_weekly_report_mv_structure.sql
psql -U playeng -d playeng -f migrations/026_weekly_goals_max_score.sql
psql -U playeng -d playeng -f migrations/027_add_normalized_total_score_to_weekly_report_mv.sql

Или через docker-compose:

docker-compose exec db psql -U playeng -d playeng -f /migrations/005_fix_weekly_report_mv.sql
docker-compose exec db psql -U playeng -d playeng -f /migrations/006_fix_weekly_report_mv_structure.sql
docker-compose exec db psql -U playeng -d playeng -f /migrations/026_weekly_goals_max_score.sql
docker-compose exec db psql -U playeng -d playeng -f /migrations/027_add_normalized_total_score_to_weekly_report_mv.sql

Обновление Materialized View

После изменения данных в таблицах nodes или entries, необходимо обновить materialized view:

REFRESH MATERIALIZED VIEW weekly_report_mv;

Связи между таблицами

• nodes.project_id → projects.id (ON DELETE CASCADE)
• nodes.entry_id → entries.id (ON DELETE CASCADE)
• weekly_goals.project_id → projects.id (ON DELETE CASCADE)

Индексы

Созданы индексы для оптимизации запросов:

• idx_nodes_project_id на nodes(project_id)
• idx_nodes_entry_id на nodes(entry_id)
• idx_weekly_goals_project_id на weekly_goals(project_id)
• idx_weekly_report_mv_project_year_week на weekly_report_mv(project_id, report_year, report_week)