rpc

RPC-First Data Access

Every data operation is a function in the api schema. No .from(). No direct table queries. No views. The api schema is the only schema exposed via the Supabase Data API — tables in public are invisible. This applies to all code: frontend components, edge functions, webhooks, cron jobs, server routes — no exceptions.

// ❌ WRONG — .from() cannot reach tables (public schema is not exposed)
const { data } = await supabase.from("charts").select("*");

// ❌ ALSO WRONG — even with service role key, .from() won't reach public tables
const admin = createClient(url, secretKey, { db: { schema: "public" } });
const { data } = await admin.from("charts").select("*");

// ✅ CORRECT
const { data } = await supabase.rpc("chart_get_by_user");

// ✅ CORRECT — within withSupabase context
const { data } = await ctx.supabase.rpc("chart_get_by_user");
const { data } = await ctx.supabaseAdmin.rpc("chart_admin_cleanup");

Function Anatomy

Every api schema function follows this structure:

CREATE OR REPLACE FUNCTION api.chart_get_by_id(p_chart_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
DECLARE
  v_result jsonb;
BEGIN
  SELECT jsonb_build_object(
    'id', c.id,
    'name', c.name,
    'created_at', c.created_at
  ) INTO v_result
  FROM public.charts c
  WHERE c.id = p_chart_id;

  IF v_result IS NULL THEN
    RAISE EXCEPTION 'Chart not found: %', p_chart_id;
  END IF;

  RETURN v_result;
END;
$$;

Key rules:

• api. schema — all data access functions live here
• SECURITY INVOKER — runs as the caller; isolation RLS applies automatically
• Permission gate first — mutating RPCs PERFORM public.auth_verify_access('<entity>.<action>') as the first statement, then scope queries with WHERE tenant_id = (SELECT public._auth_tenant_id()). Permissions live in the RPC, not in RLS (which is isolation-only).
• SET search_path = '' — prevents search path injection
• Fully qualified names — public.charts, public._auth_*, public._internal_admin_* — never bare names
• Grant EXECUTE per function — every api RPC (default-deny, like tables; there is no schema-wide GRANT ON ALL FUNCTIONS). After the definition add REVOKE ALL ON FUNCTION api.<fn>(<arg-types>) FROM PUBLIC; then GRANT EXECUTE ON FUNCTION api.<fn>(<arg-types>) TO authenticated, service_role;. The REVOKE strips Postgres' built-in PUBLIC EXECUTE (so anon can't reach it); a forgotten grant fails fast (42501). Anon-callable RPC: GRANT … TO anon, authenticated, service_role and make it SECURITY DEFINER. api._admin_* (service_role-only): REVOKE … FROM PUBLIC, anon, authenticated; GRANT EXECUTE … TO service_role (also silences DEFINER lint 0028). See the Grants section in references/rpc_patterns.md.
• p_ prefix on parameters, v_ prefix on local variables