Supabase Deployment: Production Best Practices

# .env.production - Production environment variables
# Never commit this file to version control!

# Supabase Configuration
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
SUPABASE_DB_URL=postgresql://postgres:[password]@db.your-project.supabase.co:5432/postgres

# Application Settings
NODE_ENV=production
NEXT_PUBLIC_APP_URL=https://yourapp.com
NEXT_PUBLIC_API_URL=https://api.yourapp.com

# Authentication
NEXT_PUBLIC_AUTH_REDIRECT_URL=https://yourapp.com/auth/callback
JWT_SECRET=your-jwt-secret-min-32-chars
JWT_EXPIRY=3600

# Third-party Services
STRIPE_SECRET_KEY=sk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...
RESEND_API_KEY=re_...
CLOUDFLARE_API_TOKEN=your-cloudflare-token

# Monitoring & Logging
SENTRY_DSN=https://[email protected]/...
LOGFLARE_API_KEY=your-logflare-key
LOGFLARE_SOURCE_ID=your-source-id

# Rate Limiting
REDIS_URL=redis://username:password@host:port
RATE_LIMIT_MAX=100
RATE_LIMIT_WINDOW=60000

# Feature Flags
ENABLE_ANALYTICS=true
ENABLE_NOTIFICATIONS=true
MAINTENANCE_MODE=false

// lib/env.ts - Type-safe environment validation
import { z } from 'zod'

const envSchema = z.object({
  // Supabase
  NEXT_PUBLIC_SUPABASE_URL: z.string().url(),
  NEXT_PUBLIC_SUPABASE_ANON_KEY: z.string().min(1),
  SUPABASE_SERVICE_ROLE_KEY: z.string().min(1),
  
  // App
  NODE_ENV: z.enum(['development', 'production', 'test']),
  NEXT_PUBLIC_APP_URL: z.string().url(),
  
  // Optional
  STRIPE_SECRET_KEY: z.string().optional(),
  SENTRY_DSN: z.string().url().optional(),
})

export const env = envSchema.parse(process.env)

// Validate on app startup
if (typeof window === 'undefined') {
  try {
    envSchema.parse(process.env)
    console.log('✓ Environment variables validated')
  } catch (error) {
    console.error('❌ Invalid environment variables:', error)
    process.exit(1)
  }
}

// next.config.js - Expose public env variables
module.exports = {
  env: {
    NEXT_PUBLIC_SUPABASE_URL: process.env.NEXT_PUBLIC_SUPABASE_URL,
    NEXT_PUBLIC_SUPABASE_ANON_KEY: process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY,
    NEXT_PUBLIC_APP_URL: process.env.NEXT_PUBLIC_APP_URL,
  },
  
  // Security headers
  async headers() {
    return [
      {
        source: '/:path*',
        headers: [
          {
            key: 'X-DNS-Prefetch-Control',
            value: 'on'
          },
          {
            key: 'Strict-Transport-Security',
            value: 'max-age=63072000; includeSubDomains; preload'
          },
          {
            key: 'X-Frame-Options',
            value: 'SAMEORIGIN'
          },
          {
            key: 'X-Content-Type-Options',
            value: 'nosniff'
          },
          {
            key: 'X-XSS-Protection',
            value: '1; mode=block'
          },
          {
            key: 'Referrer-Policy',
            value: 'strict-origin-when-cross-origin'
          },
          {
            key: 'Permissions-Policy',
            value: 'camera=(), microphone=(), geolocation=()'
          }
        ]
      }
    ]
  },
  
  // Redirect HTTP to HTTPS
  async redirects() {
    return [
      {
        source: '/:path*',
        has: [
          {
            type: 'header',
            key: 'x-forwarded-proto',
            value: 'http',
          },
        ],
        destination: 'https://yourapp.com/:path*',
        permanent: true,
      },
    ]
  },
}

# Migration workflow for production

# 1. Create migration locally
supabase migration new add_user_profiles

# 2. Write migration SQL
# supabase/migrations/20240126000000_add_user_profiles.sql
-- Add user profiles table
create table user_profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique not null,
  full_name text,
  avatar_url text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Enable RLS
alter table user_profiles enable row level security;

-- Policies
create policy "Users can view all profiles"
  on user_profiles for select
  using (true);

create policy "Users can update own profile"
  on user_profiles for update
  using (auth.uid() = id);

-- Indexes
create index idx_user_profiles_username on user_profiles(username);

-- Trigger for updated_at
create trigger update_user_profiles_updated_at
  before update on user_profiles
  for each row
  execute function update_updated_at_column();

# 3. Test migration locally
supabase db reset
supabase db push

# 4. Verify migration in local database
psql $DATABASE_URL -c "\d user_profiles"

# 5. Run tests
npm test

# 6. Push to staging environment
supabase link --project-ref staging-project-ref
supabase db push

# 7. Verify in staging
curl https://staging.yourapp.com/api/health

# 8. Create migration review
git add supabase/migrations/
git commit -m "feat: add user profiles table"
git push origin feature/user-profiles

# 9. After PR approval, deploy to production
# This should be automated via CI/CD

# supabase/migrations/rollback.sql - Rollback script
-- Always create rollback for destructive changes
drop trigger if exists update_user_profiles_updated_at on user_profiles;
drop policy if exists "Users can update own profile" on user_profiles;
drop policy if exists "Users can view all profiles" on user_profiles;
drop table if exists user_profiles;

# Migration best practices script
# scripts/migrate-production.sh
#!/bin/bash

set -e

echo "🚀 Starting production migration..."

# Backup database before migration
echo "📦 Creating backup..."
supabase db dump -f backup-$(date +%Y%m%d-%H%M%S).sql

# Link to production
echo "🔗 Linking to production..."
supabase link --project-ref $SUPABASE_PROJECT_REF

# Show pending migrations
echo "📋 Pending migrations:"
supabase migration list

# Confirm before proceeding
read -p "⚠️  Continue with migration? (yes/no): " confirm
if [ "$confirm" != "yes" ]; then
  echo "❌ Migration cancelled"
  exit 1
fi

# Apply migrations
echo "⏳ Applying migrations..."
supabase db push

if [ $? -eq 0 ]; then
  echo "✅ Migration completed successfully"
  
  # Verify migration
  echo "🔍 Verifying database..."
  npm run db:verify
  
  # Notify team
  curl -X POST $SLACK_WEBHOOK_URL \
    -H 'Content-Type: application/json' \
    -d '{"text":"✅ Production migration completed successfully"}'
else
  echo "❌ Migration failed"
  curl -X POST $SLACK_WEBHOOK_URL \
    -H 'Content-Type: application/json' \
    -d '{"text":"❌ Production migration failed! Check logs immediately."}'
  exit 1
fi

// Migration verification script
// scripts/verify-migration.ts
import { createClient } from '@supabase/supabase-js'

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

async function verifyMigration() {
  console.log('🔍 Verifying migration...')

  // Check table exists
  const { data: tables, error: tablesError } = await supabase
    .from('information_schema.tables')
    .select('table_name')
    .eq('table_name', 'user_profiles')

  if (tablesError || !tables?.length) {
    throw new Error('user_profiles table not found')
  }
  console.log('✓ Table exists')

  // Check RLS enabled
  const { data: rls } = await supabase.rpc('check_rls_enabled', {
    table_name: 'user_profiles'
  })

  if (!rls) {
    throw new Error('RLS not enabled on user_profiles')
  }
  console.log('✓ RLS enabled')

  // Check indexes
  const { data: indexes } = await supabase
    .from('pg_indexes')
    .select('indexname')
    .eq('tablename', 'user_profiles')

  const requiredIndexes = ['idx_user_profiles_username']
  for (const idx of requiredIndexes) {
    if (!indexes?.some(i => i.indexname === idx)) {
      throw new Error(`Missing index: ${idx}`)
    }
  }
  console.log('✓ Indexes created')

  // Test insert
  const testUserId = crypto.randomUUID()
  const { error: insertError } = await supabase
    .from('user_profiles')
    .insert({
      id: testUserId,
      username: `test_${Date.now()}`,
      full_name: 'Test User'
    })

  if (insertError) {
    throw new Error(`Insert failed: ${insertError.message}`)
  }

  // Cleanup test data
  await supabase.from('user_profiles').delete().eq('id', testUserId)
  console.log('✓ CRUD operations work')

  console.log('✅ All verifications passed')
}

verifyMigration().catch((error) => {
  console.error('❌ Verification failed:', error)
  process.exit(1)
})

# .github/workflows/deploy-production.yml
name: Deploy to Production

on:
  push:
    branches:
      - main
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Run linter
        run: npm run lint
      
      - name: Run type check
        run: npm run type-check
      
      - name: Run tests
        run: npm test
        env:
          SUPABASE_URL: ${{ secrets.SUPABASE_TEST_URL }}
          SUPABASE_ANON_KEY: ${{ secrets.SUPABASE_TEST_ANON_KEY }}

  migrate:
    runs-on: ubuntu-latest
    needs: test
    steps:
      - uses: actions/checkout@v4
      
      - name: Setup Supabase CLI
        uses: supabase/setup-cli@v1
        with:
          version: latest
      
      - name: Link to production project
        run: supabase link --project-ref ${{ secrets.SUPABASE_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      
      - name: Check for pending migrations
        id: migrations
        run: |
          PENDING=$(supabase migration list --pending)
          echo "pending=$PENDING" >> $GITHUB_OUTPUT
      
      - name: Create database backup
        if: steps.migrations.outputs.pending != ''
        run: |
          supabase db dump -f backup-$(date +%Y%m%d-%H%M%S).sql
          # Upload backup to S3 or another storage
      
      - name: Apply migrations
        if: steps.migrations.outputs.pending != ''
        run: supabase db push
      
      - name: Verify migration
        if: steps.migrations.outputs.pending != ''
        run: npm run db:verify

  deploy:
    runs-on: ubuntu-latest
    needs: migrate
    steps:
      - uses: actions/checkout@v4
      
      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
      
      - name: Install dependencies
        run: npm ci
      
      - name: Build application
        run: npm run build
        env:
          NEXT_PUBLIC_SUPABASE_URL: ${{ secrets.NEXT_PUBLIC_SUPABASE_URL }}
          NEXT_PUBLIC_SUPABASE_ANON_KEY: ${{ secrets.NEXT_PUBLIC_SUPABASE_ANON_KEY }}
          NEXT_PUBLIC_APP_URL: ${{ secrets.NEXT_PUBLIC_APP_URL }}
      
      - name: Deploy to Vercel
        uses: amondnet/vercel-action@v25
        with:
          vercel-token: ${{ secrets.VERCEL_TOKEN }}
          vercel-org-id: ${{ secrets.VERCEL_ORG_ID }}
          vercel-project-id: ${{ secrets.VERCEL_PROJECT_ID }}
          vercel-args: '--prod'
      
      - name: Run smoke tests
        run: npm run test:e2e:prod
        env:
          BASE_URL: https://yourapp.com
      
      - name: Notify deployment success
        if: success()
        uses: slackapi/slack-github-action@v1
        with:
          payload: |
            {
              "text": "✅ Production deployment successful",
              "blocks": [
                {
                  "type": "section",
                  "text": {
                    "type": "mrkdwn",
                    "text": "*Deployment Status:* ✅ Success\n*Branch:* ${{ github.ref_name }}\n*Commit:* ${{ github.sha }}"
                  }
                }
              ]
            }
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}
      
      - name: Notify deployment failure
        if: failure()
        uses: slackapi/slack-github-action@v1
        with:
          payload: |
            {
              "text": "❌ Production deployment failed",
              "blocks": [
                {
                  "type": "section",
                  "text": {
                    "type": "mrkdwn",
                    "text": "*Deployment Status:* ❌ Failed\n*Branch:* ${{ github.ref_name }}\n*Commit:* ${{ github.sha }}\n*Action:* ${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}"
                  }
                }
              ]
            }
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}

  rollback:
    runs-on: ubuntu-latest
    if: failure()
    needs: deploy
    steps:
      - name: Rollback deployment
        run: |
          # Implement rollback logic
          # Restore from backup
          # Revert to previous version
          echo "Initiating rollback..."
      
      - name: Notify rollback
        uses: slackapi/slack-github-action@v1
        with:
          payload: '{"text":"⚠️ Rollback initiated"}'
        env:
          SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}

-- Production database settings
-- supabase/production-config.sql

-- Connection pooling (adjust based on plan)
alter system set max_connections = 100;
alter system set shared_buffers = '256MB';
alter system set effective_cache_size = '1GB';
alter system set work_mem = '16MB';
alter system set maintenance_work_mem = '64MB';

-- Query performance
alter system set random_page_cost = 1.1;
alter system set effective_io_concurrency = 200;
alter system set default_statistics_target = 100;

-- WAL and checkpoints
alter system set wal_buffers = '16MB';
alter system set checkpoint_completion_target = 0.9;
alter system set max_wal_size = '2GB';
alter system set min_wal_size = '1GB';

-- Enable extensions
create extension if not exists pg_stat_statements;
create extension if not exists pgcrypto;
create extension if not exists pg_trgm;  -- For text search

-- Configure statement timeout (prevent long-running queries)
alter database postgres set statement_timeout = '30s';

-- Configure idle transaction timeout
alter database postgres set idle_in_transaction_session_timeout = '10min';

-- Log slow queries
alter system set log_min_duration_statement = 1000;  -- Log queries > 1s

-- Production RLS policies audit
create or replace function audit_rls_policies()
returns table(
  table_name text,
  rls_enabled boolean,
  policy_count bigint
) as $$
begin
  return query
  select
    t.tablename::text,
    t.rowsecurity,
    count(p.policyname)
  from pg_tables t
  left join pg_policies p on t.tablename = p.tablename
  where t.schemaname = 'public'
  group by t.tablename, t.rowsecurity
  order by t.rowsecurity, t.tablename;
end;
$$ language plpgsql;

-- Run audit to ensure RLS is enabled
select * from audit_rls_policies() where not rls_enabled;

// Production application config
// lib/config.ts
export const productionConfig = {
  // Rate limiting
  rateLimit: {
    max: 100,
    windowMs: 60000,  // 1 minute
    message: 'Too many requests, please try again later',
  },
  
  // Caching
  cache: {
    ttl: 3600,  // 1 hour
    maxSize: 100,  // Max cached items
    staleWhileRevalidate: true,
  },
  
  // Database
  database: {
    poolSize: 20,
    connectionTimeout: 10000,
    idleTimeout: 30000,
    maxRetries: 3,
  },
  
  // Session
  session: {
    maxAge: 7 * 24 * 60 * 60,  // 7 days
    secure: true,
    httpOnly: true,
    sameSite: 'lax' as const,
  },
  
  // File uploads
  upload: {
    maxFileSize: 5 * 1024 * 1024,  // 5MB
    allowedTypes: ['image/jpeg', 'image/png', 'image/webp'],
    maxFiles: 10,
  },
  
  // API
  api: {
    timeout: 30000,
    retries: 3,
    retryDelay: 1000,
  },
  
  // Monitoring
  monitoring: {
    sampleRate: 1.0,  // 100% for production
    errorTracking: true,
    performanceTracking: true,
  },
}

// Validate production config on startup
export function validateProductionConfig() {
  const required = [
    'NEXT_PUBLIC_SUPABASE_URL',
    'NEXT_PUBLIC_SUPABASE_ANON_KEY',
    'SUPABASE_SERVICE_ROLE_KEY',
  ]

  for (const key of required) {
    if (!process.env[key]) {
      throw new Error(`Missing required environment variable: ${key}`)
    }
  }

  // Validate Supabase URL format
  const url = process.env.NEXT_PUBLIC_SUPABASE_URL
  if (!url?.startsWith('https://')) {
    throw new Error('Supabase URL must use HTTPS in production')
  }

  // Ensure not using example keys
  if (process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY?.includes('example')) {
    throw new Error('Production cannot use example API keys')
  }

  console.log('✓ Production configuration validated')
}

if (process.env.NODE_ENV === 'production') {
  validateProductionConfig()
}

// Implement connection pooling
// lib/supabase/server-pool.ts
import { createClient } from '@supabase/supabase-js'
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.SUPABASE_DB_URL,
  max: 20,  // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
})

export const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  {
    db: { schema: 'public' },
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
)

// Database query with connection pooling
export async function queryWithPool<T>(query: string, params: any[] = []): Promise<T[]> {
  const client = await pool.connect()
  try {
    const result = await client.query(query, params)
    return result.rows
  } finally {
    client.release()
  }
}

// Implement caching layer
// lib/cache.ts
import { Redis } from 'ioredis'

const redis = new Redis(process.env.REDIS_URL!)

export async function getCached<T>(
  key: string,
  fetcher: () => Promise<T>,
  ttl: number = 3600
): Promise<T> {
  // Try cache first
  const cached = await redis.get(key)
  if (cached) {
    return JSON.parse(cached)
  }

  // Fetch and cache
  const data = await fetcher()
  await redis.setex(key, ttl, JSON.stringify(data))
  return data
}

export async function invalidateCache(pattern: string) {
  const keys = await redis.keys(pattern)
  if (keys.length > 0) {
    await redis.del(...keys)
  }
}

// Usage with caching
import { getCached } from '@/lib/cache'
import { createClient } from '@/lib/supabase/client'

export async function getProducts() {
  return getCached(
    'products:all',
    async () => {
      const supabase = createClient()
      const { data } = await supabase
        .from('products')
        .select('*')
        .eq('published', true)
      return data || []
    },
    3600  // Cache for 1 hour
  )
}

-- Database partitioning for large tables
-- Create partitioned table
create table analytics_events (
  id uuid default gen_random_uuid(),
  user_id uuid,
  event_type text,
  event_data jsonb,
  created_at timestamp with time zone default now()
) partition by range (created_at);

-- Create monthly partitions
create table analytics_events_2024_01 partition of analytics_events
  for values from ('2024-01-01') to ('2024-02-01');

create table analytics_events_2024_02 partition of analytics_events
  for values from ('2024-02-01') to ('2024-03-01');

-- Indexes on partitions
create index idx_analytics_2024_01_user on analytics_events_2024_01(user_id);
create index idx_analytics_2024_02_user on analytics_events_2024_02(user_id);

-- Implement read replicas strategy
// lib/supabase/replicas.ts
const PRIMARY_URL = process.env.SUPABASE_DB_URL!
const REPLICA_URL = process.env.SUPABASE_REPLICA_URL

export function getSupabaseClient(readOnly: boolean = false) {
  const url = readOnly && REPLICA_URL ? REPLICA_URL : PRIMARY_URL
  
  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
    {
      db: { connectionString: url },
    }
  )
}

// Usage
const readClient = getSupabaseClient(true)  // Read from replica
const writeClient = getSupabaseClient(false) // Write to primary

// CDN configuration for static assets
// next.config.js
module.exports = {
  images: {
    domains: ['your-project.supabase.co'],
    loader: 'custom',
    loaderFile: './lib/image-loader.ts',
  },
}

// lib/image-loader.ts
export default function cloudflareLoader({ src, width, quality }) {
  const params = [`width=${width}`]
  if (quality) {
    params.push(`quality=${quality}`)
  }
  
  return `https://cdn.yourapp.com/cdn-cgi/image/${params.join(',')}/${src}`
}