Status
Prototype, in development. Not deployed, no production users. The agent loop, the SQL guardrail, the locked-down RPC, row-level security (proven with seeded test users) and the metric library work locally. The reports library, admin panel, eval suite and pilot deployment are not built yet. Everything below describes what exists in the code today.
The Problem
Operations leaders wanted to ask the warehouse questions in plain English (think "how many people are on leave today" or "task completion rate for the new project this month") and get a trustworthy number back without filing a ticket or learning SQL. The existing dashboards were rigid, and the prior generation of this tool (a FastAPI + Plotly service) generated SQL freely with no real access boundary.
The hard parts were never SQL generation. Modern models write SQL fluently. The hard parts were: keeping a helpful-but-imperfect model from touching anything it shouldn't, stopping it from hallucinating column names against a 100+ view warehouse, and, the one that actually matters for a multi-team org, making sure a workforce-scoped user physically cannot read field-ops rows, even if the model tries. That last requirement is what drove the rewrite.
Architecture
The system is a manual Claude tool-use loop (lib/agent/index.ts) wired to a defended Postgres. Retrieval is scoped to the signed-in user's domains; the model chooses between a deterministic metric template and ad-hoc SQL; every read flows through the user's JWT so row-level security holds at the database. Next.js 16 (App Router, RSC, Turbopack), TypeScript end-to-end, Supabase (Postgres + Google OAuth + RLS + pgvector), @anthropic-ai/sdk.
The loop runs at most 8 iterations against claude-sonnet-4-6 with a prompt-cached system prompt and 4096 max output tokens, accumulating per-turn token usage and cost. It exposes five tools (lib/agent/tools/index.ts): run_sql (ad-hoc), get_metric (run a defined metric's canonical SQL template), describe_view (column lookup from agent.schema_metadata so the model confirms real column names instead of guessing), clarify (stop and ask the user to disambiguate), and finalize_answer (emit the structured response card the UI renders directly). The loop short-circuits the moment the model calls clarify or finalize_answer; otherwise tool results are fed back as the next user turn. It's a hand-rolled loop rather than the SDK runner specifically so each iteration can be audit-logged and short-circuited.
It's a multi-turn chat product. Sessions and turns persist to agent.chat_sessions / agent.chat_messages, and every turn writes one row to agent.query_audit_log (rendered SQL, row count, latency, model, tokens, cost). The audit write is the only place the service-role client is used; every read that serves a user request goes through the JWT'd client.
Key Decisions
Defense in depth: three real, independent layers, all grounded in the DB. This is the headline change from the Gen-1 app.
- A keyword/regex SQL guardrail (
lib/agent/guardrails/sql-allowlist.ts), the last line before SQL hits the wire. SELECT/WITH-only, multi-statement blocking, ~40 forbidden top-level keywords, forbidden functions (pg_sleep,dblink,lo_export, …), a schema allowlist (analytics,data_staging,reference) plus an explicit denylist, and auto-injection ofLIMIT 1000when the query lacks a top-level limit. It strips comments and string literals first so the keyword scan can't be fooled by aDROPinside a quoted string. - A locked-down RPC.
agent.execute_user_query(text)runsSECURITY INVOKER(so RLS applies as the caller), withstatement_timeout = 30sandsearch_path = pg_catalogto kill runaway queries and schema-shadowing tricks. - Row-Level Security keyed to the user's JWT. Per-view policies call
agent.current_user_can_see(domain), which checks the signed-in user'sdata_scope.domains. A workforce-scoped user simply gets zero field-ops rows back. The boundary is the database, not the application. Reads never use service-role.
The guardrail isn't there because the model is adversarial; it's there because an LLM is imperfect and will occasionally be helpfully wrong. RLS is there for when the guardrail isn't enough. It's the trust boundary that survives any application bug.
Defined-metric templates over free SQL where a metric exists. A semantic layer (lib/semantic-layer/: metric definitions, business rules, glossary, schema metadata) backs a library of 37 vetted metrics across 6 domains (workforce, field ops, finance, quality, compliance, packages). Each carries a canonical SQL formula, synonyms, caveats, and source views. When a question maps to a known metric, get_metric renders the agreed formula with parameters, so "attendance rate" always means the same thing, regardless of how the model would have improvised it.
describe_view to stop column hallucination. Rather than dump the whole catalog into context, the model calls describe_view to pull just the columns it needs from agent.schema_metadata (896 catalogued column rows) before writing ad-hoc SQL. This was a direct fix for the model thrashing on column-name guesses.
Code Samples
The guardrail's core sanitizes first, then enforces SELECT-only, the schema denylist, and a forced LIMIT (trimmed from lib/agent/guardrails/sql-allowlist.ts):
export function validateAndPrepareSql(
rawSql: string,
opts: { rowLimit?: number } = {}
): ValidatedSql {
const rowLimit = opts.rowLimit ?? DEFAULT_ROW_LIMIT; // 1000
const sql = rawSql.trim();
if (!sql) throw new SqlGuardrailError("Empty SQL", "empty");
// Blank out comments + string/dollar-quoted literals so keyword
// scanning can't be fooled by a DROP inside a quoted string.
const sanitized = stripCommentsAndStrings(sql);
// 1) No multiple statements
if (countMultipleStatements(sanitized) > 0)
throw new SqlGuardrailError("Multiple statements are not allowed", "multiple_statements");
// 2) Must start SELECT or WITH (and a WITH cannot smuggle DML)
const head = firstKeyword(sanitized);
if (head !== "SELECT" && head !== "WITH")
throw new SqlGuardrailError(`Top-level statement must be SELECT or WITH, got ${head}`, "non_select");
if (head === "WITH" && /\b(INSERT|UPDATE|DELETE|MERGE)\b/i.test(sanitized))
throw new SqlGuardrailError("WITH … INSERT/UPDATE/DELETE/MERGE is not allowed", "wcte_dml");
// 3+4) ~40 forbidden keywords (INSERT, DROP, GRANT, SET, COPY, …) and
// forbidden functions (pg_sleep, dblink, lo_export, …)
for (const kw of FORBIDDEN_TOP_LEVEL_KEYWORDS)
if (new RegExp(`\\b${kw}\\b`, "i").test(sanitized))
throw new SqlGuardrailError(`Forbidden keyword: ${kw}`, "forbidden_keyword");
// 5) Schema allowlist: reject denied schemas (public, auth, pg_catalog,
// agent, …) and unknown schema-qualified refs that aren't aliases.
const denied = findSchemaReferences(sanitized)
.find((r) => DENIED_SCHEMAS.includes(r.schema));
if (denied)
throw new SqlGuardrailError(`Schema not allowed: ${denied.schema}.${denied.relation}`, "schema_denied");
// 6) Force a bound if the model didn't write one
let finalSql = sql.replace(/;+\s*$/, "");
let limitInjected = false;
if (!hasTopLevelLimit(stripCommentsAndStrings(finalSql))) {
finalSql = `${finalSql}\nLIMIT ${rowLimit}`;
limitInjected = true;
}
return { sql: finalSql, limitInjected };
}
The RPC that actually runs the vetted SQL, the layer where RLS and the timeout bite (supabase/migrations/…_execute_user_query_rpc.sql):
CREATE OR REPLACE FUNCTION agent.execute_user_query(p_sql text)
RETURNS TABLE(row_data jsonb)
LANGUAGE plpgsql
SECURITY INVOKER -- runs as the caller, so RLS applies
SET statement_timeout = '30s' -- hard wall on runaway queries
SET search_path = pg_catalog -- no schema-shadowing surprises
AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT to_jsonb(t) FROM (' || p_sql || ') t';
END;
$$;
REVOKE EXECUTE ON FUNCTION agent.execute_user_query(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION agent.execute_user_query(text) TO authenticated, service_role;
Metrics
These are the verifiable facts about the work prototype (TypeScript/Next.js), not performance claims:
- 37 defined metrics across 6 domains: 10 in the V1 seed, 27 more added in Phase-2 migrations (workforce, field ops, finance, quality, compliance, packages), each with a canonical SQL formula, synonyms, caveats, and source views.
- 22-case SQL-guardrail test suite (
scripts/test-guardrails.ts) covers happy-path SELECT/WITH/subquery/alias cases plus rejections for DROP/INSERT/UPDATE/DELETE/TRUNCATE/SET, multiple statements, six denied schemas,pg_sleep, andWITH … DELETE. - 896-row semantic layer of per-column metadata (
agent.schema_metadata) backingdescribe_view. - Bounded execution: agent loop capped at 8 iterations; queries forced to
LIMIT 1000and a 30s statement timeout. - Per-turn observability: model, tokens (incl. cache read/creation), cost in USD, latency, rendered SQL, and row count logged to
agent.query_audit_logon every question.
The public reference build is a separate Python implementation of the same design, written for the Claude Certified Architect capstone. It carries its own suite, which runs in CI: 316 offline tests (no network, no Docker), 177 of them SQL-guard cases across two dialects, covering DROP, UPDATE, multi-statement input, CTE-wrapped DML, CTE name shadowing, comment tricks, non-allowlisted and schema-qualified tables, file and sleep functions, and LIMIT injection and clamping. Those numbers belong to that repository, not to the prototype above: the two share the design, not the code.
What I Learned
- The access boundary belongs in the database, not the agent. The Gen-1 app trusted a SQL validator. The validator is still here, but it's now the outermost of three layers, and the one I trust least. RLS keyed to the user's JWT means a scope mismatch returns zero rows by construction; no amount of clever prompting gets a workforce user into field-ops data.
- Defined metrics beat clever generation. Letting the model improvise SQL for "attendance rate" gave subtly different answers run to run. A
get_metrictemplate guarantees the agreed formula every time; the model's job shrinks to picking the metric and its parameters. - Hallucinated columns, not hallucinated logic, were the real failure mode. Giving the model a
describe_viewtool over catalogued column metadata, instead of dumping the schema or letting it guess, was the single biggest reliability win for ad-hoc questions. - A manual tool loop earns its keep when you need observability. Hand-rolling the loop (rather than the SDK runner) is what makes per-iteration audit logging, token accounting, and clarify/finalize short-circuiting possible.
Links
This is the TypeScript/Next.js rewrite of a first-generation FastAPI + Plotly app: same product surface, with a database-grounded trust boundary underneath. The employer-side code is private.
Public reference build: a small public DARA-style reporting agent, built as my Claude Certified Architect capstone. It uses a single-agent loop, typed MCP tools for read-only data access, schema-validated output and a human-escalation policy. It is early and built module by module.