Skip to main content

Database Migrations

Complete guide to the GSM Middleware database migration system, including how migrations work, how to apply them, and how to create new migrations.

Overview​

GSM Middleware uses a versioned migration system to manage the tasks database schema. Migrations are:

  • ✅ Numbered sequentially (000, 001, 002...)
  • ✅ Idempotent (safe to re-run multiple times)
  • ✅ PHP-based (use wpdb for database operations)
  • ✅ Forward and backward compatible (include up and down functions)
  • ✅ Self-documenting (include detailed purpose and version info)

Location: src/Database/migrations/

How Migrations Work​

Migration File Structure​

Each migration is a PHP file that returns an array with three keys:

<?php
/**
 * Migration: Add Performance Indexes to rm_order
 * 
 * @package GSM\Middleware\Database\Migrations
 * @since   1.9.2
 */

declare( strict_types=1 );

return [
    'version' => '1.9.2',  // Plugin version where migration was added
    
    'up' => function ( \wpdb $db ) {
        // Apply changes (create tables, add columns, create indexes)
        // Must be idempotent - check if exists before creating
    },
    
    'down' => function ( \wpdb $db ) {
        // Rollback changes (optional, for development)
        // Remove what was added in 'up'
    },
];

Idempotency Requirements​

All migrations MUST be idempotent (safe to run multiple times). This is achieved by:

  1. Tables: Use CREATE TABLE IF NOT EXISTS
  2. Columns: Check column existence before ALTER TABLE ADD COLUMN
  3. Indexes: Query INFORMATION_SCHEMA.STATISTICS before ADD INDEX
  4. Data: Use INSERT IGNORE or check for existing records

Example:

'up' => function ( \wpdb $db ) {
    // Check if index exists
    $index_exists = $db->get_var(
        $db->prepare(
            'SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
             AND TABLE_NAME = %s
             AND INDEX_NAME = %s',
            'rm_order',
            'idx_dateadded'
        )
    );

    if ( ! $index_exists ) {
        $db->query( "ALTER TABLE `rm_order` ADD INDEX `idx_dateadded` (`dateadded` DESC)" );
        
        if ( $db->last_error ) {
            throw new \RuntimeException( "Failed to add index: {$db->last_error}" );
        }
    }
},

Applying Migrations​

Via DB Environments Page​

  1. Navigate to GSM Middleware → DB Environments
  2. Click "Verify Schema" to check for missing tables/columns/indexes
  3. Review the verification results
  4. Click "Fix All" to apply all pending migrations
  5. Wait for success message

Via WP-CLI​

# Apply all pending migrations
wp gsm migrate

# Verify schema without applying
wp gsm verify-schema

# Apply specific migration
wp gsm migrate --version=011

Programmatically​

use GSM\Middleware\Database\Migration_Runner;

$runner = new Migration_Runner( $tasks_db );
$runner->apply_all_migrations();

Migration History​

000 — Baseline Schema​

Version: 0.0.1
Purpose: Complete initial database schema from production dump

Creates all foundational tables:

  • rm_sites - Site configurations
  • rm_order - Orders
  • rm_lineitems - Line items
  • rm_address - Addresses
  • rm_items - Inventory
  • shipments - Tracking
  • rm_kits - Kit structure
  • rm_promotions - Promotions
  • Supporting tables

Fully idempotent with CREATE TABLE IF NOT EXISTS. Safe to run on existing databases.

001 — Queue & Webhook Tables​

Version: 1.8.0
Purpose: Add async job queue and webhook logging

Creates:

  • rm_queue - Job queue for async processing
  • rm_webhook_log - Product/inventory webhook inbox

Enables:

  • Background job processing
  • Webhook signature validation
  • Retry logic with exponential backoff

002 — Sync Health URLs​

Version: 1.8.1
Purpose: Add health check URLs to rm_sites

Adds columns to rm_sites:

  • orders_health_url
  • inventory_health_url
  • tracking_health_url
  • bazaarvoice_health_url
  • kit_health_url

Enables per-sync health monitoring in Control Panel.

003 — Carrier Mapping Table​

Version: 1.8.2
Purpose: Map Business Central carriers to platform carriers

Creates rm_carrier_mapping table for cross-platform carrier code translation.

004 — Tracking Settings​

Version: 1.8.3
Purpose: Add tracking configuration to rm_sites

Adds columns:

  • tracking_status_filters - Status filter for tracking sync
  • tracking_auto_complete - Auto-complete orders on tracking
  • tracking_send_email - Send tracking email notifications

005 — Dispute Linkages Table​

Version: 1.8.4
Purpose: Link PayArc disputes to NMI transactions

Creates rm_payarc_dispute_linkages for chargeback resolution audit trail.

006 — PayArc Webhooks Table​

Version: 1.8.5
Purpose: Dedicated PayArc webhook inbox

Creates rm_webhooks table as source-of-record for PayArc dispute events.

007 — Queue Reference IDs​

Version: 1.8.6
Purpose: Add idempotency keys to job queue

Adds reference_id column to rm_queue with unique constraint for duplicate job prevention.

008 — Signifyd Credentials​

Version: 1.8.7
Purpose: Add Signifyd fraud protection integration

Adds columns to rm_sites:

  • signifyd_enabled
  • signifyd_team_id
  • signifyd_api_key

009 — Chargebacks Table​

Version: 1.9.0
Purpose: Unified chargeback management

Creates rm_chargebacks table for cross-provider dispute tracking.

010 — Missing rm_sites Columns​

Version: 1.9.1
Purpose: Backfill missing baseline columns on old installations

Ensures all 46 rm_sites columns exist, even on databases created before baseline migration 000.

011 — Orders Performance Indexes​

Version: 1.9.2
Purpose: Improve Orders page query performance

Adds indexes to rm_order:

  • idx_dateadded - For ORDER BY dateadded DESC queries (300ms → 80ms)
  • idx_ship_status - For shipped filter (reduces full table scans)

Impact: 70%+ reduction in Orders page load time.

012 — API Keys Table​

Version: 1.11.0
Purpose: External order ingestion API key management

Creates rm_api_keys table for Bearer token authentication:

  • SHA-256 hashed key storage
  • Key lifecycle management (generate, revoke, expire)
  • Usage tracking with last_used timestamp

013 — Report Configurations Table​

Version: 1.12.0
Purpose: Automated email report configuration storage

Creates rm_report_configs table storing:

  • Report type and schedule (cron expression)
  • Recipients list
  • Active/inactive toggle
  • Last run timestamp

014 — Drop Legacy Webhooks Table​

Version: 1.15.0
Purpose: Remove legacy PayArc webhook inbox

Drops rm_webhooks table (replaced by rm_queue + rm_webhook_log).

015 — Failed Queue Items Report​

Version: 1.15.3
Purpose: Insert default report configuration

Inserts failed_queue_items report into rm_report_configs for daily email notifications of failed queue jobs.

016 — BazaarVoice Discovery Integration​

Version: 1.16.0
Purpose: BazaarVoice Authentic Discovery API and review storage

Adds to rm_sites:

  • bv_discovery_sync — Toggle for Discovery API sync
  • bv_discovery_client_id — BV client identifier
  • bv_discovery_api_key — Production API key (encrypted)
  • bv_discovery_product_id_field — Product matching strategy

Adds to rm_items:

  • bv_rating_value — Average review rating
  • bv_review_count — Total review count
  • bv_last_synced — Last BV sync timestamp

Creates rm_item_reviews table for individual review content with SHA-256 deduplication.

017 — BV Discovery Environment​

Version: 1.16.1
Purpose: Staging/production environment support for BV Discovery

Adds to rm_sites:

  • bv_discovery_environment — Environment selector (staging/production)
  • bv_discovery_api_key_staging — Staging API key (encrypted)

018 — Not used (reserved)​

019 — BV SFTP Feed Sync​

Version: 1.17.0
Purpose: BazaarVoice SFTP Standard Client Feed import

Adds to rm_sites:

  • bv_sftp_sync — Toggle for SFTP feed import
  • bv_sftp_last_feed_date — Date of last processed feed
  • bv_sftp_last_processed_file — Filename of last processed feed

Renames bv_discovery_product_id_field → bv_product_id_field (shared by Discovery API and SFTP).

020 — Schema Markup Table & Analytics Indexes​

Version: 1.18.4 / 1.18.15
Purpose: Schema.org JSON-LD storage and analytics query performance

Creates rm_item_schema_markup table (8 columns, 4 indexes) for storing generated review JSON-LD per product.

Adds analytics performance indexes:

  • rm_lineitems.idx_item — Top Selling Products report (2.6M-row table)
  • shipments.idx_order_id — Shipping Performance and fulfillment KPI (866K rows)
  • shipments.idx_shipping_provider — GROUP BY carrier optimization
  • rm_order.idx_site_dateadded — Composite index for site-filtered date range queries
  • rm_address.idx_order_type — Composite index for Geographic Sales report (1.7M rows)
  • rm_items.idx_sku — SKU lookups in reports and order processing

Drops redundant indexes:

  • rm_address.order (superseded by composite)
  • rm_items.rm_items_site_IDX (redundant with PK)

021 — BV Schema Last Synced​

Version: 1.18.16
Purpose: Track BigCommerce schema sync timestamp

Adds to rm_sites:

  • bv_schema_last_synced — When schema markup was last synced to BigCommerce custom fields

022 — Metafield Last Synced​

Version: 1.18.37
Purpose: Track metafield push timestamp per product

Adds to rm_item_schema_markup:

  • metafield_last_synced — When combined JSON-LD was last pushed to BigCommerce metafield

023 — BV Metafield Sync Toggle​

Version: 1.18.38
Purpose: Per-site toggle for BazaarVoice Metafield Sync

Adds to rm_sites:

  • bv_metafield_sync — Enable/disable pushing review JSON-LD to BigCommerce product metafields

Creating New Migrations​

Step 1: Create Migration File​

Create file: src/Database/migrations/012_your_migration_name.php

Naming convention: {number}_{descriptive_name}.php

Step 2: Write Migration Code​

<?php
/**
 * Migration: Your Migration Title
 *
 * Detailed description of what this migration does and why.
 *
 * @package GSM\Middleware\Database\Migrations
 * @since   1.x.x
 */

declare( strict_types=1 );

return [
    'version' => '1.x.x',
    
    'up' => function ( \wpdb $db ) {
        // Check if column exists before adding
        $column_exists = $db->get_var(
            $db->prepare(
                'SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                 AND TABLE_NAME = %s
                 AND COLUMN_NAME = %s',
                'rm_sites',
                'new_column'
            )
        );

        if ( ! $column_exists ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $db->query( "ALTER TABLE `rm_sites` ADD `new_column` VARCHAR(50) NULL" );
            
            if ( $db->last_error ) {
                throw new \RuntimeException( "Failed: {$db->last_error}" );
            }
            
            error_log( 'GSM Middleware: Migration 012 added new_column to rm_sites' );
        }
    },
    
    'down' => function ( \wpdb $db ) {
        // Rollback (optional)
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $db->query( "ALTER TABLE `rm_sites` DROP COLUMN IF EXISTS `new_column`" );
    },
];

Step 3: Update Schema Verifier​

Add new column/table to src/Database/Schema_Verifier.php:

private function get_required_tables(): array {
    return [
        'rm_sites' => [
            'description' => 'Site configurations and credentials',
            'columns'     => [
                // ... existing columns ...
                'new_column' => 'New column purpose',
            ],
        ],
        // ... other tables ...
    ];
}

Step 4: Test Migration​

# Apply migration
wp gsm migrate

# Verify schema
wp gsm verify-schema

# Check for errors
tail -f wp-content/debug.log

Step 5: Document in AGENTS.md​

Update AGENTS.md with new migration details in the "Database Schema" section.

Best Practices​

✅ DO​

  • Always check existence before creating tables/columns/indexes
  • Use prepared statements for dynamic queries
  • Log success/failure with descriptive messages
  • Throw exceptions on failure with helpful error messages
  • Test on fresh database and existing database
  • Document what and why in migration docblock
  • Update Schema_Verifier to reflect new schema
  • Use meaningful names for columns/indexes
  • Add indexes for frequently queried columns

❌ DON'T​

  • Don't drop data without user confirmation
  • Don't use hardcoded database names (use DATABASE())
  • Don't skip error checking after database operations
  • Don't forget phpcs:ignore for direct queries (required by WordPress standards)
  • Don't create non-idempotent migrations
  • Don't modify old migrations after they're deployed

Troubleshooting​

Migration Fails with "Column already exists"​

Cause: Migration is not idempotent

Solution: Add existence check before ALTER TABLE:

$column_exists = $db->get_var(
    $db->prepare(
        'SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = %s
         AND COLUMN_NAME = %s',
        'rm_sites',
        'column_name'
    )
);

if ( ! $column_exists ) {
    // Add column
}

Migration Succeeds but Schema Verifier Shows Missing​

Cause: Schema_Verifier not updated with new column/table

Solution: Update get_required_tables() in Schema_Verifier.php

Migration Fails Silently​

Cause: No error logging

Solution: Check $db->last_error and throw exception:

$db->query( "ALTER TABLE ..." );

if ( $db->last_error ) {
    throw new \RuntimeException( "Migration failed: {$db->last_error}" );
}

Migration Works in Dev but Fails in Production​

Cause: Different database permissions or strict SQL modes

Solution:

  • Check SQL_MODE differences
  • Ensure column definitions are compatible with production MySQL version
  • Test on staging environment with same MySQL version