Supabase Patterns

Supabase Patterns Skill

Universal patterns for working with Supabase in any project. Covers RLS policies, realtime, storage, edge functions, and migrations.

Design Principle

This skill is framework-generic. It provides universal Supabase patterns:

• NOT tailored to Book-Vetting, ocr-service, or any specific project
• Covers common patterns applicable across all Supabase projects
• Project-specific configurations go in project-specific skills

Variables

Instructions

MANDATORY - Follow the Workflow steps below in order.

1. Check Supabase project configuration 2. Review existing RLS policies 3. Follow security-first patterns 4. Keep migrations organized

Red Flags - STOP and Reconsider

If you're about to:

• Create a table without RLS policies
• Use service role key in client-side code
• Skip migrations for schema changes
• Expose sensitive data in realtime

STOP -> Add RLS policies -> Use appropriate keys -> Then proceed

Cookbook

RLS Policies

• IF: Creating or modifying RLS policies
• THEN: Read and execute ./cookbook/rls-policies.md

Realtime Subscriptions

• IF: Setting up realtime features
• THEN: Read and execute ./cookbook/realtime-subscriptions.md

Storage Patterns

• IF: Working with Supabase Storage
• THEN: Read and execute ./cookbook/storage-patterns.md

Quick Reference

Project Structure

supabase/
├── config.toml           # Project config
├── migrations/           # SQL migrations
│   ├── 20231201000000_initial.sql
│   └── 20231202000000_add_users.sql
├── seed.sql             # Seed data
└── functions/           # Edge functions
    └── hello/
        └── index.ts

Key Commands

# Initialize project
supabase init

# Start local development
supabase start

# Generate migration
supabase migration new my_migration

# Push to remote
supabase db push

# Generate types
supabase gen types typescript --local > types/supabase.ts

RLS Policy Patterns

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- User owns row
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- User can insert own
CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Public read
CREATE POLICY "Public read"
  ON posts FOR SELECT
  USING (is_public = true);

Client Patterns

// Initialize client
import { createClient } from '@supabase/supabase-js';
import type { Database } from './types/supabase';

const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
);

// Query with types
const { data, error } = await supabase
  .from('posts')
  .select('*')
  .eq('user_id', userId);

// Insert
const { data, error } = await supabase
  .from('posts')
  .insert({ title, content, user_id: userId })
  .select()
  .single();

Realtime Pattern

// Subscribe to changes
const subscription = supabase
  .channel('posts')
  .on(
    'postgres_changes',
    { event: '*', schema: 'public', table: 'posts' },
    (payload) => {
      console.log('Change:', payload);
    }
  )
  .subscribe();

// Cleanup
subscription.unsubscribe();

Storage Pattern

// Upload file
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(`${userId}/avatar.png`, file, {
    upsert: true,
    contentType: 'image/png'
  });

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('avatars')
  .getPublicUrl(`${userId}/avatar.png`);

Security Checklist

Before Production

• [ ] RLS enabled on ALL tables
• [ ] Service role key NOT in client code
• [ ] Anon key for public operations only
• [ ] Storage buckets have policies
• [ ] Sensitive columns excluded from realtime
• [ ] API rate limiting configured
• [ ] CORS properly configured

RLS Checklist

• [ ] Every table has RLS enabled
• [ ] SELECT policies defined
• [ ] INSERT/UPDATE/DELETE policies defined
• [ ] Policies tested with different roles
• [ ] No overly permissive policies

Integration

With Schema Alignment

Supabase migrations should align with ORM models:

-- supabase/migrations/20231201000000_users.sql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

Should match:

# SQLAlchemy model
class User(Base):
    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    email: Mapped[str] = mapped_column(unique=True)
    name: Mapped[str | None]
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

Type Generation

# Generate TypeScript types from local schema
supabase gen types typescript --local > types/supabase.ts

# Use in client
import type { Database } from './types/supabase';
type Post = Database['public']['Tables']['posts']['Row'];

Best Practices

1. RLS first: Always add RLS policies when creating tables 2. Migrations for everything: Never modify schema directly 3. Type safety: Generate and use TypeScript types 4. Key hygiene: Use anon key client-side, service key server-side only 5. Test policies: Test RLS with actual user contexts 6. Realtime carefully: Only enable for tables that need it

RLS Policies Cookbook

Row Level Security (RLS) policies for Supabase PostgreSQL.

Basic Patterns

Enable RLS

-- Always enable RLS first
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owner too (recommended)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

User Owns Row

-- Users can only see their own rows
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

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

-- Users can only delete their own rows
CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);

Insert with Ownership

-- Users can insert rows owned by themselves
CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Public Read, Owner Write

-- Anyone can read public posts
CREATE POLICY "Public read"
  ON posts FOR SELECT
  USING (is_public = true);

-- Owners can read all their posts (including private)
CREATE POLICY "Owner read all"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Only owners can update
CREATE POLICY "Owner update"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id);

Advanced Patterns

Role-Based Access

-- Check user role from profiles table
CREATE POLICY "Admins can view all"
  ON posts FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Or using JWT claims
CREATE POLICY "Admin access via JWT"
  ON posts FOR ALL
  USING (auth.jwt() ->> 'role' = 'admin');

Team/Organization Access

-- Members of the same team can see each other's posts
CREATE POLICY "Team members can view"
  ON posts FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members
      WHERE user_id = auth.uid()
    )
  );

-- Team admins can update any team post
CREATE POLICY "Team admins can update"
  ON posts FOR UPDATE
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = posts.team_id
      AND team_members.user_id = auth.uid()
      AND team_members.role = 'admin'
    )
  );

Soft Delete Protection

-- Users can only see non-deleted posts
CREATE POLICY "Exclude deleted"
  ON posts FOR SELECT
  USING (
    auth.uid() = user_id
    AND deleted_at IS NULL
  );

-- Soft delete (update deleted_at) instead of hard delete
CREATE POLICY "Soft delete only"
  ON posts FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (
    -- Only allow setting deleted_at
    auth.uid() = user_id
  );

Time-Based Access

-- Posts only visible after publish date
CREATE POLICY "Published posts"
  ON posts FOR SELECT
  USING (
    published_at <= now()
    OR auth.uid() = user_id
  );

Security Functions

Create Helper Functions

-- Check if current user is admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS boolean AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM profiles
    WHERE id = auth.uid()
    AND role = 'admin'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Use in policy
CREATE POLICY "Admin access"
  ON sensitive_data FOR ALL
  USING (is_admin());

Get Current User's Team

CREATE OR REPLACE FUNCTION get_user_team_ids()
RETURNS uuid[] AS $$
BEGIN
  RETURN ARRAY(
    SELECT team_id FROM team_members
    WHERE user_id = auth.uid()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Use in policy
CREATE POLICY "Team access"
  ON team_posts FOR SELECT
  USING (team_id = ANY(get_user_team_ids()));

Testing Policies

Test as Different Users

-- Test as anonymous
SET request.jwt.claims = '{}';
SELECT * FROM posts;  -- Should see only public

-- Test as specific user
SET request.jwt.claims = '{"sub": "user-uuid-here"}';
SELECT * FROM posts;  -- Should see user's posts

-- Reset
RESET request.jwt.claims;

Debug Policies

-- See which policies exist
SELECT * FROM pg_policies WHERE tablename = 'posts';

-- Check if RLS is enabled
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'posts';

Common Mistakes

Mistake 1: Forgetting WITH CHECK

-- BAD: Missing WITH CHECK allows inserting with any user_id
CREATE POLICY "Insert"
  ON posts FOR INSERT
  USING (auth.uid() = user_id);  -- USING doesn't apply to INSERT!

-- GOOD: Use WITH CHECK for INSERT
CREATE POLICY "Insert"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

Mistake 2: Overly Permissive

-- BAD: Allows any authenticated user
CREATE POLICY "Authenticated access"
  ON sensitive_data FOR ALL
  USING (auth.uid() IS NOT NULL);

-- GOOD: Check specific permissions
CREATE POLICY "Authorized access"
  ON sensitive_data FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM permissions
      WHERE user_id = auth.uid()
      AND resource = 'sensitive_data'
      AND can_access = true
    )
  );

Mistake 3: Not Enabling RLS

-- Table without RLS is open to all!
-- Always enable immediately after CREATE TABLE
CREATE TABLE posts (...);
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

Migration Pattern

-- migrations/20231201000000_add_posts_rls.sql

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Drop any existing policies (for idempotency)
DROP POLICY IF EXISTS "Users can view own posts" ON posts;
DROP POLICY IF EXISTS "Users can create posts" ON posts;
DROP POLICY IF EXISTS "Users can update own posts" ON posts;
DROP POLICY IF EXISTS "Users can delete own posts" ON posts;

-- Create policies
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (auth.uid() = user_id);

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

CREATE POLICY "Users can delete own posts"
  ON posts FOR DELETE
  USING (auth.uid() = user_id);