ID-115 {115.1} RESEARCH — Data API schema isolation (recon + plan)

Data API Schema Isolation — Reconnaissance & Plan

Date: 2026-06-16 · Branch: canonical-pipeline-setup · Status: RECON COMPLETE — no code changed. Decision taken: move the Supabase Data API from exposing public to exposing a dedicated api schema (PostgREST “schema isolation”), with public unexposed.

Supersedes the security-posture half of the S363 migration-squash feasibility memo (machine-local scratch, deleted before S507 — the memo itself is unrecoverable). The migration-squash dry-run results (see §7) remain valid but are now decoupled from security.

0. Current state (what’s already been done)

Applied to client PROD (rovrymhhffssilaftdwd) via the Supabase dashboard (“create and expose schema” + “remove public schema from exposed schemas”). The buttons ran:
```
1
create schema if not exists api;
2
grant usage on schema api to anon, authenticated;
```
and removed public from the project’s exposed-schemas list.
No real users / platform not live, so prod’s Data API being dark is acceptable. No revert required.
Local dev + staging Vercel point at STAGING (turayklvaunphgbgscat), which is UNCHANGED — so nothing developers rely on is broken right now.
⚠️ This change is NOT captured in migrations or config.toml — it is un-migrated dashboard DDL + a PostgREST config change. This is exactly the drift class that broke both prior migration squashes (S118, S176). It MUST be reproduced as a migration + config so staging / Platform / the re-ingest preview branch are consistent (see §8).

1. Why this is the right move (best-practice basis)

PostgREST + Supabase both recommend not exposing base tables; expose security_invoker views + functions from a dedicated schema. Non-exposure returns a hard PGRST106 — a structural boundary that does not depend on per-object grant vigilance.
This replaces (not relocates) the custom machinery we built to fight Supabase’s old auto-grant defaults: the per-function REVOKE … FROM anon discipline + the migration-revoke-guard CI job are no longer load-bearing once public is unreachable. Fail-closed by schema beats fail-open-unless-you-remember-to-REVOKE — the right posture given AI-scaled mass targeting and a 2-person team.
The Supabase Oct-30-2026 platform change (new tables/functions no longer auto-exposed/auto-granted to anon/authenticated/service_role on ALL existing projects) means explicit exposure is coming regardless — doing it deliberately now is strictly better than reacting later.

Sources: PostgREST schema-isolation; Supabase hardening-data-api, using-custom-schemas, RLS guide (security_invoker), lint 0010_security_definer_view, pg_graphql search_path. (Full URL list in session log.)

2. Affected surface (precise inventory)

Full per-call-site listing: /tmp/claude/data-api-surface.md (1519 lines).

PostgREST consumers AFFECTED by unexposing public:

Consumer	Notes
App browser client (`lib/supabase/client.ts`)	anon/authenticated, RLS path — main surface
SSR + service-role (`lib/supabase/server.ts`: `createClient`, `createServiceClient`)	server routes + `lib/*`
MCP server (`lib/mcp/auth.ts`: `createMcpUserClient`/`createMcpClient`, tools/*)	large consumer
~30 ad-hoc `createClient(...)` in `scripts/`	only `auth:`/`global:` opts today, never `db:`
`scripts/bid_worker.py`	supabase-py (PostgREST): `form_questions`, `workspaces`, `template_completions` + storage
e2e tests (`e2e/`, 186 calls)	adaptable

UNAFFECTED:

cocoindex pipeline — asyncpg direct to Postgres (writes public.* via search_path). Zero changes.
mcp-apps — no direct DB client (go through the MCP server).
auth — GoTrue API (supabase.auth.*), not PostgREST.
storage — Storage API (supabase.storage.from(<bucket>): documents/templates/branding), not PostgREST.
No GraphQL/pg_graphql usage anywhere → we can also drop graphql_public from exposed schemas.

Counts:

60 tables/views reached via .from() (57 string-literal + 3 dynamic-only: signup_policy, tenant_config, content_propagation_version — absent from any literal list; easy to miss).
- 9 READ-only; ~48 READ+WRITE. Heaviest: content_items (253), workspaces (64), content_history (51), entity_mentions (44), form_questions (41), form_responses (33).
58 distinct RPCs via .rpc() (all literal, 0 dynamic). Client-side ones (anon/authenticated): filter_by_keywords, get_items_with_quality_flags, get_user_tag_counts, get_entity_summary, get_filter_counts, get_unique_authors, find_related_items, toggle_star. Rest server-side.
22 dynamic .from(variable) sites (1 in app/, 21 in scripts/) — a default-schema switch covers these automatically; a literal-by-literal rewrite would silently miss them.
No .schema('<x>') overrides anywhere. All .from/.rpc literals target public.

3. Target architecture

1
api (EXPOSED, the only Data API schema)
2
  ├─ 60 × security_invoker VIEWS  (1:1 over public base tables; explicit column lists)
3
  └─ 58 × RPC entrypoints         (SECURITY INVOKER; DEFINER ones are thin wrappers over public.*)
4

5
public (UNEXPOSED — PGRST106 hard boundary)
6
  ├─ base tables (RLS enabled; grants to anon SELECT / authenticated+service_role CRUD retained)
7
  ├─ SECURITY DEFINER helpers (q_a_search, reference_*, get_user_role, …) — NEVER in an exposed schema
8
  └─ trigger / internal / _test_ functions
9

10
private (OPTIONAL, future) — pure-internal helpers
11
auth / storage / (graphql_public dropped) — Supabase-managed, untouched

Mechanics:

Client switch (zero call-site churn): thread one shared { db: { schema: 'api' } } option through all 5 factory groups (browser, SSR, service-role, MCP, script clients) + bid_worker.py (ClientOptions(schema='api')). .from('x')→api.x, .rpc('y')→api.y. This also covers the 22 dynamic sites. Use .schema('public') only on the server/service client for rare direct-admin paths.
Views: 60 × CREATE VIEW api.<t> WITH (security_invoker = true) AS SELECT <explicit cols> FROM public.<t>;
- security_invoker = true is mandatory on every view or RLS is bypassed (lint 0010 = ERROR → CI-gate).
- 1:1 single-table views are auto-updatable → .insert/.update/.delete flow through; base-table RLS WITH CHECK/USING + grants enforce. No INSTEAD OF triggers needed for the 1:1 case.
- Explicit column lists, not SELECT * (* freezes columns at creation; CREATE OR REPLACE VIEW is append-only) — regenerate via DROP/CREATE.
- Grants: GRANT SELECT to anon, SELECT, INSERT, UPDATE, DELETE to authenticated/service_role on each view.
- Base-table grants STAY (grant_standard_public_table_access): security_invoker means the caller needs privileges on the underlying public table too. They’re safe because public is unreachable via API.
Functions: create 58 api.* entrypoints. Of the 22 SECURITY DEFINER public functions, the ~7 that are .rpc()-called (q_a_search, q_a_get_verbatim, question_match_search, question_match_recompute, reference_search, reference_get_verbatim, reference_ingest) get thin api SECURITY INVOKER wrappers calling the public definer. INVOKER RPCs can be recreated/moved into api directly. Wrapper signatures (TABLE/setof/scalar) must match exactly — the fiddliest part.
Config: supabase/config.toml [api] schemas = ["public","graphql_public"] → ["api"]; keep public in extra_search_path. Mirror on each remote (dashboard exposed-schemas).
Types: supabase gen types typescript --schema public,api (must include public explicitly).

4. SECURITY DEFINER audit (DB ground truth, full-138 state)

114 public functions: 22 SECURITY DEFINER / 92 SECURITY INVOKER, all owned by postgres. Only set_config is anon-executable today (the intentional allow-list). Posture is currently clean.

DEFINER + .rpc-called → need api invoker wrapper: q_a_search, q_a_get_verbatim, question_match_search, question_match_recompute, reference_search, reference_get_verbatim, reference_ingest.
DEFINER trigger/internal/test → stay in public, no wrapper: handle_new_user, handle_user_update, q_a_pairs_history_trigger, coerce_null_token_columns, snapshot_form_response_history, update_citation_count, rls_auto_enable, cleanup_filtered_articles, validate_layer_key, count_auth_users, grant_standard_public_table_access, _test_*.
bypassrls note: definer functions run as postgres and keep their power even when reached indirectly from api — keep light defense-in-depth REVOKEs on the sensitive ones; don’t treat non-exposure as a reason to drop all grant hygiene on definers.

5. What this retires / keeps / changes

Item	Disposition
Per-function `REVOKE EXECUTE … FROM anon` on `public` functions	RETIRE — public functions aren’t `.rpc`-reachable once `public` is unexposed
`migration-revoke-guard` CI (`scripts/check-revoke-guard.ts`)	RETIRE or REPURPOSE → lint that every `api` view has `security_invoker=true` + api grants are least-privilege
`grant_standard_public_table_access(regclass)`	KEEP — security_invoker views need base-table grants on `public`
RLS on base tables + `get_user_role()`	KEEP — still the row-level gate, now via the view path
`ensure_rls` event trigger / `rls_auto_enable()`	KEEP (still want RLS auto-on for new public tables) — or adopt Supabase’s native project-creation checkbox on NEW projects
ADD: `api` view generator + advisor lint gate (`0010`) + least-privilege api grants	new low-maintenance machinery, replaces the old

6. Open decisions (confirm before Phase 1)

Base tables: keep in public (unexposed) vs move to private. Lean: keep in public — least churn; both are equally safe (unexposed).
View generator: explicit column lists + DROP/CREATE regen (recommended) vs SELECT *. Lean: explicit.
ensure_rls event trigger: keep vs native checkbox (native is new-project-only). Lean: keep for now.
revoke-guard: retire vs repurpose to api-grant/security_invoker lint. Lean: repurpose.
Drop graphql_public from exposed schemas (no GraphQL usage)? Lean: yes, shrink surface.
Function exposure: wrapper-all vs move-invoker-and-wrap-definer. Lean: move INVOKER fns into api, wrapper only the ~7 DEFINER ones.

7. Relationship to the migration squash (decoupled)

The migration-squash dry-run (separate thread) proved the 138-migration chain replays 100% clean (db reset --local, exit 0) and a supabase migration squash produces a near-faithful 1-file baseline.
Under api-isolation, the squash’s main risk (anon regaining EXECUTE on public functions) is moot — public isn’t API-reachable. The squash is now optional file-hygiene, best timed with the ID-45 re-ingest wipe-and-recut (fresh project → no live-history repair). Not a security prerequisite.

8. Phased plan (next-session steps)

Phase 0 — Recon. ✅ DONE (this doc).

Phase 1 — Build api in a migration, validate LOCALLY:

New migration: create schema api + grant usage … to anon, authenticated (captures prod’s manual change).
Generator script → api security_invoker views (explicit cols) for the 60 tables + api entrypoints/wrappers for the 58 RPCs; least-privilege grants.
Flip config.toml [api] schemas = ["api"]; keep public in extra_search_path.
Thread { db: { schema: 'api' } } through the 5 client factory groups (+ bid_worker.py).
Regenerate types --schema public,api.
Validate: supabase db reset --local; app smoke (login / dashboard / content read+write); MCP tools; advisor lint 0010 clean; negative test — confirm public is unreachable (PGRST106) and api works; confirm the 3 dynamic-only tables resolve.

Phase 2 — Staging (turayklvaunphgbgscat): apply migration + config + dashboard exposed-schemas; full E2E; retire/repurpose revoke-guard.

Phase 3 — Prod (rovrymhhffssilaftdwd): apply the migration (lights up the currently-empty api schema); verify; app comes back.

Phase 4 — Bake into provisioning: the migration + config.toml + seed-tenant-from-bundle.ts reproduce the whole posture on the client re-ingest preview branch, Platform, and Platform staging. Optional squash at the recut.

Atomicity risk: at each remote, the [api] schemas exposure flip + the api objects + the client db.schema switch must land together, or reads 404/PGRST106. Prod is already half-flipped (exposed-schemas changed, api empty) — applying the Phase-1 migration completes it.

All four are concurrent sweeps over the same API/RPC/MCP surface this refactor touches. Coordinate, don’t run blind.

Task	Status	Intersection	Action
ID-50 — wrap ~137 routes with `defineRoute` + migrate 369 test call-sites	in_progress	Parallel broad `app/api/**` + test sweep. Different layer (route wrapper vs client `db.schema`), but overlapping files/tests.	The api-isolation client switch is small/centralized (5 factories) → low route-conflict; coordinate the test-suite changes (e2e 186 + ID-50’s 369). Land one sweep’s test churn before the other.
ID-70 — 5 RPCs `RETURNS Json → RETURNS TABLE` (`get_user_tag_counts`, `get_workspace_counts`, `merge_entities`, `get_dashboard_attention_counts`, `get_filter_counts`)	spec_needed	All 5 are in the 58-RPC `api` surface; gen-types-sequencing-sensitive (“after {64.8} cutover — one clean regen”).	Build the 5 `api` RPC entrypoints to the FINAL `RETURNS TABLE` signatures (do the RPC-wrapper slice with/after ID-70, not before — else double-wrap + double regen). The 60-view slice is independent of ID-70.
ID-71 — AI tooling surface rationalisation (58 MCP tools, retire/refine/gap)	in_progress	MCP server is one of the 5 client factories; tool retirement shrinks the DB surface.	Don’t build `api` wrappers for RPCs only used by tools ID-71 will retire; switch the MCP client `db.schema` in concert with ID-71’s MCP changes.
ID-104 — AI eval engine (touchpoint registry, `recordAiCall`)	in_progress	Actively adding tables (`eval_*`, `ai_call_events` — already in the 60).	The view generator MUST be idempotent + add a CI drift-check: any new `public` base table without an `api` view fails (keeps the surface honest while ID-104/71 land tables).

Shared chokepoint = gen types. ID-70, the ID-64.8 cutover regen, and the api-schema --schema public,api switch all rewrite database.types.ts. Fold the api-schema type-gen change into whichever regen already has to happen (ID-70 / cutover) to avoid extra regens.