Recording and reading sign-ins that did NOT succeed.
Before this existed, a wrong password produced a flash and nothing else — no row, no activity entry, nothing either the targeted account holder or the site owner could ever see. The only trace was Hammer's in-memory rate-limit counter, which is node-local, lost on restart, counts successes too, and says nothing at all until a bucket overflows.
The case that motivates it: an attacker who guesses correctly on attempt 400 triggers only the new-device email, which is indistinguishable from "I signed in from my new laptop". The 399 failures before it are the one signal that tells those two apart.
Rows are aggregated, not one-per-attempt
record/4 upserts into the unique key
(identifier, ip_network, outcome, bucket_start), where bucket_start is
the current hour. A sustained attack against one account from one network
collapses into a single row per hour whose attempt_count rises, so the
table cannot be grown by attacker effort along that axis.
It CAN still be grown along the identifier axis — an attacker spraying
distinct addresses writes one row each. That is bounded by the rate limiter
in front of it (login_limit * 3 per IP network per minute, so ~900
rows/hour/network at the default) and by retention, but it is the price of
storing the identifier verbatim, which is what makes "someone is hammering
admin@" visible. Requests the limiter REFUSES are past that bound, so a
"rate_limited" row keeps its identifier only when it names a real account
and is otherwise stored as "*" — one row per network per hour.
Write is one statement with no preceding read, so concurrent attempts never contend on a row lock.
It must never block a sign-in
Every public entry point swallows its failures. rescue alone is not enough:
an unreachable database RAISES on an unowned checkout but EXITS on a dead
pool, so the write path catches both. A security log that takes the login
form down with it is worse than no security log.
It must not become an account-existence oracle
record/4 runs on every failing branch and the HTTP response is unchanged by
it — only what is stored differs. The two branches that share the deliberately
generic "Invalid email/username or password" flash both perform the same
lookup, so neither the response nor the work done distinguishes a real
account from a fictitious one.
Summary
Functions
Failures inside 1h that trigger an alert (default 10).
Whether a burst of failures warns the account holder
(failed_login_alert_enabled, default false).
The hour at falls in — the dedup bucket.
How many failed attempts user has accumulated since since.
Whether failed sign-ins are recorded (setting login_attempt_logging_enabled,
default true).
Normalizes an identifier the way it is stored: trimmed, downcased, and truncated to 160 characters.
Deletes buckets last touched more than days ago.
The most recent failed-attempt buckets for user, newest first.
Records one failed sign-in from conn.
The configured retention period in days (login_attempt_retention_days, default 90).
Site-wide totals since since, for the admin view.
The heaviest failed-attempt buckets since since, newest and largest first.
The bucket upsert, extracted so a test can run the real statement rather than a copy that drifts from it.
Functions
@spec alert_threshold() :: pos_integer()
Failures inside 1h that trigger an alert (default 10).
@spec alerts_enabled?() :: boolean()
Whether a burst of failures warns the account holder
(failed_login_alert_enabled, default false).
Defaults OFF because it sends mail, matching new_login_alert_enabled.
@spec bucket_start(DateTime.t()) :: DateTime.t()
The hour at falls in — the dedup bucket.
@spec count_for_user_since(PhoenixKit.Users.Auth.User.t() | UUIDv7.t(), DateTime.t()) :: non_neg_integer()
How many failed attempts user has accumulated since since.
Sums attempt_count, because one row is many attempts. Returns 0 rather
than raising if the table cannot be read.
@spec enabled?() :: boolean()
Whether failed sign-ins are recorded (setting login_attempt_logging_enabled,
default true).
Unlike new_login_alert_enabled, this defaults ON: it writes one bounded row
and sends nothing, and the data is useless retroactively — an install that
turns it on after an incident has already lost the evidence.
Normalizes an identifier the way it is stored: trimmed, downcased, and truncated to 160 characters.
Truncation is not cosmetic — the field is attacker-controlled and otherwise unbounded.
@spec prune(pos_integer()) :: {:ok, non_neg_integer()}
Deletes buckets last touched more than days ago.
@spec recent_for_user(PhoenixKit.Users.Auth.User.t() | UUIDv7.t(), keyword()) :: [ PhoenixKit.Users.LoginAttempt.t() ]
The most recent failed-attempt buckets for user, newest first.
@spec record(Plug.Conn.t(), String.t() | nil, String.t(), keyword()) :: :ok
Records one failed sign-in from conn.
identifier is whatever the person typed in the email/username field.
outcome is one of PhoenixKit.Users.LoginAttempt.outcomes/0.
Pass :user in opts when the caller already holds the account (the
inactive-account branch does), to save a lookup.
Always returns :ok. Never raises, never exits.
@spec retention_days() :: pos_integer()
The configured retention period in days (login_attempt_retention_days, default 90).
@spec stats(DateTime.t()) :: %{ attempts: non_neg_integer(), buckets: non_neg_integer(), accounts: non_neg_integer(), networks: non_neg_integer() }
Site-wide totals since since, for the admin view.
attempts sums attempt_count (what actually happened); buckets counts
rows (how the total is spread); accounts and networks count distinct
targets and sources.
@spec top_since(DateTime.t(), keyword()) :: [PhoenixKit.Users.LoginAttempt.t()]
The heaviest failed-attempt buckets since since, newest and largest first.
Rows carry an attacker-controlled identifier — escape it when rendering.
@spec upsert(map(), DateTime.t(), keyword()) :: {:ok, PhoenixKit.Users.LoginAttempt.t()} | {:error, Ecto.Changeset.t()}
The bucket upsert, extracted so a test can run the real statement rather than a copy that drifts from it.
opts reaches Repo.insert/2; test/integration/prefix_migration_test.exs
passes prefix: to prove the EXCLUDED fragment below survives a
named-schema install, which the public path cannot demonstrate.