| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: UpsertUserTOTP :one |
| 4 | -- Inserts a new pending TOTP row, or replaces an existing pending row for |
| 5 | -- the same user. Confirmed rows are NOT replaced — disable+regenerate |
| 6 | -- must go through the dedicated query. |
| 7 | INSERT INTO user_totp (user_id, secret_encrypted, secret_nonce) |
| 8 | VALUES ($1, $2, $3) |
| 9 | ON CONFLICT (user_id) DO UPDATE |
| 10 | SET secret_encrypted = EXCLUDED.secret_encrypted, |
| 11 | secret_nonce = EXCLUDED.secret_nonce, |
| 12 | confirmed_at = NULL, |
| 13 | last_used_counter = 0 |
| 14 | WHERE user_totp.confirmed_at IS NULL |
| 15 | RETURNING id, user_id, secret_encrypted, secret_nonce, confirmed_at, |
| 16 | last_used_counter, created_at, updated_at; |
| 17 | |
| 18 | -- name: GetUserTOTP :one |
| 19 | SELECT id, user_id, secret_encrypted, secret_nonce, confirmed_at, |
| 20 | last_used_counter, created_at, updated_at |
| 21 | FROM user_totp |
| 22 | WHERE user_id = $1; |
| 23 | |
| 24 | -- name: ConfirmUserTOTP :execrows |
| 25 | -- Sets confirmed_at on a pending row. Returns the number of rows updated; |
| 26 | -- callers MUST check this to handle the parallel-enrollment race |
| 27 | -- (only one of two concurrent confirms wins). |
| 28 | UPDATE user_totp |
| 29 | SET confirmed_at = now(), |
| 30 | last_used_counter = $2 |
| 31 | WHERE user_id = $1 AND confirmed_at IS NULL; |
| 32 | |
| 33 | -- name: BumpTOTPCounter :execrows |
| 34 | -- Atomically advances last_used_counter only when the proposed counter is |
| 35 | -- strictly greater. Returns rows affected — 0 means a replay attempt and |
| 36 | -- the caller should reject the code. |
| 37 | UPDATE user_totp |
| 38 | SET last_used_counter = $2 |
| 39 | WHERE user_id = $1 AND $2::bigint > last_used_counter; |
| 40 | |
| 41 | -- name: DeleteUserTOTP :exec |
| 42 | DELETE FROM user_totp WHERE user_id = $1; |
| 43 |