# Epic Technical Specification: Document Upload & Management

Date: 2025-11-26
Author: TextIQ
Epic ID: 2
Status: Draft

---

## Overview

Epic 2 delivers the document management layer for DSOL, enabling clients to upload Excel documents, authenticate via API keys, track processing status, and manage their document library. This epic establishes the first tangible API functionality for end users, building on the Foundation (Epic 1) infrastructure.

The epic covers the complete document lifecycle: authentication → upload → validation → queueing → status tracking → deletion. Documents uploaded in this epic will be processed by the Extraction Pipeline (Epic 3) and indexed in the Knowledge Base (Epic 4).

## Objectives and Scope

### In Scope

- **API Key Authentication:** X-API-Key header authentication with SHA-256 hashed storage
- **Rate Limiting:** Per-key rate limiting (default 60 req/min) using Redis sliding window
- **Single Document Upload:** POST /documents with multipart/form-data Excel file upload
- **Batch Document Upload:** POST /documents/batch for up to 20 files simultaneously
- **File Validation:** Extension, magic bytes, size (≤50MB), corruption, and content checks
- **Document Status Tracking:** GET /documents/{id} with pending/processing/completed/failed states
- **Document Deletion:** DELETE /documents/{id} with cascade to all related data
- **Celery Task Queueing:** Async document processing via task queue

### Out of Scope

- Document content extraction (Epic 3)
- Knowledge base indexing (Epic 4)
- Q&A functionality (Epic 5)
- Multi-tenant document isolation (Growth Feature)
- S3 storage integration (Production - using local storage for MVP)

## System Architecture Alignment

### Components Referenced

| Component | Module Path | Purpose |
|-----------|-------------|---------|
| API Routes | `src/api/routes/documents.py` | Document upload, status, deletion endpoints |
| Auth Middleware | `src/api/middleware.py` | API key authentication and rate limiting |
| Dependencies | `src/api/dependencies.py` | Auth dependency injection |
| Workers | `src/workers/tasks.py` | Celery tasks for async processing |
| Database | `src/db/models.py` | Document and APIKey ORM models |

### Architecture Constraints

- **File Storage:** Local filesystem in development (`storage/{document_id}/{filename}`)
- **Task Queue:** Celery + Redis for async document processing
- **Auth Pattern:** API key in `X-API-Key` header, SHA-256 hashed storage
- **Error Format:** Standard DSOLError with code/message/details structure
- **Database:** PostgreSQL with existing schema from Story 1.2

## Detailed Design

### Services and Modules

| Module | Responsibility | Inputs | Outputs |
|--------|----------------|--------|---------|
| `src/api/routes/documents.py` | REST endpoints for document operations | HTTP requests | JSON responses |
| `src/api/middleware.py` | Auth validation, rate limiting | X-API-Key header | Authenticated request or 401 |
| `src/services/document_service.py` | Document business logic | File bytes, metadata | Document model |
| `src/services/auth_service.py` | API key validation | Key string | APIKey model or None |
| `src/services/file_validator.py` | File validation pipeline | File bytes | Validation result |
| `src/workers/tasks.py` | Async processing tasks | document_id | Processing status |

### Data Models and Contracts

#### Documents Table (existing from Story 1.2)

```python
class Document(Base):
    __tablename__ = "documents"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    filename: Mapped[str] = mapped_column(String(255), nullable=False)
    file_path: Mapped[str] = mapped_column(String(500), nullable=False)
    file_size_bytes: Mapped[int] = mapped_column(Integer, nullable=True)
    status: Mapped[str] = mapped_column(String(50), default="pending")
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    sheet_count: Mapped[int | None] = mapped_column(Integer, nullable=True)
    record_count: Mapped[int | None] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    processed_at: Mapped[datetime | None] = mapped_column(nullable=True)
    api_key_id: Mapped[UUID] = mapped_column(ForeignKey("api_keys.id"))
```

#### API Keys Table (existing from Story 1.2)

```python
class APIKey(Base):
    __tablename__ = "api_keys"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    key_hash: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    rate_limit_per_minute: Mapped[int] = mapped_column(Integer, default=60)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    last_used_at: Mapped[datetime | None] = mapped_column(nullable=True)
```

#### Pydantic Schemas

```python
# Request/Response models
class DocumentUploadResponse(BaseModel):
    document_id: UUID
    filename: str
    status: str
    message: str

class DocumentStatusResponse(BaseModel):
    document_id: UUID
    filename: str
    status: Literal["pending", "processing", "completed", "failed"]
    created_at: datetime
    processed_at: datetime | None = None
    sheet_count: int | None = None
    record_count: int | None = None
    error_message: str | None = None
    progress: DocumentProgress | None = None

class DocumentProgress(BaseModel):
    stage: str
    sheets_processed: int
    sheets_total: int

class BatchUploadResponse(BaseModel):
    batch_id: UUID
    documents: list[DocumentUploadResponse]
    errors: list[BatchUploadError]
    total: int
    message: str

class BatchUploadError(BaseModel):
    filename: str
    error: ErrorDetail
```

### APIs and Interfaces

#### POST /documents - Single Upload

```http
POST /documents
Content-Type: multipart/form-data
X-API-Key: {api_key}

file: {excel_file}
```

**Response (202 Accepted):**
```json
{
  "document_id": "uuid",
  "filename": "original_filename.xlsx",
  "status": "pending",
  "message": "Document queued for processing"
}
```

**Error Responses:**
- 401: Missing or invalid API key
- 400: Invalid file format
- 413: File too large (>50MB)
- 429: Rate limit exceeded

#### POST /documents/batch - Batch Upload

```http
POST /documents/batch
Content-Type: multipart/form-data
X-API-Key: {api_key}

files: [{excel_file1}, {excel_file2}, ...]
```

**Response (202 Accepted):**
```json
{
  "batch_id": "uuid",
  "documents": [
    {"document_id": "uuid1", "filename": "file1.xlsx", "status": "pending"},
    {"document_id": "uuid2", "filename": "file2.xlsx", "status": "pending"}
  ],
  "errors": [
    {"filename": "bad.txt", "error": {"code": "INVALID_EXTENSION", "message": "..."}}
  ],
  "total": 2,
  "message": "Batch queued for processing"
}
```

**Limits:** Max 20 files, 500MB total

#### GET /documents/{document_id} - Status

```http
GET /documents/{document_id}
X-API-Key: {api_key}
```

**Response (200 OK):**
```json
{
  "document_id": "uuid",
  "filename": "file.xlsx",
  "status": "completed",
  "created_at": "2025-11-26T10:00:00Z",
  "processed_at": "2025-11-26T10:00:45Z",
  "sheet_count": 10,
  "record_count": 1250,
  "symbols_found": 15,
  "cross_references": 32
}
```

#### DELETE /documents/{document_id} - Delete

```http
DELETE /documents/{document_id}
X-API-Key: {api_key}
```

**Response (200 OK):**
```json
{
  "document_id": "uuid",
  "status": "deleted",
  "message": "Document and all associated data removed"
}
```

**Error:** 409 Conflict if document is currently processing

### Workflows and Sequencing

#### Document Upload Flow

```
Client                 API                   Celery Worker         Storage
  │                     │                         │                   │
  │ POST /documents     │                         │                   │
  │ [X-API-Key, file]   │                         │                   │
  │─────────────────────>                         │                   │
  │                     │                         │                   │
  │                     │ Validate API key        │                   │
  │                     │ Validate file           │                   │
  │                     │                         │                   │
  │                     │ Save file───────────────────────────────────>
  │                     │                         │                   │
  │                     │ Create document record  │                   │
  │                     │ (status: pending)       │                   │
  │                     │                         │                   │
  │                     │ Queue task──────────────>                   │
  │                     │                         │                   │
  │<─────────────────────                         │                   │
  │ 202 Accepted        │                         │                   │
  │ {document_id}       │                         │                   │
  │                     │                         │                   │
  │                     │                         │ process_document()│
  │                     │                         │ Update status     │
  │                     │                         │ (processing)      │
```

#### Rate Limiting Flow

```
Request → Check Redis key (api_key:{key_hash}:requests)
        → If count > rate_limit → 429 Too Many Requests
        → Else → Increment counter (INCR with EXPIRE)
        → Process request
```

## Non-Functional Requirements

### Performance

| Metric | Target | Source |
|--------|--------|--------|
| File upload acceptance | < 2s for 50MB file | NFR1 |
| Status check response | < 100ms | NFR2 |
| Rate limiting check | < 10ms | Architecture |
| Concurrent uploads | ≥ 10 simultaneous | NFR4 |

### Security

| Requirement | Implementation |
|-------------|----------------|
| API key storage | SHA-256 hash, never plaintext (NFR12) |
| Rate limiting | Per-key sliding window in Redis (FR31) |
| File validation | Magic bytes + extension check |
| Document isolation | Documents scoped to API key (NFR13) |
| HTTPS | Required in production (NFR11) |

### Reliability/Availability

| Requirement | Implementation |
|-------------|----------------|
| Upload confirmation | 202 only after file persisted |
| Task retry | Celery auto-retry on failure (3 attempts) |
| Graceful degradation | Return partial batch results on individual failures |
| Data persistence | No data loss after 202 response (NFR10) |

### Observability

| Signal | Implementation |
|--------|----------------|
| Request logging | Method, path, status, duration, api_key_id |
| Upload metrics | File size, upload duration, validation result |
| Task metrics | Queue depth, processing time, failure rate |
| Rate limit events | Log when rate limit exceeded |

## Dependencies and Integrations

### Python Dependencies (from pyproject.toml)

| Package | Version | Purpose |
|---------|---------|---------|
| fastapi | 0.122 | API framework |
| python-multipart | ≥0.0.6 | File upload handling |
| celery | ≥5.3 | Task queue |
| redis | ≥5.0 | Rate limiting, task broker |
| openpyxl | 3.1.5 | Excel validation |
| sqlalchemy | ≥2.0 | ORM |
| pydantic | ≥2.0 | Request/response validation |

### External Integrations

| Integration | Purpose | Story |
|-------------|---------|-------|
| Redis | Rate limit counters, Celery broker | 2.1 |
| PostgreSQL | Document records, API keys | 2.2+ |
| Local filesystem | File storage (dev) | 2.2 |

## Acceptance Criteria (Authoritative)

### Story 2.1: API Key Authentication

1. **AC2.1.1:** Valid API key in X-API-Key header authenticates request
2. **AC2.1.2:** Missing API key returns 401 with code MISSING_API_KEY
3. **AC2.1.3:** Invalid API key returns 401 with code INVALID_API_KEY
4. **AC2.1.4:** API keys stored as SHA-256 hashes
5. **AC2.1.5:** Rate limiting applies per API key (default 60/min)
6. **AC2.1.6:** Rate limit exceeded returns 429 with retry-after header
7. **AC2.1.7:** last_used_at updated on each authenticated request
8. **AC2.1.8:** Script `scripts/create_api_key.py` generates new keys

### Story 2.2: Single Document Upload

1. **AC2.2.1:** POST /documents accepts multipart/form-data with Excel file
2. **AC2.2.2:** Returns 202 with document_id, filename, status: pending
3. **AC2.2.3:** File saved to storage/{document_id}/{filename}
4. **AC2.2.4:** Document record created with status pending
5. **AC2.2.5:** Celery task queued for processing
6. **AC2.2.6:** Files > 50MB return 413 Payload Too Large

### Story 2.3: File Validation

1. **AC2.3.1:** Extension must be .xlsx or .xls
2. **AC2.3.2:** Magic bytes match Excel format (PK for xlsx, D0 CF for xls)
3. **AC2.3.3:** File size ≤ 50MB
4. **AC2.3.4:** File opens successfully with openpyxl (not corrupted)
5. **AC2.3.5:** File has at least one sheet with data
6. **AC2.3.6:** Specific error codes: INVALID_EXTENSION, INVALID_FORMAT, FILE_TOO_LARGE, CORRUPTED_FILE, EMPTY_FILE

### Story 2.4: Batch Document Upload

1. **AC2.4.1:** POST /documents/batch accepts multiple files
2. **AC2.4.2:** Returns batch_id with individual document statuses
3. **AC2.4.3:** Valid files queued even if some fail validation
4. **AC2.4.4:** Errors array includes failed file details
5. **AC2.4.5:** Maximum 20 files per batch
6. **AC2.4.6:** Total batch size ≤ 500MB

### Story 2.5: Document Status Tracking

1. **AC2.5.1:** GET /documents/{id} returns current status
2. **AC2.5.2:** Status values: pending, processing, completed, failed
3. **AC2.5.3:** Processing status includes progress (stage, sheets_processed, sheets_total)
4. **AC2.5.4:** Completed status includes sheet_count, record_count
5. **AC2.5.5:** Failed status includes error_message
6. **AC2.5.6:** Document not found or wrong API key returns 404

### Story 2.6: Document Deletion

1. **AC2.6.1:** DELETE /documents/{id} removes document and all data
2. **AC2.6.2:** Returns 200 with status: deleted
3. **AC2.6.3:** Deletes: file, document record, extracted_records, symbol_dictionaries, cross_references, Milvus vectors
4. **AC2.6.4:** Cannot delete document with status processing (409 Conflict)
5. **AC2.6.5:** Deletion logged for audit trail

## Traceability Mapping

| AC | Spec Section | Component | Test Idea |
|----|--------------|-----------|-----------|
| AC2.1.1 | Security | AuthMiddleware | Valid key → 200 |
| AC2.1.2 | Security | AuthMiddleware | No header → 401 MISSING_API_KEY |
| AC2.1.3 | Security | AuthMiddleware | Bad key → 401 INVALID_API_KEY |
| AC2.1.4 | Data Models | APIKey model | Hash verification |
| AC2.1.5 | Performance | RateLimiter | 61st request → 429 |
| AC2.2.1 | APIs | POST /documents | Upload .xlsx |
| AC2.2.2 | APIs | DocumentUploadResponse | Response structure |
| AC2.2.3 | Workflows | FileService | File exists in storage |
| AC2.2.4 | Data Models | Document | DB record created |
| AC2.2.5 | Workflows | CeleryTask | Task in queue |
| AC2.3.1 | Data Models | FileValidator | .txt → INVALID_EXTENSION |
| AC2.3.2 | Data Models | FileValidator | Renamed .pdf → INVALID_FORMAT |
| AC2.3.3 | APIs | FileValidator | 51MB → FILE_TOO_LARGE |
| AC2.3.4 | Data Models | FileValidator | Corrupted → CORRUPTED_FILE |
| AC2.4.1 | APIs | POST /documents/batch | 3 files upload |
| AC2.4.5 | APIs | BatchUploadResponse | 21 files → 400 |
| AC2.5.1 | APIs | GET /documents/{id} | Status response |
| AC2.5.3 | Data Models | DocumentProgress | Progress fields |
| AC2.6.1 | APIs | DELETE /documents/{id} | Data removed |
| AC2.6.4 | APIs | DELETE | Processing → 409 |

## Risks, Assumptions, Open Questions

### Risks

| Risk | Impact | Mitigation |
|------|--------|------------|
| **R1:** Large file uploads may timeout | Medium | Streaming upload, increase timeout |
| **R2:** Redis unavailable breaks rate limiting | High | Fail-open pattern (allow requests), alert on Redis down |
| **R3:** File storage fills up | Medium | Monitor disk usage, implement cleanup job |

### Assumptions

| Assumption | Dependency |
|------------|------------|
| **A1:** Redis is available for rate limiting | Infrastructure (Story 1.1) |
| **A2:** Celery worker is running for async tasks | Deployment configuration |
| **A3:** Local filesystem has sufficient storage | Development environment |
| **A4:** openpyxl can validate all target Excel formats | Library capability |

### Open Questions

| Question | Status | Decision |
|----------|--------|----------|
| **Q1:** Should we support .xlsm (macro-enabled)? | Open | Defer to Growth Features |
| **Q2:** Retention policy for failed documents? | Open | Keep 7 days, then auto-delete |
| **Q3:** Should batch upload be transactional? | Decided | No - partial success allowed |

## Test Strategy Summary

### Test Levels

| Level | Scope | Framework |
|-------|-------|-----------|
| Unit | Service functions, validators | pytest |
| Integration | API endpoints with DB | pytest + TestClient |
| E2E | Full upload → status flow | pytest + real services |

### Key Test Cases

1. **Authentication Tests**
   - Valid key passes
   - Missing key returns 401
   - Invalid key returns 401
   - Expired/inactive key returns 401
   - Rate limit enforcement

2. **Upload Tests**
   - Valid .xlsx upload succeeds
   - Valid .xls upload succeeds
   - Invalid extension rejected
   - Corrupted file rejected
   - Oversized file rejected
   - Empty file rejected

3. **Batch Tests**
   - Multiple valid files succeed
   - Mixed valid/invalid returns partial success
   - Over 20 files rejected
   - Over 500MB total rejected

4. **Status Tests**
   - Pending document returns pending
   - Processing document shows progress
   - Completed document shows counts
   - Failed document shows error

5. **Deletion Tests**
   - Completed document deletes successfully
   - Processing document returns 409
   - Cascade removes all related data

### Coverage Targets

- Line coverage: ≥ 80%
- Branch coverage: ≥ 70%
- All AC have at least one test