Cron setup (pg_cron → route handlers)

The scheduled jobs are HTTP route handlers authenticated by CRON_SHARED_SECRET (see ADR-0024). Wire them after deploy, because the schedule needs the deployed app URL and the secret. Run this once in the Supabase SQL editor (replace the placeholders).

-- One-time: enable the scheduler + HTTP client.
create extension if not exists pg_cron;
create extension if not exists pg_net;

-- Replace with your deployed app origin and the CRON_SHARED_SECRET value from the app env.
-- (Consider storing the secret in Supabase Vault instead of inlining it.)
--   APP_URL  = https://<your-app-host>
--   SECRET   = <CRON_SHARED_SECRET>

-- Nightly 06:10 UTC: lock hours for ended assignments.
select cron.schedule(
  'lock-ended-assignments',
  '10 6 * * *',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/lock-ended',
    headers := jsonb_build_object('Authorization', 'Bearer <CRON_SHARED_SECRET>')
  );
  $$
);

-- Nightly 06:20 UTC: recompute interest on overdue invoices.
select cron.schedule(
  'accrue-overdue-interest',
  '20 6 * * *',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/accrue-interest',
    headers := jsonb_build_object('Authorization', 'Bearer <CRON_SHARED_SECRET>')
  );
  $$
);

-- Every 2 hours: reconcile funded Wise payouts back to prepared payments (read-only against Wise;
-- advances matched payments, snapshots variances/orphans). See ADR-0028. Needs WISE_API_KEY +
-- WISE_PROFILE_ID in the app env (the handler returns a "wise_not_configured" snapshot otherwise).
select cron.schedule(
  'wise-reconcile',
  '0 */2 * * *',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/wise-reconcile',
    headers := jsonb_build_object('Authorization', 'Bearer <CRON_SHARED_SECRET>')
  );
  $$
);

-- Working days 13:00 UTC = 08:00 America/New_York during EST (09:00 during EDT): refresh the BD
-- pipeline (ADR-0040). One pass does it all — expire stale job signals, ingest new postings, recompute
-- every facility's score + recalibrate tier cutoffs from the cohort, then run the cadence engine
-- (auto-enroll triggered prospects + heal follow-up tasks). `1-5` = Mon–Fri. pg_cron runs in UTC, so
-- this is DST-shifted by an hour in summer; if you'd rather anchor to EDT (08:00 ET in summer, 07:00
-- in winter — i.e. never after 8AM ET) use '0 12 * * 1-5' instead.
select cron.schedule(
  'pipeline-refresh',
  '0 13 * * 1-5',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/job-signals',
    headers := jsonb_build_object('Authorization', 'Bearer <CRON_SHARED_SECRET>')
  );
  $$
);

To remove a job: select cron.unschedule('lock-ended-assignments');

The handlers fail closed: if CRON_SHARED_SECRET is unset in the app env, every cron call returns 401, so accidentally exposing the route does nothing.