DEV Community

Cover image for I built an enterprise CRM Insight Agent with MCP + Spring AI — deterministic Text2SQL, not prompt injection bait
Ying Li
Ying Li

Posted on Edited on

I built an enterprise CRM Insight Agent with MCP + Spring AI — deterministic Text2SQL, not prompt injection bait

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": ["人民路店", "长江路店"] }]
}
Enter fullscreen mode Exit fullscreen mode

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}}"
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'''
Enter fullscreen mode Exit fullscreen mode

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 ≥ 2 then 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;
Enter fullscreen mode Exit fullscreen mode

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 disprove to_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
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
ying_li_2410bddf58e38117a profile image
Ying Li •

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?