Linked Email Addresses Developer Guide - edmozley/freeitsm GitHub Wiki

✉️ Linked email addresses, merging people, changing a requester - Developer Guide

How a person came to have more than one email address, how mail from any of them finds them, how a duplicate person is merged away, and how a ticket's requester is changed. Built for #308 (5.2.0), from a university user whose staff wrote from private addresses when they could not sign in. User docs: People → Email addresses, Tickets → Changing the requester, Self-Service Portal → Your email addresses.

The one-line version: one library, includes/user_emails.php, owns the rules - userIdForEmail() is what every place that matches mail to a person now calls, userLinkEmail() / userMergeInto() are what the People page, the ticket dialog and the portal call - and three tables hold the addresses, the portal's pending claims and a person's history.

1. The decisions (Ed, 2026-11-13)

Question	Answer	Why
May a linked address sign in?	No - mail only	A compromised private mailbox must not be a way into the account. Sign-in, password reset and SSO code never call this library.
A directory makes a linked address somebody's main address	The directory wins	It is the authority on work addresses. The link is removed; the old holder's history says so.
Linking another person's main address	Offer a merge	That person is nearly always a duplicate made the first time they wrote from it.
Shipping	All of it in one go	-

2. The files

File	🎨	What changed
`includes/user_emails.php`	⚙️	new - every rule below
`database/freeitsm.sql`, `includes/db_verify_schema.php`, `includes/db_verify_indexes.php`, `includes/db_verify_modules.php`, `api/system/db_verify.php`	🗄️	three tables, five foreign keys
`includes/analyst_lifecycle.php`	👤	`user_email_addresses.added_by_analyst_id`, `user_audit.analyst_id` (history)
`api/tickets/check_mailbox_email.php`, `includes/services/tickets.php`, `api/tickets/create_ticket.php`, `api/v1/resources/tickets.php`, `includes/catalogue_approvals.php`, `includes/messaging/ingest.php`, `includes/webchat/webchat.php`, `workflow/includes/engine.php`, `includes/services/assets.php`	📨	mail → person through `userIdForEmail()` / `userEmailOwner()`
`includes/tenancy.php`	🏢	`resolveTicketTenantForEmail()` step 3a': a linked address → its person's company
`api/tickets/get_users.php`	🔎	the people search finds linked addresses
`api/tickets/save_user.php`, `api/v1/resources/users.php`, `self-service/verify-email.php`	🔒	refuse a linked address as someone's main one
`includes/directory_sync.php`, `includes/ldap.php`, `api/auth/oidc_callback.php`	🔒	`userReleaseEmailForMain()` before writing a main address
`includes/services/tickets.php` (`updateTicket`)	⚙️	`user_id` - change the requester
`api/tickets/change_requester.php`, `assets/js/inbox.js`, `api/tickets/get_email_detail.php`, `assets/css/inbox.css`	🖥️	the ticket's dialog and Requester line
`api/people/emails.php`, `people/person.php`, `assets/js/people-emails.js`, `assets/css/people.css`	🖥️	the person page's cards
`api/self-service/addresses.php`, `self-service/confirm-address.php`, `self-service/includes/user-menu.php`	🖥️	the portal
`api/v1/spec.json`	🔗	`user_id` on `PATCH /tickets/{id}`
`lang/en/{people,tickets,self-service}.php`, `people/help.php`, `tickets/help.php`, `self-service/help.php`	📖	strings and help
`includes/feature_bingo/cards/tickets-core.php`	🎯	`people.linked_email`
`tests/linked-emails.php`	🧪	new: 36 checks

3. Data

CREATE TABLE user_email_addresses (            -- confirmed links only
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,                      -- FK users, CASCADE
    email VARCHAR(255) NOT NULL,               -- lower case
    added_via VARCHAR(20) NOT NULL DEFAULT 'analyst',   -- analyst | portal | merge | requester
    added_by_analyst_id INT NULL,              -- FK analysts, SET NULL
    created_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_email_addresses_email (email)    -- one owner
);
CREATE TABLE user_email_claims (               -- the portal's pending ones - NOT unique by address
    id, user_id, email, token_hash CHAR(64) UNIQUE, expires_at, created_at
);
CREATE TABLE user_audit (                      -- a person's history; analyst_id NULL = them, or the system
    id, user_id, analyst_id, field_name, old_value, new_value, created_datetime
);

user_email_claims is deliberately not unique by address: a claim proves nothing until confirmed, so it must never block anyone, and never tell anyone the address is wanted elsewhere.

Before Database Verification, userEmailsReady() is false and everything behaves as it did: nobody has a linked address, the cards and the portal section hide themselves.

4. One owner per address

function userEmailOwner(PDO $conn, ?string $email): ?array
{
    // main address first (users.email - case-insensitive collation, so a plain = uses the index)
    // then a confirmed link
    return ['user_id' => ..., 'kind' => 'main' | 'linked'] ?? null;
}

function userIdForEmail(PDO $conn, ?string $email): ?int     // the person, for MAIL

userLinkEmail() refuses invalid, own_main, already_yours, linked_elsewhere, and main_elsewhere (with other_user_id, so the caller can offer the merge). The table's unique key catches a race (23000 → linked_elsewhere).

Every place that sets a main address respects it:

Where	What happens to a linked address
Tickets → Users (`save_user.php`), `POST`/`PATCH /users`	refused: linked to another person
Portal registration (`verify-email.php`, on confirm)	use your main account - only the mailbox's owner reaches that page
Directory sync (create, and an address change), LDAP (create, re-address), SSO just-in-time	`userReleaseEmailForMain($email, 'directory' / 'ldap' / 'sso')` - the link goes, the old holder's history says so

5. Mail finds its person

Every place that turns an address into a person for mail now asks userIdForEmail() / userEmailOwner():

// api/tickets/check_mailbox_email.php - getOrCreateUser()
$owner = userEmailOwner($conn, $email);
if ($owner && $owner['kind'] === 'linked') {
    return $owner['user_id'];   // the name on a private address is not news about the person
}

Path
Inbound email	the sender's person - a linked match never overwrites their name
`TicketsService::createTicket()` by `requester_email`, the New ticket form, `POST /tickets`	the person, not a new one
Catalogue requests on behalf of an address, messaging senders with real addresses, web chat, the workflow Create a ticket action, assigning an asset by email	the same
Company routing (`resolveTicketTenantForEmail()`, shared intake)	after the exact sender list: a linked address → its person's company. A main address is still routed by its domain.
The people search	`OR EXISTS (SELECT 1 FROM user_email_addresses ... LIKE ?)`

Not changed, on purpose: messaging pseudo-addresses ([email protected] - not real mailboxes), the Starters mover/leaver match (a workplace identity), and every sign-in path.

6. Merging a duplicate

userMergePreview(PDO $conn, int $analystId, int $fromId, int $toId): array
// can, reason (not_found | same | other_company | has_login | directory), from, to, tickets, delete, references
userMergeInto(PDO $conn, int $analystId, int $fromId, int $toId): array   // + moved, outcome

Refused when the duplicate:

signs in - userHasLogin(): a portal password, an auth_provider_id, or a user_sso_identities row. A real account is never merged away automatically;
is kept by a directory (is_managed) - removing them would only bring them back;
is in another company than the target.

Then, in one transaction (joining a caller's, as the services do):

their tickets move (UPDATE tickets SET user_id), each with a ticket_audit Requester row;
their own linked addresses move;
they are deleted if userReferences() finds nothing else pointing at them - otherwise switched off with email = NULL, so the address keeps one owner;
their address is linked to the target (added_via = 'merge');
both histories say what happened.

userReferences() counts every column with a foreign key to users, and every column named user_id, *_user_id or users.manager_id - because not every reference has a key. Over-counting is the safe side: it only ever turns a delete into a switch-off.

7. Changing a ticket's requester

In TicketsService::updateTicket(), so the screen and PATCH /tickets/{id} share it:

if (array_key_exists('user_id', $in)) {
    // the person must exist and be visible to the actor (analystCanAccessUser)
    // and be in the ticket's company - "Move the ticket to their company first"
    $updates[] = 'user_id = ?';
    $audits[]  = ['Requester', $old, $new, true];   // 4th element: written even when the caller turns audit off
}

api/tickets/change_requester.php:

GET ?ticket_id=&user_id= → the current requester and userMergePreview(old, new);
POST {ticket_id, user_id, merge_old} → updateTicket(), then - if asked and possible - userMergeInto(old, new).

There is no "link the old address without merging": it is the old person's main address, and an address has one owner.

In inbox.js: a Change requester link beside the sender, a Requester line when the requester is not the sender (get_email_detail.php now returns requester_name), and openChangeRequester() - search, pick, the merge offer ticked when possible (or why not), Change.

8. The portal

api/self-service/addresses.php takes the person only from the session (no id parameter):

add stores a claim (hashed token, 24 hours) and emails a link to the address - unless it belongs to someone else, when it stores the claim but sends nothing;
the answer is the same either way, and the claim is listed as waiting either way.

// TRAP: the claim is stored for EVERY address, so a taken one shows as "waiting for
// confirmation" exactly like a free one - leaving it out would itself say "taken".

self-service/confirm-address.php?token= needs no session - the token is the proof. Single use (deleted before anything else), then userLinkEmail(..., 'portal'). Only there - to the mailbox's owner - does it say an address belongs to another account. Five claims an hour per person at most: add sends email on their behalf.

9. Testing

FREEITSM_TEST_DATABASE=<throwaway db> FREEITSM_BASE_URL=http://localhost/<that install> php tests/linked-emails.php

36 checks, with a ZZ second company added first (TRAP: isMultiTenant() is cached per process):

linking - the refusals; lower case, once; history; one owner; another's main address needs a merge;
mail - found by a linked address in any case; a new ticket from it is hers and makes no second person; it reaches her company;
merging - two tickets moved with history, the duplicate deleted and its address hers; a duplicate still referred to (as a manager) switched off with no address; never someone who signs in, never across companies;
requester - changed and in the history with audit off; another company refused;
directory - a release removes the link and records it;
HTTP as an analyst - the people search; people/emails.php list, needs_merge, merge; change_requester.php preview and POST with merge;
HTTP as a portal user - the list; her own address; someone else's address gets the same answer and a waiting row; confirming links it; the link works once; a claim on someone else's address never links; an expired link does nothing; remove; and Tickets → Users refusing a linked address as another person's main one.

Still passing: analyst-lifecycle (21), module-access-coverage (14), directory-identity-reuse (14), directory-managed-users (15), directory-sync-scopes (19), sso-claim-mapping (32), sso-dangling-link (12), people-org (94), catalogue-group-approvals (38), tenant-support (27). A headless Chrome harness checked the person page's cards, the ticket's Change requester (finding a person by a linked address, the merge offer) and the portal's section, with no script errors.

10. Traps

Never call this library from sign-in code. Linked addresses are for mail.
A linked match must not rename the person - the name on a private address is not news.
Store a portal claim for every address - leaving one out says "taken".
Merging away somebody something still points at - switch them off and blank their address, never delete.
A new path that turns an address into a person must call userIdForEmail(), or it makes duplicates again.

People · Tickets · Self-Service Portal · REST API - Tickets