​
ADR-0007: Two complementary audit systems — triggers and business log

​
Status

Accepted

​
Tags

audit, compliance, postgresql, triggers, logging, leave

​
Decision

Run two separate audit systems in parallel, each answering a different question:
SystemTableMechanismQuestion answered
AuditTrailaudit_trailsPostgreSQL AFTER triggers (automatic)“What changed in this row?”
AuditLogaudit_logManual service calls”Who did what and why?”

​
Why

A single system cannot serve both purposes efficiently:
  • Triggers capture complete before/after row snapshots automatically — zero application code needed, atomic with the write transaction. But they have no business context: no user name, no reason, no cross-entity narrative.
  • Business-level logging captures rich context (user name, IP, user agent, reason, cross-entity correlation) but requires explicit integration at every call site.
Both are needed for compliance. They complement rather than duplicate each other.

​
When to use which

Use AuditTrail (triggers) for:
  • Data recovery (“what was the field value before?”)
  • Compliance proof of row changes
  • Debugging (“when did this record change?”)
  • Zero-effort automatic tracking
Use AuditLog (manual) for:
  • Security auditing (“who accessed what from where?”)
  • Business reporting (“approvals per manager in Q4”)
  • Failed attempt logging
  • Non-CRUD actions (exports, logins, permission checks)
  • Cross-entity narratives (“approval affected both request and balance”)

​
Rules for agents

  • Never replace trigger-based audit with application code writes to audit_trails — triggers handle it automatically
  • AuditLog must be called explicitly in use cases for business actions (approve, reject, export, etc.); it does not fire automatically
  • In approval/rejection flows, call BOTH: let the trigger capture the row change, then call auditService.CreateAuditLog(...) for the business context
  • audit_trails is append-only — never UPDATE or DELETE rows
  • audit_log entries must include: UserID, UserName, UserRole, Action, EntityType, EntityID

​
Bad pattern (do not generate)

// Manually writing to audit_trails — trigger handles this
auditTrailRepo.Insert(ctx, AuditTrail{
    TableName: "leave_requests",
    OldData:   oldJSON,
    NewData:   newJSON,
}) // wrong — this is the trigger's job

// Using only AuditTrail for compliance reports — missing business context
// "Who approved this?" cannot be answered from audit_trails alone

​
Good pattern

func (s *ApprovalService) Approve(ctx context.Context, id, approverID string) error {
    request.Status = "APPROVED"
    s.leaveRequestRepo.Update(ctx, request)
    // ↑ trigger auto-logs old/new row to audit_trails

    // business context logged explicitly
    return s.auditService.CreateAuditLog(ctx, CreateAuditLogParams{
        UserID:     approverID,
        Action:     AuditActionApprove,
        EntityType: AuditEntityLeaveRequest,
        EntityID:   id,
        Details:    map[string]interface{}{"comment": "Approved for vacation"},
    })
}