I’m trying to understand whether using Grist as a primary datastore (for a website/app) is a good idea.
A few specific questions:
Is this an intended use case or is it something that could get my account flagged/banned?
How many records is too many (per table) (I know a hard answer on this likely isn’t possible and it depends on record sizes… I’m just looking for possible ballpark upper limits)?
How does Grist handle conflicting writes?
Is it possible to set a TTL on records (which would be useful for things like authentication codes)?
What database does Grist use “under the hood”?
What pros and cons are there to consider between using Grist in this way vs something like FaunaDB?
Using Grist as a primary datastore for a website/app will not by itself get you flagged/banned. Bad scenarios that lead to problems are when a creator makes a site/app, then leaves it unmonitored. Eventually something starts to give, and we need to take action. Actions we take include communicating with the owner of the document, or quarantining the document to limit its impact on others while continuing to provide service.
Writes to a Grist document are serialized. If two requests attempt to change one cell, then one request will be honored first, and then the other will overwrite it. Not sure if that answers your question - let me know if you have a different kind of conflict in mind.
There isn’t a specific TTL feature on records. If you were willing to do it manually, you could periodically filter records and delete them in bulk. That could also be automated via scripting. But I definitely see the value of an easy-to-use feature here.
As you found, individual Grist documents are stored as individual SQLite databases.
For pros and cons, I’d say Grist isn’t optimized for your use case (yet) so there’d be a lot to be said for a service tailored to you. Pay particular attention to our API limits Limits - Grist Help Center. If you have a busy site, api calls could start returning a “try again later” code to some users, which isn’t great. Also, our API also doesn’t yet offer authentication mechanisms tailored to user from a browser.
This sounds like Grist has an inherent race condition problem. If two users are looking at the same document and both change the same record at almost the same time, they won’t realize that the data they are changing might have been changed a split second before by the other user. (I suppose this is true of any other shared-document application.)
Does Grist push changes to documents that users are looking at? Let’s say that I opened the Inventory table, and talking to a customer on the phone. I’ve had the Inventory page open for five minutes. The customer wants to buy 10 Illudium Q-36 Explosive Space Modulators. I see that we have 15 in stock, so I tell him that I can take the order. What I don’t know is that just one minute before, Bob sold 10 Illudium Q-36 Explosive Space Modulators to a different customer. That means I can’t fulfill the whole order. Does Grist push the updated inventory to my view of the Inventory page so I know that we have 5 in stock instead of 15?