In a multi-tenant application, the biggest fear you'll ever have is "data leakage." That's the moment a user from Company A refreshes their page and suddenly sees an invoice belonging to Company B. It's a catastrophic failure of trust. We prevent this by "scoping" every single query to a specific tenant_id.

I want to show you how I handle this. We're going to build a small part of a project management tool where different agencies (our tenants) store their clients. If we just query by a client ID, we're asking for trouble. We need to ensure the query is always bound to the current agency.

Defining the Tenant Context

First, we need a way to know who the current tenant is. In a real app, you'd get this from a session or a JWT token. For this example, I'll just use a simple TenantContext class to hold the state. It keeps our code clean and prevents us from passing $tenantId into every single function argument, which gets tedious fast.

class TenantContext {
    public static ?int $tenantId = null;

    public static function setTenantId(int $id): void {
        self::$tenantId = $id;
    }

    public static function getTenantId(): int {
        if (self::$tenantId === null) {
            throw new Exception("Tenant context not initialized.");
        }
        return self::$tenantId;
    }
}

The Danger of Manual Scoping

Now, let's look at how we actually fetch data. The most intuitive way is to just add a WHERE clause to your SQL. I'll start with a simple function to fetch a client's details.

function getClient(PDO $pdo, int $clientId) {
    $stmt = $pdo->prepare("SELECT * FROM clients WHERE id = ? AND tenant_id = ?");
    $stmt->execute([$clientId, TenantContext::getTenantId()]);
    return $stmt->fetch();
}

This looks fine, right? But here is where I usually trip up when I'm rushing—and where you likely will too. I'll write a function to update the client's name, and in my head, I'm thinking, "I already have the primary key, I don't need the tenant ID for the update."

// WARNING: This is the mistake I just made
function updateClientName(PDO $pdo, int $clientId, string $newName) {
    $stmt = $pdo->prepare("UPDATE clients SET name = ? WHERE id = ?");
    $stmt->execute([$newName, $clientId]);
}

Wait. Stop. If a malicious user from Tenant A knows (or guesses) the clientId of a client belonging to Tenant B, they can send a request to this function and change Tenant B's data. This is a classic IDOR (Insecure Direct Object Reference) vulnerability. I forgot to scope the UPDATE query.

Building a Robust Scoping Helper

To stop myself from making this mistake, I prefer to abstract the "where" clause. Instead of writing the SQL manually every time, I'll create a small helper that forces the tenant scope into the query. While full ORMs like Eloquent do this with "Global Scopes," you can implement a simplified version yourself to keep your queries safe.

class ClientRepository {
    public function __construct(private PDO $pdo) {}

    private function applyTenantScope(string $sql): string {
        // Very simple implementation: append the tenant check
        // In a real app, you'd use a more sophisticated query builder
        if (str_contains($sql, 'WHERE')) {
            return $sql . " AND tenant_id = " . TenantContext::getTenantId();
        }
        return $sql . " WHERE tenant_id = " . TenantContext::getTenantId();
    }

    public function updateName(int $clientId, string $newName) {
        $sql = "UPDATE clients SET name = ? WHERE id = ?";
        $scopedSql = $this->applyTenantScope($sql);
        
        $stmt = $this->pdo->prepare($scopedSql);
        // Note: We add the tenant ID to the parameters because 
        // applyTenantScope just appended the text for this simple example
        $stmt->execute([$newName, $clientId]);
    }
}

By routing my updates through a repository and a scoping method, I'm creating a "safety net." I no longer have to remember to add the tenant_id check every single time; the architecture does it for me. It's much harder to accidentally leak data when the default behavior is to restrict it.

📋 Practical Task

Exercise: Implementing Scoped Deletion for Invoices

You are working on a multi-tenant billing system. You have an invoices table with columns id, amount, and tenant_id. Currently, the system has a dangerous deleteInvoice($pdo, $invoiceId) function that only filters by the primary ID.

Your Task: Rewrite the deleteInvoice function to ensure that an invoice can only be deleted if it belongs to the current tenant. You must use the TenantContext::getTenantId() method to retrieve the current tenant's ID and include it in your SQL WHERE clause to prevent cross-tenant data deletion.