SansanandClaude Sonnet 5
384c4a7217
Anyone authenticated can still read the shared platform list, but only an
admin may add, rename or delete from it.
- users.is_admin boolean, defaulting to false. The very first account ever
registered becomes an admin automatically (CreateUser's INSERT uses
NOT EXISTS (SELECT 1 FROM users) for is_admin), so there's always at
least one without manual setup; promoting anyone beyond that is a manual
UPDATE for now (documented in the migration's comment on the column).
- GetSessionAndUser, GetUserByEmail and GetUserByID now also select
is_admin, and it flows through to model.User (and so to
/api/auth/{register,login,me}'s response) and to the auth middleware's
authedUser.
- New requireAdminMiddleware + addAdminAuth, layered on top of the existing
auth middleware. Wired onto create/update/delete-platform; list/get stay
on plain addAuth, since reading the list only requires being logged in.
Verified end to end: the first registered user comes back is_admin:true,
the second false; the non-admin gets 403 on create/update/delete-platform
but 200 on list; the admin can do all of it.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
128 lines
3.7 KiB
SQL
128 lines
3.7 KiB
SQL
-- name: CreateUser :one
|
|
-- The first account ever registered becomes an admin automatically; see
|
|
-- the is_admin column's comment in db/migrations for why and the race this
|
|
-- accepts. Everyone after that registers as a non-admin.
|
|
INSERT INTO users (
|
|
email, password_hash, name, bio, is_admin
|
|
) VALUES (
|
|
$1, $2, $3, $4, NOT EXISTS (SELECT 1 FROM users)
|
|
)
|
|
RETURNING id, email, name, bio, is_admin, created_at;
|
|
|
|
-- name: GetUserByEmail :one
|
|
SELECT id, email, password_hash, name, bio, is_admin, created_at
|
|
FROM users
|
|
WHERE email = $1;
|
|
|
|
-- name: GetUserByID :one
|
|
SELECT id, email, name, bio, is_admin, created_at
|
|
FROM users
|
|
WHERE id = $1;
|
|
|
|
-- name: CreateSession :exec
|
|
INSERT INTO sessions (
|
|
token_hash, user_id, expires_at
|
|
) VALUES (
|
|
$1, $2, $3
|
|
);
|
|
|
|
-- name: GetSessionAndUser :one
|
|
SELECT users.id, users.email, users.name, users.bio, users.is_admin, sessions.expires_at
|
|
FROM sessions
|
|
JOIN users ON users.id = sessions.user_id
|
|
WHERE sessions.token_hash = $1 AND sessions.expires_at > now();
|
|
|
|
-- name: TouchSession :exec
|
|
UPDATE sessions
|
|
SET last_seen_at = now()
|
|
WHERE token_hash = $1;
|
|
|
|
-- name: DeleteSession :exec
|
|
DELETE FROM sessions
|
|
WHERE token_hash = $1;
|
|
|
|
-- name: CreatePlatform :one
|
|
INSERT INTO platform (
|
|
name
|
|
) VALUES (
|
|
$1
|
|
)
|
|
RETURNING id, name;
|
|
|
|
-- name: GetPlatform :one
|
|
SELECT id, name
|
|
FROM platform
|
|
WHERE id = $1 AND deleted_at IS NULL;
|
|
|
|
-- name: ListPlatforms :many
|
|
SELECT id, name
|
|
FROM platform
|
|
WHERE deleted_at IS NULL
|
|
ORDER BY name;
|
|
|
|
-- name: UpdatePlatform :one
|
|
UPDATE platform
|
|
SET name = $2
|
|
WHERE id = $1 AND deleted_at IS NULL
|
|
RETURNING id, name;
|
|
|
|
-- name: DeletePlatform :execrows
|
|
-- Soft delete, so the FK on game.platform_id is never touched and never
|
|
-- blocks this on its own; the NOT EXISTS guard is what stops a platform
|
|
-- that still has active games - from any user, since the list is shared -
|
|
-- from being deleted. Call GetPlatform first to tell "not found" apart from
|
|
-- "blocked by games" when this affects 0 rows.
|
|
UPDATE platform
|
|
SET deleted_at = now()
|
|
WHERE platform.id = $1 AND platform.deleted_at IS NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM game
|
|
WHERE game.platform_id = platform.id AND game.deleted_at IS NULL
|
|
);
|
|
|
|
-- name: CreateGame :one
|
|
WITH inserted AS (
|
|
INSERT INTO game (
|
|
user_id, platform_id, name, score, release_date, finished
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6
|
|
)
|
|
RETURNING id, platform_id, name, score, release_date, finished
|
|
)
|
|
SELECT inserted.id, inserted.name, inserted.platform_id, platform.name AS platform_name,
|
|
inserted.score, inserted.release_date, inserted.finished
|
|
FROM inserted
|
|
JOIN platform ON platform.id = inserted.platform_id;
|
|
|
|
-- name: GetGame :one
|
|
SELECT game.id, game.name, game.platform_id, platform.name AS platform_name,
|
|
game.score, game.release_date, game.finished
|
|
FROM game
|
|
JOIN platform ON platform.id = game.platform_id
|
|
WHERE game.id = $1 AND game.user_id = $2 AND game.deleted_at IS NULL;
|
|
|
|
-- name: ListGames :many
|
|
SELECT game.id, game.name, game.platform_id, platform.name AS platform_name,
|
|
game.score, game.release_date, game.finished
|
|
FROM game
|
|
JOIN platform ON platform.id = game.platform_id
|
|
WHERE game.user_id = $1 AND game.deleted_at IS NULL
|
|
ORDER BY game.name;
|
|
|
|
-- name: UpdateGame :one
|
|
WITH updated AS (
|
|
UPDATE game
|
|
SET platform_id = $3, name = $4, score = $5, release_date = $6, finished = $7
|
|
WHERE game.id = $1 AND game.user_id = $2 AND game.deleted_at IS NULL
|
|
RETURNING id, platform_id, name, score, release_date, finished
|
|
)
|
|
SELECT updated.id, updated.name, updated.platform_id, platform.name AS platform_name,
|
|
updated.score, updated.release_date, updated.finished
|
|
FROM updated
|
|
JOIN platform ON platform.id = updated.platform_id;
|
|
|
|
-- name: DeleteGame :execrows
|
|
UPDATE game
|
|
SET deleted_at = now()
|
|
WHERE id = $1 AND user_id = $2 AND deleted_at IS NULL;
|