SQL AI agents in production

A readable, tested reference implementation of a text-to-SQL agent built for production: grounded context, layered query validation, a debugger agent, model fallbacks, and structured observability. It is the companion repository for the Agents in production section of SQL-Ally, and it runs over the same payments dataset as the app, so answers match exactly.

Everything runs offline by default, so you can follow every chapter without an API key. Record a real model once (sql-agent record) and every lesson replays its actual answers and mistakes; anything not recorded falls back to a clearly-labelled demo model. Add an Anthropic, OpenAI or Gemini key and --live uses the real model directly. Every script prints which source answered.

Introduction — build a SQL AI agent for production

A demo text-to-SQL agent is one prompt and one execute(). A production one has to answer harder questions:

• What does "last month" mean, and who decided? → chapter 2
• What stops a prompt injection from reaching DROP TABLE? → chapter 3
• What happens when the model times out, or writes SQL that doesn't run? → chapter 4
• How do you know it's working at 3 a.m.? → chapter 5

  question
     │
     ▼
  ① context      dates, definitions, entities resolved by code
  ② prompt       schema + context + skills + memory
  ③ generate     model call through a fallback chain
  ④ validate     syntax → statement → constructs → schema → sensitive → constraints → cost
       └─ fixable failure ──► debugger agent ──► back to ④
  ⑤ execute      read-only connection, timeout, row cap
       └─ SQL error ─────────► debugger agent ──► back to ④
  ⑥ log          one JSON event per step, keyed by run_id

Quick start

# with uv (https://docs.astral.sh/uv/)
uv sync --all-extras
uv run python chapter_1/03_prepare_and_ingest.py      # generate + load the data
uv run sql-agent ask "What is the decline rate by network?" --trace
uv run streamlit run app/streamlit_app.py             # chat + monitoring dashboard
uv run pytest                                         # ~95 tests, no network needed

# optional: record a real model once, then every lesson replays it offline
cp .env.example .env                                  # add one API key
uv run --env-file .env sql-agent record

Can't run Python right now? Every lesson's output is captured in docs/outputs/ (regenerate with uv run python scripts/capture_outputs.py).

Or open it in GitHub Codespaces: PostgreSQL with a read-only role and the data are set up for you.

Chapters

Chapter	Lessons
1. SQL AI agent architecture and Python settings	overview · environment setup · prepare and ingest data · Python environment · Codespaces
2. Build context-rich SQL AI agents	prompt templates · LangChain chains · resolving ambiguity · injecting deterministic context · conversational memory · skills
3. Implementing multiple query validation layers	identifying risks · validation layers · implementing a layer · SQL safety constraints
4. Error handling and fallback methods	error handler · debugger agent · fallback models · testing framework
5. Monitoring and logs	observability · logging system · monitoring dashboard · alerting

Every lesson is one runnable script (uv run python chapter_N/NN_*.py). Add --live to use your configured providers instead of the demo model.

Repository layout

sql_ai_agent/          the package — each module maps to a chapter
  config.py            settings from llm_config.yaml + environment      (1)
  db.py, schema.py     read-only execution, catalog introspection      (1, 3)
  ingest.py            deterministic dataset, SQLite + PostgreSQL       (1)
  prompts.py           templates + JSON output contract                 (2)
  context.py           deterministic context resolver                   (2)
  memory.py, skills.py conversational memory, domain skills             (2)
  validation.py        the validation layers                            (3)
  errors.py            error taxonomy and routing                       (4)
  debugger.py          the debugger agent                               (4)
  llm/                 providers, offline models, fallback chain,       (4)
                       record/replay of real model calls
  evaluation.py        golden-set evaluation                            (4)
  observability/       JSON logging, metrics, alerts                    (5)
  agent.py             the orchestrator that ties it together
chapter_1 … chapter_5/ one script per lesson + a chapter README
skills/                domain knowledge as reviewed Markdown files
evals/golden.yaml      the golden set
evals/recording_set.yaml  questions recorded against a real model
recordings/            recorded real model calls, runs and event logs
docs/outputs/          captured output of every lesson script
postgresql/            schema and the SELECT-only agent role
app/streamlit_app.py   chat UI + monitoring dashboard
tests/                 unit, integration, Streamlit and PostgreSQL tests

Configuration

llm_config.yaml holds behaviour (provider order, timeouts, row caps, debug attempts); the environment holds secrets and per-deployment overrides:

Variable	Purpose
ANTHROPIC_API_KEY · OPENAI_API_KEY · GEMINI_API_KEY	enable a provider; missing ones are skipped
SQL_AGENT_PROVIDER	force one provider, e.g. mock
SQL_AGENT_DATABASE_URL / DATABASE_URL	sqlite:///… or postgresql://…
SQL_AGENT_LOG_PATH	where run events are written

Model names in llm_config.yaml are defaults — change them to whatever your account has access to.

Safety model

Model output is treated as untrusted input and stopped at three independent levels: validation layers (with precise reasons), a read-only time-limited connection, and — on PostgreSQL — a role that can only SELECT. The tests bypass the first level on purpose to prove the other two hold.

Notes

• The chapter structure follows the outline of a LinkedIn Learning course on production SQL AI agents. All code, data and text in this repository are original and independent; it is not affiliated with or endorsed by that course or LinkedIn.
• The offline demo model answers a fixed set of payments questions from templates and says so in every answer. It exists so the architecture is runnable for free, not as a text-to-SQL model. Real behaviour comes from recordings (sql-agent record): a replayed answer is only used for the exact prompt it was recorded against.

MIT licensed.