Capstone 4: An AI-Powered Internal Tool

Every company has a queue of small data questions that never get answered. "How many urgent tickets did enterprise customers open last week?" is a two-minute query for whoever knows the schema, and a two-day wait for everyone else. An AI assistant that answers in plain English sounds like the obvious fix, until it confidently reports a number computed from the wrong table, or a support lead asks about revenue they were never supposed to see.

In this capstone you'll build Ask Ops, an internal analytics assistant for Deskmate staff. People type a question and get back a chart, a one-line statement of exactly what was computed, and the SQL behind it. The model never writes SQL and never sees a single row of data. It picks from a catalog of metrics your team defined, your code turns that pick into a parameterized query against a read-only database role, and every question lands in an audit log.

What you'll build#

Ask Ops is a page in Deskmate's internal admin app (Next.js 16, Postgres). Ask "Median response time by team, last quarter" and you see:

• The interpretation: the model's one-line restatement ("Median minutes to first agent reply, split by support team, last quarter"), shown with the exact date range code resolved (2026-07-01 to 2026-10-01). This is the trust anchor. If it's wrong, the user knows before they look at the chart.
• A chart chosen by code: a single number, a bar chart, or a line over time.
• The SQL, collapsed, for anyone who wants to check it.
• A clarifying question when the question is ambiguous ("How are we doing?"), and a plain explanation when the catalog can't answer it.

Here's the request path:

Browser (staff only, behind SSO)
  │  POST /api/ask { question }
  ▼
Route handler ─── getStaff() ──► 401 if not staff
  │
  ├─► Planner (Claude Sonnet 5.5, structured output)
  │     sees: instructions + metric catalog (cached) + today's date + question
  │     returns: QueryPlan { kind, metric, groupBy, timeGrain, timeRange, filters }
  │
  ├─► compilePlan()   code: role check, value allow-lists, date math, SQL from catalog
  ├─► runQuery()      read-only role, read-only transaction, 5s timeout, row cap
  ├─► chooseChart()   code: stat | bar | line | table
  └─► writeAudit()    who, question, plan, SQL, rows, tokens, latency, status

It assembles parts from Structured Outputs in Practice, Generative UI, and Logging and Tracing LLM Calls; go back to those for the reasoning behind each piece.

Prerequisites: a Next.js app with an existing staff login, a Postgres database you can create views and roles in (a local copy with seed data is ideal), npm i @anthropic-ai/sdk zod postgres recharts, and ANTHROPIC_API_KEY in your environment. The code uses the @/ import alias that create-next-app sets up.