Rate limit a route handler with Postgres and no extra service

Open · 131 viewsasked 2 weeks ago by

A fixed one-minute window, counted in the database you already pay for. Good enough to stop someone looping your AI endpoint. Delete old rows with a daily cron. For serious abuse you want something in front of the app, not in it.

Snippet

Copied 7 times

create table if not exists rate_hits (
  key text not null,
  window_start timestamptz not null,
  hits int not null default 0,
  primary key (key, window_start)
);

create or replace function take_token(p_key text, p_limit int)
returns boolean language plpgsql as $$
declare
  w timestamptz := date_trunc('minute', now());
  n int;
begin
  insert into rate_hits (key, window_start, hits)
  values (p_key, w, 1)
  on conflict (key, window_start)
  do update set hits = rate_hits.hits + 1
  returning hits into n;
  return n <= p_limit;
end;
$$;

// route handler
const { data: allowed } = await supabaseAdmin.rpc("take_token", { p_key: userId, p_limit: 20 });
if (!allowed) return new Response("Too many requests", { status: 429 });

Comment

For a single-instance app this is plenty. Make sure the upsert returns the new count so it's one round trip.

chidi_eze 2 weeks ago

It does, inside the function. Good to spell out though.

lena_ops 2 weeks ago

Keyboard shortcuts

Rate limit a route handler with Postgres and no extra service

Snippet

Activity