Build and secure Supabase database applications

Covers any task involving Supabase - Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues, and client libraries (supabase-js, @supabase/ssr) across Next.js, React, SvelteKit, Astro, and Remix. Six core principles anchor it: verify against the live changelog and docs rather than training data, since function signatures and config change between versions; verify every fix with a test query; stop and reconsider after 2-3 failed attempts rather than looping on the same command; check Data API exposure settings when a SQL-created table is unexpectedly inaccessible (separate from RLS, which controls row visibility once a table is already reachable); enable RLS on every table in any exposed schema (including public by default), with policies matching the real access model rather than a copy-pasted auth.uid() pattern; and run a security checklist for anything touching auth, RLS, views, storage, or user data. That checklist names specific Supabase traps: never use user_metadata/raw_user_meta_data in authorization decisions since it's user-editable and reaches auth.jwt() (use app_metadata/raw_app_meta_data instead); deleting a user doesn't invalidate existing tokens, so sign out or revoke sessions explicitly; never expose the service_role key in public clients (any Next.js NEXT_PUBLIC_ var reaches the browser); views bypass RLS by default (use security_invoker = true on Postgres 15+, or revoke anon/authenticated access on older versions); an UPDATE needs a SELECT policy too or it silently returns 0 rows with no error; auth.role() is deprecated in favor of the TO clause, and specifically breaks when anonymous sign-ins are enabled since anon users also carry the authenticated Postgres role; TO authenticated alone is authentication without authorization (a BOLA/IDOR risk) and must be paired with an ownership predicate; UPDATE policies need both USING and WITH CHECK or a user can reassign a row's ownership; SECURITY DEFINER functions bypass RLS entirely and should never be used just to silence a permission error, and any such function left in public is callable by every role by default unless moved to a non-exposed schema with its own auth.uid() check; and storage upsert specifically needs INSERT, SELECT, and UPDATE grants together, since INSERT alone lets uploads work but silently fails on file replacement.

create policy "example" on table_name for select
to authenticated
using ( (select auth.uid()) = user_id );

CLI usage always discovers commands via --help rather than guessing, since the CLI structure changes between versions; known gotchas include supabase db query needing CLI v2.79.0+ (fall back to MCP execute_sql or psql), supabase db advisors needing v2.81.3+ (fall back to MCP get_advisors), and always generating new migration files via supabase migration new <name> rather than inventing a filename. The MCP server (https://mcp.supabase.com/mcp) is troubleshot in order: check reachability (a 401 without a token means it's up), verify .mcp.json points at the right URL, then complete the OAuth 2.1 auth flow if tools still aren't visible. Documentation lookup prefers the MCP search_docs tool first, then fetching any docs page as markdown by appending .md, then web search as a last resort. For schema changes, execute_sql (MCP) or supabase db query (CLI) run SQL directly for free iteration without migration history entries - apply_migration is explicitly wrong for this since it writes a history entry on every call, locking in whatever SQL was passed first. Once ready to commit: run advisors and fix issues, re-check the security checklist for views/functions/triggers/storage changes, generate the migration with supabase db pull <name> --local --yes, then verify with supabase migration list --local.

Use it for any Supabase task - database, auth, edge functions, realtime, storage, vectors, cron, queues, client library integration, or auth issues like login/logout flows.

Input is a Supabase task (schema change, RLS policy, auth flow, CLI/MCP setup issue). Output is verified-against-current-docs implementation: correctly-scoped RLS policies, a clean migration generated only after iterating with execute_sql/db query, and an advisors-clean, security-checklist-passed change.

Uses the Supabase CLI (--help-discovered commands), the Supabase MCP server (search_docs, execute_sql, get_advisors) with OAuth 2.1 authentication, and Supabase's own changelog and docs (fetchable as markdown) as the source of truth over training data.

Developers working with Supabase who want to avoid its specific silent-failure security traps (RLS on views, UPDATE-needs-SELECT, SECURITY DEFINER exposure, storage upsert grants) and use the correct iterate-then-migrate schema workflow instead of locking in a first-draft migration.