Kit deploy fails on hosted Supabase: kit_sync_setup_cron cannot set app.kit_cron_secret GUC

[ZappoMan]

What happened?

After running npm run setup:full with Kit configured (or setup:kit), GitHub Actions deploy to production/staging fails at Ensure kit-sync cron job when KIT_CRON_SECRET is present:

kit_sync_setup_cron failed: 403 Forbidden {"code":"42501","details":null,"hint":null,"message":"permission denied to set parameter \"app.kit_cron_secret\""}
Error: Process completed with exit code 1.

Expected: Kit setup + first deploy registers the pg_cron job that periodically invokes the kit-sync Edge Function.

Actual: Deploy succeeds through Supabase migrations, edge function deploy, and Kit secret injection, then hard-fails on cron registration. Marketing email sync never runs on a schedule.

This is reproducible on hosted Supabase (not just a misconfigured adopter repo).

How setup:full leads to this failure

1. setup:kit / setup:full (kit phase) collects or auto-generates KIT_API_KEY, KIT_CRON_SECRET, and KIT_WEBHOOK_SECRET, then syncs them to GitHub Actions secrets via GITHUB_SECRETS in scripts/lib/setup-manifest.mjs.

2. Deploy workflows (deploy-production.yml, deploy-staging.yml, pr-preview-environment.yml) deploy kit-sync / kit-webhook and set Supabase function secrets including KIT_CRON_SECRET.

3. After function deploy, scripts/ensure-kit-sync-cron.mjs calls the kit_sync_setup_cron(p_url, p_secret) RPC using the service role key.

4. The RPC (defined in supabase/migrations/20260521000000_kit_sync_cron.sql) tries:

   EXECUTE format(
     'ALTER DATABASE %I SET app.kit_cron_secret = %L',
     current_database(),
     p_secret
   );

5. Hosted Supabase returns 42501 permission denied to set parameter "app.kit_cron_secret" — even though the function is SECURITY DEFINER and the caller uses service_role.

The migration comment explains the GUC was chosen to avoid inlining the secret in cron.job.command (which is readable). That design is reasonable, but ALTER DATABASE SET for custom app.* GUCs is not permitted on hosted Supabase.

Asymmetry with other Kit deploy steps

• Kit webhook ensure (ensure-kit-webhook-endpoint.mjs) uses continue-on-error: true and skips when KIT_API_KEY is unset.
• Kit cron ensure has no continue-on-error and exits non-zero when KIT_CRON_SECRET is set (which setup:full always does when Kit is configured).

So adopters who complete Kit setup get a deploy blocker; adopters who skip Kit entirely avoid this step only if KIT_CRON_SECRET is absent.

Steps to reproduce

1. Fork BeakerStack and run npm run setup:full, completing the Kit phase (or run setup:kit and sync secrets to GitHub).
2. Ensure KIT_CRON_SECRET is in GitHub Actions secrets (auto-generated by setup if omitted locally).
3. Push to main (production) or develop (staging).
4. Workflow passes supabase db push (migration 20260521000000_kit_sync_cron.sql applies) and deploys edge functions.
5. Ensure kit-sync cron job runs node scripts/ensure-kit-sync-cron.mjs → RPC fails with the error above.

Root cause

kit_sync_setup_cron() persists the cron bearer token via a database-level custom GUC (app.kit_cron_secret). Hosted Supabase restricts setting arbitrary custom parameters this way. The failure occurs before cron.schedule() runs, so no pg_cron job is registered.

Related tracking: #325 lists “Staging smoke test: kit_sync_setup_cron() succeeds (ALTER DATABASE SET app.kit_cron_secret)” as an open follow-up — this bug report confirms it fails on hosted Supabase with the current implementation.

Suggested fix

Replace GUC storage with a private config table readable only from SECURITY DEFINER functions, e.g.:

• Create kit_sync_runtime_config (singleton, worker_url, cron_secret) with REVOKE ALL FROM PUBLIC.
• Update kit_sync_setup_cron() to INSERT ... ON CONFLICT DO UPDATE the secret/URL.
• Schedule pg_cron with a static SQL body that SELECTs URL and secret from that table (secret still not inlined in cron.job.command).

Example approach (adopter fork):

INSERT INTO kit_sync_runtime_config (singleton, worker_url, cron_secret)
VALUES (true, p_url, p_secret)
ON CONFLICT (singleton) DO UPDATE SET ...;

PERFORM cron.schedule(
  'kit-sync-worker',
  '*/5 * * * *',
  $sql$
    SELECT net.http_post(
      url := (SELECT worker_url FROM public.kit_sync_runtime_config WHERE singleton),
      headers := jsonb_build_object(
        'Authorization', 'Bearer ' || (SELECT cron_secret FROM public.kit_sync_runtime_config WHERE singleton),
        ...
      ),
      ...
    )
  $sql$
);

Ship as a follow-up migration replacing kit_sync_setup_cron().

Optional hardening:

• Add continue-on-error: true on cron ensure (matching webhook) only as a temporary safety net — adopters would still have broken Kit sync without noticing.
• Document in setup:full / Kit recipe that cron registration requires the migration fix on hosted Supabase.

Workarounds (adopters)

• Skip Kit cron: delete KIT_CRON_SECRET from GitHub secrets — deploy skips the step (KIT_CRON_SECRET not set — skipping cron registration). Kit sync will not run on a schedule.
• Skip Kit entirely: run setup:full with --skip-kit (or omit Kit secrets) if marketing email sync is not needed yet.

Environment

• Template: BeakerStack 2026.001
• Workflow: deploy-production.yml step Ensure kit-sync cron job (production)
• Hosted Supabase project (production tier)
• Supabase CLI v2.54.11 in CI

Related

• Kit Phase 3 post-#316 follow-ups: operator setup, tests, cron verification (#315) #325 — Kit Phase 3 follow-ups (cron verification checklist)
• Kit Phase 3 follow-up: deploy secret handling, plan_id pipeline, operator setup (#311) #315 — Kit Phase 3 deploy secret handling (closed)
• setup:full does not sync Supabase service role keys to GitHub Actions secrets #382 — separate setup:full gap: Supabase service role keys not synced to GitHub secrets