Migrate an application from mono-user to multi-user configuration

Hello V,

Migrating your Laravel application from a mono-user to a multi-user configuration is a common requirement as applications grow and evolve. Both of the solutions you've considered have their own pros and cons. Let's discuss them briefly and then I'll suggest a path forward.

Create a new database for the new user

This approach is often referred to as a multi-database or multi-tenant architecture. It's great for data isolation and can be beneficial if you expect to scale to a large number of users, each with significant amounts of data. However, it does add complexity to your application, especially when it comes to database migrations, connection management, and potentially increased infrastructure costs.

Keep a unique database

Adding an organisation_id to each table and using model scopes is a more traditional approach to multi-tenancy within a single database. It's simpler to manage in terms of infrastructure and can be more cost-effective. However, it requires careful attention to ensure data isolation and security, as all data is stored in the same database.

Suggested Solution

Given the information provided, I would suggest starting with the single database approach. This is because it's generally easier to implement and manage, especially if you're only adding a small number of users. Here's how you can proceed:

1. Add organisation_id to your tables: You'll need to modify your database schema to include an organisation_id column on all tables that contain user-specific data.

2. Update your models: Add a global scope to your models to automatically filter queries by the current user's organisation_id.

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class YourModel extends Model
{
    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('organisation', function (Builder $builder) {
            $builder->where('organisation_id', auth()->user()->organisation_id);
        });
    }
}

1. Update your foreign keys: Ensure that your foreign keys and relationships are updated to reflect the multi-user setup.

2. Migrate existing data: You'll need to assign an organisation_id to all existing data to associate it with the current user.

3. Update your application logic: Go through your application logic to ensure that all data creation and modification actions are scoped to the current user's organisation_id.

4. Implement robust access controls: Make sure that your application has proper authentication and authorization checks in place to prevent users from accessing data that doesn't belong to their organization.

5. Test thoroughly: Before deploying these changes, make sure to thoroughly test your application to ensure that the data isolation is working as expected and that there are no security holes.

This approach will allow you to transition to a multi-user setup with minimal changes to your infrastructure and without the overhead of managing multiple databases. If, in the future, you find that this setup is not scalable enough or you need stronger data isolation, you can consider moving to a multi-database architecture.

Remember to backup your database before making any schema changes and to test your changes in a staging environment before deploying to production.

Good luck with your migration!