Revenue

Read-only revenue analytics on top of Stripe, Polar and RevenueCat. A connector pulls customers, subscriptions and payments through the provider's REST API and keeps them current with signature-verified webhooks; SaaS Pro Max normalizes all three into one shape and derives MRR, ARR, movements, churn and ARPA from it.

Nothing in this module ever writes to a billing provider. The credentials it stores are read scopes, and there is no code path that creates, cancels or refunds anything.

• Setup guides: Stripe · Polar · RevenueCat
• Permissions: revenue.read to view, revenue.write to manage connectors. Creating or deleting a connector also needs a passkey verified in the last ten minutes.
• Billing emails are personal data: they are masked in the console unless the member also holds people.pii.read.

What a connector is

One connector is one billing account. It holds:

Both secrets go through the vault (src/lib/vault.ts), envelope-encrypted with environment='revenue'. They are decrypted for exactly one call and are never returned to the browser. Replacing a key mints a new credential and revokes the old row rather than overwriting it.

Webhook URL

https://<your host>/api/v1/webhooks/<kind>/<connectorId>?t=<webhook_path_token>

The token is a cheap first gate: a scan of /api/v1/webhooks/stripe/<uuid> never reaches the vault. It is not the authentication — every request is still verified against the provider's own signature, and an unknown connector, a wrong token and a bad signature all answer the same 401.

Ingest paths

Webhooks are the live path. The pipeline is:

1. read the raw body (never a parsed one — signatures cover exact bytes);
2. resolve the connector and compare the URL token in constant time;
3. decrypt the webhook secret and verify the provider's signature (401 on failure);
4. claim the event by its provider event id in revenue_events, inside the same transaction that applies it, so a rollback cannot make a retry look like a duplicate;
5. upsert the normalized changes, link customers to persons by email;
6. enqueue revenue.daily.recompute for the affected day;
7. answer 200.

A duplicate delivery answers 200 {"duplicate": true} and changes nothing. A failure records the event id, type and error message in revenue_events and answers 500, so the provider retries on its own schedule. Payload contents are never logged.

Sync is the catch-up path. revenue.sync.due runs hourly and fans out one revenue.sync job per live connector. Each run reads from last_sync_at - 1 day (the overlap absorbs backdating and clock drift), or the last 24 months on a first run, and walks the provider's list endpoints with its own pagination. Because every write is an upsert keyed on (connector_id, external_id), an overlapping window is free.

revenue.link.persons runs daily and links any customer that has since gained a matching person.

Normalization

MRR

amount_cents is always the total charged per interval, quantity folded in — the figure that appears on the invoice. Monthly recurring revenue is that amount divided by the number of months the interval covers:

A weekly plan at 10.00 is 43.33 of MRR, not 40.00: aligning a week to "a quarter of a month" loses a month of revenue a year.

Only active and past_due subscriptions contribute MRR. A trial is counted in its own tile and becomes MRR when it converts, so a burst of signups can never look like revenue. unpaid, paused, incomplete, canceled and expired are all zero.

Stripe subscriptions with several items are folded onto the first item's interval, so one stored amount always reproduces the true MRR even for the mixed intervals Stripe allows since the 2025-03-31 API version.

The subscription state timeline

Providers hand us a current snapshot, never a history of amounts, so revenue_subscription_states records an append-only MRR timeline. Each upsert derives the least surprising timeline its fields justify:

1. a row at started_at — trialing when a trial covers that moment, otherwise active at the full amount;
2. a row at trial_ends_at when the trial has ended;
3. a row at ended_at (else canceled_at) dropping MRR to zero, when the current status is terminal;
4. otherwise a row at the observation time carrying the current status.

Rows are upserted on (subscription_id, effective_at), so replaying a webhook changes nothing while a later price change appends a row instead of rewriting history. That is what makes expansion and contraction recoverable months later.

Movements

revenue_daily is recomputed from the timeline. A day's figures are read at its end, 23:59:59.999 UTC: for each subscription, the last state with effective_at before the boundary. Comparing that against the previous day:

So a cancellation, a pause, a move to unpaid and an expiry all read as churn, and a reactivation reads as new. The identity

new + expansion − contraction − churn = change in MRR

holds for every day and currency, and is asserted by the tests.

Payments are attributed to paid_at. Refunds are attributed to the date of the payment they reverse: providers report a refunded amount on the original charge rather than a separate dated event, so that is the only date available.

revenue.daily.recompute takes a fromDay and rebuilds every day from there to today. It always loads the whole timeline, not a slice of it — the state in force on fromDay may have been set months earlier, and reading only the window would reset MRR to zero at its left edge. It is idempotent: the range is deleted and rewritten in one transaction.

Currency policy

An application reports in one currency: the config.currency of its first connector, USD by default. revenue_daily keeps one row per (app, day, currency) in native amounts — nothing is ever converted at write time.

At read time:

• rows already in the reporting currency are used as they are;
• rows in another currency are converted only when a static rate is configured on a connector (config.rates, e.g. {"EUR": 1.08}, expressed in reporting currency per unit);
• anything else is reported separately under "Other currencies are reported separately" and excluded from the totals.

There is no exchange-rate service. A dashboard that silently restates yesterday's revenue is worse than one that shows two currencies.

Person linking

revenue_customers.person_id is set by matching the customer's normalized email (normalizeEmail from @saaspro/shared: trimmed and lower-cased) against persons.email. Emails are stored already normalized, so the join only folds the person side.

persons belongs to the analytics module, so every reference is guarded with to_regclass('public.persons') and the link is skipped when the table does not exist. Linking runs inline on every webhook and sync, and again daily.

Tables

interval is a reserved word in PostgreSQL, so revenue_subscriptions."interval" is quoted in the DDL and in every query.

Deduplication is (connector_id, external_id) everywhere, and (connector_id, external_event_id) for events — per connector rather than globally, so two applications connecting the same provider can never collide.

Stripe payments are keyed on the payment intent when one exists, which is the id both a charge and its invoice expose. That is what makes charge.succeeded and invoice.paid collapse into one row instead of counting the same money twice.

Demo data

pnpm db:seed -- --demo adds one disabled connector (it holds no credentials, so a sync would only fail) and twelve months of synthetic subscriptions and payments for the bootstrap application: trials that convert, plans that upgrade and downgrade, cancellations and the occasional refund. When the analytics demo dataset is present, the billing customers borrow its identified persons' email addresses, so a person profile, its revenue and its lifecycle stage describe the same human. Everything is deterministic — re-seeding on the same day is an upsert, not a second dataset.

Query layer

src/lib/revenue/queries.ts contains the core read paths; acquisition attribution lives in src/lib/revenue/attribution.ts:

type RevenueScope = { appId: string; orgId?: string; timezone?: string };

parseRevenueRange(input?: string, now?: Date): RevenueRange       // '7d'|'30d'|'90d'|'12m'|'custom:YYYY-MM-DD..YYYY-MM-DD'
revenueOverview(scope, range): Promise<RevenueOverview>
subscriptions(scope, { status?, q?, cursor?, limit? }): Promise<{ rows; nextCursor }>
customers(scope, { q?, cursor?, limit? }): Promise<{ rows; nextCursor }>
payments(scope, { status?, cursor?, limit? }): Promise<{ rows; nextCursor }>
personRevenue(scope, personId): Promise<PersonRevenue>
connectors(scope): Promise<ConnectorSummary[]>
maskEmail(email: string | null): string | null

Lists page with an opaque keyset cursor, newest first.

Acquisition attribution

Revenue → Attribution requires both revenue.read and analytics.read, with both modules enabled. Reports cover all application environments because billing customers are application scoped. Filters choose 7/30/90 days or 12 months, first or last touch, source/campaign/landing page, and one settlement currency.

Successful and refunded payments join billing customers to the application's people. First touch uses the person's stored acquisition snapshot only when it predates payment. Last touch uses the latest retained analytics session at or before payment; future visits never rewrite an earlier purchase. Missing links remain visibly unattributed. Customer, person and session joins enforce application and organization ownership.

Gross revenue, refunds and net revenue remain in their original currency. There is no implicit exchange-rate conversion. Unique paying customers and retained period visitors are counted separately: revenue per visitor is a period ratio, not a cohort conversion rate or lifetime value. Refunds reduce the payment's original period using the current recorded refund total. At most 200 groups appear in the table; totals include all groups. Retention limits and unattributed revenue remain visible in the report's methodology.

Additional payment sources

Lemon Squeezy adds live-store orders, renewal/update invoices and refunds. Initial invoices are omitted because the order owns that payment. Signed webhooks and bounded REST backfill verify the source; record-creation dates are labeled as such. This adapter does not import subscription MRR.

The Payment API accepts scoped, versioned server reports for other billing systems. Create a named production source under Connectors, then send snapshots with a Management API token. Event IDs bind immutable payload hashes, refunds are cumulative, and older snapshots cannot regress current payments. Source creation and payment writes are atomic with audit. Sources need no provider credentials and cannot be targeted through ingest keys or provider webhook routes.

Payment API sources are labeled Reported payment API. Application-wide cash and customer lifetime totals include them; provider attribution excludes them. A report is not independent settlement verification or entitlement activation. Neither new payment source fabricates subscription MRR from receipts. Connector changes, vault writes/rotations, deletion and recompute queueing now share their audit transaction. Edits require a passkey verified within ten minutes.

Advertising acquisition

Revenue → Acquisition compares Meta costs with verified payments, with selectable account-local periods, visit lookback, exact/mapped/unmatched evidence, daily charts and compact campaign cards. Connections, token rotation, sync status and explicit campaign mappings are managed in the same workspace. See Meta advertising setup for the matching rules, currency/refund treatment and bounded protocol. context.utm.id is now captured by the browser SDK for canonical campaign IDs.

What the assistant can do here

Ask reads the revenue picture: MRR, ARR, movement, payments, subscriptions and acquisition sources, with the deltas the module already computes. "What moved MRR this month, and which channel paid for it?" is one question across this module and analytics.

Connecting a payment or acquisition source, recording payments into one, or changing a source's mapping are proposals: the assistant shows the source and the mapping it wants to write and waits for you. Removing a mapping is destructive and asks for a typed reason and a fresh passkey. Provider keys live in the vault; no tool returns one.