---
title: 'Replace flowchart NL pipeline with per-graph mermaid embedding'
type: 'refactor'
created: '2026-05-20'
status: 'implemented'
baseline_commit: '006d8693bbd848b1f13ed57fb66a51dd135ff363'
context: []
---

<frozen-after-approval reason="human-owned intent — do not modify unless human renegotiates">

## Intent

**Problem:** `_detect_and_extract_flowchart` in `excel_parser_service.py` (and its sibling `_process_flowcharts` in the legacy `document_converter.py` Excel path) condenses every detected flowchart into one natural-language description via `FlowchartDetector` + `FlowchartExtractor`, writes it into a single cell, and (in the v2 path) emits a separate `Flowchart` `ChunkedElementDTO`. This collapses multi-graph sheets, hides graph structure from retrieval, ships a parallel chunk schema the downstream hierarchical markdown chunker does not see, and keeps three obsolete dev scripts and two stale modules alive solely to feed it.

**Approach:** Replace the path with the per-graph mermaid pipeline already prototyped in `scripts/excel_to_mermaid.py`. For each worksheet, detect every connected component as its own graph, convert each to mermaid, and embed the fenced ```mermaid``` block into the top-left cell of that graph's location range before deleting underlying shapes. The new module's component-size filter (≥2 nodes per component) absorbs the `FlowchartDetector` "is this worth processing?" gate, so the old detector and extractor modules can be deleted outright along with the three dev scripts that drive them, and the legacy `_process_flowcharts` Excel branch in `document_converter.py` is stripped (Word/PDF/PPTX paths untouched).

## Boundaries & Constraints

**Always:**
- Detect graphs per sheet (multi-graph sheets each produce their own mermaid block in their own cell).
- Wrap mermaid in a fenced code block (```` ```mermaid ... ``` ````) so downstream chunking preserves it.
- Delete `xdr:sp` and `xdr:cxnSp` from drawing XML for any sheet where graphs were extracted (same ZIP/XML mutation skeleton as today's `_delete_shapes_and_insert_description`).
- Clear worksheet cells whose text was mined as edge-label content (the `consumed_cells` list returned by `_sheet_to_graphs`) so that text doesn't leak into the parsed markdown alongside the mermaid block as duplicate content. Clearing strips `<v>`/`<is>` and the `t` attribute but keeps the `<c>` element + style.
- Both `parse_excel_file` and `parse_to_markdown` in `excel_parser_service.py` go through the new path (they share `_detect_and_extract_flowchart`).
- If no graphs come back for a sheet, that sheet is untouched (no mutation, no error).
- All mermaid conversion logic lives under `src/` — `scripts/excel_to_mermaid.py` re-imports from it but keeps its CLI behavior.
- Use the new module's own emptiness check (any sheet has ≥1 returned `GraphResult`) as the gate. No external detector class.

**Ask First:**
- Changing the public return shape of `parse_to_markdown` (the `flowchart_chunks` key — keep it, set to `[]`).
- Removing the `_convert_excel_to_html` legacy Excel branch from `document_converter.py` entirely. (This spec only strips the flowchart bits inside it; broader cleanup is out of scope.)

**Never:**
- Emit `ChunkedElementDTO(element_type="Flowchart")` — mermaid is delivered as inline markdown only.
- Import from `scripts/` in production code.
- Mutate the original Excel input bytes; work on the temp copy as today.
- Touch `airflow/dags/tasks/processing_tasks.py`. Chunker-side unification + mermaid-fence-as-atomic-leaf extraction is deferred (see `deferred-work.md`).
- Touch Word/PDF/PPTX code paths in `document_converter.py`. Only the Excel-only `_process_flowcharts` method, its single call site, and its private helper come out.
- Add a CLI entry point in the new module (the existing script keeps that role).

## I/O & Edge-Case Matrix

| Scenario | Input / State | Expected Output / Behavior | Error Handling |
|---|---|---|---|
| Single flowchart, one sheet | One connected component spanning `A12:M30` | Mermaid fenced block written into cell `A12`; shapes/connectors stripped from drawing XML | N/A |
| Multiple graphs, one sheet | Two disconnected components (main flowchart + mini sub-graph at `I50:AA55`) | Two separate mermaid blocks, each in its location range's top-left cell | N/A |
| Multi-sheet workbook, mixed | Sheet A has flowchart, Sheet B has none | Sheet A mutated per above; Sheet B untouched | N/A |
| No flowchart detected | `extract_mermaid_graphs` returns all-empty per sheet | File passed through unchanged; no extra chunks; log "no flowchart" | N/A |
| Sheet with cell-resolved edge labels | Connectors carry no text but worksheet cells near connector lines hold labels (e.g. `OK`/`NG`) — `_extract_cell_edge_labels` consumes them | Mined labels appear inside the mermaid block; source cells are cleared (`<v>`/`<is>` + `t` removed, `<c>`+style retained) so the same text does not also appear as a standalone table row in the parsed markdown | N/A |
| Mermaid conversion error on one sheet | One sheet raises during graph→mermaid | Other sheets still get their mermaid embedded; failing sheet has shapes deleted but no mermaid inserted | Catch and warn; do not abort the parse |
| `parse_to_markdown` flow | Excel with flowchart | Returned `sheets[].markdown` contains the mermaid block(s); `flowchart_chunks` is `[]` | N/A |
| Legacy `DocumentToHtmlConverter._convert_excel_to_html` | Same Excel file goes through v1 path post-strip | Conversion completes; HTML output has no flowchart description (preprocessing step 3/6 logs "skipped — flowchart preprocessing removed") and no ImportError | N/A |

</frozen-after-approval>

## Code Map

- `src/extraction_v2/excel_parser_service.py` -- `_detect_and_extract_flowchart` (called from both parse paths), `_delete_shapes_and_insert_description`, `_create_flowchart_chunk`. Target of the v2 rewrite.
- `src/extraction_v2/document_converter.py` -- legacy Excel path. Strip `_process_flowcharts` (lines 664–742), its call site in `_preprocess_with_border_detection` (lines 348–355), and its private helper `_delete_shapes_and_insert_description` (line 744+).
- `scripts/excel_to_mermaid.py` -- source of the new pipeline. Logic to be promoted into `src/`.
- `src/extraction_v2/connector_parser.py` -- already imported by the script; provides `ConnectorParser.parse_by_sheet`, `SheetDrawing`. No change.
- `src/extraction_v2/flowchart_detector.py`, `src/extraction_v2/flowchart_extractor.py` -- DELETE outright.
- `scripts/preprocess_with_flowcharts.py`, `scripts/run_flowchart_pipeline.py`, `scripts/debug_preprocessing.py` -- DELETE outright (their reason to exist is gone; `scripts/excel_to_mermaid.py` covers the developer workflow).

## Tasks & Acceptance

**Execution:**
- [x] `src/extraction_v2/mermaid_flowchart_extractor.py` -- new module. Move pure-logic functions from `scripts/excel_to_mermaid.py` (`GraphResult`, `_sheet_to_graphs`, `_detect_components`, `_component_bbox_emu`, `_emu_bbox_to_range`, `_compute_grid`, `_extract_cell_edge_labels`, `_build_nodes`, `_build_node_groups`, `_clean_shape_text`, supporting constants). Expose `extract_mermaid_graphs(xlsx_path) -> Dict[str, Tuple[List[GraphResult], List[str]]]` keyed by sheet name; the tuple is `(graphs, consumed_cell_coords)`. No CLI, no `argparse`.
- [x] `scripts/excel_to_mermaid.py` -- delete moved function bodies; re-import them from `src.extraction_v2.mermaid_flowchart_extractor`. CLI behavior unchanged.
- [x] `src/extraction_v2/excel_parser_service.py` -- rewrite `_detect_and_extract_flowchart`: call `extract_mermaid_graphs(file_path)`; if every sheet returns an empty graph list, return `None`; otherwise build `embeddings_by_sheet` AND `consumed_by_sheet`, call new helper `_embed_mermaid_and_delete_shapes(..., consumed_by_sheet=...)`, and return `(modified_file_path, sheets_with_flowcharts: List[str])`. Drop the `flowchart_data` dict and the `FlowchartDetector`/`FlowchartExtractor` imports.
- [x] `src/extraction_v2/excel_parser_service.py` -- rewrite `_delete_shapes_and_insert_description` into `_embed_mermaid_and_delete_shapes(input_path, output_path, embeddings_by_sheet: Dict[str, List[Tuple[str, str]]], consumed_by_sheet: Dict[str, List[str]])` where the embeddings value is `(cell_ref, mermaid_text)` pairs and `consumed_by_sheet` lists cell coords to clear. Reuse the existing ZIP/XML mutation skeleton; the per-sheet pass clears consumed cells **before** the embedding loop so a freshly cleared cell is reusable as an embed target.
- [x] `src/extraction_v2/excel_parser_service.py` -- remove `_create_flowchart_chunk` and its two call sites. Set `flowchart_chunks = []` in `parse_to_markdown`'s return dict to preserve the public key.
- [x] `src/extraction_v2/document_converter.py` -- delete `_process_flowcharts` method, its call+log block in `_preprocess_with_border_detection` (the "Step 3/6: Detecting and processing flowcharts" stanza), and the private `_delete_shapes_and_insert_description` helper. Renumber "Step N/6" log lines if desired (cosmetic).
- [x] `src/extraction_v2/flowchart_detector.py`, `src/extraction_v2/flowchart_extractor.py` -- `git rm`.
- [x] `scripts/preprocess_with_flowcharts.py`, `scripts/run_flowchart_pipeline.py`, `scripts/debug_preprocessing.py` -- `git rm`.
- [x] `tests/extraction_v2/test_excel_parser_flowchart.py` -- new (or extend existing) — cover the I/O Matrix scenarios using the repo-root sample files as fixtures (`振込予定年月日算出.flowchart.xlsx`, `ネットキャッシング_概要図.flowchart.xlsx`) plus a synthetic no-flowchart workbook.

**Acceptance Criteria:**
- Given an Excel file with one multi-graph sheet, when `parse_to_markdown` runs, then that `sheets[].markdown` contains N fenced ```mermaid``` blocks (one per detected graph) and `flowchart_chunks` is `[]`.
- Given an Excel file with no flowcharts, when either parse path runs, then output equals a hypothetical run with flowchart detection disabled — no spurious mutations, no log errors.
- Given an Excel file whose connectors carry no text but whose worksheet cells near connector lines hold labels (e.g. `OK`/`NG`), when parsing, then the labels appear inside the embedded mermaid block AND do not appear as standalone table-cell rows in the parsed markdown; non-label body text (multi-line cells, cells > 30 chars, decorative headers) remains intact.
- Given an Excel file where one sheet's mermaid conversion raises, when parsing, then other sheets still get their mermaid embedded and the parse completes; the failing sheet has shapes deleted but no mermaid inserted, and a warning is logged.
- Given a `git grep -E "(FlowchartDetector|FlowchartExtractor|flowchart_detector|flowchart_extractor)"` after the change, when run against the repo, then it returns zero hits — no caller is left behind.
- Given the legacy `DocumentToHtmlConverter._convert_excel_to_html` path on an Excel file with shapes, when invoked, then conversion completes without ImportError and the resulting HTML simply lacks the old flowchart NL description (shapes still flow through the remaining steps unchanged).
- Given the repo-root sample files (`振込予定年月日算出.flowchart.xlsx`, `ネットキャッシング_概要図.flowchart.xlsx`), when running the parser, then the embedded mermaid matches `scripts/excel_to_mermaid.py` output byte-for-byte on the same inputs.

## Verification

**Commands:**
- `uv run python -c "from src.extraction_v2.mermaid_flowchart_extractor import extract_mermaid_graphs; out = extract_mermaid_graphs('振込予定年月日算出.flowchart.xlsx'); print(sum(len(graphs) for graphs, _ in out.values()), sum(len(consumed) for _, consumed in out.values()))"` -- expected: `11 112` (11 graphs, 112 consumed cells on the JP sample).
- `uv run python scripts/excel_to_mermaid.py 振込予定年月日算出.flowchart.xlsx --json` -- expected: same 11-graph JSON sidecar as before refactor.
- `git grep -E "FlowchartDetector|FlowchartExtractor|flowchart_detector|flowchart_extractor"` -- expected: no matches.
- `uv run pytest tests/extraction_v2/ -k flowchart` -- expected: new I/O matrix tests pass.

**Manual checks:**
- Open the post-parse temp xlsx in LibreOffice: top-left cell of each detected graph's range contains a ```` ```mermaid ```` block; original shapes are gone; other sheets untouched.