Storing settings in multi-tenant applications

Background

I have a pragmatic approach when making architectural decisions for code: start with whatever is easiest and refactor into more complex options when you need to.

As I rebuild Sliptree on Laravel, I occasionally find that some of the initial architectural choices hit a wall. This was the case for how I implemented settings in the application.

For background, Sliptree is a single-database multi-tenant application - a tenant being a company. Each user can have access to multiple companies, and companies may have multiple users of various roles.

Each company should be customisable with things like language, timezone, invoice numbers, due dates, etc.

The number of different settings initially seemed pretty static (around 20), until I started implementing integrations for sending e-invoices. Such an integration requires storing various settings - such as whether it's enabled, the date when it was enabled, api keys, etc. Also, more settings for each integration may need to be added over time, which means now my list of possible settings is pretty dynamic.

Before I dive into what sort of roadblock I hit with my initial implementation, let's discuss various strategies for storing settings in a multi-tenant application. Oh, and while I'm using Laravel, the strategies can be applied to other tech stacks and frameworks.

Strategies for Storing Tenant Settings

There are at least 3 different options for storing tenant settings in the database:

• JSON-based settings (a settings column with JSON type on Tenant model/table)

• Column-based settings (a column for each setting in a settings table)

• Row-based settings (each row represents a single setting)

Let's explore each option in more detail below. Note that the examples are abbreviated, and do not represent a full implementation - but should be enough to give an idea of each approach.

JSON-based settings column

In this approach, we store all settings in a single JSON column in the Tenant model. It's very easy to get started with.

Example:

// Add a JSON column in your Tenant table migration
$table->json('settings')->nullable();

// Tenant model
class Tenant extends Model
{
    protected $casts = ['settings' => 'json'];

    // Function to update settings
    public function updateSettings($settings)
    {
        $this->settings = array_merge($this->settings ?? [], $settings);
        $this->save();
    }
}

// Usage
$tenant->updateSettings(['language' => 'en', 'timezone' => 'UTC']);

// Fetching settings
$tenant->settings['timezone'];

Pros:

• Very easy to set up

• Super flexible – adding new settings is a breeze.

• No need for schema changes when introducing new settings.

• Great for a dynamic list of settings.

Cons:

• Can be slower for large amounts of data.

• More complex queries to dig into JSON fields.

• No automatic typecasting for individual properties in the JSON/array structure

Column-based Settings

This strategy involves creating a separate column for each setting in a dedicated settings table. It's straightforward, especially when you have a known, limited set of settings. It also feels familiar, because it's just another model.

Example:

// Migration for a settings table
Schema::create('settings', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('tenant_id');
    $table->string('timezone')->default('UTC');
    $table->string('language')->default('en');
    // Add more columns as needed
    $table->timestamps();
});

// The Settings model
class Settings extends Model
{
    protected $fillable = ['tenant_id', 'timezone', 'language', ...];

    public function tenant() {
        $this->belongsTo(Tenant::class);
    }
}

// The Tenant model
class Tenant extends Model {

    public function settings() {
        $this->hasOne(Settings::class);
    }
}

// Updating
$settings = $tenant->settings->update(['tenant_id' => $tenantId, 'timezone' => 'UTC', 'language' => 'en']);

// Fetching settings
$tenant->settings->timezone;

Pros:

• Simple to implement and understand.

• Efficient for a small, static number of settings.

• Each setting can be automatically cast into a proper type, such as an Enum, a Carbon instance, etc

Cons:

• Requires schema updates for new settings.

• Can get unwieldy with many settings.

• Less flexible for dynamic setting requirements.

Row-based Settings

Here, each setting is stored as a separate row in a settings table. This is ideal when you expect a lot of different settings that may change over time.

Example:

// Migration for settings table
Schema::create('settings', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('tenant_id');
    $table->string('key');
    $table->text('value');
    $table->timestamps();
});

// Settings model
class Setting extends Model
{
    protected $fillable = ['tenant_id', 'key', 'value'];
}

// Usage
Setting::create(['tenant_id' => $tenantId, 'key' => 'language', 'value' => 'en']);

Pros:

• Extremely flexible and scalable.

• Easy to add or change settings.

• Simplifies queries for individual settings.

Cons:

• Might lead to a large number of rows.

• Potentially more complex data retrieval.

• More joins in queries.

My initial strategy and why it didn't pan out

I chose the column-based, settings model approach when I first implemented settings in my application. This is because it seemed like a good middle ground between JSON and row-based settings in terms of complexity, and it allowed me to treat settings like any other model, which seemed to reduce cognitive load.

Life was easy: when I needed to grab the settings for the current tenant (company), I could simply fetch it via the relation:

$company->settings; // I have all.the.settings!

Shortcomings of column-based settings

Once I started adding integrations with other platforms, I realized that probably not every company is going to set up all the integrations. Furthermore, I cannot realistically foresee all the different configuration options future integrations may require.

Also, the settings seemed to lack organization and isolation. By this I mean that some settings seemed to belong together, and were only required in a certain context or part of the app, and I did not want to load all the settings all the time just to get a few values.

For example, we allow customizing the invoice and estimate number formats. Essentially, we have a few settings to control various aspects, such as: counter, number padding, placeholders, automatic reset sequence.

With the column-based settings approach, those are represented something like this:

// Migration for a settings table
Schema::create('settings', function (Blueprint $table) {
    // other fields omitted for brevity...
    $table->unsignedBigInteger('invoice_counter')->default(1);
    $table->string('invoice_number_format')->default('{D}-{N}');
    $table->string('invoice_number_padding')->default('{D}-{N}');
    $table->string('invoice_number_reset')->default('never');
    $table->unsignedBigInteger('estimate_counter')->default(1);
    $table->string('estimate_number_format')->default('{D}-{N}');
    $table->string('estimate_number_padding')->default('{D}-{N}');
    $table->string('estimate_number_reset')->default('never');
});

There's quite a bit of repetition, and when I fetch $company->settings on a screen where I don't care about invoice or estimate number formatting, I still get them as part of the "bundle".

Of course, I could just SELECT the columns I care about, but some settings need to be always loaded in the app (such as locale), so I'd need to hack my way around Laravel to provide "default columns for select", which is possible, but I didn't want to take that route.

JSON-based or row-based settings?

I was ultimately looking for 3 things: flexibility, organization, performance.

While a JSON-based column seems very easy, it has its drawbacks in that I would need to manually cast and transform settings values as they are saved or read from the database.

Also, the JSON-based approach couples settings tightly with the model. The settings don't have an entity (like a class or model) which represents them - everything is piggybacking on the parent model (Tenant/Company) in my case. This can lead to the parent model becoming a God-object, which I definitely want to avoid.

Exploring row-based settings

Alas, I started looking at the the 3rd option - row-based settings, which at first seemed like an overkill.

After all, database queries will be more complex, especially in the context of a multi-tenant application. Also, since each single setting will be a model in itself, there can't be a single model to represent a collection of all the different settings, so it requires a paradigm shift in how I think about settings.

However, row-based settings do bring a lot of flexibility and a good separation of concerns.

It's not even strictly necessary to have database migrations when new settings are introduced, because a new setting is just a row in the existing database structure - no schema changes are required. In the context of multi-tenant applications, where each tenant may have different sets of settings, this can be hugely beneficial.

Aside from that, it would be nice if we had something similar to a Laravel Model, or perhaps a Repository, which would represent all the settings. Here's what I mean:

// I want to do this:
$settings = Settings::get(['tenant_id' => $currentTenant->id]);
$settings->timezone;

// Instead of this:
$timezone = Setting::where([
    'tenant_id' => $currentTenant->id, 
    'key' => 'timezone'
]);

One (perhaps obvious) thing to consider is that the application code has no knowledge of the different settings that are available, because there is no schema or types to define them. This can become problematic, because it will be easy to make mistakes. For example, questions such as those below may arise:

• Was the setting called api_id or api_key?

• Was the ID an integer or a string?

• Which format should we store dates in settings?

There's nothing in the codebase to act as a source of truth to answer those questions, unless we add one.

It would be nice if we could have some sort of class that will define which settings are available and what their types should be. Something like this:

class GoogleIntegrationSettings {
   public ?string $clientId = null;
   public ?string $clientSecret = null;
   public ?Carbon $activatedAt = null;

   public static function load() {
      // load all settings from DB here...
   }
}

// use the settings
$googleIntegrationSettings = GoogleIntegrationSettings::load();
$googleIntegrationSettings->activatedAt?->format('Y-m-d');

This would solve my concerns above, but there are a few more things that I would like to achieve:

• Grouping settings, ie for each different integration, or by some domain (invoice settings, estimate settings, billing settings, etc).

• Caching - I wouldn't want to run DB queries all over the place

• Probably more things that I didn't even think about...

Building all of the above is a substantial amount of work, and in the spirit of avoiding re-inventing the wheel, I looked for existing solutions. Naturally, I turned to Spatie, and they have an excellent package for managing settings in Laravel, called... drum roll... spatie/laravel-settings.

It does all of the above (and much more), so I ended up using their package to refactor from column-based settings to row-based settings.

Some of the selling points for me were:

• it allows defining strongly-typed settings, which is useful to avoid all sorts of type-related bugs (and serves a similar purpose to attribute casts in Laravel models).

• it has built-in support for grouping settings

• it's well maintained (because Spatie)

There are some things that this package does not support out of the box, though:

• Multi-tenant applications

• Being able to add new settings without adding migrations

It doesn't mean these things are not possible, it just needs some extra work. Stay tuned for the next post to see how I ended up implementing spatie/laravel-settings in a multi-tenant application, working around these limitations.

Conclusion

To recap, I initially went for a column-based settings approach in Sliptree, valuing its simplicity. However, as the need for more dynamic settings emerged, especially with e-invoice integrations, this approach's limitations became apparent.I then evaluated JSON-based and row-based options, seeking flexibility and scalability.

My choice ultimately settled on Spatie's Laravel settings package for its strong typing and settings grouping. This package, while excellent, does need some tweaks for multi-tenant applications and dynamic setting additions, which I plan to explore in my next post.