-- 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 INTEGER;

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       INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    rules_version INTEGER NOT NULL DEFAULT 0,

    done_heads    TEXT NOT NULL DEFAULT '',

    target_heads  TEXT NOT NULL DEFAULT '',

    position      INTEGER NOT NULL DEFAULT 0,

    pending       INTEGER NOT NULL DEFAULT 0,

    changes       INTEGER NOT NULL DEFAULT 0,

    bytes         INTEGER NOT NULL DEFAULT 0,

    requested_ms  INTEGER,

    pass_ms       INTEGER,

    finished_ms   INTEGER,

    error         TEXT NOT NULL DEFAULT ''

);