| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertUserGPGSubkey :one |
| 4 | -- One row per subkey of a primary key. Always inserted in the same |
| 5 | -- transaction as the parent InsertUserGPGKey so the verification |
| 6 | -- hot path's fingerprint lookup is consistent with the REST nested |
| 7 | -- shape. |
| 8 | INSERT INTO user_gpg_subkeys ( |
| 9 | gpg_key_id, fingerprint, key_id, |
| 10 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, |
| 11 | expires_at |
| 12 | ) |
| 13 | VALUES ( |
| 14 | $1, $2, $3, |
| 15 | $4, $5, $6, $7, |
| 16 | $8 |
| 17 | ) |
| 18 | RETURNING id, gpg_key_id, fingerprint, key_id, |
| 19 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, |
| 20 | expires_at, revoked_at, created_at; |
| 21 | |
| 22 | -- name: GetUserGPGSubkeyByFingerprint :one |
| 23 | -- Hot path for commit/tag signature verification. The signature |
| 24 | -- packet carries the signing subkey's fingerprint; this query |
| 25 | -- resolves it back to the primary key (and via FK to the user). |
| 26 | -- Index lookup via the partial unique index. |
| 27 | SELECT id, gpg_key_id, fingerprint, key_id, |
| 28 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, |
| 29 | expires_at, revoked_at, created_at |
| 30 | FROM user_gpg_subkeys |
| 31 | WHERE fingerprint = $1 AND revoked_at IS NULL; |
| 32 | |
| 33 | -- name: ListSubkeysForGPGKey :many |
| 34 | -- Reads all live subkeys for one primary; used when invalidating the |
| 35 | -- verification cache on primary soft-delete (every dependent subkey |
| 36 | -- needs its cache rows stamped invalidated too). |
| 37 | SELECT id, gpg_key_id, fingerprint, key_id, |
| 38 | can_sign, can_encrypt_comms, can_encrypt_storage, can_certify, |
| 39 | expires_at, revoked_at, created_at |
| 40 | FROM user_gpg_subkeys |
| 41 | WHERE gpg_key_id = $1 |
| 42 | ORDER BY id; |
| 43 | |
| 44 | -- name: SoftDeleteSubkeysForGPGKey :exec |
| 45 | -- Stamps revoked_at on every live subkey of a primary. Called in the |
| 46 | -- same transaction as SoftDeleteUserGPGKey so the partial unique index |
| 47 | -- frees up the fingerprint for re-upload if the user rotates. |
| 48 | UPDATE user_gpg_subkeys |
| 49 | SET revoked_at = now() |
| 50 | WHERE gpg_key_id = $1 AND revoked_at IS NULL; |
| 51 |