607 lines
32 KiB
SQL
607 lines
32 KiB
SQL
-- ============================================================
|
|
-- go-sms 全量表结构 DDL(36 张表,Day 11)
|
|
-- 模块前缀:account/channel/route|policy/message|audit|template/billing|settle/stats|system
|
|
-- 规范:InnoDB + utf8mb4;统一 created_at/updated_at;核心业务表含 deleted_at(软删)
|
|
-- 外键:仅强从属关系配置(日志/统计类不加,保障写入吞吐)
|
|
-- ⚠️ 权威结构以 cmd/migrate 为准(单一事实来源);本文件作为 DBA 评审参考,与 AutoMigrate 存在类型差异(BIGINT UNSIGNED vs BIGINT)
|
|
-- ============================================================
|
|
SET NAMES utf8mb4;
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 一、账号体系(7)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `account_user` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`username` VARCHAR(64) NOT NULL COMMENT '登录名',
|
|
`password_hash` VARCHAR(128) NOT NULL COMMENT '口令哈希(bcrypt)',
|
|
`real_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '联系人',
|
|
`role` VARCHAR(32) NOT NULL DEFAULT 'customer' COMMENT '角色:admin/ops/service/group/customer',
|
|
`parent_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级账号(多级代理)',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0禁用',
|
|
`ip_bind` VARCHAR(256) NOT NULL DEFAULT '' COMMENT 'IP绑定(逗号分隔)',
|
|
`balance` BIGINT NOT NULL DEFAULT 0 COMMENT '余额(条)',
|
|
`credit_balance` BIGINT NOT NULL DEFAULT 0 COMMENT '授信余额(允许透支)',
|
|
`svc_team` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '服务团队',
|
|
`expired_at` DATETIME NULL DEFAULT NULL,
|
|
`last_login_at` DATETIME NULL DEFAULT NULL,
|
|
`last_login_ip` VARCHAR(45) NOT NULL DEFAULT '',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_username` (`username`),
|
|
KEY `idx_parent` (`parent_id`),
|
|
KEY `idx_role_status` (`role`,`status`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='账号(用户)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_config` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL COMMENT '所属账号',
|
|
`business` VARCHAR(16) NOT NULL DEFAULT 'sms' COMMENT '业务:sms/intl/video/csp/nullfilter/shortlink',
|
|
`audit_mode` TINYINT NOT NULL DEFAULT 0 COMMENT '0免审 1手动 2自动拒绝',
|
|
`sms_type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '短信类别',
|
|
`fee_mode` TINYINT NOT NULL DEFAULT 0 COMMENT '进账类型',
|
|
`fail_refund` TINYINT NOT NULL DEFAULT 1 COMMENT '失败是否返还(状态开放才返还)',
|
|
`day_limit` INT NOT NULL DEFAULT 0 COMMENT '日限额(0不限)',
|
|
`speed_limit` INT NOT NULL DEFAULT 0 COMMENT '提交速度(0不限,条/秒)',
|
|
`ext_code` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '固定扩展码',
|
|
`sign_limit` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '签名限制(逗号分隔,报备审核后自动填充)',
|
|
`report_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '回执推送地址',
|
|
`uplink_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '上行推送地址',
|
|
`protocol` VARCHAR(16) NOT NULL DEFAULT 'http' COMMENT '接入:http/cmpp/smpp/web',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_account_biz` (`account_id`,`business`),
|
|
KEY `idx_deleted` (`deleted_at`),
|
|
CONSTRAINT `fk_config_account` FOREIGN KEY (`account_id`) REFERENCES `account_user`(`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='账号业务配置';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_price` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`business` VARCHAR(16) NOT NULL DEFAULT 'sms',
|
|
`price` DECIMAL(10,4) NOT NULL DEFAULT 0 COMMENT '单价(元/条)',
|
|
`effective_at` DATETIME NOT NULL COMMENT '生效时间(时间线分时计价)',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_biz_time` (`account_id`,`business`,`effective_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户分时单价';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_role` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`code` VARCHAR(32) NOT NULL COMMENT '角色码',
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`is_built_in` TINYINT NOT NULL DEFAULT 0 COMMENT '内置角色不可删',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_code` (`code`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_permission` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`code` VARCHAR(64) NOT NULL COMMENT '权限码 resource:action',
|
|
`name` VARCHAR(64) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_code` (`code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='权限点';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_role_permission` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`role_id` BIGINT UNSIGNED NOT NULL,
|
|
`permission_id` BIGINT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_role_perm` (`role_id`,`permission_id`),
|
|
CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `account_role`(`id`),
|
|
CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `account_permission`(`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-权限关联';
|
|
|
|
CREATE TABLE IF NOT EXISTS `account_balance_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`biz_type` VARCHAR(16) NOT NULL COMMENT 'recharge/credit/deduct/refund/adjust',
|
|
`amount` BIGINT NOT NULL COMMENT '变动条数(正负)',
|
|
`balance_after` BIGINT NOT NULL COMMENT '变动后余额',
|
|
`ref_id` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '关联单号',
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '操作人',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_time` (`account_id`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='余额变动日志';
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 二、通道体系(6)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `channel_channel` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`code` VARCHAR(32) NOT NULL COMMENT '通道号(接入码)',
|
|
`protocol` VARCHAR(16) NOT NULL COMMENT 'cmpp/http/sgip/smpp',
|
|
`upstream_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所属上家',
|
|
`host` VARCHAR(128) NOT NULL DEFAULT '',
|
|
`port` INT NOT NULL DEFAULT 0,
|
|
`username` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`password` VARCHAR(128) NOT NULL DEFAULT '',
|
|
`conn_count` INT NOT NULL DEFAULT 1 COMMENT '连接数(总速度=速度x连接数)',
|
|
`speed` INT NOT NULL DEFAULT 0 COMMENT '单连接提交速度',
|
|
`ext_length` TINYINT NOT NULL DEFAULT 0 COMMENT '通道号截断位数(0-21)',
|
|
`sign_mode` TINYINT NOT NULL DEFAULT 0 COMMENT '签名处理模式',
|
|
`priority` INT NOT NULL DEFAULT 0 COMMENT '优先级(省网分流)',
|
|
`weight` INT NOT NULL DEFAULT 1 COMMENT '权重比例',
|
|
`is_default` TINYINT NOT NULL DEFAULT 0 COMMENT '默认兜底通道',
|
|
`day_limit` INT NOT NULL DEFAULT 0 COMMENT '日限额(发送限制)',
|
|
`night_limit` TINYINT NOT NULL DEFAULT 0 COMMENT '夜间单链路限速(4条/s)',
|
|
`black_keywords` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '通道黑关键字',
|
|
`block_regions` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '屏蔽地区',
|
|
`template_mode` TINYINT NOT NULL DEFAULT 0 COMMENT '上游模板ID模式({%var%})',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0停用',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_code` (`code`),
|
|
KEY `idx_protocol_status` (`protocol`,`status`),
|
|
KEY `idx_upstream` (`upstream_id`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='短信通道';
|
|
|
|
CREATE TABLE IF NOT EXISTS `channel_upstream` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`contact` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`phone` VARCHAR(32) NOT NULL DEFAULT '',
|
|
`category` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '通道分类',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='上家(通道方)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `channel_price` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL,
|
|
`price` DECIMAL(10,4) NOT NULL DEFAULT 0 COMMENT '成本单价(元/条)',
|
|
`effective_at` DATETIME NOT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_channel_time` (`channel_id`,`effective_at`),
|
|
CONSTRAINT `fk_cprice_channel` FOREIGN KEY (`channel_id`) REFERENCES `channel_channel`(`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通道分时成本价';
|
|
|
|
CREATE TABLE IF NOT EXISTS `channel_group` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通道组(四步路由容器)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `channel_group_item` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`group_id` BIGINT UNSIGNED NOT NULL,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL,
|
|
`step` TINYINT NOT NULL DEFAULT 3 COMMENT '路由步骤:1条件过滤 2优先 3权重 4兜底',
|
|
`priority` INT NOT NULL DEFAULT 0,
|
|
`weight` INT NOT NULL DEFAULT 1,
|
|
`region` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '省网分流',
|
|
`carrier` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '运营商',
|
|
`keyword` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '优先发送关键字',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_group_channel` (`group_id`,`channel_id`),
|
|
KEY `idx_channel` (`channel_id`),
|
|
CONSTRAINT `fk_item_group` FOREIGN KEY (`group_id`) REFERENCES `channel_group`(`id`),
|
|
CONSTRAINT `fk_item_channel` FOREIGN KEY (`channel_id`) REFERENCES `channel_channel`(`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通道组成员(路由步骤参数)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `channel_blocked_code` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL,
|
|
`code` VARCHAR(64) NOT NULL COMMENT '屏蔽子端口号',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_channel_code` (`channel_id`,`code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='屏蔽码号';
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 三、路由与策略(5)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `route_rule` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=全局',
|
|
`channel_id` BIGINT UNSIGNED NOT NULL,
|
|
`rule_type` VARCHAR(16) NOT NULL COMMENT 'keyword/region/account/content',
|
|
`rule_config` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '匹配值(如关键字/账号/地区)',
|
|
`priority` INT NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_type` (`account_id`,`rule_type`,`status`),
|
|
KEY `idx_rr_channel` (`channel_id`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='导流/路由规则';
|
|
|
|
CREATE TABLE IF NOT EXISTS `policy_blacklist` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`mobile` VARCHAR(20) NOT NULL,
|
|
`source` TINYINT NOT NULL DEFAULT 1 COMMENT '1手工 2上行拒收自动 3第三方同步',
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归属账号(0全局)',
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_mobile_account` (`mobile`,`account_id`),
|
|
KEY `idx_created` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='本地黑名单库';
|
|
|
|
CREATE TABLE IF NOT EXISTS `policy_whitelist` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`mobile` VARCHAR(20) NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_mobile_account` (`mobile`,`account_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='白名单(忽略频次与黑名单)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `policy_keyword` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`word` VARCHAR(128) NOT NULL,
|
|
`level` TINYINT NOT NULL DEFAULT 1 COMMENT '1一级黑(强制失败) 2二级黑(免审专用) 3白(免审直通)',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_word_level` (`word`,`level`),
|
|
KEY `idx_level_status` (`level`,`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='黑白关键字';
|
|
|
|
CREATE TABLE IF NOT EXISTS `policy_frequency` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`dim` TINYINT NOT NULL DEFAULT 1 COMMENT '1同号码 2同内容',
|
|
`window_unit` TINYINT NOT NULL DEFAULT 1 COMMENT '1自然日 2滚动24h',
|
|
`max_count` INT NOT NULL DEFAULT 1 COMMENT '窗口内最大条数',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_status` (`account_id`,`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='频次策略';
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 四、消息与审核(6)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `message_task` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`task_no` VARCHAR(32) NOT NULL COMMENT '任务号(雪花)',
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`send_type` TINYINT NOT NULL DEFAULT 1 COMMENT '1群发 2个性 3定时 4文件',
|
|
`content` TEXT NULL,
|
|
`sign` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`mobile_count` INT NOT NULL DEFAULT 0,
|
|
`success_count` INT NOT NULL DEFAULT 0,
|
|
`fail_count` INT NOT NULL DEFAULT 0,
|
|
`unknown_count` INT NOT NULL DEFAULT 0,
|
|
`fee_total` DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
`audit_status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审 1通过 2拒绝 3免审 4超时失败',
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待发 1发送中 2完成 3取消 4失败',
|
|
`schedule_at` DATETIME NULL DEFAULT NULL COMMENT '定时发送时间',
|
|
`finished_at` DATETIME NULL DEFAULT NULL,
|
|
`submit_ip` VARCHAR(45) NOT NULL DEFAULT '',
|
|
`call_data` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '外部API回传数据(回执推送带回)',
|
|
`business` VARCHAR(16) NOT NULL DEFAULT 'sms' COMMENT '业务类型:sms/video(Day49 biz_type路由)',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_task_no` (`task_no`),
|
|
KEY `idx_account_time` (`account_id`,`created_at`),
|
|
KEY `idx_status_schedule` (`status`,`schedule_at`),
|
|
KEY `idx_audit` (`audit_status`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='发送任务(批次)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `message_detail` (
|
|
`id` BIGINT NOT NULL COMMENT '雪花ID(全局唯一,回执配对键)',
|
|
`task_id` BIGINT UNSIGNED NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`mobile` VARCHAR(20) NOT NULL,
|
|
`content` VARCHAR(1024) NOT NULL DEFAULT '',
|
|
`province` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '归属地(号码库)',
|
|
`carrier` VARCHAR(8) NOT NULL DEFAULT '' COMMENT '运营商(含携转)',
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待发 1已提交 2成功 3失败 4未知 5拦截',
|
|
`error_code` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'UT:xxxx/通道码',
|
|
`fee` DECIMAL(10,4) NOT NULL DEFAULT 0,
|
|
`send_at` DATETIME NULL DEFAULT NULL,
|
|
`report_at` DATETIME NULL DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_task` (`task_id`),
|
|
KEY `idx_account_mobile` (`account_id`,`mobile`),
|
|
KEY `idx_status_time` (`status`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='下行明细(热数据5天,历史入ClickHouse)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `message_report` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`detail_id` BIGINT NOT NULL COMMENT '关联明细雪花ID',
|
|
`channel_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`status` VARCHAR(16) NOT NULL COMMENT 'DELIVRD/FAILURE/UNKNOWN(归一)',
|
|
`raw_code` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '通道原始状态码',
|
|
`error_code` VARCHAR(16) NOT NULL DEFAULT '',
|
|
`report_at` DATETIME NOT NULL,
|
|
`push_status` TINYINT NOT NULL DEFAULT 0 COMMENT '客户推送状态(0未推 1成功 2失败)',
|
|
`pushed_at` DATETIME NULL DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_detail` (`detail_id`),
|
|
KEY `idx_push` (`push_status`,`created_at`),
|
|
KEY `idx_report_time` (`report_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='状态回执(30天重推窗口)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `message_uplink` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`dest_code` VARCHAR(32) NOT NULL COMMENT '上行码号(匹配分配)',
|
|
`src_mobile` VARCHAR(20) NOT NULL,
|
|
`content` VARCHAR(512) NOT NULL DEFAULT '',
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '归属(匹配失败归admin)',
|
|
`match_task_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '匹配的下行任务(15天窗口)',
|
|
`channel_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`push_status` TINYINT NOT NULL DEFAULT 0,
|
|
`recv_at` DATETIME NOT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_time` (`account_id`,`created_at`),
|
|
KEY `idx_dest_mobile` (`dest_code`,`src_mobile`,`recv_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='上行记录';
|
|
|
|
CREATE TABLE IF NOT EXISTS `uplink_code` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL COMMENT '归属账号',
|
|
`code` VARCHAR(32) NOT NULL COMMENT '码号(精确码全串/前缀码前段)',
|
|
`match_type` TINYINT NOT NULL DEFAULT 1 COMMENT '1精确 2前缀',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_code` (`code`),
|
|
KEY `idx_uplink_account_time` (`account_id`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='上行码号分配(Day49 精确>前缀两级匹配)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `audit_record` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`biz_type` VARCHAR(16) NOT NULL COMMENT 'sms/template/sign/group/shortlink',
|
|
`biz_id` BIGINT UNSIGNED NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`content` TEXT NULL,
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审 1通过 2拒绝',
|
|
`auditor` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`audit_at` DATETIME NULL DEFAULT NULL,
|
|
`reason` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_biz` (`biz_type`,`biz_id`),
|
|
KEY `idx_status_time` (`status`,`created_at`),
|
|
KEY `idx_account` (`account_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审核工作流(48h未审自动失败)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `template_sms` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`content` VARCHAR(1024) NOT NULL,
|
|
`variables` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '变量列表(逗号分隔)',
|
|
`exact_match` TINYINT NOT NULL DEFAULT 1 COMMENT '精确模板(仅创建者可用)',
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待审 1通过 2拒绝',
|
|
`audit_at` DATETIME NULL DEFAULT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_account_status` (`account_id`,`status`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='短信模板';
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 五、计费与结算(5)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `billing_order` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`order_no` VARCHAR(32) NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '充值金额(元)',
|
|
`count` BIGINT NOT NULL DEFAULT 0 COMMENT '充值条数',
|
|
`pay_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式',
|
|
`pay_status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待确认 1已到账 2失败',
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '确认人(二级权限)',
|
|
`paid_at` DATETIME NULL DEFAULT NULL,
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_order_no` (`order_no`),
|
|
KEY `idx_account_time` (`account_id`,`created_at`),
|
|
KEY `idx_pay_status` (`pay_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值订单(付款审核确认)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `billing_consume_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`business` VARCHAR(16) NOT NULL DEFAULT 'sms',
|
|
`stat_date` DATE NOT NULL COMMENT '归属日',
|
|
`submit_count` INT NOT NULL DEFAULT 0,
|
|
`success_count` INT NOT NULL DEFAULT 0,
|
|
`fail_count` INT NOT NULL DEFAULT 0,
|
|
`fee_total` DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_account_date` (`account_id`,`business`,`stat_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消耗日志(按账号/业务/天)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `settle_customer` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`settle_no` VARCHAR(32) NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`period` VARCHAR(7) NOT NULL COMMENT '账期 YYYY-MM',
|
|
`success_count` BIGINT NOT NULL DEFAULT 0,
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '应收(元)',
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0草稿 1已确认',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_account_period` (`account_id`,`period`),
|
|
KEY `idx_period` (`period`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='下家(客户)月度结算单';
|
|
|
|
CREATE TABLE IF NOT EXISTS `settle_upstream` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`settle_no` VARCHAR(32) NOT NULL,
|
|
`upstream_id` BIGINT UNSIGNED NOT NULL,
|
|
`period` VARCHAR(7) NOT NULL,
|
|
`success_count` BIGINT NOT NULL DEFAULT 0,
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '应付成本(元)',
|
|
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0草稿 1已确认 2已结账',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_upstream_period` (`upstream_id`,`period`),
|
|
KEY `idx_period` (`period`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='上家月度结算单';
|
|
|
|
CREATE TABLE IF NOT EXISTS `settle_record` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`settle_no` VARCHAR(32) NOT NULL,
|
|
`direction` TINYINT NOT NULL DEFAULT 1 COMMENT '1下家 2上家',
|
|
`amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
|
|
`pay_type` VARCHAR(16) NOT NULL DEFAULT '',
|
|
`operator` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`settled_at` DATETIME NOT NULL,
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_settle_no` (`settle_no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结账记录';
|
|
|
|
-- -----------------------------------------------------------
|
|
-- 六、统计与运维(7)
|
|
-- -----------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `stats_user_daily` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`stat_date` DATE NOT NULL,
|
|
`submit_count` INT NOT NULL DEFAULT 0,
|
|
`success_count` INT NOT NULL DEFAULT 0,
|
|
`fail_count` INT NOT NULL DEFAULT 0,
|
|
`unknown_count` INT NOT NULL DEFAULT 0,
|
|
`fee_total` DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_account_date` (`account_id`,`stat_date`),
|
|
KEY `idx_date` (`stat_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户日统计(偶数整点刷新)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `stats_channel_daily` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`channel_id` BIGINT UNSIGNED NOT NULL,
|
|
`stat_date` DATE NOT NULL,
|
|
`submit_count` INT NOT NULL DEFAULT 0,
|
|
`success_count` INT NOT NULL DEFAULT 0,
|
|
`fail_count` INT NOT NULL DEFAULT 0,
|
|
`unknown_count` INT NOT NULL DEFAULT 0,
|
|
`cost_total` DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
`est_balance` BIGINT NOT NULL DEFAULT 0 COMMENT '通道估算余额(2h刷新)',
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_channel_date` (`channel_id`,`stat_date`),
|
|
KEY `idx_date` (`stat_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通道日统计';
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_config` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`cfg_key` VARCHAR(64) NOT NULL,
|
|
`cfg_value` VARCHAR(1024) NOT NULL DEFAULT '',
|
|
`remark` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`updated_by` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_key` (`cfg_key`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统参数配置';
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_alert_rule` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`target_type` VARCHAR(16) NOT NULL COMMENT 'account/channel/system',
|
|
`target_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`metric` VARCHAR(32) NOT NULL COMMENT 'balance/error_rate/latency...',
|
|
`op` VARCHAR(4) NOT NULL DEFAULT 'lt' COMMENT 'lt/lte/gt/gte',
|
|
`threshold` DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
`notify_type` VARCHAR(32) NOT NULL DEFAULT 'wechat' COMMENT 'wechat/sms',
|
|
`notify_to` VARCHAR(128) NOT NULL DEFAULT '',
|
|
`cooldown_min` INT NOT NULL DEFAULT 30 COMMENT '告警冷却(分钟)',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`deleted_at` DATETIME NULL DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_target` (`target_type`,`target_id`,`status`),
|
|
KEY `idx_deleted` (`deleted_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='告警规则(余额/通道)';
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_login_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`username` VARCHAR(64) NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`ip` VARCHAR(45) NOT NULL DEFAULT '',
|
|
`user_agent` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`ok` TINYINT NOT NULL DEFAULT 1,
|
|
`fail_reason` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`login_at` DATETIME NOT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_username_time` (`username`,`login_at`),
|
|
KEY `idx_ip` (`ip`),
|
|
KEY `idx_login_at` (`login_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登录审计';
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_operate_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`operator` VARCHAR(64) NOT NULL,
|
|
`account_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`module` VARCHAR(32) NOT NULL,
|
|
`action` VARCHAR(32) NOT NULL,
|
|
`target` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`detail` VARCHAR(1024) NOT NULL DEFAULT '',
|
|
`ip` VARCHAR(45) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_operator_time` (`operator`,`created_at`),
|
|
KEY `idx_module_time` (`module`,`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志';
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_error_code` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`code` VARCHAR(16) NOT NULL COMMENT 'UT:xxxx/-1/-3',
|
|
`name` VARCHAR(64) NOT NULL,
|
|
`raw_value` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '通道转义值映射',
|
|
`action` TINYINT NOT NULL DEFAULT 0 COMMENT '处置:0失败 1重发 2入审',
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_code` (`code`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='错误代码表(UT:xxxx体系)';
|