-- Secret scanning, part 2 (oxygenhub#277): findings from the background

-- scan of each repository's history beside those of refused pushes, with

-- the change and bookmark they were found in and a maintainer's mark that

-- the secret was revoked; and each repository's scan progress, so a pass

-- resumes where the last one stopped and a push is scanned incrementally.

ALTER TABLE secret_findings ADD COLUMN source TEXT NOT NULL DEFAULT 'push';

ALTER TABLE secret_findings ADD COLUMN change_hash TEXT NOT NULL DEFAULT '';

ALTER TABLE secret_findings ADD COLUMN bookmark TEXT NOT NULL DEFAULT '';

ALTER TABLE secret_findings ADD COLUMN resolved_ms BIGINT;

ALTER TABLE secret_findings ADD COLUMN resolved_by TEXT NOT NULL DEFAULT '';

ALTER TABLE secret_findings ADD COLUMN resolved_reason TEXT NOT NULL DEFAULT '';

-- One history finding per value and file, however many changes carry it.

CREATE UNIQUE INDEX secret_findings_history ON secret_findings(repo_id, fingerprint, path) WHERE source = 'history';

CREATE TABLE secret_scans (

    repo_id       BIGINT PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    rules_version BIGINT NOT NULL DEFAULT 0,

    done_heads    TEXT NOT NULL DEFAULT '',

    target_heads  TEXT NOT NULL DEFAULT '',

    position      BIGINT NOT NULL DEFAULT 0,

    pending       BIGINT NOT NULL DEFAULT 0,

    changes       BIGINT NOT NULL DEFAULT 0,

    bytes         BIGINT NOT NULL DEFAULT 0,

    requested_ms  BIGINT,

    pass_ms       BIGINT,

    finished_ms   BIGINT,

    error         TEXT NOT NULL DEFAULT ''

);