| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Generic rate-limit counter queries (S35). Two write paths: |
| 4 | -- * BumpRateLimit — atomic UPSERT that rolls the window forward |
| 5 | -- when stale, increments hits otherwise. Returns |
| 6 | -- the post-update hits + window_started_at so |
| 7 | -- the caller can compute Retry-After without a |
| 8 | -- second round trip. |
| 9 | -- * BumpSignupIPThrottle — same shape against signup_ip_throttle |
| 10 | -- keyed by inet/CIDR. |
| 11 | -- |
| 12 | -- Reads (PeekRateLimit, PeekSignupIPThrottle) are kept around for |
| 13 | -- the admin observability surface; the hot path uses Bump-and-decide. |
| 14 | |
| 15 | -- name: BumpRateLimit :one |
| 16 | -- Roll-or-increment in one statement. The CASE in the UPDATE branch |
| 17 | -- handles the window roll: when the existing window started before |
| 18 | -- (now - $3 interval), we treat it as a new window and reset hits |
| 19 | -- to 1; otherwise we increment in place. |
| 20 | INSERT INTO rate_limits (scope, key, hits, window_started_at) |
| 21 | VALUES (sqlc.arg(scope), sqlc.arg(key), 1, now()) |
| 22 | ON CONFLICT (scope, key) |
| 23 | DO UPDATE SET |
| 24 | hits = CASE |
| 25 | WHEN rate_limits.window_started_at < now() - sqlc.arg(ttl)::interval |
| 26 | THEN 1 |
| 27 | ELSE rate_limits.hits + 1 |
| 28 | END, |
| 29 | window_started_at = CASE |
| 30 | WHEN rate_limits.window_started_at < now() - sqlc.arg(ttl)::interval |
| 31 | THEN now() |
| 32 | ELSE rate_limits.window_started_at |
| 33 | END |
| 34 | RETURNING hits, window_started_at; |
| 35 | |
| 36 | -- name: PeekRateLimit :one |
| 37 | SELECT scope, key, hits, window_started_at |
| 38 | FROM rate_limits |
| 39 | WHERE scope = $1 AND key = $2; |
| 40 | |
| 41 | -- name: AcquireRateLimitLease :one |
| 42 | -- Concurrent-lease variant for long-lived streams. `hits` is the |
| 43 | -- currently-held lease count. The ttl rolls stale rows forward so a process |
| 44 | -- crash or severed TCP connection cannot consume capacity indefinitely. |
| 45 | INSERT INTO rate_limits (scope, key, hits, window_started_at) |
| 46 | VALUES (sqlc.arg(scope), sqlc.arg(key), 1, now()) |
| 47 | ON CONFLICT (scope, key) |
| 48 | DO UPDATE SET |
| 49 | hits = CASE |
| 50 | WHEN rate_limits.window_started_at < now() - sqlc.arg(ttl)::interval |
| 51 | THEN 1 |
| 52 | ELSE rate_limits.hits + 1 |
| 53 | END, |
| 54 | window_started_at = CASE |
| 55 | WHEN rate_limits.window_started_at < now() - sqlc.arg(ttl)::interval |
| 56 | THEN now() |
| 57 | ELSE rate_limits.window_started_at |
| 58 | END |
| 59 | WHERE rate_limits.window_started_at < now() - sqlc.arg(ttl)::interval |
| 60 | OR rate_limits.hits < sqlc.arg(max_hits)::integer |
| 61 | RETURNING hits, window_started_at; |
| 62 | |
| 63 | -- name: ReleaseRateLimitLease :execrows |
| 64 | UPDATE rate_limits |
| 65 | SET hits = GREATEST(hits - 1, 0) |
| 66 | WHERE scope = $1 AND key = $2; |
| 67 | |
| 68 | -- name: PruneRateLimits :execrows |
| 69 | DELETE FROM rate_limits |
| 70 | WHERE window_started_at < now() - sqlc.arg(retention)::interval; |
| 71 | |
| 72 | -- name: BumpSignupIPThrottle :one |
| 73 | -- Same UPSERT shape against the inet-keyed signup throttle. |
| 74 | INSERT INTO signup_ip_throttle (cidr, hits, window_started_at) |
| 75 | VALUES (sqlc.arg(cidr), 1, now()) |
| 76 | ON CONFLICT (cidr) |
| 77 | DO UPDATE SET |
| 78 | hits = CASE |
| 79 | WHEN signup_ip_throttle.window_started_at < now() - sqlc.arg(ttl)::interval |
| 80 | THEN 1 |
| 81 | ELSE signup_ip_throttle.hits + 1 |
| 82 | END, |
| 83 | window_started_at = CASE |
| 84 | WHEN signup_ip_throttle.window_started_at < now() - sqlc.arg(ttl)::interval |
| 85 | THEN now() |
| 86 | ELSE signup_ip_throttle.window_started_at |
| 87 | END |
| 88 | RETURNING hits, window_started_at; |
| 89 | |
| 90 | -- name: PruneSignupIPThrottle :execrows |
| 91 | DELETE FROM signup_ip_throttle |
| 92 | WHERE window_started_at < now() - sqlc.arg(retention)::interval; |
| 93 |