Files

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体系)';