You've already forked agentic-coding-workflow
84 lines
2.4 KiB
SQL
84 lines
2.4 KiB
SQL
-- name: CreateUser :one
|
|
INSERT INTO users (id, email, password_hash, display_name, is_admin)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
RETURNING id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled;
|
|
|
|
-- name: GetUserByEmail :one
|
|
SELECT id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled
|
|
FROM users
|
|
WHERE email = $1;
|
|
|
|
-- name: GetUserByID :one
|
|
SELECT id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled
|
|
FROM users
|
|
WHERE id = $1;
|
|
|
|
-- name: ListUsers :many
|
|
SELECT id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled
|
|
FROM users
|
|
ORDER BY created_at ASC;
|
|
|
|
-- name: CountUsers :one
|
|
SELECT COUNT(*)::int FROM users;
|
|
|
|
-- name: CountAdmins :one
|
|
SELECT COUNT(*)::int FROM users WHERE is_admin = true AND enabled = true;
|
|
|
|
-- name: UpdateUserProfile :one
|
|
UPDATE users
|
|
SET display_name = $2, updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled;
|
|
|
|
-- name: SetUserAdmin :one
|
|
UPDATE users
|
|
SET is_admin = $2, updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled;
|
|
|
|
-- name: SetUserEnabled :one
|
|
UPDATE users
|
|
SET enabled = $2, updated_at = now()
|
|
WHERE id = $1
|
|
RETURNING id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled;
|
|
|
|
-- name: UpdateUserPassword :exec
|
|
UPDATE users
|
|
SET password_hash = $2, updated_at = now()
|
|
WHERE id = $1;
|
|
|
|
-- name: DeleteUser :exec
|
|
DELETE FROM users WHERE id = $1;
|
|
|
|
-- name: CreateSession :exec
|
|
INSERT INTO user_sessions (id, user_id, token_hash, expires_at)
|
|
VALUES ($1, $2, $3, $4);
|
|
|
|
-- name: GetSessionByTokenHash :one
|
|
SELECT s.id, s.user_id, s.token_hash, s.expires_at, s.last_seen_at, s.created_at
|
|
FROM user_sessions s
|
|
JOIN users u ON u.id = s.user_id
|
|
WHERE s.token_hash = $1
|
|
AND s.expires_at > now()
|
|
AND u.enabled = true;
|
|
|
|
-- name: TouchSession :exec
|
|
UPDATE user_sessions
|
|
SET last_seen_at = now()
|
|
WHERE token_hash = $1;
|
|
|
|
-- name: DeleteSessionByTokenHash :exec
|
|
DELETE FROM user_sessions WHERE token_hash = $1;
|
|
|
|
-- name: DeleteSessionsByUserID :exec
|
|
DELETE FROM user_sessions WHERE user_id = $1;
|
|
|
|
-- name: DeleteExpiredSessions :exec
|
|
DELETE FROM user_sessions WHERE expires_at <= now();
|
|
|
|
-- name: ListAdmins :many
|
|
SELECT id, email, password_hash, display_name, is_admin, created_at, updated_at, enabled
|
|
FROM users
|
|
WHERE is_admin = true
|
|
ORDER BY id;
|