How to Build a Secure Audit Trail in Your Web App (No Third-Party Tools)

How to Build a Secure Audit Trail in Your Web App (No Third-Party Tools)
I never cared for audit trails too much until we did our first compliance review on a healthcare project I was working on. The first question asked of the auditor was:

Please provide me with everything you did to this record in the last 90 days.

We could not. Not properly, anyway.

At that point, I made logging not an afterthought. When you are building anything that involves some sensitive data, your builds could be in healthcare, or maybe finance, or perhaps internal admin tools — you need a real audit trail. Not some console. queryable, a tamper-resistant log of who did what and when —log wrapper.
Here is a tutorial on how to build your own from scratch using Node.js, Express, and PostgreSQL. No third-party service is needed.

Prerequisites

• Node.js (v18+)
• PostgreSQL (v14+)
• Basics of SQL and Express
• pg npm package for Database Access Management

What Is an Audit Trail?

An audit trail is a record of all important events that have taken place in your system in an append-only style. The magic word is append-only — rows are appended, rows can never change or be deleted. That’s what makes it reliable.
When something goes wrong — a record gets changed, data goes missing, a user complains their information was modified — you want to be able to pull up a timeline and say exactly what happened. Without a proper audit trail, you're guessing.
The things you need to capture per action: who did it, what they did, which record was affected, when it happened, what changed (before and after), and where the request came from (IP, device).

Step 1: Design the Audit Log Table

The audit log lives in its own table and should be treated as write-only.

CREATE TABLE audit_logs (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL,
  user_email    TEXT NOT NULL,
  action        TEXT NOT NULL,          -- 'CREATE', 'UPDATE', 'DELETE', 'VIEW'
  resource      TEXT NOT NULL,          -- table or resource name, e.g. 'medications'
  resource_id   TEXT,                   -- the affected record's ID
  old_values    JSONB,                  -- snapshot before change
  new_values    JSONB,                  -- snapshot after change
  ip_address    INET,
  user_agent    TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for fast lookups by user or resource
CREATE INDEX idx_audit_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_resource ON audit_logs(resource, resource_id);
CREATE INDEX idx_audit_created_at ON audit_logs(created_at DESC);

A few things worth noting about the design. JSONB for old/new values means you don't have to run a migration every time your data model changes — the log just captures whatever shape the record had at that point in time. TIMESTAMPTZ instead of TIMESTAMP is important if your users are in different time zones. And there's no updated_at column here — audit logs don't get updated.

Step 2: Create an Audit Service

Consolidate audit logging in a single service to make it consistent and easy to call from anywhere.

// services/auditService.js
const { pool } = require('../db');

async function log({
  userId,
  userEmail,
  action,
  resource,
  resourceId = null,
  oldValues = null,
  newValues = null,
  ipAddress = null,
  userAgent = null,
}) {
  const query = `
    INSERT INTO audit_logs
      (user_id, user_email, action, resource, resource_id, old_values, new_values, ip_address, user_agent)
    VALUES
      ($1, $2, $3, $4, $5, $6, $7, $8, $9)
  `;

  const values = [
    userId,
    userEmail,
    action,
    resource,
    resourceId,
    oldValues ? JSON.stringify(oldValues) : null,
    newValues ? JSON.stringify(newValues) : null,
    ipAddress,
    userAgent,
  ];

  // Don't await — fire-and-forget to avoid blocking the main request
  pool.query(query, values).catch((err) => {
    console.error('[AuditService] Failed to write audit log:', err.message);
  });
}

module.exports = { log };

Look at pool.query is called without await. This is by design; audit logging should never cause a user-facing request to fail or slow down. I learned this the hard way when a slow database insert blocked a critical record update in production. It was easily fixed. Fire and forget. Catch errors silently and move on.

Step 3: Add It to Your Routes

Here's a real example — updating a sensitive record:

const express = require('express');
const router = express.Router();
const audit = require('../services/auditService');
const { getRecordById, updateRecord } = require('../models/record');

router.put('/:id', async (req, res) => {
  const { id } = req.params;
  const userId = req.user.id;
  const userEmail = req.user.email;

  try {
    // Fetch the current state before updating
    const oldRecord = await getRecordById(id);

    // Perform the update
    const updated = await updateRecord(id, req.body);

    // Log the change
    audit.log({
      userId,
      userEmail,
      action: 'UPDATE',
      resource: 'records',
      resourceId: id,
      oldValues: oldRecord,
      newValues: updated,
      ipAddress: req.ip,
      userAgent: req.headers['user-agent'],
    });

    res.json(updated);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Always capture oldValues before changing them. Once you take the record away, it’s gone.

Step 4: Build an Express Middleware for Read Logging

For sensitive resources, you may want to log even read (VIEW) actions. Use middleware so you don't have to add it to every route manually.

// middleware/auditRead.js
const audit = require('../services/auditService');

function auditRead(resource) {
  return (req, res, next) => {
    if (req.user) {
      audit.log({
        userId: req.user.id,
        userEmail: req.user.email,
        action: 'VIEW',
        resource,
        resourceId: req.params.id || null,
        ipAddress: req.ip,
        userAgent: req.headers['user-agent'],
      });
    }
    next();
  };
}

module.exports = auditRead;

Apply it to any route:

const auditRead = require('../middleware/auditRead');
router.get('/:id', auditRead('records'), async (req, res) => {
  // your handler
});

Step 5: Query the Audit Log

Give your admins a way to read the log. Here's a simple API endpoint with filtering:

router.get('/', async (req, res) => {
  const { userId, resource, from, to, limit = 100 } = req.query;

  let query = 'SELECT * FROM audit_logs WHERE 1=1';
  const params = [];

  if (userId) {
    params.push(userId);
    query += ` AND user_id = $${params.length}`;
  }
  if (resource) {
    params.push(resource);
    query += ` AND resource = $${params.length}`;
  }
  if (from) {
    params.push(from);
    query += ` AND created_at >= $${params.length}`;
  }
  if (to) {
    params.push(to);
    query += ` AND created_at <= $${params.length}`;
  }

  params.push(Math.min(parseInt(limit), 500));
  query += ` ORDER BY created_at DESC LIMIT $${params.length}`;

  const result = await pool.query(query, params);
  res.json(result.rows);
});

Cap the limit (here at 500) to prevent someone from accidentally dumping the entire log in one request.

Step 6: Protect the Audit Log

The audit table is only useful if nobody can tamper with it. Enforce this at the database level:

REVOKE DELETE, UPDATE ON audit_logs FROM app_user;

Beyond that, only admins and compliance roles should be able to query the log. Consider a separate database user for writing vs. reading: your API uses audit_writer (INSERT only), your admin dashboard uses audit_reader (SELECT only). A compromised API key cannot read the audit history, even if it tries.

Handling Bulk Operations

When a single action affects multiple records, create one audit entry per record — not one entry for the entire batch. This makes it possible to trace the history of any individual record without parsing bulk entries.

for (const record of affectedRecords) {
  audit.log({
    userId,
    userEmail,
    action: 'DELETE',
    resource: 'items',
    resourceId: record.id,
    oldValues: record,
    ipAddress: req.ip,
    userAgent: req.headers['user-agent'],
  });
}

Things I'd Do Differently

Working on compliance-sensitive projects has taught me a few things about audit logging that I wish I'd known earlier.
The biggest one: log the reason, not just the action. For high-risk operations like deletions or adjustments, add a reason field. When a discrepancy comes up months later, "disposal — patient refused" is infinitely more useful than just seeing a number change in the data. A plain UPDATE tells you nothing about why.
Second: store the user's role at log time. Roles change. If someone was an admin when they made a change and you only stored their user ID, you'll have to reconstruct what role they had at that point in time — usually when you're already under pressure. Just log role: 'admin' alongside the user ID.
And build the query UI early. The first few months on one project we had the data but no easy way to search it, which meant every incident investigation started with someone running raw SQL. A simple admin page with date range and user filters makes a huge difference when you actually need it.

Wrapping Up

The core pattern is simple: append-only table, a central log function that never blocks, and database-level protection against tampering. Most of the complexity is in the details — what to log, how to query it, and how to make it useful when you actually need it.
Get the foundation in place early. It's much harder to retrofit an audit trail into an existing app than to build it in from the start.

---

Zia Ullah is a full-stack developer with 12+ years of experience (since 2013), specializing in web applications for healthcare and SaaS. He works at ValueAdd, a software development company based in Sweden.