279 lines
16 KiB
Markdown
279 lines
16 KiB
Markdown
# MCP for CRM — 数据表结构与种子数据说明文档
|
||
|
||
> 数据库：PostgreSQL 15+，库 `smart_quotation_auto`（与 ERP 服务共库不同表）
|
||
> 建表/种子脚本：`sql/init.sql`（v3，幂等可重跑）；执行入口：`cd src && python3 seed.py`
|
||
> 版本：v1.0（2026-08-09）
|
||
|
||
---
|
||
|
||
## 1 概览
|
||
|
||
| # | 表名 | 用途 | 主键 | 种子行数 |
|
||
|---|------|------|------|----------|
|
||
| 1 | `customer` | 客户主数据（信用/折扣/账期） | customer_code | 20 |
|
||
| 2 | `inquiry` | 询价单 | inquiry_id | 9 |
|
||
| 3 | `inquiry_item` | 询价明细行（多零件） | id（SERIAL） | 9 |
|
||
| 4 | `quotation` | 报价单（多版本链） | quotation_id | 7 |
|
||
| 5 | `quotation_cost_detail` | 报价成本明细 | id（SERIAL） | 7 |
|
||
| 6 | `opportunity` | 商机 | opportunity_id | 8 |
|
||
| 7 | `quotation_mock` | 报价写入 CRM 模拟（独立 mock 表） | quotation_id | 0（运行时写入） |
|
||
|
||
**表关系**：
|
||
|
||
```
|
||
customer ──┬── inquiry ──── inquiry_item （客户 1─N 询价；询价 1─N 明细行）
|
||
├── quotation ── quotation_cost_detail（客户 1─N 报价；报价 1─1 成本明细）
|
||
│ └── inquiry_id 关联询价（版本链：同询价 N 版报价）
|
||
└── opportunity （客户 1─N 商机）
|
||
```
|
||
|
||
**外键**：inquiry/inquiry_item/quotation/opportunity 均外键指向 `customer.customer_code`；inquiry_item → inquiry；quotation → inquiry（可空）；quotation_cost_detail → quotation。`quotation_mock` 无任何外键，为独立模拟表。
|
||
|
||
---
|
||
|
||
## 2 表结构详解
|
||
|
||
### 2.1 customer — 客户
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `customer_code` | VARCHAR(50) | PRIMARY KEY | 客户编码，如 `OEM-2024-003` |
|
||
| `name` | VARCHAR(200) | NOT NULL | 客户名称 |
|
||
| `oem_tier` | VARCHAR(20) | NOT NULL | OEM / Tier1 / Tier2 |
|
||
| `credit_level` | VARCHAR(5) | DEFAULT 'B' | 信用等级：A/B/C/D/无评级 |
|
||
| `discount_rate` | DECIMAL(4,3) | DEFAULT 0 | 折扣率（0.040 = 4%） |
|
||
| `payment_days` | INTEGER | DEFAULT 30 | 账期天数（0 = 货到付款/预付） |
|
||
| `contact_person` / `phone` / `email` | VARCHAR | — | 联系人信息 |
|
||
| `industry` / `region` | VARCHAR(50) | — | 行业 / 区域 |
|
||
| `total_orders` | INTEGER | DEFAULT 0 | 历史订单数 |
|
||
| `total_amount` | DECIMAL(14,2) | DEFAULT 0 | 历史订单总额 |
|
||
| `notes` | TEXT | — | 备注（风险提示等） |
|
||
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 建档时间 |
|
||
|
||
### 2.2 inquiry — 询价单
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `inquiry_id` | VARCHAR(50) | PRIMARY KEY | 询价单号 `INQ-YYYY-NNNN` |
|
||
| `customer_code` | VARCHAR(50) | NOT NULL，FK | 客户编码 |
|
||
| `inquiry_date` | DATE | NOT NULL DEFAULT CURRENT_DATE | 询价日期 |
|
||
| `drawing_number` | VARCHAR(100) | — | 图纸号 |
|
||
| `part_number` | VARCHAR(100) | — | 零件号 |
|
||
| `annual_volume` | INTEGER | — | 年用量（件/年） |
|
||
| `target_price` | DECIMAL(10,2) | — | 客户目标价 ¥ |
|
||
| `status` | VARCHAR(20) | DEFAULT 'pending' | pending / quoted（首报自动推进）；种子含历史态 completed/processing/lost |
|
||
| `notes` | TEXT | — | 备注 |
|
||
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
### 2.3 inquiry_item — 询价明细行
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `id` | SERIAL | PRIMARY KEY | 自增主键 |
|
||
| `inquiry_id` | VARCHAR(50) | NOT NULL，FK | 所属询价单 |
|
||
| `line_no` | INTEGER | NOT NULL | 行号；UNIQUE(inquiry_id, line_no) |
|
||
| `part_name` | VARCHAR(200) | — | 零件名称 |
|
||
| `part_number` | VARCHAR(100) | — | 零件号 |
|
||
| `material_grade` | VARCHAR(50) | — | 客户材质牌号 |
|
||
| `volume_cc` | DECIMAL(10,2) | — | 体积 cm³ |
|
||
| `annual_qty` | INTEGER | — | 该行年用量 |
|
||
| `unit` | VARCHAR(10) | DEFAULT '件' | 单位 |
|
||
| `remarks` | TEXT | — | 备注 |
|
||
|
||
### 2.4 quotation — 报价单（多版本核心表）
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `quotation_id` | VARCHAR(50) | PRIMARY KEY | 报价单号 `QUO-YYYY-NNNN` |
|
||
| `inquiry_id` | VARCHAR(50) | FK（可空） | 所属询价单；索引 idx_quotation_inquiry |
|
||
| `customer_code` | VARCHAR(50) | NOT NULL，FK | 客户编码 |
|
||
| `quotation_date` | DATE | NOT NULL DEFAULT CURRENT_DATE | 报价日期 |
|
||
| `valid_until` | DATE | — | 有效期至 |
|
||
| `version` | INTEGER | DEFAULT 1 | 版本号；**UNIQUE(inquiry_id, version)**（并发防重） |
|
||
| `status` | VARCHAR(20) | DEFAULT 'draft' | draft/sent/approved/superseded/won/lost |
|
||
| `mold_cost` | DECIMAL(12,2) | DEFAULT 0 | 模具费（一次性） |
|
||
| `unit_price` | DECIMAL(10,2) | — | 单价 ¥ |
|
||
| `annual_volume` | INTEGER | — | 年用量 |
|
||
| `total_annual` | DECIMAL(14,2) | — | 年总额 = 单价×年用量 |
|
||
| `subtotal` | DECIMAL(14,2) | — | 小计 |
|
||
| `tax_rate` | DECIMAL(4,3) | DEFAULT 0.130 | 税率 |
|
||
| `tax_amount` | DECIMAL(14,2) | — | 税额 |
|
||
| `total_amount` | DECIMAL(14,2) | — | 含税总额 |
|
||
| `currency` | VARCHAR(10) | DEFAULT 'CNY' | 币种 |
|
||
| `payment_terms` | VARCHAR(200) | DEFAULT '月结30天' | 付款条款 |
|
||
| `delivery_terms` | VARCHAR(200) | DEFAULT '含税含运' | 交付条款 |
|
||
| `created_by` | VARCHAR(50) | DEFAULT 'AI智能报价' | 创建者 |
|
||
| `approved_by` | VARCHAR(50) | — | 审核人（人工审核后填写） |
|
||
| `remarks` | TEXT | — | 备注（测试数据防护标记写入处） |
|
||
| `created_at` | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | 创建时间 |
|
||
|
||
**版本链规则**：save_quotation 事务内 `version = MAX(version)+1`（不限次数）；旧版非终态批量置 `superseded`；`won`/`lost` 终态保留不覆盖。
|
||
|
||
### 2.5 quotation_cost_detail — 报价成本明细
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `id` | SERIAL | PRIMARY KEY | 自增主键 |
|
||
| `quotation_id` | VARCHAR(50) | NOT NULL，FK，UNIQUE(uq_quotation_cost) | 报价单号（1:1） |
|
||
| `material_cost` | DECIMAL(10,4) | — | 材料成本 ¥/件 |
|
||
| `casting_cost` | DECIMAL(10,4) | — | 压铸成本 ¥/件 |
|
||
| `machining_cost` | DECIMAL(10,4) | — | 机加工成本 ¥/件 |
|
||
| `post_process_cost` | DECIMAL(10,4) | — | 后处理成本 ¥/件 |
|
||
| `overhead_rate` | DECIMAL(4,3) | — | 管理费率 |
|
||
| `profit_rate` | DECIMAL(4,3) | — | 利润率 |
|
||
| `total_cost` | DECIMAL(10,4) | — | 四项成本小计 |
|
||
| `unit_price` | DECIMAL(10,4) | — | 单价（冗余留痕，与主表对账） |
|
||
|
||
### 2.6 opportunity — 商机
|
||
|
||
| 列 | 类型 | 约束/默认 | 说明 |
|
||
|----|------|-----------|------|
|
||
| `opportunity_id` | VARCHAR(50) | PRIMARY KEY | 商机号 `OPP-YYYY-NNNN` |
|
||
| `customer_code` | VARCHAR(50) | NOT NULL，FK | 客户编码 |
|
||
| `title` | VARCHAR(200) | NOT NULL | 商机标题（测试数据防护标记写入处） |
|
||
| `stage` | VARCHAR(20) | DEFAULT 'lead' | lead/qualified/proposal/negotiation/quoted/won/lost |
|
||
| `expected_amount` | DECIMAL(14,2) | DEFAULT 0 | 预期金额 ¥ |
|
||
| `probability` | INTEGER | DEFAULT 20 | 成交概率 % |
|
||
| `oem_program` | VARCHAR(200) | — | OEM 项目/平台 |
|
||
| `expected_close_date` | DATE | — | 预期关单日期 |
|
||
| `source` | VARCHAR(100) | — | 来源（智能报价自动生成/客户主动询价/展会获客等） |
|
||
| `created_by` | VARCHAR(50) | — | 创建者 |
|
||
| `created_at` / `updated_at` | DATE | DEFAULT CURRENT_DATE | 创建/更新日期 |
|
||
|
||
---
|
||
|
||
### 2.7 quotation_mock — 报价写入 CRM 模拟表（独立，无外键）
|
||
|
||
| 字段 | 类型 | 说明 |
|
||
|------|------|------|
|
||
| quotation_id | VARCHAR(50) PK | 模拟报价单号，`QUO-MOCK-####` 自增 |
|
||
| customer_name | VARCHAR(200) | 客户名称（自由文本，不校验） |
|
||
| part_name | VARCHAR(200) | 零件名称 |
|
||
| part_number | VARCHAR(50) | 零件号 |
|
||
| material_grade | VARCHAR(50) | 材质牌号 |
|
||
| mold_cost | DECIMAL(12,2) | 模具费，默认 0 |
|
||
| unit_price | DECIMAL(10,2) | 报价单价 |
|
||
| annual_volume | INTEGER | 年用量 |
|
||
| total_annual | DECIMAL(14,2) | 年总额（unit_price × annual_volume） |
|
||
| currency | VARCHAR(10) | 币种，默认 CNY |
|
||
| quotation_date | DATE | 报价日期，默认当天 |
|
||
| status | VARCHAR(20) | 状态，固定 `draft` |
|
||
| remarks | TEXT | 备注 |
|
||
| created_at | TIMESTAMP | 创建时间 |
|
||
|
||
> 由工具 `save_quotation_mock` 单独写入，与正式报价链路（inquiry/quotation/opportunity）完全隔离，用于模拟「报价写入 CRM」场景。
|
||
|
||
## 3 种子数据详解
|
||
|
||
### 3.1 customer — 20 家客户
|
||
|
||
覆盖设计：
|
||
|
||
- **层级**：OEM×7 / Tier1×7 / Tier2×6
|
||
- **信用梯度**：A×8 / B×6 / C×2 / D×1 / 无评级×3
|
||
- **特殊样本**：零历史新客户×3（T2-2025-002、T2-2026-001、T2-2026-002，用于信用评审场景）；长账期 90 天（OEM-2025-001）；货到付款 C/D 级（T2-2025-001/003/004）
|
||
|
||
| 编码 | 名称 | 层级 | 信用 | 折扣率 | 账期 | 区域 | 特征 |
|
||
|------|------|------|------|--------|------|------|------|
|
||
| OEM-2024-001 | 某日系主机厂 | OEM | A | 5.0% | 60 | 上海 | 长期合作，铝合金阀体壳 |
|
||
| OEM-2024-002 | 某德系主机厂 | OEM | A | 3.0% | 45 | 北京 | 质量要求高，偏好铸铁件 |
|
||
| OEM-2024-003 | 某新能源主机厂 | OEM | A | 4.0% | 60 | 合肥 | 三电配套，增长快 |
|
||
| OEM-2024-004 | 某美系主机厂 | OEM | A | 3.0% | 45 | 武汉 | 混动变速箱项目 |
|
||
| OEM-2025-001 | 某商用车主机厂 | OEM | B | 2.0% | 90 | 长春 | 长账期，注意现金流 |
|
||
| OEM-2025-002 | 某乘商两用主机厂 | OEM | B | 2.5% | 60 | 柳州 | 乘商双线采购 |
|
||
| OEM-2026-001 | 某智能电动新势力 | OEM | A | 5.0% | 45 | 上海 | 高折扣，战略培育 |
|
||
| T1-2025-001 | 某博世供应商 | Tier1 | B | 2.0% | 30 | 江苏苏州 | 变速箱配套 |
|
||
| T1-2025-002 | 某大陆供应商 | Tier1 | B | 0 | 30 | 广东广州 | 制动配套，新开发 |
|
||
| T1-2025-003 | 某采埃孚供应商 | Tier1 | A | 2.0% | 45 | 上海 | ZF 悬架配套 |
|
||
| T1-2025-004 | 某电装系供应商 | Tier1 | A | 2.5% | 45 | 广州 | 日系热管理 |
|
||
| T1-2025-005 | 某日立安斯泰莫供应商 | Tier1 | A | 2.0% | 45 | 大连 | 日系制动/转向 |
|
||
| T1-2026-001 | 某制动系统供应商 | Tier1 | B | 1.5% | 30 | 重庆 | 国产线控制动 |
|
||
| T1-2026-002 | 某液压系统供应商 | Tier1 | B | 1.0% | 60 | 长沙 | 液压阀体，账期长 |
|
||
| T2-2025-001 | 某售后市场贸易商 | Tier2 | C | 0 | 0 | 杭州 | 货到付款，注意风险 |
|
||
| T2-2025-002 | 某智能底盘初创公司 | Tier2 | 无评级 | 0 | 30 | 苏州 | 零订单新客户 |
|
||
| T2-2025-003 | 某非道路机械贸易商 | Tier2 | D | 0 | 0 | 济宁 | 仅接受预付款 |
|
||
| T2-2025-004 | 某改装件连锁商 | Tier2 | C | 0 | 0 | 成都 | 改装连锁，货到付款 |
|
||
| T2-2026-001 | 某机器人关节初创公司 | Tier2 | 无评级 | 0 | 30 | 深圳 | 人形机器人关节部件 |
|
||
| T2-2026-002 | 某低空经济无人机公司 | Tier2 | 无评级 | 0 | 30 | 深圳 | eVTOL 液压部件 |
|
||
|
||
### 3.2 inquiry + inquiry_item — 9 单询价（含明细行）
|
||
|
||
覆盖设计：2024~2025 历史闭环（completed/lost）+ 2026 在途（pending/processing）；单零件行，多零件场景由运行时写入。
|
||
|
||
| 询价单 | 客户 | 日期 | 零件 | 年用量 | 目标价 | 状态 |
|
||
|--------|------|------|------|--------|--------|------|
|
||
| INQ-2024-9005 | OEM-2024-001 | 2024-10-20 | 发动机油路阀体壳（EN AC-4600，555.56cc） | 200,000 | 55.00 | completed |
|
||
| INQ-2025-9001 | OEM-2024-001 | 2025-09-25 | 变速箱阀体外壳（ADC12，444.44cc） | 300,000 | 96.00 | completed |
|
||
| INQ-2025-9002 | OEM-2024-002 | 2025-08-10 | 制动阀体壳（HT250，388.89cc） | 150,000 | 155.00 | completed |
|
||
| INQ-2025-9003 | T1-2025-001 | 2025-05-28 | 转向器阀体壳（A356-T6，296.30cc） | 150,000 | 25.00 | lost |
|
||
| INQ-2025-9006 | OEM-2024-002 | 2025-11-05 | 变速箱阀体外壳（ADC12，小批量） | 100,000 | 100.00 | completed |
|
||
| INQ-2026-0001 | OEM-2024-001 | 2026-08-01 | 变速箱阀体外壳（ADC12，444.44cc） | 300,000 | 95.00 | processing |
|
||
| INQ-2026-0002 | T1-2025-001 | 2026-08-03 | 转向器阀体壳（A356-T6，296.30cc） | 150,000 | 55.00 | pending |
|
||
| INQ-2026-0006 | OEM-2024-004 | 2026-08-05 | 涡轮增压控制阀体壳（ADC12，320cc） | 120,000 | 48.00 | pending |
|
||
| INQ-2026-0007 | OEM-2025-001 | 2026-08-06 | 重卡制动阀体壳（HT300，850cc，1000T） | 60,000 | 260.00 | processing |
|
||
|
||
### 3.3 quotation + quotation_cost_detail — 7 张报价
|
||
|
||
覆盖设计：状态谱系 approved/won/lost/sent + 一条**两版议价链**（INQ-2025-9001：v1→v2，一致性校验基准）+ 跨客户价差样本。
|
||
|
||
| 报价单 | 询价单 | 客户 | 版本 | 状态 | 模具费 | 单价 | 年用量 | 含税总额 | 备注 |
|
||
|--------|--------|------|------|------|--------|------|--------|----------|------|
|
||
| QUO-2024-9005 | INQ-2024-9005 | OEM-2024-001 | 1 | approved | 180,000 | 52.30 | 200,000 | 11,819,800 | 2024 基线价 |
|
||
| QUO-2025-9001 | INQ-2025-9001 | OEM-2024-001 | 1 | approved | 0 | 42.50 | 300,000 | 14,407,500 | 模具费已摊销 |
|
||
| QUO-2025-9002 | INQ-2025-9001 | OEM-2024-001 | 2 | approved | 0 | 41.90 | 300,000 | 14,204,100 | 第二轮降价 |
|
||
| QUO-2025-9003 | INQ-2025-9002 | OEM-2024-002 | 1 | won | 250,000 | 46.80 | 150,000 | 7,932,600 | 已中标（终态保护样本） |
|
||
| QUO-2025-9004 | INQ-2025-9003 | T1-2025-001 | 1 | lost | 120,000 | 28.40 | 150,000 | 4,813,800 | 报价偏高丢单（利润率 28% 样本） |
|
||
| QUO-2025-9006 | INQ-2025-9006 | OEM-2024-002 | 1 | sent | 0 | 43.20 | 100,000 | 4,881,600 | 跨客户价差校验 |
|
||
| QUO-2026-0001 | INQ-2026-0001 | OEM-2024-001 | 1 | sent | 180,000 | 92.50 | 300,000 | 31,357,500 | 老客户优惠，含模具费 |
|
||
|
||
成本明细与报价 1:1（四项成本 + 12% 管理费 + 15% 利润，仅 QUO-2025-9004 为 28% 利润）。
|
||
|
||
### 3.4 opportunity — 8 条商机
|
||
|
||
覆盖设计：阶段全谱系 lead/qualified/proposal/negotiation/won/lost；金额 450 万~2775 万；AI 与销售团队双来源。
|
||
|
||
| 商机号 | 客户 | 阶段 | 金额¥ | 概率 | 关单日期 | OEM 项目 |
|
||
|--------|------|------|-------|------|----------|----------|
|
||
| OPP-2025-0005 | OEM-2024-002 | won | 8,200,000 | 100% | 2025-09-30 | 德系新制动平台 |
|
||
| OPP-2025-0006 | T1-2025-001 | lost | 4,500,000 | 0% | 2025-07-31 | EPS 转向系统 |
|
||
| OPP-2026-0001 | OEM-2024-001 | proposal | 27,750,000 | 60% | 2026-10-31 | 日系新车型 CVT |
|
||
| OPP-2026-0002 | T1-2025-001 | lead | 8,250,000 | 30% | 2026-12-31 | EPS 转向配套 |
|
||
| OPP-2026-0003 | T1-2025-003 | lead | 5,000,000 | 20% | 2026-12-31 | 主动悬架配套 |
|
||
| OPP-2026-0004 | OEM-2024-003 | negotiation | 9,000,000 | 60% | 2026-11-30 | 纯电平台热管理+制动 |
|
||
| OPP-2026-0005 | OEM-2024-004 | qualified | 12,000,000 | 40% | 2027-03-31 | 下一代混动变速箱 |
|
||
| OPP-2026-0006 | OEM-2025-001 | lead | 6,000,000 | 15% | 2026-12-31 | 重卡制动升级 |
|
||
|
||
---
|
||
|
||
## 4 运行时数据（种子之外，真实运行留痕）
|
||
|
||
以下数据由智能体真实运行产生，**不属于 init.sql**，作为演示/生产留痕保留：
|
||
|
||
- **版本链演示**：INQ-2026-0019 三版议价（v1→v2→v3，旧版 superseded，带 price_change_pct）。
|
||
- **生产报价留痕**：INQ-2026-0020~0027、QUO-2026-0019~0027、OPP-2026-0012~0018（含 TC-01/02/05 生产运行，如 QUO-2026-0027 单价 ¥8.06）。
|
||
- **数据库现状**（2026-08-09）：customer 20 / inquiry 24 / quotation 27 / opportunity 18；inquiry_item 与成本明细随行数联动。
|
||
- **回归测试数据**：TC-03 写入带「TC-03回归测试」标记，跑完经 `purge_test_records` 自清理，不累积。
|
||
|
||
---
|
||
|
||
## 5 幂等性与重跑机制
|
||
|
||
`init.sql` 整体可重复执行，无副作用（NF-3）：
|
||
|
||
1. **建表**：`CREATE TABLE IF NOT EXISTS`。
|
||
2. **历史去重**：inquiry_item 按 (inquiry_id, line_no)、quotation_cost_detail 按 quotation_id 删重（保留最小 id），随后幂等补唯一约束。
|
||
3. **版本号规范化**：对存量 quotation 按 (inquiry_id, created_at, quotation_id) 重写 version 为 1..n（幂等，已规范的行不变）。
|
||
4. **并发防重**：`UNIQUE(inquiry_id, version)` + 索引 `idx_quotation_inquiry`。
|
||
5. **种子写入**：全部 `ON CONFLICT (...) DO NOTHING`，重跑不产生重复客户/询价/报价/商机。
|
||
|
||
---
|
||
|
||
## 6 数据维护约定
|
||
|
||
| 场景 | 操作 | 说明 |
|
||
|------|------|------|
|
||
| 新客户建档 | 直接 SQL 或后续扩展工具 | customer_code 规则：OEM/T1/T2-年份-序号 |
|
||
| 报价审核 | UPDATE quotation SET status/approved_by | 当前无 MCP 写工具，人工线下完成 |
|
||
| 测试数据清理 | 仅经 MCP `purge_test_records` | 禁止直连 DELETE；非 TC-03 标记数据受防护 |
|
||
| 生产数据 | 不得清理 | 演示/运行留痕作为验收基线 |
|