1  #!/usr/bin/env node
  2  /**
  3   * check-rotation-schedule.js
  4   *
  5   * Reads credentials-metadata.json and reports overdue credential rotations.
  6   * Creates a human-review queue entry for each overdue credential so it surfaces
  7   * in the next pipeline review session.
  8   *
  9   * Usage:
 10   *   node scripts/check-rotation-schedule.js            # report only
 11   *   node scripts/check-rotation-schedule.js --queue    # also write to human_review queue
 12   */
 13  
 14  import { readFileSync } from 'fs';
 15  import { join, dirname } from 'path';
 16  import { fileURLToPath } from 'url';
 17  import { createDatabaseConnection } from '../src/utils/db.js';
 18  
 19  const __dirname = dirname(fileURLToPath(import.meta.url));
 20  const projectRoot = join(__dirname, '..');
 21  
 22  const metadataPath = join(__dirname, 'credentials-metadata.json');
 23  const writeToQueue = process.argv.includes('--queue');
 24  
 25  function loadMetadata() {
 26    const raw = readFileSync(metadataPath, 'utf-8');
 27    return JSON.parse(raw);
 28  }
 29  
 30  function checkOverdue(credentials) {
 31    const today = new Date();
 32    const results = [];
 33  
 34    for (const cred of credentials) {
 35      if (!cred.last_rotated) {
 36        results.push({ ...cred, status: 'never_rotated', days_overdue: null });
 37        continue;
 38      }
 39  
 40      const lastRotated = new Date(cred.last_rotated);
 41      const dueDate = new Date(lastRotated);
 42      dueDate.setDate(dueDate.getDate() + cred.interval_days);
 43  
 44      const daysOverdue = Math.floor((today - dueDate) / (1000 * 60 * 60 * 24));
 45      if (daysOverdue >= 0) {
 46        results.push({ ...cred, status: 'overdue', days_overdue: daysOverdue, due_date: dueDate.toISOString().slice(0, 10) });
 47      } else {
 48        const daysUntilDue = -daysOverdue;
 49        if (daysUntilDue <= 14) {
 50          results.push({ ...cred, status: 'due_soon', days_overdue: null, days_until_due: daysUntilDue, due_date: dueDate.toISOString().slice(0, 10) });
 51        }
 52      }
 53    }
 54  
 55    return results;
 56  }
 57  
 58  function writeHumanReviewItems(items) {
 59    const dbPath = join(projectRoot, 'db/sites.db');
 60    const db = createDatabaseConnection(dbPath);
 61  
 62    const insert = db.prepare(`
 63      INSERT INTO human_review (review_type, payload, created_at)
 64      VALUES ('credential_rotation', ?, CURRENT_TIMESTAMP)
 65    `);
 66  
 67    let written = 0;
 68    for (const item of items) {
 69      if (item.status !== 'overdue' && item.status !== 'never_rotated') continue;
 70  
 71      // Avoid duplicate entries: check if there's already an open item for this credential
 72      const existing = db.prepare(`
 73        SELECT id FROM human_review
 74        WHERE review_type = 'credential_rotation'
 75          AND json_extract(payload, '$.service') = ?
 76          AND resolved_at IS NULL
 77      `).get(item.service);
 78  
 79      if (existing) continue;
 80  
 81      insert.run(JSON.stringify({
 82        service: item.service,
 83        status: item.status,
 84        days_overdue: item.days_overdue,
 85        interval_days: item.interval_days,
 86        last_rotated: item.last_rotated,
 87        note: item.note,
 88      }));
 89      written++;
 90    }
 91  
 92    db.close();
 93    return written;
 94  }
 95  
 96  function printReport(overdue, all) {
 97    const today = new Date().toISOString().slice(0, 10);
 98    console.log(`\nCredential Rotation Check — ${today}\n`);
 99  
100    if (overdue.length === 0) {
101      console.log('  All credentials are current. Nothing overdue.');
102      return;
103    }
104  
105    const neverRotated = overdue.filter(c => c.status === 'never_rotated');
106    const overdueItems = overdue.filter(c => c.status === 'overdue');
107    const dueSoon = overdue.filter(c => c.status === 'due_soon');
108  
109    if (neverRotated.length > 0) {
110      console.log('  NEVER ROTATED (set last_rotated in credentials-metadata.json after first rotation):');
111      for (const c of neverRotated) {
112        console.log(`    ✗ ${c.service.padEnd(32)} — ${c.note}`);
113      }
114      console.log('');
115    }
116  
117    if (overdueItems.length > 0) {
118      console.log('  OVERDUE:');
119      for (const c of overdueItems) {
120        console.log(`    ✗ ${c.service.padEnd(32)} — ${c.days_overdue}d overdue (due ${c.due_date})`);
121      }
122      console.log('');
123    }
124  
125    if (dueSoon.length > 0) {
126      console.log('  DUE SOON (within 14 days):');
127      for (const c of dueSoon) {
128        console.log(`    ! ${c.service.padEnd(32)} — due in ${c.days_until_due}d (${c.due_date})`);
129      }
130      console.log('');
131    }
132  
133    console.log(`  See scripts/credential-rotation-playbook.md for rotation steps.`);
134    console.log('');
135  }
136  
137  // Main
138  const { credentials } = loadMetadata();
139  const items = checkOverdue(credentials);
140  
141  printReport(items, credentials);
142  
143  if (writeToQueue) {
144    try {
145      const written = writeHumanReviewItems(items);
146      if (written > 0) {
147        console.log(`  ${written} item(s) added to human_review queue.`);
148      } else {
149        console.log('  No new items added to queue (already queued or nothing overdue).');
150      }
151    } catch (err) {
152      console.error('  Warning: could not write to human_review queue:', err.message);
153      console.error('  (Run without --queue to skip this step)');
154    }
155  }