Rate limit checkout in Postgres, before the provider sees it

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:

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:

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:<id>	10 per 10 minutes	nobody buys ten times in ten minutes
checkout:ip:<address>	20 per 10 minutes	fresh accounts from one address; a signed-out buyer uses two (the bounce to sign-up, then checkout)
portal:user:<id>	10 per 10 minutes	each portal session is a provider call
portal:ip:<address>	20 per 10 minutes	any account that checked out once can open the portal
sync:user:<id>	60 per 10 minutes	the success page polls the provider every 2 seconds for 30 seconds
sync:ip:<address>	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.