| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Cache for commit and annotated-tag signature verification results. |
| 4 | -- Read by the rendering paths (commit list / single commit / tag list), |
| 5 | -- written by the verification orchestrator and the backfill worker. |
| 6 | -- |
| 7 | -- Keyed on (repo_id, commit_oid) so each (repo, object) pair has at |
| 8 | -- most one row. The `kind` discriminator separates commit-object |
| 9 | -- verifications from tag-object verifications; both reuse the same |
| 10 | -- schema because their `reason` enum + signer fields are identical. |
| 11 | -- |
| 12 | -- reason values mirror GitHub's documented enum on the commit |
| 13 | -- verification object: valid, unsigned, unknown_key, bad_email, |
| 14 | -- unverified_email, malformed_signature, invalid, expired_key, |
| 15 | -- not_signing_key. `verified` is the convenience boolean (reason == |
| 16 | -- 'valid'). |
| 17 | -- |
| 18 | -- signer_user_id and signer_subkey_id are nullable: set whenever the |
| 19 | -- signature matched a stored key cryptographically (even if the |
| 20 | -- email cross-check or expiry then disqualified it). Both are |
| 21 | -- ON DELETE SET NULL so revoking a key or deleting a user doesn't |
| 22 | -- cascade-delete historical verification rows — the row remains as |
| 23 | -- "this was once verified by a key we no longer have", and the |
| 24 | -- rendering path treats it as unverified. |
| 25 | -- |
| 26 | -- invalidated_at is the cache-staleness flag: stamped by the GPG-key |
| 27 | -- soft-delete path on every dependent row in the same tx. The next |
| 28 | -- read triggers a re-verify; the backfill worker catches up in the |
| 29 | -- background. |
| 30 | |
| 31 | -- +goose Up |
| 32 | CREATE TABLE commit_verification_cache ( |
| 33 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 34 | commit_oid text NOT NULL, |
| 35 | reason text NOT NULL, |
| 36 | verified boolean NOT NULL, |
| 37 | signer_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 38 | signer_subkey_id bigint REFERENCES user_gpg_subkeys(id) ON DELETE SET NULL, |
| 39 | kind text NOT NULL, |
| 40 | signature_armored text, |
| 41 | payload bytea, |
| 42 | verified_at timestamptz NOT NULL DEFAULT now(), |
| 43 | invalidated_at timestamptz, |
| 44 | |
| 45 | PRIMARY KEY (repo_id, commit_oid), |
| 46 | |
| 47 | CONSTRAINT commit_verification_cache_kind_known CHECK (kind IN ('commit', 'tag')), |
| 48 | CONSTRAINT commit_verification_cache_reason_known CHECK (reason IN ( |
| 49 | 'valid', |
| 50 | 'unsigned', |
| 51 | 'unknown_key', |
| 52 | 'bad_email', |
| 53 | 'unverified_email', |
| 54 | 'malformed_signature', |
| 55 | 'invalid', |
| 56 | 'expired_key', |
| 57 | 'not_signing_key' |
| 58 | )), |
| 59 | CONSTRAINT commit_verification_cache_verified_implies_valid CHECK ( |
| 60 | (verified = false) OR (reason = 'valid') |
| 61 | ), |
| 62 | CONSTRAINT commit_verification_cache_oid_format CHECK (commit_oid ~ '^[0-9a-f]{40}$') |
| 63 | ); |
| 64 | |
| 65 | CREATE INDEX commit_verification_cache_signer_user_idx |
| 66 | ON commit_verification_cache (signer_user_id) |
| 67 | WHERE signer_user_id IS NOT NULL; |
| 68 | |
| 69 | CREATE INDEX commit_verification_cache_signer_subkey_idx |
| 70 | ON commit_verification_cache (signer_subkey_id) |
| 71 | WHERE signer_subkey_id IS NOT NULL; |
| 72 | |
| 73 | CREATE INDEX commit_verification_cache_invalidated_idx |
| 74 | ON commit_verification_cache (invalidated_at) |
| 75 | WHERE invalidated_at IS NOT NULL; |
| 76 | |
| 77 | -- +goose Down |
| 78 | DROP INDEX IF EXISTS commit_verification_cache_invalidated_idx; |
| 79 | DROP INDEX IF EXISTS commit_verification_cache_signer_subkey_idx; |
| 80 | DROP INDEX IF EXISTS commit_verification_cache_signer_user_idx; |
| 81 | DROP TABLE IF EXISTS commit_verification_cache; |
| 82 |