Database Schema Documentation

Audience: Senior Developers Version: 1.0.0 Last Updated: December 2024

Overview

WooAI Chatbot Pro implements a hybrid data persistence strategy combining custom MySQL tables for high-volume transactional data with WordPress native mechanisms (options, transients, post meta) for configuration and caching.

Architecture Philosophy

Entity Relationship Diagram

                                    +------------------+
                                    |   wp_posts       |
                                    | (waa_playbook)   |
                                    +--------+---------+
                                             |
                                             | playbook_id
                                             v
+------------------+    session_id    +------------------+
|  wp_waa_events   |<---------------->| wp_waa_sessions  |
+------------------+                  +--------+---------+
| id (PK)          |                  | id (PK)          |
| session_id (FK)  |                  | session_id (UK)  |
| event_type       |                  | user_id (FK)     |
| event_data       |                  | ai_provider      |
| user_id (FK)     |                  | expires_at       |
| timestamp        |                  +------------------+
+------------------+                           |
                                               | session_id
                                               v
                                    +----------------------+
                                    | wp_waa_playbook_     |
                                    | sessions             |
                                    +----------------------+
                                    | id (PK)              |
                                    | session_id (FK)      |
                                    | playbook_id (FK)     |
                                    | current_step_index   |
                                    | status (ENUM)        |
                                    +----------------------+

+------------------+                  +------------------+
| wp_waa_vector_   |                  | wp_waa_rag_index |
| index            |                  +------------------+
+------------------+                  | id (PK)          |
| id (PK)          |                  | content_type     |
| product_id (FK)  |  sync/fallback   | content_id (FK)  |
| embedding_id     |<---------------->| embedding_vector |
| embedding_model  |                  | metadata_json    |
| metadata_json    |                  +------------------+
+------------------+

+------------------+
| wp_waa_overrides |
+------------------+
| id (PK)          |
| override_type    |
| entity_id        |
| settings_json    |
| created_by (FK)  |
+------------------+

Custom Tables

All tables use the WordPress table prefix (typically wp_) and are created via dbDelta() for safe, idempotent migrations.

wp_waa_events

Purpose: High-frequency event tracking for KPI analytics and conversion funnel analysis.

CREATE TABLE {prefix}waa_events (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_data LONGTEXT,
    user_id BIGINT(20) UNSIGNED NULL,
    timestamp DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_session (session_id),
    KEY idx_type (event_type),
    KEY idx_timestamp (timestamp),
    KEY idx_user (user_id)
) {charset_collate};

Column Specifications

Event Types

// Core events
'chat_started'      // Session initialization
'message_sent'      // User message submitted
'message_received'  // AI response delivered
'chat_ended'        // Session termination

// Conversion events
'product_clicked'   // Product link clicked
'product_viewed'    // Product details viewed
'cart_added'        // Product added to cart
'checkout_started'  // Checkout initiated
'order_completed'   // Purchase completed

// Engagement events
'playbook_started'  // Guided flow initiated
'playbook_completed'// Guided flow finished
'rating_submitted'  // User feedback received

Index Strategy

Retention Policy

Events older than 90 days are automatically purged via scheduled cleanup:

// Executed daily via wp-cron
$database->clear_old_events( 90 );

Example Data

{
    "id": 12847,
    "session_id": "550e8400-e29b-41d4-a716-446655440000",
    "event_type": "product_clicked",
    "event_data": {
        "product_id": 1234,
        "product_name": "Wireless Headphones",
        "price": 79.99,
        "source": "ai_recommendation"
    },
    "user_id": 42,
    "timestamp": "2025-12-15 14:32:18"
}

wp_waa_sessions

Purpose: Persistent storage for chat sessions with full conversation history and contextual data.

CREATE TABLE {prefix}waa_sessions (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    user_id BIGINT(20) UNSIGNED NULL,
    conversation_history LONGTEXT,
    context_data LONGTEXT,
    ai_provider VARCHAR(50),
    model_used VARCHAR(100),
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    expires_at DATETIME,
    PRIMARY KEY (id),
    UNIQUE KEY unique_session (session_id),
    KEY idx_user (user_id),
    KEY idx_created (created_at),
    KEY idx_expires (expires_at)
) {charset_collate};

Column Specifications

Session Lifecycle

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   CREATED   │────>│   ACTIVE    │────>│   EXPIRED   │
└─────────────┘     └─────────────┘     └─────────────┘
                          │
                          v
                    ┌─────────────┐
                    │   CLEANED   │
                    └─────────────┘

1. Created: Session initialized on first user message
2. Active: Messages exchanged, updated_at refreshed
3. Expired: expires_at passed (default: 1 hour of inactivity)
4. Cleaned: Removed by scheduled cleanup job

Expiration Handling

// Default session duration
$expires_at = gmdate( 'Y-m-d H:i:s', strtotime( '+1 hour' ) );

// Cleanup expired sessions
$deleted_count = $database->clear_expired_sessions();

Context Data Structure

{
    "cart_items": [
        {"product_id": 123, "quantity": 2, "price": 29.99}
    ],
    "viewed_products": [456, 789, 123],
    "current_page": "product",
    "current_product_id": 456,
    "user_preferences": {
        "language": "en",
        "currency": "USD"
    }
}

wp_waa_vector_index

Purpose: Maps WooCommerce products to external vector embeddings stored in Supabase pgvector.

CREATE TABLE {prefix}waa_vector_index (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id BIGINT(20) UNSIGNED NOT NULL,
    embedding_id VARCHAR(255),
    embedding_model VARCHAR(100),
    metadata_json LONGTEXT,
    indexed_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_product (product_id),
    KEY idx_embedding (embedding_id),
    KEY idx_indexed (indexed_at)
) {charset_collate};

Column Specifications

Relationship with Supabase

WordPress (wp_waa_vector_index)     Supabase (embeddings table)
+---------------------------+       +---------------------------+
| product_id: 123           |       | id: uuid-abc-123          |
| embedding_id: uuid-abc-123|<----->| embedding: [0.12, 0.34...]|
| metadata_json: {...}      |       | content: "Product desc"   |
+---------------------------+       +---------------------------+

wp_waa_overrides

Purpose: Store admin-defined customizations for playbooks, topics, and promotional behavior.

CREATE TABLE {prefix}waa_overrides (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    override_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    settings_json LONGTEXT,
    created_by BIGINT(20) UNSIGNED,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_type (override_type),
    KEY idx_entity (entity_id),
    KEY idx_type_entity (override_type, entity_id),
    KEY idx_created_by (created_by)
) {charset_collate};

Override Types

Example Settings JSON

{
    "priority_boost": 10,
    "custom_prompt": "Always mention free shipping...",
    "enabled_pages": ["shop", "product"],
    "excluded_products": [123, 456]
}

wp_waa_rag_index

Purpose: Local vector embeddings fallback when Supabase is unavailable or for on-premise deployments.

CREATE TABLE {prefix}waa_rag_index (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    content_type VARCHAR(50) NOT NULL,
    content_id BIGINT(20) UNSIGNED NOT NULL,
    content_text LONGTEXT,
    embedding_vector LONGTEXT,
    metadata_json LONGTEXT,
    indexed_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_content (content_type, content_id),
    KEY idx_type (content_type),
    KEY idx_indexed (indexed_at)
) {charset_collate};

Content Types

Embedding Vector Storage

Vectors are stored as JSON-encoded float arrays:

{
    "embedding_vector": "[0.0123, -0.0456, 0.0789, ...]",
    "dimensions": 384
}

Note: For production with >10k products, Supabase pgvector is recommended for efficient similarity search.

wp_waa_playbook_sessions

Purpose: State management for multi-step guided conversation flows (playbooks).

CREATE TABLE {prefix}waa_playbook_sessions (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    session_id VARCHAR(64) NOT NULL,
    playbook_id BIGINT(20) UNSIGNED NOT NULL,
    current_step_index INT(11) NOT NULL DEFAULT 0,
    step_responses LONGTEXT DEFAULT NULL,
    collected_variables LONGTEXT DEFAULT NULL,
    status ENUM('active','paused','completed','expired','abandoned') NOT NULL DEFAULT 'active',
    started_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    last_activity_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_session_status (session_id, status),
    KEY idx_expires (expires_at),
    KEY idx_playbook_id (playbook_id),
    KEY idx_last_activity (last_activity_at)
) {charset_collate};

Status States

State Management Example

{
    "session_id": "abc-123",
    "playbook_id": 42,
    "current_step_index": 2,
    "step_responses": [
        {"step": 0, "response": "Blue", "timestamp": "2025-12-15T10:00:00Z"},
        {"step": 1, "response": "Large", "timestamp": "2025-12-15T10:01:00Z"}
    ],
    "collected_variables": {
        "color": "Blue",
        "size": "Large",
        "email": "user@example.com"
    },
    "status": "active"
}

WordPress Options

Plugin configuration stored in wp_options table using WordPress Options API.

woo_ai_chatbot_settings

Type: JSON string Autoload: Yes

$settings = json_decode( get_option( 'woo_ai_chatbot_settings', '{}' ), true );

Structure

{
    "ai_provider": "gemini",
    "model": "gemini-2.5-flash-lite",
    "chat_enabled": true,
    "widget_position": "bottom-right",
    "max_session_duration": 3600,
    "max_conversation_history": 50,
    "enable_rag": true,
    "enable_analytics": true,
    "license_status": "active",
    "trial_expires": 1735689600
}

woo_ai_chatbot_version

Type: String Example: "0.1.0"

Tracks installed plugin version for upgrade routines.

woo_ai_chatbot_license

Type: Array (serialized)

[
    'key'          => 'encrypted_key_data',
    'key_hash'     => 'sha256_hash',
    'status'       => 'active',
    'type'         => 'remote',
    'plan'         => 'pro',
    'expires_at'   => '2026-12-31',
    'domain'       => 'domain_hash',
    'activated_at' => '2025-01-15 10:30:00',
    'checksum'     => 'integrity_checksum'
]

Widget Options

Individual options for backward compatibility:

Transients & Caching

Transients provide TTL-based caching with automatic object cache integration.

Usage Statistics Cache

// Key: woo_ai_chatbot_stats_{period}
// TTL: 1 hour
$stats = get_transient( 'woo_ai_chatbot_stats_daily' );

Provider Priority Cache

// Key: woo_ai_priority_order
// TTL: 1 hour
// Structure: ['gemini', 'claude', 'openai']
set_transient( self::PRIORITY_CACHE_KEY, $priority, HOUR_IN_SECONDS );

License Status Cache

// Key: woo_ai_license_status
// TTL: 72 hours
// Values: 'active', 'inactive', 'trial'
set_transient( self::CACHE_KEY, 'active', 72 * HOUR_IN_SECONDS );

Rate Limiting

// Key: woo_ai_chatbot_rate_{session_id}
// TTL: 60 seconds
// Tracks request count per session
$count = (int) get_transient( $cache_key );
set_transient( $cache_key, $count + 1, 60 );

Indexing Lock

// Key: woo_ai_chatbot_indexing
// TTL: 30 minutes
// Prevents concurrent indexing operations
set_transient( 'woo_ai_chatbot_indexing', true, 30 * MINUTE_IN_SECONDS );

Playbook Cache

// Key: woo_ai_playbooks_cache
// TTL: 1 hour
// Caches active playbook configurations
set_transient( self::CACHE_KEY, $playbooks, HOUR_IN_SECONDS );

Custom Post Types

waa_playbook

Post Type Slug: waa_playbook REST Base: /wp-json/wp/v2/waa-playbooks

register_post_type( 'waa_playbook', [
    'public'       => false,
    'show_in_rest' => true,
    'rest_base'    => 'waa-playbooks',
    'supports'     => ['title', 'custom-fields'],
    'capabilities' => ['manage_woocommerce'],
] );

Registered Meta Fields

Steps JSON Structure

[
    {
        "id": "step_1",
        "type": "question",
        "message": "What color do you prefer?",
        "options": ["Red", "Blue", "Green"],
        "variable": "color",
        "required": true
    },
    {
        "id": "step_2",
        "type": "action",
        "action": "show_products",
        "filters": {"color": "{{color}}"}
    }
]

Data Migration

Version Tracking

Database schema version is tracked separately from plugin version:

private const DB_VERSION = '1.0.0';
private const DB_VERSION_OPTION = 'woo_ai_chatbot_db_version';

Migration Flow

public function create_tables() {
    $current = get_option( self::DB_VERSION_OPTION, '0.0.0' );

    if ( version_compare( $current, '1.0.0', '<' ) ) {
        // Initial schema creation
        $this->create_events_table();
        $this->create_sessions_table();
        // ...
    }

    if ( version_compare( $current, '1.1.0', '<' ) ) {
        // Add new column
        $this->migrate_to_1_1_0();
    }

    update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
}

Adding New Columns

private function migrate_to_1_1_0() {
    global $wpdb;

    $table = $wpdb->prefix . 'waa_sessions';

    // Check if column exists
    $column_exists = $wpdb->get_var(
        "SHOW COLUMNS FROM {$table} LIKE 'sentiment_score'"
    );

    if ( ! $column_exists ) {
        $wpdb->query(
            "ALTER TABLE {$table}
             ADD COLUMN sentiment_score FLOAT NULL
             AFTER model_used"
        );
    }
}

Query Examples

Retrieve Session with Events

global $wpdb;
$prefix = $wpdb->prefix;

$session = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT s.*,
                COUNT(e.id) as event_count,
                MAX(e.timestamp) as last_event
         FROM {$prefix}waa_sessions s
         LEFT JOIN {$prefix}waa_events e ON s.session_id = e.session_id
         WHERE s.session_id = %s
         GROUP BY s.id",
        $session_id
    ),
    ARRAY_A
);

Analytics: Conversion Funnel

$funnel = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT
            event_type,
            COUNT(*) as count,
            COUNT(DISTINCT session_id) as unique_sessions
         FROM {$prefix}waa_events
         WHERE timestamp >= %s
           AND event_type IN ('chat_started', 'product_clicked', 'cart_added', 'order_completed')
         GROUP BY event_type
         ORDER BY FIELD(event_type, 'chat_started', 'product_clicked', 'cart_added', 'order_completed')",
        $start_date
    ),
    ARRAY_A
);

Active Playbook Sessions

$active_sessions = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ps.*, p.post_title as playbook_name
         FROM {$prefix}waa_playbook_sessions ps
         JOIN {$prefix}posts p ON ps.playbook_id = p.ID
         WHERE ps.status = 'active'
           AND ps.expires_at > %s
         ORDER BY ps.last_activity_at DESC
         LIMIT %d",
        current_time( 'mysql' ),
        100
    ),
    ARRAY_A
);

Performance: Bulk Event Insert

// Use single INSERT with multiple VALUES for bulk operations
$values = [];
$placeholders = [];

foreach ( $events as $event ) {
    $placeholders[] = '(%s, %s, %s, %d, %s)';
    $values[] = $event['session_id'];
    $values[] = $event['event_type'];
    $values[] = wp_json_encode( $event['data'] );
    $values[] = $event['user_id'];
    $values[] = $event['timestamp'];
}

$sql = "INSERT INTO {$prefix}waa_events
        (session_id, event_type, event_data, user_id, timestamp)
        VALUES " . implode( ', ', $placeholders );

$wpdb->query( $wpdb->prepare( $sql, $values ) );

Cleanup: Remove Stale Data

// Remove expired sessions and their events atomically
$wpdb->query( 'START TRANSACTION' );

try {
    // Get expired session IDs
    $expired = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT session_id FROM {$prefix}waa_sessions
             WHERE expires_at < %s",
            current_time( 'mysql' )
        )
    );

    if ( ! empty( $expired ) ) {
        $placeholders = implode( ',', array_fill( 0, count( $expired ), '%s' ) );

        // Delete related events
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$prefix}waa_events
                 WHERE session_id IN ({$placeholders})",
                $expired
            )
        );

        // Delete sessions
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$prefix}waa_sessions
                 WHERE session_id IN ({$placeholders})",
                $expired
            )
        );
    }

    $wpdb->query( 'COMMIT' );
} catch ( Exception $e ) {
    $wpdb->query( 'ROLLBACK' );
    throw $e;
}

Performance Considerations

Index Usage

Always verify queries use appropriate indexes:

EXPLAIN SELECT * FROM wp_waa_events
WHERE event_type = 'product_clicked'
AND timestamp > '2025-12-01';

Query Optimization Tips

1. Use LIMIT: Always paginate large result sets
2. *Avoid SELECT : Request only needed columns
3. Prepared Statements: Always use $wpdb->prepare()
4. Batch Operations: Use multi-row INSERT/DELETE
5. Index Awareness: Structure WHERE clauses to match indexes

Recommended Table Sizes

Security

SQL Injection Prevention

// ALWAYS use prepared statements
$result = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$table} WHERE user_id = %d",
        $user_id
    )
);

// NEVER do this
$wpdb->query( "DELETE FROM {$table} WHERE id = {$id}" ); // UNSAFE

Data Sanitization

$session_id = sanitize_text_field( $_POST['session_id'] );
$user_id    = absint( $_POST['user_id'] );
$event_data = wp_json_encode( $data ); // Auto-escapes

Related Documentation

• Database Implementation Guide
• API Reference
• Playbooks Architecture

Author: WooAI Development Team Contact: leowebmark@gmail.com