Schedule a Nightly Supabase Edge Function with pg_cron

pg_cron will happily report "succeeded" for a nightly job whose HTTP request came back 401. Copy a scheduled scraping setup without understanding why, and you can go weeks with an empty table and a green status.

This guide builds the smallest version of the pattern. A nightly pg_cron job calls an Edge Function through pg_net, the function fetches public records and upserts them into Postgres, and afterwards you check both the cron log and the HTTP response log.

One public write-up of a county real-estate pipeline uses this same shape, with nightly pg_cron runs feeding a Supabase backend (dev.to article). Its figures (county count, lead volume, table count) are self-reported and unverified, and nothing here depends on them. Only the pattern is reused.

Unexecuted code notice: every snippet below is unexecuted. None of it has been run against a live project, so test in a scratch project first. The URL https://example.com/api/public-records is a placeholder, not a real endpoint.

What you need

The examples assume the pieces below. No version is claimed as the latest, so pin whatever you actually install.

| Piece | Role | Version note | |---|---|---| | Supabase project | Postgres plus Edge Functions | Any current hosted project | | pg_cron extension | Schedules SQL | Enable from the Dashboard or SQL | | pg_net extension | Async HTTP from SQL | Check its docs for the default timeout | | Supabase Vault | Encrypted secrets | Enabled in the Dashboard | | Supabase CLI | Deploys the function | Run supabase --version and record it | | supabase-js | Upsert from the function | Pinned to major version 2 via an npm specifier |

Step 1: Enable the extensions and create a target table

Enable both extensions in Database > Extensions, or run:

-- UNEXECUTED
create extension if not exists pg_cron;
create extension if not exists pg_net;

create table if not exists public.county_records (
  source text not null,
  record_id text not null,
  payload jsonb not null,
  fetched_at timestamptz not null default now(),
  primary key (source, record_id)
);

The composite primary key does the real work. A re-run hits the same row instead of creating a duplicate, so retries are safe.

Step 2: Write the Edge Function

Create it with supabase functions new ingest-records, then replace the contents of index.ts:

// UNEXECUTED
import { createClient } from 'npm:@supabase/supabase-js@2'

Deno.serve(async () => {
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )

  const res = await fetch('https://example.com/api/public-records?limit=200', {
    headers: { 'User-Agent': 'my-ingest-bot (contact@example.com)' },
  })
  if (!res.ok) {
    return new Response(JSON.stringify({ error: 'upstream ' + res.status }), { status: 502 })
  }

  const rows = await res.json()
  const records = rows.map((r: any) => ({
    source: 'example-county',
    record_id: String(r.id),
    payload: r,
    fetched_at: new Date().toISOString(),
  }))

  const { error } = await supabase
    .from('county_records')
    .upsert(records, { onConflict: 'source,record_id' })
  if (error) {
    return new Response(JSON.stringify({ error: error.message }), { status: 500 })
  } This pairs well with [how to shut down dev vms at night: tags, schedule, alerts](/how-to-shut-down-dev-vms-at-night-tags-schedule-alerts).

  return new Response(JSON.stringify({ upserted: records.length }), {
    headers: { 'Content-Type': 'application/json' },
  })
})

Deploy with supabase functions deploy ingest-records.

Return real error statuses, as above. A function that answers 200 with an error in the body hides failures from everything downstream.

Step 3: Put the URL and key in Vault

Hardcode the key in the cron command and it sits in plain text in cron.job, visible to anyone with read access to that table. Vault avoids that.

-- UNEXECUTED
select vault.create_secret(
  'https://YOUR_PROJECT_REF.supabase.co/functions/v1/ingest-records',
  'ingest_url'
);
select vault.create_secret('YOUR_KEY_HERE', 'ingest_key');

The key must be one the function's JWT check accepts. Supabase has changed which key formats projects issue over time, so check the current API key documentation. Keep the key out of version control, and never commit a migration that contains the literal value.

Step 4: Schedule the nightly call

-- UNEXECUTED
select cron.schedule(
  'nightly-ingest',
  '0 8 * * *',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'ingest_url'),
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'ingest_key')
    ),
    body := '{}'::jsonb,
    timeout_milliseconds := 60000
  ) as request_id;
  $$
);

Expected output is a single job id, such as 1.

Confirm in your project whether cron expressions run in UTC (the usual default). If they do, 0 8 * * * lands in the early morning hours in Central time.

Before waiting for the schedule, run the inner select net.http_post(...) by hand once. It saves a night of guessing.

Step 5: Verify the run

Start with the cron log:

-- UNEXECUTED
select jobid, status, return_message, start_time, end_time
from cron.job_run_details
where jobid = (select jobid from cron.job where jobname = 'nightly-ingest')
order by start_time desc
limit 5;

A healthy row shows status = succeeded. Then check the part people skip, the pg_net response table:

-- UNEXECUTED
select id, status_code, timed_out, error_msg, left(content::text, 200) as body, created
from net._http_response
order by created desc
limit 5;

A good run shows status_code = 200, timed_out = false, and a body like {"upserted":200}. Then count rows in county_records and compare max(fetched_at) to last night.

The failure case: cron says succeeded, the table is empty

net.http_post only queues a request and returns an id. pg_cron is done the moment that statement finishes, so cron.job_run_details shows succeeded no matter what the function answers. This is an inference from how the two extensions are split, so confirm it on your own project by testing a bad key.

To reproduce it, rotate or mistype the Vault key, then run the job. The cron log stays green. The response table shows what actually happened:

-- UNEXECUTED
select status_code, timed_out, error_msg, left(content::text, 200) as body
from net._http_response
where status_code is distinct from 200 or timed_out
order by created desc;

A 401 means the Authorization header is wrong or stale, so update the Vault secret. A timed_out = true row with an error_msg means pg_net gave up waiting before the function answered. Raise timeout_milliseconds, check the function's logs in the Dashboard, and shrink the batch.

Two caveats. The response table is pruned periodically, so check it the next morning or copy failures into your own log table. And the cron log does not record the pg_net request id, so you match runs to responses by timestamp, unless you wrap the call in a small SQL function that stores request_id in a table of your own.

A timeout can also leave a half-finished run. Idempotent upserts make the re-run harmless.

Limits on long scrapes, and when to move off

Edge Functions have wall-clock and resource limits that vary by plan. The exact numbers are in the Supabase limits documentation. Design for the small end of them:

Also read: how to make layered box-shadow css (plus glass cards) in depth

1. Process one source per invocation, with its own cron entry, rather than looping over everything in one call.
2. Page through the source with a cursor stored in a table, and stop after a fixed batch size.
3. Upsert on a natural key so any batch can be repeated.
4. Add a small delay between upstream requests so you do not hammer the site.

Move the job elsewhere when a single source cannot finish in one invocation even in small batches, or when it needs a real browser. Heavy browser automation is a poor fit for Edge Functions (inference). A scheduled worker or container can do that job and write into the same Postgres table, while pg_cron keeps handling lightweight triggers.

Before you scrape public records at scale

Public records are not automatically free to scrape in bulk. Run through this list before the first scheduled run:

1. Read each county or appraisal district site's terms of use. Some prohibit automated access or offer a bulk download or API instead.
2. Fetch and obey robots.txt for each host.
3. Rate limit requests, send an honest User-Agent with a contact address, and back off on 429 or 5xx responses.
4. Keep upserts idempotent so retries never duplicate or corrupt data.
5. Get a compliance review before contacting anyone. State rules on outreach to property owners (telemarketing, do-not-call lists, text messaging, and real-estate wholesaling disclosure or licensing) differ by state, and a developer cannot settle them alone. Talk to a lawyer.

What to expect next

After the first scheduled night, expect to spend your time in net._http_response and the function logs, not in cron. Most early problems are a stale key, an upstream format change, or a batch that is too big.

Once the checks above pass for a week, add an alert query that fails loudly when max(fetched_at) is older than a day.