max / makenotwork

702 B · 16 lines History Blame Raw

1	-- Add a non-secret lookup prefix to backup_codes so verification is O(1)
2	-- instead of one Argon2 hash per unused row.
3	--
4	-- The prefix is the first 4 chars of the (now 16-char) code; the full code is
5	-- still Argon2-hashed in code_hash, so leaking code_prefix only narrows the
6	-- offline brute-force space from 36^16 to 36^12 (~62 bits remaining secret).
7	--
8	-- Legacy 8-char codes have code_prefix = NULL; verify falls back to the
9	-- iterate-all path for those rows until they're regenerated.
10
11	ALTER TABLE backup_codes ADD COLUMN code_prefix VARCHAR(8) NULL;
12
13	CREATE INDEX idx_backup_codes_user_prefix
14	ON backup_codes (user_id, code_prefix)
15	WHERE used_at IS NULL AND code_prefix IS NOT NULL;
16