text-to-sql-mcp

自然言語の質問を、実際の非自明なマルチテーブルスキーマに対するSQLに変換するMCPサーバーです。そして、返ってきたSQLを決して信用しません。生成されたすべてのクエリは、実行前にsqlglotによって実際のASTに解析され、バリデータを通過します。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 Python SDKのFastMCP上に構築）としてラップされ、仕様のAPIコントラクトの2つのツールを正確に公開します：

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

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

PostgresではなくSQLiteを使う理由

この環境には実行中のPostgresサーバーもDockerデーモンもないため、ターゲットデータベースは代わりにSQLiteです。これは意図的で文書化された代替であり、見落としではありません。イントロスペクションレイヤー（introspection.py）だけが本当にSQLite固有です（information_schema の代わりに sqlite_master + PRAGMA table_info を使用）。バリデータ、実行レイヤー、MCPラッパーは解析されたAST上で動作し、スキーマを生成したデータベースが何であるかを知らず、気にもしません。Postgresへのアップグレードパス： db/connection.py の sqlite3.connect(..., mode=ro) を、読み取り専用ロールに対して開いた psycopg 接続に置き換え、introspection.py の2つのクエリを information_schema.tables/columns に対して書き直し、validate_sql() に dialect="postgres" を渡します。sqlglotは両方の方言をネイティブにサポートしているため、ASTロジック自体は変わりません。

ライブのオープンデータ取得ではなく合成データセットを使う理由

仕様は実際の都市/政府のオープンデータポータルを提案しています。db/seed.py は代わりに、合成だが現実的な地方自治体データセット（実際の許可/検査/違反スキーマ（NYC DOB、シカゴの建築許可）に基づく12テーブル）を、固定シードから完全にオフラインで決定的に生成します。これは意図的なトレードオフであり、怠惰ではありません。init-db をネットワーク依存ゼロで再現可能にし（不安定なCI、レート制限、ポータルのダウンタイムなし）、デモを公開する前に仕様自体がリスクとして挙げているライセンス問題（§13）を回避します。スキーマは仕様自身の基準で本当に非自明です：12テーブル、外部キーが3ホップ深く（payments → violations → properties）、意図的なカラム名の曖昧さ（status は permits、licenses、violations、complaints に現れ、type は4つの異なるテーブルに現れる）があり、バリデータのスキーマ接地ロジックを実際に鍛えます。

インストール

pip install -e .

Python 3.10+ が必要です。実際のLLMバックエンド用のオプションの追加パッケージ（それらがある開発環境にはすでにインストールされています。ない場合にのみ必要です）：

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
  }
]

結合が多い質問：

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 },
  ...
]

曖昧な質問 — 意図的に1つの推測に黙って解決されません（エッジケースを参照）：

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%は偶然でも、額面通りに受け取るべき主張でもありません — これは意図的な設計選択によって生み出されています。ルールベースのバックエンドは25問のうち20問を認識し、推測する代わりに残りの5問では例外を発生させます（llm/rule_based.pyの_UNANSWERED_IDSを参照）。評価ハーネスはすべての質問を実際のask()パイプラインに通し、実際に返された行を、同じデータベースに対して新たに実行されたゴールドクエリと比較します。シードデータから密かに乖離する可能性のある手作業で管理された期待値ではありません。精度は難易度に応じて低下し（100% → 80% → 57%）、結合が多い質問では劇的に低くなります（単純な質問では100%なのに対し37.5%）。これは純粋に、ルールベースのバックエンドがルックアップテーブルであるためであり、ハーネスやバリデータが何か異なることをしているからではありません。これはまさに、仕様の合格基準が求めている正直なシグナルです（「精度は主張されるだけでなく、測定され報告される」）。

実際のANTHROPIC_API_KEYが設定されている場合、ask()/evalは代わりにAnthropicLLMClientを経由します（実際のAPIキーを必要とするものを参照）。精度は、フィクスチャのカバレッジではなく、実際のオープンエンドなNL→SQL品質を反映するでしょう。この環境にはAPIキーが設定されていないため、これはここでは実行されておらず、READMEはその数値を主張していません。

敵対的検証 — 100%拒否、2つの方法でテスト済み

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番目の文が正確にどのように処理されるかを固定する専用テストが2つあります（ファイル内のテスト関数は合計33個）。

• tests/test_service_adversarial.py — 同じ保証をask()レベルで、敵対的な自然言語プロンプトを拒否する代わりに常に従うフィクスチャLLMクライアントを介して提供します — 実行をブロックするのはバリデータであり、「モデルが拒否することを期待する」のではないことを証明します（この合格基準に対する仕様自身の表現）。フィクスチャモデルが決してノーと言わなくても、8/8の敵対的プロンプトは依然として拒否されます。

バリデータの_ALLOWED_ROOT_TYPESは許可リスト（Select/Union/Intersect/Except）であり、危険なキーワードのブロックリストではありません。sqlglotが認識するすべてのDML/DDL/admin文は、構造上、許可リストにない明確なASTノードタイプに解析されるため、同期を保つキーワードリストはなく、破壊的な文を通過するように名前を変更したり偽装したりする方法もありません。

実際のAPIキーを必要とするもの vs. 今日スタンドアロンで動作するもの

llm/factory.pyはバックエンドを自動的に選択します：ANTHROPIC_API_KEYが設定されていればAnthropic、そうでなければOPENAI_API_KEYが設定されていればOpenAI、そうでなければルールベースのフォールバックです。切り替えにコード変更は不要です。ASTバリデータの動作は、どのバックエンドがSQLを生成したかに関係なく同一です — これがアーキテクチャの実際の要点です（モデルの出力は提案であり、決して信用されません）。そして、敵対的スイートと一般的なバリデータテストが安全性の性質を証明するためにLLMバックエンドを一切必要としない理由でもあります。

対処されたエッジケース

• 曖昧なNL質問（§9）：黙って1つの解釈を選ぶのではなく、プロンプトはLLMにSQLの代わりにAMBIGUOUS: <clarifying question>と応答するよう指示します。service.ask()はこれを検出し、理由として明確化を添えてrejected: trueを返し、推測を実行することはありません。test_ask_handles_ambiguous_question_without_silently_guessingを参照してください。

• 結合が多い質問は別途追跡（§9）：EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — 上記の実際の100%対37.5%の分割を参照してください。

• SELECTに偽装したプロンプトインジェクション（§9）：許可リストのルートタイプチェックにより、プロンプトがどのように要求してもDROP/DELETEなどは通過できません。上記の敵対的スイートを参照してください。

• 非常に大きなテーブルのフルスキャン（§9）：_find_unfiltered_large_table_scanは、行数しきい値（デフォルト500）を超えるテーブルに対するWHEREのないSELECT かつ 結果が他の方法で制限されていないもの（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。カラムの存在チェックは意図的に保守的です（複数の結合テーブルにわたる曖昧な修飾なし参照をスキップします）。正当なクエリの誤検知拒否を避けるためです — _find_unknown_columnのdocstringを参照してください。

MCPサーバー

text-to-sql-mcp serve

stdio 経由でサーバーを実行します。任意の MCP クライアントから接続できます（例: Claude Desktop の設定に追加する、または mcp Python SDK の ClientSession で操作する）。tests/test_mcp_server.py で、mcp.shared.memory.create_connected_server_and_client_session を通じてエンドツーエンドでテストされています。これは、実際の ClientSession がインメモリトランスポート上で実際の FastMCP サーバーと通信し、外部の MCP クライアントが行うのとまったく同じように list_tools() と call_tool(...) を呼び出すもので、単に内部の 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 — エンドツーエンドの ask()（パイプライン全体の敵対的証明を含む）

• test_eval_runner.py — 評価ハーネス自体（形状、精度の内訳、曖昧性の処理）

• test_query_log.py — ロギング + オペレーター向けの拒否率レポート（実際の ask() 統合テストを含む）

• test_mcp_server.py — 実際の MCP ClientSession によるエンドツーエンド

設定

.env.example を .env にコピーし、お持ちの値を入力してください。すべてに動作するデフォルトが設定されています:

cp .env.example .env

リスク / 未解決の質問 / スコープ削減

仕様の §13 とこのポートフォリオのエンジニアリング判断の指針に従い、含まれなかった内容を正直に説明します:

• 仕様の字義どおり、SQLite ではなく Postgres。 この環境では Postgres サーバーも Docker デーモンも利用できません。上記に置き換えとアップグレードパスを記載しています。AST バリデータと実行層の設計は意図的に方言非依存にしてあるため、後で書き直す必要はありません。

• ライブのオープンデータポータルからの取得ではなく、合成データセット。 オフラインでの再現性と、仕様自体がリスクとして指摘するライセンス問題を回避するための意図的なトレードオフです。上記の専用セクションを参照してください。

• ルールベースのバックエンドは、一般的なモデルではなく、フィクスチャのルックアップテーブルです。 これは、このポートフォリオの環境制約（ここでは LLM API キーが設定されていない）によるもので、明示的かつ設計上の意図です。実際の Anthropic/OpenAI バックエンドは存在し、完全に実装されており、同一のバリデータ/実行パスを共有しています。この環境で実際の API キーに対して実行されたことがないため、ライブ生成の精度数値は主張されていません。

• 列の存在チェックはベストエフォートであり、網羅的ではありません。 誤検知による拒否のリスクを避けるため、複数テーブルの JOIN にまたがる曖昧な修飾なし列参照を意図的にスキップします（_find_unknown_column の docstring に記載）。テーブルの存在チェック（幻影テーブルに対するより価値の高い防御）は同様には制限していません。

• クエリ結果のキャッシュ/接続プーリングなし。 各 ask() は新しい読み取り専用の SQLite 接続を開きます。この規模（単一ファイルのデモ DB）では問題ありません。高 QPS の本番利用の前には対応が必要です。

• 曖昧性検出は、LLM バックエンドが AMBIGUOUS: 規約に従うことに依存しています。 ルールベースのバックエンドは、意図的に曖昧にした 1 つのフィクスチャ質問に対してこの規約を実装しています。実際の Anthropic/OpenAI 呼び出しには、共有システムプロンプト（llm/prompt.py）を通じて同じ規約に従うよう指示されますが、これはプロンプトレベルの協力であり、バリデータが独立して強制するものではありません（オープンエンドな曖昧性検出は AST チェッカーが検証できるものではありません）。

• MISSING_WHERE_LARGE_TABLE の有界結果ヒューリスティックは、仕様の字義を超えた改良です。 正確には制限ではありませんが、判断が必要な点として指摘しておきます。GROUP BY、LIMIT、および純粋な集計プロジェクションを missing-WHERE チェックの対象外とみなします。その理由と、境界の両側で動作を固定する 2 つのテストについては、エッジケース セクションを参照してください。

ライセンス

MIT — LICENSE を参照してください。