Skip to main content

Source: ocean/docs/stripe-integration.md | ✏️ Edit on GitHub

Stripe Integration Guide

Overview​

This guide explains how Ocean's Stripe integration works, including subscription management, plan-based limits, and secure credential storage.

Architecture​

1. Vault Encryption​

All sensitive database credentials are encrypted using Supabase Vault:

  • Uses deterministic encryption for searchability
  • Keys are managed by Supabase and never exposed
  • Credentials are decrypted only when needed

2. Plan-Based Limits​

Organizations have dynamic limits based on their Stripe subscription:

  • Free: 3 members, 1 database, 10GB storage
  • Starter: 10 members, 3 databases, 50GB storage
  • Professional: 50 members, 10 databases, 200GB storage
  • Enterprise: Unlimited resources

3. Automatic Provisioning​

When a user signs up:

  1. Organization is created with the user as owner
  2. Stripe customer is created with free trial
  3. Neon database is provisioned in selected region
  4. All credentials are encrypted and stored securely

Setting Up Stripe Products​

  1. Run the setup script to create products and prices:
STRIPE_SECRET_KEY=sk_test_... pnpm tsx scripts/setup-stripe-products.ts
  1. Save the generated IDs and add to Edge Function environment:
# Set the free tier price ID
pnpm supabase secrets set STRIPE_FREE_PRICE_ID=price_xxx

  1. Configure webhook endpoint in Stripe Dashboard:

    • URL: https://fldiayolmgphysdwgsuk.supabase.co/functions/v1/handle-stripe-webhook
    • Events to listen for:
      • customer.subscription.created
      • customer.subscription.updated
      • customer.subscription.deleted
      • invoice.payment_succeeded
      • invoice.payment_failed

  2. Add webhook secret to Edge Functions:

pnpm supabase secrets set STRIPE_WEBHOOK_SECRET=whsec_xxx

Database Schema​

Organizations Table​

-- New columns for subscription management
stripe_price_id TEXT                    -- Current price ID
stripe_subscription_status TEXT         -- Subscription status
trial_ends_at TIMESTAMPTZ              -- Trial end date
current_period_start TIMESTAMPTZ       -- Billing period start
current_period_end TIMESTAMPTZ         -- Billing period end
plan_limits JSONB                      -- Dynamic plan limits
usage_metrics JSONB                    -- Current usage tracking

Encrypted Credentials​

-- organization_databases table
database_password_encrypted BYTEA      -- Vault-encrypted password
connection_string_encrypted BYTEA      -- Vault-encrypted connection string

Usage Enforcement​

Member Limits​

-- Check before adding members
SELECT check_organization_limits('org-id', 'member', 1);

Database Limits​

-- Check before provisioning databases
SELECT check_organization_limits('org-id', 'database', 1);

Usage Monitoring​

-- View usage vs limits
SELECT * FROM organization_usage_summary WHERE id = 'org-id';

Edge Functions​

provision-user-resources​

  • Creates Stripe customer and subscription
  • Provisions Neon database
  • Encrypts and stores credentials
  • Updates organization with plan limits

handle-stripe-webhook​

  • Processes subscription changes
  • Updates plan limits dynamically
  • Handles payment failures
  • Manages subscription lifecycle

Security​

  1. Encryption at REST: All database credentials encrypted with Supabase Vault
  2. Access Control: Only admins can view decrypted credentials
  3. Webhook Verification: All Stripe webhooks are cryptographically verified
  4. Service Role Only: Decryption functions restricted to Edge Functions

Testing​

  1. Test subscription creation:
pnpm tsx scripts/test-stripe-subscription.ts
  1. Verify encryption:
-- Check encrypted credentials exist
SELECT id, database_password_encrypted IS NOT NULL as encrypted
FROM organization_databases;
  1. Test limit enforcement:
-- Try to exceed member limit
INSERT INTO organization_members (organization_id, user_id, role)
VALUES ('org-id', 'user-id', 'member');
-- Should fail if at limit

Monitoring​

Track subscription health:

SELECT
  name,
  stripe_subscription_status,
  plan_limits,
  usage_metrics
FROM organizations
WHERE stripe_subscription_status IN ('past_due', 'unpaid');

Future Improvements​

  1. Activity Logging: Track all resource provisioning and limit changes
  2. Usage Alerts: Notify when approaching limits
  3. Metered Billing: Track API calls and storage usage
  4. Granular Permissions: Role-based feature access