How I Built a Production AI Query Engine on 28 Tables — And Why I Used Both Text-to-SQL and Function Calling

Source: Dev.to

A real production system running on an affiliate‑marketing ERP — not a demo.

The operations team at an affiliate‑marketing company hit a classic wall. They had all the data (revenue by affiliate, fraud flags, campaign ROI, conversion rates by traffic source) live in a 28‑table MySQL database, but every non‑trivial question required SQL, and the people asking the questions weren’t the ones who could write it.

Goal

• No dashboards.
• No SQL.
• No waiting for a developer.

On paper it looked perfect: structured JSON, controlled schema, safe execution.

---

The First Attempt

A real query:

“Compare campaign ROI this month vs last month, by traffic source, excluding fraud flags, grouped by affiliate tier”

Needed:

• 15+ nested parameters
• Time‑comparison logic
• Multi‑table joins

The LLM hallucinated fields and produced garbage. Function calling broke quickly on analytical queries—SQL exists for a reason—so I switched to Text‑to‑SQL.

Text‑to‑SQL Benefits

• Flexible
• Accurate
• Handles complexity naturally

But letting an LLM generate raw SQL on a production database (financial data, affiliate records) is risky. “Only write SELECT” in a prompt is just a suggestion, not a guarantee. I needed something stronger.

---

Dual‑Approach Architecture

Instead of choosing one method, I built both:

• Handles analytical queries (Text‑to‑SQL)
• Handles actions (Function Calling)

✋ Function Calling (MCP) → The Hands

• Executes predefined tools only
• Requires human approval for sensitive operations

The Router

A lightweight classifier decides:

The AST Validator — The Real Safety Layer

The AST validator makes Text‑to‑SQL usable in production. Rather than trusting the LLM output, the generated SQL is parsed into an Abstract Syntax Tree (AST) and validated before execution.

• Blocks DELETE, DROP, UPDATE
• Detects multi‑statement injections (;)
• Executes validation in 30% this week” Router → Text‑to‑SQL LLM generates SQL AST Validator passes MySQL executes Results → dashboard

### Fraud Workflow
1. **n8n** detects an anomaly  
2. Risk is classified  
3. High‑risk → MCP tool triggered  
4. Telegram alert sent  
5. Human approval  
6. Affiliate suspended + logged  

> **18 workflows** run this pattern in production.

---

## Evaluation & Lessons Learned

The three‑layer validation may look over‑engineered, but in practice:

* **Regex** – fast, catches obvious patterns  
* **AST** – precise, enforces statement type and structure  
* **Allowlist** – strict, limits table access  

Each layer matters. Improving schema semantics (e.g., using `affiliate_tier` values like *Platinum/Gold/Silver/AtRisk* instead of a vague enum) dramatically boosts SQL quality.

Additional tooling:

* Automated accuracy tracking (Eval pipeline)  
* Semantic table routing (pgvector)  
* Dynamic context reduction  

---

## The Honest Take

Most “production AI” demos skip security entirely. The AST validator is:

* Not flashy  
* Not viral  

But it’s the only reason this system is safe on real data.

* **Brain** = Text‑to‑SQL + AST Validator → read‑only analytics  
* **Hands** = Function Calling (MCP) → actions + control  

It’s not about choosing one approach; it’s about routing correctly. If you’re building something similar, the hardest part isn’t generation—it’s control.

---

*I share more real‑world AI system breakdowns here:*  
[https://www.linkedin.com/in/rayane-louzazna-b7752b224/](https://www.linkedin.com/in/rayane-louzazna-b7752b224/)