Logs include before-and-after balance snapshots

Logging that a withdrawal of $500 occurred is not the same as being able to prove the account held $1,200 beforehand and $700 afterward. Without balance_before and balance_after captured atomically at execution time, your reconciliation process must recompute balances by replaying every prior transaction — a process that breaks the moment any log row is missing, corrupt, or out of order. NIST 800-53 AU-3 requires audit records to contain sufficient information to establish what happened; a transaction log with no balance context fails that standard because it cannot independently confirm whether the stated amount was valid at the moment it was applied. FINRA Rule 4511 and PCI-DSS v4.0 Req-10.2.2 (which enumerates required audit-log fields — user, event type, date/time, success/failure, origin, affected resource) together cover the content-of-audit-records expectation. CWE-1021 (Improper Restriction of Rendered UI Layers) is a secondary risk — UI showing a derived balance can diverge from the ledger if the log is the authoritative source and lacks before/after data.

Extend transaction_logs with two non-nullable decimal columns in db/migrations/ or prisma/schema.prisma:

ALTER TABLE transaction_logs
  ADD COLUMN balance_before DECIMAL(19,4) NOT NULL,
  ADD COLUMN balance_after  DECIMAL(19,4) NOT NULL;

In your service, read the account balance inside the same database transaction before committing the operation:

async function executeWithdrawal(userId: string, amount: number) {
  return db.transaction(async (trx) => {
    const { balance } = await trx('accounts')
      .where({ user_id: userId }).forUpdate().first();
    const newBalance = balance - amount;
    await trx('accounts').where({ user_id: userId }).update({ balance: newBalance });
    await trx('transaction_logs').insert({
      user_id: userId, operation_type: 'withdrawal',
      amount, balance_before: balance, balance_after: newBalance,
      timestamp: new Date().toISOString()
    });
  });
}

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

• Severity: high

• What to look for: Enumerate all transaction log table columns and count how many balance-related fields exist. Look for columns like balance_before, balance_after, or a snapshot JSONB field. Quote the actual column definitions found. Count every transaction-creating code path and verify each one captures both before and after balances at execution time. A single current_balance column does not count as pass — both before and after must be present.

• Pass criteria: Every transaction log entry includes at least 2 balance columns (before and after). Count all transaction-creating code paths — at least 90% must capture both balance_before and balance_after at the time of the transaction, not computed retroactively. Report the count even on pass (e.g., "5 of 5 code paths capture before/after snapshots").

• Fail criteria: Transaction logs lack balance snapshots, or fewer than 90% of code paths capture both values, or snapshots are computed retroactively (after the transaction), or only current balance is recorded (not before/after pair).

• Skip (N/A) when: Never — balance snapshots are critical for reconciliation.

• Detail on fail: Specify what balance information is logged, if any. Example: "Transaction logs record only the transaction amount — 0 of 2 balance snapshot columns found. Before/after balance snapshots are missing." or "Logs show current_balance but no balance_before field — 1 of 2 required columns present.".

• Cross-reference: Check finserv-audit-trail.balance-reconciliation.daily-reconciliation for how these snapshots feed into reconciliation logic.

• Remediation: Extend the transaction log to capture balance snapshots (in db/schema/ or prisma/schema.prisma):

  ALTER TABLE transaction_logs ADD COLUMN balance_before DECIMAL(19, 2) NOT NULL;
  ALTER TABLE transaction_logs ADD COLUMN balance_after DECIMAL(19, 2) NOT NULL;
  

  When logging a transaction, fetch the current balance before executing the operation:

  async function executeWithdrawal(userId, amount) {
    const account = await db.accounts.findOne({ userId });
    const balanceBefore = account.balance;
  
    // Execute withdrawal
    const newBalance = balanceBefore - amount;
    await db.accounts.update({ userId }, { balance: newBalance });
  
    // Log with snapshots
    await db.transactionLogs.create({
      userId,
      operationType: 'withdrawal',
      amount,
      balanceBefore,
      balanceAfter: newBalance,
      timestamp: new Date().toISOString()
    });
  }