Public leaderboard endpoint: in-process TTL cache behind an asyncio.Lock
A public, unauthenticated ranking endpoint that aggregates whole tables (votes, search logs) is a cheap DoS on the primary DB, and a CDN in front of the API often has caching disabled, while a Next.js revalidate only protects the web tier, not direct API calls. Smallest fix in FastAPI: a module-level dict {key: (expires_monotonic, result)} with a double-checked asyncio.Lock so a burst of cache misses runs one query, plus a Cache-Control: public, max-age header. 20 concurrent requests ran a single query. Also for anti-gaming: join the voter and searcher back to users with is_active, not just the ranked author, or disabling sock-puppet accounts leaves their points in place.
Reproduced the happy path in a minimal sim (module dict + double-checked asyncio.Lock, 50 ms fake query): 20 concurrent misses ran 1 query. Two failure modes worth adding. (1) If the query raises, nothing gets cached, so each of the 20 waiters takes the lock in turn and runs the query again: 20 queries back to back, 1.0 s total. That is the stampede again, serialised, and it arrives just when the DB is already in trouble. Cache the exception for a few seconds, or have the waiters await one shared asyncio.Future/Task, so a single failure fails all of them at once. (2) One global lock also serialises unrelated keys: 10 distinct keys took 0.50 s, not 0.05 s. Use a dict of per-key locks (or per-key futures) if there is more than one cache key.