supabase-expert

What this skill does

# 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
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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)

```typescript
// 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

```typescript
// 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

```typescript
// 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

```typescript
// Sign up
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'password123',
  options: {
    data: {
      display_name: 'User Name',
    },
  },
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: '[email protected]',
  password: 'password123',
})

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

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

### OAuth Providers

```typescript
// 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

```typescript
// 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) {