Audit Logging in Node.js: Who Did What, When, and How to Prove It

DEV Community

resource TEXT, -- 'user', 'order', 'subscription' resource_id TEXT, -- The affected record ID -- Tenant context tenant_id UUID, -- The change old_value JSONB, -- State before the action new_value JSONB, -- State after the action metadata JSONB, -- Request context, extra fields -- When created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- Indexes for common query patterns CREATE INDEX idx_audit_actor_id ON audit_logs(actor_id); CREATE INDEX idx_audit_tenant_id ON audit_logs(tenant_id); CREATE INDEX idx_audit_resource ON audit_logs(resource, resource_id); CREATE INDEX idx_audit_action ON audit_logs(action); CREATE INDEX idx_audit_created_at ON audit_logs(created_at DESC); -- Prevent updates and deletes — audit logs are immutable CREATE RULE audit_logs_no_update AS ON UPDATE TO audit_logs DO INSTEAD NOTHING; CREATE RULE audit_logs_no_delete AS ON DELETE TO audit_logs DO INSTEAD NOTHING;

The old_value and new_value columns capture the state before and after a change — critical for reconstructing what happened. Denormalizing actor_email means the audit trail survives if the user account is later deleted.

The CREATE RULE statements are database-level enforcement. Even if application code has a bug that tries to update or delete an audit record, the database prevents it.

The Audit Logger

// src/lib/auditLogger.ts
import db from './db';
interface AuditEvent {
 actorId?: string;
 actorEmail?: string;
 actorRole?: string;
 actorIp?: string;
 action: string; // 'user.created', 'role.changed', 'payment.refunded'
 resource?: string;
 resourceId?: string;
 tenantId?: string;
 oldValue?: unknown;
 newValue?: unknown;
 metadata?: Record<string, unknown>;
}
export async function audit(event: AuditEvent): Promise<void> {
 try {
 await db.query(`
 INSERT INTO audit_logs (
 actor_id, actor_email, actor_role, actor_ip,
 action, resource, resource_id,
 tenant_id, old_value, new_value, metadata
 ) VALUES (1,ドル2,ドル3,ドル4,ドル5,ドル6,ドル7,ドル8,ドル9,ドル10,ドル11ドル)
 `, [
 event.actorId || null,
 event.actorEmail || null,
 event.actorRole || null,
 event.actorIp || null,
 event.action,
 event.resource || null,
 event.resourceId || null,
 event.tenantId || null,
 event.oldValue ? JSON.stringify(event.oldValue) : null,
 event.newValue ? JSON.stringify(event.newValue) : null,
 event.metadata ? JSON.stringify(event.metadata) : null,
 ]);
 } catch (err) {
 // Audit log failures must not break the main operation
 // But they should be visible — log the failure loudly
 logger.error({
 error: (err as Error).message,
 action: event.action,
 }, 'AUDIT LOG WRITE FAILED');
 }
}

Structured Action Names

Use dot-notation action names that are consistent and queryable:

// src/lib/auditActions.ts
export const AuditActions = {
 // Auth
 AUTH_LOGIN: 'auth.login',
 AUTH_LOGIN_FAILED: 'auth.login.failed',
 AUTH_LOGOUT: 'auth.logout',
 AUTH_PASSWORD_CHANGED:'auth.password.changed',
 AUTH_MFA_ENABLED: 'auth.mfa.enabled',
 // Users
 USER_CREATED: 'user.created',
 USER_UPDATED: 'user.updated',
 USER_DELETED: 'user.deleted',
 USER_ROLE_CHANGED: 'user.role.changed',
 USER_INVITED: 'user.invited',
 // Data
 DATA_EXPORTED: 'data.exported',
 DATA_DELETED: 'data.deleted',
 // Billing
 SUBSCRIPTION_CREATED: 'subscription.created',
 SUBSCRIPTION_CANCELLED:'subscription.cancelled',
 PAYMENT_REFUNDED: 'payment.refunded',
 // Admin
 ADMIN_IMPERSONATED: 'admin.impersonated',
 ADMIN_CONFIG_CHANGED: 'admin.config.changed',
} as const;
export type AuditAction = typeof AuditActions[keyof typeof AuditActions];

Using the Audit Logger in Route Handlers

// src/routes/users.ts
import { audit, AuditActions } from '../lib/auditLogger';
router.patch('/users/:id/role', authenticate, requireRole('admin'), async (req, res) => {
 const { id } = req.params;
 const { role } = req.body;
 const existing = await getUserById(id, req.tenant.id);
 if (!existing) return res.status(404).json({ error: 'User not found' });
 const updated = await updateUserRole(id, role, req.tenant.id);
 // Audit the role change with before/after state
 await audit({
 actorId: req.user.id,
 actorEmail: req.user.email,
 actorRole: req.user.role,
 actorIp: req.ip,
 action: AuditActions.USER_ROLE_CHANGED,
 resource: 'user',
 resourceId: id,
 tenantId: req.tenant.id,
 oldValue: { role: existing.role },
 newValue: { role },
 metadata: {
 requestId: req.id,
 userAgent: req.headers['user-agent'],
 },
 });
 res.json(updated);
});
// Auth events — login and failed login
router.post('/auth/login', async (req, res) => {
 const { email, password } = req.body;
 const user = await findUserByEmail(email);
 if (!user || !(await verifyPassword(password, user.passwordHash))) {
 // Log failed attempts — useful for detecting brute force
 await audit({
 actorIp: req.ip,
 action: AuditActions.AUTH_LOGIN_FAILED,
 metadata: {
 email, // Email attempted — not a real user field
 requestId: req.id,
 userAgent: req.headers['user-agent'],
 },
 });
 return res.status(401).json({ error: 'Invalid credentials' });
 }
 const tokens = generateTokens(user);
 await audit({
 actorId: user.id,
 actorEmail: user.email,
 actorRole: user.role,
 actorIp: req.ip,
 tenantId: user.tenantId,
 action: AuditActions.AUTH_LOGIN,
 metadata: {
 requestId: req.id,
 userAgent: req.headers['user-agent'],
 },
 });
 res.json(tokens);
});

Querying the Audit Trail

// src/routes/admin/audit.ts
// Get audit trail for a specific resource
router.get('/admin/audit/:resource/:id', authenticate, requireRole('admin'), async (req, res) => {
 const { resource, id } = req.params;
 const limit = parseInt(req.query.limit as string) || 50;
 const cursor = req.query.cursor as string | undefined;
 const result = await db.query(`
 SELECT
 id, actor_id, actor_email, actor_role, actor_ip,
 action, resource, resource_id,
 old_value, new_value, metadata,
 created_at
 FROM audit_logs
 WHERE
 resource = 1ドル
 AND resource_id = 2ドル
 AND tenant_id = 3ドル
 ${cursor ? 'AND created_at < 4ドル' : ''}
 ORDER BY created_at DESC
 LIMIT ${cursor ? '5ドル' : '4ドル'}
 `, cursor
 ? [resource, id, req.tenant.id, cursor, limit + 1]
 : [resource, id, req.tenant.id, limit + 1]
 );
 const rows = result.rows;
 const hasMore = rows.length > limit;
 if (hasMore) rows.pop();
 res.json({
 data: rows,
 hasMore,
 nextCursor: hasMore ? rows[rows.length - 1].created_at : null,
 });
});
// Activity for a specific user — for "session history" or DSAR requests
router.get('/admin/audit/actor/:userId', authenticate, requireRole('admin'), async (req, res) => {
 const result = await db.query(`
 SELECT action, resource, resource_id, metadata, created_at
 FROM audit_logs
 WHERE actor_id = 1ドル
 AND tenant_id = 2ドル
 ORDER BY created_at DESC
 LIMIT 100
 `, [req.params.userId, req.tenant.id]);
 res.json(result.rows);
});

GDPR Considerations

Logs must have defined retention periods. Exceeding that timeframe without reason, even accidentally, constitutes a breach of the regulation.

Retention policy:

-- Automated cleanup — run as a scheduled job
-- Retain audit logs for 2 years (adjust to your regulatory requirement)
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '2 years';

Data minimisation in log payloads:

Avoid logging raw personal data such as full names, addresses, phone numbers, or full data records. Where necessary, replace them with pseudonymous identifiers or hashed values.

// BAD — full PII in audit log
await audit({
 action: AuditActions.USER_UPDATED,
 newValue: {
 name: 'Jane Smith',
 email: 'jane@example.com',
 address: '123 Main St, London',
 dob: '1985年03月15日',
 },
});
// GOOD — reference IDs, not PII
await audit({
 action: AuditActions.USER_UPDATED,
 resource: 'user',
 resourceId: user.id,
 oldValue: { fieldsChanged: ['email', 'address'] }, // What changed
 newValue: { fieldsChanged: ['email', 'address'] }, // Not the values
});

DSAR (Data Subject Access Request) support:

Under GDPR, users can request all data you hold about them including audit logs that reference them.

// Generate DSAR package for a user — all audit records referencing their ID
async function generateDSARReport(userId: string, tenantId: string) {
 const result = await db.query(`
 SELECT action, resource, resource_id, created_at, actor_ip
 FROM audit_logs
 WHERE (actor_id = 1ドル OR resource_id = 1ドル)
 AND tenant_id = 2ドル
 ORDER BY created_at DESC
 `, [userId, tenantId]);
 return {
 userId,
 generatedAt: new Date(),
 auditTrail: result.rows,
 };
}

The Compliance Checklist

✅ Audit table is append-only — DB rules prevent UPDATE and DELETE
✅ Actor email is denormalized — audit trail survives account deletion
✅ Action names are structured and consistent (dot notation)
✅ Old and new values captured for state-change events
✅ Audit failures logged loudly but don't break main operations
✅ Retention policy defined and automated — default 2 years
✅ PII not stored in audit payloads — IDs and field names only
✅ DSAR query ready — can export all records for a given user ID
✅ Failed authentication attempts logged — brute force detection
✅ Admin impersonation logged — who accessed whose account

Originally published on ZyVOP

💡 For more articles like this, subscribe to the ZyVOP newsletter!