ID-115 {115.3} TECH — Data API schema isolation (design + R1..R10)

TECH — Data API Schema Isolation (public unexposed → api exposed)

Status: TECH design. Implements data-api-isolation-PRODUCT.md. Recon: specs/id-115-data-api-schema-isolation/notes/data-api-schema-isolation-recon.md. Surface: /tmp/claude/data-api-surface.md. Every proposed change cross-references the PRODUCT invariant id(s) it satisfies. A full Risks & mitigations section folds in every gap-hunt finding.

---

0. Scope & two load-bearing facts

Moves the Supabase Data API (PostgREST) from exposing public to exposing a dedicated api schema, with public unexposed and reachable only by direct-Postgres consumers (cocoindex asyncpg, migrations, supabase db). All six §6 recon decisions ratified at their leans. Touches DDL (one schema + a generator-produced migration), config.toml, 6 TS factory functions across 5 groups + ~37 inline-createClient scripts + 7+ test/e2e/eval clients, one Python worker, type-gen, and CI (repurpose migration-revoke-guard + a new drift check).

Non-goals / untouched (INV-14): cocoindex pipeline (writes public.* via asyncpg search_path), GoTrue auth (supabase.auth.*), Storage API (supabase.storage.from(bucket)), RLS policy bodies (they continue to gate rows on the base tables; the view path inherits them via security_invoker).

Two facts that shape every section:

1. security_invoker = true is mandatory on every view or RLS is silently bypassed (advisor lint 0010_security_definer_view fires ERROR). The single most important correctness gate (INV-3).
2. security_invoker views require the caller to hold privileges on the underlying public table, so grant_standard_public_table_access and the existing base-table grants STAY — they are the inner half of a two-layer grant (base table for the invoker check, view for the API surface) (INV-10).

---

(a) Schema layout · satisfies INV-1, INV-2, INV-3, INV-6, INV-7, INV-17, INV-20

api      EXPOSED — the only Data API schema
  ├─ 60 × VIEW  WITH (security_invoker = true)   1:1 over public base tables, explicit column lists
  ├─ 58 × FUNCTION                                INVOKER entrypoints; 7 thin wrappers over public DEFINERs
  └─ GRANT USAGE ON SCHEMA api TO anon, authenticated, service_role

public   UNEXPOSED — PGRST106 hard boundary for the Data API
  ├─ base tables          RLS enabled; existing anon-SELECT / authenticated+service_role-CRUD grants RETAINED
  ├─ 22 SECURITY DEFINER  q_a_*, reference_*, question_match_*, triggers, _test_* — NEVER in an exposed schema
  ├─ 92 SECURITY INVOKER  the .rpc-called ones re-created in api; trigger/internal ones stay
  └─ rls_auto_enable() + ensure_rls event trigger + grant_standard_public_table_access(regclass)  KEPT

auth / storage   Supabase-managed, untouched
graphql_public   DROPPED from exposed schemas (no pg_graphql usage)
private          NOT created in this slice (recon §6.1 lean: keep base tables in public)

The boundary is structural, not grant-vigilant (INV-1): a request to api for public.content_items returns PGRST106 because public is not in the exposed-schema set — independent of whether anyone remembered a REVOKE. This is why the per-function REVOKE … FROM anon discipline becomes non-load-bearing (see §g); but sensitive definers keep light REVOKEs (INV-20).

First migration captures prod’s already-applied manual DDL so staging/Platform/preview converge (INV-15):

-- 20260616_api_schema_create.sql  (first of the slice)
CREATE SCHEMA IF NOT EXISTS api;
GRANT USAGE ON SCHEMA api TO anon, authenticated, service_role;
COMMENT ON SCHEMA api IS
  'Exposed Data API schema (PostgREST schema isolation). security_invoker views + '
  'invoker RPC entrypoints over public.*. public is UNEXPOSED — PGRST106 boundary.';

rls_auto_enable() + ensure_rls event trigger are KEPT verbatim (INV-17); sensitive public definers keep their REVOKEs and set_config stays the sole anon-EXECUTE function (INV-20).

---

(b) The VIEW generator · satisfies INV-3, INV-4, INV-8, INV-9, INV-10, INV-16, INV-21

A deterministic generator scripts/generate-api-views.ts (Bun, sibling to check-revoke-guard.ts) reads information_schema from the local stack post-db reset and emits one idempotent SQL migration. Re-runnable so it survives ID-104/ID-71 adding tables.

Column discovery (explicit lists, never SELECT *) — INV-9

SELECT * freezes columns at creation; a later ADD COLUMN does not propagate, and CREATE OR REPLACE VIEW is append-only. The generator emits an explicit ordered column list per view and uses DROP/CREATE (not REPLACE):

SELECT column_name, attgenerated, attidentity
FROM information_schema.columns c
JOIN pg_attribute a ON a.attrelid = (quote_ident($1))::regclass AND a.attname = c.column_name
WHERE c.table_schema = 'public' AND c.table_name = $1
ORDER BY c.ordinal_position;

The table set is the intersection of information_schema.tables (BASE TABLE, schema public) with the per-call-site surface list, explicitly including the 3 dynamic-only tables (signup_policy, tenant_config, content_propagation_version) that appear in no string-literal .from() list and would otherwise be missed (INV-4).

FK columns are projected verbatim (no alias/expr) so PostgREST view-relationship inference fires for embedded selects (INV-21). Generated/identity columns (e.g. content_items.content_text_hash, 20260416102457:629) are emitted as plain passthrough public.t.col references — selectable but, as base-table generated columns, never writable, which is correct (INV-9, INV-21 gap-3).

Emitted DDL per view (idempotent DROP/CREATE)

-- generated; do not hand-edit
DROP VIEW IF EXISTS api.content_items;
CREATE VIEW api.content_items
  WITH (security_invoker = true) AS
  SELECT
    id, workspace_id, title, body,
    content_text_hash,            -- generated col: passthrough, selectable, never insert-required
    -- … explicit ordered list incl. every FK column verbatim …
    created_at, updated_at
  FROM public.content_items;

GRANT SELECT                          ON api.content_items TO anon;            -- INV-10
GRANT SELECT, INSERT, UPDATE, DELETE  ON api.content_items TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE  ON api.content_items TO service_role;

security_invoker = true is emitted on every view unconditionally; the generator’s own post-generation grep fails the build if any CREATE VIEW api. lacks the storage parameter (INV-3).

Auto-updatability — why no INSTEAD OF triggers — INV-8

Each view is a 1:1 single-base-table projection, no joins/aggregates/DISTINCT/computed columns, making it PostgreSQL-auto-updatable: write through api.content_items rewrites onto public.content_items; base-table RLS USING/WITH CHECK + grants enforce. The generator fails loudly rather than silently emit a non-updatable view for any table whose surface entry is marked READ+WRITE.

Two-layer grant model (why base-table grants stay) — INV-10

security_invoker=true executes as the calling role, so the privilege check happens twice:

1. on api.<view> — supplied by the per-view grants above;
2. on public.<table> — supplied by the existing base-table grants (grant_standard_public_table_access).

Drop layer 2 and every api read returns “permission denied for table content_items”. So grant_standard_public_table_access(regclass) is KEPT verbatim and remains the canonical helper new-table migrations call. GRANT is naturally idempotent, so the grant block needs no DO $$ … EXCEPTION guard; only DROP VIEW IF EXISTS carries idempotency (INV-16).

Idempotency contract (survives ID-104 / ID-71 churn) — INV-16

Re-running the generator after a new public table lands produces a superset migration. The CI drift check (§g) fails any PR that adds a public base table without a matching api view, forcing a re-run in the same PR — keeping the surface honest while ID-104 (eval_*, ai_call_events) and ID-71 land tables concurrently.

---

(c) The FUNCTION strategy · satisfies INV-5, INV-6, INV-7, INV-18, INV-20

Two classes, per ratified decision §6.6 (move-invoker, wrap-definer).

Class 1 — INVOKER RPCs (re-create in api) — INV-5

The INVOKER functions among the 58 .rpc()-called set are re-created in api (same body, same signature, SECURITY INVOKER, SET search_path = public, extensions so unqualified refs resolve to public base tables). Grants: GRANT EXECUTE ON FUNCTION api.<fn>(<sig>) TO <calling roles> — anon/authenticated for the 8 client-side RPCs (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), authenticated/service_role for server-side. The public INVOKER originals stay (harmless — unreachable via API), minimising blast radius. Run gitnexus_impact on each before any in-place change.

Class 2 — the 7 DEFINER RPCs: thin api INVOKER wrappers — INV-6

q_a_search, q_a_get_verbatim, question_match_search, question_match_recompute, reference_search, reference_get_verbatim, reference_ingest are SECURITY DEFINER (run as postgres) and must never live in an exposed schema. Each gets a thin api SECURITY INVOKER wrapper that EXECUTEs the public definer and matches the original signature exactly. Wrapper signature/return type derived from DB ground truth, not guessed:

SELECT pg_get_function_identity_arguments(p.oid) AS args,
       pg_get_function_result(p.oid)              AS result_type,
       p.proretset                                AS returns_set
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.proname = $1;

TABLE/SETOF wrapper (e.g. q_a_search, reference_search, question_match_search):

CREATE OR REPLACE FUNCTION api.q_a_search(
  query_embedding vector(1024), match_count integer DEFAULT 10 /*, EXACT params */)
RETURNS TABLE (id uuid, question text, answer text, similarity double precision /*, EXACT OUT cols */)
LANGUAGE sql SECURITY INVOKER SET search_path = public, extensions
AS $$ SELECT * FROM public.q_a_search(query_embedding, match_count /*, … */); $$;
GRANT EXECUTE ON FUNCTION api.q_a_search(vector, integer /*, … */) TO authenticated, service_role;

Scalar/jsonb wrapper (e.g. reference_get_verbatim):

CREATE OR REPLACE FUNCTION api.reference_get_verbatim(p_reference_id uuid)
RETURNS jsonb LANGUAGE sql SECURITY INVOKER SET search_path = public, extensions
AS $$ SELECT public.reference_get_verbatim(p_reference_id); $$;
GRANT EXECUTE ON FUNCTION api.reference_get_verbatim(uuid) TO authenticated, service_role;

Exact-match rules the generator enforces (a test asserts): param names, types, DEFAULTs verbatim (PostgREST binds named args from the JSON body — a renamed param silently breaks the call); vector(1024) preserved (lib/validation/schemas.ts; vector params still serialised client-side with JSON.stringify); TABLE column list copied from pg_get_function_result. The wrapper is INVOKER but the inner public function remains DEFINER, so the privileged body still runs as postgres — keep light defence-in-depth REVOKEs on the sensitive definers (INV-20). Trigger/internal/test definers get no api object (INV-7).

ID-70 sequencing — INV-18

ID-70 concurrently changes 5 RPCs from RETURNS json to RETURNS TABLE (get_user_tag_counts, get_workspace_counts, merge_entities, get_dashboard_attention_counts, get_filter_counts). Build the api entrypoints for those 5 to the FINAL RETURNS TABLE signatures — do the RPC slice with/after ID-70, never before, else double-wrap + double regen.

ID-71 exclusions

Do not build api wrappers for RPCs ID-71 is retiring (wasted work + they vanish); switch the MCP client db.schema in concert with ID-71’s MCP changes. Cross-check the 58-RPC list against ID-71’s retirement set before generating.

---

(d) config.toml + remote exposure · satisfies INV-1, INV-2, INV-19

[api]
enabled = true
port = 54321
schemas = ["api"]                               # was ["public", "graphql_public"]
extra_search_path = ["public", "extensions"]    # UNCHANGED — keep public so security_invoker views +
                                                # invoker RPCs resolve unqualified public.* refs
max_rows = 1000

• graphql_public dropped (decision §6.5) — zero pg_graphql usage; shrinks surface (INV-2).
• public stays in extra_search_path — exposure and search_path are orthogonal. Removing public from exposed-schemas is the security boundary (INV-1); keeping it in extra_search_path lets the views’ unqualified FROM public.<t> and the wrapper bodies resolve at request time.

Remote dashboard mirror: each remote’s exposed-schemas (Dashboard → Settings → API, or Management API PATCH /v1/projects/{ref}/postgrest db_schema) set to api. Prod (rovrymhhffssilaftdwd) already done manually — the Phase-1 migration completes the half-flip. Staging (turayklvaunphgbgscat) flipped in Phase 2 in lockstep with the migration apply (atomicity, INV-19).

---

(e) The client-factory switch — one shared option · satisfies INV-11, INV-12

Single shared options fragment, zero call-site churn: .from('x')→api.x, .rpc('y')→api.y, and the 22 dynamic .from(variable) sites covered automatically.

New shared constant lib/supabase/schema.ts:

export const API_SCHEMA = 'api' as const;
export const DB_OPTION = { db: { schema: API_SCHEMA } } as const;

• Group 1 — browser (lib/supabase/client.ts:22): pass DB_OPTION to createBrowserClient.
• Group 2 — SSR (lib/supabase/server.ts:20 createClient): add db: { schema: API_SCHEMA } alongside cookies.
• Group 3 — service-role (lib/supabase/server.ts:59 createServiceClient): add db.schema alongside auth: { persistSession: false, autoRefreshToken: false }. service_role runs through api views, has grants on both layers, BYPASSRLS at base — behaviour preserved. Rare direct-admin paths use a per-call .schema('public') override on the service client only (INV-12).
• Group 4 — MCP (lib/mcp/auth.ts:23 createMcpUserClient): add db.schema alongside global.headers.Authorization + auth. createMcpClient/getMcpUserRole inherit it (the user_roles read at auth.ts:83-90 now hits api.user_roles — in the view set). Switch in concert with ID-71.
• Group 5 — scripts (~37 inline createClient, e.g. backfill-layers.ts:115, kb-search.ts:300, seed-e2e-users.ts:181): there is no shared script factory today — each inlines createClient(url, key[, opts]). Introduce a thin scripts/lib/supabase-script-client.ts wrapping createClient with DB_OPTION and migrate the 48 call sites (covers the 21 dynamic script sites for free). DDL-adjacent scripts opt out via .schema('public') per query (INV-12).

Python worker (scripts/bid_worker.py:54 get_supabase()):

from supabase.lib.client_options import ClientOptions
return create_client(url, key, options=ClientOptions(schema="api"))

Redirects every supabase.from_(...) (form_questions, workspaces, template_completions, form_template_fields, form_templates) and supabase.rpc("claim_next_job", …) to api. Storage (supabase.storage.from_) unaffected (INV-14). claim_next_job must exist as an api entrypoint (it is in the 58-RPC set).

Test/e2e/eval clients (gap-hunt: broader than recon’s “5 + bid_worker”, INV-11/INV-21): these construct clients OUTSIDE the factories and today set no db.schema — e2e/fixtures/supabase.ts:19, e2e/auth.setup.ts:26, e2e/tests/oauth-consent-flow.spec.ts:254/330, e2e/tests/mcp-invocation.spec.ts:184, __tests__/integration/helpers/supabase-client.ts:110, __tests__/eval/entity-classification-eval.test.ts:540, __tests__/eval/procurement-drafting-eval.test.ts:85. Centralise the e2e/integration helpers so the schema option is set once; thread db.schema='api' through all 7+. Sequence with ID-50 (369 test call-sites) + e2e (186) per recon §9 to avoid churn collisions.

---

(f) Type generation · satisfies INV-13, INV-18, INV-21

/opt/homebrew/bin/supabase gen types typescript \
  --project-id <ref> --schema public,api \
  > supabase/types/database.types.ts

Produces Database['public'] (consumed by direct-public paths + JSONB overrides) AND Database['api']. Because the factory Database generic resolves .from()/.rpc() against the default schema set by db.schema, the api views/RPCs must be present or call sites lose type-safety. 1:1 views generate near-identical row types to base tables (column lists match), so app types are stable.

Shared chokepoint: gen types is rewritten by ID-70, the ID-64.8 cutover regen, AND this --schema public,api switch. Fold the schema-flag change into whichever regen already has to happen (ID-70 or the 64.8 cutover) — one regen, not three, one merge on database.types.ts (INV-18). JSONB overrides in supabase/types/database-overrides.ts continue to apply; verify they reference public shapes that still exist post-gen. Pin a deterministic --schema ordering and add a tsc gate immediately post-regen. After regen, audit the api Views’ Relationships arrays — if empty, typed embeds break (INV-21 gap-6); see Risks.

---

(g) CI — repurpose revoke-guard + new drift check · satisfies INV-2, INV-3, INV-10, INV-16, INV-20

Repurpose, do not delete (scripts/check-revoke-guard.ts + migration-revoke-guard.yml)

The lint half (CREATE FUNCTION public.<x> must REVOKE from anon) is no longer load-bearing. Keep the robust SQL-parsing machinery (extractCreateFunctions, paren-balancer, dollar-string stripping, types-only normalisation — directly reusable) and repurpose into:

• Lint A — security_invoker enforcement (mirrors advisor 0010 at diff-time, INV-3): every CREATE VIEW api.<name> in a changed migration must carry WITH (security_invoker = true) (or = on). Missing → ::error:: PR-blocking. Catches the highest-severity defect before the live advisor lint would.
• Lint B — least-privilege api grants (INV-10): for every CREATE VIEW api.<name> assert anon=SELECT only, authenticated/service_role=CRUD-as-needed; fail on any anon write grant. The INTENTIONAL_ANON_ALLOW_LIST + validateAllowList anti-pattern guard (rationale ≥40 chars, no “TODO”) are kept and re-tasked to the small set of intentionally anon-readable api objects.
• Lint C — no CREATE FUNCTION api.<x> is SECURITY DEFINER (INV-6/INV-7): every api function must be INVOKER. Critically, update the regex so it does NOT fire the old REVOKE-presence assertion on the new api.* wrappers — else the very migration that implements the refactor self-blocks CI (gap-7). This guard change lands in the SAME PR as the api objects.

Cron mode repointed (INV-3/INV-20): query api views lacking security_invoker, api objects with anon write grants, and confirm set_config is the sole anon-EXECUTE public function:

SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'api' AND c.relkind = 'v'
  AND NOT (coalesce(c.reloptions,'{}') @> ARRAY['security_invoker=true']);
-- any row = a view bypassing RLS (drift)

New DRIFT check — public-table-without-api-view — INV-16

scripts/check-api-view-coverage.ts (or --mode=drift in the repurposed script) asserts against the local post-reset DB that every public base table in the API surface has a matching api view:

SELECT t.table_name FROM information_schema.tables t
WHERE t.table_schema='public' AND t.table_type='BASE TABLE'
  AND t.table_name NOT IN (SELECT table_name FROM information_schema.views WHERE table_schema='api')
  AND t.table_name NOT IN (/* explicit internal-only allow-list */);

Wire into the (renamed) api-grant-guard.yml on supabase/migrations/** plus a step that spins the local stack. Coordinate with schema-parity.yml and the task-view-vendor-drift re-vendor reminder (CLAUDE.md) since ledger/schema shapes shift.

---

(h) Validation matrix · satisfies INV-1, INV-3, INV-5, INV-8, INV-14, INV-15, INV-19, INV-21

#	Check	Command / method	Pass criterion
1	Migration replays clean (INV-15)	supabase db reset --local (foreground)	exit 0; api schema + 60 views + 58 funcs present
2	Every view is invoker (INV-3)	post-reset SQL: count api views w/ reloptions @> ARRAY['security_invoker=true']	count == total api views
3	Advisor lint 0010 clean (INV-3)	mcp__supabase__get_advisors / run-supabase-advisors.ts	no security_definer_view findings
4	App smoke (INV-8)	bun dev; login → dashboard → content read + insert/update/delete	RLS-correct reads; writes flow through views
5	Embedded selects / upserts / counts (INV-21)	run the 39+ embed sites, 28 upsert sites, count-with-embed sites against local reset	no PGRST200, no 42P10, correct counts
6	MCP tools (INV-5)	exercise a read + a write tool via the MCP server	api.user_roles lookup works; RLS-scoped rows correct
7	Negative — PGRST106 (INV-1)	client default schema set to public (or raw PostgREST to public.content_items)	returns PGRST106
8	Positive — api works (INV-1)	same request against api.content_items	200 with expected rows
9	Dynamic-only tables (INV-4)	exercise signup_policy, tenant_config, content_propagation_version views	reads succeed
10	DEFINER wrapper parity (INV-6)	call each of the 7 wrappers; diff shape vs public original	identical row shape / scalar
11	Python worker (INV-14)	run bid_worker.py; enqueue a job	inserts/updates through api; storage + claim_next_job ok
12	Type-gen round-trips (INV-13/INV-21)	gen types --schema public,api; bun build + tsc	Database['api'] populated, embeds typed, no errors
13	CI lints (INV-3/INV-10/INV-16)	repurposed guard + coverage drift vs the slice’s migration	exit 0; broken fixtures exit 1

Phase gating (recon §8): full matrix on local (Phase 1) → staging + full E2E + CI repurpose (Phase 2) → prod apply completes the half-flip (Phase 3) → bake into provisioning / seed-tenant-from-bundle.ts (Phase 4). At every remote, items 1/7/8 pass together (atomicity, INV-19).

---

Risks & mitigations

Severity-ordered; every gap-hunt finding folded in. The first two are blockers and gate Phase 1.

R1 (BLOCKER) — PostgREST resource embedding breaks unless FK columns are resolvable on api views — INV-21

Evidence: 39+ embed sites use application_types!inner(...) (app/workspaces/page.tsx:16, lib/intelligence/summary.ts:45, lib/mcp/tools/workspaces.ts:74, ~25 procurement routes — the application_types!inner(key) ownership check is the auth gate on those routes). 3-level nested embeds: lib/dashboard.ts:335 (form_responses!inner(form_questions!inner(workspaces!inner(name)))), lib/reorient.ts:179/190, app/api/items/[id]/effectiveness/route.ts:103-110; content_items!inner at lib/dashboard.ts:314/325 + lib/reorient.ts:131/142; taxonomy_domains(name) at 3 coverage routes. FK constraints back these on public base tables only (20260520120828:92, uc5_promotion_lineage:40-42, id58_citations_polymorphic_replace:80-82). Self-joins (content_items.parent_id/superseded_by → content_items, 20260601180102:23, 20260421222059:67) are the classic ambiguous case PostgREST cannot auto-resolve over views.

Impact: Every embedded .select() returns PGRST200 at runtime — silent breakage of dashboard, reorient, intelligence feed, the procurement workspace-gating auth routes, provenance export, and MCP workspace/content tools. Highest blast radius.

Mitigation: Each view’s explicit column list MUST include every FK column verbatim (no alias/expr) so PostgREST follows pg_depend to the base column then the base FK. After building views, RUN each embedded query against a local db reset — do NOT assume. For any embed that still fails (esp. self-joins and the 3-hop chains), add an explicit api computed-relationship (a SQL function returning SETOF the related view, named to match the embed hint), OR keep that specific multi-table read as a DEFINER RPC. Gate table-by-table in Phase-1 (matrix item 5). This is the gating early subtask (PLAN S1).

R2 (BLOCKER) — onConflict upserts may fail (views carry no UNIQUE constraint) — INV-21

Evidence: 35 onConflict / 28 .upsert() sites — app/api/admin/users/[userId]/route.ts:47 ('user_id'), app/api/procurement/[id]/questions/extract/route.ts:213 ('workspace_id,question_text'; code comment at :195 notes PostgREST resolves by column list), app/api/coverage/targets/route.ts:87, app/api/read-marks/route.ts:111, app/api/tag-morphology/flags/route.ts:122, draft routes ('question_id'). PostgREST ON CONFLICT needs a unique index on the target columns; a VIEW has none.

Impact: Upserts return 42P10 — breaks user-role admin, procurement question dedup, coverage targets, read-marks, notification prefs. Fails only on the conflict (2nd write) branch, so a naive happy-path smoke passes and production fails.

Mitigation: Validate every onConflict upsert through the api view on local db reset, forcing the conflict branch. If PostgREST cannot arbitrate through the view: (a) confirm the base-table unique index is visible through the auto-updatable view (recent PostgREST forwards to base constraints in many cases — test empirically), or (b) convert those upserts to api RPCs doing INSERT … ON CONFLICT on public, or (c) keep an explicit .schema('public') service path for those admin/pipeline upserts. Enumerate all 28 sites in the Phase-1 matrix.

R3 (HIGH) — generated/identity columns (content_text_hash) — INV-9

Evidence: 20260416102457:629 content_text_hash text GENERATED ALWAYS AS (md5(...)) STORED on content_items (253 sites). Read by app/api/admin/content-dedup/[id]/route.ts:12/93/97, lib/dedup.ts:66. Inserts already omit it (seed-e2e-users.ts:324/378). Also array/vector columns (question_embedding vector(1024), items_created uuid[], text[]).

Impact: Omit it → dedup admin route breaks (PGRST204). Include it as insertable → INSERTs through the view fail. Any non-simple-column expression forfeits auto-updatability.

Mitigation: Generator emits generated/identity columns as plain passthrough (SELECT works; auto-updatability preserved — Postgres rejects writes at the base table, which is correct). Keep every column a bare public.t.col reference (no expr/cast). Introspect pg_attribute.attgenerated/ attidentity; include but never require on insert. Confirm content-dedup route + a content_items insert both pass post-cutover.

R4 (HIGH) — client-option threading is broader than “5 factories + bid_worker” — INV-11

Evidence: Factories confirmed (client.ts:22, server.ts:20/59, auth.ts:23); bid_worker.py:54 has NO ClientOptions today. PLUS factory-bypassing clients with no db.schema: e2e/fixtures/supabase.ts:19, e2e/auth.setup.ts:26, e2e/tests/oauth-consent-flow.spec.ts:254/330, e2e/tests/mcp-invocation.spec.ts:184, __tests__/integration/helpers/supabase-client.ts:110, __tests__/eval/entity-classification-eval.test.ts:540, procurement-drafting-eval.test.ts:85.

Impact: After cutover, any client missing {db:{schema:'api'}} gets PGRST106 on every query against the real staging DB; the failure looks like test-infra breakage, not the intended boundary.

Mitigation: Inventory and update ALL constructors, not just the 5 app factories. Centralise the e2e/integration helpers so the option is set once. Sequence with ID-50 (369 test sites) + e2e (186) per recon §9.

R5 (HIGH) — gen-types currently single-schema; multi-schema changes the Database type shape — INV-13

Evidence: supabase/CLAUDE.md:23 documents --schema public (public only); recon §3.5 requires --schema public,api. The generated Database is the canonical schema source. With multiple schemas, supabase-js resolves Tables<'x'> against the default schema; db.schema:'api' retypes the client to api. database.types.ts is in the sandbox deny-list (guarded chokepoint).

Impact: Post-regen, Tables<'content_items'>/Enums<> may resolve against api (views) vs public (base tables) depending on schema ordering and the PublicSchema helper. Views and base tables differ (view Insert/Update narrower; Relationships differ) → wide TS break OR silently-wrong Insert types omitting required columns.

Mitigation: Fold the --schema public,api regen into the single ID-70 / ID-64.8 cutover regen (one regen, not three). Pin deterministic --schema ordering. Add a tsc gate immediately post-regen. Audit that Tables<'x'> usages still resolve to the intended schema and the “row shapes from Tables<>, composed shapes from z.infer” convention still holds.

R6 (HIGH) — regenerated api Views may emit empty Relationships, breaking typed embeds — INV-21

Evidence: supabase-js infers nested-select result types from the Relationships field. Embed sites (lib/dashboard.ts:335, effectiveness/route.ts:103-110) rely on this for data.form_responses.form_questions.workspaces.name to be typed. gen types historically emits Relationships: [] for views (FK constraints exist only on base tables).

Impact: Even if runtime embedding is patched, TS types embedded fields as never/unknown/ SelectQueryError — a compile wall across dashboard/reorient/effectiveness/provenance/MCP-tools, forcing unsafe as casts that mask shape drift.

Mitigation: After regen, inspect the api Views’ Relationships. If empty: (a) hand-augment the generated types (fragile), (b) define views so the generator infers relationships, or (c) accept casts only at the embed boundary with a runtime-validated z.infer schema (per CLAUDE.md “composed shapes from z.infer”). Treat typed-embed verification as a distinct gate from runtime-embed verification.

R7 (MEDIUM) — check-revoke-guard may self-block the introducing migration — INV-6/INV-16

Evidence: scripts/check-revoke-guard.ts CREATE_FUNCTION_RE is the migration-revoke-guard CI job. The api migration adds many CREATE FUNCTION api.* + CREATE VIEW api.* with GRANTs — exactly what it scans.

Impact: The existing guard’s REVOKE-presence assertion may fire on the new api wrappers (which intentionally carry no REVOKE … FROM anon), red-failing the very migration that implements the refactor — a self-blocking CI deadlock.

Mitigation: Update check-revoke-guard.ts IN THE SAME migration PR (§g Lint A/B/C). Verify the regex does not flag api. functions. Pull the guard change forward to Phase-1 so local db reset CI is green.

R8 (MEDIUM) — head:true / exact-count with embed inherits the FK-resolution risk — INV-21

Evidence: 51 head:true/count:'exact|planned|estimated' occurrences, e.g. app/api/intelligence/workspaces/[id]/metrics/route.ts:63 (count with embedded feed_articles!inner). Count-with-embed joins through the relationship — same FK dependency as R1.

Impact: Count-with-embed queries fail or return wrong counts → broken metrics/pagination. Plain single-view counts are lower-risk but still need the view to expose filtered columns.

Mitigation: Include count+embed sites (notably the metrics route) in the embedding validation matrix. A plain single-table count over an auto-updatable view is fine; count-with-embed inherits R1 and must be tested explicitly.

R9 (MEDIUM) — .schema('public') escape hatches + dedup hash RPC not yet enumerated — INV-12

Evidence: Recon §2 plans .schema('public') for direct-admin, but the codebase has ZERO .schema( overrides today. Storage/table RLS policies reference public.* by name (20260417134137:37-56 on public.read_marks/public.user_roles). The content-dedup match RPC (p_content_hash, content-dedup/[id]/route.ts:97) must exist in api.

Impact: Server/service paths that implicitly hit public via default schema will hit api views after the switch — any admin path needing a public-only column/table not mirrored, or constraint-dependent writes, silently breaks. Storage policies (run in Storage API, not PostgREST) are unaffected but reference public, which still exists (just unexposed).

Mitigation: Before cutover, enumerate every createServiceClient() write path (recon: 17 audited sites) and decide per-site .schema('public') vs the api view. The content-dedup hash RPC gets an api wrapper. Confirm storage RLS policies untouched (the policy grep supports it) and verify the dedup/hash admin flow end-to-end.

R10 (LOW) — Realtime is NOT a risk (recorded so it isn’t re-litigated)

grep for .channel(, postgres_changes, .subscribe(, removeChannel across app/lib/components/hooks/contexts returns 0 matches; no realtime config. The schema switch cannot break realtime. No action. Future note: replication is publication/schema-specific; views are not directly replicable, so adding realtime later would need the publication to target api — document as a future constraint.

---

Files this design touches (absolute paths)

• /Users/liamj/Documents/development/knowledge-hub/lib/supabase/schema.ts — new API_SCHEMA/DB_OPTION
• /Users/liamj/Documents/development/knowledge-hub/lib/supabase/client.ts — add DB_OPTION
• /Users/liamj/Documents/development/knowledge-hub/lib/supabase/server.ts — add db.schema to createClient + createServiceClient
• /Users/liamj/Documents/development/knowledge-hub/lib/mcp/auth.ts — add db.schema to createMcpUserClient
• /Users/liamj/Documents/development/knowledge-hub/scripts/bid_worker.py — ClientOptions(schema="api")
• /Users/liamj/Documents/development/knowledge-hub/scripts/lib/supabase-script-client.ts — new wrapper for ~37 scripts
• /Users/liamj/Documents/development/knowledge-hub/e2e/fixtures/supabase.ts + __tests__/integration/helpers/supabase-client.ts — central db.schema (+ the 5 other test/eval clients)
• /Users/liamj/Documents/development/knowledge-hub/supabase/config.toml — [api] schemas = ["api"]; keep extra_search_path
• /Users/liamj/Documents/development/knowledge-hub/supabase/migrations/<new>_api_schema_create.sql + <new>_api_views_and_rpcs.sql — new (generator output)
• /Users/liamj/Documents/development/knowledge-hub/scripts/generate-api-views.ts — new generator
• /Users/liamj/Documents/development/knowledge-hub/scripts/check-revoke-guard.ts — repurpose → invoker + grant + drift lint
• /Users/liamj/Documents/development/knowledge-hub/.github/workflows/migration-revoke-guard.yml — repurpose/rename → api-grant-guard.yml
• /Users/liamj/Documents/development/knowledge-hub/supabase/types/database.types.ts — regen --schema public,api (fold into ID-70 / 64.8)

KEPT unchanged (load-bearing): grant_standard_public_table_access(regclass) + rls_auto_enable()

• ensure_rls event trigger (20260514150238_…) — base-table grants are the inner half of the two-layer security_invoker grant model.