Audit Trails

Purpose:

The audit trail feature is designed to provide a detailed log of changes made to the database records. It helps in tracking modifications, ensuring data integrity, and supporting accountability and transparency within the system.

Key Components:

1. Audit Logs Table:

   • id: Unique identifier for each audit log entry.
   • tableName: The name of the table where the change occurred.
   • recordId: The ID of the record that was changed.
   • action: The type of action performed (e.g., 'create', 'update', 'delete').
   • changedBy: The identifier of the user who made the change.
   • changeDate: The timestamp when the change was made.
   • changeDetails: Detailed information about the change, usually in JSON or text format.

   Audit Logs Table:
   +------------+------------+-----------+---------+-----------+--------------+------------------+
   | id         | tableName  | recordId  | action  | changedBy | changeDate   | changeDetails    |
   +------------+------------+-----------+---------+-----------+--------------+------------------+
   | unique_id  | table_name | record_id | action  | user_id   | timestamp    | details_of_change|
   +------------+------------+-----------+---------+-----------+--------------+------------------+
   

2. Logging Function:

   • logAuditChange: This function records changes into the audit log table. It captures the table name, record ID, action type, the user who made the change, and details of the change.

   Logging Function:
   +-----------------+---------------------+------------------+-------------------+------------------+
   | tableName       | recordId            | action           | changedBy         | changeDetails    |
   +-----------------+---------------------+------------------+-------------------+------------------+
   | target_table    | target_record_id    | type_of_action   | user_id           | details_of_change|
   +-----------------+---------------------+------------------+-------------------+------------------+
   

Process Flow:

1. Trigger Event:

   • An event occurs that modifies a database record. This could be a record creation, update, or deletion.

2. Capture Change Details:

   • Details of the change are captured, including the table name, record ID, action type, user who made the change, and a description of the change.

3. Log the Change:

   • The logAuditChange function is called to log the change in the auditLogs table.

4. Store in Audit Logs Table:

   • The change is stored in the auditLogs table with a unique ID, ensuring that all changes are recorded in a structured manner.

Flow Diagram:

[Event Triggered]
      |
      v
[Capture Change Details]
      |
      +--> [Table Name]
      |
      +--> [Record ID]
      |
      +--> [Action Type]
      |
      +--> [Changed By]
      |
      +--> [Change Details]
      |
      v
[Call logAuditChange Function]
      |
      +--> [Create Audit Log Entry]
      |         |
      |         +--> [Generate Unique ID]
      |         |
      |         +--> [Populate Fields]
      |
      +--> [Insert into auditLogs Table]
                |
                v
      [Store Audit Log Entry]

Example Scenario:

User Action: A user updates the email address of a student record.

Captured Details:

• Table Name: students
• Record ID: 12345
• Action: update
• Changed By: adminUser
• Change Details: {"email": {"old": "old@example.com", "new": "new@example.com"}}

Log the Change:

1. The logAuditChange function is called with the above details.
2. A unique ID is generated for the audit log entry.
3. The audit log entry is populated and inserted into the auditLogs table.
4. The change is stored in the audit log, providing a record that the email address was updated by adminUser.