shanxia2.0/docs/sql/20261027_wechat_approval_notices.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='服务号审批通知发送记录';