# Database RPC/function references exist in schema

- **Pattern:** `ab-000040` (`ai-slop-hallucinations.data-references.db-rpc-functions-exist`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000040
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000040)

## Why it matters

A hallucinated RPC function name is more disruptive than a hallucinated table name: Supabase `.rpc()` calls to non-existent functions return a PostgREST 404 error immediately rather than an empty result set, causing hard failures in dashboards, analytics, and any server action that delegates business logic to the database. This is a reference-integrity gap (OWASP A08) specific to Supabase projects where the AI correctly patterns the `.rpc()` call but invents the function name. Unlike missing tables, there is no partial fallback — the entire call fails, and the error message (`function get_dashboard_data() does not exist`) requires knowing to look in migration SQL to diagnose.

## Severity rationale

Critical because RPC calls to non-existent functions fail with an immediate 404 error — there is no silent fallback, and every caller of that code path is broken at runtime.

## Remediation

Verify the function name against your migration SQL, then either fix the call to match an existing function or create the missing function in a new migration.

```sql
-- supabase/migrations/042_add_dashboard_rpc.sql
CREATE OR REPLACE FUNCTION public.get_dashboard_data(p_user_id UUID)
RETURNS TABLE (metric TEXT, value BIGINT) AS $$
BEGIN
  RETURN QUERY
  SELECT 'submissions'::TEXT, COUNT(*)::BIGINT
  FROM submissions
  WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```

Apply with `supabase db push`. The function name in `.rpc('get_dashboard_data')` must match the `CREATE FUNCTION public.get_dashboard_data` name exactly — Supabase strips the `public.` prefix in RPC calls. Run `supabase gen types typescript` after adding the function to get type-checked RPC argument validation.

## Detection

- **ID:** `db-rpc-functions-exist`
- **Severity:** `critical`
- **What to look for:** Walk source files for `.rpc('function_name', ...)` and `.rpc("function_name", ...)` calls on the Supabase client. Extract the function name string literal from the first argument. Then verify each function name exists in `KNOWN_FUNCTIONS` (built from migration SQL in step 8 — all `CREATE [OR REPLACE] FUNCTION [public.]function_name(...)` statements, with `public.` prefix stripped). Count all `.rpc()` calls inspected, total resolved, total unresolved.
- **Pass criteria:** 100% of `.rpc()` function name arguments resolve to a function defined in migration SQL. Report: "X .rpc() calls inspected, Y resolved, 0 unresolved."
- **Fail criteria:** At least 1 `.rpc()` call references a function not defined in the migration files.
- **Do NOT pass when:** The function name is dynamically computed (`.rpc(fnName)` where `fnName` is a variable) — flag as "dynamic RPC name" with detail.
- **Skip (N/A) when:** No `@supabase/supabase-js` in dependencies, OR no `.rpc()` calls found in source files, OR no migration files exist in `supabase/migrations/`.
- **Report even on pass:** Report the count. Example: "3 .rpc() calls inspected, 3 resolved against 24 functions defined in migrations (100%)."
- **Detail on fail:** `"1 unresolved RPC reference: supabase.rpc('get_dashboard_data') in src/lib/analytics.ts — function 'get_dashboard_data' not found in supabase/migrations/. Available functions: get_segment_scores, get_total_issues_caught, increment_scrab_plays, ..."`
- **Remediation:** A hallucinated RPC function name causes Supabase to return a 404 error at runtime. Unlike table references (which may return empty data), RPC calls to non-existent functions fail loudly. Fix the function name to match a defined function, or create the missing function in a new migration:

  ```sql
  -- supabase/migrations/042_add_dashboard_rpc.sql
  CREATE OR REPLACE FUNCTION public.get_dashboard_data(p_user_id UUID)
  RETURNS TABLE (/* ... */) AS $$
  BEGIN
    -- implementation
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER;
  ```

  Run `supabase db push` to apply the migration, then verify the RPC works with `supabase functions invoke`.

---

## External references

- owasp A08

Taxons: reference-integrity

HTML version: https://auditbuffet.com/patterns/ab-000040