The core primitive is a reserve that cannot oversubscribe. You hold a budget row per (customer, day) and a separate hold per run, and you claim against both inside one transaction.
CREATE TABLE spend_budget (
scope_kind text NOT NULL, -- 'customer_day', 'run'
scope_id text NOT NULL,
limit_cents bigint NOT NULL,
used_cents bigint NOT NULL DEFAULT 0,
reserved_cents bigint NOT NULL DEFAULT 0,
PRIMARY KEY (scope_kind, scope_id)
);
CREATE TABLE spend_ledger (
idempotency_key text PRIMARY KEY,
scope_id text NOT NULL,
amount_cents bigint NOT NULL,
state text NOT NULL, -- 'reserved', 'settled', 'released'
created_at timestamptz NOT NULL DEFAULT now()
);
The claim is a single statement per scope, and it either matches one row or none:
UPDATE spend_budget
SET reserved_cents = reserved_cents + $amount
WHERE scope_kind = $kind
AND scope_id = $id
AND used_cents + reserved_cents + $amount <= limit_cents
RETURNING limit_cents, used_cents, reserved_cents;
Zero rows returned means the cap would be broken. Run both the customer-day claim and the run claim in the same transaction. If the second returns zero rows, roll back so the first claim never happened. This is the reserve half.
Settlement follows the external call. On success, move the reservation into used; on failure, release it. Both are trivial updates:
-- settle
UPDATE spend_budget SET reserved_cents = reserved_cents - $amt,
used_cents = used_cents + $amt
WHERE scope_kind = $kind AND scope_id = $id;
UPDATE spend_ledger SET state = 'settled' WHERE idempotency_key = $key;
Two rules make this safe under load. First, the ledger insert and the budget update go in the same transaction, so a crash cannot leave a reservation with no record or a record with no reservation. Second, the ledger insert uses the idempotency key as a primary key, so a duplicate request fails the insert before it touches any counter. Stripe's design document is explicit about this: the server correlates the key with the state of the request, and on a retry after a response failure it simply replies with the cached result 5. A practical guide to the same mechanism notes that stores should keep keys for a window long enough to cover typical retries, usually 24 hours to 7 days, and reject a key reused with different request parameters 6.
Worked example: one customer, one day, three overlapping runs
Assume a customer-day cap of 5,000 cents and a per-run cap of 2,000 cents. Three runs start within the same second.
| Step | Scope | Request | Reserved before | Used before | Outcome |
|---|
| 1 | run A | 2,000 | 0 | 0 | Claim ok, limit is 2,000 |
| 2 | customer_day | 2,000 | 0 | 0 | Claim ok, 2,000 of 5,000 |
| 3 | run B | 2,000 | 0 | 0 | Claim ok |
| 4 | customer_day | 2,000 | 2,000 | 0 | Claim ok, 4,000 of 5,000 |
| 5 | run C | 2,000 | 0 | 0 | Claim ok |
| 6 | customer_day | 2,000 | 4,000 | 0 | 0 rows, roll back |
| 7 | run A settles | 1,850 actual | | | Used 1,850, reservation released |
| 8 | run B settles | 2,000 actual | | | Used 3,850 |
| 9 | run C retries | 2,000 | 0 | 3,850 | 0 rows, capped |
Run C fails with a budget error even though its own run counter is empty, because the shared day counter is nearly full. That is the correct outcome: the customer-level ceiling is the promise you made.
Note step 7. Reservations should be conservative, but settlement records the actual charge, which can be lower. That gap is the difference between a hold and a debit and it is why you keep them in separate columns.