Chapter 21 · Capstone 3: Data Q&A BI Agent

Chapter goals: Synthesize tools, Workflow, and RAG knowledge to build a "natural-language-to-chart" BI Agent — Text-to-SQL generation, read-only safety gates, chart output, and hallucination-resistant retry, all in one pipeline.

21.1 Requirements and Architecture

The two things data teams最害怕: waiting for data, writing SQL. This BI Agent lets them ask in Chinese:

"What was the sales amount in South China last month? How does it compare to last month? Draw a bar chart."

The Agent returns: SQL execution result + chart + a human结论. Architecture:

User question (natural language)
   │
   ▼
┌────────────────────┐
│ Step1 generateSql  │  Text-to-SQL Agent
│ (inject table DDL) │  generates SELECT only
└───────┬────────────┘
        ▼
┌────────────────────┐   SQL invalid / errors
│ Step2 validate     │──────────┐
│ (read-only gate)   │          │ Feedback error and retry once
└───────┬────────────┘◀─────────┘
        ▼
┌────────────────────┐   Empty results → guide rephrase
│ Step3 executeQuery │
│ (libsql read-only) │
└───────┬────────────┘
        ▼
┌────────────────────┐
│ Step4 visualize    │  chart tool produces ECharts config
└───────┬────────────┘
        ▼
┌────────────────────┐
│ Step5 summarize    │  conclusion解读 Agent (human-language summary)
└────────────────────┘

The key difference from Chapter 19's customer service Agent: this pipeline path is fully predictable, so it uses Workflow orchestration rather than a single large Agent — each step's schema is强约束; if SQL is wrong, you can pinpoint和 retry without报废 the entire chain.

21.2 Schema Injection: Letting the Model Know the Table Shape

Text-to-SQL accuracy depends on the model's understanding of the table structure. Two injection approaches:

// src/mastra/bi/schema.ts —— Maintain core table DDL as constants
export const SALES_DDL = `
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,        -- Region: 华东/华南/华北/西南
  amount REAL NOT NULL,        -- Order amount (RMB)
  status TEXT NOT NULL,        -- paid/refunded/pending
  created_at TEXT NOT NULL     -- ISO8601, e.g. '2026-08-01T10:00:00Z'
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,      -- Category: 数码/家居/服饰
  price REAL NOT NULL
);
`;

Small repos (a few tables): splice DDL directly into instructions. Dozens of tables: switch to RAG — make each table's DDL and field descriptions into docs, retrieve relevant tables, then inject (复用 Chapter 12's approach). This project has only two tables, so we选 the former:

// src/mastra/agents/sql-agent.ts —— Text-to-SQL expert
import { Agent } from '@mastra/core/agent';
import { SALES_DDL } from '../bi/schema';

export const sqlAgent = new Agent({
  id: 'sql-generator',
  name: 'SqlGenerator',
  model: 'openai/gpt-5-mini', // Text-to-SQL is a成熟 task; small model suffices
  instructions: `
You are a SQL expert. Generate a SQLite query based on the user's question.

Table structure:
${SALES_DDL}

Rules:
1. Output only one SELECT statement; no data-modifying statements allowed;
2. Time filtering uses created_at string comparison (ISO8601 is directly comparable);
3. Must include LIMIT; default LIMIT 100;
4. Output SQL only; no explanation, no markdown code fence.
`,
});

21.3 queryDatabase Tool: Read-Only Is the Bottom Line

The #1 red line for BI scenarios: never modify production data. Three layers ofdefense:

1. Database uses a read-only account (application层兜底);
2. Statement whitelist validation;
3. Mandatory LIMIT.

// src/mastra/tools/query-database.ts
import { createTool } from '@mastra/core/tools';
import { createClient } from '@libsql/client';
import { z } from 'zod';

[truncated - full content in source file]

21.4 Visualization and Summary Steps

Step 4's chart tool produces an ECharts配置 from the query results; Step 5's summary agent interprets the numbers in human language. Both are simple agents with clear instructions — the real sophistication is in theWorkflow orchestration and safety gates.

21.5 Hallucination-Resistant Retry

If the SQL validation fails (e.g., syntax error, forbidden keyword), the workflow retries once with the error反馈 injected into the sqlAgent's context. This is the "schema强约束 + 定点重试" pattern that makes Workflow superior to a single Agent for predictable pipelines.

21.6 Chapter Summary

• Pipeline architecture: generateSql → validate → executeQuery → visualize → summarize;
• Text-to-SQL uses a small model (gpt-5-mini) with DDL injected into instructions;
• Read-only safety: three-layer defense (read-only account, whitelist, LIMIT);
• Workflow over single Agent: schema强约束 enables pinpoint retry without全链报废;
• Hallucination resistance: error feedback loop for SQL retry.

🧪 随堂测验

点击你认为正确的选项。答错时会展示正确答案与原因解析。

1. Why use a Workflow instead of a single Agent for this BI capstone?

2. What are the three layers of read-only defense in the BI Agent?

3. How is hallucination resistance achieved in the SQL generation step?

4. When should you switch from injecting DDL directly to using RAG for schema injection?

🛠️ Hands-On Exercises

1. Build the full BI pipeline: sqlAgent → validate step → queryDatabase tool → chart tool → summarize agent; verify end-to-end with a sample question.
2. Test the read-only safety gates: try to inject an INSERT statement and verify it is blocked at the whitelist layer.
3. Simulate a SQL syntax error; verify the workflow retries once with the error feedback injected.

Capstone 3 complete. Course complete!