Skip to main content

Source: ocean/docs/adr/ADR-042-supabase-development-best-practices.md | ✏️ Edit on GitHub

ADR-042: Supabase Development Best Practices

Status​

Accepted

Context​

The Ocean platform is built on Supabase as the primary backend infrastructure. We need to ensure we're following Supabase best practices for security, performance, and maintainability.

Decision​

Establish and follow Supabase best practices across the platform.

Current Implementation​

1. Authentication & Security​

Row Level Security (RLS)

  • ✅ All tables have RLS enabled
  • ✅ Policies use JWT claims for user identification
  • ✅ Fail-closed approach (deny by default)
-- Example: Organizations RLS
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their organizations"
  ON organizations FOR SELECT
  USING (
    auth.uid() IN (
      SELECT user_id FROM organization_members
      WHERE organization_id = organizations.id
    )
  );

JWT Security

  • ✅ Using modern JWT keys (no legacy sb_secret_*)
  • ✅ Service role keys only in Edge Functions
  • ✅ Anon key for client-side with RLS

2. Database Design​

Schema Organization

  • ✅ Public schema for user-facing tables
  • ✅ Auth schema managed by Supabase
  • ✅ Storage schema for file management

Naming Conventions

  • ✅ Snake_case for all database objects
  • ✅ Consistent field naming (created_at, updated_at)
  • ✅ Foreign key naming pattern: <table>_<field>

Indexes

  • ✅ Primary keys on all tables
  • ✅ Foreign key indexes for joins
  • ⚠️ Missing: Composite indexes for common query patterns

3. Edge Functions​

Authentication Pattern

  • ✅ Shared wrapper for consistent auth
  • ✅ CORS handling in all functions
  • ✅ Proper error responses
export const createAuthenticatedFunction = (handler) => {
  return serve(async (req) => {
    const corsHeaders = handleCors(req)
    if (req.method === 'OPTIONS') return new Response(null, { headers: corsHeaders })

    const { user, error } = await authenticateRequest(req)
    if (error) return error

    return handler(req, { user, supabase: createClient(req) })
  })
}

Performance

  • ✅ Connection pooling via Supabase client
  • ✅ Minimal cold starts with shared utilities
  • ⚠️ Missing: Response caching for expensive operations

4. Real-time Features​

Subscriptions

  • ✅ Using Postgres Changes for real-time updates
  • ✅ Filtered subscriptions to reduce bandwidth
  • ⚠️ Not implemented: Presence features

5. Storage​

File Management

  • ✅ Bucket-based organization
  • ✅ RLS policies on storage objects
  • ⚠️ Missing: Image transformation policies

6. Migrations​

Version Control

  • ✅ Sequential migrations with timestamps
  • ✅ Atomic operations (transactions)
  • ✅ Down migrations where possible
-- Example migration structure
BEGIN;
  -- Schema changes
  ALTER TABLE organizations
    ADD COLUMN IF NOT EXISTS neon_tenant_id TEXT;

  -- Data migrations
  UPDATE organizations
    SET neon_tenant_id = gen_random_uuid()::text
    WHERE neon_tenant_id IS NULL;

  -- Constraints
  ALTER TABLE organizations
    ALTER COLUMN neon_tenant_id SET NOT NULL;
COMMIT;

7. Performance Optimization​

Query Optimization

  • ✅ Using select() with specific columns
  • ✅ Proper pagination with cursors
  • ✅ Limiting result sets
  • ⚠️ Missing: Query performance monitoring

Connection Management

  • ✅ Single client instance per context
  • ✅ Connection pooling in Edge Functions
  • ✅ Proper client disposal

8. Error Handling​

Database Errors

  • ✅ Consistent error response format
  • ✅ Proper error codes (PGRST prefixes)
  • ✅ User-friendly error messages

Retry Logic

  • ✅ Exponential backoff for transient failures
  • ✅ Idempotent operations
  • ⚠️ Missing: Circuit breaker pattern

9. Testing​

Local Development

  • ✅ Local Supabase for development
  • ✅ Seed data for testing
  • ✅ Migration testing before deployment

10. Monitoring & Observability​

Logging

  • ✅ Structured logging in Edge Functions
  • ⚠️ Missing: Centralized log aggregation
  • ⚠️ Missing: Performance metrics

Recommendations​

High Priority​

  1. Add composite indexes for common query patterns
  2. Implement query performance monitoring
  3. Add response caching for expensive operations
  4. Set up centralized logging with log aggregation

Medium Priority​

  1. Implement circuit breaker for external services
  2. Add image transformation policies for storage
  3. Enable connection pooling configuration
  4. Add database backup automation

Low Priority​

  1. Implement presence features for collaboration
  2. Add GraphQL subscriptions for real-time updates
  3. Create data archival strategy

Best Practices Summary​

✅ Currently Following​

  • Row Level Security on all tables
  • Proper JWT key management
  • Snake_case naming conventions
  • Edge Function authentication wrapper
  • Atomic migrations with version control
  • Proper error handling
  • Local development environment

⚠️ Areas for Improvement​

  • Query performance monitoring
  • Response caching strategies
  • Centralized logging
  • Composite indexes for performance
  • Circuit breaker implementation
  • Automated backups

❌ Not Implemented​

  • Real-time presence
  • Image transformations
  • GraphQL subscriptions
  • Data archival
  • ADR-005: Supabase JWT Authentication
  • ADR-014: Cloud-First Testing Strategy
  • ADR-028: Local Testing Environment
  • ADR-035: Atomic Provisioning Pattern
  • ADR-036: Unified Field Naming Convention