g1t/services/security/migrations/0002_dismissals_and_updates.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Git storage hardened, pages in tens of milliseconds, honest security alerts, and costs reconciled daily | 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); |