Tracking LLM costs per user: the schema that billing can actually trust

Your model provider tells you what the whole account spent. It does not tell you that one free-tier user generated a third of it. Until cost is attributed per user, every pricing decision — plan limits, free-tier caps, whether that enterprise deal is even profitable — is a guess. Here is the schema I use, and the two decisions in it that took real debugging to learn.

One row per model request

Every request writes exactly one row, at the moment usage is known — from the stream's finish hook, or right after a non-streaming call returns:

export async function recordUsage(args: RecordUsageArgs): Promise<void> {
  await db()
    .insert(usageEvents)
    .values({
      userId: args.userId,
      model: args.model,
      route: args.route, // which feature spent the tokens, e.g. "chat"
      inputTokens: args.usage.inputTokens,
      outputTokens: args.usage.outputTokens,
      cacheReadTokens: args.usage.cacheReadTokens,
      cacheCreationTokens: args.usage.cacheCreationTokens,
      costMicroUsd: Math.round(costUsd(args.model, args.usage) * 1_000_000),
    });
}

The column choices matter more than they look:

• route — a month in, you will want to know which feature burns the money, not just which user. Adding the column later means backfilling history you no longer have.
• The two cache columns — cache reads bill at roughly a tenth of the input rate and writes at 1.25x, so a request with heavy cache reads costs a fraction of its naive token count. Without these columns your numbers drift from the real invoice, and you cannot see whether prompt caching is actually saving you anything.
• userId as plain text, not a foreign key — deliberate. The public demo attributes anonymous traffic as ip:<addr>; a hard FK to the users table would reject those rows. Once every AI route requires auth, tighten it to a real reference.

Integer micro-USD. Never floats.

The cost column is a bigint holding millionths of a dollar: a request that costs $0.004173 is stored as 4173. One request priced as a float looks fine. A month of requests summed as floats accumulates binary rounding error, and now your dashboard, your quota check, and your Stripe invoice each show a slightly different number — three sources of truth is zero sources of truth.

Integers sum exactly. Convert to dollars once, at render time, and let every consumer read the same column.

The rollup that quotas, dashboards, and billing all share

Per-user monthly usage is one aggregate over that table:

const [row] = await db()
  .select({
    requests: sql`count(*)`,
    inputTokens: sql`coalesce(sum(input_tokens), 0)`,
    outputTokens: sql`coalesce(sum(output_tokens), 0)`,
    costMicroUsd: sql`coalesce(sum(cost_micro_usd), 0)`,
  })
  .from(usageEvents)
  .where(and(eq(usageEvents.userId, userId), gte(usageEvents.createdAt, monthStart)));

This single number is load-bearing three ways: the quota middleware compares it against the plan limit and returns 402 when exceeded, the dashboard renders it to the user, and usage-based billing reports it to Stripe. Because all three read the same rows, they cannot disagree — which is the entire point of the design.

An index on (user_id, created_at) keeps the rollup fast; it is the only query pattern the table needs to serve at request time.

Things that bite

• Decide your no-database behavior on purpose. This kit fails open — recordUsage warns and returns when no DATABASE_URL is set, so the demo runs with zero config. Fine for a starter, wrong for production billing: there, failing to record should be loud.
• Month boundaries are UTC. The rollup starts at the first of the month, UTC. Pick a convention and keep it consistent with your billing period, or users get quota resets that disagree with their invoice.
• Meter requests, not just tokens. Plans here cap monthly request volume, because "requests" is a number users can reason about. The token data stays for margin analysis either way.
• IP attribution is a stopgap. Shared offices and mobile carriers pool many humans behind one address. Good enough to cap an anonymous demo, not good enough to bill.

You can watch this pipeline run in the live demo: every message prints its model, token counts, cache activity, and exact cost — the same numbers that land in the table. Shipwright, the Next.js + Claude starter kit this blog documents, ships the whole loop — schema, metering, quotas, and the Stripe wiring — already connected.