# Coordinate 节点任务配置数据库 Schema 设计文档 本文档定义了 Coordinate 系统中三大角色(`booker`、`sentinel`、`standalone`)的 JSON 任务配置持久化到 MySQL (8.x+) 数据库的表结构(DDL)、字段映射规范、设计原则与示例数据。 --- ## 1. 设计规范与原则 1. **顶级字段直接列化**: - 基础标量类型(`string`、`bool`、`int`)映射为对应的 SQL 数据类型(`VARCHAR`、`TINYINT(1)`、`INT UNSIGNED` 等)。 - 嵌套对象类型(`object`、`array`,如 `plugin_config`、`appointment_types`、`free_config` 等)统一存储为 MySQL 原生 `JSON` 列。 2. **多节点分发支持**: - 包含 `node_id` 列(如 `node01`, `node02`),用于支持不同节点独立拉取和维护各自的任务组配置。 - 联合唯一约束采用 `UNIQUE KEY (node_id, identifier)`,既保证单节点内标识唯一,又支持跨节点部署同名任务。 3. **运维与并发审计字段**: - 统一提供 `version`(乐观锁版本号)、`updated_by`(变更操作人)、`created_at`、`updated_at`。 4. **JSON 字段默认值**: - 针对 MySQL 8.0+ 使用表达式默认值 `DEFAULT (json_object())` 或 `DEFAULT (json_array())`,防止非空写入报错。 --- ## 2. 数据库 DDL 定义 ```sql -- ============================================================================= -- 1. Booker 角色任务配置表 (booker_entries) -- ============================================================================= DROP TABLE IF EXISTS `booker_entries`; CREATE TABLE `booker_entries` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', `node_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '绑定的节点唯一标识(如 node01)', `identifier` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '任务组标识(如 vfs.ie.nl)', `enable` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否启用: 1-启用, 0-禁用', `debug` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否开启调试日志: 1-开启, 0-关闭', `need_account` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否需要账号池支持: 1-需要, 0-不需要', `need_proxy` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否需要代理池支持: 1-需要, 0-不需要', `proxy_pool` JSON NOT NULL DEFAULT (json_array()) COMMENT '指定代理池名称列表(JSON 字符串数组)', `proxy_cd` INT UNSIGNED NOT NULL DEFAULT 300 COMMENT '代理冷却时间(秒)', `session_max_life` INT UNSIGNED NOT NULL DEFAULT 1800 COMMENT '会话最大存活时间(秒)', `active_time_start` VARCHAR(5) NOT NULL DEFAULT '00:00' COMMENT '任务每日生效起始时间(HH:MM)', `active_time_end` VARCHAR(5) NOT NULL DEFAULT '23:59' COMMENT '任务每日生效结束时间(HH:MM)', `website` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '目标签证网站/登录页面 URL', `booker` JSON NOT NULL DEFAULT (json_object()) COMMENT 'Booker 专属配置对象(account_source, pool_id, target_instances 等)', `plugin_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '插件加载配置对象(lib_path, plugin_name, plugin_bin 等)', `appointment_types` JSON NOT NULL DEFAULT (json_array()) COMMENT '预约类型与路由权重配置列表', `free_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '目标使馆/国家专属业务扩展配置', `version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', `updated_by` VARCHAR(64) DEFAULT 'system' COMMENT '最后更新人', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_booker_node_identifier` (`node_id`, `identifier`), KEY `idx_booker_identifier` (`identifier`), KEY `idx_booker_updated_at` (`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Booker 角色任务配置表'; -- ============================================================================= -- 2. Sentinel 角色任务配置表 (sentinel_entries) -- ============================================================================= DROP TABLE IF EXISTS `sentinel_entries`; CREATE TABLE `sentinel_entries` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', `node_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '绑定的节点唯一标识(如 node01)', `identifier` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '任务组标识(如 vfs.ie.nl)', `enable` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否启用: 1-启用, 0-禁用', `debug` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否开启调试日志: 1-开启, 0-关闭', `need_account` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否需要账号池支持: 1-需要, 0-不需要', `need_proxy` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否需要代理池支持: 1-需要, 0-不需要', `proxy_pool` JSON NOT NULL DEFAULT (json_array()) COMMENT '指定代理池名称列表(JSON 字符串数组)', `proxy_cd` INT UNSIGNED NOT NULL DEFAULT 300 COMMENT '代理冷却时间(秒)', `session_max_life` INT UNSIGNED NOT NULL DEFAULT 1800 COMMENT '会话最大存活时间(秒)', `active_time_start` VARCHAR(5) NOT NULL DEFAULT '00:00' COMMENT '任务每日生效起始时间(HH:MM)', `active_time_end` VARCHAR(5) NOT NULL DEFAULT '23:59' COMMENT '任务每日生效结束时间(HH:MM)', `website` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '目标签证网站/登录页面 URL', `sentinel` JSON NOT NULL DEFAULT (json_object()) COMMENT 'Sentinel 专属配置对象(account_source, signal_ttl, query_wait 等)', `plugin_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '插件加载配置对象(lib_path, plugin_name, plugin_bin 等)', `appointment_types` JSON NOT NULL DEFAULT (json_array()) COMMENT '预约类型与路由权重配置列表', `free_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '目标使馆/国家专属业务扩展配置', `version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', `updated_by` VARCHAR(64) DEFAULT 'system' COMMENT '最后更新人', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_sentinel_node_identifier` (`node_id`, `identifier`), KEY `idx_sentinel_identifier` (`identifier`), KEY `idx_sentinel_updated_at` (`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Sentinel 角色任务配置表'; -- ============================================================================= -- 3. Standalone 独立任务配置表 (standalone_entries) -- ============================================================================= DROP TABLE IF EXISTS `standalone_entries`; CREATE TABLE `standalone_entries` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', `node_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '绑定的节点唯一标识(如 node01)', `order_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT '绑定的外部订单 ID(可选)', `identifier` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT '任务唯一标识(如 tls, vfs, usa)', `enable` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用: 1-启用, 0-禁用', `debug` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否开启调试日志: 1-开启, 0-关闭', `plugin_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '插件加载配置对象(lib_path, plugin_name, plugin_bin 等)', `account` JSON NOT NULL DEFAULT (json_object()) COMMENT '独立账号登录凭据对象(username, password)', `proxy_pool` JSON NOT NULL DEFAULT (json_array()) COMMENT '指定代理池名称列表(JSON 字符串数组)', `query_wait` JSON NOT NULL DEFAULT (json_object()) COMMENT '查询等待与退避策略(mode, fixed_wait, random_min, random_max)', `login_interval` INT UNSIGNED NOT NULL DEFAULT 1800 COMMENT '登录轮询/重试间隔(秒)', `session_max_life` INT UNSIGNED NOT NULL DEFAULT 1800 COMMENT '会话最大存活时间(秒)', `active_time_start` VARCHAR(5) NOT NULL DEFAULT '00:00' COMMENT '任务每日生效起始时间(HH:MM)', `active_time_end` VARCHAR(5) NOT NULL DEFAULT '23:59' COMMENT '任务每日生效结束时间(HH:MM)', `appointment_types` JSON NOT NULL DEFAULT (json_array()) COMMENT '目标预约类型与路由权重配置列表', `user_preferences` JSON NOT NULL DEFAULT (json_object()) COMMENT '抢签用户个人资料与预订偏好信息', `free_config` JSON NOT NULL DEFAULT (json_object()) COMMENT '目标使馆/国家专属业务扩展配置', `version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', `updated_by` VARCHAR(64) DEFAULT 'system' COMMENT '最后更新人', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_standalone_node_identifier` (`node_id`, `identifier`), KEY `idx_standalone_identifier` (`identifier`), KEY `idx_standalone_order_id` (`order_id`), KEY `idx_standalone_updated_at` (`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Standalone 独立抢签任务配置表'; ``` --- ## 3. JSON 与表字段映射对照表 ### 3.1 `booker_entries` 字段映射 | JSON 字段 | SQL 列名 | 类型 | 说明 | | :--- | :--- | :--- | :--- | | *(系统配置)* | `node_id` | `VARCHAR(64)` | 节点标识(如 `node01`) | | `identifier` | `identifier` | `VARCHAR(64)` | 组标识(如 `vfs.ie.nl`) | | `enable` | `enable` | `TINYINT(1)` | 布尔值 (0/1) | | `debug` | `debug` | `TINYINT(1)` | 布尔值 (0/1) | | `need_account` | `need_account` | `TINYINT(1)` | 布尔值 (0/1) | | `need_proxy` | `need_proxy` | `TINYINT(1)` | 布尔值 (0/1) | | `proxy_pool` | `proxy_pool` | `JSON` | 数组(如 `["proxy-cheap"]`) | | `proxy_cd` | `proxy_cd` | `INT UNSIGNED` | 代理冷却秒数 | | `session_max_life` | `session_max_life` | `INT UNSIGNED` | 会话存活秒数 | | `active_time_start`| `active_time_start`| `VARCHAR(5)` | 起始时间(`HH:MM`) | | `active_time_end` | `active_time_end` | `VARCHAR(5)` | 结束时间(`HH:MM`) | | `website` | `website` | `VARCHAR(512)`| 官网登录/主页链接 | | `booker` | `booker` | `JSON` | 包含 `account_source`, `account_pool_id`, `target_instances`, `keep_alive`, `account_cd`, `booking_cooldown`, `max_bookings_per_account` | | `plugin_config` | `plugin_config` | `JSON` | 包含 `lib_path`, `plugin_name`, `plugin_bin`, `plugin_proto` | | `appointment_types`| `appointment_types`| `JSON` | 包含 `weight`, `routing_key`, `city`, `visa_type`, `country` 数组 | | `free_config` | `free_config` | `JSON` | 包含 `mission_code`, `apt_configs` 等扩展字典 | --- ### 3.2 `sentinel_entries` 字段映射 | JSON 字段 | SQL 列名 | 类型 | 说明 | | :--- | :--- | :--- | :--- | | *(系统配置)* | `node_id` | `VARCHAR(64)` | 节点标识(如 `node01`) | | `identifier` | `identifier` | `VARCHAR(64)` | 组标识(如 `vfs.ie.nl`) | | `enable` | `enable` | `TINYINT(1)` | 布尔值 (0/1) | | `debug` | `debug` | `TINYINT(1)` | 布尔值 (0/1) | | `need_account` | `need_account` | `TINYINT(1)` | 布尔值 (0/1) | | `need_proxy` | `need_proxy` | `TINYINT(1)` | 布尔值 (0/1) | | `proxy_pool` | `proxy_pool` | `JSON` | 数组(如 `["proxy-cheap"]`) | | `proxy_cd` | `proxy_cd` | `INT UNSIGNED` | 代理冷却秒数 | | `session_max_life` | `session_max_life` | `INT UNSIGNED` | 会话存活秒数 | | `active_time_start`| `active_time_start`| `VARCHAR(5)` | 起始时间(`HH:MM`) | | `active_time_end` | `active_time_end` | `VARCHAR(5)` | 结束时间(`HH:MM`) | | `website` | `website` | `VARCHAR(512)`| 官网登录/主页链接 | | `sentinel` | `sentinel` | `JSON` | 包含 `account_source`, `account_pool_id`, `target_instances`, `account_cd`, `signal_ttl`, `query_wait` | | `plugin_config` | `plugin_config` | `JSON` | 包含 `lib_path`, `plugin_name`, `plugin_bin`, `plugin_proto` | | `appointment_types`| `appointment_types`| `JSON` | 包含 `weight`, `routing_key`, `city`, `visa_type`, `country` 数组 | | `free_config` | `free_config` | `JSON` | 包含 `mission_code`, `apt_configs` 等扩展字典 | --- ### 3.3 `standalone_entries` 字段映射 | JSON 字段 | SQL 列名 | 类型 | 说明 | | :--- | :--- | :--- | :--- | | *(系统配置)* | `node_id` | `VARCHAR(64)` | 节点标识(如 `node01`) | | *(系统配置)* | `order_id` | `VARCHAR(64)` | 订单标识(可选) | | `identifier` | `identifier` | `VARCHAR(64)` | 任务标识(如 `tls`, `usa`, `vfs`) | | `enable` | `enable` | `TINYINT(1)` | 布尔值 (0/1) | | `debug` | `debug` | `TINYINT(1)` | 布尔值 (0/1) | | `plugin_config` | `plugin_config` | `JSON` | 包含 `lib_path`, `plugin_name`, `plugin_bin`, `plugin_proto` | | `account` | `account` | `JSON` | 包含 `username`, `password` | | `proxy_pool` | `proxy_pool` | `JSON` | 数组(如 `["proxy-cheap"]`) | | `query_wait` | `query_wait` | `JSON` | 包含 `mode`, `fixed_wait`, `random_min`, `random_max` | | `login_interval` | `login_interval` | `INT UNSIGNED` | 登录重试/轮询秒数 | | `session_max_life` | `session_max_life` | `INT UNSIGNED` | 会话最大存活秒数 | | `active_time_start`| `active_time_start`| `VARCHAR(5)` | 起始时间(`HH:MM`) | | `active_time_end` | `active_time_end` | `VARCHAR(5)` | 结束时间(`HH:MM`) | | `appointment_types`| `appointment_types`| `JSON` | 包含 `weight`, `routing_key`, `city`, `visa_type`, `country` 数组 | | `user_preferences` | `user_preferences` | `JSON` | 申请人姓名、护照号、偏好时间等表单对象 | | `free_config` | `free_config` | `JSON` | 包含 `tls_url`, `capsolver_key`, `apt_configs` 等特定平台扩展配置 | --- ## 4. 插入与查询 SQL 示例 ### 4.1 Booker 插入示例 ```sql INSERT INTO `booker_entries` ( `node_id`, `identifier`, `enable`, `debug`, `need_account`, `need_proxy`, `proxy_pool`, `proxy_cd`, `session_max_life`, `active_time_start`, `active_time_end`, `website`, `booker`, `plugin_config`, `appointment_types`, `free_config`, `updated_by` ) VALUES ( 'node01', 'vfs.ie.nl', 0, 0, 1, 1, '["proxy-cheap"]', 300, 1800, '00:00', '23:59', 'https://visa.vfsglobal.com/irl/en/nld/login', '{"account_source": "built-in", "account_pool_id": "ie.nl.booker", "target_instances": 1, "keep_alive": 120, "account_cd": 10800, "booking_cooldown": 300, "max_bookings_per_account": 8}', '{"lib_path": "plugins", "plugin_name": "vfs_plugin", "plugin_bin": "vfs_plugin.py", "plugin_proto": "IVSPlg"}', '[{"weight": 10, "routing_key": "slot.dub.nl.tourist", "city": "Dublin", "visa_type": "Tourist", "country": "Netherlands"}]', '{"mission_code": "nld", "mission_name": "Netherlands", "country_code": "irl", "country_name": "Ireland", "culture_code": "en-US", "language": "en", "apt_configs": {"slot.dub.nl.tourist": {"center_name": "Netherlands Visa Application Center - Dublin", "address": "Cunningham House, 130 Francis Street, Dublin 8 D08 H48R", "vac_code": "NTDB", "category_name": "All Short stay Categories", "category_code": "TA", "subcategory_name": "Tourist", "subcategory_code": "To"}}}', 'admin' ); ``` ### 4.2 Sentinel 插入示例 ```sql INSERT INTO `sentinel_entries` ( `node_id`, `identifier`, `enable`, `debug`, `need_account`, `need_proxy`, `proxy_pool`, `proxy_cd`, `session_max_life`, `active_time_start`, `active_time_end`, `website`, `sentinel`, `plugin_config`, `appointment_types`, `free_config`, `updated_by` ) VALUES ( 'node01', 'vfs.ie.nl', 0, 0, 1, 1, '["proxy-cheap"]', 300, 1800, '00:00', '23:59', 'https://visa.vfsglobal.com/irl/en/nld/login', '{"account_source": "built-in", "account_pool_id": "ie.nl.sentinel", "target_instances": 1, "account_cd": 10800, "signal_ttl": 30, "query_wait": {"mode": "Random", "fixed_wait": 10, "random_min": 60, "random_max": 300}}', '{"lib_path": "plugins", "plugin_name": "vfs_plugin", "plugin_bin": "vfs_plugin.py", "plugin_proto": "IVSPlg"}', '[{"weight": 10, "routing_key": "slot.dub.nl.tourist", "city": "Dublin", "visa_type": "Tourist", "country": "Netherlands"}]', '{"mission_code": "nld", "mission_name": "Netherlands", "country_code": "irl", "country_name": "Ireland", "culture_code": "en-US", "language": "en", "apt_configs": {"slot.dub.nl.tourist": {"center_name": "Netherlands Visa Application Center - Dublin", "address": "Cunningham House, 130 Francis Street, Dublin 8 D08 H48R", "vac_code": "NTDB", "category_name": "All Short stay Categories", "category_code": "TA", "subcategory_name": "Tourist", "subcategory_code": "To"}}}', 'admin' ); ``` ### 4.3 Standalone 插入示例 ```sql INSERT INTO `standalone_entries` ( `node_id`, `identifier`, `enable`, `debug`, `plugin_config`, `account`, `proxy_pool`, `query_wait`, `login_interval`, `session_max_life`, `active_time_start`, `active_time_end`, `appointment_types`, `user_preferences`, `free_config`, `updated_by` ) VALUES ( 'node01', 'tls', 0, 1, '{"lib_path": "plugins", "plugin_name": "tls_plugin", "plugin_bin": "tls_plugin.py", "plugin_proto": "IVSPlg"}', '{"username": "18305503085@163.com", "password": "Zhangww@201207"}', '["proxy-cheap"]', '{"mode": "Random", "fixed_wait": 10, "random_min": 55, "random_max": 65}', 1800, 3600, '00:00', '23:30', '[{"weight": 10, "routing_key": "slot.sha.fr.tourist", "city": "Shanghai", "visa_type": "Tourist", "country": "France"}]', '{"support_pta": false, "expected_end_date": "2024-07-01", "expected_start_date": "2024-07-20"}', '{"tls_url": "https://visas-fr.tlscontact.com/en-us/country/cn/vac/cnSHA2fr", "location": "Shanghai", "capsolver_key": "CAP-5441DD341DD3CC2FAEF0BE6FE493EE9A", "login_captcha": {"solve_advance": false, "site_key": "6LcDpXcfAAAAAM7wOEsF_38DNsL20tTvPTKxpyn0", "page_url": "https://i2-auth.visas-fr.tlscontact.com", "task": "ReCaptchaV2TaskProxyLess"}}', 'admin' ); ```