Financial Audit Trail

Financial Audit Trail

Overview

A financial audit trail records every change to financial data — orders, payments, refunds, manual price adjustments, and invoice approvals — with who made the change, when, from what IP, and what the record looked like before and after. This infrastructure is required for PCI-DSS logging compliance, SOX ITGC evidence, GDPR erasure audit trails, and financial statement audits. Every major e-commerce platform generates some audit history; the gap is usually in completeness, manual change attribution, and export format for auditors.

When to Use This Skill

• When your finance team cannot reconstruct who changed an order total, applied a manual discount, or voided an invoice
• When preparing for an external audit and needing to produce a complete, searchable transaction history for a specific time period
• When building SOX-compliant financial systems that require evidence of control operation
• When implementing PCI-DSS logging requirements for access to cardholder data environments
• When GDPR erasure requests require proof that a customer's financial data was actually anonymized
• When investigating discrepancies between your e-commerce revenue and the payment processor's settlement report

Core Instructions

Step 1: Determine the merchant's platform and what is already captured

Step 2: Extract and supplement platform audit data

---

Shopify

Enabling and accessing audit data:

1. Go to Settings → Activity (Shopify admin) to see recent staff activity 2. For more granular data, install Shopify Audit or use the Admin API:

• GET /admin/api/2024-04/events.json returns all store events
• Filter by verb (confirmed, placed, edited, refunded, etc.) and subject_type (Order, Refund, Customer)

3. Each order has a Timeline section showing all state changes with timestamps

Export for auditors: 1. Go to Orders → Export to download order data as CSV 2. For detailed refund records: Orders → [specific order] → Timeline shows each action 3. Use the Shopify Admin API to extract the full event stream:

// Fetch all financial events for a date range
const events = await shopify.event.list({
  verb: 'confirmed,placed,refunded,voided',
  created_at_min: '2026-01-01T00:00:00Z',
  created_at_max: '2026-12-31T23:59:59Z',
  limit: 250,
});

Staff action tracking (Shopify Plus):

• Go to Settings → Users and permissions → Staff activity log
• This shows admin actions including order edits, price adjustments, and refunds with user attribution

---

WooCommerce

WooCommerce order notes provide some audit history, but do not track which admin user made a change. Install a dedicated audit plugin.

Simple History plugin (free, recommended): 1. Install Simple History from the WordPress plugin directory 2. It automatically logs:

• Order status changes with user attribution
• Product price changes
• WooCommerce setting changes
• User login/logout events

3. Go to Dashboard → Simple History to view the log 4. Export as CSV via Simple History → Settings → Export

WooCommerce Admin Audit Log (premium, ~$50/year): 1. Install from WooCommerce.com or a third-party marketplace 2. Provides more granular logging including:

• Manual order total edits
• Discount application with staff user attribution
• Refund amounts and approving user

3. Export in CSV or PDF for auditors

Manual order edit tracking: For any manual financial change (discount applied, total adjusted), add an Order Note (internal, not visible to customer) documenting:

• What was changed
• Why it was changed
• Who approved it

This is the minimum acceptable evidence for an auditor when automated logging is not in place.

---

BigCommerce

Accessing store logs: 1. Go to Advanced Settings → Store Logs 2. Filter by log type: Order (financial changes), User (admin activity), System 3. Export as CSV

Order history via API: Use the BigCommerce Orders API to extract a complete order history with status transitions:

// Get all orders modified in a date range
const orders = await bigcommerce.get('/v2/orders', {
  min_date_modified: '2026-01-01T00:00:00+00:00',
  max_date_modified: '2026-12-31T23:59:59+00:00',
  status_id: '',  // all statuses
  limit: 250,
});

Combine with the Order Transactions API (/v2/orders/{id}/transactions) to get payment captures, refunds, and voids.

---

Custom / Headless

For custom storefronts, implement an append-only audit log that records every financial mutation. The key design requirements are: immutable (app role has no UPDATE or DELETE), includes before/after state, and supports tamper detection.

CREATE TABLE financial_audit_events (
  id             UUID        NOT NULL DEFAULT gen_random_uuid(),
  seq            BIGSERIAL   NOT NULL,          -- Monotonic — gap detection
  occurred_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  event_type     VARCHAR(64) NOT NULL,           -- 'order.total_changed', 'refund.issued'
  aggregate_type VARCHAR(32) NOT NULL,           -- 'order', 'payment', 'refund'
  aggregate_id   VARCHAR(128) NOT NULL,
  actor_id       VARCHAR(128) NOT NULL,          -- user ID or 'system'
  actor_role     VARCHAR(64),
  actor_ip       INET,
  before_state   JSONB,                          -- Snapshot before change
  after_state    JSONB,                          -- Snapshot after change
  delta          JSONB,                          -- Only changed fields
  correlation_id UUID,                           -- Tie events in same request
  hash           VARCHAR(64),                    -- SHA-256 for tamper detection
  PRIMARY KEY (id)
);

-- Grant INSERT and SELECT only — never UPDATE or DELETE
GRANT INSERT, SELECT ON financial_audit_events TO app_role;
REVOKE UPDATE, DELETE ON financial_audit_events FROM app_role;

CREATE INDEX idx_fae_aggregate ON financial_audit_events (aggregate_type, aggregate_id, occurred_at DESC);
CREATE INDEX idx_fae_actor ON financial_audit_events (actor_id, occurred_at DESC);
CREATE INDEX idx_fae_seq ON financial_audit_events (seq);

Audit logger with tamper-detection hashing:

import { createHash } from 'crypto';

async function recordAuditEvent(input: {
  eventType: string;
  aggregateType: string;
  aggregateId: string;
  actorId: string;
  actorRole?: string;
  actorIp?: string;
  beforeState?: object | null;
  afterState?: object | null;
  correlationId?: string;
}): Promise<void> {
  const prevEvent = await db.financialAuditEvents.findFirst({
    where: { aggregate_type: input.aggregateType, aggregate_id: input.aggregateId },
    orderBy: { seq: 'desc' },
    select: { seq: true, hash: true },
  });

  const occurredAt = new Date().toISOString();
  const hashInput = [
    String(prevEvent?.seq ?? 0),
    occurredAt,
    input.eventType,
    input.aggregateId,
    input.actorId,
    JSON.stringify(input.afterState ?? null),
  ].join('|');

  const hash = createHash('sha256').update(hashInput).digest('hex');

  await db.financialAuditEvents.insert({
    occurred_at: occurredAt,
    event_type: input.eventType,
    aggregate_type: input.aggregateType,
    aggregate_id: input.aggregateId,
    actor_id: input.actorId,
    actor_role: input.actorRole ?? null,
    actor_ip: input.actorIp ?? null,
    before_state: input.beforeState ?? null,
    after_state: input.afterState ?? null,
    correlation_id: input.correlationId ?? null,
    hash,
    prev_hash: prevEvent?.hash ?? null,
  });
}

Export for auditors:

async function exportAuditTrail(from: Date, to: Date, format: 'json' | 'csv'): Promise<Buffer> {
  const events = await db.financialAuditEvents.findAll({
    where: { occurred_at: { gte: from, lte: to } },
    orderBy: { seq: 'asc' },
  });

  if (format === 'json') return Buffer.from(JSON.stringify(events, null, 2));

  // CSV for auditor delivery
  const rows = events.map(e => ({
    'Date/Time': e.occurred_at,
    'Event Type': e.event_type,
    'Record Type': e.aggregate_type,
    'Record ID': e.aggregate_id,
    'Actor': e.actor_id,
    'Actor Role': e.actor_role ?? '',
    'IP Address': e.actor_ip ?? '',
    'Before': e.before_state ? JSON.stringify(e.before_state) : '',
    'After': e.after_state ? JSON.stringify(e.after_state) : '',
    'Delta': e.delta ? JSON.stringify(e.delta) : '',
  }));
  return buildCsv(rows);
}

Best Practices

• Include `before_state` and `after_state` on every mutation — storing only a delta is not enough for compliance; auditors need to reconstruct the full state of a record at any point in time
• Revoke UPDATE and DELETE at the database level — application-layer checks can be bypassed; the only reliable immutability guarantee is a database permission the application role does not have
• Capture the actor's IP address alongside user ID — when investigating fraud, the IP is often more useful; log both
• Log `correlation_id` from the HTTP request — if a single API request creates multiple audit events, a shared correlation_id lets you reconstruct the full causal chain
• Export and verify a sample monthly — generate a compliance export on the first of each month and verify chain hashes; this gives you a tested evidence package before auditors request one
• Store audit events in a separate database schema — prevents an application bug or a DBA mistake from accidentally affecting audit records alongside production data

Common Pitfalls

Related Skills

• @financial-compliance-sox
• @pci-dss-compliance
• @data-retention-policies
• @gdpr-ecommerce

Design the Financial Audit Trail Database Schema

Problem/Feature Description

A fintech startup processing payments for marketplace vendors is preparing for its first SOX compliance review. The external auditors have flagged that the company has no reliable record of who changed financial records or when. The CTO has tasked a backend engineer with designing the foundational database schema for a new audit trail system that will capture every financial event — orders, payments, invoices, refunds, and general-ledger postings.

The key requirement from the compliance officer is that the audit record must be trustworthy: once written, no one should be able to quietly alter or delete it, and any tampering should be detectable by examining the data itself. The schema also needs to be practical to query — auditors will want to pull all events for a specific order, see everything a particular staff member did, or export a date-range of events in chronological order, all without full-table scans on a table that will reach hundreds of millions of rows within two years.

The company uses PostgreSQL. There is currently no existing audit infrastructure; you are designing it from scratch.

Output Specification

Produce a single file audit-schema.sql containing:

• The full CREATE TABLE statement for the audit events table with all necessary columns and appropriate data types
• All index definitions needed for efficient querying
• The database permission grants that enforce the immutability guarantee
• A brief comment above the permission block explaining the security rationale

Also produce a short schema-design-notes.md (max 1 page) summarising the key design decisions you made, including how the schema supports tamper detection and scalability at high event volumes.

Build the Financial Audit Logger Module

Problem/Feature Description

The payments engineering team at a B2B ecommerce platform has been asked by the CFO to make every financial mutation traceable after a support investigation revealed that a large order total had been changed by an unknown actor with no record of who or when. The platform handles orders, payments, and refunds across hundreds of merchants and processes about 50,000 financial events per day.

Your job is to implement the core FinancialAuditLogger TypeScript module that will become the single source of truth for all financial audit events. The team has agreed that the module must make tampering detectable — any change to historical records should be visible by examining the stored data alone, without requiring logs or external systems. It also needs to capture enough context per event that the finance team can answer questions like "who changed this order total, from which IP, and as part of which request?" without hunting through server logs.

The engineering team uses a database abstraction called db (already in scope), and the codebase uses RequestContext to carry per-request metadata (userId, userRole, ip, requestId).

Output Specification

Produce a single TypeScript file audit-logger.ts containing:

• A FinancialAuditLogger class with a record() method that inserts a new audit event
• A withAudit decorator (or higher-order function) that wraps a financial service method to automatically capture state changes
• A FinancialEvents object with typed helper functions for at least two common financial event types (e.g. order creation, refund, manual price adjustment)
• Export auditLog as a singleton instance of FinancialAuditLogger

Also produce a short audit-logger-design.md (max 1 page) explaining the tamper-detection approach you implemented and any key design decisions.

You do not need to implement the db layer or run any code — produce the TypeScript source files only. Assume TypeScript with standard Node.js built-ins available.

Build Compliance Export and Audit Chain Verification Tooling

Problem/Feature Description

A retail ecommerce company's finance team has received a request from external auditors for a complete export of all financial events from the last quarter. In parallel, the security team has become suspicious that a database administrator may have altered historical payment records following a revenue discrepancy between the platform's records and the payment processor's settlement report. They need a way to programmatically verify whether any stored audit events have been tampered with.

The company already has a financial_audit_events table (PostgreSQL) with columns including seq (BIGSERIAL), occurred_at, event_type, aggregate_type, aggregate_id, actor_id, actor_ip, before_state (JSONB), after_state (JSONB), delta (JSONB), hash (VARCHAR), prev_hash (VARCHAR), correlation_id, and metadata. The hash column was populated at insert time using a deterministic formula over event fields. The engineering team wants two things built: a compliance export function and a chain integrity verification system that can be scheduled to run automatically.

Your implementation should be production-ready TypeScript. The db abstraction is already available in scope.

Output Specification

Produce the following TypeScript files:

• audit-export.ts — a function that exports audit events for a given date range in multiple output formats, with appropriate columns for auditor review
• audit-verify.ts — a function that verifies the tamper-detection chain for a given aggregate and returns a detailed report of any discrepancies found
• audit-integrity-job.ts — a scheduled job that runs the integrity check across recent events and handles the results appropriately

Also produce implementation-notes.md (max 1 page) explaining: the hash verification approach, how you handle high event volumes in the export, and how the scheduled job is intended to be deployed.

You do not need to implement the db layer or run any code — produce the TypeScript source files only.