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).

This is the canonical, complete list of all five cron jobs (accrue-interest, wise-reconcile, job-signals/pipeline-refresh, follow-ups, retention-purge) — one per route handler under src/app/api/cron/. The condensed copy in the go-live runbook (docs/go-live.md §4) mirrors this template; if the two ever disagree, this file wins. The two recruiting-* jobs only apply when RECRUITING_ENABLED is set; skip them otherwise.

Removed: the lock-ended-assignments job (auto-lock ended assignments via status='locked') was deleted — it was a non-payroll writer of locked, which ADR-0050 reserves for payroll pay-run locks. Freezing an ended assignment's hours is now derived live from assignment state (read-only in the owner grids), so nothing needs to be scheduled. If it was scheduled on an existing deployment, unschedule it: select cron.unschedule('lock-ended-assignments');

-- 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: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: refresh the BD pipeline (ADR-0040). pg_cron fires on a fixed UTC clock
-- regardless of the app's operating timezone. For reference, 13:00 UTC = 08:00 ET during EST (09:00
-- during EDT) and 06:00 MT during MST (07:00 during MDT) — note OPERATING_TIMEZONE defaults to
-- America/Denver (Mountain) per env.ts/ADR-0024/.env.example, while the runtime settings default is
-- America/New_York (Eastern); pick the wall-clock framing that matches the facilities you prospect.
-- 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>')
  );
  $$
);

-- Daily 06:30 UTC (only when RECRUITING_ENABLED): advance due candidate nurture cadences
-- (materializes tasks + drafts emails — never auto-sends) and release referral rewards whose
-- contractor has been active ≥ 90 days. Idempotent. Skip this job if recruiting is off.
select cron.schedule(
  'recruiting-follow-ups',
  '30 6 * * *',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/follow-ups',
    headers := jsonb_build_object('Authorization', 'Bearer <CRON_SHARED_SECRET>')
  );
  $$
);

-- Daily 06:40 UTC (only when RECRUITING_ENABLED): RA 10173 storage-limitation purge — hard-deletes
-- expired, soft-deleted non-hire candidates (plus their Storage objects and raw payloads past their
-- retention window). Idempotent. Skip this job if recruiting is off.
select cron.schedule(
  'recruiting-retention-purge',
  '40 6 * * *',
  $$
  select net.http_post(
    url     := 'https://<your-app-host>/api/cron/retention-purge',
    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.