Supabase Cron: Schedule Database Jobs with pg_cron & Edge Functions

What Is pg_cron?

pg_cron is a PostgreSQL extension that runs scheduled jobs directly inside the database engine. Think of it as crontab for PostgreSQL. You define jobs using standard cron expressions and SQL commands, and pg_cron executes them at the specified times.

Because jobs run inside PostgreSQL itself, there is no network overhead and no authentication required. The jobs have full access to all database functions, tables, and extensions. This makes pg_cron ideal for database maintenance tasks like cleaning up old data, refreshing materialized views, running VACUUM, and updating aggregation tables.

Setting Up pg_cron in Supabase

pg_cron is pre-installed on all Supabase projects. You just need to enable it and start creating jobs.

Enable the Extension

Go to your Supabase dashboard, navigate to Database → Extensions, search for pg_cron, and toggle it on. Alternatively, run this SQL:

-- Enable pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Grant usage to the postgres role (usually already done on Supabase)
GRANT USAGE ON SCHEMA cron TO postgres;

Create Your First Job

-- Delete expired sessions every hour
SELECT cron.schedule(
  'cleanup-expired-sessions',   -- job name
  '0 * * * *',                  -- every hour
  $$DELETE FROM auth.sessions WHERE expires_at < NOW()$$
);

-- Verify the job was created
SELECT * FROM cron.job;

Manage Jobs

-- List all scheduled jobs
SELECT jobid, schedule, command, nodename FROM cron.job;

-- Unschedule a job by name
SELECT cron.unschedule('cleanup-expired-sessions');

-- Check recent execution history
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

SQL Examples: Common Scheduled Tasks

Delete Old Data

-- Delete logs older than 30 days, every day at 3 AM UTC
SELECT cron.schedule(
  'cleanup-old-logs',
  '0 3 * * *',
  $$DELETE FROM app_logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);

Refresh Materialized Views

-- Refresh analytics view every 15 minutes
SELECT cron.schedule(
  'refresh-analytics',
  '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY analytics_summary$$
);

Run VACUUM

-- VACUUM ANALYZE on large tables weekly on Sunday at 4 AM UTC
SELECT cron.schedule(
  'weekly-vacuum',
  '0 4 * * 0',
  $$VACUUM ANALYZE public.events$$
);

Update Aggregation Tables

-- Update daily stats every hour
SELECT cron.schedule(
  'update-daily-stats',
  '0 * * * *',
  $$
  INSERT INTO daily_stats (date, total_orders, total_revenue)
  SELECT
    CURRENT_DATE,
    COUNT(*),
    SUM(amount)
  FROM orders
  WHERE created_at >= CURRENT_DATE
  ON CONFLICT (date) DO UPDATE SET
    total_orders = EXCLUDED.total_orders,
    total_revenue = EXCLUDED.total_revenue
  $$
);

Call an Edge Function via pg_net

-- Trigger an Edge Function every day at 8 AM UTC
-- Requires pg_net extension to be enabled
SELECT cron.schedule(
  'trigger-daily-email',
  '0 8 * * *',
  $$
  SELECT net.http_post(
    url := 'https://your-project.supabase.co/functions/v1/send-daily-email',
    headers := '{"Authorization": "Bearer your-anon-key", "Content-Type": "application/json"}'::jsonb,
    body := '{"type": "daily-digest"}'::jsonb
  )
  $$
);

Edge Functions with Cron

Supabase Edge Functions are serverless functions written in TypeScript/Deno that run on Supabase's edge infrastructure. They are triggered via HTTP, which means you can schedule them in two ways:

Option A: pg_cron + pg_net (Internal)

Use pg_cron to trigger an HTTP POST to your Edge Function via the pg_net extension (as shown in the SQL example above). This keeps everything within Supabase but has limited visibility into execution results.

Option B: External Scheduler (CronJobPro)

Use CronJobPro to call your Edge Function endpoint directly via HTTP. This gives you:

• •Full execution history with response status codes
• •Duration tracking to detect slow Edge Functions
• •Automatic alerts on failure (email, Slack, webhook)
• •Timezone-aware scheduling with DST handling
• •Visual cron generator for building schedules

Example Edge Function

// supabase/functions/daily-report/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

serve(async (req) => {
  // Verify authorization
  const authHeader = req.headers.get('Authorization');
  if (!authHeader) {
    return new Response('Unauthorized', { status: 401 });
  }

  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  );

  // Generate report
  const { data, error } = await supabase
    .from('orders')
    .select('amount')
    .gte('created_at', new Date(Date.now() - 86400000).toISOString());

  if (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }

  const total = data.reduce((sum, order) => sum + order.amount, 0);

  // Send the report (e.g., via email API)
  await fetch('https://api.example.com/send-report', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ total_revenue: total, date: new Date().toISOString() }),
  });

  return new Response(JSON.stringify({ success: true, total }), {
    headers: { 'Content-Type': 'application/json' },
  });
});

Limitations

• •Database-level only: pg_cron can only execute SQL statements. It cannot directly call HTTP endpoints, send emails, or interact with external services without the pg_net extension.
• •No built-in monitoring: While cron.job_run_details logs execution results, there is no dashboard, no alerting, and no trend analysis. You must query the table manually or build custom monitoring.
• •UTC only: pg_cron runs all schedules in UTC. There is no timezone configuration. You must convert local times to UTC manually.
• •No retry on failure: If a job fails, pg_cron logs the error and moves on. There is no automatic retry mechanism. The job runs again at its next scheduled time.
• •Limited cron syntax: pg_cron supports standard five-field cron expressions but does not support all extended syntax features like L (last), W (weekday), or # (nth occurrence).
• •Shared database resources: Heavy cron jobs run inside your database and compete with application queries for CPU and memory. Long-running VACUUM or bulk DELETE operations can impact app performance.

CronJobPro for Supabase

The best approach for most Supabase projects is to combine pg_cron for database-internal tasks with CronJobPro for everything that needs HTTP triggering and monitoring:

This split gives you the best of both worlds: zero-overhead database jobs via pg_cron, and reliable, monitored HTTP scheduling via CronJobPro for everything that touches the outside world.

Frequently Asked Questions