Skip to main content

Source: ocean/docs/tenant-provisioning-workplan.md | ✏️ Edit on GitHub

Tenant Provisioning Implementation Workplan

Current State Analysis​

What's Working​

  • ✅ User signup creates organization via handle_new_user() trigger
  • ✅ Stripe customer creation via sync_user_to_stripe webhook (separate)
  • ✅ Comprehensive provision-user-resources Edge Function exists

What's Missing​

  • ❌ No trigger calling the comprehensive provisioning function
  • ❌ Two separate webhooks causing potential race conditions
  • ❌ No Neon database provisioning happening
  • ❌ No status tracking for provisioning steps

Implementation Plan​

Phase 1: Database Schema Updates​

-- 1. Add provisioning status tracking to organizations
ALTER TABLE organizations
ADD COLUMN IF NOT EXISTS provisioning_status jsonb DEFAULT jsonb_build_object(
  'stripe', 'pending',
  'neon', 'pending',
  'overall', 'pending',
  'last_updated', now()
),
ADD COLUMN IF NOT EXISTS provisioning_started_at timestamptz,
ADD COLUMN IF NOT EXISTS provisioning_completed_at timestamptz,
ADD COLUMN IF NOT EXISTS provisioning_error jsonb;

-- 2. Create provisioning_logs table for debugging
CREATE TABLE IF NOT EXISTS provisioning_logs (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  organization_id uuid REFERENCES organizations(id),
  user_id uuid REFERENCES auth.users(id),
  step text NOT NULL,
  status text NOT NULL,
  details jsonb,
  error jsonb,
  created_at timestamptz DEFAULT now()
);

-- 3. Add indexes for performance
CREATE INDEX idx_provisioning_logs_org_id ON provisioning_logs(organization_id);
CREATE INDEX idx_provisioning_logs_created_at ON provisioning_logs(created_at);
CREATE INDEX idx_organizations_provisioning_status ON organizations((provisioning_status->>'overall'));

Phase 2: Consolidate Webhooks​

Option A: Single Comprehensive Webhook (Recommended)

-- 1. Create new consolidated webhook function
CREATE OR REPLACE FUNCTION public.provision_new_tenant()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  request_id bigint;
  service_role_key text;
  edge_function_url text;
  org_record record;
BEGIN
  -- Get the organization that was just created
  SELECT * INTO org_record
  FROM organizations
  WHERE id = (
    SELECT organization_id
    FROM organization_members
    WHERE user_id = NEW.id
    ORDER BY created_at DESC
    LIMIT 1
  );

  IF NOT FOUND THEN
    RAISE LOG 'No organization found for user %, skipping provisioning', NEW.id;
    RETURN NEW;
  END IF;

  -- Get service role key from vault
  SELECT decrypted_secret INTO service_role_key
  FROM vault.decrypted_secrets
  WHERE name = 'SERVICE_ROLE_KEY';

  -- Build Edge Function URL
  edge_function_url := current_setting('app.settings.supabase_url', true) || '/functions/v1/provision-user-resources';

  -- Update provisioning status
  UPDATE organizations
  SET
    provisioning_status = jsonb_build_object(
      'stripe', 'in_progress',
      'neon', 'in_progress',
      'overall', 'in_progress',
      'last_updated', now()
    ),
    provisioning_started_at = now()
  WHERE id = org_record.id;

  -- Log provisioning start
  INSERT INTO provisioning_logs (organization_id, user_id, step, status, details)
  VALUES (org_record.id, NEW.id, 'provisioning_started', 'in_progress',
    jsonb_build_object('email', NEW.email, 'org_name', org_record.name));

  -- Call comprehensive provisioning Edge Function
  SELECT net.http_post(
    url := edge_function_url,
    body := jsonb_build_object(
      'type', 'INSERT',
      'table', 'users',
      'record', row_to_json(NEW),
      'organization', row_to_json(org_record)
    ),
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || service_role_key
    ),
    timeout_milliseconds := 30000
  ) INTO request_id;

  RAISE LOG 'Provisioning request % for user % and org %', request_id, NEW.email, org_record.name;

  RETURN NEW;
END $$;

-- 2. Replace existing 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;

CREATE TRIGGER provision_tenant_on_user_insert
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.provision_new_tenant();

Phase 3: Update Edge Function​

Update provision-user-resources to:

  1. Add status updates throughout the process
// After Stripe customer creation
await supabase
  .from('organizations')
  .update({
    provisioning_status: {
      stripe: 'completed',
      neon: 'in_progress',
      overall: 'in_progress',
      last_updated: new Date().toISOString(),
    },
  })
  .eq('id', organization.id)

// After Neon provisioning
await supabase
  .from('organizations')
  .update({
    provisioning_status: {
      stripe: 'completed',
      neon: 'completed',
      overall: 'completed',
      last_updated: new Date().toISOString(),
    },
    provisioning_completed_at: new Date().toISOString(),
  })
  .eq('id', organization.id)
  1. Add comprehensive error handling
// On any error
await supabase
  .from('organizations')
  .update({
    provisioning_status: {
      ...currentStatus,
      overall: 'failed',
      last_updated: new Date().toISOString(),
    },
    provisioning_error: {
      message: error.message,
      timestamp: new Date().toISOString(),
      step: failedStep,
    },
  })
  .eq('id', organization.id)

Phase 4: Add Monitoring & Recovery​

  1. Create monitoring function
CREATE OR REPLACE FUNCTION public.check_provisioning_status()
RETURNS TABLE (
  organization_id uuid,
  organization_name text,
  provisioning_status jsonb,
  duration interval,
  needs_attention boolean
)
LANGUAGE sql
AS $$
  SELECT
    id,
    name,
    provisioning_status,
    CASE
      WHEN provisioning_completed_at IS NOT NULL
      THEN provisioning_completed_at - provisioning_started_at
      ELSE now() - provisioning_started_at
    END as duration,
    CASE
      WHEN provisioning_status->>'overall' = 'failed' THEN true
      WHEN provisioning_status->>'overall' = 'in_progress'
        AND provisioning_started_at < now() - interval '10 minutes' THEN true
      ELSE false
    END as needs_attention
  FROM organizations
  WHERE provisioning_started_at IS NOT NULL
  ORDER BY provisioning_started_at DESC;
$$;
  1. Create retry function
CREATE OR REPLACE FUNCTION public.retry_tenant_provisioning(org_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  org_record record;
  user_record record;
  request_id bigint;
BEGIN
  -- Get organization and user details
  SELECT o.*, u.*
  INTO org_record
  FROM organizations o
  JOIN organization_members om ON om.organization_id = o.id
  JOIN auth.users u ON u.id = om.user_id
  WHERE o.id = org_id
  AND om.role = 'owner'
  LIMIT 1;

  -- Reset status
  UPDATE organizations
  SET
    provisioning_status = jsonb_build_object(
      'stripe', CASE
        WHEN stripe_customer_id IS NOT NULL THEN 'completed'
        ELSE 'pending'
      END,
      'neon', 'pending',
      'overall', 'pending',
      'last_updated', now()
    ),
    provisioning_error = null
  WHERE id = org_id;

  -- Trigger provisioning again
  -- (Implementation details...)

  RETURN jsonb_build_object(
    'success', true,
    'organization_id', org_id,
    'message', 'Provisioning retry initiated'
  );
END $$;

Phase 5: Add GraphQL Support​

extend type Organization {
  provisioningStatus: ProvisioningStatus
  provisioningError: ProvisioningError
}

type ProvisioningStatus {
  stripe: String!
  neon: String!
  overall: String!
  lastUpdated: DateTime!
  duration: Int
}

type ProvisioningError {
  message: String!
  timestamp: DateTime!
  step: String
}

extend type Query {
  organizationsNeedingAttention: [Organization!]!
}

extend type Mutation {
  retryProvisioning(organizationId: ID!): ProvisioningResult!
}

Migration Steps​

  1. Backup current data
  2. Deploy schema changes
  3. Deploy updated Edge Function
  4. Test with single user
  5. Create new trigger
  6. Disable old triggers
  7. Monitor provisioning
  8. Run recovery for existing orgs without Neon

Testing Plan​

  1. Unit Tests

    • Edge Function logic
    • Database trigger behavior
    • Error scenarios

  2. Integration Tests

    • Full signup flow
    • Retry mechanisms
    • Status tracking

  3. Load Tests

    • Multiple simultaneous signups
    • Network failures
    • API rate limits

Rollback Plan​

If issues arise:

  1. Disable new trigger
  2. Re-enable old triggers
  3. Manually provision failed accounts
  4. Fix issues and redeploy

Success Metrics​

  • All new signups get Stripe + Neon within 2 minutes
  • 0% orphaned organizations (org without Stripe or Neon)
  • Provisioning status accurately reflects state
  • Failed provisioning can be retried successfully