# User Story: Enhance Error Handling & Reporting

**Story ID:** story-v2-pipeline-testing-3
**Epic:** V2 Pipeline Comprehensive Testing
**Created:** 2025-12-01
**Status:** Ready for Development
**Story Points:** 5
**Priority:** High

---

## User Story

**As a** DevOps engineer
**I want** robust error handling, categorization, and comprehensive validation reports
**So that** I can quickly diagnose failures and understand system limitations

---

## Context

**Current Situation:**
- Basic error logging but no categorization
- No retry logic for transient failures
- Limited insights into why files fail
- No validation report generation

**Problem:**
When files fail extraction, we need to know:
- Is it a transient error (network, timeout)?
- Is it a file format issue?
- Is it a pipeline bug?
- Can we retry or is it permanent?

**Solution:**
Implement error categorization, retry logic, and comprehensive validation reporting.

---

## Technical Details

**Error Categories:**
```python
from enum import Enum

class ErrorCategory(str, Enum):
    """Categorize extraction errors for better diagnostics."""

    # Transient errors (retry possible)
    NETWORK_ERROR = "network_error"
    TIMEOUT = "timeout"
    RATE_LIMIT = "rate_limit"
    SERVICE_UNAVAILABLE = "service_unavailable"

    # File-related errors (permanent)
    FILE_CORRUPTED = "file_corrupted"
    FILE_TOO_LARGE = "file_too_large"
    UNSUPPORTED_FORMAT = "unsupported_format"
    PASSWORD_PROTECTED = "password_protected"

    # Pipeline errors (investigate)
    CONVERSION_FAILED = "conversion_failed"
    PARSING_FAILED = "parsing_failed"
    LLM_ERROR = "llm_error"
    VALIDATION_FAILED = "validation_failed"

    # System errors
    OUT_OF_MEMORY = "out_of_memory"
    DISK_FULL = "disk_full"

    # Unknown
    UNKNOWN = "unknown"

def categorize_error(error: Exception) -> ErrorCategory:
    """Categorize an exception into error category."""
    error_msg = str(error).lower()

    # Network/transient
    if "timeout" in error_msg or "timed out" in error_msg:
        return ErrorCategory.TIMEOUT
    if "rate limit" in error_msg or "429" in error_msg:
        return ErrorCategory.RATE_LIMIT
    if "connection" in error_msg or "network" in error_msg:
        return ErrorCategory.NETWORK_ERROR
    if "503" in error_msg or "unavailable" in error_msg:
        return ErrorCategory.SERVICE_UNAVAILABLE

    # File errors
    if "corrupted" in error_msg or "damaged" in error_msg or "badzipfile" in error_msg:
        return ErrorCategory.FILE_CORRUPTED
    if "too large" in error_msg or "file size" in error_msg:
        return ErrorCategory.FILE_TOO_LARGE
    if "password" in error_msg or "encrypted" in error_msg:
        return ErrorCategory.PASSWORD_PROTECTED
    if "unsupported" in error_msg or "invalid format" in error_msg:
        return ErrorCategory.UNSUPPORTED_FORMAT

    # Pipeline errors
    if "conversion" in error_msg or "docling" in error_msg:
        return ErrorCategory.CONVERSION_FAILED
    if "parsing" in error_msg or "html" in error_msg:
        return ErrorCategory.PARSING_FAILED
    if "openai" in error_msg or "llm" in error_msg or "gpt" in error_msg:
        return ErrorCategory.LLM_ERROR

    # System errors
    if "memory" in error_msg or "memoryerror" in error_msg:
        return ErrorCategory.OUT_OF_MEMORY
    if "disk" in error_msg or "no space" in error_msg:
        return ErrorCategory.DISK_FULL

    return ErrorCategory.UNKNOWN
```

**Retry Logic:**
```python
import asyncio
from typing import Optional

class RetryConfig:
    """Configuration for retry logic."""
    max_retries: int = 3
    base_delay: float = 1.0  # seconds
    max_delay: float = 30.0
    exponential_backoff: bool = True

    # Which error categories should be retried
    retryable_categories = {
        ErrorCategory.NETWORK_ERROR,
        ErrorCategory.TIMEOUT,
        ErrorCategory.RATE_LIMIT,
        ErrorCategory.SERVICE_UNAVAILABLE,
    }

async def extract_with_retry(
    pipeline,
    file_path: Path,
    config: RetryConfig = RetryConfig()
) -> Optional[list]:
    """Extract with automatic retry for transient errors."""
    last_error = None

    for attempt in range(config.max_retries + 1):
        try:
            result = await pipeline.extract_from_file(str(file_path))
            return result

        except Exception as e:
            last_error = e
            category = categorize_error(e)

            # Log the error
            logger.warning(
                "Extraction attempt failed",
                file=file_path.name,
                attempt=attempt + 1,
                category=category.value,
                error=str(e)
            )

            # Don't retry if not retryable
            if category not in config.retryable_categories:
                logger.info(
                    "Error not retryable",
                    file=file_path.name,
                    category=category.value
                )
                break

            # Don't retry if max attempts reached
            if attempt >= config.max_retries:
                logger.error(
                    "Max retry attempts reached",
                    file=file_path.name,
                    attempts=attempt + 1
                )
                break

            # Calculate backoff delay
            if config.exponential_backoff:
                delay = min(config.base_delay * (2 ** attempt), config.max_delay)
            else:
                delay = config.base_delay

            logger.info(
                "Retrying after delay",
                file=file_path.name,
                delay=delay,
                next_attempt=attempt + 2
            )

            await asyncio.sleep(delay)

    # All retries exhausted
    raise last_error
```

**Enhanced Status Tracking:**
```python
# Enhance batch_extract_v2.py status JSON
{
    "folder": "CustomerDocument/",
    "total_files": 58,
    "last_updated": "2025-12-01T10:30:00Z",
    "summary": {
        "completed": 55,
        "processing": 0,
        "pending": 0,
        "failed": 3,
        "success_rate": 94.83
    },
    "metrics": {
        "v2_success": 50,
        "v1_fallback": 5,
        "by_format": {
            ".xlsx": {"total": 22, "success": 22, "failed": 0},
            ".xls": {"total": 15, "success": 15, "failed": 0},
            ".xlsm": {"total": 21, "success": 18, "failed": 3}
        }
    },
    "files": [
        {
            "file": "example.xlsx",
            "status": "completed",
            "pipeline_version": "v2",
            "records_extracted": 150,
            "attempts": 1,
            "last_error": null,
            "error_category": null
        },
        {
            "file": "problematic.xlsm",
            "status": "failed",
            "pipeline_version": null,
            "records_extracted": 0,
            "attempts": 3,
            "last_error": "File corrupted: BadZipFile",
            "error_category": "file_corrupted"
        }
    ]
}
```

**Validation Report Generator:**
```python
from datetime import datetime
from pathlib import Path
import json

class ValidationReport:
    """Generate comprehensive validation report."""

    def __init__(self, status_file: Path):
        with open(status_file) as f:
            self.status = json.load(f)

    def generate_markdown_report(self, output_file: Path):
        """Generate human-readable markdown report."""
        report_lines = [
            "# V2 Extraction Pipeline - Validation Report",
            "",
            f"**Generated:** {datetime.now().isoformat()}",
            f"**Total Files:** {self.status['total_files']}",
            "",
            "## Summary",
            "",
            f"- **Success Rate:** {self.status['summary']['success_rate']:.2f}%",
            f"- **Completed:** {self.status['summary']['completed']}",
            f"- **Failed:** {self.status['summary']['failed']}",
            f"- **V2 Success:** {self.status['metrics']['v2_success']}",
            f"- **V1 Fallback:** {self.status['metrics']['v1_fallback']}",
            "",
            "## Success by Format",
            "",
            "| Format | Total | Success | Failed | Success Rate |",
            "|--------|-------|---------|--------|--------------|",
        ]

        for fmt, stats in self.status['metrics']['by_format'].items():
            success_rate = (stats['success'] / stats['total'] * 100) if stats['total'] > 0 else 0
            report_lines.append(
                f"| {fmt} | {stats['total']} | {stats['success']} | "
                f"{stats['failed']} | {success_rate:.1f}% |"
            )

        # Failed files section
        failed_files = [
            f for f in self.status['files']
            if f['status'] == 'failed'
        ]

        if failed_files:
            report_lines.extend([
                "",
                "## Failed Files",
                "",
                "| File | Error Category | Error Message | Attempts |",
                "|------|----------------|---------------|----------|",
            ])

            for f in failed_files:
                report_lines.append(
                    f"| {f['file']} | {f['error_category']} | "
                    f"{f['last_error'][:50]}... | {f['attempts']} |"
                )

        # Known limitations
        report_lines.extend([
            "",
            "## Known Limitations",
            "",
            "### File Format Issues",
        ])

        corrupted_count = sum(
            1 for f in failed_files
            if f.get('error_category') == 'file_corrupted'
        )
        if corrupted_count > 0:
            report_lines.append(
                f"- **Corrupted Files:** {corrupted_count} files have ZIP structure damage"
            )

        password_count = sum(
            1 for f in failed_files
            if f.get('error_category') == 'password_protected'
        )
        if password_count > 0:
            report_lines.append(
                f"- **Password Protected:** {password_count} files require password"
            )

        # Recommendations
        report_lines.extend([
            "",
            "## Recommendations",
            "",
        ])

        if self.status['summary']['success_rate'] < 95:
            report_lines.append(
                f"⚠️ **Success rate ({self.status['summary']['success_rate']:.1f}%) "
                "is below 95% target.**"
            )
            report_lines.append("")
            report_lines.append("Suggested actions:")

            if corrupted_count > 0:
                report_lines.append(
                    f"1. Investigate {corrupted_count} corrupted files - "
                    "may need source file repair"
                )

            if password_count > 0:
                report_lines.append(
                    f"2. Obtain passwords for {password_count} protected files"
                )
        else:
            report_lines.append("✅ **Success rate meets 95% target.**")

        # Write report
        with open(output_file, "w") as f:
            f.write("\n".join(report_lines))

        return output_file
```

---

## Implementation Steps

1. **Add error categorization to batch_extract_v2.py**
   - Import ErrorCategory enum
   - Add categorize_error() function
   - Update error handling to categorize exceptions
   - Store error_category in status JSON

2. **Implement retry logic**
   - Add extract_with_retry() function
   - Configure RetryConfig with defaults
   - Update process_file() to use retry wrapper
   - Log retry attempts and outcomes

3. **Enhance status JSON schema**
   - Add summary section with success_rate
   - Add metrics section with v2/v1 counts
   - Add by_format breakdown
   - Store error_category and attempts per file

4. **Create ValidationReport class**
   - Implement markdown report generator
   - Add failure analysis section
   - Include recommendations based on errors
   - Generate known limitations documentation

5. **Update batch_extract_v2.py to generate reports**
   ```python
   # At end of batch processing
   def finalize_batch():
       """Generate reports after batch completion."""
       # Calculate metrics
       total = len(self.status["files"])
       completed = sum(1 for f in self.status["files"] if f["status"] == "completed")
       success_rate = (completed / total * 100) if total > 0 else 0

       self.status["summary"] = {
           "completed": completed,
           "processing": 0,
           "pending": 0,
           "failed": total - completed,
           "success_rate": success_rate
       }

       # Save final status
       self.save_status()

       # Generate validation report
       report_gen = ValidationReport(self.status_file)
       report_path = Path("docs/feats/wrapup_pipeline_v2/VALIDATION_REPORT.md")
       report_gen.generate_markdown_report(report_path)

       logger.info(
           "Batch processing complete",
           total=total,
           success_rate=success_rate,
           report=str(report_path)
       )
   ```

6. **Update README.md with troubleshooting guide**
   ```markdown
   ### Troubleshooting Extraction Failures

   #### Common Error Categories

   **File Corrupted**
   - Symptom: `BadZipFile` or `Corrupted file` errors
   - Solution: Re-download source file or repair with Excel

   **Password Protected**
   - Symptom: `Password required` error
   - Solution: Obtain password or use unprotected version

   **Timeout/Rate Limit**
   - Symptom: `Timeout` or `429 Rate limit` errors
   - Solution: Automatic retry handles this (3 attempts with backoff)

   **LLM Error**
   - Symptom: `OpenAI API error`
   - Solution: Check Azure OpenAI credentials and quota
   ```

---

## Acceptance Criteria

1. ✅ Error categorization implemented
   - 14 error categories defined
   - categorize_error() function works correctly
   - All errors categorized in status JSON

2. ✅ Retry logic operational
   - Transient errors retried up to 3 times
   - Exponential backoff delays (1s, 2s, 4s)
   - Permanent errors not retried
   - Retry attempts logged and tracked

3. ✅ Enhanced status JSON schema
   - summary section with success_rate
   - metrics with v2/v1/by_format breakdown
   - error_category and attempts per file
   - Validates against 58-file dataset

4. ✅ Validation report generated
   - Markdown format with tables
   - Failed files listed with categories
   - Known limitations documented
   - Recommendations provided

5. ✅ Documentation updated
   - README troubleshooting section added
   - Error categories explained
   - Recovery steps documented

---

## Testing Strategy

**Unit Tests:**
```python
def test_categorize_error():
    """Test error categorization logic."""
    # Transient errors
    assert categorize_error(Exception("Connection timeout")) == ErrorCategory.TIMEOUT
    assert categorize_error(Exception("Rate limit exceeded")) == ErrorCategory.RATE_LIMIT

    # File errors
    assert categorize_error(Exception("BadZipFile")) == ErrorCategory.FILE_CORRUPTED
    assert categorize_error(Exception("Password required")) == ErrorCategory.PASSWORD_PROTECTED

    # Pipeline errors
    assert categorize_error(Exception("Docling conversion failed")) == ErrorCategory.CONVERSION_FAILED

async def test_retry_logic():
    """Test retry with transient errors."""
    attempts = 0

    async def flaky_extract(file_path: str):
        nonlocal attempts
        attempts += 1
        if attempts < 3:
            raise Exception("Connection timeout")
        return [{"data": "success"}]

    # Mock pipeline
    class MockPipeline:
        async def extract_from_file(self, file_path: str):
            return await flaky_extract(file_path)

    pipeline = MockPipeline()
    result = await extract_with_retry(pipeline, Path("test.xlsx"))

    assert attempts == 3
    assert result == [{"data": "success"}]

def test_validation_report_generation():
    """Test report generation from status JSON."""
    # Create mock status file
    status = {
        "total_files": 58,
        "summary": {"completed": 55, "failed": 3, "success_rate": 94.83},
        "metrics": {"v2_success": 50, "v1_fallback": 5},
        "files": [
            {"file": "test.xlsx", "status": "failed", "error_category": "file_corrupted"}
        ]
    }

    with open("test_status.json", "w") as f:
        json.dump(status, f)

    report_gen = ValidationReport(Path("test_status.json"))
    report_path = report_gen.generate_markdown_report(Path("test_report.md"))

    # Verify report exists and contains key sections
    assert report_path.exists()
    content = report_path.read_text()
    assert "Validation Report" in content
    assert "94.83%" in content
    assert "Failed Files" in content
```

**Integration Test:**
```bash
# Run batch processor with retry and reporting
uv run python batch_extract_v2.py

# Verify status JSON has enhanced schema
cat batch_extraction_status.json | jq '.summary'
cat batch_extraction_status.json | jq '.metrics'

# Check validation report generated
test -f docs/feats/wrapup_pipeline_v2/VALIDATION_REPORT.md && echo "✅ Report exists"

# Verify success rate
RATE=$(cat batch_extraction_status.json | jq '.summary.success_rate')
echo "Success rate: $RATE%"
```

---

## Dependencies

**External Dependencies:**
- None (uses existing stdlib and project deps)

**Internal Dependencies:**
- batch_extract_v2.py (modify)
- Story 1 (file discovery) complete
- Story 2 (test suite) helps validate

**Blockers:**
- None - can implement independently

---

## Definition of Done

- [ ] ErrorCategory enum and categorize_error() implemented
- [ ] Retry logic with exponential backoff implemented
- [ ] Enhanced status JSON schema with metrics
- [ ] ValidationReport class implemented
- [ ] Markdown report generation working
- [ ] batch_extract_v2.py updated to use retry and categorization
- [ ] README troubleshooting section added
- [ ] Unit tests passing
- [ ] Integration test with 58 files successful
- [ ] VALIDATION_REPORT.md generated
- [ ] Code committed to feature branch
- [ ] All stories complete - epic ready for validation

---

## Reference

**Tech-Spec:** `docs/feats/wrapup_pipeline_v2/tech-spec.md`
**Epic:** `docs/feats/wrapup_pipeline_v2/epics.md`
**Can Run in Parallel With:** Story 2 (test suite)
**Key Files:**
- `batch_extract_v2.py` (modify - add retry, categorization, reporting)
- `docs/feats/wrapup_pipeline_v2/VALIDATION_REPORT.md` (generate)
- `README.md` (modify - add troubleshooting)

---

**Story Status:** Ready for Development