Billing a Bulk Endpoint Where Any Row Might Be Free

I already had per-invoice billing working: reserve a credit, do the expensive call, settle or refund. Then I added a bulk endpoint - paste a list, verify them all - and the neat single-item model fell apart, because in a batch some rows cost money and some are free, and you don't know which is which until you've done the work.

This is the metering design behind the bulk GSTIN verification tool on GSTExtract - a solo GST SaaS - and the two bugs I hit building it. The interesting part isn't the happy path. It's that "charge for a batch" quietly becomes "charge for the subset of the batch that turned out to be billable, without overselling and without spending the paid API on rows the user can't pay for."

The setup

A user pastes up to 50 GST numbers. For each, I call a paid third-party registry API and return the business name, status, and address. Each fresh API call costs me real money, so the rule is 1 credit per fresh successful lookup. But three kinds of rows are free:

• Cached - I cache every result for 30 days. A repeat lookup of the same number costs me nothing, so it costs the user nothing.
• Not found - the API has no record. No data delivered, no charge.
• Invalid format - rejected by a regex before any call. Obviously free.

So a "50-item" request might bill for 12. And I can't know the 12 up front - cached vs fresh I can tell, but found vs not-found needs the call. That's the whole problem in one sentence.

Why the single-item model doesn't lift

My invoice path uses the classic reserve → settle → refund: reserve one credit before the vision-model call, settle the real count after, refund on failure. For a batch, the temptation is to do it once for the whole thing: read balance, fetch everyone, charge the found count at the end.

bal = current_balance(db, uid)
to_fetch = fresh[:bal]  # cap to what they can afford
results = await fetch_all(to_fetch)
found = sum(1 for r in results if r["found"])
charge(db, uid, found, allow_partial=True)  # <-- the hole

This oversells. Two concurrent requests both read bal = 10, both fetch 10, both try to charge - the first charges 10, the second charges 0 because allow_partial floors at zero, and that second user just got 10 paid API calls for free. It's the exact race the single-item path was designed to avoid, reintroduced the moment I batched it. Read-then-write is not atomic no matter how you dress it up.

Reserve per item, refund the misses

The fix is to keep the atomic reserve, but do it per GSTIN, and refund the ones that turn out free:

async def one(g):
    key = f"gstinbulk:{base}:{g}"
    # atomic reserve of 1 credit - a single conditional UPDATE, gated on rowcount,
    # so two concurrent reserves for the last credit can't both win
    if not reserve_one(uid, key):
        return g, None, "deferred"  # out of credits - don't call the API
    async with sem:  # bound API concurrency
        r = await asyncio.to_thread(fetch, g)
    if r["found"]:
        return g, r, "charged"  # keep the reserved credit
    refund_one(uid, key)  # not-found / failed = free
    return g, r, "free"

Each item reserves before its own paid call. A reserve failure means the balance is gone - that row is deferred, and crucially no API call is made for it, so you never burn the paid quota on a row nobody paid for. Found rows keep the reserve. Everything else refunds. The billable total is just the count of "charged", and it's correct under concurrency because the atomicity lives in the credit reserve, not in a Python-side balance read.

The reserve itself is a conditional update, not a read-modify-write:

UPDATE credit_lots SET remaining = remaining - 1 WHERE id = :lot AND remaining >= 1

Gate on rowcount: exactly one of two racing reserves for the last credit gets rowcount = 1; the other gets 0 and is told it's out of credits. SQLite serializes writers, so this holds without an explicit lock.

One more subtlety: the refund on a not-found must not feed the abuse counter. My invoice path counts a refunded failure toward a per-user daily "unreadable file" budget - a junk-upload guard. A not-found GSTIN is a perfectly normal result, not abuse, so bulk uses a plain reserve-release, not the failure-counting refund. Same primitive, different accounting.

The bug that actually cost me an hour

My first version ran the reserve inside asyncio.to_thread, alongside the fetch, thinking "offload all the blocking work." The tests immediately threw:

sqlite3.InterfaceError: bad parameter or other API misuse

The reserves were now happening on worker threads, several at once, against one SQLite connection. A SQLite connection isn't safe to share across threads like that. The fetch - an HTTP call - genuinely belongs in a thread. The DB writes do not.

The fix is a one-line conceptual rule: DB stays on the event loop; only the slow HTTP call is offloaded.

if not reserve_one(uid, key):  # sync, on the loop - fast local write
    return g, None, "deferred"
async with sem:
    r = await asyncio.to_thread(fetch, g)  # only this leaves the loop

The reserves are quick local writes; running them on the loop serializes them naturally and keeps them single-threaded, which is exactly what SQLite wants. The concurrency I actually needed was for the network calls, and those are the only thing that fans out now. This mirrors how the invoice path already worked - I'd just forgotten why it worked when I reached for to_thread on the DB.

The free tier, and one export gotcha

Anonymous users get a small daily cap of free fresh lookups (for the SEO/demo value), metered per IP; cached hits don't count, so re-checking the same list is always free. Logged-in users draw from credits as above.

And a non-billing one worth flagging: the results export to an .xlsx, and the values include names pulled from the registry plus, in the export path, client-submitted rows. A cell value starting with =, +, -, or @ is a formula-injection vector when the file is opened. Prefix those with a quote:

if s and s[0] in ("=", "+", "-", "@", "\t", "\r"):
    s = "'" + s

Cheap, and it turns a "download to Excel" feature into one that can't ship a live formula from untrusted input.

The takeaway

"Meter a batch" is three problems wearing one coat: the count is unknown until the work runs, some of the work is free, and it all happens concurrently. Reserve per item and the three collapse into one - each row gates itself, pays for itself, or refunds itself, and the total falls out correct. Keep the DB writes on the loop and let only the network fan out.

If you want to see the endpoint from the outside, it's the bulk GSTIN verification tool; the invoice-extraction engine it sits next to is open source. The billing primitives are the same ones from the earlier write-up on the single-item ledger - this is what happened when I pointed them at a batch.