Move audit logging to backend

[IanMayo]

Task Assignment: Move Audit Logging to Backend

Memory Bank Protocol

IMPORTANT: Before implementing, read and understand:

1. Current frontend audit implementation in src/providers/dataProvider/resource-callbacks/
2. Backend extension patterns in _extensions/api.js and _devExtensions/api.js
3. Soul-cli REST API structure and middleware capabilities
4. Existing audit table schema and TypeScript types

Key Context:

• Soul-cli v0.7.8 serves SQLite via REST at /api/tables/*
• JWT tokens used for authentication (no explicit middleware shown, may be in soul-cli)
• Audit table already exists with all required fields
• Frontend currently uses lifecycle callbacks via React-Admin's withLifecycleCallbacks

Current State Analysis

Frontend Audit Implementation

• Location: src/providers/dataProvider/index.ts orchestrates lifecycle callbacks
• Pattern: Each resource has lifecycle callbacks in src/providers/dataProvider/resource-callbacks/
• Audit Function: trackEvent() in src/utils/audit.ts creates audit entries
• Coverage: All CREATE, UPDATE, DELETE operations via React-Admin UI
• Gap: Direct API calls bypass these lifecycle callbacks entirely

Backend Structure

• Extensions: Custom Express routes added via _extensions/api.js (production) and _devExtensions/api.js (dev)
• Auth Handling: Login controller validates users, but JWT middleware not explicitly visible
• Database: SQLite with audit table containing: id, user, resource, dataId, activityType, dateTime, label, activityDetail, securityRelated, subjectId, subjectResource, ip

Requirements

Functional Requirements

• Capture ALL write operations (POST, PUT, DELETE) to /api/tables/* endpoints
• Extract user ID from JWT token in request headers
• Determine resource type from URL path (e.g., /api/tables/items/rows → "items")
• Map HTTP methods to audit activity types (POST → CREATE, PUT → UPDATE, DELETE → DELETE)
• Capture client IP address from request headers (consider x-forwarded-for)
• Generate human-readable audit labels matching current frontend patterns
• Handle bulk operations (updateMany, deleteMany) appropriately

Non-Functional Requirements

• Middleware must not break existing soul-cli functionality
• Audit failures should log errors but not break main operations
• Performance impact must be minimal (< 50ms added latency)
• Support parallel run with frontend audit (temporary duplication)

Technical Approach

Phase 1: Implement Backend Audit Middleware

• Create audit middleware module (_extensions/auditMiddleware.js):

  // Core structure:
  // 1. Pattern match /api/tables/* routes
  // 2. Extract JWT token and decode user ID
  // 3. Capture request details (method, path, body)
  // 4. Let request proceed to soul-cli
  // 5. After response, create audit entry if write operation succeeded

• Extract user from JWT token:

  • Check for Authorization header with Bearer token
  • Decode JWT to get user ID (investigate soul-cli's token structure)
  • Handle cases where token is missing or invalid

• Map operations to audit types:

  const methodToAuditType = {
    'POST': 'CREATE',    // matches AuditType.CREATE
    'PUT': 'UPDATE',     // matches AuditType.UPDATE
    'DELETE': 'DELETE'   // matches AuditType.DELETE
  };

• Capture resource details:

  • Parse URL to extract table name (resource)
  • Extract ID from URL for single-record operations
  • Handle bulk operations (multiple IDs)
  • Capture request body for field-level changes

• Create audit entries:

  • Use direct SQLite insert (via better-sqlite3)
  • Match existing audit table schema
  • Generate appropriate activity labels
  • Include IP address from headers

Phase 2: Integrate Middleware with Soul-CLI

• Register middleware in extensions:

  • Add to both _extensions/api.js (production) and _devExtensions/api.js (dev)
  • Ensure middleware runs AFTER authentication but BEFORE soul-cli routes
  • Use Express middleware pattern with app.use()

• Handle soul-cli response interception:

  • Monitor response status to only audit successful operations
  • Capture created IDs from POST responses
  • Handle error cases gracefully

Phase 3: Testing Implementation

• Unit tests for middleware (_extensions/tests/auditMiddleware.test.js):

  • Mock Express request/response objects
  • Test user extraction from JWT
  • Test operation type mapping
  • Test resource and ID extraction from URLs
  • Test audit entry creation
  • Test error handling

• Integration tests per resource:

  • Items: Create, Update, Delete with audit verification
  • Batches: Create, Update with audit verification
  • Users: Create, Update (password changes) with security flag
  • Dispatch: Create, Update, Finalize operations
  • Destruction: Create, Update, Finalize operations
  • Reference data: Test each reference table

• Direct API testing:

  • Use curl or Postman to bypass frontend
  • Verify audit entries created for direct API calls
  • Test bulk operations

Phase 4: Parallel Run Verification

• Deploy with both audit systems active:

  • Keep frontend lifecycle callbacks enabled
  • Backend middleware also creating entries
  • Monitor for discrepancies

• Comparison testing checklist:

  • Compare audit entry counts per resource
  • Verify all frontend-audited operations also captured by backend
  • Check for any duplicate entries (expected during parallel run)
  • Identify any operations only captured by one system

Phase 5: Frontend Cleanup

• Remove frontend audit code:

  • Remove trackEvent calls from all lifecycle callbacks
  • Remove audit parameter from lifecycle callback constructors
  • Clean up imports of audit utilities
  • Update lifecycleCallbacks function signature
  • Test that UI operations still work without frontend audit

• Update type definitions:

  • Remove audit-related types from frontend if no longer needed
  • Ensure CustomDataProvider interface still correct

Phase 6: Documentation

• Update technical documentation:

  • Document new audit middleware architecture
  • Update CLAUDE.md with new audit approach
  • Add middleware configuration notes to deployment docs

• Security documentation:

  • Document how audit trail integrity is maintained
  • Note that all API access now generates audit trails
  • Update compliance documentation if applicable

Testing Requirements

Test Coverage Checklist

• Middleware Unit Tests: 90%+ coverage of auditMiddleware.js
• Integration Tests: Each resource type (Items, Batches, Users, Projects, Dispatch, Destruction, all reference tables)
• Security Tests: Attempt to bypass audit via malformed requests, missing tokens, etc.
• Performance Tests: Measure latency impact of audit middleware
• Parallel Run Tests: Verify both systems capture same events

Test Scenarios

1. Happy Path: Normal CRUD operations generate correct audit entries
2. Bulk Operations: UpdateMany and DeleteMany create appropriate audit trails
3. Failed Operations: Failed requests don't create audit entries
4. Missing Auth: Requests without valid JWT are still audited (as failed auth attempts)
5. Direct API: Bypass frontend completely, verify audit trail exists

Migration Strategy

Rollout Plan

1. Week 1: Deploy middleware to development environment
2. Week 2: Run parallel audit (both frontend and backend) in dev
3. Week 3: Deploy to staging/test with parallel run
4. Week 4: Production deployment with parallel run
5. Week 5: Verify audit completeness, remove frontend audit code
6. Week 6: Final production deployment without frontend audit

Rollback Plan

• Frontend audit code remains until fully verified
• Middleware can be disabled via environment variable flag
• If issues found, disable middleware and continue with frontend audit

Success Criteria

• ✅ All REST API write operations create audit log entries
• ✅ Audit logs include: user ID, timestamp, resource, operation, IP
• ✅ Direct API access (curl, Postman) generates proper audit trails
• ✅ No audit events lost during migration
• ✅ Frontend audit code completely removed
• ✅ Performance impact < 50ms per request
• ✅ 100% test coverage for critical audit paths
• ✅ Documentation updated to reflect new architecture
• ✅ Security gap eliminated - no way to bypass audit logging

Questions to Resolve

1. JWT Structure: How does soul-cli handle JWT tokens? Need to investigate token payload structure to extract user ID correctly.

2. Soul-CLI Middleware: How does soul-cli v0.7.8 handle Express middleware? Does it expose the Express app instance for middleware registration?

3. Transaction Handling: Should audit entries be part of same transaction as data modifications? Currently seems independent.

4. Audit Entry IDs: For newly created records, response contains lastInsertRowid - need to ensure this is captured for audit.

5. Performance Impact: Should audit writes be asynchronous to minimize latency? Consider using setImmediate or process.nextTick.

6. Field-Level Changes: Current frontend tracks some field-level changes. Should backend middleware also capture before/after values?

Implementation Notes

Key Files to Modify

• _extensions/api.js - Add audit middleware registration
• _devExtensions/api.js - Add audit middleware for development
• _extensions/auditMiddleware.js - New file with core middleware logic
• src/providers/dataProvider/resource-callbacks/*.ts - Remove audit calls (Phase 5)
• src/providers/dataProvider/index.ts - Remove audit parameter (Phase 5)

Activity Type Mapping

Current frontend uses these activity types (from src/utils/activity-types):

• LOGIN = 0
• CREATE = 1
• UPDATE = 2
• DELETE = 3
• LOAN = 4
• RETURN = 5
• DISPATCH = 6
• ADD_ITEM_TO_BATCH = 7
• ADD_ITEM_TO_DESTRUCTION = 8
• FINALISE_BATCH = 9
• FINALISE_DESTRUCTION = 10
• HASTENER_SENT = 11
• ADD_ITEM_TO_DISPATCH = 12

Backend middleware should use same numeric values for consistency.

IP Address Extraction

// Priority order for IP extraction:
const getClientIp = (req) => {
  return req.headers['x-forwarded-for']?.split(',')[0].trim() ||
         req.headers['x-real-ip'] ||
         req.connection.remoteAddress ||
         req.socket.remoteAddress ||
         'unknown';
};

---

Agent Instructions

Your Role: Backend developer with Express/Node.js and SQLite expertise

Starting Point: Begin with Phase 1 - creating the audit middleware module. Focus on getting basic audit capture working for a single resource type (e.g., items) before expanding to all resources.

Key Constraints:

1. Must work with existing soul-cli v0.7.8 REST API structure
2. Cannot modify soul-cli core - only add middleware via extensions
3. Must maintain backward compatibility during parallel run period
4. Audit failures must not break main operations

Deliverables:

1. Working audit middleware that captures all write operations
2. Comprehensive test suite with >90% coverage
3. Documentation of middleware configuration and behavior
4. Clean removal of frontend audit code (after verification)

Remember: This is a security-critical change. Ensure no edge cases where audit can be bypassed. Test thoroughly with direct API access, not just through the UI.

[IanMayo]

Original Issue Description (Archived)

We log a lot of interactions, and flag some as being security related.

But, it is possible for an authenticated user to interact with the backend via the REST API directly, which wouldn't log changes.

We should investigate switching the logging to running via the backend. This would concretely ensure all update logging is performed. I'm not quite sure how we confidently capture the user-id, though JWT tokens may offer the solution.

For the custom api backend methods such as edit password this would be easy. For the standard SOUL interactions we'd just add an extra middleware which captures the important aspects (user, resource, method, date, etc).

---

This original description has been archived. The issue has been enhanced with detailed security requirements and acceptance criteria.

[IanMayo]

📋 Archived Original Enhanced Description

The original enhanced description has been archived here before converting to TAP format.

---

🔒 Security Vulnerability: Audit Logging Can Be Bypassed via Direct API Access

Problem Statement

The current audit logging implementation operates exclusively in the frontend (React-Admin), creating a critical security gap: authenticated users can bypass audit trails by making direct REST API calls to the backend soul-cli server. This undermines the integrity of our security audit trail and potentially violates compliance requirements.

Security Impact

• Severity: Medium (theoretical vulnerability with existing authentication controls)
• Risk: Users with API knowledge can perform write operations without generating audit logs
• Affected Operations: All CREATE, UPDATE, DELETE operations across all resources when accessed directly via REST API

Current State vs Desired State

Current State ❌

• Audit logging implemented in frontend via React-Admin lifecycle callbacks
• Direct API calls to /api/tables/* endpoints bypass audit logging entirely
• No guarantee of comprehensive audit trail for compliance
• Security-sensitive operations may go unrecorded

Desired State ✅

• All write operations generate audit logs regardless of access method
• Centralized audit logging in backend via Express middleware
• Complete removal of redundant frontend audit code
• Guaranteed audit trail for all authenticated operations

Technical Approach

Implementation Strategy

• Primary Approach: Express middleware to intercept all soul-cli REST operations
• Scope: All write operations (CREATE, UPDATE, DELETE) on all resources
• Migration: Parallel run strategy - both frontend and backend logging temporarily, then remove frontend after verification

Audit Information to Capture

• User identification (extracted from JWT token)
• Timestamp of operation
• Resource details (type, ID, affected fields)
• Operation type (CREATE, UPDATE, DELETE)
• IP address and user agent

Acceptance Criteria

• Express middleware implemented to capture all REST API write operations
• Audit logs include all required fields (user ID, timestamp, resource, operation, IP address)
• Unit tests verify middleware audit function with mocked requests
• Integration tests confirm audit entries created for each resource type (Create, Update, Delete)
• Direct API access (bypassing frontend) generates proper audit trails
• Frontend audit logging code completely removed after verification period
• Documentation updated to reflect new backend-centric audit architecture
• No audit events lost during migration from frontend to backend

Testing Requirements

1. Unit Tests: Middleware audit function with various request scenarios
2. Integration Tests: Verify audit entry creation for each resource type and operation
3. Comparison Testing: During parallel run, confirm backend captures same events as frontend
4. Security Verification: Attempt to bypass audit logging via various attack vectors

Migration Steps

1. Phase 1: Implement backend middleware audit logging
2. Phase 2: Deploy with both frontend and backend logging active (parallel run)
3. Phase 3: Verify backend logging captures all events correctly
4. Phase 4: Remove frontend audit logging code
5. Phase 5: Final verification and documentation update

Technical Considerations

• JWT token parsing for user identification
• Middleware placement in Express request pipeline
• Performance impact monitoring
• Audit log storage optimization
• Error handling for audit failures (should not break main operations)

Definition of Done

• ✅ All REST API write operations create audit log entries
• ✅ Comprehensive test coverage (unit + integration)
• ✅ Frontend audit code removed
• ✅ Documentation reflects new architecture
• ✅ Security gap eliminated - no way to bypass audit logging

Related Resources

• Current frontend audit implementation: src/providers/dataProvider/resource-callbacks/
• Backend extensions: _extensions/api.js
• Soul-cli middleware documentation
• JWT token handling in current auth system