3.7 Database Integration

Order Daemon uses a dual-table database architecture optimized for performance and scalability. This guide provides essential information for developers who need to integrate with the database for reporting, analytics, or custom functionality.

Quick Start

Basic query to get order audit logs:

global $wpdb;

// Get recent logs for a specific order
$order_logs = $wpdb->get_results($wpdb->prepare("
    SELECT l.*, p.payload
    FROM {$wpdb->prefix}odcm_audit_log l
    LEFT JOIN {$wpdb->prefix}odcm_audit_log_payloads p ON l.payload_id = p.payload_id
    WHERE l.order_id = %d
    ORDER BY l.timestamp DESC
    LIMIT 20
", $order_id));

Overview

The database integration layer provides:

Dual-Table Architecture: Separates core data from detailed payloads for performance
Optimized Indexes: Strategic indexing for common query patterns
Data Integrity: Foreign key constraints and proper relationships
Safe Access: Guidelines for secure database integration

Database Schema

Primary Audit Log Table: `wp_odcm_audit_log`

Stores core event data with optimized indexes:

Key Fields:

`log_id` – Primary key (bigint, auto-increment)
`timestamp` – When the event occurred (datetime)
`order_id` – Associated WooCommerce order ID (optional)
`event_type` – Type of event (varchar 50)
`status` – Event status (varchar 20): ‘success’, ‘error’, ‘warning’, ‘info’, etc.
`summary` – Human-readable event description (text)
`payload_id` – Foreign key to payloads table (optional)
`source` – Event source (varchar 50): ‘system’, ‘user’, ‘api’
`log_category` – Event category (varchar 20): ‘core’, ‘custom’, ‘debug’
`is_test` – Test data flag (tinyint)
`process_id` – Process correlation ID (varchar 64)

Payload Storage Table: `wp_odcm_audit_log_payloads`

Stores detailed structured data separately:

Key Fields:

`payload_id` – Primary key (bigint, auto-increment)
`payload` – JSON-encoded structured data (longtext)
`format` – Data format (varchar 10, default: ‘json’)

Order Rules Storage

Rules are stored as WordPress custom post type `odcm_order_rule`:

Post Table: Basic rule information (name, status, priority)
Post Meta: Rule configuration (conditions, actions, settings)

Data Relationships

wp_posts (Orders) ──→ wp_odcm_audit_log ──→ wp_odcm_audit_log_payloads
                           ↑
wp_posts (Rules) ──→ wp_postmeta (Rule Config)

Key Relationships:

One order can have many audit log entries
Each audit log entry can have one detailed payload (via `payload_id`)
Order deletion preserves audit trail (sets `order_id` to NULL)
Rules use custom post type `odcm_order_rule`

Common Query Patterns

Basic Queries

Get Order Audit Trail

global $wpdb;

// Get all logs for a specific order with payload data
$order_logs = $wpdb->get_results($wpdb->prepare("
    SELECT l.*, p.payload
    FROM {$wpdb->prefix}odcm_audit_log l
    LEFT JOIN {$wpdb->prefix}odcm_audit_log_payloads p ON l.payload_id = p.payload_id
    WHERE l.order_id = %d
    ORDER BY l.timestamp DESC
", $order_id));

Get Recent Errors

// Get recent error events
$recent_errors = $wpdb->get_results("
    SELECT log_id, timestamp, order_id, event_type, summary
    FROM {$wpdb->prefix}odcm_audit_log
    WHERE status = 'error'
    AND timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    ORDER BY timestamp DESC
    LIMIT 20
");

Get Completion Statistics

// Get daily completion stats
$completion_stats = $wpdb->get_results("
    SELECT 
        DATE(timestamp) as date,
        COUNT(*) as total_attempts,
        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful
    FROM {$wpdb->prefix}odcm_audit_log
    WHERE event_type = 'order_completed'
    AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY DATE(timestamp)
    ORDER BY date DESC
");

Get Process Timeline

// Get all events for a specific process (correlated by process_id)
$process_timeline = $wpdb->get_results($wpdb->prepare("
    SELECT log_id, timestamp, event_type, status, summary, order_id
    FROM {$wpdb->prefix}odcm_audit_log
    WHERE process_id = %s
    ORDER BY timestamp ASC
", $process_id));

Advanced Reporting

Integration Performance Analysis

// Analyze integration performance by source
$integration_stats = $wpdb->get_results("
    SELECT 
        source,
        event_type,
        COUNT(*) as total_events,
        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,
        SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as failed,
        ROUND(AVG(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100, 2) as success_rate
    FROM {$wpdb->prefix}odcm_audit_log
    WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND source != 'system'
    GROUP BY source, event_type
    ORDER BY total_events DESC
");

Order Processing Funnel

// Analyze order processing funnel
$processing_funnel = $wpdb->get_results("
    SELECT 
        event_type,
        COUNT(DISTINCT order_id) as unique_orders,
        COUNT(*) as total_events,
        AVG(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success_rate
    FROM {$wpdb->prefix}odcm_audit_log
    WHERE order_id IS NOT NULL
    AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY event_type
    ORDER BY unique_orders DESC
");

Integration Safety Guidelines

Read-Only Access Patterns

For reporting and analytics, use read-only queries:

// Safe read-only query example
function get_order_completion_stats($start_date, $end_date) {
    global $wpdb;
    
    return $wpdb->get_results($wpdb->prepare("
        SELECT 
            DATE(timestamp) as completion_date,
            COUNT(*) as total_completions,
            SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful,
            SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as failed
        FROM {$wpdb->prefix}odcm_audit_log 
        WHERE event_type = 'order_completed'
        AND timestamp BETWEEN %s AND %s
        GROUP BY DATE(timestamp)
        ORDER BY completion_date DESC
    ", $start_date, $end_date));
}

Write Operations Safety

Never directly insert into audit log tables – use the API:

// WRONG: Direct database insert
// $wpdb->insert($wpdb->prefix . 'odcm_audit_log', $data);

// CORRECT: Use the API
odcm_log_custom_event(
    'Custom integration event',
    $payload_data,
    $order_id,
    'success'
);

Performance Considerations

Query Optimization Tips

Use Indexes: Always leverage the provided indexes in WHERE clauses
Limit Results: Use LIMIT for large datasets
Date Ranges: Always include date ranges for time-based queries
Avoid SELECT *: Select only needed columns
JSON Queries: Use JSON functions efficiently for payload data

Batch Processing for Large Datasets

// For large datasets, use buffered queries
function process_large_dataset($start_date, $end_date) {
    global $wpdb;
    
    $offset = 0;
    $batch_size = 1000;
    
    do {
        $results = $wpdb->get_results($wpdb->prepare("
            SELECT log_id, timestamp, event_type, status, summary, order_id
            FROM {$wpdb->prefix}odcm_audit_log 
            WHERE timestamp BETWEEN %s AND %s
            ORDER BY log_id
            LIMIT %d OFFSET %d
        ", $start_date, $end_date, $batch_size, $offset));
        
        // Process batch
        foreach ($results as $row) {
            process_log_entry($row);
        }
        
        $offset += $batch_size;
        unset($results); // Free memory
        
    } while (count($results) === $batch_size);
}

Available Indexes

The audit log table includes optimized indexes for common query patterns:

Primary: `log_id` (primary key)
Order queries: `order_id`, `idx_order_timestamp`
Time-based: `timestamp`, `idx_timestamp_status`, `idx_timestamp_event_type`
Filtering: `status`, `event_type`, `source`, `log_category`
Process correlation: `process_id`
Test data: `is_test`

Always use these indexed columns in your WHERE clauses for optimal performance.