cve-dashboard/backend/migrate-to-1.1.js

#!/usr/bin/env node
// Migration script: v1.0.0 -> v1.1.0
// Adds: users, sessions tables, multi-vendor support, vendor column in documents
// Run: node migrate-to-1.1.js

const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const fs = require('fs');
const path = require('path');

const DB_FILE = './cve_database.db';
const BACKUP_FILE = `./cve_database_backup_${Date.now()}.db`;

async function migrate() {
    console.log('╔════════════════════════════════════════════════════════╗');
    console.log('║       CVE Database Migration: v1.0.0 → v1.1.0          ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');

    // Check if database exists
    if (!fs.existsSync(DB_FILE)) {
        console.log('❌ Database not found. Run setup.js for fresh install.');
        process.exit(1);
    }

    // Backup database
    console.log('📦 Creating backup...');
    fs.copyFileSync(DB_FILE, BACKUP_FILE);
    console.log(`   ✓ Backup saved to: ${BACKUP_FILE}\n`);

    const db = new sqlite3.Database(DB_FILE);

    try {
        // Run migrations in sequence
        await addUsersTable(db);
        await addSessionsTable(db);
        await addVendorToDocuments(db);
        await updateCvesConstraint(db);
        await createDefaultAdmin(db);
        await updateView(db);

        console.log('\n╔════════════════════════════════════════════════════════╗');
        console.log('║            MIGRATION COMPLETE!                         ║');
        console.log('╚════════════════════════════════════════════════════════╝');
        console.log('\n📋 Summary:');
        console.log('   ✓ Users table added');
        console.log('   ✓ Sessions table added');
        console.log('   ✓ Vendor column added to documents');
        console.log('   ✓ Multi-vendor constraint applied to cves');
        console.log('   ✓ Default admin user created (admin/admin123)');
        console.log(`\n💾 Backup saved: ${BACKUP_FILE}`);
        console.log('\n🚀 Restart your server to apply changes.\n');

    } catch (error) {
        console.error('\n❌ Migration failed:', error.message);
        console.log(`\n🔄 To restore from backup: cp ${BACKUP_FILE} ${DB_FILE}`);
        process.exit(1);
    } finally {
        db.close();
    }
}

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

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

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

async function addUsersTable(db) {
    console.log('1️⃣  Adding users table...');

    const exists = await get(db,
        "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
    );

    if (exists) {
        console.log('   ⏭️  Users table already exists, skipping');
        return;
    }

    await run(db, `
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            role VARCHAR(20) NOT NULL DEFAULT 'viewer',
            is_active BOOLEAN DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_login TIMESTAMP,
            CHECK (role IN ('admin', 'editor', 'viewer'))
        )
    `);

    await run(db, 'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)');
    console.log('   ✓ Users table created');
}

async function addSessionsTable(db) {
    console.log('2️⃣  Adding sessions table...');

    const exists = await get(db,
        "SELECT name FROM sqlite_master WHERE type='table' AND name='sessions'"
    );

    if (exists) {
        console.log('   ⏭️  Sessions table already exists, skipping');
        return;
    }

    await run(db, `
        CREATE TABLE sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id VARCHAR(255) UNIQUE NOT NULL,
            user_id INTEGER NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    `);

    await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id)');
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)');
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at)');
    console.log('   ✓ Sessions table created');
}

async function addVendorToDocuments(db) {
    console.log('3️⃣  Adding vendor column to documents...');

    // Check if vendor column exists
    const columns = await all(db, "PRAGMA table_info(documents)");
    const hasVendor = columns.some(col => col.name === 'vendor');

    if (hasVendor) {
        console.log('   ⏭️  Vendor column already exists, skipping');
        return;
    }

    // Add vendor column
    await run(db, "ALTER TABLE documents ADD COLUMN vendor VARCHAR(100)");

    // Populate vendor from the cves table based on cve_id
    await run(db, `
        UPDATE documents
        SET vendor = (
            SELECT c.vendor
            FROM cves c
            WHERE c.cve_id = documents.cve_id
            LIMIT 1
        )
        WHERE vendor IS NULL
    `);

    // Set default for any remaining nulls
    await run(db, "UPDATE documents SET vendor = 'Unknown' WHERE vendor IS NULL");

    await run(db, 'CREATE INDEX IF NOT EXISTS idx_doc_vendor ON documents(vendor)');
    console.log('   ✓ Vendor column added and populated');
}

async function updateCvesConstraint(db) {
    console.log('4️⃣  Updating CVEs table for multi-vendor support...');

    // Check current schema
    const tableInfo = await get(db,
        "SELECT sql FROM sqlite_master WHERE type='table' AND name='cves'"
    );

    if (tableInfo.sql.includes('UNIQUE(cve_id, vendor)')) {
        console.log('   ⏭️  Multi-vendor constraint already exists, skipping');
        return;
    }

    // SQLite doesn't support ALTER CONSTRAINT, so we need to rebuild the table
    console.log('   📋 Rebuilding table with new constraint...');

    // Create new table with correct schema
    await run(db, `
        CREATE TABLE cves_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            cve_id VARCHAR(20) NOT NULL,
            vendor VARCHAR(100) NOT NULL,
            severity VARCHAR(20) NOT NULL,
            description TEXT,
            published_date DATE,
            status VARCHAR(50) DEFAULT 'Open',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(cve_id, vendor)
        )
    `);

    // Copy data
    await run(db, `
        INSERT INTO cves_new (id, cve_id, vendor, severity, description, published_date, status, created_at, updated_at)
        SELECT id, cve_id, vendor, severity, description, published_date, status, created_at, updated_at
        FROM cves
    `);

    // Drop old table
    await run(db, 'DROP TABLE cves');

    // Rename new table
    await run(db, 'ALTER TABLE cves_new RENAME TO cves');

    // Recreate indexes
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_cve_id ON cves(cve_id)');
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_vendor ON cves(vendor)');
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_severity ON cves(severity)');
    await run(db, 'CREATE INDEX IF NOT EXISTS idx_status ON cves(status)');

    console.log('   ✓ Multi-vendor constraint applied');
}

async function createDefaultAdmin(db) {
    console.log('5️⃣  Creating default admin user...');

    const exists = await get(db, "SELECT id FROM users WHERE username = 'admin'");

    if (exists) {
        console.log('   ⏭️  Admin user already exists, skipping');
        return;
    }

    const passwordHash = await bcrypt.hash('admin123', 10);

    await run(db, `
        INSERT INTO users (username, email, password_hash, role, is_active)
        VALUES (?, ?, ?, ?, ?)
    `, ['admin', 'admin@localhost', passwordHash, 'admin', 1]);

    console.log('   ✓ Admin user created (admin/admin123)');
}

async function updateView(db) {
    console.log('6️⃣  Updating document status view...');

    // Drop old view if exists
    await run(db, 'DROP VIEW IF EXISTS cve_document_status');

    // Create updated view with multi-vendor support
    await run(db, `
        CREATE VIEW cve_document_status AS
        SELECT
            c.id as record_id,
            c.cve_id,
            c.vendor,
            c.severity,
            c.status,
            COUNT(DISTINCT d.id) as total_documents,
            COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) as advisory_count,
            COUNT(DISTINCT CASE WHEN d.type = 'email' THEN d.id END) as email_count,
            COUNT(DISTINCT CASE WHEN d.type = 'screenshot' THEN d.id END) as screenshot_count,
            CASE
                WHEN COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) > 0
                THEN 'Complete'
                ELSE 'Missing Required Docs'
            END as compliance_status
        FROM cves c
        LEFT JOIN documents d ON c.cve_id = d.cve_id AND c.vendor = d.vendor
        GROUP BY c.id, c.cve_id, c.vendor, c.severity, c.status
    `);

    console.log('   ✓ View updated');
}

// Run migration
migrate();