-- Password reset tokens.
--
-- The platform had no way for an author to recover a forgotten password: the
-- only route back in was asking the editor to reset it by hand. OTP login was
-- the de-facto escape hatch, and it has been removed (79 password logins since
-- April against one OTP login, which was a test).
--
-- Stored in the database rather than in memory, unlike the OTP codes this
-- replaces. A reset link is clicked minutes or hours after it is sent, and every
-- redeploy restarts the API — an in-memory token would silently invalidate every
-- link in flight, which reads to the author as "the link is broken".
CREATE TABLE password_reset_tokens (
    id           BIGSERIAL    PRIMARY KEY,
    user_id      UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- SHA-256 of the token, never the token itself. The raw value is
    -- password-equivalent: anyone holding it can take over the account without
    -- knowing the password. Storing it plainly would turn a database leak, or a
    -- backup left somewhere, into an immediate takeover of every account with a
    -- pending reset. Plain SHA-256 rather than bcrypt is right here precisely
    -- because the input is 256 bits of SecureRandom output — there is no
    -- low-entropy guess to slow down, which is the only thing a work factor buys.
    token_hash   VARCHAR(64)  NOT NULL,

    expires_at   TIMESTAMPTZ  NOT NULL,
    -- Set when the token is spent. Kept rather than deleted so a support
    -- question ("did the reset go through?") has an answer.
    used_at      TIMESTAMPTZ,
    -- Who asked. The endpoint is unauthenticated, so this is the only signal
    -- available if someone starts hammering it for a particular account.
    requested_ip VARCHAR(45),
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Lookup is always by hash, and a collision would mean two accounts sharing a
-- reset link, so the uniqueness is enforced rather than assumed.
CREATE UNIQUE INDEX idx_prt_token_hash ON password_reset_tokens (token_hash);

-- Invalidating a user's other tokens when one is issued or spent.
CREATE INDEX idx_prt_user ON password_reset_tokens (user_id);

-- The retention sweep.
CREATE INDEX idx_prt_expires_at ON password_reset_tokens (expires_at);