Source: ocean/docs/graphql-provisioning-implementation-plan.md | ✏️ Edit on GitHub

GraphQL-Based Tenant Provisioning Implementation Plan

Overview

Transform the current webhook-based provisioning system to a GraphQL-first approach for B2B platform requirements.

Current State

✅ Database triggers create organization on signup
✅ Webhook triggers Stripe customer creation
❌ No Neon database provisioning
❌ Multiple uncoordinated webhooks
❌ No visibility into provisioning status

Target Architecture

Phase 1: Database Schema Updates (Day 1)

1.1 Add Provisioning Tracking Fields

-- Migration: 20250806_add_provisioning_tracking.sql

-- Add provisioning status and metadata to organizations
ALTER TABLE public.organizations
ADD COLUMN IF NOT EXISTS provisioning_status jsonb DEFAULT jsonb_build_object(
  'stripe', jsonb_build_object('status', 'pending', 'started_at', null, 'completed_at', null, 'error', null),
  'neon', jsonb_build_object('status', 'pending', 'started_at', null, 'completed_at', null, 'error', null),
  'overall_status', 'pending',
  'version', 1
),
ADD COLUMN IF NOT EXISTS provisioning_metadata jsonb DEFAULT '{}',
ADD COLUMN IF NOT EXISTS neon_project_id text,
ADD COLUMN IF NOT EXISTS neon_database_ready boolean DEFAULT false;

-- Create provisioning history table for audit trail
CREATE TABLE IF NOT EXISTS public.provisioning_history (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  service text NOT NULL CHECK (service IN ('stripe', 'neon', 'other')),
  action text NOT NULL,
  status text NOT NULL CHECK (status IN ('started', 'completed', 'failed', 'retried')),
  details jsonb,
  error jsonb,
  created_at timestamptz DEFAULT now(),
  created_by uuid REFERENCES auth.users(id)
);

-- Indexes for performance
CREATE INDEX idx_provisioning_history_org_id ON public.provisioning_history(organization_id);
CREATE INDEX idx_provisioning_history_created_at ON public.provisioning_history(created_at DESC);
CREATE INDEX idx_organizations_provisioning_status ON public.organizations((provisioning_status->>'overall_status'));

-- RLS policies
ALTER TABLE public.provisioning_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their org provisioning history" ON public.provisioning_history
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM public.organization_members
      WHERE user_id = auth.uid()
    )
  );

1.2 Create Status Update Functions

-- Helper function to update provisioning status
CREATE OR REPLACE FUNCTION public.update_provisioning_status(
  p_org_id uuid,
  p_service text,
  p_status text,
  p_error jsonb DEFAULT NULL,
  p_metadata jsonb DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_current_status jsonb;
  v_new_status jsonb;
  v_now timestamptz := now();
BEGIN
  -- Get current status
  SELECT provisioning_status INTO v_current_status
  FROM organizations WHERE id = p_org_id;

  -- Update service status
  v_new_status := v_current_status;

  -- Update specific service
  v_new_status := jsonb_set(
    v_new_status,
    ARRAY[p_service, 'status'],
    to_jsonb(p_status)
  );

  -- Set timestamps
  IF p_status = 'in_progress' THEN
    v_new_status := jsonb_set(
      v_new_status,
      ARRAY[p_service, 'started_at'],
      to_jsonb(v_now)
    );
  ELSIF p_status IN ('completed', 'failed') THEN
    v_new_status := jsonb_set(
      v_new_status,
      ARRAY[p_service, 'completed_at'],
      to_jsonb(v_now)
    );
  END IF;

  -- Add error if failed
  IF p_status = 'failed' AND p_error IS NOT NULL THEN
    v_new_status := jsonb_set(
      v_new_status,
      ARRAY[p_service, 'error'],
      p_error
    );
  END IF;

  -- Update overall status
  IF (v_new_status->'stripe'->>'status' = 'completed' AND
      v_new_status->'neon'->>'status' = 'completed') THEN
    v_new_status := jsonb_set(v_new_status, '{overall_status}', '"completed"');
  ELSIF (v_new_status->'stripe'->>'status' = 'failed' OR
         v_new_status->'neon'->>'status' = 'failed') THEN
    v_new_status := jsonb_set(v_new_status, '{overall_status}', '"failed"');
  ELSIF (v_new_status->'stripe'->>'status' = 'in_progress' OR
         v_new_status->'neon'->>'status' = 'in_progress') THEN
    v_new_status := jsonb_set(v_new_status, '{overall_status}', '"in_progress"');
  END IF;

  -- Update organization
  UPDATE organizations
  SET
    provisioning_status = v_new_status,
    provisioning_metadata = COALESCE(provisioning_metadata, '{}') || COALESCE(p_metadata, '{}')
  WHERE id = p_org_id;

  -- Log to history
  INSERT INTO provisioning_history (organization_id, service, action, status, details, error)
  VALUES (p_org_id, p_service, 'status_update', p_status, p_metadata, p_error);

  RETURN v_new_status;
END;
$$;

Phase 2: GraphQL Schema Updates (Day 1-2)

2.1 Update GraphQL Schema

# In schema.graphql

type Organization {
  id: ID!
  name: String!
  slug: String!
  # ... existing fields ...

  # New provisioning fields
  provisioningStatus: ProvisioningStatus!
  provisioningHistory: [ProvisioningEvent!]!
  isDatabaseReady: Boolean!
}

type ProvisioningStatus {
  stripe: ServiceProvisioningStatus!
  neon: ServiceProvisioningStatus!
  overallStatus: ProvisioningState!
  lastUpdated: DateTime!
}

type ServiceProvisioningStatus {
  status: ProvisioningState!
  startedAt: DateTime
  completedAt: DateTime
  error: ProvisioningError
}

type ProvisioningError {
  code: String!
  message: String!
  details: JSON
  retryable: Boolean!
}

type ProvisioningEvent {
  id: ID!
  service: String!
  action: String!
  status: String!
  details: JSON
  createdAt: DateTime!
}

enum ProvisioningState {
  PENDING
  IN_PROGRESS
  COMPLETED
  FAILED
  RETRYING
}

# Mutations
type Mutation {
  # New signup with provisioning
  signUpWithOrganization(input: SignUpInput!): SignUpPayload!

  # Retry failed provisioning
  retryProvisioning(organizationId: ID!, service: ProvisioningService): ProvisioningStatus!

  # Check provisioning status
  refreshProvisioningStatus(organizationId: ID!): ProvisioningStatus!
}

input SignUpInput {
  email: String!
  password: String!
  firstName: String!
  lastName: String!
  organizationName: String!
  industry: String
  dataRegion: String!
}

type SignUpPayload {
  user: User!
  organization: Organization!
  provisioningStarted: Boolean!
  nextSteps: [String!]!
}

enum ProvisioningService {
  STRIPE
  NEON
  ALL
}

2.2 GraphQL Resolvers

// src/supabase/functions/graphql-v2/resolvers/provisioning.ts

import { createStripeCustomer, createNeonProject } from '../services/provisioning'
import { updateProvisioningStatus } from '../services/database'

export const provisioningResolvers = {
  Mutation: {
    signUpWithOrganization: async (_, { input }, { supabase }) => {
      // 1. Create user account
      const { data: authData, error: authError } = await supabase.auth.signUp({
        email: input.email,
        password: input.password,
        options: {
          data: {
            first_name: input.firstName,
            last_name: input.lastName,
            organization: input.organizationName,
            industry: input.industry,
            hosting_region: input.dataRegion,
          },
        },
      })

      if (authError) throw authError

      // 2. Wait for organization to be created by trigger
      const org = await waitForOrganization(authData.user.id, 5000)

      if (!org) {
        throw new Error('Organization creation timeout')
      }

      // 3. Start async provisioning
      startProvisioningAsync(org.id, authData.user)

      return {
        user: authData.user,
        organization: org,
        provisioningStarted: true,
        nextSteps: [
          'Check your email to verify your account',
          'Resources are being provisioned in the background',
          'You can start using the platform immediately',
        ],
      }
    },

    retryProvisioning: async (_, { organizationId, service }, { requireAuth }) => {
      const user = await requireAuth()

      // Verify user has access to this org
      await verifyOrgAccess(user.id, organizationId)

      if (service === 'ALL' || !service) {
        await retryAllProvisioning(organizationId)
      } else {
        await retryServiceProvisioning(organizationId, service.toLowerCase())
      }

      return getProvisioningStatus(organizationId)
    },
  },

  Organization: {
    provisioningStatus: async (org) => {
      return formatProvisioningStatus(org.provisioning_status)
    },

    provisioningHistory: async (org, _, { supabase }) => {
      const { data } = await supabase
        .from('provisioning_history')
        .select('*')
        .eq('organization_id', org.id)
        .order('created_at', { ascending: false })
        .limit(20)

      return data || []
    },

    isDatabaseReady: (org) => {
      return org.neon_database_ready || false
    },
  },
}

// Async provisioning function
async function startProvisioningAsync(orgId: string, user: any) {
  // Don't await - let it run in background
  Promise.all([provisionStripe(orgId, user), provisionNeon(orgId, user)]).catch((error) => {
    console.error('Provisioning error:', error)
    // Error is logged but doesn't affect user signup
  })
}

async function provisionStripe(orgId: string, user: any) {
  try {
    await updateProvisioningStatus(orgId, 'stripe', 'in_progress')

    const customer = await createStripeCustomer({
      email: user.email,
      name: `${user.user_metadata.first_name} ${user.user_metadata.last_name}`,
      metadata: {
        organization_id: orgId,
        user_id: user.id,
      },
    })

    // Update org with stripe customer id
    await supabase.from('organizations').update({ stripe_customer_id: customer.id }).eq('id', orgId)

    await updateProvisioningStatus(orgId, 'stripe', 'completed', null, {
      customer_id: customer.id,
    })
  } catch (error) {
    await updateProvisioningStatus(orgId, 'stripe', 'failed', {
      code: error.code || 'UNKNOWN',
      message: error.message,
      retryable: true,
    })
    throw error
  }
}

async function provisionNeon(orgId: string, user: any) {
  try {
    await updateProvisioningStatus(orgId, 'neon', 'in_progress')

    const org = await getOrganization(orgId)
    const project = await createNeonProject({
      name: `${org.slug}-${org.id}`.substring(0, 50),
      region: mapDataRegionToNeon(org.data_region),
    })

    // Store encrypted credentials
    await storeNeonCredentials(orgId, project)

    await updateProvisioningStatus(orgId, 'neon', 'completed', null, {
      project_id: project.id,
      region: project.region,
    })
  } catch (error) {
    await updateProvisioningStatus(orgId, 'neon', 'failed', {
      code: error.code || 'UNKNOWN',
      message: error.message,
      retryable: true,
    })
    throw error
  }
}

Phase 3: Edge Function Updates (Day 2)

3.1 Simplify Edge Functions

// supabase/functions/provision-resources/index.ts

import { createClient } from '@supabase/supabase-js'
import { corsHeaders } from '../_shared/cors'

interface ProvisionRequest {
  organizationId: string
  services: string[]
  retryAttempt?: number
}

Deno.serve(async (req) => {
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  try {
    const { organizationId, services = ['stripe', 'neon'] }: ProvisionRequest = await req.json()

    const supabase = createClient(
      Deno.env.get('SUPABASE_URL')!,
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
    )

    // Get organization details
    const { data: org, error: orgError } = await supabase
      .from('organizations')
      .select('*, organization_members!inner(user:users(*))')
      .eq('id', organizationId)
      .single()

    if (orgError || !org) {
      throw new Error('Organization not found')
    }

    const results = {
      stripe: null,
      neon: null,
      errors: [],
    }

    // Provision requested services in parallel
    const promises = services.map(async (service) => {
      try {
        switch (service) {
          case 'stripe':
            results.stripe = await provisionStripeCustomer(org)
            break
          case 'neon':
            results.neon = await provisionNeonDatabase(org)
            break
        }
      } catch (error) {
        results.errors.push({ service, error: error.message })
      }
    })

    await Promise.allSettled(promises)

    return new Response(
      JSON.stringify({
        success: results.errors.length === 0,
        results,
        organizationId,
      }),
      {
        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({
        error: error.message,
      }),
      {
        status: 400,
        headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      }
    )
  }
})

Phase 4: Frontend Integration (Day 2-3)

// src/components/signup-form.tsx

import { useMutation } from '@tanstack/react-query'
import { SIGN_UP_WITH_ORGANIZATION } from '@/graphql/mutations'

export function SignUpForm() {
  const [signUp, { loading }] = useMutation(SIGN_UP_WITH_ORGANIZATION, {
    onCompleted: (data) => {
      // Redirect to email verification page
      router.push('/verify-email', {
        state: {
          email: data.signUpWithOrganization.user.email,
          organizationId: data.signUpWithOrganization.organization.id
        }
      })
    }
  })

  const handleSubmit = async (values) => {
    await signUp({
      variables: {
        input: {
          email: values.email,
          password: values.password,
          firstName: values.firstName,
          lastName: values.lastName,
          organizationName: values.organizationName,
          industry: values.industry,
          dataRegion: values.dataRegion || 'us-east-1'
        }
      }
    })
  }

  return (
    <form onSubmit={handleSubmit}>
      {/* Form fields */}
      <Button type="submit" disabled={loading}>
        {loading ? 'Creating your account...' : 'Get Started'}
      </Button>
    </form>
  )
}

4.2 Provisioning Status Component

// src/components/provisioning-status.tsx

export function ProvisioningStatus({ organizationId }) {
  const { data, loading } = useQuery(GET_PROVISIONING_STATUS, {
    variables: { organizationId },
    pollInterval: 5000, // Poll every 5 seconds
    skip: !organizationId
  })

  if (loading) return <Skeleton />

  const status = data?.organization?.provisioningStatus

  return (
    <Card>
      <CardHeader>
        <CardTitle>Setting up your workspace</CardTitle>
      </CardHeader>
      <CardContent>
        <div className="space-y-4">
          <ProvisioningStep
            name="Billing System"
            status={status?.stripe?.status}
            icon={<CreditCard />}
          />
          <ProvisioningStep
            name="Database"
            status={status?.neon?.status}
            icon={<Database />}
          />
        </div>

        {status?.overallStatus === 'failed' && (
          <Alert variant="destructive" className="mt-4">
            <AlertDescription>
              Some resources failed to provision.
              <Button
                variant="link"
                onClick={() => retryProvisioning(organizationId)}
              >
                Retry
              </Button>
            </AlertDescription>
          </Alert>
        )}
      </CardContent>
    </Card>
  )
}

Phase 5: Migration & Cleanup (Day 3)

5.1 Remove Old Webhooks

-- Migration: 20250806_remove_webhook_triggers.sql

-- Remove old webhook triggers
DROP TRIGGER IF EXISTS sync_user_to_stripe_on_insert ON auth.users;
DROP TRIGGER IF EXISTS provision_tenant_on_user_insert ON auth.users;

-- Remove old webhook functions (after verifying they're not used)
DROP FUNCTION IF EXISTS public.sync_new_user_to_stripe() CASCADE;
DROP FUNCTION IF EXISTS public.webhook_provision_user_resources() CASCADE;

-- Clean up pg_net requests table (optional)
DELETE FROM net.http_request_queue
WHERE created_at < NOW() - INTERVAL '30 days';

5.2 Backfill Existing Organizations

-- Script to provision resources for existing orgs without Stripe/Neon

DO $$
DECLARE
  org record;
BEGIN
  FOR org IN
    SELECT id, name, created_at
    FROM organizations
    WHERE stripe_customer_id IS NULL
       OR neon_project_id IS NULL
    ORDER BY created_at DESC
  LOOP
    -- Call GraphQL mutation or Edge Function to provision
    PERFORM net.http_post(
      url := current_setting('app.settings.supabase_url') || '/functions/v1/provision-resources',
      headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || current_setting('app.settings.service_role_key')
      ),
      body := jsonb_build_object(
        'organizationId', org.id,
        'services', ARRAY['stripe', 'neon']
      )
    );

    -- Log the attempt
    RAISE NOTICE 'Queued provisioning for org %: %', org.id, org.name;

    -- Rate limit to avoid overwhelming APIs
    PERFORM pg_sleep(1);
  END LOOP;
END $$;

Phase 6: Monitoring & Observability (Day 4)

6.1 Add Monitoring Queries

-- Create monitoring views

CREATE OR REPLACE VIEW provisioning_metrics AS
SELECT
  COUNT(*) FILTER (WHERE provisioning_status->>'overall_status' = 'completed') as completed,
  COUNT(*) FILTER (WHERE provisioning_status->>'overall_status' = 'in_progress') as in_progress,
  COUNT(*) FILTER (WHERE provisioning_status->>'overall_status' = 'failed') as failed,
  COUNT(*) FILTER (WHERE provisioning_status->>'overall_status' = 'pending') as pending,
  AVG(
    EXTRACT(EPOCH FROM (
      (provisioning_status->'stripe'->>'completed_at')::timestamptz -
      (provisioning_status->'stripe'->>'started_at')::timestamptz
    ))
  ) as avg_stripe_duration_seconds,
  AVG(
    EXTRACT(EPOCH FROM (
      (provisioning_status->'neon'->>'completed_at')::timestamptz -
      (provisioning_status->'neon'->>'started_at')::timestamptz
    ))
  ) as avg_neon_duration_seconds
FROM organizations
WHERE created_at > NOW() - INTERVAL '30 days';

-- Alert on stuck provisioning
CREATE OR REPLACE FUNCTION check_stuck_provisioning()
RETURNS TABLE (
  organization_id uuid,
  organization_name text,
  stuck_duration interval,
  provisioning_status jsonb
)
LANGUAGE sql
AS $$
  SELECT
    id,
    name,
    NOW() - created_at as stuck_duration,
    provisioning_status
  FROM organizations
  WHERE provisioning_status->>'overall_status' = 'in_progress'
    AND created_at < NOW() - INTERVAL '10 minutes'
  ORDER BY created_at;
$$;

6.2 Add Sentry Monitoring

// In provisioning functions

import * as Sentry from '@sentry/node'

async function provisionStripe(orgId: string, user: any) {
  const transaction = Sentry.startTransaction({
    op: 'provisioning',
    name: 'provision.stripe',
    data: { organization_id: orgId },
  })

  try {
    // ... provisioning logic ...
    transaction.setStatus('ok')
  } catch (error) {
    transaction.setStatus('internal_error')
    Sentry.captureException(error, {
      tags: {
        provisioning_service: 'stripe',
        organization_id: orgId,
      },
    })
    throw error
  } finally {
    transaction.finish()
  }
}

Testing Strategy

Unit Tests

// __tests__/provisioning.test.ts

describe('Provisioning System', () => {
  it('should create user and organization atomically', async () => {
    const result = await signUpWithOrganization({
      email: 'test@example.com',
      organizationName: 'Test Corp',
    })

    expect(result.user).toBeDefined()
    expect(result.organization).toBeDefined()
    expect(result.provisioningStarted).toBe(true)
  })

  it('should handle Stripe API failures gracefully', async () => {
    // Mock Stripe to fail
    mockStripeError()

    const org = await createOrganization()
    await provisionStripe(org.id, user)

    const status = await getProvisioningStatus(org.id)
    expect(status.stripe.status).toBe('failed')
    expect(status.stripe.error.retryable).toBe(true)
  })

  it('should retry failed provisioning', async () => {
    const org = await createOrgWithFailedProvisioning()

    await retryProvisioning(org.id, 'stripe')

    const status = await getProvisioningStatus(org.id)
    expect(status.stripe.status).toBe('completed')
  })
})

Integration Tests

Test full signup flow
Test concurrent signups
Test retry mechanisms
Test monitoring queries

Load Tests

100 concurrent signups
Measure provisioning time
Check for race conditions

Rollback Plan

If issues arise:

Immediate Rollback (< 5 minutes)

-- Re-enable old webhook if needed
CREATE TRIGGER sync_user_to_stripe_on_insert
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.sync_new_user_to_stripe();

Gradual Rollback
- Keep GraphQL for new signups
- Use webhooks for specific services
- Monitor and fix issues
Data Recovery
- All provisioning attempts are logged
- Can replay from provisioning_history
- Manual provision via admin panel

Success Metrics

Performance
- Signup to dashboard < 3 seconds
- Full provisioning < 30 seconds
- 99% success rate
Reliability
- 0 orphaned organizations
- < 1% provisioning failures
- 100% retry success rate
User Experience
- Users can start immediately
- Clear provisioning status
- Self-service retry option

Timeline

Day 1: Database schema updates, GraphQL schema
Day 2: GraphQL resolvers, Edge Function updates
Day 3: Frontend integration, migration scripts
Day 4: Monitoring, testing, documentation
Day 5: Deploy to staging, test thoroughly
Day 6: Production deployment, monitor closely
Day 7: Backfill existing organizations

Total: 1 week from start to full production deployment

Overview​

Current State​

Target Architecture​

Phase 1: Database Schema Updates (Day 1)​

1.1 Add Provisioning Tracking Fields​

1.2 Create Status Update Functions​

Phase 2: GraphQL Schema Updates (Day 1-2)​

2.1 Update GraphQL Schema​

2.2 GraphQL Resolvers​

Phase 3: Edge Function Updates (Day 2)​

3.1 Simplify Edge Functions​

Phase 4: Frontend Integration (Day 2-3)​

4.1 Signup Flow​

4.2 Provisioning Status Component​

Phase 5: Migration & Cleanup (Day 3)​

5.1 Remove Old Webhooks​

5.2 Backfill Existing Organizations​

Phase 6: Monitoring & Observability (Day 4)​

6.1 Add Monitoring Queries​

6.2 Add Sentry Monitoring​

Testing Strategy​

Unit Tests​

Integration Tests​

Load Tests​

Rollback Plan​

Success Metrics​

Timeline​