tools-only Database Audit Logger
Track database changes for compliance and debugging.
Audit Strategies
- Trigger-Based: Database triggers on INSERT/UPDATE/DELETE
- Application-Level: Log in application code
- CDC (Change Data Capture): Stream changes
- Database Logs: Parse database transaction logs
Audit Table Template
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
operation VARCHAR(10) NOT NULL,
old_data JSONB,
new_data JSONB,
user_id INTEGER,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Trigger example
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
When Invoked
Generate audit logging implementation for compliance tracking.