1  /**
  2   * Cron Lock Management Utility
  3   *
  4   * Replaces config-table-based locking with dedicated cron_locks table.
  5   * Provides functions for acquiring, releasing, and checking lock status.
  6   *
  7   * Lock lifecycle:
  8   *  1. acquireLock() - Attempt to acquire lock (returns true/false)
  9   *  2. Lock held during cron job execution
 10   *  3. releaseLock() - Release lock when job completes (success or failure)
 11   *  4. Stale lock detection - Automatically clears locks older than threshold
 12   *
 13   * Usage:
 14   *   import { acquireLock, releaseLock, checkAndClearStaleLock } from './utils/cron-locks.js';
 15   *
 16   *   const lockKey = 'cron_scoring_running';
 17   *   if (!await checkAndClearStaleLock(lockKey, 'Scoring')) return;
 18   *   if (!await acquireLock(lockKey, 'Scoring stage')) return;
 19   *   try {
 20   *     // Run cron job
 21   *   } finally {
 22   *     await releaseLock(lockKey);
 23   *   }
 24   */
 25  
 26  import { run, getOne } from './db.js';
 27  
 28  /**
 29   * Acquire a lock for a cron job
 30   * @param {string} lockKey - Unique lock identifier
 31   * @param {string|null} description - Optional description of the lock
 32   * @returns {Promise<boolean>} True if lock acquired, false if already held
 33   */
 34  export async function acquireLock(lockKey, description = null) {
 35    try {
 36      await run('INSERT INTO ops.cron_locks (lock_key, description) VALUES ($1, $2)', [
 37        lockKey,
 38        description,
 39      ]);
 40      return true;
 41    } catch (err) {
 42      // Lock already exists (UNIQUE constraint violation)
 43      if (err.code === '23505') return false;
 44      throw err;
 45    }
 46  }
 47  
 48  /**
 49   * Release a lock for a cron job
 50   * @param {string} lockKey - Unique lock identifier
 51   * @returns {Promise<void>}
 52   */
 53  export async function releaseLock(lockKey) {
 54    await run('DELETE FROM ops.cron_locks WHERE lock_key = $1', [lockKey]);
 55  }
 56  
 57  /**
 58   * Check if a lock is stale (older than threshold)
 59   * @param {string} lockKey - Unique lock identifier
 60   * @param {number} staleThresholdMs - Threshold in milliseconds (default: 10 minutes)
 61   * @returns {Promise<string|null>} 'stale', 'active', or null if no lock exists
 62   */
 63  export async function checkStaleLock(lockKey, staleThresholdMs = 10 * 60 * 1000) {
 64    const lock = await getOne('SELECT updated_at FROM ops.cron_locks WHERE lock_key = $1', [lockKey]);
 65  
 66    if (!lock) return null;
 67  
 68    const lockAge = Date.now() - new Date(lock.updated_at).getTime();
 69    return lockAge > staleThresholdMs ? 'stale' : 'active';
 70  }
 71  
 72  /**
 73   * Clear a stale lock
 74   * @param {string} lockKey - Unique lock identifier
 75   * @returns {Promise<void>}
 76   */
 77  export async function clearStaleLock(lockKey) {
 78    await run('DELETE FROM ops.cron_locks WHERE lock_key = $1', [lockKey]);
 79  }
 80  
 81  /**
 82   * Check for stale lock and clear if needed
 83   * @param {string} lockKey - Unique lock identifier
 84   * @param {string} stageName - Name of the stage (for logging)
 85   * @param {number} staleThresholdMs - Threshold in milliseconds (default: 10 minutes)
 86   * @returns {Promise<boolean>} True if can proceed, false if lock is active
 87   */
 88  export async function checkAndClearStaleLock(lockKey, stageName, staleThresholdMs = 10 * 60 * 1000) {
 89    const lockStatus = await checkStaleLock(lockKey, staleThresholdMs);
 90  
 91    if (lockStatus === 'active') {
 92      // Lock is active, cannot proceed
 93      return false;
 94    }
 95  
 96    if (lockStatus === 'stale') {
 97      // Lock is stale, clear it and allow proceeding
 98      const lock = await getOne('SELECT updated_at FROM ops.cron_locks WHERE lock_key = $1', [lockKey]);
 99      const lockAge = Date.now() - new Date(lock.updated_at).getTime();
100      const ageMinutes = (lockAge / 1000 / 60).toFixed(1);
101      console.log(
102        `⚠️  Stale lock detected for ${stageName} (${ageMinutes} min old), clearing and proceeding`
103      );
104      await clearStaleLock(lockKey);
105    }
106  
107    // No lock or stale lock cleared, can proceed
108    return true;
109  }