Key Takeaway
An ERP audit trail answers who changed a record, when, and what it looked like beforehand. Without one, a wrong financial figure cannot be distinguished between a typing error, a bug and deliberate manipulation. Capturing it in a NestJS interceptor keeps the logging out of every individual service.
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 a custom ERP as a freelance consultant, the audit trail was not an afterthought — I designed it 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. In the custom ERP I built, I 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). I use application-level logging as the primary mechanism because it captures request context (user identity, IP address) that database triggers cannot easily access. I also add 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
I 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
UU KUP Article 28(11), as amended by UU 28/2007, requires the books, records and documents behind your bookkeeping, including data processed electronically or in online applications, to be kept for 10 years in Indonesia. Treat financial records and their change history as part of that obligation. Plan your retention strategy from day one: use PostgreSQL table partitioning by year, archive old partitions to cheaper storage after 2 years while keeping that storage in Indonesia, and purge a partition only once it is more than 10 years old. 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.