Case study 02

SQL chat agent

A text-to-SQL agent that streams, can be traced, and is measured.

Role: Backend engineer
Context: Extending an existing, basic text-to-SQL chatbot
Stack: Azure AI Foundry agentsAzure OpenAIAzure API ManagementAzure DevOpsServer-Sent EventsSQL

~20k → ~7k

base system-prompt tokens, tool results not counted

~86% → ~89–90%

eval accuracy after the prompt split

80% → 91%

accuracy tracked on the golden set over iterations

Moved the schema out of the prompt and into a tool the agent calls, then proved it with evals.

Key points

Streaming. Server-Sent Events carry the answer and the agent’s tool calls; a response is saved only once it is complete.
Load balancing. Round-robin across several Foundry agents through API Management, with any agent able to continue any conversation.
Prompt split. Moved the schema and rules into a tool: base prompt ~20k → ~7k tokens (tool results excluded), accuracy ~86% → ~89–90%.
Traces. Admins can see the SQL that ran and a preview of its result; the admin check is enforced in the backend.
Prompt management. Prompts live in files and an Azure DevOps workflow publishes them to the agents.
Evaluation. ~150 golden question and SQL pairs, an LLM judge and a manual check on every run; accuracy 80% → 91%.

The full write-up behind each point follows below.

Runtime path: the backend asks API Management for an agent, streams the run to the browser, and stores the conversation and traces.

Streaming answers, saving only complete ones

Answers stream to the user over Server-Sent Events together with the agent’s tool calls, so people see progress instead of a blank screen.

A response is written to the conversation history only once it has finished. If a stream is interrupted, the user gets a clear “request was interrupted, try again later” message and no half-finished answer is stored.

Spreading load across agents

Several Foundry agents serve requests. Round-robin happens in Azure API Management, which hands out agent IDs in rotation; the backend takes the ID it is given and uses it for the request.

Conversations are not tied to one agent. Any agent can continue any conversation, so rotating between them never breaks a thread.

Cutting the prompt from ~20k to ~7k tokens

The original system prompt carried the full database schema, the metric definitions and the business rules, around 20k tokens before the user had asked anything.

I split that content up and moved it into a database. The prompt now describes one tool that takes a section name and returns only the part the agent needs. The base prompt dropped to about 7k tokens, not counting tool results, and eval accuracy went from about 86% to about 89–90%. The smaller prompt was also the more accurate one.

Traces for admins

Admins can open a trace for any answer to see the SQL the agent executed and a preview of its result. Traces are stored in the database.

The admin check is enforced in the backend, not only by hiding the button in the UI, and the backend returns only the first rows of a result, with the limit set through an environment variable.

Prompts as deployable files

Prompt text lives in a file, and an Azure DevOps workflow publishes it to the Foundry agents, so prompts are published by a pipeline instead of being edited by hand on each agent.

Measuring it with a golden set

The testing team curated about 150 golden pairs of question and SQL. A script sends each question to the live API, collects the responses and passes them to a second LLM that judges the SQL and the final answer. A tester then verifies the results manually after every run.

The suite runs on demand whenever the prompt changes. Accuracy moved from 80% to 91% across iterations, and the same suite is what showed that the prompt split improved accuracy instead of hurting it.

The evaluation loop, re-run on demand whenever the prompt changes.