Database Schema

BoxCart creates 11 custom database tables to store locations, time slots, orders, baskets, and product quantity types. This reference documents every column, type, and index.

Overview

All custom tables are prefixed with {$wpdb->prefix}boxcart_ (e.g. wp_boxcart_locations on a default WordPress install). They are defined in BoxCart_Database (includes/core/class-boxcart-database.php) and created automatically on plugin activation via BoxCart_Activator::activate().

The 11 custom tables fall into four groups:

Group	Tables
Locations	`boxcart_locations`, `boxcart_location_schedules`, `boxcart_location_slots`, `boxcart_location_closures`, `boxcart_location_capacity_overrides`
Orders	`boxcart_orders`, `boxcart_order_items`, `boxcart_order_status_log`
Baskets	`boxcart_baskets`, `boxcart_basket_items`
Products	`boxcart_product_quantities`

In addition to the custom tables, BoxCart uses the WordPress boxcart_product custom post type, the boxcart_product_category taxonomy, and several user meta keys.

boxcart_locations

Stores collection location details including address, contact information, and display ordering.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`name`	`varchar(255)`	NOT NULL
`address_line_1`	`varchar(255)`	DEFAULT NULL
`address_line_2`	`varchar(255)`	DEFAULT NULL
`city`	`varchar(100)`	DEFAULT NULL
`county`	`varchar(100)`	DEFAULT NULL
`postcode`	`varchar(20)`	DEFAULT NULL
`country`	`varchar(2)`	DEFAULT 'GB'
`phone`	`varchar(32)`	DEFAULT NULL
`email`	`varchar(100)`	DEFAULT NULL
`instructions`	`text`	DEFAULT NULL
`is_active`	`tinyint(1)`	NOT NULL DEFAULT 1
`sort_order`	`int(11)`	NOT NULL DEFAULT 0
`created_at`	`datetime`	NOT NULL
`updated_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), KEY is_active, KEY sort_order

boxcart_location_schedules

Defines which days of the week each location is open. One row per location per day (0 = Sunday, 6 = Saturday).

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`location_id`	`bigint(20) unsigned`	NOT NULL
`day_of_week`	`tinyint(1)`	NOT NULL
`is_open`	`tinyint(1)`	NOT NULL DEFAULT 0

Keys: PRIMARY (id), UNIQUE KEY location_day (location_id, day_of_week), KEY location_id

boxcart_location_slots

Collection time slots per location per day of week. Each slot has a start/end time and an order capacity limit.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`location_id`	`bigint(20) unsigned`	NOT NULL
`day_of_week`	`tinyint(1)`	NOT NULL
`time_start`	`time`	NOT NULL
`time_end`	`time`	NOT NULL
`capacity`	`int(11)`	NOT NULL DEFAULT 10
`is_active`	`tinyint(1)`	NOT NULL DEFAULT 1

Keys: PRIMARY (id), KEY location_day (location_id, day_of_week), KEY is_active

boxcart_location_closures

Ad-hoc closure dates for locations (e.g. bank holidays, staff training). Orders cannot be placed for a closed date.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`location_id`	`bigint(20) unsigned`	NOT NULL
`closure_date`	`date`	NOT NULL
`reason`	`varchar(255)`	DEFAULT NULL

Keys: PRIMARY (id), UNIQUE KEY location_date (location_id, closure_date), KEY location_id, KEY closure_date

boxcart_location_capacity_overrides

Allows overriding the default slot capacity for a specific date (e.g. increasing capacity on a busy day or reducing it for a short-staffed day).

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`location_id`	`bigint(20) unsigned`	NOT NULL
`slot_id`	`bigint(20) unsigned`	NOT NULL
`override_date`	`date`	NOT NULL
`capacity`	`int(11)`	NOT NULL

Keys: PRIMARY (id), UNIQUE KEY slot_date (slot_id, override_date), KEY location_id, KEY slot_id

boxcart_orders

The main orders table. Stores customer details, collection slot selection, order totals, payment information, and timestamps.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`order_number`	`varchar(32)`	NOT NULL
`customer_id`	`bigint(20) unsigned`	DEFAULT NULL
`customer_email`	`varchar(100)`	NOT NULL
`customer_first_name`	`varchar(100)`	NOT NULL
`customer_last_name`	`varchar(100)`	NOT NULL
`customer_phone`	`varchar(32)`	DEFAULT NULL
`delivery_type`	`varchar(20)`	NOT NULL DEFAULT 'collection'
`delivery_address_1`	`varchar(255)`	DEFAULT NULL
`delivery_address_2`	`varchar(255)`	DEFAULT NULL
`delivery_city`	`varchar(100)`	DEFAULT NULL
`delivery_postcode`	`varchar(20)`	DEFAULT NULL
`delivery_instructions`	`text`	DEFAULT NULL
`delivery_fee`	`decimal(10,2)`	DEFAULT NULL
`location_id`	`bigint(20) unsigned`	DEFAULT NULL
`slot_date`	`date`	DEFAULT NULL
`slot_time_start`	`time`	DEFAULT NULL
`slot_time_end`	`time`	DEFAULT NULL
`status`	`varchar(32)`	NOT NULL DEFAULT 'pending'
`item_count`	`int(11)`	NOT NULL DEFAULT 0
`subtotal`	`decimal(10,2)`	DEFAULT NULL
`discount_amount`	`decimal(10,2)`	DEFAULT NULL
`tax_amount`	`decimal(10,2)`	DEFAULT NULL
`total`	`decimal(10,2)`	DEFAULT NULL
`payment_method`	`varchar(32)`	DEFAULT NULL
`payment_status`	`varchar(32)`	DEFAULT 'not_required'
`transaction_id`	`varchar(255)`	DEFAULT NULL
`customer_notes`	`text`	DEFAULT NULL
`admin_notes`	`text`	DEFAULT NULL
`ip_address`	`varchar(45)`	DEFAULT NULL
`user_agent`	`varchar(255)`	DEFAULT NULL
`created_at`	`datetime`	NOT NULL
`updated_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), UNIQUE KEY order_number, KEY customer_id, KEY customer_email, KEY location_id, KEY slot_date, KEY status, KEY payment_status, KEY created_at, KEY delivery_type

Migration note

Several columns on this table (delivery_*, discount_amount, tax_amount, payment_method, payment_status, transaction_id) are added via migration methods rather than the initial CREATE TABLE statement, because dbDelta does not reliably add columns to existing tables. See BoxCart_Database::ensure_order_columns() and ensure_payment_columns().

boxcart_order_items

Line items for each order. Product name and SKU are stored as snapshots so the order record is preserved even if the product is later deleted.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`order_id`	`bigint(20) unsigned`	NOT NULL
`product_id`	`bigint(20) unsigned`	NOT NULL
`product_name`	`varchar(255)`	NOT NULL
`product_sku`	`varchar(100)`	DEFAULT NULL
`quantity_type_id`	`bigint(20) unsigned`	DEFAULT NULL
`unit_label`	`varchar(50)`	DEFAULT NULL
`price_per_unit`	`decimal(10,2)`	DEFAULT NULL
`quantity`	`decimal(10,3)`	NOT NULL DEFAULT 1
`price`	`decimal(10,2)`	DEFAULT NULL
`line_total`	`decimal(10,2)`	DEFAULT NULL

Keys: PRIMARY (id), KEY order_id, KEY product_id

boxcart_order_status_log

Audit trail for order status changes. Records who changed the status and an optional note.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`order_id`	`bigint(20) unsigned`	NOT NULL
`old_status`	`varchar(32)`	DEFAULT NULL
`new_status`	`varchar(32)`	NOT NULL
`changed_by`	`bigint(20) unsigned`	DEFAULT NULL
`note`	`text`	DEFAULT NULL
`created_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), KEY order_id, KEY created_at

boxcart_baskets

Session-based shopping baskets. Supports both guest and logged-in users. Expired baskets are cleaned up by the boxcart_cleanup_expired_baskets cron job (runs twice daily).

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`session_id`	`varchar(64)`	NOT NULL
`user_id`	`bigint(20) unsigned`	DEFAULT NULL
`selected_location_id`	`bigint(20) unsigned`	DEFAULT NULL
`selected_slot_date`	`date`	DEFAULT NULL
`selected_slot_time_start`	`time`	DEFAULT NULL
`selected_slot_time_end`	`time`	DEFAULT NULL
`slot_selected_at`	`datetime`	DEFAULT NULL
`slot_reservation_expires_at`	`datetime`	DEFAULT NULL
`created_at`	`datetime`	NOT NULL
`updated_at`	`datetime`	NOT NULL
`expires_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), UNIQUE KEY session_id, KEY user_id, KEY expires_at, KEY selected_location_id

Migration note

The slot selection columns (selected_location_id, selected_slot_date, selected_slot_time_start, selected_slot_time_end, slot_selected_at, slot_reservation_expires_at) are added via BoxCart_Database::ensure_slot_columns().

boxcart_basket_items

Individual items within a basket. The unique key on (basket_id, product_id, quantity_type_id) ensures a product with the same quantity type only appears once per basket — adding it again increments the quantity.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`basket_id`	`bigint(20) unsigned`	NOT NULL
`product_id`	`bigint(20) unsigned`	NOT NULL
`quantity_type_id`	`bigint(20) unsigned`	DEFAULT NULL
`quantity`	`decimal(10,3)`	NOT NULL DEFAULT 1
`added_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), UNIQUE KEY basket_product_qty_type (basket_id, product_id, quantity_type_id), KEY basket_id, KEY product_id

Migration note

The quantity_type_id column and the change of quantity from int to decimal(10,3) were added via BoxCart_Database::ensure_quantity_type_columns().

boxcart_product_quantities

Defines quantity types for products (e.g. "per kg", "per bunch", "each"). A single product can have multiple quantity types, each with its own price per unit, step increment, and min/max constraints.

Column	Type	Attributes
`id`	`bigint(20) unsigned`	NOT NULL AUTO_INCREMENT, PRIMARY KEY
`product_id`	`bigint(20) unsigned`	NOT NULL
`unit_label`	`varchar(50)`	NOT NULL
`unit_label_plural`	`varchar(50)`	DEFAULT NULL
`display_label`	`varchar(20)`	DEFAULT NULL
`price_prefix`	`varchar(20)`	DEFAULT 'per'
`price_per_unit`	`decimal(10,2)`	NOT NULL
`allow_decimals`	`tinyint(1)`	NOT NULL DEFAULT 0
`step_increment`	`decimal(10,3)`	NOT NULL DEFAULT 1
`min_quantity`	`decimal(10,3)`	NOT NULL DEFAULT 1
`max_quantity`	`decimal(10,3)`	DEFAULT NULL
`display_order`	`int(11)`	NOT NULL DEFAULT 0
`created_at`	`datetime`	NOT NULL

Keys: PRIMARY (id), KEY product_id, KEY display_order

Migration note

The display_label column was added via BoxCart_Database::ensure_display_label_column() and price_prefix via ensure_price_prefix_column().

Database Migrations

BoxCart uses migration methods in BoxCart_Database to safely add columns to existing tables. These run on activation and are tracked via the boxcart_columns_ensured option.

Method	Purpose
`ensure_slot_columns()`	Adds slot selection columns to the baskets table
`ensure_order_columns()`	Adds delivery and discount columns to the orders table
`ensure_quantity_type_columns()`	Adds quantity type support to basket_items and order_items; changes `quantity` from `int` to `decimal`
`ensure_display_label_column()`	Adds `display_label` to product_quantities
`ensure_price_prefix_column()`	Adds `price_prefix` to product_quantities
`ensure_payment_columns()`	Adds `payment_method`, `payment_status`, `transaction_id` to orders
`fix_inconsistent_quantity_settings()`	One-time data fix for legacy quantity data

Custom Post Type

Products are stored as the boxcart_product custom post type. The CPT is not publicly queryable — products are displayed exclusively through BoxCart shortcodes and blocks.

Property	Value
Post Type	`boxcart_product`
Public	`false`
Show UI	`false`
Publicly Queryable	`false`
Has Archive	`false`
Supports	`title`, `editor`, `thumbnail`

Each product stores additional data in post meta:

Meta Key	Purpose
`_boxcart_sku`	Product SKU
`_boxcart_price`	Product price
`_boxcart_stock_status`	Stock status (`in_stock` or `out_of_stock`)
`_boxcart_sort_order`	Display sort order

Custom Taxonomy

Product categories use the boxcart_product_category custom taxonomy, attached to the boxcart_product post type.

Property	Value
Taxonomy	`boxcart_product_category`
Attached To	`boxcart_product`
Hierarchical	`true`
Public	`false`
Show UI	`false`

Term Meta: _boxcart_sort_order — controls category display order.

User Meta

BoxCart stores customer-specific data in the WordPress usermeta table:

Meta Key	Purpose
`boxcart_phone`	Customer phone number
`boxcart_favourites`	Serialised array of favourite product IDs
`boxcart_billing_*`	Billing address fields

Entity Relationships

The diagram below describes how the custom tables relate to each other. Foreign keys are enforced at the application level, not by database constraints.

Location group

boxcart_locations → boxcart_location_schedules — one location has many schedules (one per day of week).
boxcart_locations → boxcart_location_slots — one location has many time slots.
boxcart_locations → boxcart_location_closures — one location has many closure dates.
boxcart_locations → boxcart_location_capacity_overrides — one location has many capacity overrides.
boxcart_location_slots → boxcart_location_capacity_overrides — one slot can have many date-specific capacity overrides.

Order group

boxcart_orders → boxcart_order_items — one order has many line items.
boxcart_orders → boxcart_order_status_log — one order has many status change entries.
boxcart_orders.location_id → boxcart_locations.id — each order references a collection location.
boxcart_orders.customer_id → wp_users.ID — logged-in customers are linked to their WordPress user account (NULL for guest orders).
boxcart_order_items.product_id → wp_posts.ID (boxcart_product) — each line item references a product.
boxcart_order_items.quantity_type_id → boxcart_product_quantities.id — optional link to the quantity type used.

Basket group

boxcart_baskets → boxcart_basket_items — one basket has many items.
boxcart_baskets.user_id → wp_users.ID — logged-in users are linked to their basket (NULL for guests).
boxcart_baskets.selected_location_id → boxcart_locations.id — the currently selected collection location.
boxcart_basket_items.product_id → wp_posts.ID (boxcart_product) — each item references a product.
boxcart_basket_items.quantity_type_id → boxcart_product_quantities.id — optional link to the quantity type.

Product group

wp_posts (boxcart_product) → boxcart_product_quantities — one product can have many quantity types.
wp_posts (boxcart_product) → wp_term_relationships → wp_terms (boxcart_product_category) — products are assigned to categories via the standard WordPress taxonomy system.