Schema gaps surfaced by SOC 2 evaluators

[ejay-dev]

While shipping the SOC 2 per-control evaluators (PRs #40, #44/#41, #42), five schema-level gaps surfaced. Each forced an evaluator to either reduce its check or fall back to a heuristic. Listed roughly in order of how much they hold the evaluators back.

Gaps

• org_policies.category column missing → blocks CC2.1 per-category check. AICPA wants "≥1 active policy per category"; without category data CC2.1 collapses to a per-policy acknowledgement-rate check.
• org_audit_logs.actor_id column missing → forces CC7.4 to use actor_email string heuristics (system@, noreply@) for "is this a named actor?" Adding actor_id uuid REFERENCES auth.users(id) would make attribution unambiguous.
• security_events.resolved_at missing → forces CC7.3 to join security_alerts for resolution timestamps. Either denormalise resolved_at onto security_events, or document the security_events ↔ security_alerts join as the canonical resolution path.
• org_risk_register table naming → CC3.1 reads org_risks instead (the actual table name). Either rename in the spec docs to match reality, or add a register_id grouping column on org_risks if multiple registers per org are intended.
• No DB-level chain checkpoint table → verifyChainIntegrity (CC7.2) runs in-process over the entire audit_log chain. Won't scale on large tables; consider a periodic chain-checkpoint table that stores known-good (sequence_number, entry_hash) pairs the evaluator can resume from.

Why this matters

Most of these are small migrations that would tighten an existing evaluator from "heuristic" to "deterministic". A natural pairing for whoever lands the Phase 3 dashboard wiring (registerAllEvaluators() into the posture-fetch path) — adding org_policies.category while touching CC2.1's posture path is a 5-minute incremental win.

Source

Phase 2 evaluator implementation:

• feat(compliance): evaluators for SOC 2 access controls #40 (Batch A — access)
• feat(compliance): evaluators for SOC 2 logging & monitoring #41/feat(compliance): evaluators for SOC 2 logging & monitoring (Batch B) #44 (Batch B — logging & monitoring)
• feat(compliance): evaluators for SOC 2 change-mgmt + supporting #42 (Batch C — change management + supporting)