# Rate limit checkout in Postgres, before the provider sees it > One small table and one atomic upsert keep a looping user from spending your payment provider's API limit, across every serverless instance, with no Redis. Every "Buy" click on a hosted checkout is an API call to your payment provider. So is every "Manage billing" click, and every success-page check that asks the provider whether the payment went through. The provider rate-limits your account, not your customer. One signed-in user with a script that loops `/billing/checkout` spends the same budget your real buyers need. When it runs out, the provider starts answering 429, and checkout fails for everyone until it refills. So count those calls yourself, per user, and refuse the one over the limit before it leaves your server. ## Why not a Map in memory A `Map` of counters works on a laptop. On serverless it counts per instance. Ten instances means ten separate counts, and the platform adds instances exactly when traffic spikes, which is when you need the limit. You already have Postgres (billing cannot work without it), so the counters live there. One small table: ```sql create table billing_rate_limits ( key text primary key, window_start timestamptz not null default CURRENT_TIMESTAMP, hits integer not null default 1 ); ``` ## One statement per attempt Read the count, check it, then write it back, and two requests at once both read 9 and both get through a limit of 10. Do the whole thing in one upsert instead. Postgres locks the row, so concurrent requests queue on it and each gets its own count back: ```sql insert into billing_rate_limits (key, window_start, hits) values ($1, $2::timestamptz, 1) on conflict (key) do update set hits = case when billing_rate_limits.window_start <= $3::timestamptz then 1 else billing_rate_limits.hits + 1 end, window_start = case when billing_rate_limits.window_start <= $3::timestamptz then excluded.window_start else billing_rate_limits.window_start end returning hits, window_start; ``` `$2` is now and `$3` is now minus the window. A window that has ended starts again at 1; otherwise the count goes up. The returned `hits` includes this attempt, so the rule is simply `hits <= limit`. Seconds until the window ends (`window_start + window - now`) is your `Retry-After`. This is a fixed window, not a sliding one. Across a window boundary someone can get up to twice the limit. For "stop a loop from draining the provider" that is fine, and it costs one row per key instead of one row per attempt. Bind times as ISO strings with an explicit cast, as above. A JavaScript `Date` bound into raw SQL is serialised differently by each driver, and postgres.js sends one Postgres refuses. ## What to count Count every provider-calling action twice: per user, and per client address. Per user stops one account looping. Per address stops the loop that signs up a fresh account whenever the per-user count runs out, which a per-user limit alone never does: every new account starts at zero. That goes for every action, not only checkout. A success page that asks the provider on each render, counted per user only, hands each fresh account its own budget. | Key | Limit (default) | Why | |---|---|---| | `checkout:user:` | 10 per 10 minutes | nobody buys ten times in ten minutes | | `checkout:ip:

` | 60 per 10 minutes | fresh accounts looping the success page | Name the action in the key, so a user's checkout and portal counts never share a row. Count IPv6 addresses per /64: one home line gets a whole /64 and can rotate through it. Keep the pairs in one table in code (`BILLING_ACTION_LIMITS`) and give callers one function per action (`enforceBillingLimit("sync", userId)`), so nobody can add a provider call and count only the user. ## Which address to trust A per-address count is only as good as the address. Read it from a header the visitor typed and it does two bad things at once: anyone can put a stranger's address there and lock them out of checkout, and a loop can send a new one on every request and never be counted. - **Vercel** overwrites `x-forwarded-for` with the address it saw. Trust it there (`VERCEL=1` says you are there). - **`next start` with nothing in front** trusts nothing. Next keeps an `x-forwarded-for` the visitor sent and only fills it in when it is missing. - **Your own proxy or host**: name the one header it sets, in an env var (`CLIENT_IP_HEADER` in this repo, which `src/proxy.ts` copies into `x-client-ip` for every reader): `cf-connecting-ip` on Cloudflare, `fly-client-ip` on Fly.io, `x-real-ip` from nginx's `proxy_set_header X-Real-IP $remote_addr`. - If the header holds a list, count the **last** entry. A proxy that appends adds what it saw at the end; everything before it came from the visitor. - A trusted header that is missing or holds no address means the request went around the proxy, or the setting is wrong. Count all of those as one address (`unknown`) instead of skipping them, or "send no address" becomes the way past the limit. Log it once so a wrong setting gets noticed. - No trusted header: turn the per-address counts off and log that once. A spoofable address is worse than none: it adds the lockout and still stops nothing. The per-user counts still hold. ## Where the check goes - **After** the checks that cost nothing and need no count: an unknown price, an admin viewing as the user. - **Before** anything that reads the database for the purchase or calls the provider. The count is one small write; the provider call is the expensive part you are protecting. - **Inside the core function** (`startCheckout`, `createPortalUrl`), not only in the route. A server action someone adds later gets the limit for free. - **Never on webhooks.** The provider sends them. Refusing one only makes it retry, and a missed webhook is a customer who paid and got nothing. ## When the count fails If the limiter's own query fails, the database is probably down. Fail closed: do not call the provider, log it once, and tell the buyer "Billing is not available right now. Nothing was charged." A limiter that lets calls through whenever it breaks protects nothing on the day it matters. The one exception is a signed-out visitor on their way to sign up. That hop never reaches the provider, so let them through; the signed-in checkout that follows counts again and fails closed there. ## What the buyer sees A page flow never shows a 500. Over the limit, redirect to the billing page with a notice: "Too many attempts. Nothing was charged. Try again in a few minutes." A route handler that answers JSON returns 429 with `Retry-After`, which HTTP clients and SDKs already understand. ## Keep the table small Every new user and address adds a row, and nothing else removes them. When a count comes back as 1 (a new window, so maybe a new key), delete rows whose window started longer ago than the longest window, after the response is sent (`after()` in Next.js). Those rows are expired, so deleting them changes no answer. ## Prove it - Unit-test the pure parts: the decision for a count, the key builder, the address parsing (only the trusted header, its last entry, `unknown` for a missing one), and that the statement binds strings, not dates. - Loop fresh accounts from one address through every action (checkout, portal, success page) and assert the provider was called no more than the per-address limit in total. - Drive the real route N+1 times with the provider faked and assert the provider was called exactly N times. - Against a real database, sign up a fresh user, request checkout N+1 times, and check that the last one lands on the "Too many attempts" notice and the row says N+1. Give each test run a fresh account and a fresh address: the counts outlive the run, and a rerun inside the window starts over the limit. --- Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once. - Site map for agents: https://agenticboilerplate.com/llms.txt - Public API: https://agenticboilerplate.com/openapi.json - Contact: agenticstudio@gmail.com