58 lines
2.3 KiB
SQL
58 lines
2.3 KiB
SQL
-- 0021_jobs_update_kind.sql
|
|
--
|
|
-- Add 'update' to the jobs.kind CHECK constraint so the agent
|
|
-- self-update flow (P6-01) can persist its job rows. SQLite can't
|
|
-- ALTER a CHECK in place, so we rebuild the table.
|
|
--
|
|
-- Same safe rebuild pattern as 0012:
|
|
-- 1. Stash job_logs into a temp table BEFORE rebuilding jobs.
|
|
-- 2. Create jobs_new with the wider CHECK; copy data; DROP jobs;
|
|
-- RENAME jobs_new TO jobs.
|
|
-- 3. Restore job_logs (cascade-trap defence — see CLAUDE.md).
|
|
--
|
|
-- jobs_new mirrors the live schema *including* post-0012 column
|
|
-- additions (0015 added source_group_id). When adding a new
|
|
-- migration that touches this table, mirror the latest column set.
|
|
|
|
CREATE TEMPORARY TABLE _job_logs_backup AS
|
|
SELECT job_id, seq, ts, stream, payload FROM job_logs;
|
|
|
|
CREATE TABLE jobs_new (
|
|
id TEXT PRIMARY KEY,
|
|
host_id TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL CHECK (kind IN
|
|
('backup','init','forget','prune','check','unlock','restore','diff','update')),
|
|
status TEXT NOT NULL CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
|
|
scheduled_id TEXT REFERENCES schedules(id) ON DELETE SET NULL,
|
|
actor_kind TEXT NOT NULL CHECK (actor_kind IN ('user','schedule','system')),
|
|
actor_id TEXT,
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
exit_code INTEGER,
|
|
stats TEXT,
|
|
error TEXT,
|
|
created_at TEXT NOT NULL,
|
|
source_group_id TEXT REFERENCES source_groups(id) ON DELETE SET NULL
|
|
);
|
|
|
|
INSERT INTO jobs_new
|
|
SELECT id, host_id, kind, status, scheduled_id, actor_kind, actor_id,
|
|
started_at, finished_at, exit_code, stats, error, created_at,
|
|
source_group_id
|
|
FROM jobs;
|
|
|
|
DROP TABLE jobs;
|
|
ALTER TABLE jobs_new RENAME TO jobs;
|
|
|
|
CREATE INDEX jobs_host_id ON jobs(host_id);
|
|
CREATE INDEX jobs_status ON jobs(status);
|
|
CREATE INDEX jobs_created_at ON jobs(created_at);
|
|
CREATE INDEX jobs_source_group_id ON jobs(source_group_id);
|
|
|
|
-- Defensive: restore job_logs from the temp backup. INSERT OR IGNORE
|
|
-- so a re-run is harmless. Same shape as 0012's safety net.
|
|
INSERT OR IGNORE INTO job_logs (job_id, seq, ts, stream, payload)
|
|
SELECT job_id, seq, ts, stream, payload FROM _job_logs_backup;
|
|
|
|
DROP TABLE _job_logs_backup;
|