Building an ERP Audit Trail and Activity Log: PostgreSQL, NestJS Interceptors, and React Activity Feed

An audit trail answers the most important question in any business system: 'who changed this, when, and what did it look like before?' Without it, when a financial record is wrong, there's no way to tell whether it was a data entry error, a system bug, or deliberate manipulation. When I built the ERP for Commsult Indonesia, the audit trail was not an afterthought — it was designed in from the start as a compliance and trust requirement.

What Belongs in an Audit Log

Not everything needs to be audited, but anything that affects financial records, personal data, or system configuration absolutely does. For our ERP, we audit: all CREATE/UPDATE/DELETE operations on invoices, AP vouchers, leave requests, employee records, and customer/vendor records; all workflow state transitions; all role and permission changes; and all bulk operations like Excel imports and payment runs.

The audit_logs Table Schema

The audit_logs table needs to be append-only — rows are never updated or deleted. It stores: user_id (who performed the action), action (CREATE/UPDATE/DELETE), resource and resource_id (what entity was affected), old_data and new_data as JSONB (the before and after snapshots), ip_address (where the request came from), and created_at (when it happened). Partial indexes enable fast per-entity history queries.

Choosing Between Application-Level and Database-Level Logging

There are two approaches to capturing changes: application-level logging (the NestJS service writes to audit_logs as part of each operation) and database-level logging (PostgreSQL triggers). We use application-level logging as the primary mechanism because it captures request context (user identity, IP address) that database triggers cannot easily access. We also use a lightweight PostgreSQL trigger on the most sensitive tables as a safety net.

ERP Audit Trail Architecture (PostgreSQL + NestJS + React)

  ┌──────────────────────────────────────────────────────┐
  │                 APPLICATION LAYER                     │
  │                                                      │
  │  User Action → NestJS Controller → Service           │
  │                     │                                │
  │                     ▼                                │
  │            AuditInterceptor (@Injectable)            │
  │            captures: user, action, before/after      │
  └────────────────────┬─────────────────────────────────┘
                       │ writes to
                       ▼
  ┌──────────────────────────────────────────────────────┐
  │              audit_logs table (PostgreSQL)            │
  │──────────────────────────────────────────────────────│
  │  id          UUID PRIMARY KEY DEFAULT gen_random_uuid │
  │  user_id     UUID NOT NULL REFERENCES users(id)       │
  │  action      VARCHAR(50)  -- CREATE, UPDATE, DELETE   │
  │  resource    VARCHAR(100) -- 'invoice', 'leave', etc  │
  │  resource_id UUID                                     │
  │  old_data    JSONB        -- snapshot before change   │
  │  new_data    JSONB        -- snapshot after change    │
  │  ip_address  INET                                     │
  │  user_agent  TEXT                                     │
  │  created_at  TIMESTAMPTZ DEFAULT NOW()                │
  └────────────────────┬─────────────────────────────────┘
                       │ queried by
                       ▼
  ┌──────────────────────────────────────────────────────┐
  │            React: Activity Feed UI                    │
  │  ┌──────────────────────────────────────────────┐    │
  │  │ 🟢 Matthews created Invoice INV-2026-001      │    │
  │  │    Apr 11, 2026 · 09:14 AM                   │    │
  │  ├──────────────────────────────────────────────┤    │
  │  │ 🟡 Siti approved Leave Request LR-0042       │    │
  │  │    Apr 11, 2026 · 08:30 AM                   │    │
  │  ├──────────────────────────────────────────────┤    │
  │  │ 🔴 Budi voided AP Voucher APV-0019           │    │
  │  │    Apr 10, 2026 · 16:55 PM                   │    │
  │  └──────────────────────────────────────────────┘    │
  └──────────────────────────────────────────────────────┘

Store the full before/after JSON snapshot in the audit log rather than just a list of changed fields. With full JSONB snapshots, you can walk back through the audit log and show exactly what an invoice looked like on any given date — which is exactly what auditors and investigators need.

NestJS Audit Interceptor Implementation

We implement audit logging as a NestJS interceptor that wraps every state-changing API call. Interceptors in NestJS can observe both the incoming request and the outgoing response, making them ideal for capturing before/after state. The interceptor reads the request method and URL to determine the action, captures the pre-existing state, lets the handler run, then writes the audit log entry.

React Activity Feed for End Users

The audit log data is surfaced to end users through a React activity feed component. Each entity detail page has an 'Activity' tab showing its complete change history. The feed renders each log entry with the user's name, the action performed (using a human-readable label), the timestamp relative to now (using date-fns formatDistanceToNow), and for UPDATE actions, a collapsible diff showing what changed.

-- PostgreSQL: Trigger-based audit logging
-- Applied to any table that needs auditing

CREATE TABLE audit_logs (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID        NOT NULL REFERENCES users(id),
  action      VARCHAR(20) NOT NULL CHECK (action IN ('CREATE','UPDATE','DELETE')),
  resource    VARCHAR(100) NOT NULL,
  resource_id UUID,
  old_data    JSONB,
  new_data    JSONB,
  ip_address  INET,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index for fast per-resource lookups
CREATE INDEX idx_audit_resource ON audit_logs (resource, resource_id, created_at DESC);
CREATE INDEX idx_audit_user     ON audit_logs (user_id, created_at DESC);

-- NestJS: Audit Interceptor (application-level logging)
// audit.interceptor.ts
@Injectable()
export class AuditInterceptor implements NestInterceptor {
  constructor(private auditService: AuditService) {}

  intercept(context: ExecutionContext, next: CallHandler): Observable<any> {
    const req = context.switchToHttp().getRequest();
    const { method, url, user, ip, headers } = req;

    const startState = req.body ? JSON.parse(JSON.stringify(req.body)) : null;

    return next.handle().pipe(
      tap(async (response) => {
        if (['POST', 'PATCH', 'PUT', 'DELETE'].includes(method)) {
          const action = method === 'POST' ? 'CREATE'
            : method === 'DELETE' ? 'DELETE' : 'UPDATE';

          await this.auditService.log({
            userId:     user?.id,
            action,
            resource:   this.extractResource(url),
            resourceId: response?.id ?? req.params?.id,
            oldData:    action === 'UPDATE' ? startState : null,
            newData:    action !== 'DELETE' ? response : null,
            ipAddress:  ip,
          });
        }
      }),
    );
  }

  private extractResource(url: string): string {
    // '/api/v1/invoices/123' → 'invoice'
    const segment = url.split('/').filter(Boolean)[2] ?? 'unknown';
    return segment.replace(/s$/, ''); // plurals → singular
  }
}

// React: Activity feed component
export function ActivityFeed({ resourceId, resource }: Props) {
  const { data: logs } = useQuery({
    queryKey: ['audit', resource, resourceId],
    queryFn: () => api.get(`/audit-logs?resource=${resource}&resourceId=${resourceId}`),
  });

  return (
    <ul className="space-y-3">
      {logs?.map(log => (
        <li key={log.id} className="flex gap-3 text-sm">
          <span className={actionColor(log.action)}>●</span>
          <span>
            <strong>{log.user.name}</strong> {actionLabel(log.action)}{' '}
            {log.resource} <code>{log.resourceId?.slice(0, 8)}</code>
          </span>
          <time className="ml-auto text-slate-400">
            {formatDistanceToNow(new Date(log.createdAt), { addSuffix: true })}
          </time>
        </li>
      ))}
    </ul>
  );
}

Compliance and Data Retention

Under UU PDP and general accounting regulations in Indonesia, financial records and their change history must be retained for a minimum of 5 years. Plan your retention strategy from day one: use PostgreSQL table partitioning by year, archive old partitions to cheaper storage after 2 years, and purge after 7 years. Do not store audit logs in the same database as the main application data if your database instance is small.

Never allow any application role to UPDATE or DELETE rows in the audit_logs table. Grant only INSERT and SELECT to the application database user. If you need to 'correct' an audit log entry, use a compensating INSERT that notes the correction — never modify the original row. Immutability is what gives audit logs their legal and compliance value.

Performance Considerations

Writing to the audit log adds a synchronous database write to every API operation. For most operations this is negligible (under 5ms), but for bulk operations (importing 500 rows from Excel) it can add seconds. Use a queue (BullMQ) to process audit log writes asynchronously for bulk operations — write to the queue immediately (non-blocking), and a worker processes the audit entries in the background.

Audit Log as a Debugging Tool

Beyond compliance, the audit log is invaluable as a debugging tool during and after production incidents. When a user reports 'the invoice amount is wrong', the first thing you do is open the audit log for that invoice and walk back through every change. This typically resolves disputes in minutes rather than the hours it would take to reconstruct the history from email threads.

Sources & Further Reading