Multi Tenancy CMDB Case Study

🗄️ Case study: making CMDB multi-company

Part of Multi-tenancy. The recipe lives in the Developer Guide; this page is the worked example — what actually happened when CMDB became the sixth company-aware module, including the things the recipe didn't cover.

Read this if you're about to make another module multi-company, especially one where rows link to each other. CMDB is the hardest case shipped so far.

---

1. Why CMDB was different

Every earlier module was a list of independent rows. A ticket doesn't reference another ticket in a way that matters for isolation; an asset doesn't structurally depend on another asset.

A CMDB is a graph. Its entire value is that things point at each other:

        ┌──────────────┐
        │ ACME-HYPERV1 │  ← parent
        └──────┬───────┘
               │ parent_id
        ┌──────▼───────┐        depends on        ┌──────────────┐
        │  ACME-SQL01  │ ───────────────────────► │ ACME-SAN01   │
        └──────┬───────┘   cmdb_object_relationships └────────────┘
               │
               │ object_ref property ("Hosted on")
               ▼
        ┌──────────────┐
        │ ACME-RACK-04 │
        └──────────────┘

Three different mechanisms link two CIs, and each one is a separate isolation hole if you miss it:

#	Mechanism	Where it lives	Miss it and…
1	Parent / child	cmdb_objects.parent_id	a CI can be filed under another client's tree
2	Typed relationship	cmdb_object_relationships	two clients' estates get wired together
3	object_ref property	cmdb_object_properties.value_object_id	the quiet one — a property value that points at a foreign CI

The recipe's usual question — "is NULL unassigned, shared, or Default?" — was the easy part. The hard part was that a scoped list is not enough when rows can point at each other.

---

2. The decision that shaped everything

Ed's call: a CI belongs to exactly ONE company. There are no shared CIs.

This is the opposite of Knowledge, where blank means shared with everyone because an MSP's generic how-to legitimately serves every client. A client's servers are not like that — they're entirely their own.

That single answer determined the rest:

• NULL means the Default company's, the standard scoped-data convention → activeTenantFilter(), not a config resolver;
• the FK is ON DELETE SET NULL (mirroring assets) — deleting a company must not destroy the estate record;
• and because nothing is shared, every one of the three link mechanisms must be policed.

🔑 Ask the sharing question first. It isn't a schema detail — it decides whether you're writing a list filter or an invariant.

---

3. 🔴 The core lesson: an invariant must bind an all-access actor

This is the part the recipe didn't have, and the part worth carrying to every future module.

A permission check answers "can this actor reach this row?". That's enough to stop someone reading another company's CI. It is not enough to stop them linking two companies together — because an MSP administrator legitimately reaches both sides.

// NOT sufficient on its own:
assertScope($conn, $ctx, $from);   // ✅ actor can reach A
assertScope($conn, $ctx, $to);     // ✅ actor can reach B
// …an all-access actor passes both, and A and B are different companies.

// The invariant, which binds regardless of reach:
assertSameCompany($conn, $fromId, $toId, 'relationship');

Two things follow that are easy to get wrong:

• A link leaks even when the far row is unreadable. You may not be able to open the other CI, but you can still confirm it exists and bind your own records to it. Existence is information.
• Scope the pickers, not just the writes. The write guard is what makes it safe; the scoped picker is what makes it discoverable. Without both, users hit a mysterious error instead of simply not seeing an invalid choice.

Concretely: search_objects.php feeds the parent picker, the relationship-target picker and the object_ref picker. Scoping that one endpoint closed all three UI routes at once.

---

4. 📁 The file map

Colour key: 🗄️ schema · 📖 read · ✏️ write · 🔗 cross-module · 🔌 REST · 🖥️ UI/help · 📄 docs

Schema — where a scoped column is declared three times

File	🎨	What changed
database/freeitsm.sql	🗄️	cmdb_objects.tenant_id, ix_cmdb_objects_tenant_id, fk_cmdb_objects_tenant (SET NULL)
includes/db_verify_schema.php	🗄️	the expected column (this was inline in db_verify.php at the time; it now lives here so the drift guard can require it)
api/system/db_verify.php	🗄️	the FK, in a separate FK group — $schema never creates foreign keys
includes/db_verify_indexes.php	🗄️	Generated — regenerate via scripts/gen_db_verify_indexes.php, don't hand-edit

⚠️ These must agree, and drift guards now enforce it for both columns and indexes — a mismatch turns into a red card on the next Verification run. Foreign keys are still unguarded, so check those by eye. Getting a column wrong is what caused #879: a fresh install silently missing it while every upgraded install was fine. (At the time of this work the column guard did not exist; it was built immediately afterwards, prompted by exactly this bug.)

The shared helper

File	🎨	What changed
includes/tenancy.php	📖	analystCanAccessCmdbObject() — the by-id gate, twin of analystCanAccessAsset()

Reads — api/cmdb/

File	🎨	What changed
get_objects.php	📖	list scoped; parent filtered in the ON clause so a CI never vanishes; child_count subquery scoped
search_objects.php	📖	scoped — the single most valuable line, it feeds all three pickers
get_object.php	📖	by-id gate + every hydrated neighbour scoped separately (parent, children, object_ref targets, both relationship directions)
get_object_impact.php	📖	by-id gate — blast radius walks descendants, so ungated it enumerated the estate
get_object_tickets.php	🔗	by-id gate and ticketTenantFilter on the tickets; also started excluding deleted tickets
generate_object_summary.php	📖✏️	by-id gate — reads the neighbourhood into an AI prompt and writes ai_summary back
get_classes.php	📖	classes stay shared, but object_count is data and now scopes (#882 — see §6)
get_class_properties.php	📖	module guard only (config, not scoped)
get_relationship_types.php	📖	module guard only (config, not scoped)
test_ai_key.php	📖	module + AI capability guards, matching its Forms/Workflow twins
delete_class.php	✏️	deliberately NOT scoped — commented; see §6

Writes — the service layer

File	🎨	What changed
includes/services/cmdb.php	✏️	assertScope(), assertSameCompany(), resolveTenantForCreate(), tenantOf(), assertObjectRefsInCompany(); wired into all five write methods

The tell that this file was the bug: the IDE flagged $ctx as declared but not used in createObject, updateObject, deleteObject, createRelationship and deleteRelationship. The service already received the actor's company scope and ignored all of it.

Cross-module consumers

File	🎨	What changed
api/tickets/save_ticket_cmdb_object.php	🔗	ticket side was gated, CI side was not; now both, plus same-company
api/tickets/get_ticket_cmdb_objects.php	🔗	CI + parent names scoped (a pre-existing link can still straddle)
api/network-mapper/get_related_objects.php	🔗	a CMDB read living in another module — same gate, or it's a way around §3
includes/services/network_mapper.php	✏️	ActorContext threaded through replaceContents → validateNodeInput → validateObjectExists
api/network-mapper/get_diagram.php	🔗	deliberately unfiltered — comment only; see §5

REST + docs

File	🎨	What changed
api/v1/resources/cmdb.php	🔌	apiKeyTenantFilter on the list, apiKeyCanAccessTenantRow in the loader, ?company_id=, company in the serialiser, neighbours scoped, header comment corrected
api/v1/lib/openapi_schemas.php	🔌	company on CmdbObject and CmdbObjectDetail
cmdb/help.php	🖥️	section 13 "Configuration items and companies", gated on isMultiTenant()
lang/en/cmdb.php + lang/pt-BR/cmdb.php	🖥️	7 keys in the same commit — see i18n
README.md, CHANGELOG.local.md	📄	module list + #881 / #882

🎉 What you don't touch

No JavaScript changed. None. Not cmdb/browse.js, not cmdb/object.js (48 KB), not cmdb/settings/settings.js, not the CMDB block in assets/js/inbox.js.

That's the strongest argument for the architecture: the browse page renders whatever get_objects.php returns, and the pickers render whatever search_objects.php returns. Scope the endpoint and the whole UI follows. If you find yourself editing JS to hide rows, stop — you're filtering in the wrong layer, and the data is still on the wire.

---

5. 🗺️ Network Mapper: the second consumer

CMDB isn't only consumed by CMDB. Network Mapper builds diagrams out of CIs, which created two distinct problems with two different answers.

Reads and writes → gated. get_related_objects.php took a CI id on session auth alone and returned its whole neighbourhood — a way around the CMDB gate. And NetworkMapperService::validateObjectExists() only checked a CI existed, so a raw id could still plant another company's CI on a canvas. Both fixed.

Diagram rendering → deliberately left alone. get_diagram.php does not filter its nodes, and that's a decision, not an oversight:

• diagrams have no company of their own yet;
• filtering would make CIs silently vanish from existing diagrams — a worse failure than the gap it closes;
• both routes onto a canvas are now scoped, so no new diagram can acquire a foreign CI;
• so the residual is historical data only.

🔑 The general principle: when module A is scoped and module B isn't, the boundary between them needs a decision, and both answers can be wrong. Write down which you chose and why — in the code and on the wiki. Closing it properly means giving diagrams their own company, which is the first job of Network Mapper's own slice.

---

6. What stayed shared, and why

Thing	Scoped?	Reasoning
cmdb_objects (the CIs)	✅ scoped	The data. Whose kit it is.
cmdb_classes	❌ shared	Describes how you model an estate, not whose it is
cmdb_class_properties, ..._options	❌ shared	Hang off a class
cmdb_relationship_types	❌ shared	The verbs ("depends on") are vocabulary
cmdb_icons	❌ shared	Presentation
cmdb_object_properties	↪️ inherits	Via object_id
cmdb_object_relationships	↪️ inherits	Via both ends
ticket_cmdb_objects	↪️ inherits	Via both the ticket and the CI

Only one table got a tenant_id. Everything else either inherits through a parent or is genuinely install-wide config — the same call made for ticket statuses and priorities.

Two counts of the same table go opposite ways, which makes the rule concrete:

• get_classes.php → scoped. "How many CIs of this class do I have?" is a question about data. Unscoped it contradicted the list beside it and disclosed other companies' volumes. (This shipped as a bug — #882, spotted by Ed noticing the sidebar totals didn't change on switching company.)
• delete_class.php → unscoped, deliberately. "Is anyone still using this class?" must consider every company, or one company deletes a class another is actively using.

🔑 Scope by what is being counted, not by which table the query starts from. A COUNT of data hanging off a config row is still data. This is exactly where I slipped.

---

7. The gap left open: moving a CI between companies

Tickets and changes have a move to company action. CMDB deliberately doesn't.

A ticket is a standalone row, so moving it is one UPDATE. A CI sits in a tree with relationships hanging off it, so a move must decide the fate of its parent, its children and every link — and both defaults are wrong:

• leave the links → you've created exactly the cross-company links §3 forbids;
• take them → one click silently re-homes an entire subtree.

The shape when it's built: move the CI and its descendants as a unit, refuse with a clear list if any relationship or object_ref would end up straddling, and audit it. Until then a wrongly-filed CI is deleted and recreated.

---

8. How it was verified

Six slices, each committed and tested separately, then 83 checks across five suites at N=1 and N=3 — all driving the real endpoint files, not reimplementations of their SQL. That harness is reusable and documented separately: Multi-tenancy: the test harness.

Two findings came only from those tests and would otherwise have shipped:

1. A cross-company relationship leaked the other CI's name through get_object.php — the by-id gate passed (it's your CI), but the neighbour hydration didn't. This is what turned "gate the object" into "gate every neighbour independently".
2. A false green. The entire write suite passed while proving nothing — see the harness page. The tell was the positive controls failing alongside the negatives.

And one came only from Ed using it: the class sidebar counts (§6).

🔑 Isolation bugs are invisible in normal use — at N=1 everything looks perfect, and at N>1 you only notice if you happen to look at the right screen as the right user. Tests aren't optional here in the way they are for a visible feature.

---

See also

• Developer Guide — the recipe and the file map for any module
• Test harness — the reusable verification kit
• Progress tracker · Pitfalls · CMDB