Most business users cannot write a JOIN, and most analysts do not want to write the same “total sales by region” query for the fortieth time. Here is the layered architecture that lets a Teams user type a plain-language question and get back a governed, read-only answer straight from MySQL.
The system under test: a Teams chat surface, a Copilot Agent that turns intent into SQL, a custom API gate, and MySQL underneath five layers, one question in, one answer out.
Key takeaways
- Natural language to SQL breaks down in four ways: ambiguous intent, schema knowledge the model lacks, unsafe or unbounded queries, and multi-turn context that a stateless chatbot cannot retain.
- The API layer between the Copilot Agent and the database is not a formality; it is where authentication, tenant isolation, and read-only enforcement actually live.
- Microsoft Teams is the delivery surface, not the intelligence. Keep the reasoning in the Copilot Agent and API layer, so it is portable to Slack or a web app later.
- Never let generated SQL touch the database directly. Route every query through a request handler that validates, authenticates, and logs before execution.
- Use the agent for intent understanding and query generation, not for deciding what a user is allowed to see; authorization belongs in the API layer, not the prompt.
- Rent the orchestration plumbing (Copilot Studio, Teams SDK), build the query-safety logic that encodes your governance rules.
Every data team has heard some version of the same request: “Can I just ask the dashboard a question instead of filing a ticket?” Self-service analytics has promised this for a decade, and most attempts have quietly died the first time a VP typed something the tool did not expect. At Bitcot, we build AI agent and data-integration systems for enterprise teams, and natural-language-to-SQL is one use case that looks trivial in a demo but becomes serious the moment real users, real schemas, and real permissions show up. This is a field guide to the architecture that survives that transition: Microsoft Teams as the conversational front door, a Copilot Agent that understands intent and drafts SQL, a custom API layer that enforces every safety rule, and MySQL underneath, never touched directly.
Why natural language to SQL is harder than it looks
Diagnosis pass
Teams reach for a chatbot demo, wire it to an LLM, and hand it database credentials. It works for the first ten questions in the pitch deck. It falls apart the moment a real user asks something ambiguous, references last week’s conversation, or asks for something the model should not be allowed to answer. Four seams cause most of the damage.
1. Ambiguity in natural language
“Show me the total sales for this month by region” sounds simple, but “this month,” “region,” and “sales” all carry assumptions. Is this month calendar or fiscal? Does region mean sales territory, shipping region, or customer billing address? A model that guesses silently produces a confident, wrong answer, which is worse than no answer at all.
2. Schema complexity the model was never trained on
Production schemas are not the tidy tables in a tutorial. They carry legacy column names, denormalized joins, soft-deleted rows, and tribal knowledge about which table is actually authoritative. A generic LLM has no idea that orders_v2 replaced orders eighteen months ago unless that context is fed to it deliberately, every time.
3. Unsafe or unbounded queries
Anything that turns natural language into executable SQL is one bad prompt away from a DELETE, a full table scan across a hundred million rows, or a query that silently joins across a tenant boundary it should never cross. If the only thing standing between a user’s phrasing and your production database is the model’s judgment, that is not a safety architecture it is a hope.
4. Multi-turn context and session state
Real conversations build on themselves: “now break that down by product line” only means something if the system remembers what “that” refers to. A stateless request-response bot loses this thread immediately, and users stop trusting a tool that forgets what they just asked.
The trap: Because the demo works, teams skip straight to connecting a model to a live database and calling it done. Against ambiguous phrasing, an unfamiliar schema, and a user who has not memorized your data dictionary, that shortcut does not just underperform; it produces wrong numbers with total confidence, which is the fastest way to lose a business user’s trust in self-service data.
The architecture: five layers between a question and an answer
Prescription pass
The durable answer is not a smarter prompt. It is a stack where each layer does one job, and where the layer closest to the database is the strictest one, not the most trusting.
Layer 1: Microsoft Teams as the conversational surface
Teams is where the user already lives, so it is the right delivery surface, but it should stay a thin one. Its job is the chat interface, maintaining conversation history and user or tenant context, and rendering results back as tables, charts, and cards that are interactive and shareable. None of the actual reasoning belongs here. Keeping Teams thin means the same intelligence layer can later sit behind Slack, a web portal, or a mobile app without a rewrite.
Layer 2: the Copilot Agent understanding, then generation
This is where natural language becomes intent, and intent becomes SQL. The agent first works out what the user actually wants: their domain, the entities involved, and the conversational context carried over from earlier turns. Only once intent is resolved does it generate a query, and that generation step should default to read-only, validate the SQL it produces, and optimize before it ever leaves the agent. A query orchestrator then packages the request, calls the API layer, and is responsible for handling errors and partial failures gracefully rather than surfacing a stack trace to a business user.
Layer 3: the custom API layer where safety actually lives
This is the layer no demo ever shows, and it is the one that matters most. A small set of endpoints, something like /executeQuery, /metadata, and /health, sit between the agent and the database. A request handler validates the incoming query, authenticates the caller against an API key or token, authorizes the specific tenant, and logs and monitors every call before anything executes. Only after all of that does a query executor actually run the SQL against MySQL, handle exceptions, and format the result set for the response. If Layer 2 is where SQL gets written, Layer 3 is where it earns the right to run.
Layer 4: MySQL as the system of record
Underneath sits the database itself: tables, views, stored procedures, and functions reached only over a secured JDBC connection from the query executor, never directly from the agent. The database does not know or care that a person asked a question in plain English three layers up; it only ever sees a validated, authenticated, read-only query.
Layer 5: monitoring and governance, running through all of it
Every hop Teams to agent, agent to API, API to database, and the JSON response back up the chain happens over HTTPS, with read-only access enforced end to end. Logging, error handling, and usage monitoring are not bolted on afterward; they are threaded through every layer so that when something does go wrong, there is a trail to follow back to the exact request that caused it.
A natural language interface is only as trustworthy as the layer the model is not allowed to talk its way around.
Building the Copilot Agent workflow the right way
Layer 2, done properly
The temptation with any LLM-adjacent project is to let the model do more than it should: generate the SQL, decide who gets to see the result, and execute it directly against production. That collapses three separate concerns understanding, authorization, and execution into one prompt, and prompts are not an access control system.
The higher-value pattern splits responsibility cleanly. The Copilot Agent owns natural language understanding and SQL generation, and nothing past that. The custom API owns authentication, tenant authorization, and query validation, and it is the only thing with database credentials. The database owns storage and execution of an already-validated, already-scoped, already read-only query. Each layer trusts the one below it exactly as much as it has verified it, not more.
This also determines where self-healing and iteration should happen. When a generated query fails validation or times out, the orchestrator should be able to retry with a refined query or ask a clarifying follow-up in Teams: “Did you mean this fiscal month or the calendar month?” rather than silently falling back to a broader, unbounded query that happens to return something.
Build it or buy it
Microsoft’s Copilot Studio and the Teams SDK give you the conversational plumbing chat interface, session management, adaptive cards for results without building a bot framework from scratch, and that plumbing is genuinely commodity: renting it is the right call. The part worth owning is everything inside the custom API layer: the validation rules, the tenant isolation logic, the read-only enforcement, and the audit trail. That logic encodes how your organization actually governs data access, and it is not something a generic connector should own on your behalf. Rent the conversational shell. Build the gate that decides what the shell is allowed to ask for.
Rule of thumb: if a layer’s job is “let a person type a message and see a response,” buy it. If a layer’s job is “decide whether this specific request is allowed to touch this specific data,” build it, and keep it as your own intellectual property.
What a production-ready NL-to-SQL copilot looks like
Pulling it together, a system a team can actually trust in production shares a few traits. Intent resolution happens before SQL generation, so ambiguity gets caught early rather than compiled into a wrong query. Every generated query is read-only by default and validated before it is sent anywhere. A dedicated API layer authenticates, authorizes by tenant, and logs every call, and it is the only component with database credentials. MySQL only ever executes what has already been checked. And end-to-end, the whole path Teams to agent, agent to API, API to database and back runs over HTTPS with a monitoring trail a governance team would recognize.
None of these layers is exotic on its own. The engineering is in refusing to let the model’s fluency substitute for an access control layer you could have built once and reused everywhere.
Frequently Asked Questions
Why not just connect an LLM directly to the database?
Because nothing then stands between a user’s phrasing and production data except the model’s judgment. A dedicated API layer between the agent and MySQL is what actually enforces read-only access, authentication, and tenant isolation the model should never hold database credentials.
Does the Copilot Agent decide what data a user is allowed to see?
It shouldn’t. Authorization belongs in the custom API layer, which validates the caller’s identity and tenant before a query ever executes. The agent’s job is understanding intent and drafting SQL, not deciding access.
How does this handle multi-turn conversations, like "now break that down by product"?
Session and context management lives in the Teams layer and is passed through to the Copilot Agent, which maintains conversation history so follow-up questions resolve against the prior query rather than starting from zero.
Can this architecture move to Slack or a web app instead of Teams?
Yes, as long as Teams is kept thin. If the reasoning stays in the Copilot Agent and API layer rather than leaking into Teams-specific logic, swapping the front end is a delivery-layer change, not a rebuild.
