Running functions on a schedule

Edge Functions don't have a scheduler of their own. To run one on a timer, schedule a pg_cron job that sends an HTTP request to the function with pg_net. Both are already set up in every SupaHost project: don't run create extension pg_cron, it isn't needed and fails inside a project database.

The flow:

pg_cron (in your project DB) ──every N minutes──▶ net.http_post ──HTTPS──▶ /functions/v1/<name>

This page builds a nightly job for an invoice tool that emails reminders for overdue invoices.

1. Write the function so only cron can run it

Keep Require a JWT on, and inside the function accept only the service_role key. Anyone with your public anon key could otherwise trigger the job.

import { createClient } from "jsr:@supabase/supabase-js@2";

const admin = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
  { auth: { persistSession: false } },
);

// The router has already verified the JWT signature, so reading the claims is safe.
function role(req: Request): string | undefined {
  const payload = req.headers.get("Authorization")?.split(".")[1];
  if (!payload) return undefined;
  try {
    return JSON.parse(atob(payload.replace(/-/g, "+").replace(/_/g, "/"))).role;
  } catch {
    return undefined;
  }
}

Deno.serve(async (req) => {
  if (role(req) !== "service_role") {
    return Response.json({ error: "Forbidden" }, { status: 403 });
  }

  const today = new Date().toISOString().slice(0, 10);
  const { data: overdue, error } = await admin
    .from("invoices")
    .select("id, number, customer_email, total")
    .eq("status", "unpaid")
    .lt("due_date", today)
    .is("reminder_sent_at", null)
    .limit(200);

  if (error) {
    console.error(error);
    return Response.json({ error: error.message }, { status: 500 });
  }

  for (const invoice of overdue) {
    // Send the email here (see "Example: sending email"), then mark it.
    await admin.from("invoices").update({ reminder_sent_at: new Date().toISOString() }).eq("id", invoice.id);
  }

  console.log(`Sent ${overdue.length} reminders`);
  return Response.json({ reminded: overdue.length });
});

Deploy it as invoice-reminders from the Functions tab (see Deploying functions).

2. Store the key in Vault

Don't paste the service_role key into the cron job's SQL, where it would sit in plain text in cron.job. Put it in Vault once. Copy the key from Dashboard → your project → Connect tab → App (Reveal next to service_role), then run this in Studio's SQL editor:

select vault.create_secret(
  'eyJhbGciOi...your service_role key...',
  'functions_service_key',
  'Bearer token for cron jobs that call Edge Functions'
);

See Storing secrets with Vault for how Vault encrypts it.

3. Schedule the job

In Studio's SQL editor (or as a migration):

select cron.schedule(
  'invoice-reminders',
  '0 7 * * *',  -- every day at 07:00 UTC
  $$
  select net.http_post(
    url := 'https://app-one.acme.supahost.space/functions/v1/invoice-reminders',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (
        select decrypted_secret from vault.decrypted_secrets where name = 'functions_service_key'
      )
    ),
    body := jsonb_build_object('scheduled_at', now()),
    timeout_milliseconds := 30000
  );
  $$
);

You can create the same job in the dashboard: your project → Cron tab → New job, with Name invoice-reminders, a Schedule, and the select net.http_post(...) statement as the SQL.

Notes:

• Schedules run in UTC. Use standard cron syntax or '30 seconds' style intervals. See Cron syntax and examples.
• Job names use lowercase letters, numbers, - and _ (up to 49 characters).
• Jobs run as the postgres role in your project's database, which can read vault.decrypted_secrets.
• net.http_post queues the request and returns immediately; pg_net sends it in the background. Set timeout_milliseconds higher than your function normally takes, or pg_net records a timeout even if the function finishes.

4. Check that it worked

Three places tell you different things:

-- Did the cron job's SQL run? (It only queues the HTTP request.)
select status, return_message, start_time
from cron.job_run_details
where jobid = (select jobid from cron.job where jobname = 'invoice-reminders')
order by start_time desc
limit 5;

-- What did the function answer? (pg_net keeps recent responses for a short time.)
select id, status_code, timed_out, error_msg, left(content, 200) as body, created
from net._http_response
order by created desc
limit 5;

The third is the function's Logs card on the Functions tab, which shows the request line (POST /invoice-reminders → 200) and your console.log output. The Cron tab's Recent runs list shows the last 50 runs of every job.

To test without waiting for 07:00, run the select net.http_post(...) statement by itself in the SQL editor.

Change or stop the job

To change the schedule or the SQL, use Edit on the job in the Cron tab. To stop it:

select cron.unschedule('invoice-reminders');

Or use the job's Active toggle (pause) or Delete in the Cron tab. Cron jobs aren't copied to branches; see also Stopping cron jobs on a restored project or branch.

Do you need a function at all?

If the job only touches your own data (delete old rows, refresh a materialized view, recalculate totals), schedule the SQL directly with cron.schedule and skip the HTTP hop. Use a function when the job needs a secret API key or a third-party service. For large backlogs, have cron push work onto a queue and let a function work through it in batches: see Processing queue messages.

Related

• Scheduled jobs with pg_cron
• Monitoring and debugging cron jobs
• Cron job not running
• Debugging database webhooks and pg_net