SaaS Revenue Recognition: MRR, ARR, Deferred Revenue, and Churn Accounting

SaaS revenue metrics seem simple until you encounter plan upgrades mid-cycle, annual prepayments, refunds, failed payments, and multi-seat pricing. At that point, most founders discover their "MRR" number doesn't match their bank account, their accountant is confused, and their investor dashboard is showing different numbers than their analytics tool.

The fix is getting the data model right before building the metrics layer.

---

The Core Data Model

-- Subscription lifecycle events — append-only ledger
CREATE TABLE subscription_events (
  id              UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID          NOT NULL,
  subscription_id UUID          NOT NULL,
  event_type      TEXT          NOT NULL,
  -- Types: 'new', 'upgrade', 'downgrade', 'reactivation',
  --        'churn', 'pause', 'unpause', 'price_change'
  occurred_at     TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  
  -- Plan details at time of event
  plan_id         TEXT          NOT NULL,
  billing_period  TEXT          NOT NULL,  -- 'monthly' | 'annual'
  mrr_amount      NUMERIC(10,2) NOT NULL,  -- Monthly value at time of event
  arr_amount      NUMERIC(10,2) NOT NULL,  -- = mrr_amount * 12
  seat_count      INTEGER       NOT NULL DEFAULT 1,
  
  -- For tracking revenue changes
  mrr_delta       NUMERIC(10,2) NOT NULL DEFAULT 0,  -- Change from previous MRR
  
  metadata        JSONB         NOT NULL DEFAULT '{}'
);

-- Invoices for deferred revenue tracking
CREATE TABLE invoices (
  id              UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID          NOT NULL,
  subscription_id UUID          NOT NULL,
  stripe_invoice_id TEXT        UNIQUE,
  
  amount_cents    INTEGER       NOT NULL,
  currency        CHAR(3)       NOT NULL DEFAULT 'USD',
  
  billing_period_start DATE     NOT NULL,
  billing_period_end   DATE     NOT NULL,
  
  status          TEXT          NOT NULL,  -- 'draft'|'open'|'paid'|'void'|'uncollectible'
  paid_at         TIMESTAMPTZ,
  
  -- Deferred revenue columns (populated by nightly job)
  recognized_amount_cents   INTEGER NOT NULL DEFAULT 0,
  deferred_amount_cents     INTEGER NOT NULL DEFAULT 0
);

---

MRR Calculation

The key insight: MRR is a snapshot metric (what is the recurring monthly value right now?), not a cumulative metric.

-- Current MRR snapshot
-- Sum of the most recent subscription event per active tenant
WITH latest_events AS (
  SELECT DISTINCT ON (subscription_id)
    subscription_id,
    tenant_id,
    event_type,
    mrr_amount,
    occurred_at
  FROM subscription_events
  ORDER BY subscription_id, occurred_at DESC
),
active_subscriptions AS (
  SELECT *
  FROM latest_events
  WHERE event_type NOT IN ('churn', 'pause')  -- Exclude inactive
)
SELECT
  SUM(mrr_amount) AS total_mrr,
  COUNT(*) AS active_subscriptions,
  SUM(mrr_amount) * 12 AS arr
FROM active_subscriptions;

-- MRR over time (for charting)
WITH monthly_snapshots AS (
  SELECT
    DATE_TRUNC('month', gs.month)::DATE AS snapshot_month,
    se.subscription_id,
    -- Get the most recent event for each subscription as of this month
    LAST_VALUE(se.mrr_amount) OVER (
      PARTITION BY se.subscription_id, DATE_TRUNC('month', gs.month)
      ORDER BY se.occurred_at
      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS mrr_at_month
  FROM GENERATE_SERIES(
    '2026-01-01'::DATE,
    CURRENT_DATE,
    '1 month'::INTERVAL
  ) gs(month)
  JOIN subscription_events se ON se.occurred_at <= gs.month + INTERVAL '1 month'
  WHERE se.event_type NOT IN ('churn', 'pause')
)
SELECT
  snapshot_month,
  SUM(mrr_at_month) AS mrr
FROM monthly_snapshots
GROUP BY snapshot_month
ORDER BY snapshot_month;

---

MRR Movement Waterfall

The MRR waterfall breaks down what caused MRR to change month-over-month:

-- MRR movement components for a given month
-- New MRR + Expansion - Contraction - Churn = Net New MRR

WITH month_events AS (
  SELECT
    event_type,
    mrr_delta,
    tenant_id,
    subscription_id
  FROM subscription_events
  WHERE occurred_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND occurred_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
)
SELECT
  -- New MRR: brand new subscriptions
  SUM(mrr_delta) FILTER (WHERE event_type = 'new')          AS new_mrr,

  -- Expansion MRR: upgrades and seat additions (positive deltas on existing subs)
  SUM(mrr_delta) FILTER (
    WHERE event_type IN ('upgrade', 'price_change') AND mrr_delta > 0
  ) AS expansion_mrr,

  -- Contraction MRR: downgrades (negative deltas on existing subs)
  ABS(SUM(mrr_delta) FILTER (
    WHERE event_type IN ('downgrade', 'price_change') AND mrr_delta < 0
  )) AS contraction_mrr,

  -- Churned MRR: subscriptions that ended
  ABS(SUM(mrr_delta) FILTER (WHERE event_type = 'churn')) AS churned_mrr,

  -- Reactivation MRR: previously churned returning
  SUM(mrr_delta) FILTER (WHERE event_type = 'reactivation') AS reactivation_mrr,

  -- Net New MRR = New + Expansion + Reactivation - Contraction - Churn
  SUM(mrr_delta) AS net_new_mrr
FROM month_events;

---

Deferred Revenue (Annual Plans)

When a customer pays $1,200 for annual access, you've received $1,200 cash but only earned $100/month as you deliver the service. The unearned portion is deferred revenue — a liability on your balance sheet.

// src/services/revenue/deferred-revenue.service.ts

interface Invoice {
  id: string;
  amountCents: number;
  billingPeriodStart: Date;
  billingPeriodEnd: Date;
  paidAt: Date | null;
}

interface RevenueScheduleLine {
  month: Date;
  recognizableAmountCents: number;
  cumulativeRecognizedCents: number;
  remainingDeferredCents: number;
}

// Calculate monthly revenue recognition schedule for an invoice
export function buildRevenueSchedule(invoice: Invoice): RevenueScheduleLine[] {
  if (!invoice.paidAt) return []; // Can't recognize revenue on unpaid invoices

  const startDate = invoice.billingPeriodStart;
  const endDate = invoice.billingPeriodEnd;
  const totalDays =
    (endDate.getTime() - startDate.getTime()) / (1000 * 60 * 60 * 24);

  // Build month-by-month schedule
  const schedule: RevenueScheduleLine[] = [];
  let cumulativeRecognized = 0;
  let currentMonth = new Date(startDate);
  currentMonth.setDate(1); // Start of first month

  while (currentMonth <= endDate) {
    const monthEnd = new Date(currentMonth);
    monthEnd.setMonth(monthEnd.getMonth() + 1);
    monthEnd.setDate(0); // Last day of month

    // Days in this month that fall within the billing period
    const periodStart = Math.max(
      startDate.getTime(),
      currentMonth.getTime()
    );
    const periodEnd = Math.min(endDate.getTime(), monthEnd.getTime());
    const daysInPeriod = Math.max(
      0,
      (periodEnd - periodStart) / (1000 * 60 * 60 * 24)
    );

    const recognizableThisMonth = Math.round(
      (daysInPeriod / totalDays) * invoice.amountCents
    );

    cumulativeRecognized += recognizableThisMonth;

    schedule.push({
      month: new Date(currentMonth),
      recognizableAmountCents: recognizableThisMonth,
      cumulativeRecognizedCents: cumulativeRecognized,
      remainingDeferredCents:
        invoice.amountCents - cumulativeRecognized,
    });

    // Advance to next month
    currentMonth.setMonth(currentMonth.getMonth() + 1);
  }

  return schedule;
}

// Nightly job: update invoice recognition amounts
export async function updateDeferredRevenue(): Promise<void> {
  const unpaidInvoices = await db.query<Invoice>(
    `SELECT * FROM invoices
     WHERE status = 'paid'
       AND billing_period_end >= CURRENT_DATE - INTERVAL '24 months'`
  );

  for (const invoice of unpaidInvoices.rows) {
    const schedule = buildRevenueSchedule(invoice);
    const today = new Date();

    // Revenue recognized = sum of all months up to today
    const recognized = schedule
      .filter((line) => line.month <= today)
      .reduce((sum, line) => sum + line.recognizableAmountCents, 0);

    const deferred = invoice.amountCents - recognized;

    await db.query(
      `UPDATE invoices
       SET recognized_amount_cents = $1,
           deferred_amount_cents = $2
       WHERE id = $3`,
      [recognized, deferred, invoice.id]
    );
  }
}

---

Net Revenue Retention (NRR)

NRR is the most important metric for SaaS health. It measures how much revenue you retain and expand from existing customers:

NRR = (Starting MRR + Expansion - Contraction - Churn) / Starting MRR × 100%

NRR > 120%: World-class (growth from existing customers alone)
NRR 100–120%: Healthy (customers expand faster than they churn)
NRR 80–100%: Concerning (must acquire to compensate for churn)
NRR < 80%: Broken unit economics

-- Monthly Net Revenue Retention
WITH monthly_cohorts AS (
  -- Starting MRR for each tenant at the beginning of the period
  SELECT DISTINCT ON (tenant_id)
    tenant_id,
    mrr_amount AS starting_mrr
  FROM subscription_events
  WHERE occurred_at < DATE_TRUNC('month', CURRENT_DATE)  -- Before this month
  ORDER BY tenant_id, occurred_at DESC
),
current_mrr AS (
  -- Current MRR for those same tenants
  SELECT DISTINCT ON (se.tenant_id)
    se.tenant_id,
    se.mrr_amount AS current_mrr,
    se.event_type
  FROM subscription_events se
  JOIN monthly_cohorts mc ON mc.tenant_id = se.tenant_id
  ORDER BY se.tenant_id, se.occurred_at DESC
)
SELECT
  COUNT(DISTINCT mc.tenant_id) AS cohort_size,
  SUM(mc.starting_mrr) AS starting_mrr,
  SUM(
    CASE
      WHEN cm.event_type IN ('churn', 'pause') THEN 0
      ELSE COALESCE(cm.current_mrr, 0)
    END
  ) AS ending_mrr,
  ROUND(
    100.0 * SUM(
      CASE
        WHEN cm.event_type IN ('churn', 'pause') THEN 0
        ELSE COALESCE(cm.current_mrr, 0)
      END
    ) / NULLIF(SUM(mc.starting_mrr), 0),
    1
  ) AS nrr_pct
FROM monthly_cohorts mc
LEFT JOIN current_mrr cm ON cm.tenant_id = mc.tenant_id;

---

ASC 606 Compliance Notes

ASC 606 (US GAAP revenue recognition standard) requires:

1. Identify the contract with the customer
   → Each subscription is a contract; auto-renewals are contract modifications

2. Identify performance obligations
   → SaaS: typically one obligation (continuous access to software)
   → Professional services: separate obligation from subscription

3. Determine the transaction price
   → Exclude variable consideration (refunds, usage-based) until probable

4. Allocate to performance obligations
   → Bundle discounts must be allocated proportionally

5. Recognize revenue when obligation is satisfied
   → SaaS: ratably over the service period (not on payment receipt)
   → One-time setup fees: typically ratably over estimated customer life

Implementation note: For small/seed-stage companies, cash-basis accounting with MRR tracking is usually sufficient. ASC 606 compliance becomes critical at Series B+ when institutional investors require audited financials.

---

Cost Ranges for Revenue System Build

---

See Also

• SaaS Metrics Benchmarks — industry benchmarks for NRR, churn
• SaaS Pricing Strategy — pricing models and impact on MRR
• Stripe Billing Engineering — Stripe subscription integration
• SaaS Churn Prediction — predicting churn before it happens

---

Working With Viprasol

Revenue metrics built on the wrong data model produce the wrong numbers — and wrong numbers lead to wrong decisions. We design subscription event ledgers, implement deferred revenue schedules, and build revenue dashboards that match your accountant's view of the business — so your metrics and your bank account tell the same story.

SaaS engineering → | Talk to our engineers →