Project Walkthrough: Building a Multi-Tenant SaaS Backend
Build a multi-tenant SaaS backend on the shared-tables-with-tenant-id model: tenants owning users and per-tenant roles, automatic query scoping to prevent cross-tenant data leaks, tenant-bound API keys, tenant-aware background jobs, per-tenant audit logging, and a high-level usage-metering approach for plan limits.
A multi-tenant SaaS application serves many independent customers — tenants — from one codebase and, in this project's model, one shared database, which is by far the most common architecture for small-to-midsize SaaS products because it avoids the operational overhead of provisioning a separate database per customer. The entire difficulty of this model is contained in one sentence: every single query, background job, and API request must be scoped to exactly the right tenant, with zero exceptions, because a single missed WHERE clause is a cross-tenant data breach. This project builds the isolation mechanism first and treats everything else — roles, APIs, queues, audit logs, billing — as things that must respect that mechanism, tying together php-multi-tenancy-models, php-tenant-security, php-rbac, php-audit-logs, and the queue system from the previous lesson.
Project Overview and Requirements
The system needs tenants (each an independent customer organization) that own users; each user belongs to exactly one tenant and has one or more roles scoped to that tenant, since a person who is an admin at one company should have zero special privileges when interacting with a different company's data, even if that were somehow possible through a shared login. On top of that data model, we need tenant-scoped API endpoints reachable via an API key, background jobs that carry along which tenant they belong to, an audit log recording who did what within each tenant, and a lightweight usage-metering concept so a tenant's plan can enforce limits like "100 API calls per day on the free tier."
The architectural decision this project commits to — as covered in php-multi-tenancy-models — is shared tables with a tenant_id column, rather than one schema or database per tenant. This is the right default for most SaaS products because it's operationally simple (one database to back up, migrate, and monitor) and cost-efficient at small-to-medium scale, but it pushes all of the isolation burden into the application layer: nothing about the database schema itself stops a query from accidentally reading another tenant's rows, which is exactly why we build automatic query scoping rather than relying on developers to remember a WHERE tenant_id = ? on every single query by hand.
It's worth being explicit up front about what "isolation" needs to mean operationally: not just that queries return the right rows, but that even a bug in application code fails safe — an accidentally-missing tenant filter should be structurally difficult to write, not just a matter of code review catching it every time. That's the design goal that shapes almost every choice in this lesson.
• Tenants own users; each user belongs to exactly one tenant with roles scoped to that tenant (php-rbac, per-tenant). • Shared-tables-with-tenant-id model: one database, a tenant_id column on every tenant-owned table (php-multi-tenancy-models). • Goal: make a missing tenant filter structurally hard to write, not just something code review has to catch. • Scope: tenant-scoped APIs, tenant-aware queued jobs, per-tenant audit logs, high-level usage metering for plan limits.
Data Model: Tenants, Users, and Per-Tenant Roles
The tenants table is the root of the hierarchy: id, name, plan (e.g. free, pro, enterprise), and created_at. Every other tenant-owned table — users, projects, invoices, whatever the product's domain calls for — carries a tenant_id foreign key back to it. The users table adds tenant_id, email, password hash, and so on, with a crucial constraint: email uniqueness is scoped per tenant (UNIQUE(tenant_id, email)), not globally, because two different companies using this SaaS product might both have an employee named [email protected] address that happens to collide in shape but must never be treated as the same account.
Roles follow the RBAC model from php-rbac, but scoped per tenant: a roles table with tenant_id, name (e.g. owner, admin, member), and a role_permissions join table mapping roles to permission strings, plus a user_roles table linking a user to one or more roles within their own tenant. This lets each tenant customize its own role names and permission sets to a degree (a pro-tier tenant might get a custom billing_manager role) without any of that configuration leaking into or being visible to other tenants — the roles themselves are tenant-scoped rows, not global constants.
Every foreign key in this schema that points to another tenant-owned table should be a compound constraint validated against the same tenant — for example, user_roles.role_id should reference a role whose tenant_id matches user_roles.tenant_id — because without that check it's possible (through an application bug) to assign a user a role that belongs to a completely different tenant, which is a subtler version of exactly the cross-tenant leak this whole system exists to prevent.
• tenants(id, name, plan, created_at) is the root; every tenant-owned table carries tenant_id. • users(id, tenant_id, email, password_hash, ...) — email uniqueness is UNIQUE(tenant_id, email), not global. • roles and role_permissions are tenant-scoped rows, not global constants — each tenant can have its own set. • Cross-table references (like user_roles → roles) must be validated against a matching tenant_id, not just a matching id.
Automatic Query Scoping and Tenant Isolation
The centerpiece of the whole system is a mechanism that makes it structurally difficult to query a tenant-owned table without a tenant filter — not just a coding convention, but something enforced in one place that every query passes through. The approach we use is a TenantScopedRepository base class: every repository for a tenant-owned table (a ProjectRepository, an InvoiceRepository, and so on) extends it, is constructed with the current tenant's id, and every query method it exposes automatically appends WHERE tenant_id = ? using that stored id — callers of the repository never get the option to omit it, because the raw query-building methods are private to the base class.
The current tenant's id itself comes from request-scoped context established once, early in the request lifecycle — resolved from the authenticated session or, for the API, from the API key (covered next) — and passed down into every repository the request constructs, rather than being re-derived or re-checked at each individual query. This is deliberate: there should be exactly one place in the codebase where "which tenant is this request for" gets decided, and everything downstream trusts that single source rather than each layer re-parsing a token or a session on its own.
This pattern doesn't eliminate the need for tenant-security-mindedness elsewhere in the codebase — a raw SQL query written outside the repository layer can still forget the filter, and that's exactly the kind of gap php-tenant-security discusses testing for directly, for example with an automated test that logs in as tenant A, attempts to fetch a resource by ID that belongs to tenant B, and asserts a 404 rather than the record. Automatic scoping and that kind of adversarial test are complementary: the scoping mechanism reduces how often a mistake is possible, and the test catches the mistakes that get made anyway.
• TenantScopedRepository stores the current tenant_id at construction and injects it into every query automatically. • The current tenant is resolved exactly once per request (session or API key) and passed down — never re-derived per query. • Automatic scoping reduces mistakes; it doesn't replace adversarial cross-tenant access tests (php-tenant-security). • A useful test pattern: as tenant A, request a resource id known to belong to tenant B, and assert it 404s.
Tenant-Scoped API Endpoints and API Keys
For programmatic access, each tenant can issue one or more API keys, and every API key is bound to exactly one tenant at creation time — an api_keys table with tenant_id, a hashed key value (never store the raw key, following the same hashing discipline as passwords), and metadata like a label and last-used timestamp. When a request arrives with an Authorization: Bearer <key> header, the authentication middleware looks up the key's hash, and if it matches, resolves the tenant id from that row — this is the moment the request-scoped tenant context gets established for API traffic, exactly the single point of truth described in the previous section.
It's worth stating the invariant explicitly because it's the whole point of this section: an API key can never be used to act on behalf of a tenant other than the one it was issued for, and there is no endpoint, parameter, or header that can override that binding. A request body containing a tenant_id field that a client could set is a direct invitation to a cross-tenant vulnerability, so the tenant id used for scoping always comes from the authenticated key or session, never from anything the client supplies in the request itself.
Rate limiting and usage metering (discussed further below) also key off this same resolved tenant id, which means the API-key-to-tenant binding isn't just an authorization concern — it's the anchor point for billing and abuse protection too. Getting that one lookup right at the start of the request is what everything downstream depends on.
• api_keys(id, tenant_id, key_hash, label, last_used_at) — a key is bound to one tenant permanently, at issuance. • Tenant id for a request always comes from the authenticated key/session — never from a client-supplied field. • A 'tenant_id' in a request body/query string must be ignored or rejected as an authorization input, not trusted. • Rate limiting and usage metering key off the same resolved tenant id established at authentication.
Tenant-Aware Background Jobs
Background jobs (built with the queue system from the previous lesson) need the same tenant discipline as synchronous requests, but it's easier to get wrong because a job runs outside of any request context — there's no session or Authorization header for it to read. The fix is simple but easy to forget: every job payload explicitly carries a tenant_id field alongside its other data, and the worker, before invoking the job's handler, constructs the tenant-scoped repositories for that job using the payload's tenant_id, exactly mirroring how a request would establish it from an API key.
This matters concretely for something like a nightly "generate usage report" job: if that job runs once globally and loops over all tenants internally without care, a bug that mixes up which tenant's data feeds which report is a customer-visible cross-tenant leak delivered by email. The safer pattern is to enqueue one job per tenant, each carrying only that tenant's id, so the tenant boundary is enforced by the queue itself — a worker processing one job only ever has one tenant's id in scope for that entire execution, with no opportunity to accidentally mix data between tenants within a single handler invocation.
The same idempotency concern from the queue lesson still applies, now with a tenant dimension: if a tenant-scoped job gets requeued and runs twice, the second run needs to be scoped to the same single tenant and produce the same result, not accidentally reprocess a different tenant's data because some piece of shared worker state didn't get reset between attempts.
• Every job payload carries tenant_id explicitly — a job has no session or header to infer it from. • Prefer one job per tenant (e.g. one 'generate report' job per tenant, enqueued in a loop) over one job looping over all tenants internally. • The worker builds tenant-scoped repositories from the payload's tenant_id before running the handler. • Idempotent re-runs must stay scoped to the same tenant — no shared mutable state that could leak across a retry.
Audit Logging Per Tenant
Following the pattern from php-audit-logs, every significant action — a user invited, a role changed, a resource deleted, a billing plan changed — gets written to an audit_log table with tenant_id, actor_user_id, action, subject_type/subject_id, a JSON details blob, and a timestamp. In a multi-tenant system, audit logs earn their keep even more than in a single-tenant app, because a tenant's own administrators will reasonably expect to see a history of what happened in their organization specifically — "who removed this user" is a support question you'll get constantly — and that history must never show a hint of activity from any other tenant.
Because the audit log itself is a tenant-owned table, it goes through exactly the same TenantScopedRepository mechanism as everything else — a tenant's admin viewing their audit log is just another scoped query, automatically filtered to their own tenant_id, with no special-casing required. The only place that needs extra care is the write side: the action that triggers a log entry, and the tenant_id recorded on that entry, must always come from the same resolved-once tenant context as the action itself, not be independently re-derived (and potentially mismatched) at logging time.
One extension worth mentioning is retention: audit logs tend to grow indefinitely and different tenants (especially enterprise ones with compliance requirements) may need different retention windows, which is a natural fit for a per-tenant setting on the tenants row itself — another example of tenant-specific configuration living as data rather than as code.
• audit_log(id, tenant_id, actor_user_id, action, subject_type, subject_id, details, created_at) — see php-audit-logs. • Audit log reads go through the same TenantScopedRepository as everything else — no special-casing needed. • The tenant_id on a log entry must come from the same resolved context as the action it's logging, not be re-derived separately. • Retention windows can be a per-tenant setting, useful for enterprise tenants with compliance requirements.
Usage Metering and Plan Limits (Conceptual)
Billing integration itself (payment processing, invoicing, dunning) is out of scope for this project, but the piece that touches the rest of the architecture directly — usage metering — is worth building at a conceptual level, because it's what makes plan limits enforceable at all. The idea is a usage_counters table keyed by tenant_id, a metric name (like api_calls or active_users), and a period (e.g. the current calendar month), incremented as the metered action happens — an API call increments api_calls for the current period, a new user invite increments active_users, and so on.
Enforcing a plan limit then becomes a simple check at the point of the metered action: before processing an API request, look up the tenant's current api_calls count for this period and its plan's limit (a plan_limits table or simple config mapping plan name to numeric limits), and reject with a 429-style response if the tenant is over quota — the same shape of check as the rate limiter from the URL shortener project, just measured over a much longer window (a month) instead of a minute, and tied to billing plan rather than raw abuse prevention.
It's worth being clear that this is metering, not billing: this project tracks and enforces usage against a known plan limit, but does not compute an invoice, handle a payment method, or talk to a payment processor — that's a substantially larger integration (typically delegated to a provider like Stripe) that would be its own dedicated project. What we've built here is the piece a real billing integration would consume: an accurate, tenant-scoped record of how much each tenant actually used.
• usage_counters(tenant_id, metric, period, count) — incremented as metered actions happen. • Plan limit check: compare a tenant's current-period count against its plan's limit before allowing the action. • Same conceptual shape as request rate limiting, just over a monthly window and tied to a billing plan. • This is metering and enforcement, not full billing — invoicing and payment processing are a separate, larger integration.
Code Examples
Because scopedQuery() is protected and always appends the tenant_id bound at construction, no subclass method — and no caller of a subclass — has the option to run a query against a tenant-owned table without the filter. find() even redundantly checks tenant_id in its WHERE clause alongside the id, so requesting another tenant's project by id returns null exactly like a nonexistent one, rather than leaking whether that id exists at all.
The tenant id is resolved exactly once, from the hashed API key, and that value is the only one ever passed into the repository. Explicitly stripping any 'tenant_id' the client might have included in the request body makes the invariant visible in code, not just relied on by omission — a future maintainer can see plainly that client input is never trusted for tenant scoping.
enqueueForAllTenants() deliberately pushes one job per tenant rather than one job that internally loops over every tenant, so a single job execution is structurally confined to one tenant's id — there's no shared loop variable or state that could mix data between tenants within one handler run. UsageMeter shows the metering half of plan limits: an atomic upsert-style increment per tenant per period, and a simple threshold check an API handler can call before allowing a metered action.
Trusting a tenant_id field sent in the request body or query string to decide which tenant's data to operate on.
Always resolve the tenant id once from the authenticated session or API key, and ignore or strip any client-supplied tenant_id.
If a client can specify which tenant to act on, any authenticated user can potentially read or modify another tenant's data just by changing a field in their request — the authorization boundary must be anchored to something the client cannot control, which is the server-side identity behind their credentials, not a value they submit.
Enqueuing a single 'generate reports for all tenants' job that loops over every tenant internally inside one handler invocation.
Enqueue one job per tenant, each carrying only that tenant's id in its payload.
A job that loops over many tenants inside one execution has a much larger blast radius for any bug — a mix-up in which tenant's data feeds which output inside that loop is a direct cross-tenant leak. Confining each job to exactly one tenant makes that entire class of bug structurally impossible within a single job run.
Writing ad hoc raw SQL queries against tenant-owned tables outside the TenantScopedRepository layer, for one-off admin scripts or reports.
Route every query against a tenant-owned table through the scoped repository layer, even for internal tooling and one-off scripts.
The whole value of automatic scoping evaporates the moment there's a second code path that queries the same tables without it — a forgotten WHERE tenant_id clause in an internal script is just as much a data leak as one in customer-facing code, and internal tools are exactly where reviewers are least likely to be looking for this class of bug.
In the shared-tables-with-tenant-id model, why is automatic query scoping preferred over relying on developers to add a WHERE tenant_id clause by hand on every query? A. Manual WHERE clauses are slower to execute than automatic ones B. It makes a missing tenant filter structurally difficult rather than depending on every developer remembering it every time C. PHP does not allow WHERE clauses to be written manually in prepared statements D. Automatic scoping removes the need for a tenant_id column entirely
B. It makes a missing tenant filter structurally difficult rather than depending on every developer remembering it every time — A convention that depends on every developer remembering it on every query, forever, will eventually be missed. Routing all tenant-owned queries through a base repository that always injects the filter removes the opportunity to forget it.
Where should the tenant id used to authorize an API request come from? A. A tenant_id field the client includes in the request body B. The authenticated API key or session, resolved once at the start of the request C. A tenant_id query string parameter, since it is easier to read D. Whichever tenant was used in the previous request from the same IP address
B. The authenticated API key or session, resolved once at the start of the request — The tenant id must come from something the client cannot forge — the server-side binding between an API key (or session) and a tenant — never from a value the client supplies directly, or any authenticated user could access another tenant's data by simply changing that value.
Why does the usage report job enqueue one job per tenant instead of one job that loops over all tenants? A. Because the queue system cannot process more than one payload per job B. To confine each job execution to exactly one tenant's id and data, making cross-tenant mix-ups within a single run structurally impossible C. Because per-tenant jobs run faster on every hardware configuration D. Because failed_jobs can only store one tenant per row
B. To confine each job execution to exactly one tenant's id and data, making cross-tenant mix-ups within a single run structurally impossible — A single job that internally loops over every tenant has a much bigger blast radius for a bug — a mistake in that loop can leak one tenant's data into another tenant's output. One job per tenant means a single execution never has more than one tenant's id in scope.
What is the difference between the usage metering built in this project and full billing integration? A. There is no difference; usage metering is billing B. Metering tracks and enforces usage against a known plan limit; billing (invoicing, payment processing) is a separate, larger integration this project does not build C. Metering only works for free-tier tenants D. Billing must be implemented before any queries can be tenant-scoped
B. Metering tracks and enforces usage against a known plan limit; billing (invoicing, payment processing) is a separate, larger integration this project does not build — This project builds an accurate, tenant-scoped record of usage and a way to check it against a plan's limit — the input a real billing system would need — but does not compute invoices or talk to a payment processor, which is typically a much larger, separate integration.
<?php
declare(strict_types=1);
abstract class TenantScopedRepository
{
public function __construct(
protected readonly PDO $db,
private readonly int $tenantId,
) {
if ($tenantId <= 0) {
throw new InvalidArgumentException('A valid tenant id is required to construct a scoped repository');
}
}
/** Every subclass query goes through this — tenant_id is never optional. */
protected function scopedQuery(string $sql, array $params = []): PDOStatement
{
// $sql is expected to contain "... WHERE tenant_id = :tenant_id ..."
// written once, here, rather than trusted to every call site.
$stmt = $this->db->prepare($sql);
$stmt->execute(array_merge($params, ['tenant_id' => $this->tenantId]));
return $stmt;
}
protected function tenantId(): int
{
return $this->tenantId;
}
}
final class ProjectRepository extends TenantScopedRepository
{
public function find(int $projectId): ?array
{
$stmt = $this->scopedQuery(
'SELECT * FROM projects WHERE id = :id AND tenant_id = :tenant_id',
['id' => $projectId]
);
$row = $s
…<?php
declare(strict_types=1);
final class ApiAuthenticator
{
public function __construct(private readonly PDO $db) {}
/** Returns the resolved tenant id, or throws if the key is invalid. */
public function resolveTenantFromRequest(string $authorizationHeader): int
{
if (!preg_match('/^Bearer\s+(\S+)$/', $authorizationHeader, $m)) {
throw new UnauthorizedException('Missing or malformed API key');
}
$rawKey = $m[1];
$keyHash = hash('sha256', $rawKey);
$stmt = $this->db->prepare(
'SELECT tenant_id FROM api_keys WHERE key_hash = :hash LIMIT 1'
);
$stmt->execute(['hash' => $keyHash]);
$tenantId = $stmt->fetchColumn();
if ($tenantId === false) {
throw new UnauthorizedException('Invalid API key');
}
$this->db->prepare('UPDATE api_keys SET last_used_at = NOW() WHERE key_hash = :hash')
->execute(['hash' => $keyHash]);
return (int) $tenantId;
}
}
// Controller wiring: the tenant id ALWAYS comes from the key, never from the body.
function handleCreateProject(ApiAuthenticator $auth, PDO $db, array $requestBody, string $auth
…<?php
declare(strict_types=1);
final class GenerateUsageReportJob
{
public function __construct(
private readonly PDO $db,
private readonly JobQueue $queue,
) {}
/** Enqueue one job PER tenant, never one job that loops over all tenants. */
public function enqueueForAllTenants(): void
{
$stmt = $this->db->query('SELECT id FROM tenants');
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $tenantId) {
$this->queue->push('reports', 'generate_usage_report', [
'tenant_id' => (int) $tenantId,
'period' => date('Y-m'),
]);
}
}
/** The actual handler, invoked by the worker with ONE tenant's payload. */
public function handle(array $payload): void
{
$tenantId = (int) $payload['tenant_id'];
$period = $payload['period'];
$projects = new ProjectRepository($this->db, $tenantId);
$allProjects = $projects->all(); // Scoped to exactly this one tenant.
$reportBody = $this->buildReportText($allProjects, $period);
$this->sendReportEmail($tenantId, $reportBody);
}
private function buildReportText(array $projects,
…