| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertUserGPGKey :one |
| 4 | -- Inserts a parsed primary GPG key. Subkeys land in user_gpg_subkeys |
| 5 | -- in the same transaction (see InsertUserGPGSubkey). expires_at is |
| 6 | -- nullable; many keys have no expiration. revoked_at stays NULL on |
| 7 | -- insert; soft-delete sets it. |
| 8 | INSERT INTO user_gpg_keys ( |
| 9 | user_id, name, fingerprint, key_id, armored, |
| 10 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 11 | uids, subkeys, primary_algo, expires_at |
| 12 | ) |
| 13 | VALUES ( |
| 14 | $1, $2, $3, $4, $5, |
| 15 | $6, $7, $8, $9, $10, |
| 16 | $11, $12, $13, $14 |
| 17 | ) |
| 18 | RETURNING id, user_id, name, fingerprint, key_id, armored, |
| 19 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 20 | uids, subkeys, primary_algo, |
| 21 | created_at, last_used_at, revoked_at, expires_at; |
| 22 | |
| 23 | -- name: ListUserGPGKeys :many |
| 24 | -- Paginated list for the REST surface; HTML settings page reuses with |
| 25 | -- a generous limit and no offset. |
| 26 | SELECT id, user_id, name, fingerprint, key_id, armored, |
| 27 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 28 | uids, subkeys, primary_algo, |
| 29 | created_at, last_used_at, revoked_at, expires_at |
| 30 | FROM user_gpg_keys |
| 31 | WHERE user_id = $1 AND revoked_at IS NULL |
| 32 | ORDER BY created_at DESC |
| 33 | LIMIT $2 OFFSET $3; |
| 34 | |
| 35 | -- name: CountUserGPGKeys :one |
| 36 | -- Excludes revoked rows so the per-user cap (100) counts live keys. |
| 37 | SELECT count(*) FROM user_gpg_keys WHERE user_id = $1 AND revoked_at IS NULL; |
| 38 | |
| 39 | -- name: GetUserGPGKey :one |
| 40 | -- Scoped single-key lookup for REST GET-by-id. user_id filter prevents |
| 41 | -- cross-user reads (existence-leak-safe: returns no row if the id |
| 42 | -- belongs to another user). |
| 43 | SELECT id, user_id, name, fingerprint, key_id, armored, |
| 44 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 45 | uids, subkeys, primary_algo, |
| 46 | created_at, last_used_at, revoked_at, expires_at |
| 47 | FROM user_gpg_keys |
| 48 | WHERE id = $1 AND user_id = $2; |
| 49 | |
| 50 | -- name: GetUserGPGKeyForVerification :one |
| 51 | -- Non-user-scoped lookup used by the verification path. Unlike |
| 52 | -- GetUserGPGKey this query does NOT filter on user_id — the caller |
| 53 | -- already validated the subkey resolution and needs the parent |
| 54 | -- record's user_id to drive the email cross-check. Includes revoked |
| 55 | -- rows so historical commit verifications can still resolve their |
| 56 | -- signer attribution. |
| 57 | SELECT id, user_id, name, fingerprint, key_id, armored, |
| 58 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 59 | uids, subkeys, primary_algo, |
| 60 | created_at, last_used_at, revoked_at, expires_at |
| 61 | FROM user_gpg_keys |
| 62 | WHERE id = $1; |
| 63 | |
| 64 | -- name: GetUserGPGKeyByFingerprint :one |
| 65 | -- Uniqueness probe used by the add path to surface a friendly |
| 66 | -- "this key is already registered" error before the unique index |
| 67 | -- violation. Returns any row matching the fingerprint regardless of |
| 68 | -- which user owns it (global uniqueness is the contract). |
| 69 | SELECT id, user_id, name, fingerprint, key_id, armored, |
| 70 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, can_authenticate, |
| 71 | uids, subkeys, primary_algo, |
| 72 | created_at, last_used_at, revoked_at, expires_at |
| 73 | FROM user_gpg_keys |
| 74 | WHERE fingerprint = $1 AND revoked_at IS NULL; |
| 75 | |
| 76 | -- name: SoftDeleteUserGPGKey :execrows |
| 77 | -- Scoped soft-delete: stamps revoked_at, preserves the row for audit |
| 78 | -- continuity. Returns the number of rows affected so the handler can |
| 79 | -- distinguish "not found" from "deleted" without a follow-up query. |
| 80 | UPDATE user_gpg_keys |
| 81 | SET revoked_at = now() |
| 82 | WHERE id = $1 AND user_id = $2 AND revoked_at IS NULL; |
| 83 | |
| 84 | -- name: TouchUserGPGKeyLastUsed :exec |
| 85 | -- Best-effort last-used stamp called from the verification path when |
| 86 | -- a signature successfully resolves to this key. No timeout / error |
| 87 | -- propagation; the caller fires-and-forgets via a goroutine. |
| 88 | UPDATE user_gpg_keys SET last_used_at = now() WHERE id = $1; |
| 89 |