supabase-backend-platform

What this skill does

# Supabase Backend Platform Skill

---
progressive_disclosure:
  entry_point:
    summary: "Open-source Firebase alternative with Postgres, authentication, storage, and realtime"
    when_to_use:
      - "When building full-stack applications"
      - "When auth, database, and storage are required"
      - "When realtime subscriptions are needed"
      - "When using Next.js, React, or Vue"
    quick_start:
      - "Create project on Supabase console"
      - "npm install @supabase/supabase-js"
      - "Initialize client with URL and anon key"
      - "Use auth, database, storage, realtime"
  token_estimate:
    entry: 80-95
    full: 5000-6000
---

## Supabase Fundamentals

### What is Supabase?
Open-source Firebase alternative built on:
- **Postgres Database**: Full SQL database with PostgREST API
- **Authentication**: Built-in auth with multiple providers
- **Storage**: File storage with image transformations
- **Realtime**: WebSocket subscriptions to database changes
- **Edge Functions**: Serverless functions on Deno runtime
- **Row Level Security**: Postgres RLS for data access control

### Project Setup
```bash
# Install Supabase client
npm install @supabase/supabase-js

# Install CLI for local development
npm install -D supabase

# TypeScript types
npm install -D @supabase/supabase-js
```

### Client Initialization
```typescript
// lib/supabase.ts
import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient(supabaseUrl, supabaseAnonKey)

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

export const supabase = createClient<Database>(
  supabaseUrl,
  supabaseAnonKey
)
```

## Database Operations

### PostgREST API Basics
Supabase auto-generates REST API from Postgres schema:

```typescript
// SELECT * FROM posts
const { data, error } = await supabase
  .from('posts')
  .select('*')

// SELECT with filters
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10)

// SELECT with joins
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    author:profiles(name, avatar),
    comments(count)
  `)

// INSERT
const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'Hello', content: 'World' })
  .select()
  .single()

// UPDATE
const { data } = await supabase
  .from('posts')
  .update({ status: 'published' })
  .eq('id', postId)
  .select()

// DELETE
const { error } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)

// UPSERT
const { data } = await supabase
  .from('posts')
  .upsert({ id: 1, title: 'Updated' })
  .select()
```

### Advanced Queries
```typescript
// Full-text search
const { data } = await supabase
  .from('posts')
  .select('*')
  .textSearch('title', 'postgresql', {
    type: 'websearch',
    config: 'english'
  })

// Range queries
const { data } = await supabase
  .from('posts')
  .select('*')
  .gte('created_at', '2024-01-01')
  .lte('created_at', '2024-12-31')

// Array contains
const { data } = await supabase
  .from('posts')
  .select('*')
  .contains('tags', ['postgres', 'supabase'])

// JSON operations
const { data } = await supabase
  .from('users')
  .select('*')
  .eq('metadata->theme', 'dark')

// Count without data
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })

// Pagination
const pageSize = 10
const page = 2
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(page * pageSize, (page + 1) * pageSize - 1)
```

### Database Functions and RPC
```typescript
// Call Postgres function
const { data, error } = await supabase
  .rpc('get_trending_posts', {
    days: 7,
    min_score: 10
  })

// Example function in SQL
/*
CREATE OR REPLACE FUNCTION get_trending_posts(
  days INTEGER,
  min_score INTEGER
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  score INTEGER
) AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.title, COUNT(v.id)::INTEGER as score
  FROM posts p
  LEFT JOIN votes v ON p.id = v.post_id
  WHERE p.created_at > NOW() - INTERVAL '1 day' * days
  GROUP BY p.id
  HAVING COUNT(v.id) >= min_score
  ORDER BY score DESC;
END;
$$ LANGUAGE plpgsql;
*/
```

## Authentication

### Email/Password Authentication
```typescript
// Sign up
const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'secure-password',
  options: {
    data: {
      name: 'John Doe',
      avatar_url: 'https://...'
    }
  }
})

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

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

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

// Get session
const { data: { session } } = await supabase.auth.getSession()
```

### OAuth Providers
```typescript
// Sign in with OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'github',
  options: {
    redirectTo: 'http://localhost:3000/auth/callback',
    scopes: 'repo user'
  }
})

// Available providers
// github, google, gitlab, bitbucket, azure, discord, facebook,
// linkedin, notion, slack, spotify, twitch, twitter, apple
```

### Magic Links
```typescript
// Send magic link
const { data, error } = await supabase.auth.signInWithOtp({
  email: '[email protected]',
  options: {
    emailRedirectTo: 'http://localhost:3000/auth/callback'
  }
})

// Verify OTP
const { data, error } = await supabase.auth.verifyOtp({
  email: '[email protected]',
  token: '123456',
  type: 'email'
})
```

### Phone Authentication
```typescript
// Sign in with phone
const { data, error } = await supabase.auth.signInWithOtp({
  phone: '+1234567890'
})

// Verify phone OTP
const { data, error } = await supabase.auth.verifyOtp({
  phone: '+1234567890',
  token: '123456',
  type: 'sms'
})
```

### Auth State Management
```typescript
// Listen to auth changes
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN') {
    console.log('User signed in:', session?.user)
  }
  if (event === 'SIGNED_OUT') {
    console.log('User signed out')
  }
  if (event === 'TOKEN_REFRESHED') {
    console.log('Token refreshed')
  }
})

// Update user metadata
const { data, error } = await supabase.auth.updateUser({
  data: { theme: 'dark' }
})

// Change password
const { data, error } = await supabase.auth.updateUser({
  password: 'new-password'
})
```

## Row Level Security (RLS)

### RLS Fundamentals
Postgres Row Level Security controls data access at the database level:

```sql
-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: Users can read all published posts
CREATE POLICY "Public posts are viewable by everyone"
ON posts FOR SELECT
USING (status = 'published');

-- Policy: Users can only update their own posts
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id);

-- Policy: Authenticated users can insert posts
CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = author_id);

-- Policy: Users can delete their own posts
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = author_id);
```

### Common RLS Patterns
```sql
-- Public read, authenticated write
CREATE POLICY "Anyone can view posts"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL);

-- Organization-based access
CREATE POLICY "Users can view org data"
ON documents FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id
    FROM memberships
    WHERE user_id = auth.uid()
  )
);

-- Role-based access
CREATE POLICY "Admins can do anything"
ON posts FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WH