Lab 09 · Guardrailed NL-to-SQL with an eval scorecard

Rohit's CFO saw a text-to-SQL demo and wants "ask the database anything" by quarter-end. The demo was real; so is the published finding that raw schemas yield execution accuracy in the coin-flip range while curated semantic layers push it toward the nineties. The difference between demo and deployment is exactly what this lab builds: a scoped role that makes the worst case boring, a curated view layer that makes the common case correct, and an eval scorecard that tells you — with a number — whether the thing is safe to widen.

Time & tier

~120 minutes hands-on · Core tier (maps to module M5 · Governed AI Access — NL-to-SQL, RAG & MCP).

Setup

Local Postgres (Lab 01 container), new database nl2sql, AI chat as the SQL generator. The schema is deliberately trap-rich — every trap mirrors a real reporting schema:

create table customers (
  customer_id bigint primary key,
  name        text not null,
  tier        text not null,          -- 'basic' | 'plus' | 'enterprise'
  deleted_at  timestamptz             -- soft delete; NULL = live
);
create table orders (
  order_id     bigint primary key,
  customer_id  bigint references customers,
  gross_amount numeric(12,2) not null, -- includes 18% GST
  net_amount   numeric(12,2) not null, -- revenue figure the business uses
  status       text not null,          -- 'placed'|'shipped'|'delivered'|'cancelled'
  placed_at    timestamptz not null
);
create table returns (
  return_id  bigint primary key,
  order_id   bigint references orders,
  refund_amt numeric(12,2) not null,
  reason     text not null
);
insert into customers
select g, 'Cust '||g, (array['basic','plus','enterprise'])[1+g%3],
       case when g % 20 = 0 then now() - interval '30 days' end
from generate_series(1, 400) g;
insert into orders
select g, 1 + g % 400,
       round((random()*11800)::numeric,2),
       round((random()*10000)::numeric,2),
       (array['placed','shipped','delivered','delivered','cancelled'])[1+g%5],
       now() - (g % 400) * interval '1 day'
from generate_series(1, 20000) g;
insert into returns
select g, g*7 % 20000 + 1, round((random()*2000)::numeric,2),
       (array['damaged','wrong-item','quality'])[1+g%3]
from generate_series(1, 800) g;
analyze;

The guardrail role — access design first, accuracy second:

create role nl2sql_ro login password 'ask' connection limit 5;
grant connect on database nl2sql to nl2sql_ro;
grant usage on schema public to nl2sql_ro;
alter role nl2sql_ro set statement_timeout = '3s';
alter role nl2sql_ro set work_mem = '16MB';
-- note: NO table grants yet — the view layer will receive them in step 4
grant select on all tables in schema public to nl2sql_ro;  -- baseline phase only

Guided steps

1. 1.

   Author the 25-question eval set with expected results. eval.csv: id,question,expected — 20 answerable questions whose expected values you compute yourself with hand-written SQL (that SQL is your answer key, save it as truth.sql), plus 5 seeded ambiguous/unanswerable questions whose expected value is the literal REFUSE. Required coverage among the 20: total revenue last quarter (truth uses net_amount, excludes cancelled); order counts by status; top-5 customers by revenue (excludes soft-deleted customers); refund rate; average order value; monthly revenue trend. The 5 refusal probes: "What was revenue in the north region?" (no region exists), "How many active customers do we have?" ("active" undefined), "What is our profit margin?" (no cost data), "Show revenue for last fiscal year" (fiscal calendar undefined), "Who is our best customer?" ("best" undefined). Getting expected values from your own SQL before any AI runs is the step most teams skip — skip it and the scorecard measures agreement, not accuracy.

2. 2.

   Baseline run — raw schema, no help. For each of the 25 questions, prompt the assistant with only the question plus the raw DDL: "Write one PostgreSQL query. If the question is ambiguous or unanswerable from this schema, say REFUSE and why." Execute every generated query as nl2sql_ro (psql -U nl2sql_ro). Score each: correct (result matches expected), wrong (runs, differs), error, or refused. Expected baseline: roughly half the answerable questions wrong — watch specifically for gross_amount used as revenue, cancelled orders included, soft-deleted customers counted, and confident SQL for all five refusal probes. Record everything in scorecard.csv.

3. 3.

   Read the failures like a DBA, not a prompt engineer. Group the baseline misses: (a) semantic traps the schema invites (gross vs net, status filters, soft deletes), (b) genuinely ambiguous business terms, (c) SQL that timed out or misjoined. The fix for (a) and (b) is not a better prompt — it is a curated layer that makes wrong SQL hard to write. That analysis, three sentences per group, goes in the scorecard notes.

4. 4.

   Build the semantic view layer. Views that encode the business definitions, with comments the model will read:

   Copy

   create schema semantic;
   create view semantic.v_revenue as
     select o.order_id, o.customer_id, o.net_amount as revenue,
            o.placed_at::date as order_date, o.status
     from orders o
     where o.status <> 'cancelled';
   comment on view semantic.v_revenue is
     'Revenue = net_amount (GST-exclusive), cancelled orders excluded. Use for ALL revenue questions.';
   create view semantic.v_customers as
     select customer_id, name, tier from customers where deleted_at is null;
   comment on view semantic.v_customers is 'Live customers only; soft-deleted excluded.';
   create view semantic.v_returns as
     select r.return_id, r.order_id, r.refund_amt, r.reason, o.customer_id
     from returns r join orders o using (order_id);
   revoke select on all tables in schema public from nl2sql_ro;
   grant usage on schema semantic to nl2sql_ro;
   grant select on all tables in schema semantic to nl2sql_ro;
   

   The revoke matters: after curation, the NL-to-SQL role cannot reach the raw tables, so gross-vs-net is no longer a mistake the model can make. Least privilege as accuracy engineering.

5. 5.

   Curated run — same 25 questions. Prompt now carries the semantic schema DDL plus the view comments plus a refusal instruction with teeth: "Answer only from schema semantic. If a term in the question has no defined mapping (region, fiscal year, active, best, profit), refuse and name the missing definition." Execute as nl2sql_ro again, score again. Expected: answerable accuracy jumps well above baseline (the traps are structurally gone), and 4–5 of 5 probes refuse with the missing definition named.

6. 6.

   Prove the role guardrails with hostile inputs. Three probes, run as nl2sql_ro and recorded verbatim: a runaway query (select * from semantic.v_revenue a, semantic.v_revenue b) — must die at 3s to statement_timeout; a write attempt (insert, update, or drop against anything) — must fail on permissions; a raw-table read (select * from public.orders) — must fail post-revoke. The prompt never enforces any of this; the role does. Paste all three refusals into the scorecard file.

7. 7.

   Produce the scorecard and the go/no-go. scorecard.md: before/after table (correct / wrong / error / refused × 25), the refusal-probe results, the three guardrail proofs, and a one-paragraph recommendation: what accuracy threshold and refusal behavior you require before giving this to the CFO's team, and which questions stay human-answered regardless (anything touching undefined business terms until the business defines them — in the view layer, not in a prompt).

Verify

• •
  truth.sql exists and produced every expected value before any AI query ran.
• •
  scorecard.csv has all 50 executions (25 × 2 phases) scored in four categories.
• •
  Curated-phase accuracy on answerable questions exceeds baseline, and at least 4 of 5 seeded probes refuse with the missing definition named.
• •
  All three role-guardrail probes show the correct failure (timeout, permission denied, permission denied), captured verbatim.
• •
  The raw-table revoke is in place — nl2sql_ro cannot select from public.orders.

Self-grading rubric (pass = 8/10): eval set authored with independent truth (0–2); baseline run complete and honestly scored (0–2); failure analysis groups misses by cause (0–1); view layer encodes definitions and revokes raw access (0–2); curated run shows measured improvement plus correct refusals (0–2); guardrails proven at the role, not the prompt (0–1).