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

1. 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
   );

1. Add indexes for common audit queries:

• CREATE INDEX idxaudittablerecord ON auditlog (tablename, recordid)
• CREATE INDEX idxauditchangedat ON auditlog (changed_at)
• CREATE INDEX idxauditchangedby ON auditlog (changed_by)
• CREATE INDEX idxauditaction ON auditlog (tablename, action)

1. Create the PostgreSQL audit trigger function:

   CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS TRIGGER AS $$
   BEGIN
     IF TG_OP = 'INSERT' THEN
       INSERT INTO audit_log (table_name, record_id, action, new_values, changed_by, client_ip, application_name, transaction_id)
       VALUES (TG_TABLE_NAME, NEW.id::text, 'INSERT', to_jsonb(NEW), current_setting('app.user', true), inet_client_addr