Audit Trail vs Audit Log: Key Differences

TL;DR

You have TWO audit systems that serve different purposes:

Feature	`AuditTrail` (PostgreSQL)	`AuditLog` (Audit Service)
Purpose	Database-level change tracking	Business-level action logging
Scope	Row-level CRUD operations	Cross-entity business actions
Location	Centralized `audit_trails` table	Single `audit_log` table
Automatic	✅ Yes (via AFTER triggers)	❌ No (manual integration)
Granularity	Entity-level (leave tables)	Action-level
Use Case	”What changed in this record?"	"Who approved this leave request?“

1. AuditTrail (PostgreSQL AFTER Triggers)

What It Is:

Database-level change tracking - automatically captures row-level CRUD operations via PostgreSQL AFTER triggers. This MVP implementation applies to leave tables (leave_requests, leave_balances, leave_types) with full row snapshots in JSONB format.

Location:

backend/go/internal/shared/infrastructure/database/postgresql/migrations/migrator.go

Schema:

CREATE TABLE audit_trails (
    id UUID PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,     -- e.g., 'leave_requests'
    operation VARCHAR(10) NOT NULL,       -- INSERT, UPDATE, DELETE
    row_id TEXT NOT NULL,                 -- UUID of affected row
    old_data JSONB,                       -- Complete row state before change (NULL for INSERT)
    new_data JSONB,                       -- Complete row state after change (NULL for DELETE)
    changed_at TIMESTAMPTZ NOT NULL       -- When the change occurred
);

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:

Application Code
      ↓
INSERT/UPDATE/DELETE on leave_requests
      ↓
PostgreSQL AFTER Trigger (fn_audit_trail)
      ↓
INSERT into audit_trails (automatic)
      ↓
Same Transaction (atomic)

Example: Application updates a leave request status

-- Your application code executes:
UPDATE leave_requests SET status = 'APPROVED' WHERE id = 'req_123';

-- PostgreSQL trigger fires automatically and executes:
INSERT INTO audit_trails (id, table_name, operation, row_id, old_data, new_data, changed_at)
VALUES (
  gen_random_uuid(),
  'leave_requests',
  'UPDATE',
  'req_123',
  '{"id":"req_123","status":"PENDING",...}',
  '{"id":"req_123","status":"APPROVED",...}',
  CURRENT_TIMESTAMP
);

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:

{
  "id": "01HKG123...",
  "table_name": "leave_requests",
  "operation": "UPDATE",
  "row_id": "req_123",
  "old_data": {
    "id": "req_123",
    "user_id": "user_789",
    "status": "PENDING",
    "start_date": "2025-12-26",
    "end_date": "2025-12-30",
    "total_days": 5,
    "created_at": "2025-12-20T08:00:00Z"
  },
  "new_data": {
    "id": "req_123",
    "user_id": "user_789",
    "status": "APPROVED",
    "start_date": "2025-12-26",
    "end_date": "2025-12-30",
    "total_days": 5,
    "approver_id": "user_456",
    "created_at": "2025-12-20T08:00:00Z",
    "approved_at": "2025-12-26T10:00:00Z"
  },
  "changed_at": "2025-12-26T10:00:00Z"
}

2. AuditLog (New - Audit Service)

What It Is:

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

Location:

backend/go/internal/modules/leave/services/audit_service.go
backend/go/internal/core/domain/audit_log.go

Schema:

type AuditLog struct {
    ID          string                 // Audit record ID
    Timestamp   time.Time              // When action occurred
    UserID      string                 // Who performed action
    UserName    string                 // User's display name
    UserRole    string                 // User's role (staff, admin, etc.)
    Action      AuditAction            // APPROVE, REJECT, CREATE, etc.
    EntityType  AuditEntityType        // LEAVE_REQUEST, LEAVE_TYPE, etc.
    EntityID    string                 // Which entity was affected
    Before      map[string]interface{} // State before (selective fields)
    After       map[string]interface{} // State after (selective fields)
    Details     map[string]interface{} // Additional context
    IPAddress   string                 // User's IP
    UserAgent   string                 // Browser/client info
    SessionID   string                 // Session identifier
    RequestID   string                 // Request trace ID
}

Storage:

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

How It Works:

Manual - you must explicitly call it:

// You must manually call:
auditService.CreateAuditLog(ctx, CreateAuditLogParams{
    UserID:     approverID,
    UserName:   "John Doe",
    UserRole:   "manager",
    Action:     AuditActionApprove,
    EntityType: AuditEntityLeaveRequest,
    EntityID:   requestID,
    Before:     map[string]interface{}{"status": "PENDING"},
    After:      map[string]interface{}{"status": "APPROVED"},
    Details:    map[string]interface{}{"comment": "Approved for vacation"},
    IPAddress:  "192.168.1.1",
    UserAgent:  "Mozilla/5.0...",
})

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:

{
  "_id": "audit_01HKG...",
  "timestamp": "2025-12-26T10:00:00Z",
  "user_id": "user_456",
  "user_name": "John Doe",
  "user_role": "manager",
  "action": "APPROVE",
  "entity_type": "LEAVE_REQUEST",
  "entity_id": "req_123",
  "before": {
    "status": "PENDING"
  },
  "after": {
    "status": "APPROVED",
    "approver_id": "user_456"
  },
  "details": {
    "comment": "Approved for vacation",
    "total_days": 5
  },
  "ip_address": "192.168.1.1",
  "user_agent": "Mozilla/5.0...",
  "session_id": "sess_789",
  "request_id": "req_trace_abc"
}

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:

Collection: leave_requests_audit
{
  "before": { "id": "req_123", "status": "PENDING", ... },
  "after": { "id": "req_123", "status": "APPROVED", "approver_id": "user_456", ... },
  "changed_by": "user_456",
  "operation": "update"
}

Good for: “What changed in req_123?”

AuditLog Captures:

Collection: audit_log
{
  "user_name": "John Doe",
  "user_role": "manager",
  "action": "APPROVE",
  "entity_type": "LEAVE_REQUEST",
  "entity_id": "req_123",
  "details": { "comment": "Approved for vacation" },
  "ip_address": "192.168.1.1"
}

Good for: “Who approved leave requests today?”

Recommendation: Use BOTH!

They Complement Each Other:

AuditTrail (Automatic)          AuditLog (Manual)
        ↓                              ↓
   Data Changes                  Business Actions
        ↓                              ↓
   Per-Entity                    Cross-System
        ↓                              ↓
  "What changed?"              "Who did what?"

Example Integration:

func (s *ApprovalService) ApproveRequest(ctx context.Context, requestID, approverID, approverName, comment string) error {
    // 1. Update leave request
    // ✅ AuditTrail automatically captures this in leave_requests_audit
    request.Status = "APPROVED"
    s.leaveRequestRepo.Update(ctx, request, approverID)

    // 2. Deduct balance
    // ✅ AuditTrail automatically captures this in leave_balances_audit
    s.leaveBalanceRepo.DeductBalance(ctx, balanceID, days)

    // 3. Log business action
    // ✅ AuditLog manually captures the approval action
    s.auditService.CreateAuditLog(ctx, CreateAuditLogParams{
        Action: AuditActionApprove,
        EntityType: AuditEntityLeaveRequest,
        EntityID: requestID,
        Details: map[string]interface{}{
            "comment": comment,
            "affected_balance": balanceID,
        },
    })
}

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: Expansion Plan (Future)

Extend to Other Tables:

Orders and order items (business-critical)
Customers (CRM operations)
Products (inventory changes)

Async Audit Option:

Event-based outbox pattern for high-volume tables
Keep sync approach for leave (human-scale operations)

Using Both Systems Together

Current State:

✅ AuditTrail (PostgreSQL) - working for leave tables
⚠️ AuditLog - infrastructure only (needs manual integration)

Integration Pattern:

PostgreSQL AuditTrail captures data changes automatically

AuditLog (manual) adds business context and cross-entity tracking:

func (s *ApprovalService) ApproveRequest(ctx context.Context, requestID, approverID string) error {
    // 1. Update leave request
    // ✅ PostgreSQL trigger auto-logs this to audit_trails
    request.Status = "APPROVED"
    s.leaveRequestRepo.Update(ctx, request)

    // 2. Create business audit log
    // ✅ Manual AuditLog adds context
    auditService.CreateAuditLog(ctx, CreateAuditLogParams{
        Action: AuditActionApprove,
        EntityType: AuditEntityLeaveRequest,
        EntityID: requestID,
        Details: map[string]interface{}{
            "approver_id": approverID,
            "comment": "Approved for vacation",
        },
    })
}

Query Both for comprehensive auditing:

-- PostgreSQL: Get all changes to a leave request
SELECT * FROM audit_trails
WHERE table_name = 'leave_requests' AND row_id = 'req_123'
ORDER BY changed_at DESC;

-- Go: Get business context for the approval
auditLog := auditService.GetAuditLogs(ctx, AuditLogParams{
    EntityType: "LEAVE_REQUEST",
    EntityID: "req_123",
    Action: "APPROVE",
})

Summary

Question	PostgreSQL AuditTrail	AuditLog (Biz Logic)
Already working?	✅ Yes (Phase 1)	❌ No (needs integration)
Automatic?	✅ Yes (triggers)	❌ No
What changed?	✅ Complete JSONB snapshots	⚠️ Selective fields
Who did it?	⚠️ In row data (approver_id)	✅ Full user context
Why?	❌ No	✅ Yes (details, comments)
Where from?	❌ No	✅ Yes (IP, user agent)
Business actions?	❌ No	✅ Yes
Cross-entity?	❌ No (per-table)	✅ Yes
Queryable?	✅ SQL (centralized)	✅ SQL (centralized)

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

​Audit Trail vs Audit Log: Key Differences

​TL;DR

​1. AuditTrail (PostgreSQL AFTER Triggers)

​What It Is:

​Location:

​Schema:

​Storage:

​How It Works:

​What It Captures:

​What It DOESN’T Capture:

​Example Data:

​2. AuditLog (New - Audit Service)

​What It Is:

​Location:

​Schema:

​Storage:

​How It Works:

​What It Captures:

​What It DOESN’T Capture:

​Example Data:

​When to Use Which?

​Use AuditTrail (BaseRepository) For:

​Use AuditLog (Audit Service) For:

​Comparison Examples

​Scenario: Manager approves a leave request

​AuditTrail Captures:

​AuditLog Captures:

​Recommendation: Use BOTH!

​They Complement Each Other:

​Example Integration:

​Phase 1: PostgreSQL Audit Trails (Current)

​Scope: Leave Tables Only

​Implementation Details:

​Query Examples:

​Phase 2: Expansion Plan (Future)

​Extend to Other Tables:

​Async Audit Option:

​Using Both Systems Together

​Current State:

​Integration Pattern:

​Summary

​Migration from MongoDB to PostgreSQL

​What Changed in the Migration:

​No Breaking Changes:

Audit Trail vs Audit Log: Key Differences

TL;DR

1. AuditTrail (PostgreSQL AFTER Triggers)

What It Is:

Location:

Schema:

Storage:

How It Works:

What It Captures:

What It DOESN’T Capture:

Example Data:

2. AuditLog (New - Audit Service)

What It Is:

Location:

Schema:

Storage:

How It Works:

What It Captures:

What It DOESN’T Capture:

Example Data:

When to Use Which?

Use AuditTrail (BaseRepository) For:

Use AuditLog (Audit Service) For:

Comparison Examples

Scenario: Manager approves a leave request

AuditTrail Captures:

AuditLog Captures:

Recommendation: Use BOTH!

They Complement Each Other:

Example Integration:

Phase 1: PostgreSQL Audit Trails (Current)

Scope: Leave Tables Only

Implementation Details:

Query Examples:

Phase 2: Expansion Plan (Future)

Extend to Other Tables:

Async Audit Option:

Using Both Systems Together

Current State:

Integration Pattern:

Summary

Migration from MongoDB to PostgreSQL

What Changed in the Migration:

No Breaking Changes: