# DSOL - Document Semantic Overload Layer

LLM-powered document processing service that extracts semantic meaning from complex documents to enable accurate AI-driven Q&A.

## Overview

DSOL processes multiple document formats with intelligent extraction strategies to create a searchable knowledge base. Users can then ask natural language questions and receive accurate answers with exact source references.

**Supported Document Types:**
- **Excel Files**: Complex structures (merged cells, multi-level headers, symbol dictionaries, cross-references)
- **Word Documents**: Text with embedded tables, images, and formatting
- **PDF Documents**: Text-based and scanned PDFs with automatic layout detection
- **PowerPoint Presentations**: Slides with speaker notes, images, and tables

**Key Features:**
- Table-aware extraction preserving 2D structure (Excel)
- Multi-pass processing (symbols → resolution → content)
- Semantic chunking for text documents with Docling HybridChunker (8000 tokens max)
- Custom serializers for images and tables
- Hybrid retrieval (structured lookups + semantic search)
- Source traceability for every answer
- OCR disabled by default for fast PDF processing

## Prerequisites

- **Python 3.11+** - Required for type hints and async features
- **Docker & Docker Compose** - For running PostgreSQL, Redis, and Milvus
- **uv** - Fast Python package installer and resolver
- **LibreOffice** - Required for V2 extraction pipeline (.xls/.xlsm support)

### Installing Prerequisites

```bash
# Install uv (if not already installed)
curl -LsSf https://astral.sh/uv/install.sh | sh

# Verify Python version
python3 --version  # Should be 3.11+

# Verify Docker
docker --version
docker compose version

# Install LibreOffice (required for V2 extraction pipeline)
# Ubuntu/Debian
sudo apt-get update && sudo apt-get install -y libreoffice

# macOS
brew install --cask libreoffice

# Windows: Download from https://www.libreoffice.org/download/

# Verify LibreOffice installation
libreoffice --version
```

## Setup

### 1. Clone the Repository

```bash
git clone <repository-url>
cd dsol
```

### 2. Install Dependencies

```bash
# Create virtual environment and install dependencies
uv sync

# Or install with dev dependencies
uv sync --all-extras
```

### 3. Configure Environment

```bash
# Copy the example environment file
cp .env.example .env

# Edit .env with your configuration
# At minimum, set your Azure OpenAI credentials
```

#### Redis Configuration

Redis is used for Celery task queue, rate limiting, and progress tracking.

**Environment Variable:**
- `REDIS_URL`: Complete Redis connection URL (supports both `redis://` and `rediss://` for SSL)

**Example .env configurations:**

```bash
# Local development (non-SSL, no authentication)
REDIS_URL=redis://localhost:6379

# Local with database number
REDIS_URL=redis://localhost:6379/2

# Azure Redis Cache (SSL with authentication)
REDIS_URL=rediss://:your-password@your-cache.redis.cache.windows.net:6380
```

**Security:**
For production deployments using Azure Redis Cache, the connection URL includes authentication credentials. Keep your `.env` file secure and never commit it to version control.

**Verification:**
```bash
# Test Redis connection
docker exec -it dsol-redis redis-cli ping
# Should return: PONG

# Or check via Python
uv run python -c "from src.core.config import settings; print(settings.redis_url)"
```

### 4. Start Infrastructure

```bash
# Start PostgreSQL, Redis, and Milvus
docker compose up -d

# Verify all services are healthy
docker compose ps
```

Expected output:
```
NAME            STATUS                   PORTS
dsol-postgres   Up (healthy)             0.0.0.0:5432->5432/tcp
dsol-redis      Up (healthy)             0.0.0.0:6379->6379/tcp
dsol-milvus     Up (healthy)             0.0.0.0:19530->19530/tcp, 0.0.0.0:9091->9091/tcp
```

### 5. Run Database Migrations

```bash
uv run alembic upgrade head
```

## Supported Document Formats

DSOL V2 extraction pipeline supports the following document formats with unified semantic chunking:

### Excel Files
- `.xlsx` (Office Open XML)
- `.xls` (legacy Excel 97-2003, requires LibreOffice)
- `.xlsm` (macro-enabled, requires LibreOffice)

**Processing Strategy**: Excel → HTML → Table extraction → Structured records

### Word Documents
- `.docx` (Office Open XML)
- `.doc` (legacy Word 97-2003, requires LibreOffice)
- `.docm` (macro-enabled, requires LibreOffice)

**Processing Strategy**: Word → Markdown (Docling) → Semantic chunking → Contextualized chunks

### PDF Documents ✨ NEW
- Text-based PDFs
- Scanned PDFs (OCR disabled by default for performance)
- Mixed-content PDFs (text + images)
- PDFs with embedded tables and images

**Processing Strategy**: PDF → Markdown (Docling) → Semantic chunking → Contextualized chunks
**Performance**: OCR disabled for 10-30x faster processing

### PowerPoint Presentations ✨ NEW
- `.pptx` (Office Open XML)
- `.ppt` (legacy PowerPoint 97-2003, requires LibreOffice)
- `.pptm` (macro-enabled, requires LibreOffice)
- Speaker notes automatically extracted and embedded

**Processing Strategy**: PowerPoint → Markdown (Docling) → Semantic chunking → Contextualized chunks

### Unified Text Processing

All text documents (Word, PDF, PowerPoint) use:
- **Docling 2.63.0+** for conversion to Markdown
- **HybridChunker** with OpenAI tokenizer for semantic chunking (8000 tokens max)
- **ImgTableAnnotationSerializerProvider** for custom handling of images and tables
- **Metadata preservation** for source traceability

## Running the Application

### Starting Everything (Recommended)

For development, you need to run three components in separate terminals:

**Terminal 1 - Infrastructure:**
```bash
docker compose up -d
```

**Terminal 2 - API Server:**
```bash
uv run uvicorn src.api.main:app --reload --host 0.0.0.0 --port 8000
```

**Terminal 3 - Celery Worker:**
```bash
uv run celery -A src.workers.celery_app worker --loglevel=info
```

### API Server

#### Development Mode

```bash
uv run uvicorn src.api.main:app --reload --host 0.0.0.0 --port 8000
```

The API will be available at:
- **API:** http://localhost:8000
- **OpenAPI Docs:** http://localhost:8000/docs
- **Health Check:** http://localhost:8000/health

#### Production Mode

```bash
uv run uvicorn src.api.main:app --host 0.0.0.0 --port 8000 --workers 4
```

### Celery Workers

Celery workers handle background document processing tasks (extraction, embedding, indexing).

#### Development Mode

```bash
# Single worker with info logging
uv run celery -A src.workers.celery_app worker --loglevel=info

# With debug logging
uv run celery -A src.workers.celery_app worker --loglevel=debug
```

#### Production Mode

```bash
# Multiple workers with concurrency
uv run celery -A src.workers.celery_app worker --loglevel=warning --concurrency=4

# Or use multiple worker processes
uv run celery -A src.workers.celery_app worker --loglevel=warning -n worker1@%h &
uv run celery -A src.workers.celery_app worker --loglevel=warning -n worker2@%h &
```

#### Monitoring Workers

```bash
# Check active tasks
uv run celery -A src.workers.celery_app inspect active

# Check registered tasks
uv run celery -A src.workers.celery_app inspect registered

# Monitor events in real-time
uv run celery -A src.workers.celery_app events
```

## Testing the Extraction Pipeline

### Manual Single File Testing

Test the extraction pipeline on individual files:

```bash
# Interactive test - select from available files
uv run python test_pipeline_manual.py

# Test a specific file directly
echo -e "/path/to/file.xlsx\n2" | uv run python test_pipeline_manual.py
```

The test script allows you to:
- Choose between V1 (openpyxl) and V2 (Docling) pipelines
- Compare both pipelines side-by-side
- View extracted records, tables, symbols, and cross-references
- Track processing time and performance metrics

**Available test files:**
- `tests/fixtures/sample_excel/*.xlsx` - Simple test fixtures
- `CustomerDocument/**/*.xlsx` - Real Excel documents
- `CustomerDocument/**/*.docx, *.doc, *.docm` - Real Word documents

### Batch Processing Multiple Files

Process all Excel and Word files in a folder:

```bash
# Process all files in CustomerDocument/ (default)
# Discovers both Excel (.xlsx, .xls, .xlsm) and Word (.docx, .doc, .docm) files
uv run python batch_extract_v2.py

# Process files in a custom folder
uv run python batch_extract_v2.py --folder /path/to/folder

# Resume interrupted processing (skips completed files)
uv run python batch_extract_v2.py --resume

# Custom status file
uv run python batch_extract_v2.py --status-file my_status.json
```

The batch processor:
- Automatically discovers all Excel files (`.xlsx`, `.xls`, `.xlsm`)
- Automatically discovers all Word files (`.docx`, `.doc`, `.docm`)
- Converts legacy formats (.doc, .docm) to .docx using LibreOffice
- Tracks progress in `batch_extraction_status.json`
- Supports resume for interrupted processing
- Saves all results to database and indexes vectors in Milvus
- Shows real-time progress and success/failure counts

**Status tracking:** Check `batch_extraction_status.json` to see:
- Which files are completed/processing/pending/failed
- Extraction metrics broken down by document type (Excel/Word) and format
- For Word files: chunk counts, average chunk size
- For Excel files: records, tables, vectors, duration
- Error messages for failed files

### Word Document Processing

Word documents are processed using the V2 extraction pipeline with semantic chunking:

**Pipeline:**
1. **Format Normalization:** `.doc` and `.docm` files are converted to `.docx` using LibreOffice CLI
2. **Preprocessing:** Images are captioned using Azure OpenAI GPT-4o vision, shapes and comments are extracted
3. **Conversion:** Docling converts Word → DoclingDocument (preserving tables, text, images)
4. **Semantic Chunking:** HybridChunker with OpenAI tokenizer (tiktoken) creates contextualized chunks
5. **Indexing:** Chunks are embedded and indexed in Milvus for semantic search

**Features:**
- Tables preserved in markdown format within chunks
- Images captioned and embedded inline
- Comments and annotations extracted
- Accurate token counting for text-embedding-3-large model
- Max 8000 tokens per chunk with contextualization

**Supported formats:**
- `.docx` - Modern Word format (2007+)
- `.doc` - Legacy Word format (97-2003) - converted via LibreOffice
- `.docm` - Macro-enabled Word documents - converted via LibreOffice (macros stripped)

### Running Unit Tests

```bash
# Run all tests
uv run pytest

# Run with coverage
uv run pytest --cov=src

# Run specific test file
uv run pytest tests/test_api/test_health.py

# Run comprehensive V2 pipeline validation (all 58 CustomerDocument files)
uv run pytest tests/test_extraction_v2/test_comprehensive_validation.py -v
```

### Comprehensive Pipeline Validation

Run automated tests against all 58 files in CustomerDocument/:

```bash
# Run comprehensive validation test suite
uv run pytest tests/test_extraction_v2/test_comprehensive_validation.py -v

# Check validation report
cat validation_report.json | jq '.success_rate'

# View failure details
cat validation_report.json | jq '.failure_details'
```

The test suite:
- Processes all 58 Excel files (.xlsx, .xls, .xlsm)
- Attempts V2 extraction first, falls back to V1 if needed
- Validates ≥95% overall success rate
- Generates `validation_report.json` with detailed metrics

Generate a markdown validation report:

```bash
# Generate comprehensive markdown report
uv run python validation_report_generator.py

# Custom output location
uv run python validation_report_generator.py \
  --status-file batch_extraction_status.json \
  --output custom_report.md
```

## Troubleshooting Extraction Failures

### Common Error Categories

The V2 pipeline categorizes errors to help diagnose issues quickly:

#### File-Related Errors

**File Corrupted** (`file_corrupted`)
- **Symptom:** `BadZipFile`, `Corrupted file`, or `Invalid Excel structure` errors
- **Cause:** ZIP structure damage in .xlsx/.xlsm files
- **Solution:**
  - Re-download the source file
  - Open and re-save in Excel to repair structure
  - Check if file opens correctly in Excel/LibreOffice
  - Try V1 fallback (uses different parsing method)

**Password Protected** (`password_protected`)
- **Symptom:** `Password required` or `Encrypted` errors
- **Cause:** File is password-protected or encrypted
- **Solution:**
  - Obtain the password and unlock the file
  - Request an unprotected version
  - Manually unlock in Excel and re-save

**File Too Large** (`file_too_large`)
- **Symptom:** `File too large: X.XMB` error
- **Cause:** File exceeds 100MB size limit
- **Solution:**
  - Split large files into smaller workbooks
  - Increase size limit in `validate_excel_file()` function
  - Remove unnecessary sheets or data

**Unsupported Format** (`unsupported_format`)
- **Symptom:** `Unsupported format` or `Invalid format` errors
- **Cause:** File is not a valid Excel format
- **Solution:**
  - Verify file extension matches content (e.g., not a CSV renamed to .xlsx)
  - Convert to .xlsx using Excel
  - Check file is not corrupted

#### Transient Errors (Auto-Retry)

These errors trigger automatic retry with exponential backoff (3 attempts):

**Timeout** (`timeout`)
- **Symptom:** `Connection timeout` or `Timed out` errors
- **Cause:** Network or service taking too long to respond
- **Solution:** Automatic retry handles this (1s, 2s, 4s delays)

**Rate Limit** (`rate_limit`)
- **Symptom:** `Rate limit exceeded` or `429` errors
- **Cause:** Azure OpenAI API rate limiting
- **Solution:**
  - Automatic retry with backoff
  - Check Azure OpenAI quota and limits
  - Consider increasing rate limits or spreading requests

**Network Error** (`network_error`)
- **Symptom:** `Connection error` or `Network` errors
- **Cause:** Network connectivity issues
- **Solution:** Automatic retry handles transient network issues

**Service Unavailable** (`service_unavailable`)
- **Symptom:** `503 Service Unavailable` errors
- **Cause:** Azure OpenAI or other service temporarily down
- **Solution:** Automatic retry with exponential backoff

#### Pipeline Errors

**Conversion Failed** (`conversion_failed`)
- **Symptom:** `Docling conversion failed` errors
- **Cause:** Docling unable to convert Excel to HTML
- **Solution:**
  - Check file opens correctly in Excel
  - Verify LibreOffice is installed (required for .xlsm)
  - Try V1 fallback (bypasses Docling conversion)

**LLM Error** (`llm_error`)
- **Symptom:** `OpenAI API error`, `GPT error`, or `LLM` errors
- **Cause:** Azure OpenAI API issues
- **Solution:**
  - Verify Azure OpenAI credentials in `.env`
  - Check API key is valid and not expired
  - Verify deployment name is correct
  - Check quota and rate limits

**Parsing Failed** (`parsing_failed`)
- **Symptom:** `HTML parsing failed` errors
- **Cause:** Unable to parse Docling HTML output
- **Solution:**
  - Report as potential bug (include file characteristics)
  - Try V1 fallback

#### System Errors

**Out of Memory** (`out_of_memory`)
- **Symptom:** `MemoryError` or `out of memory` errors
- **Cause:** File too complex or system memory limited
- **Solution:**
  - Close other applications
  - Increase system memory
  - Split complex files

**Disk Full** (`disk_full`)
- **Symptom:** `No space left on device` errors
- **Cause:** Insufficient disk space
- **Solution:** Free up disk space

### Checking Error Categories

View error distribution in batch processing:

```bash
# View failed files with categories
cat batch_extraction_status.json | jq '.files | to_entries[] | select(.value.status == "failed") | {filename: .value.filename, category: .value.error_category, error: .value.error}'

# Count by error category
cat batch_extraction_status.json | jq '.files | [.[] | select(.status == "failed") | .error_category] | group_by(.) | map({category: .[0], count: length})'
```

### V1 Fallback Behavior

The V2 pipeline automatically falls back to V1 for certain errors:
- The test suite (`test_comprehensive_validation.py`) implements V1 fallback
- Manual fallback: Use `test_pipeline_manual.py` and select option 1 (V1 pipeline)

### Getting Help

If you encounter persistent failures:

1. **Check the error category** in `batch_extraction_status.json`
2. **Review the validation report** generated by `validation_report_generator.py`
3. **Try V1 fallback** for comparison
4. **Examine the file** manually in Excel to identify structural issues
5. **Report bugs** with file characteristics (not the file itself if sensitive)

## Project Structure

```
dsol/
├── src/
│   ├── api/          # FastAPI application
│   ├── core/         # Configuration and exceptions
│   ├── db/           # Database models and migrations
│   ├── extraction/   # Document processing pipeline
│   ├── knowledge/    # Knowledge base management
│   ├── retrieval/    # Query retrieval logic
│   ├── qa/           # Answer generation
│   ├── llm/          # Azure OpenAI integration
│   ├── models/       # Pydantic data models
│   └── workers/      # Celery background tasks
├── tests/            # Test suite
├── scripts/          # Utility scripts
└── docs/             # Documentation
```

## Technology Stack

| Component | Technology | Version |
|-----------|------------|---------|
| Language | Python | 3.11+ |
| API Framework | FastAPI | 0.122 |
| Database | PostgreSQL | 18 |
| Vector Store | Milvus | 2.4 |
| Task Queue | Celery + Redis | 5.3+ / 7 |
| LLM | Azure OpenAI | GPT-4o |
| Document Conversion | Docling | 2.63.0+ |
| Excel Processing | openpyxl | 3.1.5 |
| Word Processing | python-docx | Latest |
| Format Conversion | LibreOffice CLI | 7.0+ |
| Semantic Chunking | docling-core HybridChunker | Latest |
| Tokenization | tiktoken | 0.12.0 |

## License

Proprietary - TextIQ