SaaS resource quotas: protect the last slot

Reproduce the last-slot mistake

A hard SaaS resource quota needs an atomic decision where the resource is created. Checking a project count in the interface, then inserting later, leaves the same remaining slot available to another request. Reserve capacity in authoritative storage and commit the resource, counter change and operation receipt together.

The runnable SQLite fixture starts with four projects and a limit of five. Two connections each read four before either creates a project. Both unchecked inserts succeed, leaving six projects. The guarded path creates one project and rejects the other, finishing with five. Eight executed cases also cover replay, changed inputs, rollback, duplicate deletion and tenant scope.

Run python3 experiment.py after downloading the file. It uses the Python standard library, creates temporary databases and saves results.json. Every expected outcome is asserted, then the entire run repeats to check identical output. No service credentials or network connection are needed.

The schedule is controlled. Two SQLite connections take turns; no simultaneous threads, process failures or lock contention are tested. The first case reproduces a stale application decision with real inserts. The remaining cases exercise local database transactions, not a deployed quota service.

Put the condition beside the increment

Use one quota row for the tenant and resource class whose allowance you are enforcing. In this fixture, used counts active projects and limit_value is the current cap. The update itself tests whether capacity remains:

UPDATE quotas
SET used = used + 1
WHERE tenant = ? AND used < limit_value;

Inspect the affected-row count. One changed row means the transaction reserved the slot. Zero means it did not. The request must not proceed with an insert after a zero-row result. A preceding display query may help the customer understand the limit, but it does not replace this condition.

Every fixture tenant has an initialized quota row. In a real service, zero changed rows can also mean that the authority row is missing. Guarantee its creation or distinguish missing authority from a full cap. Both outcomes deny creation, but they need different operational explanations.

Keep the reservation inside the transaction that inserts the project. The fixture begins a SQLite write transaction with BEGIN IMMEDIATE, checks an operation receipt, reserves capacity, inserts the project and records the outcome. It commits only after all those steps succeed. SQLite's transaction documentation explains that immediate transactions begin writing at once and can fail with a busy error when another writer is active.

SQLite permits one simultaneous writer; its isolation documentation explains how writes are serialized. That engine behavior belongs to this example. A database with different isolation rules needs its own review. PostgreSQL documents that, at Read Committed, an update waiting on a changed target row re-evaluates its condition against the updated row. This supports a simple counter-row design, but does not make a separate count query and later insert atomic.

The counter is authoritative only if every path that consumes capacity uses it. Imports, administrative tools and restore operations can bypass the normal create endpoint. Inventory those writers before relying on a number that the main application maintains correctly.

Read the eight outcomes as different contracts

The last-slot comparison establishes the central difference: a stale observation permits two new projects, while a conditional reservation permits one. The other cases protect what happens after that decision.

A quota denial and a database failure have different meanings. In the guarded last-slot case, the transaction ran and found no available capacity. A busy database or unavailable authority has not supplied that decision. Preserve the distinction in internal state and customer-facing responses so retries and support actions can follow the appropriate contract.

The fixture's tenant labels are trusted arguments. They test key scope; they do not authenticate a tenant. Resolve tenant identity from an authorized request before accessing the quota row. The permission-matrix article covers that separate access decision.

Keep the operation receipt in the same commit

A client can lose the response after a project was committed. Charging its retry for another slot would turn a transport failure into a quota failure. Store a stable operation key with the created resource and return the recorded outcome when the same operation returns.

Here the key is (tenant, request_key). The receipt also records the requested name and resulting project ID. Receipt lookup happens before capacity reservation. Therefore a retry still returns its committed project when the tenant is now at the limit. If you reverse those steps, a legitimate replay can be rejected because its first attempt consumed the last slot.

Reusing the key with a different name returns a payload conflict. The API idempotency fixture explains the broader retry contract. In an actual endpoint, define which normalized inputs identify the operation, how long its receipt is kept and what an in-progress competing attempt sees.

Failure after the counter increment is deliberately injected in the fifth case. The exception handler explicitly rolls back. Neither a project nor an operation receipt survives, and the counter returns to four. The duplicate-name case reaches a database uniqueness error and follows the same rollback path. Those assertions check the configured local handler; they do not establish how a different driver handles every database error.

Do not hold this transaction open while sending email or provisioning an external service. If creation requires outside work, represent reserved and active capacity explicitly, then define release and reconciliation for uncertain outcomes. A timed-out provider call may have created the resource. Releasing its reservation because the client stopped waiting can admit work beyond the intended cap.

Release capacity through a recorded state change

The deletion helper changes a project from active to inactive only when it is still active. It decrements the quota counter only if that update changed one row. Calling the helper twice releases one slot, rather than two. This joins the resource transition and counter adjustment in one transaction.

Agree what counts before adding more transitions. An invitation may reserve a seat even before acceptance. Archiving a project may free capacity, while restoring it consumes capacity again. A downgrade may leave current resources readable but prevent additional creation. These are product choices described in the subscription-entitlement brief, not consequences of one SQL predicate.

The fixture assumes the cap never falls below current usage. Its check constraint deliberately rejects that state. A product that permits an over-limit downgrade needs a different schema policy: keep the existing count, deny further increments and identify which actions can reduce it. Do not edit the counter downward merely to make it fit the new plan.

Reconciliation should compare the counter with the precise resource states it represents. If they differ, investigate the mutation paths and repair under a defined authority. Recounting from an older snapshot while creation continues can replace an accurate counter with stale data. The repair itself needs coordination, not only a report of the discrepancy.

Move the fixture to the actual writer boundary

For endpoint acceptance, run two requests against the real database when exactly one slot remains. Keep both requests paused after any display-only count and release them toward the mutation. Require one durable creation, one explicit denial and agreement between the counter and resource rows. This integration check is proposed; the local script does not execute it.

Repeat with a lost response after commit, a failure between reservation and insertion, a duplicate deletion and a tenant that has a different cap. Include administrative creation and restoration. Test transaction retry behavior under real contention, including what the caller sees when storage cannot return an authoritative answer.

The useful completion evidence is the business state after every attempt: one resource for one accepted operation, a corresponding receipt and a counter that matches its defined resource set. Start with the last-slot case, then keep the test beside every newly added writer that can consume the allowance.

Sources

Documentation checked October 11, 2026.

1. SQLite: transaction control
2. SQLite: isolation
3. PostgreSQL: transaction isolation