`db/migrations/023_rate_limit.sql`

48 lines
-- Postgres-backed token bucket rate limiter. No Redis dependency.
--
-- Bucket per (ip, scope). Window resets when the stored window_start is
-- older than now() - p_window_seconds. Caller compares the returned count
-- against its own per-scope ceiling and 429s if over.
--
-- Designed to fail-OPEN: if PG is unavailable, callers should let the
-- request through rather than 503 the public archive. PG-down is surfaced
-- via /admin or pm2 logs separately.
--
-- Sweep job recommended hourly:
--   DELETE FROM rate_limit_bucket WHERE window_start < now() - interval '24 hours';

CREATE TABLE IF NOT EXISTS rate_limit_bucket (
  ip           inet         NOT NULL,
  scope        text         NOT NULL,
  window_start timestamptz  NOT NULL,
  count        int          NOT NULL DEFAULT 0,
  PRIMARY KEY (ip, scope)
);

CREATE INDEX IF NOT EXISTS rate_limit_bucket_window_idx
  ON rate_limit_bucket (window_start);

CREATE OR REPLACE FUNCTION rate_limit_hit(
  p_ip inet,
  p_scope text,
  p_window_seconds int
) RETURNS int LANGUAGE plpgsql AS $$
DECLARE v_count int;
BEGIN
  INSERT INTO rate_limit_bucket (ip, scope, window_start, count)
  VALUES (p_ip, p_scope, now(), 1)
  ON CONFLICT (ip, scope) DO UPDATE
    SET window_start = CASE
          WHEN rate_limit_bucket.window_start < now() - (p_window_seconds || ' seconds')::interval
            THEN now()
          ELSE rate_limit_bucket.window_start
        END,
        count = CASE
          WHEN rate_limit_bucket.window_start < now() - (p_window_seconds || ' seconds')::interval
            THEN 1
          ELSE rate_limit_bucket.count + 1
        END
  RETURNING count INTO v_count;
  RETURN v_count;
END $$;