Backend Prompts

---
name: api-rate-limit-policy-simulator
description: Simulates API rate limits before you ship them - token bucket, fixed window, sliding log and sliding counter, per client, endpoint, tier or global - against generated or recorded traffic with retrying clients. Shows who gets 429s and for how long, fixed-window boundary bursts, retry herds, noisy neighbours, abusive clients that still get through and peaks over backend capacity. Includes an offline stdlib Python simulator with --self-test.
---

# API Rate Limit Policy Simulator

You help backend and platform engineers choose rate limits with evidence instead of round numbers. A limit that looks safe on paper ("300 per minute per key") can still let 1.8x that through at a window boundary, throttle a mobile app whose thousands of users share one API key, release a herd of retries at the reset second, and do nothing against a scraper that stays just under it. You describe the policies and the traffic in a small JSON file, replay it, and read the result client by client and policy by policy.

## Files in this skill

- `scripts/rate_sim.py` - simulates token bucket, fixed window, sliding log and sliding counter limiters keyed by client, endpoint, client+endpoint or global, scoped to endpoints and client tiers; generates steady, Poisson or burst traffic (or replays a CSV trace), lets clients retry immediately, with backoff or by honouring Retry-After, and reports 429s, delays, failures, worst-case and observed window counts, peaks vs capacity, findings RL01-RL10 and a sample 429 response (Python 3.8+ standard library only)
- `references/algorithms.md` - how each algorithm behaves, its worst case, memory cost and when to pick it
- `references/policy-design.md` - choosing keys, tiers, numbers, headers and client retry rules; what the finding codes mean
- `templates/rate-limit-review.md` - the review to hand back
- `examples/orders-api.json` - a public orders API with a per-key fixed window, a login limit and a global fixed window; five clients including a shared mobile key, a bulk partner, a scraper and a credential stuffer
- `examples/orders-api-report.md` - the real simulator output on that file
- `examples/orders-api-tuned.json` - the same API after the fixes: per-tier token buckets, a global token bucket sized to capacity

## Workflow

### 1. Collect the inputs
Ask for: the limits as configured today or as proposed (algorithm, key, numbers, which endpoints and which clients), where they are enforced (gateway, CDN, app, Redis), the backend capacity in requests per second it can sustain, and the traffic: the biggest legitimate clients and their shape (steady, bursty batch jobs, many users behind one key or one NAT IP), how each one retries, and any abuse seen in logs. Real access logs beat guesses: export `t,client,endpoint` for a busy 5-15 minutes and pass it with `--trace`.

### 2. Run the simulator
```
python3 scripts/rate_sim.py examples/orders-api.json
python3 scripts/rate_sim.py examples/orders-api-tuned.json
python3 scripts/rate_sim.py policies.json --trace access-sample.csv
python3 scripts/rate_sim.py policies.json --json
python3 scripts/rate_sim.py --self-test
```
Exit 0 = no high findings, 1 = high findings (legitimate clients throttled or admitted traffic over capacity), 2 = bad input. Without a shell, reason with `references/algorithms.md`: per key, compare the client's peak per window with the limit, the worst case (2x limit for fixed windows, burst + rate for buckets) with capacity, and think through what each client does after a 429.

### 3. Read the result
- Legit clients first (RL01): any failed or delayed requests, and how long they waited. One key shared by an app's users needs its own tier or a per-user key.
- Capacity (RL03): the peak admitted second vs what the backend sustains. A global limit is the only thing that protects capacity; per-key limits multiply by the number of keys.
- Shape (RL04, RL10): fixed windows let bursts through at boundaries and hand out a whole window at once; retries without jitter all come back in the same second.
- Abuse (RL02): a scraper under the per-key limit is not stopped by limits; it needs detection, auth or a per-endpoint limit.
- Retries (RL05): attempts per logical request above 1.5 means the clients amplify load when throttled.

### 4. Tune and re-run
Change one thing at a time and re-run with the same traffic: switch fixed windows to token buckets or sliding counters, add tiers with `clients` / `exclude_clients`, size the global bucket to capacity (rate = sustainable rps, burst = what the backend absorbs for a second), add endpoint limits for login and expensive calls, and agree pacing with batch partners. Use `references/policy-design.md` for the numbers.

### 5. Deliver
Fill `templates/rate-limit-review.md`: verdict, a before/after table per client, the policy changes as config, the 429 response and headers, client guidance (Retry-After, backoff with jitter, max retries) and what to monitor after rollout.

## Example result
On `examples/orders-api.json` the shared `mobile-app` key hits the 300/minute per-key window: 1,042 of its 1,839 requests succeed only after waiting up to 60 s (RL01, high). Honouring Retry-After without jitter releases 289 retries in second 240, so admitted traffic peaks at 313 req/s against a capacity of 80 (RL03, RL10). The per-key fixed window lets 544 requests through in one rolling minute (1.8x, RL04), and the scraper still gets 45% of its calls through while its immediate retries push it to 2.67 attempts per request. The login limit stops the credential stuffer (40 of 480). In `examples/orders-api-tuned.json` (tier token buckets, global bucket 50 req/s + 30 burst, partner pacing at 2 req/s) legit clients see no 429s, the peak is 27 req/s and only the scraper findings remain.

## Rules

- Simulate with the traffic you actually have (or a trace), not an average; bursts and retries are where limits fail.
- Never call a limit safe for capacity unless a global limit bounds the total.
- Treat delayed requests of legitimate clients as harm, not as success.
- Always return 429 with Retry-After, add jitter, and tell clients how to back off.
- Rate limits are not abuse protection on their own; say when authentication, bot detection or WAF rules are needed.
- Results are a model: confirm with gateway metrics after rollout and keep the limits in config, not code.
FILE:references/algorithms.md
# Rate limit algorithms

| Algorithm | Admits | Worst case in one window | Memory per key | Good for |
|---|---|---|---|---|
| Token bucket (GCRA) | while tokens remain; refills at `rate`/s up to `burst` | burst + rate x window | 2 numbers | most APIs: smooth average, bounded bursts |
| Fixed window | `limit` per calendar window, counter resets | 2 x limit (end of one window + start of next) | 1 counter | quotas and billing ("10,000 per day"), not capacity |
| Sliding log | at most `limit` in any rolling window | limit (exact) | one timestamp per request | small limits: login, OTP, password reset |
| Sliding counter | previous window weighted by overlap + current | about limit (assumes even spread) | 2 counters | large limits where exactness is not needed |

## Token bucket
- `rate` is the long-run average; `burst` is how many calls can arrive at once after a quiet period.
- `limit` + `window_s` without a burst means rate = limit/window and burst = limit: a client can spend the whole minute in one second. Set the burst explicitly.
- Retry-After = (cost - tokens) / rate: usually well under a second, so retries spread out by themselves.
- GCRA (used by many gateways) stores one timestamp and gives the same admit/reject decisions.

## Fixed window
- Cheap (`INCR` + `EXPIRE` in Redis) and easy to explain, so it is everywhere.
- A client can send `limit` in the last second of a window and `limit` again in the first second of the next.
- Every throttled client gets Retry-After = time to the reset, so they all come back in the same second (retry herd).
- Use it for quotas that match business periods; put a token bucket in front of it for capacity.

## Sliding log
- Exact, but stores every admitted timestamp for the window: 10,000/hour per key = 10,000 entries per key.
- Retry-After = oldest relevant entry + window - now: can be long; tell the client.

## Sliding counter
- Estimate = previous count x (1 - elapsed/window) + current count.
- Error comes from uneven traffic: a burst at the end of the previous window is under-counted later (in the simulator's self-test, 10 requests at the very end of one window plus an even trickle later admit 16 in one rolling minute for a limit of 10).
- Good default for large per-key limits behind a token bucket.

## Concurrency limits (not simulated)
Limits on in-flight requests protect slow endpoints better than rate limits; combine with a rate limit for fairness.
FILE:references/policy-design.md
# Designing rate limit policies

## Pick the key
- **API key / token** for authenticated traffic. Watch for keys shared by many users (mobile apps, SDKs embedded in customer sites): give them a tier or move to per-user keys.
- **User or account id** behind the key when the API knows it; fairer than the key.
- **IP** only for anonymous endpoints; offices, schools, carriers and VPNs put thousands of users behind one IP (CGNAT). Use generous IP limits and stricter account limits.
- **Endpoint** for expensive calls (search, export, bulk) and sensitive ones (login, OTP, password reset).
- **Global** to protect capacity. Without it, per-key limits x number of keys is the real ceiling.

## Pick the numbers
1. Sustained rate per key: p99 of a normal busy client's requests per second x 2-3 headroom.
2. Burst: the largest legitimate batch (page load fan-out, sync job) that must not be throttled.
3. Global: rate = what the backend sustains at acceptable latency (load test), burst = what it absorbs for about a second.
4. Login/OTP: 5-10 per minute per account and per IP, sliding log, plus lockout or CAPTCHA after repeated failures.
5. Quotas (per day/month) are a product decision; keep them separate from capacity limits.

## Tiers
Scope policies with `clients` and `exclude_clients` in the simulator; in production map tiers to plans or key metadata (free, paid, partner, internal). Internal services should usually bypass public limits but stay under the global one.

## Responses and headers
- 429 Too Many Requests, `Retry-After` in seconds, a problem+json body naming the limit.
- Advertise the policy: the IETF httpapi ratelimit-headers draft defines `RateLimit-Policy` and `RateLimit` fields (field syntax changed between drafts); many gateways still send `X-RateLimit-Limit`, `-Remaining`, `-Reset`. Pick one and document it.
- Add 0-20% random jitter to Retry-After for fixed windows so throttled clients do not return together.
- Use 503 + Retry-After for overload (global limit), 429 for per-client limits, so clients can tell them apart.

## Client rules to publish
Honour Retry-After; otherwise exponential backoff with full jitter (1, 2, 4 s ...), at most 3-5 retries, never retry non-idempotent calls without an idempotency key; pace batch jobs instead of bursting.

## Finding codes (rate_sim.py)
| Code | Meaning |
|---|---|
| RL01 | legit client failed or delayed (high above 1% of its requests) |
| RL02 | abusive client still gets more than 25% through |
| RL03 | admitted peak over capacity, or no global limit to bound it |
| RL04 | fixed window let more than the limit through in a rolling window |
| RL05 | client retries amplify attempts 1.5x or more |
| RL06 | noisy neighbour holds most of a global limit while legit clients are rejected |
| RL07 | sliding log with a very large limit (memory) |
| RL08 | reminder: 429 + Retry-After and documented limits |
| RL09 | policy never triggers and is 10x above the busiest key |
| RL10 | retry herd: most of the peak second are retries released together |
FILE:templates/rate-limit-review.md
# Rate limit review: <service>

**Scope:** <endpoints / gateway>   **Capacity:** <80 req/s sustained>   **Traffic used:** <generated from ... / trace of 10 min on ...>
**Verdict:** <ship / ship with changes / do not ship>

## What happens today
| Client | Kind | Requests | 429s | OK after retry | Failed | Max wait | Main policy |
|---|---|---|---|---|---|---|---|
| <mobile-app> | <legit> | <1,839> | <1,081> | <1,042> | <0> | <60 s> | <per-key minute> |

Peak admitted: <313 req/s in second 240> vs capacity <80>.

## Findings
1. <HIGH RL01 - shared mobile key throttled; 57% of requests delayed up to 60 s>
2. <HIGH RL03 / MEDIUM RL10 - retry herd at window reset, 4x capacity>
3. <MEDIUM RL04 - fixed window 1.8x at boundary>
4. <MEDIUM RL02 - scraper under the per-key limit>

## Proposed policies
```json
<policies block from the tuned config>
```

## After the change (same traffic)
| Client | 429s before | 429s after | Failed before | Failed after |
|---|---|---|---|---|
| <...> | | | | |

Peak admitted after: <27 req/s>.

## Response contract
- 429 + Retry-After (+ jitter) for per-client limits; 503 + Retry-After for global overload
- Headers: <RateLimit-Policy / RateLimit or X-RateLimit-*>
- Client guidance: <honour Retry-After, backoff with jitter, max 3 retries, pace batch jobs>

## Outside rate limits
<bot detection / auth / WAF rule for the scraper; lockout or CAPTCHA for login>

## Monitor after rollout
<429 rate per client and policy, top throttled keys, p99 latency, admitted rps vs capacity; review in 2 weeks>
FILE:examples/orders-api.json
{
  "service": "Orders API (public v2)",
  "duration_s": 300,
  "capacity_rps": 80,
  "seed": 7,
  "policies": [
    {"name": "per-key minute", "algorithm": "fixed_window", "key": "client", "limit": 300, "window_s": 60},
    {"name": "login", "algorithm": "sliding_log", "key": "client", "limit": 10, "window_s": 60,
     "endpoints": ["/v2/login"]},
    {"name": "global", "algorithm": "fixed_window", "key": "global", "limit": 3600, "window_s": 60}
  ],
  "clients": [
    {"name": "mobile-app", "kind": "legit", "pattern": "poisson", "rps": 6, "endpoint": "/v2/orders",
     "retry": "honor"},
    {"name": "partner-erp", "kind": "legit", "pattern": "burst", "burst": 120, "every_s": 60, "start": 58,
     "endpoint": "/v2/orders/bulk", "retry": "backoff"},
    {"name": "checkout-web", "kind": "legit", "pattern": "poisson", "rps": 3, "endpoint": "/v2/checkout",
     "retry": "honor"},
    {"name": "price-scraper", "kind": "abusive", "pattern": "steady", "rps": 12, "start": 20,
     "endpoint": "/v2/products", "retry": "immediate"},
    {"name": "cred-stuffer", "kind": "abusive", "pattern": "steady", "rps": 2, "start": 60,
     "endpoint": "/v2/login"}
  ]
}
FILE:examples/orders-api-report.md
# Rate limit simulation: Orders API (public v2)

Simulated 300s, 7227 logical requests, 4930 admitted, peak 313 req/s admitted (second 240) vs capacity 80.

**Verdict: 2 high finding(s) - fix before shipping these limits**

## Clients

| Client | Kind | Requests | Attempts | Allowed | 429s | OK after retry | Failed | Failed % | Max Retry-After | Top policy |
|---|---|---|---|---|---|---|---|---|---|---|
| price-scraper | abusive | 3360 | 8961 | 1503 | 7458 | 15 | 1857 | 55.3% | 35.2s | per-key minute |
| mobile-app | legit | 1839 | 2920 | 1839 | 1081 | 1042 | 0 | 0.0% | 59.9s | per-key minute |
| checkout-web | legit | 948 | 948 | 948 | 0 | 0 | 0 | 0.0% | 0s | - |
| partner-erp | legit | 600 | 600 | 600 | 0 | 0 | 0 | 0.0% | 0s | - |
| cred-stuffer | abusive | 480 | 480 | 40 | 440 | 0 | 440 | 91.7% | 55s | login |

## Policies

| Policy | Algorithm | Key | Scope | Sustained | Worst case / window | Busiest key / window | Checks | Rejects |
|---|---|---|---|---|---|---|---|---|
| per-key minute | fixed_window | client | all | 5 req/s | 600 per 60s | 544 | 13909 | 8539 |
| login | sliding_log | client | /v2/login | 0.167 req/s | 10 per 60s | 10 | 480 | 440 |
| global | fixed_window | global | all | 60 req/s | 7200 per 60s | 1235 | 13909 | 0 |

## Findings

- **HIGH RL01** Legit client mobile-app is throttled on 56.7% of its requests (0 failed, 1042 only after retrying, waits up to 59.9s), mostly by policy 'per-key minute'; raise that limit or give this client its own tier.
- **HIGH RL03** Admitted traffic peaks at 313 req/s (second 240) over the backend capacity of 80 req/s; add or lower a global limit.
- **MEDIUM RL02** Abusive client price-scraper still gets 45% of its requests through (1503); tighten the per-client limit or add a stricter policy for its pattern.
- **MEDIUM RL04** Fixed window 'per-key minute' let 544 requests through in one rolling 60s window (limit 300, 1.8x) across a window boundary; use sliding_counter or a token bucket if the limit protects capacity.
- **MEDIUM RL05** Client price-scraper sends 2.67x attempts per logical request because it retries throttled calls; retries count against the limit and add load - require Retry-After or backoff with jitter.
- **MEDIUM RL05** Client mobile-app sends 1.59x attempts per logical request because it retries throttled calls; retries count against the limit and add load - require Retry-After or backoff with jitter.
- **MEDIUM RL10** Retry herd: 289 of the 313 requests admitted in second 240 are retries released by the same Retry-After / window reset; add jitter to Retry-After (or client backoff) and prefer a token bucket or sliding window so capacity is not handed out all at once.
- **INFO RL08** Return 429 with Retry-After on every rejection (see the sample headers) and document the limits so clients can pace themselves.

## Sample 429 (first rejection, t=45.0s, price-scraper /v2/products)

```
HTTP/1.1 429 Too Many Requests
Retry-After: 15
RateLimit-Policy: "per-key minute";q=300;w=60
RateLimit: "per-key minute";r=0;t=15
Content-Type: application/problem+json
```
Header names follow the IETF httpapi ratelimit-headers draft; older gateways use X-RateLimit-Limit / -Remaining / -Reset instead.
FILE:examples/orders-api-tuned.json
{
  "service": "Orders API (public v2, tuned)",
  "notes": "Same traffic as orders-api.json except partner-erp, which agreed to pace its bulk sync at 2 req/s instead of 120-request bursts. Token buckets per tier, a global token bucket sized to capacity.",
  "duration_s": 300,
  "capacity_rps": 80,
  "seed": 7,
  "policies": [
    {"name": "default key", "algorithm": "token_bucket", "key": "client", "rate": 4, "burst": 40,
     "exclude_clients": ["mobile-app", "partner-erp"]},
    {"name": "mobile tier", "algorithm": "token_bucket", "key": "client", "rate": 15, "burst": 60,
     "clients": ["mobile-app"]},
    {"name": "partner tier", "algorithm": "token_bucket", "key": "client", "rate": 3, "burst": 20,
     "clients": ["partner-erp"]},
    {"name": "login", "algorithm": "sliding_log", "key": "client", "limit": 10, "window_s": 60,
     "endpoints": ["/v2/login"]},
    {"name": "global", "algorithm": "token_bucket", "key": "global", "rate": 50, "burst": 30}
  ],
  "clients": [
    {"name": "mobile-app", "kind": "legit", "pattern": "poisson", "rps": 6, "endpoint": "/v2/orders",
     "retry": "honor"},
    {"name": "partner-erp", "kind": "legit", "pattern": "steady", "rps": 2, "start": 58,
     "endpoint": "/v2/orders/bulk", "retry": "backoff"},
    {"name": "checkout-web", "kind": "legit", "pattern": "poisson", "rps": 3, "endpoint": "/v2/checkout",
     "retry": "honor"},
    {"name": "price-scraper", "kind": "abusive", "pattern": "steady", "rps": 12, "start": 20,
     "endpoint": "/v2/products", "retry": "immediate"},
    {"name": "cred-stuffer", "kind": "abusive", "pattern": "steady", "rps": 2, "start": 60,
     "endpoint": "/v2/login"}
  ]
}
FILE:scripts/rate_sim.py
#!/usr/bin/env python3
"""API rate limit policy simulator (Python 3.8+ standard library only).

Replays generated or recorded traffic against one or more rate limit policies and shows who
gets throttled, why, for how long, and whether the policies protect the backend without
hurting legitimate clients.

Algorithms (per policy):
  token_bucket     rate (tokens/s) + burst (bucket size); or limit + window_s (rate = limit/window_s,
                   burst defaults to limit). GCRA behaves the same for admit/reject.
  fixed_window     limit requests per window_s, counter resets on window boundaries
                   (allows up to 2 x limit across a boundary)
  sliding_log      exact: at most limit requests in any window_s (stores one timestamp per request)
  sliding_counter  approximation: previous window weighted by overlap + current window (Cloudflare /
                   Redis style, about limit per rolling window, a few % error)

Config JSON:
{
  "service": "Orders API", "duration_s": 300, "capacity_rps": 60, "seed": 7,
  "policies": [
    {"name": "per-client burst", "algorithm": "token_bucket", "key": "client", "rate": 5, "burst": 20},
    {"name": "per-client minute", "algorithm": "fixed_window", "key": "client", "limit": 200, "window_s": 60},
    {"name": "login", "algorithm": "sliding_log", "key": "client", "limit": 5, "window_s": 60,
     "endpoints": ["/login"]},
    {"name": "global", "algorithm": "sliding_counter", "key": "global", "limit": 3000, "window_s": 60}
  ],
  "clients": [
    {"name": "mobile-app", "kind": "legit", "pattern": "steady", "rps": 2, "endpoint": "/orders"},
    {"name": "nightly-sync", "kind": "legit", "pattern": "burst", "burst": 40, "every_s": 60, "start": 5},
    {"name": "scraper", "kind": "abusive", "pattern": "poisson", "rps": 25, "retry": "immediate"}
  ]
}
  key        client | endpoint | client+endpoint | global
  endpoints  optional list: the policy applies only to these endpoints (prefix match)
  clients / exclude_clients   optional lists: apply only to / skip these clients (tiers, allow lists)
  pattern    steady (rps) | poisson (rps, seeded) | burst (burst requests every every_s seconds)
  start, duration   seconds (default 0 and duration_s); cost = tokens per request (default 1)
  retry      none (default) | immediate (0.1 s later) | backoff (1, 2, 4 s) | honor (Retry-After);
             max_retries default 3
  kind       legit | abusive | unknown - used to judge false positives and leakage

Recorded traffic instead of generated: --trace trace.csv with columns t,client[,endpoint][,cost]
(t in seconds from start). Clients in the config then only add kind/retry information.

Usage:
  python3 rate_sim.py config.json [--trace trace.csv] [--json]
  python3 rate_sim.py --self-test
Exit codes: 0 = no high findings, 1 = high findings (legit clients throttled or backend over
capacity), 2 = bad input.
"""
import argparse
import csv
import heapq
import json
import math
import random
import sys
from collections import defaultdict, deque

ALGOS = ("token_bucket", "fixed_window", "sliding_log", "sliding_counter")
KEYS = ("client", "endpoint", "client+endpoint", "global")


class InputError(ValueError):
    pass


# ----------------------------------------------------------------------------- limiters
class TokenBucket:
    def __init__(self, rate, burst):
        self.rate, self.burst = float(rate), float(burst)
        self.state = {}

    def check(self, key, t, cost):
        tokens, last = self.state.get(key, (self.burst, t))
        tokens = min(self.burst, tokens + (t - last) * self.rate)
        self.state[key] = (tokens, t)
        if tokens + 1e-9 >= cost:
            return True, 0.0, tokens
        return False, (cost - tokens) / self.rate, tokens

    def consume(self, key, t, cost):
        tokens, last = self.state[key]
        self.state[key] = (tokens - cost, t)


class FixedWindow:
    def __init__(self, limit, window):
        self.limit, self.window = float(limit), float(window)
        self.state = {}

    def check(self, key, t, cost):
        w = math.floor(t / self.window)
        start, count = self.state.get(key, (w, 0.0))
        if start != w:
            start, count = w, 0.0
        self.state[key] = (start, count)
        if count + cost <= self.limit + 1e-9:
            return True, 0.0, self.limit - count
        return False, (w + 1) * self.window - t, self.limit - count

    def consume(self, key, t, cost):
        start, count = self.state[key]
        self.state[key] = (start, count + cost)


class SlidingLog:
    def __init__(self, limit, window):
        self.limit, self.window = float(limit), float(window)
        self.state = defaultdict(deque)

    def check(self, key, t, cost):
        log = self.state[key]
        while log and log[0] <= t - self.window:
            log.popleft()
        used = len(log)
        if used + cost <= self.limit + 1e-9:
            return True, 0.0, self.limit - used
        need = int(used + cost - self.limit)  # entries that must expire first
        idx = min(max(need - 1, 0), used - 1) if used else 0
        wait = (log[idx] + self.window - t) if used else self.window
        return False, max(wait, 0.001), self.limit - used

    def consume(self, key, t, cost):
        for _ in range(int(round(cost))):
            self.state[key].append(t)


class SlidingCounter:
    def __init__(self, limit, window):
        self.limit, self.window = float(limit), float(window)
        self.state = {}

    def _roll(self, key, t):
        w = math.floor(t / self.window)
        start, prev, curr = self.state.get(key, (w, 0.0, 0.0))
        if w == start + 1:
            start, prev, curr = w, curr, 0.0
        elif w > start + 1:
            start, prev, curr = w, 0.0, 0.0
        self.state[key] = (start, prev, curr)
        return start, prev, curr

    def check(self, key, t, cost):
        start, prev, curr = self._roll(key, t)
        e = t - start * self.window
        est = prev * (1 - e / self.window) + curr
        if est + cost <= self.limit + 1e-9:
            return True, 0.0, self.limit - est
        lim, w = self.limit, self.window
        if curr + cost <= lim and prev > 0:
            wait = w * (1 - (lim - curr - cost) / prev) - e
        else:
            x = 0.0
            if curr > 0 and lim - cost < curr:
                x = w * (1 - (lim - cost) / curr)
            wait = (w - e) + max(0.0, x)
        return False, max(wait, 0.001), self.limit - est

    def consume(self, key, t, cost):
        start, prev, curr = self.state[key]
        self.state[key] = (start, prev, curr + cost)


def make_limiter(p):
    algo = p["algorithm"]
    if algo == "token_bucket":
        return TokenBucket(p["rate"], p["burst"])
    if algo == "fixed_window":
        return FixedWindow(p["limit"], p["window_s"])
    if algo == "sliding_log":
        return SlidingLog(p["limit"], p["window_s"])
    return SlidingCounter(p["limit"], p["window_s"])


# ----------------------------------------------------------------------------- config
def num(v, what, positive=True):
    try:
        x = float(v)
    except (TypeError, ValueError):
        raise InputError("%s must be a number, got %r" % (what, v))
    if positive and x <= 0:
        raise InputError("%s must be > 0, got %r" % (what, v))
    return x


def normalize(cfg):
    if not isinstance(cfg, dict):
        raise InputError("config must be a JSON object")
    pols = cfg.get("policies")
    if not pols:
        raise InputError("config needs a non-empty 'policies' list")
    out = []
    for i, p in enumerate(pols, 1):
        q = dict(p)
        q.setdefault("name", "policy-%d" % i)
        algo = q.get("algorithm")
        if algo not in ALGOS:
            raise InputError("policy %r: algorithm must be one of %s" % (q["name"], ", ".join(ALGOS)))
        q.setdefault("key", "client")
        if q["key"] not in KEYS:
            raise InputError("policy %r: key must be one of %s" % (q["name"], ", ".join(KEYS)))
        if algo == "token_bucket":
            if "rate" in q:
                q["rate"] = num(q["rate"], q["name"] + ".rate")
                q["burst"] = num(q.get("burst", max(1.0, q["rate"])), q["name"] + ".burst")
            elif "limit" in q:
                lim = num(q["limit"], q["name"] + ".limit")
                win = num(q.get("window_s", 1), q["name"] + ".window_s")
                q["rate"] = lim / win
                q["burst"] = num(q.get("burst", lim), q["name"] + ".burst")
            else:
                raise InputError("policy %r: token_bucket needs rate (+burst) or limit + window_s" % q["name"])
            q.setdefault("window_s", 1.0)
            q["window_s"] = num(q["window_s"], q["name"] + ".window_s")
        else:
            q["limit"] = num(q.get("limit"), q["name"] + ".limit")
            q["window_s"] = num(q.get("window_s"), q["name"] + ".window_s")
        q["endpoints"] = list(q.get("endpoints") or [])
        q["clients"] = list(q.get("clients") or [])
        q["exclude_clients"] = list(q.get("exclude_clients") or [])
        out.append(q)
    cfg = dict(cfg)
    cfg["policies"] = out
    cfg["duration_s"] = num(cfg.get("duration_s", 300), "duration_s")
    if cfg.get("capacity_rps") is not None:
        cfg["capacity_rps"] = num(cfg["capacity_rps"], "capacity_rps")
    clients = []
    for i, c in enumerate(cfg.get("clients") or [], 1):
        c = dict(c)
        c.setdefault("name", "client-%d" % i)
        c.setdefault("kind", "unknown")
        c.setdefault("pattern", "steady")
        c.setdefault("endpoint", "/")
        c.setdefault("retry", "none")
        if c["retry"] not in ("none", "immediate", "backoff", "honor"):
            raise InputError("client %r: retry must be none, immediate, backoff or honor" % c["name"])
        c["max_retries"] = int(c.get("max_retries", 3))
        c["cost"] = num(c.get("cost", 1), c["name"] + ".cost")
        c["start"] = num(c.get("start", 0), c["name"] + ".start", positive=False)
        c["duration"] = num(c.get("duration", cfg["duration_s"] - c["start"]), c["name"] + ".duration")
        if c["pattern"] in ("steady", "poisson"):
            c["rps"] = num(c.get("rps"), c["name"] + ".rps")
        elif c["pattern"] == "burst":
            c["burst"] = int(num(c.get("burst"), c["name"] + ".burst"))
            c["every_s"] = num(c.get("every_s", 60), c["name"] + ".every_s")
        else:
            raise InputError("client %r: pattern must be steady, poisson or burst" % c["name"])
        clients.append(c)
    cfg["clients"] = clients
    return cfg


def generate(cfg):
    rng = random.Random(cfg.get("seed", 1))
    reqs = []
    for c in cfg["clients"]:
        t0, t1 = c["start"], c["start"] + c["duration"]
        if c["pattern"] == "steady":
            n = int(round(c["duration"] * c["rps"]))
            reqs += [(t0 + i / c["rps"], c["name"], c["endpoint"], c["cost"]) for i in range(n)]
        elif c["pattern"] == "poisson":
            t = t0 + rng.expovariate(c["rps"])
            while t < t1:
                reqs.append((t, c["name"], c["endpoint"], c["cost"]))
                t += rng.expovariate(c["rps"])
        else:
            t = t0
            while t < t1:
                reqs += [(t + k * 0.001, c["name"], c["endpoint"], c["cost"]) for k in range(c["burst"])]
                t += c["every_s"]
    return reqs


def read_trace(path):
    reqs = []
    with open(path, newline="", encoding="utf-8") as f:
        for n, row in enumerate(csv.DictReader(f), 2):
            try:
                reqs.append((float(row["t"]), row["client"].strip(), (row.get("endpoint") or "/").strip(),
                             float(row.get("cost") or 1)))
            except (KeyError, ValueError, AttributeError):
                raise InputError("%s line %d: need t,client[,endpoint][,cost]" % (path, n))
    return reqs


# ----------------------------------------------------------------------------- simulation
def policy_key(p, client, endpoint):
    if p["endpoints"] and not any(endpoint.startswith(e) for e in p["endpoints"]):
        return None
    if (p["clients"] and client not in p["clients"]) or client in p["exclude_clients"]:
        return None
    return {"client": client, "endpoint": endpoint, "client+endpoint": client + " " + endpoint,
            "global": "*"}[p["key"]]


def simulate(cfg, reqs):
    pols = cfg["policies"]
    lims = [make_limiter(p) for p in pols]
    cinfo = {c["name"]: c for c in cfg["clients"]}
    heap = []
    for i, (t, cl, ep, cost) in enumerate(sorted(reqs)):
        heapq.heappush(heap, (t, i, cl, ep, cost, 0, i))
    seq = len(reqs)
    stats = defaultdict(lambda: {"logical": 0, "attempts": 0, "allowed": 0, "rejected": 0, "failed": 0, "delayed": 0,
                                 "max_retry_after": 0.0, "blocked_by": defaultdict(int), "first_reject": None})
    pstats = [{"checks": 0, "rejects": 0, "allowed_times": defaultdict(list), "by_client": defaultdict(int)}
              for _ in pols]
    admitted = []
    sample = None
    while heap:
        t, _, cl, ep, cost, attempt, lid = heapq.heappop(heap)
        s = stats[cl]
        if attempt == 0:
            s["logical"] += 1
        s["attempts"] += 1
        keys = [policy_key(p, cl, ep) for p in pols]
        rejecting, wait, remain = [], 0.0, {}
        for j, (lim, k) in enumerate(zip(lims, keys)):
            if k is None:
                continue
            pstats[j]["checks"] += 1
            ok, w, rem = lim.check(k, t, cost)
            remain[j] = rem
            if not ok:
                rejecting.append(j)
                wait = max(wait, w)
        if not rejecting:
            for j, (lim, k) in enumerate(zip(lims, keys)):
                if k is not None:
                    lim.consume(k, t, cost)
                    pstats[j]["allowed_times"][k].append(t)
            s["allowed"] += 1
            if attempt:
                s["delayed"] += 1
            admitted.append((t, cl, attempt))
            continue
        s["rejected"] += 1
        s["max_retry_after"] = max(s["max_retry_after"], wait)
        if s["first_reject"] is None:
            s["first_reject"] = t
        s["blocked_by"][pols[rejecting[0]]["name"]] += 1
        for j in rejecting:
            pstats[j]["rejects"] += 1
            pstats[j]["by_client"][cl] += 1
        if sample is None:
            j = rejecting[0]
            sample = {"t": round(t, 3), "client": cl, "endpoint": ep, "policy": pols[j]["name"],
                      "retry_after": max(1, math.ceil(wait)), "quota": pols[j].get("limit", pols[j].get("burst")),
                      "window_s": pols[j]["window_s"]}
        c = cinfo.get(cl, {})
        mode, maxr = c.get("retry", "none"), c.get("max_retries", 3)
        if mode != "none" and attempt < maxr:
            delay = {"immediate": 0.1, "backoff": 2.0 ** attempt, "honor": wait + 0.05}[mode]
            seq += 1
            heapq.heappush(heap, (t + delay, seq, cl, ep, cost, attempt + 1, lid))
        else:
            s["failed"] += 1
    return stats, pstats, admitted, sample


def max_in_window(times, w):
    best, dq = 0, deque()
    for t in sorted(times):
        dq.append(t)
        while dq[0] <= t - w:
            dq.popleft()
        best = max(best, len(dq))
    return best


def theoretical_max(p):
    w = p["window_s"]
    if p["algorithm"] == "token_bucket":
        return p["burst"] + p["rate"] * w
    if p["algorithm"] == "fixed_window":
        return 2 * p["limit"]
    return p["limit"]


def sustained_rps(p):
    return p["rate"] if p["algorithm"] == "token_bucket" else p["limit"] / p["window_s"]


# ----------------------------------------------------------------------------- analysis
def analyse(cfg, reqs):
    stats, pstats, admitted, sample = simulate(cfg, reqs)
    pols = cfg["policies"]
    kinds = {c["name"]: c["kind"] for c in cfg["clients"]}
    findings = []

    def add(sev, code, msg):
        findings.append({"severity": sev, "code": code, "message": msg})

    per_sec = defaultdict(int)
    retry_sec = defaultdict(int)
    for t, _, att in admitted:
        per_sec[int(t)] += 1
        if att:
            retry_sec[int(t)] += 1
    peak_rps = max(per_sec.values()) if per_sec else 0
    peak_sec = max(per_sec, key=per_sec.get) if per_sec else 0

    clients = []
    for name in sorted(stats, key=lambda n: (-stats[n]["logical"], n)):
        s = stats[name]
        fail_pct = 100.0 * s["failed"] / s["logical"] if s["logical"] else 0.0
        hurt_pct = 100.0 * (s["failed"] + s["delayed"]) / s["logical"] if s["logical"] else 0.0
        top = max(s["blocked_by"], key=s["blocked_by"].get) if s["blocked_by"] else ""
        clients.append({"client": name, "kind": kinds.get(name, "unknown"), "logical": s["logical"],
                        "attempts": s["attempts"], "allowed": s["allowed"], "rejected": s["rejected"],
                        "failed": s["failed"], "failed_pct": round(fail_pct, 1),
                        "delayed": s["delayed"],
                        "amplification": round(s["attempts"] / s["logical"], 2) if s["logical"] else 0,
                        "max_retry_after_s": round(s["max_retry_after"], 1), "top_policy": top,
                        "first_reject_s": None if s["first_reject"] is None else round(s["first_reject"], 1)})
        k = kinds.get(name, "unknown")
        if k == "legit" and hurt_pct > 1.0:
            add("high", "RL01", "Legit client %s is throttled on %.1f%% of its requests (%d failed, %d only after "
                "retrying, waits up to %.1fs), mostly by policy '%s'; raise that limit or give this client its own tier."
                % (name, hurt_pct, s["failed"], s["delayed"], s["max_retry_after"], top))
        elif k == "legit" and s["rejected"]:
            add("low", "RL01", "Legit client %s sees %d 429s but its retries recover; check it handles Retry-After."
                % (name, s["rejected"]))
        if k == "abusive" and s["logical"]:
            share = 100.0 * s["allowed"] / s["logical"]
            if share > 25:
                add("medium", "RL02", "Abusive client %s still gets %.0f%% of its requests through (%d); "
                    "tighten the per-client limit or add a stricter policy for its pattern." % (name, share, s["allowed"]))
        amp = s["attempts"] / s["logical"] if s["logical"] else 0
        if amp >= 1.5:
            add("medium", "RL05", "Client %s sends %.2fx attempts per logical request because it retries throttled "
                "calls; retries count against the limit and add load - require Retry-After or backoff with jitter."
                % (name, amp))

    if per_sec and retry_sec.get(peak_sec, 0) > 0.5 * peak_rps and peak_rps >= 20:
        add("medium", "RL10", "Retry herd: %d of the %d requests admitted in second %d are retries released by the same "
            "Retry-After / window reset; add jitter to Retry-After (or client backoff) and prefer a token bucket or "
            "sliding window so capacity is not handed out all at once." % (retry_sec[peak_sec], peak_rps, peak_sec))
    cap = cfg.get("capacity_rps")
    if cap:
        if peak_rps > cap:
            add("high", "RL03", "Admitted traffic peaks at %d req/s (second %d) over the backend capacity of %g req/s; "
                "add or lower a global limit." % (peak_rps, peak_sec, cap))
        if not any(p["key"] == "global" for p in pols):
            per_client = [sustained_rps(p) for p in pols if p["key"] in ("client", "client+endpoint")
                          and not p["endpoints"] and not p["clients"]]
            if per_client:
                n = len(stats)
                add("medium", "RL03", "No global policy: %d clients x %g req/s per client = %g req/s possible vs capacity %g."
                    % (n, min(per_client), n * min(per_client), cap))

    policies = []
    for p, ps in zip(pols, pstats):
        obs = max((max_in_window(ts, p["window_s"]) for ts in ps["allowed_times"].values()), default=0)
        theo = theoretical_max(p)
        policies.append({"name": p["name"], "algorithm": p["algorithm"], "key": p["key"],
                         "endpoints": p["endpoints"], "clients": p["clients"], "exclude_clients": p["exclude_clients"], "checks": ps["checks"], "rejects": ps["rejects"],
                         "sustained_rps": round(sustained_rps(p), 3), "window_s": p["window_s"],
                         "worst_case_per_window": round(theo, 1), "observed_max_per_window": obs})
        if p["algorithm"] == "fixed_window" and obs > p["limit"]:
            add("medium", "RL04", "Fixed window '%s' let %d requests through in one rolling %gs window (limit %g, "
                "%.1fx) across a window boundary; use sliding_counter or a token bucket if the limit protects capacity."
                % (p["name"], obs, p["window_s"], p["limit"], obs / p["limit"]))
        if p["key"] == "global" and ps["rejects"]:
            allowed_by = defaultdict(int)
            for t, cl, _ in admitted:
                allowed_by[cl] += 1
            total = sum(allowed_by.values()) or 1
            hog = max(allowed_by, key=allowed_by.get)
            victims = [c for c in ps["by_client"] if kinds.get(c) == "legit" and c != hog]
            if allowed_by[hog] / total > 0.5 and victims:
                add("high" if kinds.get(hog) != "legit" else "medium", "RL06",
                    "Noisy neighbour: %s holds %.0f%% of admitted traffic while global policy '%s' rejects %s; "
                    "add a per-client limit below the global one." % (hog, 100.0 * allowed_by[hog] / total,
                                                                     p["name"], ", ".join(sorted(victims))))
        if p["algorithm"] == "sliding_log" and p["limit"] > 1000:
            add("low", "RL07", "Sliding log '%s' stores up to %d timestamps per key; prefer sliding_counter for large limits."
                % (p["name"], p["limit"]))
        if ps["checks"] and not ps["rejects"] and obs and theo > 10 * obs:
            add("low", "RL09", "Policy '%s' never triggered; its worst case (%g per %gs) is >10x the busiest key (%d). "
                "Fine as a safety net, but it will not stop a surge." % (p["name"], theo, p["window_s"], obs))
    if sample:
        add("info", "RL08", "Return 429 with Retry-After on every rejection (see the sample headers) and document the "
            "limits so clients can pace themselves.")
    order = {"high": 0, "medium": 1, "low": 2, "info": 3}
    findings.sort(key=lambda f: (order[f["severity"]], f["code"]))
    return {"service": cfg.get("service", "API"), "duration_s": cfg["duration_s"], "requests": len(reqs),
            "admitted": len(admitted), "peak_admitted_rps": peak_rps, "peak_second": peak_sec,
            "capacity_rps": cap, "clients": clients, "policies": policies, "findings": findings,
            "sample_429": sample}


# ----------------------------------------------------------------------------- report
def fmt(x):
    return ("%g" % x) if isinstance(x, float) else str(x)


def scope(p):
    parts = []
    if p["endpoints"]:
        parts.append(", ".join(p["endpoints"]))
    if p["clients"]:
        parts.append("only " + ", ".join(p["clients"]))
    if p["exclude_clients"]:
        parts.append("not " + ", ".join(p["exclude_clients"]))
    return "; ".join(parts) or "all"


def report(r):
    L = ["# Rate limit simulation: %s" % r["service"], ""]
    cap = (" vs capacity %g" % r["capacity_rps"]) if r["capacity_rps"] else ""
    L.append("Simulated %gs, %d logical requests, %d admitted, peak %d req/s admitted (second %d)%s."
             % (r["duration_s"], r["requests"], r["admitted"], r["peak_admitted_rps"], r["peak_second"], cap))
    highs = sum(f["severity"] == "high" for f in r["findings"])
    L += ["", "**Verdict: %s**" % ("%d high finding(s) - fix before shipping these limits" % highs if highs
                                   else "policies hold - no high findings"), ""]
    L += ["## Clients", "", "| Client | Kind | Requests | Attempts | Allowed | 429s | OK after retry | Failed | Failed % | Max Retry-After | Top policy |",
          "|---|---|---|---|---|---|---|---|---|---|---|"]
    for c in r["clients"]:
        L.append("| %s | %s | %d | %d | %d | %d | %d | %d | %.1f%% | %ss | %s |" % (
            c["client"], c["kind"], c["logical"], c["attempts"], c["allowed"], c["rejected"], c["delayed"], c["failed"],
            c["failed_pct"], fmt(c["max_retry_after_s"]), c["top_policy"] or "-"))
    L += ["", "## Policies", "", "| Policy | Algorithm | Key | Scope | Sustained | Worst case / window | Busiest key / window | Checks | Rejects |",
          "|---|---|---|---|---|---|---|---|---|"]
    for p in r["policies"]:
        L.append("| %s | %s | %s | %s | %s req/s | %s per %ss | %d | %d | %d |" % (
            p["name"], p["algorithm"], p["key"], scope(p), fmt(p["sustained_rps"]),
            fmt(p["worst_case_per_window"]), fmt(p["window_s"]), p["observed_max_per_window"], p["checks"], p["rejects"]))
    L += ["", "## Findings", ""]
    if not r["findings"]:
        L.append("- none")
    for f in r["findings"]:
        L.append("- **%s %s** %s" % (f["severity"].upper(), f["code"], f["message"]))
    s = r["sample_429"]
    if s:
        L += ["", "## Sample 429 (first rejection, t=%ss, %s %s)" % (s["t"], s["client"], s["endpoint"]), "", "```",
              "HTTP/1.1 429 Too Many Requests",
              "Retry-After: %d" % s["retry_after"],
              'RateLimit-Policy: "%s";q=%s;w=%s' % (s["policy"], fmt(float(s["quota"])), fmt(float(s["window_s"]))),
              'RateLimit: "%s";r=0;t=%d' % (s["policy"], s["retry_after"]),
              "Content-Type: application/problem+json", "```",
              "Header names follow the IETF httpapi ratelimit-headers draft; older gateways use "
              "X-RateLimit-Limit / -Remaining / -Reset instead."]
    return "\n".join(L) + "\n"


# ----------------------------------------------------------------------------- self-test
def self_test():
    cases = []

    def check(name, cond, detail=""):
        cases.append((name, bool(cond), detail))

    tb = TokenBucket(rate=1, burst=3)
    ok = 0
    for i in range(5):
        a, w, _ = tb.check("k", 0.0, 1)
        if a:
            tb.consume("k", 0.0, 1)
            ok += 1
    check("token bucket burst of 3 at t=0", ok == 3, ok)
    a, w, _ = tb.check("k", 0.0, 1)
    check("token bucket retry-after 1s", (not a) and abs(w - 1.0) < 1e-6, w)
    a, w, _ = tb.check("k", 1.0, 1)
    check("token bucket refills 1 token per second", a)

    fw = FixedWindow(limit=10, window=60)
    times = [59.0 + i * 0.01 for i in range(10)] + [60.0 + i * 0.01 for i in range(10)]
    allowed = []
    for t in times:
        a, _, _ = fw.check("k", t, 1)
        if a:
            fw.consume("k", t, 1)
            allowed.append(t)
    check("fixed window boundary burst = 2x limit", max_in_window(allowed, 60) == 20, len(allowed))
    a, w, _ = fw.check("k", 61.0, 1)
    check("fixed window retry-after until reset", (not a) and abs(w - 59.0) < 1e-6, w)

    sl = SlidingLog(limit=10, window=60)
    allowed = []
    for t in times:
        a, _, _ = sl.check("k", t, 1)
        if a:
            sl.consume("k", t, 1)
            allowed.append(t)
    check("sliding log never exceeds limit", max_in_window(allowed, 60) == 10, len(allowed))
    a, w, _ = sl.check("k", 60.5, 1)
    check("sliding log retry-after = oldest + window", (not a) and abs(w - (59.0 + 60 - 60.5)) < 1e-6, w)

    sc = SlidingCounter(limit=10, window=60)
    allowed = []
    for t in times + [90.0 + i for i in range(10)]:
        a, _, _ = sc.check("k", t, 1)
        if a:
            sc.consume("k", t, 1)
            allowed.append(t)
    first = [t for t in allowed if t < 61]
    later = [t for t in allowed if t >= 90]
    check("sliding counter blocks the boundary burst", len(first) == 10, len(first))
    check("sliding counter weights previous window (about half at mid-window)", 4 <= len(later) <= 7, len(later))

    cfg = normalize({"service": "t", "duration_s": 60, "capacity_rps": 1000, "policies": [
        {"name": "pc", "algorithm": "token_bucket", "key": "client", "rate": 5, "burst": 5}],
        "clients": [{"name": "good", "kind": "legit", "rps": 2},
                    {"name": "bad", "kind": "abusive", "rps": 20, "retry": "immediate"}]})
    r = analyse(cfg, generate(cfg))
    by = {c["client"]: c for c in r["clients"]}
    check("legit client under limit not throttled", by["good"]["rejected"] == 0, by["good"])
    check("abusive client mostly throttled", by["bad"]["allowed"] <= 5 * 60 + 10, by["bad"]["allowed"])
    check("immediate retries amplify attempts", by["bad"]["amplification"] > 1.5, by["bad"]["amplification"])
    codes = {f["code"] for f in r["findings"]}
    check("RL05 retry amplification finding", "RL05" in codes, codes)

    cfg2 = normalize({"duration_s": 30, "capacity_rps": 10, "policies": [
        {"name": "g", "algorithm": "fixed_window", "key": "global", "limit": 600, "window_s": 60}],
        "clients": [{"name": "hog", "kind": "abusive", "rps": 30},
                    {"name": "app", "kind": "legit", "rps": 1, "start": 25, "duration": 5}]})
    r2 = analyse(cfg2, generate(cfg2))
    codes2 = {f["code"]: f["severity"] for f in r2["findings"]}
    check("RL03 over capacity is high", codes2.get("RL03") == "high", codes2)
    check("RL06 noisy neighbour on global", "RL06" in codes2, codes2)
    check("RL01 legit client starved", codes2.get("RL01") == "high", codes2)

    cfg3 = normalize({"duration_s": 10, "policies": [
        {"name": "login", "algorithm": "sliding_log", "key": "client", "limit": 2, "window_s": 60, "endpoints": ["/login"]}],
        "clients": [{"name": "u", "rps": 1, "endpoint": "/orders"}, {"name": "v", "rps": 1, "endpoint": "/login"}]})
    r3 = analyse(cfg3, generate(cfg3))
    by3 = {c["client"]: c for c in r3["clients"]}
    check("endpoint-scoped policy ignores other endpoints", by3["u"]["rejected"] == 0 and by3["v"]["allowed"] == 2,
          (by3["u"], by3["v"]))
    check("burst pattern generates bursts", len(generate(normalize({"duration_s": 120, "policies": cfg3["policies"],
          "clients": [{"name": "b", "pattern": "burst", "burst": 5, "every_s": 60}]}))) == 10)
    cfg4 = normalize({"duration_s": 10, "policies": [
        {"name": "default", "algorithm": "token_bucket", "key": "client", "rate": 1, "burst": 1, "exclude_clients": ["vip"]},
        {"name": "vip", "algorithm": "token_bucket", "key": "client", "rate": 10, "burst": 10, "clients": ["vip"]}],
        "clients": [{"name": "vip", "rps": 5}, {"name": "std", "rps": 5}]})
    by4 = {c["client"]: c for c in analyse(cfg4, generate(cfg4))["clients"]}
    check("client tiers: vip unthrottled, std limited", by4["vip"]["rejected"] == 0 and by4["std"]["allowed"] <= 11,
          (by4["vip"], by4["std"]))
    try:
        normalize({"policies": [{"algorithm": "leaky"}]})
        check("bad algorithm rejected", False)
    except InputError:
        check("bad algorithm rejected", True)
    rep = report(r2)
    check("report has verdict and sample 429", "Verdict" in rep and "429 Too Many Requests" in rep)

    for name, ok, detail in cases:
        print("%s  %s%s" % ("PASS" if ok else "FAIL", name, "" if ok else "  -> %r" % (detail,)))
    passed = sum(1 for _, ok, _ in cases if ok)
    print("%d/%d passed" % (passed, len(cases)))
    return 0 if passed == len(cases) else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="Simulate API rate limit policies against traffic.")
    ap.add_argument("config", nargs="?")
    ap.add_argument("--trace", help="CSV with t,client[,endpoint][,cost] instead of generated traffic")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.config:
        ap.print_usage(sys.stderr)
        return 2
    try:
        with open(a.config, encoding="utf-8") as f:
            cfg = normalize(json.load(f))
        reqs = read_trace(a.trace) if a.trace else generate(cfg)
        if not reqs:
            raise InputError("no traffic: add clients to the config or pass --trace")
        r = analyse(cfg, reqs)
    except (InputError, OSError, json.JSONDecodeError) as e:
        print("error: %s" % e, file=sys.stderr)
        return 2
    print(json.dumps(r, indent=2) if a.json else report(r), end="" if not a.json else "\n")
    return 1 if any(f["severity"] == "high" for f in r["findings"]) else 0


if __name__ == "__main__":
    sys.exit(main())

# Phoenix App taste of a pragmatic Elixir engineer
- Start new projects on the latest stable Elixir and Phoenix releases, and pin Erlang/OTP and Elixir versions in .tool-versions. Confidence: 0.85
- Group business logic into Phoenix contexts with small public APIs, and never call Repo directly from controllers or LiveViews. Confidence: 0.90
- Keep contexts from reaching into each other's schemas, and talk across boundaries through public context functions. Confidence: 0.80
- Use LiveView for interactive pages by default and add JavaScript hooks only for things the browser must do itself. Confidence: 0.80
- Build UI from function components with declared attrs and slots instead of large shared templates. Confidence: 0.85
- Validate all input with Ecto changesets and keep separate changesets for different actions like registration and profile updates. Confidence: 0.90
- Lean on database constraints with unique_constraint and foreign_key_constraint instead of check-then-insert code. Confidence: 0.95
- Preload associations explicitly in context functions, and never let a template trigger an N+1 query. Confidence: 0.85
- Use Ecto.Multi or Repo.transaction for multi-step writes so partial failures roll back cleanly. Confidence: 0.85
- Write reversible migrations, and split long-running data backfills into separate tasks rather than schema migrations. Confidence: 0.85
- Read runtime settings and secrets in config/runtime.exs from environment variables, never in compile-time config. Confidence: 0.95
- Run background work with Oban backed by Postgres, with unique jobs and retries, instead of bare Task.start calls. Confidence: 0.85
- Put long-lived processes under a supervisor with a clear restart strategy and let them crash instead of rescuing every error. Confidence: 0.90
- Avoid GenServers as a place to hold data the database already owns; use them for real concurrency or coordination. Confidence: 0.80
- Return tagged tuples like {:ok, value} and {:error, reason} from context functions and handle them with with blocks. Confidence: 0.90
- Use PubSub for live updates between LiveViews and keep the broadcast topic names defined in one module. Confidence: 0.75
- Generate auth with phx.gen.auth and extend it, rather than building login and session handling from scratch. Confidence: 0.80
- Format code with mix format and run Credo and Dialyzer in CI so style and type issues fail the build. Confidence: 0.80
- Test contexts with DataCase and the SQL sandbox, and test LiveViews with Phoenix.LiveViewTest instead of a browser. Confidence: 0.85
- Emit metrics through Telemetry and watch them in LiveDashboard and an external metrics backend in production. Confidence: 0.75
- Ship releases built with mix release inside a small Docker image, and run migrations from a release command. Confidence: 0.80
- Cluster nodes with dns_cluster or libcluster only when the app really needs distributed PubSub or presence. Confidence: 0.70

# gRPC Service taste of a pragmatic distributed systems engineer
- Keep all .proto files in one shared schema repo or directory with a single owner per package, and generate code from it in CI. Confidence: 0.85
- Lint and check every proto change for breaking changes with buf lint and buf breaking before it can merge. Confidence: 0.90
- Version packages in the name, such as billing.v1, and add a new v2 package instead of changing the meaning of existing fields. Confidence: 0.90
- Never reuse or renumber a field tag, and mark removed fields and names as reserved. Confidence: 0.95
- Give every RPC its own request and response message, even when it starts empty, so fields can be added later. Confidence: 0.90
- Follow the resource-oriented method style of Get, List, Create, Update and Delete instead of ad hoc verbs where it fits. Confidence: 0.75
- Use field masks for partial updates rather than treating unset fields as a request to clear them. Confidence: 0.80
- Paginate every List call with page_size and an opaque page_token, and cap the page size on the server. Confidence: 0.90
- Use well-known types like google.protobuf.Timestamp and Duration instead of raw integers for time values. Confidence: 0.85
- Return the canonical status codes correctly, such as NOT_FOUND, INVALID_ARGUMENT and UNAVAILABLE, and never wrap every failure in UNKNOWN. Confidence: 0.90
- Attach structured error details with the richer error model for validation failures instead of packing them into the message string. Confidence: 0.75
- Require a deadline on every client call and propagate the remaining deadline to downstream calls. Confidence: 0.95
- Retry only idempotent methods, with backoff and a retry budget configured in the service config, not in hand-written loops. Confidence: 0.85
- Pass request IDs, auth tokens and tracing context in metadata, and keep business data in the message body. Confidence: 0.85
- Put logging, auth, metrics and tracing in interceptors instead of repeating them inside each handler. Confidence: 0.90
- Use mutual TLS between internal services and verify per-call credentials on the server for anything sensitive. Confidence: 0.85
- Implement the standard gRPC health checking service and enable server reflection only outside production. Confidence: 0.80
- Prefer unary calls, and use streaming only when the data is truly incremental or long-lived. Confidence: 0.80
- Apply flow control and backpressure on streams, and send keepalive pings within limits the load balancer allows. Confidence: 0.70
- Balance load per request with an L7 proxy or client-side balancing, because HTTP/2 connections pin traffic to one backend. Confidence: 0.85
- Expose a REST or JSON gateway generated from the same protos for browsers and partners instead of maintaining a second API by hand. Confidence: 0.70
- Test handlers with an in-process server and real generated clients rather than mocking the generated stubs. Confidence: 0.80
- Document each service, method and field with comments in the proto file, since that is where readers look first. Confidence: 0.85

---
name: database-connection-pool-sizing-calculator
description: Sizes database connection pools for Postgres/MySQL apps from a short JSON of services, pods, workers, threads and pool settings - worst-case connections at autoscale max and during deploy surges vs max_connections, busy connections from rps x hold time, per-process wait odds (Erlang C), CPU and memory limits, PgBouncer client and server limits. Recommends pool sizes or proxy settings. Includes an offline stdlib Python calculator with --self-test.
---

# Database Connection Pool Sizing Calculator

You work out how many database connections an application really needs and whether its pool settings can take the database down. Most "too many clients already" outages are arithmetic: pods x worker processes x (pool_size + max_overflow), multiplied again by autoscaling and rolling deploys, against a max_connections that also has to cover admin sessions, migrations and monitoring. You do that arithmetic for every service, compare it with how many connections are actually busy at peak, and return pool sizes (or proxy settings) that fit with headroom.

## Files in this skill

- `scripts/pool_sizer.py` - reads the JSON description, computes open connections today / at autoscaling maximum / during a deploy surge, busy connections (Little's law), the chance a request waits for a connection and the mean wait per process (Erlang C), CPU sweet spot and memory, PgBouncer limits, and recommends pool sizes; `--set` runs what-ifs (Python 3.8+ standard library only)
- `references/sizing-model.md` - the formulas, where each input comes from, and the limits of the model
- `references/pool-settings-by-stack.md` - what pool_size, max_overflow, threads and timeouts are called in SQLAlchemy, Django, HikariCP, node-postgres, Prisma, Rails, Go database/sql, Celery/Sidekiq, PgBouncer and RDS Proxy
- `templates/pool-sizing-review.md` - the review to hand back
- `examples/shop-pools.json` - a shop with an API (6-12 pods x 4 gthread workers, pool 10 + overflow 20), Celery workers, an admin app and a nightly report job on a 200-connection Postgres
- `examples/pool-report.md` - the real report on that file
- `examples/shop-pools-pgbouncer.json` - the fixed setup: right-sized fixed pools, API and workers through PgBouncer in transaction mode, admin and reports direct

## Workflow

### 1. Collect the inputs
For the database: engine, `max_connections`, `superuser_reserved_connections` (Postgres default 3), connections used by everything that is not an app service (admin tools, monitoring, replication, migrations, BI), vCPUs and RAM. For each service: instances now and autoscaling maximum, worker processes per instance, threads or concurrency per process (async or not), pool size and overflow, pool timeout, request timeout, and load: peak requests or jobs per second and how long one keeps a connection checked out (`conn_hold_ms`; frameworks with a session per request hold it for the whole request). If there is no hold time, use queries per request x average query time, or the pool's "checked out" gauge as `busy_connections`. Ask for the deploy strategy (maxSurge) and any proxy (PgBouncer, RDS Proxy, ProxySQL, Supavisor). Write it as JSON like `examples/shop-pools.json`.

### 2. Run the calculator
```
python3 scripts/pool_sizer.py examples/shop-pools.json
python3 scripts/pool_sizer.py pools.json --set api.max_instances=20 --set database.max_connections=400
python3 scripts/pool_sizer.py pools.json --json
python3 scripts/pool_sizer.py --self-test
```
Exit 0 = no HIGH finding, 1 = HIGH findings, 2 = the JSON is invalid (the message names the field). Without a shell, do the same sums by hand with `references/sizing-model.md`: open = instances x processes x (pool_size + max_overflow) per service; busy = rps x hold seconds; compare both with max_connections - reserved - other.

### 3. Read the result
- HIGH findings first: limits exceeded today, at autoscaling maximum or during a deploy surge, pools or proxies too small for the load, workers saturated by database time.
- Compare "busy at peak" with "open": a large gap means idle connections hold memory and slots; a transaction-mode proxy or smaller fixed pools close it.
- "Busy at peak" above the CPU sweet spot (cores x 2 + 1) means the database itself is the bottleneck: more connections make it slower, not faster. Fix hold time (short transactions, no HTTP calls inside them, indexes) or capacity.
- Treat the Erlang C numbers as a guide: they assume evenly spread, random arrivals. Check them against the pool's wait-time and checked-out metrics.

### 4. Recommend
- Prefer fixed pools (`max_overflow` 0, min idle = max) sized per process from the recommendation, never more than the threads that can use them.
- Keep at least 10% of usable connections free at max scale + deploy surge for admin sessions, failover and migrations.
- When direct connections cannot fit, put the busy services behind a transaction-mode proxy with the server pool the report suggests; keep migrations, `LISTEN/NOTIFY`, advisory locks and session settings on a direct connection.
- Raise `max_connections` only when RAM allows it, and say what it costs.
- Set pool timeouts well below the request timeout so overload fails fast instead of piling up.
- Rerun with `--set` for the next traffic peak or the next autoscaling change.

### 5. Deliver
Fill `templates/pool-sizing-review.md`: verdict, the connection budget table, findings with fixes, the new settings per service in that stack's own names, proxy settings, and what to monitor after the change.

## Example result
On `examples/shop-pools.json` the services can open 1,230 connections today, 2,430 at autoscaling maximum and 3,050 during a deploy, against 185 usable, while only about 28 are busy at peak. The API pool (10 + 20 per process) is larger than its 8 threads can ever use, and every Celery process keeps 15 connections for 1 thread. Right-sized fixed pools still need 400 direct connections, so the report suggests a transaction-mode PgBouncer with about 53 server connections. `examples/shop-pools-pgbouncer.json` applies that and fits in 80 of 185 with no HIGH finding; the remaining MEDIUM says the 8-core database is already busy at peak (28 active vs a sweet spot of 17).

## Rules

- Never guess max_connections, autoscaling limits or hold times; ask, or label the number as an assumption in the review.
- Size for the worst case the platform allows (autoscaling max + deploy surge), not today's pod count.
- Count every client of the database, including cron jobs, BI tools, migrations and replicas' replication slots.
- Do not recommend raising max_connections as the first fix; idle connections cost memory and CPU on the database.
- Give settings in the names of the user's own framework (see `references/pool-settings-by-stack.md`).
- Say clearly that the queueing numbers are a model and name the metric that confirms them.
FILE:references/sizing-model.md
# Sizing model

## Connection budget
- Usable = `max_connections - reserved - other`. Postgres keeps `superuser_reserved_connections` (default 3) for admins; managed services reserve more (RDS rdsadmin, Cloud SQL, Azure). "Other" = monitoring agents, admin tools, BI, migrations, logical replication.
- Open connections per service = `instances x processes x (pool_size + max_overflow)`.
- Autoscaling: use `max_instances`. Rolling deploy: old and new pods overlap, `ceil(max_instances x (1 + maxSurge)) x processes x pool max`.
- Via a proxy the database sees only the proxy's server connections: `min(default_pool_size x db/user pairs, max_db_connections) + reserve_pool_size`, per proxy instance.
- Headroom: keep >= 10% of usable free at max scale + surge.

## Busy connections (Little's law)
- `busy = arrival rate x time each arrival holds a connection`. 1,200 requests/s x 15 ms = 18 connections busy on average.
- Hold time is checkout to return, not query time: a framework that opens a transaction or session for the whole request holds the connection for the whole request, including template rendering and external HTTP calls.
- Without measurements: `queries per request x average query time + ~1 ms overhead`; better, read the pool's checked-out gauge at peak.
- Peaks inside a minute are higher than the per-minute average; the calculator multiplies load by `burst_factor` (default 1.3).

## Waiting for a connection (Erlang C)
- Per process, offered load `a = busy x burst / processes` (in Erlangs). With `c` connections in the pool, the probability that a checkout has to wait is Erlang C(c, a); mean wait = `C(c, a) x hold / (c - a)`.
- `a >= c` means the pool is saturated: requests queue until `pool_timeout` and then fail.
- Recommended pool = smallest `c` with C(c, a) <= target (default 1%) and utilization `a / c` <= 0.7, never more than the threads that can use connections (sync workers) and at least 2 (or the thread count if lower).
- A sync process with N threads never uses more than N connections. If `pool >= threads`, nothing waits for a connection; requests wait for a thread instead (WORKERS_SATURATED when `a` approaches `threads`).
- Assumptions: random (Poisson) arrivals, exponential hold times, load spread evenly over processes. Real traffic is burstier; use the numbers to compare options, then confirm with pool wait metrics.

## The database's own limit
- A database runs about `cores x 2 + effective spindles` queries at once efficiently (the HikariCP / PostgreSQL wiki rule of thumb; SSD -> spindles about 1). More active connections queue inside the database and add context switching and lock contention.
- So `busy at peak` above that number is a capacity or query problem, not a pool problem.
- Each Postgres backend costs roughly 5-10 MB plus `work_mem` per sort/hash node; MySQL threads about 1-3 MB plus buffers. The calculator flags open connections x MB above 25% of RAM.

## Proxies
- Transaction mode multiplexes many client connections over few server connections; session mode pins one server connection per client and saves nothing.
- Client connections to PgBouncer are cheap (~2 kB); `max_client_conn` must cover all client pools at max scale + surge, per proxy instance.
- Transaction mode loses session state between transactions: `SET`, temp tables, advisory locks, `LISTEN/NOTIFY`, `WITH HOLD` cursors; protocol-level prepared statements need PgBouncer 1.21+ (`max_prepared_statements`). Run migrations directly.
- RDS Proxy "pins" connections that use session state, which silently turns it into session mode for those clients.

## What to monitor after a change
- Pool: checked-out connections, wait time / wait count, timeouts (SQLAlchemy `QueuePool limit ... reached`, HikariCP `Connection is not available, request timed out`).
- Database: `SELECT count(*), state FROM pg_stat_activity GROUP BY state;` (`idle in transaction` = held but unused), `max_connections` usage, CPU, `wait_event` types.
- Proxy: `SHOW POOLS;` (`cl_waiting`, `maxwait`), `SHOW STATS;`.
FILE:references/pool-settings-by-stack.md
# Pool settings by stack

| Stack | Pool size | Overflow / max | Idle min | Wait timeout | Concurrency per process |
|---|---|---|---|---|---|
| SQLAlchemy (QueuePool) | `pool_size` (5) | `max_overflow` (10) | - | `pool_timeout` (30 s) | gunicorn `--threads`, uvicorn = async |
| Django | no pool before 5.1; persistent `CONN_MAX_AGE` = 1 connection per thread; 5.1+ psycopg pool `OPTIONS["pool"]` `min_size`/`max_size` | - | `min_size` | `timeout` | gunicorn workers x threads |
| HikariCP (Java/Spring) | `maximumPoolSize` (10) | - | `minimumIdle` (= max) | `connectionTimeout` (30 s) | Tomcat `server.tomcat.threads.max` (200) |
| node-postgres (pg.Pool) | `max` (10) | - | - | `connectionTimeoutMillis` (0 = forever) | async: one process, many requests |
| Prisma | `connection_limit` in the URL (cores x 2 + 1) | - | - | `pool_timeout` (10 s) | async |
| Rails ActiveRecord | `pool` in database.yml (5) | - | - | `checkout_timeout` (5 s) | Puma threads (`RAILS_MAX_THREADS`) x workers |
| Go database/sql | `SetMaxOpenConns` (0 = unlimited) | - | `SetMaxIdleConns` (2) | context deadline | goroutines: async |
| Celery | 1 connection per worker process (Django) or the SQLAlchemy pool per process | - | - | - | `--concurrency` processes, 1 task each (prefork) |
| Sidekiq | ActiveRecord `pool` >= Sidekiq `concurrency` | - | - | - | `concurrency` threads |
| PgBouncer | `default_pool_size` (20) per db/user pair, `max_db_connections` | `reserve_pool_size` | `min_pool_size` | `query_wait_timeout` (120 s) | `max_client_conn` (100) |
| RDS Proxy | `MaxConnectionsPercent` of max_connections | - | `MaxIdleConnectionsPercent` | `ConnectionBorrowTimeout` (120 s) | - |

## Mapping to the calculator's JSON
- `processes`: gunicorn/Puma workers, Celery `--concurrency` (prefork), uWSGI processes, Node cluster workers.
- `threads`: gunicorn `--threads`, Puma threads, Tomcat threads, Sidekiq concurrency. Set `async: true` for asyncio, Node, Go, reactive Java; then the pool size is the only concurrency limit.
- `pool_size` + `max_overflow`: the most connections one process can open.
- `pool_timeout_ms`, `request_timeout_ms`: the pool's wait timeout and the HTTP/load balancer timeout.
- Serverless functions (Lambda, Cloud Run with concurrency 1): each instance is one process; use the account/function concurrency limit as `max_instances` and almost always a proxy.

## Common traps
- Django `CONN_MAX_AGE` with many threads or ASGI opens one connection per thread/task and never shares them.
- Celery/RQ workers that import the web app's SQLAlchemy engine get the web pool size (e.g. 10 + 20) for a process that runs one task at a time.
- Node `pg.Pool` per request handler (a new pool on every request) instead of one per process.
- Go without `SetMaxOpenConns`: unlimited connections under load.
- Rails: Puma threads > `pool` gives `ActiveRecord::ConnectionTimeoutError`; Sidekiq needs its own pool >= concurrency.
- Health checks and readiness probes that open a new connection every few seconds per pod.
FILE:templates/pool-sizing-review.md
# Connection pool sizing review: <system>

**Database:** <engine, size, max_connections, reserved, other -> usable>   **Proxy:** <none / PgBouncer transaction mode ...>
**Verdict:** <will run out / works but wasteful / OK> - <n> HIGH, <n> MEDIUM, <n> LOW
**Inputs measured vs assumed:** <rps and hold time from APM for api; worker hold time assumed 200 ms ...>

## Connection budget
| Scenario | Open now (current settings) | With recommended settings | Usable |
|---|---|---|---|
| Today | <1,230> | <...> | <185> |
| Autoscaling max | <2,430> | <...> | <185> |
| Max + deploy surge | <3,050> | <80 via PgBouncer> | <185> |
| Busy at peak | <~28> | <~28> | CPU sweet spot <~17> |

## Findings
| Sev | What | Why it matters | Fix |
|---|---|---|---|
| <HIGH> | <pools can open 1,230 of 185> | <'too many clients' during a traffic spike or deploy> | <fixed pools + PgBouncer> |

## New settings
| Service | Setting (in its own stack) | Old | New |
|---|---|---|---|
| <api (SQLAlchemy)> | <pool_size / max_overflow / pool_timeout> | <10 / 20 / 30 s> | <5 / 0 / 2 s> |
| <celery-worker> | <pool_size / max_overflow> | <5 / 10> | <1 / 0> |

## Proxy
- <pgbouncer.ini: pool_mode = transaction, default_pool_size = 27, reserve_pool_size = 3, max_db_connections = 27, max_client_conn = 1000; 2 instances>
- <Keep direct: migrations, admin, nightly-reports (session features)>

## Database
- <max_connections stays 200; ~80 used at max + surge>
- <Busy at peak (28) above sweet spot (17): look at the slowest queries / long transactions first>

## Roll-out and monitoring
1. <deploy pool changes to workers first, then API>
2. <watch: pool wait time, timeouts, pg_stat_activity by state, PgBouncer SHOW POOLS cl_waiting/maxwait>
3. <rerun the calculator with the measured hold time after a week>

## Open questions
- <real autoscaling max for api?>
FILE:examples/shop-pools.json
{
  "database": {
    "name": "shop-orders (Postgres 16, 8 vCPU / 32 GB)",
    "engine": "postgres",
    "max_connections": 200,
    "reserved_connections": 3,
    "other_connections": 12,
    "cpu_cores": 8,
    "ram_gb": 32,
    "mb_per_connection": 10
  },
  "deploy": {"surge_percent": 25},
  "settings": {"target_wait_probability": 0.01, "burst_factor": 1.3},
  "services": [
    {
      "name": "api",
      "instances": 6,
      "max_instances": 12,
      "processes": 4,
      "threads": 8,
      "pool_size": 10,
      "max_overflow": 20,
      "peak_rps": 1200,
      "conn_hold_ms": 15,
      "pool_timeout_ms": 30000,
      "request_timeout_ms": 10000
    },
    {
      "name": "celery-worker",
      "instances": 4,
      "max_instances": 8,
      "processes": 8,
      "threads": 1,
      "pool_size": 5,
      "max_overflow": 10,
      "peak_rps": 40,
      "conn_hold_ms": 200
    },
    {
      "name": "admin",
      "instances": 2,
      "processes": 2,
      "threads": 4,
      "pool_size": 5,
      "max_overflow": 0,
      "peak_rps": 5,
      "conn_hold_ms": 30
    },
    {
      "name": "nightly-reports",
      "instances": 1,
      "processes": 1,
      "threads": 4,
      "pool_size": 5,
      "max_overflow": 5,
      "busy_connections": 2
    }
  ]
}
FILE:examples/pool-report.md
# Connection pool sizing report

Database: **shop-orders (Postgres 16, 8 vCPU / 32 GB)** (postgres), max_connections 200, reserved 3, other 12 -> **185 usable**, 8 cores (sweet spot ~17 active)
Deploy surge: 25%   Burst factor: 1.3   Target P(wait): 1.0%

**Verdict: will run out of connections or queue** - 1 HIGH, 8 MEDIUM, 3 LOW finding(s).

## Services

| Service | Path | Procs now / max | Pool (size+overflow) | Busy at peak | Per proc load | P(wait) | Mean wait | Open now | Open max | Deploy surge |
|---|---|---|---|---|---|---|---|---|---|---|
| api | direct | 24 / 48 | 10+20 | 18 (1200 rps x 15 ms) | 0.98 | 0% | 0 ms | 720 | 1440 | 1800 |
| celery-worker | direct | 32 / 64 | 5+10 | 8 (40 rps x 200 ms) | 0.33 | 0% | 0 ms | 480 | 960 | 1200 |
| admin | direct | 4 / 4 | 5+0 | 0.15 (5 rps x 30 ms) | 0.05 | 0% | 0 ms | 20 | 20 | 30 |
| nightly-reports | direct | 1 / 1 | 5+5 | 2 (given) | 2.6 | 0% | 0 ms | 10 | 10 | 20 |

## Totals against the database

| Scenario | Direct | Via proxy (server side) | Database total | Usable | Headroom |
|---|---|---|---|---|---|
| today | 1230 | 0 | 1230 | 185 | -1045 |
| autoscaling max | 2430 | 0 | 2430 | 185 | -2245 |
| max + deploy surge | 3050 | 0 | 3050 | 185 | -2865 |

Busy at peak (all services): ~28.15 connections vs ~17 the CPUs run efficiently.

## Findings

| Sev | Code | Where | What | Fix |
|---|---|---|---|---|
| HIGH | POOL_EXCEEDS_DB_LIMIT | shop-orders (Postgres 16, 8 vCPU / 32 GB) (max_connections 200 - reserved 3 - other 12 = 185 usable) | pools can open 1230 connections at today's size; the database accepts 185 - new connections fail with 'too many clients' / 'Too many connections' under load | shrink pools to the recommendation below, drop max_overflow, or put the services behind a proxy |
| MEDIUM | DB_ACTIVE_OVER_CORES | shop-orders (Postgres 16, 8 vCPU / 32 GB) | about 28.1 connections are busy at peak but 8 cores run about 17 at once efficiently (cores x 2 + spindles); the rest queue inside the database | cut connection hold time (shorter transactions, no network calls inside them), add read replicas, or scale the database |
| MEDIUM | TOO_MANY_CONNECTIONS_FOR_CORES | shop-orders (Postgres 16, 8 vCPU / 32 GB) | up to 2430 open connections for 8 cores (304 per core); idle backends cost memory, snapshot and lock-table overhead | fewer, busier connections: smaller pools or a transaction-mode proxy |
| MEDIUM | CONNECTION_MEMORY | shop-orders (Postgres 16, 8 vCPU / 32 GB) | 2430 connections x ~10 MB = 23.7 GB of 32 GB RAM before work_mem/sort buffers | reduce open connections; keep that memory for cache (shared_buffers / page cache) |
| MEDIUM | PROXY_RECOMMENDED | shop-orders (Postgres 16, 8 vCPU / 32 GB) | only ~28 connections are busy at peak but up to 2430 are open; a transaction-mode pooler multiplexes them | PgBouncer/RDS Proxy/ProxySQL in front of the database (see recommendation) |
| MEDIUM | POOL_LARGER_THAN_CONCURRENCY | api | pool max 30 per process but only 8 thread(s) can use a connection at a time; the extra 22 can never be used | pool_size <= threads; max_overflow 0 |
| MEDIUM | POOL_LARGER_THAN_CONCURRENCY | celery-worker | pool max 15 per process but only 1 thread(s) can use a connection at a time; the extra 14 can never be used | pool_size <= threads; max_overflow 0 |
| MEDIUM | POOL_LARGER_THAN_CONCURRENCY | admin | pool max 5 per process but only 4 thread(s) can use a connection at a time; the extra 1 can never be used | pool_size <= threads; max_overflow 0 |
| MEDIUM | POOL_LARGER_THAN_CONCURRENCY | nightly-reports | pool max 10 per process but only 4 thread(s) can use a connection at a time; the extra 6 can never be used | pool_size <= threads; max_overflow 0 |
| LOW | LARGE_OVERFLOW | api | max_overflow 20 >= pool_size 10 across 48 processes makes the worst case 1440 instead of 480 | fixed pools (max_overflow 0) keep the total predictable |
| LOW | POOL_TIMEOUT_LONG | api | pool_timeout 30000 ms >= request timeout 10000 ms: a request can time out while still waiting for a connection | pool_timeout around 10-30% of the request timeout so overload fails fast |
| LOW | LARGE_OVERFLOW | celery-worker | max_overflow 10 >= pool_size 5 across 64 processes makes the worst case 960 instead of 320 | fixed pools (max_overflow 0) keep the total predictable |

## Recommended pools

| Service | pool_size | max_overflow | P(wait) per proc | Open at max | With deploy surge |
|---|---|---|---|---|---|
| api | 5 | 0 | 0.3% | 240 | 300 |
| celery-worker | 1 | 0 | 0% | 64 | 80 |
| admin | 2 | 0 | 0.1% | 8 | 12 |
| nightly-reports | 4 | 0 | 0% | 4 | 8 |

Database connections needed with these pools at max scale + deploy surge: **400** (direct 400 + proxy 0) of 185 usable -> still does NOT fit.

Direct connections cannot fit. Put the services behind a transaction-mode proxy: about **53** server connections (default_pool_size / max_db_connections) cover peak x burst with P(wait) under 1.0%; keep client pools as above and set max_client_conn above the client total.
Or raise max_connections to at least 457 (check RAM: ~10 MB per connection).

Model: Little's law for busy connections, Erlang C (M/M/c) per process with load spread evenly; peak load x burst factor. Validate with the pool's checked-out and wait metrics.
FILE:examples/shop-pools-pgbouncer.json
{
  "database": {
    "name": "shop-orders (Postgres 16, 8 vCPU / 32 GB)",
    "engine": "postgres",
    "max_connections": 200,
    "reserved_connections": 3,
    "other_connections": 12,
    "cpu_cores": 8,
    "ram_gb": 32,
    "mb_per_connection": 10
  },
  "proxy": {
    "type": "pgbouncer",
    "pool_mode": "transaction",
    "instances": 2,
    "default_pool_size": 27,
    "max_db_connections": 27,
    "reserve_pool_size": 3,
    "max_client_conn": 1000
  },
  "deploy": {"surge_percent": 25},
  "settings": {"target_wait_probability": 0.01, "burst_factor": 1.3},
  "services": [
    {
      "name": "api",
      "via_proxy": true,
      "instances": 6,
      "max_instances": 12,
      "processes": 4,
      "threads": 8,
      "pool_size": 5,
      "max_overflow": 0,
      "peak_rps": 1200,
      "conn_hold_ms": 15,
      "pool_timeout_ms": 2000,
      "request_timeout_ms": 10000
    },
    {
      "name": "celery-worker",
      "via_proxy": true,
      "instances": 4,
      "max_instances": 8,
      "processes": 8,
      "threads": 1,
      "pool_size": 1,
      "max_overflow": 0,
      "peak_rps": 40,
      "conn_hold_ms": 200
    },
    {
      "name": "admin",
      "instances": 2,
      "processes": 2,
      "threads": 4,
      "pool_size": 2,
      "max_overflow": 0,
      "peak_rps": 5,
      "conn_hold_ms": 30
    },
    {
      "name": "nightly-reports",
      "instances": 1,
      "processes": 1,
      "threads": 4,
      "pool_size": 4,
      "max_overflow": 0,
      "busy_connections": 2
    }
  ]
}
FILE:scripts/pool_sizer.py
#!/usr/bin/env python3
"""Database connection pool sizing calculator (Python 3.8+ standard library only).

Reads a JSON description of a database, an optional PgBouncer-style proxy and the services
that connect to it, then answers:
  - worst-case open connections at normal size, at autoscaling maximum and during a rolling
    deploy surge, against what the database really allows (max_connections - reserved - other)
  - how many connections are actually busy at peak (Little's law: rate x hold time)
  - per process: chance a request waits for a free connection and the mean wait (Erlang C),
    and the smallest pool that keeps waiting under the target
  - database side: busy connections against the CPU sweet spot (cores x 2 + 1), memory used by
    open connections, proxy client/server limits and pool-mode caveats
  - a recommended pool_size / max_overflow per service and, when direct connections cannot fit,
    proxy settings that do

Input (JSON):
  {"database": {"name": "orders-db", "engine": "postgres", "max_connections": 200,
                "reserved_connections": 3, "other_connections": 12, "cpu_cores": 8,
                "ram_gb": 32, "mb_per_connection": 10},
   "proxy": {"type": "pgbouncer", "pool_mode": "transaction", "default_pool_size": 30,
             "max_db_connections": 60, "max_client_conn": 2000, "instances": 1},      (optional)
   "deploy": {"surge_percent": 25},
   "settings": {"target_wait_probability": 0.01, "burst_factor": 1.3},                (optional)
   "services": [{"name": "api", "instances": 6, "max_instances": 12, "processes": 4,
                 "threads": 8, "pool_size": 10, "max_overflow": 20, "peak_rps": 1200,
                 "conn_hold_ms": 15, "pool_timeout_ms": 30000, "request_timeout_ms": 10000,
                 "via_proxy": false, "async": false}]}
  conn_hold_ms = how long one request/job keeps a connection checked out (session-per-request
  frameworks hold it for the whole request); or give queries_per_request + avg_query_ms.
  For jobs without a rate, give busy_connections (average connections in use).

Usage:
  python3 pool_sizer.py pools.json [--set api.max_instances=16] [--json]
  python3 pool_sizer.py --self-test

Exit codes: 0 = no HIGH finding, 1 = HIGH findings (limits exceeded, pools that queue),
2 = bad input.
"""
import argparse
import copy
import json
import math
import sys

SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
DEFAULTS = {"target_wait_probability": 0.01, "burst_factor": 1.3, "max_utilization": 0.7}
ENGINE_MB = {"postgres": 10, "postgresql": 10, "mysql": 3, "mariadb": 3}


class InputError(ValueError):
    pass


def erlang_c(c, a):
    """Probability that an arrival waits (all c servers busy), offered load a Erlangs."""
    if c <= 0:
        return 1.0
    if a <= 0:
        return 0.0
    if a >= c:
        return 1.0
    b = 1.0
    for k in range(1, c + 1):
        b = a * b / (k + a * b)
    return c * b / (c - a * (1 - b))


def mean_wait_ms(c, a, hold_ms):
    """Mean wait for a connection in ms (M/M/c)."""
    if a <= 0:
        return 0.0
    if a >= c:
        return float("inf")
    return erlang_c(c, a) * hold_ms / (c - a)


def smallest_pool(a, target, max_util, cap=None, floor=1):
    c = max(floor, 1)
    while True:
        if erlang_c(c, a) <= target and a / c <= max_util:
            return c if cap is None else min(c, cap)
        if cap is not None and c >= cap:
            return cap
        c += 1
        if c > 10000:
            return c


def num(d, key, default=None, kind=float, minimum=0, where=""):
    v = d.get(key, default)
    if v is None:
        return None
    if isinstance(v, bool) or not isinstance(v, (int, float)):
        raise InputError("%s%s must be a number, got %r" % (where, key, v))
    if v < minimum:
        raise InputError("%s%s must be >= %s, got %r" % (where, key, minimum, v))
    return kind(v)


def apply_set(cfg, expr):
    """--set api.max_instances=16 | database.max_connections=300 | proxy.default_pool_size=40"""
    if "=" not in expr or "." not in expr.split("=")[0]:
        raise InputError("--set needs <service|database|proxy|deploy|settings>.<key>=<value>, got %r" % expr)
    path, val = expr.split("=", 1)
    target, key = path.split(".", 1)
    try:
        value = json.loads(val)
    except ValueError:
        value = val
    if target in ("database", "proxy", "deploy", "settings"):
        cfg.setdefault(target, {})[key] = value
        return
    for s in cfg.get("services", []):
        if s.get("name") == target:
            s[key] = value
            return
    raise InputError("--set: no service named %r" % target)


class Plan:
    def __init__(self, cfg):
        self.cfg = cfg
        self.findings = []
        self.settings = dict(DEFAULTS)
        self.settings.update(cfg.get("settings") or {})
        self._db()
        self._proxy()
        self.surge = num(cfg.get("deploy") or {}, "surge_percent", 25, where="deploy.") / 100.0
        self.services = [self._service(s, i) for i, s in enumerate(cfg.get("services") or [])]
        if not self.services:
            raise InputError("no services given")
        names = [s["name"] for s in self.services]
        if len(set(names)) != len(names):
            raise InputError("service names must be unique")
        self._totals()
        self._check()

    def add(self, code, sev, where, what, fix):
        self.findings.append({"code": code, "sev": sev, "where": where, "what": what, "fix": fix})

    def _db(self):
        d = self.cfg.get("database")
        if not isinstance(d, dict):
            raise InputError("missing 'database' object")
        engine = str(d.get("engine", "postgres")).lower()
        self.db = {
            "name": d.get("name", "database"),
            "engine": engine,
            "max_connections": num(d, "max_connections", 100 if engine.startswith("postgres") else 151, int, 1, "database."),
            "reserved": num(d, "reserved_connections", 3 if engine.startswith("postgres") else 0, int, 0, "database."),
            "other": num(d, "other_connections", 0, int, 0, "database."),
            "cores": num(d, "cpu_cores", None, float, 0.5, "database."),
            "ram_gb": num(d, "ram_gb", None, float, 0.1, "database."),
            "mb_per_conn": num(d, "mb_per_connection", ENGINE_MB.get(engine, 10), float, 0, "database."),
            "spindles": num(d, "effective_spindles", 1, float, 0, "database."),
        }
        self.db["available"] = self.db["max_connections"] - self.db["reserved"] - self.db["other"]
        if self.db["available"] <= 0:
            raise InputError("database: reserved + other connections leave nothing for the services")

    def _proxy(self):
        p = self.cfg.get("proxy")
        if not p:
            self.proxy = None
            return
        mode = str(p.get("pool_mode", "transaction")).lower()
        if mode not in ("session", "transaction", "statement"):
            raise InputError("proxy.pool_mode must be session, transaction or statement")
        inst = num(p, "instances", 1, int, 1, "proxy.")
        pool = num(p, "default_pool_size", 20, int, 1, "proxy.")
        pairs = num(p, "db_user_pairs", 1, int, 1, "proxy.")
        maxdb = num(p, "max_db_connections", 0, int, 0, "proxy.")
        per_inst = pool * pairs if not maxdb else min(pool * pairs, maxdb)
        self.proxy = {"type": p.get("type", "pgbouncer"), "mode": mode, "instances": inst,
                      "default_pool_size": pool, "pairs": pairs, "max_db_connections": maxdb,
                      "reserve_pool_size": num(p, "reserve_pool_size", 0, int, 0, "proxy."),
                      "max_client_conn": num(p, "max_client_conn", 100, int, 1, "proxy."),
                      "server_conns": (per_inst + num(p, "reserve_pool_size", 0, int, 0, "proxy.")) * inst}

    def _service(self, s, i):
        w = "services[%d]." % i
        name = s.get("name") or "service%d" % (i + 1)
        r = {"name": name, "via_proxy": bool(s.get("via_proxy", False)), "async": bool(s.get("async", False))}
        r["instances"] = num(s, "instances", 1, int, 0, w)
        r["max_instances"] = max(num(s, "max_instances", r["instances"], int, 0, w), r["instances"])
        r["processes"] = num(s, "processes", 1, int, 1, w)
        r["threads"] = num(s, "threads", None, int, 1, w)
        r["pool_size"] = num(s, "pool_size", 5, int, 0, w)
        r["max_overflow"] = num(s, "max_overflow", 0, int, 0, w)
        r["pool_max"] = r["pool_size"] + r["max_overflow"]
        if r["pool_max"] < 1:
            raise InputError("%s: pool_size + max_overflow must be at least 1" % name)
        r["pool_timeout_ms"] = num(s, "pool_timeout_ms", 30000, float, 0, w)
        r["request_timeout_ms"] = num(s, "request_timeout_ms", None, float, 1, w)
        hold = num(s, "conn_hold_ms", None, float, 0, w)
        if hold is None and s.get("queries_per_request") is not None:
            hold = num(s, "queries_per_request", 0, float, 0, w) * num(s, "avg_query_ms", 0, float, 0, w) \
                + num(s, "overhead_ms", 1, float, 0, w)
        r["hold_ms"] = hold
        r["rps"] = num(s, "peak_rps", None, float, 0, w)
        r["busy_given"] = num(s, "busy_connections", None, float, 0, w)
        procs_now = r["instances"] * r["processes"]
        procs_max = r["max_instances"] * r["processes"]
        r["procs_now"], r["procs_max"] = procs_now, procs_max
        r["procs_surge"] = int(math.ceil(r["max_instances"] * (1 + self.surge))) * r["processes"] if r["max_instances"] else 0
        r["open_steady"] = procs_now * r["pool_size"]
        r["open_now"] = procs_now * r["pool_max"]
        r["open_max"] = procs_max * r["pool_max"]
        r["open_surge"] = r["procs_surge"] * r["pool_max"]
        burst = float(self.settings["burst_factor"])
        if r["rps"] is not None and r["hold_ms"] is not None:
            r["busy"] = r["rps"] * r["hold_ms"] / 1000.0
            r["load_source"] = "%g rps x %g ms" % (r["rps"], r["hold_ms"])
        elif r["busy_given"] is not None:
            r["busy"] = r["busy_given"]
            r["load_source"] = "given"
        else:
            r["busy"] = None
            r["load_source"] = "unknown"
        if r["busy"] is not None and procs_now:
            r["a_proc"] = r["busy"] * burst / procs_now  # offered load per process at peak x burst
            cap = r["threads"] if (r["threads"] and not r["async"]) else None
            hold_ms = r["hold_ms"] if r["hold_ms"] else 100.0
            # a sync process never runs more than `threads` queries at once: with pool >= threads
            # no request ever waits for a connection (it waits for a thread instead)
            r["p_wait"] = self.conn_wait(r["pool_max"], r["a_proc"], cap)
            r["wait_ms"] = 0.0 if (cap and r["pool_max"] >= cap) else mean_wait_ms(r["pool_max"], r["a_proc"], hold_ms)
            floor = min(2, cap) if cap else 2
            rec = smallest_pool(r["a_proc"], float(self.settings["target_wait_probability"]),
                                float(self.settings["max_utilization"]), cap, floor)
            r["rec_pool"] = rec
            r["rec_p_wait"] = self.conn_wait(rec, r["a_proc"], cap)
        else:
            r["a_proc"] = r["p_wait"] = r["wait_ms"] = r["rec_pool"] = r["rec_p_wait"] = None
        return r

    @staticmethod
    def conn_wait(pool, a, cap):
        if cap and pool >= cap:
            return 0.0
        return erlang_c(pool, a)

    def _totals(self):
        direct = [s for s in self.services if not s["via_proxy"]]
        proxied = [s for s in self.services if s["via_proxy"]]
        t = {}
        for k in ("open_steady", "open_now", "open_max", "open_surge"):
            t["direct_" + k] = sum(s[k] for s in direct)
            t["proxied_" + k] = sum(s[k] for s in proxied)
        server = self.proxy["server_conns"] if (self.proxy and proxied) else 0
        t["proxy_server"] = server
        for k in ("open_now", "open_max", "open_surge"):
            t["db_" + k] = t["direct_" + k] + server
        t["busy"] = sum(s["busy"] or 0 for s in self.services)
        t["busy_proxied"] = sum(s["busy"] or 0 for s in proxied)
        t["unknown_load"] = [s["name"] for s in self.services if s["busy"] is None]
        if self.db["cores"]:
            t["sweet_spot"] = int(round(self.db["cores"] * 2 + self.db["spindles"]))
        else:
            t["sweet_spot"] = None
        self.t = t

    def _check(self):
        db, t = self.db, self.t
        avail = db["available"]
        label = "%s (max_connections %d - reserved %d - other %d = %d usable)" % (
            db["name"], db["max_connections"], db["reserved"], db["other"], avail)
        if t["db_open_now"] > avail:
            self.add("POOL_EXCEEDS_DB_LIMIT", "HIGH", label,
                     "pools can open %d connections at today's size; the database accepts %d - new connections "
                     "fail with 'too many clients' / 'Too many connections' under load" % (t["db_open_now"], avail),
                     "shrink pools to the recommendation below, drop max_overflow, or put the services behind a proxy")
        elif t["db_open_max"] > avail:
            self.add("AUTOSCALE_EXCEEDS_DB_LIMIT", "HIGH", label,
                     "fine today (%d) but %d at autoscaling maximum - the scale-out that should save a traffic "
                     "spike is what exhausts the database" % (t["db_open_now"], t["db_open_max"]),
                     "size pools for max_instances, cap max_instances, or use a proxy")
        elif t["db_open_surge"] > avail:
            self.add("DEPLOY_SURGE_EXCEEDS_LIMIT", "HIGH", label,
                     "a rolling deploy at maximum scale runs %d%% extra pods for a while: %d connections > %d"
                     % (round(self.surge * 100), t["db_open_surge"], avail),
                     "lower maxSurge, deploy at lower scale, or leave this much headroom")
        elif t["db_open_surge"] > 0.9 * avail:
            self.add("LOW_CONNECTION_HEADROOM", "MEDIUM", label,
                     "deploy surge at max scale reaches %d of %d usable (%.0f%%)" % (
                         t["db_open_surge"], avail, 100.0 * t["db_open_surge"] / avail),
                     "keep at least 10% free for admin sessions, migrations and failover")
        if t["sweet_spot"] and t["busy"] > t["sweet_spot"]:
            sev = "HIGH" if t["busy"] > 2 * t["sweet_spot"] else "MEDIUM"
            self.add("DB_ACTIVE_OVER_CORES", sev, db["name"],
                     "about %.1f connections are busy at peak but %g cores run about %d at once efficiently "
                     "(cores x 2 + spindles); the rest queue inside the database" % (t["busy"], db["cores"], t["sweet_spot"]),
                     "cut connection hold time (shorter transactions, no network calls inside them), add read "
                     "replicas, or scale the database")
        open_max = t["db_open_max"]
        if t["sweet_spot"] and open_max > max(5 * t["sweet_spot"], 100):
            self.add("TOO_MANY_CONNECTIONS_FOR_CORES", "MEDIUM", db["name"],
                     "up to %d open connections for %g cores (%.0f per core); idle backends cost memory, "
                     "snapshot and lock-table overhead" % (open_max, db["cores"], open_max / db["cores"]),
                     "fewer, busier connections: smaller pools or a transaction-mode proxy")
        if db["ram_gb"] and db["mb_per_conn"]:
            mem = open_max * db["mb_per_conn"] / 1024.0
            if mem > 0.25 * db["ram_gb"]:
                self.add("CONNECTION_MEMORY", "MEDIUM", db["name"],
                         "%d connections x ~%g MB = %.1f GB of %g GB RAM before work_mem/sort buffers"
                         % (open_max, db["mb_per_conn"], mem, db["ram_gb"]),
                         "reduce open connections; keep that memory for cache (shared_buffers / page cache)")
        if self.proxy:
            self._check_proxy()
        elif t["db_open_max"] > avail and t["busy"] and t["busy"] * 3 < avail:
            self.add("PROXY_RECOMMENDED", "MEDIUM", db["name"],
                     "only ~%.0f connections are busy at peak but up to %d are open; a transaction-mode "
                     "pooler multiplexes them" % (t["busy"], t["db_open_max"]),
                     "PgBouncer/RDS Proxy/ProxySQL in front of the database (see recommendation)")
        for s in self.services:
            self._check_service(s)

    def _check_proxy(self):
        p, t = self.proxy, self.t
        proxied = [s for s in self.services if s["via_proxy"]]
        if not proxied:
            self.add("PROXY_UNUSED", "LOW", p["type"], "a proxy is configured but no service has via_proxy true",
                     "set via_proxy on the services that use it")
            return
        per_inst_clients = int(math.ceil(t["proxied_open_surge"] / float(p["instances"])))
        if per_inst_clients > p["max_client_conn"]:
            self.add("PROXY_CLIENT_LIMIT", "HIGH", p["type"],
                     "proxied pools can open %d client connections per proxy instance (deploy surge, max scale) "
                     "but max_client_conn is %d" % (per_inst_clients, p["max_client_conn"]),
                     "raise max_client_conn (cheap: ~2 kB each) or shrink the client pools")
        if p["mode"] == "session" and t["proxied_open_max"] > p["server_conns"]:
            self.add("PROXY_SESSION_MODE", "MEDIUM", p["type"],
                     "session mode pins one server connection per client connection, so %d client connections "
                     "cannot share %d server connections" % (t["proxied_open_max"], p["server_conns"]),
                     "use transaction mode (check the caveats) or size like direct connections")
        if p["mode"] in ("transaction", "statement"):
            self.add("PROXY_TRANSACTION_MODE_CAVEATS", "LOW", p["type"],
                     "%s mode: session state does not survive between transactions (SET, temp tables, advisory "
                     "locks, LISTEN/NOTIFY, WITH HOLD cursors; prepared statements need PgBouncer 1.21+ "
                     "max_prepared_statements)" % p["mode"],
                     "run migrations and session-level jobs on a direct connection")
        busy = t["busy_proxied"] * float(self.settings["burst_factor"])
        hold = [s["hold_ms"] for s in proxied if s["hold_ms"]]
        if busy:
            pw = erlang_c(p["server_conns"], busy)
            if busy >= p["server_conns"] or pw > 0.2:
                self.add("PROXY_POOL_TOO_SMALL", "HIGH", p["type"],
                         "~%.1f server connections busy at peak x burst but only %d exist (P(wait) %.0f%%)"
                         % (busy, p["server_conns"], 100 * pw),
                         "raise default_pool_size/max_db_connections to %d" % smallest_pool(
                             busy, float(self.settings["target_wait_probability"]), float(self.settings["max_utilization"])))
            elif pw > float(self.settings["target_wait_probability"]) * 5:
                self.add("PROXY_POOL_TIGHT", "MEDIUM", p["type"],
                         "P(wait) for a server connection at peak x burst is %.1f%%" % (100 * pw),
                         "add a reserve_pool_size of a few connections or raise default_pool_size")
        if hold and max(hold) > 1000 and p["mode"] != "session":
            self.add("LONG_HOLD_THROUGH_PROXY", "LOW", p["type"],
                     "a proxied service holds connections for %g ms; long transactions block multiplexing" % max(hold),
                     "move long reports/batches to their own pool or a replica")

    def _check_service(self, s):
        n = s["name"]
        if s["busy"] is None:
            self.add("MISSING_LOAD_DATA", "LOW", n, "no peak_rps + conn_hold_ms (or busy_connections): pool size "
                     "cannot be checked against load", "measure: pool checked-out gauge or rps x average hold time")
            return
        if s["procs_now"] and s["a_proc"] >= s["pool_max"]:
            self.add("POOL_TOO_SMALL", "HIGH", n,
                     "each process needs ~%.2f connections at peak x burst but its pool max is %d: requests queue "
                     "until pool_timeout (%g ms)" % (s["a_proc"], s["pool_max"], s["pool_timeout_ms"]),
                     "pool %d per process (or more processes)" % s["rec_pool"])
        elif s["p_wait"] is not None and s["p_wait"] > 0.2:
            self.add("POOL_TOO_SMALL", "HIGH", n, "P(wait for a connection) %.0f%% at peak x burst, mean wait %.1f ms"
                     % (100 * s["p_wait"], s["wait_ms"]), "pool %d per process" % s["rec_pool"])
        elif s["p_wait"] is not None and s["p_wait"] > max(0.05, float(self.settings["target_wait_probability"])):
            self.add("POOL_TIGHT", "MEDIUM", n, "P(wait for a connection) %.1f%% at peak x burst" % (100 * s["p_wait"]),
                     "pool %d per process" % s["rec_pool"])
        if s["threads"] and not s["async"] and s["a_proc"] > 0.7 * s["threads"]:
            self.add("WORKERS_SATURATED", "HIGH" if s["a_proc"] >= s["threads"] else "MEDIUM", n,
                     "each process spends ~%.2f of its %d thread(s) inside the database at peak x burst; requests "
                     "queue for a thread, and a bigger pool cannot help" % (s["a_proc"], s["threads"]),
                     "shorter connection hold time, or more processes/threads (then re-check the database totals)")
        if s["threads"] and not s["async"] and s["pool_max"] > s["threads"]:
            self.add("POOL_LARGER_THAN_CONCURRENCY", "MEDIUM", n,
                     "pool max %d per process but only %d thread(s) can use a connection at a time; the extra "
                     "%d can never be used" % (s["pool_max"], s["threads"], s["pool_max"] - s["threads"]),
                     "pool_size <= threads; max_overflow 0")
        elif s["rec_pool"] and s["pool_max"] > max(2 * s["rec_pool"], s["rec_pool"] + 5):
            self.add("POOL_OVERSIZED", "LOW", n, "pool max %d per process where %d keeps P(wait) under %.0f%%"
                     % (s["pool_max"], s["rec_pool"], 100 * float(self.settings["target_wait_probability"])),
                     "pool %d per process frees %d connections at max scale"
                     % (s["rec_pool"], (s["pool_max"] - s["rec_pool"]) * s["procs_max"]))
        if s["max_overflow"] and s["max_overflow"] >= s["pool_size"] and s["procs_max"] > 4:
            self.add("LARGE_OVERFLOW", "LOW", n, "max_overflow %d >= pool_size %d across %d processes makes the "
                     "worst case %d instead of %d" % (s["max_overflow"], s["pool_size"], s["procs_max"],
                                                       s["open_max"], s["procs_max"] * s["pool_size"]),
                     "fixed pools (max_overflow 0) keep the total predictable")
        if s["request_timeout_ms"] and s["pool_timeout_ms"] >= s["request_timeout_ms"]:
            self.add("POOL_TIMEOUT_LONG", "LOW", n, "pool_timeout %g ms >= request timeout %g ms: a request can time "
                     "out while still waiting for a connection" % (s["pool_timeout_ms"], s["request_timeout_ms"]),
                     "pool_timeout around 10-30% of the request timeout so overload fails fast")

    def recommendation(self):
        rec = []
        tot_max = tot_surge = 0
        for s in self.services:
            p = s["rec_pool"] if s["rec_pool"] is not None else s["pool_max"]
            o_max = s["procs_max"] * p
            o_surge = s["procs_surge"] * p
            rec.append({"name": s["name"], "pool_size": p, "max_overflow": 0, "per_process_p_wait": s["rec_p_wait"],
                        "open_max": o_max, "open_surge": o_surge, "via_proxy": s["via_proxy"]})
            if not s["via_proxy"]:
                tot_max += o_max
                tot_surge += o_surge
        server = self.t["proxy_server"]
        need = tot_surge + server
        out = {"services": rec, "direct_open_surge": tot_surge, "proxy_server": server, "db_needed": need,
               "fits": need <= self.db["available"]}
        busy = self.t["busy"] * float(self.settings["burst_factor"])
        if busy:
            out["proxy_pool_if_all_proxied"] = smallest_pool(busy, float(self.settings["target_wait_probability"]),
                                                             float(self.settings["max_utilization"]))
        out["max_connections_needed"] = int(math.ceil((need + self.db["reserved"] + self.db["other"]) * 1.1))
        return out

    def worst(self):
        return min((f["sev"] for f in self.findings), key=lambda x: SEV_ORDER[x]) if self.findings else None


def f1(x):
    if x is None:
        return "-"
    if x == float("inf"):
        return "inf"
    return ("%.2f" % x).rstrip("0").rstrip(".") if abs(x) < 100 else "%.0f" % x


def pct(x):
    if x is None:
        return "-"
    return "%.1f%%" % (100 * x) if x >= 0.001 else ("<0.1%" if x > 0 else "0%")


def render(pl):
    db, t, p = pl.db, pl.t, pl.proxy
    w = pl.worst()
    o = ["# Connection pool sizing report", ""]
    o.append("Database: **%s** (%s), max_connections %d, reserved %d, other %d -> **%d usable**%s" % (
        db["name"], db["engine"], db["max_connections"], db["reserved"], db["other"], db["available"],
        (", %g cores (sweet spot ~%d active)" % (db["cores"], t["sweet_spot"])) if db["cores"] else ""))
    if p:
        o.append("Proxy: %s, %s mode, %d instance(s), %d server connections to the database, max_client_conn %d"
                 % (p["type"], p["mode"], p["instances"], p["server_conns"], p["max_client_conn"]))
    o.append("Deploy surge: %d%%   Burst factor: %g   Target P(wait): %s" % (
        round(pl.surge * 100), float(pl.settings["burst_factor"]), pct(float(pl.settings["target_wait_probability"]))))
    o.append("")
    if not w:
        o.append("**Verdict: OK** - pools fit the database at max scale and deploy surge, and requests rarely wait.")
    else:
        n = {s: sum(1 for f in pl.findings if f["sev"] == s) for s in SEV_ORDER}
        o.append("**Verdict: %s** - %d HIGH, %d MEDIUM, %d LOW finding(s)." % (
            {"HIGH": "will run out of connections or queue", "MEDIUM": "works, but wasteful or tight",
             "LOW": "fine, minor tuning"}[w], n["HIGH"], n["MEDIUM"], n["LOW"]))
    o += ["", "## Services", "",
          "| Service | Path | Procs now / max | Pool (size+overflow) | Busy at peak | Per proc load | P(wait) | Mean wait | Open now | Open max | Deploy surge |",
          "|---|---|---|---|---|---|---|---|---|---|---|"]
    for s in pl.services:
        o.append("| %s | %s | %d / %d | %d+%d | %s (%s) | %s | %s | %s | %d | %d | %d |" % (
            s["name"], "proxy" if s["via_proxy"] else "direct", s["procs_now"], s["procs_max"], s["pool_size"],
            s["max_overflow"], f1(s["busy"]), s["load_source"], f1(s["a_proc"]), pct(s["p_wait"]),
            ("%s ms" % f1(s["wait_ms"])) if s["wait_ms"] is not None else "-", s["open_now"], s["open_max"], s["open_surge"]))
    o += ["", "## Totals against the database", "", "| Scenario | Direct | Via proxy (server side) | Database total | Usable | Headroom |", "|---|---|---|---|---|---|"]
    for k, lab in (("open_now", "today"), ("open_max", "autoscaling max"), ("open_surge", "max + deploy surge")):
        tot = t["db_" + k]
        o.append("| %s | %d | %d | %d | %d | %d |" % (lab, t["direct_" + k], t["proxy_server"], tot,
                                                       db["available"], db["available"] - tot))
    o.append("")
    o.append("Busy at peak (all services): ~%s connections%s." % (
        f1(t["busy"]), (" vs ~%d the CPUs run efficiently" % t["sweet_spot"]) if t["sweet_spot"] else ""))
    if t["unknown_load"]:
        o.append("No load data for: %s." % ", ".join(t["unknown_load"]))
    if pl.findings:
        o += ["", "## Findings", "", "| Sev | Code | Where | What | Fix |", "|---|---|---|---|---|"]
        for f in sorted(pl.findings, key=lambda f: SEV_ORDER[f["sev"]]):
            o.append("| %s | %s | %s | %s | %s |" % (f["sev"], f["code"], f["where"], f["what"].replace("|", "/"), f["fix"]))
    r = pl.recommendation()
    o += ["", "## Recommended pools", "", "| Service | pool_size | max_overflow | P(wait) per proc | Open at max | With deploy surge |", "|---|---|---|---|---|---|"]
    for s in r["services"]:
        o.append("| %s | %d | 0 | %s | %d | %d%s |" % (s["name"], s["pool_size"], pct(s["per_process_p_wait"]),
                                                       s["open_max"], s["open_surge"], " (client side, via proxy)" if s["via_proxy"] else ""))
    o.append("")
    o.append("Database connections needed with these pools at max scale + deploy surge: **%d** (direct %d + proxy %d) "
             "of %d usable -> %s." % (r["db_needed"], r["direct_open_surge"], r["proxy_server"], db["available"],
                                      "fits" if r["fits"] else "still does NOT fit"))
    if not r["fits"]:
        if r.get("proxy_pool_if_all_proxied"):
            o.append("")
            o.append("Direct connections cannot fit. Put the services behind a transaction-mode proxy: about "
                     "**%d** server connections (default_pool_size / max_db_connections) cover peak x burst with "
                     "P(wait) under %s; keep client pools as above and set max_client_conn above the client total."
                     % (r["proxy_pool_if_all_proxied"], pct(float(pl.settings["target_wait_probability"]))))
        o.append("Or raise max_connections to at least %d (check RAM: ~%g MB per connection)." % (
            r["max_connections_needed"], db["mb_per_conn"]))
    o += ["", "Model: Little's law for busy connections, Erlang C (M/M/c) per process with load spread evenly; "
          "peak load x burst factor. Validate with the pool's checked-out and wait metrics."]
    return "\n".join(o) + "\n"


def as_json(pl):
    def clean(x):
        if isinstance(x, float) and (math.isinf(x) or math.isnan(x)):
            return None
        if isinstance(x, dict):
            return {k: clean(v) for k, v in x.items()}
        if isinstance(x, list):
            return [clean(v) for v in x]
        return x
    return clean({"database": pl.db, "proxy": pl.proxy, "totals": pl.t, "services": pl.services,
                  "findings": pl.findings, "worst": pl.worst(), "recommendation": pl.recommendation()})


def self_test():
    cases = []

    def case(name, fn):
        try:
            ok, err = bool(fn()), ""
        except Exception as e:  # noqa
            ok, err = False, repr(e)
        cases.append((name, ok, err))

    def cfg(**kw):
        c = {"database": {"name": "db", "max_connections": 100, "reserved_connections": 3, "other_connections": 7,
                          "cpu_cores": 4, "ram_gb": 16},
             "deploy": {"surge_percent": 25},
             "services": [{"name": "api", "instances": 2, "max_instances": 4, "processes": 2, "threads": 4,
                           "pool_size": 4, "max_overflow": 0, "peak_rps": 200, "conn_hold_ms": 10}]}
        for k, v in kw.items():
            if k == "svc":
                c["services"][0].update(v)
            elif k == "services":
                c["services"] = v
            else:
                c.setdefault(k, {}).update(v)
        return c

    def codes(c):
        return {f["code"]: f["sev"] for f in Plan(c).findings}

    def raises(fn):
        try:
            fn()
        except InputError:
            return True
        return False

    case("erlang C one server equals load", lambda: abs(erlang_c(1, 0.5) - 0.5) < 1e-9)
    case("erlang C textbook value (c=2, a=1 -> 1/3)", lambda: abs(erlang_c(2, 1.0) - 1 / 3.0) < 1e-9)
    case("erlang C saturated -> 1", lambda: erlang_c(3, 3.0) == 1.0 and erlang_c(3, 5) == 1.0)
    case("erlang C decreases with servers", lambda: erlang_c(5, 3) > erlang_c(6, 3) > erlang_c(8, 3))
    case("mean wait M/M/1", lambda: abs(mean_wait_ms(1, 0.5, 10) - 10.0) < 1e-9)
    case("smallest pool meets target", lambda: (lambda c: erlang_c(c, 2.0) <= 0.01 and erlang_c(c - 1, 2.0) > 0.01 or 2.0 / (c - 1) > 0.7)(smallest_pool(2.0, 0.01, 0.7)))
    case("smallest pool capped by threads", lambda: smallest_pool(9.0, 0.01, 0.7, cap=4) == 4)
    case("usable connections", lambda: Plan(cfg()).db["available"] == 90)
    case("open totals", lambda: (lambda p: (p.t["db_open_now"], p.t["db_open_max"], p.t["db_open_surge"]) == (16, 32, 40))(Plan(cfg())))
    case("busy via Little's law", lambda: abs(Plan(cfg()).services[0]["busy"] - 2.0) < 1e-9)
    case("queries x avg ms as hold", lambda: abs(Plan(cfg(svc={"conn_hold_ms": None, "queries_per_request": 4, "avg_query_ms": 2, "overhead_ms": 2})).services[0]["hold_ms"] - 10) < 1e-9)
    case("healthy config has no HIGH", lambda: Plan(cfg()).worst() in (None, "LOW", "MEDIUM"))
    case("exceeds limit today", lambda: codes(cfg(svc={"pool_size": 30}))["POOL_EXCEEDS_DB_LIMIT"] == "HIGH")
    case("autoscale exceeds", lambda: "AUTOSCALE_EXCEEDS_DB_LIMIT" in codes(cfg(svc={"max_instances": 12})))
    case("deploy surge exceeds", lambda: "DEPLOY_SURGE_EXCEEDS_LIMIT" in codes(cfg(svc={"max_instances": 11, "pool_size": 4}, deploy={"surge_percent": 50})))
    case("pool too small", lambda: codes(cfg(svc={"pool_size": 1, "peak_rps": 400}))["POOL_TOO_SMALL"] == "HIGH")
    case("pool larger than threads", lambda: "POOL_LARGER_THAN_CONCURRENCY" in codes(cfg(svc={"pool_size": 10, "threads": 2})))
    case("pool >= threads never waits for a connection", lambda: Plan(cfg(svc={"pool_size": 1, "threads": 1, "peak_rps": 20})).services[0]["p_wait"] == 0.0)
    case("workers saturated", lambda: codes(cfg(svc={"threads": 1, "pool_size": 1, "peak_rps": 400}))["WORKERS_SATURATED"] == "HIGH")
    case("async services not capped by threads", lambda: "POOL_LARGER_THAN_CONCURRENCY" not in codes(cfg(svc={"pool_size": 10, "threads": 1, "async": True})))
    case("large overflow flagged", lambda: "LARGE_OVERFLOW" in codes(cfg(svc={"pool_size": 2, "max_overflow": 2, "threads": 8})))
    case("pool timeout longer than request", lambda: "POOL_TIMEOUT_LONG" in codes(cfg(svc={"pool_timeout_ms": 30000, "request_timeout_ms": 10000})))
    case("db over cores", lambda: "DB_ACTIVE_OVER_CORES" in codes(cfg(svc={"peak_rps": 1500, "pool_size": 4, "threads": 8, "max_instances": 2}, database={"max_connections": 500})))
    case("connection memory", lambda: "CONNECTION_MEMORY" in codes(cfg(database={"ram_gb": 0.5})))
    case("missing load data", lambda: "MISSING_LOAD_DATA" in codes(cfg(svc={"peak_rps": None})))
    case("busy_connections accepted", lambda: Plan(cfg(svc={"peak_rps": None, "busy_connections": 1.5})).services[0]["busy"] == 1.5)
    case("proxy server conns counted, not clients", lambda: (lambda p: p.t["db_open_max"] == 20)(Plan(cfg(svc={"via_proxy": True}, proxy={"pool_mode": "transaction", "default_pool_size": 20, "max_client_conn": 1000}))))
    case("max_db_connections caps proxy", lambda: Plan(cfg(svc={"via_proxy": True}, proxy={"default_pool_size": 50, "max_db_connections": 30})).proxy["server_conns"] == 30)
    case("proxy client limit", lambda: "PROXY_CLIENT_LIMIT" in codes(cfg(svc={"via_proxy": True, "max_instances": 40}, proxy={"max_client_conn": 100})))
    case("proxy pool too small", lambda: "PROXY_POOL_TOO_SMALL" in codes(cfg(svc={"via_proxy": True, "peak_rps": 1000}, proxy={"default_pool_size": 5, "max_client_conn": 1000})))
    case("session mode warning", lambda: "PROXY_SESSION_MODE" in codes(cfg(svc={"via_proxy": True, "max_instances": 10}, proxy={"pool_mode": "session", "default_pool_size": 10, "max_client_conn": 1000})))
    case("proxy recommended when direct cannot fit", lambda: "PROXY_RECOMMENDED" in codes(cfg(svc={"max_instances": 30, "pool_size": 4})))
    case("--set overrides service", lambda: (lambda c: (apply_set(c, "api.max_instances=9"), c["services"][0]["max_instances"] == 9)[1])(cfg()))
    case("--set overrides database", lambda: (lambda c: (apply_set(c, "database.max_connections=300"), Plan(c).db["max_connections"] == 300)[1])(cfg()))
    case("--set unknown service rejected", lambda: raises(lambda: apply_set(cfg(), "nope.pool_size=3")))
    case("bad number rejected", lambda: raises(lambda: Plan(cfg(svc={"pool_size": "ten"}))))
    case("no services rejected", lambda: raises(lambda: Plan(cfg(services=[]))))
    case("duplicate names rejected", lambda: raises(lambda: Plan(cfg(services=[{"name": "a"}, {"name": "a"}]))))
    case("reserved too large rejected", lambda: raises(lambda: Plan(cfg(database={"reserved_connections": 100}))))
    case("recommendation fits for sane config", lambda: Plan(cfg()).recommendation()["fits"])
    case("render has tables", lambda: "## Recommended pools" in render(Plan(cfg())) and "## Totals against the database" in render(Plan(cfg())))
    case("json output serializable", lambda: json.dumps(as_json(Plan(cfg(svc={"pool_size": 1, "peak_rps": 900})))) is not None)

    passed = sum(1 for _, ok, _ in cases if ok)
    for name, ok, err in cases:
        print(("PASS " if ok else "FAIL ") + name + ((" " + err) if err else ""))
    print("%d/%d passed" % (passed, len(cases)))
    return 0 if passed == len(cases) else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="Size database connection pools against load and database limits.")
    ap.add_argument("file", nargs="?", help="JSON description ('-' = stdin)")
    ap.add_argument("--set", action="append", default=[], metavar="TARGET.KEY=VALUE",
                    help="what-if override, e.g. api.max_instances=16 or database.max_connections=400 (repeatable)")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.file:
        ap.error("give a JSON file or --self-test")
    try:
        text = sys.stdin.read() if a.file == "-" else open(a.file, encoding="utf-8").read()
        cfg = json.loads(text)
        cfg = copy.deepcopy(cfg)
        for e in a.set:
            apply_set(cfg, e)
        pl = Plan(cfg)
    except (OSError, ValueError) as e:
        print("error: %s" % e, file=sys.stderr)
        return 2
    sys.stdout.write(json.dumps(as_json(pl), indent=2) + "\n" if a.json else render(pl))
    return 1 if pl.worst() == "HIGH" else 0


if __name__ == "__main__":
    sys.exit(main())

# Spring Boot Service taste of a pragmatic Java backend engineer
- Start on the latest Spring Boot release with a current Java LTS version, and enable virtual threads where blocking I/O dominates. Confidence: 0.80
- Organize packages by feature such as orders or billing rather than by layer like controllers, services and repositories. Confidence: 0.85
- Use constructor injection with final fields everywhere and never field injection with @Autowired. Confidence: 0.95
- Bind configuration to typed, validated @ConfigurationProperties records instead of scattered @Value strings. Confidence: 0.90
- Keep secrets out of application.yml and read them from environment variables or a secrets manager. Confidence: 0.95
- Use Java records for request and response DTOs and keep JPA entities out of controller signatures. Confidence: 0.90
- Validate requests with Bean Validation annotations and @Valid, and return ProblemDetail responses for every error. Confidence: 0.85
- Handle exceptions in one @RestControllerAdvice class instead of try/catch blocks in each controller. Confidence: 0.85
- Put @Transactional on service methods, keep transactions short, and never call remote services inside one. Confidence: 0.90
- Turn off open-in-view and fetch associations explicitly with fetch joins or entity graphs to avoid N+1 queries. Confidence: 0.90
- Manage schema changes with Flyway or Liquibase migrations and keep Hibernate ddl-auto set to validate or none. Confidence: 0.95
- Call other HTTP services through RestClient or declarative HTTP interfaces with explicit connect and read timeouts. Confidence: 0.85
- Wrap unreliable dependencies in retries with backoff and circuit breakers, and make retried operations idempotent. Confidence: 0.80
- Log in structured JSON with correlation IDs from tracing, and never log tokens, passwords or full request bodies. Confidence: 0.90
- Expose only health, info and metrics Actuator endpoints publicly, and secure or disable the rest. Confidence: 0.90
- Export metrics and traces with Micrometer and OpenTelemetry instead of custom timing code. Confidence: 0.80
- Secure endpoints with Spring Security as an OAuth2 resource server and method-level authorization for sensitive actions. Confidence: 0.85
- Write fast slice tests with @WebMvcTest and @DataJpaTest, and keep full @SpringBootTest runs for a few key flows. Confidence: 0.80
- Run integration tests against real databases and brokers in Testcontainers rather than H2 or embedded fakes. Confidence: 0.90
- Generate the OpenAPI spec from code with springdoc and publish it with every build. Confidence: 0.70
- Build container images with Buildpacks or layered jars and run them as a non-root user with memory limits set. Confidence: 0.80
- Keep dependency versions managed by the Spring Boot BOM and let a bot like Renovate open upgrade pull requests. Confidence: 0.80

# Full-Text Search taste of a search-focused backend engineer
- Start with the database's built-in full-text search, such as PostgreSQL tsvector, and add a dedicated search engine only when relevance or scale really needs it. Confidence: 0.85
- Treat the search index as a derived copy that can be rebuilt at any time from the primary database. Confidence: 0.95
- Keep the index in sync with a change feed, outbox table or queue, never with dual writes from request handlers. Confidence: 0.90
- Write to a new versioned index and switch an alias to it for mapping changes, so reindexing never causes downtime. Confidence: 0.90
- Define explicit field mappings and analyzers instead of relying on dynamic mapping guesses. Confidence: 0.90
- Store exact-match fields like IDs, slugs and statuses as keyword fields, separate from analyzed text fields. Confidence: 0.90
- Pick analyzers per language, with stemming and stop words chosen for that language, instead of one default analyzer for all content. Confidence: 0.80
- Boost title matches above body matches, and tune field weights with real queries rather than intuition alone. Confidence: 0.85
- Use filters for structured conditions like category, date or permissions so they are cached and do not affect scoring. Confidence: 0.85
- Apply access control inside the search query itself, never by filtering results after they come back. Confidence: 0.95
- Add typo tolerance with fuzzy matching only for terms longer than a few characters to avoid noisy results. Confidence: 0.75
- Serve autocomplete from a dedicated edge n-gram or prefix field, not from wildcard queries on the main text field. Confidence: 0.80
- Manage synonyms in a reviewed file or table under version control, not by editing them directly in production. Confidence: 0.75
- Use search_after or cursor pagination for deep result pages instead of large from and size offsets. Confidence: 0.85
- Log every query, its result count and its clicks so zero-result and low-click queries can be fixed. Confidence: 0.90
- Keep a small judged set of queries and expected results, and run it in CI before shipping any relevance change. Confidence: 0.80
- Try hybrid search that combines keyword and vector results only after the keyword baseline is measured. Confidence: 0.70
- Show highlighted snippets and clear facet counts so users understand why a result matched. Confidence: 0.80
- Set timeouts on search requests and fall back to a simpler query or cached results when the cluster is slow. Confidence: 0.80
- Monitor query latency percentiles, indexing lag and index size, and alert when indexing falls behind. Confidence: 0.85
- Never expose the search engine directly to browsers, and always put it behind your own API with rate limits. Confidence: 0.95

# .NET Web API taste of a pragmatic C# backend engineer
- Target the latest LTS release of .NET and turn on nullable reference types and warnings as errors in every project. Confidence: 0.90
- Use minimal APIs with grouped route builders for small services, and controllers only when a large team already knows them well. Confidence: 0.75
- Organize code by feature folders such as Orders or Billing instead of global Controllers, Services and Repositories folders. Confidence: 0.80
- Bind settings to strongly typed options classes with the options pattern and validate them at startup with ValidateOnStart. Confidence: 0.90
- Keep secrets out of appsettings.json and read them from user secrets locally and a vault or environment variables in production. Confidence: 0.95
- Register dependencies with the built-in container and keep lifetimes explicit, never injecting a scoped service into a singleton. Confidence: 0.90
- Accept a CancellationToken in every async endpoint and pass it all the way down to database and HTTP calls. Confidence: 0.90
- Never block on async code with .Result or .Wait(), and use async all the way through the call stack. Confidence: 0.95
- Use EF Core with AsNoTracking for read-only queries and project straight to DTOs with Select instead of loading full entities. Confidence: 0.85
- Manage schema changes with EF Core migrations checked into the repo and applied by a separate deploy step, not at app startup. Confidence: 0.80
- Keep request and response DTOs separate from EF entities so the database schema never leaks into the public API. Confidence: 0.90
- Validate input with FluentValidation or data annotations and return RFC 7807 problem details for every error. Confidence: 0.85
- Handle unexpected exceptions in one global exception handler instead of try/catch blocks in every endpoint. Confidence: 0.85
- Create outgoing HTTP clients through IHttpClientFactory with typed clients and resilience policies for retries and timeouts. Confidence: 0.90
- Log with structured message templates through ILogger, never with string interpolation inside log calls. Confidence: 0.85
- Export traces, metrics and logs with OpenTelemetry and expose separate liveness and readiness health check endpoints. Confidence: 0.80
- Publish an OpenAPI document from the code and generate typed clients from it instead of hand-writing them. Confidence: 0.75
- Use JWT bearer authentication with policy-based authorization rather than role string checks scattered through handlers. Confidence: 0.85
- Write integration tests with WebApplicationFactory against a real database in Testcontainers, not an in-memory provider. Confidence: 0.85
- Keep unit tests fast with xUnit and test behavior through public APIs instead of mocking every internal class. Confidence: 0.75
- Build small container images with the official SDK and runtime images and run the app as a non-root user. Confidence: 0.85
- Pin package versions centrally with Directory.Packages.props so every project in the solution uses the same versions. Confidence: 0.80

# Laravel App taste of a pragmatic PHP web developer
- Start on the latest Laravel and PHP versions with strict types declared in every file. Confidence: 0.85
- Keep controllers thin and resourceful, with single-action invokable controllers for anything that is not CRUD. Confidence: 0.85
- Validate input in Form Request classes and authorize it with policies instead of inline checks in controllers. Confidence: 0.90
- Put business logic in small action classes like CreateInvoice, not in controllers, models or route closures. Confidence: 0.75
- Use Eloquent with explicit $fillable fields or casts, and never call Model::unguard in production code. Confidence: 0.85
- Turn on Model::preventLazyLoading outside production to catch N+1 queries early, and fix them with eager loading. Confidence: 0.90
- Back every relationship with foreign keys and unique indexes in migrations, not only with Eloquent rules. Confidence: 0.90
- Write migrations that are safe to roll forward and never edit one that has already run in production. Confidence: 0.90
- Shape API responses with API Resources so the database schema never leaks directly into JSON. Confidence: 0.85
- Push slow work like email, exports and third-party calls to queued jobs running under Horizon, and make every job idempotent. Confidence: 0.90
- Use events and listeners only for real side effects, not to hide the main flow of a request. Confidence: 0.75
- Read config through config() and keep env() calls inside config files only, so config caching works. Confidence: 0.95
- Run php artisan optimize, config:cache and route:cache as part of every deploy. Confidence: 0.85
- Use Sanctum for first-party SPA and mobile auth, and reach for Passport only when you need full OAuth. Confidence: 0.80
- Test with Pest or PHPUnit using feature tests against a real database with RefreshDatabase. Confidence: 0.85
- Build test data with model factories and states instead of large shared seed files. Confidence: 0.80
- Enforce formatting with Laravel Pint and static analysis with Larastan at level 6 or higher in CI. Confidence: 0.85
- Prefer Livewire or Inertia with the Laravel starter kits before building a separate SPA. Confidence: 0.75
- Rate limit login and public API routes with named rate limiters. Confidence: 0.90
- Store uploads through the Storage facade on S3-compatible disks and serve private files via temporary signed URLs. Confidence: 0.85
- Schedule recurring tasks in the scheduler with withoutOverlapping and onOneServer, not in raw crontab entries. Confidence: 0.85
- Keep packages few and well maintained, and upgrade one major Laravel version at a time using the official upgrade guide. Confidence: 0.80

# Webhook Integration taste of a reliability-focused integrations engineer
- Verify every incoming webhook with an HMAC signature over the raw request body before parsing it as JSON. Confidence: 0.95
- Compare signatures with a constant-time function, never with a plain string equality check. Confidence: 0.95
- Reject deliveries whose signed timestamp is older than about five minutes to block replay attacks. Confidence: 0.90
- Respond with a 2xx status within a few seconds and do the real work in a background job. Confidence: 0.95
- Persist the raw event first, then process it, so nothing is lost if a worker crashes mid-way. Confidence: 0.90
- Deduplicate on the provider's event ID with a unique index, because every provider retries and delivers at least once. Confidence: 0.95
- Never assume events arrive in order, and compare a version or updated_at field before overwriting local state. Confidence: 0.85
- Treat the payload as a hint and refetch the current object from the provider's API for anything money-related. Confidence: 0.80
- Return 4xx only for permanently bad requests and 5xx for temporary failures so the sender's retries behave correctly. Confidence: 0.85
- Support at least two active signing secrets at once so secrets can be rotated without downtime. Confidence: 0.85
- When sending webhooks, retry with exponential backoff and jitter for up to a day or more, then disable the endpoint and notify its owner. Confidence: 0.90
- Send a thin, versioned payload with an event type, event ID, timestamp and resource ID instead of dumping whole database rows. Confidence: 0.80
- Name event types as resource.action in the past tense, like invoice.paid or user.deleted. Confidence: 0.80
- Give customers a delivery log with request, response, status and a manual replay button. Confidence: 0.85
- Block outgoing webhook URLs that resolve to private, loopback or link-local addresses to prevent SSRF. Confidence: 0.95
- Use separate, short timeouts for outgoing deliveries and never let one slow customer endpoint block a shared queue. Confidence: 0.90
- Require HTTPS for all webhook endpoints outside local development. Confidence: 0.90
- Offer a reconciliation API or periodic sync so consumers can recover from missed events. Confidence: 0.80
- Test handlers locally with the provider's CLI or a tunnel, and keep recorded sample payloads as test fixtures. Confidence: 0.80
- Alert on rising failure rates and growing backlogs per endpoint, not only on individual errors. Confidence: 0.80
- Never log full payloads that contain personal data, secrets or card details. Confidence: 0.90

# Rails App taste of a pragmatic Ruby on Rails developer
- Start new apps on the latest Rails with PostgreSQL, and stick to the defaults unless there is a clear reason to swap a piece out. Confidence: 0.90
- Keep controllers thin and RESTful, adding new resources like SessionsController or ArchivesController instead of custom actions. Confidence: 0.90
- Put domain logic in models and plain Ruby objects under app/models, and reach for service objects only when a workflow spans many models. Confidence: 0.75
- Use concerns sparingly and only for behavior that is genuinely shared, not to hide a bloated model. Confidence: 0.75
- Prefer Hotwire with Turbo and Stimulus for interactivity before adding a separate JavaScript SPA. Confidence: 0.85
- Validate data in the model and back it with database constraints like NOT NULL, unique indexes and foreign keys. Confidence: 0.95
- Write reversible migrations, add indexes concurrently on large tables, and never edit a migration that has already run in production. Confidence: 0.90
- Keep callbacks for simple data normalization and move side effects like emails and API calls into explicit jobs. Confidence: 0.85
- Run background work with Active Job on Solid Queue or Sidekiq, and keep every job idempotent and safe to retry. Confidence: 0.90
- Pass record IDs to jobs, not whole objects, and reload the record inside the job. Confidence: 0.85
- Catch N+1 queries with strict_loading or the Bullet gem, and fix them with includes or preload. Confidence: 0.90
- Use strong parameters everywhere and never pass params straight into update or create. Confidence: 0.95
- Store secrets in encrypted credentials or environment variables, never in committed YAML files. Confidence: 0.95
- Test with Minitest or RSpec, leaning on model and request tests, with a few system tests for critical flows. Confidence: 0.80
- Use fixtures or lean factories, and avoid deep factory chains that create half the database per test. Confidence: 0.75
- Lint with RuboCop using the rubocop-rails-omakase config or a small agreed set of rules, enforced in CI. Confidence: 0.80
- Scan for security issues with Brakeman and bundler-audit on every pull request. Confidence: 0.90
- Cache expensive views with fragment caching and cache keys based on updated_at, using Solid Cache or Redis. Confidence: 0.80
- Use scopes and query objects for reusable queries instead of long chains repeated across controllers. Confidence: 0.80
- Keep gems few and well maintained, and upgrade Rails one minor version at a time with deprecation warnings fixed first. Confidence: 0.85
- Deploy with Kamal or a managed platform using a Docker image built in CI, and run migrations as a separate release step. Confidence: 0.80
- Use Active Record encryption for sensitive columns like tokens and personal data. Confidence: 0.80

# Authentication taste of a security-minded backend engineer
- Use a well-maintained auth library or provider instead of hand-rolling login, sessions and token handling. Confidence: 0.95
- Hash passwords with Argon2id, or bcrypt with a cost of at least 12, and never with a plain fast hash like SHA-256. Confidence: 0.95
- Prefer server-side sessions in HttpOnly, Secure, SameSite=Lax cookies for first-party web apps over JWTs in localStorage. Confidence: 0.90
- Use short-lived access tokens with rotating refresh tokens when you truly need stateless tokens for APIs or mobile clients. Confidence: 0.85
- Detect refresh token reuse and revoke the whole token family when it happens. Confidence: 0.85
- Regenerate the session ID on login, logout and privilege changes to prevent session fixation. Confidence: 0.95
- Offer passkeys (WebAuthn) as a first-class sign-in method, not only as a second factor. Confidence: 0.80
- Support TOTP or passkeys for MFA and treat SMS codes as a last-resort fallback. Confidence: 0.85
- Rate limit login, signup and password reset per account and per IP, with exponential backoff instead of hard permanent lockouts. Confidence: 0.90
- Return the same generic message for unknown email and wrong password so accounts cannot be enumerated. Confidence: 0.90
- Make password reset and magic link tokens single-use, random, hashed at rest and expiring within 15 to 60 minutes. Confidence: 0.95
- Check passwords against known breached lists and drop arbitrary composition rules like "one symbol required". Confidence: 0.85
- Use OAuth 2.1 Authorization Code with PKCE for every client, and never the implicit flow. Confidence: 0.95
- Validate the state and nonce parameters on every OAuth and OIDC callback. Confidence: 0.95
- Link social logins to existing accounts only after the provider returns a verified email. Confidence: 0.90
- Keep authentication (who you are) separate from authorization (what you can do) and check permissions on the server for every request. Confidence: 0.95
- Re-ask for credentials or a passkey before sensitive actions like changing email, password or payout details. Confidence: 0.85
- Notify users by email on new device sign-ins, password changes and MFA changes. Confidence: 0.80
- Let users see and revoke their active sessions from account settings. Confidence: 0.80
- Log auth events with user ID, IP and user agent, but never log passwords, tokens or reset links. Confidence: 0.95
- Protect cookie-based state-changing requests with SameSite cookies plus CSRF tokens or Origin header checks. Confidence: 0.90
- Store signing keys in a secrets manager and support key rotation with a kid header from day one. Confidence: 0.85
- Write integration tests for the unhappy paths: expired tokens, revoked sessions, replayed links and wrong tenants. Confidence: 0.85

---
name: sql-schema-design-reviewer
description: Reviews PostgreSQL and MySQL tables (schema.sql, a dump or a pull request's CREATE TABLE) for design flaws that are cheap to fix before data exists - missing primary keys, money in float columns, foreign keys with mismatched types or no index, 32-bit ids, timestamps without time zone, nullable flags, free-text statuses, reserved names and case-sensitive unique emails. Returns corrected DDL. Includes a stdlib Python linter with --self-test.
---

# SQL Schema Design Reviewer

You review database schemas the way an experienced data modeler would at design time: before the first row is written, when changing a type or a key is a one-line edit instead of a weekend migration. You look at the shape of the data (keys, types, constraints, names, indexes), explain the concrete failure each problem leads to, and hand back corrected DDL.

This skill reviews the design of tables. It is not about deploying changes to a live database (locks, backfills, expand/contract rollouts); for that, use a migration safety review.

## Files in this skill

- `scripts/schema_lint.py` - parses CREATE TABLE, CREATE INDEX and ALTER TABLE ... ADD statements and reports keys, types, constraints, naming and index problems with severities and fixes; auto-detects PostgreSQL or MySQL (Python 3 standard library only, no database connection)
- `references/design-rules.md` - every finding code with the failure it causes, when it is acceptable to keep, and the fix
- `references/type-choices.md` - which type to use for ids, money, time, text, flags, statuses and JSON in PostgreSQL and MySQL
- `templates/schema-review.md` - the review to hand back
- `examples/shop-schema.sql` - a small online shop schema with deliberate design flaws
- `examples/shop-schema-fixed.sql` - the same schema after the review; it lints clean

## Workflow

### 1. Get the schema and the context
Ask for the DDL (schema.sql, `pg_dump --schema-only`, `mysqldump --no-data`, or the CREATE TABLE from the pull request), the engine and version, expected row counts per table in a year, and how the data is used (OLTP app, reporting, multi-tenant, replicated, exposed through an ORM). Ask which rules the team already has (UUID vs bigint ids, soft deletes, naming).

### 2. Run the linter
```
python3 scripts/schema_lint.py examples/shop-schema.sql
python3 scripts/schema_lint.py schema.sql --dialect mysql --min-severity MEDIUM
python3 scripts/schema_lint.py db/*.sql --ignore NO_CREATED_AT --json > schema-review.json
python3 scripts/schema_lint.py --self-test
```
Exit 0 means no HIGH findings, 1 means at least one HIGH (use as a CI gate on schema files), 2 means bad input. The linter is heuristic: it reads names (a `price` column in float, an `order_id` without a foreign key), so confirm each finding against the user's intent.

### 3. Review what a script cannot see
Then read the schema yourself (see `references/design-rules.md`):
- Does every table have one clear meaning and a natural unique key besides the surrogate id (sku, email, provider + provider_id)?
- Are relationships right: one-to-many vs many-to-many, ON DELETE behaviour (RESTRICT by default; CASCADE only for true children), nullable foreign keys only where the relation is optional?
- Are invariants in CHECK constraints (quantity > 0, ends_at > starts_at) instead of only in app code?
- Multi-tenant: does every tenant-owned table carry tenant_id, is it in the unique keys and the leading index column?
- Soft deletes: are unique constraints partial (`WHERE deleted_at IS NULL`)?
- Personal data: which columns hold PII, and do they need separate access or retention rules?

### 4. Deliver
Fill `templates/schema-review.md`: verdict, findings ranked by impact with the failure each one causes, then the corrected DDL (see `examples/shop-schema-fixed.sql`) and a list of decisions left to the team. If the tables already hold data, say which fixes now need a migration and flag them for a migration safety review instead of rewriting live tables in place.

## Example result
`examples/shop-schema.sql` (PostgreSQL, 5 tables): 20 findings, 4 HIGH. order_items has no primary key (duplicate lines possible); products.price is double precision and payments.amount is real (cents drift); payments.order_id is integer but references "order".id (bigserial). MEDIUM: serial ids on customers and products, timestamp without time zone on customers.created_at and placed_at, a table named "order" (reserved), foreign keys on "order".customer_id and order_items.order_id with no index. LOW: case-sensitive unique email, varchar(255) habit, nullable is_verified, free-text status, product_id without a foreign key, a redundant payments index, JSON kept in text. `examples/shop-schema-fixed.sql` fixes all of them (orders, bigint identity, numeric(12,2), timestamptz, citext, CHECK on status, composite key on order_items) and lints clean.

## Rules

- Explain each finding as the failure it causes (wrong totals, orphans, duplicate signups, an outage at 2.1 billion ids), not as a style rule.
- Keep the user's conventions (UUID ids, naming) unless they cause a real problem; say when a finding is a judgement call.
- Prefer constraints in the database over checks in the application; the database is the last line of defence.
- Money is exact: numeric/DECIMAL or integer minor units, never float, and store the currency.
- Time is an instant: timestamptz in PostgreSQL, UTC everywhere in MySQL.
- Do not propose changes to tables that already hold production data without pointing out that each one is a migration with its own risks.
FILE:references/design-rules.md
# Design rules and finding codes

| Code | Sev | What goes wrong | Acceptable when | Fix |
|---|---|---|---|---|
| NO_PRIMARY_KEY | HIGH | duplicate rows cannot be told apart or deleted one at a time; logical replication and CDC need a key; ORMs misbehave | never for OLTP tables (staging tables loaded and dropped in one job, maybe) | surrogate `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY` or a composite natural key (order_id, product_id) |
| FLOAT_MONEY | HIGH | 0.1 + 0.2 = 0.30000000000000004; sums and refunds drift by cents, reports do not tie out | never for money; floats are fine for measurements | `numeric(12,2)` / `DECIMAL(12,2)` or `bigint` minor units (`amount_cents`) plus a currency column |
| FK_TYPE_MISMATCH | HIGH | integer column referencing a bigint key: joins cast, and inserts fail once ids pass 2,147,483,647 | never | same type on both sides |
| MYISAM_ENGINE | HIGH | no transactions, no foreign keys, table locks, corruption on crash | read-only archives, rarely | `ENGINE=InnoDB` |
| INT32_PRIMARY_KEY | MEDIUM | id exhaustion at about 2.1 billion (sequences also skip on rollbacks); the fix is a full table rewrite on the biggest table | small lookup tables (statuses, countries) | `bigint` identity / `BIGINT UNSIGNED AUTO_INCREMENT` |
| TIMESTAMP_WITHOUT_TZ | MEDIUM | the stored value means different instants depending on the writer's zone; DST and server moves corrupt ordering | local wall-clock values that are not instants (store opening hours, a birthday time) | `timestamptz` |
| MONEY_TYPE | MEDIUM | Postgres `money` follows lc_monetary, one currency, rounding on division | never in new schemas | `numeric(12,2)` + currency |
| NULLABLE_AUDIT_COLUMN | MEDIUM | rows without created_at break "last 7 days" reports and backfills | never | `NOT NULL DEFAULT now()` |
| FK_WITHOUT_INDEX | MEDIUM | (PostgreSQL) deleting or updating a parent scans the whole child table under lock; joins from parent to children are slow. MySQL InnoDB creates the index itself | tiny child tables, parents never deleted | index whose leading columns are the FK columns |
| RESERVED_WORD_NAME | MEDIUM | `order`, `user`, `group` must be quoted in every query, ORM, BI tool and ad-hoc psql session | never in new schemas | plural or qualified names: orders, app_users, user_groups, sort_order |
| MYSQL_UTF8MB3 | MEDIUM | `utf8` is 3-byte: emoji and some CJK characters fail or are cut | never in new schemas | `utf8mb4` + `utf8mb4_0900_ai_ci` |
| NULLABLE_BOOLEAN | LOW | three-valued logic: `WHERE NOT is_verified` skips NULL rows | when "unknown" is a real state (then prefer an explicit status) | `NOT NULL DEFAULT false` |
| UNCHECKED_STATUS | LOW | 'Shipped', 'shipped ', 'shiped' all appear; reports split | statuses owned by another system you only mirror | `CHECK (status IN (...))`, an enum type or a lookup table FK |
| MISSING_FOREIGN_KEY | LOW | orphans after deletes, typos in ids, no documented relation | cross-database or event-sourced references; document them | `REFERENCES parent(id)` + index |
| CASE_SENSITIVE_UNIQUE_EMAIL | LOW | Ana@x.com and ana@x.com are two accounts; password resets go to the wrong one | never for login emails | `citext` or `UNIQUE INDEX ON t (lower(email))` and normalize on write |
| VARCHAR_255_HABIT | LOW | in Postgres, 255 is an arbitrary MySQL-era limit; real rules are missing and odd values get rejected | when 255 really is the rule | `text` + `CHECK (char_length(x) <= n)` for real limits |
| REDUNDANT_INDEX | LOW | an index that is a prefix of another (or of the PK) costs writes and memory for nothing | the shorter index is much smaller and very hot | drop it |
| JSON_IN_TEXT | LOW | no validation, no operators, no GIN index | the text is not really JSON | `jsonb` / `JSON` |
| NO_CREATED_AT | LOW | debugging, audits and backfills need to know when rows appeared | pure join tables | `created_at timestamptz NOT NULL DEFAULT now()` |
| MIXED_CASE_NAME | LOW | quoted "OrderItems" must be quoted forever in Postgres | never | snake_case |
| DATETIME_NO_ZONE | LOW | (MySQL) DATETIME has no zone; mixed writers store mixed zones | when every connection sets time_zone='+00:00' | enforce UTC |

## Things the linter cannot judge (review by hand)
- **Keys**: is there a natural unique key besides the surrogate id? Missing `UNIQUE (provider, provider_id)` is how payments get recorded twice.
- **ON DELETE**: RESTRICT/NO ACTION by default; CASCADE only for true child rows (order_items of an order), never from customers to payments.
- **CHECK constraints for invariants**: `quantity > 0`, `ends_at > starts_at`, `discount <= total`.
- **Multi-tenancy**: tenant_id on every tenant table, first in unique keys and indexes, and in every FK (composite FKs prevent cross-tenant references).
- **Soft deletes**: partial unique indexes `WHERE deleted_at IS NULL`, or the email can never be reused.
- **Denormalized totals**: if orders.total is stored, say how it stays equal to the sum of order_items.
- **PII**: list columns with personal data; consider a separate table with stricter access and retention.
FILE:references/type-choices.md
# Type choices (PostgreSQL / MySQL 8)

| Data | PostgreSQL | MySQL 8 | Avoid |
|---|---|---|---|
| Surrogate id | `bigint GENERATED ALWAYS AS IDENTITY` | `BIGINT UNSIGNED AUTO_INCREMENT` | `serial`/`int` on growing tables |
| Public / distributed id | `uuid` (UUIDv7 for index locality, `gen_random_uuid()` for v4) | `BINARY(16)` with UUID_TO_BIN(x, 1) | `varchar(36)` UUIDs (3x bigger, slower) |
| Money | `numeric(12,2)` (or (19,4) for rates) + `currency char(3)` | `DECIMAL(12,2)` + `CHAR(3)` | `float`, `double`, `real`, Postgres `money` |
| Money as integer | `bigint amount_cents` (mind currencies with 0 or 3 decimals) | `BIGINT` | `int` cents (overflows at 21 million) |
| Instant (event happened) | `timestamptz` | `DATETIME(6)` in UTC, or `TIMESTAMP` (2038 limit, converted by session zone) | `timestamp` without time zone |
| Calendar date | `date` | `DATE` | timestamps at midnight |
| Wall-clock time / schedule | `time` + an IANA zone column | `TIME` + zone column | `timetz` |
| Duration | `interval` or integer seconds | `INT` seconds | text like "1h30" |
| Text | `text` (+ CHECK for real limits) | `VARCHAR(n)` with a real n; `TEXT` for long bodies | `varchar(255)` by habit; `char(n)` padding |
| Case-insensitive text (email, username) | `citext` or a `lower()` unique index | case-insensitive `_ci` collation (default) | plain unique on text in Postgres |
| Flag | `boolean NOT NULL DEFAULT false` | `BOOLEAN` (TINYINT(1)) `NOT NULL DEFAULT 0` | nullable booleans, 'Y'/'N' strings |
| Status | `text` + `CHECK (... IN (...))`, enum type, or lookup table | `ENUM(...)` or lookup table | unconstrained text |
| JSON | `jsonb` (GIN index for containment) | `JSON` (generated columns to index) | JSON in text |
| IP address | `inet` / `cidr` | `VARBINARY(16)` with INET6_ATON | text |
| Large binary | object storage + key in a `text` column | same | big BLOBs in hot tables |

## Notes
- Changing a column type later rewrites the table in both engines (with a few exceptions such as widening varchar in Postgres). Get ids and money right first.
- Enum types in Postgres: adding a value is cheap, removing or renaming one is not. Lookup tables are easier to evolve and can carry labels and sort order.
- `NOT NULL` with a sensible default is the norm; nullable should mean "genuinely unknown or not applicable".
- Store the currency next to every amount, even if today there is only one.
- Name things the same everywhere: `customer_id` in every table that references customers, `_at` for instants, `_on` or `_date` for dates, `is_`/`has_` for booleans.
FILE:templates/schema-review.md
# Schema review: <project / schema file>, <date>

**Engine:** <PostgreSQL 16>   **Scope:** <5 tables: customers, products, orders, order_items, payments>
**Context:** <OLTP shop backend, ~2M orders/year, Prisma ORM, no data yet>
**Command:** `python3 scripts/schema_lint.py <shop-schema.sql>`
**Verdict:** <CHANGES NEEDED before the first release: 4 HIGH, 8 MEDIUM, 8 LOW>

## Must fix (HIGH)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <products.price> | <FLOAT_MONEY> | <0.1 + 0.2 != 0.3; order totals drift by cents> | <numeric(12,2) NOT NULL CHECK (price >= 0)> |

## Should fix (MEDIUM)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <"order"> | <RESERVED_WORD_NAME> | <must be quoted in every query and BI tool> | <rename to orders> |

## Consider (LOW / judgement calls)
- <customers.email: citext so Ana@x.com and ana@x.com are one account>

## Found by reading (not by the linter)
- <payments: add UNIQUE (provider, provider_id) so a webhook retry cannot record a payment twice>
- <ON DELETE: keep RESTRICT on payments -> orders; CASCADE only order_items -> orders>

## Corrected DDL
```sql
<full corrected schema, lints clean>
```

## Decisions for the team
- <bigint identity vs UUIDv7 for public ids>
- <store orders.total or compute it from order_items?>

## If tables already hold data
- <each change above is a migration; list them and run a migration safety review before deploying>
FILE:examples/shop-schema.sql
-- Small online shop schema (PostgreSQL), first draft for review.
-- The problems are deliberate: this is what the skill is meant to catch.

CREATE TABLE customers (
    id          serial PRIMARY KEY,
    email       varchar(255) NOT NULL UNIQUE,
    full_name   varchar(255),
    phone       varchar(255),
    is_verified boolean,
    created_at  timestamp
);

CREATE TABLE products (
    id         serial PRIMARY KEY,
    sku        varchar(64) NOT NULL UNIQUE,
    title      text NOT NULL,
    price      double precision NOT NULL,
    attributes text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "order" (
    id          bigserial PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers (id),
    status      varchar(20) NOT NULL DEFAULT 'pending',
    total       numeric(12,2) NOT NULL,
    placed_at   timestamp NOT NULL DEFAULT now(),
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES "order" (id),
    product_id bigint NOT NULL,
    quantity   integer NOT NULL,
    unit_price numeric(12,2) NOT NULL
);

CREATE TABLE payments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    integer NOT NULL REFERENCES "order" (id),
    amount      real NOT NULL,
    provider    text NOT NULL,
    provider_id text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX payments_order_idx ON payments (order_id);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:examples/shop-schema-fixed.sql
-- Small online shop schema (PostgreSQL), after review.
-- Every change is explained in templates/schema-review.md style in the review.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       citext NOT NULL UNIQUE,
    full_name   text,
    phone       text CHECK (char_length(phone) <= 32),
    is_verified boolean NOT NULL DEFAULT false,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku        text NOT NULL UNIQUE,
    title      text NOT NULL,
    price      numeric(12,2) NOT NULL CHECK (price >= 0),
    attributes jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')),
    total       numeric(12,2) NOT NULL CHECK (total >= 0),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_idx ON orders (customer_id);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id),
    product_id bigint NOT NULL REFERENCES products (id),
    quantity   integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (order_id, product_id)
);
CREATE INDEX order_items_product_idx ON order_items (product_id);

CREATE TABLE payments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders (id),
    amount      numeric(12,2) NOT NULL,
    provider    text NOT NULL,
    provider_id text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_id)
);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:scripts/schema_lint.py
#!/usr/bin/env python3
"""SQL schema design linter for PostgreSQL and MySQL DDL (Python 3 standard library only).

Reads CREATE TABLE / CREATE INDEX / ALTER TABLE ... ADD statements (a schema dump,
schema.sql, or the DDL of new tables in a pull request) and reports design problems
that are cheap to fix before data exists and expensive after: tables without a
primary key, money stored in floating point, foreign keys whose type differs from the
referenced key or that have no supporting index (PostgreSQL), *_id columns without a
foreign key, 32-bit primary keys, timestamps without time zone, nullable audit
columns and booleans, unchecked status columns, reserved-word and mixed-case names,
case-sensitive unique emails, redundant indexes, JSON kept in text columns, and MySQL
tables using MyISAM or 3-byte utf8.

It does not connect to a database and does not judge migration safety (locks,
backfills); it reviews the shape of the schema itself.

Usage:
  python3 schema_lint.py FILE.sql [FILE.sql ...] [--dialect auto|postgres|mysql]
        [--min-severity LOW|MEDIUM|HIGH] [--ignore CODE,CODE] [--json]
  python3 schema_lint.py --self-test
Exit codes: 0 = no HIGH findings, 1 = HIGH findings, 2 = bad input (unreadable file,
no CREATE TABLE found).
"""
import argparse, json, re, sys

SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
MONEY_RE = re.compile(r"(^|_)(price|amount|total|subtotal|cost|balance|fee|fees|tax|salary|refund|discount|paid|payment|revenue|credit|debit|charge)(_|$)", re.I)
RESERVED = {"user", "order", "group", "select", "table", "column", "desc", "asc", "check", "default", "from",
            "where", "limit", "offset", "references", "primary", "key", "range", "rank", "window", "all", "both",
            "end", "grant", "to", "type", "values", "constraint", "index", "match", "condition", "interval", "read", "row"}
RESERVED_PG_ONLY_SAFE = {"key", "range", "rank", "type", "values", "match", "condition", "interval", "read", "row", "index"}  # fine unquoted in Postgres
RESERVED_MY_ONLY_SAFE = {"offset", "user", "end", "type", "values", "row"}  # fine unquoted in MySQL 8
STATUS_RE = re.compile(r"^(status|state|kind|role|type|.*_status|.*_state|.*_type)$", re.I)
JSONISH_RE = re.compile(r"(^|_)(json|payload|metadata|meta|attributes|settings|extra|data)$", re.I)
INT32 = {"int", "integer", "int4", "serial", "serial4", "mediumint", "smallint", "int2", "smallserial", "tinyint"}
INT64 = {"bigint", "int8", "bigserial", "serial8"}


def finding(sev, code, table, column, problem, fix, line=0):
    return {"severity": sev, "code": code, "table": table, "column": column, "line": line, "problem": problem, "fix": fix}


def strip_comments(sql):
    out, i, n = [], 0, len(sql)
    while i < n:
        c = sql[i]
        if c in "'\"`":
            j = i + 1
            while j < n and sql[j] != c:
                j += 2 if sql[j] == "\\" and c == "'" else 1
            out.append(sql[i:j + 1]); i = j + 1
        elif sql.startswith("--", i) or c == "#" and (i == 0 or sql[i - 1] in "\n \t"):
            j = sql.find("\n", i); j = n if j < 0 else j
            out.append(" " * (j - i)); i = j
        elif sql.startswith("/*", i):
            j = sql.find("*/", i + 2); j = n if j < 0 else j + 2
            out.append(re.sub(r"[^\n]", " ", sql[i:j])); i = j
        else:
            out.append(c); i += 1
    return "".join(out)


def split_top(s, sep=","):
    parts, depth, cur, q = [], 0, [], None
    for ch in s:
        if q:
            cur.append(ch)
            if ch == q:
                q = None
            continue
        if ch in "'\"`":
            q = ch
        elif ch == "(":
            depth += 1
        elif ch == ")":
            depth -= 1
        if ch == sep and depth == 0:
            parts.append("".join(cur)); cur = []
        else:
            cur.append(ch)
    if "".join(cur).strip():
        parts.append("".join(cur))
    return parts


def statements(sql):
    clean = strip_comments(sql)
    pos = 0
    for part in split_top(clean, ";"):
        line = clean.count("\n", 0, pos + len(part) - len(part.lstrip())) + 1
        pos += len(part) + 1
        if part.strip():
            yield line, part.strip()


def ident(s):
    s = s.strip()
    quoted = s[:1] in "\"`"
    name = s.split(".")[-1].strip().strip("\"`")
    return name, quoted


def cols_list(s):
    return [ident(re.sub(r"\s+(ASC|DESC|NULLS\s+(FIRST|LAST)).*$", "", c.strip(), flags=re.I))[0].lower() if not c.strip().startswith("(") and "(" not in c else c.strip().lower()
            for c in split_top(s)]


class Schema:
    def __init__(self):
        self.tables, self.indexes, self.fks, self.enums = {}, [], [], set()
        self.dialect_hints = {"mysql": 0, "postgres": 0}


def parse(sql):
    sc = Schema()
    if re.search(r"AUTO_INCREMENT|ENGINE\s*=|`|UNSIGNED|DATETIME|TINYINT\(", sql, re.I):
        sc.dialect_hints["mysql"] += 1
    if re.search(r"\bSERIAL\b|BIGSERIAL|TIMESTAMPTZ|JSONB|GENERATED\s+(ALWAYS|BY DEFAULT)\s+AS\s+IDENTITY|::|CREATE\s+TYPE|CITEXT|CONCURRENTLY|\bTEXT\[\]", sql, re.I):
        sc.dialect_hints["postgres"] += 1
    for line, st in statements(sql):
        m = re.match(r"CREATE\s+(?:TEMP(?:ORARY)?\s+|UNLOGGED\s+)?TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]+)\s*\((.*)\)\s*(.*)$", st, re.I | re.S)
        if m:
            name, quoted = ident(m.group(1))
            t = {"name": name, "quoted": quoted, "line": line, "cols": {}, "order": [], "pk": [], "uniques": [],
                 "checks": " ".join([]), "options": m.group(3), "raw": st}
            checks = []
            for item in split_top(m.group(2)):
                parse_item(item.strip(), t, sc, checks)
            t["checks"] = " ".join(checks).lower()
            sc.tables[name.lower()] = t
            continue
        m = re.match(r"CREATE\s+(UNIQUE\s+)?INDEX\s+(?:CONCURRENTLY\s+)?(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]*)\s*ON\s+(?:ONLY\s+)?([\w\".`]+)\s*(?:USING\s+\w+\s*)?\((.*)\)\s*(.*)$", st, re.I | re.S)
        if m:
            sc.indexes.append({"table": ident(m.group(3))[0].lower(), "cols": cols_list(m.group(4)), "unique": bool(m.group(1)),
                               "name": ident(m.group(2))[0] if m.group(2) else "", "partial": "where" in m.group(5).lower(), "line": line})
            continue
        m = re.match(r"ALTER\s+TABLE\s+(?:ONLY\s+)?(?:IF\s+EXISTS\s+)?([\w\".`]+)\s+(.*)$", st, re.I | re.S)
        if m:
            tname = ident(m.group(1))[0].lower()
            for action in split_top(m.group(2)):
                a = action.strip()
                am = re.match(r"ADD\s+(?:COLUMN\s+)(?:IF\s+NOT\s+EXISTS\s+)?(.*)$", a, re.I | re.S)
                if am and tname in sc.tables:
                    parse_item(am.group(1), sc.tables[tname], sc, [])
                    continue
                am = re.match(r"ADD\s+(.*)$", a, re.I | re.S)
                if am and tname in sc.tables:
                    chk = []
                    parse_item(am.group(1), sc.tables[tname], sc, chk)
                    sc.tables[tname]["checks"] += " " + " ".join(chk).lower()
            continue
        m = re.match(r"CREATE\s+TYPE\s+([\w\".]+)\s+AS\s+ENUM", st, re.I)
        if m:
            sc.enums.add(ident(m.group(1))[0].lower())
        m = re.match(r"CREATE\s+(?:DOMAIN|EXTENSION)\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".]+)", st, re.I)
        if m:
            sc.enums.add(ident(m.group(1))[0].lower())
    return sc


def parse_item(item, t, sc, checks):
    u = item.upper()
    m = re.match(r"CONSTRAINT\s+[\w\"`]+\s+(.*)$", item, re.I | re.S)
    if m:
        item, u = m.group(1).strip(), m.group(1).strip().upper()
    if u.startswith("PRIMARY KEY"):
        t["pk"] = cols_list(re.search(r"\((.*)\)", item, re.S).group(1)); return
    if re.match(r"UNIQUE\b", u):
        cm = re.search(r"\((.*?)\)", item, re.S)
        if cm:
            cols = cols_list(cm.group(1))
            t["uniques"].append(cols)
            sc.indexes.append({"table": t["name"].lower(), "cols": cols, "unique": True, "name": "", "partial": False, "line": t["line"]})
        return
    if u.startswith("FOREIGN KEY"):
        fm = re.match(r"FOREIGN\s+KEY\s*\((.*?)\)\s*REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", item, re.I | re.S)
        if fm:
            sc.fks.append({"table": t["name"].lower(), "cols": cols_list(fm.group(1)), "ref": ident(fm.group(2))[0].lower(),
                           "ref_cols": cols_list(fm.group(3)) if fm.group(3) else None})
        return
    if u.startswith("CHECK"):
        checks.append(item); return
    if re.match(r"(UNIQUE\s+)?(KEY|INDEX)\b", u) or re.match(r"(FULLTEXT|SPATIAL)\b", u):
        cm = re.search(r"\((.*)\)", item, re.S)
        if cm:
            sc.indexes.append({"table": t["name"].lower(), "cols": cols_list(cm.group(1)), "unique": u.startswith("UNIQUE"),
                               "name": "", "partial": False, "line": t["line"]})
        return
    if u.startswith(("EXCLUDE", "LIKE", "PERIOD")):
        return
    cm = re.match(r"([\"`][^\"`]+[\"`]|[\w$]+)\s+(.*)$", item, re.S)
    if not cm:
        return
    name, quoted = ident(cm.group(1))
    rest = cm.group(2)
    tm = re.match(r"((?:double\s+precision|character\s+varying|bit\s+varying|timestamp(?:\s*\(\d+\))?(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|time(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|[\w.]+)(?:\s*\([^)]*\))?(?:\s+unsigned)?(?:\s*\[\])?)", rest, re.I)
    ctype = re.sub(r"\s+", " ", tm.group(1).lower()) if tm else ""
    ru = rest.upper()
    col = {"name": name, "quoted": quoted, "type": ctype, "not_null": "NOT NULL" in ru or "PRIMARY KEY" in ru,
           "default": "DEFAULT" in ru or "IDENTITY" in ru or "AUTO_INCREMENT" in ru or ctype.endswith("serial"),
           "check": "CHECK" in ru}
    t["cols"][name.lower()] = col
    t["order"].append(name.lower())
    if "PRIMARY KEY" in ru:
        t["pk"] = [name.lower()]
    if re.search(r"\bUNIQUE\b", ru):
        t["uniques"].append([name.lower()])
        sc.indexes.append({"table": t["name"].lower(), "cols": [name.lower()], "unique": True, "name": "", "partial": False, "line": t["line"]})
    rm = re.search(r"REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", rest, re.I)
    if rm:
        sc.fks.append({"table": t["name"].lower(), "cols": [name.lower()], "ref": ident(rm.group(1))[0].lower(),
                       "ref_cols": cols_list(rm.group(2)) if rm.group(2) else None})
    if col["check"]:
        checks.append(f"check ({name})")


def qi(name):
    return f'"{name}"' if name.lower() in RESERVED or name != name.lower() else name


def base_type(ctype):
    t = re.sub(r"\(.*?\)|\s+unsigned|\[\]", "", ctype).strip()
    return {"int4": "integer", "int": "integer", "serial": "integer", "serial4": "integer", "int8": "bigint",
            "bigserial": "bigint", "serial8": "bigint", "int2": "smallint", "smallserial": "smallint"}.get(t, t)


def lint(sql, dialect="auto", ignore=()):
    sc = parse(sql)
    if dialect == "auto":
        dialect = "mysql" if sc.dialect_hints["mysql"] > sc.dialect_hints["postgres"] else "postgres"
    out = []

    def add(*a, **k):
        f = finding(*a, **k)
        if f["code"] not in ignore:
            out.append(f)

    table_names = set(sc.tables)
    for key, t in sc.tables.items():
        tn = t["name"]
        ln = t["line"]
        if not t["pk"]:
            add("HIGH", "NO_PRIMARY_KEY", tn, "", "table has no primary key: duplicate rows cannot be told apart, updates and replication (logical replication, MySQL row-based, CDC) suffer",
                "add a bigint identity (or UUID) primary key, or a composite key on the natural unique columns", line=ln)
        elif len(t["pk"]) == 1 and t["pk"][0] in t["cols"]:
            c = t["cols"][t["pk"][0]]
            bt = base_type(c["type"])
            if bt in {"integer", "smallint", "mediumint", "tinyint"} and not re.search(r"(^|_)(type|status|kind|country|currency|lookup)s?$", tn, re.I):
                add("MEDIUM", "INT32_PRIMARY_KEY", tn, c["name"], f"primary key {c['name']} is {c['type']} (max about 2.1 billion, less for smaller types); running out means an emergency type change on the biggest table",
                    "use bigint GENERATED ALWAYS AS IDENTITY (Postgres) or BIGINT UNSIGNED AUTO_INCREMENT (MySQL)", line=ln)
        if dialect == "postgres" and t["quoted"] and t["name"] != t["name"].lower():
            add("LOW", "MIXED_CASE_NAME", tn, "", f'table "{tn}" is quoted mixed case: every query must quote it forever', "use snake_case lower-case names", line=ln)
        if tn.lower() in RESERVED and not (dialect == "postgres" and tn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and tn.lower() in RESERVED_MY_ONLY_SAFE):
            add("MEDIUM", "RESERVED_WORD_NAME", tn, "", f"table name '{tn}' is a reserved word and must be quoted in every query and ORM", "rename (e.g. users, orders, user_groups)", line=ln)
        if dialect == "mysql":
            opts = t["options"].lower()
            if re.search(r"engine\s*=\s*myisam", opts):
                add("HIGH", "MYISAM_ENGINE", tn, "", "ENGINE=MyISAM has no transactions, no foreign keys and table-level locks; crashes can corrupt it", "use ENGINE=InnoDB", line=ln)
            if re.search(r"charset\s*=\s*utf8(mb3)?\b(?!mb4)", opts):
                add("MEDIUM", "MYSQL_UTF8MB3", tn, "", "utf8 in MySQL is 3-byte utf8mb3: emoji and some CJK characters fail to insert or get mangled", "use DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", line=ln)
        if not any(re.match(r"^(created_at|created|created_on|inserted_at|date_created|creation_date)$", c) for c in t["cols"]):
            add("LOW", "NO_CREATED_AT", tn, "", "no created_at column: hard to debug, audit or backfill later", "add created_at timestamptz NOT NULL DEFAULT now()", line=ln)
        v255 = [c["name"] for c in t["cols"].values() if re.match(r"(varchar|character varying)\s*\(255\)", c["type"])]
        if dialect == "postgres" and len(v255) >= 3:
            add("LOW", "VARCHAR_255_HABIT", tn, ", ".join(v255), f"{len(v255)} columns are varchar(255): in Postgres the limit costs nothing to drop but 255 is rarely the real rule",
                "use text, with a CHECK (char_length(x) <= n) where a real limit exists", line=ln)
        for cname in t["order"]:
            c = t["cols"][cname]
            ct, bt = c["type"], base_type(c["type"])
            cn = c["name"]
            if MONEY_RE.search(cn) and bt in {"float", "real", "double", "double precision", "float4", "float8"}:
                add("HIGH", "FLOAT_MONEY", tn, cn, f"{cn} is {ct}: binary floating point cannot store 0.10 exactly, sums drift by cents",
                    "use numeric(12,2) / DECIMAL(12,2), or bigint minor units (amount_cents)", line=ln)
            if bt == "money" and dialect == "postgres":
                add("MEDIUM", "MONEY_TYPE", tn, cn, "Postgres money type depends on lc_monetary, has one fixed currency and rounds", "use numeric(12,2) plus a currency column", line=ln)
            if dialect == "postgres" and (re.match(r"^timestamp(\s*\(\d+\))?$", ct) or "without time zone" in ct):
                add("MEDIUM", "TIMESTAMP_WITHOUT_TZ", tn, cn, f"{cn} is timestamp without time zone: the instant depends on whatever zone the writer assumed", "use timestamptz (stores an absolute instant, displays in the session zone)", line=ln)
            if dialect == "mysql" and bt == "datetime" and re.search(r"_at$", cn):
                add("LOW", "DATETIME_NO_ZONE", tn, cn, f"{cn} is DATETIME (no zone): make sure every writer stores UTC", "document and enforce UTC (time_zone='+00:00' on connections) or use TIMESTAMP within its 2038 range", line=ln)
            if re.match(r"^(created_at|updated_at|inserted_at)$", cn, re.I) and not c["not_null"]:
                add("MEDIUM", "NULLABLE_AUDIT_COLUMN", tn, cn, f"{cn} is nullable{'' if c['default'] else ' and has no default'}: rows without it will appear", f"{cn} timestamptz NOT NULL DEFAULT now()", line=ln)
            if bt in {"boolean", "bool"} and not c["not_null"]:
                add("LOW", "NULLABLE_BOOLEAN", tn, cn, f"{cn} is a nullable boolean: three states (true, false, unknown) and NULL-unsafe WHERE clauses", f"{cn} boolean NOT NULL DEFAULT false", line=ln)
            if STATUS_RE.match(cn) and re.match(r"^(text|varchar|character varying|char|character)", bt) and not c["check"] \
                    and cn.lower() not in t["checks"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
                add("LOW", "UNCHECKED_STATUS", tn, cn, f"{cn} is free text: typos like 'Shipped ' and 'shiped' will end up in the data", "add CHECK (status IN (...)), an enum type, or a FK to a lookup table", line=ln)
            if bt in {"text", "longtext", "mediumtext"} and JSONISH_RE.search(cn):
                add("LOW", "JSON_IN_TEXT", tn, cn, f"{cn} looks like JSON stored as {ct}: no validation, no indexing", "use jsonb (Postgres) or JSON (MySQL) if it really is JSON", line=ln)
            if cn.lower() in RESERVED and not (dialect == "postgres" and cn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and cn.lower() in RESERVED_MY_ONLY_SAFE) and cn.lower() not in {"type"}:
                add("MEDIUM", "RESERVED_WORD_NAME", tn, cn, f"column name '{cn}' is a reserved word and needs quoting everywhere", "rename it (e.g. sort_order, user_id, group_name, ends_at)", line=ln)
            if dialect == "postgres" and c["quoted"] and cn != cn.lower():
                add("LOW", "MIXED_CASE_NAME", tn, cn, f'column "{cn}" is quoted mixed case: every query must quote it', "use snake_case", line=ln)
            if cn.lower().endswith("_id") and cname not in t["pk"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
                stem = cn.lower()[:-3]
                cands = [x for x in (stem, stem + "s", stem + "es", stem[:-1] + "ies" if stem.endswith("y") else None) if x and x in table_names]
                if cands:
                    add("LOW", "MISSING_FOREIGN_KEY", tn, cn, f"{cn} looks like a reference to {cands[0]} but has no foreign key: orphans and typos are possible", f"add REFERENCES {cands[0]}(id) (with an index)", line=ln)
            if re.search(r"(^|_)email$", cn, re.I) and [cname] in t["uniques"] and dialect == "postgres" and bt != "citext":
                if not any(i["table"] == key and any("lower(" in c2 for c2 in i["cols"]) for i in sc.indexes):
                    add("LOW", "CASE_SENSITIVE_UNIQUE_EMAIL", tn, cn, "UNIQUE on email is case-sensitive in Postgres: Ana@x.com and ana@x.com can both sign up", "CREATE UNIQUE INDEX ... ON t (lower(email)) or use citext", line=ln)
    # foreign keys: types and indexes
    for f in sc.fks:
        t = sc.tables.get(f["table"])
        r = sc.tables.get(f["ref"])
        if not t:
            continue
        ref_cols = f["ref_cols"] or (r["pk"] if r else None)
        if r and ref_cols and len(ref_cols) == len(f["cols"]):
            for c, rc in zip(f["cols"], ref_cols):
                a, b = t["cols"].get(c), r["cols"].get(rc)
                if a and b and base_type(a["type"]) != base_type(b["type"]) and not ({base_type(a["type"]), base_type(b["type"])} <= {"uuid"}):
                    add("HIGH", "FK_TYPE_MISMATCH", t["name"], a["name"], f"{a['name']} is {a['type']} but references {r['name']}.{b['name']} ({b['type']}): joins need casts and the FK fails once ids pass the smaller range",
                        f"make {a['name']} the same type as {r['name']}.{b['name']}", line=t["line"])
        if dialect == "postgres":
            covered = any(idx["table"] == f["table"] and not idx["partial"] and idx["cols"][:len(f["cols"])] == f["cols"] for idx in sc.indexes) \
                or t["pk"][:len(f["cols"])] == f["cols"] or any(u[:len(f["cols"])] == f["cols"] for u in t["uniques"])
            if not covered:
                add("MEDIUM", "FK_WITHOUT_INDEX", t["name"], ", ".join(f["cols"]), f"foreign key ({', '.join(f['cols'])}) -> {f['ref']} has no index starting with it: deletes/updates on {f['ref']} scan {t['name']}, and joins are slow",
                    f"CREATE INDEX CONCURRENTLY ON {qi(t['name'])} ({', '.join(f['cols'])})", line=t["line"])
    # redundant indexes
    for i, a in enumerate(sc.indexes):
        t = sc.tables.get(a["table"])
        if not t or a["partial"] or any("(" in c for c in a["cols"]):
            continue
        others = [b for j, b in enumerate(sc.indexes) if j != i and b["table"] == a["table"] and not b["partial"]]
        if t["pk"] and a["cols"] == t["pk"][:len(a["cols"])] and not a["unique"]:
            add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) duplicates the primary key's leading columns", "drop it", line=a["line"])
            continue
        for b in others:
            if len(b["cols"]) > len(a["cols"]) and b["cols"][:len(a["cols"])] == a["cols"] and not a["unique"]:
                add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) is a prefix of the index on ({', '.join(b['cols'])})", "drop the shorter one unless it is much smaller and hot", line=a["line"])
                break
            if b["cols"] == a["cols"] and j_before(sc.indexes, b, a) and a["unique"] == b["unique"]:
                add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"two indexes on ({', '.join(a['cols'])})", "drop one", line=a["line"])
                break
    out.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["table"], f["column"], f["code"]))
    return dialect, sc, out


def j_before(lst, b, a):
    return lst.index(b) < lst.index(a)


def report(path, dialect, sc, found):
    counts = {s: sum(1 for f in found if f["severity"] == s) for s in SEV_ORDER}
    out = [f"# Schema review: {path} ({dialect})", "",
           f"Tables: {len(sc.tables)}   Indexes: {len(sc.indexes)}   Foreign keys: {len(sc.fks)}   Findings: {len(found)} (HIGH {counts['HIGH']}, MEDIUM {counts['MEDIUM']}, LOW {counts['LOW']})", ""]
    if not found:
        out.append("No findings.")
        return "\n".join(out)
    out += ["| Sev | Code | Table.Column | Problem | Fix |", "|---|---|---|---|---|"]
    for f in found:
        loc = f["table"] + ("." + f["column"] if f["column"] else "")
        out.append(f"| {f['severity']} | {f['code']} | {loc} | {f['problem'].replace('|', '/')} | {f['fix'].replace('|', '/')} |")
    return "\n".join(out)


def self_test():
    ok_pk = "CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now()"
    cases = [
        ("clean postgres table", ok_pk + ", name text NOT NULL);", "postgres", [], ["NO_PRIMARY_KEY", "INT32_PRIMARY_KEY", "NO_CREATED_AT"]),
        ("no primary key", "CREATE TABLE logs (msg text, created_at timestamptz NOT NULL);", "auto", ["NO_PRIMARY_KEY"], []),
        ("table-level composite pk", "CREATE TABLE m (a bigint, b bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a, b));", "auto", [], ["NO_PRIMARY_KEY"]),
        ("named pk constraint", "CREATE TABLE m (id bigint, created_at timestamptz NOT NULL, CONSTRAINT m_pk PRIMARY KEY (id));", "auto", [], ["NO_PRIMARY_KEY"]),
        ("pk added by alter", "CREATE TABLE m (id bigint NOT NULL, created_at timestamptz NOT NULL); ALTER TABLE m ADD CONSTRAINT m_pkey PRIMARY KEY (id);", "auto", [], ["NO_PRIMARY_KEY"]),
        ("serial pk is int32", "CREATE TABLE orders (id serial PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", ["INT32_PRIMARY_KEY"], []),
        ("small lookup table int pk ok", "CREATE TABLE order_status (id smallint PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", [], ["INT32_PRIMARY_KEY"]),
        ("float money", ok_pk + ", total_price double precision);", "postgres", ["FLOAT_MONEY"], []),
        ("numeric money ok", ok_pk + ", amount numeric(12,2) NOT NULL);", "postgres", [], ["FLOAT_MONEY"]),
        ("float non-money ok", ok_pk + ", latitude double precision);", "postgres", [], ["FLOAT_MONEY"]),
        ("money type", ok_pk + ", balance money);", "postgres", ["MONEY_TYPE"], []),
        ("timestamp without tz", ok_pk + ", shipped_at timestamp);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
        ("timestamp(3) without time zone", ok_pk + ", shipped_at timestamp(3) without time zone);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
        ("timestamptz ok", ok_pk + ", shipped_at timestamp with time zone);", "postgres", [], ["TIMESTAMP_WITHOUT_TZ"]),
        ("nullable created_at", "CREATE TABLE t (id bigint PRIMARY KEY, created_at timestamptz);", "postgres", ["NULLABLE_AUDIT_COLUMN"], []),
        ("no created_at", "CREATE TABLE t (id bigint PRIMARY KEY, name text);", "postgres", ["NO_CREATED_AT"], []),
        ("nullable boolean", ok_pk + ", is_active boolean);", "postgres", ["NULLABLE_BOOLEAN"], []),
        ("boolean not null ok", ok_pk + ", is_active boolean NOT NULL DEFAULT true);", "postgres", [], ["NULLABLE_BOOLEAN"]),
        ("unchecked status", ok_pk + ", status varchar(20) NOT NULL);", "postgres", ["UNCHECKED_STATUS"], []),
        ("status with check ok", ok_pk + ", status text NOT NULL CHECK (status IN ('a','b')));", "postgres", [], ["UNCHECKED_STATUS"]),
        ("status table check ok", ok_pk + ", status text NOT NULL, CONSTRAINT st CHECK (status IN ('a')));", "postgres", [], ["UNCHECKED_STATUS"]),
        ("reserved table name", "CREATE TABLE \"user\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["RESERVED_WORD_NAME"], []),
        ("reserved column order", ok_pk + ", \"order\" int);", "postgres", ["RESERVED_WORD_NAME"], []),
        ("mixed case quoted", "CREATE TABLE \"OrderItems\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["MIXED_CASE_NAME"], []),
        ("fk without index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint NOT NULL REFERENCES a(id));", "postgres", ["FK_WITHOUT_INDEX"], ["FK_TYPE_MISMATCH"]),
        ("fk with index ok", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint REFERENCES a); CREATE INDEX ON b (a_id, id);", "postgres", [], ["FK_WITHOUT_INDEX"]),
        ("fk covered by composite pk", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE ab (a_id bigint REFERENCES a(id), x bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a_id, x));", "postgres", [], ["FK_WITHOUT_INDEX"]),
        ("fk type mismatch", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id integer, FOREIGN KEY (a_id) REFERENCES a (id));", "postgres", ["FK_TYPE_MISMATCH"], []),
        ("missing fk on _id", "CREATE TABLE customers (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE orders (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, customer_id bigint NOT NULL);", "postgres", ["MISSING_FOREIGN_KEY"], []),
        ("external _id without table ok", ok_pk + ", stripe_id text);", "postgres", [], ["MISSING_FOREIGN_KEY"]),
        ("case-sensitive unique email", ok_pk + ", email text NOT NULL UNIQUE);", "postgres", ["CASE_SENSITIVE_UNIQUE_EMAIL"], []),
        ("lower(email) index ok", ok_pk + ", email text NOT NULL UNIQUE); CREATE UNIQUE INDEX t_email ON t (lower(email));", "postgres", [], ["CASE_SENSITIVE_UNIQUE_EMAIL"]),
        ("redundant prefix index", ok_pk + ", a bigint, b bigint); CREATE INDEX i1 ON t (a); CREATE INDEX i2 ON t (a, b);", "postgres", ["REDUNDANT_INDEX"], []),
        ("json in text", ok_pk + ", payload text);", "postgres", ["JSON_IN_TEXT"], []),
        ("varchar 255 habit", ok_pk + ", a varchar(255), b varchar(255), c varchar(255));", "postgres", ["VARCHAR_255_HABIT"], []),
        ("mysql myisam and utf8", "CREATE TABLE `t` (`id` int unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;", "auto", ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY", "DATETIME_NO_ZONE"], ["TIMESTAMP_WITHOUT_TZ"]),
        ("mysql utf8mb4 innodb ok", "CREATE TABLE `t` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;", "auto", [], ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY"]),
        ("mysql fk needs no extra index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at datetime NOT NULL) ENGINE=InnoDB; CREATE TABLE b (id bigint PRIMARY KEY, created_at datetime NOT NULL, a_id bigint, FOREIGN KEY (a_id) REFERENCES a(id)) ENGINE=InnoDB;", "mysql", [], ["FK_WITHOUT_INDEX"]),
        ("comments and strings ignored", "-- CREATE TABLE x (a int);\n/* CREATE TABLE y (b int); */\n" + ok_pk + ", note text DEFAULT 'a;b -- c');", "postgres", [], ["NO_PRIMARY_KEY"]),
        ("alter add column parsed", ok_pk + "); ALTER TABLE t ADD COLUMN total_amount float;", "postgres", ["FLOAT_MONEY"], []),
    ]
    passed = 0
    for name, sql, dialect, expect, absent in cases:
        d, sc, found = lint(sql, dialect)
        codes = {f["code"] for f in found}
        ok = all(c in codes for c in expect) and not any(c in codes for c in absent)
        if name == "clean postgres table":
            ok = ok and not found
        if name == "comments and strings ignored":
            ok = ok and len(sc.tables) == 1
        if name.startswith("mysql myisam"):
            ok = ok and d == "mysql"
        passed += ok
        print(("PASS " if ok else "FAIL ") + name + ("" if ok else f" -> got {sorted(codes)}"))
    d, sc, found = lint("", "auto")
    ok = report("x.sql", d, sc, found).endswith("No findings.")
    passed += ok
    print(("PASS " if ok else "FAIL ") + "empty report")
    total = len(cases) + 1
    print(f"{passed}/{total} passed")
    return 0 if passed == total else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="SQL schema design linter (Postgres/MySQL DDL)")
    ap.add_argument("files", nargs="*")
    ap.add_argument("--dialect", default="auto", choices=["auto", "postgres", "mysql"])
    ap.add_argument("--min-severity", default="LOW", choices=list(SEV_ORDER))
    ap.add_argument("--ignore", default="")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.files:
        ap.print_usage(sys.stderr)
        return 2
    ignore = {c.strip() for c in a.ignore.split(",") if c.strip()}
    sql_all = []
    for p in a.files:
        try:
            with open(p, encoding="utf-8") as fh:
                sql_all.append(fh.read())
        except OSError as e:
            print(f"error: cannot read {p}: {e}", file=sys.stderr)
            return 2
    dialect, sc, found = lint("\n;\n".join(sql_all), a.dialect, ignore)
    if not sc.tables:
        print("error: no CREATE TABLE statements found", file=sys.stderr)
        return 2
    found = [f for f in found if SEV_ORDER[f["severity"]] <= SEV_ORDER[a.min_severity]]
    if a.json:
        print(json.dumps({"dialect": dialect, "tables": len(sc.tables), "findings": found}, indent=2))
    else:
        print(report(", ".join(a.files), dialect, sc, found))
    return 1 if any(f["severity"] == "HIGH" for f in found) else 0


if __name__ == "__main__":
    sys.exit(main())

# Realtime WebSocket taste of a realtime systems engineer
- Use plain WebSockets or a thin library like ws for simple cases, and reach for Socket.IO only when you need its fallbacks and rooms. Confidence: 0.75
- Prefer Server-Sent Events when updates only flow from server to client. Confidence: 0.85
- Authenticate during the handshake with a short-lived token, never by trusting a user ID sent in a later message. Confidence: 0.95
- Check the Origin header on upgrade requests to block cross-site WebSocket hijacking. Confidence: 0.90
- Define every message as a typed envelope with a type, a version and a payload, validated with a schema on both ends. Confidence: 0.90
- Send heartbeats with ping and pong and close connections that miss them, since proxies drop idle sockets silently. Confidence: 0.90
- Reconnect on the client with exponential backoff and jitter so a server restart does not cause a thundering herd. Confidence: 0.95
- Give each message a sequence number so clients can resume from the last one they saw after reconnecting. Confidence: 0.85
- Treat the socket as a notification channel and keep the database as the source of truth that clients can refetch. Confidence: 0.85
- Fan out across server instances with Redis pub/sub, NATS or a managed service instead of in-memory broadcast. Confidence: 0.90
- Keep connection servers stateless apart from the open sockets, so any instance can be drained and replaced. Confidence: 0.80
- Drain connections gracefully on deploy by telling clients to reconnect before shutting down. Confidence: 0.85
- Apply backpressure: watch the send buffer and drop or coalesce updates for slow clients instead of buffering forever. Confidence: 0.85
- Rate limit incoming messages per connection and cap message size. Confidence: 0.90
- Authorize every subscription to a room or channel on the server, not just the initial connection. Confidence: 0.95
- Batch or throttle high-frequency updates like cursor positions to a fixed tick rate. Confidence: 0.80
- Configure load balancer idle timeouts and sticky sessions deliberately when the transport needs them. Confidence: 0.80
- Track open connections, messages per second, reconnect rate and delivery latency as first-class metrics. Confidence: 0.85
- Use CRDTs like Yjs or Automerge for collaborative editing instead of inventing your own conflict resolution. Confidence: 0.80
- Load test with realistic connection counts using k6 or Artillery before launch. Confidence: 0.80

# Serverless Functions taste of a cloud backend engineer
- Keep each function small and focused on one trigger, but group closely related HTTP routes in one handler to cut cold starts. Confidence: 0.80
- Initialize SDK clients and database pools outside the handler so warm invocations reuse them. Confidence: 0.95
- Use a connection pooler or an HTTP-based database driver, since thousands of concurrent functions will exhaust direct Postgres connections. Confidence: 0.90
- Set an explicit timeout and memory size per function instead of accepting platform defaults. Confidence: 0.85
- Keep bundles small with tree-shaking and lazy imports, and measure cold start time after every major dependency change. Confidence: 0.85
- Make every event handler idempotent, because queues and event sources deliver at least once. Confidence: 0.95
- Move slow or retryable work to a queue and return quickly from HTTP functions. Confidence: 0.90
- Send failed async events to a dead letter queue with an alert on its depth. Confidence: 0.90
- Define functions, permissions and triggers as code with SST, Serverless Framework, SAM or Terraform, never by clicking in the console. Confidence: 0.90
- Grant each function its own least-privilege role scoped to the exact resources it touches. Confidence: 0.95
- Load secrets from a secrets manager or the platform's encrypted env vars, never from the repo. Confidence: 0.95
- Write structured JSON logs with a request ID and correlation ID on every line. Confidence: 0.90
- Trace requests across functions and queues with OpenTelemetry or the platform's tracing. Confidence: 0.80
- Keep business logic in plain modules and keep the handler a thin adapter, so it can be unit tested without the cloud. Confidence: 0.90
- Run integration tests against a real deployed stage per pull request rather than trusting local emulators. Confidence: 0.80
- Set reserved or maximum concurrency on functions that call fragile downstream services. Confidence: 0.85
- Use provisioned concurrency only for latency-critical paths where cold starts were measured to hurt. Confidence: 0.75
- Prefer the arm64 runtime when dependencies support it for better price per performance. Confidence: 0.70
- Set billing alerts and per-function cost dashboards before launch, not after the first surprise invoice. Confidence: 0.85
- Avoid chaining functions that call each other synchronously; use events or a workflow engine like Step Functions instead. Confidence: 0.85
- Choose containers or long-running services for steady high-throughput workloads where serverless costs more. Confidence: 0.75

# Background Jobs taste of a backend reliability engineer
- Make every job idempotent so a retry or duplicate delivery never charges a card or sends an email twice. Confidence: 0.95
- Pass IDs in the job payload and load fresh data inside the worker instead of serializing whole records. Confidence: 0.90
- Enqueue jobs in the same database transaction as the write that triggers them, using an outbox table or a Postgres-backed queue. Confidence: 0.85
- Prefer a boring, well-supported queue such as Sidekiq, BullMQ, Celery or a Postgres queue like Graphile Worker over a home-grown one. Confidence: 0.80
- Retry with exponential backoff and jitter, and cap attempts so poison jobs end up in a dead letter queue. Confidence: 0.95
- Classify errors as retryable or permanent and fail permanent ones immediately instead of burning retries. Confidence: 0.85
- Set an explicit timeout on every job and on every network call it makes. Confidence: 0.90
- Keep jobs small and short, and split long batch work into chunks that can resume from a checkpoint. Confidence: 0.85
- Use separate queues for latency-sensitive and bulk work so a backfill never delays password reset emails. Confidence: 0.90
- Rate limit jobs that call third-party APIs per provider, not per worker process. Confidence: 0.80
- Use unique job keys or locks to prevent the same scheduled job from running twice at once. Confidence: 0.85
- Run cron-style schedules from one scheduler with leader election, never from every app instance. Confidence: 0.80
- Version job payloads and keep workers able to read the previous version during deploys. Confidence: 0.80
- Handle SIGTERM gracefully: stop taking new jobs, finish or requeue the current one, then exit. Confidence: 0.90
- Log a job ID, attempt number and correlation ID on every log line from a job. Confidence: 0.85
- Alert on queue depth, oldest job age and dead letter growth, not just on worker crashes. Confidence: 0.90
- Give the dead letter queue an owner and a documented replay command. Confidence: 0.75
- Test jobs by calling the handler directly in unit tests and run one end-to-end test through the real queue in CI. Confidence: 0.80
- Avoid chaining many jobs into hidden workflows; reach for a workflow engine like Temporal when steps need orchestration. Confidence: 0.70
- Store job results and side-effect markers in the database, not only in the queue backend. Confidence: 0.75

# FastAPI Service taste of a Python API engineer
- Define request and response bodies with Pydantic v2 models and always set response_model so nothing leaks by accident. Confidence: 0.95
- Keep separate schemas for create, update and read instead of reusing the ORM model as the API shape. Confidence: 0.90
- Use async def endpoints only when every call inside is truly async; otherwise write plain def and let the threadpool handle it. Confidence: 0.85
- Inject database sessions, settings and the current user through Depends, never through module-level globals. Confidence: 0.90
- Load configuration with pydantic-settings from environment variables and fail fast at startup if anything is missing. Confidence: 0.90
- Split routes into APIRouter modules by domain and keep main.py limited to app creation and wiring. Confidence: 0.85
- Put business logic in service functions that know nothing about HTTP, so they can be reused from workers and tests. Confidence: 0.85
- Use SQLAlchemy 2.0 style with async sessions and manage the schema with Alembic migrations. Confidence: 0.80
- Use the lifespan context manager for startup and shutdown instead of the deprecated on_event hooks. Confidence: 0.85
- Raise HTTPException only at the edge and map domain errors to status codes with registered exception handlers. Confidence: 0.80
- Return a consistent error body with a machine-readable code, a message and optional details. Confidence: 0.85
- Version the API in the path (/v1) and treat the generated OpenAPI schema as a contract checked in CI. Confidence: 0.75
- Use cursor-based pagination for large lists and cap the page size on the server. Confidence: 0.80
- Test with pytest and httpx.AsyncClient, overriding dependencies instead of patching internals. Confidence: 0.90
- Run uvicorn workers behind gunicorn or a process manager in production, with --reload only in development. Confidence: 0.80
- Offload slow work to a real task queue such as Celery, Arq or Dramatiq; BackgroundTasks is only for small fire-and-forget jobs. Confidence: 0.85
- Add request IDs, structured JSON logs and OpenTelemetry tracing middleware from day one. Confidence: 0.80
- Protect endpoints with OAuth2 bearer tokens or API keys through security dependencies so the docs show the auth scheme. Confidence: 0.80
- Expose separate /healthz and /readyz endpoints, where readiness checks the database and other hard dependencies. Confidence: 0.85
- Manage dependencies with uv and a lock file, and lint and format with Ruff. Confidence: 0.75
- Type check with mypy or pyright in strict mode on the service package. Confidence: 0.70

# Django Web App taste of a Python backend engineer
- Start every project with a custom user model (AbstractUser) even if it adds no fields yet. Confidence: 0.95
- Split settings by environment and read secrets and URLs from environment variables with django-environ or similar. Confidence: 0.90
- Keep apps small and domain-focused; an app that imports half the project is a sign it should be split or merged. Confidence: 0.75
- Put business logic in plain service functions or model methods, not in views, serializers or signals. Confidence: 0.85
- Avoid signals for core workflows; call the side effect explicitly so the flow is readable and testable. Confidence: 0.85
- Use select_related and prefetch_related deliberately and check query counts with django-debug-toolbar or assertNumQueries. Confidence: 0.90
- Add database constraints (UniqueConstraint, CheckConstraint) instead of relying only on form or serializer validation. Confidence: 0.90
- Review every generated migration before committing and never edit a migration that has already run in production. Confidence: 0.90
- Write data migrations with RunPython using apps.get_model, never importing models directly. Confidence: 0.85
- Prefer class-based generic views for CRUD and function views for anything with unusual control flow. Confidence: 0.70
- Use Django REST Framework or django-ninja for APIs, with explicit serializers or schemas rather than fields = "__all__". Confidence: 0.85
- Wrap multi-step writes in transaction.atomic and enqueue background jobs with transaction.on_commit. Confidence: 0.90
- Run slow or external work in Celery or another task queue, with idempotent tasks and explicit retries. Confidence: 0.85
- Write tests with pytest-django and factory_boy instead of fixtures checked in as JSON. Confidence: 0.85
- Keep the admin for staff operations; customize list_display, search_fields and list_select_related so it stays fast. Confidence: 0.80
- Serve static files with WhiteNoise or a CDN and user uploads from object storage, never from the app server disk. Confidence: 0.85
- Run behind gunicorn or uvicorn with DEBUG=False, ALLOWED_HOSTS set and python manage.py check --deploy passing in CI. Confidence: 0.95
- Use HTMX with server-rendered templates before reaching for a separate SPA. Confidence: 0.65
- Pin dependencies with a lock file and upgrade Django LTS releases on a schedule. Confidence: 0.80
- Lint and format with Ruff and type check with mypy plus django-stubs. Confidence: 0.75

# Event-Driven Architecture taste of a distributed systems engineer
- Use events to announce facts that already happened (OrderPlaced), and commands only for requests to a single owner (ChargeCard). Confidence: 0.90
- Name events in the past tense using domain language, never after the producing table or service. Confidence: 0.85
- Define every event with a versioned schema (Avro, Protobuf or JSON Schema) in a registry and check compatibility in CI. Confidence: 0.90
- Evolve schemas only with backward-compatible changes; publish a new event type for breaking changes. Confidence: 0.85
- Assume at-least-once delivery and make every consumer idempotent with a deduplication key. Confidence: 0.95
- Publish events through the transactional outbox pattern so database writes and messages never diverge. Confidence: 0.90
- Include event id, type, version, timestamp, correlation id and causation id in every envelope. Confidence: 0.85
- Keep event payloads self-contained enough for common consumers, but do not dump whole aggregates. Confidence: 0.75
- Partition by aggregate id when ordering matters, and never rely on global ordering. Confidence: 0.85
- Route poison messages to a dead-letter queue after bounded retries with exponential backoff and jitter. Confidence: 0.90
- Alert on consumer lag and dead-letter queue depth, not just on broker health. Confidence: 0.90
- Propagate trace context through message headers so one request can be followed across services. Confidence: 0.85
- Use sagas with explicit compensating actions for multi-service workflows instead of distributed transactions. Confidence: 0.80
- Prefer choreography for simple flows and a dedicated orchestrator once a workflow has more than a few steps. Confidence: 0.70
- Reach for event sourcing only where an audit trail or temporal queries are real requirements. Confidence: 0.75
- Make replays safe: consumers must tolerate reprocessing history from any offset. Confidence: 0.80
- Set retention to match how long consumers might need to rebuild state, and document it per topic. Confidence: 0.70
- Give each topic one owning team that controls its schema and lifecycle. Confidence: 0.85
- Keep synchronous calls for queries that need an immediate answer; do not force request-response through a queue. Confidence: 0.80
- Test consumers with contract tests against the registered schema and producers with outbox integration tests. Confidence: 0.80
- Keep a living catalog (AsyncAPI or similar) of topics, producers and consumers. Confidence: 0.75

# Redis Caching taste of a backend engineer
- Default to cache-aside: read from Redis, fall back to the source of truth on a miss, then populate the cache. Confidence: 0.90
- Treat the cache as disposable; the system must stay correct (only slower) if Redis is flushed. Confidence: 0.95
- Set a TTL on every cache key; a key without an expiry is a memory leak waiting to happen. Confidence: 0.90
- Add random jitter to TTLs so keys written together do not expire together. Confidence: 0.85
- Namespace keys as service:entity:id:version (e.g. catalog:product:42:v3) and bump the version on breaking format changes. Confidence: 0.85
- Invalidate by deleting the key after a successful write, not by updating the cached value in place. Confidence: 0.80
- Protect hot keys from stampedes with a short lock (SET key value NX PX) or request coalescing. Confidence: 0.80
- Cache negative results (not found) with a short TTL to absorb lookups for missing ids. Confidence: 0.70
- Store compact serialized values (JSON for debuggability, MessagePack when size matters) and keep values under about 100 KB. Confidence: 0.75
- Use hashes for objects whose fields are read separately, and plain strings for values read whole. Confidence: 0.70
- Never run KEYS in production; use SCAN with a cursor. Confidence: 0.95
- Avoid big keys and huge collections; split anything that would make a single command block for milliseconds. Confidence: 0.85
- Use pipelining or MGET for batch reads instead of one round trip per key. Confidence: 0.85
- Put multi-step atomic logic in a Lua script or a function, not in client-side check-then-set. Confidence: 0.80
- Set maxmemory with an explicit eviction policy (allkeys-lru for pure caches, noeviction for data you must not lose). Confidence: 0.90
- Keep caches and durable data (queues, rate limits, sessions) in separate Redis instances with separate policies. Confidence: 0.80
- Use Redis Streams or a real broker for jobs; plain lists lose work when a consumer crashes. Confidence: 0.70
- Implement rate limiting with a sliding window or token bucket script, not with ad-hoc INCR plus EXPIRE races. Confidence: 0.75
- Set client timeouts and a circuit breaker so a slow Redis degrades to cache misses instead of taking the service down. Confidence: 0.90
- Monitor hit ratio, evictions, memory fragmentation and latency, and alert on a falling hit ratio. Confidence: 0.85
- Require auth and TLS, disable or rename dangerous commands (FLUSHALL, CONFIG), and never expose Redis to the internet. Confidence: 0.95
- Do not cache what is already fast; measure the source latency before adding a cache layer. Confidence: 0.80

# GraphQL API taste of a platform engineer
- Design the schema around client use cases and domain language, not around database tables. Confidence: 0.90
- Generate types for both server and client from the schema (GraphQL Code Generator or equivalent); never hand-write response types. Confidence: 0.90
- Make fields non-null by default only when the server can always resolve them; keep fields that depend on other services nullable so one failure does not null out the whole response. Confidence: 0.80
- Use one input object per mutation (createOrder(input: CreateOrderInput!)) and return a payload type, not the bare entity. Confidence: 0.85
- Return expected business errors as typed union members in the payload (e.g. OrderCreated | InsufficientStock); reserve the top-level errors array for unexpected failures. Confidence: 0.75
- Paginate lists with Relay-style cursor connections (edges, node, pageInfo) and cap first/last at a sane maximum. Confidence: 0.80
- Batch and cache data loading per request with DataLoader to kill N+1 queries. Confidence: 0.95
- Enforce query depth and complexity limits at the gateway. Confidence: 0.90
- Use persisted queries (allowlisted operation hashes) for first-party clients in production. Confidence: 0.80
- Disable introspection on public production endpoints unless the API is meant to be public. Confidence: 0.70
- Evolve the schema additively and mark old fields with @deprecated(reason: ...); never version the endpoint with /v2. Confidence: 0.90
- Remove a deprecated field only after field-usage metrics show no traffic for a full release cycle. Confidence: 0.85
- Run schema diff checks in CI and fail the build on breaking changes. Confidence: 0.90
- Use globally unique opaque IDs and implement the Node interface for refetching. Confidence: 0.70
- Keep resolvers thin: they call domain services, and authorization lives in that service layer, not in resolver if-statements. Confidence: 0.85
- Name operations in every client query so traces, logs and metrics are readable. Confidence: 0.90
- Colocate fragments with the UI components that use them. Confidence: 0.80
- Prefer custom scalars (DateTime, URL, Email) with validation over plain String. Confidence: 0.75
- Use subscriptions only for genuinely real-time data; polling is fine for anything that changes every few minutes. Confidence: 0.70
- Reach for federation only when several teams own separate subgraphs; one team means one schema. Confidence: 0.75
- Trace every resolver with OpenTelemetry and alert on slow fields, not just slow requests. Confidence: 0.80
- Document every type and field with schema descriptions; the schema is the API reference. Confidence: 0.85

# PostgreSQL taste of a backend engineer
- Use bigint identity columns (GENERATED ALWAYS AS IDENTITY) for primary keys; reach for UUIDv7 only when ids must be generated outside the database. Confidence: 0.80
- Store timestamps as timestamptz in UTC, never timestamp without time zone. Confidence: 0.95
- Use text with a CHECK constraint instead of varchar(n) for length limits. Confidence: 0.80
- Store money as numeric or as integer minor units, never as float. Confidence: 0.95
- Name tables in plural snake_case and foreign keys as <singular>_id. Confidence: 0.70
- Declare NOT NULL by default and allow NULL only when "unknown" is a real state. Confidence: 0.85
- Enforce invariants in the database with foreign keys, UNIQUE, and CHECK constraints, not only in application code. Confidence: 0.90
- Index every foreign key column that is used in joins or cascading deletes. Confidence: 0.85
- Read EXPLAIN (ANALYZE, BUFFERS) before adding an index, and again after to prove it helped. Confidence: 0.90
- Prefer partial and expression indexes over wide composite indexes for skewed filters. Confidence: 0.70
- Create indexes on large tables with CREATE INDEX CONCURRENTLY. Confidence: 0.90
- Use jsonb for genuinely schemaless attributes, but promote fields you filter on into real columns. Confidence: 0.80
- Paginate with keyset (WHERE id > $last ORDER BY id LIMIT n) instead of large OFFSET values. Confidence: 0.85
- Keep transactions short and never hold one open across a network call. Confidence: 0.90
- Set statement_timeout and idle_in_transaction_session_timeout per application role. Confidence: 0.85
- Put a connection pooler such as PgBouncer in transaction mode in front of the database. Confidence: 0.80
- Use SELECT ... FOR UPDATE SKIP LOCKED for simple job queues before adding a separate broker. Confidence: 0.70
- Use INSERT ... ON CONFLICT for idempotent upserts. Confidence: 0.85
- Give each service its own role with least privilege; the application never connects as a superuser. Confidence: 0.90
- Turn on pg_stat_statements and review the slowest queries every week. Confidence: 0.85
- Write CTEs for readability, knowing Postgres 12+ inlines them unless you add MATERIALIZED. Confidence: 0.65
- Test point-in-time recovery from backups regularly; an untested backup does not count. Confidence: 0.95

---
name: http-cache-header-reviewer
description: Reviews HTTP caching headers (Cache-Control, Expires, ETag, Last-Modified, Vary, Set-Cookie) for pages, assets, APIs. Computes browser and CDN freshness, flags cookie or personal responses a CDN may share, HTML cached too long, hashed assets cached too briefly, immutable on changing URLs, Vary fragmentation, missing validators, heuristic caching and cached errors. Suggests a header per resource. Includes a stdlib Python checker with --self-test.
---

# HTTP Cache Header Reviewer

You help web developers get caching right: fast repeat visits and low CDN bills without stale pages after a deploy or, worse, one user's data served to another. You collect real response headers, explain what each cache will actually do with them, and propose a header per resource type with a short fix list.

## Files in this skill

- `scripts/cache_audit.py` - reads response blocks (curl -sI or DevTools output) and prints resource kind, browser and CDN TTL, revalidation, flags and suggested Cache-Control (Python 3 standard library only)
- `references/cache-control-directives.md` - every directive, freshness and heuristic rules, validators, Vary, cacheable status codes
- `references/caching-strategy-playbook.md` - recommended headers per resource, deploy safety, CDN notes, how to collect and verify headers
- `templates/cache-review-report.md` - report with current vs recommended headers and a fix list
- `examples/shop-responses.txt` - eight responses from a sample shop with common mistakes

## Workflow

### 1. Collect headers
Ask for a list of representative URLs: the home page, a logged-in page, a hashed JS/CSS file, an unhashed script, an image, a font, a public API call, a personal API call, an error response. Capture with `curl -sI <url>` (add `-H 'Cookie: ...'` or `-H 'Authorization: ...'` for logged-in requests, never paste real tokens into the report) or DevTools. Put each in the block format of `examples/shop-responses.txt`: a `### METHOD URL` line, optional `> Request-Header: value` lines, the status line, then the response headers.

### 2. Run the checker
```
python3 scripts/cache_audit.py examples/shop-responses.txt
python3 scripts/cache_audit.py headers.txt --json > cache-report.json
python3 scripts/cache_audit.py --self-test
```
Exit code 1 means high-severity findings, 2 means the input could not be parsed.

### 3. Explain the findings
Order: privacy first (AUTH_SHARED_CACHE, PUBLIC_WITH_COOKIE, PERSONAL_NOT_PRIVATE), then correctness after deploys (HTML_LONG_TTL, IMMUTABLE_UNHASHED, ERROR_CACHED), then performance (HASHED_SHORT_TTL, ASSET_NO_CACHING, VARY_FRAGMENTS, NO_VALIDATOR, HEURISTIC_ONLY) and hygiene (INVALID_DIRECTIVE, NO_STORE_CONFLICT, EXPIRES_ONLY, PRAGMA_ONLY). Use `references/cache-control-directives.md` to explain why.

### 4. Recommend headers
Use `references/caching-strategy-playbook.md`. Give the exact header per resource and where to set it in the user's stack (Nginx `add_header`, Express `res.set`, Next.js `headers()`, S3/CloudFront metadata, Cloudflare rules). Mention the deploy rule: hashed assets for a year, HTML revalidates.

### 5. Verify and report
Fill `templates/cache-review-report.md`. After changes, collect headers again and re-run the checker; check CDN HIT/MISS headers.

## Example result
For `examples/shop-responses.txt`: 5 high findings (home page HTML cached 1 day, widget.js immutable without a hash, the cart API with a session cookie cached by the CDN for 2 minutes, the login page with Set-Cookie marked public and cached 10 minutes), 4 medium (app.3f9a1c7b.js cached only 1 hour, hero.jpg on an 8.1-day heuristic, Vary: Cookie, a 503 cached for 1 hour) and 1 low (cart API has no validator); only vendor.81d2e0aa.css is already ideal.

## Rules

- Treat any chance of a shared cache storing personal data as the top issue.
- Do not ask for or keep real cookies or tokens; placeholders are enough.
- Recommend purges or URL versioning when changing TTLs that are already live.
- Say when behavior depends on the CDN's configuration and suggest checking it.
FILE:references/cache-control-directives.md
# Cache-Control Directives and Freshness Rules

## Response directives
| Directive | Meaning |
|---|---|
| max-age=N | Fresh for N seconds in any cache (browser and shared) |
| s-maxage=N | Overrides max-age for shared caches (CDN, proxy) only |
| no-cache | May be stored, but must be revalidated with the origin before every use |
| no-store | Must not be stored anywhere; overrides everything else |
| private | Only the browser may store it; CDNs and proxies must not |
| public | Shared caches may store it even if it would normally not be cacheable (for example with Authorization) |
| must-revalidate | Once stale, must not be served without a successful revalidation |
| proxy-revalidate | Same as must-revalidate, for shared caches only |
| immutable | Will never change while fresh; browsers skip revalidation on reload |
| stale-while-revalidate=N | May serve stale for N seconds while refreshing in the background |
| stale-if-error=N | May serve stale for N seconds if the origin returns 5xx or is unreachable |
| no-transform | Intermediaries must not modify the body (recompress images, etc.) |

Common mistakes: `no-cache` does NOT mean "do not cache" (that is `no-store`); `max-age` values must be whole seconds (`max-age=10m` is invalid and ignored); `Pragma: no-cache` only works on requests.

## Freshness lifetime (RFC 9111)
1. Shared cache: s-maxage, else max-age, else Expires - Date.
2. Browser: max-age, else Expires - Date.
3. Neither present and Last-Modified present: caches may use a heuristic, usually 10% of (Date - Last-Modified). A file last changed 80 days ago may be reused for 8 days with no check.
4. `private` sets the shared lifetime to 0. `no-store` sets both to 0.

## Validators and revalidation
- ETag (strong `"abc"` or weak `W/"abc"`) -> client sends If-None-Match.
- Last-Modified -> client sends If-Modified-Since.
- A match returns `304 Not Modified` with no body, which is cheap. Without a validator, every revalidation downloads the whole body.

## Vary
- `Vary: Accept-Encoding` is normal and safe.
- `Vary: Cookie`, `User-Agent` or `Referer` creates one cache entry per distinct value, which usually means no CDN hits.
- `Vary: *` means the response can never be reused.

## Status codes
Cacheable by default when fresh: 200, 203, 204, 206, 300, 301, 308, 404, 405, 410, 414, 501. Error responses (4xx/5xx) should have short or no caching, or a CDN may serve an outage page for hours. Use `stale-if-error` to serve the last good copy instead.
FILE:references/caching-strategy-playbook.md
# Caching Strategy Playbook

## Recommended headers by resource
| Resource | Cache-Control | Notes |
|---|---|---|
| HTML pages, app shell | `no-cache` + ETag | Always fresh after deploys; 304s keep it cheap |
| Marketing HTML on a CDN | `public, max-age=0, s-maxage=300, stale-while-revalidate=60` | Purge the CDN on publish |
| JS/CSS with a content hash (`app.3f9a1c7b.js`) | `public, max-age=31536000, immutable` | New build = new URL |
| JS/CSS without a hash | `public, max-age=300, stale-while-revalidate=86400` | Or add hashing to the build |
| Images, user uploads with stable URLs | `public, max-age=86400, stale-while-revalidate=604800` | Version the URL when the image changes |
| Fonts (versioned URL) | `public, max-age=31536000, immutable` | Also send CORS headers |
| Public API data (prices, catalog) | `public, max-age=30, s-maxage=300, stale-if-error=600` | Keep TTLs short; purge on change |
| Personal API data (cart, profile) | `private, no-cache` + ETag | Never public |
| Secrets, banking, health records, one-time tokens | `no-store` | Also on the logout and account pages |
| Redirect 301 | `max-age=86400` at first | Browsers can cache a bare 301 forever |
| Errors 5xx | `no-store` or `max-age=0`, use stale-if-error on the good responses | |

## Deploy safety
1. Hash every built asset name, then cache it for a year.
2. Make HTML revalidate so it always points at the current hashes.
3. Keep old hashed assets online for at least one release so open tabs still work.
4. Never put `immutable` on a URL whose content can change.

## CDN notes
- Most CDNs ignore `private`/`no-store` only if misconfigured; check "respect origin headers".
- Many CDNs do not cache responses with Set-Cookie by default; some do if `public` is set. Strip Set-Cookie from static responses.
- Use a `Surrogate-Control` or CDN-Cache-Control header if the CDN supports it and you want a CDN TTL different from the browser TTL.
- Normalize the cache key (ignore tracking query strings like utm_*).

## How to collect headers
```
curl -sI https://example.com/ ; curl -sI -H 'Cookie: session=...' https://example.com/api/me
curl -s -o /dev/null -D - https://example.com/assets/app.js   # GET instead of HEAD
```
Some servers answer HEAD differently from GET; when in doubt use the GET form. In Chrome DevTools: Network tab, select a request, Headers, Response Headers (copy as text).

## Checking the result
- Reload twice in DevTools: hashed assets should show "(memory cache)" or "(disk cache)", HTML should show 304 or 200.
- CDN: look at its `Age`, `X-Cache` or `CF-Cache-Status` header (HIT / MISS / BYPASS).
FILE:templates/cache-review-report.md
# HTTP Cache Review - <site / service> - <date>

**Scope:** <URLs or HAR file>  **CDN:** <name or none>  **Collected with:** <curl -sI / DevTools / HAR>

## Summary
- Responses reviewed: <n>  High: <n>  Medium: <n>  Low: <n>
- Biggest risk: <for example personal API cached on the CDN>
- Biggest win: <for example year-long caching for hashed assets>

## Current vs recommended
| Resource | Kind | Current Cache-Control | Browser / CDN TTL | Flags | Recommended |
|---|---|---|---|---|---|
| | | | | | |

## Fix list
| Priority | Change | Where (server, framework, CDN rule) | Owner | Done |
|---|---|---|---|---|
| 1 | | | | [ ] |

## Verification
- [ ] Re-run `python3 scripts/cache_audit.py` on fresh headers: no high findings
- [ ] CDN shows HIT for static files, BYPASS/MISS for personal API
- [ ] Deploy test: new HTML loads new assets without a hard refresh
FILE:examples/shop-responses.txt
# Responses captured with: curl -sI <url>  (request headers sent are listed with "> ")
### GET https://shop.example/
HTTP/2 200
content-type: text/html; charset=utf-8
cache-control: public, max-age=86400
date: Wed, 21 Oct 2026 07:00:00 GMT

### GET https://shop.example/assets/app.3f9a1c7b.js
HTTP/2 200
content-type: application/javascript
cache-control: public, max-age=3600
etag: "3f9a1c7b"

### GET https://shop.example/assets/vendor.81d2e0aa.css
HTTP/2 200
content-type: text/css
cache-control: public, max-age=31536000, immutable
etag: "81d2e0aa"

### GET https://shop.example/js/widget.js
HTTP/2 200
content-type: application/javascript
cache-control: public, max-age=31536000, immutable

### GET https://shop.example/img/hero.jpg
HTTP/2 200
content-type: image/jpeg
date: Wed, 21 Oct 2026 07:00:00 GMT
last-modified: Sat, 01 Aug 2026 07:00:00 GMT

### GET https://shop.example/api/cart
> Cookie: session=abc123
HTTP/2 200
content-type: application/json
cache-control: public, s-maxage=120
vary: Cookie

### POST https://shop.example/login
HTTP/2 200
content-type: text/html; charset=utf-8
cache-control: public, max-age=600
set-cookie: session=xyz789; HttpOnly; Secure; SameSite=Lax

### GET https://shop.example/api/products?page=1
HTTP/2 503
content-type: application/json
cache-control: public, max-age=3600
FILE:scripts/cache_audit.py
#!/usr/bin/env python3
"""HTTP caching header reviewer (Python 3 standard library only).

Input: a text file with one block per response, as copied from `curl -sI`, browser
devtools or a HAR export:
  ### GET https://shop.example/assets/app.3f9a1c7b.js
  > Authorization: Bearer ...          (optional request headers start with "> ")
  HTTP/2 200
  content-type: application/javascript
  cache-control: public, max-age=31536000, immutable
  etag: "3f9a1c7b"
Blank lines and lines starting with "#" (but not "###") are ignored.

For each response it works out the kind (html, hashed-asset, asset, api, image, font, other),
browser and shared-cache (CDN) freshness in seconds (s-maxage > max-age > Expires - Date >
10% heuristic from Last-Modified), whether it can be revalidated, and flags:
  NO_STORE_CONFLICT, PUBLIC_WITH_COOKIE, AUTH_SHARED_CACHE, PERSONAL_NOT_PRIVATE, VARY_STAR,
  VARY_FRAGMENTS, HTML_LONG_TTL, HASHED_SHORT_TTL, IMMUTABLE_UNHASHED, ASSET_NO_CACHING,
  NO_VALIDATOR, HEURISTIC_ONLY, EXPIRES_ONLY, PRAGMA_ONLY, ERROR_CACHED, INVALID_DIRECTIVE.
It suggests a Cache-Control value per kind.
Usage:
  python3 cache_audit.py responses.txt
  python3 cache_audit.py responses.txt --json
  python3 cache_audit.py --self-test
Exit code: 0 OK, 1 high-severity flags found, 2 bad input.
"""
import argparse
import email.utils
import json
import re
import sys

KNOWN = {"max-age", "s-maxage", "no-cache", "no-store", "no-transform", "must-revalidate",
         "proxy-revalidate", "must-understand", "private", "public", "immutable",
         "stale-while-revalidate", "stale-if-error"}
NEEDS_INT = {"max-age", "s-maxage", "stale-while-revalidate", "stale-if-error"}
HASH_RE = re.compile(r"[.\-_]([0-9a-f]{8,}|[0-9A-Za-z_-]{8,}(?=\.[a-z0-9]+$))\.[a-z0-9]+$")
SEVERITY = {"NO_STORE_CONFLICT": "low", "PUBLIC_WITH_COOKIE": "high", "AUTH_SHARED_CACHE": "high",
            "PERSONAL_NOT_PRIVATE": "medium", "VARY_STAR": "medium", "VARY_FRAGMENTS": "medium",
            "HTML_LONG_TTL": "high", "HASHED_SHORT_TTL": "medium", "IMMUTABLE_UNHASHED": "high",
            "ASSET_NO_CACHING": "medium", "NO_VALIDATOR": "low", "HEURISTIC_ONLY": "medium",
            "EXPIRES_ONLY": "low", "PRAGMA_ONLY": "low", "ERROR_CACHED": "medium",
            "INVALID_DIRECTIVE": "medium"}
RECOMMEND = {
    "html": "no-cache (plus ETag or Last-Modified), or max-age=0, must-revalidate",
    "hashed-asset": "public, max-age=31536000, immutable",
    "asset": "public, max-age=3600, stale-while-revalidate=86400 (or add a content hash to the file name)",
    "image": "public, max-age=86400, stale-while-revalidate=604800",
    "font": "public, max-age=31536000, immutable (if the URL is versioned)",
    "api": "private, no-cache for user data; public, max-age=60, s-maxage=300 only for truly public data",
    "personal": "private, no-cache (or no-store for sensitive data)",
    "other": "set an explicit Cache-Control",
}


class CacheError(ValueError):
    pass


def parse_cc(value):
    """Return (directives dict, problems list)."""
    d, problems = {}, []
    for part in [p.strip() for p in value.split(",") if p.strip()]:
        k, eq, v = part.partition("=")
        k = k.strip().lower()
        v = v.strip().strip('"')
        if k not in KNOWN:
            problems.append(f"unknown directive {k!r}")
            continue
        if k in NEEDS_INT:
            if not re.fullmatch(r"\d+", v or ""):
                problems.append(f"{k} needs a whole number of seconds, got {v!r}")
                continue
            d[k] = int(v)
        else:
            d[k] = True if not eq else v
    return d, problems


def parse_blocks(text):
    blocks, cur = [], None
    for raw in text.splitlines():
        line = raw.rstrip("\r")
        if line.startswith("###"):
            cur = {"label": line[3:].strip(), "status": None, "headers": {}, "request": {}}
            blocks.append(cur)
            continue
        if not line.strip() or line.lstrip().startswith("#"):
            continue
        if cur is None:
            raise CacheError("file must start with a '### METHOD URL' line")
        if line.startswith(">"):
            k, _, v = line[1:].strip().partition(":")
            cur["request"][k.strip().lower()] = v.strip()
            continue
        m = re.match(r"HTTP/[\d.]+\s+(\d{3})", line)
        if m:
            cur["status"] = int(m.group(1))
            continue
        k, colon, v = line.partition(":")
        if not colon:
            raise CacheError(f"cannot parse header line {line!r} in block {cur['label']!r}")
        k = k.strip().lower()
        if k in cur["headers"] and k in ("cache-control", "vary"):
            cur["headers"][k] += ", " + v.strip()
        elif k == "set-cookie":
            cur["headers"].setdefault(k, []).append(v.strip())
        else:
            cur["headers"][k] = v.strip()
    if not blocks:
        raise CacheError("no response blocks found")
    return blocks


def url_of(label):
    parts = label.split()
    return parts[-1] if parts else ""


def kind_of(url, ctype):
    path = re.sub(r"[?#].*$", "", url)
    name = path.rsplit("/", 1)[-1].lower()
    ctype = ctype.lower()
    hashed = bool(HASH_RE.search(name)) and bool(re.search(r"\d", name))
    if "text/html" in ctype or name.endswith(".html") or (not ctype and "." not in name):
        return "html", False
    if "json" in ctype or "/api/" in path or "graphql" in path:
        return "api", False
    if name.endswith((".woff2", ".woff", ".ttf", ".otf")) or "font" in ctype:
        return "font", hashed
    if ctype.startswith("image/") or name.endswith((".png", ".jpg", ".jpeg", ".gif", ".webp", ".avif", ".svg", ".ico")):
        return ("hashed-asset" if hashed else "image"), hashed
    if name.endswith((".js", ".mjs", ".css", ".map", ".wasm")) or "javascript" in ctype or "text/css" in ctype:
        return ("hashed-asset" if hashed else "asset"), hashed
    return "other", hashed


def http_date(v):
    try:
        return email.utils.parsedate_to_datetime(v)
    except (TypeError, ValueError, IndexError):
        return None


def review(block):
    h, req = block["headers"], block["request"]
    status = block["status"] or 200
    url = url_of(block["label"])
    kind, hashed = kind_of(url, h.get("content-type", ""))
    cc, problems = parse_cc(h.get("cache-control", ""))
    flags = []

    def flag(code, detail):
        flags.append({"code": code, "severity": SEVERITY[code], "detail": detail})

    for p in problems:
        flag("INVALID_DIRECTIVE", p)
    cookies = h.get("set-cookie", [])
    auth = "authorization" in req or "cookie" in req
    date, expires, lastmod = http_date(h.get("date")), h.get("expires"), http_date(h.get("last-modified"))
    no_store = "no-store" in cc
    heuristic = False
    if no_store:
        browser = shared = 0
    else:
        browser = cc.get("max-age")
        if browser is None and expires is not None:
            ex = http_date(expires)
            browser = max(0, int((ex - date).total_seconds())) if ex and date else 0
        if browser is None and "no-cache" not in cc and lastmod and date and status in (200, 203, 204, 206, 300, 301, 404, 405, 410, 414, 501):
            browser = max(0, int((date - lastmod).total_seconds() / 10))
            heuristic = True
        browser = browser or 0
        shared = 0 if "private" in cc else cc.get("s-maxage", browser)
    if "no-cache" in cc:
        browser_eff, shared_eff = 0, 0
    else:
        browser_eff, shared_eff = browser, shared
    validator = "etag" in h or "last-modified" in h
    personal = bool(cookies) or auth

    if no_store and any(k in cc for k in ("max-age", "s-maxage", "public", "immutable")):
        flag("NO_STORE_CONFLICT", "no-store overrides the other directives; remove the extras so intent is clear")
    if cookies and ("public" in cc or "s-maxage" in cc) and not no_store:
        flag("PUBLIC_WITH_COOKIE", "response sets a cookie but allows shared caches; a CDN may serve one user's cookie to others")
    if auth and shared > 0 and ("public" in cc or "s-maxage" in cc) and kind in ("api", "html", "other"):
        flag("AUTH_SHARED_CACHE", "authenticated request with a response that shared caches may store; personal data can leak across users")
    elif personal and kind in ("api", "html", "other") and not no_store and "private" not in cc \
            and not any(f["code"] == "PUBLIC_WITH_COOKIE" for f in flags):
        flag("PERSONAL_NOT_PRIVATE", "personalized response without private or no-store")
    vary = [v.strip().lower() for v in h.get("vary", "").split(",") if v.strip()]
    if "*" in vary:
        flag("VARY_STAR", "Vary: * makes the response effectively uncacheable")
    frag = [v for v in vary if v in ("user-agent", "cookie", "referer")]
    if frag and shared > 0:
        flag("VARY_FRAGMENTS", f"Vary on {', '.join(frag)} splits the CDN cache into many copies; vary on a normalized header instead")
    if kind == "html" and browser_eff > 300:
        flag("HTML_LONG_TTL", f"HTML cached {fmt_s(browser_eff)} without revalidation; users keep old pages that point to deleted assets after a deploy")
    if kind == "hashed-asset" and browser_eff < 30 * 86400 and not no_store:
        flag("HASHED_SHORT_TTL", f"content-hashed file cached only {fmt_s(browser_eff)}; it can be cached for a year with immutable")
    if "immutable" in cc and not hashed:
        flag("IMMUTABLE_UNHASHED", "immutable on a URL without a content hash; browsers will not pick up changes until max-age expires")
    if kind in ("asset", "image", "font") and (no_store or "no-cache" in cc) and not personal:
        flag("ASSET_NO_CACHING", "static file is never reused from cache; every page view downloads or revalidates it")
    if ("no-cache" in cc or browser_eff == 0) and not no_store and not validator and status == 200:
        flag("NO_VALIDATOR", "must revalidate but has no ETag or Last-Modified, so every check downloads the full body")
    if heuristic:
        flag("HEURISTIC_ONLY", f"no Cache-Control or Expires; caches guess {fmt_s(browser)} from Last-Modified")
    if "cache-control" not in h and expires is not None:
        flag("EXPIRES_ONLY", "only the legacy Expires header; add Cache-Control max-age")
    if "cache-control" not in h and "pragma" in h:
        flag("PRAGMA_ONLY", "Pragma: no-cache is a request header and has no effect on responses; use Cache-Control")
    if status >= 400 and max(browser_eff, shared_eff) > 300:
        flag("ERROR_CACHED", f"status {status} cached for {fmt_s(max(browser_eff, shared_eff))}; a brief error becomes a long outage")
    rec_kind = "personal" if personal and kind in ("api", "html", "other") else kind
    return {"label": block["label"], "status": status, "kind": kind, "hashed": hashed,
            "cache_control": h.get("cache-control"), "browser_ttl_s": browser_eff, "shared_ttl_s": shared_eff,
            "revalidate": "no-cache" in cc or "must-revalidate" in cc, "validator": validator,
            "stale_while_revalidate_s": cc.get("stale-while-revalidate"), "flags": flags,
            "recommended": RECOMMEND[rec_kind]}


def fmt_s(s):
    if s is None:
        return "-"
    for unit, n in (("d", 86400), ("h", 3600), ("m", 60)):
        if s >= n:
            v = s / n
            return f"{v:.0f}{unit}" if v == int(v) else f"{v:.1f}{unit}"
    return f"{s}s"


def audit(text):
    items = [review(b) for b in parse_blocks(text)]
    return {"items": items, "high_flags": sum(1 for i in items for f in i["flags"] if f["severity"] == "high")}


def to_markdown(r):
    out = ["# HTTP cache header review", "",
           "| Response | Status | Kind | Browser TTL | CDN TTL | Revalidate | Validator | Flags |",
           "|---|---|---|---|---|---|---|---|"]
    for i in r["items"]:
        out.append(f"| {i['label']} | {i['status']} | {i['kind']} | {fmt_s(i['browser_ttl_s'])} | {fmt_s(i['shared_ttl_s'])} | "
                   f"{'yes' if i['revalidate'] else 'no'} | {'yes' if i['validator'] else 'no'} | {', '.join(f['code'] for f in i['flags']) or '-'} |")
    out += ["", "## Findings"]
    n = 0
    for sev in ("high", "medium", "low"):
        for i in r["items"]:
            for f in i["flags"]:
                if f["severity"] == sev:
                    n += 1
                    out.append(f"- **{sev.upper()} {f['code']}** {i['label']}: {f['detail']}")
    if not n:
        out.append("- No findings.")
    out += ["", "## Suggested Cache-Control"]
    for i in r["items"]:
        if i["flags"]:
            out.append(f"- {i['label']}: `{i['recommended']}` (now: `{i['cache_control'] or 'none'}`)")
    return "\n".join(out)


def self_test():
    cases = []

    def ok(name, cond):
        cases.append((name, bool(cond)))

    def one(url, *lines):
        return review(parse_blocks("### GET " + url + "\nHTTP/1.1 200 OK\n" + "\n".join(lines))[0])

    def codes(item):
        return {f["code"] for f in item["flags"]}

    ok("parse max-age", parse_cc("public, max-age=600")[0] == {"public": True, "max-age": 600})
    ok("bad max-age reported", parse_cc("max-age=10m")[1])
    ok("unknown directive reported", parse_cc("maxage=60")[1])
    ok("hashed js detected", kind_of("https://x/app.3f9a1c7b.js", "") == ("hashed-asset", True))
    ok("unhashed css", kind_of("https://x/styles.css", "text/css") == ("asset", False))
    ok("html by content type", kind_of("https://x/", "text/html; charset=utf-8")[0] == "html")
    ok("api by json", kind_of("https://x/api/cart", "application/json")[0] == "api")
    i = one("https://x/app.3f9a1c7b.js", "Cache-Control: public, max-age=31536000, immutable", "ETag: \"a\"")
    ok("ideal hashed asset clean", not codes(i) and i["browser_ttl_s"] == 31536000)
    i = one("https://x/app.3f9a1c7b.js", "Cache-Control: public, max-age=3600")
    ok("hashed short ttl", "HASHED_SHORT_TTL" in codes(i))
    i = one("https://x/main.js", "Cache-Control: public, max-age=31536000, immutable")
    ok("immutable unhashed", "IMMUTABLE_UNHASHED" in codes(i))
    i = one("https://x/", "Content-Type: text/html", "Cache-Control: max-age=86400")
    ok("html long ttl", "HTML_LONG_TTL" in codes(i))
    i = one("https://x/", "Content-Type: text/html", "Cache-Control: no-cache", "ETag: \"1\"")
    ok("html no-cache with etag clean", not codes(i) and i["browser_ttl_s"] == 0)
    i = one("https://x/", "Content-Type: text/html", "Cache-Control: no-cache")
    ok("no validator", "NO_VALIDATOR" in codes(i))
    i = one("https://x/login", "Content-Type: text/html", "Cache-Control: public, max-age=600", "Set-Cookie: sid=1")
    ok("public with cookie", "PUBLIC_WITH_COOKIE" in codes(i))
    i = review(parse_blocks("### GET https://x/api/me\n> Authorization: Bearer t\nHTTP/2 200\ncontent-type: application/json\ncache-control: public, s-maxage=300")[0])
    ok("auth shared cache", "AUTH_SHARED_CACHE" in codes(i) and i["shared_ttl_s"] == 300)
    i = review(parse_blocks("### GET https://x/api/me\n> Cookie: sid=1\nHTTP/2 200\ncontent-type: application/json\ncache-control: max-age=60")[0])
    ok("personal not private", "PERSONAL_NOT_PRIVATE" in codes(i))
    i = one("https://x/api/me", "Content-Type: application/json", "Cache-Control: private, max-age=60")
    ok("private hides from cdn", i["shared_ttl_s"] == 0 and i["browser_ttl_s"] == 60)
    i = one("https://x/a.css", "Cache-Control: no-store, max-age=60")
    ok("no-store conflict and asset no caching", {"NO_STORE_CONFLICT", "ASSET_NO_CACHING"} <= codes(i))
    i = one("https://x/a.css", "Cache-Control: public, max-age=600", "Vary: *")
    ok("vary star", "VARY_STAR" in codes(i))
    i = one("https://x/a.css", "Cache-Control: public, max-age=600", "Vary: Accept-Encoding, User-Agent")
    ok("vary user-agent fragments", "VARY_FRAGMENTS" in codes(i))
    i = one("https://x/logo.png", "Date: Wed, 21 Oct 2026 10:00:00 GMT", "Last-Modified: Mon, 11 Oct 2026 10:00:00 GMT")
    ok("heuristic 10% of 10 days = 1 day", "HEURISTIC_ONLY" in codes(i) and i["browser_ttl_s"] == 86400)
    i = one("https://x/logo.png", "Date: Wed, 21 Oct 2026 10:00:00 GMT", "Expires: Wed, 21 Oct 2026 11:00:00 GMT")
    ok("expires only gives 1h", "EXPIRES_ONLY" in codes(i) and i["browser_ttl_s"] == 3600)
    i = one("https://x/", "Content-Type: text/html", "Pragma: no-cache")
    ok("pragma only", "PRAGMA_ONLY" in codes(i))
    i = review(parse_blocks("### GET https://x/a.css\nHTTP/1.1 503 Service Unavailable\ncache-control: public, max-age=3600")[0])
    ok("error cached", "ERROR_CACHED" in codes(i))
    ok("repeated cache-control merged", parse_blocks("### GET https://x/a\nHTTP/1.1 200\ncache-control: public\ncache-control: max-age=5")[0]["headers"]["cache-control"] == "public, max-age=5")
    try:
        parse_blocks("HTTP/1.1 200 OK\n")
        ok("missing ### rejected", False)
    except CacheError:
        ok("missing ### rejected", True)
    ok("fmt_s", fmt_s(31536000) == "365d" and fmt_s(600) == "10m" and fmt_s(45) == "45s" and fmt_s(699840) == "8.1d")
    passed = sum(c for _, c in cases)
    for name, c in cases:
        print(("PASS " if c else "FAIL ") + name)
    print(f"{passed}/{len(cases)} passed")
    return 0 if passed == len(cases) else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="Review HTTP caching headers.")
    ap.add_argument("file", nargs="?", help="responses text file ('-' for stdin)")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.file:
        ap.error("responses file required (or --self-test)")
    try:
        text = sys.stdin.read() if a.file == "-" else open(a.file, encoding="utf-8").read()
        r = audit(text)
    except (OSError, CacheError) as e:
        print(f"error: {e}", file=sys.stderr)
        return 2
    print(json.dumps(r, indent=2) if a.json else to_markdown(r))
    return 1 if r["high_flags"] else 0


if __name__ == "__main__":
    sys.exit(main())

---
name: retry-backoff-policy-calculator
description: Checks retry, backoff and timeout settings across a call chain to prevent retry storms. Computes exponential delay schedules with full, equal or decorrelated jitter, worst-case latency per layer, expected tries and request amplification. Flags inner timeouts that outlive the caller, retries of non-idempotent calls, retries on 4xx, ignored Retry-After and missing caps or deadlines. Includes a stdlib Python calculator with --self-test.
---

# Retry and Backoff Policy Calculator

You help engineers set retry, backoff and timeout policies that make services more reliable instead of turning a small outage into a big one. You collect the settings of every hop in a call chain (browser, gateway, services, databases, third-party APIs), compute what they really do under failure, and propose a safer policy with numbers.

## Files in this skill

- `scripts/retry_policy.py` - reads a call-chain JSON and prints delay schedules, worst-case latency, expected tries, amplification and flags (Python 3 standard library only)
- `references/backoff-math.md` - formulas for backoff, jitter, worst-case time, expected tries, amplification and timeout nesting
- `references/retry-safety-guide.md` - what is safe to retry, idempotency keys, circuit breakers, retry budgets, common library settings
- `templates/retry-policy-review.md` - review report with current chain, findings and proposed policy
- `examples/checkout-chain.json` - a three-layer checkout chain with typical mistakes

## Workflow

### 1. Map the chain
Ask for each hop, outermost first: attempts (total tries), base delay, multiplier, max delay, jitter type, per-try timeout, overall deadline, what it retries on (status codes, "timeout", "network"), whether the call is idempotent, whether an idempotency key is sent, and whether Retry-After is honored. Read them from code or config (SDK clients, HTTP client retry adapters, Envoy/Istio routes, gRPC service config, job queues). Unknown values: use the library default and mark them as assumed.

### 2. Run the calculator
```
python3 scripts/retry_policy.py examples/checkout-chain.json
python3 scripts/retry_policy.py chain.json --failure-rate 0.5 --max-amplification 8
python3 scripts/retry_policy.py chain.json --json > retry-report.json
python3 scripts/retry_policy.py --self-test
```
Exit code 1 means high-severity findings, 2 means bad input.

### 3. Explain the findings
Order: RETRY_UNSAFE and AMPLIFICATION first (they cause duplicate side effects and outages), then INNER_OUTLIVES_CALLER, HOT_LOOP, NO_PER_TRY_TIMEOUT, then medium and low. Explain each with the numbers from the report, for example "one checkout can become 36 calls to the payments provider".

### 4. Propose a fixed policy
Use `references/backoff-math.md` and `references/retry-safety-guide.md`: retry at one layer, full jitter with a cap, inner worst case below the caller's per-try timeout, deadlines passed down, idempotency keys for writes, no retries on 4xx except 408/429, Retry-After honored, a retry budget or circuit breaker. Re-run the script on the proposed JSON and show before and after.

### 5. Write it up
Fill `templates/retry-policy-review.md` and give config snippets for the user's library.

## Example result
For `examples/checkout-chain.json` at 30% failure rate: worst-case amplification 36 calls per checkout (expected 2.74); the gateway layer's 9.3 s worst case outlives the web app's 8 s per-try timeout; the payments layer retries a non-idempotent charge 4 times without an idempotency key and retries 409; no jitter at the gateway; Retry-After ignored on 429/503.

## Rules

- Never recommend retrying a non-idempotent call without an idempotency key.
- Keep numbers honest: label library defaults and guesses as assumptions.
- Prefer fewer retries with good timeouts over many retries.
- Recommend load tests or fault injection to confirm the policy, not just the math.
FILE:references/backoff-math.md
# Backoff and Timeout Math

## Terms
- **attempts**: total tries including the first. attempts 3 = 1 try + 2 retries.
- **base**: first retry delay. **multiplier**: growth factor (usually 2). **cap**: max single delay.
- **per-try timeout**: how long one try may take before it is abandoned.
- **deadline**: total time budget for the call including all tries and delays.

## Delay before retry k (k = 1 .. attempts-1)
d(k) = min(cap, base x multiplier^(k-1))

| Jitter | Delay | Range | Mean |
|---|---|---|---|
| none | d | d | d |
| full | random(0, d) | 0 .. d | d/2 |
| equal | d/2 + random(0, d/2) | d/2 .. d | 0.75 d |
| decorrelated | min(cap, random(base, 3 x previous)) | base .. min(cap, base x 3^k) | simulated |

Full jitter spreads load best and is the default recommendation. Equal jitter keeps a minimum wait. Decorrelated jitter adapts to the previous delay and works well for long-running clients.

## Worst-case duration of one call
worst = attempts x per_try_timeout + sum of max delays, then cut at the deadline if one is set.
Example: 3 tries x 1 s + 100 ms + 200 ms = 3.3 s.

## Expected tries
With an independent failure probability p per try and n attempts:
E[tries] = 1 + p + p^2 + ... + p^(n-1)
p = 0.3, n = 3 -> 1.39. During a full outage p = 1 and E = n.

## Amplification across layers
If layer A retries 3 times and calls B, which retries 3 times and calls C, which retries 4 times, one user request can become 3 x 3 x 4 = 36 calls to C during an outage. This is a retry storm: the retries keep the failing service overloaded, so it cannot recover.

Rules of thumb:
- Retry at one layer only, usually the one closest to the user or the one that owns the idempotency key.
- Inner layers: attempts 1 or 2 with a short deadline.
- Keep worst-case amplification at or below about 10.
- Add a **retry budget**: retries may be at most 10-20% of requests over a sliding window; past that, fail fast.

## Timeout nesting
Each layer's worst case must fit inside its caller's per-try timeout, minus a margin for network and work:
inner_worst <= outer_per_try_timeout - margin
Otherwise the caller abandons the try and retries while the inner layer is still working, which doubles load. Pass a deadline down the chain (for example a `grpc-timeout` or `X-Request-Deadline` header) and stop when it is used up.

## Retry-After
For 429 and 503, servers may send `Retry-After: <seconds>` or an HTTP date. Wait at least that long (use max(Retry-After, computed backoff)), and give up if it is longer than the remaining deadline.
FILE:references/retry-safety-guide.md
# Retry Safety Guide

## What to retry
| Outcome | Retry? | Notes |
|---|---|---|
| Connection refused / reset before the request was sent | Yes | Safe for any method |
| Timeout after the request was sent | Only if idempotent or with an idempotency key | The server may have done the work |
| 408 Request Timeout | Yes, with backoff | |
| 429 Too Many Requests | Yes, after Retry-After | Never faster than the server asks |
| 500 Internal Server Error | Sometimes | Only if the error is known to be transient |
| 502 / 503 / 504 | Yes, with backoff and jitter | Honor Retry-After on 503 |
| 400 / 401 / 403 / 404 / 405 / 410 / 422 | No | Same request fails the same way; fix the request, refresh auth once at most |
| 409 Conflict | Usually no | Exception: an idempotency key that is still in progress, retry after a short wait |

## Idempotency
- GET, HEAD, OPTIONS, PUT and DELETE are idempotent by HTTP semantics, if the server implements them correctly.
- POST and PATCH are not. To retry them, send an **idempotency key** (a UUID per logical operation, e.g. `Idempotency-Key` header) that the server stores with the result for 24 hours or more. Generate the key once, before the first try, and reuse it for every retry.
- Payments, emails, SMS, order creation and stock reservation must never be retried without a key.

## Client checklist
1. Per-try timeout set and shorter than the caller's.
2. Overall deadline set and passed down the chain.
3. Exponential backoff with a cap and full jitter.
4. attempts <= 3 at the edge, 1-2 inside.
5. Retry only on the table's "Yes" rows.
6. Idempotency key on every retried non-idempotent call.
7. Retry budget or circuit breaker so retries stop during an outage.
8. Metrics: tries per request, retry rate, give-up count, by dependency.
9. Log the final error with the attempt count, not every attempt at error level.

## Circuit breaker basics
- Closed: calls flow; count failures in a rolling window.
- Open: after for example 50% failures over at least 20 calls, fail fast for 30 s.
- Half-open: let a few trial calls through; close if they succeed.
A breaker and a retry budget together stop retry storms from deepening an outage.

## Common library settings
- AWS SDKs: standard mode = 3 attempts, full jitter, base 1 s for throttling; adaptive mode adds client-side rate limiting.
- gRPC retry policy: maxAttempts, initialBackoff, maxBackoff, backoffMultiplier, retryableStatusCodes (UNAVAILABLE is the usual one); retries need hedging/transparent retry rules for non-idempotent methods.
- Envoy / Istio: retry_on: 5xx,reset,connect-failure; num_retries; per_try_timeout; set route timeout >= per_try_timeout x (num_retries + 1).
- Python `urllib3.Retry(total=3, backoff_factor=0.5, status_forcelist=[502,503,504], respect_retry_after_header=True, allowed_methods=[...])`.
Check the library docs for the version you use; defaults change.
FILE:templates/retry-policy-review.md
# Retry Policy Review - <service / call chain> - <date>

**Reviewer:** <name>  **Input:** <chain JSON / config files>  **Failure rate assumed:** <30%>

## Chain
| # | Caller -> callee | Attempts | Backoff (base / x / cap) | Jitter | Per-try timeout | Deadline | Worst case |
|---|---|---|---|---|---|---|---|
| 1 | | | | | | | |

**Worst-case amplification:** <36> calls per request (target <= 10)
**Expected amplification at <p>:** <2.7>

## Findings
| Severity | Layer | Code | Problem | Fix |
|---|---|---|---|---|
| high | | | | |

## Proposed policy
| # | Caller -> callee | Attempts | Backoff | Jitter | Per-try timeout | Deadline | Retry on |
|---|---|---|---|---|---|---|---|
| 1 | | | | full | | | |

**New worst-case amplification:** <n>  **New worst-case latency:** <s>

## Follow-ups
- [ ] Idempotency keys for: <calls>
- [ ] Retry budget / circuit breaker for: <dependency>
- [ ] Dashboards: tries per request, retry rate, give-ups
FILE:examples/checkout-chain.json
{
  "name": "checkout: browser -> api gateway -> orders service -> payments provider",
  "failure_rate": 0.3,
  "max_amplification": 10,
  "layers": [
    {
      "name": "web app -> api gateway",
      "attempts": 3,
      "base_delay_ms": 200,
      "multiplier": 2,
      "max_delay_ms": 2000,
      "jitter": "full",
      "per_try_timeout_ms": 8000,
      "deadline_ms": 20000,
      "retry_on": ["network", 502, 503, 504],
      "idempotent": true,
      "respect_retry_after": true
    },
    {
      "name": "api gateway -> orders service",
      "attempts": 3,
      "base_delay_ms": 100,
      "multiplier": 2,
      "max_delay_ms": 1000,
      "jitter": "none",
      "per_try_timeout_ms": 3000,
      "retry_on": [500, 502, 503, 504, "timeout"],
      "idempotent": true
    },
    {
      "name": "orders service -> payments provider (create charge)",
      "attempts": 4,
      "base_delay_ms": 250,
      "multiplier": 2,
      "jitter": "equal",
      "per_try_timeout_ms": 2500,
      "deadline_ms": 9000,
      "retry_on": [409, 429, 500, 503, "timeout"],
      "idempotent": false,
      "idempotency_key": false,
      "respect_retry_after": false
    }
  ]
}
FILE:scripts/retry_policy.py
#!/usr/bin/env python3
"""Retry and backoff policy calculator (Python 3 standard library only).

Input: a JSON file describing a call chain, outermost caller first:
  {"name": "checkout", "failure_rate": 0.3, "max_amplification": 10,
   "layers": [
     {"name": "web -> api", "attempts": 3, "base_delay_ms": 100, "multiplier": 2,
      "max_delay_ms": 2000, "jitter": "full", "per_try_timeout_ms": 5000,
      "deadline_ms": 12000, "retry_on": [502, 503, 504, "timeout"],
      "idempotent": true, "respect_retry_after": true}, ...]}
attempts = total tries including the first one (attempts 1 = no retries).
jitter: none | full | equal | decorrelated.  Delay before retry k (k = 1..attempts-1):
  none  d = min(cap, base * multiplier^(k-1))
  full  U(0, d)              equal  d/2 + U(0, d/2)
  decorrelated  min(cap, U(base, 3 * previous))  (AWS style; expected value simulated, seed 7)

Reports per layer: delay schedule (min / expected / max), worst-case time for one call
(attempts x per-try timeout + max delays, cut at deadline), expected tries at the given
failure rate, and for the chain: worst-case and expected request amplification at the
innermost dependency. Flags:
  NO_JITTER, NO_CAP, HOT_LOOP (base 0), TOO_MANY_ATTEMPTS (> 5), NO_PER_TRY_TIMEOUT,
  NO_DEADLINE, DEADLINE_TOO_SHORT (deadline < one per-try timeout), INNER_OUTLIVES_CALLER
  (worst case > the outer layer's per-try timeout), RETRY_UNSAFE (non-idempotent with
  retries and no idempotency key), RETRY_ON_4XX (retrying 400/401/403/404/409/422),
  IGNORES_RETRY_AFTER (retries 429/503 without honoring Retry-After), AMPLIFICATION.
Usage:
  python3 retry_policy.py chain.json
  python3 retry_policy.py chain.json --failure-rate 0.5 --json
  python3 retry_policy.py --self-test
Exit code: 0 OK, 1 high-severity flags found, 2 bad input.
"""
import argparse
import json
import random
import sys

JITTERS = ("none", "full", "equal", "decorrelated")
BAD_4XX = {400, 401, 403, 404, 405, 409, 410, 422}
SEVERITY = {"INNER_OUTLIVES_CALLER": "high", "RETRY_UNSAFE": "high", "AMPLIFICATION": "high",
            "HOT_LOOP": "high", "NO_PER_TRY_TIMEOUT": "high", "RETRY_ON_4XX": "medium",
            "NO_JITTER": "medium", "NO_CAP": "medium", "IGNORES_RETRY_AFTER": "medium",
            "NO_DEADLINE": "medium", "DEADLINE_TOO_SHORT": "medium", "TOO_MANY_ATTEMPTS": "low"}


class PolicyError(ValueError):
    pass


def num(layer, key, default=None, minimum=0):
    v = layer.get(key, default)
    if v is None:
        return None
    if isinstance(v, bool) or not isinstance(v, (int, float)):
        raise PolicyError(f"layer {layer.get('name', '?')!r}: {key} must be a number")
    if v < minimum:
        raise PolicyError(f"layer {layer.get('name', '?')!r}: {key} must be >= {minimum}")
    return v


def delays(layer):
    """Return [(min, expected, max)] in ms for each retry."""
    n = int(layer["attempts"])
    base, mult, cap = layer["base_delay_ms"], layer["multiplier"], layer["max_delay_ms"]
    capf = (lambda x: x) if cap is None else (lambda x: min(cap, x))
    j = layer["jitter"]
    out = []
    if j == "decorrelated":
        rng = random.Random(7)
        runs = 20000
        sums = [0.0] * (n - 1)
        for _ in range(runs):
            prev = base
            for k in range(n - 1):
                prev = capf(rng.uniform(base, prev * 3)) if base > 0 else 0.0
                sums[k] += prev
        hi = base
        for k in range(n - 1):
            hi = capf(hi * 3)
            out.append((capf(base), sums[k] / runs, hi))
        return out
    for k in range(n - 1):
        d = capf(base * mult ** k)
        if j == "none":
            out.append((d, d, d))
        elif j == "full":
            out.append((0.0, d / 2, d))
        else:
            out.append((d / 2, d * 0.75, d))
    return out


def normalize(raw):
    layers = raw.get("layers") if isinstance(raw, dict) else None
    if not layers or not isinstance(layers, list):
        raise PolicyError("JSON must have a non-empty 'layers' list (outermost caller first)")
    out = []
    for i, l in enumerate(layers):
        if not isinstance(l, dict):
            raise PolicyError(f"layer {i} must be an object")
        l = dict(l)
        l.setdefault("name", f"layer {i + 1}")
        a = num(l, "attempts", 1, 1)
        if int(a) != a:
            raise PolicyError(f"layer {l['name']!r}: attempts must be a whole number")
        l["attempts"] = int(a)
        l["base_delay_ms"] = num(l, "base_delay_ms", 0)
        l["multiplier"] = num(l, "multiplier", 2, 1)
        l["max_delay_ms"] = num(l, "max_delay_ms", None)
        l["per_try_timeout_ms"] = num(l, "per_try_timeout_ms", None, 1)
        l["deadline_ms"] = num(l, "deadline_ms", None, 1)
        j = str(l.get("jitter", "none")).lower()
        if j not in JITTERS:
            raise PolicyError(f"layer {l['name']!r}: jitter must be one of {', '.join(JITTERS)}")
        l["jitter"] = j
        l["retry_on"] = list(l.get("retry_on", []))
        l["idempotent"] = bool(l.get("idempotent", True))
        l["idempotency_key"] = bool(l.get("idempotency_key", False))
        l["respect_retry_after"] = bool(l.get("respect_retry_after", False))
        out.append(l)
    return out


def expected_tries(n, p):
    if p >= 1:
        return float(n)
    return sum(p ** k for k in range(n))


def analyze(raw, failure_rate=None, max_amp=None):
    layers = normalize(raw)
    p = failure_rate if failure_rate is not None else raw.get("failure_rate", 0.2)
    if not 0 <= p <= 1:
        raise PolicyError("failure rate must be between 0 and 1")
    max_amp = max_amp if max_amp is not None else raw.get("max_amplification", 10)
    res = {"name": raw.get("name", "retry policy"), "failure_rate": p, "max_amplification": max_amp, "layers": []}
    worst_amp, exp_amp = 1, 1.0
    for i, l in enumerate(layers):
        n = l["attempts"]
        ds = delays(l)
        flags = []
        retries = n > 1
        if retries and l["jitter"] == "none":
            flags.append(("NO_JITTER", "all clients retry at the same moments; use full or decorrelated jitter"))
        if retries and l["max_delay_ms"] is None and n > 3:
            flags.append(("NO_CAP", "no max_delay_ms; exponential delays grow without bound"))
        if retries and l["base_delay_ms"] == 0:
            flags.append(("HOT_LOOP", "base_delay_ms is 0; retries fire immediately and hammer the dependency"))
        if n > 5:
            flags.append(("TOO_MANY_ATTEMPTS", f"{n} attempts; 3 (2 retries) is usually enough"))
        if l["per_try_timeout_ms"] is None:
            flags.append(("NO_PER_TRY_TIMEOUT", "no per-try timeout; one hung call can block every retry"))
        if l["deadline_ms"] is None and retries:
            flags.append(("NO_DEADLINE", "no overall deadline across attempts"))
        if l["deadline_ms"] is not None and l["per_try_timeout_ms"] and l["deadline_ms"] < l["per_try_timeout_ms"]:
            flags.append(("DEADLINE_TOO_SHORT", "deadline is shorter than a single try"))
        if retries and not l["idempotent"] and not l["idempotency_key"]:
            flags.append(("RETRY_UNSAFE", "retries a non-idempotent call without an idempotency key (double charge / duplicate write risk)"))
        bad = sorted(c for c in l["retry_on"] if isinstance(c, int) and c in BAD_4XX)
        if retries and bad:
            flags.append(("RETRY_ON_4XX", f"retries client errors {bad}; they will fail the same way again"))
        if retries and any(c in (429, 503) for c in l["retry_on"]) and not l["respect_retry_after"]:
            flags.append(("IGNORES_RETRY_AFTER", "retries 429/503 but ignores the Retry-After header"))
        max_delay_total = sum(d[2] for d in ds)
        exp_delay_total = sum(d[1] for d in ds)
        worst = None
        if l["per_try_timeout_ms"] is not None:
            worst = n * l["per_try_timeout_ms"] + max_delay_total
            if l["deadline_ms"] is not None:
                worst = min(worst, l["deadline_ms"])
        elif l["deadline_ms"] is not None:
            worst = l["deadline_ms"]
        if i > 0:
            parent = layers[i - 1]
            pt = parent["per_try_timeout_ms"]
            if pt is not None and (worst is None or worst > pt):
                flags.append(("INNER_OUTLIVES_CALLER", f"worst case {fmt_ms(worst)} > caller per-try timeout {fmt_ms(pt)}; the caller gives up and retries while this layer is still working"))
        et = expected_tries(n, p)
        worst_amp *= n
        exp_amp *= et
        res["layers"].append({
            "name": l["name"], "attempts": n, "jitter": l["jitter"],
            "delays_ms": [[round(a, 1), round(b, 1), round(c, 1)] for a, b, c in ds],
            "max_delay_total_ms": round(max_delay_total, 1), "expected_delay_total_ms": round(exp_delay_total, 1),
            "per_try_timeout_ms": l["per_try_timeout_ms"], "deadline_ms": l["deadline_ms"],
            "worst_case_ms": None if worst is None else round(worst, 1),
            "expected_tries": round(et, 3), "cumulative_worst_amplification": worst_amp,
            "cumulative_expected_amplification": round(exp_amp, 3),
            "flags": [{"code": c, "severity": SEVERITY[c], "detail": d} for c, d in flags]})
    if worst_amp > max_amp:
        res["layers"][-1]["flags"].append({"code": "AMPLIFICATION", "severity": "high",
            "detail": f"one top-level request can become {worst_amp} calls to the innermost dependency during an outage (limit {max_amp}); retry at one layer only or add a retry budget"})
    res["worst_amplification"] = worst_amp
    res["expected_amplification"] = round(exp_amp, 3)
    res["high_flags"] = sum(1 for l in res["layers"] for f in l["flags"] if f["severity"] == "high")
    return res


def fmt_ms(v):
    if v is None:
        return "unbounded"
    if v >= 1000:
        s = f"{v / 1000:.2f}".rstrip("0").rstrip(".")
        return s + " s"
    return f"{v:g} ms"


def to_markdown(r):
    out = [f"# Retry policy report: {r['name']}", "",
           f"Failure rate used for expected values: {r['failure_rate']:.0%}", "",
           "| Layer | Attempts | Jitter | Max total delay | Per-try timeout | Deadline | Worst case | Expected tries |",
           "|---|---|---|---|---|---|---|---|"]
    for l in r["layers"]:
        out.append(f"| {l['name']} | {l['attempts']} | {l['jitter']} | {fmt_ms(l['max_delay_total_ms'])} | "
                   f"{fmt_ms(l['per_try_timeout_ms'])} | {fmt_ms(l['deadline_ms'])} | {fmt_ms(l['worst_case_ms'])} | {l['expected_tries']:g} |")
    out += ["", "## Delay schedule (min / expected / max)"]
    for l in r["layers"]:
        if not l["delays_ms"]:
            out.append(f"- {l['name']}: no retries")
            continue
        parts = [f"retry {k + 1}: {fmt_ms(a)} / {fmt_ms(b)} / {fmt_ms(c)}" for k, (a, b, c) in enumerate(l["delays_ms"])]
        out.append(f"- {l['name']}: " + "; ".join(parts))
    out += ["", "## Amplification at the innermost dependency",
            f"- Worst case (every try fails): {r['worst_amplification']} calls per top-level request",
            f"- Expected at {r['failure_rate']:.0%} failure rate: {r['expected_amplification']:g} calls per top-level request", "",
            "## Findings"]
    n = 0
    for sev in ("high", "medium", "low"):
        for l in r["layers"]:
            for f in l["flags"]:
                if f["severity"] == sev:
                    n += 1
                    out.append(f"- **{sev.upper()} {f['code']}** ({l['name']}): {f['detail']}")
    if not n:
        out.append("- No findings.")
    return "\n".join(out)


def self_test():
    cases = []

    def ok(name, cond):
        cases.append((name, bool(cond)))

    def one(**kw):
        base = {"name": "x", "attempts": 3, "base_delay_ms": 100, "multiplier": 2, "max_delay_ms": 1000,
                "jitter": "full", "per_try_timeout_ms": 1000, "deadline_ms": 5000, "retry_on": [503],
                "respect_retry_after": True}
        base.update(kw)
        return base

    def codes(r, i=0):
        return {f["code"] for f in r["layers"][i]["flags"]}

    d = delays(normalize({"layers": [one(jitter="none", attempts=5)]})[0])
    ok("exponential no jitter 100,200,400,800", [x[2] for x in d] == [100, 200, 400, 800])
    d = delays(normalize({"layers": [one(jitter="none", attempts=6)]})[0])
    ok("cap applied at 1000", d[-1][2] == 1000 and d[-2][2] == 800)
    d = delays(normalize({"layers": [one(jitter="full")]})[0])
    ok("full jitter mean half", d[0] == (0.0, 50.0, 100))
    d = delays(normalize({"layers": [one(jitter="equal")]})[0])
    ok("equal jitter 50..100 mean 75", d[0] == (50.0, 75.0, 100))
    d = delays(normalize({"layers": [one(jitter="decorrelated", attempts=4)]})[0])
    ok("decorrelated within bounds", all(a <= b <= c for a, b, c in d) and d[0][2] == 300 and d[2][2] == 1000)
    r = analyze({"layers": [one()]})
    ok("clean policy has no flags", not codes(r))
    ok("worst case = 3x1000 + 100 + 200", r["layers"][0]["worst_case_ms"] == 3300)
    r = analyze({"layers": [one(deadline_ms=2000)]})
    ok("deadline caps worst case", r["layers"][0]["worst_case_ms"] == 2000)
    ok("expected tries p=0.5 n=3 is 1.75", expected_tries(3, 0.5) == 1.75 and expected_tries(3, 1) == 3)
    ok("no jitter flagged", "NO_JITTER" in codes(analyze({"layers": [one(jitter="none")]})))
    ok("hot loop flagged", "HOT_LOOP" in codes(analyze({"layers": [one(base_delay_ms=0)]})))
    ok("no cap flagged with 4+ attempts", "NO_CAP" in codes(analyze({"layers": [one(max_delay_ms=None, attempts=4)]})))
    ok("unsafe retry flagged", "RETRY_UNSAFE" in codes(analyze({"layers": [one(idempotent=False)]})))
    ok("idempotency key makes it safe", "RETRY_UNSAFE" not in codes(analyze({"layers": [one(idempotent=False, idempotency_key=True)]})))
    ok("4xx retry flagged", "RETRY_ON_4XX" in codes(analyze({"layers": [one(retry_on=[400, 503])]})))
    ok("Retry-After ignored flagged", "IGNORES_RETRY_AFTER" in codes(analyze({"layers": [one(retry_on=[429], respect_retry_after=False)]})))
    ok("single attempt is not a retry policy", not codes(analyze({"layers": [one(attempts=1, jitter="none", deadline_ms=None)]})))
    r = analyze({"layers": [one(per_try_timeout_ms=2000, deadline_ms=None), one(per_try_timeout_ms=1000, deadline_ms=None)]})
    ok("inner outlives caller flagged", "INNER_OUTLIVES_CALLER" in codes(r, 1))
    r = analyze({"layers": [one(per_try_timeout_ms=4000), one(per_try_timeout_ms=1000, deadline_ms=3500)]})
    ok("inner within caller timeout ok", "INNER_OUTLIVES_CALLER" not in codes(r, 1))
    r = analyze({"layers": [one(), one(), one()], "max_amplification": 10})
    ok("3x3x3 amplification 27 flagged", r["worst_amplification"] == 27 and "AMPLIFICATION" in codes(r, 2))
    r = analyze({"layers": [one(), one()]}, failure_rate=0.5)
    ok("expected amplification 1.75^2", r["expected_amplification"] == 3.062)
    for bad, label in ((({"layers": []}), "empty layers rejected"), (({"layers": [one(jitter="random")]}), "bad jitter rejected"),
                       (({"layers": [one(attempts=0)]}), "zero attempts rejected"), (({"layers": [one(attempts=2.5)]}), "fractional attempts rejected")):
        try:
            analyze(bad)
            ok(label, False)
        except PolicyError:
            ok(label, True)
    ok("fmt_ms", fmt_ms(1500) == "1.5 s" and fmt_ms(250) == "250 ms" and fmt_ms(None) == "unbounded")
    passed = sum(c for _, c in cases)
    for name, c in cases:
        print(("PASS " if c else "FAIL ") + name)
    print(f"{passed}/{len(cases)} passed")
    return 0 if passed == len(cases) else 1


def main(argv=None):
    ap = argparse.ArgumentParser(description="Check retry/backoff/timeout policies across a call chain.")
    ap.add_argument("file", nargs="?", help="chain JSON ('-' for stdin)")
    ap.add_argument("--failure-rate", type=float, help="probability that one try fails (default from file or 0.2)")
    ap.add_argument("--max-amplification", type=float, help="flag chains above this worst-case amplification (default 10)")
    ap.add_argument("--json", action="store_true")
    ap.add_argument("--self-test", action="store_true")
    a = ap.parse_args(argv)
    if a.self_test:
        return self_test()
    if not a.file:
        ap.error("chain JSON required (or --self-test)")
    try:
        raw = json.load(sys.stdin if a.file == "-" else open(a.file, encoding="utf-8"))
        r = analyze(raw, a.failure_rate, a.max_amplification)
    except (OSError, PolicyError, ValueError) as e:
        print(f"error: {e}", file=sys.stderr)
        return 2
    print(json.dumps(r, indent=2) if a.json else to_markdown(r))
    return 1 if r["high_flags"] else 0


if __name__ == "__main__":
    sys.exit(main())