Regulated quantity-limit model

Entity model

Entity-relationship model

A customer holds licences, makes purchases against a category (e.g. a calibre), and may hold a supervised override; the rolling window is evaluated over prior purchases of the same category. Every enforcement produces a compliance-decision record — a sale, a refund, or an override — and an override, when used, is a first-class record of its own.

Definition

Enforcement rule

The window is always applied — there is no code path where an unset or zero window skips the date filter. The prior total is summed over the fixed window, the cap is compared, and a purchase that would exceed it is refused. The control fails closed: if the window cannot be determined, the sale is denied.

public function withinLimit(Customer $c, Category $cat, int $qty): bool
{
    $window = config('limits.window_days');          // validated at boot (below)
    $prior = Purchase::where('customer_id', $c->id)
        ->where('category_id', $cat->id)
        ->where('sold_at', '>=', now()->subDays($window))  // ALWAYS applied
        ->sum('quantity');

    return ($prior + $qty) <= $cat->cap;
}

The read-then-compare is subject to a race: two concurrent purchases can each observe a prior total below the cap and both proceed, exceeding it. The check MUST run under a row lock on the customer + category (SELECT … FOR UPDATE) or in a serialisable transaction, so concurrent purchases are serialised against the cap rather than racing past it.

Definition

Window definition

The window is calendar days, not business days: N × 24 hours back from now, weekends and holidays included. It is a rolling window, recomputed against the current instant at each check, not a fixed calendar period.

The boundary is half-open and evaluated in UTC. A purchase counts if its `sold_at` is strictly greater than `now() − N days` — an event exactly N days old has just aged out — and all timestamps are stored and compared in UTC, so the boundary is unambiguous and unaffected by the customer's locale or a daylight-saving change.

The current transaction is excluded from `prior_total`: the sum covers prior purchases only, and the pending quantity is added in the comparison (`prior_total + qty <= cap`), never folded into the prior sum, so a purchase is never counted against itself.

-- The current purchase is not inserted yet, so it is not in this sum.
SELECT COALESCE(SUM(quantity), 0) AS prior_total
FROM   purchases
WHERE  customer_id = :customer
  AND  category_id = :category
  AND  sold_at > (now() AT TIME ZONE 'UTC') - (:window_days || ' days')::interval;

The window is calendar days in UTC with a half-open boundary (sold_at > now − N days); prior_total sums prior purchases only, and the current quantity is added in the comparison, never into prior_total.

Contract

Batch purchases

A transaction with several line items is evaluated per category: all line items of the same category are aggregated and their combined quantity is checked against that category's cap in a single comparison. A batch MUST NOT be split into per-line checks that each pass under the cap while their sum exceeds it.

One compliance-decision record is written per category evaluated — not per line item, and not one for the whole basket — because the cap and its figures (window, prior total, cap, outcome) are per category. A three-category basket produces three decision records, each self-contained.

The check is atomic across the basket: if any category would exceed its cap, the entire transaction is refused and nothing is sold. The customer does not receive the compliant subset with the offending line silently dropped — a partial fulfilment is a new, explicit transaction, not an automatic fallback.

Line items are aggregated per category and checked as one quantity against the cap; one decision record is written per category; any category over its cap fails the whole transaction — no silent partial fulfilment.

Contract

Configuration contract

The window length is validated at application boot. A missing, zero, or non-integer value is a fatal startup error, not a permissive fallback — the application refuses to run a limit it cannot enforce, the same way it refuses to run without database credentials.

public function boot(): void
{
    $window = config('limits.window_days');
    if (! is_int($window) || $window < 1) {
        throw new \RuntimeException(
            'limits.window_days must be an integer >= 1; '
            .'refusing to start with an unenforceable statutory limit.'
        );
    }
}

Contract

Performance and scalability

The lock is held for the minimum. Acquire the row lock on (customer, category), read the prior total, decide, insert the purchase and its decision, commit. The lock MUST NOT be held across an external call — a licence-verification API, a payment gateway, a receipt printer — do those before acquiring the lock or after committing, never inside the locked window, or one slow dependency serialises every purchase for that customer.

Contention is naturally partitioned: the lock is per customer + category, so different customers never contend and only a single customer's concurrent purchases serialise — exactly the set that must be serialised for correctness. High-throughput deployments partition the purchases table by tenant, and at extreme scale shard by customer, so the windowed sum and its lock stay within one partition.

Lock acquisition fails closed. A bounded `lock_timeout` is set; if the lock cannot be acquired within it, the purchase is denied, not allowed. A contended or stuck lock resolves to a refusal — never a bypass — consistent with the control's fail-closed stance everywhere else.

The lock spans only read-decide-insert-commit and MUST NOT wrap an external call; contention is per customer+category; a lock_timeout that expires fails closed (deny), never open.

Definition

Compliance-decision log

Every enforcement writes one immutable decision record, in the same transaction as the purchase it gates, capturing the window, the prior total, the cap, and the outcome. This is the artefact an auditor requires: proof that the check ran, with the exact figures it ran on, for every transaction.

Contract

Refunds and cancellations

A refund frees capacity for future purchases. The available limit is the cap minus the net quantity acquired within the window, and a refund reduces that net: `available = cap − (prior_total − refunded_in_window)`. This is a deliberate policy choice — a genuine reversal restores the customer's headroom — and it is implemented so the arithmetic can never over-credit.

The credit is netted against in-window purchases only. `refunded_in_window` counts a refund only while its original sale still falls inside the rolling window; a refund of a sale that has already aged out is not subtracted, because that sale is no longer in `prior_total`. This bounds the result — `available` is always between `cap − net` and `cap`, never above `cap`.

Every refund and partial refund is recorded as its own compliance record (`kind = refund`) referencing the original sale, so the log holds the full sequence — sale, refund, and the net the window counted at each step. A partial refund reduces the net by the partial quantity; the original sale and the refund both remain in the log, so the audit trail is complete.

Compliance caveat — deliberate and documented. Because a refund frees capacity, a customer who is refunded but keeps the goods (a money-only refund, not a return to stock) can acquire more than the statutory cap over the window while every record shows them under it. This is an accepted trade-off of this policy. Under a hard statutory cap a deployment MUST mitigate it operationally — for example, processing category-limited refunds only against goods actually returned to inventory, requiring supervised approval for such refunds, and monitoring buy–refund–rebuy patterns — or adopt the stricter “refunds stay counted” policy instead.

-- prior_total        = Σ in-window SALE quantity
-- refunded_in_window = Σ REFUND quantity whose original sale is STILL in-window
-- Netting refunds against the SAME window bounds the result: available <= cap.
SELECT :cap - (:prior_total - :refunded_in_window) AS available;

COMPLIANCE CAVEAT: refunds free capacity, so a money-only refund where the customer keeps the goods can exceed the statutory cap while the ledger shows compliance. This is a deliberate policy choice; under a hard cap a deployment MUST mitigate it — refund only against returned stock, require supervised approval, and monitor buy–refund–rebuy patterns.

Contract

Exemptions and overrides

Some purchases are lawfully exempt (a dealer, a dedicated-status holder, a court order) or require a supervised override. Exemptions and overrides are modelled as their own records — never a silent branch that skips the check — so the audit shows the limit was bypassed deliberately and under whose authority.

An override MUST carry a named approver, a reason, a scope (this customer, this category, this transaction), and an expiry; it is time-bounded, not standing. A permanent exemption is a distinct, separately-approved status, not an override left open. The enforcement path records that an override applied, and its details, in the compliance-decision record, so a bypassed check is as auditable as an enforced one.

Every exemption and override is logged — actor, reason, scope, validity window — in the same append-only compliance log. An expired or missing override MUST fail closed: the limit applies exactly as if no override existed. There is no path where a lapsed override silently continues to permit purchases.

Exemptions and overrides are first-class, logged records with a named approver, reason, scope, and expiry; an override MUST be time-bounded, and an expired or absent override fails closed (the limit applies).