Y.E.R.A. AI

Yield Logistics & Evaluation Routing Agent

Трек	Логистика · кейс ТОО «Электрокомплект»
Хакатон	HackAlem AI
Роль	Разработчик-одиночка (Fullstack AI Engineer)
Стек	Python · Streamlit · SQLite · OpenAI API

---

1. Название проекта

Y.E.R.A. AI

Yield Logistics & Evaluation Routing Agent — аналитический агент закупа для автоматического расчёта рекомендованных (не автоматических) заказов поставщикам на основе истории продаж, остатков, товара в пути, сезонности и периодов дефицита.

---

2. Краткое описание

Менеджер закупа «Электрокомплекта» часто считает пополнение склада вручную в Excel: расчёт делается редко, разовые крупные отгрузки искажают регулярный спрос, а периоды stockout занижают потребность. Итог — перезакуп одних позиций и дефицит других.

Y.E.R.A. AI решает эту задачу детерминированным Python-движком:

• считает регулярную потребность по артикулам с учётом всех источников данных из SQLite;
• исключает аномальные разовые всплески из базы regular demand (без удаления из истории);
• компенсирует упущенный спрос в периоды отсутствия товара;
• округляет заказ под MOQ / кратность поставщика (ИЭК, Systeme Electric);
• показывает финансовый контроль кассового разрыва;
• даёт менеджеру Text-to-SQL чат для вопросов к storage.db;
• никогда не отправляет заказ поставщику без явного утверждения человеком (draft → approved).

---

3. Что реализовано

• Детерминированная очистка выбросов — detect_outliers() в core/outliers.py: IQR + доля одного client_hash в месяце; флаг is_outlier пишется в sales_transactions, история не удаляется.
• Компенсация stockout — периоды из stockout_periods увеличивают потребность (stockout_adj), чтобы дефицит не занижал регулярный заказ.
• Сезонность и тренд — индекс из seasonality_reference и отношение средних продаж 3/3 мес. (core/demand.py).
• MOQ / кратность — apply_moq_rounding() в database.py для min_ship_qty и multiplicity (демо-данные ИЭК и Systeme Electric).
• Финансовый анализ — сумма закупа по unit_cost, ввод бюджета менеджера, метрика кассового разрыва, выделение срочных позиций (критическая / высокая).
• Text-to-SQL ИИ-ассистент — вкладка чата: NL → безопасный SELECT → выполнение execute_readonly_query() / is_safe_select() → ответ Y.E.R.A. AI (модель по умолчанию gpt-4o-mini).
• Экспорт CSV — st.download_button, файл yera_recommended_orders.csv, кодировка utf-8-sig, разделитель ; для корректного открытия в Microsoft Excel.
• UI — премиум тёмная тема, горизонтальные st.tabs (Дашборд / Рекомендации / ИИ-Ассистент), KPI-карточки и hover-анимации на CSS, настройки API в expander шапки.

---

4. Как работает решение

Пользовательский сценарий для менеджера закупа:

1. Данные — при первом запуске создаётся / наполняется локальная БД storage.db (database.py: схема + демо ИЭК / Systeme Electric).
2. Дашборд — KPI (товары, критический дефицит, объём в пути, черновики), превью последнего расчёта, остатки и помесячные продажи.
3. Рекомендации — кнопка «Запустить интеллектуальный расчет пополнения Y.E.R.A. AI» → пайплайн core/pipeline.py (detect_outliers → calculate_recommendations → запись в recommended_orders со статусом draft).
4. Финансовый контроль — менеджер задаёт бюджет (₸), видит сумму закупа, срочную часть и кассовый разрыв.
5. Утверждение — выбор позиций и approved_by; автоотправки поставщику нет.
6. Выгрузка — «Скачать отчет закупа в CSV».
7. ИИ-чат — вопросы на естественном языке к storage.db (например: «Покажи товары ИЭК»).

Excel/демо-данные → SQLite (storage.db)
        ↓
 core/ (outliers + demand)  ← qty только Python, не LLM
        ↓
 recommended_orders (draft)
        ↓
 Streamlit UI: утверждение / CSV / Text-to-SQL чат

---

5. Технологии

Компонент	Использование в проекте
Python 3.11+	Бизнес-логика, ETL-сид, API-обвязка
Streamlit	UI: st.tabs, KPI st.metric, чат st.chat_message / st.chat_input, кастомный CSS (тёмная тема, hover)
pandas	Таблицы расчёта и отображения
SQLite (sqlite3)	Локальная БД storage.db
OpenAI API	Только объяснения и Text-to-SQL (gpt-4o-mini по умолчанию); не считает qty
Конфиг ключа	OPENAI_API_KEY через os.getenv / .env / st.secrets / ручной ввод в шапке

Зависимости из requirements.txt:

streamlit>=1.32.0
pandas>=2.0.0
openai>=1.40.0

Расчёт количеств заказов выполняется только детерминированным кодом в core/. LLM не участвует в формуле потребности (требование воспроизводимости).

---

6. Архитектура проекта

HackAlem.ai_project/
├── app.py                 # Streamlit UI + чат Text-to-SQL + экспорт CSV
├── database.py            # Схема SQLite, сид демо-данных, MOQ, safe SELECT
├── storage.db             # Локальная база (создаётся при запуске)
├── .env                   # OPENAI_API_KEY (не коммитить в публичный репозиторий)
├── agent_prompt.txt       # Системный промпт ассистента Y.E.R.A. AI
├── requirements.txt
├── core/
│   ├── outliers.py        # detect_outliers()
│   ├── demand.py          # calculate_recommendations() — чистые функции
│   └── pipeline.py        # Оркестрация одного прогона расчёта
└── repository/
    └── data.py            # Чтение/запись БД (без бизнес-формул)

Взаимодействие:

• app.py вызывает run_replenishment_calculation() и функции repository / database.
• core/ получает DataFrame и возвращает результат без прямых SQL внутри формул спроса.
• database.py владеет схемой таблиц (suppliers, products, sales_transactions, monthly_stock, monthly_sales, stockout_periods, transit_orders, seasonality_reference, recommended_orders) и read-only SQL для чата.
• OpenAI вызывается только из чата (и опционально для текста обоснований по уже посчитанным цифрам).

---

7. Установка и запуск

Требования

• Python 3.11+
• Ключ OpenAI в переменной окружения или в файле .env:

OPENAI_API_KEY=sk-...

На Windows (PowerShell), чтобы ключ был доступен процессу Streamlit:

$env:OPENAI_API_KEY = "sk-..."

или задайте User-переменную окружения и перезапустите терминал.

Шаги

cd HackAlem.ai_project

python -m venv .venv
.\.venv\Scripts\Activate.ps1

pip install -r requirements.txt

# (опционально) пересоздать демо-БД
python database.py

# запуск интерфейса
streamlit run app.py

Приложение откроется по адресу http://localhost:8501.

Если storage.db ещё нет, app.py сам вызовет init_db + seed_test_data при старте.

---

8. Как проверить решение

Сценарий для жюри HackAlem AI (~5 минут):

1. Открыть http://localhost:8501.
2. Убедиться, что в шапке «⚙️ Настройки API» ключ подхвачен из окружения (или ввести fallback).
3. Вкладка «📦 Рекомендации» → нажать
   «Запустить интеллектуальный расчет пополнения Y.E.R.A. AI».
4. Дождаться появления таблицы рекомендованных заказов и блоков MOQ / финансового анализа.
5. Изменить «Доступный бюджет закупа, ₸» и проверить пересчёт кассового разрыва.
6. При необходимости утвердить позиции (указать ФИО / логин утверждающего).
7. Скачать «📥 Скачать отчет закупа в CSV» и открыть в Excel (колонки должны разделиться по ;, кириллица читаема).
8. Вкладка «💬 ИИ-Ассистент» → вопрос:
   «Покажи товары ИЭК»
   → в ответе числа из БД; в expander можно посмотреть безопасный SQL SELECT.

Дополнительно на «📊 Дашборд закупа»: KPI и остатки / помесячные продажи по фильтрам поставщик / категория.

---

9. Данные и интеграции

Источник	Содержание
storage.db	Единая локальная SQLite-база
Поставщики (демо)	ИЭК, Systeme Electric
Продажи	sales_transactions (отгрузки с отрицательным quantity, обезличенный client_hash)
Агрегаты	monthly_sales, monthly_stock
В пути / дефицит	transit_orders, stockout_periods
Сезонность	seasonality_reference
MOQ	products.min_ship_qty, products.multiplicity, unit_cost
Результат	recommended_orders + человекочитаемое justification

Клиентские идентификаторы в БД только как sha256 (hash_client_id в database.py). Неанонимизированные ID клиентов в расчёт не попадают.

Интеграция с реальной 1С «Электрокомплекта» в текущей версии не подключена — данные симулируются сидером / локальным файлом БД.

---

10. Ограничения

Честно о статусе MVP:

• Локальная SQLite, без облачной репликации и многопользовательской блокировки «как в проде».
• Нет прямого API-шлюза к 1С / EDI поставщиков — для промышленного контура потребуется ETL из выгрузок 1С и отдельный backend.
• Заказ не уходит поставщику по email/API автоматически (осознанное ограничение по ТЗ).
• Качество Text-to-SQL зависит от доступности OpenAI API и корректности OPENAI_API_KEY.
• Демо-набор SKU ограничен сидером; на полной номенклатуре нужны тюнинг порогов выбросов и объёмы истории.
• Файл .env с секретами не должен попадать в публичный git.

---

11. Ссылка на deployed-версию

Локальный запуск для демонстрации жюри:

http://localhost:8501

Команда:

streamlit run app.py

---

Контакты / роль

Fullstack AI Engineer (solo) · трек «Логистика» · кейс ТОО «Электрокомплект» · HackAlem AI

Y.E.R.A. AI — рекомендованный заказ с объяснимостью, а не «чёрный ящик».