Skip to main content

Source: ocean/docs/adr/0052-authentication-flow-and-organization-provisioning.md | ✏️ Edit on GitHub

ADR-052: Authentication Flow and Organization Provisioning

Status​

Accepted

Context​

The Ocean platform uses a multi-tenant architecture where every user belongs to an organization. Understanding the authentication and provisioning flow is critical for debugging issues and maintaining the system.

Decision​

Authentication Flow​

The system follows this specific flow for user registration:

Key Design Principles​

  1. Organization-First Architecture

    • Every user MUST belong to an organization
    • Organizations are created during user signup
    • No orphaned users without organizations

  2. User-Organization Relationship

    • First user becomes the organization owner
    • Organizations table has owner_id referencing auth.users
    • This creates a dependency: user must exist before organization

  3. Atomic Provisioning

    • All provisioning happens in the handle_new_user trigger
    • If any step fails, the entire provisioning fails
    • Failures are logged to provisioning_events table

Database Schema Relationships​

auth.users (Supabase managed)
    ↓ (trigger on insert)
handle_new_user() function creates:
    ↓
organizations
    - id: UUID (PK)
    - owner_id: UUID (FK → auth.users.id)
    - name: TEXT
    - slug: TEXT (unique)
    ↓
profiles
    - id: UUID (PK, FK → auth.users.id)
    - organization_id: UUID (FK → organizations.id)
    - email: TEXT
    ↓
organization_members
    - organization_id: UUID (FK → organizations.id)
    - user_id: UUID (FK → auth.users.id)
    - role: 'owner' | 'admin' | 'member'
    - status: 'active' | 'pending' | 'suspended'

Critical Fields in Signup​

When a user signs up, these fields are passed in raw_user_meta_data:

  • organization_name or organization: Organization name
  • first_name: User's first name
  • last_name: User's last name
  • data_region: Where to host the tenant database (defaults to 'us-east-1')

Error Handling​

All errors in provisioning are logged to provisioning_events with:

  • status: 'failed'
  • error_message: The actual error
  • error_context: Debug information about what was attempted

Consequences​

Positive​

  • Clear separation between authentication (Supabase) and application logic
  • Atomic provisioning ensures data consistency
  • Organization-based security from the start
  • Audit trail of all provisioning attempts

Negative​

  • Complex trigger logic that must be maintained
  • Schema drift between environments can break provisioning
  • Tight coupling between auth and business logic
  • Debugging requires checking multiple tables

Trade-offs​

  • We chose trigger-based provisioning over application-level to ensure no user can exist without an organization
  • This makes the system more rigid but ensures data integrity
  • Schema changes require careful migration planning

Current Issues (September 2025)​

Schema Drift​

Production and local environments have diverged:

  • Column naming differences (error vs error_message)
  • Missing columns (status in organization_members)
  • Check constraint differences (allowed data_region values)
  • Function visibility issues (generate_unique_slug)

Why Account Creation Fails​

  1. Production Issues (Now Fixed):

    • ✅ handle_new_user tried to insert into error column but table had error_message
    • ✅ Missing columns: trial_ends_at, seats_included, seats_used
    • ✅ Function parameter mismatch in generate_unique_slug

  2. Local Environment Issues:

    • ❌ Missing status column in organization_members
    • ❌ Invalid default data_region value
    • ❌ Function permission issues
    • ❌ NOT NULL constraint on provisioning_events.organization_id

The root cause is that migrations were applied directly to production without being committed to version control, causing schema drift.