Revenue (Admin)

Audience: Finance, super-admins, ops · Where in app: /admin/revenue · Access: Super-admin only — backend rejects any caller whose user_type !== 'admin' (_ensureAdmin returns 403).

The Revenue screen is the company's internal money dashboard. It reports actual paid subscription revenue (from user_invoices) and live recurring revenue (MRR/ARR computed from currently-active subscriptions). It covers both direct customers and agency (white-label) customers, splits figures by currency, and includes maintenance tools to repair legacy invoice data and detect data that breaks the Stripe→Xero billing pipeline.

What it does

• Shows headline revenue metrics for a selected date range, with a delta vs. the prior comparable period.
• Plots revenue over time (a chart bucketed by day or month).
• Breaks down live MRR by plan, lists top customers by revenue, and lists recent paid invoices with receipt links.
• Provides two maintenance utilities: an invoice-field backfill (stamp missing paid_at / currency) and an orphaned-user check (find records that crash the Stripe webhook).

How it works

Data source

Two sources, never mixed:

• Actual revenue comes from the user_invoices collection (backend/app/models/InvoiceModel.js, mongoose model name user_invoices). Revenue figures aggregate invoices where invoice_status: 'paid', is_trial != true, and paid_at falls in range.
• Live MRR/ARR is computed on the fly from the subscriptions and agency_subscription collections joined to their packages / agency_packages — it is not read from invoices. MRR reflects what is currently active, not what was billed.

Invoices are written by the billing pipeline (Stripe charges → invoice records, with Xero used for invoicing/accounting sync — note xero_invoice_id, xero_invoice_number, stripe_id fields on the invoice). The revenue screen only reads invoices.

How "revenue" is computed

The controller (backend/app/controllers/AdminRevenueController.js) runs MongoDB aggregations:

• The paid_at filter is load-bearing. Every revenue aggregation matches paid_at: { $gte: start, $lte: end }. An invoice with invoice_status: 'paid' but no paid_at is silently excluded from all reports. The InvoiceModel now has a pre('save') hook that stamps paid_at = new Date() whenever a paid invoice lacks it, but legacy invoices created before that fix can still be missing it — hence the backfill tool below.
• Currencies are never summed together. Revenue is grouped by currency ($group: { _id: '$currency', total: { $sum: '$amount' } }) and each currency is shown on its own line. A null/empty currency falls back to USD. The _byCurrency() helper normalizes/uppercases the currency and sorts descending by value. The UI's formatCurrencyParts stacks one line per currency on the metric card.
• Primary currency = the currency with the most revenue in range (falls back to the largest-MRR currency, else USD).
• Period-over-period change (change_pct) is computed only within the primary currency, comparing this range's primary total to the immediately-prior equal-length range's primary total. Returns null when there is no prior revenue to compare against.

Date ranges

The UI (frontend/src/views/admin/revenue/List.vue) offers presets — Last 7d, Last 30d, Last 90d, Last 12mo (365d), All time — plus a custom date-range picker. Defaults to Last 30d. "All time" uses a start of 2020-01-01. Custom ranges send explicit start_date/end_date (ISO). The backend defaults to the last 30 days if no range is supplied.

Timeline granularity

The timeline endpoint auto-selects bucket size: monthly if the range is longer than 90 days, otherwise daily. Buckets are computed in UTC. Each bucket carries a by_currency map; the chart renders one series per currency. Empty buckets are filled with zeros so the line is continuous.

Screens & fields

Overview cards (POST /admin/revenue/overview)

Card	Field(s)	Meaning
Total Revenue	revenue_by_currency[].total, invoice_count, change_pct	Paid, non-trial revenue in range, per currency. Hint = invoice count. Delta badge = change_pct vs prior period.
MRR	mrr_by_currency[].mrr, arr_by_currency[].arr	Live monthly recurring revenue (direct + agency), per currency. Hint = ARR (MRR × 12).
Active Subscriptions	active_subscriptions, active_direct_subscriptions, active_agency_subscriptions	Count of all active subs now (direct + agency).
New Subscriptions	new_subscriptions	Non-trial subs created in range.
Trials	trial_subscriptions	Active trials right now (is_trial:true, subscription_status:'active', not expired).
Canceled	canceled_subscriptions	Cancellations in range. Direct subs counted via cancel_reason set + updated_at in range; agency subs via status:'canceled' + canceled_at in range.
Failed Payments	failed_payments	Subs whose last_failed_payment_at falls in range.
Agency Revenue	agency_revenue_by_currency[].total, direct_revenue_by_currency[].total	Revenue split: agency vs direct, per currency.

Revenue Over Time chart (POST /admin/revenue/timeline)

• One area series per currency; bucket label = day or month.
• Shows the active granularity ("day"/"month") badge.

Revenue by Plan (POST /admin/revenue/by-plan)

Table of currently active subscriptions grouped by package. Combines direct + agency plans, sorted by MRR desc.

Column	Source	Notes
Plan	title + a kind badge (Agency / Direct)	Direct title from package.title; agency title from agency_package.name.
Subscribers	total_count (+ annual_count for direct)	Count of active subs on that package.
MRR	mrr + currency	Direct annual subs are normalized to monthly: base_price × (1 − annual_discount/100) ÷ 12; monthly subs use base_price. Agency MRR = agency_package.amount.

Top Customers (POST /admin/revenue/top-customers)

Highest-revenue customers in range (default limit 10, max 50). Grouped by (user_id, agency_id, currency).

Column	Source
Customer	agency name → company name → user name/email fallback chain; agency rows flagged via is_agency.
Invoices	count of paid invoices in range.
Revenue	summed amount per currency.

Recent Paid Invoices (POST /admin/revenue/recent-invoices)

Paginated list (default 25/page, max 100), sorted by paid_at desc.

Column	Source
Date	paid_at, formatted local datetime.
Customer	agency_id.name → user_id.name → user_id.email.
Type	Agency if agency_id present, else Direct.
Amount	amount + currency.
Receipt	receipt_url (external Stripe link), else —.

Data Maintenance — backfill (POST /admin/revenue/backfill-invoice-fields)

Repairs legacy invoices so they appear in reporting. Dry-run by default; "Apply backfill" sends { apply: true } after a confirm dialog. For each paid invoice missing paid_at, it stamps paid_at = created_at. For each invoice missing currency, it resolves the currency from the invoice's subscription → package (agency invoices use the agency package) and stores it uppercased. Idempotent. Returns { mode, scanned, set_paid_at, set_currency, unresolved_currency, samples }.

Orphaned User Check (GET /migrations/check-orphaned-users)

Read-only. Finds subscriptions, agency subscriptions, and pending-Xero invoices whose user_id no longer resolves to a user — the records that crash the Stripe webhook with Cannot read properties of null (reading 'xero_contact_id'). Returns counts plus the offending _id / user_id / stripe_id rows.

Behaviors & edge cases

• Missing paid_at = invisible revenue. This was a real incident: paid invoices created on certain paths never got paid_at, so they dropped out of every report. Fixed two ways — the pre('save') hook stamps it going forward, and the backfill tool repairs history. If revenue "looks too low," run the backfill dry-run first.
• Never sum across currencies. A "Total Revenue" of "NZD 1,200 · USD 340" is two separate numbers, not 1,540 of anything. The UI deliberately stacks them; do not add them in a spreadsheet.
• MRR vs invoiced revenue diverge by design. MRR is computed from live active subscriptions and packages; invoiced revenue is historical paid invoices. They will not tie out exactly (trials, mid-cycle changes, annual amortization, refunds).
• Annual subscriptions are amortized in MRR. An annual plan contributes price × (1 − discount) / 12 to MRR, not its full annual charge.
• change_pct can be null when the prior period had zero primary-currency revenue (e.g. a brand-new currency or the first period) — the UI then shows no delta badge.
• Trials and is_trial invoices are excluded from revenue everywhere (is_trial: { $ne: true }).
• Currency unresolved during backfill happens when an invoice has no subscription, or the subscription/package has no currency — counted as unresolved_currency and left unset.

Technical implementation

• Route file: backend/app/routes/adminRevenueRoutes.js (all POST /admin/revenue/*, passport-guarded)
• Controller: backend/app/controllers/AdminRevenueController.js (overview, timeline, byPlan, topCustomers, recentInvoices, backfillInvoiceFields)
• Models: InvoiceModel (user_invoices), SubscriptionModel, AgencySubscriptionModel, PackageModel, AgencyPackageModel, UserModel, CompanyModel, AgencyModel
• Orphan check: backend/app/routes/migrationRoutes.js → GET /migrations/check-orphaned-users
• Frontend view: frontend/src/views/admin/revenue/List.vue
• Frontend repo: frontend/src/repositories/admin/revenue.js
• Related service: ../services/backend.md

Related

• overview.md — the admin portal index
• cost-tracking.md — AI provider spend (separate from subscription revenue)
• companies-users.md — per-company subscription/credit management