financial-audit-trail

What this skill does

# 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:
```javascript
// 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:
```javascript
// 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.

```sql
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:**

```typescript
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: occ