implementing-database-audit-logging

name	implementing-database-audit-logging
description	Process use when you need to track database changes for compliance and security monitoring. This skill implements audit logging using triggers, application-level logging, CDC, or native logs. Trigger with phrases like "implement database audit logging", "add audit trails", "track database changes", or "monitor database activity for compliance".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*)
version	1.0.0
author	Jeremy Longshore <[e-mail]>
license	MIT
compatible-with	claude-code, codex, openclaw
tags	["database","security","monitoring","logging"]

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