add_ticket_features.js

Go to the documentation of this file.

// Migration to add time tracking and notes tables
const pool = require('../../backend/services/db');
 
(async function run() {
  try {
    console.log('Running migration: adding time tracking and notes tables');
    
    // Create time entries table
    await pool.query(`
      CREATE TABLE IF NOT EXISTS ticket_time_entries (
        entry_id SERIAL PRIMARY KEY,
        ticket_id INTEGER REFERENCES tickets(ticket_id) ON DELETE CASCADE,
        user_id INTEGER REFERENCES users(user_id),
        start_time TIMESTAMP NOT NULL,
        end_time TIMESTAMP,
        duration_minutes INTEGER,
        description TEXT,
        billable BOOLEAN DEFAULT true,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
 
    // Create notes table supporting both public and private notes
    await pool.query(`
      CREATE TABLE IF NOT EXISTS ticket_notes (
        note_id SERIAL PRIMARY KEY,
        ticket_id INTEGER REFERENCES tickets(ticket_id) ON DELETE CASCADE,
        user_id INTEGER REFERENCES users(user_id),
        content TEXT NOT NULL,
        is_private BOOLEAN DEFAULT false,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);
 
    // Add indices for performance
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_time_entries_ticket ON ticket_time_entries(ticket_id);
      CREATE INDEX IF NOT EXISTS idx_notes_ticket ON ticket_notes(ticket_id);
      CREATE INDEX IF NOT EXISTS idx_notes_private ON ticket_notes(is_private);
    `);
    
    console.log('Migration completed successfully');
  } catch (err) {
    console.error('Migration failed:', err);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
})();