cve-dashboard/backend/migrations/add_compliance_tables.js

// Migration: Add compliance_uploads, compliance_items, compliance_notes tables
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

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

console.log('Starting add_compliance_tables migration...');

db.serialize(() => {
    // Each xlsx upload — one row per file ingested
    db.run(`
        CREATE TABLE IF NOT EXISTS compliance_uploads (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            filename    TEXT NOT NULL,
            report_date TEXT,
            uploaded_by INTEGER,
            uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            new_count       INTEGER DEFAULT 0,
            resolved_count  INTEGER DEFAULT 0,
            recurring_count INTEGER DEFAULT 0,
            FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
        )
    `, (err) => {
        if (err) console.error('Error creating compliance_uploads:', err);
        else console.log('✓ compliance_uploads created');
    });

    // One row per non-compliant asset per metric per upload.
    // hostname + metric_id is the stable identity key used to link history and notes.
    db.run(`
        CREATE TABLE IF NOT EXISTS compliance_items (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            upload_id   INTEGER NOT NULL,
            hostname    TEXT NOT NULL,
            ip_address  TEXT,
            device_type TEXT,
            team        TEXT,
            metric_id   TEXT NOT NULL,
            metric_desc TEXT,
            category    TEXT,
            extra_json  TEXT,
            status      TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'resolved')),
            first_seen_upload_id INTEGER,
            resolved_upload_id   INTEGER,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (upload_id) REFERENCES compliance_uploads(id) ON DELETE CASCADE,
            FOREIGN KEY (first_seen_upload_id) REFERENCES compliance_uploads(id) ON DELETE SET NULL,
            FOREIGN KEY (resolved_upload_id)   REFERENCES compliance_uploads(id) ON DELETE SET NULL
        )
    `, (err) => {
        if (err) console.error('Error creating compliance_items:', err);
        else console.log('✓ compliance_items created');
    });

    db.run(`
        CREATE INDEX IF NOT EXISTS idx_compliance_items_upload
        ON compliance_items(upload_id)
    `, (err) => {
        if (err) console.error('Error creating upload index:', err);
        else console.log('✓ idx_compliance_items_upload created');
    });

    db.run(`
        CREATE INDEX IF NOT EXISTS idx_compliance_items_identity
        ON compliance_items(hostname, metric_id)
    `, (err) => {
        if (err) console.error('Error creating identity index:', err);
        else console.log('✓ idx_compliance_items_identity created');
    });

    db.run(`
        CREATE INDEX IF NOT EXISTS idx_compliance_items_team_status
        ON compliance_items(team, status)
    `, (err) => {
        if (err) console.error('Error creating team/status index:', err);
        else console.log('✓ idx_compliance_items_team_status created');
    });

    // Notes keyed on (hostname, metric_id) — persists across uploads.
    // Each note is its own row so history is preserved.
    db.run(`
        CREATE TABLE IF NOT EXISTS compliance_notes (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            hostname    TEXT NOT NULL,
            metric_id   TEXT NOT NULL,
            note        TEXT NOT NULL,
            created_by  INTEGER,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
        )
    `, (err) => {
        if (err) console.error('Error creating compliance_notes:', err);
        else console.log('✓ compliance_notes created');
    });

    db.run(`
        CREATE INDEX IF NOT EXISTS idx_compliance_notes_identity
        ON compliance_notes(hostname, metric_id)
    `, (err) => {
        if (err) console.error('Error creating notes identity index:', err);
        else console.log('✓ idx_compliance_notes_identity created');
    });
});

db.close(() => {
    console.log('Migration complete!');
});