Add Database Audit Logging with AI Prompts

Create PostgreSQL audit triggers for my tables.

Tables to audit: users, posts, payments

Audit log should capture:

1. Table name
2. Operation (INSERT, UPDATE, DELETE)
3. Old values (for UPDATE/DELETE)
4. New values (for INSERT/UPDATE)
5. User who made the change
6. Timestamp
7. Client IP if available

Create:

1. audit_log table schema
2. Generic trigger function that works for any table
3. Trigger creation for each table
4. Helper function to query audit history

Store old/new values as JSONB for flexibility. Include indexes for common queries (by table, by user, by time).

Add audit logging at the application level.

ORM: Prisma/Sequelize/TypeORM/Drizzle

Create middleware/hooks that:

1. Intercept all write operations
2. Capture before/after state
3. Log the authenticated user
4. Include request context (IP, user agent)
5. Write to audit table or external service

For Prisma specifically:

• Use middleware or Prisma Client extensions
• Handle nested writes correctly
• Capture the user from request context

Output format should include:

• Entity type and ID
• Action performed
• Changed fields only (not entire record)
• Actor information
• Timestamp with timezone

Implement soft delete with full history tracking.

Instead of deleting records:

1. Add deletedAt timestamp column
2. Add deletedBy user reference
3. Filter out deleted records by default
4. Allow admins to view deleted records
5. Implement restore functionality

Also create a history table pattern:

• users_history stores all versions
• Each update creates a new history row
• Can reconstruct state at any point in time

Include:

• Migration to add soft delete columns
• Query helpers to include/exclude deleted
• History table schema and triggers
• Restore function

Create audit logging suitable for compliance requirements.

Compliance needs: SOC2/HIPAA/GDPR/PCI-DSS

Requirements:

1. All access to sensitive data is logged
2. Logs cannot be modified or deleted
3. Logs are retained for required period
4. Can demonstrate who accessed what
5. Failed access attempts are logged

Implement:

1. Read access logging (not just writes)
2. Login/logout audit trail
3. Permission change logging
4. Data export/download logging
5. Failed authentication attempts

Include:

• Log retention policy
• Log integrity verification (checksums)
• Secure log storage recommendations
• Sample compliance report queries