Database Integrity

🔗 Database Integrity — Foreign Keys, Constraints & Orphans

How FreeITSM keeps its ~200 table relationships honest: where foreign keys are defined, why an install's history determines which ones it actually has, the class of bug that causes, and the conventions that prevent it. Written for both audiences:

• Admins: the short version is run System → Database Verification after every update. It's non-destructive and it backfills missing constraints. The rest of this page explains what it's protecting you from.
• Developers/contributors: the checklist at the bottom is mandatory for any schema change.

---

🧭 The two schema sources (and the load-bearing asymmetry)

FreeITSM's schema lives in two places that serve different install paths:

Source	Used by	What it defines
database/freeitsm.sql	Fresh installs (SQL import)	The complete DDL: tables, columns, unique keys, indexes, and all ~205 foreign-key constraints with their delete rules
api/system/db_verify.php	Existing ("grown") installs, via System → Database Verification	A $schema array that creates columns + primary key only, plus separate sections that backfill unique indexes ($uniqueIndexes) and foreign keys (per-feature FK groups)

That split is deliberate — ALTER TABLE ADD CONSTRAINT on a table with existing data needs different handling than CREATE TABLE — but it creates a load-bearing asymmetry:

Important

db_verify's $schema never creates a foreign key — not even for a brand-new table. A table only gets its FKs on a grown install if someone also added them to one of db_verify's FK groups. Forget that step and you get two silently different databases: a fresh install with referential integrity, and a grown install without it.

So every install is one of two species:

• 🌱 Fresh (imported freeitsm.sql): all constraints present from day one.
• 🌳 Grown (started earlier, evolved via Database Verification): has exactly the constraints that were explicitly backfilled — historically, not all of them.

---

🐛 The bug class this creates: silent orphans

When a delete endpoint says "children cascade via FK" but the FK doesn't exist on a grown install, nothing errors — MySQL just deletes the parent row and leaves the children behind. The orphans are invisible until something trips over them (a count that's wrong, a cleanup query that no-ops, a report that joins to nothing).

Three real instances were found and fixed during the REST API rollout (July 2026), because the API's test suite deliberately exercises delete-with-children on a grown database:

Fix	Module	What was happening
#685	Knowledge	Hard-deleting an article orphaned its tag links (no FKs on a grown install), which made the "clean up orphaned tags" step a permanent no-op. Bonus: the version-history FK has no cascade by design, so on a fresh install hard-deleting an article with "Save as new version" snapshots failed outright with a 1451 FK error.
#687	Tasks	db_verify had only ever backfilled 4 of the 12 task FKs — no parent-subtask cascade, no comments cascade. Deleting a task silently orphaned its subtasks and comments.
#689	CMDB	The worst one: db_verify had zero of the CMDB's 15 FKs, while the module's whole delete design leans on cascades (the endpoint's comments literally document them). A real historic orphan was found in the wild — a ticket↔object link whose object had been deleted months earlier.

Two lessons became standing conventions (below): backfill FKs deliberately, and never let an endpoint rely on cascades alone.

---

📋 Current FK backfill coverage in Database Verification

db_verify backfills ~95 constraints across named, idempotent FK groups (each checks information_schema before attempting, and swallows failures so one bad FK never breaks verification):

Group	Covers
Tenancy / SLA / CSAT blocks	tenants, tenant_domains, analyst_tenant_access, SLA calendar/notification tables, CSAT responses
$ticketChildFks	email_attachments, ticket_notes, ticket_audit, ticket_time_entries — orphan-aware (see below)
$changeFks	Change Management lookups + child tables
$problemFks (+ indexes)	Problem statuses/priorities/tickets/audit/notes
$ssoFks	Analyst + requester SSO identity tables, provider ownership
$knowledgeFks (added #685)	All 7 knowledge FKs — articles→analysts, versions, tag links
Tasks group (completed #687)	All 12 task FKs — was 4 (status/priority/tag-map), now includes parent CASCADE, comments CASCADE, analyst/team/ticket/change SET NULL
$cmdbFks (added #689)	All 15 CMDB FKs — classes, properties, options, objects (parent CASCADE), values, relationships, ticket links
$apiKeyFks	REST API api_keys / api_key_rate_limits
Assets / service-status / LMS / RFP sections	Their respective child tables

Unique indexes are backfilled from the $uniqueIndexes array in the same file (with pre-dedup steps where historic duplicates could block the index).

Note

If your install predates July 2026, run Database Verification once after updating — it will attach the knowledge, task and CMDB constraint sets that older grown installs are missing.

---

🧨 The MySQL gotcha: FKs won't attach while orphans exist

ALTER TABLE … ADD CONSTRAINT refuses (error 1452) if the table already contains rows that violate the new FK — i.e. historic orphans block the very constraint that would have prevented them. Database Verification handles this two ways:

• Ticket child tables get the deluxe treatment: the result row explains the orphans in plain English and offers a one-click Fix button that deletes only provably-orphaned rows (parent gone) via a whitelisted endpoint, then re-runs so the FK attaches cleanly. Strictly non-destructive otherwise — db_verify never deletes data on its own.
• Everything else simply skips the FK (try/catch) and will attach it on a future run once the orphans are gone. The explicit-delete convention (next section) stops new orphans accumulating, so these self-heal over time once the data is cleaned.

Other constraints worth knowing:

• Everything is InnoDB + utf8mb4 — FKs require matching engine/charset and exactly-matching column types on both sides.
• SET NULL rules require the child column to be nullable — a NOT NULL column can't take a SET NULL rule (that's why e.g. ticket_audit.analyst_id has no SET NULL: it's NOT NULL by design, which is also why every API key acts as an analyst).
• Self-referencing cascades work (tasks.parent_task_id, cmdb_objects.parent_id both cascade whole trees) but MySQL's cascade depth is limited (15) — one reason deep trees are also deleted explicitly in code.

---

🎯 Delete-rule conventions (what each rule means here)

The delete rule encodes an ownership statement. FreeITSM uses them consistently:

Rule	Meaning	Examples
ON DELETE CASCADE	Real ownership — the child has no life without the parent	Subtasks (tasks.parent_task_id), CMDB descendant trees + property values + relationship edges, problem tickets/notes/audit, change children, tag junction tables, api_key_rate_limits
ON DELETE SET NULL	A reference that should outlive the target — losing the target shouldn't destroy the row	tasks.ticket_id/change_id/contract_id, assets.location_id, cmdb_object_properties.value_object_id, apikeys.analyst_id, ticket-link created_by_analyst_id
No action + app guard	Deletion must be a human decision — the endpoint refuses while rows reference it	Ticket/task/problem/change statuses & priorities ("Reassign them first, or set it inactive"), CMDB classes with objects, relationship types in use
Deliberately absent	Ticket children (ticket_notes, ticket_audit, …) have FKs but no cascade — delete_ticket.php/permanently_delete_ticket.php remove children explicitly so a ticket delete is always an auditable, deliberate sequence

Soft-delete flags (is_active, is_archived, deleted_datetime) sit above all of this — most user-facing "deletes" are soft, and the FK rules only come into play at true purge time (trash empty, recycle-bin purge, permanent delete).

---

🛡️ The belt-and-braces convention (adopted July 2026)

After #685/#687/#689, the standing rule for every delete endpoint — UI and REST API alike — is:

Never rely on a cascade you didn't verify exists. Delete children explicitly, children-first, in a transaction.

Why explicit deletes even where FKs do exist:

1. They work on every install — fresh, grown, or half-migrated.
2. They're self-documenting — the endpoint shows exactly what a delete takes with it.
3. They stop new orphans forming even where a historic orphan is still blocking the FK from attaching.
4. FK cascades remain in place as the second line of defence (and for anything that bypasses the endpoints).

Endpoints already converted: knowledge hard-delete + retention purge, task delete (walks the subtask tree), CMDB object delete (walks descendants, nulls object_ref back-references, removes properties/relationships/ticket links), and all their REST API v1 equivalents. Ticket deletion always worked this way.

---

🔍 Orphan-hunting toolbox (copy-paste SQL)

Read-only queries to check an install's health — all use the LEFT JOIN … IS NULL pattern:

-- Ticket children whose ticket is gone
SELECT COUNT(*) FROM ticket_notes n LEFT JOIN tickets t ON t.id = n.ticket_id WHERE t.id IS NULL;
SELECT COUNT(*) FROM ticket_audit a LEFT JOIN tickets t ON t.id = a.ticket_id WHERE t.id IS NULL;
SELECT COUNT(*) FROM email_attachments ea LEFT JOIN emails e ON e.id = ea.email_id WHERE e.id IS NULL;

-- Tasks: orphaned subtasks, comments, tag links
SELECT COUNT(*) FROM tasks s LEFT JOIN tasks p ON p.id = s.parent_task_id
 WHERE s.parent_task_id IS NOT NULL AND p.id IS NULL;
SELECT COUNT(*) FROM task_comments c LEFT JOIN tasks t ON t.id = c.task_id WHERE t.id IS NULL;
SELECT COUNT(*) FROM task_tag_map m LEFT JOIN tasks t ON t.id = m.task_id WHERE t.id IS NULL;

-- Knowledge: orphaned tag links and version snapshots
SELECT COUNT(*) FROM knowledge_article_tags at
  LEFT JOIN knowledge_articles a ON a.id = at.article_id WHERE a.id IS NULL;
SELECT COUNT(*) FROM knowledge_article_versions v
  LEFT JOIN knowledge_articles a ON a.id = v.article_id WHERE a.id IS NULL;

-- CMDB: orphaned property values, relationship edges, ticket links
SELECT COUNT(*) FROM cmdb_object_properties op
  LEFT JOIN cmdb_objects o ON o.id = op.object_id WHERE o.id IS NULL;
SELECT COUNT(*) FROM cmdb_object_relationships r
  LEFT JOIN cmdb_objects f ON f.id = r.from_object_id
  LEFT JOIN cmdb_objects t ON t.id = r.to_object_id
 WHERE f.id IS NULL OR t.id IS NULL;
SELECT COUNT(*) FROM ticket_cmdb_objects l
  LEFT JOIN cmdb_objects o ON o.id = l.cmdb_object_id WHERE o.id IS NULL;

-- Which FKs does THIS install actually have for a table?
SELECT CONSTRAINT_NAME, DELETE_RULE
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'tasks';

If a count is non-zero: clean the orphans (the matching DELETE x FROM x LEFT JOIN parent … WHERE parent.id IS NULL is safe — it only removes provably-parentless rows), then run Database Verification so the FK attaches and it can't recur.

---

✅ Developer checklist: adding or changing tables

Every schema change touches both sources, in full:

1. database/freeitsm.sql — the complete CREATE TABLE with unique keys, indexes, and every FK with an explicit, chosen delete rule (see the conventions table above — pick the rule that states the ownership truth).
2. db_verify.php $schema — the column map (columns + PK only; first AUTO_INCREMENT column becomes the PK).
3. db_verify.php $uniqueIndexes — one row per unique key (add a pre-dedup step if historic data could contain duplicates).
4. db_verify.php FK group — every FK from step 1, names and delete rules matching freeitsm.sql exactly (grep the constraint names across both files to verify).
5. Endpoints — deletes remove children explicitly, children-first, in a transaction; don't lean on step 1's cascades.
6. Test on a grown database — create parent + children, delete the parent, then run the orphan queries above. This exact test caught all three historic bugs.

Tip

Quick self-audit for any module: grep -c "CONSTRAINT" database/freeitsm.sql scoped to its tables vs the module's FK group in db_verify.php. If the counts don't reconcile, a grown install is missing constraints.

---

See also: Architecture (database conventions) · System (Database Verification) · Multi-Tenancy-Isolation (query-layer isolation, a different integrity axis) · REST API — How It Works (why API deletes are explicit).