> ## 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 queries: which rows to drop and which column carries the number you want.

Invoices look simple, but realized revenue, revenue per plan and paying-customer counts each depend on dropping the right rows.

## Revenue: use `amount_paid`, not `total`

`total` is the gross amount before refunds, and it includes invoices that were never paid. Revenue you actually collected is what was paid, minus refunds, on paid invoices:

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

* **Amounts are in major units** (dollars, not cents). Don't divide by 100.
* **`void` invoices carry real-looking totals.** Filter `status = 'paid'`.
* **Paid invoices can be negative** (credit notes). The formula above handles them.
* **Group by `currency`** if you bill in more than one.

```sql theme={null}
SELECT currency,
       sum(amount_paid) - sum(refunded_amount) AS net_revenue,
       sum(total)                              AS gross_total
FROM <namespace>.v2_6_invoices
WHERE env = 'live' AND status = 'paid'
GROUP BY currency;
```

## Revenue per plan

An invoice can cover several plans, so `plan_ids` is a JSON array. To split by plan, sum the line items, which each name one plan. Line items have no currency of their own; take it from the invoice.

```sql theme={null}
SELECT i.currency, li.plan_id, sum(li.amount) AS billed
FROM <namespace>.v2_6_invoice_line_items li
JOIN <namespace>.v2_6_invoices i
  ON i.org_id = li.org_id AND i.env = li.env AND i.invoice_id = li.invoice_id
WHERE i.env = 'live' AND i.status = 'paid'
GROUP BY i.currency, li.plan_id
ORDER BY billed DESC;
```

Line amounts are before discounts, tax and refunds, so on discounted invoices the split adds up to more than `amount_paid`. To filter invoices by plan without line items, parse `plan_ids` (see [Querying → JSON columns](https://docs.useautumn.com/documentation/lakehouse/v2-6/querying.md#json-columns)).

## Paying customers: `active` is not "paying"

Active subscriptions include your free plans and customers in a trial. A paying customer has a live, priced, non-trial base plan. Count distinct customers, because one customer can hold several subscription rows (a main plan and add-ons).

```sql theme={null}
SELECT count(DISTINCT s.internal_customer_id) AS paying_customers
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_plans p
  ON p.org_id = s.org_id AND p.env = s.env AND p.internal_plan_id = s.internal_plan_id
WHERE s.env = 'live'
  AND s.status IN ('active', 'past_due')
  AND s.add_on = false
  AND coalesce(p.price_amount, 0) > 0
  AND coalesce(s.trial_ends_at, 0) <= <now in epoch ms>;   -- not in a trial
```

> [!NOTE]
>
> For a custom plan (`is_custom = true`) the catalog plan's price may not be what the customer pays. Check the customer's own prices through [`v2_6_customer_plan_items`](https://docs.useautumn.com/documentation/lakehouse/v2-6/schema#custom-plans) if custom plans matter for your count.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.