CommerceX — Data Model & ER

Validated against the live schema on 2026-09-06 (SHOW COLUMNS, exact COUNT(*)).

Generated per-table dictionary pages live under ../../90-database/tables/ (run php developer/docs/_tools/gen_data_dictionary.php 'order%'). This page is the human layer: what each table is for, the relationships (this schema uses convention, not FK constraints), and the traps.

The central finding: order lines live on orders_master

CommerceX stores one row per line item directly on orders_master — the order carries product_id, seller_id, quantity, unit_price, subtotal, total_amount, GST breakdown, etc. There is a normalized order_items table, but it holds 0 rows and the live checkout path (CommerceOrdersModule::checkoutWithWallet()) does not write it (grep of the module for order_items returns nothing). So:

• ✅ To read what was ordered → orders_master (filter by buyer_id / seller_id / order_uid).
• ❌ Do not join order_items expecting lines — it is empty (a dormant/alternative schema).

A multi-item cart therefore produces multiple orders_master rows sharing an order_uid/ order_number (one per line), not one order header with child items. Confirm the grouping column when aggregating an "order" for display.

Core tables

orders_master (67 columns — see ../../90-database/tables/orders_master.md). Notable groups:

• identity: rowId, order_uid, order_number, buyer_id, seller_id, company_id, product_id, inventory_id, variant_json
• pricing: quantity, unit_price, subtotal, gst_amount, cgst/sgst/igst_amount, shipping_charges, delivery_charges, platform_fee, discount_amount, coupon_code, total_amount
• payment: payment_method enum(cod,online,wallet,upi), payment_status enum(pending,paid,failed,refunded), payment_id, kv_billing_uid
• status/fulfilment: order_status (varchar), delivery_mode enum(quick,regular), tracking_number, carrier_name, estimated_delivery(_at), delivered_at
• cancel/return/refund: cancelled_at, cancel_reason, cancelled_by enum(buyer,seller,admin), return_requested_at, return_approved_at, refund_amount, refund_status enum(na,pending,processed,completed), refunded_at, cancellation_fee
• GST invoice: seller_gstin, customer_gstin, place_of_supply_state(_code), seller_state(_code)

Money linkage (see the finance-ledger doc)

orders_master → central ledger via (module_key='ecommerce', order_table='orders_master', order_id=rowId) in kv_order_finance_master; kv_billing_uid → kv_billing_*; wallet_transactions funds it. Full detail: ../../40-financial/central-finance-ledger.md.

ER (convention-based; few declared FKs)

erDiagram
    customer_master  ||--o{ orders_master : "buyer_id"
    customer_master  ||--o{ orders_master : "seller_id"
    company_master   ||--o{ orders_master : "company_id"
    products_master  ||--o{ orders_master : "product_id"
    products_master  ||--o{ product_variants : "product_id"
    products_master  ||--o{ seller_inventory : "product_id"
    orders_master    ||--o{ order_status_history : "order_id"
    orders_master    ||--o{ commerce_order_routing : "order_id"
    orders_master    ||--o{ order_delivery_links : "order_id"
    orders_master    ||--|| kv_order_finance_master : "(module_key,order_table,order_id)"
    kv_order_finance_master ||--o{ kv_order_finance_entries : "finance_id"
    orders_master    ||--o{ order_escrow_ledger : "order_id"

Relationships are enforced in code, not by FK constraints. kv_docs_foreign_keys('orders_master') returns none — do not rely on information_schema FKs for this schema; use this map.

Re-validate

php developer/docs/_tools/gen_data_dictionary.php 'order%'
php -r 'require "config/baseconfig/config.php"; global $conn;
 foreach(["orders_master","order_items","shopping_cart","cart_items","product_variants","seller_inventory"] as $t)
   echo $t,"=",$conn->query("SELECT COUNT(*) FROM `$t`")->fetchColumn(),"\n";'