1  #!/usr/bin/env node
  2  
  3  /**
  4   * Cleanup Stale Cron Job Logs
  5   *
  6   * Finds cron job logs stuck in "running" status for more than 2 hours
  7   * and marks them as "timeout" to prevent dashboard confusion.
  8   *
  9   * Usage:
 10   *   npm run cron:cleanup-stale
 11   *   node scripts/cleanup-stale-jobs.js
 12   */
 13  
 14  import { createDatabaseConnection } from '../src/utils/db.js';
 15  import { join, dirname } from 'path';
 16  import { fileURLToPath } from 'url';
 17  import Logger from '../src/utils/logger.js';
 18  import dotenv from 'dotenv';
 19  
 20  dotenv.config();
 21  
 22  const __filename = fileURLToPath(import.meta.url);
 23  const __dirname = dirname(__filename);
 24  const projectRoot = join(__dirname, '..');
 25  
 26  const logger = new Logger('CleanupStaleJobs');
 27  const dbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 28  
 29  /**
 30   * Clean up stale running jobs
 31   */
 32  function cleanupStaleJobs(staleThresholdMinutes = 120) {
 33    const db = createDatabaseConnection(dbPath);
 34  
 35    try {
 36      // Find jobs stuck in "running" status for more than threshold
 37      const staleJobs = db
 38        .prepare(
 39          `
 40        SELECT
 41          id,
 42          job_name,
 43          started_at,
 44          CAST((julianday('now') - julianday(started_at)) * 24 * 60 AS INTEGER) as age_minutes
 45        FROM ops.cron_job_logs
 46        WHERE status = 'running'
 47          AND datetime(started_at, '+' || ? || ' minutes') < datetime('now')
 48        ORDER BY started_at ASC
 49      `
 50        )
 51        .all(staleThresholdMinutes);
 52  
 53      if (staleJobs.length === 0) {
 54        logger.success('No stale jobs found');
 55        return { cleaned: 0, stale: [] };
 56      }
 57  
 58      logger.warn(`Found ${staleJobs.length} stale jobs (older than ${staleThresholdMinutes} min)`);
 59  
 60      // Update each stale job to "timeout" status
 61      const updateStmt = db.prepare(
 62        `
 63        UPDATE ops.cron_job_logs
 64        SET
 65          finished_at = datetime('now'),
 66          status = 'timeout',
 67          summary = 'Job timed out - marked as stale after ' || ? || ' minutes',
 68          error_message = 'Job did not complete within expected time window (likely crashed)',
 69          items_failed = 1
 70        WHERE id = ?
 71      `
 72      );
 73  
 74      let cleaned = 0;
 75      for (const job of staleJobs) {
 76        logger.info(
 77          `Cleaning up: ${job.job_name} (started ${job.started_at}, ${job.age_minutes} min ago)`
 78        );
 79        updateStmt.run(job.age_minutes, job.id);
 80        cleaned++;
 81      }
 82  
 83      logger.success(`✓ Cleaned up ${cleaned} stale jobs`);
 84  
 85      return {
 86        cleaned,
 87        stale: staleJobs.map(j => ({
 88          id: j.id,
 89          name: j.job_name,
 90          started_at: j.started_at,
 91          age_minutes: j.age_minutes,
 92        })),
 93      };
 94    } finally {
 95      db.close();
 96    }
 97  }
 98  
 99  /**
100   * Main CLI handler
101   */
102  async function main() {
103    const args = process.argv.slice(2);
104    const thresholdArg = args.find(arg => arg.startsWith('--threshold='));
105    const threshold = thresholdArg ? parseInt(thresholdArg.split('=')[1], 10) : 120;
106  
107    logger.info(`Starting stale job cleanup (threshold: ${threshold} minutes)`);
108  
109    try {
110      const result = cleanupStaleJobs(threshold);
111  
112      console.log('\n=== Cleanup Summary ===');
113      console.log(`Stale jobs found: ${result.stale.length}`);
114      console.log(`Jobs cleaned up: ${result.cleaned}`);
115  
116      if (result.stale.length > 0) {
117        console.log('\nCleaned jobs:');
118        result.stale.forEach(job => {
119          console.log(`  • ${job.name} (${job.age_minutes} min old, started ${job.started_at})`);
120        });
121      }
122  
123      console.log('====================\n');
124  
125      return 0;
126    } catch (error) {
127      logger.error('Cleanup failed:', error);
128      return 1;
129    }
130  }
131  
132  // Run if called directly
133  if (import.meta.url === `file://${process.argv[1]}`) {
134    main()
135      .then(code => process.exit(code))
136      .catch(error => {
137        console.error('Fatal error:', error);
138        process.exit(1);
139      });
140  }
141  
142  export { cleanupStaleJobs };