Мини‑проект: схема для «Списка задач» + запросы

Мини‑проект: схема для «Списка задач» + все SQL операции

Собираем полноценный проект: БД для списка задач. Это реальный сценарий, который ты встретишь везде (Notion, GitHub, Jira и т.д.). Здесь сходится всё из трека: ограничения, индексы, агрегаты и планы запросов.

Архитектура

Схема БД списка задач: users 1 - N tasks через FOREIGN KEY, soft delete через deleted_at

Таблицы для хранения пользователей, задач и истории изменений:

-- Пользователи
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Задачи
CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  priority INT NOT NULL DEFAULT 3,
  done BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ,
  CONSTRAINT tasks_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT tasks_status_check CHECK (status IN ('new', 'in_progress', 'done', 'archived')),
  CONSTRAINT tasks_priority_check CHECK (priority >= 1 AND priority <= 5)
);

-- Индексы для быстрого поиска
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_deleted_at ON tasks(deleted_at);

INSERT: добавление данных

Вставка пользователя

-- Простой INSERT
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- INSERT RETURNING - сразу получаем созданный ID
INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
RETURNING id, email;
-- Результат: id=2, email='bob@example.com'

-- INSERT несколько строк за раз
INSERT INTO users (email, name) VALUES
  ('charlie@example.com', 'Charlie'),
  ('diana@example.com', 'Diana'),
  ('eve@example.com', 'Eve');

Вставка задач

-- Создаём задачу (статус и priority берут DEFAULT)
INSERT INTO tasks (user_id, title, description)
VALUES (1, 'Выучить SQL', 'Пройти все 12 уроков');

-- С явным статусом и приоритетом
INSERT INTO tasks (user_id, title, description, status, priority)
VALUES (1, 'Написать проект', 'Собрать настоящую БД', 'in_progress', 4);

-- Несколько задач
INSERT INTO tasks (user_id, title, status, priority) VALUES
  (2, 'Купить молоко', 'new', 1),
  (2, 'Написать отчёт', 'in_progress', 5),
  (1, 'Встреча с командой', 'new', 3);

-- Вставка с RETURNING - полезно в приложении
INSERT INTO tasks (user_id, title, status, priority)
VALUES (1, 'Задача номер 42', 'new', 2)
RETURNING id, created_at;

SELECT: чтение данных

Базовый SELECT

-- Все задачи пользователя (с сортировкой)
SELECT id, title, status, priority, created_at
FROM tasks
WHERE user_id = 1
ORDER BY created_at DESC;

-- Активные задачи (не архивированные, не удалённые)
SELECT id, title, status, priority
FROM tasks
WHERE user_id = 1
  AND status != 'archived'
  AND deleted_at IS NULL
ORDER BY priority DESC, created_at ASC;

Группировка и агрегация

-- Сколько задач в каждом статусе (разбирали в уроке про [GROUP BY](./05-group-by.md))
SELECT status, COUNT(*) as task_count
FROM tasks
WHERE user_id = 1 AND deleted_at IS NULL
GROUP BY status
ORDER BY task_count DESC;

-- Результат:
-- status       | task_count
-- in_progress  | 5
-- new          | 3
-- done         | 12

-- Среднее количество задач на пользователя
SELECT u.name, COUNT(t.id) as task_count
FROM users u
LEFT JOIN tasks t ON u.id = t.user_id AND t.deleted_at IS NULL
GROUP BY u.id, u.name
ORDER BY task_count DESC;

-- Задачи с высоким приоритетом (4-5)
SELECT u.name, t.title, t.priority
FROM tasks t
JOIN users u ON t.user_id = u.id
WHERE t.priority >= 4
  AND t.done = false
  AND t.deleted_at IS NULL
ORDER BY t.priority DESC, t.created_at ASC;

Аналитические запросы

-- Недавно созданные задачи (за последние 7 дней)
SELECT id, title, user_id, created_at
FROM tasks
WHERE created_at > NOW() - INTERVAL '7 days'
  AND deleted_at IS NULL
ORDER BY created_at DESC;

-- Неминаемые задачи (выше 7 дней)
SELECT id, title, user_id, created_at,
  (NOW() - created_at) as age
FROM tasks
WHERE status != 'done'
  AND deleted_at IS NULL
  AND created_at < NOW() - INTERVAL '7 days'
ORDER BY created_at ASC;

-- Задачи с максимальным приоритетом для каждого пользователя
SELECT DISTINCT ON (user_id)
  user_id, title, priority, created_at
FROM tasks
WHERE deleted_at IS NULL AND done = false
ORDER BY user_id, priority DESC, created_at DESC;

UPDATE: изменение данных

Простой UPDATE

-- Отметить задачу как выполненную
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE id = 10 AND user_id = 1; - ВАЖНО: проверяем user_id для безопасности

-- Обновляем приоритет
UPDATE tasks
SET priority = 5, updated_at = NOW()
WHERE id = 15 AND user_id = 1;

UPDATE с условиями

-- Переместить старые новые задачи в архив
UPDATE tasks
SET status = 'archived', updated_at = NOW()
WHERE status = 'new'
  AND created_at < NOW() - INTERVAL '30 days'
  AND deleted_at IS NULL;

-- Повысить приоритет всем задачам в процессе
UPDATE tasks
SET priority = priority + 1, updated_at = NOW()
WHERE status = 'in_progress'
  AND user_id = 1;

UPDATE с RETURNING

-- Обновляем и сразу видим результат
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE id = 42 AND user_id = 1
RETURNING id, title, status, updated_at;

DELETE: удаление данных

Hard Delete (физическое удаление)

-- Сразу удаляем задачу (осторожно!)
DELETE FROM tasks
WHERE id = 10 AND user_id = 1;

-- Удаляем все архивированные задачи старше года
DELETE FROM tasks
WHERE status = 'archived'
  AND created_at < NOW() - INTERVAL '1 year';
При физическом удалении ты теряешь данные навсегда. Для аудита и восстановления используй soft delete.

Soft Delete (мягкое удаление)

Вместо реального DELETE используем временную метку:

-- Помечаем задачу как удалённую
UPDATE tasks
SET deleted_at = NOW()
WHERE id = 10 AND user_id = 1;

-- Все запросы автоматически исключают deleted_at IS NOT NULL
SELECT id, title FROM tasks
WHERE user_id = 1 AND deleted_at IS NULL;

-- Восстановление (если было случайно удалено)
UPDATE tasks
SET deleted_at = NULL
WHERE id = 10 AND user_id = 1;

-- Физическое удаление спустя 30 дней
DELETE FROM tasks
WHERE deleted_at < NOW() - INTERVAL '30 days';

Полный сценарий: управление задачами

-- 1. Создаём пользователя
INSERT INTO users (email, name) VALUES ('john@example.com', 'John')
RETURNING id; - получаем id = 100

-- 2. Добавляем несколько задач
INSERT INTO tasks (user_id, title, description, status, priority) VALUES
  (100, 'Срочно: Подготовить отчёт', 'до завтра', 'in_progress', 5),
  (100, 'Обучение SQL', 'закончить курс', 'in_progress', 4),
  (100, 'Купить продукты', '', 'new', 1);

-- 3. Смотрим активные задачи
SELECT id, title, priority, status FROM tasks
WHERE user_id = 100 AND deleted_at IS NULL
ORDER BY priority DESC;

-- 4. Отмечаем первую задачу как выполненную
UPDATE tasks
SET done = true, status = 'done', updated_at = NOW()
WHERE id = 1 AND user_id = 100;

-- 5. Статистика по задачам
SELECT
  COUNT(*) FILTER (WHERE status = 'done') as done_count,
  COUNT(*) FILTER (WHERE status = 'in_progress') as in_progress_count,
  COUNT(*) FILTER (WHERE status = 'new') as new_count
FROM tasks
WHERE user_id = 100 AND deleted_at IS NULL;

-- 6. Мягко удаляем ненужную задачу
UPDATE tasks SET deleted_at = NOW()
WHERE id = 3 AND user_id = 100;

Индексная стратегия

-- Основные индексы
CREATE INDEX idx_tasks_user_id ON tasks(user_id); - поиск по пользователю
CREATE INDEX idx_tasks_status ON tasks(status); - фильтр по статусу
CREATE INDEX idx_tasks_deleted_at ON tasks(deleted_at); - исключение удалённых
CREATE INDEX idx_users_email ON users(email); - поиск пользователя

-- Комбинированный индекс для часто используемого запроса
CREATE INDEX idx_tasks_user_status_del ON tasks(user_id, status, deleted_at)
WHERE deleted_at IS NULL;

-- Проверяем что используется (как читать вывод - в уроке про [EXPLAIN](./11-explain.md))
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM tasks
WHERE user_id = 1 AND status = 'in_progress' AND deleted_at IS NULL;

Миграции: как менять схему со временем

-- v1: добавляем поле
ALTER TABLE tasks ADD COLUMN due_date DATE;

-- v2: добавляем индекс
CREATE INDEX idx_tasks_due_date ON tasks(due_date);

-- v3: добавляем поле с дефолтом
ALTER TABLE tasks ADD COLUMN assignee_id BIGINT REFERENCES users(id);

-- v4: переименовываем поле
ALTER TABLE tasks RENAME COLUMN done TO is_done;

-- Всегда тести миграции перед деплоем!
-- В проде делай по одной, осторожно
Никогда не забывай добавить `AND user_id = $current_user` в WHERE. Иначе пользователь видит/меняет чужие задачи! deleted_at нужен для аудита. Физическое удаление уничтожает историю.

Полный чек-лист реализации

Когда ты используешь эту схему в приложении:

  • Таблицы и индексы созданы
  • Все SELECT запросы добавляют AND deleted_at IS NULL
  • Все UPDATE/DELETE проверяют AND user_id = current_user
  • Используется soft delete (UPDATE deleted_at) вместо DELETE
  • Добавлен updated_at и обновляется при каждом UPDATE
  • Есть миграции для изменений схемы (не меняй вручную на проде)
  • Написаны unit-тесты для основных запросов
  • Проверены индексы через EXPLAIN для основных операций
  • На проде есть резервные копии (backup)
  • Логирование всех изменений (audit log таблица, если нужно)

Практические задания

  • Пересоздай всю схему в своей БД и вставь тестовые данные
  • Напиши запрос: все невыполненные задачи пользователя, отсортированные по приоритету
  • Напиши запрос: для каждого статуса - количество задач у каждого пользователя
  • Реализуй soft delete: добавь DELETE запрос через UPDATE deleted_at
  • Добавь индекс и проверь EXPLAIN - как измениась стоимость?
  • Напиши миграцию: добавить поле tags (массив текста) для категоризации
  • Создай VIEW для часто используемых запросов (например, активные задачи)
  • Подключи эту схему из приложения: database/sql в Go или PDO в PHP

Зарегистрируйтесь бесплатно, чтобы пройти квиз, решить задание с автопроверкой и вести прогресс.