MetaData support for Products, Orders and other resources

[fthobe]

Introduction
Currently custom fields need to be hardcoded into the solution.
There are endless needs to customize orders, for example:

• some countries require particular fields for the invoice (Italy requires an electronic invoice ID for every billing address)
• some products might have customization features such as: Incissions, custom text on the product, ...
• Orders might need additional addresses on file

Spree found an elegant and easy to implement approach by creating a metadata storrage inside the primary objects.
Metadata on Spree Commerce
We would sponsor or merge this feature upon guidance from the core team.

Desired behavior
Custom values can be added to an order from front-end using a meta data field in major ressources.

[kennyadsl]

I don't have strong opinions here, this seems like a legit feature. Still, the advantage of having this built-in vs implementing it custom when needed is not obvious. If someone needs a custom field in a resource, they could add one migration + store :metadata, coder: JSON in that specific resource's model, without compromising the existing standard behavior in any way.

Am I missing something and it's more complex than this?

[fthobe]

== updated twice ==

I think it's an extremely strong yet simple feature to have this onboard. Most companies just need an identifier (eg PO-Number) and having such field available easily seems low hanging fruit to me compared to Shopify or BigCommerce leveraging the ability to customize beyond what's possible with SAAS.

Especially because it can be made agnostic to preexistent code, it's an addition without removing or complicating anything.

[kennyadsl]

@solidusio/core-team thoughts on this?

[tvdeyen]

I think it could be a useful feature. Using the Rails' data store makes much sense here. We would need to add a minimal UI for it, though, right? Any ideas?

[kennyadsl]

@tvdeyen don't we have something similar for the preferences UI?

[tvdeyen]

@kennyadsl we actually do https://github.com/solidusio/solidus/tree/main/backend/app/views/spree/admin/shared/preference_fields

[fthobe]

Ok, I take a look at it today. It seems to be really low hanging fruit. I would kick out the free text field and go with anything that works with limited length and covers also tokenized links (eg shipping labels, downloads,...).

I would limit the whole thing to:

• string
• integer
• decimal

If you have particular use cases for text I am curios :)

[jarednorman]

Should this be an augmentation of product properties, made more robust and extended to other models?

[fthobe]

I would go on different resources for different reasons:

• Products & Variants: allow customization of products and subscriptions
• Orders: allow po-orders or 3rd system references
• Users: allow references for other systems and custom fields (eg Fiscal ID for Italy, you can't make an invoice without)
• Shipments: allow references
• payments: allow references
  ... I could go on and on

It's very hard not to imagine added value with such an easy way to customize.

[fthobe]

Ok, if everybody is happy we would pick that up next week and see how to port Spree's meta data.

@solidusio/core-team Is anybody particularily upset about anything spree has done in their own implementation?

[tvdeyen]

Ok, if everybody is happy we would pick that up next week and see how to port Spree's meta data.

Thanks

@solidusio/core-team Is anybody particularily upset about anything spree has done in their own implementation?

I don't think there is a value for public vs. private meta data. For public product properties we can use the features we already have.

Also, the linked git commit messages far from ideal. We prefer descriptive commit messages that explain why a change has been made and not a link to a private issue tracker.

[fthobe]

@tvdeyen theoretically it makes sense to separate in public and private:
Public: I want you to leave my package at the door step
Private: "the customer is a stinky noodle" added from an agent to backend

[fthobe]

@tvdeyen I saw there's a lot going on on the backend, can you people integrate that into the work ongoing? I wouldn't like to touch something to throw away next week.

[tvdeyen]

As far as I understood the meta data is mainly for warehousing and accounting things, not meant to be public. For public meta data we have the Product Properties. We would like to keep it as simple as possible.

[kennyadsl]

I think one area of complexity/potential decisions needed is around REST API payload. Should these fields be part of the payload?

[fthobe]

I think one area of complexity/potential decisions needed is around REST API payload. Should these fields be part of the payload?

Hmm I would handle it this way:
meta data can be an array, max 256 characters which should suffice for tokenized urls and any possible string value maximum 8 values per resource to cover 5 UTM parameters and some custom values including one per party and a free value.

As far as I understood the meta data is mainly for warehousing and accounting things, not meant to be public. For public meta data we have the Product Properties. We would like to keep it as simple as possible.

@tvdeyen Can you give an example?

[fthobe]

Hey guys, I would like to introduce you to @shahmayur001 who has worked on spree in the past for american customers mostly doing backend work.

So to break it down:
Array of 8 key value pairs attached to the resources in form of strings with max 256 characters.
Initial Feature as written by spree

To be applied to:
Orders
Line Items
Addresses
Shipments
Payments
Stock Movements / Transfers
Refund

@tvdeyen I would love to give public and private a try and validate according to interface (storefront or pure API) the access.
@kennyadsl @tvdeyen Do you have particular requirements regarding the security of this work?

[fthobe]

I think one area of complexity/potential decisions needed is around REST API payload. Should these fields be part of the payload?

Do you have particular preferences on this point?

[jarednorman]

I don't understand why we'd be storing this data as an array. I think we'd want to model this relationally. I don't care for Spree's approach for a bunch of reasons.

[fthobe]

Hey @jarednorman please be as specific as you can be.

[fthobe]

Regarding an array:

• the idea behind the spree implementation was to create values that can be within boundaries completely determined outside the system allowing more variability.

The great advantage is that any other system no matter what's already there can simply shoot in whatever value. Honestly I find that idea immensely sexy from a technical point of view and way cooler than everything I have seen regarding fields till today. It just needs to be limited and managed.

[fthobe]

Altneratively new table and split up the array and file together with resource id, would that work for you? It just blows up the requests for pretty much anything. Depending on scale I wouldn't suggest that on a large category page.

[kennyadsl]

@fthobe @jarednorman I think we are all on the same page, but using different words.

Array of 8 key value pairs

@fthobe do you mean that you are targeting those 8 resources listed that will have these metadata support?

If I understood correctly, those metadata fields are going to be JSON/JSONB columns (as Spree did), so anyone can add their own content, with the preferred/needed structure.

Related, JSONB is coming to Sqlite soon: https://sqlite.org/draft/jsonb.html

[fthobe]

Hey,
here is what @shahmayur001 and me came up with.

Format: we can either use jsonb or alternatively hash the array, both is fine for me.
SQLite currently does support jsonb (since 3.45.x) as far as I understood, so I would hash it to warrant backwards compatibility, but I am open for suggestions (I honestly strongly believe sqlite support should fade, but that's your call).

Limitations:
I would limit the amount of information to be stored to:
8 key value pairs per array
max 16 characters key lenght
max 256 characters per value
validate the json for correct escapes

Where:
All items that might have to store meta data to support external logic.

• spree_orders: external reference number like PO-Number or Internal ERP Document
• spree_line_items: allow items to be customized for examples or allow subscriptions to mapped to a relevant reference
• spree_shipments: Delivery Notes for example
• spree_payments: reconciliation in erps
• spree_stock_movements: Delivery Notes for example
• spree_addresses: additional fields for customers such as company, first name, last name,...
• spree_refunds: reconciliation items such as bank transfers
• checkouts: marketing parameters such as UTM codes
• products: additional identifiers, separate taxons,...
• promotions: marketing parameters such as UTM codes
• return_authorizations: internal references such as ticket number,....
• customer_returns: internal references such as ticket number,....
• stock_locations
• store_credit_events
• stores
• taxonomies
• taxons
• users
• variants

[jarednorman]

I am at RubyConf this week, so I don't really have the time to fully explain the implementation that I think we should go with, but I'm open to JSONB-backed implementation. I don't think the various limitations on storage are necessary. I will provide an in-depth explanation of one possible implementation path that I would be happy with next week.

[fthobe]

I am at RubyConf this week, so I don't really have the time to fully explain the implementation that I think we should go with, but I'm open to JSONB-backed implementation. I don't think the various limitations on storage are necessary. I will provide an in-depth explanation of one possible implementation path that I would be happy with next week.

I hope rubyconf is fun.

Ok on jsonb.

Are there particular reasons not to limit the model?

[fthobe]

Hey @tvdeyen @jarednorman , before we PR anything, please find with this link an example for feedback, as soon as we have understood better how this works for you we can complete it and make a pull request.

Please indicate if:

• the approach for storing the information in the DB is ok
• the approach to collect the information via API is ok
• if, where and how you would like to limit this functionality

Some Notes

• @kennyadsl noted that if we want public and private metadata appropriate configuration in roles should be present
• @jarednorman agreed with jsonb so we went with that, currently we are validating DB support for jsonb but I looked at the package managers of major distros, SQlite with jsonb support became the default packages everywhere but it should be noted in docs that an update is required

[fthobe]

In addition a suggested PR comment and commit descriptions would be appreciated to get a hang of the whole thing.