| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: UpsertCommitVerification :exec |
| 4 | -- Idempotent upsert. The verification orchestrator + backfill worker |
| 5 | -- both write through this query; both can safely run concurrently |
| 6 | -- against the same (repo_id, commit_oid) without losing data thanks |
| 7 | -- to the (repo_id, commit_oid) primary key + ON CONFLICT clause. |
| 8 | INSERT INTO commit_verification_cache ( |
| 9 | repo_id, commit_oid, reason, verified, |
| 10 | signer_user_id, signer_subkey_id, kind, |
| 11 | signature_armored, payload, verified_at |
| 12 | ) |
| 13 | VALUES ( |
| 14 | $1, $2, $3, $4, |
| 15 | $5, $6, $7, |
| 16 | $8, $9, now() |
| 17 | ) |
| 18 | ON CONFLICT (repo_id, commit_oid) DO UPDATE SET |
| 19 | reason = EXCLUDED.reason, |
| 20 | verified = EXCLUDED.verified, |
| 21 | signer_user_id = EXCLUDED.signer_user_id, |
| 22 | signer_subkey_id = EXCLUDED.signer_subkey_id, |
| 23 | kind = EXCLUDED.kind, |
| 24 | signature_armored = EXCLUDED.signature_armored, |
| 25 | payload = EXCLUDED.payload, |
| 26 | verified_at = now(), |
| 27 | invalidated_at = NULL; |
| 28 | |
| 29 | -- name: GetCommitVerification :one |
| 30 | -- Single-commit read. Used by the single-commit page renderer and the |
| 31 | -- REST commits/{sha} response. Returns no row when the commit hasn't |
| 32 | -- been verified yet; caller treats that as "compute on demand". |
| 33 | SELECT repo_id, commit_oid, reason, verified, |
| 34 | signer_user_id, signer_subkey_id, kind, |
| 35 | signature_armored, payload, verified_at, invalidated_at |
| 36 | FROM commit_verification_cache |
| 37 | WHERE repo_id = $1 AND commit_oid = $2; |
| 38 | |
| 39 | -- name: GetCommitVerificationsForOIDs :many |
| 40 | -- Batch read for the commit-list page. Takes an array of OIDs and |
| 41 | -- returns existing rows; missing OIDs are absent from the result and |
| 42 | -- the renderer treats them as "not yet verified". |
| 43 | SELECT repo_id, commit_oid, reason, verified, |
| 44 | signer_user_id, signer_subkey_id, kind, |
| 45 | signature_armored, payload, verified_at, invalidated_at |
| 46 | FROM commit_verification_cache |
| 47 | WHERE repo_id = sqlc.arg(repo_id) AND commit_oid = ANY(sqlc.arg(oids)::text[]); |
| 48 | |
| 49 | -- name: InvalidateVerificationsForSubkey :exec |
| 50 | -- Stamps invalidated_at on every cache row whose signer_subkey_id |
| 51 | -- matches. Called from the GPG-key soft-delete path in the same tx as |
| 52 | -- SoftDeleteSubkeysForGPGKey so the cache and the keyring stay in |
| 53 | -- sync. The next read of an invalidated row triggers a re-verify. |
| 54 | UPDATE commit_verification_cache |
| 55 | SET invalidated_at = now() |
| 56 | WHERE signer_subkey_id = $1 AND invalidated_at IS NULL; |
| 57 | |
| 58 | -- name: DeleteCommitVerification :exec |
| 59 | -- Used by tests to reset cache state between cases. Not called from |
| 60 | -- production code paths. |
| 61 | DELETE FROM commit_verification_cache |
| 62 | WHERE repo_id = $1 AND commit_oid = $2; |
| 63 |