buff3.0/backend-client/migrations/20260202_add_history_tables.sql

-- Migration: 添加客户端历史记录表
-- Date: 2026-02-02
-- Description: 添加缺失的历史记录表：sessions_his, system_config_his, api_nonces_his
--              这些表用于记录主表操作的历史记录，支持审计和回溯

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ==================== 会话历史记录表 ====================

-- ----------------------------
-- Table structure for sessions_his (会话表历史记录)
-- ----------------------------
DROP TABLE IF EXISTS `sessions_his`;
CREATE TABLE `sessions_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `machine_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机器码',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE/RENEW',
  PRIMARY KEY (`id`, `his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会话表历史记录';

-- ==================== 系统配置历史记录表 ====================

-- ----------------------------
-- Table structure for system_config_his (系统配置表历史记录)
-- ----------------------------
DROP TABLE IF EXISTS `system_config_his`;
CREATE TABLE `system_config_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置键名',
  `config_value` text COLLATE utf8mb4_unicode_ci COMMENT '配置值',
  `config_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '类型: int/string/bool/json',
  `app_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所属应用',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '0' COMMENT '是否系统配置',
  `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`, `his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`),
  KEY `idx_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci COMMENT='系统配置表历史记录';

-- ==================== API Nonce历史记录表 ====================

-- ----------------------------
-- Table structure for api_nonces_his (API Nonce表历史记录)
-- ----------------------------
DROP TABLE IF EXISTS `api_nonces_his`;
CREATE TABLE `api_nonces_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `nonce` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '随机字符串',
  `endpoint` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话ID',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`, `his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`),
  KEY `idx_nonce` (`nonce`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Nonce表历史记录';

SET FOREIGN_KEY_CHECKS = 1;