audit-labs/gh-attest

  1-- Migration 0008: fixes from the evidence-to-control mapping review.
  2--
  3-- 1. Secret-scanning status vocabulary. The secret_scanning_alert webhook
  4--    payload has no alert.state field (unlike Dependabot / code scanning), so
  5--    ingest recorded the webhook action (created/reopened/...) and the 'open'
  6--    mappings never matched — an open leak exported only positive rows.
  7--    Ingest now derives open/resolved from alert.resolution; historical rows
  8--    are backfilled the same way below.
  9-- 2. Per-entity subjects. Alert/member/team snapshots had a NULL subject, so
 10--    "latest row wins per (repo, subject, resource)" collapsed every alert of
 11--    a type in a repo into one evidence row — one fixed alert masked any
 12--    number of still-open ones. Ingest now stores the alert number / login /
 13--    slug in subject; historical rows are backfilled from raw_payload.
 14-- 3. Tooling-active re-homed. "Detection tooling is active" was inferred from
 15--    the latest alert event, which keeps attesting after the scanner is
 16--    disabled and never fires for a clean repo. The poller now reports the
 17--    feature state directly (resources dependabot / code_scanning /
 18--    secret_scanning, status enabled|disabled|unavailable); the status-NULL
 19--    alert mappings are replaced by 'enabled' mappings on those resources.
 20--    disabled/unavailable stay unmapped — missing tooling is recorded but not
 21--    claimed either way, matching the branch-protection precedent.
 22-- 4. SOC 2 vulnerability lifecycle consolidated under CC7.1, resolving the
 23--    CC7.1-vs-CC7.2 question left open in docs/framework-mapping.md: a known
 24--    vulnerability sits in CC7.1's "susceptibility to newly discovered
 25--    vulnerabilities" language, not CC7.2's runtime anomaly monitoring. Code
 26--    scanning gains the finding-level SOC 2 rows deferred on that decision.
 27-- 5. Human dismissals downgraded to informational. A dismissal (or a secret
 28--    "resolved" that may be wont_fix) is a recorded human decision, not a
 29--    verified remediation — the justification is what an auditor samples.
 30--    Machine-verified outcomes (fixed, auto_dismissed) stay positive.
 31-- 6. Branch-protection rationales reworded to what is actually verified: a
 32--    protection rule / active ruleset covers the default branch; the rule
 33--    contents are not checked.
 34-- 7. The `member` webhook is repository-collaborator scoped: rationales now
 35--    say so, "removed" no longer claims timeliness the event can't prove, and
 36--    org-level membership events (`organization` webhook -> org_membership)
 37--    are mapped. member_access gains the ISO A.5.18 rows it was missing.
 38-- 8. repository 'publicized' additionally flagged to SOC 2 CC6.1 — a repo
 39--    going public is a visibility change worth surfacing, not just an
 40--    inventory tick.
 41
 42-- (1) Backfill secret-scanning statuses recorded from the raw webhook action.
 43UPDATE snapshots
 44SET status = CASE
 45  WHEN json_extract(raw_payload, '$.alert.resolution') IS NULL THEN 'open'
 46  ELSE 'resolved'
 47END
 48WHERE resource = 'secret_scanning_alert'
 49  AND status NOT IN ('open', 'resolved')
 50  AND raw_payload IS NOT NULL;
 51
 52-- (2) Backfill per-entity subjects from the retained webhook payloads.
 53UPDATE snapshots
 54SET subject = CAST(json_extract(raw_payload, '$.alert.number') AS TEXT)
 55WHERE resource IN ('dependabot_alert', 'code_scanning_alert', 'secret_scanning_alert')
 56  AND subject IS NULL
 57  AND json_extract(raw_payload, '$.alert.number') IS NOT NULL;
 58
 59UPDATE snapshots
 60SET subject = json_extract(raw_payload, '$.member.login')
 61WHERE resource = 'member_access'
 62  AND subject IS NULL
 63  AND json_extract(raw_payload, '$.member.login') IS NOT NULL;
 64
 65UPDATE snapshots
 66SET subject = json_extract(raw_payload, '$.team.slug')
 67WHERE resource = 'team'
 68  AND subject IS NULL
 69  AND json_extract(raw_payload, '$.team.slug') IS NOT NULL;
 70
 71-- (3)-(8) Replace the mappings for every affected resource wholesale (same
 72-- pattern as migration 0003).
 73DELETE FROM control_mappings WHERE resource IN
 74  ('dependabot_alert', 'code_scanning_alert', 'secret_scanning_alert',
 75   'branch_protection', 'repository_ruleset', 'member_access');
 76
 77INSERT INTO control_mappings (resource, status, framework, control_id, posture, rationale) VALUES
 78  -- Branch protection / rulesets: state of the default branch's merge gate.
 79  ('branch_protection', 'enabled', 'soc2', 'CC8.1', 'positive', 'Change management — a protection rule is enforced on the default branch (rule contents not verified)'),
 80  ('branch_protection', 'disabled', 'soc2', 'CC8.1', 'negative', 'Change-control gap — no protection on the default branch; direct pushes possible'),
 81  ('branch_protection', 'enabled', 'iso27001', 'A.8.32', 'positive', 'Change management — a protection rule is enforced on the default branch (rule contents not verified)'),
 82  ('branch_protection', 'disabled', 'iso27001', 'A.8.32', 'negative', 'Change-control gap — no protection on the default branch; direct pushes possible'),
 83  ('repository_ruleset', 'enabled', 'soc2', 'CC8.1', 'positive', 'Change management — an active ruleset covers the default branch (rule contents not verified)'),
 84  ('repository_ruleset', 'disabled', 'soc2', 'CC8.1', 'negative', 'Change-control gap — no active ruleset covers the default branch'),
 85  ('repository_ruleset', 'enabled', 'iso27001', 'A.8.32', 'positive', 'Change management — an active ruleset covers the default branch (rule contents not verified)'),
 86  ('repository_ruleset', 'disabled', 'iso27001', 'A.8.32', 'negative', 'Change-control gap — no active ruleset covers the default branch'),
 87
 88  -- Detection tooling state (polled; disabled/unavailable deliberately unmapped).
 89  ('dependabot', 'enabled', 'soc2', 'CC7.1', 'positive', 'Detection tooling — Dependabot alerts are enabled on the repository'),
 90  ('dependabot', 'enabled', 'iso27001', 'A.8.8', 'positive', 'Technical vulnerability management — Dependabot alerts are enabled on the repository'),
 91  ('code_scanning', 'enabled', 'soc2', 'CC7.1', 'positive', 'Detection tooling — code scanning is enabled on the repository'),
 92  ('code_scanning', 'enabled', 'iso27001', 'A.8.29', 'positive', 'Security testing in development — code scanning is enabled on the repository'),
 93  ('secret_scanning', 'enabled', 'soc2', 'CC6.6', 'positive', 'Leaked-credential detection — secret scanning is enabled on the repository'),
 94  ('secret_scanning', 'enabled', 'soc2', 'CC6.1', 'positive', 'Logical-access credential protection — secret scanning is enabled on the repository'),
 95  ('secret_scanning', 'enabled', 'iso27001', 'A.5.17', 'positive', 'Authentication-information protection — secret scanning is enabled on the repository'),
 96
 97  -- Dependabot findings.
 98  ('dependabot_alert', 'open', 'soc2', 'CC7.1', 'negative', 'Unremediated known vulnerability'),
 99  ('dependabot_alert', 'fixed', 'soc2', 'CC7.1', 'positive', 'Vulnerability remediated'),
100  ('dependabot_alert', 'dismissed', 'soc2', 'CC7.1', 'informational', 'Dismissed by a user — risk-acceptance justification subject to review'),
101  ('dependabot_alert', 'auto_dismissed', 'soc2', 'CC7.1', 'positive', 'Auto-dismissed by GitHub (e.g. dependency removed)'),
102  ('dependabot_alert', 'open', 'iso27001', 'A.8.8', 'negative', 'Unremediated known technical vulnerability'),
103  ('dependabot_alert', 'fixed', 'iso27001', 'A.8.8', 'positive', 'Vulnerability remediated'),
104  ('dependabot_alert', 'dismissed', 'iso27001', 'A.8.8', 'informational', 'Dismissed by a user — risk-acceptance justification subject to review'),
105  ('dependabot_alert', 'auto_dismissed', 'iso27001', 'A.8.8', 'positive', 'Auto-dismissed by GitHub (e.g. dependency removed)'),
106
107  -- Code scanning findings.
108  ('code_scanning_alert', 'open', 'soc2', 'CC7.1', 'negative', 'Unremediated static-analysis finding'),
109  ('code_scanning_alert', 'fixed', 'soc2', 'CC7.1', 'positive', 'Finding remediated'),
110  ('code_scanning_alert', 'dismissed', 'soc2', 'CC7.1', 'informational', 'Dismissed by a user — risk-acceptance justification subject to review'),
111  ('code_scanning_alert', 'open', 'iso27001', 'A.8.28', 'negative', 'Unremediated static-analysis finding'),
112  ('code_scanning_alert', 'fixed', 'iso27001', 'A.8.28', 'positive', 'Finding remediated'),
113  ('code_scanning_alert', 'dismissed', 'iso27001', 'A.8.28', 'informational', 'Dismissed by a user — risk-acceptance justification subject to review'),
114
115  -- Secret scanning findings ("resolved" may be wont_fix — a human decision,
116  -- not a verified remediation).
117  ('secret_scanning_alert', 'open', 'soc2', 'CC6.6', 'negative', 'Live credential exposure'),
118  ('secret_scanning_alert', 'resolved', 'soc2', 'CC6.6', 'informational', 'Resolution recorded — reason (revoked vs. won''t-fix) subject to review'),
119  ('secret_scanning_alert', 'open', 'soc2', 'CC6.1', 'negative', 'Exposed credential undermines logical access controls'),
120  ('secret_scanning_alert', 'resolved', 'soc2', 'CC6.1', 'informational', 'Resolution recorded — reason (revoked vs. won''t-fix) subject to review'),
121  ('secret_scanning_alert', 'open', 'iso27001', 'A.5.17', 'negative', 'Exposed authentication information'),
122  ('secret_scanning_alert', 'resolved', 'iso27001', 'A.5.17', 'informational', 'Resolution recorded — reason (revoked vs. won''t-fix) subject to review'),
123
124  -- Repository collaborators (the `member` webhook is repo-scoped).
125  ('member_access', 'added', 'soc2', 'CC6.2', 'informational', 'Repository collaborator added — access grant logged for review'),
126  ('member_access', 'removed', 'soc2', 'CC6.3', 'informational', 'Repository collaborator removed — deprovisioning recorded; timeliness subject to review'),
127  ('member_access', 'edited', 'soc2', 'CC6.3', 'informational', 'Repository collaborator permission changed — logged for review'),
128  ('member_access', 'added', 'iso27001', 'A.5.18', 'informational', 'Repository collaborator added — access-rights change, audit trail'),
129  ('member_access', 'removed', 'iso27001', 'A.5.18', 'informational', 'Repository collaborator removed — access-rights change, audit trail'),
130  ('member_access', 'edited', 'iso27001', 'A.5.18', 'informational', 'Repository collaborator permission changed — access-rights change, audit trail'),
131
132  -- Organization membership changes (`organization` webhook).
133  ('org_membership', 'member_added', 'soc2', 'CC6.2', 'informational', 'Organization member added — access grant logged for review'),
134  ('org_membership', 'member_removed', 'soc2', 'CC6.3', 'informational', 'Organization member removed — deprovisioning recorded; timeliness subject to review'),
135  ('org_membership', 'member_invited', 'soc2', 'CC6.2', 'informational', 'Organization invitation issued — logged for review'),
136  ('org_membership', 'member_added', 'iso27001', 'A.5.18', 'informational', 'Organization member added — access-rights change, audit trail'),
137  ('org_membership', 'member_removed', 'iso27001', 'A.5.18', 'informational', 'Organization member removed — access-rights change, audit trail'),
138  ('org_membership', 'member_invited', 'iso27001', 'A.5.18', 'informational', 'Organization invitation issued — access-rights change, audit trail'),
139
140  -- Repository made public: worth surfacing beyond the generic inventory trail.
141  ('repository', 'publicized', 'soc2', 'CC6.1', 'informational', 'Repository made public — visibility change affecting asset confidentiality, flagged for review');