Supabase Schema Consultant

An agent specialized in Supabase database schema design, migration strategies, and Row Level Security implementation for building secure, well-structured PostgreSQL databases through Supabase's platform.

When to Use This Agent

Choose Supabase Schema Consultant when:

• Designing PostgreSQL schemas for Supabase projects
• Implementing Row Level Security (RLS) policies for multi-user applications
• Creating and managing database migrations through Supabase CLI
• Designing real-time subscription schemas using Supabase Realtime
• Integrating Supabase Auth with database-level security policies

Consider alternatives when:

• Working with self-hosted PostgreSQL without Supabase (use a PostgreSQL agent)
• Building Supabase Edge Functions without database changes (use a Deno agent)
• Using a different BaaS platform like Firebase (use a Firebase agent)

Quick Start

Copy
# .claude/agents/supabase-schema-consultant.yml
name: Supabase Schema Consultant
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a Supabase schema design expert. Design PostgreSQL
  schemas with proper RLS policies, efficient indexes, and
  migration strategies. Integrate Supabase Auth with database
  security. Optimize for Supabase's real-time and API features.

Example invocation:

Copy
claude --agent supabase-schema-consultant "Design a schema for
  a team collaboration app with workspaces, channels, messages,
  and file attachments. Include RLS policies so users can only
  see data from their own workspaces."

Core Concepts

RLS Policy Design

Copy
-- Enable RLS on table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Users can read messages from their workspace
CREATE POLICY "Users read workspace messages"
ON messages FOR SELECT
USING (
  workspace_id IN (
    SELECT workspace_id FROM workspace_members
    WHERE user_id = auth.uid()
  )
);

-- Users can insert messages in their workspace
CREATE POLICY "Users create messages in workspace"
ON messages FOR INSERT
WITH CHECK (
  workspace_id IN (
    SELECT workspace_id FROM workspace_members
    WHERE user_id = auth.uid()
  )
  AND user_id = auth.uid()
);

-- Users can update only their own messages
CREATE POLICY "Users update own messages"
ON messages FOR UPDATE
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

Supabase Auth Integration

Copy
-- auth.uid() returns the current user's ID
-- auth.jwt() returns the full JWT claims

-- RLS with role-based access
CREATE POLICY "Admins manage all workspace data"
ON workspace_settings FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM workspace_members
    WHERE workspace_id = workspace_settings.workspace_id
    AND user_id = auth.uid()
    AND role = 'admin'
  )
);

-- Profile auto-creation on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO profiles (id, email, name)
  VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'name');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

Migration Best Practices

Practice	Example
Descriptive names	20240314_add_workspace_channels.sql
Always reversible	Include both up and down SQL
RLS with schema	Add policies in same migration as tables
Seed separately	Don't mix schema and data migrations
Test locally first	supabase db reset before pushing

Configuration

Parameter	Description	Default
rls_default	Enable RLS on new tables automatically	true
auth_integration	Auto-integrate with Supabase Auth	true
realtime_enabled	Enable Realtime on new tables	false (opt-in)
migration_format	Migration file naming	Timestamp prefix
id_type	Primary key type	UUID
audit_columns	Include created_at, updated_at	true
storage_integration	Link file references to Storage	true

Best Practices

1. Enable RLS on every table immediately after creation. A table without RLS in Supabase is publicly accessible through the API. Even if you plan to add policies later, enable RLS first with a deny-all default, then add permissive policies. This prevents accidental data exposure during development. Use ALTER TABLE ... ENABLE ROW LEVEL SECURITY in the same migration that creates the table.

2. Use auth.uid() in RLS policies, not application-level checks. Database-level security through RLS cannot be bypassed by client-side code, API manipulation, or application bugs. Application-level checks can always be circumvented. RLS policies enforce security at the PostgreSQL layer, meaning even direct SQL access through Supabase's SQL editor respects the policies.

3. Create helper functions for complex RLS policy logic. When multiple tables need the same authorization check (e.g., "is user a member of this workspace?"), create a SECURITY DEFINER function that encapsulates the logic. Reference this function in RLS policies. This approach keeps policies readable, makes authorization logic testable, and ensures consistency across tables.

4. Test RLS policies with different user contexts before deploying. Use SET LOCAL role = 'authenticated'; SET LOCAL request.jwt.claims = '{"sub":"user-id"}' to simulate different users in local testing. Verify that each policy allows intended access and blocks unintended access. Test edge cases: what happens when a user is removed from a workspace? Can they still read old messages?

5. Separate real-time enabled tables from high-write tables. Supabase Realtime broadcasts every change on enabled tables to subscribed clients. Enabling Realtime on a table with thousands of writes per second creates unnecessary overhead and client-side noise. Enable Realtime only on tables where clients need live updates (messages, presence, notifications) and keep high-write tables (logs, analytics) without it.

Common Issues

RLS policies cause query performance degradation. Complex RLS policies with subqueries execute on every row access, which can be slow on large tables. Optimize by creating indexes on the columns used in policy subqueries (e.g., index on workspace_members(user_id, workspace_id)). Use security_barrier views for complex policies and cache authorization results where possible.

Users can't access data they should be able to see. Debug by checking: Is RLS enabled? Are policies using the correct operation (SELECT, INSERT, UPDATE, DELETE)? Is auth.uid() returning the expected value? Use Supabase's SQL editor with SET LOCAL to simulate the user context and run the policy's USING clause directly. Common mistake: creating a SELECT policy but forgetting INSERT or UPDATE policies.

Trigger functions fail with permission errors. Functions called by triggers on auth.users need SECURITY DEFINER to access tables they wouldn't normally have permission for. However, use SECURITY DEFINER sparingly because it executes with the function owner's permissions, bypassing RLS. Validate inputs within the function to prevent privilege escalation. Always set search_path explicitly in SECURITY DEFINER functions.