How to store OTP codes securely (not as plain hashes)

To store OTP codes securely, never store the code. Store an HMAC of it, keyed with a secret that lives outside the database, and bind the user’s email and the code’s purpose into the hash. Then make verification a single atomic database operation. Here’s why each of those matters — and, since we run exactly this in production, the actual table, the actual verify function, and the numbers we chose.

A plain hash is not enough here

The instinct is to hash the code like a password. SHA-256("418302"), store the digest. Right instinct, wrong maths.

A password has enormous entropy. A 6-digit code has exactly 10⁶ possibilities. An attacker with a database dump can hash all one million candidates in under a second, build a lookup table once, and reverse every code in the table forever. Salting per row stops the shared table but not the per-row brute force — a million hashes per code is still trivial. bcrypt or Argon2 would slow it, but they’re the wrong tool for something verified on every attempt and discarded ten minutes later.

The fix is a pepper: a high-entropy secret held in the server environment, never in the database.

// The stored value. Binding email + purpose closes cross-replay for free.
createHmac("sha256", PEPPER).update(`${email}:${purpose}:${code}`).digest("hex");

Now a database leak alone is worthless — testing even one guess requires the pepper, which lives on the application servers. Two separate breaches are needed instead of one.

Binding email and purpose into the input does two extra jobs: a code issued for one address can’t be replayed against another, and a password-reset code can’t be spent as a login code. Cross-purpose replay is a real bug class, closed in one line.

The table, unabridged

This is our production schema:

create table email_codes (
  id           uuid primary key default gen_random_uuid(),
  email        citext      not null,
  purpose      text        not null
               check (purpose in ('signup','login','password_reset','email_change')),
  code_hash    text        not null,          -- HMAC hex. Never the code itself.
  expires_at   timestamptz not null,
  consumed_at  timestamptz,                   -- non-null = spent, single use
  attempts     int         not null default 0,
  max_attempts int         not null default 5,
  ip           inet,                          -- for per-IP rate limiting
  user_agent   text,
  user_id      uuid references auth.users(id) on delete cascade,
  created_at   timestamptz not null default now()
);

Two columns carry the design. consumed_at makes single-use a data property rather than a code-path promise. attempts lives next to the hash, so the lockout can’t be dodged by spreading guesses across app instances.

Verification must be one atomic operation

The bug that survives code review: check the code in one query, mark it used in another. Two concurrent requests both pass the check, and one code redeems twice.

Our verify is a single database function, and the load-bearing line is the row lock:

select * into v_row from email_codes
 where email = p_email and purpose = p_purpose and consumed_at is null
 order by created_at desc limit 1
 for update;                    -- serialises concurrent verifies

-- expiry and attempt checks happen INSIDE the lock, then:
if v_row.code_hash = p_hash then
  update email_codes set consumed_at = now()
   where id = v_row.id and consumed_at is null;   -- second guard, belt+braces
else
  update email_codes set attempts = attempts + 1 where id = v_row.id;
end if;

for update means the second concurrent request waits, then sees consumed_at set and fails with already_used. There is no window. Wrong guesses increment attempts in the same transaction, so five wrong tries lock the code no matter how requests are spread.

One gotcha that’s easy to ship: in Postgres, SECURITY DEFINER functions are executable by PUBLIC by default. Revoke them from your anon role explicitly, or you’ve published a brute-force endpoint that skips every rate limit in your application layer:

revoke all on function consume_email_code(citext, text, text) from public;
revoke all on function consume_email_code(citext, text, text) from anon, authenticated;

The numbers we chose, and why

Parameter	Our value	Why
Code length	6 digits	Typeable from a phone screen; safety comes from attempt limits, not length
TTL	10 minutes	Long enough for a slow inbox; short enough that a leaked code is nearly dead on arrival
Max attempts	5	5 in 1,000,000 ≈ 0.0005% guess probability per code
Re-request cooldown	60 seconds	Stops send-spam without stranding a user whose email is slow
Rate limits	5/email/hr · 20/IP/hr	The email cap bounds one victim’s exposure; the IP cap bounds one attacker’s throughput

Generate with a CSPRNG (crypto.randomInt), never Math.random(). And fail closed: if the pepper env var is missing, issue nothing rather than falling back to unkeyed hashing — a misconfigured deploy should break loudly, not degrade silently into the vulnerable version this design exists to prevent.

Delete what you no longer need

Spent and expired codes have no value and are pure liability. We purge opportunistically — each time an address requests a new code, its dead rows are deleted in the same operation. The table stays small with no scheduler to run and no cron job to forget.

The checklist

1. Store HMAC(pepper, email:purpose:code) — never the code, never an unkeyed hash.
2. Pepper in the environment, not the database.
3. Verify atomically with a row lock; re-check expiry and attempts inside it.
4. consumed_at as data, not a code path — single use survives concurrency.
5. Attempts counted in the same transaction as the check.
6. Revoke SECURITY DEFINER functions from PUBLIC — the default is executable.
7. CSPRNG only. Fail closed without the pepper.
8. Purge spent codes; a small table is a small breach.

This post is one half of a pair: the other is how we stopped our auth endpoints confirming which emails exist — same system, the request side rather than the storage side.