In my first article, I shared different strategies for storing settings in a multi-tenant application. I went with an approach where each setting is stored in a separate row in database, and chose to use the Laravel Settings package by Spatie to implement this. This time, I'd like to share which tweak made it work in a single-database multi-tenant context.

Quick recap: how database queries work in single-database tenancy

Single-database multi-tenancy simply means that data for all tenants (a tenant could be a user, account, organization, team, etc) is stored in a single database, and each table has a tenant identifier, such as tentant_id. When fetching data for a specific tenant, the queries need to be scoped to ensure data from other tenants is not leaked.

For example, let's assume we have 5 tenants, and each have multiple invoices stored in our app. When tenant A logs in, we want to fetch their invoices. A naive query like Invoice::all() would mean that we get their invoices, but also all invoices from the other tenants as well. To avoid this, we need to scope the query by tenant ID, for example: Invoice::where('tenant_id', $currentTenant->id)->all().

This ensures that only the invoices that belong to the current tenant are returned. Obviously having to remember to include the `where` scope every time when making a DB query can be both tedious and lead to accidental data leaks, so it's usually a good idea to apply a global query scope as soon as the tenant is identified (or switched).

Scoping all tenant-owned model queries to current tenant

As mentioned above, it's ideal if we can automatically apply the current tenant scope to all models that belong to a tenant. This makes code easier to read, but more importantly, more secure, as it avoids human error if a developer forgets to properly scope a query.

In Sliptree, most models are owned by tenants (companies), and a handful are considered global. For tenant-owned models, I created a BelongsToCompany trait that takes care of a few things:

1. Define the belongsTo relation to the Company model

2. Ensure the company_id is set to current tenant when saving a new model

3. Add a global tenant-aware CompanyOwnedScope to all DB queries

4. Use a CompanyScopedQueryBuilder for the eloquent query builder

Let's break things down in more detail. Here's what the BelongsToCompany trait basically looks like:

namespace App\Domain\Company;

trait BelongsToCompany
{
    // This method is called automatically by Laravel when booting the model
    public static function bootBelongsToCompany(): void
    {
        // add the global qyery scope
        static::addGlobalScope(new CompanyOwnedScope());

        // set the relation to current company when creating the model
        static::creating(static function ($model) {
            if (! $model->company_id && ! $model->relationLoaded('company')) {
                $company = company();

                $model->setAttribute('company_id', $company?->id);
                $model->setRelation('company', $company);
            }
        });
    }

    public function newEloquentBuilder($query): CompanyScopedQueryBuilder
    {
        // use a company-scoped query builder
        return new CompanyScopedQueryBuilder($query);
    }

    public function company(): BelongsTo
    {
        // ensure the current company is returned if the model is not saved yet
        return $this->belongsTo(Company::class)->withDefault(fn () => company());
    }

Here's what the CompanyOwnedScope looks like:

namespace App\Domain\Company\Scopes;

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

class CompanyOwnedScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        // if there's a current company context, restrict queries
        // to the current company (tenant)
        if ($company = company()) {
            $builder->where('company_id', $company->id);
        }
    }
}

And here's what CompanyScopedQueryBuilder does:

namespace App\Domain\Shared\QueryBuilders;

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

class CompanyScopedQueryBuilder extends Builder
{
    public function newModelInstance($attributes = []): Model|static
    {
        // ensure new model instances are created with
        // the company_id applied - if a current company
        // context exists
        return parent::newModelInstance(array_merge([
            'company_id' => company()?->id
        ], $attributes));
    }

    /**
     * Apply company scope to upsert queries
     */
    public function upsert(array $values, $uniqueBy, $update = null): int
    {
        if (company()) {

            // ensure each entry has the company_id set
            $values = collect($values)
                // Note: this order makes it still possible to override
                // the company_id when calling upsert, if needed
                ->map(fn ($entry) => array_merge(
                    ['company_id' => company()->id], $entry
                ))
                ->toArray();

            // make sure the company_id is included in the uniqueBy array (and that it is an array actually)
            // this prevents accidentally updating a record from another company
            $uniqueBy = (array) $uniqueBy;

            array_unshift($uniqueBy, 'company_id');
        }

        return parent::upsert($values, $uniqueBy, $update);
    }
}

Alright, there's quiet a bit of code here, but it's actually quite straightforward. You may have noticed the global company() helper. This is just a function that tries to get the current company (tenant). I find it easier to use this that something like app(Company::class), although this is essentially what's happening under the hood.

As you can see, we are mostly just ensuring that if company() returns something (an instance of Company::class), then we want to set the company_id on new model instance and queries. This will make sure that no data from other companies is leaked.

The tricky part - and where the Laravel Settings package comes in - is when performing upserts. This package performs most DB queries using upserts, for perfomance reasons. However, in case of upserts, regular query scopes are not applied, which is why I'm overriding the upsert method in CompanyScopedQueryBuilder, to ensure each upsert query is also scoped to the current company.

Here's a step-by-step breakdown:

1. It first checks if a company is set using the company() helper function.

2. If a company is set, it ensures that each entry in the $values array (which represents the data to be inserted or updated) has the company_id set. This is done using the collect function to create a collection from the $values array, then mapping over each entry and merging the company_id into it. The result is then converted back to an array.

3. It also ensures that the company_id is included in the $uniqueBy array, which represents the columns that should be unique for the upsert operation. This is done by first ensuring $uniqueBy is an array, then prepending company_id to it using array_unshift.

4. Finally, it calls the parent upsert method with the modified $values and $uniqueBy arrays, and the $update parameter.

This method is particularly useful in multi-tenant applications where each tenant (in this case, company) has its own set of data. It helps prevent accidentally updating a record from another company.

Note that none of the above is specific to Laravel Settings - it's just how we can scope queries for any company-owned models.

Configuring Laravel Settings for single-database tenancy

In order to make Laravel Settings play well with single-database tenancy, we need quiet a few tweaks. Let's go!

First, we need to ensure that the SettingsProperty model belongs to the company. Because we may need to implement settings that are not scoped to companies, it's a good idea to extend the model:

namespace App\Domain\Company\Settings;

use App\Domain\Company\BelongsToCompany;
use Spatie\LaravelSettings\Models\SettingsProperty;

class CompanySettingsProperty extends SettingsProperty
{
    // using a custom table name for company-scoped settings
    protected $table = 'company_settings';

    // using the trait from above ;)
    use BelongsToCompany;
}

Next, we need to let Laravel Settings know that we want to use a custom model, in config/settings.php, update the repositories config with company-scoped settings:

/*
 * Settings will be stored and loaded from these repositories.
 */
'repositories' => ['database' => [
        'type' => \Spatie\LaravelSettings\SettingsRepositories\DatabaseSettingsRepository::class,
        'model' => CompanySettingsProperty::class, // using this model should ensure all queries are scoped to company
        'table' => 'company_settings',
        'connection' => null,
    ],
    // .. other settings repos here, for example, for the landlord...
],

But wait - we also need to ensure company_settings tabel exists. For this, we can modify the migration that Laravel Settings creates:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('company_settings', function (Blueprint $table): void {
            $table->id();

            $table->foreignId('company_id')
                ->constrained()
                ->cascadeOnDelete();

            $table->string('group');
            $table->string('name');
            $table->boolean('locked')->default(false);
            $table->json('payload');

            $table->timestamps();

            $table->unique(['company_id', 'group', 'name']);
        });
    }

    public function down()
    {
        Schema::dropIfExists('company_settings');
    }
};

The only difference between the default Laravel Settings migration and this one is that we now have a foreign company_id in the table, and the unique index includes the company_id. This ensures that multiple companies can have settings with the same names and groups.

Finally, to perform any company-scoped migrations for the tenants, we can use a custom migrator:

namespace App\Domain\Company\Settings;

use App\Domain\Company\Models\Company;
use App\Providers\AppServiceProvider;
use Spatie\LaravelSettings\Migrations\SettingsMigrator;

/**
 * @see AppServiceProvider::boot()
 */
class CompanyAwareSettingsMigrator extends SettingsMigrator
{
    /**
     * Set current global company scope/context in the migration.
     *
     * This allows us to migrate settings for multiple companies.
     */
    public function forCompany(Company|int $company, bool $load = false): static
    {
        if (! $company instanceof Company) {
            // Normally we don't want to load the company model, so we create an empty
            // model instance with the ID, instead. This should help keep migrations
            // faster.
            $company = ! $load ? new Company(['id' => $company]) : Company::findOrFail($company);
        }

        $company->makeCurrent();

        return $this;
    }
}

The CompanyAwareSettingsMigrator class is a custom class that extends the SettingsMigrator class from the Spatie Laravel Settings package. This class is used to handle the migration of settings in a multi-tenant application where each tenant is represented by a Company.

Here's a breakdown of the class:

• The forCompany method is used to set the current global company scope/context in the migration. This allows the application to migrate settings for multiple companies. The method accepts two parameters: $company and $load.

  • $company can be an instance of the Company model or an integer representing the company's ID.

  • $load is a boolean that determines whether to load the Company model or not. If $load is false, an empty Company model instance is created with the provided ID. If $load is true, the Company model is loaded from the database using the findOrFail method. This is done to keep migrations faster by avoiding unnecessary database queries.

  • After determining the Company instance, the makeCurrent method is called on it. This method sets the current tenant context (which the company() helper returns).

  • The method then returns the current instance ($this) to allow for method chaining.

More importantly, here's how this class can be used to migrate tenat settings. First, in AppServiceProvider::boot method, add the following:

$this->app->bind(
    SettingsMigrator::class,
    CompanyAwareSettingsMigrator::class
);

Then, here's how we can use it in a migration:

use Spatie\LaravelSettings\Migrations\SettingsMigration;
use App\Domain\Company\Models\Company;

class CreateGeneralSettings extends SettingsMigration
{
    public function up(): void
    {
        // for each company in the database, create general settings
        Company::select(['id', 'name'])->get()->each(function(Company $company) {
            // ensures that the following lines use company_id scope 
            $this->migrator->forCompany($company); 

            $this->migrator->add('general.site_name', $company->name);
            $this->migrator->add('general.site_active', true);
        });

        company()?->forgetCurrent(); // forget company context when done
    }
}

Phew! That was a mouthful! In the end, it's not too complex, but it can seem daunting at first.

The main takeaway is that when working with single-database tenancy, one has to be extra careful to ensure the correct scopes are applied at all times. It can make application code more complex, but dev-ops wise, it's simpler than a multi-database approach.