.gh-issue-4.md

Title: Replace hand-rolled Nested Set with a Closure Table for hierarchy (information_object / term / menu)

Labels: enhancement, performance, database

Description:

Heratio currently maintains AtoM's Nested Set model (lft / rgt / parent_id on information_object, term, menu) by hand, in Laravel. This carries the same O(n) write cost and fragility that the upstream AtoM project is actively moving away from, and it is already a real problem on our largest instance.

Context:

• Xercode has designed and benchmarked a Closure Table replacement for base AtoM 2.10 (Symfony 1.x / Propel), delivered as a xeClosureTablePlugin. Their performance report (2026-05-27) shows Nested Set becomes unviable for writes at ~30k–50k nodes: at 600k nodes a single leaf move takes ~100s and a 100-record import takes ~1 hour, versus sub-200ms / <10s with a closure table. Read parity is fine (point reads are sub-ms either way; full-descendant expansion is dramatically faster with closure).
• That plugin is Propel/Symfony-specific and cannot be used in Heratio (Laravel/Eloquent). But Heratio operates on the same AtoM MySQL schema and has re-implemented the same nested-set maintenance itself — so it inherits the identical problem and needs its own Laravel implementation.
• The instance that needs this most is a Heratio one: the atom/ANC instance (/usr/share/nginx/atom, DB atom) holds ~322,000 information_object rows — well past the ~30–50k viability threshold. Any large reorganisation / move / bulk import there hits the Nested Set wall today. (PSIS/archive, the only Symfony instance, is ~710 nodes and does not need this.)

Where Heratio currently depends on Nested Set:

• app/Jobs/ImportJob.php (~L509–557): on insert it reads the parent's rgt, then does a table-wide shift — ->where('rgt','>=',$parent->rgt)->increment('rgt',2) and the matching lft shift — i.e. the O(n) renumber. Also ~L729–740 uses rgt - lft + 1 for subtree width and whereBetween('lft',[lft,rgt]) for subtree selection.
• app/Console/Commands/ImportCsvCommand.php: --skip-nested-set-build option + a full nested-set rebuild path after import.
• app/Console/Commands/ExportCsvCommand.php:100: ->orderBy('io.lft') for hierarchical export order.
• app/Http/Controllers/HomeController.php:68,86: ->orderBy('menu.lft') for menu ordering.
• Any other modules/jobs/queries ordering or filtering by lft/rgt (full inventory required as step 1).

Proposal:

Note on the Xercode dependency: do NOT block on Xercode's final code. The closure table is a canonical pattern with almost no design freedom, and their design doc already specifies the schema concretely (below). It is also derived/regenerable data (rebuilt from parent_id by one command), so if Xercode's final naming ever differs, reconciliation is a trivial rename + rebuild — no data migration, nothing to lose. Proceed independently using the schema below; keep the table/column names aligned purely so a shared DB stays portable.

1. Use the canonical closure schema (matches Xercode's published design; do not invent a different shape):

   • Add information_object_closure, term_closure, menu_closure tables: (ancestor INT, descendant INT, depth INT), with the self-reference row (X, X, 0) as an invariant, UNIQUE(ancestor, descendant), indexes (ancestor, depth, descendant) and (descendant, depth), and FKs ON DELETE CASCADE to the base table.
   • Add a dedicated sibling-ordering column (e.g. sibling_order per parent) so we stop relying on lft for ordering. Treat lft/rgt as dead fields once closure is enabled (retain the columns for now to avoid breaking anything mid-migration).

2. Maintenance layer (Laravel):

   • On create / reparent: compute the node's ancestor chain (recursive CTE or set-based), delete its old descendant-side closure rows, insert the new chain with depths. Replace the increment('rgt',2) block in ImportJob entirely.
   • On move-subtree: closure-only update of the affected subtree (no global renumber).
   • On delete: rely on ON DELETE CASCADE.
   • Wrap closure writes in the same DB transaction as the model write so we never get a partial/inconsistent tree (the failure mode we're escaping).

3. Query points: replace orderBy('lft') with the sibling-order column; replace whereBetween('lft',[lft,rgt]) subtree reads with a closure JOIN (... JOIN information_object_closure c ON c.descendant = io.id WHERE c.ancestor = :id); ancestors via the reverse closure query.

4. Bulk build / migration: provide an artisan command to (re)build the closure tables from parent_id. Use a set-based build (seed depth-0, then iteratively INSERT depth+1 by joining the closure to parent edges) — far faster than per-record CTE (Xercode's unoptimised per-record build is ~1,000 rows/30s = ~5h for 600k; set-based should be minutes). This is the command that migrates the 322k atom/ANC instance.

5. OpenSearch move-reindex: Heratio uses the same denormalised ancestors field, so moving a subtree forces reindexing all descendants. Recommended approach — a single async _update_by_query that selects the subtree by term: ancestors = N and applies the ancestor delta (remove old prefix, prepend new prefix) in a Painless script, instead of looping/recomputing per document. Keeps the ancestors field intact (so aggregations/ACL filters are unaffected) and turns an O(S) reindex into one bulk op.

Schema-coordination caveat:

• dam and atom run Heratio on AtoM-schema databases. If a closure table is introduced and a DB is ever shared/migrated between the Symfony (PSIS) and Laravel (Heratio) stacks, both sides must agree on the closure layout, the sibling-order field, and lft/rgt being inert — otherwise they silently diverge. Keep the schema identical to Xercode's.

Acceptance criteria:

• Closure tables exist with the indexes/FKs above; a build command regenerates them from parent_id and runs in minutes (not hours) on the 322k atom instance.
• Insert / move / delete of descriptions no longer perform a table-wide lft/rgt renumber; closure writes are transactional with the model write.
• Hierarchical reads (export order, menu order, subtree expansion, ancestors, breadcrumbs) use the sibling-order column and the closure JOINs and return identical results to the current Nested Set output.
• A subtree move triggers a single bulk _update_by_query for the OpenSearch ancestors field rather than per-document reindexing.
• Functional parity verified on a copy of the atom/ANC (322k) dataset; move/import operations that previously took tens of seconds to minutes complete in well under a second / a few seconds.

References:

• Xercode "Design Analysis: Replacing the Nested Set Model with Closure Table in AtoM" (design doc) and "Complete Performance Report: Nested Set vs Closure Table" (2026-05-27). Available from AHG.
• Heratio files listed above (ImportJob.php, ImportCsvCommand.php, ExportCsvCommand.php, HomeController.php).