| 123456789101112131415161718192021222324252627282930313233343536373839404142434445 |
- -- ====================================================================
- -- 云端代理信誉分与指标埋点系统 (Proxy Reputation API) 数据库建表脚本
- -- 适用数据库: MySQL 5.7+ / 8.0+ / MariaDB
- -- 字符集: utf8mb4
- -- ====================================================================
- -- 1. IP 维度信誉状态表
- CREATE TABLE IF NOT EXISTS `proxy_reputation` (
- `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
- `plugin_name` VARCHAR(64) NOT NULL COMMENT '插件名称,如 vfs_plugin',
- `egress_ip` VARCHAR(64) NOT NULL COMMENT '公网出口IP',
- `score` INT NOT NULL DEFAULT 60 COMMENT '信誉分 0-100',
- `state` VARCHAR(16) NOT NULL DEFAULT 'probation' COMMENT 'trusted, probation, blocked',
- `total_calls` INT NOT NULL DEFAULT 0 COMMENT '总调用次数',
- `success_calls` INT NOT NULL DEFAULT 0 COMMENT '成功调用次数',
- `consecutive_successes` INT NOT NULL DEFAULT 0 COMMENT '连续成功次数',
- `consecutive_fails` INT NOT NULL DEFAULT 0 COMMENT '连续失败次数',
- `last_http_code` INT DEFAULT NULL COMMENT '最近一次 HTTP 响应码',
- `last_error_type` VARCHAR(128) DEFAULT NULL COMMENT '最近一次错误类型',
- `last_seen` INT DEFAULT NULL COMMENT '最近一次调用秒级时间戳',
- `unblock_time` DATETIME DEFAULT NULL COMMENT '封禁解封时间点',
- `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
- `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
- UNIQUE KEY `uk_plugin_ip` (`plugin_name`, `egress_ip`),
- INDEX `idx_plugin_state` (`plugin_name`, `state`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理出口IP信誉分与状态表';
- -- 2. 原始调用指标流水日志表
- CREATE TABLE IF NOT EXISTS `proxy_telemetry_log` (
- `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
- `client_id` VARCHAR(64) NOT NULL COMMENT '客户端唯一标识',
- `plugin_name` VARCHAR(64) NOT NULL COMMENT '插件名称',
- `egress_ip` VARCHAR(64) NOT NULL COMMENT '公网出口IP',
- `local_port` INT DEFAULT NULL COMMENT '客户端本地代理端口',
- `node_name` VARCHAR(128) DEFAULT NULL COMMENT '节点标签名称',
- `action` VARCHAR(32) NOT NULL COMMENT '调用场景: spawn, query, book',
- `success` TINYINT(1) NOT NULL COMMENT '是否成功: 1成功, 0失败',
- `http_code` INT NOT NULL COMMENT 'HTTP响应状态码',
- `error_type` VARCHAR(128) DEFAULT NULL COMMENT '错误类型归因',
- `latency_ms` INT NOT NULL DEFAULT 0 COMMENT '耗时毫秒',
- `timestamp` INT NOT NULL COMMENT '客户端上报时间戳(秒级)',
- `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服务端接收时间',
- INDEX `idx_telemetry_created_at` (`created_at`),
- INDEX `idx_telemetry_plugin_created` (`plugin_name`, `created_at`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理指标埋点流水日志表';
|