supabase-multi-env-setup

What this skill does

# Supabase Multi-Environment Setup

## Overview

Production Supabase deployments require separate projects per environment — each with its own URL, API keys, database, and RLS policies. This skill configures a three-tier environment architecture (local dev, staging, production) with safe migration promotion via `supabase db push`, environment-aware `createClient` initialization, database branching for preview deployments, and CI/CD pipelines that prevent accidental cross-environment operations.

**When to use:** Setting up a new project with multiple environments, migrating from a single-project setup to multi-env, adding staging to an existing dev/prod split, or configuring preview environments with database branching.

## Prerequisites

- Three separate Supabase projects created at [supabase.com/dashboard](https://supabase.com/dashboard) (dev, staging, production)
- Supabase CLI installed: `npm install -g supabase` or `npx supabase --version`
- `@supabase/supabase-js` v2+ installed in your project
- Node.js 18+ with framework that supports `.env` files (Next.js, Nuxt, SvelteKit, etc.)
- A secret management solution for CI (GitHub Actions Secrets, Vercel env vars, etc.)

## Instructions

### Step 1: Environment Files and Project Layout

Create one Supabase CLI project with shared migrations and per-environment credential files. Each `.env.*` file points to a different Supabase project.

**Project structure:**

```
my-app/
├── supabase/
│   ├── config.toml              # Local CLI config
│   ├── migrations/              # Shared migrations (all envs use the same schema)
│   │   └── 20260101000000_initial.sql
│   ├── seed.sql                 # Dev-only seed data (runs on db reset only)
│   └── functions/               # Edge Functions (deployed per env)
├── .env.local                   # Local dev → supabase start
├── .env.staging                 # Staging project credentials
├── .env.production              # Production project credentials
└── .gitignore                   # Must include .env.staging, .env.production
```

**Environment files:**

```bash
# .env.local — local development (safe defaults from supabase start)
NEXT_PUBLIC_SUPABASE_URL=http://127.0.0.1:54321
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImV4cCI6MTk4MzgxMjk5Nn0.EGIM96RAZx35lJzdJsyH-qQwv8Hdp7fsn3W0YpN81IU
DATABASE_URL=postgresql://postgres:[email protected]:54322/postgres
SUPABASE_ENV=local

# .env.staging — staging project
NEXT_PUBLIC_SUPABASE_URL=https://<staging-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...staging-anon-key
SUPABASE_SERVICE_ROLE_KEY=eyJ...staging-service-key
DATABASE_URL=postgres://postgres.<staging-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres
SUPABASE_ENV=staging

# .env.production — production project (NEVER commit this file)
NEXT_PUBLIC_SUPABASE_URL=https://<prod-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...prod-anon-key
SUPABASE_SERVICE_ROLE_KEY=eyJ...prod-service-key
DATABASE_URL=postgres://postgres.<prod-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres
SUPABASE_ENV=production
```

**Critical `.gitignore` entries:**

```gitignore
.env.staging
.env.production
# .env.local is safe to commit (contains only local dev keys)
```

**Link each environment to the CLI:**

```text
# Local development
npx supabase start

# Link staging (stores ref in supabase/.temp/project-ref)
npx supabase link --project-ref <staging-ref>

# Link production (re-links, overwriting staging ref)
npx supabase link --project-ref <prod-ref>
```

> **Note:** The CLI can only link one project at a time. Switch between environments by re-running `supabase link` with the target project ref before any `db push` or `functions deploy` operation.

### Step 2: Environment-Aware Client and Safeguards

Build a `createClient` wrapper that selects the correct URL and keys based on the active environment, plus production safeguards that block destructive operations.

**Environment detection (`lib/env.ts`):**

```typescript
export type Environment = 'local' | 'staging' | 'production';

export function getEnvironment(): Environment {
  // Explicit env var takes priority
  const explicit = process.env.SUPABASE_ENV;
  if (explicit === 'local' || explicit === 'staging' || explicit === 'production') {
    return explicit;
  }

  // Fallback: detect from URL
  const url = process.env.NEXT_PUBLIC_SUPABASE_URL ?? '';
  if (url.includes('127.0.0.1') || url.includes('localhost')) return 'local';
  if (url.includes('staging')) return 'staging';
  return 'production';
}

export function isProduction(): boolean {
  return getEnvironment() === 'production';
}

export function requireNonProduction(operation: string): void {
  if (isProduction()) {
    throw new Error(
      `[BLOCKED] "${operation}" is not allowed in production. ` +
      `Current SUPABASE_ENV=${process.env.SUPABASE_ENV}`
    );
  }
}
```

**Supabase client factory (`lib/supabase.ts`):**

```typescript
import { createClient, type SupabaseClient } from '@supabase/supabase-js';
import type { Database } from './database.types';
import { getEnvironment } from './env';

// Browser client (uses anon key, respects RLS)
export function createBrowserClient(): SupabaseClient<Database> {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

  return createClient<Database>(supabaseUrl, supabaseAnonKey, {
    auth: {
      autoRefreshToken: true,
      persistSession: true,
    },
    global: {
      headers: { 'x-environment': getEnvironment() },
    },
  });
}

// Server client (uses service role key, bypasses RLS)
export function createServerClient(): SupabaseClient<Database> {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY!;

  return createClient<Database>(supabaseUrl, serviceRoleKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}
```

**Production safeguards:**

```typescript
import { requireNonProduction } from './env';
import { createServerClient } from './supabase';

// Seed data — only runs in local/staging
export async function seedTestData(): Promise<void> {
  requireNonProduction('seedTestData');
  const supabase = createServerClient();
  await supabase.from('test_users').insert([
    { email: '[email protected]', role: 'admin' },
    { email: '[email protected]', role: 'member' },
  ]);
}

// Destructive reset — only runs in local
export async function resetDatabase(): Promise<void> {
  requireNonProduction('resetDatabase');
  const supabase = createServerClient();
  await supabase.rpc('truncate_all_tables');
}
```

**Environment-specific RLS policies:**

```sql
-- supabase/migrations/20260115000000_env_rls.sql
-- Allow broader access in staging for QA testing
CREATE POLICY "staging_read_all" ON public.profiles
  FOR SELECT
  USING (
    current_setting('app.environment', true) = 'staging'
    OR auth.uid() = id
  );

-- Set environment in each request via the x-environment header
-- or via a Postgres config parameter in your connection string
```

### Step 3: Migration Promotion and Database Branching

Promote migrations through environments (local -> staging -> production) and use database branching for preview deployments.

**Migration promotion workflow:**

```text
# 1. Create migration locally
npx supabase migration new add_profiles_table
# Edit: supabase/migrations/20260120000000_add_profiles_table.sql

# 2. Test locally with full reset
npx supabase db reset          # Applies all migrations + seed.sql
npx supabase test db           # Run pgTAP tests if configured

# 3. Push to staging
npx supabase link --project-ref <staging-ref>
npx supaba