Designing audit logs for enterprise WordPress setups tracking internal user modifications to online course lessons
Database Schema Design for Audit Trails
For enterprise-grade WordPress audit logging, especially when tracking modifications to sensitive content like online course lessons, a robust database schema is paramount. We need to capture not just *what* changed, but *who* changed it, *when*, and *how*. A common approach involves a dedicated audit log table. This table should be normalized to avoid redundancy while providing sufficient detail for reconstruction and analysis.
Consider the following table structure for storing audit events. This schema prioritizes clarity and extensibility.
`wp_audit_log` Table Structure
The primary table, let’s call it `wp_audit_log`, will store individual audit events. Key fields include:
log_id(BIGINT, UNSIGNED, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each log entry.user_id(BIGINT, UNSIGNED, NOT NULL): The ID of the WordPress user performing the action. This should be a foreign key to `wp_users.ID`.timestamp(DATETIME, NOT NULL): The exact time the action occurred.action(VARCHAR(100), NOT NULL): A descriptive string of the action performed (e.g., ‘lesson_updated’, ‘lesson_created’, ‘lesson_deleted’, ‘lesson_content_modified’).object_type(VARCHAR(50), NOT NULL): The type of WordPress object being modified (e.g., ‘lesson’, ‘course’, ‘post’, ‘page’).object_id(BIGINT, UNSIGNED, NOT NULL): The ID of the specific object being modified (e.g., the `ID` from `wp_posts` for a lesson).details(LONGTEXT, NULL): A JSON-encoded string containing detailed information about the changes. This is crucial for tracking specific field modifications.ip_address(VARCHAR(45), NULL): The IP address from which the action was performed.
The `details` field is where the granular tracking happens. For a lesson update, this could store an array of changes, such as:
“`json { “changes”: [ { “field”: “post_title”, “old_value”: “Original Lesson Title”, “new_value”: “Updated Lesson Title” }, { “field”: “post_content”, “old_value”: “Original lesson content…”, “new_value”: “Updated lesson content…” }, { “field”: “meta_input.lesson_duration”, “old_value”: “30”, “new_value”: “45” } ] }This structure allows for efficient querying and reconstruction of past states. Indexing `user_id`, `timestamp`, `action`, and `object_type`/`object_id` will be critical for performance.
Implementing the Audit Hook System
WordPress’s action and filter hooks provide the ideal mechanism for intercepting and logging modifications. We’ll focus on hooks related to post saving, as lessons are typically custom post types (CPTs) or standard posts.
Hooking into Post Saves
The `save_post` hook is the primary entry point. However, it fires multiple times during a save operation and can be triggered by autosaves, revisions, and bulk actions. Careful conditional logic is required.
/**
* Logs modifications to lessons.
*/
function my_audit_log_lesson_save( $post_id, $post, $update ) {
// 1. Prevent infinite loops: Don't log our own actions.
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
return;
}
if ( defined( 'DOING_AJAX' ) && DOING_AJAX ) {
// Consider logging AJAX actions if necessary, but be cautious.
// For now, we'll skip them to avoid noise.
return;
}
if ( wp_is_post_revision( $post_id ) ) {
return; // Don't log revisions.
}
if ( wp_is_post_autosave( $post_id ) ) {
return; // Don't log autosaves.
}
// 2. Check if the post type is a 'lesson' (or your relevant CPT).
// Replace 'lesson' with your actual lesson post type slug.
if ( 'lesson' !== $post->post_type ) {
return;
}
// 3. Ensure the user has permission to edit the post.
// This check is often implicitly handled by WordPress's save_post,
// but explicit checks can add layers of security.
if ( ! current_user_can( 'edit_post', $post_id ) ) {
return;
}
// 4. Determine the action: create or update.
$action = $update ? 'lesson_updated' : 'lesson_created';
// 5. Capture original data for comparison (if updating).
$old_post_data = null;
$changes = array();
if ( $update ) {
// Fetch the *previous* version of the post from the database.
// This is a critical step for accurate diffing.
$previous_post = get_post( $post_id );
if ( $previous_post ) {
$old_post_data = array(
'post_title' => $previous_post->post_title,
'post_content' => $previous_post->post_content,
// Add other core post fields as needed
);
// Compare core post fields
if ( $previous_post->post_title !== $post->post_title ) {
$changes[] = array(
'field' => 'post_title',
'old_value' => $previous_post->post_title,
'new_value' => $post->post_title,
);
}
if ( $previous_post->post_content !== $post->post_content ) {
$changes[] = array(
'field' => 'post_content',
'old_value' => $previous_post->post_content,
'new_value' => $post->post_content,
);
}
// Compare post meta (custom fields)
$meta_keys_to_track = array( 'lesson_duration', 'video_url', 'quiz_id' ); // Example meta keys
foreach ( $meta_keys_to_track as $meta_key ) {
$old_meta_value = get_post_meta( $post_id, $meta_key, true );
$new_meta_value = get_post_meta( $post_id, $meta_key, true ); // Note: $_POST will contain new values if submitted
// A more robust way to get new meta values is to inspect $_POST
// This requires careful sanitization and validation.
// For simplicity here, we'll assume get_post_meta() on the *current* $post object
// might not reflect the *submitted* values if the save_post hook runs too early.
// A better approach is to inspect $_POST directly.
// Let's refine this to inspect $_POST for submitted meta values.
// This requires careful handling of sanitization.
$submitted_meta_value = isset( $_POST[$meta_key] ) ? $_POST[$meta_key] : null;
// If the meta key was submitted and differs from the old value
if ( array_key_exists( $meta_key, $_POST ) && $old_meta_value !== $submitted_meta_value ) {
$changes[] = array(
'field' => 'meta_input.' . $meta_key,
'old_value' => $old_meta_value,
'new_value' => $submitted_meta_value,
);
} elseif ( ! array_key_exists( $meta_key, $_POST ) && ! empty( $old_meta_value ) ) {
// Meta key was present but not submitted, implying deletion or reset.
// This logic needs to be very precise based on how your form handles meta.
// For simplicity, we'll assume if it's not in $_POST, it's not changed *in this save*.
// A more advanced system might track deletions explicitly.
}
}
}
}
// 6. Prepare details for logging.
$log_details = array();
if ( ! empty( $changes ) ) {
$log_details['changes'] = $changes;
}
// Add other relevant context if needed, e.g., specific lesson settings.
// 7. Log the event.
my_audit_log_entry(
get_current_user_id(),
current_time( 'mysql' ),
$action,
'lesson',
$post_id,
! empty( $log_details ) ? wp_json_encode( $log_details ) : null,
$_SERVER['REMOTE_ADDR'] ?? null // Get IP address
);
}
add_action( 'save_post', 'my_audit_log_lesson_save', 10, 3 );
Helper Function for Logging
To keep the hook clean, abstract the database insertion into a separate function.
/**
* Inserts an audit log entry into the database.
*
* @param int $user_id The ID of the user performing the action.
* @param string $timestamp The timestamp of the action.
* @param string $action The action performed.
* @param string $object_type The type of object modified.
* @param int $object_id The ID of the object modified.
* @param string $details JSON-encoded details of the changes.
* @param string $ip_address The IP address of the user.
*/
function my_audit_log_entry( $user_id, $timestamp, $action, $object_type, $object_id, $details = null, $ip_address = null ) {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_log';
// Basic sanitization for string inputs
$action = sanitize_text_field( $action );
$object_type = sanitize_text_field( $object_type );
$ip_address = sanitize_text_field( $ip_address );
$wpdb->insert(
$table_name,
array(
'user_id' => absint( $user_id ),
'timestamp' => $timestamp,
'action' => $action,
'object_type' => $object_type,
'object_id' => absint( $object_id ),
'details' => $details, // Assumes $details is already JSON encoded and safe
'ip_address' => $ip_address,
),
array(
'%d', // user_id
'%s', // timestamp
'%s', // action
'%s', // object_type
'%d', // object_id
'%s', // details
'%s', // ip_address
)
);
}
Handling Custom Field (Meta) Changes
Tracking changes to custom fields (post meta) requires special attention. The `save_post` hook fires *after* meta has been saved. To accurately capture the “before” state of meta, we need to fetch it *before* the save operation potentially overwrites it, or more reliably, inspect the `$_POST` data and compare it against the current database values.
The example above demonstrates a basic approach by inspecting `$_POST`. A more robust solution might involve:
- Using `update_post_metadata` filter to capture old and new values directly during meta updates.
- Implementing a custom meta box that serializes its values and logs changes upon form submission.
- Leveraging WordPress’s built-in revision system if it captures sufficient detail for your needs (though often it doesn’t log granular meta changes).
Advanced Meta Tracking with `update_post_metadata`
The `update_post_metadata` filter allows us to intercept meta updates before they are committed to the database. This is often a cleaner way to track meta changes.
/**
* Logs changes to post meta for lessons.
* This filter fires before the meta is updated in the database.
*/
function my_audit_log_meta_update( $check, $meta_key, $meta_value, $prev_value ) {
// Only log for 'lesson' post types
// We need to get the post ID from the context. This filter doesn't directly provide it.
// A common workaround is to store the current post ID in a global or transient
// when save_post is triggered, or to inspect $_POST if available.
// For simplicity, let's assume we're within the context of a 'save_post' for a lesson.
// A more robust implementation would require passing the post_id context.
// A common pattern is to use a global variable set by the save_post hook.
global $my_audit_log_current_post_id; // Assume this is set by save_post hook
if ( ! $my_audit_log_current_post_id ) {
return $check; // Not in a relevant save context
}
$post_type = get_post_type( $my_audit_log_current_post_id );
if ( 'lesson' !== $post_type ) {
return $check;
}
// Define which meta keys to track
$tracked_meta_keys = array( 'lesson_duration', 'video_url', 'quiz_id' );
if ( ! in_array( $meta_key, $tracked_meta_keys, true ) ) {
return $check;
}
// If the value has actually changed
if ( $meta_value !== $prev_value ) {
// Prepare details for logging
$log_details = array(
'field' => 'meta_input.' . $meta_key,
'old_value' => $prev_value,
'new_value' => $meta_value,
);
// Log the event. We need to determine the action type (update/create)
// and user ID. This is best done in the save_post hook.
// This filter is primarily for capturing the *details*.
// The actual log entry insertion should happen in save_post.
// A common pattern: store details to be picked up by save_post.
// This requires a mechanism to pass data between hooks.
// For this example, we'll assume save_post handles the final logging.
// We'll modify save_post to collect these details.
}
return $check; // Always return the original value to allow the update.
}
// add_filter( 'update_post_metadata', 'my_audit_log_meta_update', 10, 4 ); // This filter is tricky due to context.
// Revised approach: Modify save_post to collect meta changes more reliably.
// The previous save_post example already attempts to inspect $_POST.
// A more direct way to get the *previous* meta value is to fetch it *before*
// WordPress's internal meta saving logic runs, but *after* we know it's a lesson save.
// This is complex due to the order of operations in WP.
// Let's refine the save_post hook to be more explicit about meta.
Refined `save_post` for Meta Tracking
The most reliable way within `save_post` is to fetch the *current* meta values from the database *before* WordPress saves the new ones, and then compare them with the values submitted in `$_POST`.
/**
* Logs modifications to lessons, including custom fields.
*/
function my_audit_log_lesson_save_refined( $post_id, $post, $update ) {
// ... (Initial checks from previous example: DOING_AUTOSAVE, post_type, permissions, etc.) ...
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) return;
if ( 'lesson' !== $post->post_type ) return;
if ( ! current_user_can( 'edit_post', $post_id ) ) return;
if ( wp_is_post_revision( $post_id ) || wp_is_post_autosave( $post_id ) ) return;
$action = $update ? 'lesson_updated' : 'lesson_created';
$changes = array();
// --- Core Post Data Comparison ---
if ( $update ) {
$previous_post = get_post( $post_id ); // Get the *current* state from DB before save
if ( $previous_post ) {
if ( $previous_post->post_title !== $post->post_title ) {
$changes[] = array( 'field' => 'post_title', 'old_value' => $previous_post->post_title, 'new_value' => $post->post_title );
}
if ( $previous_post->post_content !== $post->post_content ) {
$changes[] = array( 'field' => 'post_content', 'old_value' => $previous_post->post_content, 'new_value' => $post->post_content );
}
}
}
// --- Custom Meta Data Comparison ---
// Define meta keys to track. This should be dynamic or configurable.
$tracked_meta_keys = array( 'lesson_duration', 'video_url', 'quiz_id', 'prerequisites' );
// Get current meta values from DB *before* they are potentially updated by the save process
$current_meta = array();
if ( $update ) {
foreach ( $tracked_meta_keys as $meta_key ) {
$current_meta[$meta_key] = get_post_meta( $post_id, $meta_key, true );
}
}
// Compare with submitted values in $_POST
foreach ( $tracked_meta_keys as $meta_key ) {
// Check if the meta key was submitted in the form
if ( isset( $_POST[$meta_key] ) ) {
$new_meta_value = $_POST[$meta_key];
$old_meta_value = $update ? ( $current_meta[$meta_key] ?? '' ) : ''; // If creating, old value is effectively empty
// Sanitize the new value based on expected type
// Example: $new_meta_value = sanitize_text_field( $new_meta_value );
// For arrays (like checkboxes or multi-selects), you'd need different handling.
// Let's assume simple string/numeric values for now.
$new_meta_value = sanitize_text_field( $new_meta_value ); // Adjust sanitization as needed
if ( $new_meta_value !== $old_meta_value ) {
$changes[] = array(
'field' => 'meta_input.' . $meta_key,
'old_value' => $old_meta_value,
'new_value' => $new_meta_value,
);
}
} elseif ( $update ) {
// Meta key was NOT submitted. This could mean deletion or reset.
// If the meta key existed previously and is now absent from $_POST,
// we might want to log this as a deletion or reset.
// This logic depends heavily on your form implementation.
// For example, if a field is cleared, it might be submitted as an empty string.
// If it's completely omitted, it might imply deletion.
// Let's assume for now that omission means no change *in this save operation*.
// A more complex system might track explicit deletions.
}
}
// --- Log the event ---
$log_details = array();
if ( ! empty( $changes ) ) {
$log_details['changes'] = $changes;
}
my_audit_log_entry(
get_current_user_id(),
current_time( 'mysql' ),
$action,
'lesson',
$post_id,
! empty( $log_details ) ? wp_json_encode( $log_details ) : null,
$_SERVER['REMOTE_ADDR'] ?? null
);
}
// Remove the previous hook and add the refined one.
// remove_action( 'save_post', 'my_audit_log_lesson_save', 10, 3 );
add_action( 'save_post', 'my_audit_log_lesson_save_refined', 10, 3 );
Displaying and Querying Audit Logs
A dedicated admin page or a meta box on the lesson edit screen is necessary to view the audit trail. This involves querying the `wp_audit_log` table.
Admin Page Implementation (Conceptual)
Create a new top-level menu item or a submenu under a relevant section (e.g., “Lessons” or “Users”). Use the WordPress Settings API or custom page structure.
/**
* Adds an audit log administration page.
*/
function my_audit_log_admin_page() {
add_menu_page(
__( 'Audit Log', 'textdomain' ),
__( 'Audit Log', 'textdomain' ),
'manage_options', // Capability required
'audit-log',
'my_render_audit_log_page',
'dashicons-list-view',
80
);
}
add_action( 'admin_menu', 'my_audit_log_admin_page' );
/**
* Renders the audit log table.
*/
function my_render_audit_log_page() {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_log';
// Basic pagination setup
$per_page = 50;
$current_page = isset( $_GET['paged'] ) ? absint( $_GET['paged'] ) : 1;
$offset = ( $current_page - 1 ) * $per_page;
// Query logs
$logs = $wpdb->get_results(
$wpdb->prepare(
"SELECT * FROM {$table_name} ORDER BY timestamp DESC LIMIT %d OFFSET %d",
$per_page,
$offset
)
);
// Calculate total pages for pagination
$total_items = $wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" );
$total_pages = ceil( $total_items / $per_page );
?>
<div class="wrap">
<h1><?php esc_html_e( 'Audit Log', 'textdomain' ); ?></h1>
<table class="wp-list-table widefat fixed striped">
<thead>
<tr>
<th><?php esc_html_e( 'Timestamp', 'textdomain' ); ?></th>
<th><?php esc_html_e( 'User', 'textdomain' ); ?></th>
<th><?php esc_html_e( 'Action', 'textdomain' ); ?></th>
<th><?php esc_html_e( 'Object', 'textdomain' ); ?></th>
<th><?php esc_html_e( 'Details', 'textdomain' ); ?></th>
<th><?php esc_html_e( 'IP Address', 'textdomain' ); ?></th>
</tr>
</thead>
<tbody>
<?php if ( $logs ) : ?>
<?php foreach ( $logs as $log ) : ?>
<tr>
<td><?php echo esc_html( $log->timestamp ); ?></td>
<td><?php
$user_info = get_userdata( $log->user_id );
echo esc_html( $user_info->user_login ?? 'N/A' );
?></td>
<td><?php echo esc_html( $log->action ); ?></td>
<td><?php echo esc_html( $log->object_type ); ?>: <?php echo esc_html( $log->object_id ); ?></td>
<td><?php
if ( $log->details ) {
$details = json_decode( $log->details, true );
if ( isset( $details['changes'] ) && is_array( $details['changes'] ) ) {
echo '<ul style="margin:0; padding-left: 15px;">';
foreach ( $details['changes'] as $change ) {
echo '<li><strong>' . esc_html( $change['field'] ) . '</strong>: ' .
esc_html( $change['old_value'] ?? 'N/A' ) . ' → ' .
esc_html( $change['new_value'] ?? 'N/A' ) . '</li>';
}
echo '</ul>';
} else {
echo esc_html( $log->details ); // Fallback for non-structured details
}
} else {
esc_html_e( 'No details', 'textdomain' );
}
?></td>
<td><?php echo esc_html( $log->ip_address ); ?></td>
</tr>
<?php endforeach; ?>
<?php else : ?>
<tr>
<td colspan="6"><?php esc_html_e( 'No audit logs found.', 'textdomain' ); ?></td>
</tr>
</?php endif; ?>
</tbody>
</table>
<!-- Pagination -->
<div class="tablenav bottom">
<?php
$big = 999999999; // Need an unlikely integer
echo paginate_links( array(
'base' => add_query_arg( 'paged', '%#%' ),
'format' => '?paged=%#%',
'current' => $current_page,
'total' => $total_pages,
'prev_text' => __('«'),
'next_text' => __('»'),
) );
?>
</div>
</div>
<?php
}
Filtering and Searching
For enterprise use, the ability to filter logs by user, date range, action, or object is essential. This would involve adding form elements to the admin page and modifying the SQL query based on `$_GET` parameters. Ensure all user inputs are properly sanitized and escaped.
Example query modification for filtering by user ID:
// Inside my_render_audit_log_page function, before the query:
$user_filter = isset( $_GET['user_id'] ) ? absint( $_GET['user_id'] ) : 0;
$where_clauses = array();
$query_args = array( $per_page, $offset );
if ( $user_filter > 0 ) {
$where_clauses[] = "user_id = %d";
$query_args[0] = $user_filter; // Replace $per_page with the actual user_id for the query
}
$sql = "SELECT * FROM {$table_name}";
if ( ! empty( $where_clauses ) ) {
$sql .= " WHERE " . implode( ' AND ', $where_clauses );
}
$sql .= " ORDER BY timestamp DESC LIMIT %d OFFSET %d";
// Adjust query_args for the final SQL statement
$final_query_args = array_merge( $query_args, array( $per_page, $offset ) ); // This needs careful reordering based on where clauses
// A better way to handle dynamic SQL:
$sql = "SELECT * FROM {$table_name}";
$where_sql = "";
$prepared_args = array();
if ( $user_filter > 0 ) {
$where_sql .= " AND user_id = %d";
$prepared_args[] = $user_filter;
}
// Add other filters (action, date range, etc.) similarly...
if ( ! empty( $where_sql ) ) {
$sql .= " WHERE " . ltrim( $where_sql, ' AND' ); // Remove leading AND
}
$sql .= " ORDER BY timestamp DESC LIMIT %d OFFSET %d";
$prepared_args[] = $per_page;
$prepared_args[] = $offset;
$logs = $wpdb->get_results( $wpdb->prepare( $sql, $prepared_args ) );
// Update total_items query similarly
$count_sql = "SELECT COUNT(*) FROM {$table_name}";
if ( ! empty( $where_sql ) ) {
$count_sql .= " WHERE " . ltrim( $where_sql, ' AND' );
}
$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, array_slice( $prepared_args, 0, count( $prepared_args ) - 2 ) ) ); // Exclude LIMIT and OFFSET args for count
Security and Performance Considerations
Audit logs can grow very large. Implement strategies for managing log size:
- Database Indexing: Ensure `user_id`, `timestamp`, `action`, `object_type`, and `object_id` are indexed.
- Log Rotation/Archiving: Periodically move older logs to a separate archive table or an external logging system (e.g., ELK stack, Splunk