Per-Customer Cost Tracking in a Multi-Tenant App

Grouping your request log by tenant takes one line of SQL. Making that number good enough to put on an invoice takes a rollup, an idempotency key and a policy for shared cost — and skipping any of the three produces a figure that is right until the first customer checks it.

Cost and billable usage are different numbers

Conflating them is the root of most per-tenant accounting pain. They answer different questions, they move independently, and they should be separate columns.

Keep both on the row. cost_usd is what you paid; billable_units is what you will charge for, computed by a function you version. When you renegotiate a provider contract, cost drops and billable usage does not — which is exactly the behaviour you want, and is impossible if there is only one column.

The shared-cost problem

Not every dollar belongs to one tenant. Three cases come up in almost every multi-tenant LLM product, and each needs a stated policy rather than whatever the group-by happens to do:

• Cached shared prefixes. If a long system prompt is cached and reused across tenants, the tenant whose request populated the cache paid full price and everyone after paid the cached rate. Billing that literally means one customer subsidises the rest. Either bill everyone the blended rate and absorb the variance, or bill on the uncached list price and treat the cache as margin — both are defensible, silently doing the first by accident is not.
• Shared corpus embedding. Embedding a document set that all tenants search is a platform cost. Tag it tenant_id = null, feature = 'platform', and keep it out of per-tenant numbers entirely.
• Retries and failures. A request that failed and was retried cost you twice and delivered one answer. Cost gets both attempts; billable usage gets one. This is the concrete reason to log one row per attempt rather than one row per logical call.

The rollup, and why it is not a view

Querying the raw request log for a billing period works until the log is large, and then it works slowly and inconsistently — two runs of the same query minutes apart return different numbers because rows keep arriving. Billing wants a number that is stable once closed.

create table llm_usage_daily (
  tenant_id      text        not null,
  day            date        not null,
  feature        text        not null,
  model          text        not null,
  requests       bigint      not null,
  input_tokens   bigint      not null,
  output_tokens  bigint      not null,
  cached_tokens  bigint      not null,
  cost_usd       numeric(14,6) not null,
  billable_units numeric(14,4) not null,
  computed_at    timestamptz not null default now(),
  source_max_id  uuid,          -- high-water mark of rows folded in
  revision       integer     not null default 1,
  primary key (tenant_id, day, feature, model)
);

-- Recompute one day, idempotently. Safe to run any number of times.
insert into llm_usage_daily as u
  (tenant_id, day, feature, model, requests, input_tokens, output_tokens,
   cached_tokens, cost_usd, billable_units)
select tenant_id,
       started_at::date,
       feature,
       served_model,
       count(*) filter (where attempt = 1),          -- logical calls
       sum(input_tokens),
       sum(output_tokens),
       sum(cached_input_tokens),
       sum(cost_usd),                                -- every attempt costs
       sum(billable_units) filter (where attempt = 1)
from llm_request
where environment = 'prod'
  and tenant_id is not null
  and started_at >= $1::date
  and started_at <  $1::date + 1
group by 1, 2, 3, 4
on conflict (tenant_id, day, feature, model) do update
set requests       = excluded.requests,
    input_tokens   = excluded.input_tokens,
    output_tokens  = excluded.output_tokens,
    cached_tokens  = excluded.cached_tokens,
    cost_usd       = excluded.cost_usd,
    billable_units = excluded.billable_units,
    computed_at    = now(),
    revision       = u.revision + 1;

Three properties make this safe to run from a cron job with at-least-once delivery. It is keyed, so a duplicate run overwrites rather than doubles. It is scoped to one day, so a backfill is a loop. And it carries revision, so “this number changed after we showed it to the customer” is detectable rather than mysterious.

Late rows, corrections and idempotency

Rows arrive after the day they belong to. A streamed response that finished at 00:00:03 is timestamped by its start; an async worker flushes its buffer late; a provider webhook restates usage. Two rules handle nearly all of it.

• Recompute a trailing window, not just yesterday. Re-running the last three days every night costs almost nothing and absorbs everything except genuine restatements. Freeze a period only when you invoice it.
• Correct forward, never in place. Once a period is invoiced, a discovered error becomes an adjustment row in the next period with a reason code. Editing a closed period breaks the one thing an invoice needs to be: reproducible from stored data.

If you emit usage into a metering or billing system, use request_id as the idempotency key on the event, not a per-batch id. Retried deliveries are normal, and a metering system that receives the same request_id twice should record it once without either of you having to think about it.

Watch the boundary carefully at midnight and at month end. Two clocks are involved — the request’s start time and its completion — and they can fall on different days for a long streamed response. Pick one and use it everywhere: start time is the better choice, because it is known before the row is written, never changes, and matches how a user would describe when they made the request. Whichever you pick, use the same one in the rollup, the invoice and the dashboard, or three slightly different totals will circulate and nobody will be able to say which is correct.

The query finance actually asks

Not “what did we spend”. The question is which customers cost more than they pay, and it is a join between your usage rollup and your subscription table.

select s.tenant_id,
       s.plan,
       s.mrr_usd,
       round(sum(u.cost_usd), 2)                                 as model_cost,
       round(s.mrr_usd - sum(u.cost_usd), 2)                     as gross_margin,
       round(100 * (1 - sum(u.cost_usd) / nullif(s.mrr_usd, 0)), 1) as margin_pct,
       round(sum(u.cost_usd) / nullif(sum(u.requests), 0), 6)    as cost_per_request
from llm_usage_daily u
join subscription s using (tenant_id)
where u.day >= date_trunc('month', now())::date
group by 1, 2, 3
order by gross_margin asc
limit 25;

Sorting ascending is the whole trick: the first page is the list of accounts that are losing money, which is the only version of this report anyone acts on. Run it weekly, and pair it with the distribution rather than the mean — in most usage-based products a small number of tenants account for a large share of spend, so an average margin can look healthy while a handful of accounts are deeply negative.

There is a companion query worth having next to it: the same calculation for tenants on a flat plan who have stopped using the product. Those accounts look like pure margin and are usually a churn warning rather than a success, which is a reminder that a cost report read on its own tends to reward the wrong things. Pair margin with an engagement number in the same view and the conversation stays honest.

One caution about the denominator. Cost per request is a seductive unit and a misleading one, because a request is not a unit of value. Prefer cost per completed task where you can define one — per resolved ticket, per generated document, per accepted suggestion. That number moves when your prompts get better, and cost per request often does not.