Video Analytics Telegram Bot

Тестовое задание по разработке аналитического сервиса для работы с видео и замерами статистики.

Проект реализует Telegram-бота, который принимает вопросы на естественном языке (RU), преобразует их в формализованный план запроса и выполняет агрегации по данным в PostgreSQL.

---

🧠 Общая идея

Архитектура построена по принципу LLM → QueryPlan → SQLAlchemy:

1. Пользователь задаёт вопрос на русском языке
2. LLM (OpenAI) преобразует текст в строго ограниченный JSON-план запроса (QueryPlan)
3. План валидируется через Pydantic
4. На основе плана динамически строится SQL-запрос
5. Результат возвращается пользователю

LLM не генерирует SQL напрямую, а лишь описывает что нужно посчитать, а как — решает серверная логика.

---

📦 Стек технологий

• Python 3.12
• aiogram (Telegram Bot API)
• SQLAlchemy (async)
• PostgreSQL
• Pydantic v2
• OpenAI API (через HTTP/SOCKS proxy)
• Alembic (миграции)

---

🗂️ Структура проекта

video_bot/
├── bot/
│   ├── main.py          # точка входа бота
│   └── handlers.py      # обработчики сообщений
├── db/
│   ├── models.py        # SQLAlchemy модели
│   ├── session.py       # 2 сессии. 1 async для бота 2 sync для load_json и миграций
│   ├── queries.py       # выполнение QueryPlan
│   └── base.py          # экземпляр DeclarativeBase
├── llm/
│   ├── client.py        # OpenAI client
│   ├── prompt.py        # system prompt
│   └── schemas.py       # Pydantic схемы (QueryPlan)
├── services/
│   ├── dispatcher.py    # диспетчер выполнения запросов
│   └── clean_response   # очистка ответа от llm от мусора
├── config.py            # настройки проекта
├── alembic/             # миграции БД
└── README.md

---

📄 QueryPlan (ключевая концепция)

LLM возвращает JSON строго следующего вида:

{
  "source": "videos | snapshots",
  "select": {
    "type": "aggregate",
    "func": "count | sum",
    "field": "id | creator_id | views_count | delta_views_count | video_created_at",
    "distinct": true
  },
  "filters": [
    {
      "field": "created_at | video_created_at | creator_id | views_count | delta_views_count",
      "op": "= | > | < | >= | <= | between",
      "value": "..."
    }
  ]
}

Ограничения (принципиально):

• ❌ нет вложенных запросов
• ❌ нет JOIN'ов от LLM
• ❌ нет подзапросов в фильтрах
• ❌ нет raw SQL от LLM

Это гарантирует безопасность и предсказуемость.

---

🔍 Поддерживаемые типы запросов

Примеры вопросов, которые корректно обрабатываются:

• Сколько видео опубликовано за период
• Сколько разных креаторов имеют видео с > N просмотров
• Суммарный прирост просмотров за период
• Суммарные просмотры видео, опубликованных в определённый месяц
• Подсчёт уникальных календарных дней публикаций
• Анализ замеров (snapshots) с учётом времени

Все проверки выполняются через SQLAlchemy.

---

🌍 Работа через proxy

OpenAI API вызывается через HTTP / SOCKS5 proxy:

http_client = httpx.Client(proxy=settings.PROXY_URL)
client = OpenAI(api_key=..., http_client=http_client)

Это позволяет изолировать трафик OpenAI от остального приложения (Telegram работает напрямую).

---

▶️ Запуск проекта

1. git clone https://github.com/artem-sitd/video_bot.git 2. cd video_bot
2. Создаем вирт. окружение python3 -m venv venv 2. Активируем его source venv/bin/activate
3. Устанавливаем зависимости pip install -r requirements.txt
4. Копируем .env cp .env.example .env
5. Создаем БД в postgres любым вашим способом

CREATE DATABASE videos_db;
CREATE USER videos_user WITH PASSWORD '123';
ALTER DATABASE videos_db OWNER TO videos_user;

5. Заполняем .env:

   • DB_HOST=localhost
   • DB_PORT=5432 # стандартный порт
   • DB_NAME=videos_db # название вашей бд
   • DB_USER=videos_user # имя пользователя с правами на эту базу
   • DB_PASSWORD=123 # пароль БД
   • BOT_TOKEN= # токен телеграмм бота
   • OPENAI_API_KEY= # ключ от АПИ openai
   • LOGIN= # логин вашего прокси
   • PASS= # пароль вашего прокси
   • HOST= # ip адресс вашего прокси
   • PORT= # порт вашего прокси

6. Применить миграции:

alembic revision --autogenerate -m "init tables"
alembic upgrade head

7. Запустить скрипт на заполнение тестовыми данными videos.json

python3 app/loader/load_json.py

8. Запустить бота:

python3 bot/main.py

---

🧪 Тестовое задание

Проект реализован в рамках тестового задания.

Основной фокус:

• корректная интерпретация естественного языка
• строгая валидация входных данных
• отсутствие SQL-инъекций
• поддержка нетривиальных аналитических кейсов

Все автотесты проверяющей системы успешно пройдены.

---

⚠️ Примечания

• Архитектура сознательно ограничивает возможности LLM
• Расширение логики происходит на стороне сервера, а не через промпт
• Решение ориентировано на читаемость и предсказуемость, а не на "магический" SQL

---

👤 Автор

Artem Sitd
Python Backend Developer