Question on data immutability, audit logs, bookkeeping and reconciling PSP payment transactions with sales data

[elnygren]

Hey, I'm looking for some information on how Medusa handles audit logs and data immutability. For example, is order and order row data immutable? I have some past experiences where things like partial (or full) refunds to orders caused a lot of pain when trying to reconcile order data with payment transaction data for audit and bookkeeping purposes.

One problem was that order and pricing data was mutable so eg. sales reports taken between $startDate-$endDate might change if any changes to orders happened (eg. due to refunds) between taking the sales reports. *1

Ideally, things like Order, OrderRow, Price, Payment, etc. would be modelled in immutable ways so that there is always accurate historical information available on at least this kind of things:

• a) what did the customer buy
• b) at what price (and discount and taxation)
• c) what did they actually pay according to the PSP
• d) was the order fulfilled or not
• e) has the money been returned (partially or fully) or not)

*1 Btw. do note that some times there are necessary manual fixes to the data and the report should change. However, this should also be explicit (run the report without fixes vs. with fixes).

This is topic is also related to Change Data Capture and Slowly Changing Dimensions. For example, perhaps even if medusa would use a lot of mutable data models, perhaps you offer hooks for CDC to capture all the changes and then it's possible to use a data warehouse to track all of this?

[olivermrbl]

We currently don't have any audit logging in the core, but we plan to explore it later this year. Unfortunately, I can't give you any estimates of when and how we will design it. The same goes for CDC, we don't expose any hooks to set this up. I am not super familiar with the concept, but I imagine you could use the Postgres connection of Medusa directly to stream the WAL and transform it into an audit log. Though, it will probably require you to jump through quite a few hoops.

Regarding data immutability, all models are mutable. Though, the database is modeled to not lose visibility. We create snapshots of data for resources that are subject to change (e.g. variant prices on order items) and calculate totals and infer statuses on read-time.

For example, all orders' totals are calculated when you query for them. To name a few directly related to your question:

• Subtotal is a sum of LineItems (that are each a snapshot of ProductVariant price times quantity)
• Shipping total is a sum of ShippingMethods (that are each a snapshot of ShippingOption price)
• Total refunded amount is a sum of Refunds (a Refund record is created for each refund submitted)

...and the list goes on.

The same applies to concepts like fulfillment, shipments, payments, etc.

So we should store data for you to query accurate information for all the cases here. I'm happy to elaborate but hope this clarifies your two main concerns.

Let me know if there's anything else I can help with!