Encore OS — Documentation

Several Encore OS edge functions run on a schedule via pg_cron (+ pg_net) in Supabase. This guide covers all of them in one place: what each job does, the scheduling SQL, and how to verify, pause, and troubleshoot.

Prerequisites

Enable extensions in the Supabase Dashboard → Extensions:

pg_cron
pg_net

For the workflow and webhook jobs, ensure util.invoke_edge_function is available and app.settings.service_role_key is configured in Vault.

Run the SQL below in the Supabase SQL Editor, not as a migration — it depends on project-specific Vault settings, URLs, and keys. Replace YOUR_SERVICE_ROLE_KEY with the service role key from Settings → API. Never commit service role keys to code.

Job catalog

Job	Function	Schedule	Purpose
`process-workflow-queue`	`workflow-executor-worker`	Every minute	Process queued workflow executions (FW-46)
`dispatch-scheduled-workflows`	`process-scheduled-workflows`	Every minute	Enqueue due time-based workflow schedules (FW-26)
`fw-webhook-maintenance`	`fw-webhook-maintenance`	Daily 2 AM UTC	Webhook log retention purge + expired secret deactivation (FW-59)
`pf-detect-sla-violations`	`pf-detect-sla-violations`	Every minute	SLA violation detection (PF-36)
`pf-collect-metrics`	`pf-collect-metrics`	Every 5 minutes	Platform metric collection (PF-36)
`pf-purge-metric-values`	`pf-purge-metric-values`	Daily 3 AM UTC	Metric value purge (PF-36)
`pf-data-archival`	`process-data-retention`	Daily midnight UTC	Data retention archival (PF-46)
`pf-data-deletion`	`process-data-retention`	Daily 1 AM UTC	Data retention deletion, after archival (PF-46)

Scheduling SQL

Workflow execution (FW-46 / FW-26)
Webhook maintenance (FW-59)
SLA & metrics (PF-36)
Data retention (PF-46)

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

If second-level pg_cron scheduling is available, use */10 * * * * * (every 10 seconds) for lower latency.

The FW-26 process-scheduled-workflows function is the time-based companion to the worker. Each minute it finds active fw_workflow_schedules whose next_execution_at is due, enqueues a fw_workflow_executions row into the same workflow_execution_queue (so workflow-executor-worker runs them), and re-arms each schedule — advancing a cron schedule to its next run, or deactivating a one-time schedule that has fired. Conflicting runs honor the schedule’s conflict_resolution (skip / delay / error).Schedule it alongside the worker (run it before the worker each minute so freshly queued runs are picked up in the same tick):

-- Dispatch due workflow schedules → execution queue — every minute
SELECT cron.schedule(
  'dispatch-scheduled-workflows',
  '* * * * *',
  $$SELECT util.invoke_edge_function('process-scheduled-workflows', '{}'::jsonb);$$
);

The dispatcher is idempotent against concurrent ticks: it claims each due row with a compare-and-swap on next_execution_at before enqueuing, so an overlapping run can’t double-dispatch. An optional {"batch_size": N} body caps rows per tick (default 200).

-- Webhook maintenance: log retention purge + expired secret deactivation — daily at 2 AM UTC
SELECT cron.schedule(
  'fw-webhook-maintenance',
  '0 2 * * *',
  $$SELECT util.invoke_edge_function('fw-webhook-maintenance', '{}'::jsonb);$$
);

What it does:

Task	Schedule	Description
Log retention purge	Daily 2 AM UTC	Deletes `fw_webhook_logs` rows older than 90 days (paginated, 1000 per batch)
Expired secret deactivation	Daily 2 AM UTC	Sets `is_active = false` on `fw_webhook_secrets` where `expires_at < now()`

-- 1. SLA Violation Detection — every 1 minute
SELECT cron.schedule(
  'pf-detect-sla-violations',
  '* * * * *',
  $$
  SELECT net.http_post(
    url := 'https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/pf-detect-sla-violations',
    headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb,
    body := concat('{"time": "', now(), '"}')::jsonb
  ) AS request_id;
  $$
);

-- 2. Metric Collection — every 5 minutes
SELECT cron.schedule(
  'pf-collect-metrics',
  '*/5 * * * *',
  $$
  SELECT net.http_post(
    url := 'https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/pf-collect-metrics',
    headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb,
    body := concat('{"time": "', now(), '"}')::jsonb
  ) AS request_id;
  $$
);

-- 3. Metric Value Purge — daily at 3:00 AM UTC
SELECT cron.schedule(
  'pf-purge-metric-values',
  '0 3 * * *',
  $$
  SELECT net.http_post(
    url := 'https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/pf-purge-metric-values',
    headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb,
    body := concat('{"time": "', now(), '"}')::jsonb
  ) AS request_id;
  $$
);

-- 1. Daily Archival — midnight UTC
SELECT cron.schedule(
  'pf-data-archival',
  '0 0 * * *',
  $$
  SELECT net.http_post(
    url := 'https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/process-data-retention',
    headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb,
    body := '{"action": "archive"}'::jsonb
  ) AS request_id;
  $$
);

-- 2. Daily Deletion — 1 AM UTC (after archival)
SELECT cron.schedule(
  'pf-data-deletion',
  '0 1 * * *',
  $$
  SELECT net.http_post(
    url := 'https://<YOUR_PROJECT_REF>.supabase.co/functions/v1/process-data-retention',
    headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb,
    body := '{"action": "delete"}'::jsonb
  ) AS request_id;
  $$
);

Verify a job

-- Verify a job is registered (swap in the job name)
SELECT jobid, jobname, schedule, command
FROM cron.job
WHERE jobname = 'process-workflow-queue';

-- Check recent run history
SELECT jobid, job_name, status, return_message, start_time, end_time
FROM cron.job_run_details
WHERE job_name = 'process-workflow-queue'
ORDER BY start_time DESC
LIMIT 20;

Manage jobs

-- List all scheduled jobs
SELECT * FROM cron.job;

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

-- Re-enable: re-run the original cron.schedule(...) statement for that job

Troubleshooting

Symptom	Check
Job not running	`SELECT * FROM cron.job WHERE jobname = '<job>';` — is it registered?
Workflow job runs but worker does nothing	Check `fw_module_settings.fw_execution_worker_enabled` — is any org enabled?
Worker skips orgs	Check `fw_module_settings.worker_running` — stuck semaphore? Reset with the runbook.
Webhook logs not being purged	Check Edge Function logs; verify `fw_webhook_logs` has rows older than 90 days.
Secrets not deactivated	Verify secrets have `expires_at` set and it’s in the past.
Edge function errors	Check Edge Function logs in the Supabase Dashboard.

Getting Started

Data & Migration

Configuration Building Blocks

Templates & Recommendations

Scheduling & Automation

Scheduled Jobs (pg_cron)

Prerequisites

Job catalog

Scheduling SQL

Verify a job

Manage jobs

Troubleshooting

​Prerequisites

​Job catalog

​Scheduling SQL

​Verify a job

​Manage jobs

​Troubleshooting

​Related docs

Prerequisites

Job catalog

Scheduling SQL

Verify a job

Manage jobs

Troubleshooting

Related docs