From MVP to Production: Supabase in the Real World

// src/lib/supabase.ts
import { createClient } from '@supabase/supabase-js'

// For normal queries (uses pooler)
export const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

// For operations that need direct connections (migrations, etc.)
export const supabaseDirect = createClient(
  process.env.SUPABASE_URL!.replace('.supabase.co', '-pooler.supabase.co'),
  process.env.SUPABASE_ANON_KEY!
)

-- Index on user_id (very common)
CREATE INDEX idx_tasks_user_id ON tasks(user_id);

-- Composite index for common queries
CREATE INDEX idx_tasks_user_completed ON tasks(user_id, completed);

-- Index for date sorting
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);

-- View slow queries
SELECT * FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

-- Analyze a specific query
EXPLAIN ANALYZE 
SELECT * FROM tasks 
WHERE user_id = 'uuid' 
AND completed = false 
ORDER BY created_at DESC;

// src/pagination.ts
const PAGE_SIZE = 20

export async function getTasks(page: number = 1) {
  const from = (page - 1) * PAGE_SIZE
  const to = from + PAGE_SIZE - 1

  const { data, error, count } = await supabase
    .from('tasks')
    .select('*', { count: 'exact' })
    .range(from, to)
    .order('created_at', { ascending: false })

  return {
    data,
    total: count,
    page,
    totalPages: Math.ceil((count || 0) / PAGE_SIZE)
  }
}

-- View all policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual 
FROM pg_policies 
WHERE schemaname = 'public';

// src/lib/supabase-admin.ts (SERVER ONLY)
import { createClient } from '@supabase/supabase-js'

export const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

// supabase/functions/api/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'

const rateLimit = new Map<string, { count: number; reset: number }>()

serve(async (req) => {
  const ip = req.headers.get('x-forwarded-for') || 'unknown'
  const now = Date.now()
  const limit = rateLimit.get(ip)

  if (limit && limit.count > 100 && now < limit.reset) {
    return new Response('Rate limit exceeded', { status: 429 })
  }

  if (!limit || now > limit.reset) {
    rateLimit.set(ip, { count: 1, reset: now + 60000 })
  } else {
    limit.count++
  }

  // Your logic here
})

// Create migration
supabase migration new add_tasks_table

// Apply migrations locally
supabase db reset

// Apply to production
supabase db push

supabase/
  migrations/
    20240101000000_create_users.sql
    20240102000000_create_tasks.sql
    20240103000000_add_rls_policies.sql

-- supabase/migrations/20240102000000_create_tasks.sql
CREATE TABLE tasks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

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

-- Policies
CREATE POLICY "Users can view own tasks" ON tasks FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own tasks" ON tasks FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update own tasks" ON tasks FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users can delete own tasks" ON tasks FOR DELETE USING (auth.uid() = user_id);

-- Indexes
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);

// Backup database
supabase db dump > backup.sql

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