Database schema

8 custom tables. {prefix} is your site's $wpdb->prefix (usually wp_). Use these CREATE TABLE blocks as the authoritative shape when you write addons that read directly — but prefer OrderUpdatesDb for everything you can.

The plugin owns eight custom tables. No WordPress core or WooCommerce tables are touched. Each CREATE TABLE below is extracted verbatim from src/Shared/Updates/UpdatesTable.php, src/Shared/Attachments/AttachmentsTable.php, and src/Shared/Analytics/AnalyticsLookupTable.php — the source of truth for columns, types, indexes, and constraints.

Tables at a glance

Table	Purpose
`{prefix}order_updates_for_woo`	One row per update. Title, status, assignee, customer-visible flag, timestamps.
`{prefix}order_updates_for_woo_assignees`	Assignment history per update — current and past assignees, useful for the participant list.
`{prefix}order_updates_for_woo_internal_notes`	Internal notes (team-only). Belongs to an update.
`{prefix}order_updates_for_woo_customer_notes`	Customer-visible notes plus system markers (status changes, reopens, rating requests).
`{prefix}order_updates_for_woo_customer_note_history`	Revision snapshots for edited customer notes.
`{prefix}order_updates_for_woo_ratings`	One row per rated update — stars, comment, timestamps.
`{prefix}order_updates_for_woo_attachments`	Attachment metadata — pointer to the file on disk, MIME type, order / update / note bindings.
`{prefix}order_updates_for_woo_analytics_lookup`	Denormalized per-update row driving the analytics dashboard. Read-only fast path.

Don’t read or write directly

Even though the schema is documented here, addon code should never $wpdb->query() these tables directly. Use OrderUpdatesDb or AttachmentsDb:

The methods on those classes handle caching and invalidation. Direct queries skip both.
Schema details can change between minor versions. The public API on those classes stays stable.
If your addon needs data the public API doesn’t expose, open an issue — a missing accessor method is a bug.

{prefix}order_updates_for_woo

CREATE TABLE {prefix}order_updates_for_woo (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			order_id BIGINT(20) UNSIGNED NOT NULL,
			title VARCHAR(191) NOT NULL,
			status VARCHAR(50) NOT NULL DEFAULT '',
			customer_visible TINYINT(1) NOT NULL DEFAULT 0,
			color VARCHAR(20) NOT NULL DEFAULT '#dca100',
			created_by BIGINT(20) UNSIGNED NOT NULL,
			last_updated_by BIGINT(20) UNSIGNED NULL,
			solved_by BIGINT(20) UNSIGNED NULL,
			is_resolved TINYINT(1) NOT NULL DEFAULT 0,
			solved_at DATETIME NULL,
			created_at DATETIME NOT NULL,
			last_updated_at DATETIME NULL,
			assignee_since_note_id BIGINT(20) UNSIGNED NULL,
			previous_assignee_name VARCHAR(100) NULL,
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY title (title),
			KEY order_title (order_id, title),
			KEY order_created_at (order_id, created_at),
			KEY resolved_order (is_resolved, order_id),
			KEY created_at (created_at),
			KEY resolved_created_at (is_resolved, created_at)
		) {charset_collate};

{prefix}order_updates_for_woo_assignees

CREATE TABLE {prefix}order_updates_for_woo_assignees (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			assignee_user_id BIGINT(20) UNSIGNED NOT NULL,
			assigned_by BIGINT(20) UNSIGNED NOT NULL,
			assigned_at DATETIME NOT NULL,
			notified_at DATETIME NULL,
			is_active TINYINT(1) NOT NULL DEFAULT 1,
			unassigned_at DATETIME NULL,
			unassigned_by BIGINT(20) UNSIGNED NULL,
			last_updated_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY update_id (update_id),
			KEY assignee_user_id (assignee_user_id),
			KEY is_active (is_active),
			KEY assigned_by (assigned_by),
			KEY update_assignee (update_id, assignee_user_id),
			KEY update_active (update_id, is_active),
			KEY update_assigned_at (update_id, assigned_at)
		) {charset_collate};

{prefix}order_updates_for_woo_internal_notes

CREATE TABLE {prefix}order_updates_for_woo_internal_notes (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			note VARCHAR(500) NOT NULL,
			mentioned_user_ids VARCHAR(191) NOT NULL DEFAULT '',
			created_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			created_by_name VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			edited_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY update_id (update_id)
		) {charset_collate};

{prefix}order_updates_for_woo_ratings

CREATE TABLE {prefix}order_updates_for_woo_ratings (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			order_id BIGINT(20) UNSIGNED NOT NULL,
			stars TINYINT(1) UNSIGNED NULL,
			comment VARCHAR(500) NOT NULL DEFAULT '',
			created_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			created_by_name VARCHAR(191) NOT NULL DEFAULT '',
			requested_at DATETIME NULL,
			request_notified_at DATETIME NULL,
			created_at DATETIME NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY update_id (update_id),
			KEY order_id (order_id),
			KEY stars (stars)
		) {charset_collate};

{prefix}order_updates_for_woo_customer_notes

CREATE TABLE {prefix}order_updates_for_woo_customer_notes (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			note VARCHAR(500) NOT NULL,
			kind VARCHAR(20) NOT NULL DEFAULT 'note',
			queued_at DATETIME NULL,
			notified_at DATETIME NULL,
			created_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			created_by_name VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			edited_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY update_id (update_id),
			KEY update_notified (update_id, notified_at)
		) {charset_collate};

{prefix}order_updates_for_woo_customer_note_history

CREATE TABLE {prefix}order_updates_for_woo_customer_note_history (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			note_id BIGINT(20) UNSIGNED NOT NULL,
			prior_note VARCHAR(500) NOT NULL,
			edited_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			edited_by_name VARCHAR(191) NOT NULL DEFAULT '',
			edited_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY note_id (note_id)
		) {charset_collate};

{prefix}order_updates_for_woo_attachments

CREATE TABLE {prefix}order_updates_for_woo_attachments (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			order_id BIGINT(20) UNSIGNED NOT NULL,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			note_id BIGINT(20) UNSIGNED NOT NULL,
			note_type VARCHAR(20) NOT NULL DEFAULT 'internal',
			file_name VARCHAR(255) NOT NULL,
			original_name VARCHAR(255) NOT NULL,
			mime_type VARCHAR(100) NOT NULL,
			file_size BIGINT(20) UNSIGNED NOT NULL,
			uploaded_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			uploaded_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY order_id (order_id),
			KEY update_id (update_id),
			KEY note_lookup (note_id, note_type)
		) {charset_collate};

{prefix}order_updates_for_woo_analytics_lookup

CREATE TABLE {prefix}order_updates_for_woo_analytics_lookup (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			update_id BIGINT(20) UNSIGNED NOT NULL,
			order_id BIGINT(20) UNSIGNED NOT NULL,
			created_at DATETIME NOT NULL,
			created_date DATE NOT NULL,
			solved_at DATETIME NULL,
			solved_date DATE NULL,
			resolution_seconds INT UNSIGNED NULL,
			assignee_user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			created_by_user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			is_customer_initiated TINYINT(1) NOT NULL DEFAULT 0,
			customer_visible TINYINT(1) NOT NULL DEFAULT 0,
			rating TINYINT(1) UNSIGNED NULL,
			rating_at DATETIME NULL,
			has_rating_comment TINYINT(1) NOT NULL DEFAULT 0,
			product_id BIGINT(20) UNSIGNED NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY update_id (update_id),
			KEY order_id (order_id),
			KEY created_date (created_date),
			KEY solved_date (solved_date),
			KEY assignee_created (assignee_user_id, created_date),
			KEY rating (rating),
			KEY product_id (product_id)
		) {charset_collate};