Designing audit logs for enterprise WordPress setups tracking internal user modifications to real estate agent listings
Core Requirements for Enterprise Audit Logging in WordPress
Enterprise-grade WordPress deployments, particularly those managing critical data like real estate agent listings, necessitate robust audit logging. This isn’t merely about tracking who logged in; it’s about granularly recording modifications to specific data entities by internal users. For a real estate platform, this means tracking changes to agent profiles, property details linked to agents, and potentially even sensitive contact information. The logging system must be immutable, easily searchable, and integrated seamlessly into the WordPress ecosystem without compromising performance or security.
Key requirements include:
- Event Granularity: Log specific actions (create, update, delete) on defined data types (e.g., `agent_profile`, `property_listing`).
- User Attribution: Clearly link each logged event to the specific logged-in WordPress user performing the action.
- Timestamping: Accurate, server-side timestamps for every event.
- Data Integrity: Prevent tampering with log entries.
- Searchability: Efficient querying of logs based on user, date range, action, or data entity.
- Performance: Minimal impact on the front-end and back-end performance of the WordPress site.
- Scalability: Ability to handle a growing volume of log data.
Designing the Audit Log Schema
A custom database table is the most reliable approach for storing audit logs in a structured and performant manner. Relying solely on WordPress’s `wp_options` or post meta for logs is ill-advised due to performance bottlenecks and lack of structure. We’ll define a table, let’s call it `wp_audit_logs`, with the following schema:
This schema prioritizes essential information for effective auditing:
- `log_id` (BIGINT, UNSIGNED, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each log entry.
- `timestamp` (DATETIME): Server-side timestamp of the event.
- `user_id` (BIGINT, UNSIGNED, DEFAULT 0): WordPress user ID performing the action. 0 for system/cron events.
- `username` (VARCHAR(255)): Username for quick reference.
- `action` (VARCHAR(50)): The type of action performed (e.g., ‘create’, ‘update’, ‘delete’, ‘login’, ‘logout’).
- `object_type` (VARCHAR(100)): The type of entity being modified (e.g., ‘agent_profile’, ‘property_listing’, ‘user’).
- `object_id` (BIGINT, UNSIGNED): The ID of the specific object being modified.
- `details` (LONGTEXT): A JSON-encoded string containing specific field changes or relevant context.
- `ip_address` (VARCHAR(45)): The IP address from which the action originated.
The `details` field is crucial for capturing the “what” of the change. For an update action on an agent profile, it might look like this:
{
"changes": {
"phone_number": {
"old": "123-456-7890",
"new": "987-654-3210"
},
"email": {
"old": "[email protected]",
"new": "[email protected]"
}
},
"context": "Updated agent contact information via admin panel."
}
Database Table Creation and Management
The database table should be created upon plugin activation. We’ll use WordPress’s `$wpdb` global object for safe database interactions. A robust plugin would include a mechanism for schema versioning and updates.
Here’s a PHP snippet for the activation hook:
<?php
/**
* Plugin activation hook.
*/
function my_audit_log_activate() {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_logs';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
username varchar(255) DEFAULT '' NOT NULL,
action varchar(50) NOT NULL,
object_type varchar(100) NOT NULL,
object_id bigint(20) unsigned NOT NULL,
details longtext NOT NULL,
ip_address varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (log_id),
KEY idx_timestamp (timestamp),
KEY idx_user_id (user_id),
KEY idx_object (object_type, object_id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
// Optionally, set a plugin option to track the current schema version.
add_option( 'my_audit_log_db_version', '1.0' );
}
register_activation_hook( __FILE__, 'my_audit_log_activate' );
/**
* Plugin deactivation hook (optional, for cleanup).
*/
function my_audit_log_deactivate() {
// No table drop on deactivation by default for enterprise.
// Consider a separate uninstall script for complete removal.
}
register_deactivation_hook( __FILE__, 'my_audit_log_deactivate' );
/**
* Plugin uninstall hook (for complete removal).
*/
function my_audit_log_uninstall() {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_logs';
$wpdb->query( "DROP TABLE IF EXISTS $table_name" );
delete_option( 'my_audit_log_db_version' );
}
// register_uninstall_hook( __FILE__, 'my_audit_log_uninstall' ); // This would typically be in a separate uninstall.php file.
?>
The `dbDelta` function handles table creation and updates intelligently. Adding indexes on `timestamp`, `user_id`, and a composite index on `object_type` and `object_id` is crucial for query performance.
Hooking into WordPress Actions for Logging
The core of the logging mechanism involves hooking into relevant WordPress actions and filters. For real estate agent listings, this typically involves custom post types or custom database tables managed by a plugin. We’ll focus on a hypothetical custom post type `agent_profile` and a custom table `real_estate_listings` managed by a plugin.
Logging Custom Post Type (Agent Profiles) Modifications:
<?php
/**
* Logs changes to agent profiles.
*
* @param int $post_id The ID of the post being updated.
* @param WP_Post $post The post object.
* @param bool $update Whether this is an existing post being updated.
*/
function log_agent_profile_changes( $post_id, $post, $update ) {
// Ensure this is an agent profile post type and not an autosave or revision.
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
return;
}
if ( wp_is_post_revision( $post_id ) ) {
return;
}
if ( 'agent_profile' !== $post->post_type ) {
return;
}
// Only log if the user is logged in.
if ( ! is_user_logged_in() ) {
return;
}
$current_user = wp_get_current_user();
$action = $update ? 'update' : 'create';
$previous_post_data = $update ? get_post( $post_id ) : null; // Get previous data for comparison
// Compare fields to determine what changed.
$changes = array();
if ( $previous_post_data ) {
// Example: Compare post title
if ( $previous_post_data->post_title !== $post->post_title ) {
$changes['post_title'] = array(
'old' => $previous_post_data->post_title,
'new' => $post->post_title,
);
}
// Add more field comparisons here (e.g., post_content, custom fields)
// For custom fields, you'd use get_post_meta() on $post_id and compare.
$old_phone = get_post_meta( $post_id, '_agent_phone', true );
$new_phone = get_post_meta( $post_id, '_agent_phone', true ); // Note: This hook might run BEFORE meta is saved for new posts.
// For new posts, meta is saved *after* this hook.
// A better approach for new posts is to log the creation event.
if ( $old_phone !== $new_phone ) {
$changes['_agent_phone'] = array(
'old' => $old_phone,
'new' => $new_phone,
);
}
}
$log_details = array(
'changes' => $changes,
'context' => sprintf( 'Agent profile "%s" (%d) %s.', $post->post_title, $post_id, $action ),
);
// Log the event.
log_audit_event(
$current_user->ID,
$current_user->user_login,
$action,
'agent_profile',
$post_id,
$log_details,
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1' // Get IP address
);
}
add_action( 'save_post', 'log_agent_profile_changes', 10, 3 );
/**
* Logs creation of agent profiles.
* This hook is often better for initial creation as meta is saved later.
*
* @param int $post_id The ID of the post being saved.
*/
function log_agent_profile_creation( $post_id ) {
// Re-check conditions to ensure it's a new agent profile creation.
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
return;
}
if ( wp_is_post_revision( $post_id ) ) {
return;
}
// Check if the post was just created (post_date_gmt == post_modified_gmt for new posts)
$post = get_post( $post_id );
if ( 'agent_profile' !== $post->post_type || $post->post_date_gmt !== $post->post_modified_gmt ) {
return;
}
if ( ! is_user_logged_in() ) {
return;
}
$current_user = wp_get_current_user();
$log_details = array(
'context' => sprintf( 'New agent profile "%s" (%d) created.', $post->post_title, $post_id ),
'initial_data' => array( // Capture initial data if possible, though meta is saved later.
'title' => $post->post_title,
// 'meta_data' => get_post_meta($post_id) // This might be empty or incomplete here.
)
);
log_audit_event(
$current_user->ID,
$current_user->user_login,
'create',
'agent_profile',
$post_id,
$log_details,
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1'
);
}
// Use 'save_post_agent_profile' for more specific CPT logging, or a general hook with checks.
// The 'save_post' hook is more general but requires careful checks.
// For creation, hooking into 'wp_insert_post' might be more reliable for initial data capture.
// Let's refine this: use 'save_post' for updates and a separate mechanism for creation if needed.
// The initial 'save_post' hook for creation will capture basic post data. Meta will be logged by the update part of save_post if it runs again.
// A more robust approach for creation might involve hooking into the form submission or using a filter on post data *before* it's saved.
// For simplicity here, we'll rely on 'save_post' for both, acknowledging potential timing issues with meta.
// Let's simplify and rely on save_post for both, and refine the details capture.
// The 'save_post' hook runs *after* the post data is saved to the database.
// For custom fields (meta), they are saved *after* the main post object.
// To accurately capture changes for meta fields, we need to hook *after* meta is saved.
// The 'save_post_meta' action is not a standard WordPress hook.
// A common pattern is to hook into 'save_post' and then manually check/log meta changes.
// Revised approach for save_post to capture meta changes more reliably:
function log_agent_profile_changes_revised( $post_id, $post, $update ) {
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) return;
if ( wp_is_post_revision( $post_id ) ) return;
if ( 'agent_profile' !== $post->post_type ) return;
if ( ! is_user_logged_in() ) return;
$current_user = wp_get_current_user();
$action = $update ? 'update' : 'create';
$log_details = array( 'changes' => array() );
// --- Log basic post data changes ---
if ( $update ) {
$previous_post = get_post( $post_id ); // Fetch the *saved* previous version
if ( $previous_post->post_title !== $post->post_title ) {
$log_details['changes']['post_title'] = array( 'old' => $previous_post->post_title, 'new' => $post->post_title );
}
if ( $previous_post->post_content !== $post->post_content ) {
$log_details['changes']['post_content'] = array( 'old' => $previous_post->post_content, 'new' => $post->post_content );
}
} else {
// For creation, capture initial title and content
$log_details['changes']['post_title'] = array( 'new' => $post->post_title );
$log_details['changes']['post_content'] = array( 'new' => $post->post_content );
}
// --- Log custom field (meta) changes ---
// This requires knowing which meta keys to track.
$tracked_meta_keys = array( '_agent_phone', '_agent_email', '_agent_license_number' );
foreach ( $tracked_meta_keys as $meta_key ) {
$old_value = $update ? get_post_meta( $post_id, $meta_key, true ) : null;
$new_value = get_post_meta( $post_id, $meta_key, true ); // This gets the *just saved* value
// For creation, $old_value will be empty.
// For updates, compare $old_value with $new_value.
if ( $update ) {
if ( $old_value !== $new_value ) {
$log_details['changes'][$meta_key] = array( 'old' => $old_value, 'new' => $new_value );
}
} else {
// For creation, just log the new value if it's set.
if ( ! empty( $new_value ) ) {
$log_details['changes'][$meta_key] = array( 'new' => $new_value );
}
}
}
// Remove empty changes array if nothing was logged.
if ( empty( $log_details['changes'] ) ) {
// If no specific field changes were detected, log a general update/create event.
// This might happen if only status or taxonomy changed, or if meta fields were updated but not tracked.
// For enterprise, you might want to log *any* save operation on critical CPTs.
if ( $update ) {
$log_details['context'] = sprintf( 'Agent profile "%s" (%d) was updated (no specific tracked fields changed).', $post->post_title, $post_id );
} else {
$log_details['context'] = sprintf( 'New agent profile "%s" (%d) was created.', $post->post_title, $post_id );
}
} else {
$log_details['context'] = sprintf( 'Agent profile "%s" (%d) %s.', $post->post_title, $post_id, $action );
}
log_audit_event(
$current_user->ID,
$current_user->user_login,
$action,
'agent_profile',
$post_id,
$log_details,
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1'
);
}
// Use a lower priority to ensure post and meta data are fully saved.
add_action( 'save_post', 'log_agent_profile_changes_revised', 20, 3 );
/**
* Logs user login/logout events.
*/
function log_user_login_logout() {
if ( ! is_user_logged_in() ) {
// User is logging out or session expired.
// WordPress doesn't have a direct 'logout' action that reliably captures the user *before* they are logged out.
// A common workaround is to check for logged-in status on subsequent page loads.
// For simplicity, we'll log login events here. Logout logging is more complex.
return;
}
$current_user = wp_get_current_user();
// Check if this is a new login event. This is tricky as login happens on many requests.
// A more robust solution might involve transient checks or session management.
// For now, we'll log every request from a logged-in user, which is noisy.
// A better approach: Hook into 'wp_login' and 'wp_logout'.
// Let's use the dedicated login/logout hooks.
}
// Hook for successful login
function log_successful_login( $user_login, $user ) {
log_audit_event(
$user->ID,
$user_login,
'login',
'user',
$user->ID,
array( 'context' => 'User logged in successfully.' ),
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1'
);
}
add_action( 'wp_login', 'log_successful_login', 10, 2 );
// Hook for logout
function log_successful_logout( $user_id ) {
$user = get_user_by( 'id', $user_id );
if ( $user ) {
log_audit_event(
$user_id,
$user->user_login,
'logout',
'user',
$user_id,
array( 'context' => 'User logged out successfully.' ),
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1' // IP might be from the last request, not necessarily the logout itself.
);
}
}
add_action( 'wp_logout', 'log_successful_logout', 10 );
/**
* Generic function to log an audit event.
*
* @param int $user_id The user ID.
* @param string $username The username.
* @param string $action The action performed.
* @param string $object_type The type of object affected.
* @param int $object_id The ID of the object affected.
* @param array $details An array of details (will be JSON encoded).
* @param string $ip_address The IP address.
*/
function log_audit_event( $user_id, $username, $action, $object_type, $object_id, $details, $ip_address ) {
global $wpdb;
$table_name = $wpdb->prefix . 'audit_logs';
// Sanitize inputs
$user_id = absint( $user_id );
$username = sanitize_text_field( $username );
$action = sanitize_key( $action );
$object_type = sanitize_key( $object_type );
$object_id = absint( $object_id );
$ip_address = sanitize_text_field( $ip_address );
$details_json = wp_json_encode( $details );
// Ensure timestamp is set correctly
$timestamp = current_time( 'mysql' );
$wpdb->insert(
$table_name,
array(
'timestamp' => $timestamp,
'user_id' => $user_id,
'username' => $username,
'action' => $action,
'object_type' => $object_type,
'object_id' => $object_id,
'details' => $details_json,
'ip_address' => $ip_address,
),
array(
'%s', // timestamp
'%d', // user_id
'%s', // username
'%s', // action
'%s', // object_type
'%d', // object_id
'%s', // details (JSON string)
'%s', // ip_address
)
);
}
?>
The `save_post` hook is a common entry point for tracking changes to posts and custom post types. The revised `log_agent_profile_changes_revised` function demonstrates how to:
- Check for non-essential saves (autosaves, revisions).
- Verify the post type is `agent_profile`.
- Ensure a user is logged in.
- Differentiate between ‘create’ and ‘update’ actions.
- Compare current post data with previously saved data (for updates) to identify specific field changes.
- Iterate through a predefined list of critical custom fields (`tracked_meta_keys`) and log their old and new values.
- Capture initial values for new posts.
- Use the generic `log_audit_event` function to insert the record into the `wp_audit_logs` table.
Logging login/logout events is handled by dedicated hooks (`wp_login`, `wp_logout`) for better accuracy. The `log_audit_event` function is a crucial abstraction layer, ensuring consistent data formatting and sanitization before database insertion.
Handling Custom Table Modifications
If your real estate listings are managed in a custom database table (e.g., `wp_real_estate_listings`) rather than a CPT, the logging approach needs to adapt. You would hook into the functions that perform CRUD operations on this table.
Assuming you have functions like `save_listing_data( $listing_id, $data )` and `delete_listing( $listing_id )` within your custom plugin:
<?php
/**
* Wrapper function to log changes to custom listing data.
* This function should be called *after* the data has been successfully saved/deleted.
*
* @param int $listing_id The ID of the listing.
* @param array $new_data The new data being saved.
* @param array $old_data The old data (if updating).
* @param string $action 'create' or 'update'.
*/
function log_listing_modification( $listing_id, $new_data, $old_data = array(), $action = 'update' ) {
if ( ! is_user_logged_in() ) {
return;
}
$current_user = wp_get_current_user();
$changes = array();
if ( $action === 'update' ) {
// Compare $new_data with $old_data to find differences.
foreach ( $new_data as $key => $value ) {
if ( isset( $old_data[$key] ) ) {
if ( $old_data[$key] !== $value ) {
$changes[$key] = array( 'old' => $old_data[$key], 'new' => $value );
}
} else {
// New field added during update
$changes[$key] = array( 'new' => $value );
}
}
// Check for fields removed during update
foreach ( $old_data as $key => $value ) {
if ( ! isset( $new_data[$key] ) ) {
$changes[$key] = array( 'old' => $value, 'removed' => true );
}
}
} elseif ( $action === 'create' ) {
// For creation, log all new data.
$changes = $new_data;
}
$log_details = array(
'changes' => $changes,
'context' => sprintf( 'Listing ID %d %s.', $listing_id, $action ),
);
log_audit_event(
$current_user->ID,
$current_user->user_login,
$action,
'real_estate_listing', // Object type for custom table
$listing_id,
$log_details,
$_SERVER['REMOTE_ADDR'] ?? '127.0.0.1'
);
}
/**
* Example: Hooking into a hypothetical function that saves listing data.
* This assumes your plugin has a function `my_plugin_save_listing`.
*/
function my_plugin_save_listing_hook( $listing_id, $data ) {
// Fetch old data before saving
$old_data = array();
if ( $listing_id && $listing_id > 0 ) {
// Assume a function `get_listing_data_from_db` exists
$old_data = get_listing_data_from_db( $listing_id );
$action = 'update';
} else {
$action = 'create';
// For new listings, $listing_id might be 0 or null.
// The actual ID will be generated by the DB insert.
// We might need to log *after* the insert and retrieve the new ID.
// A better hook might be a filter *before* saving or a callback *after* insert.
}
// --- Perform the actual save operation ---
// $result = your_plugin_save_function( $listing_id, $data ); // This would be the actual DB call.
// Let's assume the save happens *after* this hook, and we need to capture data.
// A more robust pattern: Use filters or action hooks provided by your data management functions.
// If your save function returns the ID, you can use that.
// Example:
// $new_listing_id = your_plugin_save_function( $listing_id, $data );
// if ( $new_listing_id ) {
// log_listing_modification( $new_listing_id, $data, $old_data, $action );
// }
// For demonstration, let's assume $listing_id is valid and data is saved.
// If $listing_id is 0, it means creation. We need the *new* ID.
// This requires careful integration with your data layer.
// A common pattern is to hook into the *result* of the save operation.
// Let's refine this: Assume a filter hook `my_plugin_before_save_listing` and `my_plugin_after_save_listing`.
}
// Example using hypothetical filters:
function my_plugin_log_before_save( $data, $listing_id ) {
// Store current user and IP for later use if needed.
// This hook is good for capturing the *intended* changes.
// We can't log the final state here, but we can log the intent.
// For granular field diffs, we need the old state.
if ( $listing_id && $listing_id > 0 ) {
$old_data = get_listing_data_from_db( $listing_id );
// Store $old_data in a transient or a property for the after_save hook.
// This is getting complex. Let's stick to a simpler, direct logging approach.
}
return $data; // Pass data through
}
// add_filter( 'my_plugin_before_save_listing', 'my_plugin_log_before_save', 10, 2 );
/**
* A more direct approach: Wrap your CRUD functions.
*/
class RealEstateListingManager {
// ... other methods ...
public function save_listing( $listing_id, $data ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings';
$current_user = wp_get_current_user();
$action = 'update';
$old_data = array();
if ( ! $listing_id || $listing_id == 0 ) {
$action = 'create';
// Prepare data for insert
$insert_data = $this->prepare_data_for_db( $data ); // Your helper function
$result = $wpdb->insert( $table_name, $insert_data );
if ( $result ) {
$listing_id = $wpdb->insert_id;
// Log creation
log_listing_modification( $listing_id, $data, array(), 'create' );
}
return $result ? $listing_id : false;
} else {
// Fetch old data before update
$old_data = $this->get_listing_data_from_db( $listing_id ); // Your method to fetch data
// Prepare data for update
$update_data = $this->prepare_data_for_db( $data ); // Your helper function
$result = $wpdb->update( $table_name, $update_data, array( 'listing_id' => $listing_id ) );
if ( $result !== false ) { // update returns false on error, 0 if no rows updated, 1+ if rows updated
// Log update only if changes occurred
if ( $result > 0 ) {
log_listing_modification( $listing_id, $data, $old_data, 'update' );
}
}
return $result;
}
}
public function delete_listing( $listing_id ) {
global $wpdb;
$table_name = $wpdb->prefix . 'real_estate_listings';
$current_user = wp_get_current_user();
if ( ! $listing_id || $listing_id == 0 ) {
return false;
}
// Fetch data before deletion for logging
$listing_data = $this->get_listing_data_from_db( $listing_id );
$result = $wpdb->delete( $table_name, array( 'listing_id' => $listing_id ) );
if ( $result ) {
log_audit_event(
$current_user->ID,
$current_user->user_login,
'delete',
'real_estate_listing',
$listing_id