| # NL→SQL Assistant — архитектура (максимально нагруженный вариант) |
|
|
| **Дата:** 2026-05-10 |
| **Статус:** v1 — superseded after CX/KM review (2026-05-10). Документ оставлен как исторический. Действующая baseline: `02_architecture_v2.md`. |
|
|
| > ⚠️ **Известные ошибки v1**, исправлены в v2: |
| > - **BIRD Mini-Dev = 500 примеров** (не 1500/11 БД, как ниже в разделе 5) |
| > - **codestral-2501 deprecated** с ноября 2025 → переход на `codestral-latest` (v25.08) |
| > - 11-узловой pipeline → 6 узлов |
| > - 4 коллекции Chroma → 2 |
| > - стек: убраны Prometheus + OTel + Redis (избыточно для solo-demo) |
| > - Vega-Lite от LLM → детерминированный chart picker + Plotly шаблоны |
| > - Mistral-only → provider abstraction + 30-question bakeoff |
| > - eval target: 50% EA → baseline 35-40%, stretch 50%, hard checkpoint неделя 3 |
|
|
| > «Максимально нагруженный» здесь = всё, что реально нужно для серьёзного |
| > демо-проекта уровня Senior Data Engineer, без фейкового overengineering'а. |
| > Каждый компонент обоснован задачей; ничего «на будущее». |
|
|
| --- |
|
|
| ## 1. Системная диаграмма |
|
|
| ```text |
| ┌──────────────────────────┐ |
| │ Web UI (Next.js + React)│ |
| │ ─ chat input │ |
| │ ─ table / chart / SQL │ |
| │ ─ history + bookmarks │ |
| └────────────┬─────────────┘ |
| │ HTTPS |
| ▼ |
| ┌──────────────────────────────────────────┐ |
| │ FastAPI gateway (auth, rate-limit, CORS)│ |
| │ /ask, /databases, /history, /eval/run │ |
| └────────────┬─────────────────────────────┘ |
| │ |
| ┌────────────────────┼────────────────────────────┐ |
| │ │ │ |
| ▼ ▼ ▼ |
| ┌──────────────┐ ┌────────────────┐ ┌────────────────────┐ |
| │ LangGraph │ │ Eval harness │ │ Schema indexer │ |
| │ NL→SQL graph │ │ (BIRD/Spider) │ │ (offline pipeline) │ |
| └──────┬───────┘ └────────┬───────┘ └─────────┬──────────┘ |
| │ │ │ |
| ▼ ▼ ▼ |
| ┌──────────────────────────────────────────────────────────────┐ |
| │ Shared services layer │ |
| ├──────────────┬──────────────┬─────────────┬──────────────────┤ |
| │ Mistral API │ Chroma DB │ Postgres │ Redis │ |
| │ codestral │ schema chunks│ target DBs │ result cache │ |
| │ large-2 │ few-shot Q→S │ (multi-DB) │ rate-limit state │ |
| │ mistral-embed│ │ + traces DB │ │ |
| └──────────────┴──────────────┴─────────────┴──────────────────┘ |
| │ |
| ▼ |
| ┌──────────────────────────┐ |
| │ Observability │ |
| │ Prometheus + OpenTelemetry│ |
| │ Langfuse traces │ |
| └──────────────────────────┘ |
| ``` |
|
|
| ## 2. LangGraph pipeline |
|
|
| Реиспользуем структуру из RAG_Support_Assistant |
| (`classify → retrieve → rerank → generate → verify → evaluate`), |
| но узлы заточены под NL→SQL: |
|
|
| ```text |
| ┌────────────────┐ |
| │ classify_intent│ intent = aggregation | ranking | filter | |
| └────────┬───────┘ time_series | comparison | lookup | |
| │ distribution |
| ▼ |
| ┌────────────────┐ |
| │ select_database│ если в системе несколько БД — выбрать целевую |
| └────────┬───────┘ по интенту + ключевым словам |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ retrieve_schema│ Chroma: relevant tables + columns + value samples |
| └────────┬───────┘ |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ retrieve_examples│ Chroma: top-k похожих Q→SQL пар (few-shot) |
| └────────┬───────┘ |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ generate_sql │ codestral-2501 + structured output (JSON-mode) |
| └────────┬───────┘ { "sql": "...", "rationale": "..." } |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ static_validate│ sqlglot parse → SELECT-only guard → schema check |
| └────────┬───────┘ (table/column existence vs catalog) |
| │ FAIL ──────────► retry_loop (max 2) |
| │ OK |
| ▼ |
| ┌────────────────┐ |
| │ explain_plan │ EXPLAIN на целевой БД, отказ если cost > threshold |
| └────────┬───────┘ (защита от full-scan на больших таблицах) |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ execute │ read-only коннект, statement_timeout, LIMIT-guard |
| └────────┬───────┘ |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ verify_result │ проверки: непустой? типы соответствуют интенту? |
| └────────┬───────┘ аномалии? (нулей/null'ов слишком много) |
| │ FAIL ──────────► retry_loop |
| │ OK |
| ▼ |
| ┌────────────────┐ |
| │ choose_format │ intent + result shape → |
| └────────┬───────┘ scalar | sentence | table | chart |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ render_answer │ mistral-large-2: NL-объяснение + chart-spec (Vega-Lite) |
| └────────┬───────┘ |
| │ |
| ▼ |
| ┌────────────────┐ |
| │ persist_trace │ sqlite traces + Langfuse span + Prometheus counter |
| └────────────────┘ |
| ``` |
|
|
| **Retry loop:** при фейле узлов `static_validate`, `execute`, `verify_result` |
| граф возвращается к `generate_sql` с приклеенным error-context'ом |
| (текст ошибки + предыдущий SQL + разъяснение что не так). Лимит — 2 попытки, |
| после чего отдаётся диагностический ответ. |
|
|
| ## 3. Schema-RAG: устройство индекса |
|
|
| **Проблема:** в BIRD есть БД с 50+ таблицами. Полная схема в промпт не лезет |
| и зашумляет генерацию. |
|
|
| **Решение:** offline-пайплайн `Schema indexer` строит несколько коллекций в Chroma: |
|
|
| | Коллекция | Чанк | Эмбеддится | |
| |---|---|---| |
| | `schema_tables` | таблица | имя + описание + список колонок + 3 sample строки | |
| | `schema_columns` | колонка | имя + тип + описание + min/max/nunique + 5 sample значений | |
| | `fewshot_qsql` | Q→SQL пара | вопрос + аннотация интента (SQL не эмбеддится) | |
| | `relations` | FK-связь | from_table.col → to_table.col + семантика | |
|
|
| При вопросе `retrieve_schema` делает гибрид BM25 + dense на `schema_tables`, |
| дотягивает топ-N колонок из `schema_columns` для отобранных таблиц, |
| добавляет связи между ними. Получается компактный «срез схемы» под вопрос — |
| обычно 5-15 таблиц вместо 50+. |
|
|
| `retrieve_examples` достаёт из `fewshot_qsql` 3-5 наиболее похожих |
| вопросов с эталонными SQL — это мощно поднимает качество на сложных диалектах. |
|
|
| ## 4. Безопасность исполнения SQL |
|
|
| Read-only — это не «обещание промптом», а реальные гарды на четырёх уровнях: |
|
|
| 1. **БД-роль:** отдельный postgres-пользователь с GRANT SELECT ONLY, |
| без CREATE/INSERT/UPDATE/DELETE/TRUNCATE/ALTER. |
| 2. **Парсер:** `sqlglot` AST-валидация — отказ при не-SELECT, при множественных |
| стейтментах, при наличии CTE с DML, при `pg_*`/`information_schema` без whitelist. |
| 3. **EXPLAIN-gate:** `EXPLAIN (FORMAT JSON)` перед `EXECUTE`, |
| отказ если `Total Cost > X` (порог настраивается на БД). |
| 4. **Runtime:** `SET statement_timeout = 30s`, обязательный `LIMIT 10000` |
| если в запросе нет агрегации. |
|
|
| ## 5. Eval harness |
|
|
| Отдельный модуль `eval/`, не часть онлайн-пайплайна: |
|
|
| ```text |
| eval/ |
| ├── datasets/ |
| │ ├── bird_mini.jsonl # 1500 Q→SQL пар, 11 БД |
| │ └── stackexchange_gold.jsonl # 20 наших эталонных вопросов |
| ├── runner.py # прогон через граф, сравнение |
| ├── metrics/ |
| │ ├── execution_accuracy.py # сравнение result-set'ов |
| │ ├── exact_match.py # SQL string match (слабая метрика) |
| │ └── component_match.py # сравнение по AST-компонентам |
| └── reports/ |
| └── 2026-05-10-baseline.html # отчёт по прогонам |
| ``` |
|
|
| CI прогоняет smoke-eval на 50 примерах при каждом merge в main. |
| Полный прогон — вручную или nightly. |
|
|
| **Целевое число:** Execution Accuracy ≥ 50% на BIRD-mini dev. |
| Опубликованные результаты codestral-2501 на BIRD ~57%, так что 50% |
| своими силами на узком сабсете — реалистично. |
|
|
| ## 6. Multi-DB switching |
|
|
| В `config/databases.yml` описаны подключения: |
|
|
| ```yaml |
| databases: |
| - id: stackexchange |
| dsn: postgresql://nlsql_ro@localhost/stackexchange |
| description: "StackOverflow public data — posts, users, votes" |
| schema_index: chroma://stackexchange |
| sample_questions: ["топ-10 тегов...", "распределение..."] |
| - id: bird_california_schools |
| dsn: sqlite:///data/bird/california_schools.sqlite |
| description: "California schools — performance, demographics" |
| schema_index: chroma://bird_california_schools |
| - id: chinook |
| dsn: sqlite:///data/chinook.sqlite |
| description: "Music store — invoices, tracks, customers" |
| schema_index: chroma://chinook |
| ``` |
|
|
| UI даёт переключатель «target DB», граф читает её из state. |
|
|
| ## 7. UI (Next.js + React) |
|
|
| Минимально, но без обрезков: |
|
|
| - **Chat-style вход** с подсветкой SQL в ответе и copy-кнопкой. |
| - **Multi-format ответ** — компонент сам решает, что рендерить |
| (scalar / sentence / DataGrid / Vega-Lite chart). |
| - **«Show working»**: разворачивающийся блок с retrieved schema, few-shot, |
| rationale, EXPLAIN-планом, временем выполнения. |
| - **History + bookmarks** в localStorage + опционально на бэке. |
| - **DB switcher** + список sample-вопросов под каждую БД. |
|
|
| Отдельная страница **`/eval`** — таблица результатов eval-прогонов, |
| графики динамики Execution Accuracy по коммитам. |
|
|
| ## 8. Стек целиком |
|
|
| | Слой | Технология | Почему | |
| |---|---|---| |
| | LLM | Mistral API: codestral-2501, mistral-large-2, mistral-embed | Жёсткое требование задачи | |
| | Orchestration | LangGraph | Уже знакома по RAG_SA, retry-loop из коробки | |
| | API | FastAPI + Pydantic v2 | Стандарт, типобезопасность | |
| | Vector DB | ChromaDB | Уже знакома, локально без отдельного сервиса | |
| | SQL parser | sqlglot | Multi-dialect, AST-валидация, dialect translation | |
| | Target DB | Postgres 16 (StackExchange) + SQLite (BIRD, Chinook) | Реализм + простота | |
| | Cache | Redis 7 | Кэш результатов SQL, rate-limit | |
| | Charting | Vega-Lite (через спеку из LLM) + Plotly fallback | LLM хорошо генерит Vega-spec'и | |
| | Frontend | Next.js 15 + Tailwind + shadcn/ui | Быстрый красивый UI | |
| | Observability | Prometheus + OpenTelemetry + Langfuse | Стандартный стек, переиспользуется из RAG_SA | |
| | Tests | pytest + httpx + testcontainers (Postgres) | Реальная БД в CI | |
| | Lint/Type | ruff + mypy strict (api/, agent/) | Как в DE_project | |
| | CI | GitHub Actions | smoke-eval + pytest + ruff + mypy | |
| | Deploy | docker-compose (dev) + Dockerfile multi-stage (prod) | Достаточно для демо | |
| |
| ## 9. Структура репозитория |
| |
| ``` |
| NL_SQL/ |
| ├── api/ # FastAPI app, routers, middleware |
| ├── agent/ # LangGraph nodes, prompts, state |
| │ ├── graph.py |
| │ ├── nodes/ |
| │ │ ├── classify.py |
| │ │ ├── retrieve_schema.py |
| │ │ ├── retrieve_examples.py |
| │ │ ├── generate_sql.py |
| │ │ ├── validate.py |
| │ │ ├── execute.py |
| │ │ ├── verify.py |
| │ │ ├── render.py |
| │ │ └── retry.py |
| │ └── prompts/ |
| ├── llm/ # Mistral provider, retry, cost guard |
| ├── schema_index/ # offline indexer for Chroma |
| │ ├── extractor.py # introspect Postgres/SQLite catalog |
| │ ├── enricher.py # описания, sample values, stats |
| │ └── builder.py # build Chroma collections |
| ├── execution/ # SQL guards, EXPLAIN gate, runner |
| ├── eval/ # см. раздел 5 |
| ├── frontend/ # Next.js UI |
| ├── config/ # databases.yml, prompts.yml |
| ├── data/ # BIRD dump, Chinook, sample dumps (gitignore) |
| ├── tests/ |
| ├── docker-compose.yml |
| ├── Dockerfile |
| └── docs/ |
| ├── 00_task.md |
| ├── 01_architecture.md ← вы здесь |
| ├── 02_eval_methodology.md ← TODO |
| └── 03_demo_questions.md ← TODO |
| ``` |
| |
| ## 10. Roadmap (этапы) |
|
|
| | # | Этап | DoD | |
| |---|---|---| |
| | 1 | **Bootstrap** | poetry/uv проект, FastAPI hello, Mistral provider, тесты на провайдер с моком | |
| | 2 | **Target DBs ready** | docker-compose поднимает Postgres со StackExchange dump + SQLite Chinook + BIRD dump в `data/` | |
| | 3 | **Schema indexer** | offline скрипт строит Chroma-коллекции, smoke-тест на retrieval | |
| | 4 | **Pipeline v1** | LangGraph граф работает на Chinook (простая БД), single-shot без retry | |
| | 5 | **Guards & verify** | sqlglot guard, EXPLAIN gate, retry-loop, тесты | |
| | 6 | **Eval harness** | runner + execution_accuracy метрика, baseline на BIRD-mini | |
| | 7 | **Multi-format render** | scalar/sentence/table/chart с автоопределением + Vega-Lite spec'и | |
| | 8 | **UI v1** | chat + DB switcher + history, end-to-end на 3 БД | |
| | 9 | **Polish & deploy** | docker-compose prod-like, README, демо-видео, eval-страница | |
| |
| Этапы 1-3 — фундамент (~неделя на каждом темпе). |
| Этапы 4-6 — суть проекта (~2 недели). |
| Этапы 7-9 — витрина (~неделя). |
| |
| Итого: ~5-6 рабочих недель в спокойном темпе или 2-3 в плотном. |
| |
| ## 11. Риски |
| |
| | Риск | Вероятность | Митигация | |
| |---|---|---| |
| | codestral-2501 даёт <40% на BIRD | средняя | улучшить few-shot retrieval, добавить chain-of-thought, schema-linking шаг | |
| | StackExchange dump слишком большой для локалки (≥100GB) | высокая | взять mini-dump (`gaming.stackexchange.com`, ~1GB) — реализм без боли | |
| | EXPLAIN-gate ломает легитимные тяжёлые запросы | средняя | tune порог на БД, дать override-флаг для админа | |
| | BIRD dataset лицензия | низкая | CC-BY-SA-4.0, для демо OK | |
| | Mistral API rate limits на eval-прогоне | средняя | local cache на (prompt → response), батчинг, exponential backoff | |
| |
| ## 12. Что в этой архитектуре «нагруженного» |
| |
| Если сравнить с минимальным NL→SQL (один промпт + один вызов LLM + execute): |
| |
| - **+ LangGraph pipeline на 10+ узлов** с retry-loop и error-context. |
| - **+ Schema-RAG из 4 коллекций** вместо «вся схема в промпт». |
| - **+ Few-shot retrieval** из эталонных Q→SQL пар. |
| - **+ Static validate (sqlglot AST) + EXPLAIN-gate + 4-уровневая защита**. |
| - **+ Multi-DB** с переключателем и per-DB индексами. |
| - **+ Eval harness на публичном бенчмарке** с измеримой метрикой. |
| - **+ Multi-format рендер** (4 формата + auto-выбор графика). |
| - **+ Полноценный observability stack** (Prom + OTel + Langfuse). |
| - **+ Web-UI** с историей, eval-страницей, «show working». |
| |
| Это потолок того, что осмысленно делать для демо-проекта без скатывания |
| в production-overhead (мульти-тенант, RBAC, OIDC, freshness monitor и т.д. — |
| всё то, что в RAG_SA уместно, а здесь было бы фейк-нагрузкой). |
|
|
