# Technical Specification: Extraction Pipeline V2

**Version:** 3.0
**Status:** Implemented
**Last Updated:** 2025-12-08
**Epic:** Epic 3 - Document Processing
**Story:** Multi-Format Document Extraction (Excel, Word, PDF, PowerPoint)

---

## Executive Summary

The Extraction Pipeline V2 is a Docling-based document processing system that extracts structured data from multiple document formats:
- **Excel** (.xlsx, .xls, .xlsm) → HTML → Table Records with LLM Header Detection
- **Word** (.docx, .doc, .docm) → Markdown → Semantic Chunks
- **PDF** (.pdf) → Markdown → Semantic Chunks
- **PowerPoint** (.pptx, .ppt, .pptm) → Markdown → Semantic Chunks

**Key Innovations:**
- Border detection preprocessing ensures accurate table identification in Excel files
- Large table extraction (>1000 rows) with chunked processing for performance
- Semantic chunking with HybridChunker for text-heavy documents
- LibreOffice concurrency control via file locking mechanism
- Fork-safe HTTP client initialization for Celery workers

**Primary Goal:** Accurately extract structured and unstructured data from diverse document formats with full source traceability and vector indexing for semantic search.

---

## Table of Contents

1. [Architecture Overview](#architecture-overview)
2. [System Components](#system-components)
3. [Data Flow](#data-flow)
4. [Component Specifications](#component-specifications)
5. [Data Models](#data-models)
6. [Processing Pipelines](#processing-pipelines)
7. [Error Handling & Fallback](#error-handling--fallback)
8. [Performance Characteristics](#performance-characteristics)
9. [Configuration & Dependencies](#configuration--dependencies)
10. [Testing Strategy](#testing-strategy)
11. [Known Limitations](#known-limitations)
12. [Future Enhancements](#future-enhancements)

---

## Architecture Overview

### High-Level Architecture

```
┌─────────────────────────────────────────────────────────────────────────────┐
│                    EXTRACTION PIPELINE V2 (Multi-Format)                     │
└─────────────────────────────────────────────────────────────────────────────┘

                           Document Upload
                                 ↓
                    ┌──────────────────────────────┐
                    │   File Type Detection        │
                    │   .xlsx/.xls/.xlsm  → Excel  │
                    │   .docx/.doc/.docm  → Word   │
                    │   .pdf              → PDF    │
                    │   .pptx/.ppt/.pptm  → PowerPoint │
                    └──────────────────────────────┘
                                 ↓
    ┌────────────────────────────┼───────────────────────────────┐
    │                            │                               │
    ↓                            ↓                               ↓
┌──────────┐            ┌──────────────┐              ┌────────────────┐
│  EXCEL   │            │  WORD/PDF    │              │  POWERPOINT    │
│ PIPELINE │            │  PIPELINE    │              │  PIPELINE      │
└──────────┘            └──────────────┘              └────────────────┘
    │                            │                               │
    ↓                            ↓                               ↓
┌──────────────────┐    ┌──────────────────┐          ┌──────────────────┐
│ Border Detection │    │ Preprocessing    │          │ Speaker Notes    │
│ Separator Insert │    │ (Optional)       │          │ Extraction       │
└──────────────────┘    └──────────────────┘          └──────────────────┘
    │                            │                               │
    ↓                            ↓                               ↓
┌──────────────────┐    ┌──────────────────┐          ┌──────────────────┐
│ Docling → HTML   │    │ Docling → MD     │          │ Docling → MD     │
└──────────────────┘    └──────────────────┘          └──────────────────┘
    │                            │                               │
    ↓                            │                               │
┌──────────────────┐             └───────────┬───────────────────┘
│ HTML Table       │                         │
│ Extraction       │                         ↓
└──────────────────┘             ┌──────────────────────────┐
    │                            │ Semantic Chunking        │
    ↓                            │ (HybridChunker)          │
┌──────────────────┐             │ - Token-aware splitting  │
│ LLM Header       │             │ - Image serialization    │
│ Detection (GPT-4o)│            │ - Table serialization    │
└──────────────────┘             └──────────────────────────┘
    │                                        │
    ↓                                        │
┌──────────────────┐                         │
│ Record Building  │                         │
│ with Source Meta │                         │
└──────────────────┘                         │
    │                                        │
    └────────────────────┬───────────────────┘
                         ↓
              ┌──────────────────────┐
              │   Milvus Vector      │
              │   Indexing           │
              └──────────────────────┘
                         ↓
              ┌──────────────────────┐
              │   Database Storage   │
              │   (PostgreSQL)       │
              └──────────────────────┘
```

### Technology Stack

| Component | Technology | Version | Purpose |
|-----------|------------|---------|---------|
| Document Conversion | Docling | 2.0+ | Multi-format document → HTML/Markdown |
| Legacy Format Support | LibreOffice CLI | 7.0+ | .xls/.xlsm/.doc/.docm/.ppt/.pptm conversion |
| Legacy Excel Support | pandas + xlrd | latest | .xls → .xlsx (fallback) |
| HTML Parsing | BeautifulSoup4 | 4.12+ | Extract tables from HTML |
| Parser Backend | lxml | 5.0+ | Fast HTML/XML parsing |
| Excel Manipulation | openpyxl | 3.1.5 | Preprocessing, border detection |
| Word Processing | python-docx | latest | Word preprocessing |
| PowerPoint Processing | python-pptx | latest | Speaker notes extraction |
| Header Detection | Azure OpenAI GPT-4o | latest | LLM-based header analysis |
| Semantic Chunking | docling-core HybridChunker | latest | Token-aware document chunking |
| Token Counting | tiktoken | latest | OpenAI tokenizer for embeddings |
| Vector Storage | Milvus | 2.0+ | Semantic search vectors |
| Logging | structlog | latest | Structured logging |

---

## System Components

### Component Hierarchy

```
ExtractionPipelineV2 (Orchestrator)
    │
    ├── DocumentToHtmlConverter
    │   ├── convert_to_html() [Router: Excel/Word/PDF/PowerPoint]
    │   ├── _convert_excel_to_html()
    │   │   ├── convert_xls_to_xlsx() [pandas+xlrd for .xls]
    │   │   ├── _convert_to_xlsx_via_libreoffice() [.xlsm]
    │   │   ├── _preprocess_with_border_detection()
    │   │   │   ├── _apply_image_descriptions()
    │   │   │   ├── _apply_shape_annotations()
    │   │   │   ├── _apply_comment_markers()
    │   │   │   └── BorderTableDetector.insert_separator_rows()
    │   │   └── DocumentConverter [Docling]
    │   ├── _convert_word_to_markdown()
    │   │   ├── _convert_doc_to_docx() [LibreOffice]
    │   │   ├── _convert_docm_to_docx_direct() [XML manipulation]
    │   │   └── DocumentConverter [Docling]
    │   ├── _convert_pdf_to_markdown()
    │   │   └── DocumentConverter [Docling with PDF options]
    │   └── _convert_pptx_to_markdown()
    │       ├── _convert_ppt_to_pptx() [LibreOffice]
    │       ├── PptxPreprocessor (speaker notes)
    │       └── DocumentConverter [Docling]
    │
    ├── HtmlTableExtractor
    │   ├── extract_tables()
    │   ├── extract_all_tables() [valid + raw_text classification]
    │   ├── _extract_rows_with_spans()
    │   └── _is_valid_table()
    │
    ├── LlmHeaderDetector
    │   ├── detect_headers()
    │   ├── _fix_misclassified_header_tags()
    │   ├── _create_column_based_headers()
    │   └── _create_fallback_headers_from_html()
    │
    ├── RecordBuilder
    │   ├── build_records()
    │   ├── build_raw_text_records()
    │   ├── build_combined_raw_text_records() [token-aware merging]
    │   ├── _build_record()
    │   └── _is_record_non_empty()
    │
    ├── SemanticChunker
    │   ├── chunk_document()
    │   ├── _build_chunk_record()
    │   └── ImgTableAnnotationSerializerProvider
    │       ├── AnnotationPictureSerializer
    │       └── MarkdownTableSerializer
    │
    ├── LargeTableExtractor
    │   ├── detect_tables()
    │   ├── is_large_table() [>1000 rows threshold]
    │   ├── extract_table()
    │   ├── _extract_header_rows_as_html()
    │   ├── _extract_normalized_headers()
    │   ├── _process_data_rows() [chunked iter_rows]
    │   └── to_pipeline_records()
    │
    └── Supporting Components
        ├── WordPreprocessor
        │   ├── preprocess()
        │   ├── _extract_images()
        │   ├── _generate_image_captions() [GPT-4o Vision]
        │   ├── _extract_shapes()
        │   └── _extract_comments()
        │
        ├── PptxPreprocessor
        │   ├── preprocess()
        │   └── _extract_and_embed_notes()
        │
        ├── BorderTableDetector
        │   ├── detect_tables_by_border()
        │   ├── insert_separator_rows()
        │   └── classify_tables() [large vs normal]
        │
        └── libreoffice_lock()
            └── File-based locking for concurrent access
```

### Source Files

| File | Lines | Purpose |
|------|-------|---------|
| `pipeline.py` | 1139 | Main orchestrator with document type routing |
| `document_converter.py` | 1818 | Multi-format Docling conversion |
| `html_table_extractor.py` | 370 | HTML table parsing with span handling |
| `llm_header_detector.py` | 517 | GPT-4o header detection |
| `record_builder.py` | 488 | JSON record generation with token merging |
| `semantic_chunker.py` | 294 | HybridChunker for text documents |
| `large_table_extractor.py` | 563 | Chunked processing for large Excel tables |
| `detect_border.py` | ~500 | Border detection and separator insertion |
| `word_preprocessor.py` | 449 | Word document preprocessing |
| `pptx_preprocessor.py` | 97 | PowerPoint speaker notes extraction |
| `libreoffice_lock.py` | 65 | File locking for LibreOffice concurrency |
| `excel_loader.py` | ~200 | Excel file loading utilities |

---

## Data Flow

### 1. Celery Task Entry Point

**File:** `src/workers/extraction_v2_tasks.py`

```python
@celery_app.task(name="extract_document_v2")
def extract_document_v2(document_id: str, file_path: str):
    """
    Entry point for V2 document extraction.

    Routes based on file extension:
    - .xlsx/.xls/.xlsm → process_document_with_large_table_detection_sync()
    - .docx/.doc/.docm → process_word_document_sync()
    - .pdf            → process_pdf_document_sync()
    - .pptx/.ppt/.pptm → process_pptx_document_sync()
    """
```

### 2. Excel Pipeline

**Input Formats:** `.xlsx`, `.xls`, `.xlsm`

**Flow:**
```
Excel File
    ↓
Format Normalization
    ├── .xlsx: No conversion
    ├── .xls:  pandas+xlrd → .xlsx
    └── .xlsm: LibreOffice → .xlsx
    ↓
Preprocessing (4 Steps)
    ├── Step 1: Image extraction + AI descriptions
    ├── Step 2: Shape annotation extraction
    ├── Step 3: Comment marker application
    └── Step 4: Border detection + separator rows
    ↓
Large Table Detection
    ├── Tables >1000 rows → LargeTableExtractor (chunked)
    └── Tables ≤1000 rows → Standard Docling pipeline
    ↓
Docling Conversion → HTML
    ↓
HTML Table Extraction
    ├── Valid tables (≥2 cols) → LLM header detection
    └── Raw text tables (1 col) → Direct text records
    ↓
LLM Header Detection (GPT-4o)
    ↓
Record Building
    ↓
JSON Records with Source Metadata
```

### 3. Word Pipeline

**Input Formats:** `.docx`, `.doc`, `.docm`

**Flow:**
```
Word File
    ↓
Format Normalization
    ├── .docx: No conversion
    ├── .doc:  LibreOffice → .docx
    └── .docm: XML extraction (preserves content, strips macros)
    ↓
Docling Conversion → Markdown
    ↓
Semantic Chunking (HybridChunker)
    ├── Token-aware splitting (max 8000 tokens)
    ├── Image serialization with captions
    └── Table serialization to markdown
    ↓
Chunk Records with Contextualization
```

### 4. PDF Pipeline

**Input Formats:** `.pdf`

**Flow:**
```
PDF File
    ↓
Validation (max 100MB, PDF header check)
    ↓
Docling Conversion → Markdown
    ├── OCR disabled by default (PdfPipelineOptions)
    └── Preserves tables, images, text structure
    ↓
Semantic Chunking (HybridChunker)
    ├── Token-aware splitting
    ├── Image captions via AnnotationPictureSerializer
    └── Tables as markdown
    ↓
Chunk Records
```

### 5. PowerPoint Pipeline

**Input Formats:** `.pptx`, `.ppt`, `.pptm`

**Flow:**
```
PowerPoint File
    ↓
Format Normalization
    ├── .pptx: No conversion
    └── .ppt/.pptm: LibreOffice → .pptx
    ↓
Check for Speaker Notes
    ├── Has notes: PptxPreprocessor embeds inline
    └── No notes: Skip preprocessing
    ↓
Docling Conversion → Markdown
    ↓
Semantic Chunking (HybridChunker)
    ├── Slides as sections
    ├── Speaker notes as [SPEAKER NOTES] markers
    └── Images and tables serialized
    ↓
Chunk Records
```

---

## Component Specifications

### DocumentToHtmlConverter

**Location:** `src/extraction_v2/document_converter.py`

**Purpose:** Universal document converter supporting Excel, Word, PDF, and PowerPoint

**Key Methods:**

| Method | Input | Output | Description |
|--------|-------|--------|-------------|
| `convert_to_html()` | File path | HTML/Markdown string | Routes to appropriate converter |
| `_convert_excel_to_html()` | Excel path | HTML | Full Excel preprocessing pipeline |
| `_convert_word_to_markdown()` | Word path | Markdown | Word conversion with fallback |
| `_convert_pdf_to_markdown()` | PDF path | Markdown | PDF conversion with validation |
| `_convert_pptx_to_markdown()` | PowerPoint path | Markdown | PowerPoint with speaker notes |

**LibreOffice Lock Integration:**

```python
# Acquire lock before LibreOffice operations
lock_fd = open(LIBREOFFICE_LOCK_FILE, 'w')
fcntl.flock(lock_fd, fcntl.LOCK_EX)  # Blocking exclusive lock

try:
    result = subprocess.run([
        libreoffice_cmd,
        '--headless',
        f'-env:UserInstallation=file://{profile_dir}',  # Unique profile
        '--convert-to', 'xlsx',
        '--outdir', temp_dir,
        input_file
    ])
finally:
    fcntl.flock(lock_fd, fcntl.LOCK_UN)  # Release lock
```

### LargeTableExtractor

**Location:** `src/extraction_v2/large_table_extractor.py`

**Purpose:** Extract data from Excel tables exceeding Docling's processing limits

**Configuration:**

```python
@dataclass
class LargeTableConfig:
    max_rows_for_docling: int = 1000   # Tables larger → chunked processing
    header_sample_rows: int = 10       # Rows for LLM header detection
    chunk_size: int = 100              # Data rows per processing chunk
    max_rows_to_process: int = -1      # -1 = all rows
```

**Performance:**
- 619K cells processed in ~15 seconds
- Memory efficient with `read_only=True` mode
- Uses `iter_rows(values_only=True)` for ~100x speedup

**Processing Strategy:**

```python
def extract_table(self, file_path, table_info, document_id):
    # Step 1: Extract header rows as HTML
    header_html = self._extract_header_rows_as_html(file_path, table_info)

    # Step 2: LLM header detection
    headers = self._extract_normalized_headers(header_html, table_info)

    # Step 3: Chunked data processing
    records = self._process_data_rows(file_path, table_info, headers)

    return LargeTableResult(...)
```

### SemanticChunker

**Location:** `src/extraction_v2/semantic_chunker.py`

**Purpose:** Token-aware document chunking for Word, PDF, and PowerPoint

**Key Features:**
- OpenAI tokenizer (`text-embedding-3-large`) for accurate token counting
- Maximum 8000 tokens per chunk (embedding limit is 8191)
- Custom serializers for images and tables
- Contextualization for each chunk

**Custom Serializers:**

```python
class AnnotationPictureSerializer(MarkdownPictureSerializer):
    """Serializes images with captions and descriptions."""

    def serialize(self, item: PictureItem, ...):
        text_parts = []
        if caption:
            text_parts.append(f"[CAPTION]: {caption}")
        for annotation in item.annotations:
            if isinstance(annotation, PictureDescriptionData):
                text_parts.append(f"[PICTURE DESCRIPTION] {annotation.text}")
        return create_ser_result(text="\n".join(text_parts))

class ImgTableAnnotationSerializerProvider(ChunkingSerializerProvider):
    """Provides custom serializers for chunking."""

    def get_serializer(self, doc):
        return ChunkingDocSerializer(
            doc=doc,
            picture_serializer=AnnotationPictureSerializer(),
            table_serializer=MarkdownTableSerializer(),
        )
```

### RecordBuilder

**Location:** `src/extraction_v2/record_builder.py`

**Purpose:** Build structured JSON records from tables with headers

**Token-Aware Raw Text Merging:**

```python
def build_combined_raw_text_records(
    self,
    tables: list[HtmlTable],
    document_id: UUID,
    sheet_name: str,
    max_tokens: int = 8000,   # Embedding limit
    min_tokens: int = 100     # Merge threshold
) -> list[dict[str, Any]]:
    """
    Merges small consecutive raw text tables to optimize for embeddings.

    Logic:
    1. Each table becomes one record by default
    2. If table has < min_tokens, try to merge with next consecutive
    3. Stop merging when combined exceeds max_tokens
    """
```

### BorderTableDetector

**Location:** `src/extraction_v2/detect_border.py`

**Purpose:** Identify table boundaries using cell borders and insert separators

**Algorithm:**
1. Scan all cells for border presence
2. Use flood-fill to find contiguous bordered regions
3. Calculate bounding boxes for each region
4. Filter regions by minimum cell count (≥4 cells)
5. Insert blank separator rows between tables

**Performance Optimization:**
- Skips "inflated" sheets (>5000 rows) to avoid processing empty cells
- Uses openpyxl's `iter_rows()` for efficient scanning

### WordPreprocessor

**Location:** `src/extraction_v2/word_preprocessor.py`

**Purpose:** Preprocess Word documents before Docling conversion

**Processing Steps:**

| Step | Content Type | Embedded Format |
|------|--------------|-----------------|
| 1 | Images | `[IMAGE: {AI-generated caption}]` |
| 2 | Shapes/Text Boxes | `[SHAPE: {extracted text}]` |
| 3 | Comments | `[COMMENT_{id}] {author} ({date}): {text}` |

**Image Captioning:**
- Uses Azure OpenAI GPT-4o Vision API
- Includes surrounding paragraph context for better descriptions
- Falls back to `[Image: Caption unavailable]` on API failure

### PptxPreprocessor

**Location:** `src/extraction_v2/pptx_preprocessor.py`

**Purpose:** Extract and embed speaker notes before Docling conversion

**Processing:**
- Detects slides with speaker notes
- Adds text box at bottom of each slide: `[SPEAKER NOTES]: {notes text}`
- Preserves original presentation structure

### LibreOffice Lock

**Location:** `src/extraction_v2/libreoffice_lock.py`

**Purpose:** Serialize LibreOffice operations across worker processes

**Implementation:**

```python
LOCK_FILE_PATH = "/tmp/dsol_libreoffice.lock"

@contextmanager
def libreoffice_lock(timeout=None):
    """Blocking file lock for LibreOffice operations."""
    lock_file = open(LOCK_FILE_PATH, 'w')
    try:
        fcntl.flock(lock_file.fileno(), fcntl.LOCK_EX)  # Blocking
        yield
    finally:
        fcntl.flock(lock_file.fileno(), fcntl.LOCK_UN)
        lock_file.close()
```

**Why Needed:**
- LibreOffice can only run one instance at a time
- Multiple Celery workers may try to convert files simultaneously
- Without locking: crashes, corruption, profile lock conflicts

---

## Data Models

### ExtractionResult

**Purpose:** Encapsulate pipeline execution results

```python
@dataclass
class ExtractionResult:
    success: bool                    # True if extraction succeeded
    document_id: UUID                # Document identifier
    file_path: str                   # Source file path
    record_count: int                # Number of records extracted
    records: list[dict[str, Any]]    # Actual record data
    table_count: int = 0             # Number of tables/chunks processed
    error: str | None = None         # Error message if failed
```

### HtmlTable

**Purpose:** Represent extracted table structure from HTML

```python
@dataclass
class HtmlTable:
    html: str                        # Raw HTML of table element
    rows: List[List[str]]            # 2D array of cell text (normalized)
    sheet_name: Optional[str]        # Source sheet name
    table_index: int                 # Position in document (0-based)
    row_count: int                   # Total number of rows
    col_count: int                   # Maximum number of columns
```

### HeaderStructure

**Purpose:** Represent detected hierarchical header structure

```python
@dataclass
class HeaderStructure:
    header_rows: List[int]           # Which rows are headers (0-indexed)
    headers: List[Dict[str, Any]]    # Column definitions with hierarchy

    def get_flat_headers(self) -> List[str]:
        """Returns: ["Product -> Name", "Sales -> Q1", "Sales -> Q2"]"""
```

### LargeTableResult

**Purpose:** Result from large table extraction

```python
@dataclass
class LargeTableResult:
    success: bool
    sheet_name: str
    table_range: str
    total_rows: int
    rows_processed: int
    column_count: int
    header_depth: int
    headers: dict[str, str]          # column_letter -> header_name
    records: list[dict[str, Any]]
    error: str | None = None
    processing_time: float = 0.0
```

### Chunk Record (Word/PDF/PowerPoint)

**Purpose:** Semantic chunk from text documents

```python
{
    "file_id": "uuid-string",
    "text": "Chunk content with contextualization...",
    "metadata": {
        "page_number": 1,
        "source_filename": "document.docx",
        "unique_filename": "document.docx",
        "contains_table": false
    },
    "element_type": "Text"
}
```

---

## Processing Pipelines

### Excel Pipeline Orchestration

```python
async def process_document(self, document_id: UUID, file_path: str):
    # Step 1: Excel → HTML
    html = self.converter.convert_to_html(file_path)

    # Step 2: HTML → Tables (classify into valid + raw text)
    valid_tables, raw_text_tables = self.extractor.extract_all_tables(html)

    # Step 3 & 4: For each valid table
    for table in valid_tables:
        sheet_name = self.converter.get_sheet_name_for_table(table.table_index)
        headers = self.detector.detect_headers(table.html)
        records = self.builder.build_records(table, headers, document_id, sheet_name)
        all_records.extend(records)

    # Process raw text tables (no LLM - cost savings)
    raw_text_by_sheet = self._group_raw_text_by_sheet(raw_text_tables, valid_tables)
    for sheet_name, tables in raw_text_by_sheet.items():
        raw_records = self.builder.build_combined_raw_text_records(
            tables, document_id, sheet_name
        )
        all_records.extend(raw_records)

    return ExtractionResult(success=True, records=all_records, ...)
```

### Large Table Detection Pipeline

```python
async def process_document_with_large_table_detection(self, document_id, file_path):
    # Step 1: Detect all tables and classify by size
    tables = self.large_table_extractor.detect_tables(file_path)

    large_tables = [t for t in tables if t.get('is_large', False)]
    normal_tables = [t for t in tables if not t.get('is_large', False)]

    # Step 2: Process large tables with chunked extraction
    for table in large_tables:
        result = self.large_table_extractor.extract_table(file_path, table, document_id)
        pipeline_records = self.large_table_extractor.to_pipeline_records(result, document_id)
        all_records.extend(pipeline_records)

    # Step 3: Process normal tables with standard Docling pipeline
    if normal_tables:
        standard_result = await self.process_document(document_id, file_path)
        all_records.extend(standard_result.records)

    return ExtractionResult(success=True, records=all_records, ...)
```

### Word/PDF/PowerPoint Pipeline

```python
async def process_word_document(self, document_id: UUID, file_path: str):
    # Step 1: Word → Markdown (via Docling)
    markdown = self.converter.convert_to_html(file_path)

    # Step 2: Get DoclingDocument for chunking
    converter = DocumentConverter(
        format_options={InputFormat.DOCX: WordFormatOption()}
    )
    result = converter.convert(file_path)
    doc = result.document

    # Chunk with HybridChunker
    chunks = self.semantic_chunker.chunk_document(
        doc=doc,
        file_id=document_id,
        original_filename=Path(file_path).name
    )

    return ExtractionResult(success=True, records=chunks, ...)
```

---

## Error Handling & Fallback

### Error Categories

| Error Type | Handling Strategy | Fallback |
|------------|-------------------|----------|
| LibreOffice not found | Log warning, return failure | V1 pipeline (Excel only) |
| Docling conversion fails | Log error, return failure | V1 pipeline (Excel only) |
| LLM API fails | Log error, use fallback headers | Continue with generic headers |
| Large table timeout | Log warning, skip table | Process remaining tables |
| Word/PDF/PowerPoint failure | Log error, mark failed | No fallback (V1 doesn't support) |

### V1 Fallback Chain

```
V2 Pipeline Failure (Excel only)
    ↓
Return ExtractionResult(success=False)
    ↓
Check file type
    ├── Word/PDF/PowerPoint: Raise ExtractionError (no V1 support)
    └── Excel: Invoke V1 Pipeline (openpyxl-based)
        ↓
    V1 Pipeline processes document
        ↓
    Return V1 ExtractionResult
```

### LLM Fallback Strategies

| Scenario | Fallback Action |
|----------|----------------|
| LLM returns empty headers | Generate column-based headers (`Column 0`, `Column 1`, ...) |
| JSON parse error | Parse first HTML row as headers |
| API call fails | Parse first HTML row as headers |
| >80% generic headers | Skip table (likely fragment) |

---

## Performance Characteristics

### Benchmarks

| Operation | Target | Measured |
|-----------|--------|----------|
| Excel conversion (<10MB) | ≤10 seconds | ~8-12 seconds |
| Large table (619K cells) | ≤20 seconds | ~15 seconds |
| Header detection (LLM) | ≤3 seconds | ~1-2 seconds |
| Word conversion | ≤15 seconds | ~10-20 seconds |
| PDF conversion | ≤20 seconds | ~10-30 seconds |
| PowerPoint conversion | ≤15 seconds | ~8-15 seconds |
| Memory per file | ≤500 MB | ~300-400 MB |

### Optimization Strategies

1. **Lazy Client Initialization:**
   - LLM and OpenAI clients created on first use
   - Prevents HTTP client issues in forked Celery workers

2. **HTML Truncation for LLM:**
   - Only send first 10 rows to GPT-4o
   - Reduces tokens ~80% per table

3. **Table Filtering:**
   - Skip single-column tables (likely text blocks)
   - Skip tables with only generic column headers

4. **Large Table Chunked Processing:**
   - Uses `iter_rows(values_only=True)` for ~100x speedup
   - Memory efficient with `read_only=True` mode

5. **Token-Aware Merging:**
   - Merge small consecutive raw text tables
   - Optimize for embedding token limits (8000)

6. **LibreOffice Concurrency Control:**
   - File lock prevents crashes from concurrent access
   - Unique profile directories prevent lock conflicts

---

## Configuration & Dependencies

### Environment Variables

```bash
# Azure OpenAI (required for LLM header detection)
AZURE_OPENAI_API_KEY=your-api-key
AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/
AZURE_OPENAI_API_VERSION=2024-02-15-preview
AZURE_OPENAI_DEPLOYMENT=gpt-4o

# Redis (for progress tracking)
REDIS_URL=redis://localhost:6379/0

# Milvus (for vector indexing)
MILVUS_HOST=localhost
MILVUS_PORT=19530
```

### Python Dependencies

```toml
[tool.poetry.dependencies]
docling = "^2.0.0"           # Multi-format document conversion
docling-core = "^2.0.0"      # HybridChunker, serializers
beautifulsoup4 = "^4.12.0"   # HTML parsing
lxml = "^5.0.0"              # Fast HTML/XML parser
openpyxl = "^3.1.5"          # Excel manipulation
pandas = "^2.0.0"            # .xls conversion
xlrd = "^2.0.1"              # .xls reading
python-docx = "^1.0.0"       # Word document processing
python-pptx = "^0.6.0"       # PowerPoint processing
tiktoken = "^0.5.0"          # OpenAI tokenizer
structlog = "^23.0.0"        # Structured logging
openai = "^1.0.0"            # Azure OpenAI client
httpx = "^0.24.0"            # HTTP client
celery = "^5.0.0"            # Task queue
redis = "^5.0.0"             # Progress tracking
pymilvus = "^2.0.0"          # Vector database client
```

### System Dependencies

**LibreOffice (for legacy format support):**

```bash
# Ubuntu/Debian
sudo apt-get update && sudo apt-get install -y libreoffice

# macOS
brew install --cask libreoffice

# Verify installation
libreoffice --version
```

---

## Testing Strategy

### Unit Tests

**Coverage Areas:**

1. **Document Converter:**
   - Test routing based on file extension
   - Test Excel preprocessing pipeline
   - Test Word/PDF/PowerPoint conversion
   - Test LibreOffice lock acquisition

2. **Large Table Extractor:**
   - Test large table detection (>1000 rows)
   - Test chunked processing
   - Test header extraction from HTML

3. **Semantic Chunker:**
   - Test token counting accuracy
   - Test custom serializers
   - Test contextualization

4. **Border Detection:**
   - Test table detection with various border styles
   - Test separator row insertion
   - Test inflated sheet skipping

### Integration Tests

**Test Fixtures:**

```
tests/fixtures/extraction_v2/
├── excel/
│   ├── simple_table.xlsx
│   ├── multi_level_headers.xlsx
│   ├── large_table_5000_rows.xlsx
│   └── legacy_format.xls
├── word/
│   ├── simple_document.docx
│   ├── with_images.docx
│   └── with_comments.docx
├── pdf/
│   ├── simple.pdf
│   └── with_tables.pdf
└── powerpoint/
    ├── simple.pptx
    └── with_speaker_notes.pptx
```

---

## Known Limitations

### Current Limitations

1. **LLM Dependency:**
   - Requires Azure OpenAI API access
   - API rate limits may affect batch processing

2. **LibreOffice Requirement:**
   - Legacy formats require LibreOffice installed
   - Single-threaded due to file locking

3. **Large File Handling:**
   - Files >100MB may cause memory issues
   - PDF OCR disabled by default

4. **Format-Specific:**
   - Word preprocessing may fail on complex drawings
   - PowerPoint speaker notes only (not animations)
   - PDF OCR accuracy varies

5. **V1 Fallback Limitations:**
   - Only available for Excel files
   - Word/PDF/PowerPoint have no fallback

### Workarounds

| Limitation | Workaround |
|------------|------------|
| No LibreOffice | V1 pipeline for Excel; convert legacy formats manually |
| LLM API unavailable | Fallback to first-row headers |
| File too large | Split documents manually before upload |
| PDF text extraction poor | Enable OCR (slower) or use scanned document preprocessing |

---

## Future Enhancements

### Planned Improvements

1. **Parallel Document Processing:**
   - Process multiple documents concurrently
   - Batch LLM header detection requests

2. **Enhanced PDF Support:**
   - OCR as configurable option
   - Layout analysis for complex PDFs

3. **Streaming for Large Files:**
   - Process sheets/pages individually
   - Reduce memory footprint for >100MB files

4. **LLM Response Caching:**
   - Cache header structures for identical first-10-rows
   - Reduce API costs by 40-60%

5. **Additional Format Support:**
   - `.ods` (OpenDocument Spreadsheet)
   - `.odt` (OpenDocument Text)
   - Google Docs/Sheets API integration

---

## Appendix A: Directory Structure

```
src/extraction_v2/
├── __init__.py                      # Package exports
├── README.md                        # Component overview
├── IMPLEMENTATION_GUIDE.md          # Development guide
│
├── pipeline.py                      # Main orchestrator
├── document_converter.py            # Multi-format Docling conversion
├── html_table_extractor.py          # HTML table parsing
├── llm_header_detector.py           # GPT-4o header detection
├── record_builder.py                # JSON record generation
├── semantic_chunker.py              # HybridChunker for text documents
├── large_table_extractor.py         # Chunked processing for large tables
├── detect_border.py                 # Border detection & separators
├── excel_loader.py                  # Excel file loading utilities
├── word_preprocessor.py             # Word preprocessing
├── pptx_preprocessor.py             # PowerPoint preprocessing
└── libreoffice_lock.py              # Concurrency control

src/workers/
└── extraction_v2_tasks.py           # Celery task integration
```

---

## Appendix B: Glossary

| Term | Definition |
|------|------------|
| **Docling** | Open-source library for converting documents to HTML/Markdown |
| **HybridChunker** | Docling-core component for semantic document chunking |
| **Border Detection** | Algorithm identifying table boundaries by cell borders |
| **Separator Row** | Blank row inserted between tables for Docling identification |
| **Large Table** | Excel table with >1000 rows, requires chunked processing |
| **Semantic Chunk** | Token-bounded text segment with contextual information |
| **Fork-Safety** | Avoiding HTTP client initialization before Celery process forking |
| **LibreOffice Lock** | File-based mutex for serializing LibreOffice operations |

---

## Document History

| Version | Date | Author | Changes |
|---------|------|--------|---------|
| 1.0 | 2025-11-28 | System | Initial specification (Excel only) |
| 2.0 | 2025-12-01 | System | Added border detection, preprocessing, batch processing |
| 3.0 | 2025-12-08 | System | Multi-format support (Word, PDF, PowerPoint), large table extraction, semantic chunking, LibreOffice locking |

---

**End of Technical Specification**