Rate limits shared by every server instance

Decision

Rate limits are fixed-window counters in a rate_limits table, updated with a single atomic upsert, so every Vercel function instance shares them. Keys are HMACs of the bucket and the subject (an IP or a username), never the raw values. Limits:

Bucket	Limit
Sign-in, per IP	10 a minute
Failed sign-ins, per account	8 per 15 minutes (counted on failure, checked before trying)
Sign-up, per IP	8 per 10 minutes
Demo sign-in, per IP	30 per 10 minutes
Borrow a code, per IP	12 per 10 minutes
Code lookup, per IP	30 a minute
Username check, per IP	60 a minute
Mailing an invite, per member	6 per 10 minutes
AI log entries, per member	200 per 10 minutes
Sandbox SQL runs, per admin	30 per 10 minutes
Evaluation scoring, per admin	40 per 10 minutes

Context

The 2022 API had no rate limits. The first revival kept counters in process memory, which on Vercel means one set of counters per warm instance: a determined client spread over cold starts saw several times the intended limit.

Options considered

1. In-memory counters (the first revival). Free and fast, but per instance.
2. A hosted Redis (for example Upstash). The usual answer, but another service and account for a demo.
3. Vercel's firewall rules. Good for volume at the edge, but coarse, and it can't see "failed sign-ins for this account".
4. Counters in the existing database (chosen).

Why

One atomic statement gives exact counts under concurrency without a new service, works the same in tests, locally and on Turso, and keeps the limits next to the data they protect. The per-account limit catches password guessing spread across many IPs, which a per-IP limit misses.

What happened

• A test sends 20 concurrent requests at a limit of 10 and exactly 10 are allowed. A property test (500 random request streams) checks the window arithmetic never lets more than the limit through in one window.
• The first refusal in a window is written to the audit log; later ones in the same window aren't, so a flood doesn't flood the log too.
• Costs and weak spots: every counted request is a database write (a round trip to Turso). Fixed windows allow up to twice the limit across a window boundary. The per-account limit lets a stranger lock an account's password form for 15 minutes; the demo buttons skip it so the shared demo can't be locked out.

What I'd change

• Use a sliding window or token bucket to remove the boundary burst.
• Put a coarse limit at the edge (Vercel firewall) in front of these, so volumetric abuse never reaches the database.
• Raise an alert when the per-account limit trips repeatedly for one account.