Source: ocean/docs/LOCAL_TESTING_SETUP.md | ✏️ Edit on GitHub

Local Testing Environment Setup Guide

This guide provides comprehensive instructions for setting up and using a local Supabase testing environment to debug issues and test database changes safely.

Note: This local environment supplements our cloud-first testing strategy (see ADR-014). Use it for database debugging, migration testing, and reproducing production issues.

Table of Contents

1. Prerequisites
2. Initial Setup
3. Common Use Cases
4. Debugging Production Issues
5. Migration Development
6. Testing Best Practices
7. Troubleshooting
8. Example: Database Error Saving New User

Prerequisites

Before setting up the local testing environment, ensure you have:

• Docker Desktop installed and running
• Supabase CLI installed (brew install supabase/tap/supabase)
• PostgreSQL client (brew install postgresql)
• Node.js 18+ and pnpm installed
• At least 4GB of free disk space

Initial Setup

1. Check Docker Services

# Verify Docker is running
docker ps

# Check if Supabase containers exist
docker ps | grep supabase

2. Start Local Supabase

# From the project root
cd /Users/t/Developer/ocean

# Start Supabase (if not already running)
supabase start

# Check status
supabase status

You should see:

• API URL: http://127.0.0.1:54321
• Database URL: postgresql://postgres:postgres@127.0.0.1:54322/postgres
• Studio URL: http://127.0.0.1:54323

3. Apply Migrations

# Reset database and apply all migrations
supabase db reset --no-seed

# Or apply specific migrations
supabase migration up

4. Set Up Environment Variables

Create .env.local.test:

# Local Supabase URLs
SUPABASE_URL=http://127.0.0.1:54321
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres

# Use local service keys
SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Common Use Cases

Testing User Creation

# Use the debug script
pnpm debug:user "test@example.com" "Test Organization"

# Or run the test suite
node scripts/test-local-user-creation.js

Running SQL Queries

# Connect to local database
psql postgresql://postgres:postgres@127.0.0.1:54322/postgres

# Or use the Supabase Studio UI
open http://127.0.0.1:54323

Testing Migrations

# Create a new migration
supabase migration new my_feature

# Edit the migration file
code supabase/migrations/[timestamp]_my_feature.sql

# Apply and test
supabase migration up

# Rollback if needed
supabase db reset --no-seed

Debugging Production Issues

Step 1: Reproduce Locally

// scripts/reproduce-issue.js
const { createClient } = require('@supabase/supabase-js')

const supabase = createClient('http://127.0.0.1:54321', process.env.SUPABASE_SERVICE_ROLE_KEY)

// Reproduce the exact scenario from production

Step 2: Enable Detailed Logging

-- Enable statement logging
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

-- Check logs
docker logs supabase_db_ocean --tail 100

Step 3: Inspect Trigger Execution

-- Add debug output to triggers
RAISE NOTICE 'Debug: var_name = %', var_name;

-- View notices in psql
\set VERBOSITY verbose

Migration Development

Best Practices

1. Naming Convention: Use YYYYMMDDHHMMSS_description.sql
2. Idempotent: Make migrations safe to run multiple times
3. Transactional: Wrap in BEGIN/COMMIT when possible
4. Test Rollback: Always test migration reversal

Migration Template

-- Migration: Add new feature
-- Safe to run multiple times

BEGIN;

-- Add column if not exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'my_table'
        AND column_name = 'new_column'
    ) THEN
        ALTER TABLE my_table ADD COLUMN new_column TEXT;
    END IF;
END $$;

-- Add index if not exists
CREATE INDEX IF NOT EXISTS idx_my_table_new_column
ON my_table(new_column);

COMMIT;

Testing Best Practices

1. Clean State

# Reset before each test session
supabase db reset --no-seed

# Or clean specific tables
psql -c "TRUNCATE organizations, profiles CASCADE;"

2. Test Data Management

-- Create test data generator
CREATE OR REPLACE FUNCTION create_test_org(
  p_name TEXT DEFAULT 'Test Org',
  p_owner_email TEXT DEFAULT 'test@example.com'
) RETURNS UUID AS $$
DECLARE
  v_user_id UUID;
  v_org_id UUID;
BEGIN
  -- Create test user
  INSERT INTO auth.users (email, email_confirmed_at)
  VALUES (p_owner_email, NOW())
  RETURNING id INTO v_user_id;

  -- Create organization
  INSERT INTO organizations (name, owner_id)
  VALUES (p_name, v_user_id)
  RETURNING id INTO v_org_id;

  RETURN v_org_id;
END;
$$ LANGUAGE plpgsql;

3. Verify RLS Policies

// Test RLS policies
const { data, error } = await supabase.from('organizations').select('*').eq('id', orgId).single()

// Should fail without proper auth
expect(error).toBeTruthy()

Troubleshooting

Container Issues

# Restart containers
supabase stop
supabase start

# Reset everything
supabase stop --backup=false
docker volume prune
supabase start

Migration Conflicts

# Check migration status
supabase migration list

# Fix duplicate timestamps
cd supabase/migrations
ls -la | grep "^202508"  # Find duplicates
# Rename files to unique timestamps

Connection Issues

# Test connection
psql postgresql://postgres:postgres@127.0.0.1:54322/postgres -c "SELECT version();"

# Check container health
docker ps | grep supabase_db

Example: Database Error Saving New User

Error Details

• User: Ryan Taylor (ryan+test007@goldfish.io)
• Organization: Test 007
• Error: "Database error saving a new user"

Where This Error Comes From

The error is generated by the handle_new_user() database trigger that runs after user creation:

-- From supabase/migrations/20241119000003_comprehensive_schema_drift_resolution.sql
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  -- Attempts to create organization and profile
  -- Raises: RAISE EXCEPTION 'Database error saving new user: % (Context: %)', error_msg, error_context;
END;

Common Causes

1. Missing Metadata: The trigger expects organization info in user metadata
2. Slug Generation Conflict: Organization slug already exists
3. Constraint Violations: Missing required fields
4. RLS Policy Blocks: Though trigger runs with SECURITY DEFINER

Debugging Steps

1. Check Provisioning Events

-- Connect to production database
SELECT * FROM provisioning_events
WHERE user_id = (SELECT id FROM auth.users WHERE email = 'ryan+test007@goldfish.io')
ORDER BY created_at DESC;

2. Check Existing Data

-- Check if organization slug exists
SELECT * FROM organizations WHERE slug = 'test-007';

-- Check if user already exists
SELECT * FROM auth.users WHERE email = 'ryan+test007@goldfish.io';

-- Check profiles
SELECT * FROM profiles WHERE email = 'ryan+test007@goldfish.io';

3. Test User Creation Manually

-- Simulate what the trigger does
DO $$
DECLARE
  v_user_id uuid := gen_random_uuid();
  v_org_name text := 'Test 007';
  v_org_slug text;
BEGIN
  -- Generate slug
  v_org_slug := regexp_replace(lower(trim(v_org_name)), '[^a-z0-9-]+', '-', 'g');
  v_org_slug := regexp_replace(v_org_slug, '-+', '-', 'g');
  v_org_slug := trim(both '-' from v_org_slug);

  -- Check if slug exists
  RAISE NOTICE 'Generated slug: %', v_org_slug;

  IF EXISTS (SELECT 1 FROM organizations WHERE slug = v_org_slug) THEN
    RAISE NOTICE 'Slug already exists!';
  END IF;
END $$;

Setting Up Local Testing (Despite Cloud-First Approach)

Option 1: Local Supabase Instance (Recommended for Testing)

# Install Supabase CLI
brew install supabase/tap/supabase

# Initialize local instance
cd /Users/t/Developer/ocean
mkdir supabase-local-test
cd supabase-local-test
supabase init

# Copy production config
cp ../supabase/config.toml ./config.toml

# Update config for local testing
# Edit config.toml - ensure PostgreSQL 17

# Start local Supabase
supabase start

# Apply migrations
supabase db push --db-url "postgresql://postgres:postgres@localhost:54322/postgres"

Option 2: Remote Test Database

# Create a dedicated test project in Supabase
# Use test-specific environment variables
export SUPABASE_TEST_URL="https://your-test-project.supabase.co"
export SUPABASE_TEST_ANON_KEY="your-test-anon-key"
export SUPABASE_TEST_SERVICE_KEY="your-test-service-key"

Test Data Setup

Create supabase/seed.sql:

-- Test users with various scenarios
INSERT INTO auth.users (id, email, raw_user_meta_data) VALUES
  ('11111111-1111-1111-1111-111111111111', 'test-success@example.com',
   '{"organization_name": "Test Success Org"}'::jsonb),
  ('22222222-2222-2222-2222-222222222222', 'test-duplicate@example.com',
   '{"organization_name": "Test 007"}'::jsonb), -- Will fail due to duplicate slug
  ('33333333-3333-3333-3333-333333333333', 'test-no-org@example.com',
   '{}'::jsonb); -- Will fail due to missing org name

-- Pre-existing organization to test conflicts
INSERT INTO public.organizations (id, name, slug, owner_id) VALUES
  ('44444444-4444-4444-4444-444444444444', 'Test 007', 'test-007',
   '11111111-1111-1111-1111-111111111111');

Testing User Creation Flow

1. Test Script

// scripts/test-user-creation.js
import { createClient } from '@supabase/supabase-js'

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

async function testUserCreation(email, orgName) {
  console.log(`Testing user creation: ${email}, ${orgName}`)

  try {
    // First, check if user exists
    const { data: existingUser } = await supabase
      .from('profiles')
      .select('*')
      .eq('email', email)
      .single()

    if (existingUser) {
      console.log('User already exists:', existingUser)
      return
    }

    // Try signup
    const { data, error } = await supabase.auth.admin.createUser({
      email,
      email_confirm: true,
      user_metadata: {
        organization_name: orgName,
        full_name: 'Ryan Taylor',
      },
    })

    if (error) {
      console.error('Auth error:', error)

      // Check provisioning events for details
      const { data: events } = await supabase
        .from('provisioning_events')
        .select('*')
        .order('created_at', { ascending: false })
        .limit(5)

      console.log('Recent provisioning events:', events)
    } else {
      console.log('Success:', data)
    }
  } catch (err) {
    console.error('Unexpected error:', err)
  }
}

// Test the specific failing case
await testUserCreation('ryan+test007@goldfish.io', 'Test 007')

2. Direct Database Testing

-- Test the handle_new_user function directly
BEGIN;

-- Insert test user
INSERT INTO auth.users (id, email, raw_user_meta_data)
VALUES (
  gen_random_uuid(),
  'ryan+test007@goldfish.io',
  jsonb_build_object(
    'organization_name', 'Test 007',
    'full_name', 'Ryan Taylor'
  )
);

-- Check what happened
SELECT * FROM provisioning_events
WHERE created_at > now() - interval '1 minute'
ORDER BY created_at DESC;

SELECT * FROM organizations WHERE name = 'Test 007';
SELECT * FROM profiles WHERE email = 'ryan+test007@goldfish.io';

ROLLBACK; -- Or COMMIT if you want to keep the test data

Monitoring and Debugging

1. Enable Detailed Logging

-- Add debug logging to handle_new_user trigger
CREATE OR REPLACE FUNCTION public.handle_new_user_debug()
RETURNS trigger AS $$
DECLARE
  v_org_name text;
  v_org_slug text;
BEGIN
  RAISE LOG 'handle_new_user triggered for user %', NEW.id;
  RAISE LOG 'User metadata: %', NEW.raw_user_meta_data;

  -- Extract organization name
  v_org_name := COALESCE(
    NEW.raw_user_meta_data->>'organization_name',
    NEW.raw_user_meta_data->>'organization',
    NEW.raw_user_meta_data->>'org_name'
  );

  RAISE LOG 'Extracted org name: %', v_org_name;

  -- Continue with normal logic...
END;

2. Query Patterns for Debugging

-- Find all failed user creations
SELECT
  pe.*,
  au.email,
  au.created_at as user_created_at
FROM provisioning_events pe
LEFT JOIN auth.users au ON pe.user_id = au.id
WHERE pe.event_type = 'user_creation_failed'
  OR pe.status = 'failed'
ORDER BY pe.created_at DESC
LIMIT 20;

-- Check organization slug conflicts
SELECT
  slug,
  COUNT(*) as count,
  array_agg(name) as org_names
FROM organizations
GROUP BY slug
HAVING COUNT(*) > 1;

Improvements to Implement

1. Better Error Messages

Update the trigger to provide more specific error messages:

-- In handle_new_user function
IF v_org_name IS NULL THEN
  RAISE EXCEPTION 'Database error saving new user: Organization name is required in user metadata';
END IF;

IF EXISTS (SELECT 1 FROM organizations WHERE slug = v_org_slug) THEN
  RAISE EXCEPTION 'Database error saving new user: Organization slug "%" already exists', v_org_slug;
END IF;

2. Add Retry Logic

For slug conflicts, add number suffix:

-- Generate unique slug
DECLARE
  v_base_slug text;
  v_counter int := 1;
BEGIN
  v_base_slug := generate_slug(v_org_name);
  v_org_slug := v_base_slug;

  WHILE EXISTS (SELECT 1 FROM organizations WHERE slug = v_org_slug) LOOP
    v_org_slug := v_base_slug || '-' || v_counter;
    v_counter := v_counter + 1;
  END LOOP;
END;

3. Test Harness

Create a comprehensive test suite:

#!/bin/bash
# scripts/test-user-signup.sh

echo "Testing user signup scenarios..."

# Test 1: Normal signup
node scripts/test-user-creation.js "test-normal@example.com" "Normal Org"

# Test 2: Duplicate org name
node scripts/test-user-creation.js "test-dup1@example.com" "Test 007"
node scripts/test-user-creation.js "test-dup2@example.com" "Test 007"

# Test 3: Missing metadata
node scripts/test-user-creation.js "test-no-org@example.com" ""

# Test 4: Special characters
node scripts/test-user-creation.js "test-special@example.com" "Test & Co., Ltd."

# Check results
psql $DATABASE_URL -c "SELECT * FROM provisioning_events WHERE created_at > now() - interval '5 minutes';"

Quick Reference

Essential Commands

# Start/stop local Supabase
supabase start
supabase stop

# Reset database
supabase db reset --no-seed

# Apply migrations
supabase migration up

# Connect to database
psql postgresql://postgres:postgres@127.0.0.1:54322/postgres

# View logs
docker logs supabase_db_ocean --tail 50

# Debug user creation
pnpm debug:user "email@example.com" "Org Name"

Key URLs

• API: http://127.0.0.1:54321
• Studio: http://127.0.0.1:54323
• Database: PostgreSQL://postgres:postgres@127.0.0.1:54322/postgres

File Locations

• Migrations: /supabase/migrations/
• Debug Scripts: /scripts/debug-*.js
• Test Scripts: /scripts/test-*.js
• Environment: /.env.local.test

When to Use Local Testing

✅ Use Local For:

• Database trigger development
• Migration testing
• RLS policy debugging
• Reproducing production bugs
• Rapid prototyping

❌ Use Cloud For:

• Integration testing
• E2E testing
• Performance testing
• External API testing
• Team collaboration

Related Documentation

• ADR-028: Local Testing Environment
• ADR-014: Cloud-First Testing Strategy
• Issue Resolution Project
• System Baseline Analysis
• Supabase CLI Documentation