50 lines
1.8 KiB
JavaScript
50 lines
1.8 KiB
JavaScript
|
// Migration: Create queue_remediation_notes table
|
||
|
|
// Stores remediation notes for Ivanti todo queue items (append-only).
|
||
|
|
// FK cascade ensures notes are deleted when the parent queue item is removed.
|
||
|
|
// Idempotent — safe to re-run multiple times.
|
||
|
|
const pool = require('../db');
|
||
|
|
|
||
|
|
async function run() {
|
||
|
|
console.log('Starting queue_remediation_notes migration...');
|
||
|
|
|
||
|
|
// Verify prerequisite table exists
|
||
|
|
const { rows: queueTable } = await pool.query(`
|
||
|
|
SELECT 1 FROM information_schema.tables
|
||
|
|
WHERE table_schema = 'public' AND table_name = 'ivanti_todo_queue'
|
||
|
|
`);
|
||
|
|
if (queueTable.length === 0) {
|
||
|
|
console.error('✗ ivanti_todo_queue table does not exist. Cannot proceed.');
|
||
|
|
process.exit(1);
|
||
|
|
}
|
||
|
|
console.log('✓ ivanti_todo_queue table exists');
|
||
|
|
|
||
|
|
// Create queue_remediation_notes table
|
||
|
|
await pool.query(`
|
||
|
|
CREATE TABLE IF NOT EXISTS queue_remediation_notes (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
queue_item_id INTEGER NOT NULL REFERENCES ivanti_todo_queue(id) ON DELETE CASCADE,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
username VARCHAR(100) NOT NULL,
|
||
|
|
note_text TEXT NOT NULL,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
CONSTRAINT chk_note_text_length CHECK (char_length(note_text) <= 5000)
|
||
|
|
)
|
||
|
|
`);
|
||
|
|
console.log('✓ queue_remediation_notes table created (or already exists)');
|
||
|
|
|
||
|
|
// Create index on queue_item_id for efficient lookup
|
||
|
|
await pool.query(`
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_remediation_notes_queue_item
|
||
|
|
ON queue_remediation_notes(queue_item_id)
|
||
|
|
`);
|
||
|
|
console.log('✓ queue_item_id index created (or already exists)');
|
||
|
|
|
||
|
|
console.log('Migration complete.');
|
||
|
|
process.exit(0);
|
||
|
|
}
|
||
|
|
|
||
|
|
run().catch(err => {
|
||
|
|
console.error('Migration failed:', err.message);
|
||
|
|
process.exit(1);
|
||
|
|
});
|