boxcart.dev

Database Schema

The Free build of BoxCart creates 10 custom database tables to store locations, time slots, orders, baskets, product quantity types and the Search Insights log. BoxCart Pro adds 4 more (order messages, location closures, capacity overrides, and special times for a date) for 14 tables in total. 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 custom tables fall into five groups. Four tables are created only by BoxCart Pro and are marked accordingly:

GroupTables
Locationsboxcart_locations, boxcart_location_schedules, boxcart_location_slots, boxcart_location_closures Pro, boxcart_location_capacity_overrides Pro, boxcart_location_date_slots Pro
Ordersboxcart_orders, boxcart_order_items, boxcart_order_status_log, boxcart_order_messages Pro
Basketsboxcart_baskets, boxcart_basket_items
Productsboxcart_product_quantities
Searchboxcart_search_log (1.8.0)

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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
namevarchar(255)NOT NULL
address_line_1varchar(255)DEFAULT NULL
address_line_2varchar(255)DEFAULT NULL
cityvarchar(100)DEFAULT NULL
countyvarchar(100)DEFAULT NULL
postcodevarchar(20)DEFAULT NULL
countryvarchar(2)DEFAULT 'GB'
phonevarchar(32)DEFAULT NULL
emailvarchar(100)DEFAULT NULL
instructionstextDEFAULT NULL
is_activetinyint(1)NOT NULL DEFAULT 1
sort_orderint(11)NOT NULL DEFAULT 0
created_atdatetimeNOT NULL
updated_atdatetimeNOT 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).

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
location_idbigint(20) unsignedNOT NULL
day_of_weektinyint(1)NOT NULL
is_opentinyint(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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
location_idbigint(20) unsignedNOT NULL
day_of_weektinyint(1)NOT NULL
time_starttimeNOT NULL
time_endtimeNOT NULL
capacityint(11)NOT NULL DEFAULT 10
is_activetinyint(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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
location_idbigint(20) unsignedNOT NULL
closure_datedateNOT NULL
reasonvarchar(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).

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
location_idbigint(20) unsignedNOT NULL
slot_idbigint(20) unsignedNOT NULL
override_datedateNOT NULL
capacityint(11)NOT NULL

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

Since 1.6.0, saving an override for a slot and date that already has one updates it in place (INSERT … ON DUPLICATE KEY UPDATE).

boxcart_location_date_slots Pro

Special collection times for a date (BoxCart Pro, 1.6.0). When a location has any rows for a date, they replace that day of the week's slots from boxcart_location_slots on that date, and the date is open even if the weekly schedule closes it. A closure in boxcart_location_closures still closes it. Created by BoxCart_Pro_Database (Pro database version 1.1.0, option boxcart_pro_db_version) and removed with the location.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
location_idbigint(20) unsignedNOT NULL
slot_datedateNOT NULL
time_starttimeNOT NULL
time_endtimeNOT NULL
capacityint(11)NOT NULL DEFAULT 10. The number of orders this time can take; capacity overrides don't apply to it.

Keys: PRIMARY (id), UNIQUE KEY location_date_time (location_id, slot_date, time_start, time_end), KEY slot_date

Slots built from these rows have ids like date-12 (not numbers), so code that receives a slot from boxcart_slots_for_date shouldn't assume a weekly slot ID.

boxcart_orders

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

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
order_numbervarchar(32)NOT NULL
customer_idbigint(20) unsignedDEFAULT NULL
customer_emailvarchar(100)NOT NULL
customer_first_namevarchar(100)NOT NULL
customer_last_namevarchar(100)NOT NULL
customer_phonevarchar(32)DEFAULT NULL
delivery_typevarchar(20)NOT NULL DEFAULT 'collection'
delivery_address_1varchar(255)DEFAULT NULL
delivery_address_2varchar(255)DEFAULT NULL
delivery_cityvarchar(100)DEFAULT NULL
delivery_postcodevarchar(20)DEFAULT NULL
delivery_instructionstextDEFAULT NULL
delivery_feedecimal(10,2)DEFAULT NULL
location_idbigint(20) unsignedDEFAULT NULL
slot_datedateDEFAULT NULL
slot_time_starttimeDEFAULT NULL
slot_time_endtimeDEFAULT NULL
statusvarchar(32)NOT NULL DEFAULT 'pending'
item_countint(11)NOT NULL DEFAULT 0
subtotaldecimal(10,2)DEFAULT NULL
discount_amountdecimal(10,2)DEFAULT NULL
tax_amountdecimal(10,2)DEFAULT NULL
totaldecimal(10,2)DEFAULT NULL
payment_methodvarchar(32)DEFAULT NULL
payment_statusvarchar(32)DEFAULT 'not_required'
transaction_idvarchar(255)DEFAULT NULL
amount_paiddecimal(10,2)DEFAULT NULL
amount_refundeddecimal(10,2)DEFAULT NULL
amount_crediteddecimal(10,2)DEFAULT NULL
customer_notestextDEFAULT NULL
admin_notestextDEFAULT NULL
ip_addressvarchar(45)DEFAULT NULL
user_agentvarchar(255)DEFAULT NULL
view_tokenvarchar(64)DEFAULT NULL
shop_email_sent_atdatetimeDEFAULT NULL (1.8.0)
created_atdatetimeNOT NULL
updated_atdatetimeNOT 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, KEY view_token, KEY location_slot (location_id, slot_date, status) (1.6.0)

payment_status is one of not_required, pending, awaiting_payment, processing, paid, part_paid, failed, refunded or cancelled. part_paid (added in 1.5.0) marks a card-paid order that the customer changed to cost more, with the rest to pay at collection.

amount_paid, amount_refunded and amount_credited (added in 1.5.0, database version 1.0.4) record what the customer has paid towards the order and how much has been refunded, so a paid order can change. amount_credited is the part of the refunds that wasn't for a change to the order (the Refund button, a refund when cancelling, or one made in the Stripe dashboard): the customer doesn't owe it back. What's left to pay at collection is total minus (amount_paid − amount_refunded + amount_credited). On update, orders already paid or refunded have amount_paid set to their total. An order with no amount_paid stored counts as paid in full when its payment status is paid, part paid or refunded.

view_token is the per-order token in the customer's magic link for viewing the order. An empty token marks a card payment attempt that was never placed.

shop_email_sent_at (added in 1.8.0, database version 1.0.5) is when the shop's new order email (new_order_admin) went for the order. It stays NULL if that email was never sent. The BoxCart Today dashboard widget uses it to warn about orders from the last week whose shop email didn't go, with a Resend button. Orders placed before the update have no record, so they aren't checked: the boxcart_shop_email_tracked_since option holds when tracking started, and it starts again from that moment when the new order email is switched back on.

Migration note

Several columns on this table (delivery_*, discount_amount, tax_amount, payment_method, payment_status, transaction_id, amount_paid, amount_refunded, amount_credited, view_token, shop_email_sent_at) are also added by migration methods, because dbDelta does not reliably add columns to existing tables. See BoxCart_Database::ensure_order_columns(), ensure_payment_columns(), ensure_view_token_column() and ensure_shop_email_column().

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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
order_idbigint(20) unsignedNOT NULL
product_idbigint(20) unsignedNOT NULL
product_namevarchar(255)NOT NULL
product_skuvarchar(100)DEFAULT NULL
quantity_type_idbigint(20) unsignedDEFAULT NULL
unit_labelvarchar(50)DEFAULT NULL
price_per_unitdecimal(10,2)DEFAULT NULL
quantitydecimal(10,3)NOT NULL DEFAULT 1
pricedecimal(10,2)DEFAULT NULL
line_totaldecimal(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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
order_idbigint(20) unsignedNOT NULL
old_statusvarchar(32)DEFAULT NULL
new_statusvarchar(32)NOT NULL
changed_bybigint(20) unsignedDEFAULT NULL
notetextDEFAULT NULL
created_atdatetimeNOT NULL

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

boxcart_order_messages

Backs the customer ↔ admin messaging feature shown in the order UI. Each row represents a single message attached to an order, sent by either the customer or an admin.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
order_idbigint(20) unsignedNOT NULL, foreign key to boxcart_orders.id
sender_typevarchar(20)NOT NULL: either customer or admin
sender_idbigint(20) unsignedNOT NULL: WP user ID of the sender
messagetextNOT NULL
is_readtinyint(1)NOT NULL DEFAULT 0
created_atdatetimeNOT NULL

Keys: PRIMARY (id), KEY order_id

boxcart_baskets

Session-based shopping baskets for logged-in customers. Expired baskets are cleaned up by the boxcart_cleanup_expired_baskets cron job (runs twice daily).

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
session_idvarchar(64)NOT NULL
user_idbigint(20) unsignedDEFAULT NULL
selected_location_idbigint(20) unsignedDEFAULT NULL
selected_slot_datedateDEFAULT NULL
selected_slot_time_starttimeDEFAULT NULL
selected_slot_time_endtimeDEFAULT NULL
slot_selected_atdatetimeDEFAULT NULL
slot_reservation_expires_atdatetimeDEFAULT NULL
created_atdatetimeNOT NULL
updated_atdatetimeNOT NULL
expires_atdatetimeNOT NULL

Keys: PRIMARY (id), UNIQUE KEY session_id, KEY user_id, KEY expires_at, KEY selected_location_id, KEY slot_reservation (selected_location_id, selected_slot_date, slot_reservation_expires_at) (1.6.0)

The two 1.6.0 indexes speed up the slot capacity checks (orders booked, and baskets holding a place). Shops updating from an earlier version get them once, on the first page load after the update (BoxCart_Database::ensure_slot_indexes()).

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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
basket_idbigint(20) unsignedNOT NULL
product_idbigint(20) unsignedNOT NULL
quantity_type_idbigint(20) unsignedDEFAULT NULL
quantitydecimal(10,3)NOT NULL DEFAULT 1
added_atdatetimeNOT 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.

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
product_idbigint(20) unsignedNOT NULL
unit_labelvarchar(50)NOT NULL
unit_label_pluralvarchar(50)DEFAULT NULL
display_labelvarchar(20)DEFAULT NULL
price_prefixvarchar(20)DEFAULT 'per'
price_per_unitdecimal(10,2)NOT NULL
allow_decimalstinyint(1)NOT NULL DEFAULT 0
step_incrementdecimal(10,3)NOT NULL DEFAULT 1
min_quantitydecimal(10,3)NOT NULL DEFAULT 1
max_quantitydecimal(10,3)DEFAULT NULL
display_orderint(11)NOT NULL DEFAULT 0
created_atdatetimeNOT 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().

boxcart_search_log

The Search Insights log (1.8.0). One row per search per day: what was searched for, how many times, how many products it found and, with BoxCart Pro, how often it led to a basket add. Nothing in it links a search to a person: there's no user ID, IP address, session or cookie. Rows are only written while Search Insights is switched on (Settings > General > Products, off by default). Created by BoxCart_Database::create_search_log_table().

ColumnTypeAttributes
idbigint(20) unsignedNOT NULL AUTO_INCREMENT, PRIMARY KEY
termvarchar(60)NOT NULL. The search as typed, tidied: lower case, single spaces, at most 60 characters. Used for display.
term_keyvarchar(60)NOT NULL. The search with plurals folded, so "potatoes" and "potato" count as one search.
searched_ondateNOT NULL. The day, in the site's timezone.
searchesint(10) unsignedNOT NULL DEFAULT 0. Times it was searched that day.
resultsint(10) unsignedNOT NULL DEFAULT 0. Products the latest search found, worked out on the server.
addsint(10) unsignedNOT NULL DEFAULT 0. Times the search led to a basket add. Only counted with BoxCart Pro.

Keys: PRIMARY (id), UNIQUE KEY term_day (term_key, searched_on), KEY searched_on

Searches are written with INSERT … ON DUPLICATE KEY UPDATE, so a search has at most one row a day. Searches by shop staff and bots aren't logged, nor is anything that looks like an email address, phone number, postcode or link. Each IP address can log up to 30 searches in 10 minutes. Rows older than 180 days are deleted by the daily boxcart_cleanup_old_logs cron job.

Remove (on the Searches screen and the BoxCart Searches widget) deletes a search's rows that found nothing (results and adds both 0). Add as a search word doesn't change the table: it saves the day in the boxcart_search_fixed_terms option (term key ⇒ date), so the search leaves the "Found nothing" list in the BoxCart Searches dashboard widget until someone searches for it on a later day and it still finds nothing.

Database Migrations

BoxCart uses migration methods in BoxCart_Database to safely add columns to existing tables. They run once for each database version (stored in the boxcart_db_version option; 1.0.5 from BoxCart 1.8.0), tracked via the boxcart_columns_ensured option.

MethodPurpose
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, amount_paid, amount_refunded and amount_credited to orders, and sets amount_paid to the total on orders already paid or refunded
ensure_view_token_column()Adds view_token (and its index) to orders
ensure_shop_email_column()Adds shop_email_sent_at to orders and sets the boxcart_shop_email_tracked_since option (1.8.0)
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.

PropertyValue
Post Typeboxcart_product
Publicfalse
Show UIfalse
Publicly Queryablefalse
Has Archivefalse
Supportstitle, editor, thumbnail

Each product stores additional data in post meta:

Meta KeyPurpose
_boxcart_skuProduct SKU
_boxcart_priceProduct price
_boxcart_stock_statusStock status (in_stock or out_of_stock)
_boxcart_sort_orderDisplay sort order
_boxcart_search_wordsThe product's "Also found by" words (1.8.0): extra words the shop search finds it by. Stored lower case and comma-separated, up to 500 characters. Add as a search word (on the Searches screen and the BoxCart Searches widget) adds to it.

Custom Taxonomy

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

PropertyValue
Taxonomyboxcart_product_category
Attached Toboxcart_product
Hierarchicaltrue
Publicfalse
Show UIfalse

Term Meta: _boxcart_sort_order: controls category display order.

User Meta

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

Meta KeyPurpose
boxcart_phoneCustomer phone number
boxcart_favourites (single-site)
boxcart_favourites_{blog_id} (multisite)
Serialised array of favourite product IDs. Scoped per subsite on multisite so favourites don't bleed across stores.
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

Order group

Basket group

Product group

Search log