Database Schema

Database Schema

All custom tables use the {$wpdb->prefix}mirakl_* naming convention. Tables are created on plugin activation and updated via non-destructive migrations.

{$prefix}mirakl_api_settings

Per-store API credentials and configuration.

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255) UNIQUE
api_endpoint	varchar(500)	Mirakl MMP API base URL
api_key	varchar(500)	API key (encrypted at rest)
shop_id	varchar(255)	Nullable
active	tinyint(1) DEFAULT 1	Toggle store
locale	varchar(20)	Store language (e.g., de_DE, fr_FR)
shipping_method	varchar(255)	Nullable, per-store WC shipping method
shipping_method_title	varchar(255)	Nullable, per-store shipping label prefix
created_at	datetime
updated_at	datetime	ON UPDATE CURRENT_TIMESTAMP

{$prefix}mirakl_orders

Order mapping between Mirakl and WooCommerce.

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255)
mirakl_order_id	varchar(255)	UNIQUE with store
wc_order_id	bigint(20)	INDEX
order_status	varchar(100) DEFAULT 'pending'
total_amount	decimal(15,2)
currency	varchar(10) DEFAULT 'EUR'
customer_email	varchar(255)
created_at	datetime
updated_at	datetime

{$prefix}mirakl_sync_history

Audit trail for all sync operations.

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255)
sync_type	enum('order','product','stock','price','offer','return')
mirakl_id	varchar(255)
wc_id	bigint(20)
status	enum('success','failed','pending')
error_message	text
created_at	datetime	INDEX

{$prefix}mirakl_carriers

Carrier codes from the CA01 API.

Column	Type	Notes
id	bigint(20) PK AI
mirakl_store_name	varchar(255)
code	varchar(255)	UNIQUE with store
label	varchar(255)
standard_code	varchar(255)	Nullable
tracking_url	text	Nullable
created_at	datetime
updated_at	datetime

{$prefix}mirakl_carrier_mapping

Per-channel carrier overrides.

Column	Type	Notes
id	bigint(20) PK AI
mirakl_store_name	varchar(255)
channel_code	varchar(255)	UNIQUE with store
carrier_code	varchar(255)
tracking_url	text	Nullable
created_at	datetime
updated_at	datetime

{$prefix}mirakl_channel_categories

Category hierarchy cache from TBCATALOG classification XML.

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255)
channel_code	varchar(255)
category_code	varchar(255)	UNIQUE with store+channel
category_label	varchar(500)
parent_code	varchar(255)	Nullable
is_leaf	tinyint(1) DEFAULT 0
updated_at	datetime

{$prefix}mirakl_product_mappings

Product ID cross-references.

Column	Type	Notes
id	bigint(20) PK AI
woocommerce_product_id	bigint(20)	UNIQUE with store
mirakl_product_id	varchar(255)	INDEX
mirakl_offer_id	varchar(255)	Nullable
store_name	varchar(255)
last_sync	datetime	Nullable

{$prefix}mirakl_offer_sku_cache

SKU resolution cache (auto-populated from OF21 API).

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255)
shop_sku	varchar(255)	UNIQUE with store
product_sku	varchar(255)
offer_id	varchar(255)	Nullable
updated_at	datetime
created_at	datetime

{$prefix}mirakl_pricing_imports

PRI01 pricing import lifecycle tracking.

Column	Type	Notes
id	bigint(20) UNSIGNED PK AI
store_name	varchar(190)
mirakl_import_id	varchar(190)	UNIQUE with store
status	varchar(20) DEFAULT 'pending'	pending, running, completed, failed, cancelled
row_count	int UNSIGNED
success_count	int UNSIGNED
failed_count	int UNSIGNED
submitted_at	datetime
completed_at	datetime	Nullable
last_polled_at	datetime
poll_count	int UNSIGNED
error_message	text	Nullable
error_report	longtext	Nullable
source	varchar(40) DEFAULT 'cron'	cron or manual

{$prefix}mirakl_attributes

PM11 attribute hierarchy and VL11 value lists cache.

Column	Type	Notes
id	bigint(20) PK AI
store_name	varchar(255)
type	varchar(20)	'hierarchy' (PM11) or 'value_list' (VL11)
code	varchar(255)
label	text	Nullable
parent_code	varchar(255)	Nullable
values_json	longtext	Nullable, JSON array for VL11
updated_at	datetime

{$prefix}mirakl_fetch_times

Order fetch cursor tracking.

Column	Type	Notes
id	bigint(20) PK AI
mirakl_store_name	varchar(255) UNIQUE
last_fetch_time	datetime

{$prefix}mirakl_tracking_update_times

Tracking update cursor tracking.

Column	Type	Notes
id	bigint(20) PK AI
mirakl_store_name	varchar(255) UNIQUE
last_tracking_update_time	datetime

WordPress Options

Option	Type	Description
mirakl_connector_version	string	Plugin version on activation
mirakl_connector_db_version	string	Current DB schema version
mirakl_orders_sync_enabled	bool	Order sync toggle
mirakl_orders_sync_interval	string	Order sync cron interval
mirakl_stock_sync_enabled	bool	Stock sync toggle
mirakl_stock_sync_interval	string	Stock sync cron interval
mirakl_price_sync_enabled	bool	Price sync toggle
mirakl_price_sync_interval	string	Price sync cron interval
mirakl_products_sync_enabled	bool	Product sync toggle
mirakl_products_sync_interval	string	Product sync cron interval
mirakl_channel_delivery_note_actions	array	Per-channel delivery note URL config
mirakl_delivery_note_store_settings	array	Per-store delivery note settings (logo, language, footer)

Product Post Meta

Meta Key	Type	Description
_mirakl_active	'yes'|'no'	Product synced to Mirakl
_mirakl_brand	string	Brand name
_mirakl_gtin	string	GTIN/EAN/UPC
_mirakl_order_id	string	Mirakl order ID (on WC orders)
mirakl_order_id	string	Alternative meta key
_mirakl_channel_code	string	Channel code (on WC orders)
mirakl_channel_code	string	Alternative meta key
_tracking_number	string	Shipping tracking number
_carrier_code	string	Shipping carrier code