Source: ocean/docs/cleanup-automation.md | ✏️ Edit on GitHub

Cleanup Automation Guide

This guide explains the automated cleanup system for handling failed provisioning attempts and unverified accounts in the Ocean platform.

Overview

The cleanup automation system consists of:

1. Database Functions - Identify and track cleanup candidates
2. Edge Functions - Execute cleanup operations
3. Scheduled Jobs - Automate regular cleanup runs

Components

1. Database Functions

get_failed_provisions(hours_threshold, include_incomplete)

• Identifies organizations with failed, stuck, or incomplete provisioning
• Parameters:
  • hours_threshold (default: 24) - How many hours old a provision must be
  • include_incomplete (default: true) - Include stuck/incomplete provisions

get_cleanup_candidates(hours_threshold)

• Finds unverified user accounts older than the threshold
• Parameters:
  • hours_threshold (default: 72) - Hours since account creation

cleanup_unverified_accounts(hours_threshold, dry_run)

• Deletes unverified accounts and their organizations
• Parameters:
  • hours_threshold (default: 72) - Hours since account creation
  • dry_run (default: false) - Simulate without deleting

record_cleanup_attempt(organization_id, cleanup_type, success, resources_cleaned, error_message)

• Records cleanup attempts for audit trail
• Creates entries in the provisioning_events table

2. Edge Functions

/cleanup-failed-provisions

Handles cleanup of failed provisioning attempts.

Request:

{
  "hoursThreshold": 24, // Age of provisions to cleanup
  "includeIncomplete": true, // Include stuck provisions
  "dryRun": false, // Test mode without deletion
  "maxCleanups": 10 // Limit per run
}

Response:

{
  "success": true,
  "dryRun": false,
  "totalFound": 15,
  "processed": 10,
  "successful": 9,
  "failed": 1,
  "stripeDeleted": 8,
  "neonDeleted": 9,
  "nextRunSuggested": true
}

/scheduled-cleanup

Unified cleanup function for both unverified accounts and failed provisions.

Query Parameters:

• type: unverified, failed, or all (default: all)
• hours: Hours threshold (default: 24)
• dry_run: Test mode (default: false)

Example URLs:

/scheduled-cleanup?type=all&hours=24
/scheduled-cleanup?type=failed&hours=48&dry_run=true
/scheduled-cleanup?type=unverified&hours=72

3. Existing Functions

/cleanup-resources

• Deletes external resources (Stripe customers, Neon databases)
• Called by other cleanup functions
• Handles individual resource deletion

Setting Up Scheduled Cleanup

Option 1: Supabase Cron Jobs (Recommended)

Add to your Supabase project's cron jobs:

-- Daily cleanup of failed provisions (24+ hours old)
SELECT
  cron.schedule(
    'cleanup-failed-provisions',
    '0 2 * * *', -- Run at 2 AM UTC daily
    $$
    SELECT
      net.http_post(
        url := current_setting('app.settings.supabase_url') || '/functions/v1/scheduled-cleanup?type=failed&hours=24',
        headers := jsonb_build_object(
          'Authorization', 'Bearer ' || current_setting('app.settings.supabase_service_role_key')
        )
      );
    $$
  );

-- Weekly cleanup of unverified accounts (72+ hours old)
SELECT
  cron.schedule(
    'cleanup-unverified-accounts',
    '0 3 * * 0', -- Run at 3 AM UTC on Sundays
    $$
    SELECT
      net.http_post(
        url := current_setting('app.settings.supabase_url') || '/functions/v1/scheduled-cleanup?type=unverified&hours=72',
        headers := jsonb_build_object(
          'Authorization', 'Bearer ' || current_setting('app.settings.supabase_service_role_key')
        )
      );
    $$
  );

Option 2: External Cron Service

Use services like GitHub Actions, Vercel Cron, or cron-job.org:

# Daily cleanup
curl -X POST https://your-project.supabase.co/functions/v1/scheduled-cleanup?type=all \
  -H "Authorization: Bearer YOUR_SERVICE_ROLE_KEY"

Option 3: GitHub Actions (Example)

name: Cleanup Failed Provisions
on:
  schedule:
    - cron: '0 2 * * *' # Daily at 2 AM UTC
  workflow_dispatch: # Manual trigger

jobs:
  cleanup:
    runs-on: ubuntu-latest
    steps:
      - name: Run Cleanup
        run: |
          curl -X POST ${{ secrets.SUPABASE_URL }}/functions/v1/scheduled-cleanup?type=all \
            -H "Authorization: Bearer ${{ secrets.SUPABASE_SERVICE_ROLE_KEY }}" \
            -H "Content-Type: application/json"

Testing Cleanup

1. Dry Run Mode

Always test with dry run first:

# Test failed provisions cleanup
curl -X POST https://your-project.supabase.co/functions/v1/cleanup-failed-provisions \
  -H "Authorization: Bearer YOUR_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "hoursThreshold": 1,
    "dryRun": true
  }'

2. Monitor Cleanup Status

Query the monitoring view:

SELECT * FROM cleanup_monitoring;

3. Check Provisioning Events

Review cleanup history:

SELECT *
FROM provisioning_events
WHERE event_type LIKE 'cleanup_%'
ORDER BY created_at DESC
LIMIT 20;

Best Practices

1. Start Conservative: Begin with high hour thresholds (48-72 hours)
2. Use Dry Run: Always test with dryRun: true first
3. Monitor Regularly: Check cleanup logs and error rates
4. Gradual Rollout: Start with small maxCleanups values
5. Alert on Failures: Set up alerts for cleanup errors

Troubleshooting

Common Issues

1. "Unauthorized" errors

   • Ensure you're using the service role key
   • Check the authorization header format

2. Resources not deleted

   • Check if Stripe/Neon APIs are available
   • Verify API keys are correctly configured
   • Review circuit breaker status in logs

3. Partial cleanups

   • Normal for resources with dependencies
   • Review error messages in response
   • Check provisioning_events for details

Manual Cleanup

For specific organizations:

curl -X POST https://your-project.supabase.co/functions/v1/cleanup-resources \
  -H "Authorization: Bearer YOUR_SERVICE_ROLE_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "delete_all",
    "organizationId": "org-uuid-here"
  }'

Security Considerations

1. Authorization: Only service role keys can trigger cleanup
2. Audit Trail: All cleanup attempts are logged
3. Safe Defaults: Conservative thresholds prevent accidental deletion
4. Rollback Protection: Failed provisions already use atomic rollback

Monitoring

Use the cleanup monitoring view to track:

• Failed provisions count
• Stuck provisions count
• Blocked provisions count
• Oldest issue date
• Most recent event

-- Real-time cleanup metrics
SELECT * FROM cleanup_monitoring;

-- Cleanup success rate (last 7 days)
SELECT
  COUNT(*) FILTER (WHERE status = 'completed') AS successful,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed,
  COUNT(*) AS total
FROM provisioning_events
WHERE event_type LIKE 'cleanup_%'
AND created_at > NOW() - INTERVAL '7 days';