Database Schema

Complete reference for the GSM Middleware database schema, including all tables, columns, indexes, and relationships.

Overview

GSM Middleware uses a dual database architecture:

1. WordPress Database - Plugin settings and configurations
2. Tasks Database (rm_* tables) - All sync data, orders, inventory, and sites

The tasks database can be on a different server and is managed via DB Environments, allowing easy switching between development, staging, and production databases.

Core Tables

rm_sites

Purpose: Site configurations, credentials, and sync toggles

Critical: Yes

Column	Type	Description
id	INT	Auto-increment primary key
prefix	VARCHAR(10)	Site prefix (unique identifier)
platform	VARCHAR(20)	Platform type ('bigcommerce' or 'woocommerce')
name	VARCHAR(100)	Site display name
site_url	VARCHAR(255)	Store URL
domain	VARCHAR(50)	Domain name
is_active	TINYINT	Active status (0=disabled, 1=enabled)
staging	TINYINT	Staging environment flag
weight	VARCHAR(2)	Weight unit ('lb' or 'kg')
checkout_domain	VARCHAR(80)	Checkout domain (if different)

Sync Toggles:

Column	Type	Description
inventory_sync	TINYINT	Enable inventory sync (default: 1)
orders_sync	TINYINT	Enable order sync (default: 1)
tracking_sync	TINYINT	Enable tracking sync (default: 1)
bazaarvoice_sync	TINYINT	Enable Bazaarvoice sync (default: 0)
kit_sync	TINYINT	Enable kit sync (default: 0)

API Credentials:

Column	Type	Description
bc_client_id	TEXT	BigCommerce client ID (OAuth)
bc_auth_token	TEXT	BigCommerce auth token (encrypted)
bc_store_hash	VARCHAR(50)	BigCommerce store hash
bc_channel_id	VARCHAR(50)	BigCommerce channel ID
wc_consumer_key	TEXT	WooCommerce consumer key
wc_consumer_secret	TEXT	WooCommerce consumer secret
nmi_security_key	VARCHAR(100)	NMI payment gateway key
kit_builder_api_key	VARCHAR(100)	Kit builder API key

Health Check URLs:

Column	Type	Description
health_check_url	VARCHAR(255)	Base health check URL
health_check_inventory_url	VARCHAR(255)	Inventory health endpoint
orders_health_url	VARCHAR(500)	Orders health endpoint
inventory_health_url	VARCHAR(500)	Inventory health endpoint
tracking_health_url	VARCHAR(500)	Tracking health endpoint
bazaarvoice_health_url	VARCHAR(500)	Bazaarvoice health endpoint
kit_health_url	VARCHAR(500)	Kit health endpoint

Tracking Settings:

Column	Type	Description
tracking_status_filters	VARCHAR(100)	Status filters for tracking sync
tracking_auto_complete	TINYINT	Auto-complete orders on tracking
tracking_send_email	TINYINT	Send email notification on tracking

Signifyd Integration:

Column	Type	Description
signifyd_enabled	TINYINT	Enable Signifyd fraud protection
signifyd_team_id	VARCHAR(128)	Signifyd team identifier
signifyd_api_key	TEXT	Signifyd API key (encrypted)

Bazaarvoice Integration:

Column	Type	Description
bzv_name	VARCHAR(100)	Bazaarvoice account name / Client ID
bzv_host	VARCHAR(255)	Bazaarvoice SFTP host
bzv_user	TEXT	Bazaarvoice SFTP username
bzv_pass	TEXT	Bazaarvoice SFTP password (encrypted)
bzv_url	VARCHAR(255)	Bazaarvoice review display URL
bzv_brand	VARCHAR(50)	Brand identifier
bazaarvoice_sync	TINYINT	Enable Product Sync to SFTP (default: 0)

BazaarVoice Discovery API:

Column	Type	Description
bv_discovery_sync	TINYINT	Enable Authentic Discovery API sync
bv_discovery_environment	VARCHAR(20)	Environment: staging or production
bv_discovery_api_key	TEXT	Production API key (encrypted)
bv_discovery_api_key_staging	TEXT	Staging API key (encrypted)
bv_product_id_field	VARCHAR(20)	Product matching: prod_id, sku, or upc

BazaarVoice SFTP Feed:

Column	Type	Description
bv_sftp_sync	TINYINT	Enable SFTP Feed Review Import
bv_sftp_last_feed_date	DATE	Date of last processed feed file
bv_sftp_last_processed_file	VARCHAR(255)	Filename of last processed feed

BazaarVoice Metafield Sync:

Column	Type	Description
bv_metafield_sync	TINYINT	Enable pushing review JSON-LD to BC metafields
bv_schema_last_synced	DATETIME	Last schema markup sync to BigCommerce

Webhook Configuration:

Column	Type	Description
webhook_secret	VARCHAR(255)	Webhook signature secret
webhook_ids	TEXT	JSON array of webhook IDs
webhook_status	ENUM	Status: not_configured, active, error
webhook_last_received	DATETIME	Last webhook received timestamp

Sync Timestamps:

Column	Type	Description
order_last_run	DATETIME	Last order sync timestamp
inventory_last_run	DATETIME	Last inventory sync timestamp
shipping_last_run	DATETIME	Last shipping sync timestamp

Indexes:

• PRIMARY KEY (id, prefix)
• UNIQUE KEY Unique Prefix (prefix)
• KEY idx_is_active (is_active)
• KEY idx_platform (platform)

---

rm_order

Purpose: All orders from all sites

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
site	INT	Site ID (FK to rm_sites)
number	VARCHAR(20)	Order number from platform
nav_sales_order_number	VARCHAR(20)	NAV/BC sales order number
trans_id	VARCHAR(150)	Transaction/payment ID
shipping_agent_code	VARCHAR(30)	Carrier code (e.g., 'FEDEX')
shipping_agent_service	VARCHAR(80)	Service level (e.g., 'GROUND')
dateadded	DATETIME	When added to system
date	DATETIME	Order date from platform
contactid	INT	Contact/customer ID
imported	TINYINT	Imported to BC flag (0=no, 1=yes)
verified	TINYINT	Verified in BC flag (0=no, 1=yes)
ship_status	TINYINT	Shipped flag (0=not shipped, 1=shipped)

Payment Flags:

Column	Type	Description
klarna	TINYINT	Paid via Klarna
stripe	TINYINT	Paid via Stripe
paypal	TINYINT	Paid via PayPal
credova	TINYINT	Paid via Credova
sezzle	TINYINT	Paid via Sezzle
nmi	TINYINT	Paid via NMI gateway

Additional Flags:

Column	Type	Description
klarna_captured	TINYINT	Klarna payment captured
mailing_list	TINYINT	Customer opted into mailing list
full_synced	TINYINT	Fully synced to BC
fully_import_from_website	TINYINT	Import flag
narvar_exported	TINYINT	Exported to Narvar tracking
orders_expedited_email	TINYINT	Expedited email sent
shipping_actual_cost	DOUBLE	Actual shipping cost
shipping_imported_to_glew	TINYINT	Imported to Glew analytics
sent_to_paypal	TINYINT	Sent to PayPal

Indexes:

• PRIMARY KEY (id, site)
• KEY site (site)
• KEY rm_order_imported_IDX (imported, verified, fully_import_from_website)
• KEY rm_order_number_IDX (number)
• KEY idx_dateadded (dateadded DESC) - Performance index (Migration 011)
• KEY idx_ship_status (ship_status) - Performance index (Migration 011)
• KEY idx_site_dateadded (site, dateadded) — Composite index for analytics (Migration 020)

---

rm_lineitems

Purpose: Order line items (products in orders)

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
order	BIGINT	Order ID (FK to rm_order)
line	INT	Line number in order
item	INT	Item/product ID (FK to rm_items)
qty	INT	Quantity ordered
price	DECIMAL	Unit price
imported	TINYINT	Imported to BC
verified	TINYINT	Verified in BC
nav_order_number	VARCHAR(20)	NAV order reference

Indexes:

• PRIMARY KEY (id)
• KEY order (order)
• KEY item (item)
• KEY idx_item (item) — Performance index for Top Selling Products report (Migration 020)

---

rm_address

Purpose: Billing and shipping addresses for orders

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
order	BIGINT	Order ID (FK to rm_order)
type	TINYINT	Address type (0=shipping, 1=billing)
firstname	VARCHAR(100)	First name
lastname	VARCHAR(100)	Last name
address	VARCHAR(255)	Address line 1
address2	VARCHAR(255)	Address line 2
city	VARCHAR(100)	City
state	VARCHAR(100)	State/province
zip	VARCHAR(20)	ZIP/postal code
country	VARCHAR(2)	Country code (ISO 2-letter)
phone	VARCHAR(20)	Phone number
email	VARCHAR(255)	Email address

Indexes:

• PRIMARY KEY (id)
• KEY order (order)
• KEY idx_order_type (order, type) — Composite index for Geographic Sales report (Migration 020)

---

rm_items

Purpose: Product inventory data (quantities, metadata)

Critical: Yes

Column	Type	Description
id	INT	Auto-increment primary key
sku	VARCHAR(100)	Product SKU
site	INT	Site ID (FK to rm_sites)
inventory	INT	Stock quantity
prod_id	VARCHAR(50)	Platform product ID
type	VARCHAR(20)	Product type
active	TINYINT	Active/published status
valid	TINYINT	Validation status
price	DECIMAL	Current price
title	VARCHAR(255)	Product title
navision_status	VARCHAR(50)	BC sync status

BazaarVoice Aggregate Columns:

Column	Type	Description
bv_rating_value	DECIMAL(3,2)	Average review rating
bv_review_count	INT	Total review count
bv_last_synced	DATETIME	Last BV sync timestamp

Indexes:

• PRIMARY KEY (id)
• KEY sku (sku)
• KEY site (site)
• KEY prod_id (prod_id)
• KEY idx_sku (sku) — Performance index (Migration 020)

---

Queue & Webhook Tables

rm_queue

Purpose: Async job queue (order sync, inventory sync, dispute processing)

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
job_type	VARCHAR(100)	Job type identifier
payload	LONGTEXT	Job data (JSON)
priority	TINYINT	Priority (1=highest, 5=default, 10=lowest)
site_id	INT	Site ID (if applicable)
reference_id	VARCHAR(255)	Idempotency key (unique per entity)
status	VARCHAR(20)	Status: pending, processing, completed, failed
attempts	INT	Number of attempts
error_message	TEXT	Last error message
created_at	DATETIME	When job was created
scheduled_at	DATETIME	When job should run
started_at	DATETIME	When processing started
completed_at	DATETIME	When job completed
failed_at	DATETIME	When job failed

Indexes:

• PRIMARY KEY (id)
• KEY idx_status_priority (status, priority, created_at)
• KEY idx_job_type (job_type)
• KEY idx_site_id (site_id)
• KEY idx_scheduled (scheduled_at)
• UNIQUE KEY idx_reference_id (reference_id)

---

rm_webhook_log

Purpose: Incoming product/inventory webhook log with signature validation

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
site_id	INT	Site ID (FK to rm_sites)
event_type	VARCHAR(100)	Event type (e.g., 'product.updated')
payload_hash	VARCHAR(64)	SHA-256 hash of payload
payload	LONGTEXT	Complete webhook payload (JSON)
headers	TEXT	Request headers (JSON)
signature_valid	TINYINT	Signature validation result
status	VARCHAR(20)	Status: queued, processing, completed, failed
attempts	INT	Processing attempts
error_message	TEXT	Last error message
queue_job_id	BIGINT	FK to rm_queue (if queued)
received_at	DATETIME	When webhook was received
processed_at	DATETIME	When processing completed

Indexes:

• PRIMARY KEY (id)
• KEY idx_site_event (site_id, event_type)
• KEY idx_status (status)
• KEY idx_payload_hash (payload_hash)
• KEY idx_received_at (received_at)
• KEY idx_queue_job_id (queue_job_id)
• FOREIGN KEY (queue_job_id) REFERENCES rm_queue(id) ON DELETE SET NULL

---

BazaarVoice Tables

rm_item_reviews

Purpose: Individual BazaarVoice review content with deduplication

Critical: No

Column	Type	Description
id	INT	Auto-increment primary key
item_id	INT	FK to rm_items
review_id	VARCHAR(128)	BazaarVoice review identifier
rating	TINYINT	Star rating (1-5)
title	VARCHAR(500)	Review title
review_text	TEXT	Full review content
reviewer_name	VARCHAR(255)	Reviewer display name
submission_time	DATETIME	When review was submitted
hash	VARCHAR(64)	SHA-256 deduplication hash
source	VARCHAR(32)	Source: discovery_api or sftp_feed
created_at	TIMESTAMP	Row creation time

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_hash (hash)
• KEY idx_item_id (item_id)

---

rm_item_schema_markup

Purpose: Generated Schema.org JSON-LD review markup per product

Critical: No

Column	Type	Description
id	INT	Auto-increment primary key
item_id	INT	FK to rm_items
reviews_jsonld	LONGTEXT	Individual reviews JSON-LD
aggregate_jsonld	LONGTEXT	Aggregate rating JSON-LD
review_count	INT	Number of reviews included
generated_at	DATETIME	Last generation timestamp
metafield_last_synced	DATETIME	When metafield was last pushed to BigCommerce
created_at	TIMESTAMP	Row creation time

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_item_id (item_id)
• KEY idx_generated_at (generated_at)
• KEY idx_metafield_last_synced (metafield_last_synced)

---

Reporting & API Tables

rm_api_keys

Purpose: API keys for external order ingestion (Bearer token auth)

Critical: Yes

Column	Type	Description
id	INT	Auto-increment primary key
name	VARCHAR(100)	Key name/label
key_hash	VARCHAR(64)	SHA-256 hash of API key
key_prefix	VARCHAR(8)	First 8 chars for identification
permissions	VARCHAR(50)	Permission scope
is_active	TINYINT	Active status
last_used_at	DATETIME	Last successful use
expires_at	DATETIME	Expiration date (NULL = never)
created_at	TIMESTAMP	When key was created

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_key_hash (key_hash)
• KEY idx_is_active (is_active)

---

rm_report_configs

Purpose: Automated email report configurations and schedules

Critical: No

Column	Type	Description
id	INT	Auto-increment primary key
report_type	VARCHAR(50)	Report identifier slug
name	VARCHAR(100)	Report display name
schedule	VARCHAR(50)	Cron expression for scheduling
recipients	TEXT	Comma-separated email addresses
is_active	TINYINT	Active status
last_run_at	DATETIME	Last execution timestamp
created_at	TIMESTAMP	When config was created

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_report_type (report_type)

---

rm_chargebacks

Purpose: Unified chargeback/dispute management records

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
case_id	VARCHAR(50)	PayArc case identifier
mid	VARCHAR(50)	Merchant ID
nmi_transaction_id	VARCHAR(50)	NMI transaction reference
order_number	VARCHAR(50)	Matched middleware order number
amount	DECIMAL(10,2)	Dispute amount
reason_code	VARCHAR(20)	Dispute reason code
reason_desc	TEXT	Dispute reason description
status	VARCHAR(50)	Current dispute status
signifyd_response	LONGTEXT	Signifyd API response (JSON)
payarc_response	LONGTEXT	PayArc webhook data (JSON)
action_log	LONGTEXT	Chronological action audit trail (JSON)
created_at	TIMESTAMP	Record creation timestamp
updated_at	TIMESTAMP	Last update timestamp

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_case_id (case_id)
• KEY idx_order_number (order_number)
• KEY idx_status (status)

---

Dispute Processing Tables

rm_payarc_dispute_linkages

Purpose: PayArc ↔ NMI transaction linkage audit trail

Critical: Yes

Column	Type	Description
id	BIGINT	Auto-increment primary key
case_id	VARCHAR(50)	PayArc case ID
case_number	VARCHAR(50)	PayArc case number
mid	VARCHAR(50)	Merchant ID
nmi_transaction_id	VARCHAR(50)	NMI transaction ID
nmi_order_id	VARCHAR(50)	NMI order ID
order_number	VARCHAR(50)	Our order number
match_strategy	VARCHAR(50)	How match was found
amount	DECIMAL	Dispute amount
auth_code	VARCHAR(20)	Authorization code
arn	VARCHAR(50)	Acquirer Reference Number
transaction_date	DATE	Transaction date
reason_code	VARCHAR(20)	Dispute reason code
reason_desc	TEXT	Dispute reason description
dispute_status	VARCHAR(50)	Current dispute status
payarc_data	LONGTEXT	PayArc data (JSON)
nmi_data	LONGTEXT	NMI data (JSON)
linked_at	DATETIME	When linkage was created
created_at	TIMESTAMP	Record creation timestamp

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_case_id (case_id)
• KEY idx_nmi_transaction (nmi_transaction_id)
• KEY idx_order_number (order_number)

---

Supporting Tables

rm_carrier_mapping

Purpose: Carrier code mapping (BC → platform carrier codes)

Critical: No

Column	Type	Description
id	INT	Auto-increment primary key
platform	VARCHAR(20)	Platform ('bigcommerce' or 'woocommerce')
bc_carrier_code	VARCHAR(50)	Business Central carrier code
platform_carrier_code	VARCHAR(50)	Platform carrier code

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_platform_mapping (platform, bc_carrier_code)

---

shipments

Purpose: Shipment tracking records from Business Central

Critical: No

Column	Type	Description
id	BIGINT	Auto-increment primary key
order_id	BIGINT	FK to rm_order
tracking_number	VARCHAR(100)	Tracking number
shipping_provider	VARCHAR(50)	Carrier name
imported	TINYINT	Imported to platform flag
date	DATETIME	Shipment date

Indexes:

• PRIMARY KEY (id)
• KEY order_id (order_id)
• KEY tracking_number (tracking_number)
• KEY idx_order_id (order_id) — Performance index for Shipping Performance (Migration 020)
• KEY idx_shipping_provider (shipping_provider) — GROUP BY carrier optimization (Migration 020)

---

rm_kits

Purpose: Kit product structure (parent SKU → component SKUs)

Critical: No

Column	Type	Description
parent_sku	VARCHAR(100)	Kit/bundle parent SKU
sku	VARCHAR(100)	Component product SKU
qty	INT	Quantity of component in kit
site_id	INT	Site ID (FK to rm_sites)

Indexes:

• KEY idx_parent_sku (parent_sku)
• KEY idx_site_id (site_id)

---

rm_promotions

Purpose: BigCommerce promotion/bundle data

Critical: No

Column	Type	Description
id	INT	Auto-increment primary key
site_id	INT	Site ID (FK to rm_sites)
bc_promotion_id	INT	BigCommerce promotion ID
is_bundle	TINYINT	Is bundle promotion
buy_condition_data	TEXT	Buy conditions (JSON)
get_action_data	TEXT	Get actions (JSON)

Indexes:

• PRIMARY KEY (id)
• UNIQUE KEY idx_site_promotion (site_id, bc_promotion_id)

---

Schema Verification

The plugin includes a schema verifier that checks:

• ✅ Table existence
• ✅ Required columns
• ✅ Performance indexes
• ✅ Critical vs optional tables

Run verification:

1. Go to DB Environments page
2. Click "Verify Schema"
3. Review results
4. Click "Fix All" to apply migrations if issues found

See Database Migrations for migration system details.

Related Documentation

• Database Migrations - Migration system
• DB Environments - Environment management
• Orders Page Queries - Query analysis
• Performance Optimization - Speed tuning