config_entries_database_schema.md 18 KB

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 定义

-- =============================================================================
-- 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 插入示例

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 插入示例

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 插入示例

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'
);