Most SaaS apps need scheduled jobs. Velocity X dashboards aggregate daily sales, send weekly performance digests, and recalculate monthly cohorts for new customers. If you're on AWS or GCP, you spin up a dedicated worker, pay for the compute, and set up monitoring. On Supabase, you don't. Postgres has a built-in cron extension (`pg_cron`), and Supabase Edge Functions are serverless HTTP endpoints. Wire them together: pg_cron fires a job on schedule, calls an HTTP endpoint, your Edge Function reads/writes the database and sends emails, done. No worker queue, no Docker containers, no polling. You pay per-request like everything else. This is how production Velocity dashboards handle recurring work: pg_cron at the DB layer, Edge Functions as the compute, Resend for emails, all for the cost of a few HTTP requests a month.
Why pg_cron Is the Right Primitive for Scheduled Jobs
Supabase abstracts Postgres. pg_cron is a Postgres extension that lets you schedule SQL commands to run at intervals — cron syntax, just like your server's crontab. Most teams reach for external services like AWS Lambda, GCP Cloud Scheduler, or Vercel Cron. But if your data lives in Postgres and your compute is already on Supabase, moving jobs outside the database creates latency and cost. pg_cron keeps everything in one place: the job runs in Postgres, can read/write tables directly, and can call external APIs (via HTTP functions in Postgres or by triggering a webhook). Edge Functions handle the webhook side. The pattern: pg_cron schedules a SQL command that calls an HTTP endpoint. That endpoint is a Supabase Edge Function. The Edge Function runs TypeScript/JavaScript, reads/writes your database using the Supabase client, and returns a response. If something fails, pg_cron retries. If you need observability, you log to a table and query it. This is simple, transparent, and scales.
Enable pg_cron in Your Supabase Project
pg_cron is disabled by default. Go to your Supabase dashboard, SQL Editor, and run:
-- Enable pg_cron extension
create extension if not exists pg_cron with schema extensions;
-- Grant usage to your authenticated role
grant usage on schema extensions to authenticated;
grant all privileges on all tables in schema extensions to authenticated;That's it. pg_cron is now available. You can schedule jobs immediately.
Three Real Velocity X Jobs: Daily Snapshot, Weekly Digest, Monthly Cohort
Here are three production jobs from Velocity X dashboards, each using pg_cron + an Edge Function.
Job 1: Daily Snapshot — Aggregate Yesterday's Data
Every morning at 6am UTC, compute daily revenue, transactions, and average order value from yesterday. Store the snapshot in a `daily_metrics` table for dashboard charting.
-- Create the metrics table
create table daily_metrics (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
snapshot_date date not null,
total_revenue numeric,
transaction_count int,
average_order_value numeric,
created_at timestamp default now()
);
-- Schedule the job: every day at 6am UTC
select cron.schedule(
'daily-snapshot',
'0 6 * * *',
'select
net.http_post(
''https://' || current_setting('app.edge_functions_url') || '/daily-snapshot'',
jsonb_build_object(''org_ids'', array_agg(id))
)
from organizations'
);The Edge Function receives the list of org IDs, loops through each, calculates aggregates, and writes to `daily_metrics`:
// supabase/functions/daily-snapshot/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0';
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);
Deno.serve(async (req) => {
const { org_ids } = await req.json();
for (const orgId of org_ids) {
// Calculate yesterday's totals
const { data: transactions } = await supabase
.from('transactions')
.select('amount, created_at')
.eq('organization_id', orgId)
.gte('created_at', new Date(Date.now() - 86400000).toISOString())
.lt('created_at', new Date().toISOString());
const totalRevenue = transactions?.reduce((sum, t) => sum + t.amount, 0) || 0;
const avgOrderValue = transactions?.length ? totalRevenue / transactions.length : 0;
// Write snapshot
await supabase
.from('daily_metrics')
.insert({
organization_id: orgId,
snapshot_date: new Date(Date.now() - 86400000).toISOString().split('T')[0],
total_revenue: totalRevenue,
transaction_count: transactions?.length || 0,
average_order_value: avgOrderValue
});
}
return new Response(
JSON.stringify({ success: true, count: org_ids.length }),
{ status: 200, headers: { 'Content-Type': 'application/json' } }
);
});
Job 2: Weekly Digest — Email Performance Summary
Every Monday at 9am, send each org owner a summary of last week's performance. Use Resend for emails.
-- Schedule weekly digest: Monday at 9am UTC
select cron.schedule(
'weekly-digest',
'0 9 * * 1',
'select
net.http_post(
''https://' || current_setting('app.edge_functions_url') || '/weekly-digest'',
jsonb_build_object(''trigger'', ''cron'')
)'
);The Edge Function queries the last 7 days of metrics and emails each org owner:
// supabase/functions/weekly-digest/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0';
import { Resend } from 'https://esm.sh/resend@3.0.0';
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);
const resend = new Resend(Deno.env.get('RESEND_API_KEY'));
Deno.serve(async (req) => {
// Get all organizations
const { data: orgs } = await supabase
.from('organizations')
.select('id, name, owner_id');
for (const org of orgs || []) {
// Fetch last 7 days of metrics
const weekAgo = new Date(Date.now() - 7 * 86400000).toISOString();
const { data: metrics } = await supabase
.from('daily_metrics')
.select('total_revenue, transaction_count')
.eq('organization_id', org.id)
.gte('snapshot_date', weekAgo);
const weeklyRevenue = metrics?.reduce((sum, m) => sum + m.total_revenue, 0) || 0;
const weeklyTransactions = metrics?.reduce((sum, m) => sum + m.transaction_count, 0) || 0;
// Get owner email
const { data: owner } = await supabase
.from('users')
.select('email')
.eq('id', org.owner_id)
.single();
// Send email via Resend
await resend.emails.send({
from: 'noreply@aidxn.com',
to: owner.email,
subject: `Weekly digest: ${org.name}`,
html: `
Weekly Summary
Revenue: $${weeklyRevenue.toFixed(2)}
Transactions: ${weeklyTransactions}
`
});
}
return new Response(
JSON.stringify({ success: true, sent: orgs?.length || 0 }),
{ status: 200 }
);
});
Job 3: Monthly Cohort Recalc — Segment Users by Signup Month
First day of each month, recompute which signup cohort each user belongs to. Useful for retention dashboards.
-- Schedule monthly recalc: 1st of month at 12:01am UTC
select cron.schedule(
'monthly-cohort-recalc',
'1 0 1 * *',
'select
net.http_post(
''https://' || current_setting('app.edge_functions_url') || '/monthly-cohort-recalc'',
jsonb_build_object(''month'', now()::date)
)'
);The Edge Function updates a materialized view or a `user_cohorts` table:
// supabase/functions/monthly-cohort-recalc/index.ts
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0';
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);
Deno.serve(async (req) => {
const { month } = await req.json();
// Refresh materialized view or update cohort assignments
await supabase.rpc('refresh_user_cohorts', { target_month: month });
return new Response(
JSON.stringify({ success: true, month }),
{ status: 200 }
);
});
Edge Function Template: Read, Compute, Write, Return
Every scheduled job follows this shape:
// Standard template for any scheduled job
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0';
const supabase = createClient(
Deno.env.get('SUPABASE_URL')!,
Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')! // service role: bypasses RLS
);
Deno.serve(async (req) => {
try {
// 1. Parse request
const payload = await req.json();
// 2. Read from database
const { data, error } = await supabase
.from('table_name')
.select('*')
.eq('condition', value);
if (error) throw error;
// 3. Compute
const result = data.map(row => ({
...row,
computed_field: row.value * 2
}));
// 4. Write back
const { error: insertError } = await supabase
.from('result_table')
.insert(result);
if (insertError) throw insertError;
// 5. Return success
return new Response(
JSON.stringify({ success: true, processed: result.length }),
{ status: 200, headers: { 'Content-Type': 'application/json' } }
);
} catch (error) {
console.error('Job failed:', error);
return new Response(
JSON.stringify({ success: false, error: error.message }),
{ status: 500, headers: { 'Content-Type': 'application/json' } }
);
}
});
Key points: Use `SUPABASE_SERVICE_ROLE_KEY`, which bypasses RLS so the function can read/write across orgs. Log errors to console — Supabase captures them. Return JSON with success/failure status. If the function throws, pg_cron will retry (default: 3 times).
Monitoring: Logs, Retries, and Dead-Letter Handling
pg_cron doesn't have a built-in dashboard. You need to monitor job success yourself.
Log All Job Runs to a Table
-- Create a job_runs table
create table job_runs (
id uuid primary key default gen_random_uuid(),
job_name text not null,
started_at timestamp default now(),
completed_at timestamp,
status text, -- 'pending', 'success', 'failed'
result jsonb,
error text
);
-- At the end of each Edge Function, write a log entry
await supabase
.from('job_runs')
.insert({
job_name: 'daily-snapshot',
status: 'success',
result: { processed: result.length }
});
Query the table to see which jobs succeeded and which failed. Set up a weekly alert if `status = 'failed'` in the last 7 days.
pg_cron Retries
By default, pg_cron retries failed jobs 3 times. If a job keeps failing, pg_cron disables it. Check the `cron.job_run_details` table to see retry history:
-- See all job runs and their status
select jobid, jobname, start_time, end_time, succeeded
from cron.job_run_details
order by start_time desc
limit 20;
Dead-Letter Handling
If a job fails 3 times, pg_cron gives up. Create an alerting mechanism: a separate Edge Function that checks for disabled jobs and notifies Slack or emails you.
-- Check for disabled jobs
select jobid, jobname
from cron.job
where active = false;
-- If any exist, send an alert:
if (disabledJobs.length > 0) {
await resend.emails.send({
from: 'alerts@aidxn.com',
to: 'aiden@rebuildrelief.com.au',
subject: 'Scheduled jobs have failed',
html: `Failed jobs: ${disabledJobs.map(j => j.jobname).join(', ')}`
});
}
Deploying Edge Functions with pg_cron
Edge Functions deploy to Supabase automatically when you push to your repo (if you've set up CI/CD). Locally, test with:
-- Start Supabase locally
supabase start
-- In another terminal, serve the function
supabase functions serve
-- Test the function
curl -X POST http://localhost:54321/functions/v1/daily-snapshot \
-H "Content-Type: application/json" \
-d '{"org_ids": ["uuid"]}'
Once deployed, you can schedule the job immediately. pg_cron is already running in production.
Six FAQs
Can I schedule a job more than once per day?
Yes. Cron syntax supports minute-level precision: `*/5 * * * *` runs every 5 minutes. But be careful — running a heavy job every minute can rack up HTTP requests and database load. Monitor usage and scale accordingly.
What if the Edge Function times out?
Edge Functions have a 30-second timeout. If your job takes longer, split it into smaller tasks (process 100 rows at a time, not 1 million) or use a stored procedure in Postgres that does the heavy lifting and have the Edge Function just call it.
How do I test a cron job before scheduling it?
Call the Edge Function HTTP endpoint manually from your app or via curl. Once it works, schedule it with pg_cron. You don't need to test cron syntax — it's the same as Unix crontab.
Can I have a job call multiple Edge Functions?
Yes. One pg_cron job can call multiple HTTP endpoints in sequence using Postgres' `net.http_post`. Or have one main Edge Function call other functions internally. Both work.
What if I need to pass context (like which org to process) to the Edge Function?
The pg_cron request is JSON, so include any data in the payload. In the daily snapshot example, we passed `org_ids`. The Edge Function receives it and uses it. Simple and flexible.
How much does pg_cron cost?
Nothing. It's a Postgres extension. You pay for the Edge Function HTTP requests and database reads/writes like normal Supabase billing. For a daily job, that's ~30 requests a month — negligible.
The Bottom Line
Scheduled jobs are a core requirement of SaaS. Instead of spinning up a worker queue, pg_cron lets you schedule them directly in Postgres and trigger Edge Functions on demand. Velocity X uses this pattern for daily snapshots, weekly digests, and monthly cohort recalculations — all without a dedicated worker. Set up one pg_cron job, write one Edge Function, monitor logs to a table, and you're done. The architecture is transparent, costs next to nothing, and scales from one dashboard to thousands. Ready to build SaaS with scheduled jobs that just work? Check Aidxn Design pricing for backend partnerships. For more on database-driven security, see Supabase Row-Level Security.