1  #!/usr/bin/env node
  2  
  3  /**
  4   * Migrate Hardcoded Cron Tasks to Database
  5   *
  6   * One-time migration script to move TASKS from src/cron.js to the cron_jobs table.
  7   * Run after creating the cron_jobs table with migration 029.
  8   *
  9   * Usage: node scripts/migrate-cron-tasks.js
 10   */
 11  
 12  import { createDatabaseConnection } from '../src/utils/db.js';
 13  import { join, dirname } from 'path';
 14  import { fileURLToPath } from 'url';
 15  import dotenv from 'dotenv';
 16  
 17  dotenv.config();
 18  
 19  const __filename = fileURLToPath(import.meta.url);
 20  const __dirname = dirname(__filename);
 21  const projectRoot = join(__dirname, '..');
 22  const dbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 23  
 24  // Hardcoded tasks from src/cron.js (as of migration time)
 25  const LEGACY_TASKS = {
 26    syncEmailEvents: {
 27      name: 'Sync Email Events',
 28      description: 'Sync email tracking events from Cloudflare R2 to local database',
 29      handler_type: 'function',
 30      handler_value: 'syncEmailEvents',
 31      interval: { value: 5, unit: 'minutes' },
 32      enabled: true,
 33    },
 34    syncUnsubscribes: {
 35      name: 'Sync Unsubscribes',
 36      description: 'Sync unsubscribe requests from Cloudflare Worker to local database',
 37      handler_type: 'function',
 38      handler_value: 'syncUnsubscribes',
 39      interval: { value: 5, unit: 'minutes' },
 40      enabled: true,
 41    },
 42    pollInboundSMS: {
 43      name: 'Poll Inbound SMS',
 44      description: 'Poll Twilio for new inbound SMS messages',
 45      handler_type: 'function',
 46      handler_value: 'pollInboundSMS',
 47      interval: { value: 5, unit: 'minutes' },
 48      enabled: true,
 49    },
 50    runScoring: {
 51      name: 'Run Scoring Stage',
 52      description: 'Process sites through GPT-4o-mini scoring stage',
 53      handler_type: 'command',
 54      handler_value: 'npm run scoring',
 55      interval: { value: 5, unit: 'minutes' },
 56      enabled: true,
 57    },
 58    runRescoring: {
 59      name: 'Run Rescoring Stage',
 60      description: 'Rescore B- and below sites with below-fold screenshots',
 61      handler_type: 'command',
 62      handler_value: 'npm run rescoring',
 63      interval: { value: 5, unit: 'minutes' },
 64      enabled: true,
 65    },
 66    runEnrich: {
 67      name: 'Run Enrichment Stage',
 68      description: 'Browse key pages to find additional contact information',
 69      handler_type: 'command',
 70      handler_value: 'npm run enrich',
 71      interval: { value: 5, unit: 'minutes' },
 72      enabled: true,
 73    },
 74    runProposals: {
 75      name: 'Run Proposals Stage',
 76      description: 'Generate personalized proposals for each contact',
 77      handler_type: 'command',
 78      handler_value: 'npm run proposals',
 79      interval: { value: 5, unit: 'minutes' },
 80      enabled: true,
 81    },
 82    processPipeline: {
 83      name: 'Process Pipeline Queue',
 84      description: 'Legacy queue-based pipeline processing',
 85      handler_type: 'command',
 86      handler_value: 'node src/process.js 10',
 87      interval: { value: 15, unit: 'minutes' },
 88      enabled: false,
 89    },
 90    checkKeywords: {
 91      name: 'Check Keyword Queue',
 92      description: 'Monitor keyword processing status',
 93      handler_type: 'function',
 94      handler_value: 'checkKeywords',
 95      interval: { value: 60, unit: 'minutes' },
 96      enabled: true,
 97    },
 98    syncKeywordCounters: {
 99      name: 'Sync Keyword Counters',
100      description: 'Sync operational counters from sites table to keywords table',
101      handler_type: 'command',
102      handler_value: 'node scripts/sync-keyword-counters.js',
103      interval: { value: 1, unit: 'days' },
104      enabled: true,
105    },
106    databaseMaintenance: {
107      name: 'Database Maintenance',
108      description: 'Run PRAGMA optimize and integrity checks',
109      handler_type: 'function',
110      handler_value: 'databaseMaintenance',
111      interval: { value: 1, unit: 'days' },
112      enabled: true,
113    },
114    runTests: {
115      name: 'Run Unit Tests',
116      description: 'Execute full unit test suite with coverage',
117      handler_type: 'command',
118      handler_value: 'npm test',
119      interval: { value: 1, unit: 'days' },
120      enabled: false,
121    },
122    lintCode: {
123      name: 'Lint and Auto-fix Code',
124      description: 'Run ESLint with auto-fix on all source files',
125      handler_type: 'command',
126      handler_value: 'npm run lint:fix',
127      interval: { value: 1, unit: 'days' },
128      enabled: false,
129    },
130    formatCode: {
131      name: 'Format Code with Prettier',
132      description: 'Run Prettier on all source files',
133      handler_type: 'command',
134      handler_value: 'npm run format',
135      interval: { value: 1, unit: 'days' },
136      enabled: false,
137    },
138    securityAudit: {
139      name: 'NPM Security Audit',
140      description: 'Check npm dependencies for known security vulnerabilities',
141      handler_type: 'command',
142      handler_value: 'npm audit',
143      interval: { value: 1, unit: 'days' },
144      enabled: true,
145    },
146    checkDependencies: {
147      name: 'Check Outdated Dependencies',
148      description: 'Check for outdated npm dependencies',
149      handler_type: 'command',
150      handler_value: 'npm outdated',
151      interval: { value: 1, unit: 'days' },
152      enabled: true,
153    },
154    generateCoverage: {
155      name: 'Generate Coverage Report',
156      description: 'Generate code coverage report with c8',
157      handler_type: 'command',
158      handler_value: 'npm run test',
159      interval: { value: 1, unit: 'days' },
160      enabled: false,
161    },
162    // NOTE: sageReview and claudeCodeDocCheck removed - included in unifiedAutofix
163    fullTestSuite: {
164      name: 'Full Test Suite with Coverage',
165      description: 'Run complete test suite including unit and integration tests',
166      handler_type: 'command',
167      handler_value: 'npm run test:all',
168      interval: { value: 7, unit: 'days' },
169      enabled: false,
170    },
171    vacuumDatabase: {
172      name: 'Full Database Vacuum',
173      description: 'Run VACUUM + ANALYZE on sites.db and all backup DBs daily',
174      handler_type: 'function',
175      handler_value: 'vacuumDatabase',
176      interval: { value: 1, unit: 'days' },
177      enabled: true,
178    },
179    backupDatabase: {
180      name: 'Backup Database',
181      description: 'Create timestamped backup of sites.db',
182      handler_type: 'function',
183      handler_value: 'backupDatabase',
184      interval: { value: 7, unit: 'days' },
185      enabled: true,
186    },
187    analyzePerformance: {
188      name: 'Analyze Database Performance',
189      description: 'Analyze table and index statistics',
190      handler_type: 'function',
191      handler_value: 'analyzePerformance',
192      interval: { value: 7, unit: 'days' },
193      enabled: true,
194    },
195    checkRateLimits: {
196      name: 'Check API Rate Limit Health',
197      description: 'Check API key configuration status',
198      handler_type: 'function',
199      handler_value: 'checkRateLimits',
200      interval: { value: 7, unit: 'days' },
201      enabled: true,
202    },
203    updateClaudeCode: {
204      name: 'Update Claude Code CLI',
205      description: 'Check for and apply Claude Code CLI updates',
206      handler_type: 'command',
207      handler_value: 'claude update',
208      interval: { value: 7, unit: 'days' },
209      enabled: true,
210    },
211    // NOTE: deepCodeAnalysis removed - included in unifiedAutofix
212    technicalDebtReview: {
213      name: 'Technical Debt Review',
214      description: 'Review TODO.md for incomplete tasks',
215      handler_type: 'function',
216      handler_value: 'technicalDebtReview',
217      interval: { value: 30, unit: 'days' },
218      enabled: true,
219    },
220    // NOTE: updateDependencies removed - included in unifiedAutofix
221    securityScan: {
222      name: 'Full Security Scan',
223      description: 'Run npm audit with moderate severity threshold',
224      handler_type: 'command',
225      handler_value: 'npm audit --audit-level=moderate',
226      interval: { value: 30, unit: 'days' },
227      enabled: true,
228    },
229    unifiedAutofix: {
230      name: 'Unified Auto-Fix (Weekly Maintenance)',
231      description:
232        'Run all automated maintenance tasks (format, lint, security, deps, Sage AI, docs, tests) and commit to shared autofix branch for human review',
233      handler_type: 'function',
234      handler_value: 'unifiedAutofix',
235      interval: { value: 7, unit: 'days' },
236      enabled: true,
237    },
238  };
239  
240  async function main() {
241    console.log('🔄 Migrating cron tasks to database...\n');
242  
243    const db = createDatabaseConnection(dbPath);
244  
245    try {
246      // Check if table exists (in ops.db after split)
247      const tableExists = db
248        .prepare("SELECT name FROM ops.sqlite_master WHERE type='table' AND name='cron_jobs'")
249        .get();
250  
251      if (!tableExists) {
252        console.error(
253          '❌ Error: cron_jobs table does not exist. Run scripts/init-ops-db.js first to create ops.db.'
254        );
255        process.exit(1);
256      }
257  
258      // Check if already migrated
259      const existingCount = db.prepare('SELECT COUNT(*) as count FROM ops.cron_jobs').get().count;
260  
261      if (existingCount > 0) {
262        console.log(`⚠️  Found ${existingCount} existing jobs in database.`);
263        console.log('   This migration will skip existing task_keys to avoid duplicates.\n');
264      }
265  
266      // Prepare insert statement
267      const insertStmt = db.prepare(`
268        INSERT INTO ops.cron_jobs (
269          name, task_key, description, handler_type, handler_value,
270          interval_value, interval_unit, enabled
271        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
272      `);
273  
274      let inserted = 0;
275      let skipped = 0;
276  
277      for (const [taskKey, task] of Object.entries(LEGACY_TASKS)) {
278        // Check if already exists
279        const existing = db.prepare('SELECT task_key FROM ops.cron_jobs WHERE task_key = ?').get(taskKey);
280  
281        if (existing) {
282          console.log(`⏭️  Skipping ${taskKey} (already exists)`);
283          skipped++;
284          continue;
285        }
286  
287        // Insert task
288        insertStmt.run(
289          task.name,
290          taskKey,
291          task.description,
292          task.handler_type,
293          task.handler_value,
294          task.interval.value,
295          task.interval.unit,
296          task.enabled ? 1 : 0
297        );
298  
299        console.log(`✅ Migrated ${taskKey}: ${task.name}`);
300        inserted++;
301      }
302  
303      console.log(`\n✨ Migration complete!`);
304      console.log(`   Inserted: ${inserted} jobs`);
305      console.log(`   Skipped: ${skipped} jobs`);
306      console.log(`   Total: ${inserted + skipped + existingCount} jobs in database\n`);
307      console.log('📋 View all jobs: npm run cron:list');
308      console.log('⚙️  Manage jobs: npm run cron:manage');
309  
310      return 0;
311    } catch (error) {
312      console.error('❌ Migration failed:', error);
313      return 1;
314    } finally {
315      db.close();
316    }
317  }
318  
319  // Run if called directly
320  if (import.meta.url === `file://${process.argv[1]}`) {
321    main()
322      .then(code => process.exit(code))
323      .catch(error => {
324        console.error('Fatal error:', error);
325        process.exit(1);
326      });
327  }
328  
329  export { main, LEGACY_TASKS };