Skip to main content

Source: ocean/docs/user-provisioning-verification-guide.md | ✏️ Edit on GitHub

User Provisioning Verification Guide

This guide explains how to verify that a user account has been properly provisioned in the Ocean platform, specifically for checking the account ryan+test002@goldfish.io.

Overview​

User provisioning in Ocean involves several steps:

  1. User signs up and creates an auth account
  2. A profile is created in the profiles table
  3. An organization is created and the user is set as owner
  4. Stripe customer is provisioned for billing
  5. Neon database is provisioned for data storage
  6. All provisioning events are logged

Quick Check Methods​

Run the SQL script in your Supabase SQL editor:

# The script is located at:
/scripts/check-user-provisioning.sql

This script will check:

  • User existence in auth.users
  • Profile creation
  • Organization ownership
  • Stripe customer provisioning
  • Neon database provisioning
  • Provisioning event logs
  • Audit logs
  • A summary of provisioning status

Method 2: Node.js Script​

Use the provided Node.js script to check provisioning via GraphQL:

# Set your environment variables
export SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
export VITE_SUPABASE_URL=https://your-project.supabase.co

# Run the check script
node scripts/check-user-provisioning.js

Method 3: GraphQL Query​

Query the GraphQL endpoint directly to check user organizations:

query MyOrganizations {
  myOrganizations {
    id
    name
    slug
    plan
    stripeCustomerId
    stripeSubscriptionId
    stripeSubscriptionStatus
    billingEmail
    createdAt
    updatedAt
  }
}

query ProvisioningEvents($organizationId: ID!) {
  provisioningEvents(organizationId: $organizationId) {
    id
    eventType
    resourceType
    resourceId
    status
    errorMessage
    metadata
    startedAt
    completedAt
  }
}

Triggering Provisioning​

If provisioning hasn't completed, you can trigger it manually:

# Use the trigger script
node scripts/trigger-user-provisioning.js ryan+test002@goldfish.io

# Or use the GraphQL mutation directly
mutation ProvisionUserResources($email: String!, $metadata: JSON) {
  provisionUserResources(email: $email, metadata: $metadata) {
    success
    organizationId
    stripeCustomerId
    neonProjectId
    errors
  }
}

Database Tables to Check​

1. auth.users​

  • Check if user exists with email ryan+test002@goldfish.io
  • Note the user ID and creation time

2. profiles​

  • Verify profile exists for the user
  • Check organization_id field

3. organizations​

  • Check if user owns an organization
  • Verify stripe_customer_id is populated
  • Check plan field (should be 'free' by default)
  • Verify data_region and hosting_region are set

4. organization_databases​

  • Check if Neon database is provisioned
  • Verify status is 'active'
  • Check neon_project_id is populated

5. provisioning_events​

  • Review provisioning event logs
  • Check for any failed events
  • Verify both Stripe and Neon provisioning completed

Common Issues and Solutions​

Issue: User not found​

Solution: User may not have signed up yet. Ensure signup was completed.

Issue: Organization exists but no Stripe customer​

Solution: Run the provisioning mutation to complete Stripe setup:

node scripts/trigger-user-provisioning.js

Issue: Plan not set​

Solution: Update the organization to set plan to 'free':

UPDATE organizations
SET plan = 'free', updated_at = NOW()
WHERE owner_id = (SELECT id FROM auth.users WHERE email = 'ryan+test002@goldfish.io');

Issue: No Neon database provisioned​

Solution: Check provisioning_events for errors, then trigger provisioning again.

Provisioning Flow​

  1. User Signup → Creates auth.users record
  2. Profile Creation → Trigger creates profiles record
  3. Organization Creation → Trigger creates organization with unique slug
  4. Stripe Customer → GraphQL mutation creates Stripe customer
  5. Neon Database → GraphQL mutation provisions Neon project
  6. Event Logging → All steps logged in provisioning_events

Environment Variables Required​

For scripts to work, you need:

  • VITE_SUPABASE_URL: Your Supabase project URL
  • SUPABASE_SERVICE_ROLE_KEY: Service role key for admin access
  • VITE_SUPABASE_ANON_KEY: Anonymous key for public access

Security Notes​

  • The provisionUserResources mutation requires service role access
  • It includes rate limiting (5 attempts per minute per email)
  • User must be created within 10 minutes for provisioning to work
  • All provisioning events are audited
  • /scripts/check-user-provisioning.sql - SQL queries for verification
  • /scripts/check-user-provisioning.js - Node.js verification script
  • /scripts/trigger-user-provisioning.js - Provisioning trigger script
  • /supabase/functions/graphql-v2/resolvers/provisioning.ts - Provisioning logic
  • /supabase/migrations/20250812233110_initial_schema.sql - Database schema