-- R01-N06: persistent per-(ip, email) throttle for /auth/local.
--
-- The local-admin sign-in path had no rate limiting — an attacker could
-- brute-force the password as fast as the server could respond. Audit rows
-- captured each attempt but nothing blocked the next one.
--
-- One row per (ip_address, email) pair seen with a recent failure. Counter
-- is reset on a successful login (DELETE) and naturally rolls over after a
-- 15-minute idle window. Lock thresholds are policy code in
-- AuthThrottleRepository::computeLockout(); the table stores only counts +
-- timestamps.

CREATE TABLE auth_throttle (
    ip_address        TEXT NOT NULL,
    email             TEXT NOT NULL,
    attempts          INTEGER NOT NULL DEFAULT 0,
    first_failure_at  TEXT NOT NULL,
    last_failure_at   TEXT NOT NULL,
    locked_until      TEXT,
    PRIMARY KEY (ip_address, email)
);

CREATE INDEX idx_auth_throttle_locked ON auth_throttle(locked_until);