Email and password reset

Code

The reset-token store

Five files as tabs, in dependency order: 00003_password_reset_tokens.sql → password_resets.sql → users.sql → sessions.sql → token.go (the table, its queries, and the two existing query files that grow). Then sqlc generate.

1. db/migrations/00003_password_reset_tokens.sql — the token store, mirroring sessions:

token_hash TEXT NOT NULL UNIQUE,        -- the hash, never the token
user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
expires_at TIMESTAMPTZ NOT NULL,
used_at    TIMESTAMPTZ                  -- NULL until spent → single-use

used_at is the single-use flag: NULL means fresh, a timestamp means burnt.

2. internal/db/queries/password_resets.sql — four queries: CreatePasswordReset, GetValidPasswordReset (the one that enforces the rules — used_at IS NULL AND expires_at > now()), MarkPasswordResetUsed, and InvalidateUserPasswordResets (burn all outstanding for a user).

3. internal/db/queries/users.sql (modify) — one new query, UpdateUserPassword. A pure addition.

4. internal/db/queries/sessions.sql (modify) — one new query, DeleteSessionsForUser — the "log out every device" query a completed reset will call. Also a pure addition.

5. internal/auth/token.go (new) — the same generator sessions use, now exported for reuse:

func GenerateToken() (raw, hash string, err error) {  // raw → the link; hash → the DB
	b := make([]byte, 32); rand.Read(b)
	raw = base64.RawURLEncoding.EncodeToString(b)
	return raw, HashToken(raw), nil
}
func HashToken(raw string) string { /* SHA-256, hex */ }

GenerateToken returns two things: the raw token (goes in the emailed link) and its hash (goes in the database). The server never stores the raw token — so, exactly like sessions and passwords, a leaked backup exposes no usable reset links.

00003_password_reset_tokens.sql CREATE

Create this file in db/migrations/

-- +goose Up
-- +goose StatementBegin
-- Single-use, expiring password-reset tokens. Same pattern as sessions: the
-- emailed link carries the raw token, the database stores only its hash.
CREATE TABLE password_reset_tokens (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash TEXT        NOT NULL UNIQUE,
    user_id    UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    used_at    TIMESTAMPTZ
);
CREATE INDEX idx_password_reset_tokens_user_id ON password_reset_tokens(user_id);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE password_reset_tokens;
-- +goose StatementEnd

password_resets.sql CREATE

Create this file in internal/db/queries/

-- name: CreatePasswordReset :one
INSERT INTO password_reset_tokens (token_hash, user_id, expires_at)
VALUES ($1, $2, $3)
RETURNING *;

-- name: GetValidPasswordReset :one
SELECT * FROM password_reset_tokens
WHERE token_hash = $1
  AND used_at IS NULL
  AND expires_at > now();

-- name: MarkPasswordResetUsed :exec
UPDATE password_reset_tokens SET used_at = now() WHERE id = $1;

-- name: InvalidateUserPasswordResets :exec
UPDATE password_reset_tokens SET used_at = now()
WHERE user_id = $1 AND used_at IS NULL;

users.sql MODIFY 3 lines changed

Edit this file in internal/db/queries/

-- name: CountUsers :one
SELECT count(*) FROM users;

-- name: CreateUser :one
INSERT INTO users (email, password_hash)
VALUES ($1, $2)
RETURNING *;

-- name: GetUserByEmail :one
SELECT * FROM users WHERE email = $1;

-- name: UpdateUserPassword :exec
UPDATE users SET password_hash = $2 WHERE id = $1;

sessions.sql MODIFY 3 lines changed

Edit this file in internal/db/queries/

-- name: CreateSession :one
INSERT INTO sessions (token_hash, user_id, expires_at)
VALUES ($1, $2, $3)
RETURNING *;

-- name: GetUserBySessionToken :one
SELECT sqlc.embed(users)
FROM users
JOIN sessions ON sessions.user_id = users.id
WHERE sessions.token_hash = $1
  AND sessions.expires_at > now();

-- name: DeleteSession :exec
DELETE FROM sessions WHERE token_hash = $1;

-- name: DeleteSessionsForUser :exec
DELETE FROM sessions WHERE user_id = $1;

token.go CREATE

Create this file in internal/auth/

package auth

import (
	"crypto/rand"
	"crypto/sha256"
	"encoding/base64"
	"encoding/hex"
)

// GenerateToken returns a URL-safe random token (raw, to put in a link) and its
// SHA-256 hash (to store). We keep only the hash server-side, so the token in a
// password-reset email can't be reused from a database leak.
func GenerateToken() (raw, hash string, err error) {
	b := make([]byte, 32)
	if _, err := rand.Read(b); err != nil {
		return "", "", err
	}
	raw = base64.RawURLEncoding.EncodeToString(b)
	return raw, HashToken(raw), nil
}

// HashToken hashes a raw token for storage/lookup.
func HashToken(raw string) string {
	sum := sha256.Sum256([]byte(raw))
	return hex.EncodeToString(sum[:])
}