Different approaches to building a robust audit log system

As your project grows beyond the pilot phase and attracts more users, tracking data changes—such as fee updates or system configuration—becomes a critical requirement (Audit Log). However, in a real-world production environment, data changes can originate from multiple sources:

1. Admin UI : Manual updates performed by operators through management consoles.
2. Bulk Imports (Excel/CSV): Large-scale data updates performed periodically.
3. Automated Tasks (Workers/Cronjobs): System-triggered adjustments based on specific logic or schedules.
4. Third-party APIs : Partners or external systems updating data through integrations.
5. Direct Database Operations (SQL Scripts): Developers running manual queries to fix data or apply migrations.

If you have to "remember" to write logging code in all these places, the risk of missing something is very high.

1. Challenges with traditional approaches

Most developers typically consider two common ways to implement an Audit Log:

Approach A: Manual logging in application logic

Fetching the old state, comparing it with the new state, and inserting a record into a logs table within the code.

Workflow:

1. Client sends an update request.
2. App queries the old state from the database.
3. App performs the UPDATE operation.
4. App compares Old vs New to identify changed fields.
5. App inserts the log record into the audit_logs table.
6. App returns success to the Client.

• Pros :
  • Easy to implement for small projects.
  • Simple to include complex business context (e.g., "Reason for change" entered via UI).
• Cons :
  • High Risk : Extremely easy to miss. If a team member forgets to call the log function in a new feature, the audit trail is broken.
  • Code Clutter : Logging logic is mixed with business logic, making the codebase harder to maintain.
  • Performance Impact : Comparison and logging happen during the request, increasing the API response time.

Approach B: Database triggers

Writing trigger scripts directly in the database (PostgreSQL/MySQL) to automatically log changes on every UPDATE/INSERT.

Workflow:

1. Client sends an update request.
2. App executes a single UPDATE command.
3. Database triggers the logging logic automatically.
4. Trigger compares OLD and NEW variables within the same transaction.
5. Trigger inserts the log data into the audit_logs table.
6. Database commits the transaction and returns the result back to the App.

• Pros :
  • Absolute Reliability : Automatically enforced at the database level. Every change is captured regardless of the source or the application logic used.
  • Data Integrity : Ensures the log always matches the database state precisely.
• Cons :
  • Performance Overhead : Triggers run synchronously within the transaction. This extends data locks, potentially causing bottlenecks under high load.
  • Transaction Dependency : If the logging step fails (e.g., disk full, table lock), the main business operation will also fail ( Rollback ).
  • Hard to Scale : Logic is hidden in the DB, making it difficult to debug, test, or extend (e.g., sending logs to Slack or external analytics).

---

2. The CDC (Change Data Capture) solution with Debezium

To solve these issues fundamentally, we need a mechanism to track changes at the storage level. This is where CDC (Change Data Capture) comes in.

System Architecture

Here is the overview of the data flow:

What is CDC?

Instead of intercepting at the application or database engine level, CDC "tails" the database's transaction logs (like WAL in Postgres or Binlog in MySQL) to capture every single change.

Why use Debezium for audit logs?

Centralized Audit Service Architecture:

Thinking about a centralized architecture where multiple services can share a single audit engine:

This is the most significant advantage of the CDC model. Instead of each project managing its own log tables and templates, you can build a standalone service dedicated to Audit Logs.

• Service A, B, C... : Focus solely on business logic and database updates.
• Audit Service : Automatically captures changes from all service databases via CDC, processes them, and stores them in a dedicated log store.

• History Retrieval : When any service (or Frontend) needs to display a change history, they simply call the Audit Service API to fetch beautifully formatted data.

• Pros :

  1. High Performance : Reads changes directly from logs (WAL/Binlog), not impacting the main database performance of business services.
  2. Centralized Management : One single service handles logs for dozens of projects. Update the logic once, and all projects benefit.
  3. Decoupled Architecture : Business services are completely hands-off ("Zero-code" for logging).
  4. Extensibility : Easily stream logs to multiple destinations (Elasticsearch, BigQuery, Slack) without touching the business app.

• Cons :

  1. Infrastructure Complexity : Requires setting up and maintaining Kafka, Zookeeper, and Kafka Connect.
  2. Latency : As an asynchronous process, logs appear a few milliseconds to seconds after the primary change (Eventual Consistency).
  3. Distributed Debugging : Identifying issues across multiple asynchronous layers (Database logs, Debezium status, Kafka lags, and Consumer logic) is more complex than debugging a single SQL transaction.

---

3. Operational cost comparison

Choosing the right solution requires weighing long-term maintenance costs:

Criteria	Application Logic	Database Trigger	CDC (Change Data Capture)
Development Effort	High (must write code everywhere)	Medium (SQL scripts)	Low (one-time configuration)
Infrastructure Cost	Low	Low (but impacts DB CPU)	High (Kafka, Connect, Storage)
Maintenance Cost	Very High (breaks easily)	Medium (hard to debug SQL)	Low (highly stable system)
Operational Complexity	Low	Low	High (requires Kafka Cluster mgmt)

CDC Cost Analysis:

While CDC requires higher initial infrastructure costs (server fees for Kafka and message storage), it provides massive savings in human resources and time. Developers no longer spend 20-30% of their project time maintaining logging logic or optimizing DB performance issues caused by triggers.

---

Summary: Which approach should you choose?

Choosing the right Audit Log strategy depends on your project's maturity and specific needs:

• Stick with traditional approaches (Application Logic / Triggers) if:

• Transition to CDC and Debezium if:

Final Conclusion: While CDC requires more initial setup and infrastructure management, it is the most robust solution for scale, decoupling, and ensuring absolute data transparency. Choose the method that fits your current architecture, but consider CDC as your system scales in complexity.

The sample source code for the CDC-based centralized audit service is being packaged and will be published soon.