| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Generic counter table for auth-related rate limits. The (scope, identifier) |
| 4 | -- pair is unique. window_started_at is the start of the current window; |
| 5 | -- callers reset hits to 1 when the window has elapsed. |
| 6 | -- |
| 7 | -- Examples of (scope, identifier): |
| 8 | -- ('login', '1.2.3.4|alice') |
| 9 | -- ('signup', 'ip:1.2.3.4') |
| 10 | -- ('signup', 'email:foo@bar') |
| 11 | -- ('reset', 'email:foo@bar') |
| 12 | |
| 13 | -- +goose Up |
| 14 | CREATE TABLE auth_throttle ( |
| 15 | id bigserial PRIMARY KEY, |
| 16 | scope text NOT NULL, |
| 17 | identifier text NOT NULL, |
| 18 | hits integer NOT NULL DEFAULT 0, |
| 19 | window_started_at timestamptz NOT NULL DEFAULT now(), |
| 20 | |
| 21 | UNIQUE (scope, identifier) |
| 22 | ); |
| 23 | |
| 24 | CREATE INDEX auth_throttle_window_started_idx ON auth_throttle (window_started_at); |
| 25 | |
| 26 | -- +goose Down |
| 27 | DROP TABLE IF EXISTS auth_throttle; |
| 28 |