Where store money lands in the platform ledger

Orders and fulfilment explains what a sale moves in plain terms. This page names the rows, because when you are reconciling store takings against the platform's transaction table — or investigating a half-completed reversal — plain terms are not enough.

Every leg below is written inside the checkout transaction. Either all of them exist for an order, or none of them does.

The rows one sale writes

An order id is a UUID, and it is the anchor for everything. Below, <id> is that order id.

Every one of these rows is written with status = 'COMPLETED'. There is no pending state in the store's ledger; by the time a row exists the balance has already moved.

The buyer debit

Written by walletService.debit with operationType: ECOMMERCE_PURCHASE, which lands in the ledger as a transaction.type of the same name. Its amount is the full charge — subtotal, less discount, plus shipping, plus tax — and its description spells the arithmetic out so a receipt can be reconstructed from the row alone:

Purchase of Blue Widget x2 (80.00 - 10.00 discount + 5.00 shipping + 7.50 tax) = 82.50 USD

metadata on the same row carries orderId, productId, productName, quantity, subtotal, discountAmount, shippingCost and taxAmount as structured fields, which is usually easier to query than parsing the sentence.

The revenue leg

Only the discounted subtotal is booked as revenue, and only when it is greater than zero. It credits the Super Admin wallet with description Ecommerce order revenue: <product> x<qty>.

This is the one leg that also writes a row to admin_profit, linked to the transaction by transactionId, with type = 'TRADE'. That is the value the platform's profit reporting reads.

The pass-through leg

Shipping and tax left the buyer's wallet, so they have to arrive somewhere. They credit the Super Admin wallet separately, with description Shipping and tax collected on order <id>, and deliberately do not write an admin_profit row.

That is the point of the separation: you still owe the carrier and you still owe the tax authority. Counting the pass-through as profit would overstate your margin on every physical order. If you are reconciling what the platform holds against what you have earned, ORDER_PASSTHROUGH is the difference.

The leg is skipped entirely when shipping and tax are both zero — a digital-only sale in a store with tax switched off writes no pass-through row.

Reversal

Cancelling or rejecting an order runs one shared routine for all three admin doors — the per-order status route, the per-order update route and the bulk status route — so they cannot disagree.

Each leg carries its own idempotency key, so a reversal that half-completed and was retried does not pay anyone twice; a leg that has already run is skipped and logged rather than repeated.

No payment was found for order <id>, so it cannot be refunded.

This message means the status change did not happen. It is checked before the write, precisely so that an order can never be left CANCELLED with the buyer's money still gone. The order is still in whatever state you found it.

You will see it when the order genuinely has no purchase transaction — a row created by hand, a row imported from elsewhere, or an order whose transaction was deleted. Confirm with the query below before doing anything else.

How the payment is found, and why an old order behaves differently

The lookup tries the exact idempotency key ecom_order_<id> first. If that finds nothing, it falls back to any transaction whose referenceId is the bare order id.

That fallback exists for orders placed before the idempotency key was introduced, and it is looser than the primary path: it will match any transaction carrying that reference. In practice that is the purchase debit, because referenceId is unique — but it means an old order's reversal is resolved by a weaker rule than a new one's. If a legacy order reverses oddly, check which of the two paths matched it.

Deleting an order is blocked while its money is still with you

Both delete doors — single and bulk — run a guard before removing anything. It blocks any order that is PENDING or COMPLETED, has a purchase transaction, and has no matching <id>_refund row. Blocked orders are named:

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.

On a bulk delete the message counts the blocked orders and lists the first three. Nothing is deleted when even one is blocked.

Delete removes the row and nothing else — it does not refund, does not restore stock and does not reverse revenue. Cancel first, then delete. Deletes are soft and restorable in any case.

Reconciling by hand

Three things bite when you query the database directly.

Money columns on the transaction and admin_profit tables are DECIMAL(36,18), and the MySQL driver returns them as strings. In JavaScript "1" + 1 is "11". Coerce before any arithmetic. The order table's own money columns (subtotal, discount, shippingCost, tax, total) are DOUBLE and come back as numbers, so the two tables behave differently in the same script.

Order money is in the order's own currency. Each order row carries the currency and walletType copied from the product at checkout. Summing total across a multi-currency catalogue produces a number with no meaning. Group by currency first — the same rule the table analytics apply.

The legs of one order

SET @id = 'PASTE-THE-ORDER-UUID-HERE';

SELECT type, status, amount, currency, referenceId, idempotencyKey, description
FROM   transaction
WHERE  referenceId IN (
         @id,
         CONCAT(@id, '_fee'),
         CONCAT(@id, '_passthrough'),
         CONCAT(@id, '_fee_reversal'),
         CONCAT(@id, '_passthrough_reversal'),
         CONCAT(@id, '_refund')
       )
    OR idempotencyKey = CONCAT('ecom_order_', @id)
ORDER BY createdAt;

A settled, un-refunded sale shows three rows. A cancelled one shows six (or four, if there was no shipping or tax to collect).

Paid orders with no refund row

The same set the delete guard blocks. Useful as a standing check that no cancellation half-completed:

SELECT o.id, o.status, o.total, o.currency, o.createdAt
FROM   ecommerce_order o
JOIN   transaction p  ON p.idempotencyKey = CONCAT('ecom_order_', o.id)
LEFT   JOIN transaction r ON r.referenceId = CONCAT(o.id, '_refund')
WHERE  o.status IN ('CANCELLED', 'REJECTED')
  AND  r.id IS NULL;

Any row this returns is an order marked cancelled whose buyer was never credited — which the guarded path cannot produce, so a hit means something wrote that status outside the admin doors.

Store revenue for a period

SELECT currency, SUM(amount) AS revenue
FROM   admin_profit
WHERE  type = 'TRADE'
  AND  description LIKE 'Ecommerce order revenue:%'
  AND  createdAt >= '2026-08-01'
GROUP  BY currency;

The description filter matters: TRADE is a shared profit category and other parts of the platform write to it. And SUM(amount) here is a string sum in the driver — group by currency and convert deliberately rather than adding across denominations.

What reconciliation cannot express

A cart of three products also produces three independent orders, each with its own id and its own set of ledger rows, and shipping is charged once across the whole checkout rather than once per order. There is no parent record joining them, so "what did this customer's basket cost" is a question the ledger can only answer by grouping the buyer's transactions by time.

Related

• Orders and fulfilment — the status machine and what each admin door does.
• The analytics headers on the admin tables — Gross Revenue, Lost Revenue and Discount Given, and how they are converted.
• Store settings and dashboard — the tax and shipping rates that decide how large the pass-through leg is.