Tenant Schema Resolution in Reimpact Platform

Introduction

The Reimpact platform manages company report subscriptions. A recent update migrated subscription data to tenant-specific schemas. This post addresses an issue where hardcoded table names caused lookup failures and describes the solution: dynamic table resolution.

The Problem

After migrating the company_report_subscriptions table to tenant schemas (renamed to packaging_company_report_subscriptions), existing relationships still referenced the old table name. This resulted in errors when tenant-scoped queries attempted to access the subscriptions.

Specifically, code that looked like this:

$subscription = Subscription::where('company_id', $companyId)->first();

...would fail because the Subscription model was still configured to use the global schema. The company_id check was also not tenant-aware, potentially returning incorrect results.

The Solution: Dynamic Table Resolution and Tenant Scoping

The fix involved two key changes:

1. Dynamic Table Resolution: Using a HasTenantTable trait (or similar mechanism), the Subscription model was updated to dynamically determine the table name based on the current tenant. This ensured that queries targeted the correct tenant-specific table (packaging_company_report_subscriptions in the tenant schema).
2. Tenant-Scoped Queries: The company_id checks were replaced with tenant-scoped queries. This ensures that only subscriptions belonging to the current tenant are retrieved. Here’s a simplified example of the updated query:

use Tenancy;

$tenant = Tenancy::getTenant();
$subscription = Subscription::where('tenant_id', $tenant->id)->first();

In this example, we assume a tenant_id column exists on the subscriptions table.

Key Insight

When working with multi-tenant applications, avoid hardcoding table names and always ensure that queries are properly scoped to the current tenant. Dynamic table resolution and tenant-aware queries are essential for maintaining data integrity and preventing cross-tenant data leakage.