Designing audit logs for enterprise WordPress setups tracking internal user modifications to affiliate click tracking logs
Database Schema for Affiliate Click Tracking and Audit Logs
For enterprise-grade WordPress e-commerce setups, robust affiliate click tracking is paramount. This involves not just recording clicks but also ensuring the integrity of that data, especially when internal users might have administrative access to modify these records. A common pitfall is a flat, unversioned log table. We need a multi-table approach to separate raw click data from audit trails and to facilitate efficient querying.
Let’s define the core database tables. We’ll use MySQL for this example, as it’s the de facto standard for WordPress. The primary tables are `wp_affiliate_clicks` for raw click data and `wp_affiliate_clicks_audit` for tracking modifications.
`wp_affiliate_clicks` Table Structure
This table stores each individual affiliate click event. It should be indexed for performance, particularly on `click_id`, `affiliate_id`, and `timestamp`.
CREATE TABLE wp_affiliate_clicks (
click_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
affiliate_id INT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NULL, -- For logged-in users
session_id VARCHAR(255) NULL, -- For anonymous tracking
ip_address VARCHAR(45) NULL, -- IPv6 compatible
user_agent TEXT NULL,
referrer_url TEXT NULL,
landing_page_url TEXT NULL,
product_id INT UNSIGNED NULL,
order_id BIGINT UNSIGNED NULL, -- If a sale is attributed
conversion_timestamp DATETIME NULL,
click_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_affiliate_id (affiliate_id),
INDEX idx_user_id (user_id),
INDEX idx_order_id (order_id),
INDEX idx_click_timestamp (click_timestamp)
);
`wp_affiliate_clicks_audit` Table Structure
This table is crucial for auditing. Each row represents a change made to a record in `wp_affiliate_clicks`. It should include details about who made the change, when, and what the old and new values were. This allows for full rollback and accountability.
CREATE TABLE wp_affiliate_clicks_audit (
audit_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
original_click_id BIGINT UNSIGNED NOT NULL, -- FK to wp_affiliate_clicks
action_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
changed_by_user_id BIGINT UNSIGNED NULL, -- User ID of the admin making the change
change_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
old_data JSON NULL, -- Stores the entire old row data as JSON
new_data JSON NULL, -- Stores the entire new row data as JSON (for UPDATE/INSERT)
reason TEXT NULL, -- Optional field for admin to explain the change
INDEX idx_original_click_id (original_click_id),
INDEX idx_changed_by_user_id (changed_by_user_id),
INDEX idx_change_timestamp (change_timestamp)
);
The use of a JSON type for `old_data` and `new_data` provides flexibility. For older MySQL versions without native JSON support, you could use `LONGTEXT` and serialize/unserialize data (e.g., using PHP’s `json_encode`/`json_decode`).
Implementing Audit Triggers (MySQL)
The most robust way to ensure all modifications are logged is by using database triggers. These are stored procedures that automatically execute in response to certain events on a particular table. We’ll create triggers for `INSERT`, `UPDATE`, and `DELETE` operations on `wp_affiliate_clicks`.
`AFTER INSERT` Trigger
This trigger logs the initial creation of a click record. While not strictly an “audit” of modification, it’s good practice to have a baseline for all records.
DELIMITER $$
CREATE TRIGGER trg_affiliate_clicks_after_insert
AFTER INSERT ON wp_affiliate_clicks
FOR EACH ROW
BEGIN
-- Log the initial insert. We'll store the new data.
INSERT INTO wp_affiliate_clicks_audit (original_click_id, action_type, new_data, reason)
VALUES (
NEW.click_id,
'INSERT',
JSON_OBJECT(
'affiliate_id', NEW.affiliate_id,
'user_id', NEW.user_id,
'session_id', NEW.session_id,
'ip_address', NEW.ip_address,
'user_agent', NEW.user_agent,
'referrer_url', NEW.referrer_url,
'landing_page_url', NEW.landing_page_url,
'product_id', NEW.product_id,
'order_id', NEW.order_id,
'conversion_timestamp', NEW.conversion_timestamp,
'click_timestamp', NEW.click_timestamp
),
'Initial record creation' -- Default reason
);
END$$
DELIMITER ;
`AFTER UPDATE` Trigger
This trigger captures any changes made to an existing click record. It logs the old state and the new state.
DELIMITER $$
CREATE TRIGGER trg_affiliate_clicks_after_update
AFTER UPDATE ON wp_affiliate_clicks
FOR EACH ROW
BEGIN
-- Only log if there's an actual change in data
IF NOT OLD.affiliate_id <=> NEW.affiliate_id OR
NOT OLD.user_id <=> NEW.user_id OR
NOT OLD.session_id <=> NEW.session_id OR
NOT OLD.ip_address <=> NEW.ip_address OR
NOT OLD.user_agent <=> NEW.user_agent OR
NOT OLD.referrer_url <=> NEW.referrer_url OR
NOT OLD.landing_page_url <=> NEW.landing_page_url OR
NOT OLD.product_id <=> NEW.product_id OR
NOT OLD.order_id <=> NEW.order_id OR
NOT OLD.conversion_timestamp <=> NEW.conversion_timestamp
THEN
INSERT INTO wp_affiliate_clicks_audit (original_click_id, action_type, changed_by_user_id, old_data, new_data, reason)
VALUES (
OLD.click_id,
'UPDATE',
-- Attempt to get the current logged-in WordPress user ID.
-- This requires a mechanism to pass it to the DB, or a function that can access it.
-- For simplicity, we'll use a placeholder or a function call if available.
-- In a real PHP app, you'd likely pass this via a stored procedure parameter or a global variable.
-- Let's assume a function `get_current_wp_user_id()` exists or is passed.
-- If not, this field will be NULL.
(SELECT IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(info, '$.user_id')) AS UNSIGNED), 0) FROM information_schema.processlist WHERE id = CONNECTION_ID()), -- Placeholder, needs proper implementation
JSON_OBJECT(
'affiliate_id', OLD.affiliate_id,
'user_id', OLD.user_id,
'session_id', OLD.session_id,
'ip_address', OLD.ip_address,
'user_agent', OLD.user_agent,
'referrer_url', OLD.referrer_url,
'landing_page_url', OLD.landing_page_url,
'product_id', OLD.product_id,
'order_id', OLD.order_id,
'conversion_timestamp', OLD.conversion_timestamp,
'click_timestamp', OLD.click_timestamp
),
JSON_OBJECT(
'affiliate_id', NEW.affiliate_id,
'user_id', NEW.user_id,
'session_id', NEW.session_id,
'ip_address', NEW.ip_address,
'user_agent', NEW.user_agent,
'referrer_url', NEW.referrer_url,
'landing_page_url', NEW.landing_page_url,
'product_id', NEW.product_id,
'order_id', NEW.order_id,
'conversion_timestamp', NEW.conversion_timestamp,
'click_timestamp', NEW.click_timestamp
),
'Modification by admin' -- Placeholder for reason, ideally passed from application
);
END IF;
END$$
DELIMITER ;
Note on `changed_by_user_id`: Capturing the `changed_by_user_id` directly within a standard MySQL trigger is challenging without passing it explicitly. The placeholder `(SELECT IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(info, ‘$.user_id’)) AS UNSIGNED), 0) FROM information_schema.processlist WHERE id = CONNECTION_ID())` is a common, albeit imperfect, technique to try and retrieve session variables or custom session data if your application sets them. A more reliable method involves creating stored procedures for data modification that accept the user ID as a parameter, or using application-level logic to log changes if direct DB triggers are too complex to manage user context.
`AFTER DELETE` Trigger
This trigger logs when a click record is removed. It’s critical for understanding data integrity issues.
DELIMITER $$
CREATE TRIGGER trg_affiliate_clicks_after_delete
AFTER DELETE ON wp_affiliate_clicks
FOR EACH ROW
BEGIN
INSERT INTO wp_affiliate_clicks_audit (original_click_id, action_type, changed_by_user_id, old_data, reason)
VALUES (
OLD.click_id,
'DELETE',
-- Placeholder for changed_by_user_id, same considerations as UPDATE trigger
(SELECT IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(info, '$.user_id')) AS UNSIGNED), 0) FROM information_schema.processlist WHERE id = CONNECTION_ID()), -- Placeholder
JSON_OBJECT(
'affiliate_id', OLD.affiliate_id,
'user_id', OLD.user_id,
'session_id', OLD.session_id,
'ip_address', OLD.ip_address,
'user_agent', OLD.user_agent,
'referrer_url', OLD.referrer_url,
'landing_page_url', OLD.landing_page_url,
'product_id', OLD.product_id,
'order_id', OLD.order_id,
'conversion_timestamp', OLD.conversion_timestamp,
'click_timestamp', OLD.click_timestamp
),
'Record deleted by admin' -- Placeholder for reason
);
END$$
DELIMITER ;
WordPress Plugin Integration: PHP Implementation
While database triggers handle the low-level logging, a WordPress plugin is needed to manage the user interface for viewing audit logs, potentially adding reasons for changes, and implementing logic to prevent unauthorized modifications. We’ll focus on the PHP code for interacting with these tables and the audit log.
Retrieving Audit Logs for a Specific Click
This function demonstrates how to fetch all audit entries for a given `click_id`.
/**
* Retrieves all audit logs for a specific affiliate click.
*
* @param int $click_id The ID of the click record to audit.
* @return array An array of audit log entries, or an empty array on failure.
*/
function get_affiliate_click_audit_logs( int $click_id ): array {
global $wpdb;
$table_name = $wpdb->prefix . 'affiliate_clicks_audit';
// Ensure the table exists
if ( $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) !== $table_name ) {
error_log( "Affiliate click audit table '{$table_name}' does not exist." );
return [];
}
$sql = $wpdb->prepare(
"SELECT * FROM {$table_name} WHERE original_click_id = %d ORDER BY change_timestamp DESC",
$click_id
);
$results = $wpdb->get_results( $sql, ARRAY_A );
if ( $wpdb->last_error ) {
error_log( "Database error fetching audit logs for click ID {$click_id}: " . $wpdb->last_error );
return [];
}
// Decode JSON data for easier handling in PHP
foreach ( $results as &$row ) {
if ( ! empty( $row['old_data'] ) ) {
$row['old_data'] = json_decode( $row['old_data'], true );
}
if ( ! empty( $row['new_data'] ) ) {
$row['new_data'] = json_decode( $row['new_data'], true );
}
}
return $results;
}
Logging a Manual Modification (Application Level)
When an administrator manually edits a click record through the WordPress admin interface, you need to capture the `changed_by_user_id` and potentially the `reason`. This PHP function illustrates how to log an update, bypassing the direct DB trigger for user context and reason.
/**
* Manually logs an update to an affiliate click record, including audit trail.
* This function should be called *after* the record has been updated in the database.
*
* @param int $click_id The ID of the click record that was modified.
* @param array $old_data The data of the record *before* modification.
* @param array $new_data The data of the record *after* modification.
* @param string|null $reason An optional reason for the modification.
* @return bool True on success, false on failure.
*/
function log_manual_affiliate_click_update( int $click_id, array $old_data, array $new_data, ?string $reason = null ): bool {
global $wpdb;
$audit_table = $wpdb->prefix . 'affiliate_clicks_audit';
// Ensure the table exists
if ( $wpdb->get_var( "SHOW TABLES LIKE '{$audit_table}'" ) !== $audit_table ) {
error_log( "Affiliate click audit table '{$audit_table}' does not exist." );
return false;
}
$current_user_id = get_current_user_id(); // WordPress function to get logged-in user ID
// Sanitize and prepare data for JSON storage
$old_data_json = ! empty( $old_data ) ? wp_json_encode( $old_data ) : null;
$new_data_json = ! empty( $new_data ) ? wp_json_encode( $new_data ) : null;
$inserted = $wpdb->insert(
$audit_table,
array(
'original_click_id' => $click_id,
'action_type' => 'UPDATE',
'changed_by_user_id' => $current_user_id,
'old_data' => $old_data_json,
'new_data' => $new_data_json,
'reason' => sanitize_textarea_field( $reason ),
),
array(
'%d', // original_click_id
'%s', // action_type
'%d', // changed_by_user_id
'%s', // old_data (as JSON string)
'%s', // new_data (as JSON string)
'%s', // reason
)
);
if ( $inserted === false ) {
error_log( "Failed to insert audit log for click ID {$click_id}. DB Error: " . $wpdb->last_error );
return false;
}
return true;
}
/**
* Example of how to use the logging function when updating a click record.
* This would typically be part of your admin edit screen handler.
*/
function handle_admin_edit_affiliate_click() {
// Assume $_POST['click_id'] and other fields are submitted
if ( isset( $_POST['click_id'] ) && current_user_can( 'manage_options' ) ) { // Check capability
$click_id = intval( $_POST['click_id'] );
// Fetch the current data *before* updating
$current_click_data = get_affiliate_click_data( $click_id ); // Assume this function exists
if ( ! empty( $current_click_data ) ) {
$old_data_for_log = $current_click_data; // Store a copy
// Prepare new data from $_POST
$new_click_data = array(
'affiliate_id' => isset( $_POST['affiliate_id'] ) ? intval( $_POST['affiliate_id'] ) : $current_click_data['affiliate_id'],
'order_id' => isset( $_POST['order_id'] ) ? intval( $_POST['order_id'] ) : $current_click_data['order_id'],
// ... other fields
);
// Perform the actual database update for wp_affiliate_clicks
$update_success = update_affiliate_click_record( $click_id, $new_click_data ); // Assume this function exists
if ( $update_success ) {
// Log the manual audit entry
$reason = isset( $_POST['audit_reason'] ) ? sanitize_textarea_field( $_POST['audit_reason'] ) : '';
log_manual_affiliate_click_update( $click_id, $old_data_for_log, $new_click_data, $reason );
// Redirect or show success message
} else {
// Handle update failure
}
} else {
// Handle click not found
}
}
}
// add_action('admin_post_edit_affiliate_click', 'handle_admin_edit_affiliate_click'); // Example hook
Security and Access Control
It’s crucial to restrict access to the audit log viewing and modification capabilities. Only trusted administrators should have access to this sensitive data. Implement WordPress capabilities checks diligently.
- Capability Management: Create a custom capability (e.g.,
view_affiliate_audit_logs,edit_affiliate_clicks) and assign it to specific roles (e.g., “Affiliate Manager”, “Administrator”). - Admin UI Protection: Ensure all admin pages that display audit logs or allow modifications have capability checks at the top of the page handler.
- Data Masking: For highly sensitive data (like IP addresses), consider masking parts of it in the audit log display unless a specific “forensic” view is requested and authorized.
- Regular Audits: Periodically review the audit logs themselves to detect suspicious activity.
By combining database-level triggers with thoughtful application-level logic in your WordPress plugin, you can build a comprehensive and secure audit trail for your affiliate click tracking data, providing essential accountability and data integrity for your e-commerce operations.