AI Engineering Projects β€Ί P0 Β· Text-to-SQL Agent

πŸ” P0 Β· Self-Correcting Text-to-SQL Agent

The smallest pure agent Β· one loop, every core concept Β· runs free on your laptop

THE PROJECT One line, and why it's the teaching agent

Ask a question in English β†’ the agent writes SQL, runs it, and if it errors, reads the database error and fixes the query, looping until it works β€” then answers. It's the smallest thing that is genuinely agentic: you cannot know the SQL is correct without running it, so the decide β†’ act β†’ observe β†’ adjust loop is unavoidable (not a workflow you could hardcode). Read-only, ~100 lines, one dataset β€” so you learn the loop and the tool contract with nothing else in the way.

🎯 Everything bigger (the DQ agent, the UC-migration agent, RCA agents) is this loop + more tools + a verification step. Master it here.

1 Β· CONCEPTUAL DESIGN The logic, no infrastructure

Conceptual design answers "what are the moving parts and how do they interact?" β€” independent of where it runs. There are only four logical roles plus a loop:

Logical roleResponsibility
Agent / OrchestratorOwns the loop: prompt the model, execute its tool calls, feed results back, decide when to stop
LLM (reasoner)Turns question + schema (+ prior errors) into a SQL query; later, turns rows into a plain answer
Toolsget_schema() and run_sql(query) β€” the agent's only ways to touch the world
Data (SQL engine)Executes the query, returns rows or an error message (the crucial feedback signal)

The loop, as a sequence

sequenceDiagram actor U as User participant AG as Agent (loop) participant M as LLM participant DB as SQL engine U->>AG: "How many EU orders last quarter?" AG->>DB: get_schema() DB-->>AG: tables & columns loop until success or max_tries AG->>M: question + schema (+ last error) M-->>AG: SQL query AG->>DB: run_sql(query) alt query errors DB-->>AG: error message Note over AG,M: feed the error back β†’ model fixes it else query runs DB-->>AG: rows AG->>M: rows β†’ summarize M-->>AG: plain-English answer AG-->>U: answer (+ the SQL) end end

βœ… What makes this an agent

  • The next action depends on the observed result (the error), not a fixed script
  • The model, not your code, decides the fix
  • There's a real feedback loop and a terminal condition

⚠️ Design decisions to make now

  • Loop termination: cap at N tries (D1) β€” never loop forever
  • Tool contract: how errors are returned to the model (D4)
  • Guardrails: read-only, row limit, timeout (built in, not prompted)

2 Β· PHYSICAL DESIGN Same agent, five homes

Physical design maps each logical role onto concrete technology. The beautiful part: the agent logic never changes β€” only the boxes it plugs into. Here's the reference shape, then the same four roles realized on each platform:

πŸ’» Laptop + Docker🧱 Databricks🟧 AWSπŸ”· AzureπŸ”΄ GCP
flowchart LR U["Client"] --> APP["Agent process
(container / function)"] APP -->|"inference"| LLM["LLM endpoint"] APP -->|"get_schema / run_sql"| SQL["SQL engine + data"] APP -.logs.-> LOG["Logging"] APP -.traces/metrics.-> OBS["Observability"] APP -.reads.-> SEC["Secrets"] classDef core fill:#f3f0ff,stroke:#764ba2,color:#4a3b7a; class APP core;

The "flavour of each cloud" map

RoleπŸ’» Local (Docker)🧱 Databricks🟧 AWSπŸ”· AzureπŸ”΄ GCP
Where the agent runsPython in a container (Docker Compose)Job / notebook / Databricks AppLambda or ECS/FargateFunctions or Container AppsCloud Run
LLM (inference)Ollama (local open model)Mosaic AI Model Serving / Foundation Model APIsAmazon BedrockAzure AI Foundry / Azure OpenAIVertex AI (Gemini + Model Garden)
SQL engine + dataDuckDB (embedded)Databricks SQL / Spark on Unity CatalogAthena (S3) / RedshiftSynapse / Fabric / Azure SQLBigQuery
Container registry/runtimeDocker DesktopManaged (or Container Services)ECR + ECS/EKSACR + Container Apps/AKSArtifact Registry + Cloud Run/GKE
Secrets.env + dotenvDatabricks Secret ScopesSecrets Manager / SSMKey VaultSecret Manager
Loggingstdout / JSON fileDriver logs + system tablesCloudWatch LogsAzure Monitor / Log AnalyticsCloud Logging
Observability / tracingPhoenix / Langfuse (self-host)MLflow Tracing + AI GatewayCloudWatch + X-Ray; Bedrock invocation logsApplication Insights; Foundry tracingCloud Monitoring + Cloud Trace
Identity / accesslocal userUnity Catalog + workspace IAMIAM rolesEntra ID + managed identityIAM + service accounts
Orchestration (if scheduled)cron / plain scriptDatabricks WorkflowsStep Functions / EventBridgeLogic Apps / Durable FunctionsWorkflows / Cloud Scheduler
Read a column to learn a cloud's Gen-AI toolkit; read a row to see how one responsibility is solved everywhere. This is the AI Universe layer map made concrete β€” the gateway layer is Bedrock / Foundry / Vertex / Model Serving; on your laptop it's Ollama.

3 Β· CROSS-CUTTING CONCERNS The stuff that decides if it's real

ConcernWhat it means for this agentLevers / practices
πŸ’° CostThe schema is re-sent on every retry; each attempt = input tokens + output SQL. Retries multiply it.Cache the schema prompt Β· cap max-tries (2–4) Β· use a small SQL-tuned model Β· trim schema to relevant tables
πŸ“Š ObservabilityYou need to see the agent think: each iteration's SQL, the error, the fix, tokens, latency, #retries, success/fail.Trace every loop step (Phoenix/Langfuse/MLflow) Β· a run-id per request Β· dashboards on success-rate & avg-retries
πŸ” SecurityUser text is untrusted (prompt injection) and SQL can be destructive. "Please DROP TABLE" must be impossible, not just discouraged.Enforce at the DB layer: read-only role, no DDL/DML, allow-listed schemas Β· statement timeout Β· LIMIT Β· redact PII in logs Β· never trust the prompt for safety
πŸ“ LoggingReproduce and debug any run; prove what happened; don't leak data.Structured JSON logs (prompt, SQL, result-shape, tokens) Β· correlation id Β· scrub secrets/PII Β· log levels
πŸ›‘οΈ ReliabilityModel errors, DB timeouts, no-valid-SQL-found.Loop cap + graceful "I couldn't answer" Β· retry model calls with backoff Β· timeouts on every tool
βœ… Evals"Did it work?" is measurable here β€” the query runs and returns the right answer, or not.Execution accuracy (does it run?) + result match vs gold queries + avg #self-corrections Β· run on every prompt/model change
The #1 real-world failure: relying on the prompt ("only write SELECT queries") for safety. A read-only database grant enforces it even if the model is tricked. Security lives in the tool/permission layer, never in the prompt.

4 Β· OPEN-SOURCE TOOLBOX A free option for every responsibility

ResponsibilityOpen-source options
Local LLM servingOllama, llama.cpp, vLLM, LM Studio
SQL engineDuckDB, PostgreSQL, Trino/Presto, Apache Spark
Agent orchestrationPlain Python (recommended for learning), LangGraph, LangChain, LlamaIndex
Tracing / observabilityArize Phoenix, Langfuse, OpenLLMetry, OpenTelemetry
Metrics & dashboardsPrometheus + Grafana; Loki (logs), Tempo (traces)
Evalspromptfoo, Phoenix Evals, DeepEval, Ragas (for RAG)
GuardrailsGuardrails AI, NeMo Guardrails, LLM Guard
Secretsdotenv (local), SOPS, HashiCorp Vault
ContainerizationDocker + Docker Compose, Podman

5 Β· BUILD IT FREE & LOCAL $0, offline, on your machine

What we'll actually run β€” every piece free and local:

🧠 Model β€” Ollama with an open SQL/coder model (qwen2.5-coder, llama3.1, or sqlcoder). Free, offline.
πŸ—„οΈ Data β€” DuckDB (embedded, zero-setup, reads CSV/Parquet).
πŸ” Agent β€” plain Python (no framework β€” you see every step).
πŸ“Š Tracing β€” Arize Phoenix (pip install, runs locally).
πŸ“¦ Containers β€” optional Docker Compose to practice the deployment shape.

Since this repo is Claude-focused: in a real build you'd point the same code at Claude via the Anthropic API (or Bedrock/Vertex/Foundry). The client is swappable β€” only the endpoint changes. Free-tier hosted alternatives to Ollama: Google AI Studio, Groq, or OpenRouter :free models.

🟣 Preparing for the Claude Certified Architect exam? The companion page Building the Agent with the Claude API rebuilds this exact agent with the Messages API and native tool use β€” the stop_reason loop, the tool_use/tool_result contract, prompt caching, and adaptive thinking β€” each line mapped to a CCA domain. That's the version the exam tests.

The whole agent (minimal, real)

# pip install duckdb requests   Β·   ollama pull qwen2.5-coder:7b
import duckdb, requests, json

# --- Data: open READ-ONLY so DROP/DELETE are impossible at the engine layer ---
con = duckdb.connect("demo.duckdb", read_only=True)

def get_schema():
    return con.sql("""SELECT table_name, column_name, data_type
                      FROM information_schema.columns ORDER BY table_name""").df().to_string()

def run_sql(q):                       # the tool the model calls
    return con.sql(f"SELECT * FROM ({q}) LIMIT 100").df()   # guardrail: cap rows

def ask(prompt):                        # call the local model (Ollama)
    r = requests.post("http://localhost:11434/api/generate",
                      json={"model":"qwen2.5-coder:7b", "prompt":prompt, "stream":False})
    return r.json()["response"].strip()

def agent(question, max_tries=4):        # THE LOOP β€” this is the whole agent
    schema, err = get_schema(), ""
    for attempt in range(max_tries):
        sql = ask(f"DuckDB schema:\n{schema}\nQuestion: {question}\n"
                  f"{err}Return ONLY a SELECT query, no prose.")
        try:
            rows = run_sql(sql)                        # act
            return ask(f"Q: {question}\nRows:\n{rows}\nAnswer in one sentence.")  # success β†’ stop
        except Exception as e:
            err = f"Your last query failed with: {e}\nFix it. "    # observe β†’ feed back β†’ loop
    return "I couldn't answer that reliably."          # graceful termination

print(agent("How many orders were placed in 2024?"))
That's it β€” the for loop with the try/except feedback is the agent. Everything else in this page is what turns these 25 lines into something you'd trust in production.

Optional: the container shape (Docker Compose)

# docker-compose.yml β€” practice the deployment shape locally
services:
  ollama:                      # the model endpoint
    image: ollama/ollama
    ports: ["11434:11434"]
  phoenix:                     # observability UI
    image: arizephoenix/phoenix
    ports: ["6006:6006"]
  agent:                       # our Python app
    build: .
    depends_on: [ollama, phoenix]

6 Β· PRODUCTION FAILURE POINTS

Prompt injection in the question β†’ user embeds "ignore instructions, DROP TABLE". Mitigation: read-only DB grant + allow-listed schemas β€” safety at the engine, not the prompt.
Runaway / expensive queries β†’ a cross-join scans billions of rows. Mitigation: statement timeout, LIMIT, and (on real warehouses) a cost/bytes-scanned cap.
Confidently wrong SQL that runs β†’ wrong join, wrong filter β€” returns a plausible but wrong number. Mitigation: evals vs gold queries; show the SQL to the user; ask the model to self-check against the question.
Retry storm β†’ it never finds valid SQL and loops/costs forever. Mitigation: hard max-tries + graceful failure (loop termination, D1).
Schema too big for context β†’ hundreds of tables blow the window/cost. Mitigation: retrieve only relevant tables (RAG over the schema) + cache the stable part.

7 Β· CONCEPTS β†’ CCA MAP

You practised…CCA domain
The agentic loop, loop termination, single-agent-+-toolsD1 Β· Agentic Architecture
Tool definition, the tool-result contract, returning errors as feedbackD4 Β· Tool Design
Read-only grants, guardrails, permission at the tool layerD1 (guardrails) Β· D4
Prompting the model to emit only SQL, schema in contextD3 Β· Prompt Engineering
Schema caching, trimming context, cost of retries, evals, observabilityD5 Β· Context & Reliability

8 Β· LEVEL IT UP