zsElectric-boot/sql/mysql/agent_aftersales_m1.sql

CREATE TABLE IF NOT EXISTS agent_tool_registry (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  tool_name VARCHAR(128) NOT NULL COMMENT 'MCP工具名',
  tool_version VARCHAR(32) NOT NULL DEFAULT 'v1' COMMENT '工具版本',
  description VARCHAR(512) NOT NULL COMMENT '工具说明',
  transport VARCHAR(32) NOT NULL DEFAULT 'mcp' COMMENT '工具传输协议',
  handler_bean VARCHAR(192) NOT NULL COMMENT 'Java处理器Bean或路由标识',
  risk_level VARCHAR(16) NOT NULL COMMENT '风险等级: LOW, MEDIUM, HIGH',
  enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  require_approval TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否需要审批',
  idempotent TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否支持幂等',
  input_schema_json JSON NOT NULL COMMENT '输入参数JSON Schema',
  output_schema_json JSON NULL COMMENT '输出结果JSON Schema',
  allowed_roles JSON NULL COMMENT '允许调用的角色列表',
  sensitive_fields JSON NULL COMMENT '需要脱敏的字段列表',
  rate_limit_per_minute INT NOT NULL DEFAULT 60 COMMENT '单工具分钟限流',
  timeout_ms INT NOT NULL DEFAULT 10000 COMMENT '工具超时时间',
  remark VARCHAR(512) NULL COMMENT '备注',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',
  UNIQUE KEY uk_agent_tool_name_version (tool_name, tool_version),
  KEY idx_agent_tool_enabled_risk (enabled, risk_level),
  KEY idx_agent_tool_handler (handler_bean)
) COMMENT='Agent MCP工具注册表';

CREATE TABLE IF NOT EXISTS agent_approval (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  approval_id VARCHAR(64) NOT NULL COMMENT '审批ID',
  trace_id VARCHAR(64) NOT NULL COMMENT 'Agent运行链路ID',
  pending_action_id BIGINT NULL COMMENT '关联冻结动作ID',
  biz_type VARCHAR(64) NOT NULL COMMENT '业务类型',
  biz_no VARCHAR(128) NULL COMMENT '业务编号',
  risk_level VARCHAR(16) NOT NULL COMMENT '风险等级',
  status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态',
  request_user_id BIGINT NULL COMMENT '发起用户ID',
  request_username VARCHAR(128) NULL COMMENT '发起用户名',
  request_reason VARCHAR(1024) NULL COMMENT '申请原因',
  risk_reason VARCHAR(1024) NULL COMMENT '命中高风险的原因',
  policy_evidence_json JSON NULL COMMENT '政策依据和RAG命中证据',
  requested_amount DECIMAL(10,2) NULL COMMENT '申请金额',
  approver_id BIGINT NULL COMMENT '审批人ID',
  approver_username VARCHAR(128) NULL COMMENT '审批人名称',
  decision_comment VARCHAR(1024) NULL COMMENT '审批意见',
  decision_time DATETIME NULL COMMENT '审批时间',
  expires_at DATETIME NOT NULL COMMENT '审批过期时间',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',
  UNIQUE KEY uk_agent_approval_id (approval_id),
  KEY idx_agent_approval_trace (trace_id),
  KEY idx_agent_approval_status_time (status, create_time),
  KEY idx_agent_approval_request_user (request_user_id, status),
  KEY idx_agent_approval_biz (biz_type, biz_no)
) COMMENT='Agent高风险动作审批表';

CREATE TABLE IF NOT EXISTS agent_pending_action (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  action_id VARCHAR(64) NOT NULL COMMENT '冻结动作ID',
  approval_id VARCHAR(64) NOT NULL COMMENT '关联审批ID',
  trace_id VARCHAR(64) NOT NULL COMMENT 'Agent运行链路ID',
  tool_name VARCHAR(128) NOT NULL COMMENT '待执行MCP工具名',
  tool_version VARCHAR(32) NOT NULL DEFAULT 'v1' COMMENT '工具版本',
  risk_level VARCHAR(16) NOT NULL COMMENT '风险等级',
  params_json JSON NOT NULL COMMENT '冻结后的工具参数',
  params_hash CHAR(64) NOT NULL COMMENT '参数SHA-256摘要',
  idempotency_key VARCHAR(128) NOT NULL COMMENT '幂等键',
  execute_status VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '执行状态',
  execute_result_json JSON NULL COMMENT '执行结果',
  error_message TEXT NULL COMMENT '失败原因',
  retry_count INT NOT NULL DEFAULT 0 COMMENT '重试次数',
  executed_at DATETIME NULL COMMENT '执行完成时间',
  expires_at DATETIME NOT NULL COMMENT '冻结动作过期时间',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',
  UNIQUE KEY uk_agent_action_id (action_id),
  UNIQUE KEY uk_agent_action_idempotency (idempotency_key),
  KEY idx_agent_action_approval (approval_id),
  KEY idx_agent_action_trace (trace_id),
  KEY idx_agent_action_status_time (execute_status, create_time),
  KEY idx_agent_action_tool (tool_name, tool_version)
) COMMENT='Agent审批后待恢复执行动作表';

CREATE TABLE IF NOT EXISTS agent_audit_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  trace_id VARCHAR(64) NOT NULL COMMENT 'Agent运行链路ID',
  span_id VARCHAR(64) NULL COMMENT '步骤Span ID',
  parent_span_id VARCHAR(64) NULL COMMENT '父级Span ID',
  approval_id VARCHAR(64) NULL COMMENT '关联审批ID',
  pending_action_id VARCHAR(64) NULL COMMENT '关联冻结动作ID',
  actor_type VARCHAR(32) NOT NULL COMMENT '操作者类型',
  actor_id VARCHAR(64) NULL COMMENT '操作者ID',
  actor_name VARCHAR(128) NULL COMMENT '操作者名称',
  step VARCHAR(64) NOT NULL COMMENT '步骤',
  event_type VARCHAR(64) NOT NULL COMMENT '事件类型',
  tool_name VARCHAR(128) NULL COMMENT '工具名',
  risk_level VARCHAR(16) NULL COMMENT '风险等级',
  input_json JSON NULL COMMENT '输入快照',
  output_json JSON NULL COMMENT '输出快照',
  status VARCHAR(32) NOT NULL COMMENT '状态',
  error_message TEXT NULL COMMENT '错误信息',
  duration_ms BIGINT NULL COMMENT '步骤耗时毫秒',
  ip_address VARCHAR(64) NULL COMMENT '客户端IP',
  user_agent VARCHAR(512) NULL COMMENT '客户端User-Agent',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  KEY idx_agent_audit_trace_time (trace_id, create_time),
  KEY idx_agent_audit_approval (approval_id),
  KEY idx_agent_audit_action (pending_action_id),
  KEY idx_agent_audit_tool_time (tool_name, create_time),
  KEY idx_agent_audit_status_time (status, create_time)
) COMMENT='Agent全链路审计日志表';

CREATE TABLE IF NOT EXISTS aftersales_work_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  work_order_no VARCHAR(64) NOT NULL COMMENT '售后工单号',
  trace_id VARCHAR(64) NOT NULL COMMENT 'Agent运行链路ID',
  order_no VARCHAR(128) NOT NULL COMMENT '充电订单号',
  charge_record_id BIGINT NULL COMMENT '充电记录ID',
  user_id BIGINT NULL COMMENT '用户ID',
  phone_masked VARCHAR(64) NULL COMMENT '脱敏手机号',
  issue_type VARCHAR(64) NOT NULL COMMENT '问题类型',
  issue_level VARCHAR(16) NOT NULL DEFAULT 'normal' COMMENT '问题等级',
  source VARCHAR(32) NOT NULL DEFAULT 'agent' COMMENT '来源',
  suggestion TEXT NOT NULL COMMENT 'Agent处理建议',
  policy_evidence_json JSON NULL COMMENT '政策依据和RAG命中证据',
  order_snapshot_json JSON NULL COMMENT '订单和充电记录快照',
  status VARCHAR(32) NOT NULL DEFAULT 'created' COMMENT '状态',
  created_by_agent TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否Agent创建',
  created_by VARCHAR(64) NULL COMMENT '创建人',
  assigned_to VARCHAR(64) NULL COMMENT '处理人',
  resolve_result TEXT NULL COMMENT '处理结果',
  closed_at DATETIME NULL COMMENT '关闭时间',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',
  UNIQUE KEY uk_aftersales_work_order_no (work_order_no),
  KEY idx_aftersales_order_no (order_no),
  KEY idx_aftersales_trace (trace_id),
  KEY idx_aftersales_status_time (status, create_time),
  KEY idx_aftersales_issue_type (issue_type, create_time)
) COMMENT='售后工单表';

INSERT INTO agent_tool_registry
  (tool_name, tool_version, description, transport, handler_bean, risk_level, enabled, require_approval, idempotent, input_schema_json, output_schema_json)
VALUES
  ('query_charge_order', 'v1', '查询充电订单', 'mcp', 'defaultAgentBusinessToolExecutor', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('query_charge_session', 'v1', '查询充电记录', 'mcp', 'defaultAgentBusinessToolExecutor', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('retrieve_aftersales_policy', 'v1', '检索售后政策', 'mcp', 'defaultAgentBusinessToolExecutor', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('create_aftersales_work_order', 'v1', '创建售后工单', 'mcp', 'defaultAgentBusinessToolExecutor', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('request_refund', 'v1', '发起退款审批', 'mcp', 'defaultAgentBusinessToolExecutor', 'HIGH', 1, 1, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('request_compensation', 'v1', '发起补偿审批', 'mcp', 'defaultAgentBusinessToolExecutor', 'HIGH', 1, 1, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('resume_after_approval', 'v1', '审批后恢复冻结动作', 'mcp', 'agentToolOrchestrator', 'HIGH', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('get_approval_status', 'v1', '查询审批状态', 'mcp', 'agentToolOrchestrator', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT()),
  ('write_agent_audit', 'v1', '写入Agent审计日志', 'mcp', 'defaultAgentBusinessToolExecutor', 'LOW', 1, 0, 1, JSON_OBJECT(), JSON_OBJECT())
ON DUPLICATE KEY UPDATE
  description = VALUES(description),
  handler_bean = VALUES(handler_bean),
  risk_level = VALUES(risk_level),
  enabled = VALUES(enabled),
  require_approval = VALUES(require_approval),
  update_time = CURRENT_TIMESTAMP;