SaaS Revenue Metrics Calculated Directly From Postgres

MRR, ARR, NRR, churn, and LTV all have clean, textbook definitions, yet most SaaS companies calculate them inconsistently because the numbers pass through exports, spreadsheets, and third-party dashboards before anyone looks at them. A different definition can creep in at each of those handoffs, a snapshot can go stale, or an edge case like a mid-month upgrade or a prorated cancellation can get dropped without anyone noticing. The underlying data usually lives in Postgres, in the subscriptions and billing tables that record what customers actually pay. But the metric itself tends to get calculated somewhere else, one or two steps removed from that source, and that distance is where the trust breaks down.

Subscription revenue doesn't arrive in neat, discrete transactions the way a one-time purchase does. Charges accrue continuously, plans change mid-cycle, and a customer who upgrades on the 14th of the month produces a different number depending on whether a calculation measures by day, by billing period, or by calendar month. A metric computed at the wrong moment, or against the wrong grain, can still look like a normal, plausible figure while quietly diverging from what actually happened in the business.

That gap now matters more than it used to, because AI agents are starting to run these calculations directly, on demand, without a human checking the math before it reaches a board deck or a Slack channel. An agent handed a loosely scoped table and asked for this month's NRR will return an answer with total confidence whether or not the underlying query handled reactivations correctly or excluded failed payments from churn. No one downstream has a reason to question a number that comes back clean and fast. The rest of this piece works through how each of these metrics should actually be calculated, starting from the tables that already hold the answer.

Contents of the Subscription and Billing Tables in Postgres

Nearly every subscription SaaS business already stores the raw material for all five metrics in three kinds of tables. A subscriptions table holds the plan, status, and billing interval for each customer. A subscription_items or line_items table breaks that down further, into per-seat or per-usage amounts. And a billing_events or invoices table logs the actual charges, credits, and refunds as they happen.

A few fields across those tables do most of the work. Each active subscription needs a normalized monthly amount, either stored directly or derivable from an annual figure, along with a start date and a nullable cancellation or end date. A status field, or better, an event log that captures upgrades, downgrades, and pauses, makes it possible to break MRR down into its component movements. A customer or account identifier has to persist across time so that subscriptions can be linked into cohorts, which is the entire basis for a metric like NRR. And the invoice and payment records need to distinguish revenue that was actually recognized from charges that were merely attempted, since that distinction is what separates genuine churn from a failed credit card.

Teams running billing through Stripe or Paddle already have this structure available, either replicated into a warehouse or queryable directly. Stripe's own documentation describes querying subscription and invoice tables through Sigma and replicating billing data through its Data Pipeline product. Paddle's documentation, along with Supabase's Paddle foreign data wrapper, shows the same logical structure under different table names. The schema details vary by processor, but the shape is consistent enough that the SQL patterns that follow need only minor adaptation. Getting these inputs clean and complete is most of the work. Everything from here is a matter of aggregating them correctly.

How MRR is calculated from subscription rows and decomposed into movements

A recurring-revenue metric is the sum of normalized monthly revenue across every active subscription at a given point in time. Annual plans get divided by 12, monthly plans are taken at face value, and both are counted net of any discounts or credits applied at the subscription level. The basic query filters subscriptions down to those with an active status whose billing period covers the target month, sums the monthly_amount column (deriving it from interval and amount where it isn't stored directly), and groups the result by month to build a time series.

SELECT
  date_trunc('month', s.period_start) AS month,
  SUM(
    CASE WHEN s.interval = 'year' THEN s.amount / 12
         ELSE s.amount END
  ) AS mrr
FROM subscriptions s
WHERE s.status = 'active'
GROUP BY 1
ORDER BY 1;

A single MRR figure for a given month tells a founder very little on its own. What matters is breaking that total into four components, each computed by comparing a customer's subscription state this month against last month. New MRR comes from subscriptions that started this month for a customer with no prior subscription row. Expansion MRR comes from existing subscriptions whose monthly amount went up, whether from an upgrade, an added seat, or a higher usage tier. Contraction MRR is the mirror case, where the monthly amount went down. Churned MRR covers subscriptions that were active last month and are canceled or lapsed this month.

New plus Expansion minus Contraction minus Churned equals Net New MRR; that breakdown is far more useful than the net figure alone. Two companies can post the same net new MRR while telling opposite stories: one growing on fresh acquisition, the other barely holding steady while expansion from existing customers offsets heavy churn. ARR needs no separate calculation at all: it's simply MRR multiplied by 12.

A customer who canceled and later came back should get counted as Reactivation MRR, not New MRR, because counting them as new inflates the acquisition signal and masks a retention problem. The fix is a subquery that checks whether the customer_id has any prior subscription row before classifying the current one as new.

How Net Revenue Retention Is Calculated

Net Revenue Retention takes the MRR snapshot further, tracking a single cohort of customers over time. It's calculated by taking all customers active at the start of a measurement window, usually 12 months back, summing what they paid then, and dividing that into what the same cohort pays now, including zero for anyone who has since churned. A result above the break-even point means existing customers are growing revenue on their own, without a single new logo added.

The query logic depends on a cohort join: customer_id has to link across both time slices so that each customer's starting MRR lines up with their current MRR, including the zeros from churned accounts. That's a self-join or a window function run across the subscription history, and it's exactly the kind of operation Postgres handles natively. A pre-built metric inside a billing dashboard typically can't express this logic without exporting the underlying rows first and rebuilding the cohort join somewhere else.

Public SaaS companies often report the same figure to investors under a different name: Dollar-Based Net Retention Rate, or DBNRR. The calculation doesn't change, only the label does, so recognizing the two as equivalent matters when reading an investor deck or a public filing.

An NRR above 120% is an exceptional level, signaling that expansion within the existing base alone is strong enough to fund the business without new customers. Below that break-even point, a company has to acquire new customers faster than it loses existing revenue just to hold its current size, a materially harder position to grow from. That's the number investors interrogate closely, because it answers a question that new-customer growth alone can't: whether the business compounds on what it already has.

How customer churn and revenue churn are calculated

Customer churn and revenue churn answer different questions, and treating them as interchangeable produces a distorted read on the business. A company that loses a long tail of small accounts while keeping its largest customers will show high customer churn alongside low revenue churn, two very different diagnoses depending on which number gets reported.

Customer churn rate counts distinct customer_ids with a cancellation date inside the period and divides that by the count of customers active at the start of the period. Revenue churn, sometimes called Gross Revenue Churn, instead takes Churned MRR straight from the movement table built earlier and divides it by the MRR that existed at the start of the period. One measures how many accounts left. The other measures how much revenue left with them, and the two numbers can move in opposite directions in the same month.

The practical failure mode is a team that watches customer churn rate decline and calls it progress, while its largest accounts are quietly downgrading in the background, a pattern visible only in revenue churn or in the Contraction MRR line from the movement table. Both are sitting in the same subscriptions data already in use for MRR, so there's no reason to track one without the other.

A related distinction deserves separate handling: a subscription that lapses because a card expired is not the same event as a customer who deliberately canceled. Mixing failed payments into the voluntary churn number inflates it and obscures the real retention signal. That classification belongs in the invoices table, where payment attempts and outcomes are recorded.

Deriving LTV and the Burn Multiple from the Same Postgres Tables

Customer Lifetime Value builds directly on the figures already calculated above. It takes three inputs: Average Revenue Per Account (ARPA), gross margin, and customer churn rate, combined as CLTV = (ARPA × Gross Margin %) ÷ Customer Churn Rate. ARPA itself comes from a simple division: MRR for the period divided by the count of active accounts in that same period, a two-line query against the subscriptions table. Gross margin usually comes from revenue and cost records elsewhere in the business, or gets passed in as a known parameter, but churn and ARPA both come straight out of the tables already in use.

The Burn Multiple, net burn divided by net new ARR, measures how efficiently a company turns capital into revenue growth. Burn itself lives outside Postgres, since it depends on cash flow data held in accounting systems. But net new ARR is simply the output of the MRR movement table built earlier, multiplied out to an annual figure, which makes Postgres the natural home for the denominator of that ratio and a natural join point for the numerator if expense data happens to be stored there as well. The calculation isn't fully native to the database, and claiming otherwise would overstate what the subscription tables can do on their own.

Where LTV earns its keep is in segmentation. With a customer_source or plan_id column on the subscriptions table, the same formula can run per cohort, per plan tier, or per acquisition channel instead of producing one blended company-wide figure. That turns LTV from a single abstract number into a signal pointing at which customers, acquired through which channel, are actually worth the cost of acquiring them.

The Risk of Running These Queries Against a Production Database

Postgres holds everything needed to calculate these metrics, but running the aggregation queries directly against the same production database that serves the live application introduces two risks that compound each other. The first is query contention: the second is unrestricted access that violates the basic principle of least privilege.

Analytical queries that scan large subscription history tables hold locks and tie up connection slots from the same pool serving live user traffic. That's a routine failure mode under load, not a hypothetical one, and it raises latency spikes at exactly the moment a business is busy enough for the metrics to matter most.

The access side of the risk grows sharper once AI agents are the ones running these queries. An agent with a direct line into the production database can read, or even write if permissions are misconfigured, tables it has no legitimate business touching. Supabase's own platform governance reflects this concern directly: explicit grants are now required to control which tables are exposed through the auto-generated REST API, with Row Level Security handling, separately, which rows an authorized role can see once it has access.

The safer pattern keeps analytics read-only, scoped to what's needed, isolated from the production path, and running against governed, pre-modeled datasets. That holds whether the consumer asking the question is a human analyst or an autonomous agent. An agent can write a perfectly valid query against ungoverned tables and still return a confidently wrong answer, because nothing in that table tells it what a column means, what grain the data sits at, or what counts as a valid subscription for the specific metric it's been asked to calculate.

How a Governed Data Layer Makes These Metrics Trustworthy

Every metric in this piece is only as trustworthy as the layer that exposes it. Two analysts running the same raw SQL against the same tables can land on different MRR figures depending on how each one happens to handle a mid-month plan change, a prorated invoice, or a reactivated customer, unless those decisions get made once and applied everywhere the metric is used.

The fix is to pre-calculate these metrics into governed, versioned datasets rather than leaving each consumer to re-derive the logic from scratch. MRR, NRR, churn, and LTV, each computed according to one agreed definition, land in a queryable form that a dashboard, a Slack report, or an AI agent can all pull from without re-implementing the underlying rules.

The Model Context Protocol, or MCP, is emerging as the interface standard that lets AI agents retrieve metrics like these safely. An MCP server exposing pre-modeled Parquet datasets, queryable through DuckDB, gives an agent fast, accurate, inexpensive access to exactly the context it needs, without ever opening a direct connection into the production database.

The property that matters most in all of this is consistency: one definition of MRR, one definition of churn, one definition of NRR, applied the same way across every dashboard, every stakeholder, and every agent query that touches it. That consistency is worth more than any dashboard feature or query optimization built on top of it, because it closes off the silent divergence, the different number each handoff quietly introduces, that made these metrics hard to trust.