> ## Documentation Index
> Fetch the complete documentation index at: https://docs.useautumn.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Working with invoices

> Revenue, and paying-customer metrics — the rows to drop and the column that actually carries truth.

<Warning>
  **Available on request** — The Autumn Lakehouse is provisioned per customer. Contact us at [hey@useautumn.com](mailto:hey@useautumn.com) to get access.
</Warning>

The same lesson as [Working with balances](/documentation/lakehouse/balance-semantics) applies across the rest of the schema: **the warehouse stores inputs; the business number is a derivation that drops rows.** Columns are named like a clean OLTP table, but realized revenue, MRR, and paying-customer counts each require knowing which rows to exclude and which column actually carries truth. The traps below all surfaced from running realistic BI queries against live data.

## Revenue: `total` is not realized revenue

`sum(total)` over invoices **overstates** what you collected — typically by low double digits — because `total` is the **gross, pre-refund** amount and includes discounts, proration, and partially-paid invoices. Realized revenue is what cleared, net of refunds:

```text theme={null}
realized_revenue = Σ amount_paid − Σ refunded_amount   over status = 'paid'
```

Three traps around it:

* **Amounts are in the currency's major unit (dollars), not Stripe cents.** Sanity-check one known invoice before scaling — porting Stripe intuition will put you off by 100×.
* **`void` invoices carry real-looking totals** and must be excluded. Filter `status = 'paid'`, not merely non-null.
* **`paid` includes negative totals** (credit notes / refunds modeled as negative invoices) — expected, and correctly handled by the `amount_paid − refunded_amount` form.

```sql theme={null}
SELECT
  sum(amount_paid) - sum(refunded_amount) AS net_revenue,
  sum(total)                              AS gross_total_do_not_use
FROM `<catalog>`.`<namespace>.v2_3_invoices`
WHERE env = 'live' AND status = 'paid';
```

## Paying customers: `status = 'active'` is not "paying"

Active subscriptions include your entire **free** base and everyone **mid-trial**. A naive `count()` of active rows can overstate paying customers by orders of magnitude. A paying customer has an active, **priced**, **non-trial** base subscription — and you must dedup, because one customer holds several subscription rows (versions + add-ons).

```sql theme={null}
SELECT uniqExact(s.internal_customer_id) AS paying_customers
FROM `<catalog>`.`<namespace>.v2_3_subscriptions` AS s
INNER JOIN `<catalog>`.`<namespace>.v2_3_plans` AS p
  ON p.internal_id = s.internal_product_id
WHERE s.env = 'live'
  AND s.status = 'active'
  AND s.add_on = false
  AND coalesce(p.price_amount, 0) > 0
  AND coalesce(s.trial_ends_at, 0) <= toUnixTimestamp(now()) * 1000;  -- not currently in trial
```

<Note>
  Row count ≠ customer count: subscription rows exceed distinct customers because of plan versions, duplicate base rows, and add-ons. Anything customer-level needs `uniqExact(internal_customer_id)`, never `count()`.
</Note>

## Cross-reference: which overage?

Overage (on the balances side) has the same "two figures" hazard as revenue here — **billable** (what you invoice) vs **displayed** (what the dashboard header shows) diverge by undrawn grants. If a report combines revenue and overage, make sure both pages agree on which overage you mean. See [Working with balances → Overage](/documentation/lakehouse/balance-semantics#overage-is-derived-not-stored-and-there-are-two-of-them).

<Note>
  All queries here use `env = 'live'` (**not** `production`) and the `` `<catalog>`.`<namespace>.v2_3_…` `` addressing form. Join on `internal_*` ids — external ids are mutable and versioned. See [Querying](/documentation/lakehouse/querying) for the addressing and `IS NULL` footguns.
</Note>