# Story 1.2: Database Schema & Migrations

Status: done

## Story

As a **developer**,
I want **the database schema created with proper migrations**,
so that **I can persist documents, records, and query logs**.

## Acceptance Criteria

1. **AC1:** Running `alembic upgrade head` creates the following tables with correct schema:
   - `documents` (id UUID PK, filename VARCHAR(255), file_path VARCHAR(500), file_size_bytes INT, status VARCHAR(50), error_message TEXT, sheet_count INT, record_count INT, created_at TIMESTAMP, processed_at TIMESTAMP, api_key_id UUID FK)
   - `extracted_records` (id UUID PK, document_id UUID FK CASCADE, sheet_name VARCHAR(255), row_number INT, col_range VARCHAR(50), content JSONB, resolved_content JSONB, headers JSONB, created_at TIMESTAMP)
   - `symbol_dictionaries` (id UUID PK, document_id UUID FK CASCADE, sheet_name VARCHAR(255), symbol VARCHAR(50), meaning TEXT, context VARCHAR(255), created_at TIMESTAMP)
   - `cross_references` (id UUID PK, source_record_id UUID FK CASCADE, target_record_id UUID FK SET NULL, reference_text TEXT, reference_type VARCHAR(50), resolved BOOLEAN, created_at TIMESTAMP)
   - `api_keys` (id UUID PK, key_hash VARCHAR(255) UNIQUE, name VARCHAR(255), rate_limit_per_minute INT DEFAULT 60, is_active BOOLEAN DEFAULT true, created_at TIMESTAMP, last_used_at TIMESTAMP)
   - `query_logs` (id UUID PK, api_key_id UUID FK, query_text TEXT, answer_text TEXT, confidence FLOAT, sources JSONB, processing_time_ms INT, created_at TIMESTAMP)

2. **AC2:** All foreign key constraints are properly defined:
   - `documents.api_key_id` -> `api_keys.id`
   - `extracted_records.document_id` -> `documents.id` (CASCADE delete)
   - `symbol_dictionaries.document_id` -> `documents.id` (CASCADE delete)
   - `cross_references.source_record_id` -> `extracted_records.id` (CASCADE delete)
   - `cross_references.target_record_id` -> `extracted_records.id` (SET NULL on delete)
   - `query_logs.api_key_id` -> `api_keys.id`

3. **AC3:** GIN indexes are created on JSONB columns for fast JSON queries:
   - `idx_extracted_records_content` on `extracted_records.content`
   - `idx_extracted_records_resolved_content` on `extracted_records.resolved_content`
   - `idx_query_logs_sources` on `query_logs.sources`

4. **AC4:** Additional indexes exist for common query patterns:
   - `idx_documents_status` on `documents.status`
   - `idx_documents_api_key_id` on `documents.api_key_id`
   - `idx_extracted_records_document_id` on `extracted_records.document_id`
   - `idx_extracted_records_sheet_name` on `extracted_records.sheet_name`
   - `idx_symbol_dictionaries_document_id` on `symbol_dictionaries.document_id`
   - `idx_symbol_dictionaries_symbol` on `symbol_dictionaries.symbol`

5. **AC5:** Rollback migration works correctly:
   - Running `alembic downgrade -1` removes all tables
   - No orphaned objects remain after rollback

6. **AC6:** SQLAlchemy ORM models in `src/db/models.py` match the migration schema exactly

7. **AC7:** Alembic configuration is properly set up:
   - `alembic.ini` configured with async PostgreSQL URL
   - `src/db/migrations/env.py` configured for async SQLAlchemy
   - Migration files in `src/db/migrations/versions/`

## Tasks / Subtasks

- [x] **Task 1: Configure Alembic for async SQLAlchemy** (AC: 7)
  - [x] Create `alembic.ini` in project root with async configuration
  - [x] Update `src/db/migrations/env.py` for async support using `asyncpg`
  - [x] Configure Alembic to use `DATABASE_URL` from environment
  - [x] Test alembic can connect to PostgreSQL container

- [x] **Task 2: Implement SQLAlchemy ORM models** (AC: 6)
  - [x] Create `Base` declarative base class in `src/db/models.py`
  - [x] Implement `Document` model with all columns and relationships
  - [x] Implement `ExtractedRecord` model with JSONB columns
  - [x] Implement `SymbolDictionary` model
  - [x] Implement `CrossReference` model with both foreign keys
  - [x] Implement `ApiKey` model with unique constraint on key_hash
  - [x] Implement `QueryLog` model

- [x] **Task 3: Create initial migration** (AC: 1, 2)
  - [x] Generate migration with `alembic revision --autogenerate -m "initial_schema"`
  - [x] Review generated migration for correctness
  - [x] Ensure all foreign key constraints use correct ON DELETE behavior
  - [x] Add CASCADE for document-related records, SET NULL for cross-reference targets

- [x] **Task 4: Add indexes to migration** (AC: 3, 4)
  - [x] Add GIN indexes for JSONB columns (content, resolved_content, sources)
  - [x] Add B-tree indexes for common query columns
  - [x] Add index on symbol_dictionaries.symbol for symbol lookups
  - [x] Verify indexes are created in upgrade and dropped in downgrade

- [x] **Task 5: Test migration up and down** (AC: 1, 5)
  - [x] Run `alembic upgrade head` against PostgreSQL container
  - [x] Verify all 6 tables are created with correct schema
  - [x] Verify all indexes exist using `\di` in psql
  - [x] Run `alembic downgrade -1` and verify clean removal
  - [x] Run upgrade again to confirm idempotency

- [x] **Task 6: Update session.py for async engine** (AC: 7)
  - [x] Implement async SQLAlchemy engine factory in `src/db/session.py`
  - [x] Create async session maker
  - [x] Add `get_async_session` dependency for FastAPI

## Dev Notes

### Architecture Patterns and Constraints

- **Database:** PostgreSQL 18 with asyncpg driver
- **ORM:** SQLAlchemy 2.0+ with async support
- **Migrations:** Alembic with async configuration
- **UUID Primary Keys:** All tables use UUID as primary key (uuid_generate_v4())
- **Timestamps:** All `created_at` columns default to `func.now()` (UTC)
- **JSONB:** Used for flexible schema storage (content, resolved_content, headers, sources)
- **Cascade Deletes:** Document deletion cascades to extracted_records, symbol_dictionaries

### Project Structure Notes

From Story 1.1, the following files exist and need modification:
- `src/db/__init__.py` - exists (empty)
- `src/db/session.py` - exists (empty shell, needs async engine implementation)
- `src/db/models.py` - exists (empty shell, needs ORM models)
- `src/db/migrations/__init__.py` - exists (empty)

New files to create:
- `alembic.ini` - Alembic configuration
- `src/db/migrations/env.py` - Alembic environment with async support
- `src/db/migrations/versions/` - Directory for migration files
- `src/db/migrations/versions/<timestamp>_initial_schema.py` - Initial migration

### Database Schema Reference

From tech-spec-epic-1.md, the complete schema is:

```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] = 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)
```

### Learnings from Previous Story

**From Story 1-1-project-initialization (Status: done)**

- **Project Structure Created**: All `src/db/` files exist as empty shells - use these files, do not recreate
- **Docker Services Running**: PostgreSQL 18 is healthy at `localhost:5432` with credentials `dsol/dsol_dev_password/dsol`
- **PostgreSQL 18 Note**: Volume mount uses `/var/lib/postgresql` (not `/var/lib/postgresql/data`) due to PG18 directory structure change
- **Package Manager**: Project uses `uv` (not Poetry) - use `uv run alembic` commands
- **Dependencies Available**: SQLAlchemy, asyncpg, Alembic already installed via pyproject.toml

[Source: docs/sprint-artifacts/1-1-project-initialization.md#Dev-Agent-Record]

### References

- [Source: docs/sprint-artifacts/tech-spec-epic-1.md#Data-Models-and-Contracts] - Complete ORM model definitions
- [Source: docs/sprint-artifacts/tech-spec-epic-1.md#Detailed-Design] - Database layer architecture
- [Source: docs/architecture.md#Database-Schema] - Schema design decisions
- [Source: docs/epics.md#Story-1.2] - Original story definition

## Dev Agent Record

### Context Reference

N/A - Story context will be generated by story-context workflow

### Agent Model Used

Claude Opus 4.5 (claude-opus-4-5-20251101)

### Debug Log References

- Alembic autogenerate correctly detected all 6 tables and 9 custom indexes
- PostgreSQL 18 async connection via asyncpg worked without issues
- Downgrade/upgrade cycle completed successfully, confirming idempotent migrations

### Completion Notes List

- Implemented complete Pydantic Settings configuration in `src/core/config.py` for all environment variables
- Created 6 SQLAlchemy ORM models with proper relationships and cascade behaviors
- Configured Alembic for async PostgreSQL using asyncpg driver
- Generated initial migration with all tables, indexes, and foreign key constraints
- Verified migration upgrade/downgrade cycle works correctly
- Implemented async session factory with FastAPI dependency injection support

### File List

| Status | File Path |
|--------|-----------|
| MODIFIED | src/core/config.py |
| MODIFIED | src/db/models.py |
| MODIFIED | src/db/session.py |
| NEW | alembic.ini |
| NEW | src/db/migrations/env.py |
| NEW | src/db/migrations/script.py.mako |
| NEW | src/db/migrations/versions/20251126_211554_b88b5d952ac3_initial_schema.py |