77 lines
3.4 KiB
SQL
77 lines
3.4 KiB
SQL
-- 服务号审批通知；先执行本文件，再启用 WECHAT_OA_ENABLED。
|
|
-- MySQL 5.7 / utf8mb4。无历史审批补发，无密钥存储。
|
|
CREATE TABLE IF NOT EXISTS `sys_wechat_oa_binding` (
|
|
`id` varchar(64) NOT NULL,
|
|
`user_id` varchar(64) NOT NULL,
|
|
`appid` varchar(64) NOT NULL,
|
|
`openid` varchar(64) DEFAULT NULL,
|
|
`subscribed` smallint NOT NULL DEFAULT 0,
|
|
`tenant_no` varchar(64) NOT NULL,
|
|
`order_num` int DEFAULT NULL,
|
|
`remark` varchar(512) DEFAULT NULL,
|
|
`create_by` varchar(64) DEFAULT NULL,
|
|
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`update_by` varchar(64) DEFAULT NULL,
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`del_flag` smallint NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_oa_binding_user` (`tenant_no`,`appid`,`user_id`),
|
|
UNIQUE KEY `uk_oa_binding_openid` (`tenant_no`,`appid`,`openid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='服务号用户绑定';
|
|
|
|
CREATE TABLE IF NOT EXISTS `sys_wechat_approval_cycle` (
|
|
`id` varchar(64) NOT NULL,
|
|
`business_type` varchar(32) NOT NULL,
|
|
`business_id` varchar(64) NOT NULL,
|
|
`cycle_id` varchar(64) NOT NULL,
|
|
`submitted_at` datetime NOT NULL,
|
|
`assignments` text NOT NULL,
|
|
`tenant_no` varchar(64) NOT NULL,
|
|
`order_num` int DEFAULT NULL,
|
|
`remark` varchar(512) DEFAULT NULL,
|
|
`create_by` varchar(64) DEFAULT NULL,
|
|
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`update_by` varchar(64) DEFAULT NULL,
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`del_flag` smallint NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_oa_cycle_business` (`tenant_no`,`business_type`,`business_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='服务号通知审批轮次';
|
|
|
|
CREATE TABLE IF NOT EXISTS `sys_wechat_notice` (
|
|
`id` varchar(64) NOT NULL,
|
|
`appid` varchar(64) NOT NULL,
|
|
`business_type` varchar(32) NOT NULL,
|
|
`business_id` varchar(64) NOT NULL,
|
|
`cycle_id` varchar(64) NOT NULL,
|
|
`event_key` varchar(64) NOT NULL,
|
|
`kind` varchar(16) NOT NULL,
|
|
`task_key` varchar(256) NOT NULL DEFAULT '',
|
|
`recipient_id` varchar(64) NOT NULL,
|
|
`result` varchar(16) DEFAULT NULL,
|
|
`template_id` varchar(128) NOT NULL,
|
|
`payload` text NOT NULL,
|
|
`page` varchar(1024) NOT NULL DEFAULT '',
|
|
`status` varchar(16) NOT NULL DEFAULT 'pending',
|
|
`attempts` int NOT NULL DEFAULT 0,
|
|
`next_attempt_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`claimed_at` datetime DEFAULT NULL,
|
|
`sent_at` datetime DEFAULT NULL,
|
|
`message_id` varchar(128) DEFAULT NULL,
|
|
`last_error` varchar(256) DEFAULT NULL,
|
|
`tenant_no` varchar(64) NOT NULL,
|
|
`order_num` int DEFAULT NULL,
|
|
`remark` varchar(512) DEFAULT NULL,
|
|
`create_by` varchar(64) DEFAULT NULL,
|
|
`create_dept_id` varchar(64) NOT NULL DEFAULT '',
|
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`update_by` varchar(64) DEFAULT NULL,
|
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`del_flag` smallint NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_oa_notice_event` (`event_key`),
|
|
KEY `idx_oa_notice_due` (`status`,`next_attempt_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='服务号审批通知发送记录';
|