research/rate-limiting-an-ai-endpoint-with-d1

Rate-limiting an AI endpoint with a single D1 table

Engineering Note · October 5, 2026 · 10 minute read · AI integration · Cloudflare · Cost control

To rate-limit an AI endpoint without extra infrastructure, keep a counter table in your existing database, increment it with one atomic upsert before you call the model, and refuse the request when the count passes a limit. That bounds your spend per visitor and per day, and it needs a single table.

An endpoint that calls a language model is a way for strangers to spend your money. Before it ships it needs a budget that is enforced before the call, not discovered afterward on an invoice.

What requirements should the budget meet?

We wanted four properties:

  • A cap per visitor per day, so one person or script can’t consume everything.
  • A global daily ceiling, so a distributed abuse pattern still has a limit.
  • Counting before calling, so rejected requests never reach the model.
  • No raw IP addresses stored.

The table

CREATE TABLE ai_usage (
  day   TEXT    NOT NULL,   -- UTC date, YYYY-MM-DD
  key   TEXT    NOT NULL,   -- hashed IP, or '*' for the global counter
  calls INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (day, key)
);

One row per visitor per day, plus one row with the key * for the site-wide total.

How does one statement do the counting?

An upsert with RETURNING increments the counter and reads the new value in a single statement, so two simultaneous requests can’t both see the same old count:

INSERT INTO ai_usage (day, key, calls) VALUES (?, ?, 1)
ON CONFLICT(day, key) DO UPDATE SET calls = calls + 1
RETURNING calls

The handler runs it for the visitor key first. If the returned count exceeds the visitor limit, the request is refused. Only then does it run the same statement for the global key, so a visitor who is already over their limit doesn’t spend global budget.

The script below runs that logic with tiny limits (3 per visitor, 5 global) so the output is short. It uses Python’s sqlite3 module; RETURNING needs SQLite 3.35 or newer, and we ran SQLite 3.45.1. D1 is built on SQLite, so the SQL semantics carry over, but everything measured in this note comes from local SQLite, not from D1.

import sqlite3
print("sqlite", sqlite3.sqlite_version)
BUMP = """INSERT INTO ai_usage (day, key, calls) VALUES (?, ?, 1)
ON CONFLICT(day, key) DO UPDATE SET calls = calls + 1 RETURNING calls"""
VISITOR_LIMIT, GLOBAL_LIMIT = 3, 5          # tiny limits so the output is short

def allow(db, day, visitor):
    try:                                     # fail closed: any meter error means no budget
        v = db.execute(BUMP, (day, visitor)).fetchone()[0]
        if v > VISITOR_LIMIT: return False, f"visitor over limit (calls={v})"
        g = db.execute(BUMP, (day, "*")).fetchone()[0]
        if g > GLOBAL_LIMIT: return False, f"global over limit (calls={g})"
        return True, f"ok (visitor={v}, global={g})"
    except sqlite3.Error as e:
        return False, f"meter error, denied: {e}"

db = sqlite3.connect(":memory:", isolation_level=None)
print("table missing:", allow(db, "2025-01-01", "alice"))
db.execute("""CREATE TABLE ai_usage (day TEXT NOT NULL, key TEXT NOT NULL,
  calls INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (day, key))""")
for who in ["alice"] * 5 + ["bob"] * 3 + ["carol"]:
    print(who.ljust(6), allow(db, "2025-01-01", who))
print(db.execute("SELECT key, calls FROM ai_usage ORDER BY key").fetchall())
sqlite 3.45.1
table missing: (False, 'meter error, denied: no such table: ai_usage')
alice  (True, 'ok (visitor=1, global=1)')
alice  (True, 'ok (visitor=2, global=2)')
alice  (True, 'ok (visitor=3, global=3)')
alice  (False, 'visitor over limit (calls=4)')
alice  (False, 'visitor over limit (calls=5)')
bob    (True, 'ok (visitor=1, global=4)')
bob    (True, 'ok (visitor=2, global=5)')
bob    (False, 'global over limit (calls=6)')
carol  (False, 'global over limit (calls=7)')
[('*', 7), ('alice', 5), ('bob', 3), ('carol', 1)]

Three things to read off this output. Alice’s refused calls (visitor counts 4 and 5) never touched the global row, which stayed at 3 until Bob arrived. The counters record attempts, not successes: once a limit is passed, refused requests keep incrementing the row, so the global value reached 7 against a limit of 5. And the missing-table case came back as a denial, not an exception.

Why not SELECT, then UPDATE?

Because the gap between the read and the write is where limits leak. We ran eight processes against one SQLite file, 500 requests each, comparing the single statement with a read followed by a write:

import sqlite3, os
from multiprocessing import Pool
N_PROC, N_EACH = 8, 500
def conn():
    c = sqlite3.connect("race.db", timeout=30, isolation_level=None)
    c.execute("PRAGMA journal_mode=WAL"); return c
def atomic(_):
    c = conn()
    return [c.execute("""INSERT INTO ai_usage VALUES ('d','k',1)
      ON CONFLICT(day,key) DO UPDATE SET calls = calls + 1 RETURNING calls""").fetchone()[0] for _ in range(N_EACH)]
def naive(_):
    c = conn(); seen = []
    for _ in range(N_EACH):
        row = c.execute("SELECT calls FROM ai_usage WHERE day='d' AND key='k'").fetchone()
        n = (row[0] if row else 0) + 1
        if row: c.execute("UPDATE ai_usage SET calls=? WHERE day='d' AND key='k'", (n,))
        else: c.execute("INSERT OR IGNORE INTO ai_usage VALUES ('d','k',?)", (n,))
        seen.append(n)
    return seen
def run(fn, label):
    for f in ("race.db", "race.db-wal", "race.db-shm"):
        if os.path.exists(f): os.remove(f)
    c = conn()
    c.execute("CREATE TABLE ai_usage (day TEXT NOT NULL, key TEXT NOT NULL, calls INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (day,key))")
    with Pool(N_PROC) as p: flat = [x for r in p.map(fn, range(N_PROC)) for x in r]
    final = c.execute("SELECT calls FROM ai_usage").fetchone()[0]
    print(f"{label} requests={len(flat)} final_counter={final} distinct_values_returned={len(set(flat))}")
if __name__ == "__main__":
    print("sqlite", sqlite3.sqlite_version)
    run(atomic, "single-statement upsert:")
    run(naive,  "SELECT then UPDATE:     ")

Two consecutive runs:

sqlite 3.45.1
single-statement upsert: requests=4000 final_counter=4000 distinct_values_returned=4000
SELECT then UPDATE:      requests=4000 final_counter=500 distinct_values_returned=1499
sqlite 3.45.1
single-statement upsert: requests=4000 final_counter=4000 distinct_values_returned=4000
SELECT then UPDATE:      requests=4000 final_counter=2725 distinct_values_returned=2725

The upsert counted every request exactly once and handed each caller a unique number. The read-then-write version lost most of its updates, and the amount lost changed between runs, which is what a race looks like. A limiter that undercounts by that much is not a limiter. This shows the SQL pattern on SQLite; we make no claim about how D1 schedules concurrent requests.

How do you hash the visitor?

The visitor key is a SHA-256 of the IP address combined with the day and a secret already present in the environment. Rotating daily means a key can’t be used to track someone across days. Including a secret means the table can’t be reversed by hashing the small space of possible IPv4 addresses. Nothing in the table identifies a person, which also keeps the privacy statement short.

The Node script below shows the shape of that key and attacks it. It is our illustration of the idea, not a copy of the production code.

import { createHash } from "node:crypto";
const sha = (s) => createHash("sha256").update(s).digest("hex");
const key = (ip, day, secret) => sha(`${secret}:${day}:${ip}`).slice(0, 16);

const ip = "192.168.44.201";
console.log("node", process.version);
console.log("day 1, secret A:", key(ip, "2025-01-01", "secret-A"));
console.log("day 2, secret A:", key(ip, "2025-01-02", "secret-A"));
console.log("day 1, secret B:", key(ip, "2025-01-01", "secret-B"));

const target1 = sha(ip);                            // unsalted: sha256(ip)
const target2 = key(ip, "2025-01-01", "secret-A");  // keyed
function crack(match) {                             // search all of 192.168.0.0/16
  const t0 = process.hrtime.bigint(); let tries = 0;
  for (let a = 0; a < 256; a++) for (let b = 0; b < 256; b++) {
    tries++; const cand = `192.168.${a}.${b}`;
    if (match(cand)) return { found: cand, tries, ms: Number(process.hrtime.bigint() - t0) / 1e6 };
  }
  return { found: null, tries };
}
const r1 = crack((c) => sha(c) === target1);
console.log("unsalted sha256(ip):", r1.found, "after", r1.tries, "tries,", r1.ms.toFixed(1), "ms");
const r2 = crack((c) => sha(c) === target2);        // attacker lacks the secret
console.log("keyed key, no secret:", r2.found, "after", r2.tries, "tries");

const N = 1_000_000; const t0 = process.hrtime.bigint();
for (let i = 0; i < N; i++) sha(String(i));
const rate = N / (Number(process.hrtime.bigint() - t0) / 1e9);
console.log(`measured: ${Math.round(rate)} hashes/s (single thread, node crypto)`);
console.log(`full IPv4 sweep at that rate: ${(2 ** 32 / rate / 60).toFixed(1)} minutes`);
node v22.22.2
day 1, secret A: 054e5ce789b3e472
day 2, secret A: 5b2093d1f1efa73d
day 1, secret B: 299f59c7098942f8
unsalted sha256(ip): 192.168.44.201 after 11466 tries, 36.8 ms
keyed key, no secret: null after 65536 tries
measured: 888400 hashes/s (single thread, node crypto)
full IPv4 sweep at that rate: 80.6 minutes

The same IP gets a different key each day and under a different secret. The unsalted hash was recovered from a leaked row in 36.8 ms; the keyed one survived all 65,536 guesses. The hash rate and sweep time are for this sandbox on one thread, so treat them as an order of magnitude, not a benchmark.

Why should the meter fail closed?

The most important line in the design is what happens when the counter itself breaks: the migration was never applied, the binding is missing, or the database is briefly unavailable. We treat every one of those as “no budget.” The AI feature declines to run and the product falls back to its non-AI behavior. The table missing line in the output above is that rule at work.

Failing open feels friendlier and is wrong. A broken meter that waves every request through is exactly the condition in which a bill gets large, and it will usually break at the worst moment.

Housekeeping

Counters are only useful for a day. On the first call of each day, which is detectable because the global count returns 1, the handler deletes rows older than a week. There is no scheduled job to forget about, and the table stays small without anyone thinking about it.

The delete is a date comparison on day, the first primary-key column. We sketched it and measured it with 10,000 visitors a day over 30 days; our production SQL may be written differently:

import sqlite3, os, time, datetime
if os.path.exists("house.db"): os.remove("house.db")
db = sqlite3.connect("house.db", isolation_level=None)
db.execute("CREATE TABLE ai_usage (day TEXT NOT NULL, key TEXT NOT NULL, calls INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (day, key))")
VISITORS = 10_000
def add_day(d):
    day = (datetime.date(2025, 1, 1) + datetime.timedelta(days=d)).isoformat()
    db.executemany("INSERT INTO ai_usage VALUES (?,?,1)", ((day, f"{i:016x}") for i in range(VISITORS)))
    db.execute("INSERT INTO ai_usage VALUES (?,?,?)", (day, "*", VISITORS)); return day
def prune(today):
    t0 = time.perf_counter()
    n = db.execute("DELETE FROM ai_usage WHERE day < date(?, '-7 days')", (today,)).rowcount
    return n, (time.perf_counter() - t0) * 1000
print("sqlite", sqlite3.sqlite_version)
db.execute("BEGIN")
for d in range(30): today = add_day(d)
db.execute("COMMIT")
print("rows:", db.execute("SELECT COUNT(*) FROM ai_usage").fetchone()[0], "file bytes:", os.path.getsize("house.db"))
n, ms = prune(today);  print(f"first prune, 22 days of backlog: {n} rows, {ms:.1f} ms")
today = add_day(30)
n, ms = prune(today);  print(f"steady state, one day ages out:  {n} rows, {ms:.1f} ms")
print("rows left:", db.execute("SELECT COUNT(*) FROM ai_usage").fetchone()[0], "oldest day:", db.execute("SELECT MIN(day) FROM ai_usage").fetchone()[0])
sqlite 3.45.1
rows: 300030 file bytes: 23285760
first prune, 22 days of backlog: 220022 rows, 212.9 ms
steady state, one day ages out:  10001 rows, 8.8 ms
rows left: 80008 oldest day: 2025-01-24

Even at a traffic level far above a small site’s, the table holds about eight days of rows and the daily prune is a one-day delete. That is local SQLite timing, and it moves a little from run to run; D1 latency is a different thing and we did not measure it.

Defaults and tuning

Our defaults are 40 calls per visitor per day and 600 site-wide, both overridable through environment variables. A complete interview uses two to three calls, so the visitor limit leaves generous room for retries while still turning a script into a nuisance rather than a cost. The global ceiling is the number to set deliberately: take the most you would be comfortable paying in one day for this feature, divide by the cost of a call, and use that.

What we would add next

This design is intentionally minimal. If the endpoint grows, the obvious extensions are a short burst limit per visitor to blunt scripted floods, and a dashboard query over the table to see usage by day. Neither is needed to be safe, which is the point: the smallest mechanism that bounds the risk is the one that actually gets shipped.

Frequently asked questions

How do you rate limit an AI endpoint without Redis?

Use one table with a primary key of day and key, plus a global row keyed '*'. Run an upsert with RETURNING for the visitor key, refuse if the returned count exceeds the visitor limit, then run it for the global key. This needs only the database you already have, and it runs before the model is called.

Why use INSERT ON CONFLICT RETURNING instead of SELECT then UPDATE?

A separate read and write lets concurrent requests see the same old count. In our SQLite test, eight processes made 4000 requests: the single-statement upsert ended at exactly 4000, while SELECT-then-UPDATE ended at 500 in one run and 2725 in another. Lost updates mean the limit silently stops working.

Should a rate limiter fail open or fail closed?

Fail closed for anything that costs money. If the table is missing, the binding is absent, or the database is unavailable, treat it as no budget and fall back to the non-AI behavior. A meter that waves requests through when it breaks fails at the moment you most need it.

How can you count visitors without storing IP addresses?

Store a SHA-256 of the IP combined with the day and a server-side secret. The day makes the key unlinkable across days, and the secret stops reversal by guessing. In our test, an unsalted SHA-256 of an IP was recovered by searching a /16 block in 36.8 ms, while the keyed version was not.

How do you choose the global daily limit?

Decide the most you would be comfortable paying in one day for the feature, divide by the cost of one model call, and use the result. The per-visitor limit is a nuisance filter; the global limit is the number that bounds your spend. Both should be overridable through environment variables so you can change them without a deploy.

More from Research

← All research