// @ts-check
import { query, HEX2uuid } from '@commtool/sql-query';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { decideMemberALinks } from './memberALinkDecision.js';
import { rebuildListEntries } from '../../tree/rebuildListEntries.js';
/**
* @import {ExpressRequestAuthorized, ExpressResponse} from './../../types.js'
*/
/**
* Invariant repair: exactly one ACTIVE `memberA` link per object — for every type.
*
* Migration `20260925-links-single-membera-trigger` installs the `BEFORE INSERT`
* trigger that enforces the invariant for every NEW link. A trigger cannot fix the
* rows that were written before it existed. This endpoint does that.
*
* ## Removing a link means deleting it
*
* `Links` is a system-versioned table: `ValidFrom` / `ValidUntil` are generated
* aliases of the system period (`ROW START` / `ROW END`) and cannot be written. A
* link is therefore "active" while its current version exists, and the only way to
* take it out of the active set is `DELETE`. The history stays readable through
* `FOR SYSTEM_TIME ALL`, so nothing is lost — the removal remains auditable.
*
* ## Which link is the intended one?
*
* The defect mechanism is one-sided: a transfer inserts the new `memberA` link and
* fails to close the previous one. The new link is therefore the intention and the
* stale one is the leftover — **the chronologically later link wins**. `ValidFrom`
* is the insert time of the row version, so "later" means "inserted last", which is
* exactly the transfer. This is corroborated by the production data: for 48 of the 80
* duplicate `person`/`extern` objects `ObjectBase.hierarchie` already equals the
* target of the NEWER link — the versioned column pair stayed in sync with the
* transfer, only the old link was never removed. Where the timestamp cannot decide (two
* links started at the very same system period, as an import produces them), the
* object's own `member`-family link to the target breaks the tie; only a tie without
* such evidence is reported for a human.
*
* The rules themselves live in `memberALinkDecision.js` (pure, unit-tested).
*
* ## Deletion debris
*
* Next to link duplication the repair removes **links whose source object was
* deleted**: a dead owner makes the link inert, it can never satisfy the invariant for
* a live object, and it shows up as garbage in link walks. Those links are reported in
* their own bucket (`linksOfDeletedObjects`) so the report never looks like it is
* talking about an object that does not exist. `restorePerson` is unaffected — it
* reads links from the system history, which the removal preserves.
*
* ## The one case that is left alone
*
* A dangling link is only removed while the object keeps at least one live link. An
* object whose links ALL point at deleted targets has no valid link to keep — the
* invariant is unreachable for it. Removing the last link would change its behaviour
* and, because the report walks `Links`, hide it from every future run. It stays as it
* is and is reported under `skipped`. There are ~1.6k such objects in production, mostly
* `entry` rows whose list was deleted; deciding them (deleting the object) is object
* maintenance, not link maintenance.
*
* ## The case link removal must not decide: one `entry` for several lists
*
* An `entry` object exists once per (person, list). When one entry carries the `memberA`
* link of several dlists, the person genuinely belongs to every one of those lists — the
* links are not surplus, they just share the wrong carrier. Removing one would silently
* drop the person from a list, so the repair leaves the links alone and SPLITS the entry
* instead: `rebuildListEntries(person)` gives every matching list its own entry object
* (see its claim/detach pass). Those objects are reported in `listEntriesToRebuild` —
* separately from `skipped`, which stays reserved for cases that need a human. The split
* is requested by `fix` as well and its outcome is reported in `rebuiltListEntries`.
*
* ## Scope: the whole database, on purpose
*
* The endpoint is deliberately NOT scoped to the organisation of the request:
*
* - the invariant is global — "one active `memberA` link per object" holds for every
* object of every organisation;
* - the trigger from migration `20260925` is global as well: it fires on every insert
* into `Links`, regardless of which organisation the link belongs to;
* - the legacy violations span all organisations in the database, so scoping the
* repair to one organisation would leave every other organisation broken;
* - the repair needs no organisation attribution at all — it works per source object
* and only consults the link set of that object.
*
* Sibling DB-wide maintenance endpoints (`optimize`, `compressHistory`) work the same
* way. An organisation lives in the same database and is rooted at the group that
* carries the self-referencing `memberSys` link.
*
* ## Only links
*
* Removing a surplus link leaves the derived state (`ObjectBase.stage`/`hierarchie`,
* the rendered `Title`, `Member.Data`) untouched. Run `recreateObjects` afterwards to
* pull that along — see `needsRecreate` and `flaggedObjectBase` in the report.
*
* GET /maintenance/memberAConsistency → report only, no writes.
* GET /maintenance/memberAConsistency?fix=true → additionally remove the surplus links.
*/
/** Query parameter values that switch from report-only to repair mode. */
const TRUTHY = new Set(['1', 'true', 'yes', 'on']);
/**
* `memberALinkDecision` flags a multi-list `entry` with this prefix. Those are the
* objects the link repair must not decide — the entry has to be split, not trimmed.
*/
const ENTRY_SPLIT_REASON = 'entry corroborated by';
/**
* @param {unknown} value
* @returns {boolean}
*/
const isFixRequested = (value) => TRUTHY.has(String(value ?? '').toLowerCase());
/**
* SQL returns numeric columns as strings; levels are compared numerically.
* @param {unknown} value
* @returns {number|null}
*/
const num = (value) => (value === null || value === undefined || value === '' ? null : Number(value));
/**
* Guards the one assumption the repair leans on: as soon as a link to an EXISTING
* target is removed, exactly one such link has to be kept. Without that the object
* would silently end up with zero `memberA` links.
*
* @param {any} source
* @param {any} decision
*/
const assertNoInvariantRisk = (source, decision) => {
// An orphaned source has no live object, so "zero kept links" is not a risk but
// the intended outcome — the guard only protects objects that still exist.
if (source.orphaned) return;
const removesExistingLink = decision.remove.some((/** @type {any} */ l) => !l.dangling);
if (removesExistingLink && decision.keep.length !== 1) {
throw new Error(
`memberAConsistency: refusing to remove links of ${HEX2uuid(source.UID)} `
+ `without exactly one kept link (kept ${decision.keep.length})`
);
}
};
/**
* Every active `memberA` link whose source holds more than one of them, plus every
* link whose target no longer exists (`TargetExists = 0` → dangling).
*
* ## Why this is written in two phases
*
* The obvious single-pass form
*
* ```sql
* LEFT JOIN ObjectBase t ON (t.UID = l.UIDTarget AND t.ValidUntil > NOW())
* WHERE … AND (dup OR t.UID IS NULL)
* ```
*
* does not come back on production-sized data: `Links` holds ~470k active links, and
* a `LEFT JOIN` is evaluated for every one of them before the `WHERE` narrows
* anything, so `ObjectBase` is probed ~470k times. The candidate set is only ~2k rows,
* so the candidate links are selected FIRST (the derived table below) and enriched
* afterwards — that drops the runtime from minutes to a few hundred milliseconds.
*
* The source is LEFT JOINed: the links of a deleted object are still in the table and
* must not silently disappear from the report.
*
* @returns {Promise<any[]>}
*/
const fetchMemberALinks = () => query(
`SELECT c.SourceUID,
c.LinkValidFrom,
c.LinkTargetUID,
s.Type AS SourceType,
s.Title AS SourceTitle,
s.UIDBelongsTo AS SourceBelongsTo,
s.stage AS SourceStage,
s.hierarchie AS SourceHierarchie,
(t.UID IS NOT NULL) AS TargetExists,
t.Type AS TargetType,
t.Title AS TargetTitle,
t.stage AS TargetStage,
t.hierarchie AS TargetHierarchie,
(dy.UID IS NOT NULL) AS HasDynamic,
EXISTS (SELECT 1 FROM Links co
WHERE co.UID = c.SourceUID
AND co.Type IN ('member','memberS','memberG','memberGA','function')
AND co.UIDTarget = c.LinkTargetUID
AND co.ValidUntil > NOW()) AS TargetCorroborated
FROM (
SELECT l.UID AS SourceUID, l.UIDTarget AS LinkTargetUID, l.ValidFrom AS LinkValidFrom
FROM Links l
WHERE l.Type = 'memberA' AND l.ValidUntil > NOW()
AND (l.UID IN (SELECT d.UID FROM Links d
WHERE d.Type = 'memberA' AND d.ValidUntil > NOW()
GROUP BY d.UID HAVING COUNT(*) > 1)
OR NOT EXISTS (SELECT 1 FROM ObjectBase o
WHERE o.UID = l.UIDTarget AND o.ValidUntil > NOW())
OR NOT EXISTS (SELECT 1 FROM ObjectBase o
WHERE o.UID = l.UID AND o.ValidUntil > NOW()))
) c
LEFT JOIN ObjectBase s ON (s.UID = c.SourceUID AND s.ValidUntil > NOW())
LEFT JOIN ObjectBase t ON (t.UID = c.LinkTargetUID AND t.ValidUntil > NOW())
LEFT JOIN Links dy ON (dy.UID = c.SourceUID AND dy.Type = 'dynamic'
AND dy.UIDTarget = c.LinkTargetUID AND dy.ValidUntil > NOW())`,
[],
{ log: false }
);
/**
* Removes one `memberA` link. Deletes only the current version; the history row
* survives the system versioning and stays inspectable.
*
* `ValidUntil > NOW()` is repeated on purpose: in a system-versioned table it is the
* "is current" test, so a link that another process removed in between is a no-op.
*
* @param {Buffer} sourceUID - binary UID of the link owner
* @param {Buffer} targetUID - binary UID of the target to remove
*/
const removeMemberALink = (sourceUID, targetUID) => query(
`DELETE FROM Links
WHERE UID = ? AND Type = 'memberA' AND UIDTarget = ? AND ValidUntil > NOW()`,
[sourceUID, targetUID],
{ log: false }
);
/**
* Groups the flat link rows per source object.
*
* Keyed by the UUID string — two `Buffer`s with equal content would be different Map
* keys and split one object with several links into several single-link objects.
*
* @param {any[]} rows
* @returns {Map<string, any>}
*/
const groupBySource = (rows) => {
/** @type {Map<string, any>} */
const sources = new Map();
for (const row of rows) {
const key = HEX2uuid(row.SourceUID);
let source = sources.get(key);
if (!source) {
source = {
UID: row.SourceUID,
type: row.SourceType ?? null,
title: row.SourceTitle ?? null,
/**
* Owner of the source object. For an `entry` this is the person, and
* `rebuildListEntries` is keyed by it — the entry itself cannot be rebuilt.
*/
UIDBelongsTo: row.SourceBelongsTo ?? null,
obStage: num(row.SourceStage),
obHierarchie: num(row.SourceHierarchie),
/** The source object is gone — its links can only be reported. */
orphaned: row.SourceType === null,
links: []
};
sources.set(key, source);
}
source.links.push({
/**
* `l.UIDTarget`, never `t.UID`: a dangling link has no `t` row, and the
* target UID is exactly what the removal has to match on.
*/
targetUID: row.LinkTargetUID,
targetType: row.TargetType ?? null,
targetTitle: row.TargetTitle ?? null,
targetStage: num(row.TargetStage),
targetHierarchie: num(row.TargetHierarchie),
validFrom: row.LinkValidFrom,
hasDynamic: Boolean(row.HasDynamic),
/**
* The object also holds a `member`-family link to this target — the tie
* breaker when the `ValidFrom` comparison cannot decide (see
* `memberALinkDecision.js`).
*/
corroborated: Boolean(row.TargetCorroborated),
dangling: !row.TargetExists
});
}
return sources;
};
/**
* Link shape for the report.
*
* The UID is rendered with `HEX2uuid`, which yields the canonical `UUID-…` form the
* API uses everywhere. The generated SQL column `TUIDTarget` must NOT be used for
* this: it renders the same bytes WITHOUT the `UUID-` prefix, so report and API
* would disagree on the format.
*
* @param {any} link
* @returns {any}
*/
const linkReport = (link) => ({
targetUID: HEX2uuid(link.targetUID),
targetTitle: link.targetTitle,
targetType: link.targetType,
targetStage: link.targetStage,
targetHierarchie: link.targetHierarchie,
validFrom: link.validFrom,
hasDynamic: link.hasDynamic,
/**
* The object also holds a `member`-family link to this target. Surfaced so a reader
* of the report can see WHY a `ValidFrom` tie was decided the way it was.
*/
corroborated: link.corroborated
});
/**
* Builds the report and, with `fix`, removes the surplus links.
*
* Exported separately from the HTTP handler so the same logic can be run against a
* database directly (dry run on a production copy) without going through the API.
*
* @param {{fix?: boolean}} [options]
* @returns {Promise<any>} the report
*/
export const buildMemberAConsistencyReport = async ({ fix = false } = {}) => {
const sources = groupBySource(await fetchMemberALinks());
const result = {
checked: sources.size,
/**
* Sources holding more than one ACTIVE `memberA` link to an EXISTING target —
* the invariant violation the migration trigger now prevents from reappearing.
*/
duplicates: /** @type {any[]} */ ([]),
/** Active `memberA` links pointing at an object that no longer exists. */
dangling: /** @type {any[]} */ ([]),
/**
* Links whose SOURCE object was deleted — deletion debris. Removed by the
* repair like the rest, but listed separately: a human scanning the report
* should not have to wonder why an object shows up that does not exist.
*/
linksOfDeletedObjects: /** @type {any[]} */ ([]),
/**
* Links the decision wants to remove — populated in BOTH modes, so a dry run
* shows the full impact before anything is written.
*/
wouldRemove: /** @type {any[]} */ ([]),
/** Removed links (fix only). */
removedLinks: /** @type {any[]} */ ([]),
/**
* The kept link matches no `ObjectBase.stage`/`hierarchie` — the versioned
* level is stale, or the kept link is the spurious one. Run
* `recreateObjects` afterwards; check the flagged objects first.
*/
flaggedObjectBase: /** @type {any[]} */ ([]),
/** `ObjectBase.stage = 0` AND `hierarchie = 0` — an uninitialised level row. */
needsRecreate: /** @type {any[]} */ ([]),
/**
* `entry` objects that carry the membership of SEVERAL dlists. Removing a link
* would drop the person from a list, so the repair does not touch them — the
* entry has to be SPLIT. `rebuildListEntries` (run by `fix`) gives every
* matching list its own entry. Listed in BOTH modes.
*/
listEntriesToRebuild: /** @type {any[]} */ ([]),
/** Outcome of the splits (fix only). */
rebuiltListEntries: /** @type {any[]} */ ([]),
/** Cases that need a human decision. */
skipped: /** @type {any[]} */ ([])
};
/**
* Persons whose entries were split during the fix run. Keyed by the canonical UUID
* string so several multi-list entries of the same person trigger ONE rebuild.
* Run after the loop: the rebuild rewrites the entry set, and the link removals
* above must not race with it.
* @type {Map<string, Buffer>}
*/
const personsToRebuild = new Map();
for (const source of sources.values()) {
const decision = decideMemberALinks(source);
assertNoInvariantRisk(source, decision);
const danglingLinks = source.links.filter((/** @type {any} */ l) => l.dangling);
const aliveLinks = source.links.filter((/** @type {any} */ l) => !l.dangling);
const reportBase = {
UID: HEX2uuid(source.UID),
type: source.type,
title: source.title,
objectBase: { stage: source.obStage, hierarchie: source.obHierarchie }
};
// ---- reporting -----------------------------------------------------
// The buckets partition cleanly: a source is either deleted, or it is live and
// then either holds duplicates or dangling links — never two of those at once.
if (source.orphaned) {
result.linksOfDeletedObjects.push({
...reportBase,
links: source.links.map(linkReport)
});
} else {
// Only more than one link to an EXISTING target is a duplicate; a dead link
// is reported separately as dangling.
if (aliveLinks.length > 1) {
result.duplicates.push({
...reportBase,
mode: source.type === 'entry' ? 'entry-dynamic' : 'newest-validFrom',
links: source.links.map(linkReport),
kept: decision.keep.map(linkReport)
});
}
if (danglingLinks.length > 0) {
result.dangling.push({ ...reportBase, links: danglingLinks.map(linkReport) });
}
if (source.obStage === 0 && source.obHierarchie === 0) {
result.needsRecreate.push({ ...reportBase, kept: decision.keep.map(linkReport) });
}
if (decision.keep.length > 0) {
const matches = decision.keep.some(
(/** @type {any} */ l) =>
l.targetStage === source.obStage && l.targetHierarchie === source.obHierarchie
);
if (!matches) {
result.flaggedObjectBase.push({
...reportBase,
kept: decision.keep.map(linkReport),
reason: 'ObjectBase level matches no kept link — recreate derived state or re-check the link'
});
}
}
}
if (decision.skipReason) {
// A multi-list `entry` is not a link problem: the person belongs to every one
// of those lists and merely shares one entry object, so no link may be
// removed. Reported on its own bucket and, with `fix`, split by
// `rebuildListEntries` after the loop. `skipped` stays reserved for cases
// that really need a human.
const splitEntry =
source.type === 'entry'
&& source.UIDBelongsTo
&& decision.skipReason.startsWith(ENTRY_SPLIT_REASON);
if (splitEntry) {
const personUID = HEX2uuid(source.UIDBelongsTo);
result.listEntriesToRebuild.push({
...reportBase,
personUID,
reason: decision.skipReason,
links: source.links.map(linkReport)
});
// `rebuildListEntries` is keyed by the person (Buffer, binary(16)).
if (fix) personsToRebuild.set(personUID, source.UIDBelongsTo);
} else {
result.skipped.push({
...reportBase,
reason: decision.skipReason,
links: source.links.map(linkReport)
});
}
continue;
}
// What the repair WOULD remove — visible in a dry run as well.
if (decision.remove.length > 0) {
result.wouldRemove.push({
...reportBase,
deletedSource: source.orphaned,
// The three counts partition `links`, each link counted exactly once and
// for its actual reason: a deleted owner, a dead target, or duplication.
orphanedCount: source.orphaned ? decision.remove.length : 0,
danglingCount: source.orphaned ? 0 : danglingLinks.length,
surplusCount: source.orphaned ? 0 : decision.remove.length - danglingLinks.length,
links: decision.remove.map(linkReport)
});
}
if (!fix) continue;
// ---- repair --------------------------------------------------------
for (const link of decision.remove) {
await removeMemberALink(source.UID, link.targetUID);
result.removedLinks.push({
...reportBase,
removed: linkReport(link),
kept: decision.keep[0] ? linkReport(decision.keep[0]) : null
});
}
}
// ---- entry splits ------------------------------------------------------
// Splitting rewrites the whole entry set of a person (creates entries, moves links),
// so it runs last — after every link removal — and once per person, no matter how
// many of that person's entries are multi-list.
for (const [personUID, personBuffer] of personsToRebuild) {
try {
await rebuildListEntries(personBuffer);
result.rebuiltListEntries.push({ personUID, success: true });
} catch (e) {
// `rebuildListEntries` logs and swallows its own errors; this is belt and
// braces so one failed split cannot abort the whole repair.
errorLoggerUpdate(e);
result.rebuiltListEntries.push({
personUID,
success: false,
error: String((/** @type {any} */ (e))?.message ?? e)
});
}
}
return result;
};
/**
* Report and (optionally) repair duplicate and dangling `memberA` links.
*
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const memberAConsistency = async (req, res) => {
try {
const fix = isFixRequested(req.query.fix);
const result = await buildMemberAConsistencyReport({ fix });
res.json({ success: true, fix, result });
} catch (e) {
errorLoggerRead(e);
errorLoggerUpdate(e);
res.status(500).json({ success: false, message: 'Internal server error' });
}
};