gitfor/platform

-- Public keys can't prove ownership, so the same key may be registered by several accounts; verification
-- picks the owner whose verified email matches the committer.
CREATE TABLE signing_keys_v2 (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('ssh', 'gpg')),
  title TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  public_key TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (user_id, kind, fingerprint)
);
INSERT INTO signing_keys_v2 (id, user_id, kind, title, fingerprint, public_key, created_at)
  SELECT id, user_id, kind, title, fingerprint, public_key, created_at FROM signing_keys;

CREATE TABLE signing_key_ids_v2 (
  key_id TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  signing_key_id TEXT NOT NULL REFERENCES signing_keys_v2(id) ON DELETE CASCADE,
  PRIMARY KEY (fingerprint, signing_key_id)
);
INSERT INTO signing_key_ids_v2 (key_id, fingerprint, signing_key_id)
  SELECT key_id, fingerprint, signing_key_id FROM signing_key_ids;

DROP TABLE signing_key_ids;
DROP TABLE signing_keys;
ALTER TABLE signing_keys_v2 RENAME TO signing_keys;
ALTER TABLE signing_key_ids_v2 RENAME TO signing_key_ids;
CREATE INDEX signing_keys_user ON signing_keys(user_id);
CREATE INDEX signing_keys_fingerprint ON signing_keys(kind, fingerprint);
CREATE INDEX signing_key_ids_key_id ON signing_key_ids(key_id);
CREATE INDEX signing_key_ids_signing_key ON signing_key_ids(signing_key_id);

-- Earlier results trusted unverified emails, unbound subkeys and SHA-1; re-verify everything.
DELETE FROM commit_signatures;