Adopter migrations and PostgREST schema exposure are not wired together in deploy/CI

[ZappoMan]

Summary

BeakerStack splits database changes across two migration tracks — template migrations (supabase/migrations/, applied by supabase db push / supabase start) and adopter migrations (adopter/db/migrations/, applied separately by npm run db:apply-adopter via psql). When an adopter adds a custom schema (e.g. app) and exposes it in supabase/config.toml, the template does not guarantee that PostgREST (local or hosted) can actually serve that schema at the right time.

This gap surfaced while building NewApp, which stores product data in an adopter-owned app schema and calls it from the client via supabase.schema('app').rpc(...).

Symptoms

Environment	What happens
Local / CI	supabase start can fail because [api].schemas in config.toml lists app, but the schema is only created later by db:init-adopter / adopter migrations (which CI runs after start).
Hosted (staging/production/preview)	Adopter migrations apply successfully (tables, RPCs, RLS exist in Postgres), but the client gets Invalid schema: app and RPC calls return 406 Not Acceptable because hosted PostgREST was never configured to expose app.
Deploy	deploy-staging.yml / deploy-production.yml run npm run db:apply-adopter -- --linked, but historically did not install postgresql-client even though db:apply-adopter shells out to psql.

Locally, config.toml is sufficient. On hosted Supabase, config.toml is not applied automatically unless a deploy step runs supabase config push or equivalent.

Root cause

Three systems are out of sync:

1. Schema existence — adopter init.sql + adopter/db/migrations/ create the app schema and objects.
2. PostgREST exposure — controlled by config.toml locally, and on hosted by dashboard settings, supabase config push, or SQL against the authenticator role (pgrst.db_schemas + NOTIFY pgrst).
3. Deploy ordering — template migrations and adopter migrations run as separate steps, but PostgREST config is only pushed opportunistically via sync-supabase-auth-config.sh, which is skipped entirely when unrelated secrets (RESEND_SMTP_PASS, Google OAuth) are unset — even though [api].schemas lives in the same config.toml.

The template documents a manual dashboard step (“add app to Exposed schemas”) in adopter README material, but deploy workflows do not reliably automate PostgREST exposure when adopter migrations become load-bearing.

Example (NewApp)

NewApp adds:

• adopter/db/migrations/* — DDL + SECURITY DEFINER RPCs in schema app
• supabase/config.toml — schemas = ["public", "graphql_public", "app"]
• Client hooks — supabase.schema('app').rpc('newapp_list_recent', ...)

Expected: merge to develop → staging deploy → Memory UI works.

Actual without extra adopter-side work:

• CI: supabase-tests job fails at Start Supabase services (schema referenced before it exists).
• Staging: migrations applied, but browser console shows Invalid schema: app and failed RPC calls.

NewApp had to add adopter-side workarounds:

1. A template migration (supabase/migrations/…) with CREATE SCHEMA IF NOT EXISTS app so PostgREST can start before adopter migrations run.
2. An adopter migration that sets authenticator.pgrst.db_schemas and NOTIFY pgrst after adopter DDL is applied.
3. Explicit postgresql-client install in deploy workflows before db:apply-adopter.

These are reasonable patches for one app, but they are symptoms of missing template infrastructure.

Recommended fix (template-level)

1. Bootstrap adopter schemas in template migrations

When config.toml lists a custom schema in [api].schemas, the template should ship a matching template migration that runs at supabase start / db push time:

CREATE SCHEMA IF NOT EXISTS app;

Adopter DDL remains in adopter/db/migrations/; the template migration is only a shell so PostgREST can start.

Optionally: lint or generate this from config.toml so adopters cannot forget.

2. Dedicated PostgREST sync deploy step (decoupled from auth config)

Add a deploy step that always runs after db:apply-adopter, independent of sync-supabase-auth-config.sh:

# Pseudocode — link + push only [api] section, or run documented SQL
supabase link …
supabase config push --yes   # OR a small script that PATCHes postgrest db_schemas

Requirements:

• Does not depend on SMTP or OAuth secrets.
• Runs on staging, production, and PR preview after adopter migrations.
• Idempotent.

Alternative acceptable approach: a template-provided adopter migration helper (or final adopter migration hook) that sets authenticator.pgrst.db_schemas from a declared list in adopter config — but the template should own the pattern, not each adopter.

3. Install postgresql-client wherever db:apply-adopter runs

db:apply-adopter.mjs requires psql. CI already installs postgresql-client; deploy workflows should too (or use a shared composite action):

• .github/workflows/deploy-staging.yml
• .github/workflows/deploy-production.yml
• PR preview scripts (preview already checks; make consistent)

4. Document the contract in one place

Clarify in template docs (adopter/db/README.md or docs/ARCHITECTURE.md):

• Template vs adopter migration responsibilities
• That hosted PostgREST exposure is not automatic from adopter migrations alone
• The exact deploy/CI order: supabase db push → db:apply-adopter → PostgREST sync
• That editing an already-applied adopter migration file does not re-run on hosted (new migration required)

5. (Optional) CI guard

Add a check that every schema listed in supabase/config.toml [api].schemas either:

• exists in a template migration bootstrap, or
• has a documented PostgREST sync mechanism that deploy runs

Acceptance criteria

• Fresh CI run: supabase start succeeds when config.toml exposes an adopter schema that adopter migrations populate later.
• Fresh hosted deploy (staging): after deploy workflow completes, supabase.schema('app').rpc(...) works without manual dashboard changes.
• Deploy does not require adopter-specific PostgREST SQL migrations for the common case.
• db:apply-adopter succeeds in deploy workflows without adopter-specific workflow patches.

Related areas

• scripts/db-apply-adopter.mjs (always calls psql)
• scripts/sync-supabase-auth-config.sh (full config push, gated on unrelated secrets)
• .github/workflows/test.yml vs deploy-staging.yml / deploy-production.yml
• Supabase docs: Using custom schemas