cve-dashboard/backend/migrations/add_user_groups.js

// Migration: Add user_group column to users table and map legacy roles
// Mapping: admin→Admin, editor→Standard_User, viewer→Read_Only
// NULL/unrecognized roles default to Read_Only
// Idempotent — safe to run multiple times
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

/**
 * Run the migration against the given database instance.
 * Exported for testing with in-memory databases.
 * @param {sqlite3.Database} db
 * @returns {Promise<void>}
 */
function runMigration(db) {
    return new Promise((resolve, reject) => {
        db.serialize(() => {
            // Check if user_group column already exists
            db.all("PRAGMA table_info(users)", (err, columns) => {
                if (err) {
                    reject(err);
                    return;
                }

                const hasUserGroup = columns.some(col => col.name === 'user_group');

                if (hasUserGroup) {
                    console.log('✓ user_group column already exists — skipping migration');
                    resolve();
                    return;
                }

                console.log('Adding user_group column to users table...');

                // SQLite doesn't support ADD COLUMN with CHECK inline in all versions,
                // so we add the column first, map values, then recreate with constraint.
                // However, SQLite also doesn't support ALTER TABLE ADD CONSTRAINT.
                // Strategy: add column, map values, create index.
                // The CHECK constraint is enforced via table rebuild.

                db.run(
                    `ALTER TABLE users ADD COLUMN user_group VARCHAR(20) NOT NULL DEFAULT 'Read_Only'`,
                    (err) => {
                        if (err) {
                            reject(err);
                            return;
                        }
                        console.log('✓ Added user_group column');

                        // Map existing roles to groups
                        db.run(
                            `UPDATE users SET user_group = 'Admin' WHERE role = 'admin'`,
                            function(err) {
                                if (err) { reject(err); return; }
                                console.log(`  ✓ Mapped ${this.changes} admin(s) → Admin`);

                                db.run(
                                    `UPDATE users SET user_group = 'Standard_User' WHERE role = 'editor'`,
                                    function(err) {
                                        if (err) { reject(err); return; }
                                        console.log(`  ✓ Mapped ${this.changes} editor(s) → Standard_User`);

                                        db.run(
                                            `UPDATE users SET user_group = 'Read_Only' WHERE role = 'viewer'`,
                                            function(err) {
                                                if (err) { reject(err); return; }
                                                console.log(`  ✓ Mapped ${this.changes} viewer(s) → Read_Only`);

                                                // Map NULL or unrecognized roles to Read_Only
                                                db.run(
                                                    `UPDATE users SET user_group = 'Read_Only' WHERE user_group = 'Read_Only' AND role NOT IN ('admin', 'editor', 'viewer')`,
                                                    function(err) {
                                                        if (err) { reject(err); return; }
                                                        console.log(`  ✓ Mapped ${this.changes} unrecognized role(s) → Read_Only`);

                                                        // Create index on user_group
                                                        db.run(
                                                            `CREATE INDEX IF NOT EXISTS idx_users_user_group ON users(user_group)`,
                                                            (err) => {
                                                                if (err) { reject(err); return; }
                                                                console.log('✓ Created idx_users_user_group index');

                                                                // Add CHECK constraint via trigger (SQLite can't ALTER TABLE ADD CONSTRAINT)
                                                                db.run(
                                                                    `CREATE TRIGGER IF NOT EXISTS check_user_group_insert
                                                                     BEFORE INSERT ON users
                                                                     FOR EACH ROW
                                                                     WHEN NEW.user_group NOT IN ('Admin', 'Standard_User', 'Leadership', 'Read_Only')
                                                                     BEGIN
                                                                         SELECT RAISE(ABORT, 'Invalid user_group value. Must be Admin, Standard_User, Leadership, or Read_Only');
                                                                     END`,
                                                                    (err) => {
                                                                        if (err) { reject(err); return; }
                                                                        db.run(
                                                                            `CREATE TRIGGER IF NOT EXISTS check_user_group_update
                                                                             BEFORE UPDATE OF user_group ON users
                                                                             FOR EACH ROW
                                                                             WHEN NEW.user_group NOT IN ('Admin', 'Standard_User', 'Leadership', 'Read_Only')
                                                                             BEGIN
                                                                                 SELECT RAISE(ABORT, 'Invalid user_group value. Must be Admin, Standard_User, Leadership, or Read_Only');
                                                                             END`,
                                                                            (err) => {
                                                                                if (err) { reject(err); return; }
                                                                                console.log('✓ Created user_group validation triggers');
                                                                                console.log('Migration complete!');
                                                                                resolve();
                                                                            }
                                                                        );
                                                                    }
                                                                );
                                                            }
                                                        );
                                                    }
                                                );
                                            }
                                        );
                                    }
                                );
                            }
                        );
                    }
                );
            });
        });
    });
}

// Run directly if executed as a script
if (require.main === module) {
    const dbPath = path.join(__dirname, '..', 'cve_database.db');
    const db = new sqlite3.Database(dbPath);
    console.log('Starting add_user_groups migration...');

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

module.exports = { runMigration };