Project 3: Enterprise Compliance & Automated Event Audit Engine0%

Project 3: Enterprise Compliance & Automated Event Audit Engine

Advanced25 min read•Updated: 2026-09-12

Project 3: Enterprise Compliance & Automated Event Audit Engine

Advanced Capstone Project 3: Enterprise Compliance & Automated Event Audit Engine

In enterprise environments governed by PCI-DSS, HIPAA, and GDPR regulations, database modifications must be permanently recorded in an immutable, forensic audit trail.

In this capstone, you will architect an Automated Event Audit & Compliance Engine utilizing Row-Level Triggers, Immutable JSON Event Logs, and Analytical Anomaly Detection Queries.

1. Database Schema DDL

1CREATE DATABASE IF NOT EXISTS compliance_audit_db;
2USE compliance_audit_db;
3 
4-- 1. Sensitive Master Table: Corporate Payroll & Salaries
5CREATE TABLE employee_compensation (
6    emp_id INT AUTO_INCREMENT PRIMARY KEY,
7    employee_name VARCHAR(100) NOT NULL,
8    department VARCHAR(50) NOT NULL,
9    base_salary DECIMAL(12, 2) NOT NULL,
10    bonus_percentage DECIMAL(4, 2) NOT NULL DEFAULT 0.00,
11    is_active BOOLEAN NOT NULL DEFAULT TRUE,
12    last_modified_by VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
13    last_modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
14) ENGINE = InnoDB;
15 
16-- 2. Immutable Compliance Audit Ledger
17CREATE TABLE compliance_audit_trail (
18    audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
19    table_name VARCHAR(50) NOT NULL,
20    action_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
21    record_id INT NOT NULL,
22    client_user VARCHAR(100) NOT NULL,
23    client_ip VARCHAR(50) NOT NULL,
24    delta_payload JSON NOT NULL,
25    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
26) ENGINE = InnoDB;

2. Enforcing Immutability via Trigger Protection

An audit log is useless if an attacker or rogue administrator can modify or delete audit rows! We enforce Append-Only Immutability directly using triggers that reject UPDATE and DELETE on the audit table:

1DELIMITER //
2 
3-- Reject any attempts to UPDATE audit records!
4CREATE TRIGGER trg_protect_audit_update
5BEFORE UPDATE ON compliance_audit_trail
6FOR EACH ROW
7BEGIN
8    SIGNAL SQLSTATE '45000'
9        SET MESSAGE_TEXT = 'SECURITY VIOLATION: Compliance audit records are immutable and cannot be updated!';
10END //
11 
12-- Reject any attempts to DELETE audit records!
13CREATE TRIGGER trg_protect_audit_delete
14BEFORE DELETE ON compliance_audit_trail
15FOR EACH ROW
16BEGIN
17    SIGNAL SQLSTATE '45000'
18        SET MESSAGE_TEXT = 'SECURITY VIOLATION: Compliance audit records cannot be deleted!';
19END //
20 
21DELIMITER ;

3. Deploying the Change Data Capture (CDC) Triggers

1DELIMITER //
2 
3-- Audit Trigger for INSERT
4CREATE TRIGGER trg_comp_audit_insert
5AFTER INSERT ON employee_compensation
6FOR EACH ROW
7BEGIN
  INSERT INTO compliance_audit_trail (
      table_name, action_type, record_id, client_user, client_ip, delta_payload
  )
  VALUES (
      'employee_compensation',
      'INSERT',
      NEW.emp_id,
      USER(),
      SUBSTRING_INDEX(USER(), '@', -1),
      JSON_OBJECT(
          'new_state', JSON_OBJECT(
              'name', NEW.employee_name,
              'salary', NEW.base_salary,
              'bonus', NEW.bonus_percentage
          )
      )
  );
25END //
26 
27-- Audit Trigger for UPDATE (Captures Before AND After deltas!)
28CREATE TRIGGER trg_comp_audit_update
29AFTER UPDATE ON employee_compensation
30FOR EACH ROW
31BEGIN
  -- Log only if financial fields or active status changed
  IF OLD.base_salary != NEW.base_salary 
     OR OLD.bonus_percentage != NEW.bonus_percentage 
     OR OLD.is_active != NEW.is_active THEN
     
      INSERT INTO compliance_audit_trail (
          table_name, action_type, record_id, client_user, client_ip, delta_payload
      )
      VALUES (
          'employee_compensation',
          'UPDATE',
          NEW.emp_id,
          USER(),
          SUBSTRING_INDEX(USER(), '@', -1),
          JSON_OBJECT(
              'old_state', JSON_OBJECT(
                  'salary', OLD.base_salary,
                  'bonus', OLD.bonus_percentage,
                  'is_active', OLD.is_active
              ),
              'new_state', JSON_OBJECT(
                  'salary', NEW.base_salary,
                  'bonus', NEW.bonus_percentage,
                  'is_active', NEW.is_active
              ),
              'salary_delta', NEW.base_salary - OLD.base_salary
          )
      );
  END IF;
61END //
62 
63DELIMITER ;

4. Testing Compliance & Forensic Analytics

1-- 1. Insert sample staff
2INSERT INTO employee_compensation (employee_name, department, base_salary, bonus_percentage)
3VALUES ('Dr. Elizabeth Shaw', 'Research', 140000.00, 0.10);
4 
5-- 2. Execute salary increase
6UPDATE employee_compensation 
7SET base_salary = 165000.00 
8WHERE emp_id = 1;
9 
10-- 3. Verify that rogue attempts to alter the audit log are BLOCKED!
11-- This will FAIL with custom security exception!
12UPDATE compliance_audit_trail SET client_user = 'hacker' WHERE audit_id = 1;
13-- ERROR 1644 (45000): SECURITY VIOLATION: Compliance audit records are immutable and cannot be updated!

5. Compliance Forensic Reporting Query

Auditors want to see any salary changes exceeding $10,000 within the last 30 days:

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart

Step 1

delta_payload

Step 2

>'$.old_state.salary' AS previous_salary,

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does our compliance system enforce that audit records cannot be tampered with or deleted by rogue administrators?

Question 2

What does SUBSTRING_INDEX(USER(), '@', -1) extract?

Question 3

Why is the salary_delta explicitly calculated and stored in the update audit JSON payload?

Question 4

What happens if a statement updating employee_compensation fails due to a check constraint?

Question 5

Under what regulatory standards is immutable audit logging mandatory?

Related Lessons

Previous Lesson	Next Lesson
Project 2: Multi-Tenant Analytics Warehouse with Partitioning	None

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 3: Enterprise Compliance & Automated Event Audit Engine

Advanced25 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Project 3: Enterprise Compliance & Automated Event Audit Engine

Advanced Capstone Project 3: Enterprise Compliance & Automated Event Audit Engine

In enterprise environments governed by PCI-DSS, HIPAA, and GDPR regulations, database modifications must be permanently recorded in an immutable, forensic audit trail.

In this capstone, you will architect an Automated Event Audit & Compliance Engine utilizing Row-Level Triggers, Immutable JSON Event Logs, and Analytical Anomaly Detection Queries.

1. Database Schema DDL

1CREATE DATABASE IF NOT EXISTS compliance_audit_db;
2USE compliance_audit_db;
3 
4-- 1. Sensitive Master Table: Corporate Payroll & Salaries
5CREATE TABLE employee_compensation (
6    emp_id INT AUTO_INCREMENT PRIMARY KEY,
7    employee_name VARCHAR(100) NOT NULL,
8    department VARCHAR(50) NOT NULL,
9    base_salary DECIMAL(12, 2) NOT NULL,
10    bonus_percentage DECIMAL(4, 2) NOT NULL DEFAULT 0.00,
11    is_active BOOLEAN NOT NULL DEFAULT TRUE,
12    last_modified_by VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
13    last_modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
14) ENGINE = InnoDB;
15 
16-- 2. Immutable Compliance Audit Ledger
17CREATE TABLE compliance_audit_trail (
18    audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
19    table_name VARCHAR(50) NOT NULL,
20    action_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
21    record_id INT NOT NULL,
22    client_user VARCHAR(100) NOT NULL,
23    client_ip VARCHAR(50) NOT NULL,
24    delta_payload JSON NOT NULL,
25    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
26) ENGINE = InnoDB;

2. Enforcing Immutability via Trigger Protection

1DELIMITER //
2 
3-- Reject any attempts to UPDATE audit records!
4CREATE TRIGGER trg_protect_audit_update
5BEFORE UPDATE ON compliance_audit_trail
6FOR EACH ROW
7BEGIN
8    SIGNAL SQLSTATE '45000'
9        SET MESSAGE_TEXT = 'SECURITY VIOLATION: Compliance audit records are immutable and cannot be updated!';
10END //
11 
12-- Reject any attempts to DELETE audit records!
13CREATE TRIGGER trg_protect_audit_delete
14BEFORE DELETE ON compliance_audit_trail
15FOR EACH ROW
16BEGIN
17    SIGNAL SQLSTATE '45000'
18        SET MESSAGE_TEXT = 'SECURITY VIOLATION: Compliance audit records cannot be deleted!';
19END //
20 
21DELIMITER ;

3. Deploying the Change Data Capture (CDC) Triggers

1DELIMITER //
2 
3-- Audit Trigger for INSERT
4CREATE TRIGGER trg_comp_audit_insert
5AFTER INSERT ON employee_compensation
6FOR EACH ROW
7BEGIN
  INSERT INTO compliance_audit_trail (
      table_name, action_type, record_id, client_user, client_ip, delta_payload
  )
  VALUES (
      'employee_compensation',
      'INSERT',
      NEW.emp_id,
      USER(),
      SUBSTRING_INDEX(USER(), '@', -1),
      JSON_OBJECT(
          'new_state', JSON_OBJECT(
              'name', NEW.employee_name,
              'salary', NEW.base_salary,
              'bonus', NEW.bonus_percentage
          )
      )
  );
25END //
26 
27-- Audit Trigger for UPDATE (Captures Before AND After deltas!)
28CREATE TRIGGER trg_comp_audit_update
29AFTER UPDATE ON employee_compensation
30FOR EACH ROW
31BEGIN
  -- Log only if financial fields or active status changed
  IF OLD.base_salary != NEW.base_salary 
     OR OLD.bonus_percentage != NEW.bonus_percentage 
     OR OLD.is_active != NEW.is_active THEN
     
      INSERT INTO compliance_audit_trail (
          table_name, action_type, record_id, client_user, client_ip, delta_payload
      )
      VALUES (
          'employee_compensation',
          'UPDATE',
          NEW.emp_id,
          USER(),
          SUBSTRING_INDEX(USER(), '@', -1),
          JSON_OBJECT(
              'old_state', JSON_OBJECT(
                  'salary', OLD.base_salary,
                  'bonus', OLD.bonus_percentage,
                  'is_active', OLD.is_active
              ),
              'new_state', JSON_OBJECT(
                  'salary', NEW.base_salary,
                  'bonus', NEW.bonus_percentage,
                  'is_active', NEW.is_active
              ),
              'salary_delta', NEW.base_salary - OLD.base_salary
          )
      );
  END IF;
61END //
62 
63DELIMITER ;

4. Testing Compliance & Forensic Analytics

1-- 1. Insert sample staff
2INSERT INTO employee_compensation (employee_name, department, base_salary, bonus_percentage)
3VALUES ('Dr. Elizabeth Shaw', 'Research', 140000.00, 0.10);
4 
5-- 2. Execute salary increase
6UPDATE employee_compensation 
7SET base_salary = 165000.00 
8WHERE emp_id = 1;
9 
10-- 3. Verify that rogue attempts to alter the audit log are BLOCKED!
11-- This will FAIL with custom security exception!
12UPDATE compliance_audit_trail SET client_user = 'hacker' WHERE audit_id = 1;
13-- ERROR 1644 (45000): SECURITY VIOLATION: Compliance audit records are immutable and cannot be updated!

5. Compliance Forensic Reporting Query

Auditors want to see any salary changes exceeding $10,000 within the last 30 days:

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart

Step 1

delta_payload

Step 2

>'$.old_state.salary' AS previous_salary,

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does our compliance system enforce that audit records cannot be tampered with or deleted by rogue administrators?

Question 2

What does SUBSTRING_INDEX(USER(), '@', -1) extract?

Question 3

Why is the salary_delta explicitly calculated and stored in the update audit JSON payload?

Question 4

What happens if a statement updating employee_compensation fails due to a check constraint?

Question 5

Under what regulatory standards is immutable audit logging mandatory?

Related Lessons

Previous Lesson	Next Lesson
Project 2: Multi-Tenant Analytics Warehouse with Partitioning	None

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.