​

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

​

Status

​

Tags

​

Decision

​

Why

• 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.

​

When to use which

• Data recovery (“what was the field value before?”)
• Compliance proof of row changes
• Debugging (“when did this record change?”)
• Zero-effort automatic tracking

• 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"},
    })
}