Supabase open-source Firebase alternative with Postgres, authentication, storage, and realtime subscriptions. Use when building full-stack applications…
SKILL.md

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

# 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

// 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:

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

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

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

// Sign up
const { data, error } = await supabase.auth.signUp({
  email: 'user@example.com',
  password: 'secure-password',
  options: {
    data: {
      name: 'John Doe',
      avatar_url: 'https://...'
    }
  }
})

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({
  email: 'user@example.com',
  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

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

// Send magic link
const { data, error } = await supabase.auth.signInWithOtp({
  email: 'user@example.com',
  options: {
    emailRedirectTo: 'http://localhost:3000/auth/callback'
  }
})

// Verify OTP
const { data, error } = await supabase.auth.verifyOtp({
  email: 'user@example.com',
  token: '123456',
  type: 'email'
})

Phone Authentication

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

// 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:

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

-- 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
    WHERE user_id = auth.uid()
    AND role = 'admin'
  )
);

-- Time-based access
CREATE POLICY "View published or scheduled posts"
ON posts FOR SELECT
USING (
  status = 'published'
  OR (status = 'scheduled' AND publish_at <= NOW())
);

RLS Helper Functions

-- Get current user ID
SELECT auth.uid();

-- Get current user JWT
SELECT auth.jwt();

-- Get specific claim
SELECT auth.jwt()->>'email';

-- Custom claims
SELECT auth.jwt()->'app_metadata'->>'role';

Storage

File Upload

// Upload file
const { data, error } = await supabase.storage
  .from('avatars')
  .upload('public/avatar1.png', file, {
    cacheControl: '3600',
    upsert: false
  })

// Upload with progress
const { data, error } = await supabase.storage
  .from('avatars')
  .upload('public/avatar1.png', file, {
    onUploadProgress: (progress) => {
      console.log(`${progress.loaded}/${progress.total}`)
    }
  })

// Upload from URL
const { data, error } = await supabase.storage
  .from('avatars')
  .uploadToSignedUrl('path', token, file)

File Operations

// Download file
const { data, error } = await supabase.storage
  .from('avatars')
  .download('public/avatar1.png')

// Get public URL
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('public/avatar1.png')

// Create signed URL (temporary access)
const { data, error } = await supabase.storage
  .from('avatars')
  .createSignedUrl('private/document.pdf', 3600) // 1 hour

// List files
const { data, error } = await supabase.storage
  .from('avatars')
  .list('public', {
    limit: 100,
    offset: 0,
    sortBy: { column: 'name', order: 'asc' }
  })

// Delete file
const { data, error } = await supabase.storage
  .from('avatars')
  .remove(['public/avatar1.png'])

// Move file
const { data, error } = await supabase.storage
  .from('avatars')
  .move('public/avatar1.png', 'public/avatar2.png')

Image Transformations

// Transform image
const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl('avatar1.png', {
    transform: {
      width: 200,
      height: 200,
      resize: 'cover',
      quality: 80
    }
  })

// Available transformations
// width, height, resize (cover|contain|fill),
// quality (1-100), format (origin|jpeg|png|webp)

Storage RLS

-- Enable RLS on storage
CREATE POLICY "Avatar images are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'avatars' AND (storage.foldername(name))[1] = 'public');

CREATE POLICY "Users can upload their own avatar"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'avatars'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

CREATE POLICY "Users can delete their own avatar"
ON storage.objects FOR DELETE
USING (
  bucket_id = 'avatars'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

Realtime Subscriptions

Database Changes

// Subscribe to inserts
const channel = supabase
  .channel('posts-insert')
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'posts'
    },
    (payload) => {
      console.log('New post:', payload.new)
    }
  )
  .subscribe()

// Subscribe to updates
const channel = supabase
  .channel('posts-update')
  .on(
    'postgres_changes',
    {
      event: 'UPDATE',
      schema: 'public',
      table: 'posts',
      filter: 'id=eq.1'
    },
    (payload) => {
      console.log('Updated:', payload.new)
      console.log('Previous:', payload.old)
    }
  )
  .subscribe()

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

// Unsubscribe
supabase.removeChannel(channel)

Presence (Track Online Users)

// Track presence
const channel = supabase.channel('room-1')

// Track current user
channel
  .on('presence', { event: 'sync' }, () => {
    const state = channel.presenceState()
    console.log('Online users:', state)
  })
  .on('presence', { event: 'join' }, ({ key, newPresences }) => {
    console.log('User joined:', newPresences)
  })
  .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
    console.log('User left:', leftPresences)
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await channel.track({
        user_id: userId,
        online_at: new Date().toISOString()
      })
    }
  })

// Untrack
await channel.untrack()

Broadcast (Send Messages)

// Broadcast messages
const channel = supabase.channel('chat-room')

channel
  .on('broadcast', { event: 'message' }, (payload) => {
    console.log('Message:', payload)
  })
  .subscribe()

// Send message
await channel.send({
  type: 'broadcast',
  event: 'message',
  payload: { text: 'Hello', user: 'John' }
})

Edge Functions

Edge Function Basics

Serverless functions on Deno runtime:

# Create function
supabase functions new my-function

# Serve locally
supabase functions serve

# Deploy
supabase functions deploy my-function

Edge Function Example

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

serve(async (req) => {
  try {
    // Get auth header
    const authHeader = req.headers.get('Authorization')!

    // Create Supabase client
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      { global: { headers: { Authorization: authHeader } } }
    )

    // Verify user
    const { data: { user }, error } = await supabase.auth.getUser()
    if (error) throw error

    // Process request
    const { data } = await supabase
      .from('posts')
      .select('*')
      .eq('author_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: 400, headers: { 'Content-Type': 'application/json' } }
    )
  }
})

Invoke Edge Function

// From client
const { data, error } = await supabase.functions.invoke('my-function', {
  body: { name: 'John' }
})

// With auth
const { data, error } = await supabase.functions.invoke('my-function', {
  headers: {
    Authorization: `Bearer ${session.access_token}`
  },
  body: { name: 'John' }
})

Next.js Integration

App Router Setup

// lib/supabase/client.ts (Client Component)
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 (Server Component)
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function createClient() {
  const cookieStore = await 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
        },
        set(name: string, value: string, options: CookieOptions) {
          cookieStore.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          cookieStore.set({ name, value: '', ...options })
        },
      },
    }
  )
}

// lib/supabase/middleware.ts (Middleware)
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'

export async function updateSession(request: NextRequest) {
  let response = NextResponse.next({
    request: {
      headers: request.headers,
    },
  })

  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: CookieOptions) {
          request.cookies.set({ name, value, ...options })
          response = NextResponse.next({
            request: { headers: request.headers },
          })
          response.cookies.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          request.cookies.set({ name, value: '', ...options })
          response = NextResponse.next({
            request: { headers: request.headers },
          })
          response.cookies.set({ name, value: '', ...options })
        },
      },
    }
  )

  await supabase.auth.getUser()
  return response
}

Middleware

// middleware.ts
import { updateSession } from '@/lib/supabase/middleware'

export async function middleware(request: NextRequest) {
  return await updateSession(request)
}

export const config = {
  matcher: [
    '/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)',
  ],
}

Server Component

// app/posts/page.tsx
import { createClient } from '@/lib/supabase/server'

export default async function PostsPage() {
  const supabase = await createClient()

  const { data: posts } = await supabase
    .from('posts')
    .select('*')
    .order('created_at', { ascending: false })

  return (
    <div>
      {posts?.map((post) => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
        </article>
      ))}
    </div>
  )
}

Client Component

// app/components/new-post.tsx
'use client'

import { useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function NewPost() {
  const [title, setTitle] = useState('')
  const supabase = createClient()

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault()

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

    const { error } = await supabase
      .from('posts')
      .insert({ title, author_id: user.id })

    if (!error) {
      setTitle('')
    }
  }

  return (
    <form onSubmit={handleSubmit}>
      <input
        value={title}
        onChange={(e) => setTitle(e.target.value)}
        placeholder="Post title"
      />
      <button>Create</button>
    </form>
  )
}

Server Actions

// app/actions/posts.ts
'use server'

import { revalidatePath } from 'next/cache'
import { createClient } from '@/lib/supabase/server'

export async function createPost(formData: FormData) {
  const supabase = await createClient()

  const { data: { user } } = await supabase.auth.getUser()
  if (!user) {
    return { error: 'Not authenticated' }
  }

  const title = formData.get('title') as string

  const { error } = await supabase
    .from('posts')
    .insert({ title, author_id: user.id })

  if (error) {
    return { error: error.message }
  }

  revalidatePath('/posts')
  return { success: true }
}

TypeScript Type Generation

Generate Types from Database

# Install CLI
npm install -D supabase

# Login
npx supabase login

# Link project
npx supabase link --project-ref your-project-ref

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

# Or from local database
npx supabase gen types typescript --local > types/supabase.ts

Use Generated Types

// types/supabase.ts (generated)
export type Database = {
  public: {
    Tables: {
      posts: {
        Row: {
          id: string
          title: string
          content: string | null
          author_id: string
          created_at: string
        }
        Insert: {
          id?: string
          title: string
          content?: string | null
          author_id: string
          created_at?: string
        }
        Update: {
          id?: string
          title?: string
          content?: string | null
          author_id?: string
          created_at?: string
        }
      }
    }
  }
}

// Usage
import { createClient } from '@supabase/supabase-js'
import { Database } from '@/types/supabase'

const supabase = createClient<Database>(url, key)

// Type-safe queries
const { data } = await supabase
  .from('posts') // TypeScript knows this table exists
  .select('title, content') // Autocomplete for columns
  .single()

// data is typed as { title: string; content: string | null }

Supabase CLI and Local Development

Setup Local Development

# Initialize Supabase
npx supabase init

# Start local Supabase (Postgres, Auth, Storage, etc.)
npx supabase start

# Stop
npx supabase stop

# Reset database
npx supabase db reset

# Status
npx supabase status

Database Migrations

# Create migration
npx supabase migration new create_posts_table

# Edit migration file
# supabase/migrations/20240101000000_create_posts_table.sql

# Apply migrations
npx supabase db push

# Pull remote schema
npx supabase db pull

# Diff local vs remote
npx supabase db diff

Migration Example

-- supabase/migrations/20240101000000_create_posts_table.sql
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID NOT NULL REFERENCES auth.users(id),
  status TEXT NOT NULL DEFAULT 'draft',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- Policies
CREATE POLICY "Anyone can view published posts"
ON posts FOR SELECT
USING (status = 'published');

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

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

-- Indexes
CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX posts_status_idx ON posts(status);

-- Trigger for updated_at
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION moddatetime(updated_at);

Security Best Practices

API Key Management

// NEVER expose service_role key in client
// Use anon key for client-side
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! // Public
)

// Service role key only on server
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // Secret, bypasses RLS
  { auth: { persistSession: false } }
)

RLS Best Practices

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

-- Default deny (no policy = no access)
-- Explicitly grant access with policies

-- Test policies as different users
SET request.jwt.claims.sub = 'user-id';
SELECT * FROM posts; -- Test as this user

-- Disable RLS only for admin operations
-- Use service_role key from server, never client

Input Validation

// Validate on client and server
function validatePost(data: unknown) {
  const schema = z.object({
    title: z.string().min(1).max(200),
    content: z.string().max(10000).optional()
  })

  return schema.parse(data)
}

// Server-side validation in Edge Function
serve(async (req) => {
  const body = await req.json()

  try {
    const validated = validatePost(body)
    // Process validated data
  } catch (error) {
    return new Response(
      JSON.stringify({ error: 'Invalid input' }),
      { status: 400 }
    )
  }
})

Environment Variables

# .env.local
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ... # Public
SUPABASE_SERVICE_ROLE_KEY=eyJ... # Secret, server-only

# Production: Use environment variables in hosting platform
# Never commit .env files to git

Production Deployment

Database Optimization

-- Add indexes for common queries
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);
CREATE INDEX posts_author_status_idx ON posts(author_id, status);

-- Optimize full-text search
CREATE INDEX posts_title_search_idx ON posts
USING GIN (to_tsvector('english', title));

-- Analyze query performance
EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 'xxx';

-- Vacuum and analyze
VACUUM ANALYZE posts;

Connection Pooling

// Use connection pooling for serverless
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  auth: {
    persistSession: true,
    autoRefreshToken: true,
  },
  global: {
    headers: { 'x-my-custom-header': 'my-value' },
  },
})

// Configure pool in Supabase dashboard
// Settings > Database > Connection pooling

Monitoring

// Enable query logging
const supabase = createClient(url, key, {
  global: {
    fetch: async (url, options) => {
      console.log('Query:', url)
      return fetch(url, options)
    }
  }
})

// Monitor in Supabase Dashboard
// - Database performance
// - API usage
// - Storage usage
// - Auth activity

Backup Strategy

# Automatic backups (Pro plan+)
# Daily backups with point-in-time recovery

# Manual backup
pg_dump -h db.xxx.supabase.co -U postgres -d postgres > backup.sql

# Restore
psql -h db.xxx.supabase.co -U postgres -d postgres < backup.sql

Supabase vs Firebase

Similarities

Backend-as-a-Service platform

Authentication with multiple providers

Realtime data synchronization

File storage

Serverless functions

Generous free tier

Key Differences

Database

Supabase: PostgreSQL (SQL, full control)

Firebase: Firestore (NoSQL, limited queries)

Queries

Supabase: Full SQL, joins, aggregations

Firebase: Limited filtering, no joins

Security

Supabase: Row Level Security (Postgres native)

Firebase: Security Rules (custom syntax)

Open Source

Supabase: Fully open source, self-hostable

Firebase: Proprietary, Google-hosted only

Pricing

Supabase: Compute-based, predictable

Firebase: Usage-based, can spike

Ecosystem

Supabase: Postgres ecosystem (extensions, tools)

Firebase: Google Cloud Platform integration

Migration Considerations

// Firestore collection query
const snapshot = await db
  .collection('posts')
  .where('status', '==', 'published')
  .orderBy('createdAt', 'desc')
  .limit(10)
  .get()

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

// Complex queries easier in Supabase
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    author:profiles!inner(name),
    comments(count)
  `)
  .gte('created_at', startDate)
  .lte('created_at', endDate)
  .order('created_at', { ascending: false })
// Firebase would require multiple queries + client-side joins

Advanced Patterns

Optimistic Updates

'use client'

import { useState, useOptimistic } from 'react'
import { createClient } from '@/lib/supabase/client'

export function PostList({ initialPosts }: { initialPosts: Post[] }) {
  const [posts, setPosts] = useState(initialPosts)
  const [optimisticPosts, addOptimisticPost] = useOptimistic(
    posts,
    (state, newPost: Post) => [...state, newPost]
  )

  const supabase = createClient()

  const createPost = async (title: string) => {
    const tempPost = {
      id: crypto.randomUUID(),
      title,
      created_at: new Date().toISOString()
    }

    addOptimisticPost(tempPost)

    const { data } = await supabase
      .from('posts')
      .insert({ title })
      .select()
      .single()

    if (data) {
      setPosts([...posts, data])
    }
  }

  return (
    <div>
      {optimisticPosts.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  )
}

Infinite Scroll

'use client'

import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

const PAGE_SIZE = 20

export function InfinitePostList() {
  const [posts, setPosts] = useState<Post[]>([])
  const [page, setPage] = useState(0)
  const [hasMore, setHasMore] = useState(true)

  const supabase = createClient()

  useEffect(() => {
    const loadMore = async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .range(page * PAGE_SIZE, (page + 1) * PAGE_SIZE - 1)
        .order('created_at', { ascending: false })

      if (data) {
        setPosts([...posts, ...data])
        setHasMore(data.length === PAGE_SIZE)
      }
    }

    loadMore()
  }, [page])

  return (
    <div>
      {posts.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
      {hasMore && (
        <button onClick={() => setPage(page + 1)}>
          Load More
        </button>
      )}
    </div>
  )
}

Debounced Search

'use client'

import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
import { useDebounce } from '@/hooks/use-debounce'

export function SearchPosts() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState<Post[]>([])
  const debouncedQuery = useDebounce(query, 300)

  const supabase = createClient()

  useEffect(() => {
    if (!debouncedQuery) {
      setResults([])
      return
    }

    const search = async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .textSearch('title', debouncedQuery)
        .limit(10)

      if (data) setResults(data)
    }

    search()
  }, [debouncedQuery])

  return (
    <div>
      <input
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search posts..."
      />
      {results.map((post) => (
        <div key={post.id}>{post.title}</div>
      ))}
    </div>
  )
}

Summary

Supabase provides a complete backend platform with:

Postgres Database with REST and GraphQL APIs

Built-in Authentication with multiple providers

Row Level Security for granular access control

File Storage with image transformations

Realtime Subscriptions for live updates

Edge Functions for serverless compute

Next.js Integration with Server and Client Components

TypeScript Support with auto-generated types

Local Development with Supabase CLI

Production Ready with monitoring and backups

Use Supabase when a full-featured backend with the power of Postgres, built-in auth, and realtime capabilities is needed, all with excellent TypeScript and Next.js integration.
supabase-backend-platform

SKILL.md

related skills
