Database Audit Logger

Database Audit Logger

Overview

Implement database audit logging to track all data modifications (INSERT, UPDATE, DELETE) with full before/after values, user identity, timestamps, and application context. This skill supports trigger-based auditing for PostgreSQL and MySQL, change data capture (CDC) patterns, and application-level audit logging.

Prerequisites

• Database credentials with CREATE TABLE, CREATE FUNCTION, and CREATE TRIGGER permissions
• psql or mysql CLI for executing audit setup DDL
• Understanding of applicable compliance requirements (which tables, which operations, retention period)
• Estimated storage for audit logs: plan for 10-30% of the audited table's data volume per year
• Separate tablespace or storage volume for audit data to prevent audit growth from affecting application performance

Instructions

1. Identify tables requiring audit logging based on compliance and business needs:

   • Tables containing PII (users, contacts, addresses) -- GDPR/HIPAA requirement
   • Tables containing financial data (transactions, payments, invoices) -- SOX/PCI-DSS requirement
   • Tables containing access control data (roles, permissions, API keys) -- security requirement
   • Determine which operations to audit per table: INSERT, UPDATE, DELETE, or all three

2. Create the audit log table with comprehensive metadata:

   CREATE TABLE audit_log (
     id BIGSERIAL PRIMARY KEY,
     table_name VARCHAR(100) NOT NULL,
     record_id TEXT NOT NULL,
     action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
     old_values JSONB,
     new_values JSONB,
     changed_columns TEXT[],
     changed_by VARCHAR(100),
     changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     client_ip INET,
     application_name VARCHAR(100),
     transaction_id BIGINT
   );