Billing Models

Guide to implementing billing and subscription management for multi-tenant SaaS.

Overview

Lager Guru's multi-tenant architecture supports various billing models. This document outlines common patterns and implementation strategies.

Billing Model Types

1. Per-Tenant Billing

Each tenant pays a fixed monthly/annual fee regardless of usage.

Use Cases:

• Small to medium businesses
• Predictable revenue
• Simple pricing structure

Implementation:

-- Subscription table
CREATE TABLE public.tenant_subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid UNIQUE NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  plan_id text NOT NULL, -- 'basic', 'professional', 'enterprise'
  status text NOT NULL CHECK (status IN ('active', 'canceled', 'past_due', 'trialing')),
  current_period_start timestamp with time zone NOT NULL,
  current_period_end timestamp with time zone NOT NULL,
  cancel_at_period_end boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

-- Billing history
CREATE TABLE public.tenant_billing_history (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  subscription_id uuid REFERENCES public.tenant_subscriptions(id),
  amount numeric NOT NULL,
  currency text DEFAULT 'USD',
  status text NOT NULL CHECK (status IN ('pending', 'paid', 'failed', 'refunded')),
  invoice_url text,
  paid_at timestamp with time zone,
  created_at timestamp with time zone DEFAULT now()
);

Pricing Tiers:

const PRICING_PLANS = {
  basic: {
    name: 'Basic',
    price: 99, // USD per month
    features: ['up_to_10_users', 'basic_support'],
    limits: { users: 10, storage_gb: 10 },
  },
  professional: {
    name: 'Professional',
    price: 299,
    features: ['unlimited_users', 'priority_support', 'api_access'],
    limits: { users: null, storage_gb: 100 },
  },
  enterprise: {
    name: 'Enterprise',
    price: 999,
    features: ['unlimited_users', 'dedicated_support', 'sso', 'custom_integrations'],
    limits: { users: null, storage_gb: null },
  },
};

2. Per-User Billing

Tenant pays based on number of active users.

Use Cases:

• Scalable pricing
• Fair cost distribution
• Usage-based revenue

Implementation:

-- Track user counts
CREATE TABLE public.tenant_user_counts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid UNIQUE NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  active_users integer NOT NULL DEFAULT 0,
  billed_users integer NOT NULL DEFAULT 0, -- Users included in billing
  last_counted_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

-- Pricing per user
CREATE TABLE public.tenant_pricing (
  tenant_id uuid PRIMARY KEY REFERENCES public.tenants(id) ON DELETE CASCADE,
  price_per_user numeric NOT NULL, -- e.g., 10.00 USD
  billing_cycle text NOT NULL CHECK (billing_cycle IN ('monthly', 'annual')),
  minimum_users integer DEFAULT 1,
  created_at timestamp with time zone DEFAULT now()
);

Billing Calculation:

async function calculateTenantBill(tenantId: string) {
  const { data: userCount } = await supabase
    .from('tenant_user_counts')
    .select('billed_users')
    .eq('tenant_id', tenantId)
    .single();
  
  const { data: pricing } = await supabase
    .from('tenant_pricing')
    .select('price_per_user, billing_cycle')
    .eq('tenant_id', tenantId)
    .single();
  
  const monthlyAmount = (userCount.billed_users || 0) * (pricing.price_per_user || 0);
  
  return {
    tenant_id: tenantId,
    user_count: userCount.billed_users,
    price_per_user: pricing.price_per_user,
    monthly_amount: monthlyAmount,
    annual_amount: monthlyAmount * 12,
  };
}

3. Usage-Based Billing

Tenant pays based on actual usage (API calls, storage, transactions).

Use Cases:

• Pay-as-you-go
• High variability in usage
• Cost reflects value

Implementation:

-- Usage tracking
CREATE TABLE public.tenant_usage (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  metric_type text NOT NULL, -- 'api_calls', 'storage_gb', 'shipments', 'users'
  quantity numeric NOT NULL,
  period_start timestamp with time zone NOT NULL,
  period_end timestamp with time zone NOT NULL,
  created_at timestamp with time zone DEFAULT now()
);

-- Usage-based pricing
CREATE TABLE public.usage_pricing (
  metric_type text PRIMARY KEY,
  unit_price numeric NOT NULL, -- Price per unit
  tier_breaks jsonb, -- { "0-1000": 0.01, "1000-10000": 0.008, ... }
  created_at timestamp with time zone DEFAULT now()
);

Usage Tracking:

async function trackUsage(
  tenantId: string,
  metricType: string,
  quantity: number
) {
  const periodStart = new Date();
  periodStart.setDate(1); // First of month
  const periodEnd = new Date(periodStart);
  periodEnd.setMonth(periodEnd.getMonth() + 1);
  
  await supabase.from('tenant_usage').upsert({
    tenant_id: tenantId,
    metric_type: metricType,
    quantity,
    period_start: periodStart.toISOString(),
    period_end: periodEnd.toISOString(),
  }, {
    onConflict: 'tenant_id,metric_type,period_start',
  });
}

// Example: Track API calls
await trackUsage(tenantId, 'api_calls', 1);

// Example: Track storage
const storageGB = await calculateStorage(tenantId);
await trackUsage(tenantId, 'storage_gb', storageGB);

Billing Calculation:

async function calculateUsageBill(tenantId: string, period: Date) {
  const { data: usage } = await supabase
    .from('tenant_usage')
    .select('metric_type, quantity')
    .eq('tenant_id', tenantId)
    .gte('period_start', period.toISOString())
    .lt('period_end', new Date(period.getTime() + 30 * 24 * 60 * 60 * 1000).toISOString());
  
  const { data: pricing } = await supabase
    .from('usage_pricing')
    .select('*');
  
  let total = 0;
  const breakdown: Record<string, number> = {};
  
  usage?.forEach(metric => {
    const price = pricing?.find(p => p.metric_type === metric.metric_type);
    if (price) {
      const cost = metric.quantity * price.unit_price;
      total += cost;
      breakdown[metric.metric_type] = cost;
    }
  });
  
  return { total, breakdown };
}

Hybrid Models

Combine multiple billing approaches:

interface HybridBilling {
  baseFee: number; // Fixed monthly fee
  perUserFee: number; // Per active user
  usageFees: {
    apiCalls: number;
    storageGB: number;
    shipments: number;
  };
}

async function calculateHybridBill(tenantId: string): Promise<HybridBilling> {
  const subscription = await getSubscription(tenantId);
  const userCount = await getUserCount(tenantId);
  const usage = await getUsage(tenantId);
  
  return {
    baseFee: subscription.base_fee,
    perUserFee: userCount * subscription.price_per_user,
    usageFees: {
      apiCalls: usage.api_calls * 0.001,
      storageGB: usage.storage_gb * 0.10,
      shipments: usage.shipments * 0.50,
    },
  };
}

Integration with Payment Providers

Stripe Integration

// Create Stripe customer for tenant
async function createStripeCustomer(tenantId: string) {
  const { data: tenant } = await supabase
    .from('tenants')
    .select('name, company')
    .eq('id', tenantId)
    .single();
  
  const customer = await stripe.customers.create({
    name: tenant.name,
    metadata: { tenant_id: tenantId },
  });
  
  // Store Stripe customer ID
  await supabase
    .from('tenants')
    .update({ stripe_customer_id: customer.id })
    .eq('id', tenantId);
  
  return customer;
}

// Create subscription
async function createSubscription(tenantId: string, planId: string) {
  const { data: tenant } = await supabase
    .from('tenants')
    .select('stripe_customer_id')
    .eq('id', tenantId)
    .single();
  
  const subscription = await stripe.subscriptions.create({
    customer: tenant.stripe_customer_id,
    items: [{ price: PRICING_PLANS[planId].stripe_price_id }],
    metadata: { tenant_id: tenantId },
  });
  
  // Store subscription
  await supabase.from('tenant_subscriptions').insert({
    tenant_id: tenantId,
    plan_id: planId,
    stripe_subscription_id: subscription.id,
    status: subscription.status,
    current_period_start: new Date(subscription.current_period_start * 1000),
    current_period_end: new Date(subscription.current_period_end * 1000),
  });
}

Feature Gating

Restrict features based on subscription plan:

async function checkFeatureAccess(
  tenantId: string,
  feature: string
): Promise<boolean> {
  const { data: subscription } = await supabase
    .from('tenant_subscriptions')
    .select('plan_id, status')
    .eq('tenant_id', tenantId)
    .single();
  
  if (subscription.status !== 'active') {
    return false;
  }
  
  const plan = PRICING_PLANS[subscription.plan_id];
  return plan.features.includes(feature);
}

// Usage
const canUseSSO = await checkFeatureAccess(tenantId, 'sso');
if (!canUseSSO) {
  throw new Error('SSO requires Enterprise plan');
}

Usage Limits

Enforce limits based on plan:

async function checkUsageLimit(
  tenantId: string,
  metric: string,
  currentUsage: number
): Promise<{ allowed: boolean; limit: number | null }> {
  const { data: subscription } = await supabase
    .from('tenant_subscriptions')
    .select('plan_id')
    .eq('tenant_id', tenantId)
    .single();
  
  const plan = PRICING_PLANS[subscription.plan_id];
  const limit = plan.limits[metric];
  
  if (limit === null) {
    return { allowed: true, limit: null }; // Unlimited
  }
  
  return {
    allowed: currentUsage < limit,
    limit,
  };
}

// Usage
const { allowed, limit } = await checkUsageLimit(tenantId, 'users', userCount);
if (!allowed) {
  throw new Error(`User limit reached (${limit} users)`);
}

Billing Webhooks

Handle payment provider webhooks:

// supabase/functions/stripe-webhook/index.ts
Deno.serve(async (req) => {
  const signature = req.headers.get('stripe-signature');
  const body = await req.text();
  
  const event = stripe.webhooks.constructEvent(
    body,
    signature,
    Deno.env.get('STRIPE_WEBHOOK_SECRET')!
  );
  
  switch (event.type) {
    case 'invoice.payment_succeeded':
      await handlePaymentSuccess(event.data.object);
      break;
    case 'invoice.payment_failed':
      await handlePaymentFailed(event.data.object);
      break;
    case 'customer.subscription.deleted':
      await handleSubscriptionCanceled(event.data.object);
      break;
  }
  
  return new Response(JSON.stringify({ received: true }), {
    headers: { 'Content-Type': 'application/json' },
  });
});

async function handlePaymentSuccess(invoice: any) {
  const tenantId = invoice.metadata.tenant_id;
  
  // Record payment
  await adminClient.from('tenant_billing_history').insert({
    tenant_id: tenantId,
    amount: invoice.amount_paid / 100, // Convert from cents
    status: 'paid',
    paid_at: new Date(invoice.status_transitions.paid_at * 1000),
    invoice_url: invoice.hosted_invoice_url,
  });
  
  // Update subscription
  await adminClient
    .from('tenant_subscriptions')
    .update({ status: 'active' })
    .eq('tenant_id', tenantId);
}

Best Practices

1. Clear Pricing: Make pricing transparent and easy to understand
2. Usage Tracking: Accurately track all billable metrics
3. Grace Periods: Allow grace periods for payment failures
4. Notifications: Notify tenants of billing events
5. Audit Trail: Keep complete billing history
6. Compliance: Follow payment regulations (PCI DSS, etc.)

Related Documentation

• Tenant Bootstrap - Setting up new tenants
• Database Structure - Billing table schemas