-- Run this on the VPS's `core` database (same DB api.php connects to
-- for accounts) before deploying the new api.php. Without this table,
-- login rate limiting silently does nothing (it fails open, logged).

CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(191) NOT NULL,
    attempted_at DATETIME NOT NULL,
    INDEX idx_identifier_time (identifier, attempted_at)
);

-- Optional: periodically clear old rows (e.g. via a daily cron) so the
-- table doesn't grow forever. Anything older than a day is useless for
-- rate limiting since the window is only 10 minutes.
-- DELETE FROM login_attempts WHERE attempted_at < (NOW() - INTERVAL 1 DAY);
