Audit report artifact formats

An RBAC drift pipeline is only as useful to an auditor as the file it hands them. This guide defines the concrete JSON and CSV artifact shapes a drift pipeline emits as evidence — the canonical record schema, the fields every framework demands, and the projection logic that turns one detection event into a SOC 2, PCI DSS, or HIPAA-ready report without re-querying the database.

The failure scenario is familiar to anyone who has sat through an evidence request: the pipeline detects drift correctly, but exports a wall of unstructured log lines that no assessor can map to a control. The auditor asks “show me that analytics_ro never held write access to the cardholder schema,” and the team spends a week hand-assembling a spreadsheet. The fix is to treat the artifact format as a first-class contract. Every scored deviation from the rule-based drift scoring engine is written once as a canonical evidence record, and each framework report is a deterministic projection of that record — never a re-derivation. This guide is the format layer of the broader Compliance Evidence & Audit Reporting topic area.

Prerequisites and scope

This stage assumes the drift pipeline has already produced scored, deduplicated deviations. It reads those records and emits evidence files; it does not query the target database directly, so it inherits none of the catalog-connection risk.

• Python runtime: 3.11 or newer with pydantic v2 for schema validation and serialization. CSV projection uses only the standard-library csv module; JSON uses json plus pydantic’s model_dump.
• Upstream inputs: a normalized deviation stream carrying at minimum principal, object scope, privilege, environment, and a compliance control identifier. The object-to-control mapping is sourced from Privilege Scope Mapping, which tags each database object with its data-sensitivity tier and the frameworks that govern it.
• Catalog reads (upstream only): the deviation producer reads information_schema.role_table_grants, pg_roles, and pg_auth_members on PostgreSQL, or mysql.role_edges and information_schema.schema_privileges on MySQL 8. The artifact layer never touches these.
• An evidence sink: append-only object storage or a table with an immutable-writes policy. The evidence hash on each record is what lets Evidence Integrity and Signing chain and sign the exported artifacts.

The scope is deliberately narrow: canonical record in, framework report out. It does not decide what is a violation (that is scoring) and it does not sign the output (that is the integrity layer) — it defines the shape that both depend on.

Core implementation walkthrough

Step 1 — Define the canonical evidence record

Every framework report descends from one record type. The seven required fields are the intersection of what SOC 2, PCI DSS, and HIPAA assessors independently ask for: who (principal), over what (object), holding which right (privilege), governed by which control (control), judged how (decision), when (timestamp), and provably unaltered (evidence hash).

from datetime import datetime, timezone
from hashlib import sha256
from typing import Literal
from pydantic import BaseModel, Field, computed_field

Decision = Literal["compliant", "violation", "exception_approved", "remediated"]

class EvidenceRecord(BaseModel):
    """One canonical, framework-agnostic evidence row per scored deviation."""
    principal: str                      # role/user, canonicalized
    object: str                         # schema.table or schema.*
    privilege: str                      # SELECT, INSERT, ALL PRIVILEGES, ...
    control: str                        # governing control id, e.g. SOC2-CC6.1
    decision: Decision
    observed_at: datetime               # tz-aware UTC
    sensitivity: Literal["pii", "ephi", "cardholder", "financial", "internal", "public"]

    @computed_field  # type: ignore[misc]
    @property
    def evidence_hash(self) -> str:
        """Content address over the identifying fields — excludes nothing mutable."""
        payload = f"{self.principal}|{self.object}|{self.privilege}|{self.control}|{self.decision}|{self.observed_at.isoformat()}"
        return sha256(payload.encode("utf-8")).hexdigest()

Verify that the hash is stable for identical content and tz-aware:

rec = EvidenceRecord(
    principal="analytics_ro", object="cust.cards", privilege="SELECT",
    control="PCI-7.2.1", decision="violation",
    observed_at=datetime(2026, 7, 18, 14, 22, 7, tzinfo=timezone.utc),
    sensitivity="cardholder",
)
assert rec.evidence_hash == EvidenceRecord(**rec.model_dump(exclude={"evidence_hash"})).evidence_hash
print(rec.evidence_hash[:16])   # -> deterministic 16-hex prefix

Step 2 — Project the canonical record into a framework report

A projection is a pure function EvidenceRecord -> dict. It selects a subset of fields, renames them to the vocabulary the assessor expects, and adds framework-scoped constants (the review period, the attester). Because it is pure, re-running it over the same record set is byte-identical — the idempotency property auditors rely on.

def project_soc2(rec: EvidenceRecord, *, reviewer: str, period: str) -> dict:
    """SOC 2 CC6.1/CC6.3 periodic access-review row."""
    return {
        "review_period": period,
        "reviewer": reviewer,
        "principal": rec.principal,
        "reviewed_access": f"{rec.privilege} ON {rec.object}",
        "trust_criteria": rec.control,
        "disposition": "retain" if rec.decision == "compliant" else "revoke",
        "evidence_hash": rec.evidence_hash,
    }

def project_pci(rec: EvidenceRecord, *, attester: str) -> dict:
    """PCI DSS Requirement 7 least-privilege attestation row (CDE objects only)."""
    return {
        "cde_object": rec.object,
        "principal": rec.principal,
        "granted_privilege": rec.privilege,
        "business_need_documented": rec.decision in {"compliant", "exception_approved"},
        "least_privilege_ok": rec.decision == "compliant",
        "attester": attester,
        "evidence_hash": rec.evidence_hash,
    }

Verify that both projections carry the same evidence hash, so an auditor can tie a PCI row back to its SOC 2 sibling and to the canonical source:

soc = project_soc2(rec, reviewer="j.okoro", period="2026-Q2")
pci = project_pci(rec, attester="j.okoro")
assert soc["evidence_hash"] == pci["evidence_hash"] == rec.evidence_hash

Step 3 — Serialize to the two on-disk formats

JSON is the default for nested, machine-ingested evidence; CSV is what most QSAs and access-review committees actually open. Emit both from the same projected dicts, with deterministic key and column ordering so a diff between two exports is meaningful.

import csv, io, json

def to_json(rows: list[dict]) -> str:
    """Stable-key JSON array for machine ingestion."""
    return json.dumps(rows, indent=2, sort_keys=True, ensure_ascii=False)

def to_csv(rows: list[dict], columns: list[str]) -> str:
    """Fixed-column CSV; columns are declared, never inferred from dict order."""
    buf = io.StringIO()
    writer = csv.DictWriter(buf, fieldnames=columns, extrasaction="ignore")
    writer.writeheader()
    writer.writerows(rows)
    return buf.getvalue()

PCI_COLUMNS = ["cde_object", "principal", "granted_privilege",
               "business_need_documented", "least_privilege_ok",
               "attester", "evidence_hash"]

Declaring PCI_COLUMNS explicitly rather than inferring from dict.keys() is what keeps a CSV schema stable when the projection later gains a field — new keys are dropped by extrasaction="ignore" until the column list is deliberately versioned.

Idempotency and safety contract

The artifact layer is a pure transform, and its trustworthiness rests on that purity:

• No database side effects. Projection and serialization read canonical records and write files. They never issue DDL and never re-open a catalog connection, so producing evidence a thousand times changes nothing in the monitored database.
• Content-addressed and reproducible. evidence_hash is derived only from identifying fields, never from the export timestamp or the reviewer name. Two exports of the same deviation set produce identical hashes, which is precisely what an assessor recomputes to prove the file was not edited after generation.
• Additive-only field evolution. A framework projection may gain fields across versions, but the canonical record’s seven required fields are frozen. Version the projection functions, not the source record, so historical evidence remains re-projectable.
• Convergence. An unchanged deviation set yields byte-identical JSON and CSV. The pipeline’s fixed point is a clean run that reproduces last period’s evidence exactly, hash for hash.

Compliance alignment

A single canonical record set satisfies the evidence clause of three frameworks at once, because each control asks for a different view of the same access facts:

Each row of that table is a concrete deliverable authored in this topic area: SOC 2 access review evidence in JSON builds the CC6.1/CC6.3 document with its full pydantic model; PCI DSS Requirement 7 least-privilege attestation report specifies the exact CSV columns a QSA expects on CDE-tagged objects; and HIPAA §164.312 access control evidence export proves unique user identification and access control over ePHI. Once emitted, artifacts flow to Evidence Integrity and Signing for tamper-evident chaining.

Troubleshooting matrix

The artifact layer fails quietly — it keeps producing well-formed files that happen to be wrong. Each row pairs a signature with a remediation.

When a projection needs a fact the canonical record does not carry, that is a signal to widen the canonical schema deliberately and re-score — not to bolt a second catalog query onto the artifact layer, which would reintroduce the very drift-vs-evidence skew this format contract exists to prevent.

Related

• SOC 2 access review evidence in JSON — the CC6.1/CC6.3 periodic-review document, pydantic model and example output.
• PCI DSS Requirement 7 least-privilege attestation report — the CSV/JSON columns a QSA expects on CDE objects.
• HIPAA §164.312 access control evidence export — proving unique user identification and access control over ePHI.
• Evidence Integrity and Signing — chaining and signing the artifacts this stage emits.
• Rule-Based Drift Scoring — the engine whose scored deviations become canonical records.
• Privilege Scope Mapping — the object-to-sensitivity contract every projection filters on.

Up: Compliance Evidence & Audit Reporting