RateLimiter database storage follow-ups: atomic upsert, schema discriminator, RMW locking, engine docs

[bpamiri]

Follow-up scope deliberately deferred from the database-backed rate-limit enforcement fix (internal framework review 2026-06-09, finding T8). The fix made storage="database" actually enforce on every engine; these items close the remaining (bounded, disclosed) gaps.

1. Dialect-aware atomic upsert for the fixedWindow first-insert race

$dbIncrement() (vendor/wheels/middleware/RateLimiter.cfc) uses UPDATE-first + SELECT MAX + insert-on-miss + one re-read. Concurrent first requests for the same key/window can each insert a row, producing a bounded one-time-per-window undercount (at most N-1 extra requests for N racing nodes, once per window). A real fix is a per-dialect atomic upsert:

• MySQL: INSERT ... ON DUPLICATE KEY UPDATE counter = counter + 1
• PostgreSQL / SQLite: INSERT ... ON CONFLICT (store_key) DO UPDATE SET counter = wheels_rate_limits.counter + 1
• SQL Server: MERGE (with HOLDLOCK) or the UPDATE-then-INSERT retry-on-2627 idiom
• Oracle: MERGE INTO

All of these require a UNIQUE index on store_key, which item 2 currently blocks.

2. Schema redesign with a row-type discriminator

A UNIQUE(store_key) index cannot be added today because the slidingWindow strategy inserts one row per request into the same wheels_rate_limits table (counter rows and event rows share the schema). Proposed: add a row-type discriminator column (e.g. row_type in counter|event|bucket) and scope uniqueness to counter/bucket rows (partial/filtered unique index where supported, or split tables). This is a schema migration for existing wheels_rate_limits tables, so it needs an upgrade path (the table is auto-created, so probe-and-alter or rename-and-recreate are both viable).

3. Locking for DB tokenBucket / slidingWindow read-modify-write

$dbTokenBucket() and $dbSlidingWindow() do unguarded read-modify-write sequences; the in-process cflock only serializes within one node, so multi-node deployments can interleave (token over-grant / event-count skew). Options: SELECT ... FOR UPDATE where supported, optimistic concurrency via a version column, or single-statement atomic forms (UPDATE ... SET tokens = LEAST(...)).

Neither residual race is a sustainable attacker bypass — the undercount is bounded and once-per-window — but they should be closed for strict multi-node accounting.

4. Document engine support for storage="database"

$detectDatabaseType() maps MySQL, PostgreSQL, SQL Server, Oracle, H2, SQLite (and falls back to a generic dialect). The rate-limiting guide (web/sites/guides/src/content/docs/v4-0-0/digging-deeper/rate-limiting.mdx) should state which engines are supported/CI-verified for database storage and what the fallback behavior is.

[wheels-bot]

Wheels Bot — Triage

Classified as framework-design (API/feature design question).
Cross-framework research will follow shortly — Rails, Laravel, Django,
and other modern frameworks will be consulted before any path forward
is proposed.

<!-- wheels-bot:triage:2911 -->
<!-- wheels-bot:triage-class:framework-design -->

[wheels-bot]

Wheels Bot — Cross-Framework Research

Question

How should the RateLimiter middleware close the multi-node DB-storage gaps: (1) dialect-aware atomic upsert for the bounded first-insert race in $dbIncrement, (2) a schema shape that lets UNIQUE(store_key) exist even though sliding-window currently inserts one row per request, (3) cross-node RMW locking for $dbTokenBucket and $dbSlidingWindow, and (4) documented engine support for storage=database.

How other frameworks handle this

Framework	Naming	Surface	Trade-offs	Edge cases
Rails 8 (ActionController::RateLimiting + Solid Cache)	rate_limit to:, within:, by:, with:, store:, name:	Controller DSL over ActiveSupport::Cache	Fixed-window only in core	Solid Cache: SELECT FOR UPDATE + upsert_all(on_duplicate: :update) keyed on key_hash. One row per key
Laravel 11 (Cache\RateLimiter + DatabaseStore)	RateLimiter::for, Limit::perMinute()->by(), throttle:name	Facade + middleware	Fixed-window only; Cache::lock() for distributed mutex	add is insertOrIgnore (catches dup-key on MSSQL); increment wraps SELECT FOR UPDATE + UPDATE in a txn. One row per key
Django / DRF	THROTTLE_RATES = mapping of scope to N/period	Throttle classes delegate to Django cache	DB cache incr() explicitly disclaimed as non-atomic	DatabaseCache._base_set swallows DatabaseError; django-ratelimit refuses the DB backend outright
Phoenix / Hammer	use Hammer, backend: ...; hit(key, scale, limit)	Behaviour, pluggable backend	No SQL backend exists in the org	Algorithm-per-table, no discriminator. Atomicity from dirty_update_counter / ETS / Redis INCR
Spring Boot (Bucket4j JDBC)	ProxyManager<K>, Bucket, Bandwidth, Refill, tryConsume(n)	Dialect-specific ProxyManager per RDBMS	Opaque serialised state blob; no auto-cleanup	PG: advisory-lock OR SELECT FOR UPDATE; others: FOR UPDATE only. First-insert via INSERT ON CONFLICT DO NOTHING. Single table bucket(id PK, state BLOB, expires_at) for every strategy
Symfony (RateLimiter component) — picked because the storage/lock separation mirrors the Wheels shape	RateLimiterFactory, policy, limit, interval, cache_pool, lock_factory	StorageInterface + separate LockFactory	No first-party SQL Storage; LockFactory mandatory for cross-node	Sliding-window stored as aggregate (hitCount + hitCountForLastWindow, weighted rolling average) — NOT one row per request. Schema-collision dissolves

Dominant pattern

Three convergences, one divergence.

• One row per key, every strategy. Bucket4j, Symfony, Solid Cache, Laravel: single row per (key). No surveyed framework stores one row per request. The current Wheels sliding-window (DELETE+INSERT per hit) is an outlier.
• Atomic upsert via the dialect native primitive, not a discriminator column. ON CONFLICT / ON DUPLICATE KEY UPDATE / MERGE / insertOrIgnore — none of them add a row-type column.
• Cross-node RMW = DB lock primitives. PG advisory locks OR SELECT FOR UPDATE. In-process locks are universally declared insufficient.
• Divergence — sliding-window: event-log vs aggregate. Symfony approximates with a weighted rolling average (one row per key); Hammer stores one row per hit. The Wheels issue picks event-log and asks how to add a discriminator. The Symfony design dissolves the question entirely — aggregate sliding-window means UNIQUE(store_key) works for every strategy. Trade-off: aggregate is O(1) state, accurate to within one window boundary; event-log is exact but pays O(N) and needs cross-node-safe pruning.

Recommended path for Wheels

Symfony + Bucket4j hybrid. Refactor sliding-window from event-log to weighted aggregate so every strategy stores one row per store_key. Add UNIQUE(store_key) + dialect-aware atomic upsert for $dbIncrement. Wrap token-bucket and sliding-window RMW in transaction + SELECT ... FOR UPDATE (PG advisory-lock variant optional). This is a smaller schema change than the row-type discriminator in the issue body — the discriminator only existed to work around the per-request-row design no other framework uses.

// vendor/wheels/middleware/RateLimiter.cfc — sketch

private struct function $dbIncrement(required string clientKey, required string storeKey, required numeric resetAt) {
    $ensureTable();
    if (variables.maxRequests == 0) return {allowed: false, remaining: 0, resetAt: arguments.resetAt};
    var expiresAt = DateAdd("s", variables.windowSeconds, Now());
    QueryExecute($atomicIncrementSqlFor(dialect=$detectDatabaseType()), {
        storeKey:  {value: arguments.storeKey, cfsqltype: "cf_sql_varchar"},
        expiresAt: {value: expiresAt,          cfsqltype: "cf_sql_timestamp"}
    });
    var row = QueryExecute("SELECT counter FROM wheels_rate_limits WHERE store_key = :storeKey",
        {storeKey: {value: arguments.storeKey, cfsqltype: "cf_sql_varchar"}});
    var counter = row.counter[1];
    var allowed = counter <= variables.maxRequests;
    return {allowed: allowed, remaining: allowed ? variables.maxRequests - counter : 0, resetAt: arguments.resetAt};
}

private struct function $dbWithRowLock(required string clientKey, required any callback) {
    transaction action="begin" {
        try {
            var row = QueryExecute($selectForUpdateSqlFor(dialect=$detectDatabaseType()),
                {clientKey: {value: arguments.clientKey, cfsqltype: "cf_sql_varchar"}});
            var result = arguments.callback(row);
            transaction action="commit";
            return result;
        } catch (any e) {
            transaction action="rollback";
            return $handleError("DB error", arguments.clientKey);
        }
    }
}
// $dbSlidingWindow and $dbTokenBucket both become $dbWithRowLock(callback=) calls.
// Sliding window math: estimate = prevCounter * (1 - elapsed/window) + counter + 1.

Dialect SQL fragments dispatch on what $detectDatabaseType() already returns: mysql -> ON DUPLICATE KEY UPDATE; postgresql/sqlite -> INSERT ... ON CONFLICT(store_key) DO UPDATE; sqlserver/oracle -> MERGE; h2 -> MERGE INTO ... USING. Lives in vendor/wheels/middleware/RateLimiter.cfc as private $-prefixed helpers; composes with the existing constructor API, $detectDatabaseType(), and $handleError().

Cross-engine notes

Dialect-aware SQL is plain string assembly — no closures, no obj.map(), no struct member calls, no cfheader-style tag wrappers, no arguments scope shadowing. Clean across Lucee 5/6/7, Adobe CF 2018/2021/2023/2025, BoxLang. Verify-in-test: SQL Server MERGE needs a trailing semicolon; SQLite has no SELECT FOR UPDATE so its branch uses BEGIN IMMEDIATE; H2 needs MERGE INTO ... USING ... WHEN MATCHED and only runs on Lucee.

Open questions

• Schema migration path. The table is auto-created. Probe-and-ALTER (add prev_counter, window_started_at, drop per-request rows on first run after upgrade) vs rename-and-recreate (cleaner; discards in-flight state).
• Sliding-window accuracy regression. Aggregate is a behavior change vs current exact event-log. Error bound is at most one window of hits. A slidingWindowMode: exact|approximate knob could preserve the old shape under a flag.
• PG advisory locks as opt-in? Bucket4j ships them separately because the workload trade-off is not universal.
• Engine matrix. Bucket4j publishes 5 (PG/MySQL/MariaDB/MSSQL/Oracle); Solid Cache 3 (SQLite/PG/MySQL). Documenting which Wheels CI engines are supported vs best-effort generic is a separate decision.

Confidence: medium

Downgrade triggers: (a) the +1 framework (Symfony) does sliding-window meaningfully differently from Hammer, and the trade-off (exact vs at-most-one-window approximation) is non-trivial; (b) the schema-migration path on an auto-created table without migration history is not a pre-existing Wheels convention. The atomic-upsert and SELECT FOR UPDATE pieces themselves are high-confidence — every framework converges on them.

Sources

• Rails ActionController::RateLimiting — https://github.com/rails/rails/blob/main/actionpack/lib/action_controller/metal/rate_limiting.rb
• Rails Solid Cache Entries — https://github.com/rails/solid_cache/blob/main/lib/solid_cache/store/entries.rb
• Laravel RateLimiter — https://github.com/laravel/framework/blob/11.x/src/Illuminate/Cache/RateLimiter.php
• Laravel DatabaseStore — https://github.com/laravel/framework/blob/11.x/src/Illuminate/Cache/DatabaseStore.php
• DRF throttling — https://www.django-rest-framework.org/api-guide/throttling/
• Django DatabaseCache — https://github.com/django/django/blob/main/django/core/cache/backends/db.py
• Hammer Mnesia FixWindow — https://github.com/ExHammer/hammer-backend-mnesia/blob/master/lib/hammer/mnesia/fix_window.ex
• Bucket4j JDBC — https://bucket4j.com/8.13.1/toc.html
• Symfony RateLimiter — https://symfony.com/doc/current/rate_limiter.html
• Symfony SlidingWindow — https://github.com/symfony/rate-limiter/blob/7.2/Policy/SlidingWindow.php
• Symfony Lock PdoStore — https://github.com/symfony/lock/blob/7.3/Store/PdoStore.php

[wheels-bot]

Wheels Bot — Fix proposed

Draft PR: #2916

Spec: vendor/wheels/tests/specs/middleware/RateLimiterSpec.cfc — adds one spec under RateLimiter storage=database — dialect-aware schema that exercises the bug (table never created on non-MySQL engines) and flips pre/post fix.
Implementation: vendor/wheels/middleware/RateLimiter.cfc — adds $detectDatabaseType() helper, dispatches per-dialect CREATE TABLE + CREATE INDEX statements from $ensureTable().

Scope is intentionally narrow: this PR addresses item 4 (engine support — the documented engines were silently broken outside MySQL because the swallowed CREATE error fail-closed-blocked every request) and introduces the $detectDatabaseType() helper that items 1, 2, and 3 will build on. The remaining items stay out of this PR — the cross-framework research flagged medium confidence on the schema-migration path (item 2) and the slidingWindow accuracy regression (item 2/3), both of which want human-in-the-loop review before any code lands. While writing the spec I also noted that $dbIncrement()'s INSERT-then-UPDATE pattern relies on a UNIQUE(store_key) the schema doesn't enforce — same root cause as item 1/2, same human-in-the-loop reason for deferring.

Local test execution was unavailable in the bot sandbox (no wheels in PATH; chmod/symlink/mkdir denied); verification rides on the PR's bot-tdd-gate.yml and the full engine matrix.

A human review is required before merge. Reviewer A and Reviewer B will weigh in shortly. Doc updates (MDX guides, .ai/wheels/, CLAUDE.md) are handled separately by bot-update-docs.yml.

[bpamiri]

Wave-2 framework review (2026-06-09) — RateLimiter memory-store follow-ups (companion to this issue's database-store scope).

These were held during wave 2 because vendor/wheels/middleware/RateLimiter.cfc was occupied by PR #2913 (DB enforcement, since merged) and adjoin this issue's design work. Filing here to keep RateLimiter follow-ups consolidated. Line numbers verified against origin/develop @ 8971094.

• MA13 [High, perf] Emergency eviction runs unsynchronized with a full-store closure-comparator sort on the request thread — the only locked eviction is inside $maybeCleanup; the at-capacity path (lines 148-150) calls $evictOldest (514-601) unlocked, taking two full keySet().toArray() snapshots and an ArraySort over up to 100k entries with a CFML closure comparator on the request thread. Under a key-rotation attack — exactly the condition the eviction exists for — concurrent new-key requests each run a full scan+sort, amplifying CPU load. Recommendation: lock the eviction so a single evictor runs; replace the full sort with sampled/approximate LRU eviction.
• MA14 [Medium, perf] $maybeCleanup performs a full O(store-size) scan inline on a request thread every 10 seconds — lines 146, 441-507: one request per 10-second window absorbs a full-store sweep (including an inner timestamp-array loop for sliding-window) before its own rate check runs. Throttled and lock-guarded, hence medium. Recommendation: bound the work per pass (cursor-based, or at most N keys per sweep), or move cleanup off the request path.
• MA1 [Low, quality] init() validates windowSeconds/maxRequests twice — lines 65-77 and 93-107; the second block is an unreachable merge artifact. Recommendation: delete the duplicate.

Source: internal multi-agent framework review 2026-06-09, wave 2 (issues phase). Findings middleware-auth MA1/MA13/MA14.