DEV Community

EllisVance1273
EllisVance1273

Posted on

Prepaid Balance Metering in Postgres — Provider Usage Records or Your Own Counters

#billing #postgres #metering #architecture

Invoice from the platform's usage records, and use your own counters for two other jobs: splitting that invoice across tenants, and firing the alarm before a prepaid balance hits zero. One number can't do both well. The invoice has to survive an argument with a vendor who has their own ledger; the alarm has to run on rows you can query in milliseconds, without waiting for yesterday's export to land.

Source of truth Attribution accuracy Freshness Holds up in a dispute Typical failure
Platform usage records Only the dimensions the platform stamps Minutes to a day Yes, it is the vendor's own ledger No tenant dimension; rows arrive late
Your own counters Exactly your tenant model Milliseconds Only with an append-only audit trail Retries and crashes double-count
Both, reconciled nightly Your model, checked against theirs Fresh for alarms, settled for invoices Yes One more pipeline you own

Row three is the recommendation, and the rest of this is what it costs. Take the system I keep in mind while writing this: a homework grader for a few hundred classrooms in one edtech tenant pool, prepaid, running unattended over weekends. Each graded submission fans out into a model call, a PDF render, and a few megabytes of stored artifacts. The finance question is which of those two numbers ends up on a classroom's line item. The operational question is which one gets to wake somebody up.

Why the two numbers never line up

Because "one unit" is defined twice. Your counter increments when your code decides work happened; the platform's record increments when its billing pipeline decides work happened, and those two moments have different opinions about retries, cache hits, partial output, and rounding. A grading call that streams 80% of a response and then loses the connection is the honest example — your worker may count nothing, because the job goes back on the queue and the retry produces the complete artifact, while the platform reasonably counts the tokens it actually generated on both attempts. Now multiply by a retry storm on a Sunday night. Neither side is lying, and no amount of arguing changes the arithmetic, because the disagreement lives in the definition and not in the data.

Drift is not a defect. It is two ledgers with different edges.

Four sources of drift show up over and over, and they are worth writing into a runbook rather than rediscovering each month-end:

  • Unit definitions: tokens versus characters, stored bytes versus GB-months, a request versus a billable operation.
  • Retries and idempotency: whether a replayed request is one billable event or two.
  • Late arrival: usage rows that land hours after the call, sometimes after your invoice job ran.
  • Period boundaries: the platform closes the month in UTC; your school district's invoice closes in local time, so a whole evening of grading can fall into the wrong period.

That last one is dull and it bites hard. Pick the period boundary once, store it as an explicit timestamp range on the invoice row, and never derive it from date_trunc('month', now()) in a job that might run in a different timezone than the one you tested in.

Should I invoice from platform usage records or my own counters for multi-tenant billing?

Invoice the total from the platform's records; allocate that total across tenants with your own counters. The asymmetry is about who you have to convince. A vendor dispute is settled against the vendor's ledger, so an invoice built on your numbers starts from a weaker position — you are asking them to accept your arithmetic about their service. A tenant dispute is settled against your attribution, and no platform can do that for you unless every tenant's traffic is separable at the source.

Their number is the total. Your number is the split.

Which means your counters have to be reconstructible rather than merely current. A mutable balance_cents column that you decrement on each call is the design that quietly loses a retry and can never explain itself six weeks later, when a school asks why March cost more than February. Append-only usage events with a unique idempotency key, a stored unit price, and an immutable occurrence timestamp let you recompute any invoice from scratch and diff it against the last one. Usage-based billing services — Stripe Billing and OpenMeter among them — all expect events pushed with a customer id and an idempotency key, which tells you something: the attribution decision happens upstream of the billing system, in your code, before anything leaves your process.

Stamp the tenant before the request leaves your process

Attribution accuracy is the axis that decides this, so it deserves the most engineering. Three ways to get a tenant dimension into the platform's own records, in descending order of how much they cost you:

A credential per tenant is the strongest, because the platform's records then carry the tenant identity natively and reconciliation becomes a per-key comparison instead of a per-total one. It also means key sprawl, and key sprawl is where prepaid systems get hurt — a few hundred credentials to store, rotate, and revoke, none of which belong in source control or in a Postgres row you forgot to encrypt. OWASP's secrets management guidance is the boring checklist worth following here: central storage, automated rotation, no secrets in code or CI logs, and an audit trail on access.

A tenant label attached to each request is cheaper, and it works only if the platform actually stamps arbitrary labels into the records it exports. Plenty of services accept metadata on the request, show it in a dashboard, and lack it in the billing export. Check the export, not the dashboard.

A join on request id is the fallback: you store the id the platform returned, they include it in the export, and you reconcile line by line. If the export carries no tenant dimension and no id you can join on, per-tenant numbers stay your word against theirs. That is not a reason to avoid a platform, but it is the thing to verify before you commit a billing model to it.

The watchdog that keeps a prepaid balance alive

The alarm runs on your counters, on purpose. An export that lands once a day cannot tell you that a runaway retry loop is eating the balance at three in the morning.

type UsageEvent = {
  tenantId: string;            // classroom id, stamped before the call leaves this process
  unit: "grading_call" | "pdf_render" | "stored_mb";
  qty: number;                 // integer or NUMERIC, never a float
  providerRequestId: string | null;  // join key for reconciliation, when one comes back
  idempotencyKey: string;      // unique index: a replayed job records once
  occurredAt: Date;
};

// The unique index is the whole trick. A retried grading job is safe to record twice.
async function record(sql: Sql, e: UsageEvent): Promise<void> {
  await sql`
    insert into usage_events
      (tenant_id, unit, qty, provider_request_id, idempotency_key, occurred_at)
    values
      (${e.tenantId}, ${e.unit}, ${e.qty}, ${e.providerRequestId},
       ${e.idempotencyKey}, ${e.occurredAt})
    on conflict (idempotency_key) do nothing`;
}
Enter fullscreen mode Exit fullscreen mode

Runway, not spend, is what you page on. A balance chart tells you what already happened; hours-of-runway tells you whether anyone needs to act before Monday.

// Burn-down from our own counters, because they are current.
const [{ spend24h }] = await sql`
  select coalesce(sum(qty * unit_price_cents), 0) / 100.0 as "spend24h"
  from usage_events
  where occurred_at > now() - interval '24 hours'`;

const hoursOfRunway = spend24h > 0 ? (balanceUsd / spend24h) * 24 : Infinity;
if (hoursOfRunway < 72) await page(`prepaid balance under ${hoursOfRunway.toFixed(0)}h of runway`);

// Nightly, after the platform export lands: compare, don't overwrite.
const drift = Math.abs(oursCents - theirsCents) / Math.max(1, theirsCents);
if (drift > 0.005) {
  await holdInvoicing(periodStart, periodEnd);   // a human looks before anything is sent
}
Enter fullscreen mode Exit fullscreen mode

Two details in there matter more than the shape of the code. The reconciliation job compares and holds; it never rewrites your event log to match the export, because the moment it does you lose the ability to explain the difference. And the drift budget is a policy number, not a law — 0.5% is tight enough for unit-priced calls and probably too tight for storage, where rounding to GB-months makes small differences normal. I am not sure there is a defensible universal threshold; pick one, log every breach with the period and the two totals, and widen it only with the log in front of you.

When your own counters should be the invoice source

Three cases flip the default, and they are common enough that "always invoice from the platform" would be bad advice.

The first is composite pricing. If you sell "graded assignment" at a flat rate and that expands into a model call, a render, and storage across two vendors, no single platform record maps to your SKU. Your counter is the only thing that knows what a unit of your product is.

The second is self-hosted components. Your own Postgres, your own queue, your own workers have no vendor ledger, so there is nothing to reconcile against and the audit burden is entirely yours.

The third is real-time gating. Cutting a tenant off at zero balance cannot wait for a daily export, so the counter decides — and it must be the same counter the invoice allocation uses, or you will refuse work you later bill for.

The catch is what you inherit when your counters become authoritative: append-only storage with a retention policy that outlives your longest dispute window, a documented recompute path, and a per-invoice ledger you can hand to a school district without an apology. Metering platforms are not suitable when your billable unit exists only inside your own domain model, and a credential-per-tenant scheme is not suitable when the number of tenants outruns your ability to rotate secrets safely. Stick with the platform's records for the total whenever the platform can stamp your tenant dimension, because it is one less ledger to defend. Move to your own only when it can't.

References

Top comments (0)