Designing audit logs for enterprise WordPress setups tracking internal user modifications to custom subscription logs
Database Schema for Audit Trails
When designing audit logs for modifications to custom subscription data within an enterprise WordPress environment, a robust database schema is paramount. We need to capture who made the change, when, what was changed, and the context of that change. For this, a dedicated `wp_audit_logs` table is recommended. This table will store immutable records of all significant events.
Consider the following structure for the `wp_audit_logs` table:
CREATE TABLE wp_audit_logs (
log_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NULL DEFAULT NULL,
username VARCHAR(60) NOT NULL DEFAULT '',
action VARCHAR(100) NOT NULL DEFAULT '',
object_type VARCHAR(100) NOT NULL DEFAULT '',
object_id BIGINT(20) UNSIGNED NULL DEFAULT NULL,
timestamp DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
old_value LONGTEXT NULL DEFAULT NULL,
new_value LONGTEXT NULL DEFAULT NULL,
ip_address VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (log_id),
KEY idx_user_id (user_id),
KEY idx_action (action),
KEY idx_object_type (object_type),
KEY idx_object_id (object_id),
KEY idx_timestamp (timestamp)
);
Key fields:
log_id: Unique identifier for each log entry.user_id: The WordPress user ID of the individual making the change. NULL for system-generated events.username: The username for easier readability and debugging.action: A descriptive string of the action performed (e.g., ‘subscription_created’, ‘subscription_updated’, ‘subscription_deleted’, ‘status_changed’).object_type: The type of object being modified (e.g., ‘subscription’, ‘customer’).object_id: The ID of the specific object being modified.timestamp: The date and time the action occurred.old_value: A serialized representation (e.g., JSON or PHP serialized array) of the object’s state *before* the modification.new_value: A serialized representation of the object’s state *after* the modification.ip_address: The IP address from which the action was performed.
Hooking into Subscription Modifications
To capture these modifications, we need to hook into the relevant WordPress actions and filters. Assuming you have a custom post type or a custom table for subscriptions, you’ll need to identify the save/update hooks for those. For custom tables, this often involves direct interaction within your plugin’s data access layer. For custom post types, WordPress’s built-in hooks are your friend.
Let’s assume a custom post type named ‘subscription‘ and a meta key ‘_subscription_details‘ that stores an array of subscription data. We’ll use the ‘save_post_subscription‘ hook.
/**
* Logs modifications to subscription post types.
*/
function my_plugin_log_subscription_changes( $post_id, $post, $update ) {
// Prevent infinite loops and autosaves.
if ( wp_is_post_revision( $post_id ) || wp_is_post_autosave( $post_id ) ) {
return;
}
// Ensure we are dealing with the correct post type.
if ( 'subscription' !== $post->post_type ) {
return;
}
// Get current user information.
$user_id = get_current_user_id();
$user = wp_get_current_user();
$username = $user->user_login ?? 'system'; // Fallback for system actions
// Get existing subscription details.
$old_details = get_post_meta( $post_id, '_subscription_details', true );
$new_details = get_post_meta( $post_id, '_subscription_details', true ); // This will be updated later in the save process
// Determine the action.
$action = $update ? 'subscription_updated' : 'subscription_created';
// If it's an update, we need to capture the state *before* the current save operation.
// This requires a slight adjustment: we'll capture the state *before* the meta update.
// A common pattern is to hook into `update_post_meta` or `save_post` and compare.
// For simplicity here, we'll assume `get_post_meta` *before* the save hook has access to the old data.
// In a real-world scenario, you might need to store the old meta value in a transient or a temporary variable
// if the meta is updated *before* your save_post hook runs its logic.
// For demonstration, let's assume $old_details holds the pre-save state.
// If $new_details is not yet updated by other plugins/processes, we'll fetch it again after meta update.
// Log the event.
my_plugin_log_event( array(
'user_id' => $user_id,
'username' => $username,
'action' => $action,
'object_type' => 'subscription',
'object_id' => $post_id,
'timestamp' => current_time( 'mysql' ),
'old_value' => ! empty( $old_details ) ? serialize( $old_details ) : null,
'new_value' => ! empty( $new_details ) ? serialize( $new_details ) : null, // This will be the *final* state after save
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
// If the subscription details meta key was updated, we need to ensure the 'new_value' reflects the *final* state.
// A more robust approach for 'new_value' would be to hook into `update_post_meta` for the specific meta key.
// For `save_post`, if other plugins might modify the meta *after* this hook, capturing the true 'new_value' is tricky.
// A common workaround is to re-fetch the meta *after* all other `save_post` actions have completed,
// or to use a higher priority hook for `save_post`.
}
add_action( 'save_post_subscription', 'my_plugin_log_subscription_changes', 10, 3 );
/**
* Helper function to log an event to the audit log table.
*
* @param array $data Log data.
*/
function my_plugin_log_event( $data ) {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_logs';
// Ensure required fields are present.
$log_entry = wp_parse_args( $data, array(
'user_id' => null,
'username' => 'system',
'action' => 'generic_event',
'object_type' => 'unknown',
'object_id' => null,
'timestamp' => current_time( 'mysql' ),
'old_value' => null,
'new_value' => null,
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
$wpdb->insert( $table_name, $log_entry );
}
Important Considerations for `save_post` hook:
- Priority: The priority (e.g., 10) determines when your function runs relative to others hooked to the same action. If other plugins modify the subscription data *after* your hook runs, your captured 'new_value' might be inaccurate. You might need to use a higher priority (lower number) or a different hook.
- `$update` parameter: This boolean tells you if the post is being updated (true) or created (false).
- Autosaves and Revisions: Always check for autosaves and revisions to avoid cluttering your audit log with unnecessary entries.
- `$post` object: The `$post` object contains the *current* state of the post. To get the *old* state, you typically need to fetch it *before* the save operation or use a hook that fires earlier.
A more precise way to capture `old_value` and `new_value` for specific meta fields is to hook into `update_post_meta`.
/**
* Logs changes to specific subscription meta keys.
*/
function my_plugin_log_subscription_meta_changes( $meta_id, $object_id, $meta_key, $meta_value, $prev_value ) {
// Ensure we are dealing with the correct object type (e.g., post type 'subscription').
$post_type = get_post_type( $object_id );
if ( 'subscription' !== $post_type ) {
return;
}
// Target specific meta keys you want to audit.
$audited_meta_keys = array( '_subscription_details', '_subscription_status', '_customer_id' );
if ( ! in_array( $meta_key, $audited_meta_keys, true ) ) {
return;
}
// Get current user information.
$user_id = get_current_user_id();
$user = wp_get_current_user();
$username = $user->user_login ?? 'system';
// Determine the action based on the meta key and whether it's a new value or an update.
$action = 'subscription_meta_updated';
if ( $prev_value === false || $prev_value === '' ) { // Check if it's a new meta entry
$action = 'subscription_meta_created';
} elseif ( $meta_value === $prev_value ) { // No actual change
return;
}
// Log the event.
my_plugin_log_event( array(
'user_id' => $user_id,
'username' => $username,
'action' => $action . ':' . $meta_key, // e.g., 'subscription_meta_updated:_subscription_details'
'object_type' => 'subscription_meta',
'object_id' => $object_id,
'timestamp' => current_time( 'mysql' ),
'old_value' => ! empty( $prev_value ) ? serialize( $prev_value ) : null,
'new_value' => ! empty( $meta_value ) ? serialize( $meta_value ) : null,
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
}
add_action( 'update_post_meta', 'my_plugin_log_subscription_meta_changes', 10, 5 );
This `update_post_meta` hook provides ` $prev_value`, which is crucial for accurately logging the state before the change. We also refine the `object_type` and `action` to be more specific about meta changes.
Handling Custom Table Modifications
If your subscription data resides in a custom database table (e.g., `wp_my_subscriptions`), you'll need to wrap your CRUD operations within your plugin's data access functions and inject logging calls there.
Example within a hypothetical `MySubscriptionRepository` class:
class MySubscriptionRepository {
private $wpdb;
private $table_name;
public function __construct() {
global $wpdb;
$this->wpdb = $wpdb;
$this->table_name = $wpdb->prefix . 'my_subscriptions';
}
/**
* Creates a new subscription.
*
* @param array $data Subscription data.
* @return int|false Insert ID or false on failure.
*/
public function create( $data ) {
$user_id = get_current_user_id();
$user = wp_get_current_user();
$username = $user->user_login ?? 'system';
$insert_result = $this->wpdb->insert( $this->table_name, $data );
if ( $insert_result ) {
$new_subscription_id = $this->wpdb->insert_id;
my_plugin_log_event( array(
'user_id' => $user_id,
'username' => $username,
'action' => 'subscription_created',
'object_type' => 'custom_subscription',
'object_id' => $new_subscription_id,
'timestamp' => current_time( 'mysql' ),
'old_value' => null,
'new_value' => serialize( $data ),
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
}
return $insert_result ? $this->wpdb->insert_id : false;
}
/**
* Updates an existing subscription.
*
* @param int $subscription_id The ID of the subscription to update.
* @param array $data The data to update.
* @return bool True on success, false on failure.
*/
public function update( $subscription_id, $data ) {
$user_id = get_current_user_id();
$user = wp_get_current_user();
$username = $user->user_login ?? 'system';
// Fetch current data before update to log old_value.
$current_data = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE id = %d", $subscription_id ), ARRAY_A );
if ( ! $current_data ) {
return false; // Subscription not found.
}
$update_result = $this->wpdb->update( $this->table_name, $data, array( 'id' => $subscription_id ) );
if ( $update_result !== false ) { // Note: update_result is number of rows affected, not boolean
my_plugin_log_event( array(
'user_id' => $user_id,
'username' => $username,
'action' => 'subscription_updated',
'object_type' => 'custom_subscription',
'object_id' => $subscription_id,
'timestamp' => current_time( 'mysql' ),
'old_value' => serialize( $current_data ),
'new_value' => serialize( array_merge( $current_data, $data ) ), // Merge to show full new state
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
}
return $update_result !== false;
}
/**
* Deletes a subscription.
*
* @param int $subscription_id The ID of the subscription to delete.
* @return bool True on success, false on failure.
*/
public function delete( $subscription_id ) {
$user_id = get_current_user_id();
$user = wp_get_current_user();
$username = $user->user_login ?? 'system';
// Fetch current data before delete to log old_value.
$current_data = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE id = %d", $subscription_id ), ARRAY_A );
if ( ! $current_data ) {
return false; // Subscription not found.
}
$delete_result = $this->wpdb->delete( $this->table_name, array( 'id' => $subscription_id ) );
if ( $delete_result !== false ) { // Note: delete_result is number of rows affected, not boolean
my_plugin_log_event( array(
'user_id' => $user_id,
'username' => $username,
'action' => 'subscription_deleted',
'object_type' => 'custom_subscription',
'object_id' => $subscription_id,
'timestamp' => current_time( 'mysql' ),
'old_value' => serialize( $current_data ),
'new_value' => null, // No new value after deletion
'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1',
) );
}
return $delete_result !== false;
}
}
In this scenario, the logging logic is directly embedded within the repository methods. This ensures that every database operation is audited. The `my_plugin_log_event` function remains the central point for writing to the `wp_audit_logs` table.
Displaying and Searching Audit Logs
For an enterprise setup, a dedicated admin interface to view and search audit logs is essential. This would typically be a custom admin page within your plugin.
Key features for the admin interface:
- Table View: Display logs in a sortable, filterable table. Columns should include Timestamp, User, Action, Object Type, Object ID, and a summary of changes.
- Filtering: Allow filtering by date range, user, action type, and object ID.
- Search: Implement a search function that can query usernames, actions, and potentially the serialized `old_value`/`new_value` fields (though full-text search on `LONGTEXT` can be performance-intensive).
- Detail View: A modal or separate page to show the full `old_value` and `new_value` for a selected log entry, perhaps with a diff view for complex changes.
- Export: Functionality to export logs to CSV for external analysis or compliance.
When displaying `old_value` and `new_value`, remember they are serialized. You'll need to unserialize them (e.g., using `unserialize()` in PHP) and then format them for readability, perhaps using JSON encoding for structured data or a simple key-value display.
/**
* Renders the audit log admin page.
*/
function my_plugin_render_audit_log_page() {
// ... (HTML structure for table, filters, search form) ...
global $wpdb;
$table_name = $wpdb->prefix . 'audit_logs';
// Basic query for demonstration. Real implementation needs pagination, filtering, searching.
$logs = $wpdb->get_results( "SELECT * FROM {$table_name} ORDER BY timestamp DESC LIMIT 50" );
if ( $logs ) {
echo '<table class="wp-list-table widefat fixed striped">';
echo '<thead><tr><th>Timestamp</th><th>User</th><th>Action</th><th>Object</th><th>Details</th></tr></thead>';
echo '<tbody>';
foreach ( $logs as $log ) {
echo '<tr>';
echo '<td>' . esc_html( $log->timestamp ) . '</td>';
echo '<td>' . esc_html( $log->username ) . '</td>';
echo '<td>' . esc_html( $log->action ) . '</td>';
echo '<td>' . esc_html( $log->object_type ) . ':' . esc_html( $log->object_id ) . '</td>';
echo '<td><button class="button view-details" data-log-id="' . esc_attr( $log->log_id ) . '">View</button></td>';
echo '</tr>';
}
echo '</tbody></table>';
// Modal for details (JavaScript required)
echo '<div id="audit-log-modal" style="display:none;"><h3>Log Details</h3><pre id="log-details-content"></pre></div>';
} else {
echo '<p>No audit logs found.</p>';
}
// ... (JavaScript for modal interaction) ...
}
// Add the admin page
function my_plugin_add_audit_log_admin_page() {
add_menu_page(
'Audit Logs',
'Audit Logs',
'manage_options', // Capability required
'audit-logs',
'my_plugin_render_audit_log_page',
'dashicons-list-view', // Icon
80 // Position
);
}
add_action( 'admin_menu', 'my_plugin_add_audit_log_admin_page' );
// JavaScript for modal (enqueue this script)
function my_plugin_enqueue_audit_log_scripts( $hook ) {
if ( 'toplevel_page_audit-logs' !== $hook ) {
return;
}
// Enqueue jQuery and your custom script
wp_enqueue_script( 'jquery' );
wp_enqueue_script( 'my-audit-log-script', plugin_dir_url( __FILE__ ) . 'js/audit-log.js', array( 'jquery' ), '1.0', true );
}
add_action( 'admin_enqueue_scripts', 'my_plugin_enqueue_audit_log_scripts' );
The JavaScript file (`js/audit-log.js`) would handle AJAX requests to fetch detailed log data (including unserialized `old_value` and `new_value`) when the "View" button is clicked and display it in a modal. For enterprise-grade applications, consider using a JavaScript framework or a more sophisticated table library for enhanced interactivity.
Security and Performance Considerations
Security:
- Access Control: Ensure only authorized users (e.g., administrators, auditors) can access the audit log interface. Use WordPress's capability system (`manage_options` is a common choice).
- Data Integrity: The audit log table should ideally be append-only. Prevent direct modification or deletion of log entries by unauthorized means. Consider database-level triggers or application-level checks.
- Sensitive Data: Be cautious about what data you serialize into `old_value` and `new_value`. Avoid logging sensitive information like passwords or API keys directly. If necessary, mask or encrypt such fields.
- IP Address Logging: Ensure reliable IP address capture. Be aware of proxies and load balancers that might obscure the original client IP.
Performance:
- Indexing: The database indexes on `user_id`, `action`, `object_type`, `object_id`, and `timestamp` are crucial for efficient querying.
- Table Size: Audit logs can grow very large. Implement a log rotation or archiving strategy. This could involve moving older logs to a separate archive table or an external logging system (e.g., ELK stack, Splunk) after a certain period.
- Serialization: Serializing large data structures can impact performance. Consider logging only key fields or using JSON for better interoperability and potential indexing.
- Query Optimization: For the admin interface, use pagination and efficient SQL queries. Avoid full table scans, especially on large log tables.
- External Logging: For very high-traffic sites or strict compliance requirements, consider sending logs to a dedicated, scalable logging service rather than relying solely on the WordPress database.