Scaling with Schemas: Multi-Tenancy Migration in Laravel

The Reimpact platform is undergoing a significant architectural shift, moving from a MySQL row-level multi-tenancy model to a more robust PostgreSQL schema-per-tenant setup. This post dives into the key considerations and steps involved in this migration, leveraging Laravel and Filament.

The Motivation for Schema-Based Tenancy

Row-level multi-tenancy in MySQL, while initially simpler to implement, presents challenges as the application scales. Data isolation becomes more complex, and the potential for performance bottlenecks increases. PostgreSQL's schema-per-tenant approach offers better isolation, security, and scalability for multi-tenant applications.

Key Components of the Migration

This migration involves several core components:

• Database Structure: A new PostgreSQL schema (pgsql-schema.sql) was created, converted from the existing MySQL schema, to serve as the template for each tenant's database.
• Schema Management: The stancl/tenancy package is used to manage the lifecycle of tenant schemas, providing tools for creation, dropping, and switching between schemas.
• Tenant Schema Service: A custom TenantSchemaService encapsulates the operations for interacting with tenant schemas, ensuring a consistent and controlled approach.
• Automatic Schema Creation: A CompanyObserver is implemented to automatically create a new schema whenever a new company (tenant) is added to the system. This streamlines the onboarding process.
• Data Migration: A custom Artisan command db:sync-mysql-to-pgsql is used for migrating data from the existing MySQL database to the corresponding PostgreSQL schemas.

Example: Tenant Schema Service

The TenantSchemaService provides a central point for managing tenant schemas. Here's a simplified example:

<?php

namespace App\Services;

use Stancl\Tenancy\Contracts\TenantManager;

class TenantSchemaService
{
    protected $tenantManager;

    public function __construct(TenantManager $tenantManager)
    {
        $this->tenantManager = $tenantManager;
    }

    public function createSchema(string $tenantId): void
    {
        $this->tenantManager->create($tenantId);
    }

    public function switchSchema(string $tenantId): void
    {
        $this->tenantManager->switchTo($tenantId);
    }
}

This service uses the stancl/tenancy package to handle the actual schema creation and switching. It provides a cleaner, more abstract API for the rest of the application.

Filament Integration

The project also migrates the admin panel from Nova to Filament v5. This involves recreating resources for managing various aspects of the platform, including products, materials, recipes, brands, warehouses, sales, purchases, rates, and massive uploads. Filament's flexible form and table builders make this process efficient.

Conclusion

Migrating to a schema-per-tenant architecture with PostgreSQL is a complex but worthwhile endeavor for the Reimpact platform. By leveraging packages like stancl/tenancy and adopting a well-defined service layer, the project aims to achieve better scalability, security, and isolation in its multi-tenant environment. The migration also involves updating the admin panel using Filament v5, streamlining the management of the platform's various resources.