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

Platform	Built-in Audit Capability	What You Need to Add
Shopify	Order timeline records all events (created, payment captured, refunded, edited); admin action attribution is limited	Export timeline data via Admin API; for admin changes, enable Staff activity logging under Settings
WooCommerce	Order notes show customer-visible actions; system notes show some status changes	Install WooCommerce Admin Audit Log or Simple History plugin for full admin action tracking
BigCommerce	Store logs in Advanced Settings → Store Logs for system events; order history available via API	For admin action attribution, export via API and combine with server access logs
Custom / Headless	Nothing built in	Must build — see Custom section

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

Problem	Solution
Audit events missing because developers call db.update() directly	The audit logger must be called in the service layer, not the controller; add a code review checklist item that flags direct db.update calls on financial tables
before_state is null because the developer only captures state after the change	Fetch and snapshot the record BEFORE the mutation inside the same database transaction
Audit table grows to hundreds of millions of rows, slowing queries	Partition the table by occurred_at (monthly partitions); keep 12 months on hot storage, archive older partitions to S3 + Athena
An attacker who compromises the app role can delete audit rows	Revoke DELETE from all roles; consider a secondary write-only log stream to an external service (CloudWatch Logs, Datadog)
Compliance export takes hours to generate	Pre-build indexed views for common audit report patterns; ensure the occurred_at index is used in range queries

Related Skills

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