text-to-sql-mcp

Un servidor MCP que traduce preguntas en lenguaje natural a SQL sobre un esquema real no trivial de varias tablas — y nunca se fía del SQL que recibe. Cada consulta generada se analiza en un AST real (mediante sqlglot) y pasa por un validador antes de que se ejecute nada: las sentencias que no sean SELECT, la inyección de múltiples sentencias, las funciones peligrosas, las tablas/columnas alucinadas y los escaneos completos sin límite de tablas grandes son rechazados estructuralmente, no confiando en el propio juicio de un LLM.

La salida del modelo es una propuesta, no una orden. Un validador decide qué se ejecuta realmente.

Estado

Los hitos M1–M4 de la especificación están implementados y probados: introspección de esquema, generación de NL→SQL (backends reales de Anthropic/OpenAI + un respaldo offline determinista), el validador AST, el banco de pruebas de evaluación etiquetado y el envoltorio del servidor MCP. Consulte Riesgos / Preguntas abiertas para ver lo que se difirió deliberadamente.

Arquitectura

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)

Todo lo anterior está envuelto como un servidor MCP (mcp_server.py, construido sobre FastMCP del SDK oficial de Python mcp) que expone exactamente las dos herramientas del contrato de API de la especificación:

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

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

Por qué SQLite en lugar de Postgres

La especificación apunta específicamente a Postgres. Este entorno no tiene un servidor Postgres en ejecución ni un daemon de Docker, por lo que la base de datos objetivo es SQLite — una sustitución deliberada y documentada, no un descuido. La capa de introspección (introspection.py) es la única pieza que es genuinamente específica de SQLite (sqlite_master + PRAGMA table_info en lugar de information_schema); el validador, la capa de ejecución y el envoltorio MCP operan sobre el AST analizado y no saben ni les importa qué base de datos produjo el esquema. Ruta de actualización a Postgres: sustituya el sqlite3.connect(..., mode=ro) de db/connection.py por una conexión psycopg abierta contra un rol de solo lectura, reescriba las dos consultas de introspection.py contra information_schema.tables/columns, y pase dialect="postgres" a validate_sql() — sqlglot soporta ambos dialectos de forma nativa, por lo que la lógica del AST en sí no cambia.

Por qué un conjunto de datos sintético en lugar de una descarga en vivo de datos abiertos

La especificación sugiere un portal real de datos abiertos de una ciudad/gobierno. db/seed.py genera, en su lugar, un conjunto de datos municipal sintético pero realista — 12 tablas modeladas a partir de esquemas reales de permisos/inspecciones/violaciones (NYC DOB, permisos de construcción de Chicago) — de forma determinista a partir de una semilla fija, completamente sin conexión. Esto fue una compensación deliberada, no pereza: mantiene init-db reproducible con cero dependencia de red (sin CI inestable, sin límites de tasa, sin tiempo de inactividad del portal) y evita la cuestión de licencias que la propia especificación señala como riesgo (§13) antes siquiera de publicar una demo. El esquema es genuinamente no trivial según el propio estándar de la especificación: 12 tablas, claves foráneas de tres niveles de profundidad (payments → violations → properties), y una ambigüedad deliberada en los nombres de columna (status aparece en permits, licenses, violations y complaints; type en cuatro tablas diferentes) que ejercita de verdad la lógica de anclaje al esquema del validador.

Instalación

pip install -e .

Requiere Python 3.10+. Extras opcionales para los backends reales de LLM (ya instalados en los entornos de desarrollo que los tienen; solo necesarios si no los tienes):

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

Inicio rápido — demostración real contra una base de datos SQLite real

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

Una pregunta con muchos 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 },
  ...
]

Una pregunta ambigua — deliberadamente no resuelta en silencio a una única suposición (ver Casos límite):

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: ...

Prueba de que el validador, no el modelo, es lo que bloquea el SQL destructivo — esto utiliza un cliente de prueba que representa a un modelo comprometido/inyectado por prompt que siempre cumple con la solicitud destructiva:

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.

Ahora compruebe lo que ve el operador — la tasa de rechazo, reportada por separado de la precisión (ver más abajo):

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

(Ese 0.6 no es un número objetivo a alcanzar — es lo que produjo la mezcla real de preguntas formuladas en esta sesión, en esta ejecución exacta. Volver a ejecutar init-db y repetir los comandos anteriores lo reproduce exactamente, ya que tanto los datos de la semilla como el backend basado en reglas son deterministas.)

Precisión en el conjunto de evaluación etiquetado

text-to-sql-mcp eval

Salida real, backend basado en reglas, esta semilla (25 preguntas: 8 fáciles / 10 medianas / 7 difíciles, cubriendo el requisito de 20–30 preguntas de la especificación):

{
  "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 }
  }
}

Este 80% no es una coincidencia ni una afirmación tomada al pie de la letra — es producto de una decisión de diseño deliberada: el backend basado en reglas reconoce 20 de las 25 preguntas y lanza una excepción en las otras 5 en lugar de adivinar (ver _UNANSWERED_IDS de llm/rule_based.py). El banco de pruebas de evaluación ejecuta cada pregunta a través del pipeline real de ask() y compara las filas realmente devueltas contra una consulta de referencia ejecutada recién hecha contra la misma base de datos — no son números esperados mantenidos a mano que pudieran desviarse silenciosamente de los datos de la semilla. La precisión decae con la dificultad (100% → 80% → 57%) y es dramáticamente menor en preguntas con muchos JOIN (37.5% frente al 100% en las simples) puramente porque el backend basado en reglas es una tabla de consulta, no porque el banco de pruebas o el validador estén haciendo algo diferente — que es exactamente la señal honesta que pide el criterio de aceptación de la especificación ("la precisión se mide y se reporta, no simplemente se afirma").

Con una ANTHROPIC_API_KEY real configurada, ask()/eval pasan por AnthropicLLMClient en su lugar (ver Qué necesita una clave de API real) y la precisión reflejaría la calidad real de NL→SQL de tipo abierto en lugar de la cobertura de fixtures — eso no se ejecutó en este entorno, ya que aquí no hay ninguna clave de API configurada, y el README no reclama un número para ello.

Validación adversarial — 100% de rechazo, probada de dos maneras

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

• tests/test_validator_adversarial.py — 29 cadenas SQL deliberadamente maliciosas/malformadas (DROP, DELETE, UPDATE, INSERT, CREATE TABLE AS SELECT, ALTER, PRAGMA, ATTACH DATABASE, GRANT, VACUUM/REINDEX, inyección de sentencias apiladas mediante ;, load_extension/readfile/writefile, SELECT ... INTO, entrada vacía/basura) alimentadas directamente a validate_sql() — 29/29 rechazadas, más 2 pruebas dedicadas que fijan exactamente cómo se manejan las segundas sentencias ocultas en comentarios (33 funciones de prueba en total en el archivo).

• tests/test_service_adversarial.py — la misma garantía a nivel de ask(), mediante un cliente LLM de prueba que siempre cumple con un prompt adversarial en lenguaje natural en lugar de rechazarlo — demostrando que el validador es lo que bloquea la ejecución, "no esperando que el modelo se niegue" (la propia redacción de la especificación para este criterio de aceptación). 8/8 prompts adversariales terminan siendo rechazados aunque el modelo de prueba nunca dice que no.

El _ALLOWED_ROOT_TYPES del validador es una lista de permitidos (Select/Union/Intersect/Except), no una lista de bloqueo de palabras clave peligrosas — toda sentencia DML/DDL/admin que sqlglot reconoce se analiza, por construcción, a un tipo de nodo AST distinto que no está en la lista de permitidos, por lo que no hay una lista de palabras clave que mantener sincronizada y no hay forma de renombrar o disfrazar una sentencia destructiva para que pase.

Qué necesita una clave de API real vs. qué funciona de forma autónoma hoy

llm/factory.py selecciona el backend automáticamente: Anthropic si ANTHROPIC_API_KEY está configurada, si no OpenAI si OPENAI_API_KEY está configurada, si no el respaldo basado en reglas — no se necesitan cambios de código para cambiar. El comportamiento del validador AST es idéntico independientemente del backend que haya producido el SQL — ese es el punto real de la arquitectura (la salida del modelo es una propuesta, nunca se confía en ella), y es por eso que la suite adversarial y las pruebas generales del validador no necesitan ningún backend LLM para demostrar la propiedad de seguridad.

Casos límite manejados

• Pregunta en lenguaje natural ambigua (§9): en lugar de elegir silenciosamente una interpretación, el prompt instruye al LLM a responder AMBIGUOUS: <clarifying question> en lugar de SQL; service.ask() lo detecta y devuelve rejected: true con la aclaración como motivo, sin ejecutar nunca una suposición. Ver test_ask_handles_ambiguous_question_without_silently_guessing.

• Preguntas con muchos JOIN rastreadas por separado (§9): EvalQuestion.is_join_heavy + EvalReport.accuracy_by_join_heaviness() — ver la división real de 100% vs. 37.5% más arriba.

• Inyección de prompt disfrazada de SELECT (§9): la comprobación de tipo raíz basada en lista de permitidos significa que un DROP/DELETE/etc. no puede pasar sin importar cómo lo pida el prompt; ver las suites adversariales de arriba.

• Escaneo completo de tabla muy grande (§9): _find_unfiltered_large_table_scan marca un SELECT sin WHERE sobre una tabla por encima del umbral de recuento de filas (500 por defecto) y cuyo resultado no está acotado de otro modo (sin GROUP BY, sin LIMIT, no es un agregado puro). Esa última cláusula es un refinamiento deliberado más allá de la redacción literal de la especificación: sin ella, consultas de informes ordinarias como SELECT COUNT(*) FROM permits serían rechazadas junto a consultas genuinamente costosas como SELECT * FROM permits, lo que haría inútil al validador para informes reales. Ver test_pure_aggregate_on_large_table_passes_without_where vs. test_unfiltered_select_star_on_large_table_is_rejected.

• Discrepancias de esquema (nombres de tabla/columna alucinados): se comprueban estructuralmente contra el esquema introspectado, no mediante coincidencia de cadenas contra una lista fija — test_unknown_table_is_rejected, test_unknown_column_on_known_table_is_rejected. La comprobación de existencia de columnas es deliberadamente conservadora (omite referencias ambiguas no calificadas entre múltiples tablas unidas) para evitar rechazos por falso positivo de consultas legítimas — ver el docstring de _find_unknown_column.

Servidor MCP

text-to-sql-mcp serve

Ejecuta el servidor a través de stdio. Apunta cualquier cliente MCP a él (p. ej., añádelo a la configuración de Claude Desktop, o manéjalo con la ClientSession del SDK mcp de Python). Probado de extremo a extremo en tests/test_mcp_server.py mediante mcp.shared.memory.create_connected_server_and_client_session — una ClientSession real que habla con un servidor FastMCP real a través de un transporte en memoria, llamando a list_tools() y call_tool(...) exactamente como lo haría un cliente MCP externo, y no solo invocando directamente las funciones subyacentes de Python.

Testing

pytest

91 pruebas, todas pasan. Desglose:

• test_introspection.py — precisión de la introspección del esquema (tablas, columnas, recuentos de filas, umbral de tabla grande)

• test_rule_based_llm.py — cobertura del backend determinista, incluidas sus lagunas deliberadas

• test_execution.py — aplicación de solo lectura (defensa en profundidad), truncación por límite de filas

• test_validator_general.py — las consultas válidas pasan, anclaje al esquema, lógica de tabla grande acotada frente a no acotada

• test_validator_adversarial.py — suite adversarial de 29 casos, 100 % de rechazo

• test_service_ask.py / test_service_adversarial.py — ask() de extremo a extremo, incluida la prueba adversarial de todo el pipeline

• test_eval_runner.py — el propio banco de pruebas de evaluación (forma, desgloses de precisión, manejo de ambigüedad)

• test_query_log.py — registro + informe de tasa de rechazo orientado al operador, incluida una prueba de integración real de ask()

• test_mcp_server.py — de extremo a extremo mediante una ClientSession de MCP real

Configuración

Copia .env.example a .env y rellena lo que tengas: todo tiene un valor predeterminado funcional:

cp .env.example .env

Riesgos / Preguntas abiertas / Recortes de alcance

Relato honesto de lo que no se incluyó, según el §13 de la propia especificación y el mandato de criterio de ingeniería de este portafolio:

• Postgres, no SQLite, según la redacción literal de la especificación. No hay ningún servidor Postgres ni daemon de Docker disponible en este entorno. Sustitución documentada + ruta de actualización más arriba; el validador de AST y el diseño de la capa de ejecución se mantuvieron agnósticos al dialecto a propósito para que esto no sea una reescritura más adelante.

• Conjunto de datos sintético, no una extracción de un portal de datos abiertos en vivo. Compensación deliberada para la reproducibilidad sin conexión y para eludir la cuestión de licencias que la propia especificación señala como riesgo; consulte la sección dedicada más arriba.

• El backend basado en reglas es una tabla de búsqueda de fixtures, no un modelo general. Esto es explícito y por diseño según las restricciones de entorno de este portafolio (aquí no hay claves API de LLM configuradas); los backends reales de Anthropic/OpenAI existen, están completamente implementados y comparten la misma ruta de validación/ejecución; simplemente nunca se ejecutaron contra una clave API real en este entorno, por lo que no se afirma ninguna cifra de precisión de generación en vivo.

• La comprobación de existencia de columnas es de mejor esfuerzo, no exhaustiva. Omite deliberadamente las referencias ambiguas a columnas sin calificar en uniones de múltiples tablas para no arriesgar rechazos de falsos positivos; documentado en el docstring de _find_unknown_column. La comprobación de existencia de tablas (la protección de mayor valor contra tablas alucinadas) no está igualmente matizada.

• Sin caché de resultados de consultas / agrupación de conexiones. Cada ask() abre una nueva conexión SQLite de solo lectura. Adecuado a esta escala (base de datos de demostración de un solo archivo); requeriría atención antes de un uso en producción de alto QPS.

• La detección de ambigüedad depende de que el backend LLM siga la convención AMBIGUOUS:. El backend basado en reglas la implementa para su única pregunta de fixture deliberadamente ambigua; a una llamada real de Anthropic/OpenAI se le indica que siga la misma convención mediante el prompt de sistema compartido (llm/prompt.py), pero esto es cooperación a nivel de prompt, no aplicada de forma independiente por el validador (la detección de ambigüedad abierta no es algo que un comprobador de AST pueda verificar).

• La heurística de resultados acotados de MISSING_WHERE_LARGE_TABLE es un refinamiento más allá de la redacción literal de la especificación, no es exactamente una limitación, pero merece la pena señalarla como una decisión de criterio: trata GROUP BY, LIMIT y las proyecciones de agregación pura como exentas de la comprobación de falta de WHERE. Consulte la sección Casos límite para ver el razonamiento y las dos pruebas que fijan el comportamiento a cada lado de la línea.

Licencia

MIT — consulte LICENSE.