Video summary

NL2SQL Agent Architecture: The Design Pattern Nobody Teaches

Main summary

Key takeaways

Technology

NL2SQL agent: What problem it solves

  • Users send natural-language analytics/requests (e.g., healthcare claims/policy questions) to an external backend system containing many relational tables (potentially 100+ tables with complex PK/FK relationships).
  • The goal of the NL-to-SQL agent is to:
    1. Understand the intent/purpose of the user’s NL query.
    2. Use that understanding to generate a correct SQL query.
    3. Execute SQL via tools against the backend.
    4. Convert the SQL results (rows/context) into a natural-language response for the UI.

Proposed “design pattern nobody teaches” (high-level architecture)

The system is split into layers/components:

  1. External system / database (data plane)

    • Holds the actual tables/schema and receives SQL execution.
    • The AI typically does not have direct DB access.

  2. MCP layer (tool access)

    • Uses an MCP-like tool server concept.
    • Exposes tools such as “Run Query” that accept SQL and execute it in the external system, returning query results / row context.

  3. AI agent workflow (control plane)

    • Orchestrates end-to-end reasoning steps:
      • intent classification
      • section selection in the schema knowledge
      • context construction
      • SQL generation + validation loop
      • query execution
      • response generation

  4. Knowledge layer / schema representation

    • The agent needs schema details to generate SQL (table names, columns, PK/FK relationships).
    • To avoid dumping a huge schema into the LLM’s limited context window and to handle schema changes dynamically, maintain schema knowledge outside the LLM prompt via:
      • Wiki/OKF-style structured knowledge base (MD-file based; mentions Google’s OKF/Open Knowledge Format concept)
      • Graph database approach (e.g., nodes for tables/columns, edges for relationships using graph DBs such as Neo4j, Orient, Obsidian—mentioned as examples)
    • A key requirement: schema knowledge must be kept up to date.

  5. Data input pipeline / synchronization pipeline

    • A module that syncs backend schema changes (new tables/columns/relationships) into the knowledge layer.
    • Proposed as an event-driven or process pipeline to keep knowledge current.

  6. (Optional) History layer

    • Stores conversation history to support follow-up questions in chat-style interactions.

Agent workflow steps (detailed)

The workflow is designed as sequential steps managed by a central orchestrator/supervisor:

  1. FastAPI layer / endpoint

    • Provides an API endpoint (e.g., chat) that triggers the agent workflow.

  2. Intermediary intent agent

    • Classifies the NL input into types such as:
      • greetings/chit-chat
      • domain questions (claims/policies)
      • follow-up questions / additional domain questions

  3. Direct response agent (for non-domain requests)

    • For greetings/chitchat, responds directly without generating SQL or calling tools.

  4. Orchestrator (supervisor)

    • Controls the domain-query path.

  5. Section selection agent

    • Determines which “section” of the knowledge base to use (e.g., claims vs policy vs member), based on user intent.
    • Necessary because schema knowledge is organized into sections.

  6. Context constructor agent

    • Uses the selected schema “section” to build minimal relevant context for the LLM (instead of sending everything).

  7. SQL generator agent

    • Generates SQL from NL + constructed context.
    • Notes that LLMs can hallucinate/produce incorrect SQL.

  8. SQL validator agent + feedback loop

    • Checks generated SQL correctness against context/system rules.
    • Uses an accuracy threshold concept (example mentioned: ~85%).
    • If below threshold:
      • validator returns feedback/root cause
      • generator regenerates
      • repeats until threshold met or retry limit exceeded
    • Includes retry limit / break condition to prevent infinite loops.
    • If still failing after retries: an error handler agent produces a generic error response to the UI.

  9. Query executor

    • Once validated, passes SQL to the MCP “Run Query” tool.
    • Retrieves row results/context.

  10. Response generator agent

    • Converts row results + system prompt/rules into the final natural-language UI response.

Key technical emphasis

  • Schema/context management is central: SQL generation depends on accurate schema information.
  • Avoid large schema dumps in a single system prompt due to:
    • LLM context window limits
    • dynamic schema updates requiring redeployments
  • Use a knowledge base (wiki/OKF or graph DB) plus a synchronization pipeline to keep schema knowledge current.
  • Use SQL validation with a retry/feedback loop to reduce hallucinations and ensure SQL correctness.
  • Use MCP tools as the controlled execution boundary between the AI agent and the database.

Main speakers/sources

  • Speaker: the single instructor/author of the video (referred to as “I’ve been teaching…”; no specific name provided in the subtitles).
  • Source concepts/papers mentioned:
    • Google’s OKF (Open Knowledge Format) and related wiki/LM-wiki ideas
    • Graph DB examples: Neo4j, Orient, Obsidian (mentioned as potential knowledge graph stores)

Original video