Keep the API responsive under heavy downloads and large score sets #906

Summary

The API stalls under load. A few expensive requests, usually whole-score-set CSV downloads by scripts, tie up the processes that serve everyone else, and the largest score sets can't be served at all. This epic collects the work that keeps the API responsive: bound how long a request can wait, shed load that would stall the site, and make large reads cheap enough that shedding is rarely needed. It is Pillar 3 of the R2026.4 plan.

Glossary

• CSV build cap. Each API process builds at most a couple of large score set CSVs at once and refuses the rest with a 503 and Retry-After. A stopgap until exports are cheap (perf(csv): cap concurrent large CSV builds per process #898; removed in Remove the CSV build cap #902).
• WAF limits. Per-IP rate limits at the load balancer: 1,500 requests per 5 minutes overall, and 100 per 5 minutes on the score set download routes. Over the limit returns 429 (Add API rate limiting #669).
• Lean view. GET /score-sets/{urn}/variants, one small record per variant, which drives the score set page's charts and the variant page on the allele data model branch. RT is that branch; the glossary on Move read enforcement into Postgres row-level security #808 explains the branch names.
• Variant number. The integer after # in a variant URN, which orders a score set's variants. Variant.variant_number in the models.

Decisions

1. Database limits are set per API connection. Statement 30s (45s on the CSV routes), lock 5s, idle-in-transaction 60s, pool wait 10s. The worker, scripts and migrations run without them. Every limit is an environment variable, and 0 disables it. (API handlers block the event loop, and database calls have no timeout #881)
2. Shed load at two layers. The WAF limits cap how often one IP can ask; the CSV build cap caps how much work runs at once. Neither replaces making requests cheaper. (Add API rate limiting #669, perf(csv): cap concurrent large CSV builds per process #898)
3. Stream large responses instead of capping rows. Full downloads stay a supported feature. (Stream /scores & /counts CSV responses (no row cap) #770, Score set page 504s for score sets with about 500k variants #451)
4. Caching and revalidation are decided on measurements. Options and criteria in Choose how whole-set responses are cached and revalidated #916; profiling in Measure where time and bytes go when serving whole score sets #914. Any cache fails open. (Cache public CSV responses (first consumer of the API cache) #771)
5. Bulk download across score sets is the public data dump's job. The API serves people working with one score set at a time.
6. Rate limits are per user in the API, with the WAF as a per-IP backstop. Signed-in users get more. (Rate-limit by user and tier in the API, with the WAF as a per-IP backstop #917)
7. The CSV build cap is temporary. It comes out once exports are cheap or cached and refusals stop appearing in the logs. (Remove the CSV build cap #902)

Order of work

1. Protection, in the 2026.3.0 release: database limits and 503s (API handlers block the event loop, and database calls have no timeout #881's first PR), the variant-number index, the CSV build cap, the enforced WAF download limit (Add API rate limiting #669) and the deployment settings.
2. Make the largest score sets servable by streaming: Score set page 504s for score sets with about 500k variants #451, Stream /scores & /counts CSV responses (no row cap) #770, Stream the public dump's NDJSON files into the archive #192. Doesn't wait on anything below.
3. Move blocking work off the event loop (API handlers block the event loop, and database calls have no timeout #881's second PR). Routers RT doesn't touch can go now; the score set and variant routes wait for RT to merge.
4. Protect the lean view like the CSV routes, with RT (Protect the lean variant view like the CSV download routes #904).
5. Measure (Measure where time and bytes go when serving whole score sets #914), then decide caching and revalidation (Choose how whole-set responses are cached and revalidated #916).
6. Make exports cheaper: Hoist per-row namespace resolution and batch the ClinVar fetch in CSV export #845, paging by variant number (Page score set downloads without walking every skipped row #901), the statistics counts (Statistics count routes time out #900), search (Cap score set search and remove its per-result queries #885), per-row validation (Build bulk response rows without validating each through Pydantic #915).
7. Cache, per Choose how whole-set responses are cached and revalidated #916: mavedb-deployment#32 and Cache public CSV responses (first consumer of the API cache) #771 if Redis is chosen. Per-user rate limits (Rate-limit by user and tier in the API, with the WAF as a per-IP backstop #917). Then remove the CSV build cap (Remove the CSV build cap #902).
8. Worker reliability: ClinVar control refresh can freeze the worker on a row lock #882, Worker outbound calls have no timeout #883, Worker jobs misreport upstream failures #887, Close job lifecycle gaps left after stuck-job detection #888, Re-map score sets with mapped variants that have neither a mapping nor an error #820.

Done when

• A script downloading every score set's CSV doesn't slow down other requests.
• The largest score set's page and CSV downloads complete without a 504.
• No API request holds a database connection past the load balancer's 60s idle timeout.
• The CSV build cap is removed.