功能说明

设计并创建短信平台资源、平台调整历史、小区资源、小区配置历史、小区使用明细等表结构，支撑平台总额度配置、分配、监控与历史记录查询。

表结构设计

CREATE TABLE sms_platform (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '平台ID',
  name VARCHAR(100) NOT NULL COMMENT '平台名称',
  provider_name VARCHAR(100) NOT NULL COMMENT '服务商名称',
  remain_count INT NOT NULL DEFAULT 0 COMMENT '平台剩余条数',
  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_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信平台资源';

CREATE TABLE sms_platform_adjust_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
  platform_id BIGINT NOT NULL COMMENT '平台ID',
  adjust_type TINYINT NOT NULL COMMENT '1增加 2减少',
  adjust_amount INT NOT NULL COMMENT '调整数量',
  before_count INT NOT NULL COMMENT '调整前数量',
  after_count INT NOT NULL COMMENT '调整后数量',
  operator VARCHAR(50) NOT NULL COMMENT '操作人',
  remark VARCHAR(200) DEFAULT '' COMMENT '备注',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_platform_created (platform_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台调整历史';

CREATE TABLE sms_community (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '小区ID',
  platform_id BIGINT NOT NULL COMMENT '平台ID',
  name VARCHAR(100) NOT NULL COMMENT '小区名称',
  remain_count INT NOT NULL DEFAULT 0 COMMENT '小区剩余条数',
  used_total INT NOT NULL DEFAULT 0 COMMENT '历史使用总计',
  used_month 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_created (platform_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区资源';

CREATE TABLE sms_community_allocate_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
  community_id BIGINT NOT NULL COMMENT '小区ID',
  platform_id BIGINT NOT NULL COMMENT '平台ID',
  mode TINYINT NOT NULL COMMENT '1按金额 2按条数',
  pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '分配金额',
  convert_ratio DECIMAL(10,4) NOT NULL COMMENT '转换系数',
  sms_count INT NOT NULL COMMENT '分配条数',
  before_remain INT NOT NULL COMMENT '小区分配前剩余',
  after_remain INT NOT NULL COMMENT '小区分配后剩余',
  valid_from DATETIME DEFAULT NULL COMMENT '有效期开始',
  valid_to DATETIME DEFAULT NULL COMMENT '有效期结束，NULL为长期',
  remark VARCHAR(200) DEFAULT '' COMMENT '备注',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  operator VARCHAR(50) NOT NULL COMMENT '操作人',
  INDEX idx_comm_created (community_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区配置历史';

CREATE TABLE sms_usage_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
  community_id BIGINT NOT NULL COMMENT '小区ID',
  platform_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 '0失败 1成功 2部分成功',
  success_count INT NOT NULL DEFAULT 0 COMMENT '成功数',
  fail_count INT NOT NULL DEFAULT 0 COMMENT '失败数',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_comm_time (community_id, push_time DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='小区使用情况';

字段说明

索引设计

• PRIMARY KEY: id
• INDEX: idx_platform_created(platform_id, created_at DESC) for history queries
• INDEX: idx_comm_created(community_id, created_at DESC) for allocation history
• INDEX: idx_comm_time(community_id, push_time DESC) for usage logs

迁移脚本路径

migrations/2024xxxx_create_sms_resource_tables.sql

验收标准

• 表结构创建成功，字符集utf8mb4
• 主外键及索引符合查询场景
• 字段注释完整，默认值和非空约束正确