Most applications log errors. Fewer log the events that matter to the business: who changed a permission, who exported a CSV of customer data, who deleted a record that cannot be recovered, who approved a payment. These are the events that a regulator, an auditor, a support team, or a forensic investigation needs to reconstruct what happened.
Application logs and audit logs are different things. Application logs are operational — they tell you what your system did. Audit logs are evidentiary — they tell you what your users did, in a form you can trust. GDPR Article 30 requires organizations to maintain a record of processing activities, and audit trails are the technical implementation of that requirement.
This guide covers the full implementation: an immutable audit log table, middleware that captures every state change, querying the audit trail, and the GDPR considerations that determine what you log and how long you keep it.
What Belongs in an Audit Log
Not everything. Logging too much is a problem — collecting too much information in logs can violate GDPR principles. Logs themselves become repositories of personal data and require the same protections as primary datasets. Excessive logging increases the attack surface and complicates compliance efforts.
Log the events that answer: "If something went wrong, could I reconstruct exactly what happened and who was responsible?"
Log these:
Authentication events: login, logout, failed login, password change, MFA changes
Permission changes: role assignments, access grants/revocations
Data exports: any bulk export of user or customer data
Destructive actions: delete, archive, purge
Financial events: payment attempts, refunds, plan changes
Admin actions: any action taken by an admin on behalf of another user
Sensitive data access: viewing PII, medical records, financial data
Do not log these:
Read operations on non-sensitive data (viewing a product listing)
Internal system events (cache misses, background job progress)
Raw personal data in the log payload — use IDs and hashed identifiers
The Audit Log Schema
The audit log table must be append-only. No updates, no deletes — including from your own application.
CREATE TABLE audit_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Who did it
actor_id UUID, -- NULL for system/anonymous actions
actor_email TEXT, -- Denormalized — survives user deletion
actor_role TEXT,
actor_ip INET,
-- What they did
action TEXT NOT NULL, -- 'user.login', 'payment.refunded', 'role.changed'
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!
Top comments (0)