cve-dashboard/backend/migrations/add_closed_gone_state.js

// Migration: Add CLOSED_GONE state to ivanti_finding_archives
//
// The archive table tracks findings that disappear from the Open findings set.
// Previously it only tracked: ARCHIVED → RETURNED → CLOSED.
//
// This migration adds a CLOSED_GONE state for findings that were confirmed
// in the Ivanti Closed set but then disappeared from it on a subsequent sync.
// This closes a visibility gap where findings could vanish from the Closed API
// results (e.g., due to VRR rescore below the severity threshold) without
// being tracked.
//
// SQLite does not support ALTER TABLE to modify CHECK constraints, so this
// migration recreates the table with the expanded constraint.
//
// Safe to re-run — uses IF NOT EXISTS and checks for existing data.
//
// Usage: node backend/migrations/add_closed_gone_state.js

const path    = require('path');
const sqlite3 = require('sqlite3').verbose();
const dbPath  = path.join(__dirname, '..', 'cve_database.db');
const db      = new sqlite3.Database(dbPath);

console.log('Starting CLOSED_GONE state migration...');

function run(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
            if (err) reject(err);
            else resolve(this);
        });
    });
}

function all(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) reject(err);
            else resolve(rows || []);
        });
    });
}

async function migrate() {
    // Check if the table already has the CLOSED_GONE state
    const tableInfo = await all("SELECT sql FROM sqlite_master WHERE name='ivanti_finding_archives'");
    if (tableInfo.length > 0 && tableInfo[0].sql.includes('CLOSED_GONE')) {
        console.log('✓ ivanti_finding_archives already has CLOSED_GONE state — skipping');
        return;
    }

    if (tableInfo.length === 0) {
        // Table doesn't exist yet — create it fresh with the new constraint
        await run(`
            CREATE TABLE ivanti_finding_archives (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                finding_id TEXT NOT NULL UNIQUE,
                finding_title TEXT NOT NULL DEFAULT '',
                host_name TEXT NOT NULL DEFAULT '',
                ip_address TEXT NOT NULL DEFAULT '',
                current_state TEXT NOT NULL CHECK(current_state IN ('ARCHIVED','RETURNED','CLOSED','CLOSED_GONE')),
                last_severity REAL NOT NULL DEFAULT 0,
                first_archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_transition_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('✓ Created ivanti_finding_archives with CLOSED_GONE state');
        return;
    }

    // Table exists but needs the constraint updated — recreate with data migration
    console.log('  Recreating table with expanded CHECK constraint...');

    await run('BEGIN TRANSACTION');
    try {
        // 1. Rename existing table
        await run('ALTER TABLE ivanti_finding_archives RENAME TO ivanti_finding_archives_old');

        // 2. Create new table with expanded constraint
        await run(`
            CREATE TABLE ivanti_finding_archives (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                finding_id TEXT NOT NULL UNIQUE,
                finding_title TEXT NOT NULL DEFAULT '',
                host_name TEXT NOT NULL DEFAULT '',
                ip_address TEXT NOT NULL DEFAULT '',
                current_state TEXT NOT NULL CHECK(current_state IN ('ARCHIVED','RETURNED','CLOSED','CLOSED_GONE')),
                last_severity REAL NOT NULL DEFAULT 0,
                first_archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_transition_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 3. Copy data
        await run(`
            INSERT INTO ivanti_finding_archives
                (id, finding_id, finding_title, host_name, ip_address, current_state,
                 last_severity, first_archived_at, last_transition_at, created_at)
            SELECT id, finding_id, finding_title, host_name, ip_address, current_state,
                   last_severity, first_archived_at, last_transition_at, created_at
            FROM ivanti_finding_archives_old
        `);

        // 4. Recreate indexes
        await run('CREATE INDEX IF NOT EXISTS idx_archive_finding_id ON ivanti_finding_archives(finding_id)');
        await run('CREATE INDEX IF NOT EXISTS idx_archive_current_state ON ivanti_finding_archives(current_state)');

        // 5. Drop old table
        await run('DROP TABLE ivanti_finding_archives_old');

        await run('COMMIT');
        console.log('✓ ivanti_finding_archives updated with CLOSED_GONE state');
    } catch (err) {
        await run('ROLLBACK').catch(() => {});
        throw err;
    }
}

migrate()
    .then(() => {
        console.log('Migration complete.');
        db.close();
    })
    .catch((err) => {
        console.error('Migration failed:', err);
        db.close();
        process.exit(1);
    });