Has anyone here used SQLite on a web app with database file per tenant or even lower granularity? How fast does the attach work? Can you do transactions over multiple databases as well?
I think that would make it hard to run queries across the whole dataset. You'll end up re-implementing features that are provided for free by proper rdbms like Postgres.
That might be the goal. If you are storing private data for your users which should not be queried. You can have separate encryption keys for each user and keep their data on separate databases.
We did pretty much that ages ago when I built a webmail provider.
Marketing hated it, because it meant collecting user data took conscious effort which meant they had to ask the dev team, which meant requests were often turned down when hard questions were asked about whether it was justified to collate personal information.
That was a feature, from my perspective.
We actually took most user demographics data entirely offline. Data that was only intended to create aggregates anonymised profiles of our users were kept encrypted in bank box when not being analysed. Any analysis on it was done on an airgapped machine, and only the anonymised reports taken out.
The only central database that was online was one that kept a mapping of whether or not a given user name was available.
Then each storage shard kept track of which users were on which backend, and the account data that had to be online for each user (primarily their actual mail since we were a mail provider, along with settings etc. for their mailbox) were kept on a per user basis.
It worked well, but you need to have buyin from the top for this approach, as there will be constant pressure for easing access to more and more user data.
First, use advances in privacy technology to create a service-wide data warehouse that has enough information to help you make good decisions without exposing any specific user’s data. Done properly, users will benefit from your improved decision-making without giving up their personal data. Differential Privacy can do this.
Second, give users the opportunity to download their own little database in native format (e.g. SQLite) This is the ultimate in data portability. I think Dolt [0] might be good for this, because its git-like approach gives you push/pull syncing as well as diffing. That would make it easy for users to keep a local copy of the data up to date.
Third, you can start to support self-hosting and perhaps even open-source the primary user-facing application. The hosted service sells convenience and features enabled by the privacy-respecting data warehouse.
The big questions, of course, are many:
- Would users pay for this?
- Does increased development cost and reduced velocity outweigh the privacy benefits?
- Would the open-source component enable clones that undermine your business, or attract new users who may eventually upgrade to your paid service?
I would like to find out the answers!