Supabase Advisor — Accepted Warnings

Living document of database advisor (Performance + Security) warnings that have been reviewed and accepted as intentional. This list lets reviewers distinguish known-noise from new findings on every advisor run.

The Supabase advisor does not support per-function suppression today (see discussion #41197). Until it does, this file is the source of truth.

How to use

When you run the advisor (Dashboard → Advisors, or supabase db lint --linked):

1. Cross-reference each finding against the tables below.
2. Anything listed here is intentional — move on.
3. Anything NOT listed must be either fixed or evaluated and added here with rationale (in the same PR that decides to accept it).

Process for accepting a new warning

1. Confirm the function/table actually does what the advisor describes.
2. Confirm there's no app-side fix possible (e.g. revoking EXECUTE doesn't break a real caller, the table doesn't have an obvious missing index, RLS really should be off).
3. Add a row to the relevant table below with:
4. The exact lint code (e.g. 0029_authenticated_security_definer_function_executable)
5. The function/table identifier
6. Why it's intentional (one sentence)
7. The PR or commit that decided it
8. If the rationale changes (e.g. "we'll fix this when we refactor X"), update the row — don't leave stale "we should fix this someday" entries.

---

Currently accepted

Lint 0028 — Public Can Execute SECURITY DEFINER Function (anon)

Function	Why accepted	Decided in
public.check_phone_exists(p_phone_number text)	Login page calls it pre-auth to check whether a phone is already registered. Anon EXECUTE is intentional.	DEV-558 PR #239

Lint 0029 — Signed-In Users Can Execute SECURITY DEFINER Function (authenticated)

Function	Why accepted	Decided in
public.add_days_to_phase(...)	Performs ALTER TABLE … DISABLE TRIGGER on production_phases, which requires table-owner privileges. DEFINER is the only viable mechanism. RLS still gates the caller's overall write rights on the phase — DEFINER only bypasses the trigger-management restriction. Called from services/productionPhase/actions.ts.	DEV-558 PR #239
public.row_is_visible_to_caller(text, uuid, jsonb, uuid)	Single generic G1 visibility predicate covering both RLS (auth.uid()) and service_role callers. Returns TRUE unless a sensitive_rules entry with non-empty required_permissions matches the row itself or one of its ancestors AND the caller's clearance for the rule's own scope does not contain the required key. p_user_id (4th arg, defaults NULL) selects between get_user_clearance(auth.uid(), ...) and get_user_clearance_for_user(p_user_id, ...). The rule's denormalised organization_id / project_id decides which scope to consult. MUST be DEFINER: called from RLS policies on tables that are themselves RLS-gated; INVOKER would tunnel through sensitive_rules's own SELECT policy creating recursion. Replaces the DEV-579 visibility chain (entity_is_visible_to_caller, project_relationship_is_visible_to_caller, budget_header_is_visible_to_caller, budget_item_is_visible_to_caller, record_is_visible, record_is_visible_to_user, user_is_visible_to_caller) and the 4 *_row_is_sensitive write-gate helpers, all dropped in Phase 4. Adding a new sensitive-eligible table = one new policy with that table's ancestor list — no new helpers.	DEV-587 PR TBD
public.get_user_clearance(scope_type, uuid)	Returns the flat set of permission keys the calling user holds in the given scope. UNION of direct grants and role-derived grants, filtered for status=Active / not expired / not revoked / role is*active. Returns text[] ('{}' when no grants). MUST be DEFINER: called from RLS policies on every gateable table starting Phase 4 of the sensitive-data rebuild. INVOKER would tunnel through user_accesses's own RLS, which gates on project membership — that's a different question and would silently return empty in some valid cases. Reads user_accesses + permissions + permission_role_links directly (NOT the v_user*\*\_permissions views) to avoid recursion. STABLE so the planner caches the result across all row checks in one query.	DEV-587 PR TBD
public.get_user_clearance_for_user(uuid, scope_type, uuid)	User-id-explicit twin of get_user_clearance. Same body and filters, parameterised on user_id instead of auth.uid(). Used by server-side paths under service_role (approval routing, digest cron) where auth.uid() is NULL. DEFINER for the same reason: avoid tunnelling through user_accesses RLS.	DEV-587 PR TBD
public.caller_has_permission(text[], uuid, boolean)	Generic per-call permission resolver used by RLS SELECT policies on approval_requests, transactions, projects, and attachments. Single array-keyed signature covers three patterns: '{}'::text[] = any active access on the project (no key filter); ARRAY['k'] = exact-key match; ARRAY['a','b'] = caller must hold at least one of the listed keys. MUST be DEFINER because the body reads v_user_permissions (→ user_accesses × permissions × permission_role_links × project_relationships), several of which are themselves RLS-gated — INVOKER would silently tunnel through user_accesses RLS and return false negatives for users whose access is granted via roles or through a sibling project_relationship. LANGUAGE plpgsql STABLE so Postgres memoises the result per (caller, args) within a query — measured 36% latency reduction on the approvals view in the May 19 perf pass (per-row v_user_permissions walk → single helper call). Pass p_check_org_cascade=TRUE when an org-scoped grant should imply visibility on the project's rows (approval pattern); FALSE for project-only (transactions/projects/attachments pattern).	this PR
public.caller_accessible_project_ids(text[], boolean)	Precomputed-set RLS pattern (Layer 3 of the budget RLS chain collapse): returns the uuid[] of project IDs the caller can access for the given permission keys ('{}' = any active access), evaluated ONCE per query instead of re-running caller_has_permission per row. Used by the SELECT policies on budget_headers, budget_items, budget_item_daily_allocations, budget_allocation_transaction_items, and production_phases — project_id = ANY (caller_accessible_project_ids(...)) is a single index probe on each table's project_id index. MUST be DEFINER for the same reason as caller_has_permission: it reads v_user_permissions which sits on RLS-gated tables; INVOKER would silently tunnel and return false negatives for role-derived grants. LANGUAGE sql STABLE PARALLEL SAFE so the planner memoises the result for the lifetime of the query.	this PR
public.is_organization_creator(uuid, uuid)	Mirror of is_project_creator for organization scope. Called from the WITH CHECK / USING clauses on user_accesses INSERT / UPDATE / DELETE so the org creator can bootstrap themselves as organization_owner and keep managing grants. MUST be DEFINER (unlike is_project_creator, which is INVOKER): the organizations SELECT policy routes through v_user_organization_permissions → v_user_permissions, which LEFT JOINs project_relationships (whose SELECT policy EXISTS-into user_accesses). Under INVOKER, the lookup on organizations re-enters that chain while user_accesses is mid-mutation and Postgres aborts with 42P17 infinite recursion detected in policy for relation "user_accesses". DEFINER bypasses RLS on the lookup; the semantic check (caller owns created_by_user_id) is preserved by the explicit p_user_id argument the policy passes. is_project_creator stays INVOKER because the projects SELECT policy short-circuits on created_by_user_id = auth.uid().	this PR
public.build_fts_query(text)	Pure tsquery string builder — takes user search input, returns a sanitised to_tsquery-compatible string. No data access, no privileged operations. SECURITY DEFINER because it lives alongside other FTS helpers that historically were DEFINER, but is genuinely safe to expose to authenticated: any signed-in user can already build the same tsquery client-side. Granted to authenticated because it is called from query_transactions (INVOKER) on every global search; without the grant the RPC failed with permission denied for function build_fts_query → UI surfaced as the generic Error querying transactions.	this PR

Other lints

None currently accepted.