Coordinate 节点任务配置数据库 Schema 设计文档
本文档定义了 Coordinate 系统中三大角色(booker、sentinel、standalone)的 JSON 任务配置持久化到 MySQL (8.x+) 数据库的表结构(DDL)、字段映射规范、设计原则与示例数据。
1. 设计规范与原则
- 顶级字段直接列化:
- 基础标量类型(
string、bool、int)映射为对应的 SQL 数据类型(VARCHAR、TINYINT(1)、INT UNSIGNED 等)。
- 嵌套对象类型(
object、array,如 plugin_config、appointment_types、free_config 等)统一存储为 MySQL 原生 JSON 列。
- 多节点分发支持:
- 包含
node_id 列(如 node01, node02),用于支持不同节点独立拉取和维护各自的任务组配置。
- 联合唯一约束采用
UNIQUE KEY (node_id, identifier),既保证单节点内标识唯一,又支持跨节点部署同名任务。
- 运维与并发审计字段:
- 统一提供
version(乐观锁版本号)、updated_by(变更操作人)、created_at、updated_at。
- 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'
);