text-to-sql-mcp

MCP-сервер, который переводит вопросы на естественном языке в SQL поверх реальной нетривиальной схемы с несколькими таблицами — и никогда не доверяет полученному SQL. Каждый сгенерированный запрос разбирается в настоящий AST (через sqlglot) и прогоняется через валидатор до любого выполнения: не-SELECT-выражения, многооператорная инъекция, опасные функции, галлюцинированные таблицы/колонки и неограниченные полные сканирования больших таблиц отклоняются структурно, а не доверием к собственному суждению LLM.

Результат модели — это предложение, а не команда. Валидатор решает, что в действительности выполняется.

Статус

Вехи M1–M4 из спецификации реализованы и протестированы: интроспекция схемы, генерация NL→SQL (настоящие бэкенды Anthropic/OpenAI + детерминированный офлайн-фолбэк), AST-валидатор, размеченный оценочный стенд и обёртка MCP-сервера. О том, что было сознательно отложено, см. Риски / Открытые вопросы.

Архитектура

NL question
    │
    ▼
schema introspection (introspection.py)  ──► SQLite catalog (sqlite_master + PRAGMA table_info)
    │  grounds the prompt in the *real* schema, not guessed names
    ▼
LLMClient.generate_sql()  (llm/factory.py picks one)
    │  - AnthropicLLMClient  (real Claude API call, used if ANTHROPIC_API_KEY is set)
    │  - OpenAILLMClient     (real OpenAI API call, used if OPENAI_API_KEY is set)
    │  - RuleBasedLLMClient  (deterministic fixture lookup, the offline default)
    ▼
candidate SQL string  ──────────────────►  never trusted past this point
    │
    ▼
validate_sql()  (validator/ast_validator.py)
    │  1. parseable?                       -- sqlglot.parse()
    │  2. exactly one statement?           -- reject `SELECT ...; DROP ...`
    │  3. root node is SELECT/UNION/       -- allow-list, not a blocklist
    │     INTERSECT/EXCEPT?
    │  4. no SELECT ... INTO?
    │  5. no dangerous function calls?     -- load_extension, readfile, writefile, ...
    │  6. every table exists in schema?
    │  7. every resolvable column exists?  -- best-effort, conservative
    │  8. large table + no WHERE + not     -- the "unbounded full scan" check
    │     a bounded/aggregate result?
    │
    ├── reject ──► {rejected: true, rejection_reason: "..."}
    │
    ▼ ok
execute_query()  (execution.py)  ──► SQLite opened `mode=ro` + `PRAGMA query_only=ON`
    │  (defense in depth: even a validator bug can't write, because the
    │   connection itself refuses)
    ▼
{sql, rows, rejected: false}
    │
    ▼
query_log  (query_log.py)  ──► every call logged, rejection rate reported
                                 separately from accuracy (see below)

Всё вышеперечисленное обёрнуто в MCP-сервер (mcp_server.py, построенный на FastMCP из официального Python SDK mcp), который предоставляет ровно два инструмента из API-контракта спецификации:

• list_schema() -> {tables: [{name, columns: [{name, type}]}]}

• ask(question: str) -> {sql, rows, rejected, rejection_reason}

Почему SQLite вместо Postgres

Спецификация нацелена именно на Postgres. В этом окружении нет запущенного сервера Postgres и нет демона Docker, поэтому целевая база данных — SQLite. Это осознанная задокументированная замена, а не оплошность. Слой интроспекции (introspection.py) — единственная часть, которая действительно привязана к SQLite (sqlite_master + PRAGMA table_info вместо information_schema); валидатор, слой выполнения и обёртка MCP работают с разобранным AST и не знают и не заботятся о том, какая база данных создала схему. Путь обновления до Postgres: замените sqlite3.connect(..., mode=ro) из db/connection.py на соединение psycopg, открытое к роли только для чтения, перепишите два запроса в introspection.py на information_schema.tables/columns и передавайте dialect="postgres" в validate_sql() — sqlglot нативно поддерживает оба диалекта, поэтому сама логика AST не меняется.

Почему синтетический набор данных вместо загрузки живых открытых данных

Спецификация предлагает реальный городской/государственный портал открытых данных. Вместо этого db/seed.py генерирует синтетический, но реалистичный муниципальный набор данных — 12 таблиц, смоделированных по образцу реальных схем разрешений/проверок/нарушений (NYC DOB, Chicago building permits) — детерминированно из фиксированного сида, полностью офлайн. Это был осознанный компромисс, а не лень: он сохраняет воспроизводимость init-db без сетевых зависимостей (без нестабильного CI, без ограничения частоты запросов, без простоев портала) и обходит лицензионный вопрос, который сама спецификация называет риском (§13), ещё до публикации демо. Схема действительно нетривиальна по собственным критериям спецификации: 12 таблиц, внешние ключи на три хопа вглубь (payments → violations → properties) и намеренная неоднозначность имён колонок (status встречается в permits, licenses, violations и complaints; type — в четырёх разных таблицах), что по-настоящему проверяет логику привязки к схеме валидатора.

Установка

pip install -e .

Требуется Python 3.10+. Опциональные дополнительные зависимости для настоящих LLM-бэкендов (уже установлены в dev-окружениях, где они есть; нужны только если их нет):

pip install -e ".[anthropic]"   # anthropic SDK
pip install -e ".[openai]"      # openai SDK

Быстрый старт — реальный демонстрационный запуск на реальной базе данных SQLite

# 1. Build the demo database (12 tables, ~8,700 rows, deterministic seed 42)
text-to-sql-mcp init-db

# 2. Inspect the schema the model is grounded in
text-to-sql-mcp schema

# 3. Ask a question -- no API key needed, uses the deterministic rule-based backend
text-to-sql-mcp ask "How many permits are there in total?"

backend:  rule-based
sql:      SELECT COUNT(*) AS count FROM permits
rejected: False
rows (1):
[
  {
    "count": 2600
  }
]

Вопрос с большим числом JOIN:

text-to-sql-mcp ask "How many permits does each contractor hold?"

backend:  rule-based
sql:      SELECT c.business_name, COUNT(*) AS permit_count FROM permits p JOIN contractors c ON p.contractor_id = c.contractor_id GROUP BY c.business_name ORDER BY permit_count DESC
rejected: False
rows (50):
[
  { "business_name": "Garcia Builders", "permit_count": 167 },
  { "business_name": "Kim Builders", "permit_count": 144 },
  { "business_name": "Miller Plumbing Co", "permit_count": 119 },
  ...
]

Неоднозначный вопрос — намеренно не разрешаемый молча в одну догадку (см. Граничные случаи):

text-to-sql-mcp ask "Show me the recent activity."

backend:  rule-based
sql:      AMBIGUOUS: 'Recent activity' could mean permits, inspections, violations, complaints, or payments -- and over what time window. Please specify which type of record and a date range or property.
rejected: True
reason:   Question is ambiguous and was not silently resolved to one interpretation. Clarification needed: ...

Доказательство того, что именно валидатор, а не модель, блокирует деструктивный SQL — здесь используется фикстурный клиент, заменяющий скомпрометированную/подвергшуюся промпт-инъекции модель, которая всегда соглашается на деструктивный запрос:

python - <<'EOF'
from text_to_sql_mcp.config import get_settings
from text_to_sql_mcp.service import ask

class MaliciousFixtureLLMClient:
    name = "malicious-fixture"
    def generate_sql(self, question, schema):
        return "DROP TABLE permits"

result = ask("Please delete all the permit records.",
             llm_client=MaliciousFixtureLLMClient(), settings=get_settings())
print("sql:     ", result.sql)
print("rejected:", result.rejected)
print("reason:  ", result.rejection_reason)
EOF

sql:      DROP TABLE permits
rejected: True
reason:   Statement type 'Drop' is not a read-only SELECT/UNION/INTERSECT/EXCEPT query. Only SELECT-family statements may be executed.

Теперь посмотрим, что видит оператор, — доля отклонений, которая сообщается отдельно от точности (см. ниже):

text-to-sql-mcp rejection-report

{
  "total_queries": 5,
  "rejected": 3,
  "accepted": 2,
  "rejection_rate": 0.6,
  "rejected_by_reason": {
    "ambiguous_question": 1,
    "generation_failed": 1,
    "not_select": 1
  }
}

(Это значение 0.6 — не целевое число, к которому нужно стремиться; это то, что фактически получилось из реального набора вопросов, заданных в этой сессии, при этом конкретном запуске. Повторный запуск init-db и повторение команд выше воспроизводит его в точности, поскольку и сид-данные, и бэкенд на правилах детерминированы.)

Точность на размеченном оценочном наборе

text-to-sql-mcp eval

Реальный вывод, бэкенд на правилах, этот сид (25 вопросов: 8 лёгких / 10 средних / 7 сложных, покрывающих требование спецификации в 20–30 вопросов):

{
  "total_questions": 25,
  "correct": 20,
  "accuracy": 0.8,
  "rejected": 6,
  "rejection_rate": 0.24,
  "by_difficulty": {
    "easy":   { "total": 8,  "correct": 8, "accuracy": 1.0 },
    "medium": { "total": 10, "correct": 8, "accuracy": 0.8 },
    "hard":   { "total": 7,  "correct": 4, "accuracy": 0.5714 }
  },
  "by_join_heaviness": {
    "simple":     { "total": 17, "correct": 17, "accuracy": 1.0 },
    "join_heavy": { "total": 8,  "correct": 3,  "accuracy": 0.375 }
  }
}

Это 80% — не совпадение и не цифра, принимаемая на веру — это результат осознанного дизайн-решения: бэкенд на правилах распознаёт 20 из 25 вопросов и выбрасывает исключение на остальных 5 вместо того чтобы угадывать (см. _UNANSWERED_IDS в llm/rule_based.py). Оценочный стенд прогоняет каждый вопрос через реальный конвейер ask() и сравнивает фактически возвращённые строки с эталонным запросом, выполненным заново на той же базе данных, — это не поддерживаемые вручную ожидаемые числа, которые могли бы незаметно разойтись с сид-данными. Точность падает с ростом сложности (100% → 80% → 57%) и резко ниже на вопросах с большим числом JOIN (37.5% против 100% на простых) исключительно потому, что бэкенд на правилах — это таблица поиска, а не потому, что стенд или валидатор делают что-то иначе, — это именно тот честный сигнал, который требует критерий приёмки спецификации («точность измеряется и сообщается, а не просто декларируется»).

С настроенным реальным ANTHROPIC_API_KEY запросы ask()/eval вместо этого идут через AnthropicLLMClient (см. Что требует настоящего API-ключа), и точность отражала бы реальное качество произвольного NL→SQL, а не покрытие фикстурами. В этом окружении такой запуск не выполнялся, поскольку здесь не настроен ни один API-ключ, и README не заявляет для него численных значений.

Состязательная валидация — 100% отклонение, проверено двумя способами

pytest tests/test_validator_adversarial.py tests/test_service_adversarial.py -v

• tests/test_validator_adversarial.py — 29 намеренно вредоносных/некорректных SQL-строк (DROP, DELETE, UPDATE, INSERT, CREATE TABLE AS SELECT, ALTER, PRAGMA, ATTACH DATABASE, GRANT, VACUUM/REINDEX, инъекция с составными операторами через ;, load_extension/readfile/writefile, SELECT ... INTO, пустой/мусорный ввод), поданных напрямую в validate_sql() — отклонены 29/29, плюс 2 отдельных теста, точно фиксирующих обработку вторых операторов, протаскиваемых в комментариях (всего 33 тестовые функции в файле).

• tests/test_service_adversarial.py — та же гарантия на уровне ask(), через фикстурный LLM-клиент, который всегда соглашается с состязательным запросом на естественном языке, а не отказывает ему, — что доказывает: валидатор блокирует выполнение, «не надеясь, что модель откажется» (собственная формулировка спецификации для этого критерия приёмки). 8/8 состязательных промптов в конечном счёте отклоняются, даже несмотря на то, что фикстурная модель никогда не говорит «нет».

_ALLOWED_ROOT_TYPES валидатора — это белый список (Select/Union/Intersect/Except), а не чёрный список опасных ключевых слов — каждый DML/DDL/административный оператор, распознаваемый sqlglot, по построению разбирается в отдельный узел AST, отсутствующий в белом списке, поэтому нет списка ключевых слов, который нужно поддерживать в актуальном состоянии, и нет способа переименовать или замаскировать деструктивный оператор так, чтобы он прошёл проверку.

Что требует настоящего API-ключа, а что работает автономно сегодня

llm/factory.py выбирает бэкенд автоматически: Anthropic, если задан ANTHROPIC_API_KEY, иначе OpenAI, если задан OPENAI_API_KEY, иначе — фолбэк на правилах; для переключения не нужно менять код. Поведение AST-валидатора идентично независимо от того, какой бэкенд сгенерировал SQL — в этом и состоит смысл архитектуры (вывод модели — это предложение, которому никогда не доверяют), и именно поэтому состязательному набору и общим тестам валидатора вообще не нужен LLM-бэкенд для доказательства свойства безопасности.

Обрабатываемые граничные случаи

• Неоднозначный вопрос на естественном языке (§9): вместо того чтобы молча выбирать одну интерпретацию, промпт предписывает LLM отвечать AMBIGUOUS: <clarifying question> вместо SQL; service.ask() обнаруживает это и возвращает rejected: true с уточнением в качестве причины, никогда не выполняя догадку. См. test_ask_handles_ambiguous_question_without_silently_guessing.

• Вопросы с большим числом JOIN отслеживаются отдельно (§9): EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — см. реальное соотношение 100% против 37.5% выше.

• Промпт-инъекция, замаскированная под SELECT (§9): проверка корневого типа по белому списку означает, что DROP/DELETE/и т.д. не пройдут, как бы промпт об этом ни просил; см. состязательные наборы выше.

• Полное сканирование очень большой таблицы (§9): _find_unfiltered_large_table_scan помечает SELECT без WHERE по таблице, превышающей порог по количеству строк (по умолчанию 500), и чей результат иным образом не ограничен (нет GROUP BY, нет LIMIT, не чисто агрегатный). Последнее условие — осознанное уточнение сверх буквальной формулировки спецификации: без него обычные отчётные запросы вроде SELECT COUNT(*) FROM permits отклонялись бы вместе с действительно дорогими SELECT * FROM permits, что сделало бы валидатор бесполезным для реальной отчётности. См. test_pure_aggregate_on_large_table_passes_without_where против test_unfiltered_select_star_on_large_table_is_rejected.

• Несоответствия схеме (галлюцинированные имена таблиц/колонок): проверяются структурно на основе интроспектированной схемы, а не сопоставлением строк с жёстко заданным списком — test_unknown_table_is_rejected, test_unknown_column_on_known_table_is_rejected. Проверка существования колонок намеренно консервативна (пропускает неоднозначные неквалифицированные ссылки в нескольких объединённых таблицах), чтобы избежать ложноположительных отклонений легитимных запросов — см. docstring в _find_unknown_column.

MCP-сервер

text-to-sql-mcp serve

Запускает сервер через stdio. Направьте на него любой MCP-клиент (например, добавьте его в конфигурацию Claude Desktop или управляйте им через ClientSession из Python SDK mcp). Протестировано end-to-end в tests/test_mcp_server.py через mcp.shared.memory.create_connected_server_and_client_session — реальный ClientSession, общающийся с реальным сервером FastMCP через транспорт в памяти, вызывающий list_tools() и call_tool(...) точно так же, как это делал бы внешний MCP-клиент, а не просто напрямую вызывающий базовые функции Python.

Тестирование

pytest

91 тест, все проходят. Разбивка:

• test_introspection.py — точность интроспекции схемы (таблицы, столбцы, количество строк, порог больших таблиц)

• test_rule_based_llm.py — покрытие детерминированного бэкенда, включая его намеренные пробелы

• test_execution.py — обеспечение режима только для чтения (эшелонированная защита), усечение по лимиту строк

• test_validator_general.py — корректные запросы проходят, привязка к схеме, логика больших таблиц: с ограничением и без

• test_validator_adversarial.py — 29 состязательных сценариев, 100% отклонение

• test_service_ask.py / test_service_adversarial.py — end-to-end ask(), включая полное состязательное доказательство для всего конвейера

• test_eval_runner.py — сам оценочный каркас (структура, разбивка по точности, обработка неоднозначностей)

• test_query_log.py — логирование + отчёт об уровне отклонений для оператора, включая реальный интеграционный тест ask()

• test_mcp_server.py — end-to-end через реальный MCP ClientSession

Конфигурация

Скопируйте .env.example в .env и заполните то, что у вас есть — у всех переменных есть рабочие значения по умолчанию:

cp .env.example .env

Риски / Открытые вопросы / Сокращения объёма

Честный отчёт о том, что не вошло, согласно разделу §13 самого ТЗ и требованию данного портфолио об инженерном суждении:

• Postgres, а не SQLite, согласно буквальной формулировке ТЗ. В этом окружении нет ни сервера Postgres, ни демона Docker. Задокументированные замена и путь обновления приведены выше; AST-валидатор и слой исполнения сознательно спроектированы независимыми от диалекта, чтобы впоследствии это не пришлось переписывать.

• Синтетический набор данных, а не выгрузка из действующего портала открытых данных. Это осознанный компромисс ради воспроизводимости в офлайн-режиме и обхода вопроса лицензирования, который само ТЗ отмечает как риск — см. посвящённый этому раздел выше.

• Бэкенд на основе правил — это поисковая таблица фикстур, а не универсальная модель. Это явное и намеренное решение, обусловленное ограничениями окружения данного портфолио (здесь не настроено ни одного LLM API-ключа) — настоящие бэкенды Anthropic/OpenAI существуют, полностью реализованы и используют тот же путь валидации/исполнения; просто в этом окружении их ни разу не запускали с боевым API-ключом, поэтому никакая точность живой генерации не заявляется.

• Проверка существования столбцов выполняется по принципу best-effort, а не исчерпывающе. Она намеренно пропускает неоднозначные неквалифицированные ссылки на столбцы в многотабличных JOIN, чтобы не рисковать ложноположительными отклонениями, — это задокументировано в docstring функции _find_unknown_column. Проверка существования таблиц (более ценная защита от выдуманных таблиц) такими оговорками не ограничена.

• Никакого кэширования результатов запросов / пула соединений. Каждый вызов ask() открывает новое read-only подключение к SQLite. На таком масштабе это нормально (однофайловая демонстрационная БД); перед продакшеном с высоким QPS этому потребуется внимание.

• Обнаружение неоднозначности зависит от того, следует ли LLM-бэкенд соглашению AMBIGUOUS:. Бэкенд на основе правил реализует его для своего единственного намеренно неоднозначного вопроса из фикстур; при реальном вызове Anthropic/OpenAI модели предписывается следовать тому же соглашению через общий системный промпт (llm/prompt.py), но это кооперация на уровне промпта, а не независимо обеспечиваемая валидатором (обнаружение неоднозначностей в общем виде не относится к тому, что AST-проверка способна верифицировать).

• Эвристика ограниченного результата для MISSING_WHERE_LARGE_TABLE — это уточнение, выходящее за буквальную формулировку ТЗ, не столько ограничение, сколько инженерное решение, которое стоит отметить: она считает GROUP BY, LIMIT и чисто агрегатные проекции освобождёнными от проверки отсутствия WHERE. Обоснование и два теста, фиксирующих поведение по обе стороны границы, см. в разделе Пограничные случаи.

Лицензия

MIT — см. LICENSE.