Compliance gap assessment quarterly

A quarterly compliance gap assessment is the mechanism by which a team discovers that a retention policy was accidentally shortened, a new developer removed a role restriction, or the daily reconciliation job has been silently failing for three weeks. SOX §404 requires not only that controls exist but that management evaluates their effectiveness on an ongoing basis — a quarterly review operationalizes that requirement at the team level. NIST 800-53 AU-6 requires organizations to review and analyze audit records to find indications of inappropriate activity and report findings to designated officials at a defined frequency. FINRA Rule 3110 (Supervision) requires that firms establish and maintain supervisory procedures reasonably designed to achieve compliance — a quarterly assessment is a direct implementation of that supervision requirement.

Implement a quarterly assessment script in src/jobs/compliance-assessment.ts that checks the three most critical controls and persists results to compliance_assessments:

async function runQuarterlyAssessment() {
  const checks = [];

  // 1. Retention: oldest log must be ≥7 years old (or policy must state ≥2,555 days)
  const oldest = await db('transaction_logs').orderBy('timestamp').first();
  const retentionDays = oldest
    ? (Date.now() - new Date(oldest.timestamp).getTime()) / 86_400_000
    : 0;
  checks.push({ name: 'retention_7yr', passed: retentionDays >= 2555,
    detail: `${Math.floor(retentionDays)} days retained` });

  // 2. Access: compliance_role must be the only role with SELECT
  const openRoles = await db.raw(
    `SELECT grantee FROM information_schema.role_table_grants
     WHERE table_name='transaction_logs' AND privilege_type='SELECT'
     AND grantee NOT IN ('compliance_role','dba_role')`);
  checks.push({ name: 'access_restricted', passed: openRoles.rows.length === 0,
    detail: `${openRoles.rows.length} non-compliance roles with SELECT` });

  // 3. Reconciliation: zero failed runs in last 90 days
  const failures = await db('reconciliation_runs')
    .where('status', 'failed')
    .where('run_at', '>=', new Date(Date.now() - 90 * 86_400_000))
    .count();
  checks.push({ name: 'daily_reconciliation', passed: Number(failures) === 0,
    detail: `${failures} failed reconciliation runs in last 90 days` });

  await db('compliance_assessments').insert({ quarter: getCurrentQuarter(), checks });
  if (checks.some(c => !c.passed)) await alertCompliance('Compliance gaps found', checks);
}

cron.schedule('0 9 1 1,4,7,10 *', runQuarterlyAssessment);