<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Ying Li</title>
    <description>The latest articles on DEV Community by Ying Li (@ying_li_2410bddf58e38117a).</description>
    <link>https://dev.to/ying_li_2410bddf58e38117a</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4251656%2Fa1b065bc-8132-4db5-8c74-0ac1ad22427c.jpg</url>
      <title>DEV Community: Ying Li</title>
      <link>https://dev.to/ying_li_2410bddf58e38117a</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/ying_li_2410bddf58e38117a"/>
    <language>en</language>
    <item>
      <title>I built an enterprise CRM Insight Agent with MCP + Spring AI — deterministic Text2SQL, not prompt injection bait</title>
      <dc:creator>Ying Li</dc:creator>
      <pubDate>Wed, 18 Nov 2026 10:41:28 +0000</pubDate>
      <link>https://dev.to/ying_li_2410bddf58e38117a/i-built-an-enterprise-crm-insight-agent-with-mcp-spring-ai-deterministic-text2sql-not-prompt-2ph8</link>
      <guid>https://dev.to/ying_li_2410bddf58e38117a/i-built-an-enterprise-crm-insight-agent-with-mcp-spring-ai-deterministic-text2sql-not-prompt-2ph8</guid>
      <description>&lt;h1&gt;
  
  
  What I built
&lt;/h1&gt;

&lt;p&gt;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 &lt;strong&gt;live&lt;/strong&gt; results, not mockups.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;"What was last month's GMV?"&lt;/strong&gt; → 951,680.40 CNY across 1,508 orders&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;📷 &lt;br&gt;
&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1i7wdjg98laj82ha0lz1.gif" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F1i7wdjg98laj82ha0lz1.gif" alt=" " width="720" height="405"&gt;&lt;/a&gt;&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;&lt;strong&gt;"How does Q3 compare to Q2?"&lt;/strong&gt; → 2,064 members vs 844, +144.5% YoY&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;📷 &lt;br&gt;
&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fc9umjksmichf6qm5ckii.gif" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fc9umjksmichf6qm5ckii.gif" alt=" " width="720" height="405"&gt;&lt;/a&gt;&lt;/p&gt;
&lt;/blockquote&gt;




&lt;h1&gt;
  
  
  Why not just Text2SQL?
&lt;/h1&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Problem&lt;/th&gt;
&lt;th&gt;Pure Text2SQL&lt;/th&gt;
&lt;th&gt;This project&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Same question → different SQL each time?&lt;/td&gt;
&lt;td&gt;✅ Yes&lt;/td&gt;
&lt;td&gt;❌ Deterministic YAML config → compiled SQL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SQL injection risk?&lt;/td&gt;
&lt;td&gt;✅ Yes&lt;/td&gt;
&lt;td&gt;❌ Model outputs enums, parameters are &lt;code&gt;?&lt;/code&gt; placeholders only&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;JOIN doubling (orders × order_details)?&lt;/td&gt;
&lt;td&gt;✅ Model makes this mistake constantly&lt;/td&gt;
&lt;td&gt;❌ &lt;code&gt;grain&lt;/code&gt; config validated at compile time&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Column drift (VARCHAR amount SUM truncates)?&lt;/td&gt;
&lt;td&gt;✅ Model doesn't know&lt;/td&gt;
&lt;td&gt;❌ &lt;code&gt;NUM()&lt;/code&gt; wrapper auto-injected for declared columns&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  The architecture: model plans, framework compiles
&lt;/h2&gt;

&lt;p&gt;The LLM never generates SQL. It outputs a structured plan:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nl"&gt;"metric"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"MEMBER_CONSUMPTION"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"dimensions"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"store"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"timeRange"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="nl"&gt;"period"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"prev_month"&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nl"&gt;"filters"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="nl"&gt;"store"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"人民路店"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"长江路店"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A custom compiler renders this to SQL deterministically. Same question = same SQL = same number, every time.&lt;/p&gt;

&lt;h2&gt;
  
  
  The YAML that powers it
&lt;/h2&gt;

&lt;p&gt;Metrics are defined in SnakeYAML — non-engineers can read and extend them. Here's what MEMBER_CONSUMPTION looks like:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="c1"&gt;# member.yaml (excerpt)
&lt;/span&gt;&lt;span class="na"&gt;MEMBER_CONSUMPTION&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;description&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;会员消费总额&lt;/span&gt;
  &lt;span class="na"&gt;grain&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;member_mobile&lt;/span&gt;          &lt;span class="c1"&gt;# 粒度：不跨门店/订单
&lt;/span&gt;  &lt;span class="na"&gt;join&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;on&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"e.expense_mobile = m.member_mobile"&lt;/span&gt;
  &lt;span class="na"&gt;dimensions&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;store&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;m.member_allot_store&lt;/span&gt;
    &lt;span class="na"&gt;city&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;m.member_city&lt;/span&gt;
  &lt;span class="na"&gt;measures&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;total&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"SUM(NUM(e.expense_realamt))"&lt;/span&gt;
    &lt;span class="na"&gt;count&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"COUNT(DISTINCT e.expense_id)"&lt;/span&gt;
  &lt;span class="na"&gt;sql&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"SELECT {{dimensions}}, {{measures}} FROM expense_calendar_info e JOIN member_info m {{join}} {{timeRange}} {{filters}} GROUP BY {{groupBy}}"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A &lt;code&gt;MetricDefinitionValidator&lt;/code&gt; runs at startup and catches problems like:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;SUM on a VARCHAR column (auto-adds &lt;code&gt;NUM()&lt;/code&gt; wrapper)&lt;/li&gt;
&lt;li&gt;grain 跨粒度 (e.g. trying to join orders with order_details without GROUP BY)&lt;/li&gt;
&lt;li&gt;dimension not registered in column-types.yaml&lt;/li&gt;
&lt;/ul&gt;




&lt;h1&gt;
  
  
  Full architecture
&lt;/h1&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;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)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;






&lt;h1&gt;
  
  
  Tech stack
&lt;/h1&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Layer&lt;/th&gt;
&lt;th&gt;Technology&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;MCP Server&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Spring Boot 3.5 + MCP SSE + 5 &lt;code&gt;@Tool&lt;/code&gt; endpoints&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;MCP Client&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Spring AI 1.1 + Spring AI MCP Client + Redisson ChatMemory&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Metric Engine&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;SnakeYAML + custom SQL compiler&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;LLM&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Doubao Ark API (deepseek-v4-flash-ga-260731) — MCP is model-agnostic, swap in minutes&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;SQL Guard&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;ReadOnlySqlGuard + &lt;strong&gt;WildcardPositionCheck&lt;/strong&gt; (reject leading &lt;code&gt;%&lt;/code&gt; on indexed columns) + &lt;strong&gt;MetadataBootstrapValidator&lt;/strong&gt; (INFORMATION_SCHEMA auto-infer FK cardinality at startup; AST validation WIP)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Frontend&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Vue 3 + Vite + Element Plus&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Database&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;MySQL 8.0 (utf8mb4, read-only user)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;E2E&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Playwright (4 test scenarios, auto-record videos + GIFs)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Infra&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;Docker Compose (MySQL + Redis Stack + server + client + Vite)&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;




&lt;h1&gt;
  
  
  What went wrong (the painful parts)
&lt;/h1&gt;

&lt;p&gt;Four bugs that each took 2+ hours to diagnose:&lt;/p&gt;

&lt;h2&gt;
  
  
  1. 豆包 Ark API: endpoint ID ≠ model name
&lt;/h2&gt;

&lt;p&gt;Ark API requires the &lt;strong&gt;endpoint ID&lt;/strong&gt; (UUID format: &lt;code&gt;deepseek-v4-flash-ga-260731&lt;/code&gt;), not the model name (&lt;code&gt;deepseek-v4-flash&lt;/code&gt;). Spent 2 hours debugging a 404 because Spring AI's OpenAI adapter appended &lt;code&gt;/v3/v1/chat/completions&lt;/code&gt; instead of &lt;code&gt;/v3/chat/completions&lt;/code&gt;.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="c1"&gt;# Wrong
&lt;/span&gt;&lt;span class="na"&gt;spring&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;ai&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;openai&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;completions-path&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;/v1/chat/completions&lt;/span&gt;    &lt;span class="c1"&gt;# ← wrong level
&lt;/span&gt;
&lt;span class="c1"&gt;# Right  
&lt;/span&gt;&lt;span class="na"&gt;spring&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;ai&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;openai&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
      &lt;span class="na"&gt;chat&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="na"&gt;completions-path&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;/chat/completions&lt;/span&gt;      &lt;span class="c1"&gt;# ← correct path
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  2. MySQL double-encoding via PowerShell pipe
&lt;/h2&gt;

&lt;p&gt;Piping UTF-8 SQL through PowerShell into &lt;code&gt;docker exec -i crm-mysql mysql&lt;/code&gt; corrupted Chinese table comments into mojibake (&lt;code&gt;C3A4C2BC...&lt;/code&gt;). Solution: &lt;code&gt;docker cp&lt;/code&gt; the SQL file into the container, then run &lt;code&gt;mysql source /path/file.sql --default-character-set=utf8mb4&lt;/code&gt; from inside.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Docker Desktop proxy hijack
&lt;/h2&gt;

&lt;p&gt;Docker daemon was configured with &lt;code&gt;overrideProxyHttp: 127.0.0.1:7890&lt;/code&gt; (Clash). When Clash died, containers couldn't even &lt;code&gt;dnf install maven&lt;/code&gt; — every &lt;code&gt;curl&lt;/code&gt; to &lt;code&gt;cdn.amazonlinux.com&lt;/code&gt; returned SSL connect error. Solved by restarting Clash. Lesson: &lt;strong&gt;check Docker proxy before anything else&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Phone number double-escaping in mock data
&lt;/h2&gt;

&lt;p&gt;The mock data generator called &lt;code&gt;sq()&lt;/code&gt; twice on the same mobile number:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="c1"&gt;# generate_mock_data.py — the bug&lt;/span&gt;
&lt;span class="n"&gt;member_mobile&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;sq&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;phone&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;                     &lt;span class="c1"&gt;# → "'1316849353'" (Python string with quotes)&lt;/span&gt;
&lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;append&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;sq&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;member_mobile&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;       &lt;span class="c1"&gt;# → sq("'1316849353'") → '''1316849353'''&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Result: MySQL stored &lt;code&gt;'1316849353&lt;/code&gt; (12 chars, leading literal quote), but member table had &lt;code&gt;1316849353&lt;/code&gt; (10 chars). JOINs never matched. All store-level member consumption showed ¥0.&lt;/p&gt;

&lt;p&gt;Fix: store the raw mobile in &lt;code&gt;_raw_mobile&lt;/code&gt;, pass only raw values to the orders tuple.&lt;/p&gt;




&lt;h1&gt;
  
  
  What's next (open issues welcome)
&lt;/h1&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;[ ] &lt;strong&gt;AST-level SQL validation&lt;/strong&gt;  — current ReadOnlySqlGuard is regex-based, can be bypassed with clever comments (&lt;code&gt;/* bypass */ DELETE FROM orders&lt;/code&gt;). Need JSqlParser AST walk.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;[ ] &lt;strong&gt;SubjectContext row-level security&lt;/strong&gt;  — production needs user-scoped data (store manager sees only their store's data).&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;[ ] &lt;strong&gt;Pre-aggregation for non-additive metrics&lt;/strong&gt;  — repurchase rate, churn rate need &lt;code&gt;GROUP BY mobile HAVING COUNT ≥ 2&lt;/code&gt; then re-aggregate.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;[x] &lt;strong&gt;CI pipeline&lt;/strong&gt; ✅ — GitHub Actions 全绿。踩坑链：bash CRLF + &lt;code&gt;chmod +x&lt;/code&gt; → &lt;code&gt;List.&amp;lt;String[]&amp;gt;of()&lt;/code&gt; 显式类型 → 测试断言对齐新逻辑 → 契约基线更新。4 次迭代搞定。&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;[ ] &lt;strong&gt;Spring Boot 4 + Spring AI 2.0 migration&lt;/strong&gt;  — when stable.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;h2&gt;
  
  
  🆕 UPDATE: Nov 19 — Peer Review Lands
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Tom Zafred&lt;/strong&gt; (Founder &amp;amp; Principal Architect, Biomed Systems Limited) gave 3 recommendations on DEV.to. All landed within 24 hours, GitHub Issues #1–#3 closed, CI green ✅.&lt;/p&gt;

&lt;h3&gt;
  
  
  1. Reject leading &lt;code&gt;%&lt;/code&gt; wildcards on indexed columns → WildcardPositionCheck
&lt;/h3&gt;

&lt;p&gt;LIKE &lt;code&gt;%keyword&lt;/code&gt; forces full table scan, defeats BTREE indexes. We added a compile-time gate that &lt;strong&gt;rejects leading&lt;/strong&gt; &lt;code&gt;%&lt;/code&gt; / &lt;code&gt;_&lt;/code&gt; but allows &lt;strong&gt;suffix&lt;/strong&gt; wildcards (index-friendly). Covered: &lt;code&gt;member_name&lt;/code&gt;, &lt;code&gt;member_allot_staffName&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Commit:&lt;/strong&gt; f299dac | &lt;strong&gt;Issue #1&lt;/strong&gt; (closed)&lt;/p&gt;

&lt;h3&gt;
  
  
  2. Shrink tool-definitions.json via lazy-loaded MCP Resource → SyncResourceSpecification
&lt;/h3&gt;

&lt;p&gt;The &lt;code&gt;set_level&lt;/code&gt; 50+ codebook bloated &lt;code&gt;@Tool description&lt;/code&gt; to ~50KB — LLM parsing overhead. Moved it to an &lt;strong&gt;MCP Resource&lt;/strong&gt; (&lt;code&gt;crm://levels/set_level-codes&lt;/code&gt;) pulled on demand.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Result:&lt;/strong&gt; tool-definitions.json &lt;strong&gt;50KB → 8KB&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Commit:&lt;/strong&gt; fdc8836 | &lt;strong&gt;Issue #2&lt;/strong&gt; (closed)&lt;/p&gt;

&lt;h3&gt;
  
  
  3. Auto-infer FK cardinality from INFORMATION_SCHEMA → MetadataBootstrapValidator
&lt;/h3&gt;

&lt;p&gt;Tom provided an INFORMATION_SCHEMA SQL to detect single-column unique indexes and auto-infer JOIN cardinality:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="no"&gt;TABLE_NAME&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;INDEX_NAME&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;MIN&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;COLUMN_NAME&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;key_col&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="no"&gt;INFORMATION_SCHEMA&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="no"&gt;STATISTICS&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="no"&gt;TABLE_SCHEMA&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="no"&gt;NON_UNIQUE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="no"&gt;TABLE_NAME&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;INDEX_NAME&lt;/span&gt;
&lt;span class="k"&gt;HAVING&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="cm"&gt;(*) = 1;
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;Design decision:&lt;/strong&gt; only &lt;strong&gt;proves &lt;code&gt;to_one&lt;/code&gt;&lt;/strong&gt; (has unique index → to_one ✅). Never &lt;strong&gt;disproves &lt;code&gt;to_many&lt;/code&gt;&lt;/strong&gt; (no unique index → trust YAML, don't warn). Business logic ≠ DDL — &lt;code&gt;member_mobile&lt;/code&gt; is one-to-one in business but declared as regular INDEX, not UNIQUE, in our CRM schema.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Real CRM schema results:&lt;/strong&gt; all 4 relationships skipped (no unique indexes on JOIN columns). Need CRM DBA to confirm if &lt;code&gt;ALTER TABLE member_info ADD UNIQUE INDEX ...&lt;/code&gt; is safe.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Commit:&lt;/strong&gt; 2cd618d, ba6c269 | &lt;strong&gt;Issue #3&lt;/strong&gt; (closed)&lt;/p&gt;

&lt;h3&gt;
  
  
  Lessons
&lt;/h3&gt;

&lt;ul&gt;
&lt;li&gt;MCP Resources are the right tool for large static reference tables — don't embed in &lt;code&gt;@Tool description&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;INFORMATION_SCHEMA can partially validate your metadata YAML against real DDL&lt;/li&gt;
&lt;li&gt;Conservative by design: only prove &lt;code&gt;to_one&lt;/code&gt;, never disprove &lt;code&gt;to_many&lt;/code&gt; — business rules often outpace DDL&lt;/li&gt;
&lt;/ul&gt;




&lt;h1&gt;
  
  
  Try it yourself
&lt;/h1&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;&lt;span class="c"&gt;# 1. Clone&lt;/span&gt;
git clone github.com/h472591847/crm-insight-agent
&lt;span class="nb"&gt;cd&lt;/span&gt; crm-insight-agent

&lt;span class="c"&gt;# 2. Start MySQL + Redis&lt;/span&gt;
docker compose up -d mysql redis

&lt;span class="c"&gt;# 3. Seed mock data (12k orders, 3k members)&lt;/span&gt;
&lt;span class="nb"&gt;cd&lt;/span&gt; backend/mock-data &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; 
python generate_mock_data.py &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; 
&lt;span class="nb"&gt;cd&lt;/span&gt; ..
docker cp mock-data/init/02_mock_data.sql crm-mysql:/tmp/
docker &lt;span class="nb"&gt;exec&lt;/span&gt; crm-mysql mysql -uroot -p123456 --default-character-set=utf8mb4 crmtest -e &lt;span class="s2"&gt;"SOURCE /tmp/02_mock_data.sql;"&lt;/span&gt;

&lt;span class="c"&gt;# 4. Start MCP Server + Client&lt;/span&gt;
docker run -p 9060:9060 -v &lt;span class="s2"&gt;"$PWD:/app"&lt;/span&gt; -v &lt;span class="s2"&gt;"$HOME/.m2/repository:/root/.m2/repository"&lt;/span&gt; amazoncorretto:21 bash run-server.sh
docker run -p 9090:9090 -v &lt;span class="s2"&gt;"$PWD:/app"&lt;/span&gt; -v &lt;span class="s2"&gt;"$HOME/.m2/repository:/root/.m2/repository"&lt;/span&gt; amazoncorretto:21 bash run-client.sh

&lt;span class="c"&gt;# 5. Start frontend&lt;/span&gt;
&lt;span class="nb"&gt;cd&lt;/span&gt; frontend &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; npm install &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; npm run dev -- --port 9173
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Open &lt;code&gt;http://localhost:9173/crmAgent/&lt;/code&gt; and ask "上月GMV是多少？" — you should see the same 951,680.40 CNY result.&lt;/p&gt;




&lt;h1&gt;
  
  
  GitHub repo
&lt;/h1&gt;

&lt;p&gt;⭐ &lt;strong&gt;github.com/h472591847/crm-insight-agent&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;




</description>
      <category>mcp</category>
      <category>springai</category>
      <category>text2sql</category>
      <category>opensource</category>
    </item>
  </channel>
</rss>
