Skip to main content

​
Overview

SecurAtlas uses the pg_cron extension to schedule recurring database jobs. These jobs run inside PostgreSQL and can call SQL functions directly or invoke Edge Functions via pg_net.
All cron times are in UTC. The Supabase dashboard shows job history under Database > Extensions > pg_cron.

​
Active Jobs

Job NameScheduleTime (UTC)Purpose
nightly_risk_recompute0 2 * * *2:00 AMRecompute risk scores for all tenants
securatlas_nightly_integration_sync0 3 * * *3:00 AMTrigger integration syncs for all connections
token_refresh_cron0 */6 * * *Every 6 hoursRefresh expiring OAuth tokens

​
Job Definitions

​
Nightly Risk Recompute

Runs at 2 AM UTC. Recalculates risk scores and inserts snapshots for all active tenants. 
SELECT cron.schedule(
  'nightly_risk_recompute',
  '0 2 * * *',
  $$SELECT fn_recompute_all_tenant_risk_scores()$$
);
The fn_recompute_all_tenant_risk_scores() function iterates over all tenants with active subscriptions, computes the composite risk score, and inserts a row in tenant_risk_snapshots.

​
Nightly Integration Sync

Runs at 3 AM UTC (after risk recompute). Triggers integration syncs for all active connections. 
SELECT cron.schedule(
  'securatlas_nightly_integration_sync',
  '0 3 * * *',
  $$SELECT fn_trigger_nightly_sync()$$
);

​
fn_trigger_nightly_sync()

This function uses pg_net to call the trigger-sync Edge Function for each active connection: 
CREATE OR REPLACE FUNCTION fn_trigger_nightly_sync()
RETURNS void AS $$
DECLARE
  conn RECORD;
  edge_url text;
BEGIN
  edge_url := current_setting('app.settings.edge_function_url', true)
    || '/trigger-sync';

  FOR conn IN
    SELECT id FROM integration_connections
    WHERE status = 'active'
  LOOP
    PERFORM net.http_post(
      url     := edge_url,
      headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key', true)
      ),
      body    := jsonb_build_object('connection_id', conn.id)
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
The pg_net extension sends HTTP requests asynchronously. The function returns immediately; sync completion is tracked in integration_sync_jobs.

​
Token Refresh

Runs every 6 hours. Refreshes OAuth tokens that are near expiry to prevent sync failures. 
SELECT cron.schedule(
  'token_refresh_cron',
  '0 */6 * * *',
  $$SELECT fn_refresh_expiring_tokens()$$
);

​
Optional: 6-Hour Sync

For tenants that need more frequent integration syncs, an optional 6-hour sync can be enabled: 
SELECT cron.schedule(
  'frequent_integration_sync',
  '0 */6 * * *',
  $$SELECT fn_trigger_nightly_sync()$$
);
This uses the same fn_trigger_nightly_sync() function but runs more frequently. Enable this only for tenants on premium plans to manage API rate limits.

​
Managing Jobs

​
List All Jobs

SELECT jobid, jobname, schedule, command, active
FROM cron.job
ORDER BY jobname;

​
Disable a Job

SELECT cron.unschedule('securatlas_nightly_integration_sync');

​
Re-enable a Job

Re-run the cron.schedule() call with the same job name. If the name already exists, it updates the existing schedule.

​
Run a Job Manually

Execute the underlying function directly: 
-- Manually trigger risk recompute
SELECT fn_recompute_all_tenant_risk_scores();

-- Manually trigger integration sync
SELECT fn_trigger_nightly_sync();

​
View Job History

SELECT jobid, jobname, start_time, end_time, status, return_message
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

​
Execution Order

Jobs are scheduled to avoid conflicts: 
2:00 AM  ──  Risk Recompute (uses yesterday's data)
3:00 AM  ──  Integration Sync (pulls fresh data)
Risk recompute runs before the nightly sync intentionally. The sync at 3 AM pulls fresh data, and the next night’s risk recompute at 2 AM will incorporate that data. This avoids recomputing risk on stale data.