audit-labs/gh-attest

 1-- snapshots.control_id was a placeholder for a mapping approach we didn't
 2-- end up using — mapping happens via a join against control_mappings at
 3-- query/export time instead, so re-mapping doesn't require re-ingesting
 4-- webhook history.
 5DROP INDEX IF EXISTS idx_snapshots_control;
 6ALTER TABLE snapshots DROP COLUMN control_id;
 7
 8CREATE TABLE control_mappings (
 9  id INTEGER PRIMARY KEY AUTOINCREMENT,
10  resource TEXT NOT NULL,
11  status TEXT,               -- NULL matches any status for this resource
12  framework TEXT NOT NULL,   -- 'soc2' | 'iso27001'
13  control_id TEXT NOT NULL,  -- e.g. 'CC8.1', 'A.8.32'
14  posture TEXT NOT NULL,     -- 'positive' | 'negative' | 'informational'
15  rationale TEXT NOT NULL
16);
17
18CREATE INDEX idx_control_mappings_resource ON control_mappings(resource);
19
20INSERT INTO control_mappings (resource, status, framework, control_id, posture, rationale) VALUES
21  -- branch protection / ruleset present
22  ('branch_protection', 'created', 'soc2', 'CC8.1', 'positive', 'Change management — code changes require review before merge'),
23  ('branch_protection', 'edited', 'soc2', 'CC8.1', 'positive', 'Change management — code changes require review before merge'),
24  ('branch_protection', 'created', 'iso27001', 'A.8.32', 'positive', 'Change management'),
25  ('branch_protection', 'edited', 'iso27001', 'A.8.32', 'positive', 'Change management'),
26  ('repository_ruleset', 'created', 'soc2', 'CC8.1', 'positive', 'Change management — code changes require review before merge'),
27  ('repository_ruleset', 'edited', 'soc2', 'CC8.1', 'positive', 'Change management — code changes require review before merge'),
28  ('repository_ruleset', 'created', 'iso27001', 'A.8.32', 'positive', 'Change management'),
29  ('repository_ruleset', 'edited', 'iso27001', 'A.8.32', 'positive', 'Change management'),
30
31  -- branch protection / ruleset removed
32  ('branch_protection', 'deleted', 'soc2', 'CC8.1', 'negative', 'Change control gap — direct pushes now possible'),
33  ('branch_protection', 'deleted', 'iso27001', 'A.8.32', 'negative', 'Change control gap — direct pushes now possible'),
34  ('repository_ruleset', 'deleted', 'soc2', 'CC8.1', 'negative', 'Change control gap — direct pushes now possible'),
35  ('repository_ruleset', 'deleted', 'iso27001', 'A.8.32', 'negative', 'Change control gap — direct pushes now possible'),
36
37  -- dependabot_alert
38  ('dependabot_alert', NULL, 'soc2', 'CC7.1', 'positive', 'Detection tooling is active'),
39  ('dependabot_alert', 'open', 'soc2', 'CC7.2', 'negative', 'Unremediated known vulnerability'),
40  ('dependabot_alert', 'fixed', 'soc2', 'CC7.2', 'positive', 'Remediated'),
41  ('dependabot_alert', 'dismissed', 'soc2', 'CC7.2', 'positive', 'Remediated (risk accepted)'),
42  ('dependabot_alert', 'auto_dismissed', 'soc2', 'CC7.2', 'positive', 'Remediated (e.g. dependency removed)'),
43
44  -- code_scanning_alert
45  ('code_scanning_alert', NULL, 'iso27001', 'A.8.29', 'positive', 'Security testing in development is active'),
46  ('code_scanning_alert', 'open', 'iso27001', 'A.8.28', 'negative', 'Unremediated finding'),
47  ('code_scanning_alert', 'fixed', 'iso27001', 'A.8.28', 'positive', 'Remediated'),
48  ('code_scanning_alert', 'dismissed', 'iso27001', 'A.8.28', 'positive', 'Remediated (risk accepted)'),
49
50  -- secret_scanning_alert
51  ('secret_scanning_alert', NULL, 'soc2', 'CC6.6', 'positive', 'Leaked-credential detection is active'),
52  ('secret_scanning_alert', 'open', 'soc2', 'CC6.6', 'negative', 'Live credential exposure'),
53  ('secret_scanning_alert', 'resolved', 'soc2', 'CC6.6', 'positive', 'Exposure remediated'),
54
55  -- member_access
56  ('member_access', 'added', 'soc2', 'CC6.2', 'informational', 'Access grant — logged for review'),
57  ('member_access', 'removed', 'soc2', 'CC6.3', 'positive', 'Timely access removal'),
58  ('member_access', 'edited', 'soc2', 'CC6.3', 'informational', 'Access-level change — logged for review'),
59
60  -- team and repository metadata — audit trail, not itself pass/fail
61  ('team', NULL, 'iso27001', 'A.5.18', 'informational', 'Access-rights change, audit trail'),
62  ('repository', NULL, 'iso27001', 'A.5.9', 'informational', 'Asset inventory trail');