| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: CreateUser :one |
| 4 | INSERT INTO users (username, display_name, password_hash) |
| 5 | VALUES ($1, $2, $3) |
| 6 | RETURNING *; |
| 7 | |
| 8 | -- name: GetUserByID :one |
| 9 | SELECT * |
| 10 | FROM users |
| 11 | WHERE id = $1 AND deleted_at IS NULL; |
| 12 | |
| 13 | -- name: GetUserByUsername :one |
| 14 | SELECT * |
| 15 | FROM users |
| 16 | WHERE username = $1 AND deleted_at IS NULL; |
| 17 | |
| 18 | -- name: UpdateUserPassword :exec |
| 19 | UPDATE users |
| 20 | SET password_hash = $2, |
| 21 | password_algo = $3, |
| 22 | password_updated_at = now() |
| 23 | WHERE id = $1; |
| 24 | |
| 25 | -- name: LinkUserPrimaryEmail :exec |
| 26 | -- Sets the FK only. Does NOT flip users.email_verified — that happens via |
| 27 | -- MarkUserEmailPrimaryVerified after the user clicks the verification link. |
| 28 | UPDATE users |
| 29 | SET primary_email_id = $2 |
| 30 | WHERE id = $1; |
| 31 | |
| 32 | -- name: MarkUserEmailPrimaryVerified :exec |
| 33 | -- Called after MarkUserEmailVerified for the primary email, to flip the |
| 34 | -- denormalized users.email_verified flag. |
| 35 | UPDATE users |
| 36 | SET email_verified = true |
| 37 | WHERE id = $1; |
| 38 | |
| 39 | -- name: TouchUserLastLogin :exec |
| 40 | UPDATE users |
| 41 | SET last_login_at = now() |
| 42 | WHERE id = $1; |
| 43 | |
| 44 | -- name: SuspendUser :exec |
| 45 | UPDATE users |
| 46 | SET suspended_at = now(), |
| 47 | suspended_reason = $2 |
| 48 | WHERE id = $1; |
| 49 | |
| 50 | -- name: SoftDeleteUser :exec |
| 51 | UPDATE users |
| 52 | SET deleted_at = now() |
| 53 | WHERE id = $1; |
| 54 | |
| 55 | -- name: CountUsers :one |
| 56 | SELECT count(*) FROM users WHERE deleted_at IS NULL; |
| 57 | |
| 58 | -- name: UpdateUserProfile :exec |
| 59 | UPDATE users |
| 60 | SET display_name = $2, |
| 61 | bio = $3, |
| 62 | location = $4, |
| 63 | website = $5, |
| 64 | company = $6, |
| 65 | pronouns = $7 |
| 66 | WHERE id = $1; |
| 67 | |
| 68 | -- name: UpdateUserAvatarKey :exec |
| 69 | UPDATE users |
| 70 | SET avatar_object_key = $2 |
| 71 | WHERE id = $1; |
| 72 | |
| 73 | -- name: RenameUser :exec |
| 74 | -- Wrapped by the username-change flow inside a tx that also writes |
| 75 | -- username_redirects, so the old name becomes a redirect target atomically. |
| 76 | UPDATE users |
| 77 | SET username = $2 |
| 78 | WHERE id = $1; |
| 79 | |
| 80 | -- name: CountRecentUsernameChanges :one |
| 81 | -- Drives the 3-changes-per-60d cap. |
| 82 | SELECT count(*) FROM username_redirects |
| 83 | WHERE user_id = $1 AND changed_at > $2; |
| 84 | |
| 85 | -- name: UpdateUserTheme :exec |
| 86 | UPDATE users SET theme = $2 WHERE id = $1; |
| 87 | |
| 88 | -- name: BumpUserSessionEpoch :exec |
| 89 | UPDATE users SET session_epoch = session_epoch + 1 WHERE id = $1; |
| 90 | |
| 91 | -- name: GetUserSessionEpoch :one |
| 92 | SELECT session_epoch FROM users WHERE id = $1; |
| 93 | |
| 94 | -- name: RestoreUserAccount :exec |
| 95 | -- Clears deleted_at; called when a user logs in within the 14-day grace |
| 96 | -- window. The login handler enforces the window check before calling. |
| 97 | UPDATE users SET deleted_at = NULL WHERE id = $1; |
| 98 | |
| 99 | -- name: GetUserIncludingDeleted :one |
| 100 | -- Like GetUserByID but returns the row even when deleted_at IS NOT NULL. |
| 101 | SELECT * FROM users WHERE id = $1; |
| 102 | |
| 103 | -- name: GetUserByUsernameIncludingDeleted :one |
| 104 | SELECT * FROM users WHERE username = $1; |
| 105 |