Directory Maintenance Developer Guide

🛠️ Imported people - Developer Guide

Since 5.3.0 · #261, #263 · PR #264 by vsc55 (review) · User guide: Imported people · Emailing the report: Emailed reports - Developer Guide

One rule for every directory source (LDAP, CardDAV, Entra ID), and one service that looks after the people a source imported: three lists, the writes on them, the "what if" previews, and the delete of a source itself. The service never emits HTTP; the endpoints pass it an ActorContext and turn its ServiceError into JSON.

📁 Files

File	Role
includes/users.php	userManagedSql() - the rule. userDeleteBlockedByHistory(), userDeleteBlockedByRecords(), userDeletePerson() and the three lists of tables they work from (USER_DELETE_CASCADE, USER_DELETE_SET_NULL, USER_DELETE_LOGS)
includes/services/directory_maintenance.php	DirectoryMaintenance - the lists (orphans(), stale(), imported(), importRuns(), deletedSources()), the counts (orphanCount(), staleCount()), the previews (previewSourceDelete(), previewRemove()), the writes (apply(), releaseSource())
api/system/directory_maintenance.php	Administrators only. GET ?action=orphans / stale / runs / imported / preview_delete / preview_remove (never writes); POST {action:'apply', list, do, ids, provider, days, run}
api/system/delete_sso_provider.php	Deleting a source: {id, people: 'keep' | 'left'}, all in one transaction (see below)
api/tickets/delete_user.php	Tickets → Users delete: the directory refusal (#238), the history refusal, then userDeletePerson()
system/directory-maintenance/index.php	The Imported people page (a Maintenance tool, sys-page); ?source=<id> opens on that source
assets/js/directory-maintenance.js	The page's lists, and the preview dialog (sys-modal-xl) shared with System → Authentication's delete. Loads pdf-writer.js and email-report.js only when PDF or Email is pressed
system/maintenance/includes/tools.php	The Maintenance card; its count is DirectoryMaintenance::orphanCount()
system/includes/imported_people_notice.php	importedPeopleNotice() - the line under Run on the LDAP, CardDAV and Entra pages (system/sso/provider.php, carddav.php, entra.php)
includes/people.php	peopleOrgGaps() - the orphans as a third, read-only gap on the organisation chart
system/help/directory-maintenance.php	In-app help, Imported people
tests/directory-maintenance.php	89 checks, always rolled back - see the end

🔑 The rule: managed only while the source exists

function userManagedSql(string $u = 'u'): string
{
    return "($u.is_managed = 1 AND EXISTS (SELECT 1 FROM auth_providers ump WHERE ump.id = $u.auth_provider_id))";
}

Every reader of is_managed asks this instead of the column - the locked fields on Tickets → Users, the portal profile and Assets → Users, the delete refusal (#238), Starters, People and its gaps, the merge preview. It is written to be dropped in as a column, so the code reading the row does not change:

"SELECT u.id, ..., " . userManagedSql() . " AS is_managed FROM users u ..."

Why not is_managed alone: deleting a source left its people flagged, and with no protocol userDirectoryOwnedFields() locks the whole directory list - locked for good, with nothing ever updating them.

Why EXISTS and not auth_provider_id IS NOT NULL: the foreign key that NULLs the id is in database/freeitsm.sql, but Database Verification adds no constraints, so on a verified install a deleted source leaves a dangling id. EXISTS frees those people the moment the code is in - no migration.

📋 The three lists

The WHERE of each list lives in one private method, and the page, the count, the preview and the write all use it - so they cannot disagree about who is in it.

List	WHERE	Actions (ACTIONS)
orphans - source deleted	orphanWhere(): u.is_managed = 1 AND NOT EXISTS (SELECT 1 FROM auth_providers op WHERE op.id = u.auth_provider_id)	keep, left, delete
stale - no longer brought in	staleWhere($providerId, $days): the source's managed people with last_seen_in_source empty or older than $days (STALE_DAYS = 30, 90, 180, 365; the page and the notice use STALE_DEFAULT = 90)	keep, left, delete
imported - Remove imported people	importedWhere($providerId, $runId, $deleted, $logged): created by a live run of the source (one run, or any), and still its - see below for a deleted source	remove

• keep - release(): is_managed = 0, auth_provider_id = NULL. An ordinary person from now on.
• left - is_active = 0 and deactivated_datetime; on the orphans list it also releases them, or they would stay on the list for good.
• delete / remove - userDeletePerson(), after the checks below.

Each list is at most LIMIT (500) people; a preview, read in full, at most PREVIEW_LIMIT (5000). Everything is scoped by peopleScope($conn, $actor->actorId, 'u.tenant_id').

Every write works the list out again

apply() never trusts the ids it is given. It selects them again with the list's own WHERE and the analyst's company scope, and anybody not in that result is skipped Not in this list any more.:

[$scope, $sArgs] = peopleScope($conn, $actor->actorId, 'u.tenant_id');
$st = $conn->prepare("SELECT u.id, u.display_name, u.preferred_name, u.email, u.username FROM users u
                       WHERE u.id IN ($marks) AND $where $scope");
$st->execute(array_merge($ids, $args, $sArgs));

A stale page or an id typed into a request does nothing to anybody else.

Remove, and Delete on the stale list, wait for the source to stop importing

private static function sourceImports(array $src): bool
{
    return (int)$src['enabled'] === 1 && (int)$src['sync_enabled'] === 1;
}

While both are on, apply() throws ServiceError('conflict', 'source_imports', ...): the next run would create everybody again. These do not ask userDeleteBlockedByDirectory() (the hand-delete rule, #238) - undoing an import is exactly what that rule leaves a way through for.

🧬 Created or adopted - and where that is recorded

An import either created a person or adopted one who was already here (matched by email). Only created people are ever deleted (Ed, #261).

• The import history: directory_sync_entries.action = 'created' or 'adopted', per live run in directory_sync_runs.
• The delete's log entry: releaseSource() reads whom the source's imports created before anything changes and writes them into its system_logs row (log_type = 'directory_maintenance', details.list = 'source_deleted', details.created = [ids]). That is what lets Remove work after the source is gone: deletedSources() reads those rows (and any import history that outlived the source) and offers "Entra ID (deleted)".

For a deleted source, importedWhere() takes the people managed by no source that exists, created by the history or listed in the log entry:

if ($deleted) {
    ...
    if (!$runId && $logged) {
        $created = "($created OR u.id IN (" . implode(',', array_fill(0, count($logged), '?')) . '))';
    }
    return ['NOT ' . userManagedSql('u') . " AND $created", $args];
}

Delete on the orphans and stale lists asks notCreatedReasons() per person: adopted → Existed before the import - kept.; created (history, or the log entry for their deleted source) → go ahead; nothing recorded either way → Nothing records that an import created this person - kept.; on the stale list, never found by any import (last_seen_in_source empty) → kept.

🗑️ Deleting a person: userDeletePerson() and what blocks it

The one way a person is deleted - Tickets → Users and the maintenance tools both come here, after their own checks. A verified install has no foreign keys, so it does by hand what database/freeitsm.sql would:

foreach (USER_DELETE_CASCADE as $t => $c) $run("DELETE FROM `$t` WHERE `$c` = ?");
foreach (USER_DELETE_SET_NULL as [$t, $c]) $run("UPDATE `$t` SET `$c` = NULL WHERE `$c` = ?");
$conn->prepare("DELETE FROM users WHERE id = ?")->execute([$userId]);

List	Tables	Treatment
USER_DELETE_CASCADE	portal_user_preferences, portal_notifications, user_sso_identities, user_email_addresses, user_email_claims, user_audit, messaging_identity_links, directory_group_members, project_members, user_password_reset_tokens, manager_grants	Theirs - deleted with them
USER_DELETE_SET_NULL	users.manager_id, ticket_recordings.recorded_by_user_id, form_submissions.submitted_by_user_id, contracts.customer_user_id, ticket_assets.created_by_user_id, domains.customer_user_id, jml_cases.user_id, jml_cases.manager_user_id	Records that outlive them - the name is let go
USER_DELETE_LOGS	directory_sync_entries.user_id, carddav_write_log.user_id	Facts about the past - left as they are

It opens its own transaction unless the caller holds one, and skips a table this install has not got (42S02 / 42S22).

What blocks it:

• userDeleteBlockedByHistory() - tickets they raised, equipment they hold. Both delete paths ask it.
• userDeleteBlockedByRecords() - bulk tools only. Any other record that names them, found through userReferences() (which also finds columns with no foreign key), ignoring their own rows, users.manager_id and the logs. Tickets → Users does not ask it: one person, deleted on purpose, and their records lose the name as the schema's ON DELETE SET NULL says.

In the service, both are deleteBlocked(); a blocked person is skipped with the reason, never an error.

🔁 Deleting a source: the order

api/system/delete_sso_provider.php, one transaction:

1. beginTransaction().
2. DirectoryMaintenance::releaseSource() - first, while users.auth_provider_id still says who belongs to it: refuses if any of its people are in a company the analyst cannot access; records whom its imports created; marks them as left if asked; sets is_managed = 0, auth_provider_id = NULL on every row pointing at the source; logs it (list = 'source_deleted', logged even with nobody to release, because the entry is where the deleted source's name is kept).
3. Deletes, explicitly, what a verified install has no foreign key to cascade: carddav_write_log, directory_group_members then directory_groups, user_sso_identities, analyst_sso_identities, sso_group_teams, sso_groups_seen - each tolerating a missing table.
4. DELETE FROM auth_providers.
5. Resets analysts.auth_provider_id and teams that signed in with it, then re-applies team sign-in methods (analystSignInApplyMany()).
6. commit(). A ServiceError (the company check) returns its own words; anything else rolls back and returns a fixed sentence - never the exception's text.

directory_sync_runs is not deleted: a verified install keeps the import history. An install made from database/freeitsm.sql has ON DELETE CASCADE there, so it goes; historyGoesWithSource() reads information_schema.REFERENTIAL_CONSTRAINTS so the preview says which happens on this install.

🔍 The previews

?action=preview_delete&provider=<id>&people=keep|left and ?action=preview_remove&provider=<id>[&run=<id>][&ids=1,2,3] - GET, nothing written.

• previewSourceDelete() returns the people and each one's outcome (kept, marked_left, kept_left), the fields that unlock (userDirectoryOwnedFields()), people_hidden and can_delete, and every record that names the source with its outcome (removed, kept, password for analysts who sign in with it, the reader marked you), plus whether the scheduled import imports it now.
• previewRemove() returns who would be removed or skipped (and why), with each person's sign-in links, group memberships and how many report to them.

The dialog builds a model from the preview and turns it into HTML, a PDF (pdf-writer.js) or an email (emailReportToMe(), which posts the PDF and the HTML; System → Emailed reports decides what the email carries):

async function emailReport(m) {
    const send = await emailer();          // loads email-report.js the first time
    const blob = await pdfBlob(m);
    return send({
        subject: m.title,
        html: '<h2>' + esc(m.title) + '</h2>' + m.lines.map(l => '<p>' + esc(l) + '</p>').join('') + modelHtml(m),
        filename: pdfName(m),
        pdf: blob
    });
}

🏢 Company scope

• Lists, counts and previews: peopleScope() - the companies the analyst can access (nothing on a single-company install).
• Deleting a source changes every company's people, so sourcePeopleOutOfReach() counts those outside the analyst's companies (0 for an analyst with every company, analystHasAllTenantAccess()); the preview lists theirs and says how many more (people_hidden), and releaseSource() refuses with ServiceError('forbidden', 'company_scope', ...) while it is above 0.
• The Maintenance card's count (orphanCount()) is every company: it is a "somebody should look" signal for administrators.

⚠️ TRAPs

• TRAP: never read users.is_managed on its own. Use userManagedSql(). The column stays 1 on a person whose source has gone (a verified install has no foreign key to clear it), and reading it alone locks them for good.
• TRAP: keep USER_DELETE_CASCADE / USER_DELETE_SET_NULL in step with database/freeitsm.sql. Every REFERENCES users (id) ON DELETE CASCADE belongs in the first; a table holding history must never go there - it goes in the second, or blocks the delete.
• TRAP: never delete a person with a bare DELETE FROM users. Use userDeletePerson(), or a verified install is left with rows pointing at nobody.
• TRAP: releaseSource() must run before the source row goes. After the delete nothing says whose people they were, and on a schema-file install the import history - the record of whom it created - cascades away with it.
• TRAP: a new list or action must re-select in apply(). Acting on the ids posted is how a stale page deletes the wrong person.
• TRAP: anything new that hangs off auth_providers must be deleted by hand in delete_sso_provider.php and listed in previewSourceDelete(). Database Verification adds no foreign keys, and the preview promises it lists everything.

🧪 Verifying

Run it in the test install's own folder (its config.php points at the throwaway database; the test refuses any other):

cd c:/wamp64/www/freeitsm-prtest
FREEITSM_TEST_DATABASE=freeitsm_prtest FREEITSM_BASE_URL=http://localhost/freeitsm-prtest php tests/directory-maintenance.php

It builds two companies, two sources, people imported by each, a source deleted the way a verified install deletes one (leaving a dangling id), an import run that created one person and adopted another, and a person with a ticket - inside a transaction that is always rolled back. It goes through the real userManagedSql(), personDetail(), peopleOrgGaps() and DirectoryMaintenance, and checks each preview against the write it describes. Controls: a person whose source still exists stays managed and off the orphan list; an analyst limited to one company sees nobody from the other; Remove deletes nobody it was not asked to.

Related: tests/system-maintenance.php (the Maintenance card), tests/people-org.php (the third gap), tests/directory-managed-users.php (the locked fields).