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:
| Group | Tables |
|---|---|
| Locations | boxcart_locations, boxcart_location_schedules, boxcart_location_slots, boxcart_location_closures Pro, boxcart_location_capacity_overrides Pro, boxcart_location_date_slots Pro |
| Orders | boxcart_orders, boxcart_order_items, boxcart_order_status_log, boxcart_order_messages Pro |
| Baskets | boxcart_baskets, boxcart_basket_items |
| Products | boxcart_product_quantities |
| Search | boxcart_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.
| 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
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.
| Column | Type | Attributes |
|---|---|---|
id | bigint(20) unsigned | NOT NULL AUTO_INCREMENT, PRIMARY KEY |
location_id | bigint(20) unsigned | NOT NULL |
slot_date | date | NOT NULL |
time_start | time | NOT NULL |
time_end | time | NOT NULL |
capacity | int(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.
| 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 |
amount_paid | decimal(10,2) | DEFAULT NULL |
amount_refunded | decimal(10,2) | DEFAULT NULL |
amount_credited | decimal(10,2) | DEFAULT NULL |
customer_notes | text | DEFAULT NULL |
admin_notes | text | DEFAULT NULL |
ip_address | varchar(45) | DEFAULT NULL |
user_agent | varchar(255) | DEFAULT NULL |
view_token | varchar(64) | DEFAULT NULL |
shop_email_sent_at | datetime | DEFAULT NULL (1.8.0) |
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, 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.
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.
| 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_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.
| Column | Type | Attributes |
|---|---|---|
id | bigint(20) unsigned | NOT NULL AUTO_INCREMENT, PRIMARY KEY |
order_id | bigint(20) unsigned | NOT NULL, foreign key to boxcart_orders.id |
sender_type | varchar(20) | NOT NULL: either customer or admin |
sender_id | bigint(20) unsigned | NOT NULL: WP user ID of the sender |
message | text | NOT NULL |
is_read | tinyint(1) | NOT NULL DEFAULT 0 |
created_at | datetime | NOT 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).
| 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, 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()).
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
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
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().
| Column | Type | Attributes |
|---|---|---|
id | bigint(20) unsigned | NOT NULL AUTO_INCREMENT, PRIMARY KEY |
term | varchar(60) | NOT NULL. The search as typed, tidied: lower case, single spaces, at most 60 characters. Used for display. |
term_key | varchar(60) | NOT NULL. The search with plurals folded, so "potatoes" and "potato" count as one search. |
searched_on | date | NOT NULL. The day, in the site's timezone. |
searches | int(10) unsigned | NOT NULL DEFAULT 0. Times it was searched that day. |
results | int(10) unsigned | NOT NULL DEFAULT 0. Products the latest search found, worked out on the server. |
adds | int(10) unsigned | NOT 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.
| 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, 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.
| 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 |
_boxcart_search_words | The 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.
| 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 (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
- 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.
- boxcart_locations → boxcart_location_date_slots: one location has many special times, grouped by date (Pro).
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: each order is linked to the customer's WordPress user account.
- boxcart_orders → boxcart_order_messages: one order has many customer ↔ admin messages.
- 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: each basket is linked to the logged-in customer.
- 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.
Search log
- boxcart_search_log stands alone: it holds no user, order or product IDs.