SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit.
For a somewhat trained audience however it can be quite interesting for some specific problem domain ...
I think you are conflating the file with the workflow. A proper UI is the solution to making something not "terrible for end users to edit".
With the original solution, you have an autocontained file that virtually any user knows how to edit, structure, expand, version, email, compare, discuss...
The level of user knowledge that you need for a similar solution based on a standalone SQLite file that you can version is another different world, e.g. to relate two values you would need to perhaps create a view or a trigger. And even with the most knowledgeable user you would still lack functionality such as simply pasting an image as a means of documentation and be able to see it, or WYSIWYG colors.
Gonna have to disagree with you here. Very few people know how to version, compare, let alone if there are multiple collaborators, Excel files. Is there even a decent way of doing that outside of Excel Online / Google Sheets?
Yes, that's horrible. But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file.
Same with version control: you just add _v17_20230909_final to the name. Yes, it's horrible. Yes, it's buggy. But yes, it also runs the world.
Are you trying to suggest that either you can't do that with sqlite, or that people didn't require training to do this in Excel?
sofixa: version and comparison is not really supported in Excel
harperlee: agree that software support is not there, but in practice average user knows how to do it and is quite comfortable doing it in Excel
btreecat: sqlite also has filenames and you can learn how to use it
harperlee: agree on the filename, Excel files and sqlite files are externally, opaquely versionable in the same way. the point about people learning is moot though, average user already knows Excel because they were forced to in the past, but does not have the time to learn new things.
SQLite is also an auto contained file. There are similar tabular GUI tools that could let you interact with sqlite using a similar workflow. Users knowing that thing is not inherent, they had to be trained on it, and they can be retrained as they will be for other workflows and business tools.
Remember, you still need an external app (Excel) to open it's files, the files themselves are just data, exactly like sqlite. So you could just make an excel plugin to interface with SQLite.
SQLite is as version-able as an excel file, as in not very with standard tools.
Why are you pasting images into excel? Doesn't matter, sqlite handles that fine actually. https://www.sqlite.org/fasterthanfs.html
The main argument against sqlite is that you would need to build an interface or figure out how to train folks on existing tools. That's not a huge argument against it in my experience, it's a strong social/political one in many orgs but rarely a technical issue.
The design of a solution needs to look into way more requirements than just the technical ones, time and money being 2 big ones. I think most of the HN readers would agree that you could end up building an interface with most of the Excel functionality, even more perhaps, on top of sqlite, and have your particular group of users trained on it.
I think ideally you'd want multiple backends, either SQLite, or flat files that are Git/SyncThing friendly.
I was even thinking the file format could have a file UUID and record timestamps, so that if you put a different version of the same file in the same folder(Like with a SyncThing conflict file) it would give you a merged view, with newest-record-wins logic.
Formulas I think would be the easy part. Just write it all in Python and use one of the many Excel compatible formulas implementations, and just make sure all changes went through the app.
Maybe you could even have a REST API to build other things on top of it.
The web frontend wouldn't be too hard, it could just be a Vue3 app with an HTML table element.
Then you could have a cell type who's value was a query, which would embed a DB browser list widget in the cell, and that cell type could have the option to bind its selected row to another cell.
Like VB+Excel+Access+My fork of Freeboard with inputs in one!
I’d assume the issue will be that an sql table is not free form, you can’t randomly decide to write in some other cell.
Imagine if you said everything with SQLite instead of Excel, and all of a sudden your just talking about structured config in a database. Not new, not crazy, and generally a decent practice.