238 lines
8.8 KiB
SQL
238 lines
8.8 KiB
SQL
CREATE TABLE managed_instances (
|
|
id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL CHECK (length(trim(display_name)) BETWEEN 1 AND 80),
|
|
aws_region TEXT NOT NULL CHECK (length(trim(aws_region)) BETWEEN 3 AND 32),
|
|
lightsail_instance_name TEXT NOT NULL
|
|
CHECK (length(trim(lightsail_instance_name)) BETWEEN 1 AND 255),
|
|
cloudflare_zone_name TEXT NOT NULL
|
|
CHECK (length(trim(cloudflare_zone_name)) BETWEEN 3 AND 253),
|
|
cloudflare_zone_id TEXT NOT NULL DEFAULT '' CHECK (length(cloudflare_zone_id) <= 64),
|
|
cloudflare_record_name TEXT NOT NULL
|
|
CHECK (length(trim(cloudflare_record_name)) BETWEEN 3 AND 253),
|
|
socks_port INTEGER NOT NULL DEFAULT 1080 CHECK (socks_port BETWEEN 1 AND 65535),
|
|
proxy_health_check INTEGER NOT NULL DEFAULT 1
|
|
CHECK (proxy_health_check IN (0, 1)),
|
|
health_timeout_seconds INTEGER NOT NULL DEFAULT 120
|
|
CHECK (health_timeout_seconds BETWEEN 10 AND 600),
|
|
release_grace_seconds INTEGER NOT NULL DEFAULT 75
|
|
CHECK (release_grace_seconds BETWEEN 60 AND 600),
|
|
enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
|
|
config_version INTEGER NOT NULL DEFAULT 1 CHECK (config_version > 0),
|
|
last_known_ip TEXT,
|
|
last_checked_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
archived_at TEXT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX uq_managed_instances_display_name_active
|
|
ON managed_instances(display_name COLLATE NOCASE)
|
|
WHERE archived_at IS NULL;
|
|
|
|
CREATE UNIQUE INDEX uq_managed_instances_aws_target_active
|
|
ON managed_instances(aws_region, lightsail_instance_name)
|
|
WHERE archived_at IS NULL;
|
|
|
|
CREATE UNIQUE INDEX uq_managed_instances_dns_record_active
|
|
ON managed_instances(lower(cloudflare_record_name))
|
|
WHERE archived_at IS NULL;
|
|
|
|
CREATE INDEX idx_managed_instances_active
|
|
ON managed_instances(archived_at, enabled, display_name);
|
|
|
|
CREATE TABLE instance_groups (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 80),
|
|
enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1)),
|
|
interval_minutes INTEGER NOT NULL DEFAULT 60
|
|
CHECK (interval_minutes BETWEEN 5 AND 10080),
|
|
next_run_at TEXT,
|
|
last_run_at TEXT,
|
|
config_version INTEGER NOT NULL DEFAULT 1 CHECK (config_version > 0),
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
archived_at TEXT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX uq_instance_groups_name_active
|
|
ON instance_groups(name COLLATE NOCASE)
|
|
WHERE archived_at IS NULL;
|
|
|
|
CREATE INDEX idx_instance_groups_due
|
|
ON instance_groups(enabled, next_run_at)
|
|
WHERE archived_at IS NULL;
|
|
|
|
CREATE TABLE instance_group_members (
|
|
group_id TEXT NOT NULL REFERENCES instance_groups(id) ON DELETE CASCADE,
|
|
instance_id TEXT NOT NULL REFERENCES managed_instances(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL CHECK (position >= 0),
|
|
PRIMARY KEY (group_id, instance_id),
|
|
UNIQUE (instance_id)
|
|
);
|
|
|
|
CREATE INDEX idx_instance_group_members_order
|
|
ON instance_group_members(group_id, position, instance_id);
|
|
|
|
CREATE TABLE fleet_runs (
|
|
id TEXT PRIMARY KEY,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('instance', 'group')),
|
|
target_id TEXT NOT NULL,
|
|
target_name TEXT NOT NULL,
|
|
trigger TEXT NOT NULL CHECK (trigger IN ('manual', 'scheduled', 'recovery')),
|
|
status TEXT NOT NULL CHECK (
|
|
status IN (
|
|
'queued', 'running', 'needs_attention', 'cleanup_pending',
|
|
'succeeded', 'failed', 'cancelled'
|
|
)
|
|
),
|
|
active_slot INTEGER UNIQUE CHECK (active_slot IS NULL OR active_slot = 1),
|
|
current_item_id TEXT,
|
|
lease_owner TEXT,
|
|
lease_until TEXT,
|
|
error_code TEXT,
|
|
error_message TEXT,
|
|
total_items INTEGER NOT NULL DEFAULT 0 CHECK (total_items >= 0),
|
|
succeeded_items INTEGER NOT NULL DEFAULT 0
|
|
CHECK (succeeded_items >= 0 AND succeeded_items <= total_items),
|
|
started_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
finished_at TEXT
|
|
);
|
|
|
|
CREATE INDEX idx_fleet_runs_started_at ON fleet_runs(started_at DESC);
|
|
CREATE INDEX idx_fleet_runs_target ON fleet_runs(target_type, target_id, started_at DESC);
|
|
CREATE INDEX idx_fleet_runs_status ON fleet_runs(status, started_at DESC);
|
|
|
|
CREATE TABLE fleet_run_items (
|
|
id TEXT PRIMARY KEY,
|
|
run_id TEXT NOT NULL REFERENCES fleet_runs(id) ON DELETE CASCADE,
|
|
instance_id TEXT NOT NULL REFERENCES managed_instances(id) ON DELETE RESTRICT,
|
|
position INTEGER NOT NULL CHECK (position >= 0),
|
|
status TEXT NOT NULL CHECK (
|
|
status IN (
|
|
'queued', 'running', 'needs_attention', 'cleanup_pending',
|
|
'succeeded', 'failed', 'cancelled'
|
|
)
|
|
),
|
|
stage TEXT NOT NULL,
|
|
stage_started_at TEXT NOT NULL,
|
|
attempt_count INTEGER NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
|
|
config_version INTEGER NOT NULL CHECK (config_version > 0),
|
|
instance_display_name TEXT NOT NULL,
|
|
aws_region TEXT NOT NULL,
|
|
lightsail_instance_name TEXT NOT NULL,
|
|
cloudflare_zone_name TEXT NOT NULL,
|
|
cloudflare_zone_id TEXT NOT NULL DEFAULT '',
|
|
cloudflare_record_name TEXT NOT NULL,
|
|
socks_port INTEGER NOT NULL CHECK (socks_port BETWEEN 1 AND 65535),
|
|
proxy_health_check INTEGER NOT NULL CHECK (proxy_health_check IN (0, 1)),
|
|
health_timeout_seconds INTEGER NOT NULL
|
|
CHECK (health_timeout_seconds BETWEEN 10 AND 600),
|
|
release_grace_seconds INTEGER NOT NULL
|
|
CHECK (release_grace_seconds BETWEEN 60 AND 600),
|
|
old_static_ip_name TEXT,
|
|
old_ip TEXT,
|
|
new_static_ip_name TEXT,
|
|
new_ip TEXT,
|
|
dns_ip_before TEXT,
|
|
dns_ip_after TEXT,
|
|
grace_until TEXT,
|
|
aws_operation_id TEXT,
|
|
error_code TEXT,
|
|
error_message TEXT,
|
|
started_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
finished_at TEXT,
|
|
UNIQUE (run_id, instance_id)
|
|
);
|
|
|
|
CREATE INDEX idx_fleet_run_items_run_order
|
|
ON fleet_run_items(run_id, position, id);
|
|
CREATE INDEX idx_fleet_run_items_instance
|
|
ON fleet_run_items(instance_id, started_at DESC);
|
|
CREATE INDEX idx_fleet_run_items_status
|
|
ON fleet_run_items(status, updated_at);
|
|
|
|
CREATE TABLE fleet_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
run_id TEXT NOT NULL REFERENCES fleet_runs(id) ON DELETE CASCADE,
|
|
item_id TEXT REFERENCES fleet_run_items(id) ON DELETE CASCADE,
|
|
occurred_at TEXT NOT NULL,
|
|
stage TEXT NOT NULL,
|
|
level TEXT NOT NULL CHECK (level IN ('info', 'warning', 'error')),
|
|
message TEXT NOT NULL,
|
|
details_json TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
|
|
CREATE INDEX idx_fleet_events_run ON fleet_events(run_id, id);
|
|
CREATE INDEX idx_fleet_events_item ON fleet_events(item_id, id);
|
|
|
|
CREATE TABLE fleet_operation_locks (
|
|
slot INTEGER PRIMARY KEY CHECK (slot = 1),
|
|
kind TEXT NOT NULL CHECK (kind IN ('rotation', 'dns_sync')),
|
|
owner_id TEXT NOT NULL UNIQUE,
|
|
lease_until TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
INSERT INTO managed_instances (
|
|
id, display_name, aws_region, lightsail_instance_name,
|
|
cloudflare_zone_name, cloudflare_zone_id, cloudflare_record_name,
|
|
socks_port, proxy_health_check, health_timeout_seconds,
|
|
release_grace_seconds, enabled, config_version, created_at, updated_at
|
|
)
|
|
SELECT
|
|
'legacy-instance', 'Legacy instance', aws_region, lightsail_instance_name,
|
|
cloudflare_zone_name, cloudflare_zone_id, cloudflare_record_name,
|
|
socks_port, proxy_health_check, health_timeout_seconds,
|
|
75, 0, config_version, created_at, updated_at
|
|
FROM app_settings
|
|
WHERE id = 1
|
|
AND trim(aws_region) <> ''
|
|
AND trim(lightsail_instance_name) <> ''
|
|
AND trim(cloudflare_zone_name) <> ''
|
|
AND trim(cloudflare_record_name) <> '';
|
|
|
|
INSERT INTO instance_groups (
|
|
id, name, enabled, interval_minutes, next_run_at, last_run_at,
|
|
config_version, created_at, updated_at
|
|
)
|
|
SELECT
|
|
'legacy-group', 'Legacy group', 0, schedule_state.interval_minutes,
|
|
NULL, schedule_state.last_run_at, 1, schedule_state.created_at, schedule_state.updated_at
|
|
FROM schedule_state
|
|
WHERE schedule_state.id = 1
|
|
AND EXISTS (SELECT 1 FROM managed_instances WHERE id = 'legacy-instance');
|
|
|
|
INSERT INTO instance_group_members(group_id, instance_id, position)
|
|
SELECT 'legacy-group', 'legacy-instance', 0
|
|
WHERE EXISTS (SELECT 1 FROM managed_instances WHERE id = 'legacy-instance')
|
|
AND EXISTS (SELECT 1 FROM instance_groups WHERE id = 'legacy-group');
|
|
|
|
INSERT INTO rotation_events(
|
|
run_id, occurred_at, stage, level, message, details_json
|
|
)
|
|
SELECT
|
|
id,
|
|
strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
|
|
stage,
|
|
'warning',
|
|
'轮换工作流已升级，旧任务不能继续执行',
|
|
'{"code":"WORKFLOW_UPGRADED"}'
|
|
FROM rotation_runs
|
|
WHERE active_slot = 1;
|
|
|
|
UPDATE rotation_runs
|
|
SET status = 'failed',
|
|
stage = 'failed',
|
|
active_slot = NULL,
|
|
lease_owner = NULL,
|
|
lease_until = NULL,
|
|
error_code = 'WORKFLOW_UPGRADED',
|
|
error_message = '轮换工作流已升级，请使用静态 IP 轮换新建任务',
|
|
updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
|
|
finished_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
|
|
WHERE active_slot = 1;
|
|
|
|
DELETE FROM operation_locks;
|