Compliance Automation and Tooling Questions

Direct answer

Model the chain as linked, versioned records (requirement to control to implementation to test to evidence) with effective dates on every record, so any report is a query, not a document assembled by hand. "What changed since last audit" is a query on version and effective-date fields between two dates.

The model

Worked example (code run, output pasted)

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE requirement (req_id TEXT, framework TEXT, version TEXT, clause TEXT, text TEXT, PRIMARY KEY (req_id, version));
CREATE TABLE control (control_id TEXT, version INT, statement TEXT, owner TEXT, effective_from TEXT, PRIMARY KEY (control_id, version));
CREATE TABLE mapping (req_id TEXT, req_version TEXT, control_id TEXT, strength TEXT);
CREATE TABLE implementation (control_id TEXT, system TEXT, check_name TEXT);
CREATE TABLE evidence (control_id TEXT, control_version INT, system TEXT, collected_at TEXT, result TEXT, artifact_sha TEXT);
""")
db.executemany("INSERT INTO requirement VALUES (?,?,?,?,?)", [
 ("NIST-IA-2.1", "NIST SP 800-53", "r5", "IA-2(1)", "MFA for privileged accounts")])
db.executemany("INSERT INTO control VALUES (?,?,?,?,?)", [
 ("CTL-MFA-ADMIN", 1, "Admins use MFA", "iam-team", "2024-01-01"),
 ("CTL-MFA-ADMIN", 2, "Admins use phishing-resistant MFA", "iam-team", "2025-03-01")])
db.execute("INSERT INTO mapping VALUES ('NIST-IA-2.1','r5','CTL-MFA-ADMIN','full')")
db.execute("INSERT INTO implementation VALUES ('CTL-MFA-ADMIN','idp-prod','admin_mfa_enforced')")
db.executemany("INSERT INTO evidence VALUES (?,?,?,?,?,?)", [
 ("CTL-MFA-ADMIN",2,"idp-prod","2025-06-30","pass","a1f09c"),
 ("CTL-MFA-ADMIN",2,"idp-prod","2025-07-01","pass","b72e44")])

print("TRACE: requirement -> control -> implementation -> latest evidence")
for r in db.execute("""
 SELECT m.req_id, m.control_id, i.system, i.check_name, e.collected_at, e.result, e.artifact_sha
 FROM mapping m JOIN implementation i USING (control_id)
 JOIN evidence e ON e.control_id=m.control_id AND e.system=i.system
 WHERE e.collected_at = (SELECT MAX(collected_at) FROM evidence WHERE control_id=m.control_id)"""):
    print(r)

LAST_AUDIT = "2025-01-01"
print("CHANGED since last audit (", LAST_AUDIT, ")")
for r in db.execute("SELECT control_id, version, statement, effective_from FROM control WHERE effective_from > ?", (LAST_AUDIT,)):
    print(r)

Output:

TRACE: requirement -> control -> implementation -> latest evidence
('NIST-IA-2.1', 'CTL-MFA-ADMIN', 'idp-prod', 'admin_mfa_enforced', '2025-07-01', 'pass', 'b72e44')
CHANGED since last audit ( 2025-01-01 )
('CTL-MFA-ADMIN', 2, 'Admins use phishing-resistant MFA', '2025-03-01')

One query returns the whole chain for NIST SP 800-53 IA-2(1), and a second returns the control that tightened in March. A real "changed" report would also diff mappings, implementations and test definitions, not only the control statement.

Reading the code in plain language. The trace query starts from the mapping row and attaches the implementation with JOIN ... USING (control_id), which means "pair rows that share the same control_id value". It then attaches evidence rows for the same control and system (each evidence row also records which control version it tested, here version 2, so a report can separate evidence gathered against the old wording from proof of the new wording; this short trace query does not filter on that column, and a production version would add a condition that the evidence's control_version equals the control's latest version). The final WHERE keeps only the newest evidence row: for each mapping row, the inner SELECT MAX(collected_at) looks up the latest collection date for that control, and only evidence collected on that date survives (a subquery that depends on the outer row like this is called a correlated subquery). That is why the 2025-06-30 row is dropped and the 2025-07-01 row printed. In the schema, PRIMARY KEY (control_id, version) allows many versions of one control but never the same version twice, so rewriting a control means inserting version 3, not editing version 2. The change query simply lists control versions whose effective date is later than the last audit date; ISO-formatted dates sort correctly as text.

Generating the reports

• Traceability report: for each requirement in scope, list its controls, systems, latest result and evidence link. Coverage is computed: a requirement counts as covered if it has an approved mapping to an active control whose latest test is within its freshness window (the maximum age, for example 7 days, at which a result still counts as current).
• Change report: all rows with a version or effective date after the last audit date, grouped by entity, with reviewer and reason.
• Gaps first: list requirements with no mapping, controls with no test, and tests with stale or missing evidence. These matter more to an auditor than the covered rows.

What breaks when something is revised

A concrete renumbering. In the ISO/IEC 27001 move from the 2013 to the 2022 edition, the control "A.9.4.2 Secure log-on procedures" became "A.8.5 Secure authentication". With versioned requirements, the 2022 row is added, the old mapping to CTL-MFA-ADMIN is carried forward as proposed, and a reviewer approves it. A withdrawn clause that nothing replaces leaves a control with no requirement pointing at it, which is an orphan control in the table above, and it appears in the gaps list for a human decision.

Pitfalls

• Editing rows in place destroys the "what changed" answer; append and version instead.
• A green coverage number built on stale evidence is the same silent-failure problem as an unchecked dashboard, so show freshness next to coverage.

Direct answer

Build the obligation as data and the checks as code: counsel and the privacy or compliance team translate each local legal requirement into a plain-language control, engineers turn the objectively testable ones into pipeline checks tagged by market (consumer-protection law covers things like fair advertising, pricing display and refunds), and the judgement-heavy ones become a documented human approval. Adding a market then means adding a configuration file, not designing a new pipeline.

Step 1: A single obligation register

For each requirement keep: jurisdiction, source citation and effective date, the internal control statement in plain English, an owner, how it is tested (automated, manual or both) and the status. Lawyers interpret the law; engineers do not. You are translating counsel's interpretation into tests, and recording who decided.

Step 2: Sort every requirement into three tiers

Step 3: Structure for scale

• A core rule library (one definition per rule) plus a per-market manifest listing which rules apply and with which parameters (allowed regions, retention limits, notice locale).
• The pipeline reads the manifest of every market a release targets and runs only those rules. New market means a new manifest, plus new rules only where law differs.
• Every rule has fixture tests (small saved examples, one that must pass and one that must fail, which the rule is run against whenever it changes), so changing a rule is safe.
• A central platform team owns the engine; product teams self-serve through manifests. A shared template means 50 teams do not each build their own checks.

What the register and manifest look like

One register row, with illustrative values:

One market manifest, a small configuration file the pipeline reads (values illustrative):

market: DE
rules:
  - id: consent-before-tracking
    params: { max_tracker_requests_before_consent: 0, banner_locale: de }
  - id: data-region
    params: { allowed_regions: [eu-central-1, eu-west-1] }
  - id: privacy-notice-version
    params: { locale: de, min_version: 4 }
  - id: dpia-linked
    params: { required_for: high_risk_features }

The rules themselves live once in the core library; the manifest only chooses them and sets their parameters. A fixture test for consent-before-tracking would hold two saved page loads: one that fires 0 tracker requests before consent (must pass) and one that fires 2 (must fail). Adding another market means copying a manifest and changing its values, and writing a new rule only where that market's law genuinely differs.

Step 4: Change in law

Counsel or a regulatory-monitoring service raises changes; each becomes a register entry with an effective date and an owner. Updated rules are versioned and switched on at the effective date, with a warning period where the check reports but does not block. Release gates evaluate rules in force on the release date.

Where automation stops

Interpretation, balancing tests (weighing the company's interest in using data against the individual's rights) and legal risk acceptance stay with counsel. The pipeline proves that required artifacts and technical conditions exist; it does not prove the law is satisfied. Keep human approval narrow: ask only for the tier-three items, so the gate is not a queue for everything.

Worked example

A team launches in a new EU market: the manifest enables four checks (consent-before-tracking, data-region, privacy-notice-version and dpia-linked). The first three pass. The dpia-linked check passes on presence, because a DPIA exists and is linked, but the feature uses a new data category the DPIA did not cover, so the register flags legal approval. The release proceeds when legal signs. Nothing was hand-built for this market.

Pitfalls

• Treating a green pipeline as legal clearance.
• Rules that only counsel can read; keep the plain-language statement next to each test.
• Stale rules after a law changes, which is why each rule carries a review date.

Direct answer

I would translate each wiki sentence into a rule with four parts (the next section shows one sentence converted): the data it applies to (via classification tags, labels such as classification=restricted attached to a dataset), the attribute to measure, the pass condition, and the point where it is checked. I would then split enforcement by what each platform can actually prevent, assign each policy an owner who is accountable for it, and report adherence with the denominator visible, so improvement means more compliant data, not less data in scope.

From wiki page to machine-checkable policy

The cells show one example per cloud of how each requirement can be met; the same rule shape maps onto whichever feature a platform offers, so nothing here is a list to memorize.

One wiki sentence, converted

Take the wiki line "Customer personal data is deleted after 7 years." It becomes a rule with the four parts filled in:

Applies to:   datasets tagged data_class=personal
Measure:      the expiry or lifecycle-delete setting on the dataset, in days
Pass if:      the setting exists and is at most 2555 days (7 years x 365 days; the
              legal team states in the policy whether leap days are counted)
Checked:      at plan time for resources Terraform creates, and by a weekly scan
              of live inventory for everything else

Every sentence on the wiki gets the same four slots. A sentence that cannot fill them, such as "handle data carefully", is a sign the requirement needs rewording with the legal owner before it can be automated. A bucket tagged personal with no expiry setting, or an expiry of 4,000 days, fails; an untagged bucket is reported as a classification gap rather than silently passing.

A rule whose input is a classification tag needs classification to exist first. Without it the policy has nothing to evaluate, so an untagged dataset is itself a finding.

Where each rule is evaluated

• Plan time (before deploy, when the infrastructure code is previewed and nothing exists yet): best for settings Terraform manages, because it stops a mistake before it exists.
• Runtime scan: needed for anything created outside Terraform and for drift. Drift means a live setting differing from what the code says, for example a hand edit in a console. Use the clouds' own inventory services (AWS Config and Google Cloud Asset Inventory, each of which records the resources in an account or project and their settings) as the data source.
• Data plane: masking and access are enforced by the platform at query time. Scanning confirms the policy is attached, but only a test query run as a low-privilege principal confirms the output is actually masked.
• Effectiveness check for retention: a lifecycle rule existing is not proof data was deleted. Periodically count objects or rows older than the retention period, and note that backups, replicas and derived tables are separate copies to cover.

Ownership

• Legal and privacy own the requirement text and the retention schedule.
• Data governance owns classification.
• Platform or security engineering owns the policy code and checks, in a repository where requirement-changing pull requests need legal approval.
• The data owner for each dataset is accountable for remediation and requests any exception.

Showing leadership that it is improving (illustrative numbers)

Suppose 400 datasets are in scope. In the first month 320 are classified (80%) and 256 of those are compliant. Later, 360 are classified (90%) and 306 are compliant.

Showing only the second-last column would flatter a team that classified fewer datasets. Report coverage and compliance together, with the trend, exception count and age, and time to remediate.

Caveat

A check proves a setting, not that the rule is legally sufficient. Whether a retention period or a masking approach satisfies a regulation is a decision for legal counsel, and the policy encodes their decision.

Direct answer

Compute the score as a deterministic, versioned function of check results, vulnerability age and freshness, and store every input so any score can be recomputed. Three rules keep it honest: a stale result counts as unknown, not pass; a failing critical control caps the score; and the data comes from collectors (central jobs or agents, run by a team other than the service owner, that gather check results from the systems) the service owner does not control. Automation opens tickets by threshold. People decide exceptions, scope and disputes.

Inputs and normalization

• Control-check results: pass, fail or unknown per control per service, with the run time.
• Vulnerability feeds: findings mapped to a service through an ownership catalog (a list that says which team owns which service), weighted by severity and age against the remediation deadline.
• Telemetry: for example whether logging is actually flowing. Convert to a pass or fail check first, so the score only consumes one kind of input. In the code below, logging_enabled is that conversion.

Scoring logic and what it does

from datetime import date

TODAY = date(2026, 10, 4)
MAX_AGE_DAYS = 7          # a check result older than this counts as UNKNOWN, not pass

# (control id, weight, is_critical)
controls = [("mfa_enforced", 30, True), ("encryption_at_rest", 25, True),
            ("patch_sla_met", 25, False), ("logging_enabled", 20, False)]

# service -> control -> (result, date of last run)
results = {
    "checkout": {"mfa_enforced": ("pass", date(2026, 10, 3)), "encryption_at_rest": ("pass", date(2026, 10, 3)),
                 "patch_sla_met": ("fail", date(2026, 10, 3)), "logging_enabled": ("pass", date(2026, 10, 2))},
    "search":   {"mfa_enforced": ("pass", date(2026, 10, 3)), "encryption_at_rest": ("fail", date(2026, 10, 3)),
                 "patch_sla_met": ("pass", date(2026, 10, 3)), "logging_enabled": ("pass", date(2026, 10, 3))},
    "reports":  {"mfa_enforced": ("pass", date(2026, 9, 10)), "encryption_at_rest": ("pass", date(2026, 10, 3)),
                 "patch_sla_met": ("pass", date(2026, 10, 3)), "logging_enabled": ("pass", date(2026, 10, 3))},
}

def score(svc):
    total, earned, why, cap = sum(w for _, w, _ in controls), 0, [], 100
    for cid, w, critical in controls:
        res, ran = results[svc][cid]
        state = res if (TODAY - ran).days <= MAX_AGE_DAYS else "unknown"
        if state == "pass":
            earned += w
        else:
            why.append(f"{cid}={state}(-{w})")
            if critical:
                cap = 59          # a failing or unknown critical control caps the score
    raw = round(100 * earned / total)
    return min(raw, cap), raw, why

for svc in results:
    final, raw, why = score(svc)
    print(f"{svc}: score={final} (raw {raw}) reasons: {', '.join(why) or 'none'}")

checkout: score=75 (raw 75) reasons: patch_sla_met=fail(-25)
search: score=59 (raw 75) reasons: encryption_at_rest=fail(-25)
reports: score=59 (raw 70) reasons: mfa_enforced=unknown(-30)

The weights are illustrative. Checkout scores 75 with one failing non-critical control. Search has the same raw 75 but scores 59 because its failing control is critical (encryption at rest). Reports has a raw 70, and the 59 comes from the MFA check last run on 2026-09-10, 24 days before 2026-10-04, past the 7-day freshness limit, so it counts as unknown and the critical cap applies. Weights and the cap are the policy choices to agree with service owners and the security leadership, and a model change must be backtested (replayed over past data to see which scores the new weights would have produced) before release.

The cap of 59 is deliberately one point below the illustrative ticket line of 60 used in the table below, so a failing or unknown critical control always opens a ticket however well the service scores elsewhere.

Vulnerability age enters the same way, as a converted check. In the code, patch_sla_met is the result of that conversion: it passes only if no open finding is older than its deadline (illustrative deadlines: 15 days for critical, 30 for high, 90 for medium). Checkout has a critical finding opened on 2026-09-10, which is 24 days old on 2026-10-04 against a 15-day deadline, so patch_sla_met fails and costs 25 points, which is the checkout result above.

Explainability for auditors and owners

Store per score: the inputs, each control's contribution, the scoring-model version and the timestamp. The owner sees "you lost 25 points because patch_sla_met failed" instead of an opaque number. An auditor can recompute a score from stored inputs. The score is a prioritization aid: audit evidence is still the control-level results underneath it.

Automate versus human

Stop it being gamed

1. Results come from central collectors, not self-reports.
2. The inventory is the source of truth: anything running but not in the catalog is flagged as unowned, so a service cannot escape by omission.
3. Exceptions expire and visibly reduce the score.
4. A small random sample of passes is independently re-verified each quarter.
5. Do not tie the score directly to individual performance reviews. People optimize whatever is measured (Goodhart's law: a measure that becomes a target stops being a good measure).

Stop it going stale

The freshness rule above, a heartbeat alert from each collector (an alert when its regular "I am alive" signal stops), and an "as of" timestamp on every score shown. A dashboard that looks green because collectors died is the worst failure mode, which is why unknown is scored as not-pass.

Direct answer

I would make the evidence tamper-evident and independently verifiable by signing it at the point of collection, chaining every record to the one before, storing it where it cannot be deleted, and giving the auditor the public key and periodic head hashes directly so they can verify without trusting us. Tamper-evident means alteration cannot happen unnoticed, which is weaker than "cannot happen". I would also tell the auditor plainly what this does not prove.

Threat model first

Who could alter evidence: a collector administrator, a storage administrator, a pipeline bug, or the team that owns the failing control. The design must hold even against our own admins.

Two building blocks

A hash (here SHA-256, a standard hash function) is a short fingerprint computed from data: the same data always gives the same fingerprint, and changing one character gives a completely different one, so a fingerprint shows whether data was changed but not who changed it. A digital signature is made with a private key that only the signer holds and checked with the matching public key, which can be shared freely. A valid signature shows the signed data is unchanged since signing and that the signer held the private key. The design below uses hashes to link records and a signature to anchor the chain. The core of the answer is three things: a hash chain, a signature by someone other than the evidence administrators, and a copy of the latest hash held outside our control. The rest (compliance versus governance mode, a timestamp authority, break-glass access) sets how strongly or how long those three hold.

The pipeline

1. Collect with a read-only service identity, and record source, time and tool version.
2. Hash and chain. Each record's hash covers its own content plus the previous record's hash, so changing one record breaks every later link.
3. Sign the chain head (the newest hash, which depends on every record before it) with a private key held in a key management service or HSM (hardware security module, a tamper-resistant key device), controlled by a different team than the evidence administrators.
4. Store immutably. S3 Object Lock in compliance mode (a write-once-read-many setting) means a protected object version cannot be overwritten or deleted by any user, including the account root user, until its retention date. Governance mode can be bypassed by someone holding a special permission, so it is weaker.
5. Anchor externally. Send head hashes at period close to the auditor, or to an RFC 3161 time-stamping authority (a trusted third party that signs a hash with a time, proving the data existed before that time).
6. Give the auditor a verifier and the public key. They run it themselves.

CloudTrail (the AWS activity log) has a built-in example of the same idea, log file integrity validation: SHA-256 hashes for each log file, an hourly digest file referencing them, signed with SHA-256 with RSA (a widely used signature scheme), with each digest containing the previous digest's signature. In plain terms, CloudTrail fingerprints each log file, signs an hourly list of those fingerprints, and links each list to the one before it, which is the chain-plus-signature pattern above.

Runnable demonstration of the chain and the verifier

import hashlib, json
from cryptography.hazmat.primitives.asymmetric.ed25519 import Ed25519PrivateKey

def sha256(b): return hashlib.sha256(b).hexdigest()

# --- collector side -------------------------------------------------------
signing_key = Ed25519PrivateKey.generate()
public_key = signing_key.public_key()      # given to the auditor out of band

items = [
    ("2025-03-01T02:00:00Z", "iam-users", {"users_without_mfa": 0}),
    ("2025-03-02T02:00:00Z", "iam-users", {"users_without_mfa": 1}),
    ("2025-03-03T02:00:00Z", "iam-users", {"users_without_mfa": 0}),
]
manifest, prev = [], "0" * 64
for ts, check, result in items:
    body = json.dumps({"ts": ts, "check": check, "result": result}, sort_keys=True)
    entry_hash = sha256((prev + body).encode())   # each entry commits to the one before
    manifest.append({"body": body, "prev": prev, "hash": entry_hash})
    prev = entry_hash
signature = signing_key.sign(prev.encode())      # signs the head of the chain

# --- auditor side: needs only manifest, signature, public key ------------
def verify(manifest, signature, public_key):
    prev = "0" * 64
    for i, e in enumerate(manifest):
        if e["prev"] != prev or sha256((prev + e["body"]).encode()) != e["hash"]:
            return f"FAIL: chain broken at entry {i}"
        prev = e["hash"]
    try:
        public_key.verify(signature, prev.encode())
    except Exception:
        return "FAIL: head signature invalid"
    return f"OK: {len(manifest)} entries, head {prev[:12]}"

print(verify(manifest, signature, public_key))

# tamper with the middle record (the failing MFA check) and re-verify
tampered = json.loads(json.dumps(manifest))
tampered[1]["body"] = tampered[1]["body"].replace('"users_without_mfa": 1', '"users_without_mfa": 0')
print(verify(tampered, signature, public_key))

# attacker also rebuilds every hash after the edit; only the signature stops this
prev = "0" * 64
for e in tampered:
    e["prev"] = prev
    e["hash"] = sha256((prev + e["body"]).encode())
    prev = e["hash"]
print(verify(tampered, signature, public_key))

# deleting the newest entry leaves a valid chain, but the signed head no longer matches
print(verify(manifest[:-1], signature, public_key))

Real output:

OK: 3 entries, head 673b90aca7e9
FAIL: chain broken at entry 1
FAIL: head signature invalid
FAIL: head signature invalid

Plain walkthrough of the demo. Each entry stores the hash of the entry before it, so the three entries form a chain. When the middle record is edited, its recomputed hash no longer equals the stored one, so verification fails at entry 1. To hide that, an attacker must recompute entry 1's hash, which changes what entry 2 must store as its previous hash, which changes entry 2's hash, and so on, so every later hash changes. That is what the third case does. The chain is then consistent again, but its head is a different value from the one the signature was made over, and the attacker does not hold the private key to produce a new signature, so the signature check fails.

The first check verifies. The fourth check, deleting the newest entry, leaves a consistent chain but a different head from the one the signature covers, so it also fails. The demo needs the cryptography package. Ed25519, used in it, is a modern digital-signature algorithm; any standard signature scheme would show the same behavior.

Other sources, retention and access

• Endpoints, containers and network devices: have a local agent or the ingesting collector sign the record with a per-source identity, and produce a signed attestation report (a signed statement of what was collected from which source).
• Retention and disposition: retention is set per evidence class by the longest applicable requirement, with counsel confirming. When evidence is deleted after retention, a deletion record is written into the chain so the absence is explained.
• Access control: auditors get a read-only role, writers cannot delete, admins are separate, and every read is logged.
• Break-glass access: reading is allowed through a named role that opens an incident, expires, and is logged. It does not allow deletion, since compliance-mode retention blocks that regardless.

What I still could not prove

1. That the source was true at collection. A compromised source or collector is faithfully signed.
2. That nothing was left unrecorded. The chain shows that nothing recorded was changed, not that every check that should have run was recorded. A schedule of expected runs covers this.
3. That the check logic is adequate for the control objective.
4. The exact time. A timestamp authority proves the hash existed before that time, not that the collector clock was accurate.
5. Key honesty. Someone holding the signing key who also controls storage could rebuild and re-sign everything, which is why key custody is separate and head hashes go outside our control early. The demo's signature check protects only while the key is held by someone other than the attacker.