audit-labs/gh-attest

  1import { PDFDocument, StandardFonts, rgb, type PDFFont, type PDFPage } from "pdf-lib";
  2
  3export type Framework = "soc2" | "iso27001" | "all";
  4export type ExportFormat = "csv" | "pdf";
  5
  6export interface EvidenceRow {
  7  repo: string | null;
  8  // Org-scoped facts (access review) identify a person or team here rather
  9  // than a repository.
 10  subject: string | null;
 11  resource: string;
 12  status: string;
 13  framework: string;
 14  control_id: string;
 15  posture: string;
 16  rationale: string;
 17  captured_at: string;
 18}
 19
 20// Current posture = the latest snapshot per (repo, resource) for this
 21// installation, joined to the control mapping table. snapshots is
 22// append-only, so "latest row wins" gives point-in-time current state.
 23// Rows whose resource/status pair has no mapping (e.g. "unavailable", or
 24// raw push/installation events) simply don't appear — no evidence either way.
 25export async function buildEvidenceRows(
 26  db: D1Database,
 27  installationId: number,
 28  framework: Framework,
 29): Promise<EvidenceRow[]> {
 30  const { results } = await db
 31    .prepare(
 32      `WITH access_latest AS (
 33         SELECT MAX(captured_at) AS t FROM snapshots
 34         WHERE installation_id = ?1 AND resource IN ('org_member', 'team_member')
 35       ),
 36       latest AS (
 37         SELECT repo, subject, resource, status, captured_at,
 38                ROW_NUMBER() OVER (
 39                  PARTITION BY repo, subject, resource
 40                  ORDER BY captured_at DESC, id DESC
 41                ) AS rn
 42         FROM snapshots
 43         WHERE installation_id = ?1
 44       )
 45       SELECT l.repo, l.subject, l.resource, l.status, cm.framework, cm.control_id,
 46              cm.posture, cm.rationale, l.captured_at
 47       FROM latest l
 48       JOIN control_mappings cm
 49         ON cm.resource = l.resource
 50        AND (cm.status IS NULL OR cm.status = l.status)
 51       WHERE l.rn = 1
 52         AND (?2 = 'all' OR cm.framework = ?2)
 53         -- Access facts are a full set per poll: a member who lost access has
 54         -- no newer row, so "latest row per subject" would keep attesting
 55         -- their access forever. Only the most recent poll batch counts.
 56         AND (
 57           l.resource NOT IN ('org_member', 'team_member')
 58           OR l.captured_at = (SELECT t FROM access_latest)
 59         )
 60       ORDER BY cm.framework, cm.control_id, l.repo`,
 61    )
 62    .bind(installationId, framework)
 63    .all<EvidenceRow>();
 64
 65  return results;
 66}
 67
 68const CSV_COLUMNS: Array<keyof EvidenceRow> = [
 69  "framework",
 70  "control_id",
 71  "posture",
 72  "repo",
 73  "subject",
 74  "resource",
 75  "status",
 76  "rationale",
 77  "captured_at",
 78];
 79
 80export function renderCsv(rows: EvidenceRow[]): string {
 81  const lines = [CSV_COLUMNS.map(csvEscape).join(",")];
 82  for (const row of rows) {
 83    lines.push(CSV_COLUMNS.map((col) => csvEscape(row[col] ?? "")).join(","));
 84  }
 85  // CRLF line endings — RFC 4180, and what spreadsheet apps expect.
 86  return lines.join("\r\n") + "\r\n";
 87}
 88
 89function csvEscape(value: string | number): string {
 90  const str = String(value);
 91  if (/[",\r\n]/.test(str)) {
 92    return `"${str.replaceAll('"', '""')}"`;
 93  }
 94  return str;
 95}
 96
 97export interface ExportMeta {
 98  framework: Framework;
 99  installationId: number;
100  generatedAt: string;
101}
102
103interface Column {
104  header: string;
105  key: keyof EvidenceRow;
106  width: number;
107}
108
109const PDF_COLUMNS: Column[] = [
110  { header: "Control", key: "control_id", width: 60 },
111  { header: "Posture", key: "posture", width: 75 },
112  { header: "Repo / Subject", key: "repo", width: 175 },
113  { header: "Resource", key: "resource", width: 110 },
114  { header: "Status", key: "status", width: 82 },
115];
116
117const PAGE = { width: 612, height: 792, margin: 50 };
118const ROW_HEIGHT = 15;
119const FONT_SIZE = 8;
120
121const POSTURE_COLOR: Record<string, ReturnType<typeof rgb>> = {
122  positive: rgb(0.1, 0.5, 0.2),
123  negative: rgb(0.7, 0.15, 0.15),
124  informational: rgb(0.4, 0.4, 0.4),
125};
126
127export async function renderPdf(rows: EvidenceRow[], meta: ExportMeta): Promise<Uint8Array> {
128  const doc = await PDFDocument.create();
129  const font = await doc.embedFont(StandardFonts.Helvetica);
130  const bold = await doc.embedFont(StandardFonts.HelveticaBold);
131
132  const frameworkLabel = meta.framework === "all" ? "SOC 2 + ISO 27001" : meta.framework.toUpperCase();
133  let page = doc.addPage([PAGE.width, PAGE.height]);
134  let y = PAGE.height - PAGE.margin;
135
136  // Title block.
137  page.drawText("Compliance Evidence", { x: PAGE.margin, y, size: 18, font: bold });
138  y -= 22;
139  page.drawText(
140    `${frameworkLabel}  ·  Installation ${meta.installationId}  ·  Generated ${meta.generatedAt}  ·  ${rows.length} findings`,
141    { x: PAGE.margin, y, size: 9, font, color: rgb(0.35, 0.35, 0.35) },
142  );
143  y -= 24;
144
145  y = drawHeader(page, bold, y);
146
147  for (const row of rows) {
148    if (y < PAGE.margin + ROW_HEIGHT) {
149      page = doc.addPage([PAGE.width, PAGE.height]);
150      y = PAGE.height - PAGE.margin;
151      y = drawHeader(page, bold, y);
152    }
153    let x = PAGE.margin;
154    for (const col of PDF_COLUMNS) {
155      // The repo column doubles as the scope column: org-level access facts
156      // carry a subject (member/team) instead of a repository.
157      const raw = col.key === "repo" ? String(row.repo ?? row.subject ?? "") : String(row[col.key] ?? "");
158      const text = truncate(raw, font, FONT_SIZE, col.width - 4);
159      const color = col.key === "posture" ? POSTURE_COLOR[row.posture] ?? rgb(0, 0, 0) : rgb(0.1, 0.1, 0.1);
160      page.drawText(text, { x, y, size: FONT_SIZE, font, color });
161      x += col.width;
162    }
163    y -= ROW_HEIGHT;
164  }
165
166  return doc.save();
167}
168
169function drawHeader(page: PDFPage, bold: PDFFont, y: number): number {
170  let x = PAGE.margin;
171  for (const col of PDF_COLUMNS) {
172    page.drawText(col.header, { x, y, size: FONT_SIZE, font: bold });
173    x += col.width;
174  }
175  const lineY = y - 4;
176  page.drawLine({
177    start: { x: PAGE.margin, y: lineY },
178    end: { x: PAGE.width - PAGE.margin, y: lineY },
179    thickness: 0.5,
180    color: rgb(0.6, 0.6, 0.6),
181  });
182  return y - ROW_HEIGHT;
183}
184
185function truncate(text: string, font: PDFFont, size: number, maxWidth: number): string {
186  if (font.widthOfTextAtSize(text, size) <= maxWidth) return text;
187  let truncated = text;
188  while (truncated.length > 1 && font.widthOfTextAtSize(truncated + "…", size) > maxWidth) {
189    truncated = truncated.slice(0, -1);
190  }
191  return truncated + "…";
192}