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. 核心关系说明

主要业务关系如下：

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