Cron, vault and pg_net

Lesson 2 · about 15 minutes · how Postgres calls our own edge functions on a schedule, and why that path failed silently for a week.

Direction B on the system map. Nothing outside Postgres triggers the price refreshes, the nightly backfills or the Slack alerts. A cron extension fires SQL, the SQL asks an HTTP extension to call an edge function, and the secrets it needs live in an encrypted table. Three extensions, one wrapper function, and one invariant that a test now enforces because a human learned it the hard way.

The three extensions

ExtensionWhat it gives SQLWhere you meet it
pg_croncron.schedule(name, crontab, sql) and cron.unschedule(name). Jobs live in cron.job; runs in cron.job_run_details.43 migrations call cron.schedule
pg_netnet.http_post(url, headers, body). Asynchronous: it queues the request and returns a request id immediately. The response, including a 403, lands later in net._http_response.private.post_to_edge_function
Vaultvault.decrypted_secrets: a view that decrypts on read. Holds supabase_url and supabase_service_role_key, plus per-user SnapTrade secrets by id.migration 20251117191248, 20260309195344
Why vault and not an env var? Postgres cannot read edge-function env vars, and current_setting('app.supabase_url') was the first attempt; it threw "unrecognized configuration parameter" in prod. Migration 20251117191248 unscheduled every job and rebuilt them on vault. Vault is the only secret store SQL can reach. migrations/20251117191248_update_cron_jobs_to_use_vault_secrets.sql:1-6

The path, step by step

1A migration schedules a job. The body is either an inline net.http_post (older jobs) or a bare SELECT some_wrapper(); (newer jobs). Before scheduling, the migration unschedules by name inside DO $$ … EXCEPTION WHEN OTHERS THEN NULL END $$ so a fresh database does not error. migrations/20260612183405_schedule_news_refresh.sql:10-33
2pg_cron fires the SQL. The job runs as the postgres role. If the SQL returns without raising, cron.job_run_details records succeeded. That word means "the SQL ran", not "the HTTP call worked". migrations/20260714200657:11-14
3The wrapper calls private.post_to_edge_function(path, body). It reads the URL and the service_role key from vault, adds Content-Type and the mandatory Origin: https://api.wallstreetbets.com, and calls net.http_post. It is SECURITY DEFINER so it can read vault, and execute is revoked from public, anon and authenticated because it forwards the service key to any path a caller names. migrations/20260714200640_route_intraday_refresh_through_wrapper_functions.sql:38-76
4pg_net delivers the request to Kong, which routes it to the edge function exactly like a client request. The function's CORS gate sees the Origin, passes, and validateServiceRole accepts the bearer. _shared/cors.ts · _shared/jwt/default.ts
5The response is written to net._http_response keyed by the request id. Nobody reads that table unless they go looking. A 403 here and a "succeeded" in step 2 are perfectly compatible.

The week the intraday tier went empty

July 2026. Migration 20260707200103 rescheduled the four intraday refresh jobs to fix an interval-type bug. It rewrote the job bodies from scratch and left out the Origin header. Every call 403'd at the CORS gate, before the handler. pg_cron logged success on every run. The same day another migration retired the tier's other writer, so the two-day retention sweep drained the table to nothing and one-day charts were blank for a week. migrations/20260714200640:4-17

Three fixes came out of it, and they are the shape of how this repo hardens things:

FixMechanismWhere
Move the header out of the thing a reschedule rewritesJobs call a wrapper function; the wrapper calls post_to_edge_function, which owns the header. A reschedule rewrites the cron command, not a function body.20260714200640
Make the invariant a testcron-job-integrity.test.sql scans both cron commands and function bodies for any net.http_post to a CORS-enforcing function that lacks an Origin, and also checks every SELECT foo(); job names a function that exists.tests/database/cron-job-integrity.test.sql
Watch the data, not the transportcheck_price_ingestion_health() reports how stale the newest bar is per tier. A runner every 15 minutes records history in private.price_ingestion_health, suppresses intraday alerts outside 04:00–20:00 ET, and rate-limits to one alert per tier per four hours.20260714200657

The health runner posts to a vault secret named ops_alert_webhook_url. Migration 20260925104500 states that secret was never created, so today the runner records history and raises a WARNING instead of posting. The news draft alert was fixed by routing through the notify-slack edge function, which does have the URL as an env var. If you wire ops alerts, take the same route.

Why a trigger wraps the alert in its own exception block. news.notify_draft_created() runs AFTER INSERT on articles. post_to_edge_function raises if supabase_url is missing from vault; without the BEGIN … EXCEPTION block that raise would roll back the article insert. The article is the point, the alert is not. migrations/20260925104500_route_slack_alerts_via_edge_function.sql:21-35

Check yourself

Answer from memory. The mechanisms matter more than the names.

Do this in the repo

  1. List the scheduled jobs on your local stack and spot which ones inline net.http_post versus call a wrapper: 
    psql postgresql://postgres:postgres@localhost:54322/postgres -c "select jobname, schedule, left(command, 60) from cron.job order by jobname"
  2. Run the guard that would have caught the July outage, then read its two assertions: cd src && npx supabase test db --debug 2>&1 | grep -A2 cron-job-integrity (or open src/supabase/tests/database/cron-job-integrity.test.sql).
  3. Call the health check by hand and predict which tiers will be unhealthy on a fresh local database: select * from check_price_ingestion_health();

Primary source

Supabase: pg_net, specifically the section on reading responses. Then pg_cron for cron.job_run_details.