-- GPG keys users added for commit verification, and the cached verification result per commit.
-- Applied by store/migrate.rs. Timestamps are RFC 3339 UTC stamps from time.rs.
-- The armored public key is stored as given. `emails` is a JSON array of the key's uid emails.
-- Rows are deleted with the user (user_delete.rs) and with the repo (Store::delete_repo).

CREATE TABLE gpg_keys (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id),
    fingerprint TEXT NOT NULL UNIQUE,
    armored     TEXT NOT NULL,
    emails      TEXT NOT NULL,
    created_at  TEXT NOT NULL
);

CREATE INDEX gpg_keys_user ON gpg_keys (user_id);

-- status: verified | unverified | unsigned | unknown_key | bad. kind: ssh | gpg, NULL when unsigned.
-- signer_user_id is set when the signing key is known to belong to a user. It has no foreign key
-- because user deletion clears these rows explicitly.
CREATE TABLE commit_signatures (
    repo_id        TEXT NOT NULL REFERENCES repos(id),
    oid            TEXT NOT NULL,
    status         TEXT NOT NULL,
    kind           TEXT,
    signer_user_id TEXT,
    key_fingerprint TEXT,
    checked_at     TEXT NOT NULL,
    PRIMARY KEY (repo_id, oid)
);

CREATE INDEX commit_signatures_signer ON commit_signatures (signer_user_id);