ChatBI MVP Architecture
Overview
Build a ChatBI system: user asks questions in natural language → system generates SQL → executes → displays interactive charts with AI insights.
Architecture: NL → S2SQL (semantic SQL / MQL) → Physical SQL. LLM generates S2SQL using business terms (bizName), a deterministic Translator converts to physical SQL. LLM never touches physical table/column names.
Tech stack assumed: React (frontend) + Python/FastAPI (backend). Architecture is language-agnostic.
Five Core Layers
User: "最近7天各分区播放量怎么样"
│
▼
┌─ Layer 3: RAG ────────────────┐ Trie + Embedding recall
│ "播放量" → views (metric) │ Identify schema elements
│ "分区" → category (dim) │ in user query
│ "最近7天" → DateConf{-7d} │
└───────────────┬───────────────┘
│
▼
┌─ Layer 1: NL → S2SQL → SQL ──┐ 5-stage pipeline
│ MAPPING → PARSING → │ LLM generates S2SQL (bizName)
│ CORRECTING → TRANSLATING │ Translator → physical SQL
│ → EXECUTE │
└───────────────┬───────────────┘
│
┌───────┴───────┐
▼ ▼
┌─ Layer 2 ───┐ ┌─ Layer 5 ──────────┐
│ Multi-turn │ │ Attribution │
│ Context save │ │ LLM summary + YoY │
│ + LLM rewrite│ │ + drill-down recs │
└──────────────┘ └─────────────────────┘
│ │
└───────┬───────┘
▼
┌─ Layer 4: Visualization ──────┐
│ Auto chart type → ECharts │
│ User can toggle chart/table │
│ Drill-down → re-query │
└───────────────────────────────┘
File Index
This skill is split into focused files. Read in order, or jump to what you need.
Core Architecture (read first)
| File |
Contents |
When to read |
data-models.md |
All shared POJOs: SemanticSchema, SchemaMapInfo, SemanticParseInfo, QueryResult |
Always start here — these connect every layer |
nl2sql-pipeline.md |
Layer 1 in detail: 5-stage pipeline, prompt template, self-consistency, corrector chain, S2SQL→physical translator |
Core of the system |
wiring.md |
How all layers connect: full request flow, plugin registration, DB tables |
When you need to see the big picture |
Supporting Layers
| File |
Contents |
When to read |
layers-2-3-5.md |
Multi-turn dialogue (Layer 2), RAG knowledge base (Layer 3), Attribution analysis (Layer 5) |
After understanding Layer 1 |
visualization.md |
Chart auto-classification, ECharts configs, chart type toggle, interaction patterns (Layer 4) |
Frontend implementation |
Implementation Guides
| File |
Contents |
When to read |
data-generation.md |
How to generate demo data with Faker + Pandas + SQLite, semantic model YAML, few-shot exemplars |
Before you start coding |
frontend-interaction.md |
Complete frontend state machine, component tree, API calls, drill-down/metric-switch/date-filter flows |
Frontend implementation |
bilibili-example.md |
Full end-to-end: B站 creator analytics, from data generation to chart display, trace one query |
When you want a concrete example |
ui-design-system.md |
CSS variables, color palette, typography, component recipes, shadows, ECharts theme |
When building UI |
Planning
| File |
Contents |
When to read |
mvp-plan.md |
4-week MVP scope, SuperSonic source code reference, common pitfalls, Python+React tech stack recommendations |
Project planning |
Getting Started
First time: Read files in this order:
data-models.md — understand the data structuresnl2sql-pipeline.md — understand the core enginebilibili-example.md — see a concrete example end-to-endwiring.md — see how everything connects
Starting to code:
5. data-generation.md — generate your demo data
6. mvp-plan.md — follow the 4-week plan
7. ui-design-system.md — copy CSS variables into your project
Implementing specific layers:
layers-2-3-5.md — for multi-turn, RAG, or attributionvisualization.md — for chartsfrontend-interaction.md — for frontend state machine and components
Core Principles
- Semantic layer isolation: LLM generates S2SQL (bizName), NEVER physical SQL. The Translator is deterministic.
- Plugin chain architecture: Each layer is a chain of plugins registered in config, executed sequentially. Add/remove plugins without touching core code.
- Dual strategy everywhere: Rule-based (fast, deterministic) first → LLM-based (flexible) fallback. Applies to parsing, correction, and mapping.
- Full context persistence: Save the entire
SemanticParseInfo (not just query text) after each turn. Include history SQL in multi-turn rewrite prompt.
- User can always override: Chart type auto-selected but user can toggle. Filters auto-detected but user can adjust.
Reference Implementation
SuperSonic is the reference architecture. See mvp-plan.md for a source code file map to key classes.
1---2name: chatbi-mvp3description: Use when building a ChatBI (conversational BI) MVP from scratch, need to understand the 5 core capabilities (NL2SQL, multi-turn dialogue, RAG knowledge base, data visualization, intelligent attribution), or want to reference SuperSonic's architecture to guide implementation4---56# ChatBI MVP Architecture78## Overview910Build a ChatBI system: user asks questions in natural language → system generates SQL → executes → displays interactive charts with AI insights.1112**Architecture:** NL → S2SQL (semantic SQL / MQL) → Physical SQL. LLM generates S2SQL using business terms (bizName), a deterministic Translator converts to physical SQL. LLM never touches physical table/column names.1314**Tech stack assumed:** React (frontend) + Python/FastAPI (backend). Architecture is language-agnostic.1516## Five Core Layers1718```19User: "最近7天各分区播放量怎么样"20 │21 ▼22┌─ Layer 3: RAG ────────────────┐ Trie + Embedding recall23│ "播放量" → views (metric) │ Identify schema elements24│ "分区" → category (dim) │ in user query25│ "最近7天" → DateConf{-7d} │26└───────────────┬───────────────┘27 │28 ▼29┌─ Layer 1: NL → S2SQL → SQL ──┐ 5-stage pipeline30│ MAPPING → PARSING → │ LLM generates S2SQL (bizName)31│ CORRECTING → TRANSLATING │ Translator → physical SQL32│ → EXECUTE │33└───────────────┬───────────────┘34 │35 ┌───────┴───────┐36 ▼ ▼37┌─ Layer 2 ───┐ ┌─ Layer 5 ──────────┐38│ Multi-turn │ │ Attribution │39│ Context save │ │ LLM summary + YoY │40│ + LLM rewrite│ │ + drill-down recs │41└──────────────┘ └─────────────────────┘42 │ │43 └───────┬───────┘44 ▼45┌─ Layer 4: Visualization ──────┐46│ Auto chart type → ECharts │47│ User can toggle chart/table │48│ Drill-down → re-query │49└───────────────────────────────┘50```5152## File Index5354This skill is split into focused files. Read in order, or jump to what you need.5556### Core Architecture (read first)5758| File | Contents | When to read |59|------|----------|-------------|60| `data-models.md` | All shared POJOs: SemanticSchema, SchemaMapInfo, SemanticParseInfo, QueryResult | Always start here — these connect every layer |61| `nl2sql-pipeline.md` | Layer 1 in detail: 5-stage pipeline, prompt template, self-consistency, corrector chain, S2SQL→physical translator | Core of the system |62| `wiring.md` | How all layers connect: full request flow, plugin registration, DB tables | When you need to see the big picture |6364### Supporting Layers6566| File | Contents | When to read |67|------|----------|-------------|68| `layers-2-3-5.md` | Multi-turn dialogue (Layer 2), RAG knowledge base (Layer 3), Attribution analysis (Layer 5) | After understanding Layer 1 |69| `visualization.md` | Chart auto-classification, ECharts configs, chart type toggle, interaction patterns (Layer 4) | Frontend implementation |7071### Implementation Guides7273| File | Contents | When to read |74|------|----------|-------------|75| `data-generation.md` | How to generate demo data with Faker + Pandas + SQLite, semantic model YAML, few-shot exemplars | Before you start coding |76| `frontend-interaction.md` | Complete frontend state machine, component tree, API calls, drill-down/metric-switch/date-filter flows | Frontend implementation |77| `bilibili-example.md` | Full end-to-end: B站 creator analytics, from data generation to chart display, trace one query | When you want a concrete example |78| `ui-design-system.md` | CSS variables, color palette, typography, component recipes, shadows, ECharts theme | When building UI |7980### Planning8182| File | Contents | When to read |83|------|----------|-------------|84| `mvp-plan.md` | 4-week MVP scope, SuperSonic source code reference, common pitfalls, Python+React tech stack recommendations | Project planning |8586## Getting Started8788**First time:** Read files in this order:891. `data-models.md` — understand the data structures902. `nl2sql-pipeline.md` — understand the core engine913. `bilibili-example.md` — see a concrete example end-to-end924. `wiring.md` — see how everything connects9394**Starting to code:**955. `data-generation.md` — generate your demo data966. `mvp-plan.md` — follow the 4-week plan977. `ui-design-system.md` — copy CSS variables into your project9899**Implementing specific layers:**100- `layers-2-3-5.md` — for multi-turn, RAG, or attribution101- `visualization.md` — for charts102- `frontend-interaction.md` — for frontend state machine and components103104## Core Principles1051061. **Semantic layer isolation**: LLM generates S2SQL (bizName), NEVER physical SQL. The Translator is deterministic.1072. **Plugin chain architecture**: Each layer is a chain of plugins registered in config, executed sequentially. Add/remove plugins without touching core code.1083. **Dual strategy everywhere**: Rule-based (fast, deterministic) first → LLM-based (flexible) fallback. Applies to parsing, correction, and mapping.1094. **Full context persistence**: Save the entire `SemanticParseInfo` (not just query text) after each turn. Include history SQL in multi-turn rewrite prompt.1105. **User can always override**: Chart type auto-selected but user can toggle. Filters auto-detected but user can adjust.111112## Reference Implementation113114[SuperSonic](https://github.com/tencentmusic/supersonic) is the reference architecture. See `mvp-plan.md` for a source code file map to key classes.
yugef3h