Usage-based billing in SvelteKit

The table is the design

// The entire write path.
export async function recordUsage(
  db: Db,
  input: { orgId: string; metric: string; quantity: number },
): Promise<void> {
  await db.insert(usageRecords).values({
    id: crypto.randomUUID(),
    orgId: input.orgId,
    metric: input.metric,        // 'ai_tokens', 'api_calls', …
    quantity: input.quantity,
    recordedAtMs: Date.now(),
  });
}

Four columns, and each one is a decision:

• orgId first. Tenancy is not something you add to a usage table later; if the row cannot answer "whose usage is this", no later migration fixes that cleanly.
• metric is a free string. One table serves AI tokens, API calls, gigabytes and everything you invent next quarter. The cost is that a typo creates a metric — which is why listUsageMetrics() exists: your pricing page can read the real metric names instead of a hard-coded list that drifts.
• quantity is a plain number, not an integer and not a decimal type. For token counts that is fine; for money-adjacent quantities, store integers in minor units and say so in the column comment.
• recordedAtMs is wall-clock at write time. A usage row is an append-only fact. Nothing in the service updates or deletes one — corrections are new rows, because an auditable meter has to be able to show what it knew and when.

The read path, and where it stops scaling

// The read path that ships:
export async function getUsageSummary(db: Db, orgId: string, metric: string, sinceMs: number) {
  const rows = await db.select().from(usageRecords);          // every row
  return rows
    .filter((r) => r.orgId === orgId && r.metric === metric && r.recordedAtMs >= sinceMs)
    .reduce((sum, r) => sum + r.quantity, 0);
}

// What it becomes at scale — same signature, one query, one index:
const [{ total }] = await db
  .select({ total: sql<number>`coalesce(sum(${usageRecords.quantity}), 0)` })
  .from(usageRecords)
  .where(and(
    eq(usageRecords.orgId, orgId),
    eq(usageRecords.metric, metric),
    gte(usageRecords.recordedAtMs, sinceMs),
  ));

The shipped getUsageSummary() selects every row in the table and filters in JavaScript. That is honest and correct at starter scale — and it is O(table), not O(rows-for-this-org). The comment on the right is the same function as a single indexed aggregate; adopt it the day your table crosses a few hundred thousand rows, with an index on (orgId, metric, recordedAtMs).

Saying this out loud is the point. A metering path that quietly degrades from milliseconds to seconds shows up first as a slow admin page and then as a timeout on the night you close the month.

Close the period: snapshot, then invoice

// Close a billing period: sum once, persist the number, invoice from it.
const since = startOfBillingPeriod(org);      // your period rule, not ours
const total = await getUsageSummary(db, org.id, 'api_calls', since);

await db.insert(invoiceLines).values({
  id: crypto.randomUUID(),
  orgId: org.id,
  periodStartMs: since,
  metric: 'api_calls',
  quantity: total,
  computedAtMs: Date.now(),
});
// From here the invoice reads invoiceLines, never usageRecords.
// A late row changes next period, not last month's invoice.

The single most expensive mistake in usage billing is invoicing from the live table. Do that and a row that arrives two days late silently changes a number the customer already paid — or worse, a customer who disputes an invoice asks you to reproduce it and the table no longer says what it said on the first of the month.

Persisting the period total at close time makes the invoice reproducible, and makes late rows a next-period adjustment rather than a rewrite of history.

Seats and usage must not argue

Two different seams bill the same organization, and they must not both claim authority over the total. The rule that holds up:

• Seats answer "how many people are in this org right now" — a live read of memberships, enforced at invite time by the seat-limit check, with a concurrency guard so two simultaneous invites cannot both take the last seat.
• Usage answers "how much did this org consume in a period" — an append-only log, summed, snapshotted.
• Neither derives the other. A per-seat allowance that subtracts from metered usage sounds elegant and produces an invoice nobody can reconstruct from either table alone.

Both feed the same billing adapter interface, so the provider (PayPal NCP, Stripe, or a manual invoice) sees one shape regardless of how the number was produced.

Idempotency: the part the service cannot do for you

recordUsage() inserts unconditionally. If the caller is a webhook that gets delivered twice, you record the usage twice — and this is the most common way a metered bill is wrong by a factor of two.

The fix belongs at the call site, because only the caller knows what makes an event unique: carry the provider's event id, and insert into a dedupe table with a unique constraint before recording. The insert either wins (bill it) or violates the constraint (it is a retry, skip it).

Production checklist

• A usage row is an immutable fact with a timestamp: orgId, metric name, quantity, recordedAtMs. Never update one — record another.
• The metric name is a free string, so the same table serves "ai_tokens", "api_calls" and "storage_bytes" without a migration per metric.
• Seat billing and metered billing are separate seams: seats are a count of memberships read at invite time, usage is an append-only log summed over a window. Do not derive one from the other.
• Sum in SQL once the table is large. The shipped getUsageSummary() selects every row and filters in JavaScript — correct, but O(table) per call.
• Bill from a snapshot, not from the live table. Close a period by writing its total somewhere durable, so a late-arriving row cannot retroactively change an invoice.
• Idempotency belongs at the call site: recordUsage() inserts unconditionally, so a retried webhook records the same usage twice unless you carry a dedupe key.

Related guides: Seat billing adapter pattern · API keys and webhook endpoints · Append-only audit log