Source: ocean/docs/adr/023-database-trigger-error-handling.md | ✏️ Edit on GitHub

ADR-023: Database Trigger Error Handling

Date: 2024-08-20

Status

Accepted

Context

The handle_new_user trigger was failing silently or with generic errors when:

1. Expected metadata fields were missing or had different names
2. Organization creation failed
3. Profile creation failed

The generic "Database error saving new user" message made debugging difficult, and the trigger would fail completely rather than gracefully handling edge cases.

Decision

We will implement comprehensive error handling in database triggers:

1. Defensive programming - Handle missing or misnamed fields
2. Detailed error context - Track execution flow and values
3. Error logging - Log errors to provisioning_events table
4. Graceful degradation - Continue with defaults when possible

Implementation Pattern

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
DECLARE
  error_context TEXT;
  error_msg TEXT;
  error_detail TEXT;
BEGIN
  BEGIN
    -- Track context throughout execution
    error_context := 'Starting handle_new_user';

    -- Defensive field extraction
    org_name := COALESCE(
      NEW.raw_user_meta_data->>'organization_name',
      NEW.raw_user_meta_data->>'organization',  -- Handle both field names
      'My Organization'  -- Default fallback
    );

    error_context := format('Extracted org_name=%s', org_name);

    -- Main logic here...

    -- Log success
    INSERT INTO provisioning_events (
      event_type, status, metadata
    ) VALUES (
      'user.signup', 'completed',
      jsonb_build_object('context', error_context)
    );

    RETURN NEW;

  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
      error_msg = MESSAGE_TEXT,
      error_detail = PG_EXCEPTION_DETAIL;

    -- Log detailed error
    INSERT INTO provisioning_events (
      event_type, status, error, metadata
    ) VALUES (
      'user.signup', 'failed', error_msg,
      jsonb_build_object(
        'error_detail', error_detail,
        'error_context', error_context,
        'user_id', NEW.id,
        'email', NEW.email
      )
    );

    -- Re-raise with context
    RAISE EXCEPTION 'Database error saving new user: % (Context: %)',
      error_msg, error_context;
  END;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Error Tracking Standards

1. Always track context throughout execution
2. Use COALESCE for field extraction with defaults
3. Log both successes and failures to provisioning_events
4. Include all available context in error metadata
5. Re-raise with meaningful message for debugging

Consequences

Positive

• Much easier to debug trigger failures
• Can handle schema evolution gracefully
• Audit trail of all provisioning attempts
• Can identify patterns in failures

Negative

• More complex trigger code
• Additional database writes for logging
• Need to manage provisioning_events table size

Monitoring

1. Set up alerts on provisioning_events failures
2. Regular review of error patterns
3. Clean up old provisioning_events records
4. Monitor trigger execution time