Tables, cascades and what a delete really removes

E-commerce adds eleven tables to the same MySQL schema core already uses. No second database, no separate connection, no migration files — the tables are synced from the model definitions on boot.

This page is for the three jobs that need the schema rather than the screens: writing a report against the database, planning a backup or a migration, and working out what a Permanent delete button is actually about to remove.

The eleven tables

Every primary key is a CHAR(36) UUID with a v4 default. Nine of the eleven are paranoid — a delete stamps deletedAt and the row stays. The two that are not (ecommerce_order_item, ecommerce_user_discount) have no deletedAt column at all, so any delete that reaches them is permanent.

The order carries the money; the order item carries the goods

This split is the single most important thing on the page, because the two halves have different delete rules.

ecommerce_order

There is no orderNumber, no reference, no paymentId and no product column on the order. A cart of three products produces three independent order rows with three unrelated UUIDs and no parent record joining them.

ecommerce_order_item

Unique on (orderId, productId). No timestamps, no deletedAt.

The one-use-per-customer record

ecommerce_user_discount is four columns and easy to miss:

Unique on (userId, discountId). Written as an upsert inside the checkout transaction, immediately after the wallet debit, whenever a discount was applied. It is the only place a code's usage is recorded.

Checkout reads it twice:

• SELECT COUNT(*) WHERE discountId = ? AND status = true against the discount's maxUses, and
• SELECT ... WHERE userId = ? AND discountId = ? AND status = true for "You have already used this discount".

Shipment records: your figures, not the buyer's

ecommerce_shipping is a log of a consignment. It is not connected to what anyone was charged.

cost and tax here are your own carrier figures — what the consignment cost you. They are not what the buyer was charged: that is ecommerce_order.shippingCost and ecommerce_order.tax, written by checkout from ecommerceDefaultShippingCost and the tax rate in force at sale time.

They are not private, though. GET /api/ecommerce/order/{id} eager-loads the whole shipment row, and the customer's own order page renders shipping.cost as its Shipping line and shipping.tax as its Tax line, then adds both into the Total it displays. The admin order page prints the same two values with a $ in front of them, whatever currency the carrier invoiced you in. So whatever you type into a shipment's cost and tax is shown to every buyer whose order is assigned to that shipment, and the total that page displays will not match ecommerce_order.total — that column is what was actually debited, and it is the only figure a report, a receipt or a refund should use.

Note the precision difference: shipment money is FLOAT, order money is DOUBLE. Do not sum the two together and expect them to agree.

Orders point at a shipment through ecommerce_order.shippingId, so the relationship is many orders to one shipment. Operational detail is on Shipping and fulfilment records.

The rest, briefly

Stock, correctly

Both order doors — POST /api/ecommerce/cart/checkout and POST /api/ecommerce/order — decrement stock inside the transaction that creates the order, with a conditional update:

UPDATE ecommerce_product
   SET inventoryQuantity = inventoryQuantity - :quantity
 WHERE id = :productId
   AND inventoryQuantity >= :quantity

If that update touches zero rows, the handler throws Product inventory changed during checkout and the whole transaction rolls back — no order, no debit, no stock movement. It is what makes two simultaneous buyers of the last unit safe.

Three consequences for anyone reading the table:

• A PENDING physical order has already taken its stock. Reserved and sold are the same state here.
• Only PHYSICAL products are touched. inventoryQuantity on a DOWNLOADABLE product is inert — it is stored and ignored.
• Cancelling or rejecting an order puts the stock back, in the same transaction as the refund.

The cart is not a table

There is no cart table and no cart endpoint that stores anything. The cart lives in the browser's localStorage, under the key ecommerce-storage, and only cart and wishlist are persisted from that store.

• Nothing is reserved when a customer adds to the cart.
• Clearing site data empties it, and it does not follow the customer to another device.
• The wishlist is the one thing that lives in both places: the browser copy is a mirror, and ecommerce_wishlist / ecommerce_wishlist_item are the server-side record.
• A cart persisted by an older build can hold product entries missing walletType; the store repairs those on load rather than failing checkout.

So "how many abandoned carts do we have" is not a question this schema can answer. There is no row until checkout succeeds.

Soft delete, permanent delete and restore

Every store table with a deletedAt is wired the same way. The admin tables expose all three doors:

On the screens: the Show deleted button in the table toolbar reveals soft-deleted rows and turns the row menu into Restore and Permanent delete. Permanent delete raises a confirmation dialog; restore does not.

The button renders on every paranoid table, which is all seven store screens. Permissions decide whether it works, not whether it appears: a role missing either the view. or the delete. key sees it greyed out, with a tooltip saying they need both. "The Show deleted button does nothing" is a permissions answer, not a bug.

The cascade map

Every foreign key in this addon is declared ON DELETE CASCADE. A soft delete never reaches them; a permanent delete reaches all of them, in the database, below every application guard.

user ─┬─> ecommerce_order ─┬─> ecommerce_order_item
      │                    └─> ecommerce_shipping_address
      ├─> ecommerce_review
      ├─> ecommerce_wishlist ─> ecommerce_wishlist_item
      └─> ecommerce_user_discount

ecommerce_category ─> ecommerce_product ─┬─> ecommerce_review
                                         ├─> ecommerce_discount ─> ecommerce_user_discount
                                         ├─> ecommerce_order_item
                                         └─> ecommerce_wishlist_item

ecommerce_shipping ─> ecommerce_order ─┬─> ecommerce_order_item
                                       └─> ecommerce_shipping_address

Two of those paths are data-loss events, and neither warns you beyond the generic confirmation dialog.

Permanently deleting a category

ecommerce_category → ecommerce_product is a cascade, so one permanent category delete removes every product in it, and each product then removes its reviews, its discount codes (and their usage records), its wishlist entries and its order items.

The order rows survive. Their items do not. Afterwards you hold orders that recorded a charge for a product that no longer exists, with the buyer's licence key and download path gone, and no soft-delete copy of either.

Permanently deleting a shipment

This is the one nobody expects. ecommerce_shipping.id is the parent of ecommerce_order.shippingId, and that FK is ON DELETE CASCADE too. So a permanent delete of a shipment record deletes every order assigned to it — and, through those orders, their items and shipping addresses.

The orders table refuses to delete a paid, unrefunded order:

Order <id> has been paid for and not refunded. Cancel or reject it first — that refunds the buyer and restores the stock — then delete it.

That guard lives in the order delete handler. A cascade from the shipping table never calls it. Since a shipment is deliberately shared across a courier run, one permanent delete can take out a whole batch of paid orders.

Deleting a customer

Deleting a user cascades into their orders, reviews, wishlist, shipping addresses and discount usage records. The store keeps no anonymised copy of a sale. If you delete a customer for a data-erasure request, your revenue history loses those orders — export first.

Checking your own database

The cascades come from the model definitions, which are what created the tables. To confirm what your install actually has:

SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, CONSTRAINT_NAME
  FROM information_schema.KEY_COLUMN_USAGE
 WHERE TABLE_SCHEMA = DATABASE()
   AND REFERENCED_TABLE_NAME LIKE 'ecommerce\_%';

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, DELETE_RULE
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = DATABASE()
   AND TABLE_NAME LIKE 'ecommerce\_%';

Reporting and backup notes

• Exclude soft-deleted rows. Nine of the eleven tables have deletedAt, and the admin screens hide those rows by default. A raw SELECT does not — add WHERE deletedAt IS NULL or your revenue figures will include deleted orders.
• Sum total, in one currency at a time. Orders carry their own currency and walletType. Adding a USD order to a USDT order produces a number that means nothing.
• Join on the order item for product-level reporting. The order row does not name a product; ecommerce_order_item.productId does.
• ecommerce_order.status is the entitlement. A digital download is served only for a COMPLETED order. Do not infer it from the transaction ledger.
• Back up the uploads directory as well as the database. Self-hosted download files and every product and category image live on disk; the tables hold paths only. Core's Backup and restore covers the commands.
• Nothing in this schema needs a manual migration. Tables are synced from the models at boot, so an extension update restarts into the new shape.

Related: API and data for the endpoints, Permissions and roles for who can reach the delete doors, Categories and products and Orders and fulfilment for the screens that drive all of the above.