unis_crm/docs/数据结构.md

# 紫光汇智 CRM 数据库表设计文档

## 1. 文档说明

本文档用于说明紫光汇智 CRM 系统当前数据库表设计，主要依据以下真实文件整理：

- `sql/init_full_pg17.sql`

本文档目标：

- 作为数据库设计说明的统一输出
- 为开发、测试、运维、交付提供表结构参考
- 为后续字段扩展、索引优化和数据治理提供基线

说明：

- 本文档优先以当前初始化脚本中的真实表结构为准
- 若文档内容与历史设计稿不一致，应以 `sql/init_full_pg17.sql` 为最终准绳

## 2. 数据库概况

当前系统数据库使用 `PostgreSQL 17`，业务表主要分为以下几类：

- 用户基础信息
- 客户与商机
- 拓展管理
- 工作管理
- 首页待办与动态

当前 CRM 自有表包括：

- `sys_user`
- `crm_customer`
- `crm_opportunity`
- `crm_opportunity_followup`
- `crm_sales_expansion`
- `crm_channel_expansion`
- `crm_channel_expansion_contact`
- `crm_expansion_followup`
- `work_checkin`
- `work_daily_report`
- `work_daily_report_comment`
- `work_todo`
- `sys_activity_log`

## 3. 核心关系说明

主要业务关系如下：

```mermaid
erDiagram
  SYS_USER ||--o{ CRM_CUSTOMER : owns
  SYS_USER ||--o{ CRM_OPPORTUNITY : owns
  SYS_USER ||--o{ CRM_SALES_EXPANSION : owns
  SYS_USER ||--o{ CRM_CHANNEL_EXPANSION : owns
  SYS_USER ||--o{ WORK_CHECKIN : submits
  SYS_USER ||--o{ WORK_DAILY_REPORT : submits
  SYS_USER ||--o{ WORK_TODO : owns
  SYS_USER ||--o{ SYS_ACTIVITY_LOG : operates

  CRM_CUSTOMER ||--o{ CRM_OPPORTUNITY : contains
  CRM_OPPORTUNITY ||--o{ CRM_OPPORTUNITY_FOLLOWUP : has
  CRM_CHANNEL_EXPANSION ||--o{ CRM_CHANNEL_EXPANSION_CONTACT : has
  WORK_DAILY_REPORT ||--o{ WORK_DAILY_REPORT_COMMENT : has
```

补充说明：

- `crm_opportunity` 可关联 `crm_sales_expansion` 和 `crm_channel_expansion`
- `crm_expansion_followup` 通过 `biz_type + biz_id` 关联销售拓展或渠道拓展
- `work_checkin` 通过 `biz_type + biz_id` 关联销售拓展、渠道拓展或商机

## 4. 通用设计约定

### 4.1 主键设计

所有业务表主键均采用：

- `bigint generated by default as identity`

优点：

- 与 PostgreSQL 原生自增机制兼容
- 适合单库单体系统场景

### 4.2 时间字段

大多数业务表统一包含：

- `created_at`
- `updated_at`

并通过统一触发器函数 `set_updated_at()` 自动维护更新时间。

### 4.3 审计与状态字段

多数表包含状态字段，例如：

- `status`
- `priority`
- `archived`
- `pushed_to_oms`
- `landed_flag`

这类字段用于支撑页面展示、筛选与流程状态控制。

### 4.4 约束设计

当前约束主要包括：

- 主键约束
- 唯一约束
- 外键约束
- `check` 约束

用途：

- 保证状态枚举合法
- 保证业务编码唯一性
- 保证级联关系完整

## 5. 表设计明细

### 5.1 `sys_user`

表用途：

- 存储系统用户基础信息
- 为业务模块提供负责人、提交人、操作人基础身份数据

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 用户主键 |
| `user_code` | varchar(50) | 工号/员工编号 |
| `username` | varchar(50) | 登录账号 |
| `real_name` | varchar(50) | 姓名 |
| `mobile` | varchar(20) | 手机号 |
| `email` | varchar(100) | 邮箱 |
| `org_id` | bigint | 所属组织 ID |
| `job_title` | varchar(100) | 职位 |
| `status` | smallint | 状态，默认 1 |
| `hire_date` | date | 入职日期 |
| `avatar_url` | varchar(255) | 头像地址 |
| `password_hash` | varchar(255) | 密码摘要 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_sys_user_username`
- `uk_sys_user_mobile`

索引：

- `idx_sys_user_org_id`

### 5.2 `crm_customer`

表用途：

- 存储客户主档信息
- 为商机表提供客户归属

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 客户主键 |
| `customer_code` | varchar(50) | 客户编码 |
| `customer_name` | varchar(200) | 客户名称 |
| `customer_type` | varchar(50) | 客户类型 |
| `industry` | varchar(50) | 所属行业 |
| `province` | varchar(50) | 省份 |
| `city` | varchar(50) | 城市 |
| `address` | varchar(255) | 详细地址 |
| `owner_user_id` | bigint | 当前负责人 |
| `source` | varchar(50) | 客户来源 |
| `status` | varchar(30) | 状态：`potential/following/won/lost` |
| `remark` | text | 备注 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_crm_customer_code`
- `status check (status in ('potential', 'following', 'won', 'lost'))`

索引：

- `idx_crm_customer_owner`
- `idx_crm_customer_name`

### 5.3 `crm_opportunity`

表用途：

- 存储商机主数据
- 是 CRM 业务的核心主表

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 商机主键 |
| `opportunity_code` | varchar(50) | 商机编号 |
| `opportunity_name` | varchar(200) | 商机名称 |
| `customer_id` | bigint | 客户 ID |
| `owner_user_id` | bigint | 商机负责人 ID |
| `sales_expansion_id` | bigint | 关联销售拓展 ID |
| `channel_expansion_id` | bigint | 关联渠道拓展 ID |
| `pre_sales_id` | bigint | 售前 ID |
| `pre_sales_name` | varchar(100) | 售前姓名 |
| `project_location` | varchar(100) | 项目所在地 |
| `operator_name` | varchar(100) | 运作方 |
| `amount` | numeric(18,2) | 商机金额 |
| `expected_close_date` | date | 预计结单日期 |
| `confidence_pct` | varchar(1) | 把握度：`A/B/C` |
| `stage` | varchar(50) | 商机阶段 |
| `opportunity_type` | varchar(50) | 商机类型 |
| `product_type` | varchar(100) | 产品类型 |
| `source` | varchar(50) | 商机来源 |
| `competitor_name` | varchar(200) | 竞品名称 |
| `archived` | boolean | 是否归档 |
| `pushed_to_oms` | boolean | 是否已推送 OMS |
| `oms_push_time` | timestamptz | 推送时间 |
| `description` | text | 说明/备注 |
| `status` | varchar(30) | 状态：`active/won/lost/closed` |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_crm_opportunity_code`
- `fk_crm_opportunity_customer`
- `fk_crm_opportunity_sales_expansion`
- `fk_crm_opportunity_channel_expansion`
- `confidence_pct check (confidence_pct in ('A', 'B', 'C'))`
- `status check (status in ('active', 'won', 'lost', 'closed'))`

索引：

- `idx_crm_opportunity_customer`
- `idx_crm_opportunity_owner`
- `idx_crm_opportunity_sales_expansion`
- `idx_crm_opportunity_channel_expansion`
- `idx_crm_opportunity_stage`
- `idx_crm_opportunity_expected_close`
- `idx_crm_opportunity_archived`

### 5.4 `crm_opportunity_followup`

表用途：

- 存储商机跟进记录
- 支撑商机详情页时间线与推进历史

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 跟进记录主键 |
| `opportunity_id` | bigint | 商机 ID |
| `followup_time` | timestamptz | 跟进时间 |
| `followup_type` | varchar(50) | 跟进方式 |
| `content` | text | 跟进内容 |
| `next_action` | varchar(255) | 下一步动作 |
| `followup_user_id` | bigint | 跟进人 ID |
| `source_type` | varchar(30) | 来源类型 |
| `source_id` | bigint | 来源记录 ID |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `fk_crm_opportunity_followup_opportunity on delete cascade`

索引：

- `idx_crm_opportunity_followup_opportunity_time`
- `idx_crm_opportunity_followup_user`
- `idx_crm_opportunity_followup_source`

### 5.5 `crm_sales_expansion`

表用途：

- 存储销售人员拓展信息

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 销售拓展主键 |
| `employee_no` | varchar(50) | 工号/员工编号 |
| `candidate_name` | varchar(50) | 候选人姓名 |
| `office_name` | varchar(100) | 办事处/代表处 |
| `mobile` | varchar(20) | 手机号 |
| `email` | varchar(100) | 邮箱 |
| `target_dept` | varchar(100) | 所属部门 |
| `industry` | varchar(50) | 所属行业 |
| `title` | varchar(100) | 职务 |
| `intent_level` | varchar(20) | 合作意向：`high/medium/low` |
| `stage` | varchar(50) | 跟进阶段 |
| `has_desktop_exp` | boolean | 是否有云桌面经验 |
| `in_progress` | boolean | 是否持续跟进 |
| `employment_status` | varchar(20) | 状态：`active/left/joined/abandoned` |
| `expected_join_date` | date | 预计入职日期 |
| `owner_user_id` | bigint | 负责人 ID |
| `remark` | text | 备注 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_crm_sales_expansion_owner_employee_no`
- `intent_level check (intent_level in ('high', 'medium', 'low'))`
- `employment_status check (employment_status in ('active', 'left', 'joined', 'abandoned'))`

索引：

- `idx_crm_sales_expansion_owner`
- `idx_crm_sales_expansion_stage`
- `idx_crm_sales_expansion_mobile`

### 5.6 `crm_channel_expansion`

表用途：

- 存储渠道拓展信息

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 渠道拓展主键 |
| `channel_code` | varchar(50) | 渠道编码 |
| `province` | varchar(50) | 省份 |
| `city` | varchar(50) | 城市 |
| `channel_name` | varchar(200) | 渠道名称 |
| `office_address` | varchar(255) | 办公地址 |
| `channel_industry` | varchar(100) | 聚焦行业 |
| `certification_level` | varchar(100) | 认证级别 |
| `annual_revenue` | numeric(18,2) | 年营收 |
| `staff_size` | integer | 人员规模 |
| `contact_established_date` | date | 建立联系日期 |
| `intent_level` | varchar(20) | 合作意向：`high/medium/low` |
| `has_desktop_exp` | boolean | 是否有云桌面经验 |
| `contact_name` | varchar(50) | 主联系人姓名，兼容旧结构 |
| `contact_title` | varchar(100) | 主联系人职务，兼容旧结构 |
| `contact_mobile` | varchar(20) | 主联系人电话，兼容旧结构 |
| `channel_attribute` | varchar(100) | 渠道属性 |
| `internal_attribute` | varchar(100) | 新华三内部属性 |
| `stage` | varchar(50) | 渠道合作阶段 |
| `landed_flag` | boolean | 是否已落地 |
| `expected_sign_date` | date | 预计签约日期 |
| `owner_user_id` | bigint | 负责人 ID |
| `remark` | text | 备注 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_crm_channel_expansion_code`，仅对非空 `channel_code` 生效
- `intent_level check (intent_level in ('high', 'medium', 'low'))`
- `staff_size check (staff_size is null or staff_size >= 0)`

索引：

- `idx_crm_channel_expansion_owner`
- `idx_crm_channel_expansion_stage`
- `idx_crm_channel_expansion_name`

### 5.7 `crm_channel_expansion_contact`

表用途：

- 存储渠道拓展联系人明细

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 联系人主键 |
| `channel_expansion_id` | bigint | 渠道拓展 ID |
| `contact_name` | varchar(50) | 联系人姓名 |
| `contact_mobile` | varchar(20) | 联系人电话 |
| `contact_title` | varchar(100) | 联系人职务 |
| `sort_order` | integer | 排序号 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `fk_crm_channel_expansion_contact_channel on delete cascade`

索引：

- `idx_crm_channel_expansion_contact_channel`

### 5.8 `crm_expansion_followup`

表用途：

- 存储销售拓展和渠道拓展的统一跟进记录

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 跟进记录主键 |
| `biz_type` | varchar(20) | 业务类型：`sales/channel` |
| `biz_id` | bigint | 业务对象 ID |
| `followup_time` | timestamptz | 跟进时间 |
| `followup_type` | varchar(50) | 跟进方式 |
| `content` | text | 跟进内容 |
| `next_action` | varchar(255) | 下一步动作 |
| `followup_user_id` | bigint | 跟进人 ID |
| `visit_start_time` | timestamptz | 拜访开始时间 |
| `evaluation_content` | text | 评估内容 |
| `next_plan` | text | 后续规划 |
| `source_type` | varchar(30) | 来源类型 |
| `source_id` | bigint | 来源记录 ID |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `biz_type check (biz_type in ('sales', 'channel'))`

索引：

- `idx_crm_expansion_followup_biz_time`
- `idx_crm_expansion_followup_user`
- `idx_crm_expansion_followup_source`

### 5.9 `work_checkin`

表用途：

- 存储外勤打卡记录

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 打卡主键 |
| `user_id` | bigint | 打卡人 ID |
| `checkin_date` | date | 打卡日期 |
| `checkin_time` | timestamptz | 打卡时间 |
| `biz_type` | varchar(20) | 关联对象类型：`sales/channel/opportunity` |
| `biz_id` | bigint | 关联对象 ID |
| `biz_name` | varchar(200) | 关联对象名称 |
| `longitude` | numeric(10,6) | 经度 |
| `latitude` | numeric(10,6) | 纬度 |
| `location_text` | varchar(255) | 打卡地点 |
| `remark` | varchar(500) | 备注说明 |
| `user_name` | varchar(100) | 打卡人姓名快照 |
| `dept_name` | varchar(200) | 部门快照 |
| `status` | varchar(30) | 状态：`normal/abnormal/reissue` |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `work_checkin_biz_type_check`
- `status check (status in ('normal', 'abnormal', 'reissue'))`

索引：

- `idx_work_checkin_user_date`

### 5.10 `work_daily_report`

表用途：

- 存储销售日报主记录

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 日报主键 |
| `user_id` | bigint | 提交人 ID |
| `report_date` | date | 日报日期 |
| `work_content` | text | 今日工作内容 |
| `tomorrow_plan` | text | 明日工作计划 |
| `source_type` | varchar(30) | 提交来源：`manual/voice` |
| `submit_time` | timestamptz | 提交时间 |
| `status` | varchar(30) | 状态：`draft/submitted/read/reviewed` |
| `score` | integer | 评分，0-100 |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `uk_work_daily_report_user_date`
- `source_type check (source_type in ('manual', 'voice'))`
- `status check (status in ('draft', 'submitted', 'read', 'reviewed'))`
- `score check (score between 0 and 100)`

索引：

- `idx_work_daily_report_user_date`
- `idx_work_daily_report_status`

### 5.11 `work_daily_report_comment`

表用途：

- 存储日报点评记录

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 点评主键 |
| `report_id` | bigint | 日报 ID |
| `reviewer_user_id` | bigint | 点评人 ID |
| `score` | integer | 点评分数 |
| `comment_content` | text | 点评内容 |
| `reviewed_at` | timestamptz | 点评时间 |
| `created_at` | timestamptz | 创建时间 |

关键约束：

- `fk_work_daily_report_comment_report on delete cascade`
- `score check (score between 0 and 100)`

索引：

- `idx_work_daily_report_comment_report`

### 5.12 `work_todo`

表用途：

- 存储首页待办事项

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 待办主键 |
| `user_id` | bigint | 所属用户 ID |
| `title` | varchar(200) | 待办标题 |
| `biz_type` | varchar(30) | 业务类型：`opportunity/expansion/report/other` |
| `biz_id` | bigint | 业务对象 ID |
| `due_date` | timestamptz | 截止时间 |
| `status` | varchar(20) | 状态：`todo/done/canceled` |
| `priority` | varchar(20) | 优先级：`high/medium/low` |
| `created_at` | timestamptz | 创建时间 |
| `updated_at` | timestamptz | 更新时间 |

关键约束：

- `biz_type check (biz_type in ('opportunity', 'expansion', 'report', 'other'))`
- `status check (status in ('todo', 'done', 'canceled'))`
- `priority check (priority in ('high', 'medium', 'low'))`

### 5.13 `sys_activity_log`

表用途：

- 存储首页最新动态日志

主要字段：

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | bigint PK | 动态主键 |
| `biz_type` | varchar(30) | 业务类型 |
| `biz_id` | bigint | 业务对象 ID |
| `action_type` | varchar(50) | 动作类型 |
| `title` | varchar(200) | 动态标题 |
| `content` | varchar(500) | 动态内容 |
| `operator_user_id` | bigint | 操作人 ID |
| `created_at` | timestamptz | 创建时间 |

索引：

- `idx_sys_activity_log_created`
- `idx_sys_activity_log_biz`

## 6. 索引设计汇总

当前索引设计主要围绕以下查询场景展开：

- 客户按负责人、名称查询
- 商机按客户、负责人、阶段、归档状态、预计结单日查询
- 商机跟进按商机和时间倒序查询
- 销售拓展按负责人、手机号查询
- 渠道拓展按负责人、名称查询
- 渠道联系人按主表查询
- 拓展跟进按业务对象和时间倒序查询
- 打卡和日报按用户、日期倒序查询
- 动态按时间倒序查询

## 7. 触发器与自动维护机制

当前脚本中定义了统一触发器：

- `set_updated_at()`

并为以下表创建了自动更新时间触发器：

- `sys_user`
- `crm_customer`
- `crm_opportunity`
- `crm_opportunity_followup`
- `crm_sales_expansion`
- `crm_channel_expansion`
- `crm_channel_expansion_contact`
- `crm_expansion_followup`
- `work_checkin`
- `work_daily_report`
- `work_todo`

说明：

- 在表记录更新时，`updated_at` 会自动刷新为当前时间

## 8. 兼容性设计说明

当前初始化脚本不仅支持全新环境，也兼容旧环境升级，主要处理包括：

- `sys_user.dept_id` 向 `org_id` 迁移
- `crm_sales_expansion.target_dept_id` 向 `target_dept` 文本字段迁移
- `crm_channel_expansion` 补齐 `channel_code`、`city`、`certification_level` 等字段
- 从旧版 `crm_channel_expansion` 联系人字段迁移到 `crm_channel_expansion_contact`
- `crm_opportunity` 补齐销售拓展、渠道拓展、售前、运作方、竞品等字段
- `work_checkin` 补齐关联对象与人员快照字段
- 商机和拓展跟进表补齐来源字段

这意味着：

- 当前数据库设计既考虑了新建环境，也考虑了历史演进连续性
- 部署时优先执行统一入口脚本，而不是分散执行历史 DDL

## 9. 数据设计建议

结合当前设计，后续建议关注以下方向：

1. 为外键字段逐步补齐显式外键约束，提升数据一致性。
2. 对 `work_content`、`tomorrow_plan` 等承载结构化内容的文本字段，明确 JSON 存储规范。
3. 对高频查询场景继续做慢 SQL 监控与索引优化。
4. 对商机推送、日报回写等关键动作增加审计表或事件表。
5. 对历史动态、历史图片和归档商机建立数据归档策略。

## 10. 相关文件

- `sql/init_full_pg17.sql`
- `docs/business-schema-design.md`
- `docs/system-construction.md`
- `docs/technical-system-planning.md`
- `docs/deployment-guide.md`