Is an immutable data model possible in AppSheet?

[takuya_miyai]

A project team adopting DDD asked me if an immutable data model is possible with AppSheet.

I agree with the high ideals of the immutable data model, but I think it is very difficult to proceed with actual application development based on the immutable data model.

In addition, I responded that it is not realistic to try to achieve this in AppSheet because it would require application development based on the following restrictions.

• Need to prohibit all Update and Delete at the table level
• It would naturally also prohibit App formula, Reset on edit and more.
• Slice needed for all tables to represent the most recent table statement
• Conditioning to achieve the above would be a complex Expression.

However, I am also thinking that perhaps an AppSheet app could be created that employs an immutable data model.
If such an app exists, I would be very interested to know the implementation policy and would be glad to hear about it.

Reference links for immutable data model

https://vertabelo.com/blog/immutable-data-in-database-tip/

https://terminusdb.com/blog/9-reasons-why-you-need-an-immutable-database/

https://www.tibco.com/reference-center/what-is-immutable-data

[1minManager]

I think you can achieve what you want in Appsheet to limit what users do. But there are no restrictions if someone has access to the App Definition or the data source.

Simon@1minManager.com

[Steve]

I think applying an immutable data model with AppSheet is possible.

[SkrOYC]

Is this the idea that everything we do leaves a history behind it? I like it, but I’m new to the actual concept.

Sounds doable anyway.

As @1minManager said, you have to make sure your data is not accesible from outside AppSheet, which should not be dificult if you are using SQL, for example.

---

We use Microsoft Services so I have a Sharepoint site that no one else can access where the data of our apps is saved. I guess there is something similar on GSuite

[takuya_miyai]

@1minManager @SkrOYC @Steve

I am pleased to hear back comments from you experienced folks.?
It is true that AppSheet can use data sources that are easily accessible, so we need to take that into consideration.

And I too think the idea is very interesting regarding the immutable data model. If we can eliminate Update and Delete, the system can be more robust.

And I have partially implemented something similar in AppSheet. (For example, when there are critical columns that need to be fully tracked)

However, I am wondering if it is really feasible to adopt an immutable data model across systems and applications.

For example, consider a table that manages Contact. This is the concept of a resource table in the immutable data model. Anything that might change would need to be carved out into an associated event table.

First, the phone number may change, so let’s create an Update tel table.
Second, the address may also change, so create an Update address table.
Third, perhaps the name may also change, so create an Update Name table.
…
Only PK is left in the contact table…

To further define the state of the latest Contact, we need to collect only the information of the latest event using the Slice or Security filter in AppSheet.
For example, the Tel column would look like this in VC?

ANY(SELECT(Update tel[Value], [LastmodifiedDatetime]=MAX(Update tel[LastmodifiedDatetime]))

Perhaps my thinking is too principled, but I feel that it would be very difficult to create an application that employs such a fully immutable data model.

At the very least, I feel that NoSQL is the data model that is needed, not the RDB world.

This problem is something of a thought experiment for me, so I don’t expect the right answer to be easy to find.
I am just glad to have your comments.

[SkrOYC]

I mean, I would prevent any usage of VC to be honest.
We can fire actions on form save to put that value whetever we want, which seems like a better idea.

Another way would be to go the other way around and use log tables, so any data change will leave it’s mark in history

[SkrOYC]

Heck! You could even trick the user to think they are editing a row when actually they are adding a new one to another table. That would work IMO.

We need to get a custom action that overrides the “Edit” one and opens a LINKTOFORM() to an identical table and the initial values of that table are the fields of the current row. Then on Form Save just the values that are different are placed to the original table leaving a record with the previous and new fields on a log table

[takuya_miyai]

Yes, I agree.
And the method of logging by data changes is actually achievable with a spreadsheet.
This could be called an immutable data model.

It would be interesting to have this changelog accessible from AppSheet.

[SkrOYC]

Yes, although I’m not completely sure how accurate and detailed a spreadsheet history is.

I could even make a Sample App to test my hypothesis about a seamless from UX POV inmutable behaviour