Skip to main content

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

ADR-005: Multi-Tenant Database Provisioning Architecture

Date: 2025-07-30 Status: Proposed

Context​

We need to implement a multi-tenant database provisioning system where each organization gets its own isolated Neon database. Currently, the system has partial infrastructure in place but tenant provisioning is not fully implemented.

Current State​

  1. Database Structure:

    • organizations table has tenant fields (neon_tenant_id, neon_database_url)
    • organization_databases table exists for tracking provisioned databases
    • provisioning_events table for audit trail

  2. Edge Function:

    • provision-user-resources handles Stripe customer creation
    • Has code for Neon provisioning but may not be fully deployed

  3. GraphQL:

    • Organization queries exist but don't expose tenant data
    • No mutations for tenant management

Decision​

Implement a complete tenant provisioning system with the following architecture:

1. Database Schema Updates​

-- Ensure organization_databases table has all needed fields
ALTER TABLE organization_databases
ADD COLUMN IF NOT EXISTS graphql_endpoint text,
ADD COLUMN IF NOT EXISTS api_key_encrypted bytea,
ADD COLUMN IF NOT EXISTS ssl_certificate_encrypted bytea;

-- Add provisioning status tracking
ALTER TABLE organizations
ADD COLUMN IF NOT EXISTS provisioning_status jsonb DEFAULT '{
  "neon": "pending",
  "graphql": "pending",
  "ssl": "pending"
}'::jsonb;

2. Provisioning Workflow​

3. GraphQL Schema Extensions​

extend type Organization {
  provisioningStatus: ProvisioningStatus!
  databaseStatus: DatabaseStatus
}

type ProvisioningStatus {
  neon: ServiceStatus!
  graphql: ServiceStatus!
  ssl: ServiceStatus!
  lastUpdated: DateTime!
}

type DatabaseStatus {
  provider: String!
  region: String!
  status: String!
  healthCheck: HealthCheckResult
}

type HealthCheckResult {
  status: String!
  latency: Int
  lastChecked: DateTime!
}

extend type Mutation {
  provisionTenant(input: ProvisionTenantInput!): ProvisionTenantPayload!
  retryProvisioning(organizationId: ID!): ProvisioningStatus!
  checkTenantHealth(organizationId: ID!): HealthCheckResult!
}

input ProvisionTenantInput {
  organizationId: ID!
  region: String!
  databaseSize: DatabaseSize
}

enum DatabaseSize {
  SMALL
  MEDIUM
  LARGE
}

enum ServiceStatus {
  PENDING
  PROVISIONING
  ACTIVE
  FAILED
  SUSPENDED
}

4. Implementation Components​

A. Enhanced Edge Function (provision-tenant-resources)​

  • Provision Neon database with proper naming convention
  • Setup GraphQL endpoint on the tenant database
  • Configure SSL certificates
  • Store all credentials encrypted in Supabase Vault
  • Update provisioning status atomically

B. GraphQL Resolvers​

  • Add provisioning status to organization queries
  • Implement health check resolvers
  • Add retry logic for failed provisioning
  • Implement proper authorization checks

C. Client Components​

  • Provisioning status UI component
  • Database health dashboard
  • Retry provisioning button
  • Region selector during onboarding

D. Monitoring & Management​

  • Background job to check tenant health
  • Automated retry for failed provisioning
  • Usage metrics collection
  • Cost tracking per tenant

5. Security Considerations​

  1. Credential Management:

    • All database credentials encrypted using Supabase Vault
    • Service role keys never exposed to client
    • Separate API keys per tenant

  2. Network Isolation:

    • Each tenant gets unique database endpoint
    • SSL/TLS enforcement
    • IP allowlisting support

  3. Access Control:

    • Only organization owners can view provisioning status
    • Admin dashboard for super admin management
    • Audit logging for all provisioning events

6. Migration Strategy​

  1. Phase 1: Deploy updated schema and edge functions
  2. Phase 2: Add GraphQL mutations and UI components
  3. Phase 3: Migrate existing organizations
  4. Phase 4: Enable automatic provisioning for new signups

Consequences​

Positive​

  • True multi-tenant isolation
  • Scalable architecture
  • Better compliance and security
  • Per-tenant performance tuning
  • Easier backup/restore per tenant

Negative​

  • Increased infrastructure complexity
  • Higher operational costs
  • More complex monitoring
  • Longer initial setup time

Implementation Checklist​

  • Update database schema
  • Enhance provision-user-resources edge function
  • Create GraphQL schema extensions
  • Implement GraphQL resolvers
  • Create tenant provisioning UI
  • Add monitoring dashboard
  • Write migration scripts
  • Update documentation
  • Add integration tests
  • Deploy in stages