1  -- Migration 100: WAL checkpoint cron + composite index for buildStatusTree
 2  --
 3  -- 1. Composite index on site_status(created_at, site_id, status) so the
 4  --    time-filtered subquery in buildStatusTree() uses a covering index
 5  --    instead of a full 1.3M-row scan.
 6  --
 7  -- 2. WAL checkpoint cron job runs every 4 hours to keep WAL size bounded
 8  --    even when the pipeline holds long write transactions.
 9  
10  -- Covering index for the delta24h/delta1h subqueries in buildStatusTree
11  CREATE INDEX IF NOT EXISTS idx_site_status_time_cover
12    ON site_status(created_at, site_id, status);
13  
14  -- WAL checkpoint cron (every 4 hours, PASSIVE mode — non-blocking)
15  INSERT OR IGNORE INTO cron_jobs (
16    name, task_key, description, handler_type, handler_value,
17    interval_value, interval_unit, enabled, timeout_seconds
18  ) VALUES (
19    '0.1 WAL Checkpoint',
20    'walCheckpoint',
21    'Runs PRAGMA wal_checkpoint(PASSIVE) to keep WAL file size bounded',
22    'function',
23    'walCheckpoint',
24    4, 'hours', 1, 60
25  );