aivfo-tl-3.0/sql/migrations/2026-06-18-operation-log.sql

-- 全量操作日志表（操作审计）。库:log。与 system_log 并列,共享 trace_id 可 join。见 需求文档/14 §5。
-- 幂等:DROP IF EXISTS + CREATE。
-- ★适用场景（G4-2 已合并 base）:全新部署不需要本文件——base 脚本 sql/log.sql 已含 operation_log,
--   跑 sql/init-database.sh 一次到位;本文件仅供已部署旧 log 库增量补表。
DROP TABLE IF EXISTS `operation_log`;
CREATE TABLE `operation_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `trace_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '全链路串联ID',
  `parent_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '父子调用层级ID',
  `op_time` datetime(3) NULL DEFAULT NULL COMMENT '操作时间(毫秒)',
  `project` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '端/服务:operate/front/tl-control...',
  `module` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '功能模块(可读):对焦/串口/患者...',
  `operation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作(可读):一键标定/打开端口...',
  `operator` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '谁:登录用户/工程师/系统/设备SN',
  `input` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '输入(JSON;大对象只存文件名/关键字段)',
  `output` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '输出(JSON;同上)',
  `result` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '结果:成功/失败',
  `error` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '报错:消息+堆栈摘要',
  `elapsed_ms` bigint NULL DEFAULT NULL COMMENT '耗时(ms)',
  `level` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '级别(操作级默认)',
  `house_sn` int NULL DEFAULT NULL COMMENT '仓室号(便于按舱过滤)',
  `well_sn` int NULL DEFAULT NULL COMMENT 'well号',
  `tl_sn` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '设备SN',
  `host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '来源主机/IP',
  `create_time` datetime(3) NULL DEFAULT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_trace`(`trace_id`) USING BTREE COMMENT '按trace_id拉链路/join system_log',
  INDEX `idx_query`(`project`, `module`, `op_time`) USING BTREE COMMENT '按项目>模块>时间查询',
  INDEX `idx_device`(`tl_sn`, `house_sn`, `op_time`) USING BTREE COMMENT '按设备/舱查询',
  INDEX `idx_optime`(`op_time`) USING BTREE COMMENT '保留期清理用'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '全量操作日志(操作审计);与system_log经trace_id关联' ROW_FORMAT = Dynamic;