What’s in the database
- Only your company’s data. Every table with a
company_idcolumn holds only your company’s rows. Tables without one, such as currency and exchange-rate lists, are shared reference data. - A copy, as of the last sync. The data is as fresh as Last synced on the Databases screen. A sync replaces the whole copy in one step, so a query never sees a half-finished sync.
- Deleted records are left out of
customers,members,productsandvariants.orderskeeps every order, including draft and cancelled orders and orders with a zero total. - No foreign keys. Tables have primary keys on
id, but no foreign keys. Join on the columns in How the tables join.
Money and currency
- Amounts are decimals in the currency’s main unit, such as
49.95for $49.95, not cents. - An order’s amounts are in the order’s currency,
orders.currency_code. Line items and refunds are in their order’s currency. Never add up amounts in different currencies. - Columns ending in
_base, such astotal_price_base, hold the same amount in US dollars. Use them to total orders across currencies.orders.base_sourcesays where the dollar amount came from:stored: the dollar amount Fluid recorded with the order.presentment_usd: the order was in US dollars, so the order’s own amount is used.official_monthly_rate: converted with the official exchange rate for the order’s month.unavailable: no rate was found, and the_basecolumns are empty.
- Subscription prices are in the subscription’s own
currency_code.
Main tables
orders
One row per order.line_items
One row per product line on an order. Joins toorders on order_id, and to products and variants on product_id and variant_id.
price is the price of one unit, so a line’s amount is price * quantity. Other columns include title, quantity, total_tax, total_discount, total_refunded, cv and qv.
customers
One row per customer. Joins to orders throughmember_id, not a customer ID. Columns include email, first_name, last_name, phone, orders_count, total_spent_in_base, last_order_id and external_id.
orders_count counts all of the customer’s orders. total_spent_in_base adds up, in US dollars, only orders whose financial_status is paid.
members
One row per member.id joins to orders.member_id. Columns include enroller_member_id and sponsor_member_id, which point to other members, and rank_id and member_type_id.
products and variants
products has one row per product, with sku, status (published, draft, scheduled or archived) and active. variants has one row per variant, with product_id, sku, title and active.
commerce_subscriptions
One row per subscription. Columns includestatus, member_id (the subscriber), rep_member_id, variant_id, quantity, current_price, currency_code, next_billing_date, last_bill_date and cancelled_at.
orders_subscriptions
Links subscriptions to the orders they created:order_id and subscription_id. orders has no subscription column, so join through this table.
payments and refunds
payments has one row per payment, with order_id, subscription_id, status, amount, amount_in_base, refunded_amount and currency_code. refunds has one row per refund, with order_id, amount, reason and refund_type. order_item_refunds links a refund to the line items it covers: refund_id, item_id (the line item’s id) and quantity.
order_reps
The reps on each order:order_id, member_id and role. role is attribution, buyer, buyer_rep, sponsor or volume.
How the tables join
For example, this query lists last month’s best-selling products by units:
Status values
There’s no flag for test orders. Sample orders have
type = 'sample'.
To count revenue the way the admin’s Home dashboard does, see Commerce metrics.