# Log export with cryptographic completeness proof

- **Pattern:** `ab-001389` (`finserv-audit-trail.retention-compliance.export-completeness`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001389
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001389)

## Why it matters

During a regulatory examination, an auditor will request a complete export of audit records for a specified time period — and they will expect a way to verify that the export is complete and unaltered. Without a cryptographic hash and a record count in the export metadata, the auditor cannot distinguish a complete export from one where rows were selectively omitted. NIST 800-53 AU-9 requires protection of audit information integrity, which extends to exported copies. SOX §404 internal control assessments require that the process for producing audit evidence is itself controlled and verifiable. PCI-DSS 4.0 Req-10.3 requires that audit log files are protected. FINRA Rule 4511 requires that records be available for examination — an export without a completeness proof is an unverifiable record.

## Severity rationale

Medium because an export without a cryptographic completeness proof cannot be used as admissible regulatory evidence, potentially invalidating the audit trail's legal standing during an examination.

## Remediation

Add a completeness metadata block to every export response in `src/app/api/admin/audit-logs/export/route.ts`:

```ts
import crypto from 'crypto';

export async function GET(req: Request) {
  const { startDate, endDate } = Object.fromEntries(new URL(req.url).searchParams);
  const rows = await db('transaction_logs')
    .whereBetween('timestamp', [startDate, endDate]);

  const payload = JSON.stringify(rows);
  const exportHash = crypto.createHash('sha256').update(payload).digest('hex');

  return new Response(payload, {
    headers: {
      'Content-Type': 'application/json',
      'Content-Disposition': `attachment; filename=audit_${startDate}_${endDate}.json`,
      'X-Export-Record-Count': String(rows.length),
      'X-Export-Date-Range': `${startDate}/${endDate}`,
      'X-Export-Hash-SHA256': exportHash,
      'X-Export-Timestamp': new Date().toISOString()
    }
  });
}
```

Include the same four fields — `record_count`, `date_range`, `export_hash`, `export_timestamp` — in the response body or a sidecar manifest file so they travel with the export artifact.

## Detection

- **ID:** `export-completeness`
- **Severity:** `medium`
- **What to look for:** Count all export endpoints or functions (CSV, JSON, PDF). For each, enumerate the metadata fields included in the export. Verify at least 3 completeness proof fields: record count, date range, and cryptographic hash/signature of the export. Quote the actual hash algorithm used if present. An export without at least a record count and date range does not count as pass.
- **Pass criteria:** At least 1 audit log export function exists that includes at least 3 metadata fields proving completeness (record count, date range, hash/signature). Report the count even on pass (e.g., "1 export endpoint found with 4 completeness fields: record_count, date_range, sha256_hash, export_timestamp").
- **Fail criteria:** No export capability exists (0 endpoints), or exports lack at least 3 completeness metadata fields.
- **Skip (N/A) when:** Never — regulators require the ability to export complete audit trails.
- **Detail on fail:** `"No audit log export function found — 0 endpoints produce audit data exports."` or `"Export exists but includes only 1 of 3 required completeness fields (record count only, no hash or date range)."`.
- **Remediation:** Add a secure export endpoint (in `src/app/api/admin/audit-logs/export/route.ts`):

  ```ts
  import crypto from 'crypto';
  import { createObjectCsvWriter } from 'csv-writer';

  app.get('/api/admin/audit-logs/export', authenticate, requireRole('compliance'), async (req, res) => {
    const { startDate, endDate } = req.query;

    const logs = await db.transactionLogs
      .find({ timestamp: { $gte: new Date(startDate), $lte: new Date(endDate) } })
      .lean()
      .exec();

    // Create CSV
    const csvPath = `/tmp/audit_export_${Date.now()}.csv`;
    const writer = createObjectCsvWriter({
      path: csvPath,
      header: [
        { id: 'id', title: 'ID' },
        { id: 'timestamp', title: 'Timestamp' },
        { id: 'user_id', title: 'User ID' },
        { id: 'operationType', title: 'Operation' },
        // ... all columns
      ]
    });

    await writer.writeRecords(logs);

    // Compute completeness proof
    const fileHash = crypto.createHash('sha256');
    const fileContent = fs.readFileSync(csvPath);
    fileHash.update(fileContent);
    const exportHash = fileHash.digest('hex');

    const completenessProof = {
      exportDate: new Date().toISOString(),
      recordCount: logs.length,
      dateRange: { startDate, endDate },
      exportHash,
      exportHashAlgorithm: 'sha256'
    };

    // Send CSV with proof in header
    res.setHeader('X-Export-Proof-Hash', exportHash);
    res.setHeader('X-Export-Record-Count', logs.length);
    res.download(csvPath, `audit_export_${startDate}_${endDate}.csv`, () => {
      fs.unlinkSync(csvPath);
    });
  });
  ```

## External references

- nist AU-9
- sox Section 404
- external FINRA-Rule-4511 — https://www.finra.org/rules-guidance/rulebook/finra-rules/4511
- pci-dss Req-10.3

Taxons: regulatory-conformance, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-001389