meteor_detection_system/meteor-web-backend/migrations/1766300000002_add-missing-indexes.js

/**
 * Migration: Add missing indexes for frequently queried columns
 *
 * Fixes: Missing indexes on frequently filtered/sorted columns
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param pgm {import('node-pg-migrate').MigrationBuilder}
 * @param run {() => void | undefined}
 * @returns {Promise<void> | void}
 */
export const up = (pgm) => {
  console.log('Adding missing indexes...');

  // validated_events.media_url - frequently queried for display
  pgm.createIndex('validated_events', 'media_url', {
    name: 'idx_validated_events_media_url',
    method: 'hash', // Hash index for equality lookups
  });

  // payment_records.status - frequently filtered for payment status
  pgm.createIndex('payment_records', 'status', {
    name: 'idx_payment_records_status',
  });

  // subscription_history.action - frequently filtered by action type
  pgm.createIndex('subscription_history', 'action', {
    name: 'idx_subscription_history_action',
  });

  // raw_events.file_type - frequently filtered for file type statistics
  pgm.createIndex('raw_events', 'file_type', {
    name: 'idx_raw_events_file_type',
  });

  // devices.firmware_version - for device management queries
  pgm.createIndex('devices', 'firmware_version', {
    name: 'idx_devices_firmware_version',
    where: 'firmware_version IS NOT NULL',
  });

  // device_certificates.expires_at - for certificate renewal queries
  pgm.createIndex('device_certificates', 'expires_at', {
    name: 'idx_device_certificates_expires_at',
  });

  // user_subscriptions.current_period_end - for subscription renewal queries
  pgm.createIndex('user_subscriptions', 'current_period_end', {
    name: 'idx_user_subscriptions_period_end',
  });

  // Composite index for common subscription queries
  pgm.createIndex('user_subscriptions', ['status', 'current_period_end'], {
    name: 'idx_user_subscriptions_status_period',
  });

  // weather_observations.observation_time - for time-based queries
  pgm.createIndex('weather_observations', 'observation_time', {
    name: 'idx_weather_observations_time',
  });

  console.log('Missing indexes added successfully.');
};

/**
 * @param pgm {import('node-pg-migrate').MigrationBuilder}
 * @param run {() => void | undefined}
 * @returns {Promise<void> | void}
 */
export const down = (pgm) => {
  console.log('Removing added indexes...');

  pgm.dropIndex('validated_events', 'media_url', {
    name: 'idx_validated_events_media_url',
    ifExists: true,
  });

  pgm.dropIndex('payment_records', 'status', {
    name: 'idx_payment_records_status',
    ifExists: true,
  });

  pgm.dropIndex('subscription_history', 'action', {
    name: 'idx_subscription_history_action',
    ifExists: true,
  });

  pgm.dropIndex('raw_events', 'file_type', {
    name: 'idx_raw_events_file_type',
    ifExists: true,
  });

  pgm.dropIndex('devices', 'firmware_version', {
    name: 'idx_devices_firmware_version',
    ifExists: true,
  });

  pgm.dropIndex('device_certificates', 'expires_at', {
    name: 'idx_device_certificates_expires_at',
    ifExists: true,
  });

  pgm.dropIndex('user_subscriptions', 'current_period_end', {
    name: 'idx_user_subscriptions_period_end',
    ifExists: true,
  });

  pgm.dropIndex('user_subscriptions', ['status', 'current_period_end'], {
    name: 'idx_user_subscriptions_status_period',
    ifExists: true,
  });

  pgm.dropIndex('weather_observations', 'observation_time', {
    name: 'idx_weather_observations_time',
    ifExists: true,
  });

  console.log('Indexes removed.');
};