implementing-database-audit-logging

•

1.9k2593 months ago

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".

Quick Install

npxskills add jeremylongshore/claude-code-plugins-plus-skills--skill implementing-database-audit-logging

Instructions

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

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

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

Loading…

Related Skills

skill-creator

Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.

367,360

skill-creator

Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.

367,309

1password

Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.

365,915

himalaya

CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).

365,885

Tags & Topics

aiautomationclaude-codedevopsmcpai-agents

Database Audit Logger

Overview

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

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

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

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`

implementing-database-audit-logging

Database Audit Logger

Overview

Prerequisites

Instructions

Related Skills

skill-creator

skill-creator

1password

himalaya

skill-creator

skill-creator

1password

himalaya

Database Audit Logger

Overview

Prerequisites

Instructions

Output

Error Handling

Examples

Resources

Related Skills

skill-creator

skill-creator

1password

himalaya

Related Skills

skill-creator

skill-creator

1password

himalaya