steid

steid/migrations/20260908090500_create_password_resets.sql

1	-- One-shot password reset tokens.
2	--
3	-- Only the SHA-256 of the token is stored, exactly as for `tokens` and `sessions`, so a
4	-- dumped database contains nothing anyone could put in a link.
5	--
6	-- `used_at` rather than a delete: a spent link has to answer "already used" rather than
7	-- "never existed", and the row is the only trace that a reset happened at all. Rows are
8	-- therefore kept, and are small — a dead one is 30 minutes past being interesting.
9	--
10	-- Indexed by hash because that is the lookup the reset page performs; by user because
11	-- issuing a link invalidates that user's earlier ones.
12	create table password_resets (
13	id text primary key,
14	user_id text not null references users (id) on delete cascade,
15	token_hash text not null unique,
16	created_at integer not null,
17	expires_at integer not null,
18	used_at integer
19	);
20
21	create index password_resets_user_id on password_resets (user_id);