> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Scheduled Jobs (pg_cron)

> Schedule Encore OS edge functions with pg_cron and pg_net — workflow execution, webhook maintenance, SLA detection, metrics, and data retention.

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.

<Warning>
  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.
</Warning>

## 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

<Tabs>
  <Tab title="Workflow execution (FW-46 / FW-26)">
    ```sql theme={null}
    -- Process workflow execution queue — every minute
    SELECT cron.schedule(
      'process-workflow-queue',
      '* * * * *',
      $$SELECT util.invoke_edge_function('workflow-executor-worker', '{}'::jsonb);$$
    );
    ```

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

    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):

    ```sql theme={null}
    -- 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).
  </Tab>

  <Tab title="Webhook maintenance (FW-59)">
    ```sql theme={null}
    -- 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()`   |
  </Tab>

  <Tab title="SLA & metrics (PF-36)">
    ```sql theme={null}
    -- 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;
      $$
    );
    ```
  </Tab>

  <Tab title="Data retention (PF-46)">
    ```sql theme={null}
    -- 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;
      $$
    );
    ```
  </Tab>
</Tabs>

## Verify a job

```sql theme={null}
-- 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

```sql theme={null}
-- 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.                                  |

## Related docs

* [FW-46 Admin Guide](/fw/durable-execution-worker-admin-guide)
* [FW-46 Runbook](/fw/durable-execution-worker-runbook)
* [FW-59 Integration Doc](/architecture/integrations/external-webhook-triggers-integration)
* [FW-59 Spec](https://github.com/Encore-OS/encoreos/blob/development/specs/fw/specs/FW-59-external-webhook-triggers.md)