Is there a built in Collection changelog?

Ask the community
1

I looked around, but couldn’t find anything in the docs/APIs/dashboards/Google search. Does Wix keep track of changes to a Collection in any kind of changelog that is accessible to me?

It’s cool if it doesn’t. But I thought I’d ask first, before writing my own.

FWIW, the person I’m creating this site for is not hugely computer literate. She needs to make changes to the database, like adding new products, and changing existing ones. I know she is going to inadvertently make mistakes and want to revert back to a previous version of the row. I hope to make this easy and painless for her by allowing her to pick a past version of the row from a change history. Or ask me to revert to a previous version if there’s a changelog I (as site Admin) have access to.

Thanks in advance
Tracey

2

No, Wix data does not have a built in change log like some other db’s do, so you will have to roll your own.

3

@polkaset one way of doing this is to create an event sourcing system . Something goes wrong, dumb the DB and replay all events till you get back to your safe point

Another way is document versioning: you never delete/modify a document but always insert a new version of it. That means you’ll have to work with your own id system and won’t be able to rely on datasets for that (since one document will have several versions so multiple _id). You’ll also have to code a clever query system so only the last version of each item is returned.

In both cases, you should prevent any write operation on the collection from the content manager to prevent your client from breaking the history system.

Either you use those techniques either you use another one (please share feedback :slight_smile: ), this will be a big endeavor and I’m wondering if the extra work really balances the inconvenience?

I personally used the events sourcing technique for a project that required fine auditing (who did what and when) This was worth the work because the feature was critical for the business.

In my opinion, you are better off building some nice dashboard for your client so she won’t be able to mess up the records (just tell her to never go in the content manager).

let me know if you have any questions and please share your end result:)

PS: I just thought of another way that might be simpler to implement (mix of both techniques). For each collection, create after hooks (insert, modify, delete). The hooks copy the previous version of a document into an “archive collection”

Your archive collection has columns “collection”, “documentId”(original _id from the main collection), “version”(auto incremented) and document (content of the main document), action (insert, update, delete).

Therefore you’ll have a trace of all versions of all documents. and you could restore any past version of each document

This will slow down a bit write operations but you won’t have any problem adapting your current project. You’ll only need a clever script to reverse a collection from your archive

PPS: that last technique won’t work with multi-reference fields as they are not provided in afterhooks

4

Hi, Kentin,

Thank you for those strategies. In the past, I have used both event sourcing and versioning. The versioning was for a system where it was critical for legal reasons to track and store every change, when it was made, who made it, and for what reason. Those are both way more heavy weight than I need here. I was planning on a pretty basic solution of writing a change log to another table whenever my client updated anything in the database. Basically just flatten the row and write out strings/numbers/arrays/objects to a new table. Or probably I’ll just write one big JSON blob and be done with it. The most likely scenario is that she’ll do something…make a mistake…and then email me saying “help help! I messed something up!” and I’ll go into the change history and restore what she wants. If she does this habitually, I’ll make her a UI so she can skip the “help help!” email and restore the change without my intervention. This is a small operation. I don’t need to restore changes for thousands of users on a big enterprise system. It’s one store owner who is a friend of mine. I don’t anticipate this will happen often, but I want to plan for it because I know it eventually will happen.

You’re right, I could do this in an after insert/update hook, but…as you mentioned…multirefs…don’t even get me started on multirefs. Too late…you got me started…Why…oh why…are they so crucial, yet so utterly dysfunctional all at the same time? Without them, there is NO other way of modeling m:n relationships. Yes, I have multirefs. I use them to enforce specific values in columns (basically, enums. ie, if I have a column for “color”, I have a reference table with Red, Yellow, etc. I don’t want someone typing in “Yabba Dabba Doo!” for color).

Oh…you got me started on multirefs, and it is actually is pertinent to the solution I’m implementing for the change log.

First. I don’t allow anyone to make changes through the content manager other than me. And even I don’t really use it. My client is not a site admin. Instead, I have created web UIs that allow her to update the database.

Second. I had to “solve” (polite for “hack”) a solution for the invisible multiref update problem for another purpose, so I will re-use the same strategy here. Since I only allow updates through a single UI, I can actually be my own “after” hook. After the save, I just run whatever I want. Since I control the code that is run in the event handlers, I can run whatever I want, even if only a multiref has changed. Right now, whenever she hits the save button, I save the object, and then call my own reindex function that writes the whole record out to a separate table that is in a better format for searching and displaying search results. While I’m doing that, I’ll also write it out to a third table that is the change log. I’ll delete old rows in the change log with a cron job. I don’t need to keep anything older than a couple of months.

Why am I doing this? It requires some back story. I have a main table that has a pile of multirefs. I want to be able to search/filter the main table on various columns/attributes. Filtering on multirefs is actually very easy. But then I need to display the results in a repeater, and this is quite literally impossible if some of those columns are multirefs. To get around this, I have a separate Search Index table that contains all the searchable fields from the main table, and all of its reference and multiref fields (the IDs) flattened out in arrays. The problem I had was how to update that table? I could not use hooks, because updating multirefs is…as you mentioned…invisible. Even if you update another column AND a multiref, afterUpdate runs before the multiref is updated, so if you have code in the afterUpdate that writes out the multiref, you’re writing out the old value. Oh…don’t get me going…too late…

I call the indexer after a save, and not by using a hook. Basically, my click handler is my hook. I also have a cron job set up as a sweeper, just in case I miss an update, or I update something using the content manager.

I have a “wish list” request to add 2 changes that would fix multirefs and make them useful. 1. Give me a hook that triggers on an insert/replace/remove reference. 2. Return the IDs in the multiref column as an array when you query the main table similar to how single ref IDs are already returned. Just the IDs…not the entire referenced row. All I need to make life happy are the IDs in an array.

The addition of these two things would allow me to actually use the only mechanism Wix provides to model m:n relationships.

OK. I think I over-answered this…

Cheers

5

@polkaset I know your pain all to well :stuck_out_tongue: and I fully agree on your change request

One way around this is to use an array of id instead of mutli-reference field. Most query will work the same way but you’ll have a bit of extra work to do if you need to retrieve all referenced items.

Usually, my referenced set is smaller than the main data. So I query them all along side the main data and build the full item in the frontend.
This means hooks available but I cannot use dataset anymore (which I rarely do anyway)

Another trick is to use an intermediary collection with 2 single referenced field. Like you would do with a join table in SQL. This is useful when you have a high number of reference (> 50) so you can retrieve everything at once