Blame · Line-by-line history

`0030_signing_keys.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0030_signing_keys.sqlBlame43 lines · 1 contributor

Normal view Raw

3951454Claude	1	-- Block J3 — Commit signature verification (GPG + SSH "Verified" badge)
	2	--
	3	-- Users register GPG or SSH public keys bound to an email identity. When we
	4	-- render a commit we extract the embedded signature, hash the key fingerprint
	5	-- out of the PGP/SSH armored blob, and look it up here. A match paired with
	6	-- an author email that the key declares → "Verified" badge.
	7	--
	8	-- Verifications are memoised in commit_verifications to avoid re-parsing the
	9	-- raw commit object on every page view.
	10
	11	CREATE TABLE IF NOT EXISTS "signing_keys" (
	12	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	13	"user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	14	"key_type" text NOT NULL, -- 'gpg' \| 'ssh'
	15	"title" text NOT NULL,
	16	"fingerprint" text NOT NULL, -- lowercased hex (GPG) or base64 SHA256 (SSH)
	17	"public_key" text NOT NULL, -- armored/authorized_keys form as uploaded
	18	"email" text, -- optional UID/comment email binding
	19	"expires_at" timestamp,
	20	"last_used_at" timestamp,
	21	"created_at" timestamp NOT NULL DEFAULT now()
	22	);
	23
	24	CREATE UNIQUE INDEX IF NOT EXISTS "signing_keys_fp_unique"
	25	ON "signing_keys" ("key_type", "fingerprint");
	26	CREATE INDEX IF NOT EXISTS "signing_keys_user_idx"
	27	ON "signing_keys" ("user_id");
	28
	29	CREATE TABLE IF NOT EXISTS "commit_verifications" (
	30	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	31	"repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE,
	32	"commit_sha" text NOT NULL,
	33	"verified" boolean NOT NULL DEFAULT false,
	34	"reason" text NOT NULL, -- 'valid' \| 'unsigned' \| 'unknown_key' \| 'expired' \| 'bad_sig' \| 'email_mismatch'
	35	"signature_type" text, -- 'gpg' \| 'ssh' \| null
	36	"signer_key_id" uuid REFERENCES "signing_keys"("id") ON DELETE SET NULL,
	37	"signer_user_id" uuid REFERENCES "users"("id") ON DELETE SET NULL,
	38	"signer_fingerprint" text,
	39	"verified_at" timestamp NOT NULL DEFAULT now()
	40	);
	41
	42	CREATE UNIQUE INDEX IF NOT EXISTS "commit_verifications_sha_unique"
	43	ON "commit_verifications" ("repository_id", "commit_sha");