Skip to content
Sign in

flagon-io/g1t

public
0

g1t/services/security/migrations/0005_security_suite.sql

234 lines7,502 bytesCodeBlame
1-- The security suite: custom secret patterns, push protection bypasses
2-- and their review, validity checks, code scanning from SARIF uploads,
3-- the dependency graph, dependency review on pull requests, and the daily
4-- counts the workspace's overview draws its trends from.
5
6-- Whether the repository is private (paid features need the activation on
7-- a private one), and its settings: JSON of the contracts'
8-- RepoSecuritySettings, defaults when null.
9ALTER TABLE repos ADD COLUMN private INTEGER NOT NULL DEFAULT 1;
10ALTER TABLE repos ADD COLUMN settings TEXT;
11
12-- What a secret's issuer said when last asked, how it got past push
13-- protection, and the custom pattern that found it.
14ALTER TABLE secrets ADD COLUMN validity TEXT;
15ALTER TABLE secrets ADD COLUMN validity_checked_at TEXT;
16ALTER TABLE secrets ADD COLUMN bypass_reason TEXT;
17ALTER TABLE secrets ADD COLUMN bypass_comment TEXT;
18ALTER TABLE secrets ADD COLUMN bypassed_by TEXT;
19ALTER TABLE secrets ADD COLUMN bypassed_at TEXT;
20ALTER TABLE secrets ADD COLUMN bypass_approved_by TEXT;
21ALTER TABLE secrets ADD COLUMN pattern_id TEXT;
22ALTER TABLE secrets ADD COLUMN pattern_name TEXT;
23
24-- Every place a secret was found: a secret is one alert however many
25-- files, lines and commits hold it.
26CREATE TABLE secret_locations (
27 secret_id TEXT NOT NULL,
28 repo_id TEXT NOT NULL,
29 path TEXT NOT NULL,
30 line INTEGER NOT NULL,
31 commit_hash TEXT NOT NULL,
32 -- push | history
33 source TEXT NOT NULL,
34 found_at TEXT NOT NULL,
35 PRIMARY KEY (secret_id, path, line, commit_hash)
36);
37CREATE INDEX secret_locations_repo ON secret_locations (repo_id);
38
39-- A workspace's security settings.
40CREATE TABLE workspace_settings (
41 namespace TEXT PRIMARY KEY,
42 delegated_bypass INTEGER NOT NULL DEFAULT 0,
43 validity_checks INTEGER NOT NULL DEFAULT 0,
44 updated_by TEXT,
45 updated_at TEXT
46);
47
48-- Custom patterns: a workspace's (repo_id null) or one repository's.
49-- state: draft | published
50CREATE TABLE custom_patterns (
51 id TEXT PRIMARY KEY,
52 namespace TEXT NOT NULL,
53 repo_id TEXT,
54 name TEXT NOT NULL,
55 pattern TEXT NOT NULL,
56 before_text TEXT,
57 after_text TEXT,
58 -- JSON array of strings.
59 test_strings TEXT NOT NULL DEFAULT '[]',
60 state TEXT NOT NULL,
61 created_by TEXT NOT NULL,
62 created_at TEXT NOT NULL,
63 updated_by TEXT NOT NULL,
64 updated_at TEXT NOT NULL
65);
66CREATE INDEX custom_patterns_scope ON custom_patterns (namespace, repo_id);
67
68-- Requests to bypass push protection, when delegated bypass is on.
69-- state: pending | approved | denied | cancelled
70CREATE TABLE bypass_requests (
71 id TEXT PRIMARY KEY,
72 repo_id TEXT NOT NULL,
73 namespace TEXT NOT NULL,
74 secret_id TEXT NOT NULL,
75 requester TEXT NOT NULL,
76 reason TEXT NOT NULL,
77 comment TEXT,
78 state TEXT NOT NULL DEFAULT 'pending',
79 reviewer TEXT,
80 review_comment TEXT,
81 created_at TEXT NOT NULL,
82 reviewed_at TEXT
83);
84CREATE INDEX bypass_requests_namespace ON bypass_requests (namespace, state, created_at);
85CREATE INDEX bypass_requests_secret ON bypass_requests (secret_id);
86
87-- SARIF uploads, each read at once into analyses.
88-- status: complete | failed
89CREATE TABLE sarif_uploads (
90 id TEXT PRIMARY KEY,
91 repo_id TEXT NOT NULL,
92 commit_sha TEXT NOT NULL,
93 git_ref TEXT NOT NULL,
94 status TEXT NOT NULL,
95 -- JSON arrays: what was wrong, and the analyses made.
96 errors TEXT NOT NULL DEFAULT '[]',
97 analyses TEXT NOT NULL DEFAULT '[]',
98 created_by TEXT,
99 created_at TEXT NOT NULL
100);
101CREATE INDEX sarif_uploads_repo ON sarif_uploads (repo_id, created_at);
102
103-- One tool's run on one commit.
104CREATE TABLE analyses (
105 id TEXT PRIMARY KEY,
106 repo_id TEXT NOT NULL,
107 sarif_id TEXT NOT NULL,
108 tool TEXT NOT NULL,
109 tool_version TEXT,
110 category TEXT NOT NULL,
111 commit_sha TEXT NOT NULL,
112 git_ref TEXT NOT NULL,
113 pull INTEGER,
114 results INTEGER NOT NULL DEFAULT 0,
115 new_alerts INTEGER NOT NULL DEFAULT 0,
116 fixed_alerts INTEGER NOT NULL DEFAULT 0,
117 dropped INTEGER NOT NULL DEFAULT 0,
118 created_at TEXT NOT NULL
119);
120CREATE INDEX analyses_repo ON analyses (repo_id, created_at);
121CREATE INDEX analyses_pull ON analyses (repo_id, pull, created_at);
122
123-- Code scanning alerts on the default branch, one per tool, category and
124-- fingerprint, numbered per repository.
125-- status: open | dismissed | fixed
126CREATE TABLE code_alerts (
127 id TEXT PRIMARY KEY,
128 repo_id TEXT NOT NULL,
129 number INTEGER NOT NULL,
130 tool TEXT NOT NULL,
131 category TEXT NOT NULL,
132 fingerprint TEXT NOT NULL,
133 rule_id TEXT NOT NULL,
134 rule_name TEXT,
135 rule_description TEXT,
136 help TEXT,
137 help_uri TEXT,
138 tags TEXT NOT NULL DEFAULT '[]',
139 level TEXT NOT NULL,
140 security_severity TEXT,
141 severity TEXT NOT NULL,
142 message TEXT NOT NULL,
143 path TEXT,
144 start_line INTEGER,
145 end_line INTEGER,
146 start_column INTEGER,
147 end_column INTEGER,
148 status TEXT NOT NULL,
149 first_commit TEXT NOT NULL,
150 last_commit TEXT NOT NULL,
151 created_at TEXT NOT NULL,
152 updated_at TEXT NOT NULL,
153 fixed_at TEXT,
154 dismiss_reason TEXT,
155 dismiss_comment TEXT,
156 dismissed_by TEXT,
157 dismissed_at TEXT,
158 issue INTEGER,
159 UNIQUE (repo_id, tool, category, fingerprint),
160 UNIQUE (repo_id, number)
161);
162CREATE INDEX code_alerts_repo ON code_alerts (repo_id, status);
163
164-- Each analysis's results by fingerprint, so alerts can name the analyses
165-- that reported them and pull requests their own results.
166CREATE TABLE analysis_results (
167 analysis_id TEXT NOT NULL,
168 repo_id TEXT NOT NULL,
169 fingerprint TEXT NOT NULL,
170 -- JSON of the result: rule, level, severities, message, location.
171 result TEXT NOT NULL,
172 PRIMARY KEY (analysis_id, fingerprint)
173);
174CREATE INDEX analysis_results_repo ON analysis_results (repo_id, fingerprint);
175
176-- What the suite reported on each pull request: one row per check
177-- (`code` or `review`), for its head commit.
178CREATE TABLE pull_checks (
179 repo_id TEXT NOT NULL,
180 pull INTEGER NOT NULL,
181 kind TEXT NOT NULL,
182 commit_sha TEXT NOT NULL,
183 -- success | failure | error
184 state TEXT NOT NULL,
185 description TEXT NOT NULL,
186 -- JSON: the review, or the code scanning results.
187 detail TEXT NOT NULL DEFAULT '{}',
188 -- Fingerprints already commented on, JSON array, so a result is
189 -- commented on once per pull request.
190 commented TEXT NOT NULL DEFAULT '[]',
191 updated_at TEXT NOT NULL,
192 PRIMARY KEY (repo_id, pull, kind)
193);
194
195-- The dependency graph: every package the lockfiles on the default branch
196-- resolve, replaced on each read.
197CREATE TABLE dependencies (
198 repo_id TEXT NOT NULL,
199 manifest TEXT NOT NULL,
200 ecosystem TEXT NOT NULL,
201 name TEXT NOT NULL,
202 version TEXT NOT NULL,
203 -- direct | transitive | unknown
204 relationship TEXT NOT NULL,
205 development INTEGER NOT NULL DEFAULT 0,
206 license TEXT,
207 PRIMARY KEY (repo_id, manifest, ecosystem, name, version)
208);
209
210-- Fixes g1t was put on for secrets and vulnerable dependencies (a code
211-- scanning alert keeps its own issue).
212CREATE TABLE alert_fixes (
213 alert_id TEXT PRIMARY KEY,
214 repo_id TEXT NOT NULL,
215 issue INTEGER NOT NULL,
216 created_by TEXT NOT NULL,
217 created_at TEXT NOT NULL
218);
219
220-- Open alerts by type and severity, once a day per repository: the
221-- overview's trends.
222CREATE TABLE snapshots (
223 repo_id TEXT NOT NULL,
224 namespace TEXT NOT NULL,
225 day TEXT NOT NULL,
226 alert_type TEXT NOT NULL,
227 critical INTEGER NOT NULL DEFAULT 0,
228 high INTEGER NOT NULL DEFAULT 0,
229 medium INTEGER NOT NULL DEFAULT 0,
230 low INTEGER NOT NULL DEFAULT 0,
231 unknown INTEGER NOT NULL DEFAULT 0,
232 PRIMARY KEY (repo_id, day, alert_type)
233);
234CREATE INDEX snapshots_namespace ON snapshots (namespace, day);