Designing audit logs for enterprise WordPress setups tracking internal user modifications to vendor commission records
Database Schema for Audit Trails
For enterprise WordPress installations, robust audit logging is paramount, especially when tracking modifications to sensitive data like vendor commissions. A well-designed audit trail database schema is the foundation. We’ll create a dedicated table to store these audit events, ensuring immutability and efficient querying. This table should capture essential information about the change: who made it, when, what was changed, and the old/new values.
Here’s a proposed SQL schema for the audit log table. We’ll name it `wp_audit_log_commissions` to keep it distinct and easily identifiable.
CREATE TABLE wp_audit_log_commissions (
log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL COMMENT 'ID of the user who made the change',
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'When the change occurred',
action VARCHAR(50) NOT NULL COMMENT 'Type of action (e.g., UPDATE, CREATE, DELETE)',
record_id BIGINT UNSIGNED NOT NULL COMMENT 'ID of the commission record affected',
field_name VARCHAR(100) NULL COMMENT 'Name of the field that was changed',
old_value TEXT NULL COMMENT 'The value of the field before the change',
new_value TEXT NULL COMMENT 'The value of the field after the change',
ip_address VARCHAR(45) NULL COMMENT 'IP address of the user',
INDEX idx_user_id (user_id),
INDEX idx_timestamp (timestamp),
INDEX idx_record_id (record_id),
INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Key considerations for this schema:
log_id: A unique identifier for each log entry.user_id: Links the log entry to the WordPress user who performed the action. A foreign key constraint towp_usersis advisable in a production environment.timestamp: Records the exact time of the modification.action: Differentiates between types of changes (e.g., ‘UPDATE’, ‘CREATE’, ‘DELETE’).record_id: Identifies the specific commission record that was modified. This assumes you have a primary key for your commission records.field_name: Crucial for granular tracking; specifies which field within the commission record was altered.old_valueandnew_value: Storing both allows for easy diffing and understanding the exact change. UsingTEXTaccommodates potentially large values.ip_address: Useful for security investigations.
Hooking into WordPress for Commission Modifications
To capture these modifications, we need to hook into WordPress actions that occur when commission data is saved or updated. This typically involves custom post types, meta boxes, or dedicated plugin tables. For this example, let’s assume vendor commissions are stored as post meta associated with a custom post type, say `vendor_commission`.
We’ll leverage the `save_post` hook, but with careful consideration to avoid infinite loops and to target only relevant post types and meta updates.
/**
* Plugin Name: Vendor Commission Audit Log
* Description: Logs modifications to vendor commission records.
* Version: 1.0
* Author: Antigravity
*/
// Prevent direct access
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
/**
* Logs changes to vendor commission records.
*/
function ag_log_commission_changes( $post_id ) {
// Check if this is an autosave
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
return $post_id;
}
// Check if the user has permissions to edit posts
if ( ! current_user_can( 'edit_post', $post_id ) ) {
return $post_id;
}
// Check if it's our custom post type
if ( 'vendor_commission' !== get_post_type( $post_id ) ) {
return $post_id;
}
// Get current user ID
$user_id = get_current_user_id();
if ( ! $user_id ) {
// Log as anonymous or a specific system user if needed
$user_id = 0; // Or a dedicated system user ID
}
// Get IP Address
$ip_address = $_SERVER['REMOTE_ADDR'] ?? 'N/A';
// Get all existing meta data for this post
$old_meta_data = get_post_meta( $post_id );
// Get the data submitted via the form (assuming it's $_POST)
// This part is highly dependent on your form structure.
// For demonstration, let's assume we're tracking 'commission_rate' and 'payout_threshold'.
$submitted_data = array();
if ( isset( $_POST['commission_rate'] ) ) {
$submitted_data['commission_rate'] = sanitize_text_field( $_POST['commission_rate'] );
}
if ( isset( $_POST['payout_threshold'] ) ) {
$submitted_data['payout_threshold'] = sanitize_text_field( $_POST['payout_threshold'] );
}
// Add other fields you want to track here...
// Compare old and new values and log differences
$fields_to_track = array( 'commission_rate', 'payout_threshold' ); // Add fields from your form
foreach ( $fields_to_track as $field ) {
$old_value = isset( $old_meta_data[$field][0] ) ? maybe_unserialize( $old_meta_data[$field][0] ) : null;
$new_value = isset( $submitted_data[$field] ) ? $submitted_data[$field] : null;
// Only log if there's a change and the field is relevant
if ( $old_value !== $new_value && array_key_exists( $field, $submitted_data ) ) {
ag_insert_audit_log( $user_id, 'UPDATE', $post_id, $field, $old_value, $new_value, $ip_address );
}
}
// Handle new meta data that wasn't there before (CREATE action for fields)
foreach ( $submitted_data as $field => $new_value ) {
if ( ! array_key_exists( $field, $old_meta_data ) ) {
ag_insert_audit_log( $user_id, 'CREATE', $post_id, $field, null, $new_value, $ip_address );
}
}
}
add_action( 'save_post', 'ag_log_commission_changes', 10, 1 );
/**
* Inserts a log entry into the audit table.
*
* @param int $user_id The ID of the user performing the action.
* @param string $action The type of action performed.
* @param int $record_id The ID of the record affected.
* @param string $field_name The name of the field changed.
* @param mixed $old_value The old value of the field.
* @param mixed $new_value The new value of the field.
* @param string $ip_address The IP address of the user.
*/
function ag_insert_audit_log( $user_id, $action, $record_id, $field_name, $old_value, $new_value, $ip_address ) {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_log_commissions';
// Ensure values are strings for TEXT column
$old_value_str = is_string( $old_value ) ? $old_value : ( $old_value === null ? null : serialize( $old_value ) );
$new_value_str = is_string( $new_value ) ? $new_value : ( $new_value === null ? null : serialize( $new_value ) );
$wpdb->insert(
$table_name,
array(
'user_id' => $user_id,
'action' => $action,
'record_id' => $record_id,
'field_name' => $field_name,
'old_value' => $old_value_str,
'new_value' => $new_value_str,
'ip_address' => $ip_address,
),
array(
'%d', // user_id
'%s', // action
'%d', // record_id
'%s', // field_name
'%s', // old_value (can be NULL)
'%s', // new_value (can be NULL)
'%s', // ip_address
)
);
}
/**
* Activation hook to create the audit log table.
*/
function ag_activate_plugin() {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_log_commissions';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL COMMENT 'ID of the user who made the change',
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'When the change occurred',
action VARCHAR(50) NOT NULL COMMENT 'Type of action (e.g., UPDATE, CREATE, DELETE)',
record_id BIGINT UNSIGNED NOT NULL COMMENT 'ID of the commission record affected',
field_name VARCHAR(100) NULL COMMENT 'Name of the field that was changed',
old_value TEXT NULL COMMENT 'The value of the field before the change',
new_value TEXT NULL COMMENT 'The value of the field after the change',
ip_address VARCHAR(45) NULL COMMENT 'IP address of the user',
INDEX idx_user_id (user_id),
INDEX idx_timestamp (timestamp),
INDEX idx_record_id (record_id),
INDEX idx_action (action)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
register_activation_hook( __FILE__, 'ag_activate_plugin' );
Explanation:
- The `ag_log_commission_changes` function is hooked into `save_post`.
- It includes essential checks:
DOING_AUTOSAVE: Prevents logging during auto-saves.current_user_can('edit_post', $post_id): Ensures the user has edit permissions.get_post_type($post_id): Filters for our specific `vendor_commission` post type.- It retrieves the current user ID and IP address.
- Crucially, it compares the existing post meta (`get_post_meta`) with the submitted data (`$_POST`). This comparison is where we identify changes.
- The `ag_insert_audit_log` function is a helper to abstract the database insertion logic, making the main hook cleaner. It uses `$wpdb` for safe database operations and handles serialization for complex data types.
- The `ag_activate_plugin` function, registered with `register_activation_hook`, ensures the audit log table is created when the plugin is activated.
Handling Different Data Types and Complex Fields
Vendor commissions might involve more than just simple string or numeric fields. They could include serialized data (like arrays or objects for custom configurations), JSON, or even relationships to other entities. The `ag_insert_audit_log` function uses `serialize()` to store these complex types in the `TEXT` columns. When retrieving, `maybe_unserialize()` is used to convert them back.
For fields that are arrays or objects, the comparison logic needs to be robust. A simple `!==` might not be sufficient if the order of elements in an array changes but the content remains the same. For such cases, consider using array comparison functions like array_diff or serializing both values and comparing the serialized strings.
// Example of comparing serialized arrays for changes
$old_value_serialized = isset( $old_meta_data[$field][0] ) ? $old_meta_data[$field][0] : null;
$new_value_serialized = isset( $submitted_data[$field] ) ? serialize( $submitted_data[$field] ) : null;
if ( $old_value_serialized !== $new_value_serialized ) {
// Log the change, storing both original and new serialized values
ag_insert_audit_log( $user_id, 'UPDATE', $post_id, $field, $old_value_serialized, $new_value_serialized, $ip_address );
}
If your commission data is stored in a custom table rather than post meta, you would adapt the `save_post` hook to trigger on actions related to that table (e.g., using custom AJAX handlers or hooks specific to your custom table management). The core principle of capturing old vs. new values remains the same.
Querying and Displaying Audit Logs
Once logs are being generated, you’ll need a way to view them. This typically involves a custom admin page or a meta box on the commission record itself. Here’s a basic example of how to query the audit log table to display recent changes for a specific commission record.
/**
* Displays audit log entries for a specific commission record.
* Assumes this is called within the context of a single commission post.
*
* @param int $commission_id The ID of the commission post.
*/
function ag_display_commission_audit_log( $commission_id ) {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_log_commissions';
$logs = $wpdb->get_results(
$wpdb->prepare(
"SELECT * FROM $table_name WHERE record_id = %d ORDER BY timestamp DESC",
$commission_id
)
);
if ( $logs ) {
echo '<h3>' . __( 'Audit Log', 'your-text-domain' ) . '</h3>';
echo '<table class="wp-list-table widefat fixed striped">';
echo '<thead><tr>';
echo '<th>' . __( 'Timestamp', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'User', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'Action', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'Field', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'Old Value', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'New Value', 'your-text-domain' ) . '</th>';
echo '<th>' . __( 'IP Address', 'your-text-domain' ) . '</th>';
echo '</tr></thead>';
echo '<tbody>';
foreach ( $logs as $log ) {
$user_info = get_userdata( $log->user_id );
$user_display_name = $user_info ? $user_info->display_name : __( 'System/Anonymous', 'your-text-domain' );
// Attempt to unserialize for better display if needed
$old_value_display = $log->old_value;
if ( $old_value_display !== null ) {
$unserialized_old = maybe_unserialize( $old_value_display );
if ( $unserialized_old !== $old_value_display ) { // Check if unserialization changed it
$old_value_display = is_array( $unserialized_old ) || is_object( $unserialized_old ) ? '' . esc_html( print_r( $unserialized_old, true ) ) . '
' : esc_html( $unserialized_old );
} else {
$old_value_display = esc_html( $old_value_display );
}
} else {
$old_value_display = '' . __( 'N/A', 'your-text-domain' ) . '';
}
$new_value_display = $log->new_value;
if ( $new_value_display !== null ) {
$unserialized_new = maybe_unserialize( $new_value_display );
if ( $unserialized_new !== $new_value_display ) {
$new_value_display = is_array( $unserialized_new ) || is_object( $unserialized_new ) ? '' . esc_html( print_r( $unserialized_new, true ) ) . '
' : esc_html( $unserialized_new );
} else {
$new_value_display = esc_html( $new_value_display );
}
} else {
$new_value_display = '' . __( 'N/A', 'your-text-domain' ) . '';
}
echo '<tr>';
echo '<td>' . esc_html( $log->timestamp ) . '</td>';
echo '<td>' . esc_html( $user_display_name ) . '</td>';
echo '<td>' . esc_html( $log->action ) . '</td>';
echo '<td>' . esc_html( $log->field_name ) . '</td>';
echo '<td>' . $old_value_display . '</td>';
echo '<td>' . $new_value_display . '</td>';
echo '<td>' . esc_html( $log->ip_address ) . '</td>';
echo '</tr>';
}
echo '</tbody></table>';
} else {
echo '<p>' . __( 'No audit history found for this commission.', 'your-text-domain' ) . '</p>';
}
}
// Example of how to call this function, e.g., in a meta box callback
// add_action( 'add_meta_boxes', 'ag_add_commission_meta_box' );
// function ag_add_commission_meta_box() {
// add_meta_box(
// 'commission_audit_log',
// __( 'Commission Audit History', 'your-text-domain' ),
// 'ag_render_commission_audit_log_meta_box',
// 'vendor_commission', // Your post type
// 'normal',
// 'low'
// );
// }
//
// function ag_render_commission_audit_log_meta_box( $post ) {
// ag_display_commission_audit_log( $post->ID );
// }
This function queries the `wp_audit_log_commissions` table, retrieves logs for a given `record_id`, and formats them into an HTML table using WordPress’s standard CSS classes for a consistent look and feel. It also attempts to unserialize and pretty-print complex data types for better readability.
Security and Performance Considerations
Security:
- Data Sanitization: Always sanitize any data before inserting it into the database, especially user-provided input like IP addresses or potentially malicious values in `old_value`/`new_value` if they are not strictly controlled. The `ag_insert_audit_log` function uses prepared statements via `$wpdb->insert`, which is crucial for preventing SQL injection.
- Access Control: Ensure that the audit log display is restricted to users with appropriate permissions (e.g., administrators or specific roles). The example code for displaying logs should be placed within a function that checks user capabilities.
- Log Tampering: While this setup provides a good audit trail, true immutability in a compromised environment is challenging. For extreme security needs, consider off-site logging or blockchain-based solutions, though these are beyond a typical WordPress plugin.
Performance:
- Database Indexing: The provided schema includes indexes on `user_id`, `timestamp`, `record_id`, and `action`. These are vital for efficient querying, especially as the log table grows.
- Log Volume: For high-traffic sites, the audit log table can become very large. Implement a log rotation or archival strategy. This could involve periodically moving older logs to a separate archive table or a data warehouse, or deleting logs older than a certain retention period (e.g., 1-2 years), depending on compliance requirements.
- Query Optimization: When displaying logs, ensure queries are specific. The example uses `WHERE record_id = %d`, which is efficient. Avoid `SELECT *` on very large tables without specific `WHERE` clauses.
- Batching Inserts: If many meta fields are updated simultaneously, you might be performing multiple `ag_insert_audit_log` calls within a single `save_post` execution. For extreme cases, consider batching these inserts into a single database transaction or a single `INSERT` statement with multiple value sets if performance becomes an issue.
By implementing a structured audit log system like this, enterprise WordPress sites can gain critical visibility into changes made to sensitive vendor commission data, enhancing accountability and security.