migrate-customer-portal.js

Go to the documentation of this file.

require('dotenv').config();
const { Pool } = require('pg');
 
// Parse DATABASE_URL from environment
const DATABASE_URL = process.env.DATABASE_URL || 'postgresql://user:password@localhost:5432/rmm_psa';
 
// Strip sslmode=require from URL to ensure pg respects explicit ssl options
const cleanedUrl = DATABASE_URL.replace(/([?&])sslmode=require(&|$)/, (m, p1, p2) => (p2 === '&' ? p1 : ''));
 
const pool = new Pool({
    connectionString: cleanedUrl,
    ssl: {
        rejectUnauthorized: false
    }
});
 
async function migrate() {
    const client = await pool.connect();
    
    try {
        console.log('Starting customer portal database migration...\n');
        
        // Start transaction
        await client.query('BEGIN');
        
        // 1. Create customer_users table
        console.log('Creating customer_users table...');
        await client.query(`
            CREATE TABLE IF NOT EXISTS customer_users (
                customer_user_id SERIAL PRIMARY KEY,
                tenant_id UUID NOT NULL,
                customer_id INTEGER NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                first_name VARCHAR(100),
                last_name VARCHAR(100),
                phone VARCHAR(50),
                is_primary BOOLEAN DEFAULT false,
                is_active BOOLEAN DEFAULT true,
                last_login TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(tenant_id, customer_id, email),
                CONSTRAINT fk_customer_users_tenant 
                    FOREIGN KEY (tenant_id) 
                    REFERENCES tenants(tenant_id) 
                    ON DELETE CASCADE,
                CONSTRAINT fk_customer_users_customer 
                    FOREIGN KEY (customer_id) 
                    REFERENCES customers(customer_id) 
                    ON DELETE CASCADE
            )
        `);
        console.log('✅ customer_users table created');
        
        // Create indexes for customer_users
        console.log('Creating indexes for customer_users...');
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_users_tenant 
            ON customer_users(tenant_id)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_users_customer 
            ON customer_users(customer_id)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_users_email 
            ON customer_users(email)
        `);
        console.log('✅ Indexes created for customer_users\n');
        
        // 2. Create customer_sessions table
        console.log('Creating customer_sessions table...');
        await client.query(`
            CREATE TABLE IF NOT EXISTS customer_sessions (
                session_id SERIAL PRIMARY KEY,
                customer_user_id INTEGER NOT NULL,
                token_hash VARCHAR(255) NOT NULL UNIQUE,
                ip_address VARCHAR(50),
                user_agent TEXT,
                expires_at TIMESTAMP NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_customer_sessions_user 
                    FOREIGN KEY (customer_user_id) 
                    REFERENCES customer_users(customer_user_id) 
                    ON DELETE CASCADE
            )
        `);
        console.log('✅ customer_sessions table created');
        
        // Create indexes for customer_sessions
        console.log('Creating indexes for customer_sessions...');
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_sessions_user 
            ON customer_sessions(customer_user_id)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_sessions_token 
            ON customer_sessions(token_hash)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_sessions_expires 
            ON customer_sessions(expires_at)
        `);
        console.log('✅ Indexes created for customer_sessions\n');
        
        // 3. Create customer_audit_log table
        console.log('Creating customer_audit_log table...');
        await client.query(`
            CREATE TABLE IF NOT EXISTS customer_audit_log (
                log_id SERIAL PRIMARY KEY,
                tenant_id UUID NOT NULL,
                customer_user_id INTEGER,
                action VARCHAR(100) NOT NULL,
                entity_type VARCHAR(50),
                entity_id INTEGER,
                details JSONB,
                ip_address VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_customer_audit_tenant 
                    FOREIGN KEY (tenant_id) 
                    REFERENCES tenants(tenant_id) 
                    ON DELETE CASCADE,
                CONSTRAINT fk_customer_audit_user 
                    FOREIGN KEY (customer_user_id) 
                    REFERENCES customer_users(customer_user_id) 
                    ON DELETE SET NULL
            )
        `);
        console.log('✅ customer_audit_log table created');
        
        // Create indexes for customer_audit_log
        console.log('Creating indexes for customer_audit_log...');
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_audit_tenant 
            ON customer_audit_log(tenant_id)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_audit_user 
            ON customer_audit_log(customer_user_id)
        `);
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_customer_audit_created 
            ON customer_audit_log(created_at)
        `);
        console.log('✅ Indexes created for customer_audit_log\n');
        
        // 4. Add columns to customers table
        console.log('Checking customers table for missing columns...');
        
        // Check if allow_portal_access exists
        const portalAccessCheck = await client.query(`
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = 'customers' 
            AND column_name = 'allow_portal_access'
        `);
        
        if (portalAccessCheck.rows.length === 0) {
            await client.query(`
                ALTER TABLE customers 
                ADD COLUMN allow_portal_access BOOLEAN DEFAULT true
            `);
            console.log('✅ Added allow_portal_access column to customers');
        } else {
            console.log('⏭️  allow_portal_access column already exists');
        }
        
        // Check if portal_notes exists
        const portalNotesCheck = await client.query(`
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = 'customers' 
            AND column_name = 'portal_notes'
        `);
        
        if (portalNotesCheck.rows.length === 0) {
            await client.query(`
                ALTER TABLE customers 
                ADD COLUMN portal_notes TEXT
            `);
            console.log('✅ Added portal_notes column to customers');
        } else {
            console.log('⏭️  portal_notes column already exists');
        }
        
        console.log();
        
        // Commit transaction
        await client.query('COMMIT');
        
        console.log('✅ Migration completed successfully!\n');
        
        // Verify tables exist
        console.log('Verifying customer portal tables...');
        const tableCheck = await client.query(`
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_name IN ('customer_users', 'customer_sessions', 'customer_audit_log')
            ORDER BY table_name
        `);
        
        console.log('\nCustomer Portal tables:');
        tableCheck.rows.forEach(row => {
            console.log(`  ✓ ${row.table_name}`);
        });
        
        // Check customer_users count
        const userCount = await client.query('SELECT COUNT(*) FROM customer_users');
        console.log(`\nCurrent customer portal users: ${userCount.rows[0].count}`);
        
    } catch (error) {
        // Rollback on error
        await client.query('ROLLBACK');
        console.error('\n❌ Migration failed:', error.message);
        console.error(error.stack);
        process.exit(1);
    } finally {
        client.release();
        await pool.end();
    }
}
 
// Run migration
migrate();