Audit design

What is Auditing?

Tracking who did what, when in your database. Maintains an immutable record of data access and changes.

---

Why Audit?

Reason	Example
Compliance	FINRA, SOX, HIPAA, GDPR requirements
Security	”Who deleted these records?”
Forensics	Investigate breaches or errors
Debugging	”Why did this order status change?”

---

What to Audit

High Priority (Must Have)

• Authentication (login, logout, failed attempts)
• Authorization changes (role/permission changes)
• Financial transactions (orders, payments, balance changes)
• Admin actions (configuration, user management)
• Data modifications (INSERT, UPDATE, DELETE)

Medium Priority

• Schema changes (ALTER TABLE)
• Bulk operations
• Access to sensitive tables

Low Priority

• All SELECT queries (usually overkill, high volume)

---

Implementation Approaches

1. Trigger-Based Audit

-- Audit log table
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    action VARCHAR(10),  -- INSERT, UPDATE, DELETE
    record_id BIGINT,
    user_id INT,
    old_data JSONB,
    new_data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);
-- Trigger function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, user_id, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.id, current_setting('app.user_id', true), 
            to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Attach to table
CREATE TRIGGER audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

2. Application-Level Audit

• Log in application code before/after database operations
• More control over context captured
• Can write async to separate system

3. Database Built-in Audit

• PostgreSQL: pgAudit extension
• Oracle: Unified Auditing
• MySQL Enterprise: Audit plugin
• SQL Server: SQL Server Audit

---

Best Practices

1. Secure the Audit Table

-- Make append-only
REVOKE UPDATE, DELETE ON audit_log FROM app_user;
-- Or use trigger to prevent deletes

2. Capture Sufficient Context

-- Good: user_id, username, ip_address, request_id, session_id
-- Bad: Only user_id

3. Handle Large Volume

• Partition audit tables by month
• Archive to cold storage regularly
• Consider separate audit database

4. Don’t Over-Audit

• Focus on high-value tables
• Avoid auditing every table

5. Async Writing

• Don’t slow down transactions with sync writes
• Use message queues or background workers

---

Key Takeaways

• Audit ≠ Logging - Audit has legal/compliance implications
• Make it immutable - Append-only, no updates/deletes
• Plan retention - Audit logs themselves need retention policies
• Balance - Too much noise is as bad as too little
• Query pattern: SELECT * FROM audit_log WHERE table_name = 'orders' AND record_id = ?