Supabase Integration Expert Skill

Overview

This skill helps you build secure, scalable Supabase integrations. Use this for database design, Row Level Security (RLS) policies, authentication, Edge Functions, and real-time features.

Core Principles

1. Security First

• Always enable RLS on tables with user data

• Use service role key only in secure server contexts

• Use anon key for client-side operations

• Test policies thoroughly

2. Type Safety

• Generate TypeScript types from schema

• Use generated types in application

• Keep types in sync with schema changes

3. Performance

• Use indexes for frequently queried columns

• Implement pagination for large datasets

• Use select() to limit returned fields

• Cache when appropriate

Database Schema Design

Basic Table Creation

-- Create a table with standard fields create table public.items ( id uuid default gen_random_uuid() primary key, created_at timestamp with time zone default timezone('utc'::text, now()) not null, updated_at timestamp with time zone default timezone('utc'::text, now()) not null, user_id uuid references auth.users(id) on delete cascade not null, title text not null, description text, status text default 'draft' check (status in ('draft', 'published', 'archived')) );

-- Create updated_at trigger create or replace function public.handle_updated_at() returns trigger as $$ begin new.updated_at = now(); return new; end; $$ language plpgsql;

create trigger set_updated_at before update on public.items for each row execute function public.handle_updated_at();

-- Create index create index items_user_id_idx on public.items(user_id); create index items_status_idx on public.items(status);

Foreign Keys & Relations

-- One-to-many relationship create table public.comments ( id uuid default gen_random_uuid() primary key, created_at timestamp with time zone default now() not null, item_id uuid references public.items(id) on delete cascade not null, user_id uuid references auth.users(id) on delete cascade not null, content text not null );

-- Many-to-many relationship create table public.item_tags ( item_id uuid references public.items(id) on delete cascade, tag_id uuid references public.tags(id) on delete cascade, primary key (item_id, tag_id) );

Row Level Security (RLS)

Basic RLS Patterns

-- Enable RLS alter table public.items enable row level security;

-- Users can read their own items create policy "Users can read own items" on public.items for select using (auth.uid() = user_id);

-- Users can insert their own items create policy "Users can insert own items" on public.items for insert with check (auth.uid() = user_id);

-- Users can update their own items create policy "Users can update own items" on public.items for update using (auth.uid() = user_id) with check (auth.uid() = user_id);

-- Users can delete their own items create policy "Users can delete own items" on public.items for delete using (auth.uid() = user_id);

Advanced RLS Patterns

-- Public read, authenticated write create policy "Anyone can read published items" on public.items for select using (status = 'published');

create policy "Authenticated users can insert" on public.items for insert to authenticated with check (true);

-- Role-based access create policy "Admins can do everything" on public.items for all using ( exists ( select 1 from public.user_roles where user_id = auth.uid() and role = 'admin' ) );

-- Shared access create policy "Users can read shared items" on public.items for select using ( auth.uid() = user_id or exists ( select 1 from public.item_shares where item_id = items.id and shared_with = auth.uid() ) );

Anonymous/Guest Access

-- Allow anonymous reads create policy "Anonymous can read public content" on public.items for select to anon using (status = 'published');

-- Allow anonymous inserts (for guest mode) create policy "Anonymous can create items" on public.items for insert to anon with check (true);

Client Integration

Setup Client (Next.js)

// lib/supabase/client.ts import { createBrowserClient } from '@supabase/ssr'

export function createClient() { return createBrowserClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! ) }

// lib/supabase/server.ts import { createServerClient } from '@supabase/ssr' import { cookies } from 'next/headers'

export function createServerClient() { const cookieStore = cookies()

return createServerClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!, { cookies: { get(name: string) { return cookieStore.get(name)?.value }, }, } ) }

CRUD Operations

// Query data const { data, error } = await supabase .from('items') .select('*') .eq('status', 'published') .order('created_at', { ascending: false }) .limit(10)

// Insert data const { data, error } = await supabase .from('items') .insert({ title: 'New Item', user_id: userId }) .select() .single()

// Update data const { data, error } = await supabase .from('items') .update({ title: 'Updated Title' }) .eq('id', itemId) .select() .single()

// Delete data const { error } = await supabase .from('items') .delete() .eq('id', itemId)

// Complex joins const { data, error } = await supabase .from('items') .select( *, comments ( id, content, user:user_id ( email ) ) ) .eq('user_id', userId)

Real-time Subscriptions

// Subscribe to changes const channel = supabase .channel('items-changes') .on( 'postgres_changes', { event: '*', schema: 'public', table: 'items', filter: user_id=eq.${userId}, }, (payload) => { console.log('Change received!', payload) // Update local state } ) .subscribe()

// Cleanup channel.unsubscribe()

Authentication

Email/Password Auth

// Sign up const { data, error } = await supabase.auth.signUp({ email: 'user@example.com', password: 'password123', options: { data: { display_name: 'User Name', }, }, })

// Sign in const { data, error } = await supabase.auth.signInWithPassword({ email: 'user@example.com', password: 'password123', })

// Sign out const { error } = await supabase.auth.signOut()

// Get current user const { data: { user } } = await supabase.auth.getUser()

OAuth Providers

// Google OAuth const { data, error } = await supabase.auth.signInWithOAuth({ provider: 'google', options: { redirectTo: ${window.location.origin}/auth/callback, }, })

// Handle callback // app/auth/callback/route.ts export async function GET(request: Request) { const { searchParams } = new URL(request.url) const code = searchParams.get('code')

if (code) { const supabase = createServerClient() await supabase.auth.exchangeCodeForSession(code) }

return NextResponse.redirect(new URL('/dashboard', request.url)) }

Auth Middleware

// middleware.ts import { createServerClient } from '@supabase/ssr' import { NextResponse } from 'next/server' import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) { const response = NextResponse.next()

const supabase = createServerClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!, { cookies: { get(name: string) { return request.cookies.get(name)?.value }, set(name: string, value: string, options: any) { response.cookies.set(name, value, options) }, remove(name: string, options: any) { response.cookies.set(name, '', { ...options, maxAge: 0 }) }, }, } )

const { data: { user } } = await supabase.auth.getUser()

// Redirect to login if not authenticated if (!user && request.nextUrl.pathname.startsWith('/dashboard')) { return NextResponse.redirect(new URL('/login', request.url)) }

return response }

export const config = { matcher: ['/dashboard/:path*'], }

Edge Functions

Basic Edge Function

// supabase/functions/hello/index.ts import { serve } from 'https://deno.land/std@0.168.0/http/server.ts' import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => { try { // Get Supabase client const supabase = createClient( Deno.env.get('SUPABASE_URL') ?? '', Deno.env.get('SUPABASE_ANON_KEY') ?? '', { auth: { autoRefreshToken: false, persistSession: false, }, } )

// Get user from auth header
const authHeader = req.headers.get('Authorization')
const token = authHeader?.replace('Bearer ', '')
const { data: { user } } = await supabase.auth.getUser(token)

if (!user) {
  return new Response(
    JSON.stringify({ error: 'Unauthorized' }),
    { status: 401, headers: { 'Content-Type': 'application/json' } }
  )
}

// Your logic here
const { data, error } = await supabase
  .from('items')
  .select('*')
  .eq('user_id', user.id)

return new Response(
  JSON.stringify({ data }),
  { headers: { 'Content-Type': 'application/json' } }
)

} catch (error) { return new Response( JSON.stringify({ error: error.message }), { status: 500, headers: { 'Content-Type': 'application/json' } } ) } })

Type Generation

Generate TypeScript types

npx supabase gen types typescript --project-id your-project-id > types/supabase.ts

Use in code

import { Database } from '@/types/supabase'

type Item = Database['public']['Tables']['items']['Row'] type ItemInsert = Database['public']['Tables']['items']['Insert'] type ItemUpdate = Database['public']['Tables']['items']['Update']

Common Patterns

Soft Deletes

alter table public.items add column deleted_at timestamp with time zone;

create policy "Users cannot see deleted items" on public.items for select using (deleted_at is null);

-- Soft delete function create or replace function soft_delete_item(item_id uuid) returns void as $$ begin update public.items set deleted_at = now() where id = item_id; end; $$ language plpgsql security definer;

Audit Logs

create table public.audit_logs ( id uuid default gen_random_uuid() primary key, created_at timestamp with time zone default now() not null, user_id uuid references auth.users(id), table_name text not null, record_id uuid not null, action text not null, changes jsonb );

-- Trigger function create or replace function public.audit_trigger() returns trigger as $$ begin insert into public.audit_logs (user_id, table_name, record_id, action, changes) values ( auth.uid(), TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(NEW) - to_jsonb(OLD) ); return NEW; end; $$ language plpgsql security definer;

Troubleshooting

Common Issues

• 401 Errors: Check RLS policies, ensure user is authenticated

• 403 Errors: RLS policy blocking operation

• Row not found: Policy may be filtering it out

• Connection issues: Check URL and API keys

• Type mismatches: Regenerate types after schema changes

Debugging RLS

-- Test as specific user set request.jwt.claims = '{"sub": "user-uuid-here"}';

-- Check what policies apply select * from pg_policies where tablename = 'items';

-- Disable RLS temporarily (for testing only!) alter table public.items disable row level security;

Best Practices Checklist

• Enable RLS on all tables with user data

• Create indexes for foreign keys and frequently queried columns

• Use UUID for primary keys

• Add created_at and updated_at timestamps

• Implement soft deletes for important data

• Use check constraints for enum-like fields

• Generate and use TypeScript types

• Test RLS policies thoroughly

• Use service role key only server-side

• Implement proper error handling

• Add audit logs for sensitive operations

• Use transactions for multi-step operations

When to Use This Skill

Invoke this skill when:

• Designing database schemas

• Creating or debugging RLS policies

• Setting up authentication

• Building Edge Functions

• Implementing real-time features

• Troubleshooting Supabase issues

• Optimizing database queries

• Setting up type generation