ID-115 {115.2} PRODUCT — Data API schema isolation (INV-1..21)

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

Status: PRODUCT spec. Behaviour invariants for the move of the Supabase Data API (PostgREST) from exposing public to exposing a dedicated api schema. All six §6 recon decisions are ratified at their stated leans. Recon: specs/id-115-data-api-schema-isolation/notes/data-api-schema-isolation-recon.md. Surface inventory: /tmp/claude/data-api-surface.md.

Each invariant is numbered, testable, and carries its verification. Categories: security, behavior-preservation, maintenance, provisioning. These are the acceptance gates the TECH design (data-api-isolation-TECH.md) maps its changes onto, and the PLAN (data-api-isolation-PLAN.md) sequences to satisfy.

---

INV-1 — public is not exposed (PGRST106 boundary) · security

The public schema is NOT in the project’s PostgREST exposed-schemas list on local, staging, and prod. Any anon/authenticated/service-role Data API request naming a public object — e.g. .from('content_items') without an api view, or .schema('public').from(...) from a non-service client — returns PostgREST error PGRST106 (schema not exposed), not data.

Verification: Negative integration test — a raw PostgREST request (or supabase-js with .schema('public')) against content_items and at least one RPC asserts HTTP 406 / PGRST106. Plus a config assertion that supabase/config.toml [api] schemas does not contain public, and the remote exposed-schemas (read via Management API / dashboard) matches.

INV-2 — api is the only exposed app schema · security

The api schema is the ONLY schema exposed to the Data API for application objects: config.toml [api] schemas = ["api"] (with graphql_public dropped per decision 5, public removed). anon + authenticated hold USAGE on api only via migration, not dashboard DDL.

Verification: Config-file assertion on [api] schemas; a CI lint that fails if public or graphql_public reappear; a migration-presence check that create schema api + grant usage on schema api to anon, authenticated exist in supabase/migrations/.

INV-3 — every view is security_invoker (RLS preserved) · security

Every one of the 60 api views is created WITH (security_invoker = true), so RLS on the underlying public base table is enforced for the calling role. A non-owner authenticated user reading/writing through an api view sees and mutates only rows their base-table RLS policy permits — identical row visibility to the pre-switch public-exposed behaviour.

Verification: Supabase advisor lint 0010_security_definer_view returns zero findings (CI gate); a catalog query (pg_class.reloptions) asserts security_invoker=true on all api.* views; RLS behavioural test — authenticated user A cannot read/update workspace rows owned by user B through api.workspaces (the same assertion that passed against public.workspaces pre-switch).

INV-4 — every .from() target has an api view (incl. dynamic-only) · behavior-preservation

All 60 tables/views in the Data API surface — the 57 string-literal .from() targets PLUS the 3 dynamic-only tables signup_policy, tenant_config, content_propagation_version — have a corresponding api.<name> view, so every .from() call site (static and dynamic) resolves under the api default schema.

Verification: Catalog query asserts an api view exists for each of the 60 names (driven from an enumerated list that includes the 3 dynamic-only tables); a drift test maps every .from('literal') and each known dynamic resolution from the surface inventory to an existing api view.

INV-5 — every .rpc() has an api entrypoint with a satisfying signature · behavior-preservation

All 58 distinct RPCs in the .rpc() surface have a callable api.<fn> entrypoint whose signature (scalar/SETOF/TABLE return type and argument list) satisfies every call site. The 8 client-callable 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) are reachable by authenticated/anon as appropriate; the remaining server-side RPCs by service_role.

Verification: Catalog query asserts an api function exists for each of the 58 names; a per-RPC smoke test (or the existing MCP/route suite re-run under the api default schema) exercises each entrypoint and asserts a non-error response shape matching the pre-switch result.

INV-6 — DEFINER RPCs reached only via thin api invoker wrappers · security

The ~7 SECURITY DEFINER functions that are .rpc()-reachable (q_a_search, q_a_get_verbatim, question_match_search, question_match_recompute, reference_search, reference_get_verbatim, reference_ingest) are exposed only via thin api SECURITY INVOKER wrappers that delegate to the public definer. The definer bodies remain in unexposed public and are never directly .rpc()-callable.

Verification: Catalog query asserts each of the 7 api wrappers has prosecdef=false (invoker) while the public target has prosecdef=true; negative test asserts .rpc('q_a_search') resolving to public.q_a_search directly returns PGRST106; functional test asserts the api wrapper returns the same result as the pre-switch direct call.

INV-7 — trigger/internal/test DEFINER functions are NOT exposed · security

SECURITY DEFINER trigger/internal/test functions (handle_new_user, rls_auto_enable, grant_standard_public_table_access, _test_*, etc.) are NOT exposed in api and have no api wrapper; they remain reachable only internally (triggers/event-triggers/migrations), never via the Data API.

Verification: Catalog query asserts none of the named definer trigger/internal/test functions exist as api.* objects; the advisor lint and the negative PGRST106 test cover their non-exposure.

INV-8 — 1:1 views are auto-updatable (writes preserved) · behavior-preservation

The 1:1 single-table api views are auto-updatable: .insert(), .update(), and .delete() through api.<view> propagate to the public base table and are gated by the base table’s RLS WITH CHECK/USING and column grants — no INSTEAD OF triggers required, and writes succeed/fail identically to the pre-switch public path.

Verification: Write-path integration test through api.content_items and a representative sample of the ~48 read+write tables: authenticated insert/update/delete succeeds for permitted rows and is rejected (RLS violation) for non-permitted rows, matching pre-switch assertions.

INV-9 — explicit column lists, superset of call-site columns · behavior-preservation

api views use explicit column lists (not SELECT *) and are regenerated via DROP/CREATE. The projected columns of each view are a superset of (or equal to) the columns actually read/written by its call sites, so no call site loses a column after the switch. Generated/identity columns (e.g. content_items.content_text_hash) are included as plain passthrough columns — selectable but never required on insert.

Verification: Generator-output check — each api view DDL enumerates columns; a column-coverage test cross-references view columns against columns referenced in .select()/.insert()/.update() for that table (or, minimally, against the public base-table column set) and fails on any missing column; gen-types output (INV-13) compiling is a backstop.

INV-10 — least-privilege per-view grants; base grants retained · security

Per-view Data API grants are least-privilege: anon holds SELECT only; authenticated and service_role hold SELECT, INSERT, UPDATE, DELETE on each api view as required by that view’s read/write mode (the 9 READ-only tables grant no write to anon and only the writes their call sites need). Base-table grants in public (via grant_standard_public_table_access) are RETAINED because security_invoker requires underlying-table privileges.

Verification: Repurposed revoke-guard / api-grant lint (decision 4) asserts the grant matrix per api view (anon=SELECT, authenticated/service_role=CRUD-as-needed) and fails on over-grant (e.g. anon INSERT); catalog query confirms public base tables still carry their standard grants.

INV-11 — all client factories thread a shared db.schema='api' · behavior-preservation

All five client factory groups (browser lib/supabase/client.ts; SSR + service-role lib/supabase/server.ts; MCP lib/mcp/auth.ts; ~37 inline-createClient scripts) plus scripts/bid_worker.py thread a shared { db: { schema: 'api' } } (Python ClientOptions(schema='api')) default, so .from('x')→api.x and .rpc('y')→api.y with no per-call-site rewrites — including the 22 dynamic .from(variable) sites. Test/e2e/eval clients that bypass the factories also thread the option (see INV-21).

Verification: Static assertion/test that each factory constructs its client with db.schema='api'; an ast-dataflow/grep check that no call site sets a conflicting per-call schema; the dynamic-site coverage is proven by INV-4’s drift test resolving all 22 variable targets to api views.

INV-12 — public escape hatch only on server/service client · security

Rare direct-admin/service paths that must reach public do so ONLY via an explicit .schema('public') override on the server/service-role client (never anon/authenticated), and such overrides are enumerated and intentional — anon/authenticated clients have no public escape hatch.

Verification: Grep/ast-dataflow inventory of .schema('public') invocations asserts each is on a service-role or server client in an admin-guarded/cron/pipeline path; a test asserts no browser/anon client invokes .schema('public').

INV-13 — gen-types are --schema public,api and the app type-checks · maintenance

Generated TypeScript types are produced with supabase gen types typescript --schema public,api (both schemas explicit), and the app type-checks (tsc) cleanly against the api-schema client default — Database['api'] carries the view/RPC row shapes the code consumes, and Tables<'x'>/z.infer usages remain sound.

Verification: bun build / tsc --noEmit passes post-regen; a check asserts the gen-types command/string in supabase/CLAUDE.md and any regen script includes --schema public,api; CI fails if database.types.ts lacks an api schema key.

INV-14 — pipeline / auth / storage are unaffected · behavior-preservation

The cocoindex ingestion pipeline (scripts/cocoindex_pipeline/*, asyncpg direct-to-Postgres writing public.* via search_path), GoTrue auth (supabase.auth.*), and Storage (supabase.storage.from(documents|templates|branding)) are UNAFFECTED by the Data API schema switch and continue to operate against their existing targets.

Verification: Python pipeline tests (python3 -m pytest scripts/tests/) pass unchanged; an auth login E2E and a storage upload/download E2E pass post-switch; grep confirms no asyncpg/auth/storage path was rerouted through api.

INV-15 — the posture is fully migration+config reproducible · provisioning

The entire posture (schema api + USAGE grants + 60 views + 58 RPC entrypoints/wrappers + least-privilege grants) is reproduced by checked-in MIGRATIONS plus supabase/config.toml, not dashboard DDL. supabase db reset --local rebuilds it from zero, and the migration is idempotent/replayable so staging, prod, the re-ingest preview branch, and Platform converge with no manual dashboard step — closing the un-migrated-DDL drift class that broke S118/S176.

Verification: supabase db reset --local exits 0 and produces all api objects; schema-parity workflow (schema-parity.yml) shows prod==staging after apply; absence-check that the change is in supabase/migrations/ + config.toml (not only a dashboard mutation); migration replays cleanly twice (idempotency).

INV-16 — CI drift-check keeps the surface honest; generator idempotent · maintenance

A CI drift-check fails the build if any public base table reachable by the app lacks a corresponding api view (or any .rpc()-surface function lacks an api entrypoint), keeping the surface honest as in-flight tasks ID-50/70/71/104 continue adding tables and RPCs. The view/entrypoint generator is idempotent (re-running produces no diff).

Verification: A drift-check script (new or repurposed revoke-guard) enumerates public base tables + .rpc surface and asserts 1:1 api coverage, exiting non-zero on a gap; running the generator twice yields an empty git diff (idempotency test).

INV-17 — ensure_rls event trigger retained · maintenance

The ensure_rls event trigger / rls_auto_enable() is RETAINED (decision 3): any newly created public base table automatically gets RLS enabled, so the defence-in-depth row gate is never silently absent on tables added by ID-104/70/71 et al.

Verification: Catalog query asserts the ensure_rls event trigger exists post-switch; a test creates a new public table in a transaction and asserts relrowsecurity=true was auto-set.

INV-18 — ID-70 RPCs built to FINAL RETURNS TABLE signature · behavior-preservation

The 5 RPCs being changed by ID-70 from RETURNS Json to RETURNS TABLE (get_user_tag_counts, get_workspace_counts, merge_entities, get_dashboard_attention_counts, get_filter_counts) get their api entrypoints built to the FINAL RETURNS TABLE signature (sequenced with/after ID-70), avoiding a double-wrap and a redundant gen-types regen. The api-schema type-gen change is folded into the ID-70 / ID-64.8-cutover regen rather than adding an extra regen pass.

Verification: Signature catalog query asserts these 5 api entrypoints return the TABLE shape (not json); a sequencing check that the api-schema --schema public,api regen commit coincides with the ID-70/cutover regen (single database.types.ts rewrite, no intermediate json-shape generation).

INV-19 — atomicity per remote (no half-applied window) · security

At each remote, the exposed-schemas flip ([api] schemas), the api objects (views+functions+grants), and the client db.schema='api' switch land together, so no window exists where reads 404/PGRST106 against a half-applied state. On prod (already half-flipped: exposed-schemas changed, api empty) applying the migration COMPLETES the flip and restores Data API function.

Verification: Staging/prod post-deploy smoke test (login + dashboard read + content read+write + one MCP tool + one client RPC) returns 200/data not PGRST106; a deploy-ordering check (runbook/CI) ties the migration apply, config change, and client-code deploy to the same release.

INV-20 — definer grant hygiene does not regress · security

The retired per-function REVOKE EXECUTE ... FROM anon discipline does NOT regress security: sensitive public SECURITY DEFINER functions retain light defence-in-depth REVOKEs (they run as postgres with bypassrls even when reached indirectly), and set_config remains the only intentionally anon-executable allow-listed function.

Verification: Catalog query asserts set_config is the sole anon-EXECUTE-granted public function and that named sensitive definers (q_a_, reference_) retain their REVOKEs; the repurposed lint covers this in place of the old per-migration REVOKE rule.

INV-21 — embedded selects, upserts, and counts work through views · behavior-preservation

PostgREST resource embedding, onConflict upserts, and count/head:true requests that worked against public continue to work through the api views and resolve to the same data:

• Every FK column is present verbatim (no alias/expr) in each view so PostgREST view-relationship inference fires; multi-hop embeds (dashboard/reorient 3-level chains) and self-joins (content_items.parent_id/superseded_by) resolve, OR are served by an explicit api computed-relationship function / retained DEFINER RPC.
• onConflict upserts (28 call sites) arbitrate against the base-table unique index through the auto-updatable view, OR are converted to an api RPC / .schema('public') service path.
• count-with-embed requests (e.g. the intelligence metrics route) return correct counts.
• The generated api Views carry Relationships so nested-select result types are inferred (not never/unknown).

Verification: A dedicated embedding/upsert/count smoke set runs against a local db reset and asserts no PGRST200 (relationship), no 42P10 (conflict-target), and correct counts — table-by-table across the 39+ embed sites, 28 upsert sites, and the count-with-embed sites; a post-regen type check confirms embedded fields are typed (not never). This is the highest-blast-radius gate and runs before any remote apply.