I’m assuming that clients don’t have access to the cake server, so credentials are secure… for instance, db and other passwords are stored in app.php but that’s fine because that file is not accessible by users. I also assume the schema of all client dbs are the same, only the data differs.
Store each client’s db info and credentials in the app database. At startup (say, Controller.initialize or Controller.startup), read the client’s db information from the app db and create a “clientdb” connection using ConnectionManager::config(). This will be accessible globally. App db will be available via “default” connection and client db via “clientdb” connection.
In each table definition (in the Model/Table/ENTITYTable.php file), add
public static function defaultConnectionName() {return "clientdb";}
Then Cake will know to go to the client database when accessing that table. Pretty neat.
Now that I think about it, that may even allow cross-db queries, not sure if Cake is smart enough to do that although it has enough information…
As for subusers - either store the client db credentials in the subuser’s record in appdb, or have some sort of pointing scheme. It’s basically on you. But the point is that any subuser has access to the main user’s db info and credentials and uses them to create the clientdb connection on startup.
As for scalability, AFAIK Cake doesn’t cache db connections between calls, so you’re OK there. If you did somehow implement db connection caching, the cache would have to be smart enough to handle differing db’s across calls by looking at the db details not the connection name. And manage clientdb connections with some sort of FIFO cache if there were theoretically millions of possible db’s.
This is for Cakephp 3.x, not sure if anything has changed in 4 which would affect the above.