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 two kinds of table, and they use opposite conventions for tenant_id. Getting this distinction right up front saves you every time.

	Scoped data	Config list
Examples	tickets, assets, changes	ticket types, asset statuses, locations
tenant_id meaning	the one company that owns this row	NULL = a global default shared by all; set = one company's own
NULL means	"the Default company" (unrouted / pre‑migration)	"everyone sees it"
Model	one owner, hard isolation	global default + per‑company add/hide
Read helper	activeTenantFilter()	getTenantConfigRows()

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.

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)

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).
• 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

---

8. The checklist

• Decided each table's shape (scoped data vs config list) — they use opposite tenant_id conventions.
• tenant_id in freeitsm.sql and db_verify ($schema + index/FK/unique sections).
• 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.