Designing audit logs for enterprise WordPress setups tracking internal user modifications to hospital clinic appointments
Database Schema for Audit Trails
For robust auditing of internal user modifications to hospital clinic appointments within a WordPress environment, a dedicated audit log table is essential. This table should capture granular details about each change, including who made the change, when it occurred, what was changed, and the specific appointment affected. We’ll design a simple yet effective schema for this purpose.
The table, let’s call it wp_clinic_appointment_audit_log, will have the following columns:
log_id(BIGINT, UNSIGNED, AUTO_INCREMENT, PRIMARY KEY): Unique identifier for each log entry.appointment_id(BIGINT, UNSIGNED, NOT NULL): The ID of the clinic appointment that was modified. This should be a foreign key to your appointments table (assuming a custom post type or a separate table for appointments).user_id(BIGINT, UNSIGNED, NOT NULL): The ID of the WordPress user who performed the action.action(VARCHAR(50), NOT NULL): The type of action performed (e.g., ‘created’, ‘updated’, ‘deleted’, ‘rescheduled’, ‘cancelled’).field_changed(VARCHAR(100), NULLABLE): The specific field that was modified (e.g., ‘appointment_date’, ‘patient_name’, ‘doctor_id’, ‘status’). NULL if the action doesn’t target a specific field (e.g., ‘created’, ‘deleted’).old_value(LONGTEXT, NULLABLE): The value of the field before the change. Use LONGTEXT to accommodate potentially large data.new_value(LONGTEXT, NULLABLE): The value of the field after the change.timestamp(DATETIME, NOT NULL, DEFAULT CURRENT_TIMESTAMP): The date and time when the action occurred.ip_address(VARCHAR(45), NULLABLE): The IP address from which the action was performed.
To create this table, you can use a SQL query executed via a WordPress plugin’s activation hook or a database management tool:
SQL Table Creation
CREATE TABLE wp_clinic_appointment_audit_log (
log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
appointment_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
action VARCHAR(50) NOT NULL,
field_changed VARCHAR(100) NULL,
old_value LONGTEXT NULL,
new_value LONGTEXT NULL,
timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(45) NULL,
INDEX idx_appointment_id (appointment_id),
INDEX idx_user_id (user_id),
INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
It’s crucial to add indexes on appointment_id, user_id, and timestamp for efficient querying of audit data.
Plugin Structure and Activation
We’ll create a simple WordPress plugin to manage this audit logging. The plugin will include an activation hook to create the database table if it doesn’t already exist.
Create a new directory in wp-content/plugins/, for example, clinic-appointment-auditor. Inside this directory, create the main plugin file, clinic-appointment-auditor.php.
Plugin Header and Activation Hook
/*
Plugin Name: Clinic Appointment Auditor
Plugin URI: https://example.com/clinic-appointment-auditor
Description: Audits modifications to clinic appointments by internal users.
Version: 1.0
Author: Your Name
Author URI: https://example.com
License: GPL2
*/
// Prevent direct access to the file
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
/**
* Plugin activation hook.
* Creates the audit log table.
*/
function caa_activate_plugin() {
global $wpdb;
$table_name = $wpdb->prefix . 'clinic_appointment_audit_log';
$charset_collate = $wpdb->get_charset_collate();
// Check if table exists
if ( $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" ) !== $table_name ) {
$sql = "CREATE TABLE $table_name (
log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
appointment_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
action VARCHAR(50) NOT NULL,
field_changed VARCHAR(100) NULL,
old_value LONGTEXT NULL,
new_value LONGTEXT NULL,
timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(45) NULL,
INDEX idx_appointment_id (appointment_id),
INDEX idx_user_id (user_id),
INDEX idx_timestamp (timestamp)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
}
register_activation_hook( __FILE__, 'caa_activate_plugin' );
/**
* Add a deactivation hook to clean up if necessary (optional).
*/
function caa_deactivate_plugin() {
// Optionally, you could add code here to remove the table on deactivation,
// but it's generally safer to leave audit data.
}
register_deactivation_hook( __FILE__, 'caa_deactivate_plugin' );
When this plugin is activated, the caa_activate_plugin function will run, checking for the existence of the audit log table and creating it if necessary using WordPress’s dbDelta function.
Hooking into Appointment Modifications
The core of the auditing logic lies in hooking into the processes that modify clinic appointments. This will depend heavily on how clinic appointments are managed. If they are a custom post type (CPT), we’ll use WordPress’s post-related hooks. If they are stored in a custom table, we’ll need to hook into the functions that perform CRUD operations on that table.
Let’s assume clinic appointments are managed as a custom post type named clinic_appointment. We’ll use the save_post hook, which fires after a post or page has been saved to the database.
Logging Post Saves
/**
* Logs changes to clinic appointments.
*
* @param int $post_id The ID of the post being saved.
* @param WP_Post $post The post object.
* @param bool $update Whether this is an existing post being updated.
*/
function caa_log_appointment_save( $post_id, $post, $update ) {
// Check if it's an autosave
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
return $post_id;
}
// Check if it's a revision
if ( wp_is_post_revision( $post_id ) ) {
return $post_id;
}
// Check if it's our custom post type
if ( 'clinic_appointment' !== $post->post_type ) {
return $post_id;
}
// Ensure the user has permission to edit posts (or a more specific capability)
if ( ! current_user_can( 'edit_post', $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, but usually we want logged-in users.
// For this example, we'll skip if no user is logged in.
return $post_id;
}
// Get IP Address
$ip_address = '';
if ( ! empty( $_SERVER['REMOTE_ADDR'] ) ) {
$ip_address = sanitize_text_field( $_SERVER['REMOTE_ADDR'] );
}
// Determine action
$action = $update ? 'updated' : 'created';
// If it's a new post, we only need to log the creation.
if ( ! $update ) {
caa_add_audit_log( $post_id, $user_id, 'created', null, null, null, $ip_address );
return $post_id;
}
// For updates, compare old and new values
$old_post_data = get_post_meta( $post_id, '_original_post_data', true ); // We'll store this temporarily
// If _original_post_data is not set, it means this is the first save after creation,
// or we haven't implemented storing previous versions.
// For simplicity, let's assume we can fetch previous data if available, or log a general update.
// A more robust solution would involve storing the previous state before saving.
// Let's simulate fetching previous data for demonstration.
// In a real scenario, you'd likely fetch this from a transient, cache, or a dedicated meta field
// that is updated *before* the save_post hook processes the current data.
// For now, we'll log a general 'updated' if we can't diff specific fields.
// --- Advanced Diffing Logic ---
// To properly diff, we need access to the post data *before* it was saved.
// A common pattern is to hook into 'wp_insert_post_data' and store the old data in a transient or meta.
// For this example, let's assume we have a way to get the old data.
// If not, we'll log a general 'updated' action.
// Example: Fetching data from a meta field that was populated *before* this save.
// This requires a separate mechanism, e.g., hooking into 'edit_form_before_save'.
// For demonstration, let's assume we have a function `caa_get_previous_post_data($post_id)`
// that returns an array of the post's data before the current save.
$previous_post = caa_get_previous_post_data( $post_id ); // Placeholder function
if ( $previous_post ) {
$current_post_data = array(
'post_title' => $post->post_title,
'post_content' => $post->post_content,
// Add other relevant fields like custom meta keys for appointments
'appointment_date' => get_post_meta( $post_id, 'appointment_date', true ),
'doctor_id' => get_post_meta( $post_id, 'doctor_id', true ),
'patient_id' => get_post_meta( $post_id, 'patient_id', true ),
'status' => $post->post_status, // Or a custom status meta
);
// Compare fields
$fields_to_compare = array(
'post_title',
'post_content',
'appointment_date',
'doctor_id',
'patient_id',
'status',
);
foreach ( $fields_to_compare as $field ) {
$old_value = isset( $previous_post[$field] ) ? $previous_post[$field] : null;
$new_value = isset( $current_post_data[$field] ) ? $current_post_data[$field] : null;
if ( $old_value !== $new_value ) {
caa_add_audit_log( $post_id, $user_id, 'updated', $field, $old_value, $new_value, $ip_address );
}
}
} else {
// Fallback: Log a general update if we can't diff specific fields.
// This might happen if the previous data wasn't captured correctly.
caa_add_audit_log( $post_id, $user_id, 'updated', null, null, null, $ip_address );
}
// --- End Advanced Diffing Logic ---
return $post_id;
}
add_action( 'save_post', 'caa_log_appointment_save', 10, 3 );
/**
* Placeholder function to simulate fetching previous post data.
* In a real implementation, this would retrieve data stored *before* the save.
* This could be done by hooking into 'wp_insert_post_data' and storing the old data
* in a transient or a temporary meta field.
*
* @param int $post_id
* @return array|null
*/
function caa_get_previous_post_data( $post_id ) {
// Example: Retrieve from a transient.
// $transient_key = 'caa_prev_post_data_' . $post_id;
// $previous_data = get_transient( $transient_key );
// delete_transient( $transient_key ); // Clean up after use
// For demonstration, let's return a hardcoded example or null.
// A real implementation needs to store this data *before* the save.
// For instance, in 'wp_insert_post_data' hook:
/*
add_filter( 'wp_insert_post_data', function( $data, $postarr ) use ( $post_id ) {
if ( isset( $postarr['ID'] ) && $postarr['ID'] == $post_id ) {
$old_post = get_post( $post_id );
if ( $old_post ) {
$old_meta = get_post_meta( $post_id );
$previous_data = array(
'post_title' => $old_post->post_title,
'post_content' => $old_post->post_content,
'appointment_date' => isset( $old_meta['appointment_date'][0] ) ? $old_meta['appointment_date'][0] : null,
'doctor_id' => isset( $old_meta['doctor_id'][0] ) ? $old_meta['doctor_id'][0] : null,
'patient_id' => isset( $old_meta['patient_id'][0] ) ? $old_meta['patient_id'][0] : null,
'status' => $old_post->post_status,
);
set_transient( 'caa_prev_post_data_' . $post_id, $previous_data, HOUR_IN_SECONDS );
}
}
return $data;
}, 10, 2 );
*/
// Returning null for now to indicate the need for a proper diffing mechanism.
return null;
}
The caa_log_appointment_save function performs several crucial checks:
- It prevents logging for autosaves and revisions.
- It verifies that the post type is indeed
clinic_appointment. - It checks if the current user has the necessary permissions to edit the post.
- It retrieves the current user’s ID and IP address.
- For new posts (
!$update), it logs a single ‘created’ action. - For existing posts (
$update), it attempts to compare the old and new values of relevant fields. This is the most complex part. A robust solution requires capturing the ‘old’ state of the post and its meta *before* thesave_posthook processes the current data. The placeholder functioncaa_get_previous_post_dataillustrates where this logic would reside. A common pattern is to use thewp_insert_post_datafilter to store the previous data in a transient or temporary meta field.
Helper Function to Add Audit Log Entry
/**
* Adds a single entry to the audit log table.
*
* @param int $appointment_id The ID of the appointment.
* @param int $user_id The ID of the user performing the action.
* @param string $action The action performed (e.g., 'created', 'updated').
* @param string $field_changed The specific field that was changed (optional).
* @param mixed $old_value The old value of the field (optional).
* @param mixed $new_value The new value of the field (optional).
* @param string $ip_address The IP address of the user (optional).
*/
function caa_add_audit_log( $appointment_id, $user_id, $action, $field_changed = null, $old_value = null, $new_value = null, $ip_address = null ) {
global $wpdb;
$table_name = $wpdb->prefix . 'clinic_appointment_audit_log';
// Sanitize and prepare values
$appointment_id = absint( $appointment_id );
$user_id = absint( $user_id );
$action = sanitize_text_field( $action );
$field_changed = ! is_null( $field_changed ) ? sanitize_text_field( $field_changed ) : null;
$ip_address = ! is_null( $ip_address ) ? sanitize_text_field( $ip_address ) : null;
// Convert values to strings for storage, handling potential complex types
$old_value_str = ! is_null( $old_value ) ? maybe_serialize( $old_value ) : null;
$new_value_str = ! is_null( $new_value ) ? maybe_serialize( $new_value ) : null;
// Ensure values don't exceed LONGTEXT limits (though unlikely with serialization)
if ( $old_value_str && strlen( $old_value_str ) > 65535 ) { // LONGTEXT max is 4GB, but DB might have limits
$old_value_str = substr( $old_value_str, 0, 65535 ) . '... (truncated)';
}
if ( $new_value_str && strlen( $new_value_str ) > 65535 ) {
$new_value_str = substr( $new_value_str, 0, 65535 ) . '... (truncated)';
}
$wpdb->insert(
$table_name,
array(
'appointment_id' => $appointment_id,
'user_id' => $user_id,
'action' => $action,
'field_changed' => $field_changed,
'old_value' => $old_value_str,
'new_value' => $new_value_str,
'ip_address' => $ip_address,
),
array(
'%d', // appointment_id
'%d', // user_id
'%s', // action
'%s', // field_changed
'%s', // old_value
'%s', // new_value
'%s', // ip_address
)
);
}
This caa_add_audit_log function is a utility that handles the actual database insertion. It sanitizes all input, uses maybe_serialize to store potentially complex data types (like arrays or objects) in the LONGTEXT fields, and then inserts the record into the wp_clinic_appointment_audit_log table. The format array ensures correct data type handling by $wpdb->insert.
Handling Other Actions (Deletion, Cancellation)
Beyond simple updates, we need to log other significant actions like deletion or cancellation. These might use different hooks or require specific logic within the save_post hook (e.g., if an appointment is moved to a ‘cancelled’ status).
Logging Deletions
The delete_post hook fires when a post is deleted. We can use this to log the deletion event.
/**
* Logs the deletion of a clinic appointment.
*
* @param int $post_id The ID of the post being deleted.
*/
function caa_log_appointment_delete( $post_id ) {
// Verify it's our CPT
$post = get_post( $post_id );
if ( ! $post || 'clinic_appointment' !== $post->post_type ) {
return;
}
// We don't have the user ID directly in this hook, but we can try to get it
// from the current user if the deletion is initiated via the admin interface.
// For deletions initiated by cron jobs or external systems, this might be harder.
$user_id = get_current_user_id();
if ( ! $user_id ) {
// Attempt to get user from $_POST if available (e.g., bulk delete)
if ( isset( $_POST['user_id'] ) ) {
$user_id = absint( $_POST['user_id'] );
} else {
// Fallback to a system user or log as unknown if necessary
$user_id = 0; // Or a specific ID for 'system'
}
}
$ip_address = '';
if ( ! empty( $_SERVER['REMOTE_ADDR'] ) ) {
$ip_address = sanitize_text_field( $_SERVER['REMOTE_ADDR'] );
}
// Log the deletion. No field_changed, old_value, new_value needed.
caa_add_audit_log( $post_id, $user_id, 'deleted', null, null, null, $ip_address );
}
add_action( 'delete_post', 'caa_log_appointment_delete', 10, 1 );
Note that getting the user ID reliably in the delete_post hook can be tricky, especially if the deletion is not directly initiated by an admin user through the UI. The code includes a basic attempt to retrieve it.
Logging Status Changes (e.g., Cancellation)
If ‘cancelled’ or ‘rescheduled’ are represented by changes in the post status or a custom meta field (e.g., _appointment_status), the save_post hook will already handle these if they are included in the diffing logic. However, you might want to log these specific actions with more descriptive names.
/**
* Custom logging for specific status changes.
* This function would be called from within caa_log_appointment_save
* if a specific status change is detected.
*/
function caa_log_specific_status_change( $post_id, $user_id, $old_status, $new_status, $ip_address ) {
if ( $old_status !== $new_status ) {
if ( 'cancelled' === $new_status ) {
caa_add_audit_log( $post_id, $user_id, 'cancelled', 'status', $old_status, $new_status, $ip_address );
} elseif ( 'rescheduled' === $new_status ) { // Assuming 'rescheduled' is a status or meta value
caa_add_audit_log( $post_id, $user_id, 'rescheduled', 'status', $old_status, $new_status, $ip_address );
} else {
// Log general status update if not a specific named action
caa_add_audit_log( $post_id, $user_id, 'updated', 'status', $old_status, $new_status, $ip_address );
}
}
}
// --- Integration into caa_log_appointment_save ---
// Inside caa_log_appointment_save, after fetching $previous_post and $current_post_data:
/*
$old_status = isset( $previous_post['status'] ) ? $previous_post['status'] : null;
$new_status = isset( $current_post_data['status'] ) ? $current_post_data['status'] : null;
if ( $old_status !== $new_status ) {
caa_log_specific_status_change( $post_id, $user_id, $old_status, $new_status, $ip_address );
}
// ... rest of the field comparison logic ...
*/
This approach allows for more semantically meaningful log entries for critical actions like cancellations, even if they are technically just status updates.
Displaying Audit Logs
To make the audit logs useful, they need to be accessible. This typically involves creating a new admin page or adding a meta box to the appointment edit screen.
Admin Page for Audit Logs
We can create a top-level menu item in the WordPress admin area to display the audit logs. This page will query the wp_clinic_appointment_audit_log table and present the data in a sortable, filterable table.
/**
* Adds an admin menu page for viewing audit logs.
*/
function caa_add_admin_menu() {
add_menu_page(
__( 'Appointment Audit Log', 'clinic-appointment-auditor' ),
__( 'Audit Log', 'clinic-appointment-auditor' ),
'manage_options', // Capability required to view
'clinic-appointment-audit-log',
'caa_render_audit_log_page',
'dashicons-list-view', // Icon
85 // Position
);
}
add_action( 'admin_menu', 'caa_add_admin_menu' );
/**
* Renders the audit log admin page.
*/
function caa_render_audit_log_page() {
global $wpdb;
$table_name = $wpdb->prefix . 'clinic_appointment_audit_log';
// Basic pagination and filtering would be implemented here.
// For simplicity, we'll fetch all logs.
$logs = $wpdb->get_results( "SELECT * FROM $table_name ORDER BY timestamp DESC" );
?>
This code adds a menu item and a basic table to display the logs. For a production environment, you would need to implement pagination, sorting, and filtering (e.g., by date range, user, or appointment ID) to handle potentially large numbers of log entries efficiently.
Security and Best Practices
When implementing an audit log system, several security and best practice considerations are paramount:
- Permissions: Ensure that only authorized users (e.g., administrators, auditors) can view the audit logs. The
manage_optionscapability is used in the example, but you might define a custom capability for this role. - Data Integrity: Protect the audit log table from unauthorized modifications or deletions. This can be achieved through database user permissions and by ensuring your plugin code is secure.
- Data Retention: Define a policy for how long audit logs should be retained. For compliance reasons (e.g., HIPAA in healthcare), logs might need to be kept for several years. Implement a mechanism for archiving or purging old logs.
- Performance: As the audit log table grows, it can impact database performance. Regularly review and optimize queries, and consider archiving older data to a separate table or database.
- Error Handling: Implement robust error handling in your logging functions to ensure that logging failures do not disrupt the primary functionality of your application.
- Serialization/Deserialization: Be cautious when storing and retrieving complex data types. Use
maybe_serializeandmaybe_unserialize(or equivalent) and always sanitize output when displaying serialized data. - IP Address Logging: Be aware of privacy regulations regarding IP address logging. Ensure compliance with local laws and provide clear notice to users if necessary.
By following these guidelines and implementing the provided code structure, you can build a comprehensive and reliable audit logging system for your enterprise WordPress setup, ensuring accountability and traceability for critical modifications to hospital clinic appointments.