Create the audit log table

-- PostgreSQL audit log table
CREATE TABLE audit_logs (
  id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  record_id VARCHAR(100),
  action VARCHAR(20) NOT NULL,  -- INSERT, UPDATE, DELETE, SELECT
  old_data JSONB,
  new_data JSONB,
  changed_fields TEXT[],
  user_id VARCHAR(100),
  user_email VARCHAR(255),
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for common queries
CREATE INDEX idx_audit_table_name ON audit_logs(table_name);
CREATE INDEX idx_audit_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_created_at ON audit_logs(created_at);
CREATE INDEX idx_audit_record_id ON audit_logs(record_id);

-- Prevent modifications to audit logs
REVOKE UPDATE, DELETE ON audit_logs FROM PUBLIC;
REVOKE UPDATE, DELETE ON audit_logs FROM myapp_user;

Create the audit trigger function

-- PostgreSQL audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
  old_data JSONB;
  new_data JSONB;
  changed_fields TEXT[];
  user_info JSONB;
BEGIN
  -- Get user context (set by application)
  user_info := current_setting('app.current_user', true)::JSONB;

  IF TG_OP = 'DELETE' THEN
    old_data := to_jsonb(OLD);
    new_data := NULL;
  ELSIF TG_OP = 'INSERT' THEN
    old_data := NULL;
    new_data := to_jsonb(NEW);
  ELSIF TG_OP = 'UPDATE' THEN
    old_data := to_jsonb(OLD);
    new_data := to_jsonb(NEW);
    -- Calculate changed fields
    SELECT array_agg(key) INTO changed_fields
    FROM jsonb_each(to_jsonb(OLD)) AS o(key, value)
    WHERE to_jsonb(NEW) ->> key IS DISTINCT FROM value::text;
  END IF;

  INSERT INTO audit_logs (
    table_name,
    record_id,
    action,
    old_data,
    new_data,
    changed_fields,
    user_id,
    user_email,
    ip_address
  ) VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    TG_OP,
    old_data,
    new_data,
    changed_fields,
    user_info ->> 'user_id',
    user_info ->> 'email',
    (user_info ->> 'ip_address')::INET
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Apply triggers to tables

-- Apply audit trigger to sensitive tables
CREATE TRIGGER users_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER orders_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

CREATE TRIGGER payments_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON payments
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Helper function to add audit triggers to any table
CREATE OR REPLACE FUNCTION add_audit_trigger(target_table TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE format('
    CREATE TRIGGER %I_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON %I
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function()
  ', target_table, target_table);
END;
$$ LANGUAGE plpgsql;

-- Usage: SELECT add_audit_trigger('my_table');

Set user context from your application

// Node.js/Prisma - set user context before queries
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Middleware to set audit context
prisma.$use(async (params, next) => {
  const userContext = getRequestContext(); // From your auth middleware

  if (userContext) {
    await prisma.$executeRaw`
      SELECT set_config('app.current_user', ${JSON.stringify({
        user_id: userContext.userId,
        email: userContext.email,
        ip_address: userContext.ipAddress
      })}, true)
    `;
  }

  return next(params);
});

// Or with raw pg client
async function setAuditContext(client, user) {
  await client.query(
    `SELECT set_config('app.current_user', $1, true)`,
    [JSON.stringify({
      user_id: user.id,
      email: user.email,
      ip_address: user.ip
    })]
  );
}

Application-level audit logging

For more control or NoSQL databases, implement at the application layer:

// Application-level audit logging
interface AuditLog {
  tableName: string;
  recordId: string;
  action: 'CREATE' | 'UPDATE' | 'DELETE' | 'READ';
  oldData?: object;
  newData?: object;
  userId: string;
  userEmail: string;
  ipAddress: string;
  userAgent: string;
  timestamp: Date;
}

class AuditLogger {
  async log(entry: Omit) {
    await prisma.auditLog.create({
      data: {
        ...entry,
        oldData: entry.oldData ? JSON.stringify(entry.oldData) : null,
        newData: entry.newData ? JSON.stringify(entry.newData) : null,
        timestamp: new Date()
      }
    });
  }

  async logUpdate(
    tableName: string,
    recordId: string,
    oldData: object,
    newData: object,
    user: User,
    request: Request
  ) {
    await this.log({
      tableName,
      recordId,
      action: 'UPDATE',
      oldData,
      newData,
      userId: user.id,
      userEmail: user.email,
      ipAddress: request.ip,
      userAgent: request.headers['user-agent'] || ''
    });
  }
}

// Usage in your API
async function updateUser(id: string, data: UpdateUserInput, ctx: Context) {
  const oldUser = await prisma.user.findUnique({ where: { id } });
  const newUser = await prisma.user.update({ where: { id }, data });

  await auditLogger.logUpdate('users', id, oldUser, newUser, ctx.user, ctx.request);

  return newUser;
}

Query audit logs

-- Find all changes to a specific record
SELECT * FROM audit_logs
WHERE table_name = 'users' AND record_id = '123'
ORDER BY created_at DESC;

-- Find all actions by a specific user
SELECT * FROM audit_logs
WHERE user_id = 'user_456'
ORDER BY created_at DESC
LIMIT 100;

-- Find all deletions in the last 24 hours
SELECT * FROM audit_logs
WHERE action = 'DELETE'
  AND created_at > NOW() - INTERVAL '24 hours';

-- Find suspicious activity (bulk operations)
SELECT user_id, action, table_name, COUNT(*)
FROM audit_logs
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id, action, table_name
HAVING COUNT(*) > 100;

Protect audit logs

-- Create separate schema for audit logs
CREATE SCHEMA audit;
ALTER TABLE audit_logs SET SCHEMA audit;

-- Create audit admin role
CREATE ROLE audit_admin NOLOGIN;
GRANT USAGE ON SCHEMA audit TO audit_admin;
GRANT SELECT ON audit.audit_logs TO audit_admin;
-- Note: No INSERT/UPDATE/DELETE granted to humans

-- App user can only insert
GRANT USAGE ON SCHEMA audit TO myapp_user;
GRANT INSERT ON audit.audit_logs TO myapp_user;
GRANT USAGE ON SEQUENCE audit.audit_logs_id_seq TO myapp_user;

-- Prevent truncation
REVOKE TRUNCATE ON audit.audit_logs FROM PUBLIC;

-- Consider partitioning for large tables
CREATE TABLE audit.audit_logs_2024_01 PARTITION OF audit.audit_logs
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');