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

sql
1-- Create a table with standard fields
2create table public.items (
3  id uuid default gen_random_uuid() primary key,
4  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
5  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
6  user_id uuid references auth.users(id) on delete cascade not null,
7  title text not null,
8  description text,
9  status text default 'draft' check (status in ('draft', 'published', 'archived'))
10);
11
12-- Create updated_at trigger
13create or replace function public.handle_updated_at()
14returns trigger as $$
15begin
16  new.updated_at = now();
17  return new;
18end;
19$$ language plpgsql;
20
21create trigger set_updated_at
22  before update on public.items
23  for each row
24  execute function public.handle_updated_at();
25
26-- Create index
27create index items_user_id_idx on public.items(user_id);
28create index items_status_idx on public.items(status);

Foreign Keys & Relations

sql
1-- One-to-many relationship
2create table public.comments (
3  id uuid default gen_random_uuid() primary key,
4  created_at timestamp with time zone default now() not null,
5  item_id uuid references public.items(id) on delete cascade not null,
6  user_id uuid references auth.users(id) on delete cascade not null,
7  content text not null
8);
9
10-- Many-to-many relationship
11create table public.item_tags (
12  item_id uuid references public.items(id) on delete cascade,
13  tag_id uuid references public.tags(id) on delete cascade,
14  primary key (item_id, tag_id)
15);

Row Level Security (RLS)

Basic RLS Patterns

sql
1-- Enable RLS
2alter table public.items enable row level security;
3
4-- Users can read their own items
5create policy "Users can read own items"
6  on public.items for select
7  using (auth.uid() = user_id);
8
9-- Users can insert their own items
10create policy "Users can insert own items"
11  on public.items for insert
12  with check (auth.uid() = user_id);
13
14-- Users can update their own items
15create policy "Users can update own items"
16  on public.items for update
17  using (auth.uid() = user_id)
18  with check (auth.uid() = user_id);
19
20-- Users can delete their own items
21create policy "Users can delete own items"
22  on public.items for delete
23  using (auth.uid() = user_id);

Advanced RLS Patterns

sql
1-- Public read, authenticated write
2create policy "Anyone can read published items"
3  on public.items for select
4  using (status = 'published');
5
6create policy "Authenticated users can insert"
7  on public.items for insert
8  to authenticated
9  with check (true);
10
11-- Role-based access
12create policy "Admins can do everything"
13  on public.items for all
14  using (
15    exists (
16      select 1 from public.user_roles
17      where user_id = auth.uid()
18      and role = 'admin'
19    )
20  );
21
22-- Shared access
23create policy "Users can read shared items"
24  on public.items for select
25  using (
26    auth.uid() = user_id
27    or exists (
28      select 1 from public.item_shares
29      where item_id = items.id
30      and shared_with = auth.uid()
31    )
32  );

Anonymous/Guest Access

sql
1-- Allow anonymous reads
2create policy "Anonymous can read public content"
3  on public.items for select
4  to anon
5  using (status = 'published');
6
7-- Allow anonymous inserts (for guest mode)
8create policy "Anonymous can create items"
9  on public.items for insert
10  to anon
11  with check (true);

Client Integration

Setup Client (Next.js)

typescript
1// lib/supabase/client.ts
2import { createBrowserClient } from '@supabase/ssr'
3
4export function createClient() {
5  return createBrowserClient(
6    process.env.NEXT_PUBLIC_SUPABASE_URL!,
7    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
8  )
9}
10
11// lib/supabase/server.ts
12import { createServerClient } from '@supabase/ssr'
13import { cookies } from 'next/headers'
14
15export function createServerClient() {
16  const cookieStore = cookies()
17
18  return createServerClient(
19    process.env.NEXT_PUBLIC_SUPABASE_URL!,
20    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
21    {
22      cookies: {
23        get(name: string) {
24          return cookieStore.get(name)?.value
25        },
26      },
27    }
28  )
29}

CRUD Operations

typescript
1// Query data
2const { data, error } = await supabase
3  .from('items')
4  .select('*')
5  .eq('status', 'published')
6  .order('created_at', { ascending: false })
7  .limit(10)
8
9// Insert data
10const { data, error } = await supabase
11  .from('items')
12  .insert({ title: 'New Item', user_id: userId })
13  .select()
14  .single()
15
16// Update data
17const { data, error } = await supabase
18  .from('items')
19  .update({ title: 'Updated Title' })
20  .eq('id', itemId)
21  .select()
22  .single()
23
24// Delete data
25const { error } = await supabase
26  .from('items')
27  .delete()
28  .eq('id', itemId)
29
30// Complex joins
31const { data, error } = await supabase
32  .from('items')
33  .select(`
34    *,
35    comments (
36      id,
37      content,
38      user:user_id (
39        email
40      )
41    )
42  `)
43  .eq('user_id', userId)

Real-time Subscriptions

typescript
1// Subscribe to changes
2const channel = supabase
3  .channel('items-changes')
4  .on(
5    'postgres_changes',
6    {
7      event: '*',
8      schema: 'public',
9      table: 'items',
10      filter: `user_id=eq.${userId}`,
11    },
12    (payload) => {
13      console.log('Change received!', payload)
14      // Update local state
15    }
16  )
17  .subscribe()
18
19// Cleanup
20channel.unsubscribe()

Authentication

Email/Password Auth

typescript
1// Sign up
2const { data, error } = await supabase.auth.signUp({
3  email: 'user@example.com',
4  password: 'password123',
5  options: {
6    data: {
7      display_name: 'User Name',
8    },
9  },
10})
11
12// Sign in
13const { data, error } = await supabase.auth.signInWithPassword({
14  email: 'user@example.com',
15  password: 'password123',
16})
17
18// Sign out
19const { error } = await supabase.auth.signOut()
20
21// Get current user
22const { data: { user } } = await supabase.auth.getUser()

OAuth Providers

typescript
1// Google OAuth
2const { data, error } = await supabase.auth.signInWithOAuth({
3  provider: 'google',
4  options: {
5    redirectTo: `${window.location.origin}/auth/callback`,
6  },
7})
8
9// Handle callback
10// app/auth/callback/route.ts
11export async function GET(request: Request) {
12  const { searchParams } = new URL(request.url)
13  const code = searchParams.get('code')
14
15  if (code) {
16    const supabase = createServerClient()
17    await supabase.auth.exchangeCodeForSession(code)
18  }
19
20  return NextResponse.redirect(new URL('/dashboard', request.url))
21}

Auth Middleware

typescript
1// middleware.ts
2import { createServerClient } from '@supabase/ssr'
3import { NextResponse } from 'next/server'
4import type { NextRequest } from 'next/server'
5
6export async function middleware(request: NextRequest) {
7  const response = NextResponse.next()
8
9  const supabase = createServerClient(
10    process.env.NEXT_PUBLIC_SUPABASE_URL!,
11    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
12    {
13      cookies: {
14        get(name: string) {
15          return request.cookies.get(name)?.value
16        },
17        set(name: string, value: string, options: any) {
18          response.cookies.set(name, value, options)
19        },
20        remove(name: string, options: any) {
21          response.cookies.set(name, '', { ...options, maxAge: 0 })
22        },
23      },
24    }
25  )
26
27  const { data: { user } } = await supabase.auth.getUser()
28
29  // Redirect to login if not authenticated
30  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
31    return NextResponse.redirect(new URL('/login', request.url))
32  }
33
34  return response
35}
36
37export const config = {
38  matcher: ['/dashboard/:path*'],
39}

Edge Functions

Basic Edge Function

typescript
1// supabase/functions/hello/index.ts
2import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
4
5serve(async (req) => {
6  try {
7    // Get Supabase client
8    const supabase = createClient(
9      Deno.env.get('SUPABASE_URL') ?? '',
10      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
11      {
12        auth: {
13          autoRefreshToken: false,
14          persistSession: false,
15        },
16      }
17    )
18
19    // Get user from auth header
20    const authHeader = req.headers.get('Authorization')
21    const token = authHeader?.replace('Bearer ', '')
22    const { data: { user } } = await supabase.auth.getUser(token)
23
24    if (!user) {
25      return new Response(
26        JSON.stringify({ error: 'Unauthorized' }),
27        { status: 401, headers: { 'Content-Type': 'application/json' } }
28      )
29    }
30
31    // Your logic here
32    const { data, error } = await supabase
33      .from('items')
34      .select('*')
35      .eq('user_id', user.id)
36
37    return new Response(
38      JSON.stringify({ data }),
39      { headers: { 'Content-Type': 'application/json' } }
40    )
41  } catch (error) {
42    return new Response(
43      JSON.stringify({ error: error.message }),
44      { status: 500, headers: { 'Content-Type': 'application/json' } }
45    )
46  }
47})

Type Generation

bash
1# Generate TypeScript types
2npx supabase gen types typescript --project-id your-project-id > types/supabase.ts
3
4# Use in code
5import { Database } from '@/types/supabase'
6
7type Item = Database['public']['Tables']['items']['Row']
8type ItemInsert = Database['public']['Tables']['items']['Insert']
9type ItemUpdate = Database['public']['Tables']['items']['Update']

Common Patterns

Soft Deletes

sql
1alter table public.items add column deleted_at timestamp with time zone;
2
3create policy "Users cannot see deleted items"
4  on public.items for select
5  using (deleted_at is null);
6
7-- Soft delete function
8create or replace function soft_delete_item(item_id uuid)
9returns void as $$
10begin
11  update public.items
12  set deleted_at = now()
13  where id = item_id;
14end;
15$$ language plpgsql security definer;

Audit Logs

sql
1create table public.audit_logs (
2  id uuid default gen_random_uuid() primary key,
3  created_at timestamp with time zone default now() not null,
4  user_id uuid references auth.users(id),
5  table_name text not null,
6  record_id uuid not null,
7  action text not null,
8  changes jsonb
9);
10
11-- Trigger function
12create or replace function public.audit_trigger()
13returns trigger as $$
14begin
15  insert into public.audit_logs (user_id, table_name, record_id, action, changes)
16  values (
17    auth.uid(),
18    TG_TABLE_NAME,
19    NEW.id,
20    TG_OP,
21    to_jsonb(NEW) - to_jsonb(OLD)
22  );
23  return NEW;
24end;
25$$ language plpgsql security definer;

Troubleshooting

Common Issues

1. 401 Errors: Check RLS policies, ensure user is authenticated
2. 403 Errors: RLS policy blocking operation
3. Row not found: Policy may be filtering it out
4. Connection issues: Check URL and API keys
5. Type mismatches: Regenerate types after schema changes

Debugging RLS

sql
1-- Test as specific user
2set request.jwt.claims = '{"sub": "user-uuid-here"}';
3
4-- Check what policies apply
5select * from pg_policies where tablename = 'items';
6
7-- Disable RLS temporarily (for testing only!)
8alter 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