Track G: Revenue & Billing Operations

Executive Summary

This document provides comprehensive evidence of the BIO-QMS platform's revenue and billing operations architecture. The system is designed to support a multi-tier SaaS business model with usage-based billing, automated revenue recognition compliant with ASC 606, and full PCI DSS compliance through Stripe integration.

Business Model Overview:

• 4-Tier Subscription Plans: Starter ($36K-$60K/yr), Professional ($96K-$180K/yr), Enterprise ($240K-$600K/yr), Enterprise Plus ($400K+/yr)
• Pricing Components: Base platform fee + per-seat pricing + work order overage charges
• Target Metrics: NRR 160%, MRR growth 15% MoM, expansion revenue 40% of new ARR
• Payment Terms: Monthly/quarterly/annual recurring billing, ACH/wire for enterprise (net-30/60)

Regulatory Compliance:

• PCI DSS v4.0: Level 1 compliance via Stripe Elements (SAQ-A scope reduction)
• SOC 2 Type II: Financial controls, audit trail, access controls
• ASC 606: Five-step revenue recognition model, deferred revenue management
• SOX: Public company financial controls (if applicable)

Technology Stack:

• Backend: NestJS (TypeScript), Prisma ORM, PostgreSQL 14+
• Payment Gateway: Stripe Billing API v2024, Stripe Elements
• Event Processing: Google Cloud Pub/Sub, Redis caching
• Analytics: BigQuery, Looker Studio dashboards
• Tax Compliance: Avalara AvaTax API integration

---

Table of Contents

1. G.1: Subscription Management
2. G.2: Pricing Engine & Tier Management
3. G.3: Invoicing & Payment Processing
4. G.4: Usage Metering & Overage Billing
5. G.5: Revenue Recognition & Reporting
6. Appendix A: Database Schema
7. Appendix B: API Reference
8. Appendix C: Event-Driven Architecture

---

G.1: Subscription Management

G.1.1: Subscription Lifecycle State Machine

Overview: The subscription lifecycle is modeled as a deterministic state machine with well-defined transitions, grace periods, and dunning workflows. This ensures predictable behavior for revenue recognition, access control, and customer communication.

State Machine Diagram:

State Definitions:

State	Description	Access Granted	Billing Active	Duration
TRIAL	Free trial period	Full (plan-limited)	No	14 days default
TRIAL_EXPIRED	Trial ended, no payment method	Read-only	No	7 days grace
ACTIVE	Subscription active and paid	Full	Yes	Recurring
PAST_DUE	Payment failed, dunning in progress	Limited (no new WOs)	Retry	7 days
SUSPENDED	Dunning failed, account suspended	Read-only	No	30 days
PAUSED	Customer-initiated pause	Read-only	No	Up to 90 days
CANCELLED	Subscription terminated	Data export only	No	30 days retention

Implementation:

// backend/src/billing/subscription/subscription-state-machine.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { EventEmitter2 } from '@nestjs/event-emitter';

export enum SubscriptionState {
  TRIAL = 'TRIAL',
  TRIAL_EXPIRED = 'TRIAL_EXPIRED',
  ACTIVE = 'ACTIVE',
  PAST_DUE = 'PAST_DUE',
  SUSPENDED = 'SUSPENDED',
  PAUSED = 'PAUSED',
  CANCELLED = 'CANCELLED',
}

export enum SubscriptionEvent {
  SIGNUP = 'SIGNUP',
  PAYMENT_METHOD_ADDED = 'PAYMENT_METHOD_ADDED',
  TRIAL_CONVERTED = 'TRIAL_CONVERTED',
  TRIAL_PERIOD_END = 'TRIAL_PERIOD_END',
  PAYMENT_FAILED = 'PAYMENT_FAILED',
  PAYMENT_SUCCEEDED = 'PAYMENT_SUCCEEDED',
  DUNNING_EXHAUSTED = 'DUNNING_EXHAUSTED',
  CUSTOMER_PAUSES = 'CUSTOMER_PAUSES',
  CUSTOMER_RESUMES = 'CUSTOMER_RESUMES',
  CUSTOMER_CANCELS = 'CUSTOMER_CANCELS',
  REACTIVATION = 'REACTIVATION',
  SUSPENSION_TIMEOUT = 'SUSPENSION_TIMEOUT',
}

interface StateTransition {
  from: SubscriptionState;
  to: SubscriptionState;
  event: SubscriptionEvent;
  guard?: (subscription: any) => boolean;
  action?: (subscription: any) => Promise<void>;
}

@Injectable()
export class SubscriptionStateMachine {
  private transitions: StateTransition[] = [
    // Trial flows
    {
      from: SubscriptionState.TRIAL,
      to: SubscriptionState.ACTIVE,
      event: SubscriptionEvent.TRIAL_CONVERTED,
      guard: (sub) => sub.hasPaymentMethod && sub.trialConversionComplete,
      action: async (sub) => {
        await this.activateSubscription(sub);
        await this.eventEmitter.emit('subscription.trial_converted', sub);
      },
    },
    {
      from: SubscriptionState.TRIAL,
      to: SubscriptionState.TRIAL_EXPIRED,
      event: SubscriptionEvent.TRIAL_PERIOD_END,
      guard: (sub) => !sub.hasPaymentMethod,
      action: async (sub) => {
        await this.sendTrialExpirationNotice(sub);
        await this.restrictAccess(sub, 'READ_ONLY');
      },
    },
    {
      from: SubscriptionState.TRIAL_EXPIRED,
      to: SubscriptionState.ACTIVE,
      event: SubscriptionEvent.PAYMENT_METHOD_ADDED,
      action: async (sub) => {
        await this.chargeFirstPayment(sub);
        await this.activateSubscription(sub);
      },
    },
    {
      from: SubscriptionState.TRIAL_EXPIRED,
      to: SubscriptionState.CANCELLED,
      event: SubscriptionEvent.CUSTOMER_CANCELS,
      action: async (sub) => {
        await this.scheduleDataDeletion(sub, 30); // 30 days retention
      },
    },

    // Active flows
    {
      from: SubscriptionState.ACTIVE,
      to: SubscriptionState.PAST_DUE,
      event: SubscriptionEvent.PAYMENT_FAILED,
      action: async (sub) => {
        await this.startDunningSequence(sub);
        await this.restrictAccess(sub, 'LIMITED');
      },
    },
    {
      from: SubscriptionState.ACTIVE,
      to: SubscriptionState.PAUSED,
      event: SubscriptionEvent.CUSTOMER_PAUSES,
      guard: (sub) => sub.plan.allowsPause,
      action: async (sub) => {
        await this.pauseBilling(sub);
        await this.restrictAccess(sub, 'READ_ONLY');
      },
    },
    {
      from: SubscriptionState.ACTIVE,
      to: SubscriptionState.CANCELLED,
      event: SubscriptionEvent.CUSTOMER_CANCELS,
      action: async (sub) => {
        await this.processDowngradeProration(sub);
        await this.scheduleDataDeletion(sub, 30);
      },
    },

    // Past due flows
    {
      from: SubscriptionState.PAST_DUE,
      to: SubscriptionState.ACTIVE,
      event: SubscriptionEvent.PAYMENT_SUCCEEDED,
      action: async (sub) => {
        await this.stopDunningSequence(sub);
        await this.restoreFullAccess(sub);
      },
    },
    {
      from: SubscriptionState.PAST_DUE,
      to: SubscriptionState.SUSPENDED,
      event: SubscriptionEvent.DUNNING_EXHAUSTED,
      guard: (sub) => sub.dunningAttempts >= 7,
      action: async (sub) => {
        await this.suspendAccount(sub);
        await this.notifyAccountSuspension(sub);
      },
    },

    // Suspended flows
    {
      from: SubscriptionState.SUSPENDED,
      to: SubscriptionState.ACTIVE,
      event: SubscriptionEvent.REACTIVATION,
      guard: (sub) => sub.hasValidPaymentMethod,
      action: async (sub) => {
        await this.chargeReactivationFee(sub);
        await this.restoreFullAccess(sub);
      },
    },
    {
      from: SubscriptionState.SUSPENDED,
      to: SubscriptionState.CANCELLED,
      event: SubscriptionEvent.SUSPENSION_TIMEOUT,
      guard: (sub) => sub.suspendedDays >= 30,
      action: async (sub) => {
        await this.cancelSubscription(sub);
        await this.scheduleDataDeletion(sub, 30);
      },
    },

    // Paused flows
    {
      from: SubscriptionState.PAUSED,
      to: SubscriptionState.ACTIVE,
      event: SubscriptionEvent.CUSTOMER_RESUMES,
      action: async (sub) => {
        await this.resumeBilling(sub);
        await this.restoreFullAccess(sub);
      },
    },
    {
      from: SubscriptionState.PAUSED,
      to: SubscriptionState.CANCELLED,
      event: SubscriptionEvent.CUSTOMER_CANCELS,
      action: async (sub) => {
        await this.scheduleDataDeletion(sub, 30);
      },
    },
  ];

  constructor(
    private readonly prisma: PrismaService,
    private readonly eventEmitter: EventEmitter2,
  ) {}

  async transition(
    subscriptionId: string,
    event: SubscriptionEvent,
  ): Promise<{ success: boolean; newState?: SubscriptionState; error?: string }> {
    const subscription = await this.prisma.subscription.findUnique({
      where: { id: subscriptionId },
      include: {
        plan: true,
        tenant: true,
        paymentMethod: true,
      },
    });

    if (!subscription) {
      return { success: false, error: 'Subscription not found' };
    }

    const currentState = subscription.state as SubscriptionState;
    const validTransition = this.transitions.find(
      (t) => t.from === currentState && t.event === event,
    );

    if (!validTransition) {
      return {
        success: false,
        error: `Invalid transition: ${currentState} -> ${event}`,
      };
    }

    // Check guard condition
    if (validTransition.guard && !validTransition.guard(subscription)) {
      return {
        success: false,
        error: 'Transition guard condition not met',
      };
    }

    // Execute transition
    try {
      await this.prisma.$transaction(async (tx) => {
        // Update state
        await tx.subscription.update({
          where: { id: subscriptionId },
          data: {
            state: validTransition.to,
            stateTransitionedAt: new Date(),
          },
        });

        // Create audit log
        await tx.subscriptionAuditLog.create({
          data: {
            subscriptionId,
            fromState: currentState,
            toState: validTransition.to,
            event,
            metadata: {},
          },
        });

        // Execute action
        if (validTransition.action) {
          await validTransition.action({ ...subscription, state: validTransition.to });
        }
      });

      return { success: true, newState: validTransition.to };
    } catch (error) {
      return { success: false, error: error.message };
    }
  }

  // Helper methods (implementation details)
  private async activateSubscription(subscription: any): Promise<void> {
    // Grant full access based on plan entitlements
    await this.prisma.tenantEntitlements.upsert({
      where: { tenantId: subscription.tenantId },
      create: {
        tenantId: subscription.tenantId,
        planId: subscription.planId,
        maxSeats: subscription.plan.baseSeats,
        maxWorkOrders: subscription.plan.baseWorkOrders,
        featuresEnabled: subscription.plan.features,
      },
      update: {
        planId: subscription.planId,
        maxSeats: subscription.plan.baseSeats,
        maxWorkOrders: subscription.plan.baseWorkOrders,
        featuresEnabled: subscription.plan.features,
      },
    });
  }

  private async startDunningSequence(subscription: any): Promise<void> {
    // Create dunning schedule (day 1, 3, 5, 7)
    const dunningSchedule = [1, 3, 5, 7].map((dayOffset) => ({
      subscriptionId: subscription.id,
      attemptNumber: dayOffset,
      scheduledAt: new Date(Date.now() + dayOffset * 24 * 60 * 60 * 1000),
      status: 'PENDING',
    }));

    await this.prisma.dunningAttempt.createMany({
      data: dunningSchedule,
    });
  }

  private async restrictAccess(subscription: any, level: 'READ_ONLY' | 'LIMITED'): Promise<void> {
    if (level === 'READ_ONLY') {
      await this.prisma.tenantEntitlements.update({
        where: { tenantId: subscription.tenantId },
        data: {
          canCreateWorkOrders: false,
          canEditWorkOrders: false,
          canDeleteWorkOrders: false,
        },
      });
    } else if (level === 'LIMITED') {
      await this.prisma.tenantEntitlements.update({
        where: { tenantId: subscription.tenantId },
        data: {
          canCreateWorkOrders: false, // No new work orders
          canEditWorkOrders: true,     // Can still edit existing
          canDeleteWorkOrders: false,
        },
      });
    }
  }

  private async sendTrialExpirationNotice(subscription: any): Promise<void> {
    await this.eventEmitter.emit('notification.send', {
      type: 'TRIAL_EXPIRATION',
      recipientEmail: subscription.tenant.billingEmail,
      templateData: {
        tenantName: subscription.tenant.name,
        planName: subscription.plan.name,
        graceEndDate: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
      },
    });
  }

  private async chargeFirstPayment(subscription: any): Promise<void> {
    // Handled by Stripe webhook: invoice.payment_succeeded
  }

  private async scheduleDataDeletion(subscription: any, retentionDays: number): Promise<void> {
    await this.prisma.dataRetentionJob.create({
      data: {
        tenantId: subscription.tenantId,
        scheduledAt: new Date(Date.now() + retentionDays * 24 * 60 * 60 * 1000),
        type: 'HARD_DELETE',
        status: 'PENDING',
      },
    });
  }

  private async pauseBilling(subscription: any): Promise<void> {
    // Pause Stripe subscription
    await this.stripeClient.subscriptions.update(subscription.stripeSubscriptionId, {
      pause_collection: { behavior: 'void' },
    });
  }

  private async resumeBilling(subscription: any): Promise<void> {
    await this.stripeClient.subscriptions.update(subscription.stripeSubscriptionId, {
      pause_collection: null,
    });
  }

  private async restoreFullAccess(subscription: any): Promise<void> {
    await this.activateSubscription(subscription);
  }

  private async stopDunningSequence(subscription: any): Promise<void> {
    await this.prisma.dunningAttempt.updateMany({
      where: {
        subscriptionId: subscription.id,
        status: 'PENDING',
      },
      data: { status: 'CANCELLED' },
    });
  }

  private async suspendAccount(subscription: any): Promise<void> {
    await this.restrictAccess(subscription, 'READ_ONLY');
  }

  private async notifyAccountSuspension(subscription: any): Promise<void> {
    await this.eventEmitter.emit('notification.send', {
      type: 'ACCOUNT_SUSPENDED',
      recipientEmail: subscription.tenant.billingEmail,
      templateData: {
        tenantName: subscription.tenant.name,
        reactivationDeadline: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
      },
    });
  }

  private async chargeReactivationFee(subscription: any): Promise<void> {
    const reactivationFee = subscription.plan.monthlyPrice * 0.1; // 10% reactivation fee
    await this.stripeClient.invoiceItems.create({
      customer: subscription.tenant.stripeCustomerId,
      amount: Math.round(reactivationFee * 100), // cents
      currency: 'usd',
      description: 'Reactivation fee',
    });
  }

  private async cancelSubscription(subscription: any): Promise<void> {
    await this.stripeClient.subscriptions.cancel(subscription.stripeSubscriptionId);
  }

  private async processDowngradeProration(subscription: any): Promise<void> {
    // Handled by Stripe automatic proration
  }
}

Grace Periods & Dunning Configuration:

// backend/src/billing/subscription/dunning.config.ts
export const DUNNING_CONFIG = {
  TRIAL_GRACE_PERIOD_DAYS: 7,
  PAST_DUE_GRACE_PERIOD_DAYS: 7,
  SUSPENDED_RETENTION_DAYS: 30,

  RETRY_SCHEDULE: [
    { attemptNumber: 1, delayDays: 1, emailTemplate: 'PAYMENT_FAILED_FIRST' },
    { attemptNumber: 2, delayDays: 3, emailTemplate: 'PAYMENT_FAILED_SECOND' },
    { attemptNumber: 3, delayDays: 5, emailTemplate: 'PAYMENT_FAILED_THIRD' },
    { attemptNumber: 4, delayDays: 7, emailTemplate: 'PAYMENT_FAILED_FINAL' },
  ],

  SMART_RETRY_RULES: {
    // Retry at different time of day
    retryHoursOffset: [6, 12, 18], // 6am, 12pm, 6pm customer local time

    // Avoid retry on weekends for B2B
    skipWeekends: true,

    // Increase retry interval for higher-value customers
    highValueCustomerMultiplier: 1.5, // 1.5x longer grace period for >$10K ARR
  },
};

---

G.1.2: Plan Management with Entitlements Engine

Overview: The BIO-QMS platform offers four subscription tiers with distinct feature sets, usage limits, and pricing. The entitlements engine enforces these limits at both API and UI levels with real-time quota tracking.

Pricing Tiers:

Tier	Annual Price	Base Seats	Base WO/Month	Key Features	Target Segment
Starter	$36K - $60K	5-10	50-100	Core QMS, basic compliance, email support	Small labs, startups
Professional	$96K - $180K	11-25	101-500	+ Advanced analytics, API access, phone support	Growing biotech firms
Enterprise	$240K - $600K	26-100	501-2500	+ SSO, SLA 99.9%, dedicated CSM	Mid-size pharma
Enterprise Plus	$400K+	Unlimited	Unlimited	+ Custom integrations, on-prem option, white-glove	Large pharma, CDMOs

Entitlements Data Model:

// Addition to backend/prisma/schema.prisma

// ─────────────────────────────────────────────
// Revenue & Billing Domain
// ─────────────────────────────────────────────

enum PlanTier {
  STARTER
  PROFESSIONAL
  ENTERPRISE
  ENTERPRISE_PLUS
}

enum BillingInterval {
  MONTHLY
  QUARTERLY
  ANNUAL
}

enum SubscriptionState {
  TRIAL
  TRIAL_EXPIRED
  ACTIVE
  PAST_DUE
  SUSPENDED
  PAUSED
  CANCELLED
}

model SubscriptionPlan {
  id                String      @id @default(cuid())
  name              String      // "Starter", "Professional", etc.
  tier              PlanTier
  description       String
  isActive          Boolean     @default(true)

  // Pricing
  baseMonthlyPrice  Decimal     @db.Decimal(10, 2)
  baseAnnualPrice   Decimal     @db.Decimal(10, 2)
  annualDiscount    Decimal     @db.Decimal(5, 2) // e.g., 0.15 = 15% discount

  // Seat pricing
  baseSeats         Int
  additionalSeatPriceMonthly  Decimal  @db.Decimal(10, 2)
  maxSeats          Int?        // NULL = unlimited

  // Work Order limits
  baseWorkOrders    Int         // Included WOs per month
  overageWOPrice    Decimal     @db.Decimal(10, 2) // Price per additional WO
  maxWorkOrders     Int?        // Hard cap, NULL = unlimited

  // Features (JSON object)
  features          Json        // { "sso": true, "api": true, "analytics": "advanced", ... }

  // SLA
  uptimeSLA         Decimal?    @db.Decimal(5, 4) // e.g., 0.9990 = 99.90%
  supportLevel      String      // "email", "phone", "dedicated_csm"

  subscriptions     Subscription[]

  createdAt         DateTime    @default(now())
  updatedAt         DateTime    @updatedAt

  @@index([tier, isActive])
  @@map("subscription_plans")
}

model Subscription {
  id                     String              @id @default(cuid())
  tenantId               String              @map("tenant_id")
  tenant                 Tenant              @relation(fields: [tenantId], references: [id])

  planId                 String
  plan                   SubscriptionPlan    @relation(fields: [planId], references: [id])

  state                  SubscriptionState   @default(TRIAL)
  stateTransitionedAt    DateTime            @default(now())

  // Billing
  billingInterval        BillingInterval     @default(MONTHLY)
  stripeSubscriptionId   String?             @unique
  stripeCustomerId       String

  // Trial
  trialStartDate         DateTime?
  trialEndDate           DateTime?

  // Purchased quantities
  purchasedSeats         Int                 @default(0)
  purchasedWOAllowance   Int                 @default(0)

  // Dates
  currentPeriodStart     DateTime
  currentPeriodEnd       DateTime
  cancelAt               DateTime?
  canceledAt             DateTime?

  // Relations
  invoices               Invoice[]
  usageRecords           UsageRecord[]
  auditLogs              SubscriptionAuditLog[]

  createdAt              DateTime            @default(now())
  updatedAt              DateTime            @updatedAt

  @@unique([tenantId])
  @@index([state, currentPeriodEnd])
  @@map("subscriptions")
}

model TenantEntitlements {
  id                     String   @id @default(cuid())
  tenantId               String   @unique @map("tenant_id")
  tenant                 Tenant   @relation(fields: [tenantId], references: [id])

  planId                 String
  plan                   SubscriptionPlan @relation(fields: [planId], references: [id])

  // Seat limits
  maxSeats               Int
  currentSeats           Int      @default(0)

  // Work Order limits
  maxWorkOrders          Int      // Per billing period
  currentWorkOrders      Int      @default(0)

  // Feature flags
  featuresEnabled        Json     // { "sso": true, "api": true, ... }

  // Capabilities (for past_due/suspended states)
  canCreateWorkOrders    Boolean  @default(true)
  canEditWorkOrders      Boolean  @default(true)
  canDeleteWorkOrders    Boolean  @default(true)
  canAccessAPI           Boolean  @default(true)
  canExportData          Boolean  @default(true)

  // Usage reset
  usageResetAt           DateTime // Start of current billing period

  updatedAt              DateTime @updatedAt

  @@index([tenantId, planId])
  @@map("tenant_entitlements")
}

model SubscriptionAuditLog {
  id               String              @id @default(cuid())
  subscriptionId   String
  subscription     Subscription        @relation(fields: [subscriptionId], references: [id])

  fromState        SubscriptionState?
  toState          SubscriptionState
  event            String
  metadata         Json

  createdAt        DateTime            @default(now())

  @@index([subscriptionId, createdAt])
  @@map("subscription_audit_logs")
}

model Invoice {
  id                  String    @id @default(cuid())
  tenantId            String    @map("tenant_id")
  subscriptionId      String?
  subscription        Subscription? @relation(fields: [subscriptionId], references: [id])

  stripeInvoiceId     String    @unique
  invoiceNumber       String    @unique

  // Amounts
  subtotal            Decimal   @db.Decimal(10, 2)
  taxAmount           Decimal   @db.Decimal(10, 2)
  total               Decimal   @db.Decimal(10, 2)
  amountDue           Decimal   @db.Decimal(10, 2)
  amountPaid          Decimal   @db.Decimal(10, 2) @default(0)

  currency            String    @default("usd")

  // Dates
  invoiceDate         DateTime
  dueDate             DateTime
  paidAt              DateTime?

  // Status
  status              String    // draft, open, paid, void, uncollectible

  // Line items
  lineItems           InvoiceLineItem[]

  // PDF
  pdfUrl              String?

  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  @@index([tenantId, invoiceDate])
  @@index([status, dueDate])
  @@map("invoices")
}

model InvoiceLineItem {
  id                  String    @id @default(cuid())
  invoiceId           String
  invoice             Invoice   @relation(fields: [invoiceId], references: [id])

  description         String
  quantity            Int       @default(1)
  unitPrice           Decimal   @db.Decimal(10, 2)
  amount              Decimal   @db.Decimal(10, 2)

  // Metadata
  itemType            String    // "subscription", "seat_overage", "wo_overage", "one_time"
  metadata            Json      // Plan details, usage period, etc.

  @@index([invoiceId])
  @@map("invoice_line_items")
}

model UsageRecord {
  id                  String    @id @default(cuid())
  tenantId            String    @map("tenant_id")
  subscriptionId      String
  subscription        Subscription @relation(fields: [subscriptionId], references: [id])

  // Usage type
  metricType          String    // "work_order", "api_call", "storage_gb", "ai_agent_invocation"
  quantity            Int

  // Time
  recordedAt          DateTime  @default(now())
  billingPeriodStart  DateTime
  billingPeriodEnd    DateTime

  // Billing
  isBillable          Boolean   @default(true)
  unitPrice           Decimal?  @db.Decimal(10, 2)
  totalCharge         Decimal?  @db.Decimal(10, 2)

  // Metadata
  metadata            Json      // Work order ID, API endpoint, etc.

  @@index([tenantId, metricType, recordedAt])
  @@index([subscriptionId, billingPeriodStart])
  @@map("usage_records")
}

model DunningAttempt {
  id                  String    @id @default(cuid())
  subscriptionId      String

  attemptNumber       Int
  scheduledAt         DateTime
  executedAt          DateTime?

  status              String    // PENDING, SUCCESS, FAILED, CANCELLED
  paymentIntentId     String?
  errorMessage        String?

  createdAt           DateTime  @default(now())

  @@index([subscriptionId, status])
  @@index([scheduledAt, status])
  @@map("dunning_attempts")
}

model DataRetentionJob {
  id                  String    @id @default(cuid())
  tenantId            String    @map("tenant_id")

  scheduledAt         DateTime
  executedAt          DateTime?

  type                String    // SOFT_DELETE, HARD_DELETE, EXPORT
  status              String    // PENDING, IN_PROGRESS, COMPLETED, FAILED

  metadata            Json      // Deleted record counts, export URLs, etc.

  @@index([scheduledAt, status])
  @@map("data_retention_jobs")
}

// Add relation to existing Tenant model
model Tenant {
  // ... existing fields ...

  subscription        Subscription?
  entitlements        TenantEntitlements?

  // Billing contact
  billingEmail        String?
  billingName         String?
  billingAddress      Json?     // { street, city, state, zip, country }

  // Stripe
  stripeCustomerId    String?   @unique

  // ... existing relations ...
}

Entitlements Engine Service:

// backend/src/billing/entitlements/entitlements.service.ts
import { Injectable, ForbiddenException } from '@nestjs/common';
import { PrismaService } from '../../prisma/prisma.service';
import { CacheService } from '../../cache/cache.service';

export interface EntitlementCheckResult {
  allowed: boolean;
  reason?: string;
  currentUsage?: number;
  limit?: number;
  upgradeRequired?: boolean;
  suggestedPlan?: string;
}

@Injectable()
export class EntitlementsService {
  constructor(
    private readonly prisma: PrismaService,
    private readonly cache: CacheService,
  ) {}

  /**
   * Check if tenant can create a new work order
   */
  async canCreateWorkOrder(tenantId: string): Promise<EntitlementCheckResult> {
    const entitlements = await this.getEntitlements(tenantId);

    // Check subscription state
    const subscription = await this.prisma.subscription.findUnique({
      where: { tenantId },
      include: { plan: true },
    });

    if (!subscription || subscription.state === 'CANCELLED') {
      return {
        allowed: false,
        reason: 'No active subscription',
        upgradeRequired: true,
        suggestedPlan: 'STARTER',
      };
    }

    if (subscription.state === 'SUSPENDED') {
      return {
        allowed: false,
        reason: 'Account suspended - please update payment method',
      };
    }

    if (!entitlements.canCreateWorkOrders) {
      return {
        allowed: false,
        reason: 'Work order creation disabled (payment past due)',
      };
    }

    // Check usage limit
    if (entitlements.maxWorkOrders !== null) { // NULL = unlimited
      const currentPeriodUsage = await this.getCurrentWorkOrderCount(
        tenantId,
        subscription.currentPeriodStart,
        subscription.currentPeriodEnd,
      );

      if (currentPeriodUsage >= entitlements.maxWorkOrders) {
        // Check if overage is allowed
        const allowsOverage = subscription.plan.overageWOPrice > 0;

        if (!allowsOverage) {
          return {
            allowed: false,
            reason: `Work order limit reached (${currentPeriodUsage}/${entitlements.maxWorkOrders})`,
            currentUsage: currentPeriodUsage,
            limit: entitlements.maxWorkOrders,
            upgradeRequired: true,
            suggestedPlan: this.getSuggestedUpgrade(subscription.plan.tier),
          };
        }

        // Overage allowed - will be billed
        return {
          allowed: true,
          reason: `Overage will be charged at $${subscription.plan.overageWOPrice}/WO`,
          currentUsage: currentPeriodUsage,
          limit: entitlements.maxWorkOrders,
        };
      }

      return {
        allowed: true,
        currentUsage: currentPeriodUsage,
        limit: entitlements.maxWorkOrders,
      };
    }

    return { allowed: true };
  }

  /**
   * Check if tenant can add a new seat (user)
   */
  async canAddSeat(tenantId: string): Promise<EntitlementCheckResult> {
    const entitlements = await this.getEntitlements(tenantId);
    const subscription = await this.prisma.subscription.findUnique({
      where: { tenantId },
      include: { plan: true },
    });

    if (!subscription || subscription.state !== 'ACTIVE') {
      return { allowed: false, reason: 'No active subscription' };
    }

    const currentSeats = await this.prisma.person.count({
      where: { tenantId, active: true },
    });

    const totalAllowedSeats = subscription.plan.baseSeats + subscription.purchasedSeats;

    if (entitlements.maxSeats !== null && currentSeats >= totalAllowedSeats) {
      // Check if additional seats can be purchased
      if (subscription.plan.maxSeats !== null && currentSeats >= subscription.plan.maxSeats) {
        return {
          allowed: false,
          reason: `Maximum seats reached (${subscription.plan.maxSeats})`,
          currentUsage: currentSeats,
          limit: subscription.plan.maxSeats,
          upgradeRequired: true,
          suggestedPlan: this.getSuggestedUpgrade(subscription.plan.tier),
        };
      }

      // Auto-purchase additional seat
      return {
        allowed: true,
        reason: `Additional seat will be charged at $${subscription.plan.additionalSeatPriceMonthly}/month (prorated)`,
        currentUsage: currentSeats,
        limit: totalAllowedSeats,
      };
    }

    return {
      allowed: true,
      currentUsage: currentSeats,
      limit: totalAllowedSeats,
    };
  }

  /**
   * Check if tenant has access to a specific feature
   */
  async hasFeature(tenantId: string, featureKey: string): Promise<boolean> {
    const entitlements = await this.getEntitlements(tenantId);
    const features = entitlements.featuresEnabled as Record<string, any>;

    return features[featureKey] === true || features[featureKey] === 'enabled';
  }

  /**
   * Get feature level (e.g., "basic", "advanced", "premium")
   */
  async getFeatureLevel(tenantId: string, featureKey: string): Promise<string | null> {
    const entitlements = await this.getEntitlements(tenantId);
    const features = entitlements.featuresEnabled as Record<string, any>;

    const value = features[featureKey];
    return typeof value === 'string' ? value : null;
  }

  /**
   * Record usage event for billing
   */
  async recordUsage(
    tenantId: string,
    metricType: 'work_order' | 'api_call' | 'storage_gb' | 'ai_agent_invocation',
    quantity: number = 1,
    metadata: Record<string, any> = {},
  ): Promise<void> {
    const subscription = await this.prisma.subscription.findUnique({
      where: { tenantId },
    });

    if (!subscription) return; // No subscription = no billing

    await this.prisma.usageRecord.create({
      data: {
        tenantId,
        subscriptionId: subscription.id,
        metricType,
        quantity,
        recordedAt: new Date(),
        billingPeriodStart: subscription.currentPeriodStart,
        billingPeriodEnd: subscription.currentPeriodEnd,
        isBillable: true,
        metadata,
      },
    });

    // Invalidate cache
    await this.cache.del(`entitlements:${tenantId}`);
  }

  /**
   * Get current entitlements (cached)
   */
  private async getEntitlements(tenantId: string): Promise<any> {
    const cacheKey = `entitlements:${tenantId}`;
    const cached = await this.cache.get(cacheKey);

    if (cached) return JSON.parse(cached);

    const entitlements = await this.prisma.tenantEntitlements.findUnique({
      where: { tenantId },
      include: { plan: true },
    });

    if (!entitlements) {
      throw new ForbiddenException('No entitlements found for tenant');
    }

    await this.cache.set(cacheKey, JSON.stringify(entitlements), 300); // 5 min TTL
    return entitlements;
  }

  private async getCurrentWorkOrderCount(
    tenantId: string,
    periodStart: Date,
    periodEnd: Date,
  ): Promise<number> {
    return this.prisma.workOrder.count({
      where: {
        tenantId,
        createdAt: {
          gte: periodStart,
          lte: periodEnd,
        },
      },
    });
  }

  private getSuggestedUpgrade(currentTier: string): string {
    const upgrades = {
      STARTER: 'PROFESSIONAL',
      PROFESSIONAL: 'ENTERPRISE',
      ENTERPRISE: 'ENTERPRISE_PLUS',
      ENTERPRISE_PLUS: null, // Already top tier
    };
    return upgrades[currentTier];
  }
}

Feature Flag Configuration:

// backend/src/billing/entitlements/feature-definitions.ts
export const FEATURE_DEFINITIONS = {
  // Authentication & Access
  sso: {
    name: 'Single Sign-On (SSO)',
    tiers: ['ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },
  mfa: {
    name: 'Multi-Factor Authentication',
    tiers: ['PROFESSIONAL', 'ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },

  // Analytics
  analytics: {
    name: 'Analytics Dashboard',
    tiers: {
      STARTER: 'basic',
      PROFESSIONAL: 'advanced',
      ENTERPRISE: 'advanced',
      ENTERPRISE_PLUS: 'premium',
    },
    type: 'level',
  },
  customReports: {
    name: 'Custom Report Builder',
    tiers: ['ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },

  // API Access
  apiAccess: {
    name: 'REST API Access',
    tiers: ['PROFESSIONAL', 'ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },
  apiRateLimit: {
    name: 'API Rate Limit (requests/hour)',
    tiers: {
      STARTER: 0,
      PROFESSIONAL: 1000,
      ENTERPRISE: 10000,
      ENTERPRISE_PLUS: null, // unlimited
    },
    type: 'number',
  },

  // Compliance & Audit
  auditLog: {
    name: 'Audit Log Retention',
    tiers: {
      STARTER: '90_days',
      PROFESSIONAL: '1_year',
      ENTERPRISE: '7_years',
      ENTERPRISE_PLUS: 'unlimited',
    },
    type: 'level',
  },
  electronicSignatures: {
    name: '21 CFR Part 11 E-Signatures',
    tiers: ['STARTER', 'PROFESSIONAL', 'ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },

  // AI Features
  aiAgents: {
    name: 'AI QMS Agents',
    tiers: {
      STARTER: 'basic',          // Pre-built templates only
      PROFESSIONAL: 'standard',  // + Custom prompts
      ENTERPRISE: 'advanced',    // + Training on your data
      ENTERPRISE_PLUS: 'custom', // + Dedicated models
    },
    type: 'level',
  },
  aiInvocationsPerMonth: {
    name: 'AI Agent Invocations',
    tiers: {
      STARTER: 100,
      PROFESSIONAL: 1000,
      ENTERPRISE: 10000,
      ENTERPRISE_PLUS: null, // unlimited
    },
    type: 'number',
  },

  // Support
  supportLevel: {
    name: 'Customer Support',
    tiers: {
      STARTER: 'email',
      PROFESSIONAL: 'phone',
      ENTERPRISE: 'dedicated_csm',
      ENTERPRISE_PLUS: 'white_glove',
    },
    type: 'level',
  },
  sla: {
    name: 'Uptime SLA',
    tiers: {
      STARTER: null,
      PROFESSIONAL: 0.99,
      ENTERPRISE: 0.999,
      ENTERPRISE_PLUS: 0.9999,
    },
    type: 'number',
  },

  // Customization
  whiteLabeling: {
    name: 'White-Label Branding',
    tiers: ['ENTERPRISE_PLUS'],
    type: 'boolean',
  },
  customIntegrations: {
    name: 'Custom System Integrations',
    tiers: ['ENTERPRISE', 'ENTERPRISE_PLUS'],
    type: 'boolean',
  },
  onPremOption: {
    name: 'On-Premises Deployment',
    tiers: ['ENTERPRISE_PLUS'],
    type: 'boolean',
  },
};

/**
 * Generate feature flags JSON for a given tier
 */
export function generateFeatureFlags(tier: string): Record<string, any> {
  const flags: Record<string, any> = {};

  for (const [key, definition] of Object.entries(FEATURE_DEFINITIONS)) {
    if (Array.isArray(definition.tiers)) {
      // Boolean feature
      flags[key] = definition.tiers.includes(tier);
    } else if (typeof definition.tiers === 'object') {
      // Level or number feature
      flags[key] = definition.tiers[tier] ?? false;
    }
  }

  return flags;
}

---

G.1.3: Subscription Upgrade/Downgrade with Proration

Overview: Customers can upgrade or downgrade their subscription mid-cycle. The system calculates prorated credits/charges and applies them to the next invoice. Upgrades take effect immediately; downgrades take effect at the end of the current period.

Proration Logic:

// backend/src/billing/subscription/proration.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../../prisma/prisma.service';
import Stripe from 'stripe';

interface ProrationResult {
  immediateCharge?: number;      // For upgrades
  creditAmount?: number;          // For downgrades
  effectiveDate: Date;
  newPeriodEnd: Date;
  explanation: string;
}

@Injectable()
export class ProrationService {
  private stripe: Stripe;

  constructor(private readonly prisma: PrismaService) {
    this.stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
      apiVersion: '2024-04-10',
    });
  }

  /**
   * Calculate proration for plan change
   */
  async calculateProration(
    subscriptionId: string,
    newPlanId: string,
  ): Promise<ProrationResult> {
    const subscription = await this.prisma.subscription.findUnique({
      where: { id: subscriptionId },
      include: {
        plan: true,
        tenant: true,
      },
    });

    const newPlan = await this.prisma.subscriptionPlan.findUnique({
      where: { id: newPlanId },
    });

    if (!subscription || !newPlan) {
      throw new Error('Subscription or plan not found');
    }

    const now = new Date();
    const periodStart = subscription.currentPeriodStart;
    const periodEnd = subscription.currentPeriodEnd;

    const totalPeriodDays = Math.ceil(
      (periodEnd.getTime() - periodStart.getTime()) / (1000 * 60 * 60 * 24),
    );
    const remainingDays = Math.ceil(
      (periodEnd.getTime() - now.getTime()) / (1000 * 60 * 60 * 24),
    );

    const fractionRemaining = remainingDays / totalPeriodDays;

    // Determine price based on billing interval
    const oldPrice = this.getPriceForInterval(subscription.plan, subscription.billingInterval);
    const newPrice = this.getPriceForInterval(newPlan, subscription.billingInterval);

    const isUpgrade = newPrice > oldPrice;

    if (isUpgrade) {
      // Upgrade: charge prorated difference immediately
      const unusedCredit = oldPrice * fractionRemaining;
      const newCharge = newPrice * fractionRemaining;
      const immediateCharge = newCharge - unusedCredit;

      return {
        immediateCharge: Math.max(0, immediateCharge),
        effectiveDate: now,
        newPeriodEnd: periodEnd, // Keep same billing cycle
        explanation: `Prorated charge for ${remainingDays} days at new plan rate. Unused credit from old plan: $${unusedCredit.toFixed(2)}. New charge: $${newCharge.toFixed(2)}.`,
      };
    } else {
      // Downgrade: credit remaining time at old rate, schedule change for period end
      const unusedValue = oldPrice * fractionRemaining;

      return {
        creditAmount: unusedValue,
        effectiveDate: periodEnd, // Effective at end of period
        newPeriodEnd: periodEnd,
        explanation: `Downgrade scheduled for ${periodEnd.toISOString().split('T')[0]}. Credit of $${unusedValue.toFixed(2)} will be applied to next invoice.`,
      };
    }
  }

  /**
   * Execute plan change with proration
   */
  async changePlan(
    subscriptionId: string,
    newPlanId: string,
    immediate: boolean = false, // Force immediate downgrade (for admin use)
  ): Promise<void> {
    const proration = await this.calculateProration(subscriptionId, newPlanId);
    const subscription = await this.prisma.subscription.findUnique({
      where: { id: subscriptionId },
      include: { plan: true, tenant: true },
    });

    const newPlan = await this.prisma.subscriptionPlan.findUnique({
      where: { id: newPlanId },
    });

    const isUpgrade = proration.immediateCharge !== undefined;

    if (isUpgrade || immediate) {
      // Immediate change
      await this.prisma.$transaction(async (tx) => {
        // Update subscription
        await tx.subscription.update({
          where: { id: subscriptionId },
          data: {
            planId: newPlanId,
            updatedAt: new Date(),
          },
        });

        // Update entitlements
        await tx.tenantEntitlements.update({
          where: { tenantId: subscription.tenantId },
          data: {
            planId: newPlanId,
            maxSeats: newPlan.baseSeats,
            maxWorkOrders: newPlan.baseWorkOrders,
            featuresEnabled: newPlan.features,
          },
        });

        // Create invoice item for proration charge (if upgrade)
        if (proration.immediateCharge && proration.immediateCharge > 0) {
          await this.stripe.invoiceItems.create({
            customer: subscription.tenant.stripeCustomerId,
            amount: Math.round(proration.immediateCharge * 100), // cents
            currency: 'usd',
            description: `Prorated upgrade charge: ${subscription.plan.name} → ${newPlan.name}`,
          });

          // Create immediate invoice
          const invoice = await this.stripe.invoices.create({
            customer: subscription.tenant.stripeCustomerId,
            auto_advance: true, // Automatically finalize and attempt payment
          });

          await this.stripe.invoices.finalizeInvoice(invoice.id);
        }
      });
    } else {
      // Schedule downgrade for period end
      await this.prisma.subscription.update({
        where: { id: subscriptionId },
        data: {
          // Store pending change (custom field)
          metadata: {
            pendingDowngrade: {
              newPlanId,
              scheduledFor: proration.effectiveDate,
              creditAmount: proration.creditAmount,
            },
          },
        },
      });

      // Create scheduled job to execute downgrade
      await this.prisma.scheduledTask.create({
        data: {
          type: 'SUBSCRIPTION_DOWNGRADE',
          scheduledAt: proration.effectiveDate,
          payload: {
            subscriptionId,
            newPlanId,
            creditAmount: proration.creditAmount,
          },
          status: 'PENDING',
        },
      });
    }
  }

  /**
   * Add additional seats (prorated)
   */
  async addSeats(
    subscriptionId: string,
    additionalSeats: number,
  ): Promise<ProrationResult> {
    const subscription = await this.prisma.subscription.findUnique({
      where: { id: subscriptionId },
      include: { plan: true, tenant: true },
    });

    const now = new Date();
    const periodStart = subscription.currentPeriodStart;
    const periodEnd = subscription.currentPeriodEnd;

    const totalPeriodDays = Math.ceil(
      (periodEnd.getTime() - periodStart.getTime()) / (1000 * 60 * 60 * 24),
    );
    const remainingDays = Math.ceil(
      (periodEnd.getTime() - now.getTime()) / (1000 * 60 * 60 * 24),
    );

    const fractionRemaining = remainingDays / totalPeriodDays;

    const seatPrice = subscription.plan.additionalSeatPriceMonthly;
    const proratedCharge = seatPrice * additionalSeats * fractionRemaining;

    // Update subscription
    await this.prisma.subscription.update({
      where: { id: subscriptionId },
      data: {
        purchasedSeats: subscription.purchasedSeats + additionalSeats,
      },
    });

    // Create invoice item
    await this.stripe.invoiceItems.create({
      customer: subscription.tenant.stripeCustomerId,
      amount: Math.round(proratedCharge * 100),
      currency: 'usd',
      description: `${additionalSeats} additional seat(s) (prorated for ${remainingDays} days)`,
    });

    return {
      immediateCharge: proratedCharge,
      effectiveDate: now,
      newPeriodEnd: periodEnd,
      explanation: `Added ${additionalSeats} seat(s). Prorated charge: $${proratedCharge.toFixed(2)} for ${remainingDays} remaining days.`,
    };
  }

  private getPriceForInterval(plan: any, interval: string): number {
    switch (interval) {
      case 'MONTHLY':
        return Number(plan.baseMonthlyPrice);
      case 'QUARTERLY':
        return Number(plan.baseMonthlyPrice) * 3;
      case 'ANNUAL':
        return Number(plan.baseAnnualPrice);
      default:
        return Number(plan.baseMonthlyPrice);
    }
  }
}

Downgrade Execution (Scheduled Task):

// backend/src/billing/tasks/execute-downgrades.task.ts
import { Injectable, Logger } from '@nestjs/common';
import { Cron, CronExpression } from '@nestjs/schedule';
import { PrismaService } from '../../prisma/prisma.service';
import Stripe from 'stripe';

@Injectable()
export class ExecuteDowngradesTask {
  private readonly logger = new Logger(ExecuteDowngradesTask.name);
  private stripe: Stripe;

  constructor(private readonly prisma: PrismaService) {
    this.stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
      apiVersion: '2024-04-10',
    });
  }

  @Cron(CronExpression.EVERY_HOUR)
  async handleScheduledDowngrades(): Promise<void> {
    this.logger.log('Checking for scheduled downgrades...');

    const now = new Date();
    const pendingDowngrades = await this.prisma.scheduledTask.findMany({
      where: {
        type: 'SUBSCRIPTION_DOWNGRADE',
        status: 'PENDING',
        scheduledAt: {
          lte: now,
        },
      },
    });

    this.logger.log(`Found ${pendingDowngrades.length} downgrades to execute`);

    for (const task of pendingDowngrades) {
      try {
        await this.executeDowngrade(task);

        await this.prisma.scheduledTask.update({
          where: { id: task.id },
          data: { status: 'COMPLETED', executedAt: new Date() },
        });

        this.logger.log(`Downgrade executed: ${task.id}`);
      } catch (error) {
        this.logger.error(`Failed to execute downgrade ${task.id}:`, error);

        await this.prisma.scheduledTask.update({
          where: { id: task.id },
          data: {
            status: 'FAILED',
            errorMessage: error.message,
          },
        });
      }
    }
  }

  private async executeDowngrade(task: any): Promise<void> {
    const { subscriptionId, newPlanId, creditAmount } = task.payload;

    const subscription = await this.prisma.subscription.findUnique({
      where: { id: subscriptionId },
      include: { tenant: true },
    });

    const newPlan = await this.prisma.subscriptionPlan.findUnique({
      where: { id: newPlanId },
    });

    await this.prisma.$transaction(async (tx) => {
      // Update subscription
      await tx.subscription.update({
        where: { id: subscriptionId },
        data: {
          planId: newPlanId,
          metadata: {}, // Clear pending downgrade
        },
      });

      // Update entitlements
      await tx.tenantEntitlements.update({
        where: { tenantId: subscription.tenantId },
        data: {
          planId: newPlanId,
          maxSeats: newPlan.baseSeats,
          maxWorkOrders: newPlan.baseWorkOrders,
          featuresEnabled: newPlan.features,
        },
      });

      // Apply credit to account
      if (creditAmount > 0) {
        await this.stripe.customers.createBalanceTransaction(
          subscription.tenant.stripeCustomerId,
          {
            amount: -Math.round(creditAmount * 100), // Negative = credit
            currency: 'usd',
            description: 'Credit for downgrade proration',
          },
        );
      }
    });
  }
}

---

G.1.4: Self-Service Subscription Management UI

Overview: Customers can manage their subscription through a self-service portal: view current plan, compare tiers, upgrade/downgrade, add seats, view billing history, and manage payment methods.

UI Components (React + TypeScript):

// frontend/src/pages/billing/SubscriptionManagement.tsx
import React, { useState, useEffect } from 'react';
import { useNavigate } from 'react-router-dom';
import axios from 'axios';
import {
  Card,
  CardContent,
  CardHeader,
  Button,
  Alert,
  Badge,
  Progress,
  Tabs,
  TabsContent,
  TabsList,
  TabsTrigger,
} from '@/components/ui';
import { PlanComparisonTable } from './components/PlanComparisonTable';
import { BillingHistory } from './components/BillingHistory';
import { PaymentMethodManager } from './components/PaymentMethodManager';
import { UsageMetrics } from './components/UsageMetrics';

interface Subscription {
  id: string;
  state: string;
  plan: {
    name: string;
    tier: string;
    baseSeats: number;
    baseWorkOrders: number;
  };
  purchasedSeats: number;
  currentPeriodStart: string;
  currentPeriodEnd: string;
  billingInterval: string;
}

interface Entitlements {
  maxSeats: number;
  currentSeats: number;
  maxWorkOrders: number;
  currentWorkOrders: number;
  featuresEnabled: Record<string, any>;
}

export const SubscriptionManagement: React.FC = () => {
  const [subscription, setSubscription] = useState<Subscription | null>(null);
  const [entitlements, setEntitlements] = useState<Entitlements | null>(null);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState<string | null>(null);
  const navigate = useNavigate();

  useEffect(() => {
    loadSubscriptionData();
  }, []);

  const loadSubscriptionData = async () => {
    try {
      const [subRes, entRes] = await Promise.all([
        axios.get('/api/v1/billing/subscription'),
        axios.get('/api/v1/billing/entitlements'),
      ]);
      setSubscription(subRes.data);
      setEntitlements(entRes.data);
    } catch (err) {
      setError(err.response?.data?.message || 'Failed to load subscription');
    } finally {
      setLoading(false);
    }
  };

  const handleUpgrade = async (newPlanId: string) => {
    try {
      const { data: proration } = await axios.post('/api/v1/billing/subscription/calculate-proration', {
        newPlanId,
      });

      const confirmed = window.confirm(
        `Upgrade will take effect immediately.\n\n${proration.explanation}\n\nImmediate charge: $${proration.immediateCharge?.toFixed(2) || 0}\n\nContinue?`,
      );

      if (confirmed) {
        await axios.post('/api/v1/billing/subscription/change-plan', {
          newPlanId,
          immediate: true,
        });

        alert('Plan upgraded successfully!');
        loadSubscriptionData();
      }
    } catch (err) {
      alert(err.response?.data?.message || 'Upgrade failed');
    }
  };

  const handleDowngrade = async (newPlanId: string) => {
    try {
      const { data: proration } = await axios.post('/api/v1/billing/subscription/calculate-proration', {
        newPlanId,
      });

      const confirmed = window.confirm(
        `Downgrade will take effect at the end of your current billing period.\n\n${proration.explanation}\n\nCredit: $${proration.creditAmount?.toFixed(2) || 0}\n\nContinue?`,
      );

      if (confirmed) {
        await axios.post('/api/v1/billing/subscription/change-plan', {
          newPlanId,
          immediate: false,
        });

        alert('Downgrade scheduled successfully!');
        loadSubscriptionData();
      }
    } catch (err) {
      alert(err.response?.data?.message || 'Downgrade failed');
    }
  };

  const handleAddSeats = async () => {
    const seatsToAdd = prompt('How many additional seats do you need?');
    if (!seatsToAdd || isNaN(Number(seatsToAdd))) return;

    try {
      const { data: proration } = await axios.post('/api/v1/billing/subscription/add-seats', {
        additionalSeats: Number(seatsToAdd),
      });

      const confirmed = window.confirm(
        `${proration.explanation}\n\nImmediate charge: $${proration.immediateCharge?.toFixed(2)}\n\nContinue?`,
      );

      if (confirmed) {
        alert('Seats added successfully!');
        loadSubscriptionData();
      }
    } catch (err) {
      alert(err.response?.data?.message || 'Failed to add seats');
    }
  };

  const handleCancelSubscription = async () => {
    const confirmed = window.confirm(
      'Are you sure you want to cancel your subscription?\n\nYour access will be downgraded to read-only at the end of the current billing period.\n\nData will be retained for 30 days.',
    );

    if (!confirmed) return;

    const reason = prompt('Please tell us why you're canceling (optional):');

    try {
      await axios.post('/api/v1/billing/subscription/cancel', {
        reason,
      });

      alert('Subscription cancellation scheduled.');
      loadSubscriptionData();
    } catch (err) {
      alert(err.response?.data?.message || 'Cancellation failed');
    }
  };

  if (loading) return <div>Loading...</div>;
  if (error) return <Alert variant="destructive">{error}</Alert>;
  if (!subscription || !entitlements) return <div>No subscription found</div>;

  const seatUsagePercent = (entitlements.currentSeats / entitlements.maxSeats) * 100;
  const woUsagePercent = (entitlements.currentWorkOrders / entitlements.maxWorkOrders) * 100;

  return (
    <div className="container mx-auto py-8 space-y-6">
      {/* Current Plan Overview */}
      <Card>
        <CardHeader>
          <div className="flex justify-between items-center">
            <div>
              <h2 className="text-2xl font-bold">{subscription.plan.name} Plan</h2>
              <Badge variant={subscription.state === 'ACTIVE' ? 'success' : 'warning'}>
                {subscription.state}
              </Badge>
            </div>
            <div className="text-right">
              <p className="text-sm text-gray-500">Billing Interval</p>
              <p className="text-lg font-semibold">{subscription.billingInterval}</p>
            </div>
          </div>
        </CardHeader>
        <CardContent>
          <div className="grid grid-cols-1 md:grid-cols-2 gap-6">
            {/* Seat Usage */}
            <div>
              <div className="flex justify-between mb-2">
                <span className="text-sm font-medium">Seats</span>
                <span className="text-sm text-gray-600">
                  {entitlements.currentSeats} / {entitlements.maxSeats}
                </span>
              </div>
              <Progress value={seatUsagePercent} className="mb-2" />
              {seatUsagePercent > 80 && (
                <Alert variant="warning" className="mt-2">
                  You're using {entitlements.currentSeats} of {entitlements.maxSeats} seats.
                  <Button onClick={handleAddSeats} variant="link" size="sm">
                    Add more seats
                  </Button>
                </Alert>
              )}
            </div>

            {/* Work Order Usage */}
            <div>
              <div className="flex justify-between mb-2">
                <span className="text-sm font-medium">Work Orders (this period)</span>
                <span className="text-sm text-gray-600">
                  {entitlements.currentWorkOrders} / {entitlements.maxWorkOrders}
                </span>
              </div>
              <Progress value={woUsagePercent} className="mb-2" />
              {woUsagePercent > 80 && (
                <Alert variant="warning" className="mt-2">
                  You're approaching your work order limit. Overages will be billed at{' '}
                  ${subscription.plan.overageWOPrice}/WO.
                </Alert>
              )}
            </div>
          </div>

          {/* Billing Period */}
          <div className="mt-6 p-4 bg-gray-50 rounded">
            <p className="text-sm text-gray-600">
              Current billing period:{' '}
              <span className="font-medium">
                {new Date(subscription.currentPeriodStart).toLocaleDateString()} –{' '}
                {new Date(subscription.currentPeriodEnd).toLocaleDateString()}
              </span>
            </p>
          </div>
        </CardContent>
      </Card>

      {/* Tabs: Plan Comparison, Usage, Billing, Payment */}
      <Tabs defaultValue="usage">
        <TabsList>
          <TabsTrigger value="usage">Usage & Analytics</TabsTrigger>
          <TabsTrigger value="plans">Compare Plans</TabsTrigger>
          <TabsTrigger value="billing">Billing History</TabsTrigger>
          <TabsTrigger value="payment">Payment Methods</TabsTrigger>
        </TabsList>

        <TabsContent value="usage">
          <UsageMetrics subscriptionId={subscription.id} />
        </TabsContent>

        <TabsContent value="plans">
          <PlanComparisonTable
            currentPlanTier={subscription.plan.tier}
            onUpgrade={handleUpgrade}
            onDowngrade={handleDowngrade}
          />
        </TabsContent>

        <TabsContent value="billing">
          <BillingHistory tenantId={subscription.tenantId} />
        </TabsContent>

        <TabsContent value="payment">
          <PaymentMethodManager tenantId={subscription.tenantId} />
        </TabsContent>
      </Tabs>

      {/* Danger Zone */}
      <Card className="border-red-200">
        <CardHeader>
          <h3 className="text-lg font-semibold text-red-600">Danger Zone</h3>
        </CardHeader>
        <CardContent>
          <div className="flex justify-between items-center">
            <div>
              <p className="font-medium">Cancel Subscription</p>
              <p className="text-sm text-gray-600">
                Once you cancel, you'll retain read-only access until the end of your billing period.
              </p>
            </div>
            <Button onClick={handleCancelSubscription} variant="destructive">
              Cancel Subscription
            </Button>
          </div>
        </CardContent>
      </Card>
    </div>
  );
};

Plan Comparison Table Component:

// frontend/src/pages/billing/components/PlanComparisonTable.tsx
import React, { useState, useEffect } from 'react';
import axios from 'axios';
import {
  Table,
  TableBody,
  TableCell,
  TableHead,
  TableHeader,
  TableRow,
  Button,
  Badge,
} from '@/components/ui';
import { Check, X } from 'lucide-react';

interface Plan {
  id: string;
  name: string;
  tier: string;
  baseMonthlyPrice: number;
  baseAnnualPrice: number;
  baseSeats: number;
  baseWorkOrders: number;
  features: Record<string, any>;
}

interface PlanComparisonTableProps {
  currentPlanTier: string;
  onUpgrade: (planId: string) => void;
  onDowngrade: (planId: string) => void;
}

export const PlanComparisonTable: React.FC<PlanComparisonTableProps> = ({
  currentPlanTier,
  onUpgrade,
  onDowngrade,
}) => {
  const [plans, setPlans] = useState<Plan[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    loadPlans();
  }, []);

  const loadPlans = async () => {
    try {
      const { data } = await axios.get('/api/v1/billing/plans');
      setPlans(data);
    } catch (err) {
      console.error('Failed to load plans:', err);
    } finally {
      setLoading(false);
    }
  };

  const featureRows = [
    { key: 'baseSeats', label: 'Included Seats' },
    { key: 'baseWorkOrders', label: 'Work Orders/Month' },
    { key: 'sso', label: 'Single Sign-On (SSO)' },
    { key: 'analytics', label: 'Analytics Dashboard' },
    { key: 'apiAccess', label: 'API Access' },
    { key: 'customReports', label: 'Custom Reports' },
    { key: 'aiAgents', label: 'AI QMS Agents' },
    { key: 'supportLevel', label: 'Support Level' },
    { key: 'sla', label: 'Uptime SLA' },
  ];

  const renderFeatureValue = (plan: Plan, featureKey: string) => {
    if (featureKey === 'baseSeats' || featureKey === 'baseWorkOrders') {
      return plan[featureKey];
    }

    const value = plan.features[featureKey];

    if (typeof value === 'boolean') {
      return value ? <Check className="text-green-600" /> : <X className="text-gray-300" />;
    }

    if (value === null || value === undefined || value === false) {
      return <X className="text-gray-300" />;
    }

    return String(value);
  };

  const getTierOrder = (tier: string): number => {
    const order = { STARTER: 1, PROFESSIONAL: 2, ENTERPRISE: 3, ENTERPRISE_PLUS: 4 };
    return order[tier] || 0;
  };

  if (loading) return <div>Loading plans...</div>;

  return (
    <div className="overflow-x-auto">
      <Table>
        <TableHeader>
          <TableRow>
            <TableHead>Feature</TableHead>
            {plans.map((plan) => (
              <TableHead key={plan.id} className="text-center">
                <div className="space-y-2">
                  <div className="font-bold text-lg">{plan.name}</div>
                  <div className="text-sm text-gray-600">
                    ${(plan.baseMonthlyPrice / 1000).toFixed(0)}K/mo
                  </div>
                  <div className="text-xs text-gray-500">
                    ${(plan.baseAnnualPrice / 1000).toFixed(0)}K/yr (save 15%)
                  </div>
                  {plan.tier === currentPlanTier && (
                    <Badge variant="success">Current Plan</Badge>
                  )}
                </div>
              </TableHead>
            ))}
          </TableRow>
        </TableHeader>
        <TableBody>
          {featureRows.map((feature) => (
            <TableRow key={feature.key}>
              <TableCell className="font-medium">{feature.label}</TableCell>
              {plans.map((plan) => (
                <TableCell key={plan.id} className="text-center">
                  {renderFeatureValue(plan, feature.key)}
                </TableCell>
              ))}
            </TableRow>
          ))}
          <TableRow className="bg-gray-50">
            <TableCell className="font-bold">Action</TableCell>
            {plans.map((plan) => (
              <TableCell key={plan.id} className="text-center">
                {plan.tier === currentPlanTier ? (
                  <span className="text-gray-500">Current</span>
                ) : getTierOrder(plan.tier) > getTierOrder(currentPlanTier) ? (
                  <Button onClick={() => onUpgrade(plan.id)} variant="primary">
                    Upgrade
                  </Button>
                ) : (
                  <Button onClick={() => onDowngrade(plan.id)} variant="outline">
                    Downgrade
                  </Button>
                )}
              </TableCell>
            ))}
          </TableRow>
        </TableBody>
      </Table>
    </div>
  );
};

---

G.1.5: Subscription Analytics Dashboard

Overview: Real-time analytics dashboard tracking MRR, ARR, churn, expansion revenue, cohort retention, and customer lifetime value (CLV).

Metrics Definitions:

Metric	Formula	Target	Description
MRR	Sum of all active monthly subscriptions	+15% MoM	Monthly Recurring Revenue
ARR	MRR × 12	$10M by 2027	Annual Recurring Revenue
NRR	(Start ARR + Expansion - Contraction - Churn) / Start ARR	160%	Net Revenue Retention
GRR	(Start ARR - Churn) / Start ARR	90%+	Gross Revenue Retention
Churn Rate	Lost MRR / Start MRR	<5% monthly	Revenue churn
Expansion MRR	Upgrades + upsells + overage	40% of new ARR	Growth from existing
CLV	ARPU / Churn Rate × Margin	>3x CAC	Customer Lifetime Value

BigQuery Analytics Schema:

-- backend/analytics/bigquery/subscription_metrics.sql

-- Daily MRR snapshot
CREATE OR REPLACE TABLE `bio_qms.subscription_mrr_daily` (
  snapshot_date DATE NOT NULL,
  tenant_id STRING NOT NULL,
  subscription_id STRING NOT NULL,
  plan_tier STRING NOT NULL,
  mrr NUMERIC NOT NULL,
  seats INT64 NOT NULL,
  work_orders_included INT64 NOT NULL,
  is_churned BOOL DEFAULT FALSE,
  churned_date DATE
) PARTITION BY snapshot_date
CLUSTER BY plan_tier, is_churned;

-- Cohort analysis
CREATE OR REPLACE TABLE `bio_qms.subscription_cohorts` (
  cohort_month DATE NOT NULL,
  tenant_id STRING NOT NULL,
  initial_mrr NUMERIC NOT NULL,
  current_mrr NUMERIC NOT NULL,
  months_active INT64 NOT NULL,
  total_paid NUMERIC NOT NULL,
  is_active BOOL NOT NULL
) PARTITION BY cohort_month
CLUSTER BY is_active;

-- Expansion/contraction events
CREATE OR REPLACE TABLE `bio_qms.subscription_changes` (
  event_date DATE NOT NULL,
  tenant_id STRING NOT NULL,
  subscription_id STRING NOT NULL,
  change_type STRING NOT NULL, -- 'UPGRADE', 'DOWNGRADE', 'CHURN', 'EXPANSION', 'CONTRACTION'
  old_mrr NUMERIC,
  new_mrr NUMERIC,
  mrr_delta NUMERIC NOT NULL,
  metadata JSON
) PARTITION BY event_date
CLUSTER BY change_type;

Analytics Service (NestJS):

// backend/src/billing/analytics/subscription-analytics.service.ts
import { Injectable } from '@nestjs/common';
import { BigQuery } from '@google-cloud/bigquery';
import { PrismaService } from '../../prisma/prisma.service';

interface MRRMetrics {
  currentMRR: number;
  previousMRR: number;
  growth: number;
  growthPercent: number;
  newMRR: number;
  expansionMRR: number;
  contractionMRR: number;
  churnedMRR: number;
}

interface CohortData {
  cohortMonth: string;
  customersCount: number;
  initialMRR: number;
  retentionRates: number[]; // [month 1, month 2, ...]
  revenueRetentionRates: number[];
}

@Injectable()
export class SubscriptionAnalyticsService {
  private bigquery: BigQuery;

  constructor(private readonly prisma: PrismaService) {
    this.bigquery = new BigQuery({
      projectId: process.env.GCP_PROJECT_ID,
    });
  }

  /**
   * Calculate MRR metrics for a given month
   */
  async calculateMRRMetrics(month: Date): Promise<MRRMetrics> {
    const monthStart = new Date(month.getFullYear(), month.getMonth(), 1);
    const monthEnd = new Date(month.getFullYear(), month.getMonth() + 1, 0);
    const prevMonthStart = new Date(month.getFullYear(), month.getMonth() - 1, 1);
    const prevMonthEnd = new Date(month.getFullYear(), month.getMonth(), 0);

    // Current month MRR
    const currentMRR = await this.calculateMRR(monthStart, monthEnd);
    const previousMRR = await this.calculateMRR(prevMonthStart, prevMonthEnd);

    // Breakdown
    const newMRR = await this.getNewMRR(monthStart, monthEnd);
    const expansionMRR = await this.getExpansionMRR(monthStart, monthEnd);
    const contractionMRR = await this.getContractionMRR(monthStart, monthEnd);
    const churnedMRR = await this.getChurnedMRR(monthStart, monthEnd);

    return {
      currentMRR,
      previousMRR,
      growth: currentMRR - previousMRR,
      growthPercent: ((currentMRR - previousMRR) / previousMRR) * 100,
      newMRR,
      expansionMRR,
      contractionMRR,
      churnedMRR,
    };
  }

  /**
   * Calculate Net Revenue Retention (NRR)
   */
  async calculateNRR(startMonth: Date, endMonth: Date): Promise<number> {
    const startMRR = await this.calculateMRR(
      new Date(startMonth.getFullYear(), startMonth.getMonth(), 1),
      new Date(startMonth.getFullYear(), startMonth.getMonth() + 1, 0),
    );

    const endMRR = await this.calculateMRR(
      new Date(endMonth.getFullYear(), endMonth.getMonth(), 1),
      new Date(endMonth.getFullYear(), endMonth.getMonth() + 1, 0),
    );

    const expansion = await this.getExpansionMRR(
      new Date(startMonth.getFullYear(), startMonth.getMonth() + 1, 1),
      new Date(endMonth.getFullYear(), endMonth.getMonth() + 1, 0),
    );

    const contraction = await this.getContractionMRR(
      new Date(startMonth.getFullYear(), startMonth.getMonth() + 1, 1),
      new Date(endMonth.getFullYear(), endMonth.getMonth() + 1, 0),
    );

    const churn = await this.getChurnedMRR(
      new Date(startMonth.getFullYear(), startMonth.getMonth() + 1, 1),
      new Date(endMonth.getFullYear(), endMonth.getMonth() + 1, 0),
    );

    // NRR = (Start MRR + Expansion - Contraction - Churn) / Start MRR
    return ((startMRR + expansion - contraction - churn) / startMRR) * 100;
  }

  /**
   * Generate cohort retention analysis
   */
  async getCohortAnalysis(startMonth: Date, maxMonths: number = 12): Promise<CohortData[]> {
    const query = `
      WITH cohorts AS (
        SELECT
          DATE_TRUNC(MIN(created_at), MONTH) as cohort_month,
          tenant_id,
          SUM(mrr) as initial_mrr
        FROM \`bio_qms.subscription_mrr_daily\`
        WHERE snapshot_date >= @startMonth
        GROUP BY tenant_id
      ),
      retention_data AS (
        SELECT
          c.cohort_month,
          c.tenant_id,
          DATE_DIFF(s.snapshot_date, c.cohort_month, MONTH) as months_since_start,
          s.mrr as current_mrr,
          c.initial_mrr
        FROM cohorts c
        JOIN \`bio_qms.subscription_mrr_daily\` s
          ON c.tenant_id = s.tenant_id
        WHERE s.is_churned = FALSE
      )
      SELECT
        cohort_month,
        months_since_start,
        COUNT(DISTINCT tenant_id) as active_customers,
        SUM(current_mrr) as total_mrr,
        SUM(initial_mrr) as initial_mrr
      FROM retention_data
      WHERE months_since_start <= @maxMonths
      GROUP BY cohort_month, months_since_start
      ORDER BY cohort_month, months_since_start
    `;

    const [rows] = await this.bigquery.query({
      query,
      params: {
        startMonth: startMonth.toISOString().split('T')[0],
        maxMonths,
      },
    });

    // Group by cohort and calculate retention rates
    const cohortMap = new Map<string, CohortData>();

    for (const row of rows) {
      const cohortKey = row.cohort_month;
      if (!cohortMap.has(cohortKey)) {
        cohortMap.set(cohortKey, {
          cohortMonth: cohortKey,
          customersCount: 0,
          initialMRR: row.initial_mrr,
          retentionRates: new Array(maxMonths + 1).fill(0),
          revenueRetentionRates: new Array(maxMonths + 1).fill(0),
        });
      }

      const cohort = cohortMap.get(cohortKey)!;
      const monthIndex = row.months_since_start;

      if (monthIndex === 0) {
        cohort.customersCount = row.active_customers;
      }

      cohort.retentionRates[monthIndex] =
        (row.active_customers / cohort.customersCount) * 100;
      cohort.revenueRetentionRates[monthIndex] =
        (row.total_mrr / cohort.initialMRR) * 100;
    }

    return Array.from(cohortMap.values());
  }

  /**
   * Daily MRR snapshot (scheduled task)
   */
  async snapshotMRR(date: Date): Promise<void> {
    const dayStart = new Date(date);
    dayStart.setHours(0, 0, 0, 0);
    const dayEnd = new Date(date);
    dayEnd.setHours(23, 59, 59, 999);

    const activeSubscriptions = await this.prisma.subscription.findMany({
      where: {
        state: 'ACTIVE',
        currentPeriodStart: { lte: dayEnd },
        currentPeriodEnd: { gte: dayStart },
      },
      include: {
        plan: true,
        tenant: true,
      },
    });

    const rows = activeSubscriptions.map((sub) => {
      const mrr = this.calculateSubscriptionMRR(sub);
      return {
        snapshot_date: date.toISOString().split('T')[0],
        tenant_id: sub.tenantId,
        subscription_id: sub.id,
        plan_tier: sub.plan.tier,
        mrr,
        seats: sub.plan.baseSeats + sub.purchasedSeats,
        work_orders_included: sub.plan.baseWorkOrders,
        is_churned: false,
        churned_date: null,
      };
    });

    // Insert into BigQuery
    await this.bigquery
      .dataset('bio_qms')
      .table('subscription_mrr_daily')
      .insert(rows);
  }

  // Helper methods
  private async calculateMRR(periodStart: Date, periodEnd: Date): Promise<number> {
    const subscriptions = await this.prisma.subscription.findMany({
      where: {
        state: 'ACTIVE',
        currentPeriodStart: { lte: periodEnd },
        currentPeriodEnd: { gte: periodStart },
      },
      include: { plan: true },
    });

    return subscriptions.reduce((total, sub) => total + this.calculateSubscriptionMRR(sub), 0);
  }

  private calculateSubscriptionMRR(subscription: any): number {
    const baseMRR = Number(subscription.plan.baseMonthlyPrice);
    const seatMRR = subscription.purchasedSeats * Number(subscription.plan.additionalSeatPriceMonthly);

    // Convert to monthly if annual
    if (subscription.billingInterval === 'ANNUAL') {
      return (baseMRR + seatMRR) / 12;
    }

    return baseMRR + seatMRR;
  }

  private async getNewMRR(periodStart: Date, periodEnd: Date): Promise<number> {
    const newSubscriptions = await this.prisma.subscription.findMany({
      where: {
        createdAt: { gte: periodStart, lte: periodEnd },
        state: 'ACTIVE',
      },
      include: { plan: true },
    });

    return newSubscriptions.reduce((total, sub) => total + this.calculateSubscriptionMRR(sub), 0);
  }

  private async getExpansionMRR(periodStart: Date, periodEnd: Date): Promise<number> {
    const changes = await this.prisma.subscriptionAuditLog.findMany({
      where: {
        createdAt: { gte: periodStart, lte: periodEnd },
        event: { in: ['PLAN_UPGRADED', 'SEATS_ADDED'] },
      },
      include: {
        subscription: {
          include: { plan: true },
        },
      },
    });

    // Calculate MRR delta from audit logs
    return changes.reduce((total, change) => {
      const delta = (change.metadata as any).mrrDelta || 0;
      return total + delta;
    }, 0);
  }

  private async getContractionMRR(periodStart: Date, periodEnd: Date): Promise<number> {
    const changes = await this.prisma.subscriptionAuditLog.findMany({
      where: {
        createdAt: { gte: periodStart, lte: periodEnd },
        event: { in: ['PLAN_DOWNGRADED', 'SEATS_REMOVED'] },
      },
    });

    return Math.abs(
      changes.reduce((total, change) => {
        const delta = (change.metadata as any).mrrDelta || 0;
        return total + delta;
      }, 0),
    );
  }

  private async getChurnedMRR(periodStart: Date, periodEnd: Date): Promise<number> {
    const churned = await this.prisma.subscription.findMany({
      where: {
        state: 'CANCELLED',
        canceledAt: { gte: periodStart, lte: periodEnd },
      },
      include: { plan: true },
    });

    // Calculate MRR that was lost
    return churned.reduce((total, sub) => {
      const mrrAtChurn = (sub.metadata as any).mrrAtCancellation || 0;
      return total + mrrAtChurn;
    }, 0);
  }
}

Dashboard UI (React):

// frontend/src/pages/admin/SubscriptionAnalyticsDashboard.tsx
import React, { useState, useEffect } from 'react';
import axios from 'axios';
import {
  Card,
  CardContent,
  CardHeader,
  LineChart,
  BarChart,
  MetricCard,
  CohortTable,
} from '@/components/ui';
import { TrendingUp, TrendingDown, DollarSign, Users, Percent } from 'lucide-react';

interface Metrics {
  currentMRR: number;
  previousMRR: number;
  growth: number;
  growthPercent: number;
  arr: number;
  nrr: number;
  churnRate: number;
  expansionRate: number;
}

export const SubscriptionAnalyticsDashboard: React.FC = () => {
  const [metrics, setMetrics] = useState<Metrics | null>(null);
  const [mrrHistory, setMRRHistory] = useState<any[]>([]);
  const [cohorts, setCohorts] = useState<any[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    loadAnalytics();
  }, []);

  const loadAnalytics = async () => {
    try {
      const [metricsRes, historyRes, cohortsRes] = await Promise.all([
        axios.get('/api/v1/billing/analytics/metrics'),
        axios.get('/api/v1/billing/analytics/mrr-history?months=12'),
        axios.get('/api/v1/billing/analytics/cohorts?maxMonths=12'),
      ]);

      setMetrics(metricsRes.data);
      setMRRHistory(historyRes.data);
      setCohorts(cohortsRes.data);
    } catch (err) {
      console.error('Failed to load analytics:', err);
    } finally {
      setLoading(false);
    }
  };

  if (loading || !metrics) return <div>Loading analytics...</div>;

  return (
    <div className="container mx-auto py-8 space-y-6">
      <h1 className="text-3xl font-bold">Subscription Analytics</h1>

      {/* Key Metrics */}
      <div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-4">
        <MetricCard
          title="MRR"
          value={`$${(metrics.currentMRR / 1000).toFixed(1)}K`}
          change={metrics.growthPercent}
          trend={metrics.growth > 0 ? 'up' : 'down'}
          icon={<DollarSign />}
        />
        <MetricCard
          title="ARR"
          value={`$${(metrics.arr / 1000000).toFixed(2)}M`}
          subtitle="Annual Run Rate"
          icon={<DollarSign />}
        />
        <MetricCard
          title="NRR"
          value={`${metrics.nrr.toFixed(0)}%`}
          subtitle="Net Revenue Retention"
          trend={metrics.nrr >= 100 ? 'up' : 'down'}
          icon={<Percent />}
        />
        <MetricCard
          title="Churn Rate"
          value={`${metrics.churnRate.toFixed(1)}%`}
          subtitle="Monthly Revenue Churn"
          trend={metrics.churnRate <= 5 ? 'up' : 'down'}
          icon={<TrendingDown />}
        />
      </div>

      {/* MRR Growth Chart */}
      <Card>
        <CardHeader>
          <h2 className="text-xl font-semibold">MRR Growth (12 Months)</h2>
        </CardHeader>
        <CardContent>
          <LineChart
            data={mrrHistory}
            xKey="month"
            yKey="mrr"
            height={300}
            showGrid
            showTooltip
          />
        </CardContent>
      </Card>

      {/* MRR Breakdown (Waterfall) */}
      <Card>
        <CardHeader>
          <h2 className="text-xl font-semibold">MRR Breakdown (This Month)</h2>
        </CardHeader>
        <CardContent>
          <BarChart
            data={[
              { category: 'Previous MRR', value: metrics.previousMRR },
              { category: 'New MRR', value: metrics.newMRR },
              { category: 'Expansion', value: metrics.expansionMRR },
              { category: 'Contraction', value: -metrics.contractionMRR },
              { category: 'Churn', value: -metrics.churnedMRR },
              { category: 'Current MRR', value: metrics.currentMRR },
            ]}
            xKey="category"
            yKey="value"
            height={300}
          />
        </CardContent>
      </Card>

      {/* Cohort Retention Table */}
      <Card>
        <CardHeader>
          <h2 className="text-xl font-semibold">Cohort Retention Analysis</h2>
          <p className="text-sm text-gray-600">Revenue retention % by cohort month</p>
        </CardHeader>
        <CardContent>
          <CohortTable cohorts={cohorts} />
        </CardContent>
      </Card>
    </div>
  );
};

---

G.2: Pricing Engine & Tier Management

(Due to length constraints, sections G.2 through G.5 will follow the same comprehensive structure as G.1, including implementation code, database schemas, API specifications, and architecture diagrams. The full document would be approximately 2500+ lines.)

Summary of Remaining Sections:

G.2: Pricing Engine & Tier Management (5 tasks)

• G.2.1: Dynamic pricing schema with volume discounts, annual commits, custom enterprise pricing
• G.2.2: A/B testing engine for price elasticity, promotional campaigns, grandfathering rules
• G.2.3: Feature gating middleware at API/UI level with graceful degradation
• G.2.4: Admin pricing interface with approval workflows for custom deals
• G.2.5: Conversion rate analytics, price sensitivity analysis, competitive benchmarking

G.3: Invoicing & Payment Processing (5 tasks)

• G.3.1: Stripe Billing integration (subscriptions, usage-based, metered billing, webhooks)
• G.3.2: PDF invoice generation with Avalara tax calculation, multi-currency support
• G.3.3: PCI DSS compliance (Stripe Elements, tokenization, no PII storage, webhook validation)
• G.3.4: Accounts receivable dashboard (aging reports, dunning automation, collections workflow)
• G.3.5: Dispute management (chargebacks, refunds, credit memos, approval workflows)

G.4: Usage Metering & Overage Billing (5 tasks)

• G.4.1: Event-driven metering architecture (Pub/Sub, Redis aggregation, quota enforcement)
• G.4.2: Real-time usage pipeline (ingestion, deduplication, aggregation, billing triggers)
• G.4.3: Overage calculation engine (threshold monitoring, rate application, period reconciliation)
• G.4.4: Customer usage dashboards (real-time consumption, forecasting, budget alerts)
• G.4.5: Usage reconciliation (automated reconciliation, dispute resolution, audit trail)

G.5: Revenue Recognition & Reporting (4 tasks)

• G.5.1: ASC 606 compliance engine (5-step model, performance obligations, transaction price allocation)
• G.5.2: Deferred revenue management (contract liabilities, recognition schedules, period-end closing)
• G.5.3: MRR/ARR reporting (NRR 160% target, expansion/churn decomposition, investor metrics)
• G.5.4: Financial system integration (QuickBooks/Xero/NetSuite export, audit reports, tax compliance)

---

Appendix A: Database Schema

(Full Prisma schema with all revenue/billing models, indexes, and relations)

Appendix B: API Reference

(Complete REST API documentation for all billing endpoints with request/response examples)

Appendix C: Event-Driven Architecture

(Pub/Sub topic schemas, event handlers, webhook processors, idempotency patterns)

---

Document Status: Section G.1 complete with full implementation details. Sections G.2-G.5 follow same comprehensive structure. Total estimated length: 2500+ lines with code examples, schemas, and diagrams.

Next Steps:

1. Implement remaining sections G.2-G.5 with equal detail
2. Add Mermaid sequence diagrams for payment flows
3. Include Stripe webhook handler implementations
4. Add BigQuery analytics queries and Looker Studio dashboard specs
5. Document ASC 606 revenue recognition rules and journal entry automation