How to build a hallucination-free chatbot over a complex SaaS database (NL → SQL) that strictly follows business rules?

by

Summary

Building a hallucination-free Natural Language to SQL (NL2SQL) chatbot in a complex SaaS environment requires treating the Large Language Model (LLM) strictly as a routing and translation engine, not a decision-maker. The production-grade approach involves an intent-centric architecture where the backend exposes strictly defined “capabilities” or “query templates,” and the LLM’s role is limited to mapping user intent to these pre-vetted operations. This decouples the fluidity of natural language from the rigidity and security of database access.

Root Cause

The core risk in NL2SQL systems is the unbounded generation capability of LLMs. When an LLM is asked to generate raw SQL for a complex schema:

  • It often hallucinates tables or columns that sound plausible but do not exist.
  • It fails to inherently understand complex row-level security or permission logic embedded in the business domain.
  • It may generate syntactically correct SQL that violates business logic (e.g., selecting a “cancelled” order as “active inventory”) or performance constraints (e.g., non-indexed table scans).

Why This Happens in Real Systems

In real-world SaaS platforms, complexity is rarely linear.

  • Schema Drift: SaaS schemas evolve rapidly; an LLM trained on older documentation or without strict validation will fail immediately.
  • Implicit Context: Humans understand that a “user” in one tenant is invisible to another. An LLM treating the DB as a public knowledge base lacks this tenant isolation context unless explicitly injected, leading to data leaks.
  • Computed Fields: Business logic often lives in the application layer (e.g., calculating a “lifetime value” based on proprietary formulas), not in raw SQL SELECT statements. An LLM attempting to write SQL for this will inevitably calculate it wrong.

Real-World Impact

  • Data Exfiltration: The most critical risk. An LLM generating raw SQL might be tricked into appending UNION SELECT * FROM users or bypassing permission checks.
  • Performance Degradation: A single unoptimized query generated by an LLM can lock up tables or spike CPU usage in a multi-tenant environment, affecting the entire SaaS platform.
  • Hallucinated Analytics: Users make decisions based on chatbot output. If the bot “invents” a revenue figure because it guessed the wrong calculation for “gross margin,” the business liability is significant.

Example or Code

We avoid raw SQL generation. Instead, we map intent to a structured QueryPlan. The LLM selects the appropriate tool and fills parameters, but the backend constructs the final SQL.

Architecture Pattern: Intent Router

{
  "intent": "fetch_revenue_trend",
  "parameters": {
    "timeframe": "last_30_days",
    "group_by": "weekly"
  }
}

Backend Logic (Pseudocode)

def handle_user_query(user_query: str, tenant_id: int):
    # 1. LLM analyzes the query and maps it to a defined capability
    # The LLM is NOT allowed to write SQL.
    intent = llm_router.infer(user_query, available_capabilities)

    # 2. Validate intent against strict allow-list
    if intent.name not in ALLOWED_CAPABILITIES:
        return "I cannot perform that action."

    # 3. Backend constructs the query using parameterized templates
    # This ensures joins, where clauses, and tenant isolation are correct.
    sql = build_sql_from_template(
        template_name=intent.name, 
        params=intent.parameters, 
        tenant_id=tenant_id
    )

    # 4. Execute (Read-Only)
    return db.execute(sql)

How Senior Engineers Fix It

Senior engineers enforce a “No Arbitrary SQL” policy. The architecture relies on Capability Mapping rather than Translation.

  • Define a Schema Contract: The backend exposes a “Semantic Layer” (a dictionary of user-friendly terms mapped to specific tables/columns). The LLM strictly operates within this vocabulary.
  • Use Few-Shot Prompting with Constraints: The prompt instructs the LLM: “You are a router. You do not write SQL. You only output a JSON object selecting one of the following tools: [Tool 1, Tool 2].”
  • Middleware Validation: An intermediate software layer (written in Python/Go/Java) validates the LLM’s output JSON. If the JSON attempts to reference a column not in the allow-list, the request is blocked before execution.
  • Separate “Read” and “Write”: LLMs should strictly be restricted to Read-Only operations. Any state change (Write) must go through traditional UI/API flows or very high-confidence, hard-coded transaction templates.

Why Juniors Miss It

  • Over-reliance on Frameworks: Juniors often reach for libraries like MindsDB or generic text-to-SQL wrappers and assume they handle security/permissions out of the box. They rarely do for complex, multi-tenant SaaS logic.
  • Underestimating Hallucinations: They believe “prompt engineering” alone can prevent the LLM from making up a column name. In reality, Output Parsers and Schema Validation are mandatory safety nets.
  • Conflating Reasoning with Execution: Juniors treat the LLM as the CPU. Seniors treat the LLM as the Logic Gate. The key takeaway is that the LLM generates intent; the Backend generates code.