39 lines
1.7 KiB
JavaScript
39 lines
1.7 KiB
JavaScript
|
// Migration: Add VCL reporting columns to compliance_items and create compliance_snapshots table
|
||
|
|
const pool = require('../db');
|
||
|
|
|
||
|
|
async function run() {
|
||
|
|
console.log('Starting VCL reporting migration...');
|
||
|
|
try {
|
||
|
|
await pool.query(`ALTER TABLE compliance_items ADD COLUMN IF NOT EXISTS resolution_date DATE DEFAULT NULL`);
|
||
|
|
console.log('✓ resolution_date column added (or already exists)');
|
||
|
|
|
||
|
|
await pool.query(`ALTER TABLE compliance_items ADD COLUMN IF NOT EXISTS remediation_plan TEXT DEFAULT NULL`);
|
||
|
|
console.log('✓ remediation_plan column added (or already exists)');
|
||
|
|
|
||
|
|
await pool.query(`
|
||
|
|
CREATE TABLE IF NOT EXISTS compliance_snapshots (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
snapshot_month TEXT NOT NULL,
|
||
|
|
vertical TEXT NOT NULL,
|
||
|
|
total_devices INTEGER NOT NULL DEFAULT 0,
|
||
|
|
compliant INTEGER NOT NULL DEFAULT 0,
|
||
|
|
non_compliant INTEGER NOT NULL DEFAULT 0,
|
||
|
|
compliance_pct NUMERIC(5,2) DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
UNIQUE(snapshot_month, vertical)
|
||
|
|
)
|
||
|
|
`);
|
||
|
|
console.log('✓ compliance_snapshots table created (or already exists)');
|
||
|
|
|
||
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS idx_compliance_snapshots_month ON compliance_snapshots(snapshot_month)`);
|
||
|
|
console.log('✓ idx_compliance_snapshots_month index created (or already exists)');
|
||
|
|
} catch (err) {
|
||
|
|
console.error('Migration error:', err.message);
|
||
|
|
process.exit(1);
|
||
|
|
}
|
||
|
|
console.log('Migration complete.');
|
||
|
|
process.exit(0);
|
||
|
|
}
|
||
|
|
|
||
|
|
run();
|