> ## Documentation Index
> Fetch the complete documentation index at: https://docs.loremstock.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Implementing Accountability and Audit Logging in Node.js

> Track user actions with a MySQL audit_logs table using db.query() callbacks, implement HTTP logging with Morgan, and structured application logging with Winston.

Accountability means every significant action in your system can be traced back to who did it, when, and from where. This page builds a complete logging stack: Morgan for HTTP request logs, a MySQL `audit_logs` table using `db.query()`, and Winston for structured application logs.

## What Is Accountability

Accountability ensures the system can answer: **who did what, when, from where, and with what result?**

<CardGroup cols={3}>
  <Card title="Who" icon="user">
    User ID and role from the JWT (req.user.id)
  </Card>

  <Card title="What" icon="clipboard-list">
    Action: CREATE, UPDATE, DELETE, LOGIN
  </Card>

  <Card title="When" icon="clock">
    Exact timestamp recorded in the DB
  </Card>

  <Card title="Where" icon="globe">
    Client IP address and browser user-agent
  </Card>

  <Card title="On What" icon="database">
    Resource type (users, posts) and specific ID
  </Card>

  <Card title="Result" icon="check">
    SUCCESS or FAILURE based on HTTP status code
  </Card>
</CardGroup>

**Non-repudiation**: Users cannot deny performing an action when audit logs with timestamps exist.

## MySQL audit\_logs Table

```sql theme={null}
CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  action ENUM('CREATE','READ','UPDATE','DELETE','LOGIN','LOGOUT','EXPORT') NOT NULL,
  resource VARCHAR(100) NOT NULL,
  resource_id VARCHAR(100) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent TEXT DEFAULT NULL,
  result ENUM('SUCCESS','FAILURE') NOT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_action (action),
  INDEX idx_created_at (created_at)
);
```

## Audit Log Service

```javascript theme={null}
// services/auditLogService.js
const db = require('../config/db');

function sanitizeBody(body) {
  if (!body || typeof body !== 'object') return body;
  const sensitive = ['password', 'token', 'secret', 'creditCard', 'apiKey'];
  const sanitized = Object.assign({}, body);
  sensitive.forEach(function (field) {
    if (sanitized[field]) sanitized[field] = '[REDACTED]';
  });
  return sanitized;
}

function createAuditLog(userId, action, resource, resourceId, ipAddress, userAgent, result, details) {
  const detailsStr = details ? JSON.stringify(sanitizeBody(details)) : null;

  const sql = 'INSERT INTO audit_logs (user_id, action, resource, resource_id, ip_address, user_agent, result, details) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';

  db.query(sql, [userId || null, action, resource, resourceId || null, ipAddress || null, userAgent || null, result, detailsStr], function (err) {
    if (err) {
      // Audit log failure should never crash the main request
      console.error('Audit log error:', err.message);
    }
  });
}

module.exports = { createAuditLog, sanitizeBody };
```

## Using Audit Logs in Routes

Call `createAuditLog()` after the DB operation inside the callback:

```javascript theme={null}
const { createAuditLog } = require('../services/auditLogService');

// DELETE /api/users/:id with audit logging
app.delete('/api/users/:id', verifyToken, checkRole('admin'), function (req, res) {
  const sql = 'DELETE FROM users WHERE id = ?';

  db.query(sql, [req.params.id], function (err, result) {
    if (err) {
      createAuditLog(req.user.id, 'DELETE', 'users', req.params.id, req.ip, req.headers['user-agent'], 'FAILURE', { error: err.message });
      return res.status(500).json({ message: 'Error deleting user', err });
    }
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }

    createAuditLog(req.user.id, 'DELETE', 'users', req.params.id, req.ip, req.headers['user-agent'], 'SUCCESS', null);
    return res.status(204).send();
  });
});
```

## Morgan: HTTP Request Logging

```bash theme={null}
npm install morgan
```

```javascript theme={null}
const morgan = require('morgan');
const fs = require('fs');
const path = require('path');

// Create logs directory
fs.mkdirSync(path.join(__dirname, 'logs'), { recursive: true });

const accessLogStream = fs.createWriteStream(
  path.join(__dirname, 'logs', 'access.log'),
  { flags: 'a' } // append
);

// Development: colorful console output
if (process.env.NODE_ENV === 'development') {
  app.use(morgan('dev'));
} else {
  // Production: write to file
  app.use(morgan('combined', { stream: accessLogStream }));
}
```

Morgan output example (`dev` format):

```text theme={null}
GET /api/users 200 8.345 ms - 453
POST /api/login 401 3.120 ms - 45
DELETE /api/users/5 204 5.678 ms - -
```

| Format     | Use Case                                        |
| ---------- | ----------------------------------------------- |
| `dev`      | Development: colored, concise                   |
| `combined` | Production: Apache-style with IP and user-agent |

## Winston: Application Logging

```bash theme={null}
npm install winston
```

```javascript theme={null}
// config/logger.js
const winston = require('winston');
const path = require('path');
const fs = require('fs');

fs.mkdirSync(path.join(__dirname, '../logs'), { recursive: true });

const logger = winston.createLogger({
  level: process.env.LOG_LEVEL || 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.errors({ stack: true }),
    winston.format.json()
  ),
  transports: [
    new winston.transports.File({ filename: 'logs/error.log', level: 'error', maxsize: 10485760, maxFiles: 5 }),
    new winston.transports.File({ filename: 'logs/combined.log', maxsize: 10485760, maxFiles: 7 })
  ]
});

if (process.env.NODE_ENV !== 'production') {
  logger.add(new winston.transports.Console({
    format: winston.format.combine(winston.format.colorize(), winston.format.simple())
  }));
}

module.exports = logger;
```

Usage:

```javascript theme={null}
const logger = require('./config/logger');

logger.error('DB connection failed', { message: err.message });
logger.warn('Rate limit approaching', { userId: req.user.id });
logger.info('User logged in', { userId: results[0].id, ip: req.ip });
logger.debug('Request body', { body: req.body });
```

### Log Levels

| Level   | Use When                                         |
| ------- | ------------------------------------------------ |
| `error` | Application errors that prevent normal operation |
| `warn`  | Unexpected but non-fatal behavior                |
| `info`  | Normal events: startup, login, CRUD success      |
| `debug` | Detailed data for debugging (dev only)           |

## What NOT to Log

<Warning>
  Never log these — log files can be stored indefinitely and exposed to monitoring tools:

  * Passwords (plaintext or hashed)
  * JWT tokens
  * Credit card numbers
  * API keys and secrets
  * Social security numbers
</Warning>

## Querying Audit Logs

```javascript theme={null}
// GET /api/admin/audit-logs?userId=5&action=DELETE
app.get('/api/admin/audit-logs', verifyToken, checkRole('admin'), function (req, res) {
  const userId = req.query.userId;
  const action = req.query.action;

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

  if (userId) { sql += ' AND user_id = ?'; params.push(userId); }
  if (action) { sql += ' AND action = ?'; params.push(action); }

  sql += ' ORDER BY created_at DESC LIMIT 100';

  db.query(sql, params, function (err, results) {
    if (err) return res.status(500).json({ message: 'Error fetching audit logs', err });
    return res.status(200).json({ data: results });
  });
});
```

## Key Terms

| Term                | Definition                                                                      |
| ------------------- | ------------------------------------------------------------------------------- |
| **Audit log**       | Chronological record of significant system actions for compliance and forensics |
| **Accountability**  | Security principle ensuring every action can be traced to a user                |
| **Non-repudiation** | Users cannot deny performing an action proven by audit logs                     |
| **Log level**       | Severity of a log message: error, warn, info, debug                             |
| **Morgan**          | HTTP request logging middleware for Express                                     |
| **Winston**         | Flexible Node.js logging library with multiple transports                       |

## Common Mistakes

<Accordion title="Logging sensitive data">
  Always call sanitizeBody() before passing request body to createAuditLog(). Logging passwords is a GDPR violation.
</Accordion>

<Accordion title="Crashing the request on audit log failure">
  Audit log failures should be caught and printed to console only. Never let a failed INSERT into audit\_logs break the API response.
</Accordion>

<Accordion title="No indexes on audit_logs">
  Without indexes on user\_id, action, and created\_at, queries over a large audit\_logs table take seconds. Always define indexes.
</Accordion>