806 lines
67 KiB
Transact-SQL
806 lines
67 KiB
Transact-SQL
/* ============================================================================
|
||
FMS 工作流与审批表建表脚本（wf_*）
|
||
|
||
依据文档：FMS新系统核心表结构设计.md §16 工作流与审批
|
||
语义设计见 FMS工作流与审批设计.md
|
||
|
||
使用说明：
|
||
1. 执行前先切换到目标数据库（USE [数据库名]）。
|
||
2. 脚本只建表、索引和说明属性，不含初始化数据。
|
||
3. 不使用外键、CHECK、触发器；关联完整性和业务校验由业务层处理。
|
||
4. SQL Server 不支持建表内联注释，列注释通过扩展属性 MS_Description 持久化，
|
||
SSMS 的表设计器和列属性说明直接读取。
|
||
5. 脚本按新建表编写；表已存在时需先删除后重新执行。
|
||
============================================================================ */
|
||
|
||
SET NOCOUNT ON;
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.1 流程定义
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_definition (
|
||
b_id varchar(50) not null primary key, -- 流程定义编码（创建后不可修改）
|
||
b_name nvarchar(200) not null, -- 流程名称
|
||
b_i18n varchar(150) null, -- 多语言资源键（s_i18n.b_key）
|
||
b_canuse tinyint not null default 1, -- 是否启用（0/1）
|
||
b_bz nvarchar(2000) null, -- 备注
|
||
b_created_by varchar(50) null, -- 创建人（用户编码）
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_by varchar(50) null, -- 最后修改人
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_definition_canuse
|
||
on dbo.wf_definition (b_canuse, b_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程定义表（稳定逻辑身份，执行内容在版本表）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程定义编码（创建后不可修改）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程名称',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_name';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'多语言资源键（s_i18n.b_key）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_i18n';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_bz';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_created_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_updated_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_definition', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.2 流程版本
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_version (
|
||
b_id varchar(50) not null primary key, -- 版本 ID
|
||
b_definition_id varchar(50) not null, -- 流程定义编码（wf_definition.b_id）
|
||
b_version_no int not null, -- 版本序号（同一流程内递增）
|
||
b_status varchar(20) not null default 'draft', -- 版本状态：draft(草稿)/published(已发布)/retired(已停用)
|
||
b_schema_version int not null default 1, -- 配置结构版本（JSON 结构升级用）
|
||
b_canvas_json nvarchar(max) null, -- 设计器布局（节点坐标、缩放），不参与执行
|
||
b_checksum varchar(128) null, -- 发布时计算的配置摘要
|
||
b_published_by varchar(50) null, -- 发布人（用户编码）
|
||
b_published_at datetime2 null, -- 发布时间
|
||
b_bz nvarchar(2000) null, -- 备注
|
||
b_created_by varchar(50) null, -- 创建人
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_by varchar(50) null, -- 最后修改人
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create unique index ux_wf_version_no
|
||
on dbo.wf_version (b_definition_id, b_version_no);
|
||
GO
|
||
|
||
create index ix_wf_version_status
|
||
on dbo.wf_version (b_definition_id, b_status, b_version_no);
|
||
GO
|
||
|
||
create unique index ux_wf_version_draft
|
||
on dbo.wf_version (b_definition_id)
|
||
where b_status = 'draft';
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程版本表（draft/published/retired，已发布版本不可原地修改）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程定义编码（wf_definition.b_id）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_definition_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本序号（同一流程内递增）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_version_no';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本状态：draft(草稿)/published(已发布)/retired(已停用)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'配置结构版本（JSON 结构升级用）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_schema_version';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'设计器布局（节点坐标、缩放），不参与执行',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_canvas_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发布时计算的配置摘要',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_checksum';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发布人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_published_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发布时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_published_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_bz';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_created_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_updated_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_version', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.3 流程绑定
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_binding (
|
||
b_id varchar(50) not null primary key, -- 绑定 ID
|
||
b_module_id varchar(50) not null, -- 模块编码（s_module.b_id，data/virtual 模块）
|
||
b_event varchar(30) not null, -- 业务事件：submit(送审)/resubmit(重新提交)/change(变更)
|
||
b_version_id varchar(50) not null, -- 已发布流程版本（wf_version.b_id）
|
||
b_match_json nvarchar(max) null, -- 业务匹配条件 AST
|
||
b_org_scope_json nvarchar(max) null, -- 公司、组织范围
|
||
b_status_field varchar(50) null, -- 业务状态字段（必须来自 s_field）
|
||
b_status_map_json nvarchar(max) null, -- 流程状态到业务状态的映射
|
||
b_priority int not null default 0, -- 匹配优先级（小的先匹配）
|
||
b_effective_from datetime2 null, -- 生效开始时间
|
||
b_effective_to datetime2 null, -- 生效结束时间
|
||
b_canuse tinyint not null default 1, -- 是否启用（0/1）
|
||
b_bz nvarchar(2000) null, -- 备注
|
||
b_created_by varchar(50) null, -- 创建人
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_by varchar(50) null, -- 最后修改人
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_binding_select
|
||
on dbo.wf_binding (b_module_id, b_event, b_canuse, b_priority, b_effective_from, b_effective_to);
|
||
GO
|
||
|
||
create unique index ux_wf_binding_active_priority
|
||
on dbo.wf_binding (b_module_id, b_event, b_priority)
|
||
where b_canuse = 1;
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程绑定表（模块 + 业务事件 + 条件 → 流程版本）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'绑定 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'模块编码（s_module.b_id，data/virtual 模块）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_module_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务事件：submit(送审)/resubmit(重新提交)/change(变更)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_event';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已发布流程版本（wf_version.b_id）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_version_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务匹配条件 AST',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_match_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司、组织范围',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_org_scope_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务状态字段（必须来自 s_field）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_status_field';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程状态到业务状态的映射',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_status_map_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'匹配优先级（小的先匹配）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_priority';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生效开始时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_effective_from';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生效结束时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_effective_to';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_bz';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_created_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_updated_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_binding', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.4 流程节点
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_node (
|
||
b_id varchar(50) not null primary key, -- 节点 ID
|
||
b_version_id varchar(50) not null, -- 流程版本（wf_version.b_id）
|
||
b_node_key varchar(80) not null, -- 版本内稳定编码（历史记录引用它）
|
||
b_name nvarchar(200) not null, -- 节点名称
|
||
b_i18n varchar(150) null, -- 多语言资源键
|
||
b_node_type varchar(30) not null, -- 节点类型：start(开始)/user_task(人工审批)/cc_task(抄送)/exclusive_gateway(条件分支)/parallel_gateway(并行分支)/service_task(服务节点)/end(结束)
|
||
b_approval_mode varchar(30) null, -- 审批方式：single(单人)/any(或签)/all(会签)/serial(串行会签)/n_of_m(N 人通过)/percentage(按比例通过)
|
||
b_reject_mode varchar(30) null, -- 驳回策略：terminate(驳回结束)/return_initiator(退回发起人)/return_previous(退回上一节点)
|
||
b_due_hours decimal(10,2) null, -- 审批时限（小时）
|
||
b_config_json nvarchar(max) null, -- 节点扩展配置（requiredCount、requiredRatio、formPolicy 等）
|
||
b_xh int not null default 0, -- 显示顺序
|
||
b_canuse tinyint not null default 1 -- 是否启用（0/1）
|
||
);
|
||
GO
|
||
|
||
create unique index ux_wf_node_key
|
||
on dbo.wf_node (b_version_id, b_node_key);
|
||
GO
|
||
|
||
create index ix_wf_node_type
|
||
on dbo.wf_node (b_version_id, b_node_type, b_xh, b_node_key);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程节点表（版本内节点定义）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程版本（wf_version.b_id）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_version_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本内稳定编码（历史记录引用它）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点名称',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_name';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'多语言资源键',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_i18n';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点类型：start(开始)/user_task(人工审批)/cc_task(抄送)/exclusive_gateway(条件分支)/parallel_gateway(并行分支)/service_task(服务节点)/end(结束)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_node_type';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批方式：single(单人)/any(或签)/all(会签)/serial(串行会签)/n_of_m(N 人通过)/percentage(按比例通过)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_approval_mode';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驳回策略：terminate(驳回结束)/return_initiator(退回发起人)/return_previous(退回上一节点)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_reject_mode';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批时限（小时）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_due_hours';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点扩展配置（requiredCount、requiredRatio、formPolicy 等）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_config_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'显示顺序',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_xh';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.5 节点连线
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_edge (
|
||
b_id varchar(50) not null primary key, -- 连线 ID
|
||
b_version_id varchar(50) not null, -- 流程版本
|
||
b_from_node_key varchar(80) not null, -- 来源节点编码
|
||
b_to_node_key varchar(80) not null, -- 目标节点编码
|
||
b_name nvarchar(200) null, -- 连线名称（画布标签，留空时前端按条件摘要显示）
|
||
b_condition_json nvarchar(max) null, -- 连线条件 AST
|
||
b_priority int not null default 0, -- 判断优先级（小的先判断）
|
||
b_canuse tinyint not null default 1, -- 是否启用（0/1）
|
||
b_bz nvarchar(1000) null -- 备注
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_edge_from
|
||
on dbo.wf_edge (b_version_id, b_from_node_key, b_canuse, b_priority, b_to_node_key);
|
||
GO
|
||
|
||
create index ix_wf_edge_to
|
||
on dbo.wf_edge (b_version_id, b_to_node_key, b_canuse, b_priority, b_from_node_key);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点连线表（含条件分支）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'连线 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程版本',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_version_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_from_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'目标节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_to_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'连线名称（画布标签，留空时前端按条件摘要显示）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_name';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'连线条件 AST',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_condition_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'判断优先级（小的先判断）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_priority';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_edge', @level2type=N'COLUMN', @level2name=N'b_bz';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.6 审批人规则
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_actor_rule (
|
||
b_id varchar(50) not null primary key, -- 规则 ID
|
||
b_version_id varchar(50) not null, -- 流程版本
|
||
b_node_key varchar(80) not null, -- 节点编码
|
||
b_actor_type varchar(30) not null, -- 解析类型：user(指定用户)/initiator(发起人)/initiator_manager(发起人直属领导)/dept_manager(部门负责人)/position(岗位)/org_role(组织角色)/field_user(业务字段用户)/custom(自定义解析器)
|
||
b_actor_ref varchar(150) null, -- 用户、岗位、角色或字段编码
|
||
b_resolve_json nvarchar(max) null, -- 解析参数
|
||
b_condition_json nvarchar(max) null, -- 规则适用条件 AST
|
||
b_fallback_type varchar(30) null, -- 解析为空时的兜底：none(不兜底)/admin(管理员)/initiator(发起人)/custom(自定义)
|
||
b_fallback_ref varchar(150) null, -- 兜底引用
|
||
b_priority int not null default 0, -- 优先级（串行会签的顺序来源）
|
||
b_canuse tinyint not null default 1, -- 是否启用（0/1）
|
||
b_bz nvarchar(1000) null -- 备注
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_actor_rule_node
|
||
on dbo.wf_actor_rule (b_version_id, b_node_key, b_canuse, b_priority, b_actor_type);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批人规则表（节点内审批人、抄送人的解析规则）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规则 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程版本',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_version_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解析类型：user(指定用户)/initiator(发起人)/initiator_manager(发起人直属领导)/dept_manager(部门负责人)/position(岗位)/org_role(组织角色)/field_user(业务字段用户)/custom(自定义解析器)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_actor_type';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户、岗位、角色或字段编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_actor_ref';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解析参数',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_resolve_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规则适用条件 AST',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_condition_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解析为空时的兜底：none(不兜底)/admin(管理员)/initiator(发起人)/custom(自定义)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_fallback_type';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'兜底引用',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_fallback_ref';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'优先级（串行会签的顺序来源）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_priority';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_actor_rule', @level2type=N'COLUMN', @level2name=N'b_bz';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.7 流程实例
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_instance (
|
||
b_id uniqueidentifier not null primary key, -- 实例 ID（应用层生成 UUIDv7）
|
||
b_definition_id varchar(50) not null, -- 流程定义编码
|
||
b_version_id varchar(50) not null, -- 流程版本（提交时固定）
|
||
b_binding_id varchar(50) not null, -- 命中的绑定（wf_binding.b_id）
|
||
b_module_id varchar(50) not null, -- 业务模块编码
|
||
b_event varchar(30) not null, -- 业务事件：submit(送审)/resubmit(重新提交)/change(变更)
|
||
b_business_id varchar(50) not null, -- 业务主键（统一按字符串保存）
|
||
b_business_no nvarchar(200) null, -- 业务单号（列表展示）
|
||
b_org_id varchar(50) null, -- 公司/组织编码
|
||
b_initiator_id varchar(50) not null, -- 发起人（用户编码）
|
||
b_initiator_dept_id varchar(50) null, -- 发起人部门编码
|
||
b_status varchar(30) not null, -- 实例状态：running(运行中)/approved(已通过)/rejected(已驳回)/returned(已退回)/withdrawn(已撤回)/cancelled(已取消)/expired(已过期)/error(异常待处理)
|
||
b_context_json nvarchar(max) null, -- 运行变量
|
||
b_snapshot_json nvarchar(max) null, -- 提交时业务快照
|
||
b_current_summary_json nvarchar(max) null, -- 列表展示用当前节点摘要
|
||
b_row_version bigint not null default 0, -- 乐观锁版本号
|
||
b_idempotency_key varchar(100) null, -- 幂等键
|
||
b_started_at datetime2 not null, -- 开始时间
|
||
b_completed_at datetime2 null, -- 结束时间
|
||
b_created_by varchar(50) null, -- 创建人
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_by varchar(50) null, -- 最后修改人
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_instance_business
|
||
on dbo.wf_instance (b_module_id, b_business_id, b_event, b_status, b_id);
|
||
GO
|
||
|
||
create index ix_wf_instance_initiator
|
||
on dbo.wf_instance (b_initiator_id, b_status, b_started_at, b_id);
|
||
GO
|
||
|
||
create index ix_wf_instance_version
|
||
on dbo.wf_instance (b_version_id, b_started_at, b_id);
|
||
GO
|
||
|
||
create unique index ux_wf_instance_idempotency
|
||
on dbo.wf_instance (b_module_id, b_business_id, b_idempotency_key)
|
||
where b_idempotency_key is not null;
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程实例表（某张业务单据某次提交的运行流程）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例 ID（应用层生成 UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程定义编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_definition_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程版本（提交时固定）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_version_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'命中的绑定（wf_binding.b_id）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_binding_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务模块编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_module_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务事件：submit(送审)/resubmit(重新提交)/change(变更)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_event';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务主键（统一按字符串保存）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_business_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务单号（列表展示）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_business_no';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公司/组织编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_org_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发起人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_initiator_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发起人部门编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_initiator_dept_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实例状态：running(运行中)/approved(已通过)/rejected(已驳回)/returned(已退回)/withdrawn(已撤回)/cancelled(已取消)/expired(已过期)/error(异常待处理)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'运行变量',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_context_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'提交时业务快照',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_snapshot_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列表展示用当前节点摘要',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_current_summary_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'乐观锁版本号',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_row_version';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'幂等键',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_idempotency_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_started_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_completed_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_created_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_updated_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_instance', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.8 节点运行
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_node_run (
|
||
b_id uniqueidentifier not null primary key, -- 节点运行 ID（UUIDv7）
|
||
b_instance_id uniqueidentifier not null, -- 流程实例（wf_instance.b_id）
|
||
b_node_key varchar(80) not null, -- 节点编码
|
||
b_run_no int not null default 1, -- 同一节点的第几次运行（退回、循环用）
|
||
b_status varchar(30) not null, -- 节点状态：pending(待激活)/running(运行中)/completed(已完成)/skipped(已跳过)
|
||
b_required_count int null, -- 应签人数（按比例时在节点激活时换算）
|
||
b_completed_count int not null default 0, -- 已通过人数
|
||
b_rejected_count int not null default 0, -- 已驳回人数
|
||
b_entered_at datetime2 null, -- 进入时间
|
||
b_exited_at datetime2 null, -- 离开时间
|
||
b_actor_snapshot_json nvarchar(max) null, -- 审批人规则快照
|
||
b_created_at datetime2 null -- 创建时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_node_run_instance
|
||
on dbo.wf_node_run (b_instance_id, b_status, b_node_key, b_run_no, b_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点运行表（节点在实例中的一次进入和处理记录）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点运行 ID（UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程实例（wf_instance.b_id）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_instance_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'同一节点的第几次运行（退回、循环用）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_run_no';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点状态：pending(待激活)/running(运行中)/completed(已完成)/skipped(已跳过)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'应签人数（按比例时在节点激活时换算）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_required_count';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已通过人数',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_completed_count';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已驳回人数',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_rejected_count';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'进入时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_entered_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'离开时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_exited_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批人规则快照',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_actor_snapshot_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_node_run', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.9 待办任务
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_task (
|
||
b_id uniqueidentifier not null primary key, -- 任务 ID（UUIDv7）
|
||
b_instance_id uniqueidentifier not null, -- 流程实例
|
||
b_node_run_id uniqueidentifier not null, -- 节点运行（会签、或签聚合判断用）
|
||
b_node_key varchar(80) not null, -- 节点编码
|
||
b_actor_rule_id varchar(50) null, -- 命中的审批人规则
|
||
b_assignee_type varchar(30) not null, -- 受理人类型：user(指定用户)/position(岗位)/role(角色)/delegate(委托)
|
||
b_assignee_id varchar(50) not null, -- 实际用户编码
|
||
b_seq int not null default 0, -- 同节点内处理顺序（串行会签生效）
|
||
b_status varchar(30) not null, -- 任务状态：waiting(未激活)/pending(待处理)/claimed(已抢占)/approved(已通过)/rejected(已驳回)/cancelled(已取消)/expired(已过期)
|
||
b_due_at datetime2 null, -- 应处理时间
|
||
b_claimed_by varchar(50) null, -- 抢占人
|
||
b_claimed_at datetime2 null, -- 抢占时间
|
||
b_completed_by varchar(50) null, -- 处理人
|
||
b_completed_at datetime2 null, -- 处理时间
|
||
b_rule_snapshot_json nvarchar(max) null, -- 审批人解析快照
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_task_assignee
|
||
on dbo.wf_task (b_assignee_id, b_status, b_due_at, b_id);
|
||
GO
|
||
|
||
create index ix_wf_task_instance
|
||
on dbo.wf_task (b_instance_id, b_status, b_node_run_id, b_id);
|
||
GO
|
||
|
||
create index ix_wf_task_due
|
||
on dbo.wf_task (b_status, b_due_at, b_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'待办任务表（一名实际审批人一行）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'任务 ID（UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程实例',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_instance_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点运行（会签、或签聚合判断用）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_node_run_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'命中的审批人规则',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_actor_rule_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'受理人类型：user(指定用户)/position(岗位)/role(角色)/delegate(委托)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_assignee_type';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际用户编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_assignee_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'同节点内处理顺序（串行会签生效）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_seq';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'任务状态：waiting(未激活)/pending(待处理)/claimed(已抢占)/approved(已通过)/rejected(已驳回)/cancelled(已取消)/expired(已过期)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'应处理时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_due_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抢占人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_claimed_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抢占时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_claimed_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'处理人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_completed_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'处理时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_completed_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批人解析快照',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_rule_snapshot_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_task', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.10 审批动作
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_action (
|
||
b_id uniqueidentifier not null primary key, -- 动作 ID（UUIDv7）
|
||
b_instance_id uniqueidentifier not null, -- 流程实例
|
||
b_node_run_id uniqueidentifier null, -- 节点运行
|
||
b_task_id uniqueidentifier null, -- 待办任务
|
||
b_action varchar(30) not null, -- 动作编码：submit(送审)/approve(通过)/reject(驳回)/return(退回)/withdraw(撤回)/cancel(取消)/claim(抢占)/transfer(转交)/delegate(委托)/reassign(转派)/add_approver(加签)/urge(催办)/cc(抄送)
|
||
b_operator_id varchar(50) null, -- 操作人（用户编码，系统动作为空）
|
||
b_from_status varchar(30) null, -- 变更前状态
|
||
b_to_status varchar(30) null, -- 变更后状态
|
||
b_comment nvarchar(2000) null, -- 审批意见
|
||
b_payload_json nvarchar(max) null, -- 动作载荷（命中的条件、边、加签位置等）
|
||
b_request_id varchar(64) null, -- 请求 ID
|
||
b_trace_id varchar(64) null, -- 链路 ID
|
||
b_occurdatetime datetime2 not null -- 发生时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_action_instance
|
||
on dbo.wf_action (b_instance_id, b_occurdatetime, b_id);
|
||
GO
|
||
|
||
create index ix_wf_action_operator
|
||
on dbo.wf_action (b_operator_id, b_occurdatetime, b_id);
|
||
GO
|
||
|
||
create unique index ux_wf_action_request
|
||
on dbo.wf_action (b_request_id)
|
||
where b_request_id is not null;
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批动作表（只追加，不更新历史动作）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'动作 ID（UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程实例',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_instance_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节点运行',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_node_run_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'待办任务',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_task_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'动作编码：submit(送审)/approve(通过)/reject(驳回)/return(退回)/withdraw(撤回)/cancel(取消)/claim(抢占)/transfer(转交)/delegate(委托)/reassign(转派)/add_approver(加签)/urge(催办)/cc(抄送)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_action';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作人（用户编码，系统动作为空）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_operator_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'变更前状态',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_from_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'变更后状态',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_to_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审批意见',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_comment';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'动作载荷（命中的条件、边、加签位置等）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_payload_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'请求 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_request_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'链路 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_trace_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发生时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_action', @level2type=N'COLUMN', @level2name=N'b_occurdatetime';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.11 抄送记录
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_cc (
|
||
b_id uniqueidentifier not null primary key, -- 抄送 ID（UUIDv7）
|
||
b_instance_id uniqueidentifier not null, -- 流程实例
|
||
b_node_run_id uniqueidentifier null, -- 来源节点运行
|
||
b_node_key varchar(80) null, -- 来源节点编码
|
||
b_recipient_id varchar(50) not null, -- 抄送接收人（用户编码）
|
||
b_actor_rule_id varchar(50) null, -- 命中的抄送规则
|
||
b_status varchar(20) not null default 'unread', -- 阅读状态：unread(未读)/read(已读)
|
||
b_read_at datetime2 null, -- 阅读时间
|
||
b_rule_snapshot_json nvarchar(max) null, -- 抄送人解析快照
|
||
b_created_at datetime2 null -- 创建时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_cc_recipient
|
||
on dbo.wf_cc (b_recipient_id, b_status, b_created_at, b_id);
|
||
GO
|
||
|
||
create index ix_wf_cc_instance
|
||
on dbo.wf_cc (b_instance_id, b_created_at, b_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抄送记录表（抄送人不产生待办，不参与节点判定）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抄送 ID（UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流程实例',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_instance_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源节点运行',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_node_run_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'来源节点编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_node_key';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抄送接收人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_recipient_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'命中的抄送规则',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_actor_rule_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'阅读状态：unread(未读)/read(已读)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'阅读时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_read_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抄送人解析快照',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_rule_snapshot_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_cc', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.12 委托
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_delegation (
|
||
b_id varchar(50) not null primary key, -- 委托 ID
|
||
b_from_user_id varchar(50) not null, -- 委托人（用户编码）
|
||
b_to_user_id varchar(50) not null, -- 受托人（用户编码）
|
||
b_scope_json nvarchar(max) null, -- 委托范围（模块、动作、组织）
|
||
b_start_at datetime2 not null, -- 生效时间
|
||
b_end_at datetime2 not null, -- 失效时间
|
||
b_reason nvarchar(500) null, -- 委托原因
|
||
b_canuse tinyint not null default 1, -- 是否启用（0/1）
|
||
b_created_by varchar(50) null, -- 创建人
|
||
b_created_at datetime2 null, -- 创建时间
|
||
b_updated_by varchar(50) null, -- 最后修改人
|
||
b_updated_at datetime2 null -- 最后修改时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_delegation_user
|
||
on dbo.wf_delegation (b_from_user_id, b_canuse, b_start_at, b_end_at, b_to_user_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'委托表（审批人把待办委托给他人处理）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'委托 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'委托人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_from_user_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'受托人（用户编码）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_to_user_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'委托范围（模块、动作、组织）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_scope_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生效时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_start_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'失效时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_end_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'委托原因',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_reason';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用（0/1）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_canuse';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_created_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_created_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改人',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_updated_by';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_delegation', @level2type=N'COLUMN', @level2name=N'b_updated_at';
|
||
GO
|
||
|
||
/* ============================================================================
|
||
16.13 通知消息
|
||
============================================================================ */
|
||
|
||
create table dbo.wf_outbox (
|
||
b_id uniqueidentifier not null primary key, -- 消息 ID（UUIDv7）
|
||
b_event_code varchar(80) not null, -- 事件编码
|
||
b_aggregate_type varchar(30) not null, -- 聚合类型：instance(流程实例)/task(待办任务)
|
||
b_aggregate_id varchar(50) not null, -- 聚合 ID
|
||
b_payload_json nvarchar(max) not null, -- 消息载荷
|
||
b_status varchar(20) not null default 'pending', -- 发送状态：pending(待发送)/sent(已发送)/failed(发送失败)
|
||
b_retry_count int not null default 0, -- 重试次数
|
||
b_next_retry_at datetime2 null, -- 下次重试时间
|
||
b_last_error nvarchar(2000) null, -- 最后错误
|
||
b_occurdatetime datetime2 not null -- 发生时间
|
||
);
|
||
GO
|
||
|
||
create index ix_wf_outbox_dispatch
|
||
on dbo.wf_outbox (b_status, b_next_retry_at, b_occurdatetime, b_id);
|
||
GO
|
||
|
||
-- 说明属性（MS_Description）：SSMS 表设计器与列属性说明的来源
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'通知消息表（outbox，异步发送，不阻塞审批事务）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息 ID（UUIDv7）',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'事件编码',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_event_code';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'聚合类型：instance(流程实例)/task(待办任务)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_aggregate_type';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'聚合 ID',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_aggregate_id';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息载荷',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_payload_json';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送状态：pending(待发送)/sent(已发送)/failed(发送失败)',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_status';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'重试次数',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_retry_count';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'下次重试时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_next_retry_at';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后错误',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_last_error';
|
||
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发生时间',
|
||
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'wf_outbox', @level2type=N'COLUMN', @level2name=N'b_occurdatetime';
|
||
GO
|
||
|