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 organisation_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_organisation_creator(uuid, uuid)	Mirror of is_project_creator for organisation scope. Called from the WITH CHECK / USING clauses on user_accesses INSERT / UPDATE / DELETE so the org creator can bootstrap themselves as organisation_owner and keep managing grants. MUST be DEFINER (unlike is_project_creator, which is INVOKER): the organisations SELECT policy routes through v_user_organisation_permissions → v_user_permissions, which LEFT JOINs project_relationships (whose SELECT policy EXISTS-into user_accesses). Under INVOKER, the lookup on organisations 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
public.caller_can_access_organisation_code(text)	True when the calling user holds an ORGANISATION-scoped permission grant on the organisation named by organisation_code (org-scope grants only — a purely project-scoped grant does NOT satisfy it; this is the deliberate fail-closed write scope, stricter than the project-scoped SELECT side). Called from the files storage-bucket INSERT policy on storage.objects to scope uploads to the org in the object path (organisations/<organisation_code>/...); the read-side attachment join cannot gate INSERT because the attachments row does not exist yet at upload time. MUST be DEFINER: the body reads v_user_accessible_organisations (→ v_user_organisation_permissions → user_accesses × permissions × permission_role_links), which is security_invoker and whose base-table RLS does NOT re-resolve when evaluated inside a storage.objects policy — an inlined EXISTS over the view returned true standalone but silently false inside the policy's WITH CHECK, so legitimate org members were blocked from uploading. DEFINER resolves it correctly, mirroring caller_has_permission / caller_accessible_project_ids. LANGUAGE sql STABLE. The semantic check (caller belongs to the org) is preserved by the organisation_code argument the policy passes from the object path.	this PR
public.get_record_effective_required_permissions(text, uuid)	Caller-independent UNION of the effective required-permission KEYS that gate a record (own row-level required_permissions + ancestor cascade resolving to the rule's scope :view key), mirroring row_is_visible_to_caller's own/ancestor/scope_filter matching but WITHOUT the per-caller clearance check. Fixes the masking views' historical blind spot where sensitive_required_permissions was computed via unnest(required_permissions) and so dropped cascade-only rules (a parent marked sensitive for its children via scope_filter, empty required_permissions on the parent). v_transactions / v_payments now delegate their sensitive_required_permissions column to it; approval routing reads it via the views. MUST be DEFINER for the same reason as row_is_visible_to_caller: it reads RLS-gated sensitive_rules (plus the row's own transactions / payments ancestor FKs); INVOKER would tunnel through sensitive_rules's own SELECT policy creating recursion. Granted to authenticated (and service_role) is safe — it returns only permission-key STRINGS derived from the rule catalogue + the row's own ancestor ids, never any gated row data, so it leaks nothing a caller couldn't already infer from has_sensitive_rule. LANGUAGE sql STABLE.	this PR
public.record_is_visible_to_user(text, uuid, uuid)	Per-user sensitivity visibility: resolves the record's cascade ancestors + subtype in-DB (same per-type CASE as get_record_effective_required_permissions) and delegates to row_is_visible_to_caller(..., p_user_id), so each rule is evaluated at its own scope (org rule → the user's org-scope clearance, project rule → project-scope) exactly like RLS. Exists for approval routing and recipient gating: an approver/recipient is eligible for a sensitive record iff they can see it — the previous app-side check flattened all rules into one union compared against project-scope clearance only, wrongly excluding org-scope grant holders (e.g. organisation:owner), and the old app helper passed an EMPTY ancestor list so cascade rules were ignored. MUST be DEFINER: the ancestor-building reads transactions / payments (RLS-gated) and must answer for a third-party user independent of the caller's own row visibility; it also sits on the DEFINER row_is_visible_to_caller. Granted to authenticated (and service_role) is safe — returns a single boolean, no row data; a nonexistent record id is indistinguishable from a non-sensitive one (both TRUE), so it is not an existence oracle. Residual accepted surface: an authenticated user who already knows a record UUID can probe whether a given user can see it (cross-user visibility boolean). Accepted because UUIDs are not enumerable, no row data is returned, and the equivalent surface already exists via get_record_effective_required_permissions (which reveals the required keys) + get_user_clearance (own clearance); revisit if record ids ever become guessable. anon revoked. LANGUAGE sql STABLE.	this PR
public.count_pending_processing_issues(text, uuid)	Returns the COUNT of pending + current processing_issues for one record. MUST be DEFINER: reading processing_issues under INVOKER runs its per-row SELECT policy, which re-derives the caller's entire permission set (user_accesses → project_relationships via row_is_visible_to_caller → permission_role_links → permissions) plus a nested EXISTS on processing_jobs calling caller_has_permission — production EXPLAIN ANALYZE showed ~4060 loops / 3.35s to annotate a single approval row. DEFINER skips that RLS. Safe to grant authenticated: every caller has already established visibility of the PARENT record (the v_user_pending_approvals view gates on row_is_visible_to_caller(record_type, record_id) before calling this), and the function returns only an integer COUNT for that already-visible record — no issue content. Used by v_user_pending_approvals. LANGUAGE sql STABLE.	DEV-749 PR #356
public.count_pending_issues_by_resolvability(text, uuid[])	Bulk twin of count_pending_processing_issues: per-record pending + current processing_issue counts split by submitter_resolvable. Same DEFINER rationale (skips the expensive per-row processing_issues RLS that made getTransactionsPaginatedDataOnly ~6s and getPendingIssuesByResolvabilityBulk ~5.5s in production). Safe to grant authenticated: callers pass only ids of records the RLS-bound list query already returned, so parent visibility is established; the function returns only COUNTS per already-visible record, no issue content. Used by getPendingProcessingIssuesCountBatch (transactions list) and getPendingIssuesByResolvabilityBulk. LANGUAGE sql STABLE.	DEV-749 PR #356

Other lints

None currently accepted.