丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Database Auditing: Tracking Data Changes

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Database Auditing: Tracking Data Changes

Database auditing tracks data changes for compliance, security, and debugging. A comprehensive audit system records who changed what, when the change occurred, the old and new values, and the context of the change.

Audit Strategies

Trigger-based auditing uses database triggers to capture changes. An audit trigger fires on INSERT, UPDATE, or DELETE operations and writes change records to an audit table. This approach captures all changes regardless of how they reach the database—application code, admin tools, or direct SQL.

Application-level auditing logs changes in the application layer. Each service explicitly writes audit records when it modifies data. This provides richer context (user ID, request ID, business reason) but only captures changes made through the application.

Change Data Capture (CDC) streams database changes to a log (like Kafka) for external consumers. Debezium is the most popular CDC tool. It reads the database transaction log and publishes change events without modifying application code.

Audit Table Design

A minimal audit table includes: audit_id (primary key), table_name, row_id, operation (INSERT, UPDATE, DELETE), old_values (JSON), new_values (JSON), changed_by, changed_at, transaction_id. Consider partitioning by date for query performance.

Include a session context so you know not just who changed data but under what circumstances. Store API endpoint, IP address, and correlation ID if available.

Querying Audit Data

Audit tables grow quickly. Index by table_name + row_id for point queries, by changed_at for time-based queries, and by changed_by for user activity audits. Consider archiving old audit records to cost-effective storage. Set retention policies based on compliance requirements.

Performance Impact

Writing audit records adds latency to every data modification. Consider asynchronous auditing—write to a queue and process audit records in the background. Batch audit writes to reduce database transaction overhead. Monitor audit queue depth to detect processing bottlenecks.

Compliance

Many regulations require audit trails. GDPR requires tracking personal data access. SOX requires financial data change tracking. PCI-DSS requires audit trails for cardholder data. Design your audit system to meet the strictest relevant regulation.

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Auditing: Tracking Data Changes

Database Auditing: Tracking Data Changes