1  /**
  2   * setup-split-dbs.js
  3   *
  4   * Creates minimal ops.db and telemetry.db test fixtures and sets OPS_DB_PATH /
  5   * TEL_DB_PATH env vars so that createDatabaseConnection() can ATTACH them.
  6   *
  7   * Run as a --require / --import before the test suite, or call setupSplitDbs()
  8   * from a beforeEach/before hook.
  9   *
 10   * The created files are co-located with DATABASE_PATH so each isolated test DB
 11   * gets its own ops/tel pair.
 12   */
 13  
 14  import Database from 'better-sqlite3';
 15  import { mkdirSync } from 'fs';
 16  import { dirname, join } from 'path';
 17  
 18  export function setupSplitDbs(sitesDbPath) {
 19    const dir = dirname(sitesDbPath || process.env.DATABASE_PATH || '/tmp/test-sites.db');
 20    mkdirSync(dir, { recursive: true });
 21  
 22    const opsPath = join(dir, 'test-ops.db');
 23    const telPath = join(dir, 'test-telemetry.db');
 24  
 25    process.env.OPS_DB_PATH = opsPath;
 26    process.env.TEL_DB_PATH = telPath;
 27  
 28    // Create ops.db with full schema matching db/ops.db
 29    const ops = new Database(opsPath);
 30    ops.exec(`
 31      CREATE TABLE IF NOT EXISTS migrations (
 32        id INTEGER PRIMARY KEY AUTOINCREMENT,
 33        filename TEXT UNIQUE NOT NULL,
 34        applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
 35      );
 36      CREATE TABLE IF NOT EXISTS settings (
 37        key TEXT PRIMARY KEY,
 38        value TEXT NOT NULL,
 39        description TEXT,
 40        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
 41      );
 42      CREATE TABLE IF NOT EXISTS pipeline_control (
 43        id INTEGER PRIMARY KEY CHECK (id = 1),
 44        paused BOOLEAN DEFAULT 0,
 45        paused_by TEXT,
 46        paused_at DATETIME,
 47        current_stage TEXT,
 48        last_cycle_at DATETIME,
 49        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 50        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 51        current_browser_stage TEXT,
 52        last_api_cycle_at DATETIME,
 53        last_browser_cycle_at DATETIME
 54      );
 55      INSERT OR IGNORE INTO pipeline_control (id) VALUES (1);
 56      CREATE TABLE IF NOT EXISTS cron_jobs (
 57        id INTEGER PRIMARY KEY AUTOINCREMENT,
 58        name TEXT NOT NULL UNIQUE,
 59        task_key TEXT NOT NULL UNIQUE,
 60        description TEXT,
 61        handler_type TEXT NOT NULL CHECK(handler_type IN ('function', 'command')),
 62        handler_value TEXT NOT NULL,
 63        interval_value INTEGER NOT NULL,
 64        interval_unit TEXT NOT NULL CHECK(interval_unit IN ('minutes', 'hours', 'days', 'weeks')),
 65        enabled BOOLEAN DEFAULT 1,
 66        last_run_at DATETIME,
 67        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 68        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 69        pause_pipeline BOOLEAN DEFAULT 0,
 70        critical BOOLEAN DEFAULT 1,
 71        timeout_seconds INTEGER DEFAULT NULL
 72      );
 73      CREATE INDEX IF NOT EXISTS idx_cron_jobs_enabled ON cron_jobs(enabled);
 74      CREATE INDEX IF NOT EXISTS idx_cron_jobs_task_key ON cron_jobs(task_key);
 75      CREATE TABLE IF NOT EXISTS cron_job_logs (
 76        id INTEGER PRIMARY KEY AUTOINCREMENT,
 77        job_name TEXT NOT NULL,
 78        started_at DATETIME NOT NULL,
 79        finished_at DATETIME,
 80        status TEXT NOT NULL CHECK(status IN ('running', 'success', 'failed', 'timeout')),
 81        summary TEXT,
 82        full_log TEXT,
 83        items_processed INTEGER DEFAULT 0,
 84        items_failed INTEGER DEFAULT 0,
 85        error_message TEXT,
 86        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 87      );
 88      CREATE INDEX IF NOT EXISTS idx_cron_logs_job ON cron_job_logs(job_name);
 89      CREATE INDEX IF NOT EXISTS idx_cron_logs_started ON cron_job_logs(started_at);
 90      CREATE INDEX IF NOT EXISTS idx_cron_logs_status ON cron_job_logs(status);
 91      CREATE INDEX IF NOT EXISTS idx_cron_logs_job_started ON cron_job_logs(job_name, started_at);
 92      CREATE TABLE IF NOT EXISTS cron_locks (
 93        lock_key TEXT PRIMARY KEY,
 94        acquired_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 95        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 96        description TEXT
 97      );
 98      CREATE INDEX IF NOT EXISTS idx_cron_locks_updated ON cron_locks(updated_at);
 99    `);
100    ops.close();
101  
102    // Create telemetry.db with full schema matching db/telemetry.db
103    const tel = new Database(telPath);
104    tel.exec(`
105      CREATE TABLE IF NOT EXISTS llm_usage (
106        id INTEGER PRIMARY KEY AUTOINCREMENT,
107        site_id INTEGER,
108        stage TEXT NOT NULL,
109        provider TEXT NOT NULL,
110        model TEXT NOT NULL,
111        prompt_tokens INTEGER NOT NULL,
112        completion_tokens INTEGER NOT NULL,
113        total_tokens INTEGER NOT NULL,
114        estimated_cost DECIMAL(10, 6),
115        request_id TEXT,
116        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
117      );
118      CREATE TABLE IF NOT EXISTS agent_logs (
119        id INTEGER PRIMARY KEY AUTOINCREMENT,
120        task_id INTEGER,
121        agent_name TEXT NOT NULL,
122        log_level TEXT CHECK(log_level IN ('debug', 'info', 'warn', 'error')),
123        message TEXT NOT NULL,
124        data_json TEXT,
125        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
126      );
127      CREATE INDEX IF NOT EXISTS idx_agent_logs_task ON agent_logs(task_id);
128      CREATE INDEX IF NOT EXISTS idx_agent_logs_agent ON agent_logs(agent_name, created_at);
129      CREATE TABLE IF NOT EXISTS agent_tasks (
130        id INT,
131        task_type TEXT,
132        assigned_to TEXT,
133        created_by TEXT,
134        parent_task_id INT,
135        priority INT,
136        status TEXT,
137        context_json TEXT,
138        result_json TEXT,
139        error_message TEXT,
140        retry_count INT,
141        reviewed_by TEXT,
142        approval_json TEXT,
143        created_at NUM,
144        started_at NUM,
145        completed_at NUM
146      );
147      CREATE INDEX IF NOT EXISTS idx_agent_tasks_assigned_to ON agent_tasks(assigned_to, status);
148      CREATE INDEX IF NOT EXISTS idx_agent_tasks_parent ON agent_tasks(parent_task_id);
149      CREATE INDEX IF NOT EXISTS idx_agent_tasks_priority ON agent_tasks(priority DESC, created_at ASC);
150      CREATE INDEX IF NOT EXISTS idx_agent_tasks_status ON agent_tasks(status);
151      CREATE TABLE IF NOT EXISTS agent_messages (
152        id INTEGER PRIMARY KEY AUTOINCREMENT,
153        task_id INTEGER,
154        from_agent TEXT NOT NULL,
155        to_agent TEXT NOT NULL,
156        message_type TEXT CHECK(message_type IN ('question', 'answer', 'handoff', 'notification')),
157        content TEXT NOT NULL,
158        metadata_json TEXT,
159        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
160        read_at DATETIME
161      );
162      CREATE INDEX IF NOT EXISTS idx_agent_messages_task ON agent_messages(task_id);
163      CREATE INDEX IF NOT EXISTS idx_agent_messages_to ON agent_messages(to_agent, read_at);
164      CREATE TABLE IF NOT EXISTS agent_outcomes (
165        id INT,
166        task_id INT,
167        agent_name TEXT,
168        task_type TEXT,
169        outcome TEXT,
170        context_json TEXT,
171        result_json TEXT,
172        duration_ms INT,
173        created_at NUM
174      );
175      CREATE INDEX IF NOT EXISTS idx_agent_outcomes_agent ON agent_outcomes(agent_name, task_type);
176      CREATE INDEX IF NOT EXISTS idx_agent_outcomes_task_type ON agent_outcomes(task_type, outcome);
177      CREATE INDEX IF NOT EXISTS idx_agent_outcomes_outcome ON agent_outcomes(outcome, created_at);
178      CREATE INDEX IF NOT EXISTS idx_agent_outcomes_created ON agent_outcomes(created_at);
179      CREATE TABLE IF NOT EXISTS agent_state (
180        agent_name TEXT PRIMARY KEY,
181        last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
182        current_task_id INTEGER,
183        status TEXT DEFAULT 'idle',
184        metrics_json TEXT
185      );
186      CREATE TABLE IF NOT EXISTS agent_llm_usage (
187        id INTEGER PRIMARY KEY AUTOINCREMENT,
188        agent_name TEXT NOT NULL,
189        task_id INTEGER,
190        model TEXT NOT NULL,
191        prompt_tokens INTEGER NOT NULL,
192        completion_tokens INTEGER NOT NULL,
193        cost_usd DECIMAL(10, 6) NOT NULL,
194        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
195      );
196      CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_agent ON agent_llm_usage(agent_name);
197      CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_task ON agent_llm_usage(task_id);
198      CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_created ON agent_llm_usage(created_at);
199      CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_cost ON agent_llm_usage(cost_usd DESC);
200      CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_agent_date ON agent_llm_usage(agent_name, DATE(created_at));
201      CREATE TABLE IF NOT EXISTS system_metrics (
202        id INTEGER PRIMARY KEY AUTOINCREMENT,
203        cpu_percent REAL NOT NULL,
204        disk_read_mb REAL NOT NULL,
205        disk_write_mb REAL NOT NULL,
206        memory_percent REAL NOT NULL,
207        recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP
208      );
209      CREATE INDEX IF NOT EXISTS idx_system_metrics_recorded_at ON system_metrics(recorded_at);
210      CREATE TABLE IF NOT EXISTS system_health (
211        id INTEGER PRIMARY KEY AUTOINCREMENT,
212        check_type TEXT NOT NULL,
213        status TEXT NOT NULL CHECK (status IN ('ok', 'warning', 'critical')),
214        details TEXT,
215        action_taken TEXT,
216        created_at TEXT DEFAULT (datetime('now'))
217      );
218      CREATE INDEX IF NOT EXISTS idx_system_health_check_type ON system_health(check_type);
219      CREATE INDEX IF NOT EXISTS idx_system_health_created_at ON system_health(created_at);
220      CREATE INDEX IF NOT EXISTS idx_system_health_status ON system_health(status);
221      CREATE TABLE IF NOT EXISTS pipeline_metrics (
222        id INTEGER PRIMARY KEY AUTOINCREMENT,
223        stage_name TEXT NOT NULL,
224        sites_processed INTEGER DEFAULT 0,
225        sites_succeeded INTEGER DEFAULT 0,
226        sites_failed INTEGER DEFAULT 0,
227        duration_ms INTEGER NOT NULL,
228        started_at DATETIME NOT NULL,
229        finished_at DATETIME NOT NULL,
230        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
231      );
232      CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_stage ON pipeline_metrics(stage_name, started_at);
233      CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_started ON pipeline_metrics(started_at);
234    `);
235    tel.close();
236  
237    return { opsPath, telPath };
238  }
239  
240  export function teardownSplitDbs() {
241    delete process.env.OPS_DB_PATH;
242    delete process.env.TEL_DB_PATH;
243  }
244  
245  // Auto-run if invoked as a module initialiser (--import flag)
246  if (process.env.DATABASE_PATH) {
247    setupSplitDbs(process.env.DATABASE_PATH);
248  }