Skip to main content

Source: ocean/docs/adr/0030-database-views-for-billing.md | ✏️ Edit on GitHub

0030. Database Views for Complex Billing Queries

Date: 2025-08-14

Status​

Accepted

Context​

The billing overview page was executing multiple complex queries with joins across 5+ tables:

  • organizations
  • subscription_items
  • stripe_prices
  • stripe_products
  • payment_methods
  • invoices
  • usage_records

This resulted in:

  • Slow page loads (2-3 seconds)
  • Complex resolver logic
  • Difficult to maintain SQL queries
  • High database CPU usage

Decision​

We created optimized database views that pre-join and aggregate billing data, focusing on Supabase as the source of truth for application state while leaving analytics to Stripe.

Views Created​

  1. billing_dashboard: Comprehensive billing information with pre-joined subscription and invoice data
  2. organization_billing_summary: Simplified view for listing organizations
  3. subscription_details: Detailed subscription information with expanded price/product data

Key Design Principles​

  • Supabase stores application state (what plan, what resources)
  • Stripe handles revenue analytics (MRR, churn, etc.)
  • Views optimize for read performance
  • No duplication of Stripe's analytics capabilities

Consequences​

Positive​

  1. Performance: Billing page load reduced from 2s to 200ms
  2. Maintainability: Complex joins centralized in database
  3. Consistency: Single source of truth for billing state
  4. Flexibility: Easy to add new fields to views

Negative​

  1. Schema Coupling: Views depend on table structure
  2. Migration Complexity: View updates require careful planning
  3. Storage: Slight increase in database storage

Performance Improvements​

-- Before: 6 separate queries with complex joins
-- After: 1 query
SELECT * FROM billing_dashboard WHERE organization_id = ?

Alternatives Considered​

  1. Materialized Views Everywhere

    • Rejected: Unnecessary complexity for data that changes frequently

  2. GraphQL Schema Stitching

    • Rejected: Would require direct Stripe API calls from frontend

  3. Caching Layer Only

    • Rejected: Still requires complex queries, just less frequently

Migration Strategy​

  1. Create views without disrupting existing queries
  2. Update GraphQL resolvers to use views
  3. Monitor performance
  4. Remove old query logic

References​