Monitoring and incidents

SELECT actor_id, action, resource_type, resource_id, detail, created_at
FROM control.audit_log
WHERE tenant_id = :tenant_id
ORDER BY created_at DESC
LIMIT 100;

-- Current backlog by type and status (the same numbers /metrics reports, but filterable)
SELECT job_type, status, count(*)
FROM control.platform_jobs
GROUP BY job_type, status
ORDER BY job_type, status;

-- Jobs claimed but not making progress -- candidates for a stuck worker
SELECT id, tenant_id, job_type, locked_by, locked_at, attempts, max_attempts
FROM control.platform_jobs
WHERE status = 'running'
  AND locked_at < now() - interval '5 minutes'
ORDER BY locked_at;

-- Jobs approaching their retry ceiling -- likely to fail terminally soon
SELECT id, tenant_id, job_type, attempts, max_attempts, error_detail, available_at
FROM control.platform_jobs
WHERE status = 'queued'
  AND attempts >= max_attempts - 1
ORDER BY available_at;

-- Failed jobs in the last hour, grouped by cause
SELECT job_type, error_detail, count(*)
FROM control.platform_jobs
WHERE status = 'failed'
  AND finished_at > now() - interval '1 hour'
GROUP BY job_type, error_detail
ORDER BY count(*) DESC;