欠落した Cron スケジュールの検出

WITH ordered_runs AS (
  SELECT
    schedule_name,
    environment,
    timestamp_utc AS run_at,
    expected_interval_minutes,
    LAG(timestamp_utc) OVER (
      PARTITION BY schedule_name, environment
      ORDER BY timestamp_utc
    ) AS previous_run_at
  FROM cron_runs
  WHERE timestamp_utc >= now() - INTERVAL '7 days'
)
SELECT
  schedule_name,
  environment,
  previous_run_at,
  run_at,
  expected_interval_minutes,
  date_part('second', run_at - previous_run_at) / 60.0 AS observed_gap_minutes
FROM ordered_runs
WHERE previous_run_at IS NOT NULL
  AND date_part('second', run_at - previous_run_at) / 60.0
    > expected_interval_minutes * 1.5
ORDER BY observed_gap_minutes DESC;