# Epic Technical Specification: Foundation & API Skeleton

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

---

## Overview

Epic 1 establishes the foundational infrastructure for DSOL, creating the development environment, database schema, and FastAPI application shell that all subsequent epics build upon. This epic delivers no user-facing functionality but provides the essential scaffolding that enables document upload (Epic 2), extraction (Epic 3), indexing (Epic 4), and Q&A (Epic 5) capabilities.

The foundation follows a pipeline architecture with FastAPI as the API gateway, PostgreSQL for structured data, Milvus for vector search, and Celery+Redis for async processing. All infrastructure decisions align with the architecture document's technology choices: Python 3.11+, FastAPI 0.122, PostgreSQL 18, Milvus 1.16, and Azure OpenAI.

## Objectives and Scope

**In Scope:**
- Project scaffolding with Poetry dependency management
- Docker Compose configuration for local development (PostgreSQL 18, Redis 7, Milvus 1.16)
- Database schema with all 6 tables defined in architecture.md
- Alembic migrations infrastructure
- FastAPI application shell with route structure
- Structured logging with structlog (JSON format)
- Health check endpoint with dependency monitoring
- Error handling middleware and base exception classes
- Environment configuration via Pydantic Settings

**Out of Scope:**
- Document upload functionality (Epic 2)
- API key authentication implementation (Epic 2)
- Any extraction, indexing, or Q&A logic (Epics 3-5)
- Production deployment configuration
- CI/CD pipeline setup

## System Architecture Alignment

This epic implements the core infrastructure layer defined in architecture.md:

**Components Created:**
- `src/api/` - FastAPI application structure (main.py, routes/, middleware.py, dependencies.py)
- `src/core/` - Configuration and exception handling (config.py, exceptions.py)
- `src/db/` - Database layer (session.py, models.py, migrations/)

**Technology Stack Alignment:**
| Component | Architecture Spec | This Epic |
|-----------|------------------|-----------|
| Python | 3.11+ | 3.11+ |
| FastAPI | 0.122 | 0.122 |
| PostgreSQL | 18 | 18 (via Docker) |
| Redis | 7+ | 7 (via Docker) |
| Milvus | 1.16 | 1.16 (via Docker) |
| structlog | Latest | Latest |
| Alembic | Latest | Latest |

**Constraints Honored:**
- All environment configuration via environment variables
- UTC timestamps throughout
- snake_case for Python modules/functions, PascalCase for classes
- JSONB columns for flexible content storage

## Detailed Design

### Services and Modules

| Module | Responsibility | Dependencies |
|--------|---------------|--------------|
| `src/api/main.py` | FastAPI app initialization, lifespan management | FastAPI, routes, middleware |
| `src/api/routes/health.py` | Health check endpoint | PostgreSQL, Redis, Milvus clients |
| `src/api/middleware.py` | CORS, error handling, request logging | structlog |
| `src/api/dependencies.py` | Dependency injection setup | SQLAlchemy session |
| `src/core/config.py` | Pydantic Settings for env vars | pydantic-settings |
| `src/core/exceptions.py` | DSOLError base class hierarchy | None |
| `src/db/session.py` | SQLAlchemy async session factory | SQLAlchemy, asyncpg |
| `src/db/models.py` | ORM model definitions | SQLAlchemy |

### Data Models and Contracts

**SQLAlchemy ORM Models (src/db/models.py):**

```python
# Base model with common fields
class Base(DeclarativeBase):
    pass

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] = mapped_column(Text, nullable=True)
    sheet_count: Mapped[int] = mapped_column(Integer, nullable=True)
    record_count: Mapped[int] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    processed_at: Mapped[datetime] = mapped_column(nullable=True)
    api_key_id: Mapped[UUID] = mapped_column(ForeignKey("api_keys.id"), nullable=True)

class ExtractedRecord(Base):
    __tablename__ = "extracted_records"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    document_id: Mapped[UUID] = mapped_column(ForeignKey("documents.id", ondelete="CASCADE"))
    sheet_name: Mapped[str] = mapped_column(String(255), nullable=False)
    row_number: Mapped[int] = mapped_column(Integer, nullable=False)
    col_range: Mapped[str] = mapped_column(String(50), nullable=True)
    content: Mapped[dict] = mapped_column(JSONB, nullable=False)
    resolved_content: Mapped[dict] = mapped_column(JSONB, nullable=True)
    headers: Mapped[list] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())

class SymbolDictionary(Base):
    __tablename__ = "symbol_dictionaries"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    document_id: Mapped[UUID] = mapped_column(ForeignKey("documents.id", ondelete="CASCADE"))
    sheet_name: Mapped[str] = mapped_column(String(255), nullable=True)
    symbol: Mapped[str] = mapped_column(String(50), nullable=False)
    meaning: Mapped[str] = mapped_column(Text, nullable=False)
    context: Mapped[str] = mapped_column(String(255), nullable=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())

class CrossReference(Base):
    __tablename__ = "cross_references"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    source_record_id: Mapped[UUID] = mapped_column(ForeignKey("extracted_records.id", ondelete="CASCADE"))
    target_record_id: Mapped[UUID] = mapped_column(ForeignKey("extracted_records.id", ondelete="SET NULL"), nullable=True)
    reference_text: Mapped[str] = mapped_column(Text, nullable=False)
    reference_type: Mapped[str] = mapped_column(String(50), nullable=True)
    resolved: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(default=func.now())

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] = 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] = mapped_column(nullable=True)

class QueryLog(Base):
    __tablename__ = "query_logs"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    api_key_id: Mapped[UUID] = mapped_column(ForeignKey("api_keys.id"), nullable=True)
    query_text: Mapped[str] = mapped_column(Text, nullable=False)
    answer_text: Mapped[str] = mapped_column(Text, nullable=True)
    confidence: Mapped[float] = mapped_column(Float, nullable=True)
    sources: Mapped[dict] = mapped_column(JSONB, nullable=True)
    processing_time_ms: Mapped[int] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(default=func.now())
```

**Pydantic Settings (src/core/config.py):**

```python
class Settings(BaseSettings):
    # Database
    database_url: str

    # Vector store
    milvus_url: str = "http://localhost:6333"
    milvus_collection: str = "dsol_records"

    # Azure OpenAI
    azure_openai_api_key: str
    azure_openai_endpoint: str
    azure_openai_api_version: str = "2024-02-15-preview"
    azure_openai_deployment: str
    azure_openai_embedding_deployment: str

    # Redis
    redis_url: str = "redis://localhost:6379"

    # API
    api_rate_limit: int = 60
    max_upload_size_mb: int = 50

    # Logging
    log_level: str = "INFO"

    model_config = SettingsConfigDict(env_file=".env")
```

### APIs and Interfaces

**Health Check Endpoint:**

| Method | Path | Request | Response | Errors |
|--------|------|---------|----------|--------|
| GET | `/health` | None | HealthResponse | 503 if critical dependency down |

**Response Model:**
```python
class DependencyHealth(BaseModel):
    status: Literal["healthy", "unhealthy"]
    latency_ms: Optional[int] = None
    error: Optional[str] = None

class HealthResponse(BaseModel):
    status: Literal["healthy", "degraded", "unhealthy"]
    version: str
    dependencies: dict[str, DependencyHealth]
```

**Example Response:**
```json
{
  "status": "healthy",
  "version": "1.0.0",
  "dependencies": {
    "database": {"status": "healthy", "latency_ms": 5},
    "redis": {"status": "healthy", "latency_ms": 2},
    "milvus": {"status": "healthy", "latency_ms": 8}
  }
}
```

### Workflows and Sequencing

**Application Startup Sequence:**

```
1. Load Settings from environment
2. Configure structlog (JSON in prod, pretty in dev)
3. Create SQLAlchemy async engine
4. Create Redis connection pool
5. Create Milvus client
6. Register middleware (CORS, error handling, request logging)
7. Include route modules
8. Yield (application ready)
9. On shutdown: close connections
```

**Health Check Sequence:**

```
GET /health
    │
    ├─> Check PostgreSQL (SELECT 1)
    │   └─> Record latency, status
    │
    ├─> Check Redis (PING)
    │   └─> Record latency, status
    │
    ├─> Check Milvus (list_collections)
    │   └─> Record latency, status
    │
    └─> Aggregate results
        ├─> All healthy → status: "healthy"
        ├─> Any unhealthy but app functional → status: "degraded"
        └─> Critical dependency down → 503
```

## Non-Functional Requirements

### Performance

| Metric | Target | Source |
|--------|--------|--------|
| Health check response | < 500ms | Story 1.5 |
| API startup time | < 5s | General |
| Database connection pool | 10-20 connections | Architecture |

**Implementation:**
- Connection pooling via SQLAlchemy async engine
- Redis connection pool with max connections
- Health checks with individual timeouts (300ms each)

### Security

| Requirement | Implementation | Source |
|-------------|----------------|--------|
| NFR11: HTTPS only | Configured at deployment layer | PRD |
| NFR12: API keys hashed | SHA-256 in api_keys.key_hash | PRD |
| NFR14: No sensitive logging | structlog filter for content fields | PRD |

**This Epic:**
- No authentication in Epic 1 (deferred to Epic 2)
- Health endpoint public (no auth required)
- Logging excludes document content

### Reliability/Availability

| Requirement | Implementation | Source |
|-------------|----------------|--------|
| NFR8: 99% uptime | Health check for monitoring | PRD |
| NFR9: Graceful errors | DSOLError hierarchy + middleware | PRD |
| NFR10: Data persistence | PostgreSQL ACID, connection retry | PRD |

**Error Handling Pattern:**
```python
# Middleware catches all exceptions
try:
    response = await call_next(request)
except DSOLError as e:
    return JSONResponse(status_code=400, content={"error": e.to_dict()})
except Exception:
    logger.exception("Unhandled error")
    return JSONResponse(status_code=500, content={"error": {"code": "INTERNAL_ERROR"}})
```

### Observability

| Signal | Implementation | Format |
|--------|----------------|--------|
| Logs | structlog | JSON with timestamp, level, event, request_id |
| Metrics | (Future) | Prometheus format |
| Tracing | request_id middleware | UUID per request |

**Required Log Fields:**
- `timestamp`: ISO 8601 UTC
- `level`: DEBUG/INFO/WARNING/ERROR
- `event`: Event name (e.g., "request_started", "health_check")
- `request_id`: UUID for request correlation
- `method`, `path`, `status_code`, `duration_ms` for HTTP requests

## Dependencies and Integrations

**Python Dependencies (pyproject.toml):**

```toml
[tool.poetry.dependencies]
python = "^3.11"
fastapi = "0.122"
uvicorn = {extras = ["standard"], version = "^0.30"}
pydantic = "^2.0"
pydantic-settings = "^2.0"
sqlalchemy = {extras = ["asyncio"], version = "^2.0"}
asyncpg = "^0.29"
alembic = "^1.13"
redis = "^5.0"
milvus-client = "^1.16"
structlog = "^24.0"
httpx = "^0.27"  # For health checks

[tool.poetry.group.dev.dependencies]
pytest = "^8.0"
pytest-asyncio = "^0.23"
black = "^24.0"
ruff = "^0.5"
mypy = "^1.10"
```

**Infrastructure Dependencies (docker-compose.yml):**

| Service | Image | Port | Purpose |
|---------|-------|------|---------|
| postgres | postgres:18 | 5432 | Structured data |
| redis | redis:7 | 6379 | Task queue, rate limiting |
| milvus | milvus/milvus:v1.16.0 | 6333 | Vector store |

**External Integrations:**
- Azure OpenAI: Not used in Epic 1 (credentials configured, not called)

## Acceptance Criteria (Authoritative)

1. **AC1:** Running `poetry install` completes without errors and installs all dependencies
2. **AC2:** Running `docker-compose up -d` starts PostgreSQL 18, Redis 7, and Milvus 1.16 containers
3. **AC3:** Running `alembic upgrade head` creates all 6 database tables with correct schema
4. **AC4:** Running `uvicorn src.api.main:app --reload` starts the API on port 8000
5. **AC5:** `GET /health` returns 200 with status "healthy" when all dependencies are up
6. **AC6:** `GET /health` returns status "degraded" with details when a non-critical dependency fails
7. **AC7:** OpenAPI docs are accessible at `/docs`
8. **AC8:** All API requests generate structured JSON logs with timestamp, level, event, and request_id
9. **AC9:** Log level is configurable via `LOG_LEVEL` environment variable
10. **AC10:** `.env.example` contains all required environment variables
11. **AC11:** Error responses follow format: `{"error": {"code": "...", "message": "..."}}`
12. **AC12:** Alembic rollback (`alembic downgrade -1`) successfully removes tables

## Traceability Mapping

| AC | Spec Section | Component(s) | Test Idea |
|----|--------------|--------------|-----------|
| AC1 | Dependencies | pyproject.toml | Run `poetry install` in clean env |
| AC2 | Dependencies | docker-compose.yml | Verify container health |
| AC3 | Data Models | src/db/models.py, migrations/ | Inspect tables in psql |
| AC4 | APIs | src/api/main.py | HTTP request to localhost:8000 |
| AC5 | APIs | src/api/routes/health.py | Integration test with all services |
| AC6 | Reliability | src/api/routes/health.py | Stop Redis, check response |
| AC7 | APIs | src/api/main.py | Browser access to /docs |
| AC8 | Observability | src/api/middleware.py | Check log output format |
| AC9 | Observability | src/core/config.py | Set LOG_LEVEL=DEBUG, verify |
| AC10 | Dependencies | .env.example | Manual review |
| AC11 | Reliability | src/core/exceptions.py | Trigger error, check format |
| AC12 | Data Models | migrations/ | Run downgrade, verify |

## Risks, Assumptions, Open Questions

**Risks:**
- **R1:** PostgreSQL 18 is recent; some tooling may not fully support it
  - *Mitigation:* SQLAlchemy 2.0 supports PostgreSQL 18; test early
- **R2:** Docker resource usage may be high for local development
  - *Mitigation:* Document minimum system requirements

**Assumptions:**
- **A1:** Development machine has Docker and Python 3.11+ installed
- **A2:** Azure OpenAI credentials will be provided before Epic 3
- **A3:** Single-tenant MVP; no multi-tenancy needed in foundation

**Open Questions:**
- **Q1:** Should we include pre-commit hooks in Epic 1 or defer?
  - *Recommendation:* Include for code quality from start
- **Q2:** Production deployment target (AWS, Azure, GCP)?
  - *Impact:* Affects Dockerfile optimization, not Epic 1

## Test Strategy Summary

**Test Levels:**

1. **Unit Tests:** Not primary focus for Epic 1 (mostly infrastructure)
2. **Integration Tests:**
   - Database connection and migrations
   - Health check with real dependencies
   - Middleware error handling
3. **Smoke Tests:**
   - Full docker-compose up → API responds
   - All containers healthy

**Test Framework:** pytest + pytest-asyncio

**Coverage Targets:**
- AC1-AC4: Manual verification during development
- AC5-AC6: Automated integration tests
- AC7-AC12: Mix of automated and manual

**Test Files:**
```
tests/
├── conftest.py           # Fixtures for DB, Redis, Milvus
├── test_api/
│   ├── test_health.py    # AC5, AC6
│   └── test_middleware.py # AC8, AC11
└── test_db/
    └── test_migrations.py # AC3, AC12
```

---

_Generated by BMAD Epic Tech Context Workflow_
_For: TextIQ_