implementing-database-audit-logging

# Database Audit Logger

This skill provides automated assistance for database audit logger tasks.

## Prerequisites

Before using this skill, ensure:
- Database credentials with CREATE TABLE and CREATE TRIGGER permissions
- Understanding of compliance requirements (GDPR, HIPAA, SOX, PCI-DSS)
- Sufficient storage for audit logs (estimate 10-30% of data size)
- Decision on audit log retention period
- Access to database documentation for table schemas
- Monitoring tools configured for audit log analysis

## Instructions

### Step 1: Define Audit Requirements
1. Identify tables requiring audit logging based on compliance needs
2. Determine events to audit (INSERT, UPDATE, DELETE, SELECT for sensitive data)
3. Define which columns contain sensitive data requiring audit
4. Document retention requirements for audit logs
5. Identify users/roles whose actions need auditing

### Step 2: Choose Audit Strategy
1. **Trigger-Based Auditing**: Best for comprehensive row-level tracking
   - Pros: Automatic, no application changes, captures all changes
   - Cons: Performance overhead, complex trigger maintenance
2. **Application-Level Auditing**: Best for selective auditing
   - Pros: Flexible, lower database overhead, easier debugging
   - Cons: Requires application changes, can miss direct database changes
3. **Change Data Capture (CDC)**: Best for real-time streaming
   - Pros: Minimal performance impact, real-time analysis, external processing
   - Cons: Complex setup, requires CDC infrastructure
4. **Native Database Logs**: Best for general monitoring
   - Pros: No setup, captures everything, built-in
   - Cons: High volume, limited retention, difficult to query

### Step 3: Design Audit Table Schema
1. Create audit log table with these core columns:
   - audit_id (primary key), table_name, action (INSERT/UPDATE/DELETE)
   - record_id (reference to audited record), old_values (JSON), new_values (JSON)
   - changed_by (user), changed_at (timestamp), client_ip, application_cont