[HARD] Design and implement persistent brute-force lockout for authentication, beyond basic per-IP rate limiting

[Emmyt24]

Description

The smaller, already-tracked ticket on this board adds basic per-IP middleware rate limiting to /v1/auth/login/signup, which helps but is trivially bypassed by an attacker rotating source IPs (residential proxy pools, botnets) while still hammering one specific target account. This ticket asks for the fuller, persistent policy: per-account lockout (independent of source IP) backed by the database, with a sane unlock path, that composes with (rather than replaces) the per-IP layer.

Requirements and Context

• Design and justify, in the PR description, a concrete lockout policy: e.g. N consecutive failed attempts for a given email within a rolling window triggers a temporary lockout of that account (independent of which IP the attempts came from), with exponential backoff on repeated lockout triggers. State your chosen N, window, and backoff curve and why.
• Persist failed-attempt counters and lockout state in the database (new table/columns — a new migration), not in-memory, so lockout state survives a server restart and is consistent across horizontally-scaled API instances.
• The lockout check must not reveal account existence: a locked-out nonexistent email and a locked-out real email must return indistinguishable responses, consistent with login's existing enumeration-safe error message.
• A successful login must reset the failed-attempt counter for that account.
• Consider and document (implementing if reasonably scoped, or explicitly deferring with justification if not) an admin/support unlock path, since a legitimate user who trips the lockout needs some way back in.

Suggested Execution

Branch: hard/api/persistent-auth-lockout

Implement Changes

• Add the new migration and Store methods for tracking/checking/resetting failed-attempt state.
• Wire the check into crates/api/src/auth.rs::login before/around the existing password verification, preserving the current enumeration-safe error message for every rejection path (bad password, locked out, nonexistent account).
• Compose with (don't replace) the existing or in-progress per-IP rate-limiting layer — both should be able to coexist.

Test and Commit

• account_is_locked_out_after_the_configured_threshold_of_failed_attempts_regardless_of_source_ip.
• locked_out_response_is_indistinguishable_from_a_normal_bad_password_response.
• successful_login_resets_the_failed_attempt_counter.
• lockout_state_persists_across_a_simulated_process_restart (i.e. survives being read from a fresh Store connection, proving it's not in-memory-only).
• lockout_and_per_ip_rate_limiting_compose_without_conflicting.
• Run cargo test -p octo-api && cargo build --release locally before committing.

Example Commit Message

feat(api): add persistent, per-account brute-force lockout for login

Per-IP rate limiting alone is trivially bypassed by rotating source IPs
against one target account. Adds a database-persisted, per-account lockout
policy with exponential backoff, composing with (not replacing) the existing
IP-level throttle, while preserving the enumeration-safe error contract.

Guidelines

• Justify every numeric choice (threshold, window, backoff curve) in the PR description — these are judgment calls, and reviewers need your reasoning, not just the numbers.
• Never let any new response path leak whether an email is registered — audit every new branch against the existing enumeration-safe convention in login.
• Reference this issue with Closes #<issue-number> in the PR description.

Submission Requirements (Hard Issue)

This is one of the hard/complex issues on this tracker. It touches more than one crate (or backend + frontend), so review will look for a design paragraph in the PR description explaining the approach you chose and any tradeoffs. In addition to the Guidelines above, your PR description must include:

• A screenshot of the full test suite passing locally (e.g. the tail of cargo test --workspace or npm run build && npx vitest run showing a green/passing summary).
• A screenshot of the feature building successfully end-to-end (e.g. cargo build --release for Rust changes, or npm run build for frontend changes) with no errors.
• PRs missing either screenshot will be asked to add them before review starts.