Daily balance reconciliation against source-of-truth ledger

Without automated daily reconciliation, a discrepancy between the account balance table and the transaction ledger can persist indefinitely — and under SOX §404 that is a material weakness in internal controls over financial reporting. NIST 800-53 AU-2 requires that the organization identify events significant enough to warrant auditing; a mismatch between the authoritative ledger and running balances is exactly that event. FINRA Rule 4370 (Business Continuity Planning) requires that firms be able to reconstruct financial records. An ad-hoc or weekly reconciliation job means errors introduced by bugs, failed transactions, or double-credits can compound for days before detection, making remediation far more expensive and regulatorily exposed. A $0.01 tolerance threshold is the industry standard — anything larger conceals rounding errors that can be systematically exploited.

Add a cron job that runs every day at a fixed UTC time in src/jobs/reconciliation.ts:

import cron from 'node-cron';

async function reconcileBalances() {
  const accounts = await db('accounts').select('id', 'user_id', 'balance');
  for (const acct of accounts) {
    const { ledger_total } = await db('transaction_logs')
      .where({ user_id: acct.user_id })
      .sum('signed_amount as ledger_total')
      .first();
    const discrepancy = Math.abs(acct.balance - (ledger_total ?? 0));
    if (discrepancy > 0.01) {
      await db('reconciliation_issues').insert({
        account_id: acct.id, system_balance: acct.balance,
        ledger_balance: ledger_total, discrepancy,
        discovered_at: new Date()
      });
    }
  }
}

// Fires at 02:00 UTC every day
cron.schedule('0 2 * * *', reconcileBalances);

Persist each run's status to reconciliation_runs so the quarterly compliance assessment (see finserv-audit-trail.tamper-evidence.compliance-gap-assessment) can count failures.

• ID: finserv-audit-trail.balance-reconciliation.daily-reconciliation

• Severity: high

• What to look for: Count all scheduled jobs (cron, scheduled tasks, cloud functions) and identify which ones perform reconciliation. Quote the actual cron schedule expression found (e.g., 0 2 * * *). Verify the reconciliation logic compares account balances against a ledger of transactions. A weekly or monthly schedule does not count as pass — must run at least once every 24 hours.

• Pass criteria: At least 1 automated daily reconciliation process exists with a cron schedule that fires at least every 24 hours. Reconciliation logic sums or validates balances against transaction logs and identifies discrepancies with a tolerance threshold of no more than $0.01. Report the count even on pass (e.g., "1 reconciliation job found, runs at 02:00 UTC daily").

• Fail criteria: No automated daily reconciliation job found, or reconciliation runs less frequently than daily, or reconciliation is manual (ad-hoc) rather than automated.

• Skip (N/A) when: Never — daily reconciliation is a cornerstone of financial integrity.

• Detail on fail: "No automated daily reconciliation found — 0 cron jobs reference reconciliation logic." or "Reconciliation job exists but cron is '0 2 * * 0' (weekly, not daily).".

• Cross-reference: Check finserv-audit-trail.balance-reconciliation.reconciliation-alerts for what happens when mismatches are found, and finserv-audit-trail.balance-reconciliation.balance-snapshots for snapshot data used in reconciliation.

• Remediation: Implement a daily reconciliation job (in src/jobs/reconciliation.ts or src/cron/):

  // In your cron job handler (runs daily at 02:00 UTC)
  async function reconcileBalances() {
    const accounts = await db.accounts.findAll();
  
    for (const account of accounts) {
      // Calculate balance from transaction ledger
      const ledgerBalance = await db.transactionLogs
        .aggregate([
          { $match: { userId: account.userId } },
          {
            $group: {
              _id: '$userId',
              total: { $sum: '$amount' } // Simplified: handle debits/credits
            }
          }
        ]);
  
      const calculatedBalance = ledgerBalance[0]?.total || 0;
      const discrepancy = account.balance - calculatedBalance;
  
      if (Math.abs(discrepancy) > 0.01) { // Tolerance for rounding
        await db.reconciliationIssues.create({
          accountId: account.id,
          systemBalance: account.balance,
          ledgerBalance: calculatedBalance,
          discrepancy,
          discoveredAt: new Date()
        });
      }
    }
  }
  
  // Schedule daily at 02:00 UTC
  cron.schedule('0 2 * * *', reconcileBalances);