Back to portfolio

Case Study | SQL | Audit Evidence

Audit logging and metrics for security automation decisions.

Implemented an audit pattern that records alert handling, remediation decisions, exceptions, failures, and operational outcomes for cloud security workflows.

Implementation Outcomes Production
Input
Decision
Action
Evidence

Challenge

Automation is only trustworthy when teams can explain what happened. Security workflows need to record the finding, decision inputs, action taken, result, and failure state so operations and leadership can review outcomes.

Approach

  • Defined audit fields for finding ID, resource, owner, severity, workflow name, action, status, and timestamp.
  • Captured exception paths, skipped remediation reasons, failed notifications, and manual review states.
  • Designed records to support both detailed investigation and summary reporting.
  • Kept audit writes separate from action logic so failed logging could be handled deliberately.

Implementation Details

The audit model is designed around explainability. Each row should tell the story of a workflow run: what finding came in, what decision was made, what action was attempted, whether it succeeded, and what evidence supports that result. 

automation_audit_log
  finding_id
  workflow_name
  cloud_provider
  resource_id
  owner
  severity
  decision
  action_taken
  result_status
  failure_reason
  created_at

Outcomes

Evidence by default

Workflow runs produce a record of inputs, decisions, action, result, and exception state.

Operational visibility

Security teams can report on notification failures, remediation skips, exception patterns, and workflow volume.

Better troubleshooting

Failures can be grouped by reason instead of being hidden inside ad hoc script output.

Security Controls Demonstrated

  • Gave teams evidence for what automation did and why.
  • Supported operational metrics such as alert volume, remediation outcomes, failures, and exception trends.
  • Made it easier to troubleshoot workflow gaps and improve playbook reliability.

Production Considerations

  • Dashboards for workflow health, failure categories, and remediation cycle time.
  • Retention policy and data classification for audit records.
  • Schema migrations and automated validation for audit table changes.