Audit Trail vs Audit Log Audit Trail vs Audit Log

Audit Trail vs Audit Log: Key Differences

TL;DR

You have TWO audit systems that serve different purposes:

1. AuditTrail (PostgreSQL AFTER Triggers)

What It Is:

Database-level change tracking - automatically captures row-level CRUD operations via PostgreSQL AFTER triggers. It applies to the leave tables (leave_requests, leave_balances, leave_types), the CRM and orders tables (customers, orders, order_items) and the rental tables, with full row snapshots in JSONB format (customers.embedding is omitted).

Location:

Schema:

Storage:

  • Stored in single centralized table: audit_trails
  • All leave-related changes in one place for easy querying

How It Works:

Automatically - triggered by PostgreSQL AFTER triggers on write operations:
Example: Application updates a leave request status

What It Captures:

  • ✅ Complete row snapshots (full before/after state as JSONB)
  • ✅ When it happened (timestamp to microsecond precision)
  • ✅ What operation (INSERT/UPDATE/DELETE)
  • ✅ Which row (row_id for easy filtering)
  • ✅ Transactional consistency (audit entry rolls back if transaction fails)

What It DOESN’T Capture:

  • ❌ Who made the change (user context is in the row data, but not metadata)
  • ❌ Why the change was made (no business context)
  • ❌ Cross-entity actions (each table audited independently)
  • ❌ User details (name, role, IP, user agent)
  • ❌ Failed operations (only committed changes are logged)
  • ❌ Identity context (application must determine user from authorization)

Example Data:


2. AuditLog (New - Audit Service)

What It Is:

Business-level action logging - manually captures business actions with full context.

Location:

Schema:

Storage:

  • Stored in single centralized table: audit_log
  • All business actions across all entities

How It Works:

Manual - you must explicitly call it:

What It Captures:

  • ✅ Business actions (approve, reject, export, etc.)
  • ✅ User context (name, role, IP, user agent)
  • ✅ Why (details, comments, reasons)
  • ✅ Cross-entity context (can log multiple entities in one action)
  • ✅ Failed attempts (can log even if operation fails)
  • ✅ Non-CRUD actions (export, view, login, etc.)

What It DOESN’T Capture:

  • ❌ Automatic - requires manual integration
  • ❌ Full entity snapshots (only selective fields)

Example Data:


When to Use Which?

Use AuditTrail (BaseRepository) For:

  • ✅ Data recovery - “What was the value before?”
  • ✅ Compliance - “Show all changes to this record”
  • ✅ Debugging - “Who changed this field?”
  • ✅ Automatic tracking - No code changes needed

Use AuditLog (Audit Service) For:

  • ✅ Security auditing - “Who accessed what from where?”
  • ✅ Business reporting - “How many approvals did each manager do?”
  • ✅ Compliance reports - “Show all leave approvals in Q4”
  • ✅ User activity - “What did this user do today?”
  • ✅ Failed attempts - “Who tried to approve without permission?”

Comparison Examples

Scenario: Manager approves a leave request

AuditTrail Captures:

Good for: “What changed in req_123?”

AuditLog Captures:

Good for: “Who approved leave requests today?”

Recommendation: Use BOTH!

They Complement Each Other:

Example Integration:

Result:
  • leave_requests_audit has the request change
  • leave_balances_audit has the balance change
  • audit_log has the approval action linking both

Phase 1: PostgreSQL Audit Trails (Current)

Scope: Leave Tables Only

  • ✅ leave_requests - auto-audited via trigger
  • ✅ leave_balances - auto-audited via trigger
  • ✅ leave_types - auto-audited via trigger
  • ❌ Other tables (orders, customers, products) - Phase 2

Implementation Details:

  • Trigger function: fn_audit_trail() (shared across all audited tables)
  • Per-table triggers: trg_audit_leave_requests, trg_audit_leave_balances, trg_audit_leave_types
  • Idempotent on every startup (safe to re-run migrations)
  • Composite indexes for efficient querying by table + date or table + row

Query Examples:

See PostgreSQL Audit Trail Query Examples for comprehensive patterns.

Phase 2: CRM and Orders (Done)

  • customers, orders and order_items use the same AFTER triggers as leave.
  • customers uses fn_audit_trail_omit('embedding'): the re-generated embedding vector is kept out of old_data/new_data.
  • There is no event-based (outbox/NATS) audit path. The outbox carries domain events only. See ADR-0002.

Still not audited:

  • Products (inventory changes)

Using Both Systems Together

Current State:

  • ✅ AuditTrail (PostgreSQL) - working for leave, rental, CRM (customers) and orders (orders, order_items) tables
  • ⚠️ AuditLog - infrastructure only (needs manual integration)

Integration Pattern:

  1. PostgreSQL AuditTrail captures data changes automatically
  2. AuditLog (manual) adds business context and cross-entity tracking:
  3. Query Both for comprehensive auditing:

Summary

Recommendation: Deploy both! They serve different purposes and complement each other perfectly.

Migration from MongoDB to PostgreSQL

What Changed in the Migration:

Before (MongoDB):
  • AuditTrail stored in per-collection tables (leave_requests_audit, leave_balances_audit)
  • Populated by application BaseRepository code
  • No cross-collection view of all changes
After (PostgreSQL AFTER Triggers):
  • AuditTrail stored in single audit_trails table
  • Populated automatically by database triggers
  • Easy to query all changes across leave tables
  • No application code changes needed
  • Safe to re-run migrations on every startup

No Breaking Changes:

  • AuditLog (Audit Service) remains unchanged
  • Query patterns are slightly different (now using audit_trails table instead of per-collection)
  • Same information captured (before/after snapshots)
  • Same compliance and debugging value