Skip to content
Canonical

ID-115 {115.4} PLAN — Data API schema isolation (S1..S14)

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

Section titled “PLAN — Data API Schema Isolation (public unexposed → api exposed)”

Status: PLAN. Taskmaster-shape subtasks for data-api-isolation-TECH.md / data-api-isolation-PRODUCT.md. Recon: specs/id-115-data-api-schema-isolation/notes/data-api-schema-isolation-recon.md.

Dependencies are sibling-only (Sx → Sy within this list). Order respects the mandated flow: build api objects → flip config → switch clients → regen types → validate locally → staging → prod → bake into provisioning. R1 (embedding) is placed as a gating early subtask (S1). The gen-types chokepoint is S11. In-flight coordination: ID-70 (S6), ID-71 (S5/S8), ID-104 drift (S10), ID-50 test-suite (S9).

14 subtasks (S1–S14).

S1 — Embedding/upsert/count feasibility spike (GATING)

Section titled “S1 — Embedding/upsert/count feasibility spike (GATING)”
  • title: Prove FK-embedding, onConflict upserts, and count-with-embed survive api views
  • details (load-bearing): R1 + R2 are blockers; resolve BEFORE committing to a naive 1:1 view plan. Build a throwaway local api view set for the embed-heavy tables only — workspaces, application_types, content_items (self-join parent_id/superseded_by), form_responses, form_questions, taxonomy_domains, feed_articles — each with FK columns projected verbatim (no alias/expr). Run, against a local db reset: the 39+ embed sites (esp. the 3-hop chains lib/dashboard.ts:335, lib/reorient.ts:179/190, app/api/items/[id]/effectiveness/route.ts:103-110; the application_types!inner(key) ownership auth gate on ~25 procurement routes), the 28 .upsert() onConflict sites forcing the conflict (2nd-write) branch, and the count-with-embed metrics route (app/api/intelligence/workspaces/[id]/metrics/route.ts:63). For each failure decide the fallback: computed-relationship api function (SETOF related view, named to the embed hint), retained DEFINER RPC, or .schema('public') service path / api upsert RPC. Output: a decision table feeding S2/S4. Files: throwaway supabase/migrations/_spike_*.sql (not committed), notes in the ledger task.
  • testStrategy: Each embed/upsert/count query runs against local db reset with zero PGRST200 / 42P10 / wrong-count, or has a documented fallback.
  • dependencies: none

S2 — api schema migration + view generator

Section titled “S2 — api schema migration + view generator”
  • title: Generator-produced create schema api + 60 security_invoker views (explicit cols)
  • details: New scripts/generate-api-views.ts reads information_schema from local post-db reset and emits idempotent DROP/CREATE SQL. First migration <ts>_api_schema_create.sql: CREATE SCHEMA IF NOT EXISTS api + GRANT USAGE … TO anon, authenticated, service_role (captures prod’s manual DDL). Then <ts>_api_views_and_rpcs.sql views: explicit ordered column lists (never SELECT *), every FK column verbatim (S1), generated/identity columns (pg_attribute.attgenerated/attidentity) as passthrough-only, WITH (security_invoker = true) on every view, per-view grants (anon SELECT; authenticated/service_role CRUD). Table set = surface-list ∩ public BASE TABLEs, INCLUDING the 3 dynamic-only (signup_policy, tenant_config, content_propagation_version). Generator fails loudly on a READ+WRITE table that would emit a non-updatable view, and self-greps that every CREATE VIEW api. has the storage param. Apply S1 fallbacks (computed-relationship funcs) here.
  • testStrategy: supabase db reset --local exits 0; catalog query confirms 60 views all security_invoker=true; generator run twice yields empty git diff (INV-3/4/8/9/16).
  • dependencies: S1

S3 — api RPC entrypoints (INVOKER re-create) + DEFINER wrappers

Section titled “S3 — api RPC entrypoints (INVOKER re-create) + DEFINER wrappers”
  • title: 58 api.* RPC entrypoints; thin invoker wrappers over the 7 public definers
  • details: Extend the generator (or a cross-checked hand migration) to emit INVOKER RPC copies in api (SET search_path = public, extensions; EXECUTE grants to the calling roles — anon/authenticated for the 8 client RPCs, authenticated/service_role for server). For the 7 DEFINER RPCs (q_a_search, q_a_get_verbatim, question_match_search, question_match_recompute, reference_search, reference_get_verbatim, reference_ingest): thin api INVOKER wrappers EXECUTEing the public definer, signature/return derived from pg_get_function_identity_arguments + pg_get_function_result (param names/types/DEFAULTs verbatim; vector(1024) preserved; TABLE OUT cols copied). NO api object for trigger/internal/test definers. EXCLUDE RPCs ID-71 is retiring (cross-check its retirement set first). claim_next_job and the content-dedup hash RPC must be in the set. Run gitnexus_impact before touching any public function in place.
  • testStrategy: Catalog query: 58 api functions exist; the 7 wrappers prosecdef=false while targets prosecdef=true; per-wrapper parity diff vs public original matches (INV-5/6/7).
  • dependencies: S2

S4 — Embedding fallbacks + .schema('public') escape-hatch inventory

Section titled “S4 — Embedding fallbacks + .schema('public') escape-hatch inventory”
  • title: Land the S1 computed-relationships / RPC fallbacks and enumerate service public paths
  • details: Materialise every S1 fallback into the committed migrations: computed-relationship api SQL functions for unresolved embeds (self-joins, 3-hop chains), api upsert RPCs for onConflict sites that cannot arbitrate through the view, and the content-dedup hash RPC api wrapper. Enumerate every createServiceClient() write path (recon: 17 sites) and mark each as api-view vs requires .schema('public'). Document the .schema('public') allow-list (service/server clients only).
  • testStrategy: Re-run the S1 embed/upsert/count matrix against the committed migrations: zero PGRST200/42P10; enumerated .schema('public') sites all on service/server clients (INV-12/21).
  • dependencies: S2, S3

S5 — config.toml flip + remote-exposure runbook note

Section titled “S5 — config.toml flip + remote-exposure runbook note”
  • title: [api] schemas = ["api"] (drop public + graphql_public); keep extra_search_path
  • details: Edit supabase/config.toml [api]: schemas = ["api"]; leave extra_search_path = ["public","extensions"] unchanged (the boundary is the exposed-schemas set; search_path keeps unqualified public.* refs resolving). Document the remote dashboard / Management-API db_schema change for staging + prod (prod already done manually). Note the MCP exposed-surface coordinates with ID-71.
  • testStrategy: Config assertion that schemas == ["api"] and contains neither public nor graphql_public; local db reset + a PGRST106 negative read confirms the boundary (INV-1/2).
  • dependencies: S2

S6 — ID-70 RPC-signature coordination (SEQUENCING)

Section titled “S6 — ID-70 RPC-signature coordination (SEQUENCING)”
  • title: Build the 5 ID-70 RPC api entrypoints to the FINAL RETURNS TABLE signatures
  • details: ID-70 changes get_user_tag_counts, get_workspace_counts, merge_entities, get_dashboard_attention_counts, get_filter_counts from RETURNS json to RETURNS TABLE. Build their api entrypoints (S3) to the FINAL TABLE signature — do this slice WITH/AFTER ID-70, never before, else double-wrap + double regen. If ID-70 has not landed when S3 runs, gate these 5 here and hold the type-gen (S12) until both coincide.
  • testStrategy: Signature catalog query asserts these 5 api entrypoints return TABLE (not json); the type-gen commit coincides with ID-70/cutover (single database.types.ts rewrite) (INV-18).
  • dependencies: S3

S7 — Shared client option + 4 app factory groups

Section titled “S7 — Shared client option + 4 app factory groups”
  • title: lib/supabase/schema.ts DB_OPTION threaded through browser/SSR/service/MCP
  • details: New lib/supabase/schema.ts exporting API_SCHEMA='api' + DB_OPTION. Thread into createBrowserClient (client.ts:22), createClient + createServiceClient (server.ts:20/59), createMcpUserClient (auth.ts:23, inherited by createMcpClient/getMcpUserRole). Covers the 22 dynamic .from(variable) sites for free. Run gitnexus_impact on each factory first.
  • testStrategy: Static test that each factory sets db.schema='api'; ast-dataflow/grep asserts no conflicting per-call schema (INV-11).
  • dependencies: S2

S8 — Script factory + Python worker + MCP coordination

Section titled “S8 — Script factory + Python worker + MCP coordination”
  • title: scripts/lib/supabase-script-client.ts wrapper; bid_worker.py ClientOptions; MCP/ID-71
  • details: New scripts/lib/supabase-script-client.ts wrapping createClient with DB_OPTION; migrate the ~37 inline createClient scripts (empirical .ts count; e.g. backfill-layers.ts:115, kb-search.ts:300, seed-e2e-users.ts:181) — covers the 21 dynamic script sites. bid_worker.py:54 get_supabase() → create_client(url, key, options=ClientOptions(schema="api")) (redirects from_/rpc("claim_next_job"); storage unaffected). DDL-adjacent scripts opt out via .schema('public'). Switch the MCP client db.schema in concert with ID-71’s MCP changes (S7’s MCP edit lands with ID-71).
  • testStrategy: Grep asserts no remaining inline createClient without the wrapper/DB_OPTION; bid_worker.py smoke against local enqueues a job through api (INV-11/14).
  • dependencies: S7

S9 — Test/e2e/eval client threading (ID-50 COORDINATION)

Section titled “S9 — Test/e2e/eval client threading (ID-50 COORDINATION)”
  • title: Thread db.schema='api' through the 7+ factory-bypassing test/e2e/eval clients
  • details: Gap-hunt R4: clients outside the factories set no db.schema and hit the real staging DB — 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. Centralise e2e/fixtures/supabase.ts + __tests__/integration/helpers/supabase-client.ts so the option is set once; thread through all. Coordinate test-suite churn with ID-50 (369 test sites) + e2e (186) — land one sweep’s test changes before the other to avoid collisions.
  • testStrategy: Integration + e2e suites run green against staging post-cutover; no PGRST106 in test output (INV-11/21).
  • dependencies: S7

S10 — Repurpose revoke-guard (anti-self-block) + drift check

Section titled “S10 — Repurpose revoke-guard (anti-self-block) + drift check”
  • title: api-grant-guard.yml: security_invoker + least-privilege + no-definer + coverage drift
  • details: Repurpose scripts/check-revoke-guard.ts (reuse its SQL parser) IN THE SAME PR as the api objects to avoid the self-block (R7). Lint A: every CREATE VIEW api.<name> has security_invoker=true. Lint B: least-privilege grants (anon SELECT only; no anon write). Lint C: no CREATE FUNCTION api.<x> is SECURITY DEFINER; remove the old REVOKE-presence assertion firing on api.* wrappers. New --mode=drift (or scripts/check-api-view-coverage.ts): every public BASE TABLE in the surface has an api view (INV-16). Cron repointed to query api views lacking security_invoker + anon write grants + set_config sole anon-EXECUTE. Rename migration-revoke-guard.yml → api-grant-guard.yml; coordinate with schema-parity.yml + task-view-vendor-drift.
  • testStrategy: Guard passes on the slice’s own migration; intentionally-broken fixtures (view missing security_invoker; new public table without a view; anon INSERT grant) exit 1 (INV-2/3/10/16/20).
  • dependencies: S2, S3

S11 — gen-types regen (CHOKEPOINT) + tsc gate

Section titled “S11 — gen-types regen (CHOKEPOINT) + tsc gate”
  • title: Regen --schema public,api, audit Relationships, type-check
  • details: SHARED CHOKEPOINT — gen types is rewritten by ID-70, the ID-64.8 cutover, AND this switch. Fold the --schema public,api flag into whichever regen already happens (ID-70/cutover) — one regen, not three. Update the documented command in supabase/CLAUDE.md. Pin deterministic --schema ordering. Post-regen, AUDIT the api Views’ Relationships arrays (R6): if empty, apply the chosen fallback (view definition that infers relationships, hand-augment, or z.infer at the embed boundary). Verify Tables<'x'> usages still resolve to the intended schema (R5) and JSONB overrides reference still-existing public shapes. Runs AFTER the api objects + fallbacks are final (S2/S3/S4) and the ID-70 signatures are settled (S6), and BEFORE the validation matrix (types must exist for tsc/tests to pass).
  • testStrategy: bun build / tsc --noEmit passes; Database['api'] present; embedded fields typed (not never); regen-command check includes --schema public,api (INV-13/18/21).
  • dependencies: S2, S3, S4, S6

S12 — Local validation matrix

Section titled “S12 — Local validation matrix”
  • title: Run the full Phase-1 validation matrix on local db reset
  • details: Execute the TECH §(h) validation items against local (type-gen is owned by S11 — NOT repeated here): migration replays clean; all views invoker; advisor lint 0010 clean; ensure_rls event trigger + rls_auto_enable() still present post-reset and a fresh public table auto-gets relrowsecurity=true (INV-17 — the api migration must not disturb the retained auto-RLS machinery); app smoke (login/dashboard/content CRUD); the S1 embed/upsert/count set; MCP read+write tools; PGRST106 negative + api positive; 3 dynamic-only tables; 7 DEFINER-wrapper parity; bid_worker.py. Local gate before any remote (types already regenerated in S11).
  • testStrategy: All matrix rows pass; ensure_rls event trigger present + auto-RLS on a new table verified (INV-17); bun run test + python3 -m pytest scripts/tests/ green (INV-1/3/5/8/14/17/19/21).
  • dependencies: S4, S5, S6, S8, S9, S10, S11

S13 — Staging cutover + prod completion (atomic per remote)

Section titled “S13 — Staging cutover + prod completion (atomic per remote)”
  • title: Apply migration + config + client deploy together on staging, then complete prod
  • details: Phase 2 — apply migrations + config.toml + dashboard exposed-schemas on staging (turayklvaunphgbgscat) in lockstep with the client-code deploy (atomicity, INV-19); run full E2E + the post-deploy smoke (login + dashboard read + content read+write + one MCP tool + one client RPC → 200, not PGRST106). Phase 3 — apply the migration on prod (rovrymhhffssilaftdwd), which COMPLETES the already-half-flipped state (exposed-schemas changed, api empty) and restores Data API function; re-run smoke. schema-parity.yml must show prod==staging after apply.
  • testStrategy: Staging + prod post-deploy smoke returns 200/data not PGRST106; schema-parity green (INV-15/19).
  • dependencies: S12

S14 — Bake into provisioning

Section titled “S14 — Bake into provisioning”
  • title: Reproduce the posture in seed-tenant-from-bundle.ts + preview branch + Platform
  • details: Phase 4 — ensure the migrations + config.toml reproduce the full posture (schema + USAGE + 60 views + 58 entrypoints/wrappers + least-privilege grants) on the client re-ingest preview branch, Platform, and Platform staging via seed-tenant-from-bundle.ts with no manual dashboard step. Confirm the drift check (S10) runs in the provisioning CI. Optional migration squash timed with the ID-45 re-ingest recut (not a security prerequisite).
  • testStrategy: A fresh preview branch / Platform provision yields all api objects from migrations alone; no dashboard DDL required (INV-15).
  • dependencies: S13

Sequencing notes

Section titled “Sequencing notes”
  • gen-types chokepoint: S11 is the single regen; fold into ID-70 / ID-64.8 cutover, never an extra pass. S11 (gen-types) precedes S12 (validation) — types must exist before tsc/tests run.
  • ID-70 (S6): gate the 5 RETURNS TABLE api entrypoints with/after ID-70.
  • ID-71 (S5/S8): exclude retiring RPCs from S3; switch the MCP client db.schema with ID-71’s MCP changes.
  • ID-104 (S10): the coverage drift check keeps the surface honest as ID-104 adds eval_* / ai_call_events.
  • ID-50 (S9): coordinate test-suite churn (369 ID-50 sites + 186 e2e) — land one sweep before the other.
  • R1 (embedding, S1) is the gating early subtask — a naive 1:1 view plan that drops Relationships silently breaks the dashboard, intelligence feed, and the procurement workspace-gating auth routes.