Skip to main content
Version: MarketPulse

Audit Log System

Author(s)​

  • Ashik

Last Updated Date​

2026-03-12

SRS References​

  • 3.2.1 User Activity Tracking
  • 3.2.2 Data Change Auditing
  • 3.5.1 Compliance & Security Monitoring
  • 3.5.2 Access Control Audit Trail

Version History​

VersionDateChangesAuthor
1.02026-03-11Initial draft for comprehensive Audit Log SystemAshik

Feature Overview​

Objective:
Implement a production-grade audit logging system that tracks all critical user actions, data changes, and system events across the MarketPulse platform. The system captures Who did What, When with Before/After snapshots—essential for compliance, troubleshooting, and security auditing.

Scope:

  • Track all CRUD operations on critical entities (Dealer, User, Reseller, Config, Source, Invoice, Role, etc.).
  • Capture login/logout events with IP address and User-Agent for security monitoring.
  • Store before/after snapshots for update and delete operations using JSONB.
  • Provide rich filtering capabilities: by entity type, action type, user, date range, and keyword search.
  • Support pagination for efficient handling of large audit log datasets (10,000+ entries).
  • Implement scope-based authorization (auditlog.view) to restrict access to authorized users only.
  • Enable detail expansion to view field-level changes with before/after JSON comparison.

Dependencies:

  • PostgreSQL 18 database with JSONB support
  • Dapper for data access
  • JWT-based authentication system
  • Scope-based authorization middleware
  • ManagementService APIs
  • AuthenticationService APIs

Requirements​

Functional Requirements​

  1. Audit Log Capture

    • Automatically log all critical operations: Create, Update, Delete, Login, Logout, StatusChange, Export, PermissionChange.
    • Capture metadata: User ID, Entity Type, Entity ID, Action Type, Description, Severity, IP Address, User-Agent.
    • Generate unique AuditLogId (UUID) for each entry.
    • Store timestamps in UTC with TIMESTAMPTZ.
    • Support severity levels: Info, Warning, Error, Critical.

  2. Change Tracking

    • Capture before/after snapshots for Update and Delete operations.
    • Store snapshots as JSONB columns directly in the auditlog table.
    • Calculate and store array of changed field names (changedFields).

  3. Audit Log Retrieval

    • Fetch paginated list of audit entries with filtering and search.
    • Retrieve single audit entry by AuditLogId with full details including before/after JSON snapshots.
    • Exclude soft-deleted entries (if applicable).
    • Include actor information (user name, email) via JOIN with userdetails.

  4. Filtering & Search

    • Filter by multiple entity types (Dealer, User, Config, Source, etc.).
    • Filter by multiple action types (Create, Update, Delete, Login, etc.).
    • Filter by specific user (actor).
    • Filter by specific entity instance.
    • Filter by date range (startDate, endDate).
    • Keyword search in description and actor name (case-insensitive ILIKE).
    • Support combining multiple filters simultaneously.

  5. Authorization

    • Require auditlog.view scope to access all audit log endpoints.
    • Enforce JWT Bearer authentication.
    • Future: Support auditlog.export scope for CSV/Excel export.

  6. Pagination

    • Cursor-based pagination for efficient navigation through large datasets.
    • Default: 20 entries per page.
    • Maximum: 100 entries per page (prevent abuse).
    • Return metadata: nextCursor, previousCursor, hasNextPage, hasPreviousPage, totalCount (optional), limit.

Non-Functional Requirements​

  1. Security

    • Immutable audit trail—append-only, no updates or deletes allowed.
    • Redact sensitive data (passwords, API keys, tokens) from before/after snapshots.
    • Enforce scope-based access control strictly.
    • Log security-critical events (failed login attempts, permission escalations) at Warning/Critical severity.

  2. Performance

    • Query response time less than 500ms for cursor-paginated lists (tested with 10,000+ entries).
    • Use composite indexes on (userid, entitytype, createdat DESC) for common queries.
    • List queries exclude JSONB columns to avoid fetching heavy data unnecessarily.
    • Cursor-based queries use indexed columns (createdat, auditlogid) for efficient seek operations.

  3. Scalability

    • Support 2+ years of audit log retention (compliance baseline).
    • Efficient cursor-based pagination for large datasets (no OFFSET performance issues).
    • GIN indexes on JSONB columns for future searchable JSON queries.

  4. Reliability

    • Transactional integrity: audit logs written atomically with business operations (or async fire-and-forget for non-critical actions).
    • Comprehensive error handling with detailed logging.
    • Fallback mechanisms for audit helper failures (log error, continue business operation).

Design Specifications​

UI/UX Design​

(Admin Portal / Operations Dashboard)

  • Audit Log List View:

    • Tabular display with columns: Timestamp (relative: "23d ago"), Actor (name + email), Entity Type, Action, Description, Severity badge.
    • Filter panel: Multi-select dropdowns for Entity Types and Action Types; Date range picker; User autocomplete; Keyword search box.
    • Pagination controls: Previous/Next buttons, limit selector (20/50/100), optional total count.
    • Click on any row to navigate to detail view.

  • Audit Log Detail View:

    • Full entry metadata: AuditLogId, Actor details, Entity info, Action, Description, Severity, IP, User-Agent, Timestamp (absolute + relative).
    • Collapsible sections for Before/After snapshots with JSON diff highlighting (NULL for Create/Login/Logout/Export actions).
    • Changed Fields list with badges.
    • Breadcrumb navigation back to list view.

  • Access Control:

    • Show "Access Denied" page for users without auditlog.view scope.
    • Admin role has scope by default; Ops Managers can be granted access.

Workflow​

Audit Capture Workflow:

  1. User performs action (e.g., Update Dealer Config) via API.
  2. Controller extracts User ID from JWT claims.
  3. For Update/Delete operations: Controller fetches existing entity to capture "before" snapshot.
  4. Controller calls Service to perform business operation.
  5. On success, Controller calls Audit Logger Helper:
    • Serialize beforeData and afterData to JSON (if applicable).
    • Calculate changedFields by comparing object properties.
    • Insert single row into auditlog table with all metadata and snapshots.
    • For Create/Login/Logout: beforeData and afterData remain NULL.
    • For Update: both beforeData and afterData are populated.
    • For Delete: only beforeData is populated.
  6. Return response to user.

Important: Data insertion happens after the business operation succeeds. If the operation fails, no audit log is created.

Insertion Timing by Action​

Action Typeauditlog InsertbeforeData / afterDataWhen Inserted
Create (Dealer, User)✅ Yes❌ NULLAfter successful creation
Update (Config, Dealer)✅ Yes✅ Both populatedAfter successful update
Login✅ Yes❌ NULLAfter authentication succeeds
Logout✅ Yes❌ NULLWhen user logs out
Export✅ Yes❌ NULLAfter export completes
StatusChange✅ Yes✅ Optional (before/after)After status change succeeds

Audit Retrieval Workflow:

  1. Admin/Ops Manager calls GET /audit-logs with filter params and cursor.
  2. Middleware validates JWT and auditlog.view scope → 403 if missing.
  3. Controller calls Service layer to get audit logs with the provided filter.
  4. Service validates pagination params (limit ≤ 100).
  5. Service calls Data Access Layer to retrieve cursor-paginated audit logs.
  6. Data Access Layer builds dynamic SQL WHERE clause based on filters and cursor.
  7. Query executes using cursor-based WHERE clause (e.g., WHERE createdat < cursor_timestamp).
  8. SELECT excludes JSONB columns (beforedata, afterdata) for list performance.
  9. LEFT JOINs userdetails table for actor name/email.
  10. Service maps entities to DTOs, calculates relative time, generates next/previous cursors.
  11. Controller wraps response in CursorPaginatedData<AuditLogSummary>.
  12. Return 200 OK with cursor-paginated results.

Detail Expansion Workflow:

  1. User clicks "View Details" for audit entry.
  2. Client calls GET /audit-logs/{auditLogId}.
  3. Scope validation → 403 if missing.
  4. Service calls Data Access Layer to get audit log details by ID.
  5. Data Access Layer fetches from auditlog table (all columns including beforedata, afterdata, changedfields).
  6. LEFT JOIN userdetails table for actor name/email.
  7. Service deserializes JSONB strings to objects, calculates relative time.
  8. Return AuditLogDetailsResponse with full entry metadata and before/after data.

File Structure​

Recommended Organization:

CommonLibrary/
├── Helper/
│   ├── AuditLog/
│   │   ├── AuditDescriptionTemplates.cs         // Static template definitions (constants)
│   │   ├── AuditDescriptionBuilder.cs           // Template generation logic
│   │   └── AuditChangeDetector.cs               // Before/After comparison utility
│   ├── JwtTokenHelper.cs                        // Existing helper
│   └── PasswordHasher.cs                        // Existing helper
│
ContractLibrary/
├── Common/
│   ├── AuditLog/
│   │   ├── AuditLog.cs            
│
AuthenticationService/
├── Services/
│   └── AuditLog/
│       └── AuditLogHelper.cs                    // Service-specific audit helper
├── DAL/
│   └── AuditLog/
│       └── AuditLogDAL.cs                       // Service-specific DAL
│
DatabaseService/
├── Scripts/
│   ├── 000010-CreateAuditLogTables.sql          // Audit table migration with required scopes

Design Rationale:

  1. CommonLibrary/Helper/AuditLog/

    • Contains pure utility logic with no dependencies
    • Templates are static constants (compile-time safe)
    • Reusable across all services
    • No database access

  2. ContractLibrary/Common/AuditLog/

    • Shared DTOs and entities
    • Enums for type safety
    • Used by all services for consistency

  3. Service-Specific Implementation (ManagementService, AuthenticationService):

    • Each service has its own AuditLogHelper.cs that:
      • Uses templates from CommonLibrary
      • Calls service-specific AuditLogDAL
      • Orchestrates the full audit logging flow
    • Follows existing DAL/Service/Controller pattern
    • Maintains service autonomy and testability

Description Templates​

Purpose: Centralized, human-readable templates for audit log descriptions to ensure consistency across the platform.

Template Categories​

1. ScrapingConfig Changes

Field ChangedTemplate PatternExample Output
DailyRecordCap (increase)Increased daily cap from {before} to {after}"Increased daily cap from 150 to 200"
DailyRecordCap (decrease)Decreased daily cap from {before} to {after}"Decreased daily cap from 200 to 100"
ConfidenceThresholdChanged confidence threshold from {before} to {after}"Changed confidence threshold from 0.70 to 0.85"
DataRetentionDaysChanged data retention from {before} to {after} days"Changed data retention from 90 to 120 days"
IncludeKeywords (add)Added keyword(s): {keywords}"Added keyword(s): 'low miles', 'certified'"
IncludeKeywords (remove)Removed keyword(s): {keywords}"Removed keyword(s): 'salvage'"
ExcludeKeywords (add)Added exclusion keyword(s): {keywords}"Added exclusion keyword(s): 'rebuilt'"
ExcludeKeywords (remove)Removed exclusion keyword(s): {keywords}"Removed exclusion keyword(s): 'flood'"
ConfidencePresetChanged confidence preset from {before} to {after}"Changed confidence preset from Balanced to Strict"
Multiple fields{change1}; {change2}; {change3}"Increased daily cap from 150 to 200; Added keyword: 'warranty'"

2. Dealer Changes

Field ChangedTemplate PatternExample Output
StatusChanged dealer status from {before} to {after}"Changed dealer status from Active to Suspended"
DealerNameUpdated dealer name from '{before}' to '{after}'"Updated dealer name from 'ABC Motors' to 'ABC Auto Group'"
PrimaryEmailUpdated primary email from '{before}' to '{after}'"Updated primary email from 'old@example.com' to 'new@example.com'"
PrimaryPhoneUpdated primary phone from '{before}' to '{after}'"Updated primary phone from '+1234567890' to '+1987654321'"
TimezoneChanged timezone from {before} to {after}"Changed timezone from US/Eastern to US/Central"
DealerLogoUrlUpdated dealer logo"Updated dealer logo"
IsGroupHeadChanged group head status to {after}"Changed group head status to true"
GroupHeadIdAssigned to group head: {name}"Assigned to group head: Premium Auto Group"

3. GeographySetting Changes

Field ChangedTemplate PatternExample Output
GeoModeChanged geography mode from {before} to {after}"Changed geography mode from Radius to Manual"
CenterZipCodeChanged center zipcode from {before} to {after}"Changed center zipcode from 12345 to 67890"
RadiusMilesChanged radius from {before} to {after} miles"Changed radius from 50 to 75 miles"
GeoValues (add)Added {count} location(s): {locations}"Added 3 location(s): Dallas, Houston, Austin"
GeoValues (remove)Removed {count} location(s): {locations}"Removed 2 location(s): San Antonio, Fort Worth"

4. VehicleSetting Changes

Field ChangedTemplate PatternExample Output
MinYear / MaxYearChanged year range from {beforeMin}-{beforeMax} to {afterMin}-{afterMax}"Changed year range from 2016-2024 to 2018-2026"
MinPrice / MaxPriceChanged price range from ${beforeMin}-${beforeMax} to ${afterMin}-${afterMax}"Changed price range from $5,000-$50,000 to $10,000-$75,000"
Makes (add)Added make(s): {makes}"Added make(s): Toyota, Honda"
Makes (remove)Removed make(s): {makes}"Removed make(s): Kia"
BodyTypes (add)Added body type(s): {types}"Added body type(s): SUV, Truck"
BodyTypes (remove)Removed body type(s): {types}"Removed body type(s): Convertible"
CurrencyChanged currency from {before} to {after}"Changed currency from USD to CAD"

5. Scraping Source Changes

ActionTemplate PatternExample Output
Add sourceAdded scraping source: {sourceName}"Added scraping source: Craigslist"
Remove sourceRemoved scraping source: {sourceName}"Removed scraping source: eBay Motors"
Activate sourceActivated scraping source: {sourceName}"Activated scraping source: Facebook Marketplace"
Pause sourcePaused scraping source: {sourceName}"Paused scraping source: Autotrader"
Change frequencyChanged {sourceName} frequency from {before} to {after}"Changed Craigslist frequency from Daily to Weekly"
Change scrape modeChanged {sourceName} mode from {before} to {after}"Changed Facebook mode from Manual to Scheduled"

6. User / Authentication Changes

ActionTemplate PatternExample Output
Login (success)Successful login from {ipAddress}"Successful login from 192.168.1.100"
Login (failure)Failed login attempt from {ipAddress}: {reason}"Failed login attempt from 10.0.0.50: Invalid password"
LogoutUser logged out"User logged out"
Password resetPassword reset requested"Password reset requested"
Email changeUpdated email from '{before}' to '{after}'"Updated email from 'user@old.com' to 'user@new.com'"
Phone changeUpdated phone number"Updated phone number"
Status changeChanged user status from {before} to {after}"Changed user status from Active to Suspended"

7. Role / Permission Changes

ActionTemplate PatternExample Output
Role changeChanged user role from {before} to {after}"Changed user role from Viewer to Editor"
Scope addedAdded scope(s): {scopes}"Added scope(s): dealer.create, dealer.update"
Scope removedRemoved scope(s): {scopes}"Removed scope(s): dealer.delete"
Permission escalation[CRITICAL] Elevated permissions: {details}"[CRITICAL] Elevated permissions: Admin access granted"

8. Export Actions

Export TypeTemplate PatternExample Output
Dealer exportExported dealer list ({count} records)"Exported dealer list (247 records)"
Config exportExported scraping configurations"Exported scraping configurations"
Audit log exportExported audit logs ({count} entries)"Exported audit logs (1,523 entries)"

9. Create Actions

Entity TypeTemplate PatternExample Output
DealerCreated new dealer: {dealerName} ({dealerCode})"Created new dealer: ABC Motors (DL-00123)"
UserCreated new user: {email} (Role: {roleName})"Created new user: john@example.com (Role: Dealer Admin)"
SourceCreated new scraping source: {sourceName}"Created new scraping source: Custom API"
ConfigCreated scraping configuration"Created scraping configuration"

Implementation Guidelines:

  1. Priority Order — When multiple fields change, list most impactful first:

    • Critical: Status changes, Permission escalations
    • High: Email/Phone changes, DailyRecordCap, Confidence settings
    • Medium: Keywords, Geography, Vehicle filters
    • Low: Logo, timezone

  2. Concatenation — Join multiple changes with "; " separator:

    • Example: "Increased daily cap from 150 to 200; Added keyword: 'warranty'"

  3. Sensitive Data Exclusion — Never include:

    • Passwords (before or after)
    • API keys
    • Security tokens
    • Full credit card numbers

  4. String Formatting — Wrap string values in single quotes for clarity:

    • ✅ "Updated dealer name from 'ABC Motors' to 'ABC Auto Group'"
    • ❌ "Updated dealer name from ABC Motors to ABC Auto Group"

  5. Pluralization — Use correct singular/plural forms:

    • ✅ "Added 1 keyword: 'certified'"
    • ✅ "Added 3 keywords: 'certified', 'warranty', 'low miles'"

  6. Null Handling — Display "null" or "unlimited" for null values:

    • Example: "Changed daily cap from unlimited to 200"

Storage Location:

  • Templates stored as static constants in CommonLibrary/Helper/AuditLog/AuditDescriptionTemplates.cs
  • Builder logic in CommonLibrary/Helper/AuditLog/AuditDescriptionBuilder.cs
  • Used by service-specific AuditLogHelper.cs in each microservice

Data Models​

Entity: AuditLogEntity

public class AuditLogEntity
{
    public Guid AuditLogId { get; set; }
    public Guid UserId { get; set; }
    public AuditSubjectType AuditSubjectType { get; set; }
    public AuditActionType AuditActionType { get; set; }
    public string Description { get; set; } = string.Empty;
    public string? IpAddress { get; set; }
    public string? UserAgent { get; set; }
    public DateTime CreatedAt { get; set; }
    
    // Change tracking fields (JSONB)
    public string? BeforeData { get; set; }  // JSON string
    public string? AfterData { get; set; }   // JSON string
    public List<string>? ChangedFields { get; set; }  // Array of changed field names
    
    // Navigation properties (populated via JOIN)
    public string? ActorName { get; set; }
    public string? ActorEmail { get; set; }
}

Enums:

public enum AuditSubjectType
{
    Dealer = 1,
    Reseller,
    Config,
    Source,
    ScrapingJob,
    Invoice,
    Export,
    Role
}

public enum AuditActionType
{
    Create = 1,
    Update,
    Delete,
    Export,
    Login,
    Logout,
    StatusChange,
    PasswordReset
}

DTO: AuditLogSummary

public record AuditLogSummary
{
    public Guid AuditLogId { get; init; }
    public Guid UserId { get; init; }
    public string ActorName { get; init; } = string.Empty;
    public string ActorEmail { get; init; } = string.Empty;
    public AuditSubjectType AuditSubjectType { get; init; }
    public AuditActionType AuditActionType { get; init; }
    public string Description { get; init; } = string.Empty;
    public DateTime CreatedAt { get; init; }
    public string RelativeTime { get; init; } = string.Empty; // "23d ago"
}

DTO: AuditLogDetailsResponse

public record AuditLogDetailsResponse
{
    public Guid AuditLogId { get; init; }
    public Guid UserId { get; init; }
    public string ActorName { get; init; } = string.Empty;
    public string ActorEmail { get; init; } = string.Empty;
    public AuditSubjectType AuditSubjectType { get; init; }
    public AuditActionType AuditActionType { get; init; }
    public string Description { get; init; } = string.Empty;
    public string? IpAddress { get; init; }
    public string? UserAgent { get; init; }
    public DateTime CreatedAt { get; init; }
    public string RelativeTime { get; init; } = string.Empty;
    public object? BeforeData { get; init; } // Deserialized JSON
    public object? AfterData { get; init; }  // Deserialized JSON
    public List<string>? ChangedFields { get; init; }
}

DTO: AuditLogFilter

public record AuditLogFilter : CursorPaginationFilter
{
    public List<AuditSubjectType>? EntityTypes { get; init; }
    public List<AuditActionType>? ActionTypes { get; init; }
    public Guid? UserId { get; init; }
    public string? SearchKeyword { get; init; }
}

Base DTO: CursorPaginationFilter

public record CursorPaginationFilter
{
    // Use one of these: NextCursor to get items AFTER the given cursor,
    // or PreviousCursor to get items BEFORE the given cursor.
    public string? NextCursor { get; init; }   // fetch next page (older/newer depending on sort)
    public string? PreviousCursor { get; init; }  // fetch previous page

    // Page size
    public int Limit { get; init; } = 20;
}

Response Model: CursorPaginatedData

public class CursorPaginatedData<T>
{
    public List<T> Data { get; set; } = [];

    public string? NextCursor { get; set; }
    public string? PreviousCursor { get; set; }

    public bool HasNextPage { get; set; }
    public bool HasPreviousPage { get; set; }

    public int? TotalCount { get; set; }

    public int Limit { get; set; }
}

Database Schema:

-- auditlog table (unified table with all fields)
CREATE TABLE IF NOT EXISTS auditlog (
    auditlogid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    userid UUID NOT NULL,
    auditsubjecttype INTEGER NOT NULL,
    auditactiontype INTEGER NOT NULL,
    description TEXT NOT NULL,
    ipaddress VARCHAR(45),
    useragent TEXT,
    beforedata JSONB,
    afterdata JSONB,
    changedfields TEXT[],
    createdat TIMESTAMPTZ NOT NULL DEFAULT now(),
    
    CONSTRAINT fkauditlog_user FOREIGN KEY (userid) 
        REFERENCES userdetails(userid) ON DELETE RESTRICT,
    CONSTRAINT chkauditlog_entitytype CHECK (entitytype BETWEEN 1 AND 8),
    CONSTRAINT chkauditlog_actiontype CHECK (actiontype BETWEEN 1 AND 8)
);

-- Indexes for efficient querying
CREATE INDEX IF NOT EXISTS idxauditlog_userid 
    ON auditlog(userid);
CREATE INDEX IF NOT EXISTS idxauditlog_entitytype 
    ON auditlog(entitytype);
CREATE INDEX IF NOT EXISTS idxauditlog_actiontype 
    ON auditlog(actiontype);
CREATE INDEX IF NOT EXISTS idxauditlog_createdat 
    ON auditlog(createdat DESC);
CREATE INDEX IF NOT EXISTS idxauditlog_composite 
    ON auditlog(userid, entitytype, createdat DESC);

-- GIN indexes for JSONB columns (for future searchable JSON queries)
CREATE INDEX IF NOT EXISTS idxauditlog_beforedata 
    USING GIN (beforedata);
CREATE INDEX IF NOT EXISTS idxauditlog_afterdata 
    USING GIN (afterdata);

API Interfaces​

EndpointMethodPayloadResponseStatus Codes
/audit-logsGETAuditLogFilterCursorPaginatedData<AuditLogSummary>200, 400, 403, 500
/audit-logs/{auditLogId}GETPath: auditLogId (Guid)ResponseWithData<AuditLogDetailsResponse>200, 403, 404, 500

API Request/Response Examples​

Get Paginated Audit Logs (with filtering)​

Request:

GET /audit-logs?entityTypes=1,4&actionTypes=2&searchKeyword=keyword&limit=20&nextCursor=eyJjcmVhdGVkYXQiOiIyMDI2LTAyLTE2VDAxOjMwOjAwWiJ9
Authorization: Bearer <token>

Note: The cursor is a base64-encoded JSON containing the timestamp and ID of the last item. For the first request, omit both nextCursor and previousCursor.

Response (200 OK):

{
  "data": [
    {
      "auditLogId": "123e4567-e89b-12d3-a456-426614174000",
      "userId": "987e6543-e21b-12d3-a456-426614174999",
      "actorName": "Sarah Chen",
      "actorEmail": "sarah.chen@example.com",
      "auditSubjectType": 4,
      "auditActionType": 2,
      "description": "Added keyword: 'low miles'",
      "createdAt": "2026-02-16T01:30:00Z",
      "relativeTime": "23d ago"
    },
    {
      "auditLogId": "234e5678-e89b-12d3-a456-426614174001",
      "userId": "987e6543-e21b-12d3-a456-426614174999",
      "actorName": "Sarah Chen",
      "actorEmail": "sarah.chen@example.com",
      "auditSubjectType": 1,
      "auditActionType": 2,
      "description": "Increased daily cap from 150 to 200",
      "createdAt": "2026-02-16T01:30:00Z",
      "relativeTime": "23d ago"
    }
  ],
  "nextCursor": "eyJjcmVhdGVkYXQiOiIyMDI2LTAyLTE2VDAxOjMwOjAwWiIsImlkIjoiMjM0ZTU2NzgtZTg5Yi0xMmQzLWE0NTYtNDI2NjE0MTc0MDAxIn0=",
  "previousCursor": null,
  "hasNextPage": true,
  "hasPreviousPage": false,
  "totalCount": 1247,
  "limit": 20
}

Error Response (400 Bad Request):

{
  "status": 400,
  "message": "Invalid pagination parameters: limit must be between 1 and 100."
}

Error Response (403 Forbidden):

{
  "status": 403,
  "message": "Access denied. Required scope: auditlog.view"
}

Get Audit Log Details​

Request:

GET /audit-logs/123e4567-e89b-12d3-a456-426614174000
Authorization: Bearer <token>

Response (200 OK):

{
  "status": 200,
  "message": "Audit log details retrieved successfully.",
  "data": {
    "auditLogId": "123e4567-e89b-12d3-a456-426614174000",
    "userId": "987e6543-e21b-12d3-a456-426614174999",
    "actorName": "Sarah Chen",
    "actorEmail": "sarah.chen@example.com",
    "auditSubjectType": 4,
    "auditActionType": 2,
    "description": "Added keyword: 'low miles'",
    "ipAddress": "192.168.1.100",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/122.0.0.0",
    "createdAt": "2026-02-16T01:30:00Z",
    "relativeTime": "23d ago",
    "beforeData": {
      "scrapingConfigId": "456e7890-e21b-12d3-a456-426614174888",
      "dealerId": "789e0123-e45b-12d3-a456-426614174111",
      "includeKeywords": ["certified", "warranty"],
      "excludeKeywords": ["salvage", "rebuilt"],
      "dailyRecordCap": 150,
      "confidenceThreshold": 0.70
    },
    "afterData": {
      "scrapingConfigId": "456e7890-e21b-12d3-a456-426614174888",
      "dealerId": "789e0123-e45b-12d3-a456-426614174111",
      "includeKeywords": ["certified", "warranty", "low miles"],
      "excludeKeywords": ["salvage", "rebuilt"],
      "dailyRecordCap": 150,
      "confidenceThreshold": 0.70
    },
    "changedFields": ["includeKeywords"]
  }
}

Error Response (404 Not Found):

{
  "status": 404,
  "message": "Audit log details not found for this entry."
}

Acceptance Criteria​

  • ✅ All CRUD operations on critical entities (Dealer, User, Config) automatically create audit entries.
  • ✅ Login/Logout events logged with IP address, User-Agent, and timestamp.
  • ✅ Update operations capture before/after snapshots with changedFields array.
  • ✅ Filtering works for all combinations: entity types, actions, keyword search.
  • ✅ Cursor-based pagination handles 10,000+ entries efficiently (less than 500ms per page).
  • ✅ Only users with auditlog.view scope can access endpoints (403 otherwise).
  • ✅ Relative time displayed correctly ("23d ago", "5h ago", "just now").
  • ✅ Detail view shows JSON diff for configuration changes with before/after comparison.
  • ✅ Sensitive data (passwords, API keys) never logged in before/after snapshots.
  • ✅ Audit logs are immutable (append-only, no updates/deletes).

Deployment Considerations​

Infrastructure​

  • PostgreSQL 18 with JSONB support (already in place).
  • Ensure sufficient disk space for 2+ years of audit log retention.
  • GIN indexes on JSONB columns for future full-text search capabilities.
  • Database backup strategy includes auditlog table.

Rollout Steps​

  1. Database Migration:

    • Deploy 000010-CreateAuditLogTables.sql to create auditlog table with all fields.
    • Verify indexes created successfully (including GIN indexes on JSONB columns).
    • Validate constraints with test data.

  2. Backend Deployment:

    • Deploy Audit Logger Helper utility to Common Library.
    • Deploy Data Access Layer, Service Layer, and API Controller to Management Service.
    • Deploy DTOs and Entities to Contract Library.
    • Restart services in staging environment.

  3. Scope Configuration:

    • Assign scope to Admin role via role-scope mapping.
    • Verify scope enforcement via API tests.

  4. Integration Instrumentation:

    • Phase 1: Instrument high-priority API endpoints (Authentication, Dealer Management).
    • Phase 2: Instrument medium-priority API endpoints (Scraping, User Management).
    • Phase 3: Instrument low-priority API endpoints (Role Management, Reseller Management).
    • Validate audit capture after each phase.

  5. Monitoring:

    • Set up OpenTelemetry metrics for audit log write latency.
    • Configure alerts for audit log write failures (critical operations).
    • Monitor database table growth rate.

  6. Production Rollout:

    • Enable feature flag for audit logging (if applicable).
    • Monitor for 48 hours in production.
    • Verify no performance degradation on business operations.
    • Announce feature availability to admin users.

Risks & Mitigations​

RiskImpactMitigation
Audit log write failures block user operationsCriticalImplement async fire-and-forget for non-critical actions; only block on critical security events (login, permission changes).
Database storage exhaustion from excessive loggingHighImplement 2-year retention policy; automated archival to cold storage after 1 year; monitor disk usage with alerts.
Performance degradation from JSONB queriesMediumJSONB columns only fetched when needed (detail expansion endpoint); use GIN indexes for searchable JSON; keep list queries lightweight by excluding JSONB fields.
Sensitive data leakage in before/after snapshotsCriticalWhitelist fields for serialization; never log passwords, API keys, tokens; code review all AuditLogHelper calls.
Insufficient filtering causes slow queriesMediumComposite indexes on common filter combinations; limit max to 100 entries per request; recommend filters over full scans.
Scope bypass vulnerabilityHighComprehensive unit tests for authorization middleware; security audit of all audit log endpoints.

Future Enhancements​

  1. Audit Log Export:

    • Add GET /audit-logs/export?format=csv endpoint.
    • Reuse existing ExcelService pattern for XLSX export.
    • Include filters in export (same filter params as list endpoint).
    • Scope: auditlog.export (separate from auditlog.view).

  2. Retention & Archival Automation:

    • Background job to archive audit logs older than 1 year to S3/cold storage.
    • Background job to hard-delete audit logs older than 2 years (compliance cutoff).
    • Configurable retention periods per entity type.

  3. Real-Time Audit Log Streaming:

    • WebSocket/SignalR endpoint for live audit log feed.
    • Dashboard widget showing recent audit activity (last 24 hours).
    • Push notifications for Critical severity events.

  4. Audit Analytics Dashboard:

    • Summary metrics: Total entries per day/week/month, Top actors, Top entity types, Action type distribution.
    • Charts: Timeline of audit activity, Severity breakdown (pie chart), Entity type heatmap.
    • Anomaly detection: Flag unusual patterns (e.g., 100+ login failures from same IP).

  5. Enhanced JSON Search:

    • Full-text search within JSONB before/after snapshots.
    • Query builder UI for complex JSONB path queries (e.g., afterData.includeKeywords @> '["low miles"]').
    • Indexed JSONB fields for faster searches.

  6. Audit Log Replay:

    • "Undo" functionality for certain operations (e.g., restore deleted dealer using before snapshot).
    • "Replay" functionality to recreate sequence of actions for debugging.

  7. Compliance Reports:

    • Pre-built report templates: User access history, Data modification summary, Failed login attempts.
    • Scheduled report generation (daily/weekly/monthly) sent via email.
    • PDF export with timestamp and digital signature.

  8. Machine Learning-Based Anomaly Detection:

    • Train model on normal audit patterns.
    • Flag anomalous behavior: Unusual login times, Rapid succession of deletes, Permission escalations.
    • Integration with security monitoring tools (SIEM).

Appendix​

Implementation Patterns​

  • LoginHistory Pattern: Existing login tracking with IP address and User-Agent capture
  • RefinedDataChangelog Pattern: Field-level change detection and diff calculation
  • CRUD Controller Pattern: Standard create/read/update/delete with authorization
  • Common Response Wrappers: ResponseWithData<T> and CursorPaginatedData<T> patterns

Glossary​

  • Audit Log: Immutable record of a user action or system event.
  • Entity Type: Category of resource affected (e.g., Dealer, User, Config).
  • Action Type: Type of operation performed (e.g., Create, Update, Delete).
  • Severity: Criticality level of the event (Info, Warning, Error, Critical).
  • Before/After Snapshot: JSONB representation of entity state before and after operation.
  • Changed Fields: Array of property names that differ between before and after snapshots.
  • Relative Time: Human-readable time elapsed since event (e.g., "23d ago").
  • Scope: Permission required to access audit log endpoints (auditlog.view).