82 lines
3.7 KiB
SQL
82 lines
3.7 KiB
SQL
-- ============================================================================
|
||
-- Krow — rate limit counters
|
||
--
|
||
-- Phase 5. One row per (bucket, window), counting requests.
|
||
--
|
||
-- WHY POSTGRES AND NOT REDIS
|
||
--
|
||
-- The existing limiter (httpserver/ratelimit.go) is an in-process map, which
|
||
-- has a failure mode that is easy to miss: behind N instances the effective
|
||
-- limit is N times the configured one, because each instance counts only what
|
||
-- it saw. A limit that silently multiplies by the replica count is not a limit.
|
||
--
|
||
-- Redis would work and is the conventional answer. It is not the right answer
|
||
-- here: this service has exactly one piece of shared infrastructure, and adding
|
||
-- a second means another thing to run, monitor, secure and fail over — for a
|
||
-- counter. Postgres already provides the one primitive this needs, an atomic
|
||
-- read-modify-write, in a single statement:
|
||
--
|
||
-- INSERT … ON CONFLICT (bucket, window_start) DO UPDATE
|
||
-- SET count = rate_limits.count + 1
|
||
-- RETURNING count
|
||
--
|
||
-- That is correct under concurrency without a transaction, without a lock taken
|
||
-- in application code, and without a round trip to decide anything.
|
||
--
|
||
-- FIXED WINDOWS, NOT A SLIDING LOG
|
||
--
|
||
-- A sliding window is more accurate and costs a row per request. A fixed window
|
||
-- costs one row per bucket per window and admits a known burst — up to 2× the
|
||
-- limit across a window boundary. For abuse prevention that is an acceptable
|
||
-- trade, and it is the difference between a counter table and an append-only
|
||
-- log nobody wants to sweep.
|
||
--
|
||
-- NO RAW CREDENTIAL IS EVER A BUCKET KEY. Callers hash anything sensitive
|
||
-- before it reaches this table — see internal/ratelimit. A bucket naming a
|
||
-- token would put that token in a table, in a log, and in every EXPLAIN a
|
||
-- developer ever runs.
|
||
--
|
||
-- Target schema: public. No system schema is read or written.
|
||
-- ============================================================================
|
||
|
||
SET search_path = public;
|
||
|
||
CREATE TABLE rate_limits (
|
||
-- The thing being limited: a scope prefix and an already-hashed subject,
|
||
-- e.g. "oauth.register:ip:<sha256>" or "mcp.call:token:<sha256>".
|
||
bucket text NOT NULL,
|
||
|
||
-- The window this count belongs to, truncated to the window size. Part of
|
||
-- the key rather than a column to compare, so a new window is a new row and
|
||
-- expiry is "delete old rows" rather than "reset a counter" — which means
|
||
-- two instances rolling over at once cannot lose each other's increments.
|
||
window_start timestamptz NOT NULL,
|
||
|
||
count integer NOT NULL DEFAULT 0,
|
||
|
||
-- When this row may be deleted. Carried explicitly rather than derived from
|
||
-- window_start plus a duration the cleanup would have to know, so windows of
|
||
-- different sizes can share one table and one sweep.
|
||
expires_at timestamptz NOT NULL,
|
||
|
||
PRIMARY KEY (bucket, window_start),
|
||
|
||
CONSTRAINT rate_limits_count_non_negative CHECK (count >= 0),
|
||
CONSTRAINT rate_limits_expires_after_window CHECK (expires_at > window_start)
|
||
);
|
||
|
||
-- The sweep. Ordered by the column it filters on, so deleting a batch is a
|
||
-- range scan rather than a sequential scan of every live counter.
|
||
CREATE INDEX rate_limits_expires_idx ON rate_limits (expires_at);
|
||
|
||
COMMENT ON TABLE rate_limits IS
|
||
'Fixed-window rate limit counters, shared across API instances. Incremented '
|
||
'with a single atomic INSERT … ON CONFLICT DO UPDATE … RETURNING.';
|
||
|
||
COMMENT ON COLUMN rate_limits.bucket IS
|
||
'Scope plus an ALREADY-HASHED subject. Never a raw token, code or password.';
|
||
|
||
COMMENT ON COLUMN rate_limits.window_start IS
|
||
'Start of the fixed window, truncated to its size. Part of the key so a new '
|
||
'window is a new row rather than a reset of an existing counter.';
|