Database Verification Developer Guide

🛠️ Database Verification — Developer Guide

How the System → Database Verification page is built and how to keep it healthy: the two data flows behind it, how a table is filed under a module, and what to do when a foreign key or index is missing. This is the maintenance companion to Database Integrity, which owns the deeper truth about foreign keys, delete rules and orphans — read that first if you're changing the schema itself.

• Admins: you don't need this page — just run System → Database Verification after every update. It's non-destructive.
• Developers/contributors: this is how the page works and the checklist at the bottom for changing it.

---

🗺️ What the page shows

The results are a card grid, one card per table (~200 of them):

• A module colour swatch + label (Tickets, Assets, System, …) — which area of the app the table belongs to.
• A status dot — green ok, amber created, blue updated, red error — the outcome of this verification run.
• A FIX flag on any table whose orphaned rows are blocking a foreign key (see Database Integrity → the MySQL gotcha).
• A search box (filter by table name) and a module dropdown (narrow to one area, with counts).
• Clicking a summary card (OK / Created / Updated / Errors) filters the grid to just that status — click Errors to jump straight to the tables that need attention, click again to clear. All three filters (search, module, status) compose.

Click a card to open that table's live structure — columns, indexes and foreign keys as they exist in the database right now. Columns show their types, PK/UNIQUE badges, nullability and defaults; foreign keys show their on-delete rules.

---

🔀 Two data flows (keep them separate in your head)

The page is deliberately split into a mutating half and a read-only half. They never share code, which is what makes the pretty detail view safe to touch.

Flow	Endpoint	Mutates?	Job
Verify	api/system/db_verify.php	Yes — CREATE TABLE / ADD COLUMN / ADD CONSTRAINT	Bring a grown install's schema up to date. This is the Database Integrity machinery — $schema, $uniqueIndexes, the FK groups.
Describe	api/system/db_describe.php	No — pure information_schema reads	Report one table's current structure for the detail modal.

Important

The detail view reports what the database holds, not what it should hold. db_describe.php reads information_schema and never compares against an expected schema. So a card can be green and its detail can show a table that is missing a foreign key — because "green" means "verification ran without error", and on a grown install a constraint is only present if it was explicitly backfilled. Answering "is anything missing?" is the Verify flow's job (it re-adds), not the Describe flow's.

The one gotcha in db_describe.php

information_schema column names come back UPPERCASE on some MySQL builds and lowercase on others. Every SELECT in the endpoint therefore aliases each column to an explicit lowercase name (SELECT column_name AS column_name …) so the PHP that reads $row['column_name'] is casing-independent. If you add a query here, alias its columns the same way — otherwise it works on your box and emits Undefined array key warnings (served as broken JSON) on someone else's.

---

🧩 How a table is filed under a module

The table→module mapping lives in includes/db_verify_modules.php and is derived, not hand-maintained. dbVerifyModuleForTable('ticket_notes') returns 'tickets' by:

1. Checking $DB_VERIFY_TABLE_OVERRIDES — a short exact-name map for tables whose name doesn't carry their module (users → tickets, servers → assets).
2. Otherwise walking $DB_VERIFY_MODULE_PREFIXES in order and returning the first prefix that matches the start of the name (ticket → tickets, sla_ → tickets, cmdb_ → cmdb, …).
3. Falling back to 'other' if nothing matches.

Colours and labels come from dbVerifyModuleMeta(), which reuses the app's own getModuleColors() (includes/module-colors.php) so the cards match the waffle menu. Groups with no colour of their own (rfp, other) get a neutral slate.

Adding a new table

Usually nothing to do here — if the table name starts with an existing module prefix (ticket_*, knowledge_*, asset_*), it files itself. Only touch this file if:

• the name doesn't match any prefix → it lands in Other (harmless, but add a prefix rule or an override to file it), or
• the name matches the wrong prefix → add an exact-name entry to $DB_VERIFY_TABLE_OVERRIDES.

Note

This file holds no schema truth. A wrong guess only mis-colours a card or drops it into "Other" — it can never affect verification, break a migration, or lose data. That's why deriving-with-a-fallback is safe where a hand-maintained 200-row list would just drift.

A table used by more than one module

Some tables genuinely serve several modules — teams is consumed by Tickets, Tasks, Contracts and the Workflow engine; system_settings backs almost everything. Pick one primary owner — the module that owns the table's lifecycle (where its rows are created and managed), not everywhere it's read. teams is filed under System because that's where teams are administered, even though four modules read it.

The grid is a filing-and-filter aid, not a semantic contract. One table = one card = one swatch; there's no notion of a table belonging to two modules, and that's deliberate — a card that appeared under two filters would double-count and confuse the totals. If the "right" owner is genuinely ambiguous, that ambiguity has no consequence: put it wherever a developer would look for it first (an override makes the choice explicit and self-documenting).

---

🔧 What if an index is missing?

Database Verification restores it. As of the index-backfill pass, db_verify carries the complete list of every named secondary index in freeitsm.sql (includes/db_verify_indexes.php) and, on each run, adds any that a grown install is missing — idempotently (present → skip, missing → add). So an index that was accidentally dropped, or that never existed on an old install, comes back on the next verify.

This closed a real gap. Historically db_verify only backfilled the handful of indexes it was explicitly told about, so a grown install could be missing foundational unique keys — uq_users_email, uq_tickets_number and others were found absent on a live install, which silently permits duplicate data (two users with one email). The backfill pass restores all of them.

The one case it won't force: a unique key blocked by duplicate data

If a UNIQUE key can't be added because the table already contains duplicate rows, the backfill reports it and moves on — it never deletes data to force the constraint. Unlike orphaned FK child rows (which have a one-click Fix, because a parentless row is provably junk), a duplicate is a judgement call: which row do you keep? So the card turns red with "Could not add unique index … — duplicate rows exist; resolve them, then re-run", and you clean the duplicates by hand (or with a considered query) before re-running. This is exactly what happens on a real install whose software_inventory_detail accumulated duplicate (host_id, app_id) rows before the unique existed.

Adding a new index

Still the two-source discipline — the backfill list is generated from freeitsm.sql, not a substitute for it:

1. database/freeitsm.sql — add the index to the CREATE TABLE (a foreign key also needs its explicit delete rule — see the conventions).
2. Regenerate the backfill list: php scripts/gen_db_verify_indexes.php rewrites includes/db_verify_indexes.php from freeitsm.sql. Review the diff; commit both files together.

Foreign keys are not in this list — they're integrity constraints with delete rules, handled by db_verify's FK groups (see below and Database Integrity). The index backfill only covers KEY / UNIQUE KEY / INDEX.

You can't forget step 2 — the drift guard catches it

The whole point of a generated mirror is that it can go stale: add an index to freeitsm.sql, forget to regenerate, and grown installs silently miss it — the exact drift the backfill exists to end. So Database Verification checks itself on every run. dbVerifyIndexListSelfCheck() (includes/db_verify_index_parse.php) re-parses freeitsm.sql and compares it to the committed list; if they've drifted, the results show a red "index backfill list" card naming the offending index and telling you to regenerate. It's silent when in sync (every shipped install — both files come from one commit), so it only ever fires for a developer mid-change.

Crucially, the same parser produces the list and audits it (both call dbVerifyParseIndexesFromSql()), so the generator and the checker can't disagree about what an index is — the classic way two "consistent" halves quietly diverge. This mirrors the capSelfCheck() / settings-manifest self-checks: the source of truth is code, and the app proves the mirror matches rather than trusting a human to have kept it so.

Tip

This is the answer to "how do we not let this drift happen again?" — not "remember to run the generator" (discipline fails), but "the app fails loudly the moment you don't". If you ever add a third generated mirror of the schema, give it a self-check too.

Warning

db_verify's $schema never creates a foreign key, not even for a brand-new table — only the FK groups do. Add a table's columns to $schema but forget its FK group and you ship a grown install with no referential integrity. This is the single most common schema-maintenance miss; the Database Integrity checklist exists to catch it. (Indexes are now covered automatically by the generated list; FKs still need the manual group.)

🧬 The column drift guard

Indexes were the easy half. Columns are guarded too, and the two guards work differently for a reason worth understanding.

Why columns are the harder case

	Indexes	Columns
Sources	freeitsm.sql → generated mirror	freeitsm.sql and db_verify_schema.php, both hand-written
Drift means	"you forgot to regenerate"	the two authors disagreed
Directions	one	two, each breaking a different install

Because both column files are maintained by hand, they can disagree either way — and the consequences are not symmetrical:

Drift	Who breaks	Who's fine
🔴 In db_verify_schema.php but not freeitsm.sql	a NEW install — missing the column until someone runs Verification	every existing install
🟠 In freeitsm.sql but not db_verify_schema.php	an EXISTING install — never gains the column on upgrade	every fresh install

The bug that prompted it

When Asset Management went multi-company, asset_locations.tenant_id was added to Verification but not to freeitsm.sql, while get_asset_locations.php already filtered on it. Every existing install was fine. A fresh install fell over on the Locations tree and the asset location picker.

🔑 Why it survived review — the part worth internalising. While developing you only ever exercise the upgrade path. You run Database Verification, it goes green, everything works. The fresh-install path is invisible to you until a stranger downloads the project — and then it's the only path they take. A schema change can be fully "tested" and still be broken for every new user. Discipline can't close that; a check that runs every time can.

How it works

dbVerifyColumnSelfCheck() (includes/db_verify_column_parse.php) parses every CREATE TABLE block out of freeitsm.sql and compares it against includes/db_verify_schema.php, reporting worst-first with the consequence spelled out, not just the column name:

🔴 In Database Verification but MISSING from freeitsm.sql —
   a NEW install will not have: asset_locations.tenant_id

$schema now lives in its own file. It moved out of db_verify.php (2,503 lines of it) into includes/db_verify_schema.php as a returnable array, precisely so the guard can require it — the same reasoning as the generated index list. Regex-parsing PHP source was the alternative and is worse.

⚠️ The comparison is deliberately forgiving

It compares base type + length + nullability only. DEFAULT, AUTO_INCREMENT, COMMENT, COLLATE, whitespace and case are ignored on purpose, because those differ harmlessly between the two files today.

🔑 A guard that cries wolf gets switched off in someone's head long before it gets switched off in code. Alarm fatigue is how a check like this dies. There's an explicit noise-resistance test asserting that changing a DEFAULT produces no report — treat that test as load-bearing.

Still worth doing by hand

The guard proves the two files agree. It can't prove either one is right. So when you change the schema, still import freeitsm.sql into a throwaway database and run the feature against it — the only way to see what a new user sees:

putenv('MYSQL_PWD=' . DB_PASSWORD);   // keeps the password out of the command line
shell_exec("\"$mysql\" -u " . DB_USERNAME . " scratch_db < database/freeitsm.sql");

You can also run the whole of Database Verification headlessly against a scratch database — set $_SESSION['setup_access'] = true (the fresh-install path, no analyst required), point DB_NAME at the scratch schema, and require db_verify.php. That exercises table creation from $schema end to end. Do this rather than trusting php -l: a lint pass proves nothing here, since a PHP fatal in this endpoint is served as HTTP 200 with a broken body.

What's still unguarded

Foreign keys. They're built from the explicit FK groups in db_verify.php, and nothing compares those against freeitsm.sql. That's the natural next self-check — and per the warning below, a forgotten FK group is the single most common schema-maintenance miss.

---

🚫 Why we don't check against an exported "golden" database

A tempting idea: dump a known-good install's structure to JSON and have Database Verification diff every install against it. We deliberately don't, for three reasons:

1. A live install is a grown schema, not a clean reference. db_verify only ever adds columns — it never modifies them (see db_verify only ever ADDs). So any real database is the accumulation of every historical state, carrying drift a fresh freeitsm.sql import wouldn't have. Freezing it as "correct" would bless that drift as the standard.
2. It creates a third source of truth. There are already two — freeitsm.sql and db_verify — and keeping them in step is the standing discipline. A golden JSON makes three, tripling the drift surface for no new guarantee.
3. Per-install noise. MySQL names unnamed constraints per install, and engine/collation defaults vary, so an exact-match diff would false-positive on structures that are actually fine.

The kernel worth keeping: db_verify adds missing structure but is quiet about what — the detail modal shows live state, not a diff against expected. The index backfill now narrows this: it names each index it restores in the card's notes. If we ever want the same for a fuller "you were missing X" audit, the "expected" set must come from the code that already defines it ($schema, the generated index list, the FK groups — all mirrors of freeitsm.sql), compared to information_schema — not from a live box. That keeps the existing sources of truth authoritative instead of minting a third.

---

✅ Developer checklist

Changing the DB Verify page itself:

• Adding an information_schema query to db_describe.php? Alias every selected column to lowercase (casing gotcha above).
• Keep db_describe.php read-only — it must never mutate. The safety of the detail view rests on it being decoupled from db_verify.php.
• A new table lands in Other? Add a prefix rule or override in includes/db_verify_modules.php. A table filed under the wrong module? Add an exact-name override.
• A table serves several modules? File it under the module that owns its lifecycle, via an override for clarity.

Adding or changing an index: add it to database/freeitsm.sql, then run php scripts/gen_db_verify_indexes.php to rebuild includes/db_verify_indexes.php, and commit both. Database Verification restores it on grown installs automatically — and if you forget the regenerate step, its drift guard turns red naming the index, so you can't ship the mismatch unnoticed.

Changing the schema (the important one) — follow the full Database Integrity checklist: both freeitsm.sql and db_verify ($schema + regenerated index list + FK group), matching names, explicit delete rules, explicit children-first deletes in endpoints, tested on a grown database.

---

See also: Database Integrity (FKs, delete rules, orphans — the schema truth) · Architecture (database conventions) · Admin Access Control (why these endpoints are administrator-only).