Building a Production-Ready AI Query Engine with Text-to-SQL and Function Calling

The author describes their experience building a production AI query engine that combines text-to-SQL and function calling approaches to enable non-technical users to ask questions in plain English and get real answers instantly.

Details

The author worked with an affiliate marketing company that had a 28-table MySQL database with valuable data, but non-technical users struggled to get answers to complex questions that required SQL. The goal was to enable these users to ask questions in plain English and get instant, accurate answers without relying on developers or dashboards. Initially, the author tried a function calling approach, but found it quickly broke down for analytical queries with complex requirements. Switching to text-to-SQL solved the flexibility and accuracy issues, but raised concerns about security risks in a production database. The solution was to combine both approaches - text-to-SQL for analytical queries (the brain) and function calling for actions (the hands), with a router pattern to determine which path to take. The real safety layer is the AST (Abstract Syntax Tree) validator, which uses a multi-step process (regex filtering, AST parsing, and a table allowlist) to ensure only valid SQL is executed, even if the language model generates something potentially malicious. For actions, the author uses a controlled MCP (function calling) layer, where the language model can only call predefined tools, and sensitive operations require human approval before execution. This pattern is used across 18 production workflows. The author shares lessons learned, including the importance of the multi-layer validation approach, the value of clear schema semantics, and ideas for future improvements like automated accuracy tracking and dynamic context reduction.