finsilabs/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