Stats
Actions
Tags
Implements trigger-based audit logging for PostgreSQL and MySQL to track data changes (INSERT/UPDATE/DELETE) with metadata for compliance and security monitoring.
How this skill is triggered — by the user, by Claude, or both
Slash command
/database-audit-logger:implementing-database-audit-loggingThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
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.
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.
psql or mysql CLI for executing audit setup DDLIdentify tables requiring audit logging based on compliance and business needs:
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
);
Add indexes for common audit queries:
CREATE INDEX idx_audit_table_record ON audit_log (table_name, record_id)CREATE INDEX idx_audit_changed_at ON audit_log (changed_at)CREATE INDEX idx_audit_changed_by ON audit_log (changed_by)CREATE INDEX idx_audit_action ON audit_log (table_name, action)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(), current_setting('application_name'), txid_current());
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, changed_by, client_ip, application_name, transaction_id)
VALUES (TG_TABLE_NAME, NEW.id::text, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), current_setting('app.user', true), inet_client_addr(), current_setting('application_name'), txid_current());
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, record_id, action, old_values, changed_by, client_ip, application_name, transaction_id)
VALUES (TG_TABLE_NAME, OLD.id::text, 'DELETE', to_jsonb(OLD), current_setting('app.user', true), inet_client_addr(), current_setting('application_name'), txid_current());
END IF;
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
Attach triggers to each audited table:
CREATE TRIGGER audit_users AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger_func()Pass application-level user context to the database session so audit logs capture the actual application user (not just the database role):
SET LOCAL app.user = '[email protected]'current_setting('app.user', true) in the triggerPartition the audit_log table by month for efficient querying and archival:
CREATE TABLE audit_log (...) PARTITION BY RANGE (changed_at)CREATE TABLE audit_log_2024_01 PARTITION OF audit_log FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')Protect audit log integrity:
pg_audit extension for additional tamper protectionCreate compliance report queries:
SELECT * FROM audit_log WHERE table_name = 'users' AND record_id = '12345' ORDER BY changed_atSELECT * FROM audit_log WHERE changed_by = '[email protected]' ORDER BY changed_at DESCSELECT changed_by, table_name, action, COUNT(*) FROM audit_log WHERE changed_at > NOW() - INTERVAL '1 hour' GROUP BY 1,2,3 HAVING COUNT(*) > 100SELECT * FROM audit_log WHERE EXTRACT(HOUR FROM changed_at) NOT BETWEEN 8 AND 18Set up audit log archival: move audit records older than the retention period to cold storage (S3, Azure Blob). Maintain the archive manifest for retrieval. Typical retention: 1-3 years in database, 7+ years in cold storage for financial data.
| Error | Cause | Solution |
|---|---|---|
| Audit trigger slows INSERT/UPDATE operations | Trigger overhead on high-write tables | Audit only critical columns instead of full rows; use asynchronous audit with pg_notify and a listener process; batch audit writes |
| Audit table consuming excessive disk space | High write volume tables generating millions of audit records | Partition by month; archive old partitions to cold storage; audit only specific columns with WHEN clause on trigger |
current_setting('app.user') returns NULL | Application not setting session variable before database operations | Set default in trigger: COALESCE(current_setting('app.user', true), current_user); add connection pool checkout hook |
| Audit log INSERT fails, blocking application operation | Audit table full, permission error, or constraint violation | Use BEGIN ... EXCEPTION WHEN OTHERS THEN NULL; END in trigger to prevent audit failures from blocking operations; alert on audit failures |
| Cannot determine which columns changed in UPDATE | Full row stored as JSON, no column-level diff | Add changed_columns computation in trigger: compare OLD and NEW field by field; store only changed fields in new_values |
HIPAA-compliant audit logging for a healthcare database: Audit triggers on patient_records, prescriptions, and lab_results tables capture all modifications with practitioner identity. Audit logs are immutable (no UPDATE/DELETE grants), partitioned monthly, and archived to encrypted S3 after 1 year. Quarterly compliance reports show access patterns per practitioner and flag unusual access (patient records accessed without an appointment).
Detecting unauthorized data modifications: Audit log query reveals 500 DELETE operations on the billing table by a service account at 3 AM, outside normal business hours. Alert triggers for bulk operations exceeding 100 rows. Investigation traces the operations to a misconfigured cleanup job. Audit log provides the complete list of deleted records for restoration.
GDPR data access request fulfillment: When a user requests their data access log under GDPR Article 15, the audit system provides a complete history of who accessed or modified their personal data: SELECT changed_by, action, changed_at, changed_columns FROM audit_log WHERE table_name = 'users' AND record_id = '12345' ORDER BY changed_at. The report is generated within the 30-day compliance window.
npx claudepluginhub jeremylongshore/claude-code-plugins-plus-skills --plugin database-audit-loggerImplements trigger-based or application-level audit logging with immutable append-only tables. Useful for regulatory compliance, security audits, debugging data changes, and undo workflows.
Designs immutable, hash-chained audit logs for LGPD compliance (Art. 6, X) with Prisma schema and Next.js middleware. Use for audit logging, accountability, or incident registration.
Generates audit trail infrastructure for .NET entities: IAuditable interface, EF Core SaveChanges interceptor, and automatic population of created/updated fields with user context.