Multi-Tenancy in Laravel with PostgreSQL Schemas: A Migration Story

Introduction

Row-level filtering is common for multi-tenancy, but what if you need stronger isolation? At Reimpact's platform, we're migrating towards PostgreSQL schemas to achieve true tenant separation, leveraging Laravel, Filament, and the middleware pattern.

This post details our approach to dynamically setting the PostgreSQL search_path for each tenant, ensuring data isolation and simplifying queries.

The Approach: Tenant Schema Middleware

Our core strategy involves a custom middleware that activates the appropriate database schema based on the current tenant. This middleware sets the PostgreSQL search_path at the beginning of each request, directing all database operations to the tenant's isolated schema. We are leveraging Filament for our admin panels.

<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Support\Facades\DB;
use App\Services\TenantContext;

class SetTenantSchemaMiddleware
{
    public function handle($request, Closure $next)
    {
        $tenant = $request->route('tenant'); // Or however you identify your tenant

        if ($tenant) {
            TenantContext::activate($tenant->schema_name);
            DB::statement('SET search_path = ?', [$tenant->schema_name]);
        }

        return $next($request);
    }
}

This middleware intercepts incoming requests, determines the tenant (in this example from the route parameters), and sets the PostgreSQL search_path accordingly. The TenantContext service provides methods to activate and deactivate the tenant context, which is essential for running jobs and commands in the correct schema.

Updating Resources and Jobs

With schema-based multi-tenancy, filtering by company_id in our Eloquent models becomes redundant. When the TenantContext is active, we bypass the company_id filter in our Filament resources. This ensures that each tenant only sees their own data, enforced at the database level.

Furthermore, background jobs interacting with tenant-specific data must also operate within the correct schema. We wrap these jobs (e.g., homologation processing, recipe duplication) within the TenantContext to ensure they execute in the appropriate tenant's schema.

<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use App\Services\TenantContext;

class ProcessHomologationChunk implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function handle()
    {
        TenantContext::activate($this->tenant->schema_name);
        try {
            // Your homologation processing logic here
        } finally {
            TenantContext::deactivate();
        }
    }
}

The ProcessHomologationChunk job activates the tenant context before running the core logic and deactivates it afterward, guaranteeing that all database interactions occur within the correct schema.

Models and Tenant Awareness

Certain models, like DashboardRefreshLog and Rate, require tenant awareness even outside the main application context. A HasTenantTable trait ensures these models are associated with the correct tenant schema. This approach centralizes tenant logic and avoids code duplication.

Conclusion

Migrating to PostgreSQL schemas provides robust tenant isolation for the Reimpact platform. The key is a well-designed middleware that dynamically sets the search_path and a TenantContext service to manage schema activation across different parts of the application. If you're building a multi-tenant application with Laravel, consider leveraging PostgreSQL schemas for enhanced security and data segregation.