Multi Tenancy Developer Guide

Multi‑Tenancy: Developer Guide

How to make a module — or a single settings tab — company‑aware. This is the engineering recipe behind Multi‑Tenancy: the exact helpers, the two data shapes, and a copy‑paste‑and‑adapt path proven across Tickets, Changes, Problems and Asset Management. If you're adding a new module or retrofitting an existing one, start here.

The prime directive: invisible at N=1. Every change below is gated so that on a single‑company install the behaviour is byte‑identical to before. You never ship a "turn on multi‑tenancy" switch — the machinery simply lies dormant until a second company exists. Get this right and the feature is safe to roll out module‑by‑module.

---

1. First, decide the shape of each thing

Multi‑tenancy touches three kinds of table, and they use different — sometimes opposite — conventions for tenant_id. Getting this distinction right up front saves you every time. Always ask first: does NULL here mean the Default company, everyone, or nobody in particular?

	Scoped data	Config list	Connection / intake
Examples	tickets, assets, changes	ticket types, asset statuses, locations	mailboxes, messaging channels, web chat widgets
tenant_id meaning	the one company that owns this row	NULL = a global default shared by all; set = one company's own	NULL = shared intake; set = pinned to one company
NULL means	"the Default company" (unrouted / pre‑migration)	"everyone sees it"	"serves every company — route each message by its sender"
Model	one owner, hard isolation	global default + per‑company add/hide	pinned or shared (a property of the row, never a mode toggle)
Read helper	activeTenantFilter()	getTenantConfigRows()	none — the list is deliberately install‑wide
Write gate	analystCanAccess<X>()	ownership check in save_/delete_	analystCanAccessChannel() + analystCanAssignTenant()

A row of scoped data belongs to exactly one company and must never leak to another. A config list is a menu of options a scoped row points at; a company inherits the shared defaults and may add its own or hide ones it doesn't want.

A connection is different again: it is the plumbing by which work arrives, and it's administered centrally by the MSP. Its list is intentionally not filtered — an admin configuring inbound routing needs to see every mailbox and channel at once, exactly as api/tickets/get_mailboxes.php has always done. What is gated is writing: you may only pin a connection to a company you can reach, and you may only edit or delete one that is already yours.

⚠️ The trap. NULL on a connection does not mean "the Default company owns it". Reaching for activeTenantFilter() here — which treats NULL as Default‑owned — would hide every shared‑intake channel from every client company and silently break per‑sender routing. Shared intake is nobody's property, so anyone holding the relevant capability may administer it. That's three distinct meanings of NULL in one codebase: assume nothing, and read the table's own comment before you scope it.

A read that returns credentials is the exception. Capabilities generally guard writes, not reads — but a read handing back secrets needs the same care as a write. Before leaving any connection list unfiltered, check what it actually serialises: api/messaging/get_channels.php returns a has_credentials boolean and never the secret itself, which is what makes an install‑wide list defensible there.

Where the isolation boundary sits. Company routing for an inbound message is a hardcoded synchronous membrane (resolveTicketTenantForEmail() / resolveTicketTenantForChannel()). It is the isolation boundary and must never become a workflow rule, or you open a cross‑client leak window. Everything downstream of it (department, priority, assignment) is safe to be rule‑driven.

The meta‑principle (the most important design rule): never add an install‑wide "mode" toggle. Flexibility lives in the row — a config list is "global default + per‑company override," so an MSP that never overrides feels fully‑global and one that overrides per client feels fully‑per‑company, with no switch to flip.

---

2. The toolbox (includes/tenancy.php)

Everything routes through a handful of shared helpers. Learn these six and you can scope anything.

isMultiTenant($conn)                       // false at N=1 → every helper below no-ops
getActiveTenantId($conn, $analystId)       // the analyst's current company context
getDefaultTenantId($conn)                  // the silent Default company's id

// SCOPED DATA — a WHERE fragment for a list query. ['',[]] at N=1.
[$sql, $args] = activeTenantFilter($conn, $analystId, 'a');   // 'a' = table alias
//  → " AND (a.tenant_id = ? OR a.tenant_id IS NULL)"  for the Default company
//  → " AND a.tenant_id = ?"                            for a client company

// SCOPED DATA — the per-row gate for a by-id read/write. true at N=1.
analystCanAccessTicket($conn, $analystId, $id)   // + Problem / Change / Asset twins

// CONFIG LIST — resolves "global-not-hidden + this company's own".
getTenantConfigRows($conn, 'asset_types', 'asset_type', $tenantId, $cols, $where, $orderBy)

// CONNECTION — the write gates. NULL tenant_id = shared intake, so both
// deliberately ALLOW it: a shared channel belongs to no one company.
analystCanAccessChannel($conn, $analystId, $channelId)   // may I administer this one?
analystCanAssignTenant($conn, $analystId, $tenantId)     // may I pin something to this company?

The REST layer (api/v1/lib/auth.php) has the exact parallels, keyed on the API key's company scope instead of the analyst's:

[$sql, $args] = apiKeyTenantFilter($conn, $apiKey, 'a');          // list scope
apiKeyCanAccessTenantRow($conn, $apiKey, 'assets', $id)           // by-id gate
apiKeyDefaultTenantId($conn, $apiKey)                             // company for a new row

Each helper is defensive by construction: single‑company → no‑op; unknown id → refuse; a part‑migrated table (column not added yet) → don't block. That's what makes the rollout safe mid‑flight.

---

3. Recipe A — scope a data table

Worked example: giving assets a company. Six steps.

3.1 Schema

Add tenant_id INT NULL in both database/freeitsm.sql and the $schema array in api/system/db_verify.php (columns are auto‑added on Verify; UNIQUE keys + FKs are separate — add them in db_verify's index/FK sections too). For a data table the FK is ON DELETE SET NULL (never cascade‑delete records when a company is removed); index the column for the scope filter.

`tenant_id` INT NULL,                 -- NULL = the Default company
KEY `idx_assets_tenant` (`tenant_id`),
CONSTRAINT `fk_assets_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE SET NULL

3.2 Scope every list read

Append the filter to each list query — the UI endpoints and any dashboard/aggregate queries (they leak totals otherwise):

[$tSql, $tArgs] = activeTenantFilter($conn, (int)$_SESSION['analyst_id'], 'a');
$sql = "SELECT ... FROM assets a WHERE 1=1" . $tSql;
$params = array_merge($params, $tArgs);

3.3 Gate every by‑id read/write

Add the module's analystCanAccess<X>() twin to tenancy.php (they're all the same 15 lines — single‑company→true, unknown id→false, NULL→Default), then guard each by‑id endpoint. Don't forget the child reads — an asset's disks/software/history were fetchable by asset_id across companies until each got the gate:

if (!analystCanAccessAsset($conn, (int)$_SESSION['analyst_id'], (int)$assetId)) {
    echo json_encode(['success' => false, 'error' => 'Asset not found']);  // framed as not-found
    exit;
}

3.4 Stamp the company on create

Whoever creates the row decides its company. For assets, ingest agents authenticate by API key, so the key carries the company (apikeys.tenant_id) and the row is stamped from it — see the identity wrinkle below.

3.5 Enforce it in the service (if there is one)

If writes go through a shared service (Service Layer), gate there too so the API and UI are both covered. ActorContext carries a companyScope; refuse out‑of‑scope rows:

private static function assertScope(PDO $conn, ActorContext $ctx, array $row): void {
    if ($ctx->companyScope === null) return;                     // all-access → no gate
    $tid = $row['tenant_id'] ?? null;
    $tid = ($tid === null) ? getDefaultTenantId($conn) : (int)$tid;
    if (!in_array($tid, $ctx->companyScope, true)) {
        throw new ServiceError('not_found', 'not_found', 'Not found.');
    }
}

3.6 Scope the REST resource

Standing rule: a module that gets tenant_id also gets its api/v1/resources/<module>.php scoped — apiKeyTenantFilter on the list, apiKeyCanAccessTenantRow in the by‑id loader, company_id+default on create, a company field in the serialiser, and the same for its reference lists. Update the OpenAPI SPEC and the REST-API-<Module> wiki page.

---

4. Recipe B — make a settings tab per‑company

Worked example: asset types (mirror ticket types exactly — the canonical implementation). Four endpoints + a UI tweak.

4.1 Schema

tenant_id INT NULL (here NULL = a global default). Widen the name‑unique from (name) to (tenant_id, name) so a company may hold an option whose name matches a shared default. Global‑name dedup can't be enforced by that key (NULL rows aren't de‑duped), so it's enforced in the API instead — that's deliberate. FK is ON DELETE CASCADE (a company's own options go with the company).

4.2 get_* — resolve the visible list, plus a manage view

The consumer list uses the resolver; ?manage=1 in a client context additionally returns the two groups the settings screen needs:

$rows = getTenantConfigRows($conn, 'asset_types', 'asset_type', $activeId, 'id, name, ...');
// $resp['scoped'] = ['globals' => [...with a `hidden` flag], 'own' => [...], 'company' => ...]

4.3 save_* — scope, dedup in code, guard ownership

The context decides scope; name‑uniqueness is checked against what this company actually sees (its own + non‑hidden globals); you may only edit your own rows:

$scopeTenant = $isDefaultCtx ? null : $activeId;         // NULL = global default
// ...clash check across (own ∪ visible globals)...
INSERT INTO asset_types (name, ..., tenant_id) VALUES (?, ..., $scopeTenant);

4.4 delete_* and set_*_hidden

Delete guards ownership + an in‑use guard (can't remove an option live rows still point at). set_*_hidden is the "hide" half: it writes tenant_config_hidden (tenant_id, entity_type, entity_id) — the shared default is never touched, so historic rows still resolve it and it's fully reversible.

4.5 The UI

The settings JS just needs to (a) request ?manage=1, (b) when scoped is present, render two groups — the company's own (editable) and the shared defaults (each with an eye toggle) — and (c) POST to set_*_hidden. If your settings page uses a generic config‑driven renderer (assets does), add setHidden/hiddenParam to the endpoint config and a toggleHidden(), and every tab gets it for free.

---

5. Special cases you'll hit

• A tree (e.g. locations). The flat add/hide model doesn't fit, and you still have to enforce that a node's parent is in the same scope as the node. First choose the scope model by asking "does a shared default make sense here?" — for physical locations the answer is no (a client's offices are entirely its own; there's no shared office across clients), so scope the tree like data, not config: activeTenantFilter in the get_ endpoint (the Default company owns the pre-existing NULL rows; each client sees only its own), and the same filter to validate the parent. Because the settings tree and the pickers all build from that one get_ endpoint, scoping it scopes the whole UI. (If a tree genuinely does have shared defaults, use "shared + own" instead — getTenantConfigRows with no hidden rows — but reach for that only when it's real.)
• A cross‑module shared registry (e.g. suppliers, shared by Assets + Contracts + RFP). Making it private‑per‑company would leak a company's rows into the other modules' lists until they're scoped too. Either scope all consumers together, or keep it shared (the pragmatic default — and per the meta‑principle you can add per‑company later without a painful migration).
• A graph of rows that link to each other (CMDB). When the answer to "can this be shared?" is no, every way two rows can link becomes something to police. CMDB has three — parent/child, typed relationships, and object_ref properties — and all three needed the same check. 🔑 The invariant must bind an ALL-ACCESS actor too. A scope check can't help there: they can legitimately reach both companies, so nothing stops them wiring two clients' estates together. And a link leaks even when the far row is unreadable — you can still confirm it exists and bind your own records to it. Scope the pickers as well as the writes: the picker is what makes the rule discoverable rather than a mysterious error.
• Statuses / structural lists. Consider keeping them global: cross‑company reporting needs a status to mean the same everywhere, and invariants ("there is always a closed status") are hard to protect per‑company. (Ticket statuses stayed global for this reason; asset statuses were made per‑company by product choice — both are valid, it's a judgement call, not a default.)

---

6. The identity wrinkle

If a row has a natural key that was globally unique — a hostname, an asset tag, an email — it must become unique per company (two clients can each have a LAPTOP-01). That means:

• the uniqueness check moves from WHERE hostname = ? to WHERE hostname = ? AND tenant_id <=> ? (<=> is NULL‑safe, so a Default‑company key matches NULL rows);
• whoever creates the row must know the company. For machine ingest, pin it to the credential: apikeys.tenant_id gives each agent's key a company, and every WHERE hostname in the ingest path scopes to it. This is the "pinned mailbox" idea applied to asset ingest.

A single shared connection that pulls rows for several companies (a shared vCenter/Intune) can't derive one company from its credential — that needs a routing rule (one‑company‑per‑connection, route‑by‑domain, or a triage queue), which is its own piece of work. Until then, such rows land in the Default company as "needs assigning."

---

7. Verify — the two tests that matter

Every slice must pass both:

1. N=1 is a no‑op. On a single‑company install the output is unchanged. This is mostly proven by construction (every helper no‑ops), but sanity‑check a couple of endpoints.
2. N=2 isolates. On a two‑company DB, company A cannot see, read‑by‑id, or write company B's rows; a config default hidden for A is unaffected for B.

You don't need the full UI to prove this — drive the helpers/service directly from the CLI against a multi‑company dev DB:

$scopedToB = new ActorContext(1, [$companyB]);
AssetsService::updateFields($conn, $scopedToB, $companyA_assetId, ['model' => 'X']);
// → ServiceError 'not_found'  ✓ isolated

Config resolution is just as quick to check:

getTenantConfigRows($conn, 'asset_types', 'asset_type', $companyA);  // globals + A's own, not B's

⚠️ Run N=1 and N=2 in separate processes

tenantCount() memoises in a static, so isMultiTenant() is evaluated once per process and every helper below it inherits that answer. A script that checks N=1 behaviour, inserts a second company, then checks isolation will report the second half as completely unscoped — the count is still cached at 1. That looks exactly like "my guards don't work" and will send you debugging correct code. Build the two fixtures as separate database + separate process runs.

⚠️ Always include a positive control — or you'll ship a false green

A suite of "was it blocked?" assertions passes just as happily when the request was broken as when it was refused. Building the CMDB write suite, php://input turned out to be empty under the CLI SAPI, so every write failed validation with 'name' is required before reaching a single tenancy check — and all six isolation assertions passed. Nine green checks that proved nothing at all.

The tell was that the positive controls failed alongside the negatives: "co4 analyst CAN rename its own CI" was red. Without those in the suite, the false green ships. So for every "X cannot do Y", assert the matching "X can do Y in its own company" — if both move together, your harness is broken, not your code.

(If you hit the same php://input problem, override the php:// stream wrapper in the runner so a piped body reaches the endpoint.)

Drive the real endpoint, not a copy of its SQL

Re-implementing an endpoint's query in the test proves your reimplementation is correct. Include the actual file instead: define DB_NAME before config.php loads db_config.php (a second define() on the same constant is ignored, so yours wins), set a session id and write analyst_id into it before the endpoint's own session_start() runs, then require the endpoint and capture its output. That way the SQL, the PDO parameter order and the guards all get exercised. Ordering bugs are the ones this catches: a filter fragment in a SELECT subquery or a JOIN … ON clause binds before the WHERE params, and getting that wrong silently swaps values rather than erroring.

A safe fixture: build a scratch database, never touch the dev DB

You don't need a multi-company dev database to prove isolation, and you shouldn't mutate one to get it. Import database/freeitsm.sql into a throwaway schema, seed exactly the companies and analysts the test needs, call the helpers, then drop it:

// password via env so it never lands in a command line, a log, or terminal output
putenv('MYSQL_PWD=' . DB_PASSWORD);
shell_exec(sprintf('"%s" -u %s -h %s %s < "%s/database/freeitsm.sql"',
    $mysql, escapeshellarg(DB_USERNAME), escapeshellarg(DB_SERVER),
    escapeshellarg($TEST), $root));

This doubles as a fresh-install test — if freeitsm.sql has drifted from db_verify, the import or the first scoped query fails right there (see Database Verification). Two notes: freeitsm.sql already seeds the Default company as id 1, so don't insert it again; and team-granted access must be exercised too, since getAccessibleTenantIds() unions analyst + team grants and a guard that only reads analyst_tenant_access will pass a naive test and still be wrong.

---

8. The checklist

• Decided each table's shape (scoped data vs config list vs connection) — they use different tenant_id conventions. Say out loud what NULL means here.
• tenant_id in freeitsm.sql and db_verify ($schema + index/FK/unique sections) — and the two agree. Dev only ever exercises the db_verify upgrade path, so baseline drift is invisible until a stranger does a fresh install.
• A client‑supplied company_id is validated against the actor's access, not just "does this company exist" — and refused, never silently downgraded to NULL.
• Every list read filtered (activeTenantFilter) — including dashboards/aggregates.
• Every by‑id read/write gated (analystCanAccess<X>) — including child collections.
• Create path stamps the company; natural keys made per‑company unique.
• Config tabs use getTenantConfigRows + save/delete/set_hidden (mirror ticket types) + the two‑group UI.
• Service enforces scope (ctx->companyScope); REST resource scoped (standing rule).
• N=1 no‑op and N=2 isolation both verified on a dev DB.
• CHANGELOG, in‑app help, and the Multi‑Tenancy‑Progress + Settings wiki pages updated.

---

Reference

• Helpers: includes/tenancy.php (analyst side), api/v1/lib/auth.php (API‑key side).
• Canonical config‑list implementation: api/tickets/{get,save,delete,set_ticket_type_hidden}.php.
• Canonical data‑scoping + service: Asset Management (api/assets/*, includes/services/assets.php, api/v1/resources/assets.php).
• Related: Multi‑Tenancy · Settings: global vs per‑company · Isolation · Pitfalls · Progress · Service Layer.