Lab 08 — NL→SQL Banking Analytics (the mandatory SQL skill)

Goal. Build a natural-language → SQL feature ("ask questions about your transactions in plain language") over a banking database — covering the JD's mandatory "Strong expertise in Python and SQL" and a flagship banking GenAI use case. The real lesson is doing it safely: read-only, table-allowlisted, row-level-secured, and injection-proof — because the alternative is letting an LLM run arbitrary SQL on customer data, which is a catastrophe in a bank. Read alongside K01 §8 (function calling), K03 §12 (safe tools), and K09 §9.

Stack. SQLite (stdlib sqlite3) · a parameterized template NL→SQL generator (stand-in for an Azure OpenAI call) · a real SQL safety guard. Local fallback. Everything is offline and deterministic; the only swappable piece is the generator (AzureOpenAISqlGenerator stub shows the gpt-4o call).

SQL in 5 minutes (from zero, for this lab)

SQL is the language for querying relational data — tables of rows and columns with relationships. A bank's transactional data is relational, so SQL is unavoidable. The pieces you use here:

SELECT cols FROM table — read columns from a table. SELECT * = all columns.
WHERE cond — filter rows (amount < 0 = money out).
JOIN — combine tables on a key. transactions JOIN accounts ON transactions.account_id = accounts.id links each transaction to its account (and thus its customer).
Aggregates — SUM, COUNT, AVG, MIN, MAX collapse many rows into one number. SUM(-amount) totals spend.
GROUP BY col — aggregate per group: GROUP BY category gives a total per category.
ORDER BY col [DESC] + LIMIT n — sort and take the top n (e.g. the largest transaction).
Parameters / binding — never paste user input into SQL text; pass it as a bound value (WHERE description LIKE :merchant) so it can't change the query's meaning. This is how you prevent SQL injection.
View — a saved query you can treat like a table. We use views for row-level security (below).

The schema (db.py): customers 1—* accounts 1—* transactions. amount is signed (negative = spend).

Why NL→SQL is dangerous (and the four-layer fix)

Letting an LLM generate SQL that you then run is function calling where the tool is a database. If you trust the generated SQL, a prompt (or an injection) can read every customer's data, or DROP a table. So the app — never the model — enforces four layers (nl2sql.py):

Layer	Control	Stops
1. Read-only	reject anything that isn't a single `SELECT`	`DROP`/`UPDATE`/`DELETE`/`INSERT`
2. Allowlist	SQL may only touch per-tenant views (`my_accounts`, `my_transactions`)	reading base tables, other schemas
3. Row-level security	those views are defined for the caller's `customer_id` (from the authenticated session, cast to int)	one customer seeing another's data — even if the NL says "all customers"
4. Parameterization	user values (a merchant name) are bound, never concatenated	SQL injection

The model proposes SQL; the guard validates it, the executor scopes and runs it, and the answer is phrased from the real rows — so a number is never invented, same principle as every money fact in this track.

Run it (offline, no Azure)

pip install -r requirements.txt        # only pytest; sqlite3 is stdlib
python run.py                          # NL->SQL answers + RLS + guard + injection demo
pytest -q                              # 12 tests = success criteria

Code tour

File	What it teaches
db.py	the banking schema (customers/accounts/transactions) + deterministic seed data
nl2sql.py	the guard (read-only + allowlist), the executor (per-tenant views = RLS, bound params), the template generator, and `NL2SQLTool` (the agent-callable tool); `AzureOpenAISqlGenerator` swap stub
run.py	demo: NL→SQL answers, row-level security across two customers, the guard blocking unsafe SQL, and a neutralized injection payload
test_nl2sql.py	correctness (aggregates, joins, grouping) and all four safety layers

How it plugs into the rest

This is an agent tool (Lab 02 / Lab 07): register NL2SQLTool.answer as a function the agent can call for analytics questions ("how much did I spend on groceries last month?"). The agent routes the question, the tool returns grounded rows, and the LLM phrases the answer — with the SQL and rows captured in the audit log. In production the generator becomes a gpt-4o call; the guard and executor are unchanged, because you never trust generated SQL regardless of which model wrote it.

Talking point

"NL→SQL is function calling where the tool is a database, so the danger isn't the model — it's trusting its SQL. I make it safe with four app-enforced layers: read-only SELECT validation, an allowlist limited to per-tenant views, row-level security so the customer_id comes from the session and the views physically can't return another customer's data, and parameter binding so a merchant name can't inject. The model proposes SQL; the app validates, scopes, executes, and the answer is phrased from the real rows — never an invented number."

Resume bullet

"Built a safe NL→SQL banking-analytics tool (Azure OpenAI generates SQL; the app enforces read-only validation, a per-tenant view allowlist, row-level security from the authenticated session, and parameterized execution), exposed as an agent tool with full SQL/row audit — answering customers' transaction questions in natural language without ever trusting model-generated SQL."

AI Specialist