# DSOL - Technical Specification

**Author:** TextIQ
**Date:** 2025-12-01
**Project Level:** Feature Enhancement
**Change Type:** Testing & Enhancement
**Development Context:** Brownfield - Existing V2 Extraction Pipeline

---

## Context

### Available Documents

**Loaded Documentation:**
- ✓ Extraction Pipeline V2 Tech-Spec (docs/specs/extraction-pipeline-v2-tech-spec.md) - Comprehensive 1,232-line specification
- ✓ Product Brief (docs/brief.md)
- ✓ Sprint artifacts and user stories
- ✓ Batch extraction status tracking (batch_extraction_status.json)

**Current State:**
- V2 extraction pipeline implemented and operational
- Batch processing script (`batch_extract_v2.py`) partially deployed
- 58 Excel files in CustomerDocument/ folder for testing
- Current success: 27/37 files completed (73% of tracked files)
- **Gap:** 21 files not tracked (missing `.xlsm` pattern in glob)

### Project Stack

**Runtime Environment:**
- **Language:** Python 3.11+
- **Framework:** FastAPI 0.122
- **API Server:** Uvicorn with async support
- **Task Queue:** Celery 5.3+ with Redis 7.0+

**Data Layer:**
- **Database:** PostgreSQL 18 (SQLAlchemy 2.0+, asyncpg, alembic)
- **Vector Store:** Milvus 2.4+ (pymilvus)

**Document Processing:**
- **Excel Parsing:** openpyxl 3.1.5, xlrd 2.0.2+ (.xls support)
- **Document Conversion:** Docling 2.63.0+ (Excel → HTML)
- **LLM Integration:** Azure OpenAI (GPT-4o) via openai 1.0+

**Development Tools:**
- **Testing:** pytest 8.0+, pytest-asyncio 0.23+
- **Code Quality:** black 24.0+, ruff 0.5+, mypy 1.10+
- **Logging:** structlog 24.0+

**System Dependencies:**
- LibreOffice 7.0+ (required for .xlsm conversion)

### Existing Codebase Structure

**Project Organization:**
```
dsol/
├── src/
│   ├── extraction_v2/          # V2 pipeline implementation
│   │   ├── pipeline.py         # Main orchestrator
│   │   ├── excel_to_html_converter.py
│   │   ├── html_table_extractor.py
│   │   ├── llm_header_detector.py
│   │   ├── record_builder.py
│   │   ├── detect_border.py
│   │   └── excel_loader.py
│   ├── workers/                # Celery tasks
│   │   └── extraction_v2_tasks.py
│   └── db/                     # Database models
├── tests/                      # Test suite
│   └── test_extraction_v2/     # V2 pipeline tests
├── batch_extract_v2.py         # Batch processing script
├── test_pipeline_manual.py     # Manual testing utility
└── CustomerDocument/           # Test data (58 Excel files)
```

**Key Modules:**
- `ExtractionPipelineV2` - Main pipeline orchestrator
- `ExcelToHtmlConverter` - Handles .xlsx, .xls, .xlsm conversion
- `HtmlTableExtractor` - Parses Docling HTML output
- `LlmHeaderDetector` - GPT-4o header detection
- `RecordBuilder` - Generates JSON records
- `batch_extract_v2.py` - Batch processing with status tracking

**Testing Infrastructure:**
- pytest configuration in pyproject.toml
- Async test support enabled
- Test discovery pattern: `test_*.py`

---

## The Change

### Problem Statement

**Current Situation:**
The V2 extraction pipeline has been implemented and tested on a subset of files, but comprehensive validation against all real-world Excel files in CustomerDocument/ is incomplete.

**Specific Issues:**
1. **Incomplete File Discovery:** Batch processor only tracks 37/58 files (64% coverage)
   - Missing pattern: `.xlsm` files not included in glob search
   - 21 macro-enabled Excel files unprocessed

2. **Lack of Comprehensive Testing:** No automated test suite validating extraction across all file types
   - Manual testing only covers sample cases
   - No regression testing against full dataset
   - Success rate unknown for complete file set

3. **Unknown Edge Cases:** Real-world files may expose:
   - Unsupported Excel features
   - File corruption scenarios
   - Performance issues with large/complex files
   - Format variations not covered in development

4. **No Success Metrics:** Cannot measure whether 95% success target is met
   - No automated validation
   - No quality metrics tracking
   - No comparison with V1 baseline

**Business Impact:**
- Cannot confidently deploy V2 pipeline to production
- Risk of silent failures on unsupported file types
- No data-driven confidence in system reliability

### Proposed Solution

**Solution Overview:**
Implement comprehensive testing and enhancement strategy to achieve 95%+ extraction success rate across all 58 CustomerDocument/ files.

**Four-Part Approach:**

**1. Fix File Discovery (Quick Win)**
- Update `batch_extract_v2.py` to include `.xlsm` pattern
- Re-scan CustomerDocument/ to track all 58 files
- Verify all file types discovered

**2. Create Automated Test Suite**
- Build pytest test that processes all 58 files
- Validate extraction success/failure for each
- Track success rate metrics
- Generate detailed failure reports

**3. Enhance Pipeline Robustness**
- Improve error handling for edge cases
- Strengthen V1 fallback mechanism
- Add file validation pre-checks
- Implement retry logic for transient failures

**4. Implement Success Metrics**
- Track success rate per file type (.xlsx, .xls, .xlsm)
- Compare V2 vs V1 extraction quality
- Generate validation reports
- Monitor for regressions

**Expected Outcome:**
- ✅ All 58 files tracked and processed
- ✅ ≥95% extraction success rate
- ✅ Automated test coverage
- ✅ Production-ready V2 pipeline

### Scope

**In Scope:**

1. **File Discovery Enhancement**
   - Update batch_extract_v2.py glob patterns
   - Add .xlsm support
   - Verify complete file discovery

2. **Automated Testing**
   - Create test_extraction_v2_comprehensive.py
   - Test all 58 CustomerDocument/ files
   - Success rate calculation
   - Failure analysis reporting

3. **Error Handling Improvements**
   - Pre-flight file validation
   - Graceful degradation to V1
   - Better error messages
   - Retry mechanisms

4. **Metrics & Reporting**
   - Success rate tracking
   - Per-file-type statistics
   - V1 vs V2 comparison
   - Regression detection

5. **Documentation**
   - Update README with test instructions
   - Document known limitations
   - Add troubleshooting guide

**Out of Scope:**

1. ❌ New extraction features (V2 pipeline feature-complete)
2. ❌ Performance optimizations (focus on correctness first)
3. ❌ UI/frontend changes (backend-only work)
4. ❌ Database schema changes
5. ❌ Milvus index optimizations
6. ❌ Additional file formats beyond Excel (.csv, .pdf, etc.)
7. ❌ Production deployment automation (separate DevOps work)

---

## Implementation Details

### Source Tree Changes

**File Modifications:**

1. **batch_extract_v2.py** - MODIFY
   - Line 222: Update glob patterns to include .xlsm
   - Change: `for pattern in ["**/*.xlsx", "**/*.xls"]:`
   - To: `for pattern in ["**/*.xlsx", "**/*.xls", "**/*.xlsm"]:`
   - Purpose: Discover all 58 files including 21 missing .xlsm files

2. **batch_extract_v2.py** - MODIFY
   - Add file validation pre-check before processing
   - Add retry logic for transient failures
   - Enhance error reporting in status JSON

**New Test Files:**

3. **tests/test_extraction_v2/test_comprehensive_validation.py** - CREATE
   - Automated test suite for all CustomerDocument/ files
   - Success rate calculation and reporting
   - Per-file-type metrics
   - Failure analysis and categorization

4. **tests/test_extraction_v2/test_batch_processor.py** - CREATE
   - Unit tests for batch_extract_v2.py enhancements
   - File discovery validation
   - Status tracking verification
   - Resume functionality testing

5. **tests/test_extraction_v2/conftest.py** - MODIFY
   - Add fixtures for CustomerDocument/ files
   - Mock Azure OpenAI for faster testing
   - Database/Milvus test fixtures

**Documentation Updates:**

6. **README.md** - MODIFY
   - Add comprehensive testing section
   - Document .xlsm support
   - Add troubleshooting guide for common failures

7. **docs/feats/wrapup_pipeline_v2/VALIDATION_REPORT.md** - CREATE
   - Template for validation results
   - Success metrics tracking
   - Known limitations documentation

### Technical Approach

**1. File Discovery Enhancement**

Update batch processor to discover all Excel formats:

```python
# Current (missing .xlsm)
for pattern in ["**/*.xlsx", "**/*.xls"]:
    excel_files.extend(self.folder_path.glob(pattern))

# Enhanced (complete coverage)
for pattern in ["**/*.xlsx", "**/*.xls", "**/*.xlsm"]:
    excel_files.extend(self.folder_path.glob(pattern))
```

**2. Comprehensive Test Suite**

Target: 95%+ success rate across 58 files with V2 + V1 fallback.

**3. Error Handling**

Pre-flight validation, categorization, retry logic for transient failures.

**4. Success Metrics**

Track success rate per format (.xlsx/.xls/.xlsm) in enhanced status JSON.

### Integration Points

- `ExtractionPipelineV2` - V2 pipeline
- `ExtractionPipeline` - V1 fallback
- Azure OpenAI - LLM header detection
- PostgreSQL - Storage
- Milvus - Vector indexing

---

## Implementation Stack

- Python 3.11+
- FastAPI 0.122
- pytest 8.0+
- openpyxl 3.1.5
- docling 2.63.0+
- SQLAlchemy 2.0+
- Milvus 2.4+

---

## Acceptance Criteria

**Overall Success:**
1. ✅ 58/58 files discovered and tracked
2. ✅ ≥95% extraction success rate (V2 + V1 fallback)
3. ✅ Automated test suite passing
4. ✅ Documentation complete

---

**End of Technical Specification**