Import Pipeline

Verified against the code · 4996d1ceb770fb4545edebc5fd140792bfb23830 · 2026-10-20

956 D1b-4 (2026-10-20): 6 finding(s) fixed — the relation-import uuid is derive_overlay_relation_uuid(idem_key) with relation_import_idem_key(file_hash, base_id, rowIdx), shared by router and engine; the on-disk target is a single eidos:target (node IRI / ExternalRef{domain,ref} with no placeholder written / UnresolvedRef{eidos:ref}), toNodeId/unresolvedTargetRef are legacy-read only; no re-keying when an endpoint resolves; parse_excel/normalize_paths still exist (only the routes and excel* helpers were retired); the result status counts committed>0 OR enriched>0 (BL-IE-018 back-fill); the graph engine path is backend/domain/graph_crud_engine.py. Re-verified against main 4996d1ce.

flowchart LR
%% generated_from: import-pipeline
%% verified_at_commit: 4996d1ceb770fb4545edebc5fd140792bfb23830
%% description: Import Pipeline lifecycle
%% legend: aw=atomic-write, du=deterministic-uuid, owoc=overlay-wins-on-conflict, pbc=preview-before-commit
  subgraph api
    parse-excel["Parse Excel · pbc"]
  end
  subgraph domain
    evaluate-commands["Evaluate Commands · du"]
    commit-batch["Commit Batch · aw du pbc"]
    cache-invalidation["Cache Invalidation · owoc"]
  end
  subgraph frontend
    user-confirmation["User Confirmation · pbc"]
  end
  parse-excel --> evaluate-commands
  evaluate-commands --> user-confirmation
  user-confirmation --> commit-batch
  commit-batch --> cache-invalidation

Retired routes (hærdning 3.4.1 / #588). The original bare-AddFromExcel HTTP routes POST /mutations/import/excel/preview and .../commit — and their helpers _excel_parse_and_setup / _excel_evaluate_commands — were retired. (The pure parse_excel / normalize_paths functions still exist in backend/api/excel_importer.py, lines 45 and 624.) The live import surface is the product-node import, POST /mutations/import/excel/nodes/preview and .../nodes/commit. This chapter documents that live pipeline.

Overview — Excel as a Graph Import Format

The import pipeline converts a user-supplied .xlsx spreadsheet into a batch of new graph nodes persisted as overlay files. It is the primary mechanism for bulk-populating a branch of the EIDOS hierarchical graph without requiring the user to create nodes one at a time through the CRUD UI.

Each sheet row declares one node by its Reference path — a dot-separated string of segment labels (UCA10.QAB01.K02) that encodes the node's position in the tree — plus an optional Description, an optional product NodeType, and an optional Note. A dot-separated path implicitly declares its intermediate ancestors as well as the leaf, and the pipeline materialises any missing ancestors automatically.

Why Excel?

Excel is the lowest-friction format for the engineers and architects who own the source data. They already maintain hierarchical reference structures (subsystem breakdowns, functional trees) in spreadsheets. The format encodes hierarchy implicitly through dot notation: UCA10.QAB01.K02 means a leaf node K02 whose parent is QAB01, whose parent is UCA10. This matches the graph's own reference convention, so the import format and the graph's internal path representation are the same language.

A critical design decision is that the import is pure on the read side. The module backend/api/excel_importer.py performs no I/O and has no side effects — parse_product_node_rows turns bytes into row dicts. All writes are performed exclusively through the graph engine (backend/domain/graph_crud_engine.py) after evaluation returns its data structures, so the parse logic can be unit-tested without a running server.

---

Stage 1: File Upload and Parse

HTTP surface

The import reaches the server through two endpoints defined in backend/api/mutations_excel_router.py:

• POST /mutations/import/excel/nodes/preview — handled by mutations_import_product_nodes_preview
• POST /mutations/import/excel/nodes/commit — handled by mutations_import_product_nodes_commit

Both endpoints accept a multipart/form-data body containing:

Field	Type	Description
file	UploadFile	The .xlsx binary payload
anchorNodeId	str (Form)	UUID of the graph node under which top-level paths will be placed
anchorProductType	str (Form, optional)	Explicit product-type for the anchor; falls back to the anchor's stored type, then the default root type
sheet	str (Form, optional)	Worksheet name to read; defaults to the active sheet
fuseRoot	bool (Form, default false)	BL-IE-023 — collapse each row's first Reference segment onto the anchor

Both endpoints are write-guarded. _require_mutations() blocks imports when the server is in read-only mode. The route's IamScope (via scope: IamGuard) enforces the node-scoped write gate — scope.require_write(anchorNodeId) blocks viewer-role users from importing (BL-IAM-008, hærdning 3.2). The commit endpoint additionally calls ctx.require_write() through a MutationContext, which enforces row-level write permissions.

parse_product_node_rows

def parse_product_node_rows(
    file_bytes: bytes, sheet_name: str | None = None, *, require_node_type: bool = True,
) -> tuple[list[dict], str, list[dict]]

Returns (rows, file_hash, parse_errors) where each row is {"path": str, "path_lower": str, "description": str, "node_type": str, "note": str}.

• file_hash is the hex-encoded SHA-256 of the raw bytes, computed before the workbook is opened. Because it is taken over the raw bytes (not the parsed content), it is stable across re-uploads of the same file regardless of the active sheet, and it is available even if parsing subsequently fails. It becomes the basis for the idempotency key in later stages.
• Column discovery is case-insensitive: the header row is lowercased before the Reference / Description / NodeType / Note indices are located.
• require_node_type is passed as server_mod._DOMAIN_USES_NODE_TYPES. Product domains require a canonical NodeType on every row. Non-product domains (Location +, Signal ;, Type %, Discipline &) pass require_node_type=False: the NodeType column becomes optional, any value present is ignored, and every row's node_type is "". This lets a Locations export round-trip through the same single canonical parser (Contract 02.01: no forked location parser).

Any non-OOXML payload (wrong type, corrupt, or truncated archive) is wrapped inside the parser as a domain ValueError, which the endpoints convert to a 400 PARSE_ERROR — never an uncaught HTTP 500 (BL-IE-035, Contract 05 R5/R13/R31). No openpyxl/zipfile class name leaks into the response.

After parsing, the endpoint reads the effective graph and, if anchorNodeId is not present in it, returns a 400 ANCHOR_NOT_FOUND before any evaluation.

---

Stage 2: Anchor Fuse and Type Resolution

fuse_anchor_first_segment (opt-in template fuse)

def fuse_anchor_first_segment(rows: list[dict]) -> tuple[list[dict], list[str]]

When fuseRoot is set (BL-IE-023 — the default in the import dialog), each row's first Reference segment is collapsed onto the anchor: a template that models a reusable subtree can be dropped under any selected anchor. The rule is strip the first segment of every relative path and join the remainder onto the anchor; a single-segment relative path (the template root) maps onto the anchor itself and is dropped. A path whose first character is an ISO 81346 domain/aspect sigil (=, -, +, %) is absolute and is left untouched. The function also returns the distinct first segments so the preview can warn (MULTI_ROOT) when a sheet fuses more than one template root onto a single anchor.

Preview and commit apply the identical fuse so the committed tree is byte-identical to the previewed one (CCR 16).

Tree prefix and anchor product-type

The endpoint resolves tree_root_id = mutation_engine.find_subtree_root(anchorNodeId, eff) and derives a tree_prefix from the leading non-alphanumeric characters of the root label (the domain sigil), so imported labels stay consistent with the existing tree's convention.

For product domains the anchor's effective product-type is resolved in order: the explicit anchorProductType form value (mapped through _CANONICAL_NODE_TYPES), then the anchor's stored productType, then _PRODUCT_ROOT_DEFAULT_TYPE (Functional System). For non-product domains the resolved type is simply the anchor's stored type or blank.

---

Stage 3: Two-Pass Evaluation and UUID Derivation

After parsing, _product_nodes_evaluate_commands (in backend/api/mutations_excel_router.py) performs a speculative evaluation pass — it validates every row against the current effective graph without performing any writes. It deep-copies the effective graph into eff_local and, as each valid row passes, augments eff_local with the planned node so later rows in the same batch can see parents declared earlier in the same file.

def _product_nodes_evaluate_commands(
    rows, file_hash, anchor_node_id, anchor_product_type,
    tree_prefix, tree_root_id, username, eff, assignments, *, enforce_node_types=True,
) -> tuple[list[dict], dict, list[str] | None, dict]

• Pass 1 builds a file-level path_lower → node_type map (so out-of-order rows resolve their parents) and pre-seeds path_uuid_map / path_type_map by walking the anchor's existing subtree (a BFS over children_of, stripping the tree prefix).
• Pass 2 processes rows topologically — shallower paths first (sorted by segment-dot count, then alphabetically) — so a child is always evaluated after its parent.

For a product domain with an untyped anchor, the evaluator computes anchor_type_choices: the set of product-types the anchor could take that is compatible with every top-level row (via _PRODUCT_VALID_PARENT_FOR), which the dialog offers the user.

UUID derivation

For each valid new node the UUID is computed deterministically:

node_uuid = mutation_engine.derive_entity_uuid(cmd, parent_context=parent_id)

The deterministic-uuid invariant (CCR 16) guarantees that the same (command, parent_context) always produces the same UUID. Preview and commit therefore produce identical UUIDs with no shared in-memory state, and re-running a commit after a partial failure is safe.

Per-row status and error codes

Each row becomes a command with status new, skipped, or invalid:

Code	Status	Meaning
PARENT_NOT_IN_TREE_OR_FILE	invalid	The parent path is neither in the existing tree nor declared earlier in the file
NODE_TYPE_CASCADE_VIOLATION	invalid	Product domain only — node_type is not an allowed child of the parent's type (_PRODUCT_CHILD_TYPES)
NODE_LABEL_DUPLICATE	skipped	A sibling with the same label stem already exists; its UUID is reused so the subtree below it can still be placed
ALREADY_COMMITTED	skipped	An idempotency record from a prior import resolves to a node already in the graph

NODE_LABEL_DUPLICATE uses stem-based detection: labels that differ only in a leading sigil character (CTP01 vs -CTP01) are treated as the same node, so re-importing a file that partially overlaps existing content places the rest of the subtree under the existing parent.

---

Stage 4: Idempotency

Idempotency key for node rows

The key for a node row is constructed inline in _product_nodes_evaluate_commands (and re-derived identically in the commit route):

idem_key = f"excel-nodes-{file_hash[:16]}-{anchor_node_id[:8]}-{path_lower}"

Component	Source	Purpose
file_hash[:16]	SHA-256 of raw bytes, first 16 hex chars	Ties the key to the specific file content
anchor_node_id[:8]	First 8 chars of the anchor node's UUID	Distinguishes imports of the same file under different anchors
path_lower	Lowercased dot-separated path	Identifies the specific row within the file

The anchor dimension is critical for template reuse: importing the same template file under node HG20 and later under HG21 produces distinct keys, so the second import is not skipped as already-committed.

On-disk record and orphan handling

When a node is committed, mutation_store.write_idempotency writes a JSON-LD record to {mutations_dir}/idempotency/{idem_key}.jsonld whose eidos:entityId field holds the created node's UUID. On re-import, the evaluator reads the record via mutation_store.get_idempotency_record(idem_key):

• If eidos:entityId resolves to a node present in the effective graph, the row is skipped (ALREADY_COMMITTED) and its UUID is reused so children resolve their parent.
• If the record exists but the node is absent from the graph (an orphaned record — overlay file missing or UUID mismatch), the evaluator falls through to stem-based duplicate detection and re-creates the node.

Re-importing an unchanged file therefore writes no duplicate nodes and raises no errors: every row resolves to skipped, and the preview reports toCreate: 0.

---

Stage 5: Write

The commit endpoint mutations_import_product_nodes_commit runs the same parse-and-evaluate pipeline as preview, then iterates the command list and writes every status == "new" command. A skipped row whose existing node has an empty Description/Note is back-filled via update_node (fill-on-empty, BL-IE-018) and counted as enriched.

Row-by-row write loop

Each write goes through graph_crud_engine.create_node, which derives the deterministic UUID, writes the overlay node file atomically via write_atomic (fsync + rename — the atomic-write invariant), writes the idempotency record, and writes a snapshot record. The lock_fn=_get_subtree_lock serialises concurrent writes to the same subtree.

Writes are wrapped in an engine batch: begin_batch() before the loop and end_batch() after it coalesce every per-row cache invalidation into a single invalidation on batch close, and only if at least one node actually changed (the engine owns invalidation — the route makes no _invalidate_* call, hærdning 2.4). skipped and invalid rows do not abort the batch; their errors are accumulated and the loop continues, so a partially-valid file writes all valid rows and reports the rest.

Result codes

status	Condition
"complete"	No errors, and at least one node committed or enriched
"partial"	Some nodes committed or enriched, some errors
"failed"	Zero nodes committed and zero enriched (all skipped or invalid)

(backend/api/mutations_excel_router.py:749: _did = committed > 0 or enriched > 0.)

Committed nodes carry origin="excel-import" in their overlay file, so audit queries can distinguish bulk-import nodes from single-node CRUD.

---

Format Reference

Product-node import sheet

Excel column	Required	Graph field	Notes
Reference	Yes	Node reference path	Dot-separated; case-preserved for display, lowercased for deduplication and key derivation
Description	No	node.properties.description	Empty string if column absent or cell blank
NodeType	Product domains only	node.productType	Canonical product-type (Functional System / Technical System / Component); ignored (blank) for non-product domains
Note	No	row note	Always present on the row dict; empty when the column or cell is absent

Column discovery is case-insensitive (the header row is lowercased before lookup), so REFERENCE, reference, and Reference are all valid.

Example row and resulting graph nodes

Given the Excel row UCA10.QAB01.K02 | Cooling capacity monitor | Component, imported under an anchor that is the parent of UCA10, three nodes are created parents-first — UCA10, UCA10.QAB01, then UCA10.QAB01.K02 (label K02, description Cooling capacity monitor). The idempotency key for the leaf is excel-nodes-{sha256[:16]}-{anchorId[:8]}-uca10.qab01.k02, and each overlay file is written atomically and becomes visible in the effective graph after the batch's single invalidate().

---

Relation import — EXTERNAL / UNRESOLVED rows (G4, #931)

The relation importer (mutations_io_router.mutations_import_relations_commit → graph_crud_engine.create_relation) handles three target shapes:

Row status	Target channel	targetId passed to the engine	On-disk eidos:target (WP4b #965)
OK	in-model (local graph node)	resolved node UUID	node IRI
EXTERNAL	cross-domain (another backend owns the target)	stable UUID v5 placeholder from derive_relation_uuid_for_unresolved_target(source, ext_ref, predicate)	eidos:ExternalRef {domain, ref} — placeholder NOT written; the reader re-derives it
UNRESOLVED	deferred local (target node not yet in the graph)	empty string ("")	eidos:UnresolvedRef {eidos:ref}

For UNRESOLVED rows the target uuid stays empty so consumers that test to / targetId for truthiness see "no target yet" (CCR 05). The human-readable local ref the user typed in the Excel sheet is carried on disk as the eidos:UnresolvedRef node's eidos:ref (backend/domain/mutation_engine.py:738; the pre-WP4b eidos:toNodeId / eidos:unresolvedTargetRef keys are legacy-read only), and the preview/commit API carries it as targetPath / externalTargetRef on each row (tgtUuid is empty for UNRESOLVED).

The relation's OWN uuid is derive_overlay_relation_uuid(idem_key) (WP4 #953), with idem_key = relation_import_idem_key(file_hash, base_id, rowIdx) (backend/api/mutations_io_router.py:502/:555). The router passes the same idem_key to create_relation as its idempotency key, so engine and router agree on the on-disk id; the uuid no longer depends on source, ref or predicate. Distinct rows produce distinct relation files, re-imports of the same .xlsx under the same anchor land on the same file, and the planning pass's commit_existing_ext_rels set (built over loaded relations carrying either external_ref or unresolved_ref) treats a re-imported row as EXISTS and skips it.

Consequences:

• Idempotent re-import. Importing the same .xlsx under the same anchor produces identical relation UUIDs across runs. The commit path writes the same file on disk; skipped rows cover the second-and-subsequent runs.
• No shadow overlays. The former random UUID placeholder could produce two different relation files for the same (source, cross-domain-ref, predicate) across runs; G4 collapses this to one.
• No re-keying on resolution. The relation UUID is idem-key derived (WP4), not endpoint derived, so when an UNRESOLVED target later resolves to a real node the relation keeps its UUID (backend/infra/key_derivation.py:89: moving an endpoint does not change the UUID).

Preview and commit both carry the row's resolution state verbatim: tgtUuid for OK rows, the stable derived placeholder for EXTERNAL rows, and an empty tgtUuid + the human-readable externalTargetRef / targetPath for UNRESOLVED rows.

Stage Reference

Parse Excel

Engine: excel-importer — Zone: api

POST /mutations/import/excel/nodes/preview — read uploaded .xlsx bytes; parse_product_node_rows: extract Reference + Description (+ optional NodeType) rows, compute file_hash (SHA-256); optional BL-IE-023 anchor fuse collapses each row's first Reference segment onto the anchor

Invariants enforced: preview-before-commit

Evaluate Commands

Engine: excel-importer — Zone: domain

_product_nodes_evaluate_commands: two-pass topological validation against the EffectiveGraph — assign status new / skipped (already exists) / invalid, cascade node-type checking + anchor-type-choice computation; return command list with file_hash — no writes at this stage

Invariants enforced: deterministic-uuid

User Confirmation

Engine: frontend-crud — Zone: frontend

display preview summary (toCreate / skipped / invalid counts) and command list; user confirms before commit is allowed

Invariants enforced: preview-before-commit

Commit Batch

Engine: mutation-store — Zone: domain

POST /mutations/import/excel/nodes/commit — re-derive same commands deterministically from file_hash (CCR 16); call graph_crud_engine.create_node for each "new" command; write overlay files atomically via write_atomic (fsync + rename)

Invariants enforced: atomic-write, deterministic-uuid, preview-before-commit

Cache Invalidation

Engine: eidos-loader — Zone: domain

invalidate EffectiveGraph _cache after batch commit

Invariants enforced: overlay-wins-on-conflict