Audit System Best Practices

Table of Contents

Quick Start
Core Principles
Phase 2: Normalized Audit Schema
Common Patterns
Transaction Grouping
Payload Storage Strategy
- When to Store Payloads
- Payload Offloading
Convenience Views
Anti-Patterns to Avoid
Testing Audit Functionality
Troubleshooting
- Audit Events Not Appearing
- NULL entity_id in Audit Log
Summary Checklist
Additional Resources

This guide provides best practices for using the @Auditable annotation to maintain a clear, consistent audit trail.

Quick Start

@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.APP_PORT,
    id = "#result.id",
    entity = AppPort.class,
    changeReason = "#request.changeReason"
)
@Transactional
public AppPortResponse updateAppPort(Long id, AppPortRequest request) {
    // implementation
}

Core Principles

1. Always Use Entity IDs for CRUD Operations

✅ Correct:

@Auditable(
    operation = AuditOperation.CREATE,
    context = AuditContext.USER_ADMIN,
    id = "#result.id",  // ✅ Use actual entity ID
    entity = UserEntity.class
)
public UserDto createUser(UserRequest request) { ... }

❌ Incorrect:

@Auditable(
    operation = AuditOperation.CREATE,
    context = AuditContext.USER_ADMIN,
    id = "'user'",  // ❌ Don't use constants
    entity = UserEntity.class
)

2. Use AuditContext Constants

Always use AuditContext constants instead of string literals for better consistency and refactoring safety.

✅ Correct:

@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.NORTHBOUND_CONFIG,  // ✅ Type-safe constant
    id = "#result.id",
    entity = NorthboundConfigEntry.class
)

❌ Incorrect:

@Auditable(
    operation = AuditOperation.UPDATE,
    context = "NorthboundConfig",  // ❌ String literal (typo-prone)
    id = "#result.id",
    entity = NorthboundConfigEntry.class
)

3. Use Helpers for Special Cases

For operations that don’t have a single entity ID (imports, bulk operations, global configs), use AuditEntityIdHelper:

Import Operations

@Auditable(
    operation = AuditOperation.IMPORT,
    context = AuditContext.APP_PORT_IMPORT,
    id = "#T(com.friendly.ftconfigsservice.audit.AuditEntityIdHelper).importSession()",
    entity = AppPort.class,
    changeReason = "'Import from file: ' + #file.originalFilename"
)
public ImportResult importAppPorts(MultipartFile file) { ... }

Bulk Operations

@Auditable(
    operation = AuditOperation.BULK_DELETE,
    context = AuditContext.USER_ADMIN,
    id = "#T(com.friendly.ftconfigsservice.audit.AuditEntityIdHelper).bulkOperation()",
    entity = UserEntity.class,
    changeReason = "'Bulk deletion of ' + #ids.size() + ' users'"
)
public BulkResult deleteUsers(List<Long> ids) { ... }

Global Configuration (Singleton Entities)

@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.PROVISION_PORTAL_PARAMS,
    id = "#T(com.friendly.ftconfigsservice.audit.AuditEntityIdHelper).globalConfig('provision-portal')",
    entity = ProvisionPortalParamEntry.class,
    changeReason = "#request.changeReason"
)
public ConfigDto updateGlobalConfig(ConfigDto request) { ... }

4. Provide Change Reasons for Important Operations

Use the changeReason parameter to capture why a change was made, especially for:

Administrative actions
Bulk operations
Configuration changes
Deletions

@Auditable(
    operation = AuditOperation.DELETE,
    context = AuditContext.USER_ADMIN,
    id = "#id",
    entity = UserEntity.class,
    changeReason = "#reason"  // ✅ Capture the reason
)
public void deleteUser(Long id, String reason) { ... }

You can also accept change reasons in request DTOs:

public class ConfigUpdateRequest {
    private String configValue;
    private String changeReason;  // Optional field for users to explain changes
}

@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.ACS_CONFIGURATION,
    id = "#result.id",
    entity = AcsConfigurationEntry.class,
    changeReason = "#request.changeReason"
)
public ConfigDto updateConfig(ConfigUpdateRequest request) { ... }

Phase 2: Normalized Audit Schema

Starting from Phase 2, the audit system uses a normalized database schema that provides significant improvements over the previous design.

Architecture Overview

The Phase 2 schema consists of four tables:

audit_event          - Core event metadata (operation, timestamp, user, etc.)
  ↓ M:N
audit_event_snapshot - Links events to snapshots with BEFORE/AFTER role
  ↓ N:1
audit_snapshot       - Deduplicated entity snapshots (content-based hashing)

audit_change         - Field-level change tracking (field_path, old_value, new_value)

Key Benefits

60% storage savings: Snapshot deduplication using SHA-256 content hashing
80-90% fewer NULL values: Normalized schema with clear semantics
Field-level tracking: Query "who changed field X" with structured data
Timeline capability: Reconstruct complete entity history chronologically
Better performance: Optimized indexes for common queries

Snapshot Deduplication

When multiple events result in the same entity state, the snapshot is stored only once and referenced multiple times:

-- Check deduplication effectiveness
SELECT content_hash, ref_count
FROM audit_snapshot
WHERE ref_count > 1
ORDER BY ref_count DESC
LIMIT 10;

Example: If you update an AppPort and then immediately update it back to the original values, both UPDATE events will reference the same "after" snapshot.

Field-Level Change Tracking

Every change is recorded with the exact field path that changed:

-- Find all changes to a specific field
SELECT ae.occurred_at, ae.username, ac.old_value, ac.new_value
FROM audit_change ac
JOIN audit_event ae ON ac.event_id = ae.id
WHERE ac.field_path = 'description'
  AND ae.entity_type = 'AppPort'
  AND ae.entity_id = '123'
ORDER BY ae.occurred_at DESC;

Field paths support: * Simple fields: description, port, enabled * Nested objects: address.city, config.timeout * Arrays: ports[0].protocol, tags[1]

Timeline API

The Timeline Service provides programmatic access to entity change history:

Get Entity Timeline

Retrieve complete chronological history for a specific entity:

@Autowired
private AuditTimelineService timelineService;

// Get full history for AppPort id=123
EntityTimelineResponse timeline = timelineService.getEntityTimeline("AppPort", "123");

// Response includes:
// - entityType: "AppPort"
// - entityId: "123"
// - totalEvents: 5
// - entries: List<TimelineEntryDto>
//   * eventId: unique event ID
//   * occurredAt: timestamp
//   * operation: CREATE/UPDATE/DELETE
//   * username: who made the change
//   * changeReason: why it was changed
//   * changedFields: ["description", "port"]
//   * beforeSnapshotId: snapshot ID before change (null for CREATE)
//   * afterSnapshotId: snapshot ID after change (null for DELETE)
//   * transactionId: groups related operations

Get Recent Activity by Type

View recent changes across all entities of a type:

// Dashboard: show last 50 AppPort changes
List<TimelineEntryDto> recentActivity =
    timelineService.getRecentActivityByType("AppPort");

Get User Activity

Audit trail for a specific user:

// Show all changes made by admin user
List<TimelineEntryDto> userActions =
    timelineService.getUserActivity("admin@example.com");

Get Transaction Events

View all operations from a single HTTP request:

// Get all changes from request req-abc123
List<TimelineEntryDto> transactionOps =
    timelineService.getTransactionEvents("req-abc123");

Querying Snapshots

Retrieve the actual entity state at a point in time:

-- Get the "after" snapshot from a specific event
SELECT s.content
FROM audit_snapshot s
JOIN audit_event_snapshot es ON s.id = es.snapshot_id
WHERE es.event_id = 12345
  AND es.snapshot_role = 'AFTER';

The content field contains the complete JSON representation of the entity at that moment.

Performance Considerations

Indexes

The Phase 2 schema includes optimized indexes:

idx_event_entity_history (entity_type, entity_id, occurred_at DESC) - fast timeline queries
idx_snapshot_hash (content_hash) - instant deduplication lookups
idx_event_transaction (transaction_id) - group related operations
idx_change_field_path (field_path) - field history queries

Best Practices

Timeline queries: Add LIMIT for entities with many events to avoid slow queries
Field history: Filter by entity_type and entity_id before filtering by field_path
Snapshot content: Avoid SELECT * on audit_snapshot if you only need metadata

Migration from Phase 1

If you’re upgrading from Phase 1 schema:

Automatic: New changesets are applied via Liquibase
Backward compatible: Old views remain available during transition
Gradual migration: Historical data can be backfilled in batches
Validation: Compare row counts and verify deduplication savings

-- Verify migration completeness
SELECT
    (SELECT COUNT(*) FROM audit_log) AS phase1_events,
    (SELECT COUNT(*) FROM audit_event) AS phase2_events;

-- Check deduplication savings
SELECT
    COUNT(*) AS unique_snapshots,
    SUM(ref_count) AS total_references,
    ROUND(100.0 * (1 - COUNT(*) / SUM(ref_count)), 2) AS savings_pct
FROM audit_snapshot;

Common Patterns

CREATE Operations

@Auditable(
    operation = AuditOperation.CREATE,
    context = AuditContext.APP_PORT,
    id = "#result.id",           // Use result because entity doesn't exist yet
    entity = AppPort.class
)
@Transactional
public AppPortResponse createAppPort(AppPortRequest request) {
    AppPort entity = appPortMapper.toEntity(request);
    AppPort saved = repository.save(entity);
    return appPortMapper.toDto(saved);  // Must return entity with ID
}

UPDATE Operations

@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.APP_PORT,
    id = "#id",                  // Use parameter because entity already exists
    entity = AppPort.class,
    changeReason = "#request.changeReason"
)
@Transactional
public AppPortResponse updateAppPort(Long id, AppPortRequest request) {
    AppPort entity = repository.findById(id)
        .orElseThrow(() -> new NotFoundException("AppPort not found: " + id));
    appPortMapper.updateEntity(entity, request);
    AppPort saved = repository.save(entity);
    return appPortMapper.toDto(saved);
}

DELETE Operations

@Auditable(
    operation = AuditOperation.DELETE,
    context = AuditContext.APP_PORT,
    id = "#id",                  // Use parameter
    entity = AppPort.class,
    storePayload = true,         // Capture final state before deletion
    storeDiff = false,           // No diff needed for deletes
    changeReason = "#reason"
)
@Transactional
public void deleteAppPort(Long id, String reason) {
    AppPort entity = repository.findById(id)
        .orElseThrow(() -> new NotFoundException("AppPort not found: " + id));
    repository.delete(entity);
}

IMPORT Operations

@Auditable(
    operation = AuditOperation.IMPORT,
    context = AuditContext.NORTHBOUND_CONFIG_IMPORT,
    id = "#T(AuditEntityIdHelper).importSession()",
    entity = NorthboundConfigEntry.class,
    storePayload = false,        // Large imports - don't store full payload
    storeDiff = false,
    changeReason = "'Import from file: ' + #file.originalFilename"
)
@Transactional
public ImportResult importConfig(MultipartFile file) {
    // Process import
    return result;
}

Transaction Grouping

All audit events from the same HTTP request are automatically grouped by transaction_id (derived from request_id).

You can query related changes:

SELECT * FROM audit_log
WHERE transaction_id = 'abc123'
ORDER BY occurred_at;

Or use the convenience view:

SELECT * FROM v_audit_transaction_groups
WHERE transaction_id = 'abc123';

Payload Storage Strategy

When to Store Payloads

✅ Store for: CRUD operations on business entities
❌ Don’t store for: Bulk operations, large imports, authentication events

// ✅ Store payload for individual entity operations
@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.USER,
    id = "#id",
    entity = UserEntity.class,
    storePayload = true,  // ✅ Good - captures before/after state
    storeDiff = true      // ✅ Good - shows what changed
)

// ❌ Don't store payload for bulk operations
@Auditable(
    operation = AuditOperation.IMPORT,
    context = AuditContext.APP_PORT_IMPORT,
    id = "#T(AuditEntityIdHelper).importSession()",
    entity = AppPort.class,
    storePayload = false,  // ✅ Good - avoids huge payloads
    storeDiff = false
)

Payload Offloading

Payloads larger than audit.max-inline-payload-bytes (default: 65536) are automatically offloaded to filesystem or S3 based on audit.storage-strategy.

No code changes needed - it’s handled automatically by AuditAspect.

Convenience Views

Several views are available for common queries:

Entity History

-- Get full history for a specific entity
SELECT * FROM v_audit_entity_history
WHERE entity_type = 'AppPort'
  AND entity_id = '123'
ORDER BY occurred_at DESC;

Recent Changes

-- Get last 100 changes across all entities
SELECT * FROM v_audit_recent_changes
LIMIT 100;

User Activity

-- Get activity summary for a user
SELECT * FROM v_audit_user_activity
WHERE username = 'admin@example.com';

Transaction Groups

-- Get all operations from a specific transaction
SELECT * FROM v_audit_transaction_groups
WHERE transaction_id = 'req-abc123';

Anti-Patterns to Avoid

❌ Don’t Use entity_id for Counts

// ❌ BAD: entity_id should identify an entity, not represent a count
@Auditable(
    operation = AuditOperation.BULK_UPDATE,
    context = AuditContext.USER_ADMIN,
    id = "#result.items()?.size()",  // ❌ Don't do this
    entity = UserEntity.class
)

Use AuditEntityIdHelper.bulkOperation() instead and put count in payload:

// ✅ GOOD: Use helper for bulk operations
@Auditable(
    operation = AuditOperation.BULK_UPDATE,
    context = AuditContext.USER_ADMIN,
    id = "#T(AuditEntityIdHelper).bulkOperation()",  // ✅ Unique ID
    entity = UserEntity.class,
    changeReason = "'Bulk update of ' + #result.count + ' users'"
)

❌ Don’t Use entity_id for Filenames

// ❌ BAD: Filename is not an entity identifier
@Auditable(
    operation = AuditOperation.IMPORT,
    context = AuditContext.APP_PORT_IMPORT,
    id = "#file.originalFilename",  // ❌ Don't do this
    entity = AppPort.class
)

Use importSession() and put filename in changeReason:

// ✅ GOOD: Use import session helper
@Auditable(
    operation = AuditOperation.IMPORT,
    context = AuditContext.APP_PORT_IMPORT,
    id = "#T(AuditEntityIdHelper).importSession()",  // ✅ Unique session
    entity = AppPort.class,
    changeReason = "'Import from: ' + #file.originalFilename"
)

❌ Don’t Use String Literals for Context

// ❌ BAD: String literals are error-prone
@Auditable(
    operation = AuditOperation.UPDATE,
    context = "AppPort",  // ❌ Typo-prone, refactoring-unsafe
    id = "#result.id",
    entity = AppPort.class
)

Use AuditContext constants:

// ✅ GOOD: Type-safe constants
@Auditable(
    operation = AuditOperation.UPDATE,
    context = AuditContext.APP_PORT,  // ✅ Type-safe, IDE-friendly
    id = "#result.id",
    entity = AppPort.class
)

Testing Audit Functionality

When testing audited methods:

@Test
void testAuditedUpdate() {
    // Arrange
    Long id = 1L;
    AppPortRequest request = new AppPortRequest();
    request.setChangeReason("Test update");

    // Act
    AppPortResponse result = service.updateAppPort(id, request);

    // Assert
    verify(auditPublisher).publishEvent(argThat(event ->
        event.getOperation() == AuditOperation.UPDATE &&
        event.getContext().equals(AuditContext.APP_PORT) &&
        event.getEntityId().equals(String.valueOf(id)) &&
        event.getChangeReason().equals("Test update")
    ));
}

Troubleshooting

Audit Events Not Appearing

Check transaction commit: Audit events are published AFTER_COMMIT. If the transaction rolls back, no audit event is saved.
Check SpEL expressions: Use @Slf4j and check logs for SpEL evaluation failures.
Check entity snapshots: If storePayload=true, ensure the entity can be serialized to JSON.

NULL entity_id in Audit Log

This is expected for:

Import operations (use importSession())
Bulk operations (use bulkOperation())
Global configs (use globalConfig())

For normal CRUD operations, ensure your SpEL expression correctly resolves the ID:

id = "#result.id"     // For CREATE (use result)
id = "#id"            // For UPDATE/DELETE (use parameter)

Summary Checklist

When adding @Auditable:

Use AuditOperation enum (CREATE/UPDATE/DELETE/IMPORT/etc)
Use AuditContext constant (not string literal)
Use proper id expression:
- #result.id for CREATE
- #id for UPDATE/DELETE
- Helper methods for imports/bulk/global
Specify correct entity class
Add changeReason for important operations
Set storePayload=false for bulk/import operations
Ensure method is @Transactional

Additional Resources

Audit System Improvements Proposal
AuditContext class - full list of context constants
AuditEntityIdHelper class - helper methods for special cases
AuditOperation enum - available operation types