# Story 3.8a: Multi-Pass Pipeline Integration

Status: review

## Story

As a **system**,
I want **the extraction pipeline to execute multi-pass extraction with symbol and cross-reference processing**,
So that **symbols are detected before content extraction and resolved before indexing**.

## Acceptance Criteria

1. **AC3.8a.1:** Execute PASS 1 - Symbol Dictionary Detection (before table extraction)
   - Scan all sheets for symbol dictionary patterns using `SymbolDetector` (Story 3.5)
   - Store detected symbols in `symbol_dictionaries` table
   - Build in-memory symbol lookup for PASS 3
   - Log symbol detection results with counts

2. **AC3.8a.2:** Execute PASS 2 - Table Extraction (existing 3.4a flow)
   - Execute existing pipeline: TableDetector → HeaderExtractor → MergedCellResolver → RecordBuilder
   - Store records in `extracted_records` table with `content` field
   - Maintain existing progress tracking for `extracting_tables` stage

3. **AC3.8a.3:** Execute PASS 3 - Symbol Resolution (after records exist)
   - Resolve symbols in extracted content using `SymbolResolver` (Story 3.6)
   - Update records with `resolved_content` JSONB field
   - Log unresolved symbols as warnings
   - Include symbol resolution stats in summary

4. **AC3.8a.4:** Execute PASS 4 - Cross-Reference Detection (after records exist)
   - Detect cross-reference patterns using `CrossRefDetector` (Story 3.8)
   - Attempt to resolve references to target records
   - Store in `cross_references` table with `resolved` flag
   - Include cross-reference stats in summary

5. **AC3.8a.5:** Progress tracking shows current pass
   - `detecting_symbols` stage for PASS 1
   - `extracting_tables` stage for PASS 2
   - `resolving_symbols` stage for PASS 3
   - `detecting_references` stage for PASS 4
   - Update Redis progress with current stage

6. **AC3.8a.6:** Extraction summary includes all pass statistics
   - Return comprehensive extraction summary:
     ```python
     {
         "document_id": "uuid",
         "sheets_processed": 3,
         "tables_detected": 5,
         "records_extracted": 1250,
         "symbols_detected": 15,
         "symbols_resolved": 142,
         "unresolved_symbols": 3,
         "cross_references_found": 45,
         "cross_references_resolved": 38,
         "cross_references_unresolved": 7,
         "duration_ms": 8500,
         "warnings": []
     }
     ```

## Tasks / Subtasks

- [ ] **Task 1: Refactor pipeline to multi-pass architecture** (AC: 3.8a.1-3.8a.4)
  - [ ] Modify `ExtractionPipeline.process_document()` to call passes in order
  - [ ] Ensure PASS 1 (symbol detection) runs BEFORE PASS 2 (table extraction)
  - [ ] Pass symbol lookup dictionary from PASS 1 to PASS 3
  - [ ] Maintain transaction boundaries between passes

- [ ] **Task 2: Implement PASS 1 - Symbol Detection integration** (AC: 3.8a.1)
  - [ ] Add `_run_symbol_detection()` method to `ExtractionPipeline`
  - [ ] Use `SymbolDetector.detect_symbol_sheets()` from Story 3.5
  - [ ] Store detected symbols in database via `symbol_dictionaries` table
  - [ ] Return symbol lookup dict for use in PASS 3
  - [ ] Update progress to `detecting_symbols` stage

- [ ] **Task 3: Verify PASS 2 - Table Extraction** (AC: 3.8a.2)
  - [ ] Confirm existing `_run_extraction()` method handles table extraction
  - [ ] Verify TableDetector → HeaderExtractor → MergedCellResolver → RecordBuilder flow
  - [ ] Ensure records stored in `extracted_records` with `content` field
  - [ ] Confirm progress updates to `extracting_tables` stage

- [ ] **Task 4: Implement PASS 3 - Symbol Resolution integration** (AC: 3.8a.3)
  - [ ] Add `_run_symbol_resolution()` method to `ExtractionPipeline`
  - [ ] Use `SymbolResolver.resolve_document()` from Story 3.6
  - [ ] Pass symbol lookup from PASS 1 to resolver
  - [ ] Update records with `resolved_content` field
  - [ ] Update progress to `resolving_symbols` stage

- [ ] **Task 5: Verify PASS 4 - Cross-Reference Detection** (AC: 3.8a.4)
  - [ ] Confirm existing `_run_cross_ref_detection()` from Story 3.8 is called
  - [ ] Verify detection runs after records exist (PASS 2 complete)
  - [ ] Confirm cross-references stored in `cross_references` table
  - [ ] Ensure progress updates to `detecting_references` stage

- [ ] **Task 6: Implement comprehensive progress tracking** (AC: 3.8a.5)
  - [ ] Add `_update_stage_progress()` helper method
  - [ ] Ensure Redis progress includes: stage, sheets_processed, records_count
  - [ ] Track timing for each pass for performance analysis
  - [ ] Handle Redis connection failures gracefully

- [ ] **Task 7: Consolidate extraction summary statistics** (AC: 3.8a.6)
  - [ ] Extend `ExtractionSummary` dataclass with all pass statistics
  - [ ] Add `symbols_detected`, `symbols_resolved`, `unresolved_symbols` fields
  - [ ] Aggregate warnings from all passes
  - [ ] Calculate total duration across all passes

- [ ] **Task 8: Unit tests for multi-pass orchestration** (AC: All)
  - [ ] Test pass execution order (1 → 2 → 3 → 4)
  - [ ] Test symbol lookup passed from PASS 1 to PASS 3
  - [ ] Test each pass failure handling (continue vs fail)
  - [ ] Test progress tracking updates per stage

- [ ] **Task 9: Integration tests for full pipeline** (AC: All)
  - [ ] Test complete document processing with symbols and cross-refs
  - [ ] Test extraction summary contains all statistics
  - [ ] Test pipeline resilience (partial failures don't fail entire extraction)
  - [ ] Verify database state after each pass

## Dev Notes

### Architecture Patterns and Constraints

From architecture.md:
- **Module location:** `src/extraction/pipeline.py` (architecture.md:62)
- **Multi-pass requirement:** ADR-003 states "Extract in passes (symbols → resolve → content)"
- **Error handling:** Non-critical passes (symbol resolution, cross-ref) should not fail extraction
- **Progress tracking:** Use Redis for real-time progress updates

From epics.md Story 3.8a:
- **Prerequisites:** Story 3.4a, 3.5, 3.6, 3.8
- **FRs Covered:** FR11-13 (Symbol resolution integration), FR15-17 (Cross-reference integration)
- **Pass Order:** Symbol detection MUST complete before table extraction begins

### Learnings from Previous Story

**From Story 3.8: Cross-Reference Detection and Resolution (Status: review)**

- **CrossRefDetector Pattern:** `src/extraction/cross_ref_detector.py` shows:
  - `detect_references(content)` for pattern matching
  - `resolve_reference(match, document_id, session)` for resolution
  - `process_document(document_id, session)` for full flow
  - Non-critical execution: errors logged but don't fail pipeline

- **Pipeline Integration Pattern:**
  - `_run_cross_ref_detection()` already added to `ExtractionPipeline`
  - Extended `ExtractionSummary` with `cross_references_found`, `cross_references_resolved`, `cross_references_unresolved`
  - Progress stage `detecting_references` already implemented

- **Testing Patterns:**
  - Mock database queries with `MagicMock`
  - Chain mocks for `.filter().order_by().all()` patterns
  - Integration tests verify full detection → resolution → storage flow

- **Files Created in 3.8:**
  - `src/extraction/cross_ref_detector.py` - Use `CrossRefDetector` class
  - `tests/extraction/test_cross_ref_detector.py` - Reference for test patterns
  - `tests/integration/test_cross_ref_integration.py` - Reference for integration tests

[Source: docs/sprint-artifacts/3-8-cross-reference-detection-resolution.md#Dev-Agent-Record]

### Existing Components to Integrate

**SymbolDetector (Story 3.5):**
- Location: `src/extraction/symbol_detector.py`
- Method: `detect_symbol_sheets(workbook)` - returns detected symbol dictionaries
- Stores symbols in `symbol_dictionaries` table

**SymbolResolver (Story 3.6):**
- Location: `src/extraction/symbol_resolver.py`
- Method: `resolve_document(document_id, session)` - resolves symbols in records
- Updates `resolved_content` JSONB field in `extracted_records`
- Returns `SymbolResolutionSummary` with stats

**CrossRefDetector (Story 3.8):**
- Location: `src/extraction/cross_ref_detector.py`
- Method: `process_document(document_id, session)` - detects and resolves cross-refs
- Returns `CrossRefResolutionSummary` with stats

**ExtractionPipeline (Story 3.4a):**
- Location: `src/extraction/pipeline.py`
- Current flow: load workbook → detect tables → extract headers → resolve merged → build records
- Needs: Insert symbol detection before table extraction, add symbol resolution after

### Multi-Pass Flow Diagram

```
PASS 1: Symbol Detection
  ├── SymbolDetector.detect_symbol_sheets(workbook)
  ├── Store symbols in symbol_dictionaries table
  └── Build in-memory symbol_lookup dict

PASS 2: Table Extraction (existing)
  ├── TableDetector.detect_tables()
  ├── HeaderExtractor.extract_headers()
  ├── MergedCellResolver.resolve()
  └── RecordBuilder.build_records()
      └── Store in extracted_records table

PASS 3: Symbol Resolution
  ├── SymbolResolver.resolve_document()
  │   ├── Load symbol_lookup from PASS 1
  │   └── Update resolved_content in extracted_records
  └── Log unresolved symbols

PASS 4: Cross-Reference Detection
  ├── CrossRefDetector.process_document()
  │   ├── Detect patterns in content
  │   └── Resolve to target records
  └── Store in cross_references table
```

### Project Structure Notes

Current module structure:
```
src/extraction/
├── pipeline.py              # MODIFY: Add multi-pass orchestration
├── symbol_detector.py       # Story 3.5: Symbol detection
├── symbol_resolver.py       # Story 3.6: Symbol resolution
├── cross_ref_detector.py    # Story 3.8: Cross-reference detection
├── table_detector.py        # Story 3.1: Table boundaries
├── header_extractor.py      # Story 3.2: Header extraction
├── merged_cell_resolver.py  # Story 3.3: Merged cells
├── record_builder.py        # Story 3.4: Record building
└── models.py                # MODIFY: Extend ExtractionSummary
```

### References

- [Source: docs/epics.md#Story-3.8a] - Original story definition with acceptance criteria
- [Source: docs/architecture.md#ADR-003] - Multi-pass extraction decision
- [Source: docs/architecture.md:62] - Pipeline module location
- [Source: docs/sprint-artifacts/3-8-cross-reference-detection-resolution.md] - Previous story patterns
- [Source: docs/sprint-artifacts/3-5-symbol-dictionary-detection.md] - Symbol detector implementation
- [Source: docs/sprint-artifacts/3-6-symbol-resolution-in-content.md] - Symbol resolver implementation

## Dev Agent Record

### Context Reference

<!-- Path(s) to story context XML will be added here by context workflow -->

### Agent Model Used

{{agent_model_name_version}}

### Debug Log References

### Completion Notes List

### File List