Enhancements to Tenant Schema Management and Database Refresh

Introduction

The Reimpact platform is undergoing improvements in how it manages tenant schemas and streamlines database refreshing. These changes aim to improve data consistency, developer experience, and overall system maintainability.

Tenant Schema Updates

Several updates have been made to how tenant schemas are populated and managed:

• Purchase Data Mapping: Added a mapping for purchases to packaging_purchases during tenant schema population. This ensures that purchase data is correctly propagated to tenant-specific schemas.
• Report Data Mapping: Included a mapping for generated_reports_calculated_data, addressing a previous issue where it was skipped due to data type mismatches (bigint/uuid). A filter_join was implemented to handle this.
• Data Type Correction: Corrected the generated_reports_calculated_data.generated_report_id type from bigint to uuid via a tenant migration, ensuring data integrity across schemas.
• Rate Model Adjustment: Removed HasTenantTable from the Rate model, ensuring rates are managed only in the public schema. The packaging_rates table was dropped from tenant schemas through a migration.

Streamlined Database Refresh

A new command, db:refresh-from-production, has been introduced to simplify the database refresh process. This command chains the following actions:

1. Sync: Synchronizes the database.
2. Create Schemas: Creates the tenant schemas.
3. Populate: Populates the tenant schemas with data.

This command provides a one-step solution for refreshing the database with production data in a development or testing environment.

use Illuminate\Support\Facades\Artisan;

class DatabaseService
{
    public function refreshFromProduction(): void
    {
        Artisan::call('db:sync');
        Artisan::call('tenants:create-schemas');
        Artisan::call('tenants:populate');
    }
}

Environment Configuration

The SYNC_MYSQL_* environment variable calls have been moved to the appropriate component file to address PHPStan issues and improve code maintainability.

Lessons Learned

When designing multi-tenant applications, it's crucial to carefully manage data types and schema migrations to ensure data consistency. Automating common tasks like database refreshing can significantly improve developer productivity.

Actionable Takeaway: Review your multi-tenant database schema and ensure data types are consistent across public and tenant-specific schemas. Implement automated scripts or commands to streamline database refresh processes.