Skip to main content

Source: ocean/docs/adr/0034-materialized-views-strategy.md | ✏️ Edit on GitHub

0034. Materialized Views for Usage Aggregation

Date: 2025-08-14

Status​

Accepted

Context​

After receiving feedback that "Stripe handles revenue analytics," we reconsidered our materialized view strategy. The initial implementation included revenue aggregation that duplicated Stripe's capabilities.

Key insight: Supabase should be the source of truth for:

  • Application state (subscriptions, plans, provisioning)
  • Usage metrics (API calls, data processed)
  • Resource allocation (database provisioning status)

Stripe should handle:

  • Revenue analytics (MRR, ARR, churn)
  • Payment processing
  • Financial reporting

Decision​

We created focused materialized views that aggregate only what Supabase needs to track:

1. Organization Usage Summary​

Aggregates usage metrics for billing and resource planning:

  • Total API calls
  • Data processed (GB)
  • Active users count
  • Current month usage (for billing)

2. Active Subscriptions View​

Quick lookup of subscription states for operational needs:

  • Provisioning status
  • Days until renewal
  • Trial status
  • Resource readiness

Removed Views​

  • Monthly Revenue Summary - Stripe handles this
  • Billing Metrics (MRR/ARR) - Stripe's domain
  • Churn Analytics - Stripe provides better insights

Consequences​

Positive​

  1. Clear Boundaries: No duplication with Stripe
  2. Focused Purpose: Views serve specific application needs
  3. Maintainable: Fewer views to refresh and maintain
  4. Performance: Only aggregate necessary data

Negative​

  1. No Revenue Overview: Must use Stripe dashboard for financial metrics
  2. API Calls: Some reports require Stripe API calls

Design Principles​

  1. Supabase tracks what the application needs to function
  2. Stripe tracks what the business needs to understand revenue
  3. No duplication of capabilities
  4. Views optimize for application performance, not reporting

Alternatives Considered​

  1. Full Analytics Platform

    • Rejected: Stripe already provides this

  2. No Materialized Views

    • Rejected: Usage aggregation queries too expensive

  3. ETL to Analytics Warehouse

    • Deferred: Not needed at current scale

Refresh Strategy​

-- Refresh every 6 hours via pg_cron
SELECT cron.schedule(
  'refresh-usage-views',
  '0 */6 * * *',
  'SELECT refresh_billing_materialized_views();'
);

Future Considerations​

As we scale, we might:

  • Add more usage-focused views
  • Implement real-time usage tracking
  • Create tenant-specific usage views
  • Never duplicate Stripe's analytics

References​