Import Pipeline¶

Verified — minor divergences from the code — 1 finding(s) · 19d408be · 2026-08-25

The described parse/normalize/evaluate/commit/cache-invalidation behaviour, function signatures (parse_excel, normalize_paths, fuse_anchor_first_segment, _excel_evaluate_commands, relation_import_idem_key), error codes (NODE_LABEL_DUPLICATE, PARENT_NOT_RESOLVED, ALREADY_COMMITTED), idempotency-key format, and write-atomic/cache-invalidation mechanics all match the code exactly. The one material gap is stale file attribution: the doc and the KM entity's sources.files repeatedly claim the two endpoints and the _excel_evaluate_commands/_excel_parse_and_setup helpers are 'defined in backend/server.py', but they were extracted into backend/api/mutations_excel_router.py on 2026-07-02 (after this doc's verified_at_commit of 2026-06-22). server.py now only includes the router and re-exports the two helper names for backward compatibility; it no longer contains their definitions.

Divergences from the code — details

Sev	Where	Doc says	Code does	Evidence
MEDIUM	Stage 1: File Upload and Parse — "HTTP surface" section, and Stage 3 header referencing `_excel_evaluate_commands` in `backend/server.py`	"The import reaches the server through two endpoints defined in backend/server.py" (and, further down, "`_excel_evaluate_commands` in `backend/server.py` performs a speculative evaluation pass"); KM entity sources.files lists only backend/api/excel_importer.py and backend/server.py.	The route handlers `mutations_import_excel_preview` / `mutations_import_excel_commit` and the helpers `_excel_parse_and_setup` / `_excel_evaluate_commands` are defined in backend/api/mutations_excel_router.py (extracted from server.py 2026-07-02, per that file's own module docstring). backend/server.py only does `app.include_router(_mutations_excel_router)` and re-exports the two helper names as module attributes for test-patching compatibility (server.py:1921-1934) — it no longer contains their implementation.	`backend/api/mutations_excel_router.py:96 (function def), :145 (function def), :502 (route def), :549 (route def); backend/server.py:1921-1934 (include + re-export only)`

flowchart LR
%% generated_from: import-pipeline
%% verified_at_commit: d50caf3143612a76bde48787104e90a4ac202ff3
%% 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

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.

The pipeline handles three categories of data:

Nodes — any dot-separated path in the Path column implicitly declares all intermediate ancestor nodes as well as the leaf node itself.
Properties — each leaf node may carry a Description string that is stored as the node's description property.
Relations — a separate relation-import path (parse_relation_rows / resolve_relation_rows) handles explicit edge declarations; that path is not described here. The node-import pipeline described in this document does not write relations.

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 pipeline imposes a minimal schema — one mandatory Path column plus an optional Description column — so most existing sheets can be imported with minor renaming.

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. All writes are performed exclusively in backend/server.py after the parse module returns its data structures. This separation means 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/server.py:

POST /mutations/import/excel/preview — handled by mutations_import_excel_preview
POST /mutations/import/excel/commit — handled by mutations_import_excel_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
`sheet`	`str` (Form, optional)	Worksheet name to read; defaults to the active sheet

Both endpoints are write-guarded. _require_mutations() blocks imports when the server is in read-only mode. _require_iam_write(username, anchorNodeId) blocks viewer-role users from importing (BL-IAM-008). The commit endpoint additionally calls ctx.require_write() through a MutationContext, which enforces row-level write permissions.

Both endpoints immediately read the entire file into memory with await file.read(), then delegate to _excel_parse_and_setup.

`_load_workbook_guarded`¶

def _load_workbook_guarded(file_bytes: bytes, **kwargs)

This is a thin wrapper around openpyxl.load_workbook. Its sole purpose is converting two openpyxl-specific exceptions — InvalidFileException (wrong MIME type or extension) and zipfile.BadZipFile (corrupt or truncated archive) — into a plain ValueError with the message "File is not a valid .xlsx spreadsheet.".

Without this guard, either exception would propagate uncaught past the import endpoint's except ValueError handler and surface as an HTTP 500. With the guard, both become a 400 PARSE_ERROR response (Contract 05 R5/R13/R31). This is the only place in the codebase that normalises openpyxl exceptions — do not add parallel exception handling in route handlers.

`parse_excel`¶

def parse_excel(file_bytes: bytes, sheet_name: str | None = None) -> tuple[list[dict], str]

Param	Type	Description
`file_bytes`	`bytes`	Raw `.xlsx` content
`sheet_name`	`str \\| None`	Worksheet to read; falls back to active sheet if name is unknown

Returns (rows, file_hash) where: - rows is a list of {"path": str, "description": str} dicts, one per non-empty data row - file_hash is the hex-encoded SHA-256 of the raw bytes, computed before the workbook is opened

The SHA-256 is computed over the raw bytes, not over the parsed content. This means the hash is stable across re-uploads of the same file regardless of which sheet is active, 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 "path" index is located. If there is no Path column, parse_excel raises ValueError("Excel file must contain a 'Path' column"). Rows where the Path cell is empty or whitespace-only are silently skipped. The Description column is optional; if absent, every row gets description: "".

The workbook is opened with read_only=True, data_only=True so formula results are returned rather than formula strings, and the file is closed before returning.

Stage 2: Path Normalization¶

What "path" means¶

In the EIDOS hierarchical graph, every node has a reference path — a dot-separated string of segment labels that encodes its position in the tree. The path UCA10.QAB01.K02 identifies a unique node by the ordered sequence of ancestor labels from the tree root down to itself.

When a user writes UCA10.QAB01.K02 in an Excel cell, they are declaring that the leaf node K02 should exist. But K02 can only exist if UCA10.QAB01 exists, and UCA10.QAB01 can only exist if UCA10 exists. The pipeline must materialise all intermediate nodes automatically.

`normalize_paths`¶

def normalize_paths(rows: list[dict]) -> list[dict]

Param	Type	Description
`rows`	`list[dict]`	Output of `parse_excel`; each dict has `path` and `description`

Returns a deduplicated, sorted list of path descriptors. Each descriptor has {"path": str, "path_lower": str, "description": str}.

The function expands every row into its full ancestor chain. Given the input row {"path": "UCA10.QAB01.K02", "description": "A leaf"}, the function emits three entries:

path	path_lower	description
`UCA10`	`uca10`	`""`
`UCA10.QAB01`	`uca10.qab01`	`""`
`UCA10.QAB01.K02`	`uca10.qab01.k02`	`"A leaf"`

Intermediate ancestors receive an empty description because the user did not explicitly describe them. Only the leaf path carries the description from the source row.

If two rows in the file share an ancestor (e.g. UCA10.QAB01.K02 and UCA10.QAB01.K03), the ancestor UCA10.QAB01 is emitted exactly once. Deduplication is keyed on the lowercased path, but the original-case path is preserved for display and label derivation.

The sort key is (depth, lexicographic), ensuring parents always appear before their children in the output list. This ordering is load-bearing: the commit stage processes rows in this order so that when a child is committed, its parent UUID is already known.

`fuse_anchor_first_segment`¶

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

This function is used when the user imports a template file — a file that models a reusable subsystem subtree. In a template, the first path segment is a placeholder for whatever anchor node the user selects in the import dialog, not a literal label to create.

Rule: strip the first segment of every relative (sigil-less) path and join the remainder onto the anchor.

Example — given anchorNodeId is the node for HG20, and the file contains:

Before fuse	After fuse
`TEMPLATE.CTP01.K02`	`CTP01.K02`
`TEMPLATE.CTP01.K03`	`CTP01.K03`
`TEMPLATE`	(dropped — single segment maps onto anchor itself)

The TEMPLATE root row is dropped because it maps onto the anchor node itself, which already exists. A single-segment relative path has nothing left after stripping the placeholder.

A path whose first character is an ISO 81346 domain/aspect sigil (such as =, -, +, %) is absolute and is left untouched. Absolute paths do not participate in anchor-relative fusing.

The function also returns distinct_first_segments so the caller can warn when the sheet contains more than one distinct first segment, which would mean multiple independent template roots are being fused onto a single anchor.

A badly-formed path vs a normalized one¶

A badly-formed path is one where: - A child row appears in the file without its parent row (e.g. only UCA10.QAB01.K02 is listed, but UCA10 and UCA10.QAB01 are absent). normalize_paths creates the missing ancestors automatically, so this is repaired, not rejected. - A path segment is blank after stripping (e.g. UCA10..K02). The split-and-filter logic in normalize_paths silently drops empty segments, so this would produce UCA10.K02 — a different node than intended. Developers must validate inputs upstream if blank segments are a concern. - The path cell contains only whitespace. parse_excel skips such rows entirely.

A normalized path list has the invariant: for every path A.B.C in the list, A and A.B also appear in the list and appear before A.B.C in iteration order.

Stage 3: Validation and UUID Derivation¶

After normalization, _excel_evaluate_commands in backend/server.py performs a speculative evaluation pass — it walks every normalized path and validates it against the current effective graph without performing any writes.

def _excel_evaluate_commands(normalized, file_hash, tree_prefix, anchor_node_id, tree_root_id, username, eff, assignments)

The function deep-copies the effective graph into eff_local at the start. As each path passes validation, the function adds the new node speculatively to eff_local. This means child validations in the same batch can see parents that were declared earlier in the same file but do not yet exist on disk. Without this speculative local graph, a two-level hierarchy in a single import file would have every non-root row fail with PARENT_NOT_RESOLVED.

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. This means:

Preview and commit produce the same UUIDs without any shared in-memory state.
Re-running the commit after a partial failure is safe: nodes that were already committed retain their UUIDs.
The UUID of a node created via import is not a random UUID4; it is derived from the command payload.

Validation rules¶

Each command is validated by mutation_engine.validate_add_node. The most common error codes that reach the import pipeline are:

Code	Meaning	Effect on batch
`NODE_LABEL_DUPLICATE`	A sibling with this label already exists	Row is `skipped`; its UUID is resolved from the existing node so children can still be placed under it
`PARENT_NOT_RESOLVED`	Parent path could not be matched (internal error in speculative graph)	Row is `invalid`; children of this row will also be `invalid`
`ALREADY_COMMITTED`	Idempotency record found from a prior import	Row is `skipped`

NODE_LABEL_DUPLICATE receives special treatment: rather than marking the row invalid, the evaluator looks up the existing sibling's UUID and adds it to path_uuid_map. This allows the rest of the subtree under a pre-existing parent to be created normally, which is the expected behaviour when a user re-imports a file that partially overlaps with existing graph content.

Stage 4: Idempotency Check¶

Idempotency key for node rows¶

The idempotency key for a node row is constructed inline in both _excel_evaluate_commands and mutations_import_excel_commit:

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

The key encodes three independent dimensions:

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 parents
`path_lower`	Lowercased dot-separated path	Identifies the specific row within the file

The anchor dimension is critical for template reuse. If the same template file is imported under node HG20 and later under HG21, the two imports must produce distinct idempotency keys so the second import is not skipped. Without the anchor component, all rows from the second import would be found in the idempotency store and skipped as already-committed.

For relation rows the analogous function is relation_import_idem_key:

def relation_import_idem_key(file_hash: str, base_node_id: str | None, row_idx) -> str:
    anchor = base_node_id or "root"
    return f"excel-rel-{file_hash[:16]}-{anchor}-row{row_idx}"

Relation keys use row_idx rather than the path string because relation rows are addressed by position in the file, not by a derived canonical path.

Idempotency record on disk¶

When a node is committed, mutation_store.write_idempotency writes a JSON-LD file to:

{mutations_dir}/idempotency/{idem_key}.jsonld

The file contains:

{
  "@context": "...",
  "@type": "eidos:IdempotencyRecord",
  "eidos:key": "excel-abc123...-deadbeef-uca10.qab01.k02",
  "eidos:entityId": "<uuid-of-created-node>",
  "eidos:result": "created",
  "eidos:createdAt": "2026-06-23T10:00:00Z",
  "eidos:payloadHash": "<hash>"
}

The eidos:entityId field is used during re-import: when _excel_evaluate_commands finds an existing idempotency record via mutation_store.get_idempotency_record(idem_key), it reads existing.get("eidos:entityId", "") and places that UUID into path_uuid_map. Children of this already-committed node can then resolve their parent correctly.

What happens on re-import of the same file¶

parse_excel computes the same file_hash from the unchanged bytes.
normalize_paths produces the identical normalized list.
_excel_evaluate_commands finds an existing idempotency record for every row's idem_key.
Every row is assigned status: "skipped" with error code ALREADY_COMMITTED.
The preview response reports toCreate: 0, skipped: N, invalid: 0.
If the user proceeds to commit, the commit loop skips every row and returns status: "failed" (zero committed, zero errors, all skipped).

No duplicate nodes are written and no errors are raised. The import is fully idempotent.

Stage 5: Write¶

The commit endpoint mutations_import_excel_commit runs the same parse-and-evaluate pipeline as preview, then iterates the command list and performs writes for every status == "new" command.

Row-by-row write loop¶

for cmd_info in commands:
    if cmd_info["status"] == "skipped":
        skipped += 1
        continue
    if cmd_info["status"] == "invalid":
        errors.append({...})
        skipped += 1
        continue
    # status == "new"
    payload = CreateNodePayload(
        label           = cmd_info["label"],
        description     = desc,
        idempotency_key = idem_key,
        origin          = "excel-import",
    )
    eng_result = await create_node(ctx, parent_id, payload, lock_fn=_get_subtree_lock)

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 atomically - Writes a snapshot record

The lock_fn=_get_subtree_lock serialises concurrent writes to the same subtree, preventing race conditions when two import operations target the same anchor simultaneously.

Per-row error handling without batch abort¶

Errors on individual rows do not abort the batch. The loop accumulates errors in errors: list[dict] and increments skipped for error rows, then continues to the next row. This means a partially-valid file writes all valid rows and reports invalid rows in the response body.

The origin="excel-import" field on CreateNodePayload is stored in the overlay file. It allows audit queries to distinguish nodes created by bulk import from nodes created through the single-node CRUD interface.

Result codes¶

`status_val`	Condition
`"complete"`	No errors, at least one node committed
`"partial"`	Some nodes committed, some errors
`"failed"`	Zero nodes committed (all skipped or invalid)

Cache invalidation¶

After at least one node is committed, mutation_store.invalidate() is called. This clears the in-memory EffectiveGraph cache so subsequent reads see the newly written nodes. If zero nodes are committed (pure re-import), invalidate() is not called, saving a cache rebuild.

Format Reference¶

Node import sheet (used by this workflow)¶

Excel column	Required	Graph field	Notes
`Path`	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

All other columns are ignored. Column discovery is case-insensitive (the header row is lowercased before lookup), so PATH, path, and Path are all valid.

Example row and resulting graph node¶

Given the Excel row:

Path	Description
`UCA10.QAB01.K02`	`Cooling capacity monitor`

After running through the full pipeline with anchor node <uuid-of-UCA10-parent>:

Three nodes are created (parents first):

UCA10 — label UCA10, description "", parent = anchor node
UCA10.QAB01 — label QAB01, description "", parent = UUID of UCA10
UCA10.QAB01.K02 — label K02, description "Cooling capacity monitor", parent = UUID of UCA10.QAB01

The idempotency key for the leaf node is:

excel-{sha256[:16]}-{anchorId[:8]}-uca10.qab01.k02

The overlay file for the leaf node is written atomically to the mutations directory and becomes immediately visible in the effective graph after mutation_store.invalidate() is called.

Stage Reference¶

Parse Excel¶

Engine: excel-importer — Zone: api

POST /mutations/import/excel/preview — read uploaded .xlsx bytes; parse_excel: extract Path + Description rows, compute file_hash (SHA-256); normalize_paths: sort and deduplicate path descriptors

Invariants enforced: preview-before-commit

Evaluate Commands¶

Engine: excel-importer — Zone: domain

_excel_evaluate_commands: for each normalized path, compare against EffectiveGraph to assign status new / skipped (already exists) / invalid; 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/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

Import Pipeline¶

Overview — Excel as a Graph Import Format¶

Why Excel?¶

Stage 1: File Upload and Parse¶

HTTP surface¶

_load_workbook_guarded¶

parse_excel¶

Stage 2: Path Normalization¶

What "path" means¶

normalize_paths¶

fuse_anchor_first_segment¶

A badly-formed path vs a normalized one¶

Stage 3: Validation and UUID Derivation¶

UUID derivation¶

Validation rules¶

Stage 4: Idempotency Check¶

Idempotency key for node rows¶

Idempotency record on disk¶

What happens on re-import of the same file¶

Stage 5: Write¶

Row-by-row write loop¶

Per-row error handling without batch abort¶

Result codes¶

Cache invalidation¶

Format Reference¶

Node import sheet (used by this workflow)¶

Example row and resulting graph node¶

Stage Reference¶

Parse Excel¶

Evaluate Commands¶

User Confirmation¶

Commit Batch¶

Cache Invalidation¶

`_load_workbook_guarded`¶

`parse_excel`¶

`normalize_paths`¶

`fuse_anchor_first_segment`¶