g1t/services/security/migrations/0002_dismissals_and_updates.sql
| 1 | -- Dismissals with reasons, likely test values, each alert's activity, and |
| 2 | -- security updates: the pull requests g1t opens itself to upgrade a |
| 3 | -- vulnerable package. |
| 4 | |
| 5 | -- Why a secret was dismissed (false_positive, used_in_tests, revoked, |
| 6 | -- wont_fix), and why its value looks made for tests, when it does. |
| 7 | ALTER TABLE secrets ADD COLUMN dismiss_reason TEXT; |
| 8 | ALTER TABLE secrets ADD COLUMN test_value TEXT; |
| 9 | |
| 10 | -- A vulnerability someone dismissed: its status is 'dismissed', and these |
| 11 | -- stay while it is found again, until someone reopens it. |
| 12 | ALTER TABLE vulnerabilities ADD COLUMN dismiss_reason TEXT; |
| 13 | ALTER TABLE vulnerabilities ADD COLUMN dismiss_comment TEXT; |
| 14 | ALTER TABLE vulnerabilities ADD COLUMN dismissed_by TEXT; |
| 15 | ALTER TABLE vulnerabilities ADD COLUMN dismissed_at TEXT; |
| 16 | |
| 17 | -- What `.g1t/dependencies.yml` said when the dependencies were last read: |
| 18 | -- JSON of the contracts' VersionUpdatesState. |
| 19 | ALTER TABLE repos ADD COLUMN version_updates TEXT; |
| 20 | |
| 21 | -- What happened to each alert: dismissed, reopened, and its security |
| 22 | -- update's steps. Found and decided-before-this rows are read from the |
| 23 | -- alerts themselves. |
| 24 | CREATE TABLE alert_activity ( |
| 25 | id TEXT PRIMARY KEY, |
| 26 | repo_id TEXT NOT NULL, |
| 27 | alert_id TEXT NOT NULL, |
| 28 | action TEXT NOT NULL, |
| 29 | actor TEXT, |
| 30 | reason TEXT, |
| 31 | comment TEXT, |
| 32 | number INTEGER, |
| 33 | at TEXT NOT NULL |
| 34 | ); |
| 35 | CREATE INDEX alert_activity_repo ON alert_activity (repo_id, at); |
| 36 | |
| 37 | -- One security update per vulnerable package: the branch a sandbox pushes |
| 38 | -- the new version to, then the pull request g1t opens from it. |
| 39 | -- state: requested | open | merged | closed | superseded | needs_code | failed |
| 40 | CREATE TABLE updates ( |
| 41 | repo_id TEXT NOT NULL, |
| 42 | ecosystem TEXT NOT NULL, |
| 43 | package TEXT NOT NULL, |
| 44 | target TEXT NOT NULL, |
| 45 | state TEXT NOT NULL, |
| 46 | branch TEXT, |
| 47 | pull INTEGER, |
| 48 | issue INTEGER, |
| 49 | error TEXT, |
| 50 | requested_at TEXT NOT NULL, |
| 51 | updated_at TEXT NOT NULL, |
| 52 | PRIMARY KEY (repo_id, ecosystem, package) |
| 53 | ); |
| 54 | CREATE INDEX updates_state ON updates (state, updated_at); |
| 55 | CREATE INDEX updates_branch ON updates (repo_id, branch); |
| 56 | |
| 57 | -- Pushes too large to scan before they were stored (repos let them through |
| 58 | -- unscanned, so imports of real repositories work): their new commits, |
| 59 | -- `base`..`head` on `git_ref`, scanned after they land, a page at a time. |
| 60 | -- state: pending | done |
| 61 | CREATE TABLE push_scans ( |
| 62 | id TEXT PRIMARY KEY, |
| 63 | repo_id TEXT NOT NULL, |
| 64 | git_ref TEXT NOT NULL, |
| 65 | head TEXT NOT NULL, |
| 66 | -- Where the branch was before the push; null for a new branch. |
| 67 | base TEXT, |
| 68 | -- Where the next page starts; null before the first. |
| 69 | cursor TEXT, |
| 70 | pusher TEXT, |
| 71 | state TEXT NOT NULL DEFAULT 'pending', |
| 72 | commits INTEGER NOT NULL DEFAULT 0, |
| 73 | pages INTEGER NOT NULL DEFAULT 0, |
| 74 | found INTEGER NOT NULL DEFAULT 0, |
| 75 | created_at TEXT NOT NULL, |
| 76 | finished_at TEXT |
| 77 | ); |
| 78 | CREATE INDEX push_scans_state ON push_scans (state, created_at); |