Hi @alex01 – it’s great to see your apps’ usage is expanding, and getting interest from external customers!
When you’re creating big “multi-tenant” apps like this, there are two major approaches, and you’ve touched on them both:
1. All in one big database
This is the “default” approach: You can have a table of organisations, and a table of users, and link each user to an organisation. You’re right that you have to be careful here, and make sure that you only expose data to the users of the correct organisation. Possible tools here are restricted views, which have the same API as data tables but only refer to a subset of data (eg “all rows linked to this organisation”). You can also impose your own internal rules, eg “I never use app_tables.foo.search() directly; I always use my own search function that enforces organisation=anvil.server.session['current_organisation'] for every search”.
This approach is most scalable and flexible when you have lots of customer organisations, because creating a new organisation is as simple as adding a row to your database.
2. Separate databases
Alternatively, you can have a separate database for every organisation, to ensure that their data is entirely siloed off. The easiest way is to have one deployment environment for each customer, each pointing to a separate database. Each tenant will log in through a separate URL, but you can point all those environments to a single Git branch so you can release code to them all at once.
This approach offers high certainty that your customers’ data is isolated from other organisations, but it still doesn’t protect you from accidental within-organisation disclosure, so the security of your code is still critical. It is also a bit more cumbersome to set up (a new environment/DB for each customer, with migrations required on updates), so it’s probably more appropriate to products with a smaller number of big-ticket customers. Finally, if you do ever find yourself wanting to build features that link multiple customers, it could be a painful migration. (Slack, for example, started out with a siloed architecture, and therefore had to do a lot of work to implement “shared channels” between organisations.)
Generally, we recommend the “all in one database” approach for most developers, but I hope I’ve set out the pros and cons here to help you make your own informed decision.