36 lines
1.4 KiB
SQL
36 lines
1.4 KiB
SQL
-- 0022_fleet_updates.sql
|
|
--
|
|
-- Tables backing the rolling fleet-update worker (P6-02). One row in
|
|
-- fleet_updates per "update all" invocation, a child row per host so
|
|
-- the worker can iterate in position order, report progress, and
|
|
-- record per-host outcome. Halt-on-fail semantics live in the worker
|
|
-- (internal/server/fleetupdate); this schema just captures state.
|
|
|
|
CREATE TABLE fleet_updates (
|
|
id TEXT PRIMARY KEY,
|
|
started_at TEXT NOT NULL,
|
|
started_by_user_id TEXT NOT NULL REFERENCES users(id),
|
|
target_version TEXT NOT NULL,
|
|
status TEXT NOT NULL CHECK (status IN
|
|
('running','completed','halted','cancelled')),
|
|
current_host_id TEXT REFERENCES hosts(id),
|
|
halted_reason TEXT,
|
|
completed_at TEXT
|
|
);
|
|
|
|
CREATE INDEX fleet_updates_status ON fleet_updates(status);
|
|
|
|
CREATE TABLE fleet_update_hosts (
|
|
fleet_update_id TEXT NOT NULL REFERENCES fleet_updates(id) ON DELETE CASCADE,
|
|
host_id TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL,
|
|
status TEXT NOT NULL CHECK (status IN
|
|
('pending','running','succeeded','failed','skipped')),
|
|
job_id TEXT REFERENCES jobs(id) ON DELETE SET NULL,
|
|
failed_reason TEXT,
|
|
PRIMARY KEY (fleet_update_id, host_id)
|
|
);
|
|
|
|
CREATE INDEX fleet_update_hosts_position
|
|
ON fleet_update_hosts(fleet_update_id, position);
|