Database.Middle.How would you track changes (auditing) in a table?

Tracking changes in a table — known as auditing — is essential for data integrity, compliance, and debugging. There are several techniques, depending on how deep and automated you want the auditing to be.

✅ Common Methods to Track Changes (Auditing)

1. Audit Table with Triggers

Create a separate audit log table that stores changes (INSERT, UPDATE, DELETE) via database triggers.

🔧 Example (PostgreSQL):

CREATE TABLE user_audit (
    id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT now(),
    changed_by TEXT -- optionally, who made the change
);

123

CREATE OR REPLACE FUNCTION audit_user_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO user_audit(user_id, action, old_data, new_data)
        VALUES (
            NEW.id,
            'UPDATE',
            to_jsonb(OLD),
            to_jsonb(NEW)
        );
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO user_audit(user_id, action, old_data, new_data)
        VALUES (
            OLD.id,
            'DELETE',
            to_jsonb(OLD),
            NULL
        );
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO user_audit(user_id, action, old_data, new_data)
        VALUES (
            NEW.id,
            'INSERT',
            NULL,
            to_jsonb(NEW)
        );
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_user_changes();

2. Change Data Capture (CDC)

Use database engine-level tools or log-based replication to track changes.

• PostgreSQL: Logical replication or wal2json
• MySQL: Binary log + Debezium (Kafka)
• Oracle: GoldenGate
• SQL Server: CDC or Change Tracking features

✅ Best for real-time stream processing of changes.

3. Temporal Tables (System-Versioned)

Let the database automatically store row history.

• SQL Server and MariaDB support system-versioned temporal tables.
• Tracks valid_from, valid_to dates and maintains history automatically.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL,
    valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_to DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON);

4. Manual History Tables

Maintain a parallel users_history table that you insert into manually in your app whenever data changes.

✅ Simple, but requires discipline in application code.

5. ORM-Level Auditing

Frameworks like Hibernate or Django can auto-track changes:

• Hibernate Envers (Java)
• Django Simple History (Python)
• Entity Framework Audit (C#)

✅ Good for full-stack applications with strong ORM usage.

📦 What to Include in Audit Logs

Field	Purpose
action	Type of change (INSERT, UPDATE, etc.)
timestamp	When the change occurred
user_id	Who made the change (if available)
old_data	Pre-change values (for UPDATE/DELETE)
new_data	New values (for INSERT/UPDATE)
table_name	If auditing multiple tables

---

✅ Summary

Method	Best For	Overhead	Realtime?
Triggers + Audit Table	General auditing, regulatory compliance	Low-Medium	❌
CDC (Change Data Capture)	Stream processing, analytics	Medium	✅
Temporal Tables	Built-in history management	Low	❌
ORM-based Auditing	Apps with heavy ORM use	Low	❌
Manual App-Level Logging	Full control, but error-prone	Medium	❌