Durable Execution Worker — Runbook

-- Unschedule the cron job
SELECT cron.unschedule('process-workflow-queue');

-- Verify it's removed
SELECT * FROM cron.job WHERE jobname = 'process-workflow-queue';

SELECT cron.schedule(
  'process-workflow-queue',
  '* * * * *',
  $$SELECT util.invoke_edge_function('workflow-executor-worker', '{}'::jsonb);$$
);

-- Check which orgs are stuck
SELECT organization_id, worker_running, worker_last_run_at
FROM fw_module_settings
WHERE worker_running = true;

-- Reset for a specific org
UPDATE fw_module_settings
SET worker_running = false
WHERE organization_id = '<org_id>'
  AND worker_running = true;

-- Reset all stuck semaphores (use with caution)
UPDATE fw_module_settings
SET worker_running = false
WHERE worker_running = true
  AND worker_last_run_at < now() - interval '5 minutes';

-- Total pending messages
SELECT count(*) FROM pgmq.q_workflow_execution_queue WHERE vt <= now();

-- Messages by org
SELECT message->>'organization_id' AS org_id, count(*)
FROM pgmq.q_workflow_execution_queue
WHERE vt <= now()
GROUP BY 1
ORDER BY 2 DESC;

-- Oldest unprocessed message
SELECT msg_id, enqueued_at, message->>'execution_id' AS execution_id
FROM pgmq.q_workflow_execution_queue
WHERE vt <= now()
ORDER BY enqueued_at ASC
LIMIT 1;

-- Recent failed executions
SELECT id, rule_id, organization_id, status, retry_count, last_error, 
       next_retry_at, started_at, completed_at
FROM fw_workflow_executions
WHERE status IN ('failed', 'retry_pending')
ORDER BY completed_at DESC NULLS LAST
LIMIT 20;

-- DLQ contents
SELECT msg_id, enqueued_at,
       message->>'execution_id' AS execution_id,
       message->>'error' AS error,
       message->>'total_attempts' AS attempts
FROM pgmq.q_workflow_dlq
ORDER BY enqueued_at DESC
LIMIT 20;

UPDATE fw_module_settings
SET fw_execution_worker_enabled = false, worker_running = false;

SELECT cron.unschedule('process-workflow-queue');

-- Worker last run times
SELECT organization_id, worker_last_run_at, worker_last_batch_size,
       now() - worker_last_run_at AS time_since_last_run
FROM fw_module_settings
WHERE fw_execution_worker_enabled = true
ORDER BY worker_last_run_at DESC NULLS LAST;

-- Cron job status
SELECT jobid, jobname, schedule, active
FROM cron.job
WHERE jobname = 'process-workflow-queue';