## 功能说明 为短信资源管理提供平台剩余、分配、历史与使用记录的数据库表结构，支撑前后端查询与写入。 ## 表结构设计 ```sql CREATE TABLE sms_platform_resource ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', platform_id BIGINT NOT NULL COMMENT '平台ID', platform_name VARCHAR(100) NOT NULL COMMENT '平台名称', provider_name VARCHAR(100) NOT NULL COMMENT '服务商名称', remaining_count INT NOT NULL DEFAULT 0 COMMENT '平台剩余条数', warning_threshold INT NOT NULL DEFAULT 1000 COMMENT '告警阈值', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_platform (platform_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信平台资源'; CREATE TABLE sms_platform_adjust_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', platform_id BIGINT NOT NULL COMMENT '平台ID', adjust_type TINYINT NOT NULL COMMENT '调整类型:1增加,2减少', change_count INT NOT NULL COMMENT '调整数量(正数)', before_count INT NOT NULL COMMENT '调整前剩余', after_count INT NOT NULL COMMENT '调整后剩余', operator_id BIGINT NOT NULL COMMENT '操作人ID', operator_name VARCHAR(50) NOT NULL COMMENT '操作人姓名', remark VARCHAR(255) DEFAULT NULL COMMENT '备注', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_platform_created (platform_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台资源调整日志'; CREATE TABLE sms_community_quota ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', platform_id BIGINT NOT NULL COMMENT '平台ID', community_id BIGINT NOT NULL COMMENT '小区ID', community_name VARCHAR(100) NOT NULL COMMENT '小区名称', allocated_total INT NOT NULL DEFAULT 0 COMMENT '累计分配条数', used_count INT NOT NULL DEFAULT 0 COMMENT '已使用条数', remaining_count INT NOT NULL DEFAULT 0 COMMENT '当前剩余条数', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_platform_community (platform_id, community_id), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区短信额度'; CREATE TABLE sms_community_alloc_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', platform_id BIGINT NOT NULL COMMENT '平台ID', community_id BIGINT NOT NULL COMMENT '小区ID', community_name VARCHAR(100) NOT NULL COMMENT '小区名称', method TINYINT NOT NULL COMMENT '分配方式:1按金额,2按条数', amount DECIMAL(10,2) DEFAULT NULL COMMENT '分配金额', convert_rate DECIMAL(10,4) NOT NULL COMMENT '转换系数', count INT NOT NULL COMMENT '对应条数(四舍五入后)', before_remaining INT NOT NULL COMMENT '分配前剩余', after_remaining INT NOT NULL COMMENT '分配后剩余', effective_start DATETIME DEFAULT NULL COMMENT '有效期起', effective_end DATETIME DEFAULT NULL COMMENT '有效期止, NULL长期', remark VARCHAR(255) DEFAULT NULL COMMENT '备注', operator_id BIGINT NOT NULL COMMENT '操作人ID', operator_name VARCHAR(50) NOT NULL COMMENT '操作人姓名', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_platform_comm_created (platform_id, community_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区分配历史'; CREATE TABLE sms_community_usage ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键', platform_id BIGINT NOT NULL COMMENT '平台ID', community_id BIGINT NOT NULL COMMENT '小区ID', push_time DATETIME NOT NULL COMMENT '推送时间', push_name VARCHAR(100) NOT NULL COMMENT '推送名称', content TEXT NOT NULL COMMENT '推送内容', status TINYINT NOT NULL COMMENT '推送状态:1成功,2部分成功,3失败', success_count INT NOT NULL DEFAULT 0 COMMENT '接收成功数', fail_count INT NOT NULL DEFAULT 0 COMMENT '接收失败数', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_platform_comm_push (platform_id, community_id, push_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区短信使用记录'; ``` ## 字段说明 sms_platform_resource: platform_id(必填, 平台标识), platform_name, provider_name, remaining_count, warning_threshold。 sms_platform_adjust_log: adjust_type(1增加/2减少), change_count(正整数), before_count, after_count, operator信息, remark。 sms_community_quota: allocated_total, used_count, remaining_count支撑计算已分配未使用。 sms_community_alloc_log: method、amount、convert_rate、count、有效期、before/after剩余、remark、操作人。 sms_community_usage: push_name、content、status、success_count、fail_count、push_time。 ## 索引设计 - PRIMARY KEY: 各表id - INDEX: idx_platform, idx_platform_created, idx_platform_community, idx_created, idx_platform_comm_created, idx_platform_comm_push ## 迁移脚本路径 - migrations/202409_create_sms_resource_tables.sql ## 验收标准 - [ ] 表结构创建成功 - [ ] 索引创建正确 - [ ] 字段注释完整