Skip to main content

Source: ocean/docs/adr/028-local-testing-environment.md | ✏️ Edit on GitHub

ADR-028: Local Testing Environment Setup

Status​

Accepted

Context​

Despite our cloud-first testing strategy (ADR-014), we encountered critical production issues that were difficult to debug without a local environment:

  1. "Database error saving a new user" - A production bug in the handle_new_user trigger that referenced a non-existent column
  2. Complex trigger debugging - Database triggers and RLS policies are difficult to test in shared cloud environments
  3. Migration testing - No way to safely test schema changes before production deployment
  4. Rapid iteration - Cloud environments add latency to the development cycle

The specific issue that prompted this decision was a bug where the handle_new_user trigger tried to insert into an error column in provisioning_events, but the actual column was named error_message. This simple bug caused all user signups to fail with a generic error message.

Decision​

We will maintain a local Supabase testing environment alongside our cloud-first approach for specific use cases:

  1. Database trigger development and debugging
  2. Migration testing and validation
  3. RLS policy testing
  4. Reproducing production issues locally
  5. Rapid prototyping of database changes

The local environment will:

  • Use Docker containers managed by Supabase CLI
  • Match production PostgreSQL version (17.x)
  • Apply all production migrations
  • Use separate environment variables (.env.local.test)
  • Not replace cloud testing for integration and E2E tests

Implementation​

1. Local Supabase Setup​

# Using existing Supabase CLI and Docker
supabase status  # Check local services
supabase db reset --no-seed  # Apply migrations

2. Environment Configuration​

# .env.local.test
SUPABASE_URL=http://127.0.0.1:54321
DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres

3. Migration Management​

  • Fixed migration naming conflicts (multiple files with same date prefix)
  • Created migration to fix the handle_new_user trigger bug
  • Established naming convention: YYYYMMDDHHMMSS_description.sql

4. Debug Tools Created​

  • /scripts/debug-user-creation.js - Interactive user creation debugger
  • /scripts/debug-user-creation.sql - SQL queries for investigation
  • /scripts/test-local-user-creation.js - Automated test scenarios

5. Documentation​

  • /docs/LOCAL_TESTING_SETUP.md - Comprehensive setup guide
  • Debug scripts with --help flags
  • SQL scripts for common debugging scenarios

Consequences​

Positive​

  1. Faster debugging - Reproduce and fix issues in minutes, not hours
  2. Safe experimentation - Test migrations and triggers without affecting others
  3. Better understanding - Direct database access reveals hidden issues
  4. Cost savings - Reduce cloud resource usage for development
  5. Offline development - Work without internet connection

Negative​

  1. Environment drift - Local may diverge from production
  2. Additional maintenance - Must keep Docker containers updated
  3. Incomplete parity - Can't test all cloud features locally (Edge Functions with external APIs)
  4. Developer discipline - Must remember to test in cloud before deploying

Neutral​

  1. Hybrid approach - Cloud-first remains primary, local is supplementary
  2. Optional setup - Developers can choose when to use local
  3. Migration complexity - Must handle both local and cloud migrations

Mitigation Strategies​

  1. Regular sync - Pull production schema changes weekly
  2. CI/CD validation - All changes must pass cloud tests
  3. Documentation - Clear guides on when to use local vs cloud
  4. Monitoring - Track which bugs are caught locally vs cloud

Metrics for Success​

  • Time to resolve database-related bugs
  • Number of production incidents related to schema/trigger issues
  • Developer satisfaction with debugging capabilities
  • Migration success rate

Alternatives Considered​

  1. Cloud-only with better logging - Still too slow for rapid iteration
  2. Dedicated test cloud instances - Expensive and still has latency
  3. Database unit tests only - Doesn't catch trigger/RLS interactions
  4. Full local stack - Too complex, violates cloud-first principle

References​

  • ADR-014: Cloud-First Testing Strategy
  • Issue: "Database error saving a new user" production bug
  • PR: #[number] - Local testing environment setup
  • Supabase CLI documentation

Review Date​

February 2025 - Evaluate effectiveness and usage patterns