What I built
A natural-language data analysis agent for CRM data — ask in plain English, get aggregated results in sub-seconds. Connects to a seeded MySQL (12k orders / 3k members) and returns live results, not mockups.
"What was last month's GMV?" → 951,680.40 CNY across 1,508 orders
"How does Q3 compare to Q2?" → 2,064 members vs 844, +144.5% YoY
Why not just Text2SQL?
| Problem | Pure Text2SQL | This project |
|---|---|---|
| Same question → different SQL each time? | ✅ Yes | ❌ Deterministic YAML config → compiled SQL |
| SQL injection risk? | ✅ Yes | ❌ Model outputs enums, parameters are ? placeholders only |
| JOIN doubling (orders × order_details)? | ✅ Model makes this mistake constantly | ❌ grain config validated at compile time |
| Column drift (VARCHAR amount SUM truncates)? | ✅ Model doesn't know | ❌ NUM() wrapper auto-injected for declared columns |
The architecture: model plans, framework compiles
The LLM never generates SQL. It outputs a structured plan:
{
"metric": "MEMBER_CONSUMPTION",
"dimensions": ["store"],
"timeRange": { "period": "prev_month" },
"filters": [{ "store": ["人民路店", "长江路店"] }]
}
A custom compiler renders this to SQL deterministically. Same question = same SQL = same number, every time.
The YAML that powers it
Metrics are defined in SnakeYAML — non-engineers can read and extend them. Here's what MEMBER_CONSUMPTION looks like:
# member.yaml (excerpt)
MEMBER_CONSUMPTION:
description: 会员消费总额
grain: member_mobile # 粒度:不跨门店/订单
join:
- on: "e.expense_mobile = m.member_mobile"
dimensions:
store: m.member_allot_store
city: m.member_city
measures:
total: "SUM(NUM(e.expense_realamt))"
count: "COUNT(DISTINCT e.expense_id)"
sql: "SELECT {{dimensions}}, {{measures}} FROM expense_calendar_info e JOIN member_info m {{join}} {{timeRange}} {{filters}} GROUP BY {{groupBy}}"
A MetricDefinitionValidator runs at startup and catches problems like:
- SUM on a VARCHAR column (auto-adds
NUM()wrapper) - grain 跨粒度 (e.g. trying to join orders with order_details without GROUP BY)
- dimension not registered in column-types.yaml
Full architecture
Browser (Vue 3 + Vite)
│
▼
Vite dev proxy (/sse, /chat, /api)
│
▼
MCP Client (Spring Boot 3.5 + Spring AI 1.1)
├── ChatClient (Doubao Ark API, deepseek-v4-flash-ga-260731)
├── Redisson ChatMemory (session continuity)
└── MCP SSE Client → connects to server
│ MCP over SSE (JSON-RPC)
▼
MCP Server (Spring Boot 3.5)
├── 5 custom @Tool endpoints:
│ ├── MetricTool.queryMetric ← 主入口:结构化查询 → 编译 → 执行
│ ├── MetricTool.listMetrics ← 列所有可用指标
│ ├── MetricTool.explainMetric ← 干跑 SQL 给模型看
│ ├── AdhocTool.adhocQuery ← 兜底:只读 SQL
│ └── OfficeStoreTool ← 门店分配查询
├── MetricRegistry (YAML load at startup)
├── SqlCompiler (deterministic renderer)
├── DerivedCalculator (客单价 = SALES/ORDERS)
├── MetricQueryService (orchestration)
├── ReadOnlySqlGuard (regex + keyword blocklist)
├── SyncResourceSpecification (crm://levels/set_level-codes) ← set_level 50+ 编码按需拉取
├── MetadataBootstrapValidator (INFORMATION_SCHEMA) ← 启动期 FK cardinality 自校验
└── WildcardPositionCheck (reject leading % on indexed columns) ← LIKE 前导通配符硬拒
│ JDBC (read-only DB credential)
▼
MySQL 8.0 (utf8mb4, crmtest schema, 12k orders / 3k members)
Tech stack
| Layer | Technology |
|---|---|
| MCP Server | Spring Boot 3.5 + MCP SSE + 5 @Tool endpoints |
| MCP Client | Spring AI 1.1 + Spring AI MCP Client + Redisson ChatMemory |
| Metric Engine | SnakeYAML + custom SQL compiler |
| LLM | Doubao Ark API (deepseek-v4-flash-ga-260731) — MCP is model-agnostic, swap in minutes |
| SQL Guard | ReadOnlySqlGuard + WildcardPositionCheck (reject leading % on indexed columns) + MetadataBootstrapValidator (INFORMATION_SCHEMA auto-infer FK cardinality at startup; AST validation WIP) |
| Frontend | Vue 3 + Vite + Element Plus |
| Database | MySQL 8.0 (utf8mb4, read-only user) |
| E2E | Playwright (4 test scenarios, auto-record videos + GIFs) |
| Infra | Docker Compose (MySQL + Redis Stack + server + client + Vite) |
What went wrong (the painful parts)
Four bugs that each took 2+ hours to diagnose:
1. 豆包 Ark API: endpoint ID ≠ model name
Ark API requires the endpoint ID (UUID format: deepseek-v4-flash-ga-260731), not the model name (deepseek-v4-flash). Spent 2 hours debugging a 404 because Spring AI's OpenAI adapter appended /v3/v1/chat/completions instead of /v3/chat/completions.
# Wrong
spring:
ai:
openai:
completions-path: /v1/chat/completions # ← wrong level
# Right
spring:
ai:
openai:
chat:
completions-path: /chat/completions # ← correct path
2. MySQL double-encoding via PowerShell pipe
Piping UTF-8 SQL through PowerShell into docker exec -i crm-mysql mysql corrupted Chinese table comments into mojibake (C3A4C2BC...). Solution: docker cp the SQL file into the container, then run mysql source /path/file.sql --default-character-set=utf8mb4 from inside.
3. Docker Desktop proxy hijack
Docker daemon was configured with overrideProxyHttp: 127.0.0.1:7890 (Clash). When Clash died, containers couldn't even dnf install maven — every curl to cdn.amazonlinux.com returned SSL connect error. Solved by restarting Clash. Lesson: check Docker proxy before anything else.
4. Phone number double-escaping in mock data
The mock data generator called sq() twice on the same mobile number:
# generate_mock_data.py — the bug
member_mobile = sq(phone) # → "'1316849353'" (Python string with quotes)
orders.append((sq(member_mobile), ...)) # → sq("'1316849353'") → '''1316849353'''
Result: MySQL stored '1316849353 (12 chars, leading literal quote), but member table had 1316849353 (10 chars). JOINs never matched. All store-level member consumption showed ¥0.
Fix: store the raw mobile in _raw_mobile, pass only raw values to the orders tuple.
What's next (open issues welcome)
[ ] AST-level SQL validation — current ReadOnlySqlGuard is regex-based, can be bypassed with clever comments (
/* bypass */ DELETE FROM orders). Need JSqlParser AST walk.[ ] SubjectContext row-level security — production needs user-scoped data (store manager sees only their store's data).
[ ] Pre-aggregation for non-additive metrics — repurchase rate, churn rate need
GROUP BY mobile HAVING COUNT ≥ 2then re-aggregate.[x] CI pipeline ✅ — GitHub Actions 全绿。踩坑链:bash CRLF +
chmod +x→List.<String[]>of()显式类型 → 测试断言对齐新逻辑 → 契约基线更新。4 次迭代搞定。[ ] Spring Boot 4 + Spring AI 2.0 migration — when stable.
🆕 UPDATE: Nov 19 — Peer Review Lands
Tom Zafred (Founder & Principal Architect, Biomed Systems Limited) gave 3 recommendations on DEV.to. All landed within 24 hours, GitHub Issues #1–#3 closed, CI green ✅.
1. Reject leading % wildcards on indexed columns → WildcardPositionCheck
LIKE %keyword forces full table scan, defeats BTREE indexes. We added a compile-time gate that rejects leading % / _ but allows suffix wildcards (index-friendly). Covered: member_name, member_allot_staffName.
Commit: f299dac | Issue #1 (closed)
2. Shrink tool-definitions.json via lazy-loaded MCP Resource → SyncResourceSpecification
The set_level 50+ codebook bloated @Tool description to ~50KB — LLM parsing overhead. Moved it to an MCP Resource (crm://levels/set_level-codes) pulled on demand.
Result: tool-definitions.json 50KB → 8KB
Commit: fdc8836 | Issue #2 (closed)
3. Auto-infer FK cardinality from INFORMATION_SCHEMA → MetadataBootstrapValidator
Tom provided an INFORMATION_SCHEMA SQL to detect single-column unique indexes and auto-infer JOIN cardinality:
SELECT TABLE_NAME, INDEX_NAME, MIN(COLUMN_NAME) AS key_col
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND NON_UNIQUE = 0
GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) = 1;
Design decision: only proves to_one (has unique index → to_one ✅). Never disproves to_many (no unique index → trust YAML, don't warn). Business logic ≠ DDL — member_mobile is one-to-one in business but declared as regular INDEX, not UNIQUE, in our CRM schema.
Real CRM schema results: all 4 relationships skipped (no unique indexes on JOIN columns). Need CRM DBA to confirm if ALTER TABLE member_info ADD UNIQUE INDEX ... is safe.
Commit: 2cd618d, ba6c269 | Issue #3 (closed)
Lessons
- MCP Resources are the right tool for large static reference tables — don't embed in
@Tool description - INFORMATION_SCHEMA can partially validate your metadata YAML against real DDL
- Conservative by design: only prove
to_one, never disproveto_many— business rules often outpace DDL
Try it yourself
# 1. Clone
git clone github.com/h472591847/crm-insight-agent
cd crm-insight-agent
# 2. Start MySQL + Redis
docker compose up -d mysql redis
# 3. Seed mock data (12k orders, 3k members)
cd backend/mock-data &&
python generate_mock_data.py &&
cd ..
docker cp mock-data/init/02_mock_data.sql crm-mysql:/tmp/
docker exec crm-mysql mysql -uroot -p123456 --default-character-set=utf8mb4 crmtest -e "SOURCE /tmp/02_mock_data.sql;"
# 4. Start MCP Server + Client
docker run -p 9060:9060 -v "$PWD:/app" -v "$HOME/.m2/repository:/root/.m2/repository" amazoncorretto:21 bash run-server.sh
docker run -p 9090:9090 -v "$PWD:/app" -v "$HOME/.m2/repository:/root/.m2/repository" amazoncorretto:21 bash run-client.sh
# 5. Start frontend
cd frontend && npm install && npm run dev -- --port 9173
Open http://localhost:9173/crmAgent/ and ask "上月GMV是多少?" — you should see the same 951,680.40 CNY result.
GitHub repo
⭐ github.com/h472591847/crm-insight-agent
Star if this helps, open an issue if something's broken, or drop a comment — I'd love to hear from people building similar enterprise agent tools.


Top comments (1)
Repo: github.com/h472591847/crm-insight-agent
Would love feedback on the security model — the regex-based ReadOnlySqlGuard
can be bypassed with comment injection (/* */ DELETE). Planning to add
JSqlParser AST walk next. Anyone done this for MCP tools already?