40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
-- Sessions and magic-link tokens.
|
|
--
|
|
-- Both are stored hashed: the plaintext never reaches the database, so a dump
|
|
-- cannot be replayed into a login.
|
|
|
|
-- name: UpsertUser :one
|
|
INSERT INTO users (id, email, email_verified)
|
|
VALUES (@id, @email, true)
|
|
ON CONFLICT (email) DO UPDATE
|
|
SET email_verified = true, updated_at = now()
|
|
RETURNING id, email, name, is_admin;
|
|
|
|
-- name: CreateVerification :exec
|
|
INSERT INTO verifications (id, identifier, value, expires_at)
|
|
VALUES (@id, @identifier, @value, @expires_at);
|
|
|
|
-- name: ConsumeVerification :one
|
|
UPDATE verifications SET consumed_at = now(), updated_at = now()
|
|
WHERE value = @value AND consumed_at IS NULL AND expires_at > now()
|
|
RETURNING identifier;
|
|
|
|
-- name: CreateSession :exec
|
|
INSERT INTO sessions (id, token, expires_at, user_id, ip_address, user_agent)
|
|
VALUES (@id, @token, @expires_at, @user_id, @ip_address, @user_agent);
|
|
|
|
-- name: UserBySessionToken :one
|
|
SELECT u.id, u.email, u.name, u.is_admin
|
|
FROM sessions s
|
|
JOIN users u ON u.id = s.user_id
|
|
WHERE s.token = @token AND s.expires_at > now();
|
|
|
|
-- name: DeleteSession :exec
|
|
DELETE FROM sessions WHERE token = @token;
|
|
|
|
-- name: PurgeExpiredSessions :exec
|
|
DELETE FROM sessions WHERE expires_at < now();
|
|
|
|
-- name: PurgeExpiredVerifications :exec
|
|
DELETE FROM verifications WHERE expires_at < now() - interval '1 day';
|