1  -- Migration 042: Create pipeline service control and metrics tables
 2  -- Enables continuous pipeline service with pause/resume for scheduled tasks
 3  
 4  -- Single-row control table (singleton pattern)
 5  CREATE TABLE IF NOT EXISTS pipeline_control (
 6      id INTEGER PRIMARY KEY CHECK (id = 1),  -- Only one row ever exists
 7      paused BOOLEAN DEFAULT 0,
 8      paused_by TEXT,                         -- Which job requested pause
 9      paused_at DATETIME,
10      current_stage TEXT,                     -- Which stage is currently running
11      last_cycle_at DATETIME,                 -- Last full cycle completion
12      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
13      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
14  );
15  
16  -- Initialize control table
17  INSERT OR IGNORE INTO pipeline_control (id, paused) VALUES (1, 0);
18  
19  -- Metrics tracking for pipeline performance monitoring
20  CREATE TABLE IF NOT EXISTS pipeline_metrics (
21      id INTEGER PRIMARY KEY AUTOINCREMENT,
22      stage_name TEXT NOT NULL,               -- 'assets', 'scoring', 'rescoring', etc.
23      sites_processed INTEGER DEFAULT 0,
24      sites_succeeded INTEGER DEFAULT 0,
25      sites_failed INTEGER DEFAULT 0,
26      duration_ms INTEGER NOT NULL,
27      started_at DATETIME NOT NULL,
28      finished_at DATETIME NOT NULL,
29      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
30  );
31  
32  CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_stage
33      ON pipeline_metrics(stage_name, started_at);
34  
35  CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_started
36      ON pipeline_metrics(started_at);
37  
38  -- Add pause_pipeline flag to cron_jobs (for long-running tasks)
39  ALTER TABLE cron_jobs ADD COLUMN pause_pipeline BOOLEAN DEFAULT 0;
40  
41  -- Mark long-running tasks that should pause the pipeline
42  UPDATE cron_jobs SET pause_pipeline = 1
43  WHERE task_key IN (
44      'backupDatabase',
45      'vacuumDatabase',
46      'unifiedAutofix',
47      'databaseMaintenance'
48  );
49  
50  -- Disable old pipeline stage cron jobs (replaced by continuous service)
51  UPDATE cron_jobs SET enabled = 0
52  WHERE task_key IN (
53      'runAssets',
54      'runScoring',
55      'runRescoring',
56      'runEnrich',
57      'runProposals',
58      'runOutreach',
59      'runReplies'
60  );