# Schema Compatibility Plan: Word vs Excel Records (Metadata-Based Design)

**Date:** 2025-12-01
**Context:** Word document extraction produces different data structures than Excel
**Solution:** Unified schema with common top-level fields + metadata JSON for format-specific attributes
**Design:** All Excel and Word content stored in single Milvus collection

---

## Problem Statement

**Current Excel Schema (from RecordBuilder):**
```json
{
  "content": {
    "Header 1": "Value 1",
    "Header 2": "Value 2",
    "Nested > Header": "Value 3"
  },
  "_source": {
    "document_id": "uuid",
    "sheet": "Sheet1",
    "row": 5,
    "table_id": 0
  }
}
```

**Key Issues:**
1. ✗ No unified schema for multiple document types
2. ✗ Excel-specific fields (sheet, row) don't work for Word
3. ✗ Can't distinguish content types (tables vs paragraphs vs images)
4. ✗ Format-specific attributes scattered across structure
5. ✗ Not scalable for adding new document types (PDF, PowerPoint)

---

## Solution: Metadata-Based Unified Schema

### Design Principles

1. **Single Collection:** All content (Excel + Word) in one Milvus collection
2. **Common Top-Level Fields:** Only essential fields shared by all content types
3. **Metadata JSON Field:** All format-specific attributes go here
4. **Backward Compatible:** Existing Excel records can be migrated
5. **Scalable:** Easy to add new document types without schema changes
6. **Queryable:** Filter by content_type and metadata attributes

---

## Unified Milvus Schema

### Collection Schema (8 Core Fields)

```python
def create_collection(self):
    """Create unified Milvus collection for all document types."""
    fields = [
        # Common fields (all document types)
        FieldSchema(name="id", dtype=DataType.INT64, is_primary=True, auto_id=True),
        FieldSchema(name="embedding", dtype=DataType.FLOAT_VECTOR, dim=1536),
        FieldSchema(name="document_id", dtype=DataType.VARCHAR, max_length=100),
        FieldSchema(name="file_name", dtype=DataType.VARCHAR, max_length=255),
        FieldSchema(name="page", dtype=DataType.VARCHAR, max_length=100),  # Sheet OR page
        FieldSchema(name="content_type", dtype=DataType.VARCHAR, max_length=50),
        FieldSchema(name="content", dtype=DataType.VARCHAR, max_length=5000),  # Searchable text

        # All format-specific data goes here
        FieldSchema(name="metadata", dtype=DataType.JSON)
    ]

    schema = CollectionSchema(fields, description="Unified document content")
    collection = Collection(name="documents", schema=schema)

    # Indexes
    collection.create_index(
        field_name="content_type",
        index_params={"index_type": "INVERTED"}
    )
    collection.create_index(
        field_name="embedding",
        index_params={"metric_type": "COSINE", "index_type": "HNSW"}
    )

    return collection
```

### Field Descriptions

| Field | Type | Description |
|-------|------|-------------|
| `id` | INT64 | Auto-generated primary key |
| `embedding` | FLOAT_VECTOR(1536) | Vector embedding for semantic search |
| `document_id` | VARCHAR(100) | UUID from PostgreSQL documents table |
| `file_name` | VARCHAR(255) | Original filename (e.g., "report.xlsx") |
| `page` | VARCHAR(100) | Sheet name (Excel) OR page identifier (Word) |
| `content_type` | VARCHAR(50) | Content discriminator (see below) |
| `content` | VARCHAR(5000) | Searchable text representation |
| `metadata` | JSON | All format-specific attributes |

### Content Types

| content_type | Source | Description |
|--------------|--------|-------------|
| `table_row` | Excel OR Word | Tabular data from spreadsheets or doc tables |
| `paragraph` | Word | Text paragraphs |
| `heading` | Word | Document headings (h1-h6) |
| `list` | Word | Ordered/unordered lists |
| `image` | Word | Images with AI captions |
| `shape` | Word | Shapes/text boxes |
| `comment` | Word | Document comments |

---

## Sample Records

### 1. Excel Table Row

```json
{
  "id": 1001,
  "embedding": [0.023, -0.145, 0.567, ...],
  "document_id": "abc-123-excel",
  "file_name": "sales_report.xlsx",
  "page": "Q1 Sales",
  "content_type": "table_row",
  "content": "Product: Widget A | Revenue: $50000 | Quarter: Q1 | Growth: +15%",
  "metadata": {
    "sheet_name": "Q1 Sales",
    "row_number": 5,
    "table_id": 0,
    "column_count": 4,
    "has_formula": true,
    "formula_cells": ["D5"],
    "has_merged_cells": false,
    "cell_styles": {
      "B5": {"bold": true, "color": "green"}
    },
    "data": {
      "Product": "Widget A",
      "Revenue": "$50000",
      "Quarter": "Q1",
      "Growth": "+15%"
    }
  }
}
```

### 2. Word Paragraph

```json
{
  "id": 1002,
  "embedding": [0.112, -0.034, 0.789, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_1",
  "content_type": "paragraph",
  "content": "The Q1 sales exceeded expectations with Widget A leading revenue growth by 15%.",
  "metadata": {
    "paragraph_index": 3,
    "style": "body",
    "alignment": "left",
    "indentation": 0,
    "line_spacing": 1.15,
    "char_count": 82,
    "word_count": 13,
    "has_formatting": true,
    "formatting": {
      "bold_ranges": [[15, 28]],
      "italic_ranges": [[50, 58]]
    }
  }
}
```

### 3. Word Heading

```json
{
  "id": 1003,
  "embedding": [0.445, -0.223, 0.112, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_0",
  "content_type": "heading",
  "content": "Project Objectives",
  "metadata": {
    "heading_level": 2,
    "heading_index": 2,
    "style": "Heading 2",
    "numbering": null,
    "outline_level": 2,
    "is_toc_entry": true
  }
}
```

### 4. Word Table Row

```json
{
  "id": 1004,
  "embedding": [0.334, -0.221, 0.456, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_2",
  "content_type": "table_row",
  "content": "Task: Design Phase | Owner: Jane Smith | Status: Complete | Due: 2025-11-30",
  "metadata": {
    "table_id": 0,
    "row_number": 2,
    "column_count": 4,
    "has_merged_cells": false,
    "table_caption": "Project Timeline",
    "row_header": false,
    "data": {
      "Task": "Design Phase",
      "Owner": "Jane Smith",
      "Status": "Complete",
      "Due": "2025-11-30"
    }
  }
}
```

### 5. Word List

```json
{
  "id": 1005,
  "embedding": [0.556, -0.112, 0.334, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_1",
  "content_type": "list",
  "content": "Key deliverables: Document extraction pipeline, Vector search capability, API endpoints, Testing framework",
  "metadata": {
    "list_index": 0,
    "list_type": "unordered",
    "list_level": 0,
    "item_count": 4,
    "items": [
      "Document extraction pipeline",
      "Vector search capability",
      "API endpoints",
      "Testing framework"
    ],
    "bullet_style": "•"
  }
}
```

### 6. Word Image

```json
{
  "id": 1006,
  "embedding": [0.678, -0.445, 0.234, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_2",
  "content_type": "image",
  "content": "Architecture diagram showing the data flow from document upload through extraction pipeline to vector storage",
  "metadata": {
    "image_index": 0,
    "caption": "Architecture diagram showing the data flow from document upload through extraction pipeline to vector storage",
    "image_type": "inline",
    "width_px": 800,
    "height_px": 600,
    "format": "png",
    "ai_generated_caption": true,
    "context_before": "The system architecture is illustrated below:",
    "context_after": "As shown in the diagram, the pipeline consists of five stages."
  }
}
```

### 7. Word Comment

```json
{
  "id": 1007,
  "embedding": [0.223, -0.567, 0.445, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_1",
  "content_type": "comment",
  "content": "This needs to be updated with the latest timeline from the PM meeting on 2025-11-28",
  "metadata": {
    "comment_id": "1",
    "author": "John Doe",
    "author_initials": "JD",
    "date": "2025-11-28T14:30:00Z",
    "commented_text": "The project timeline is on track",
    "comment_text": "This needs to be updated with the latest timeline from the PM meeting on 2025-11-28",
    "is_resolved": false,
    "parent_comment_id": null
  }
}
```

### 8. Word Shape/Text Box

```json
{
  "id": 1008,
  "embedding": [0.889, -0.334, 0.112, ...],
  "document_id": "def-456-word",
  "file_name": "project_plan.docx",
  "page": "page_3",
  "content_type": "shape",
  "content": "IMPORTANT: All deliverables must be reviewed by the architect before implementation",
  "metadata": {
    "shape_index": 0,
    "shape_type": "text_box",
    "text": "IMPORTANT: All deliverables must be reviewed by the architect before implementation",
    "position": {
      "x": 100,
      "y": 200,
      "width": 300,
      "height": 100
    },
    "style": {
      "fill_color": "#FFFF00",
      "border_color": "#FF0000",
      "border_width": 2
    }
  }
}
```

---

## Metadata Field Specifications

### Excel: `table_row`

```json
{
  "sheet_name": "string",          // Excel sheet name
  "row_number": "integer",         // Row number in sheet (1-based)
  "table_id": "integer",           // Table identifier (0, 1, 2...)
  "column_count": "integer",       // Number of columns
  "has_formula": "boolean",        // Contains formula cells
  "formula_cells": ["string"],     // List of cell references with formulas (e.g., ["D5", "E10"])
  "has_merged_cells": "boolean",   // Contains merged cells
  "cell_styles": "object",         // Cell formatting info {"B5": {"bold": true, "color": "green"}}
  "data": "object"                 // Key-value pairs (headers → values)
}
```

### Word: `paragraph`

```json
{
  "paragraph_index": "integer",    // Position in document (0-based)
  "style": "string",               // Paragraph style name (e.g., "body", "normal")
  "alignment": "string",           // left, center, right, justify
  "indentation": "integer",        // Indentation in points
  "line_spacing": "float",         // Line spacing multiplier (e.g., 1.15, 1.5)
  "char_count": "integer",         // Character count
  "word_count": "integer",         // Word count
  "has_formatting": "boolean",     // Has bold/italic/underline
  "formatting": "object"           // {"bold_ranges": [[0, 10]], "italic_ranges": [[15, 25]]}
}
```

### Word: `heading`

```json
{
  "heading_level": "integer",      // 1-6 (h1, h2, h3, etc.)
  "heading_index": "integer",      // Position in heading hierarchy (0-based)
  "style": "string",               // Heading style name (e.g., "Heading 2")
  "numbering": "string|null",      // Numbering format (e.g., "1.2.3" or null)
  "outline_level": "integer",      // Outline level (1-9)
  "is_toc_entry": "boolean"        // Included in table of contents
}
```

### Word: `list`

```json
{
  "list_index": "integer",         // Position in document (0-based)
  "list_type": "string",           // "ordered" or "unordered"
  "list_level": "integer",         // Nesting level (0, 1, 2... for sub-lists)
  "item_count": "integer",         // Number of list items
  "items": ["string"],             // Array of list items
  "bullet_style": "string"         // Bullet character (•, -, *) or numbering format (1., a., i.)
}
```

### Word: `image`

```json
{
  "image_index": "integer",        // Position in document (0-based)
  "caption": "string",             // AI-generated caption
  "image_type": "string",          // "inline" or "floating"
  "width_px": "integer",           // Width in pixels
  "height_px": "integer",          // Height in pixels
  "format": "string",              // png, jpg, emf, wmf
  "ai_generated_caption": "boolean", // True if caption is AI-generated
  "context_before": "string",      // Text before image (for context)
  "context_after": "string"        // Text after image (for context)
}
```

### Word: `comment`

```json
{
  "comment_id": "string",          // Unique comment ID
  "author": "string",              // Comment author name
  "author_initials": "string",     // Author initials (e.g., "JD")
  "date": "datetime",              // Comment creation date (ISO 8601)
  "commented_text": "string",      // Text being commented on
  "comment_text": "string",        // Comment content
  "is_resolved": "boolean",        // Resolved status
  "parent_comment_id": "string|null" // For threaded comments (null if top-level)
}
```

### Word: `shape`

```json
{
  "shape_index": "integer",        // Position in document (0-based)
  "shape_type": "string",          // text_box, rectangle, circle, arrow, etc.
  "text": "string",                // Text content (if any)
  "position": "object",            // {"x": 100, "y": 200, "width": 300, "height": 100}
  "style": "object"                // {"fill_color": "#FFFF00", "border_color": "#FF0000", "border_width": 2}
}
```

### Word: `table_row` (from Word document)

```json
{
  "table_id": "integer",           // Table identifier (0, 1, 2...)
  "row_number": "integer",         // Row number in table (0-based)
  "column_count": "integer",       // Number of columns
  "has_merged_cells": "boolean",   // Contains merged cells
  "table_caption": "string",       // Table caption (if any)
  "row_header": "boolean",         // Is this a header row
  "data": "object"                 // Key-value pairs (headers → values)
}
```

---

## Implementation Changes

### 1. Update RecordBuilder (Excel)

**File:** `src/extraction_v2/record_builder.py`

```python
def build_milvus_record(
    self,
    row_data: list[str],
    flat_headers: list[str],
    document_id: UUID,
    file_name: str,
    sheet_name: str,
    row_number: int,
    table_index: int,
    has_formula: bool = False,
    formula_cells: list[str] = None
) -> dict[str, Any]:
    """Build Milvus record from Excel row."""

    # Build data object (headers → values)
    data = {}
    for col_idx, cell_value in enumerate(row_data):
        if col_idx < len(flat_headers):
            header_key = flat_headers[col_idx]
            if cell_value:
                data[header_key] = cell_value
        else:
            if cell_value:
                data[f"Column {col_idx}"] = cell_value

    # Build content string (searchable text)
    content = " | ".join([f"{k}: {v}" for k, v in data.items()])

    # Build Milvus record with metadata
    return {
        "document_id": str(document_id),
        "file_name": file_name,
        "page": sheet_name,
        "content_type": "table_row",
        "content": content,
        "metadata": {
            "sheet_name": sheet_name,
            "row_number": row_number,
            "table_id": table_index,
            "column_count": len(flat_headers),
            "has_formula": has_formula,
            "formula_cells": formula_cells or [],
            "has_merged_cells": False,  # TODO: Detect from workbook
            "cell_styles": {},  # TODO: Extract from workbook
            "data": data
        }
    }
```

### 2. Create ContentExtractor (Word)

**File:** `src/extraction_v2/content_extractor.py`

```python
class ContentExtractor:
    """Extract all content types from Word HTML with metadata."""

    def extract_paragraph(
        self,
        p_element: Tag,
        paragraph_index: int,
        document_id: UUID,
        file_name: str,
        page_name: str
    ) -> dict:
        """Extract paragraph with metadata."""
        text = p_element.get_text(strip=True)
        style = p_element.get("class", ["normal"])[0]

        return {
            "document_id": str(document_id),
            "file_name": file_name,
            "page": page_name,
            "content_type": "paragraph",
            "content": text,
            "metadata": {
                "paragraph_index": paragraph_index,
                "style": style,
                "alignment": "left",  # TODO: Extract from HTML/docx
                "indentation": 0,
                "line_spacing": 1.0,
                "char_count": len(text),
                "word_count": len(text.split()),
                "has_formatting": False,  # TODO: Detect bold/italic
                "formatting": {}
            }
        }

    def extract_heading(
        self,
        h_element: Tag,
        heading_level: int,
        heading_index: int,
        document_id: UUID,
        file_name: str,
        page_name: str
    ) -> dict:
        """Extract heading with metadata."""
        text = h_element.get_text(strip=True)

        return {
            "document_id": str(document_id),
            "file_name": file_name,
            "page": page_name,
            "content_type": "heading",
            "content": text,
            "metadata": {
                "heading_level": heading_level,
                "heading_index": heading_index,
                "style": f"Heading {heading_level}",
                "numbering": None,
                "outline_level": heading_level,
                "is_toc_entry": True
            }
        }

    def extract_list(
        self,
        list_element: Tag,
        list_index: int,
        document_id: UUID,
        file_name: str,
        page_name: str
    ) -> dict:
        """Extract list with metadata."""
        list_type = "ordered" if list_element.name == "ol" else "unordered"
        items = [li.get_text(strip=True) for li in list_element.find_all("li")]
        content = ", ".join(items)

        return {
            "document_id": str(document_id),
            "file_name": file_name,
            "page": page_name,
            "content_type": "list",
            "content": content,
            "metadata": {
                "list_index": list_index,
                "list_type": list_type,
                "list_level": 0,  # TODO: Detect nesting
                "item_count": len(items),
                "items": items,
                "bullet_style": "•" if list_type == "unordered" else "1."
            }
        }

    def extract_image(
        self,
        image_data: dict,
        image_index: int,
        document_id: UUID,
        file_name: str,
        page_name: str
    ) -> dict:
        """Extract image with AI-generated caption in metadata."""
        caption = image_data.get("caption", "")

        return {
            "document_id": str(document_id),
            "file_name": file_name,
            "page": page_name,
            "content_type": "image",
            "content": caption,
            "metadata": {
                "image_index": image_index,
                "caption": caption,
                "image_type": "inline",
                "width_px": image_data.get("width", 0),
                "height_px": image_data.get("height", 0),
                "format": image_data.get("format", "png"),
                "ai_generated_caption": True,
                "context_before": image_data.get("context_before", ""),
                "context_after": image_data.get("context_after", "")
            }
        }
```

### 3. Update Pipeline Integration

**File:** `src/extraction_v2/pipeline.py`

```python
async def _extract_word(self, file_path: str, document_id: UUID) -> list[dict]:
    """Extract from Word document and build Milvus records."""
    file_name = Path(file_path).name

    # Convert to HTML
    html = self.converter.convert_word_to_html(file_path)
    if not html:
        return []

    # Extract all content types
    extractor = ContentExtractor()
    soup = BeautifulSoup(html, "lxml")

    records = []

    # Extract paragraphs
    for idx, p in enumerate(soup.find_all("p")):
        if p.get_text(strip=True):
            record = extractor.extract_paragraph(
                p, idx, document_id, file_name, f"page_{idx // 50}"
            )
            records.append(record)

    # Extract headings
    heading_idx = 0
    for level in range(1, 7):
        for h in soup.find_all(f"h{level}"):
            record = extractor.extract_heading(
                h, level, heading_idx, document_id, file_name, f"page_0"
            )
            records.append(record)
            heading_idx += 1

    # Extract lists
    list_idx = 0
    for list_elem in soup.find_all(["ul", "ol"]):
        record = extractor.extract_list(
            list_elem, list_idx, document_id, file_name, f"page_0"
        )
        records.append(record)
        list_idx += 1

    # TODO: Extract tables, images, shapes, comments

    return records
```

### 4. Update Milvus Insertion

**File:** `src/knowledge/vector_store.py`

```python
async def insert_records(self, records: list[dict]):
    """Insert records into Milvus with embeddings."""

    # Generate embeddings for all records
    contents = [r["content"] for r in records]
    embeddings = await self.get_embeddings(contents)

    # Prepare data for insertion
    data = {
        "embedding": embeddings,
        "document_id": [r["document_id"] for r in records],
        "file_name": [r["file_name"] for r in records],
        "page": [r["page"] for r in records],
        "content_type": [r["content_type"] for r in records],
        "content": [r["content"] for r in records],
        "metadata": [r["metadata"] for r in records]
    }

    # Insert into Milvus
    self.collection.insert(data)
    self.collection.flush()
```

---

## Migration Strategy

### Phase 1: Schema Update (Milvus)

**Option A: Add metadata field to existing collection (if supported)**
```python
# Not all Milvus versions support schema evolution
# Check documentation for your Milvus version
collection.add_field(
    FieldSchema(name="metadata", dtype=DataType.JSON)
)
```

**Option B: Create new collection and migrate (recommended)**

```python
def migrate_to_metadata_schema():
    """Migrate existing records to new metadata-based schema."""

    # 1. Create new collection with metadata field
    new_collection = create_collection_with_metadata()

    # 2. Query all existing records
    old_records = old_collection.query(
        expr="id >= 0",
        output_fields=["*"]
    )

    # 3. Transform records to new schema
    new_records = []
    for record in old_records:
        new_record = {
            "document_id": record["document_id"],
            "file_name": record["file_name"],
            "page": record.get("page") or record.get("sheet"),  # Handle old "sheet" field
            "content_type": "table_row",  # All existing records are Excel
            "content": record["content"],
            "metadata": {
                "sheet_name": record.get("sheet", record.get("page")),
                "row_number": record.get("row", 0),
                "table_id": record.get("table_id", 0),
                "column_count": 0,  # TODO: Calculate from content
                "has_formula": False,
                "formula_cells": [],
                "has_merged_cells": False,
                "cell_styles": {},
                "data": {}  # TODO: Parse from content
            }
        }
        new_records.append(new_record)

    # 4. Generate embeddings (reuse if stored, otherwise regenerate)
    embeddings = [record.get("embedding") for record in old_records]

    # 5. Insert into new collection
    new_collection.insert({
        "embedding": embeddings,
        "document_id": [r["document_id"] for r in new_records],
        "file_name": [r["file_name"] for r in new_records],
        "page": [r["page"] for r in new_records],
        "content_type": [r["content_type"] for r in new_records],
        "content": [r["content"] for r in new_records],
        "metadata": [r["metadata"] for r in new_records]
    })

    # 6. Verify migration
    old_count = old_collection.num_entities
    new_count = new_collection.num_entities
    assert old_count == new_count, "Migration count mismatch!"

    # 7. Switch collections (update config to use new collection)
    # 8. Delete old collection (after verification period)
```

### Phase 2: Database Migration (PostgreSQL)

```python
# Alembic migration: add document_type and content counts
def upgrade():
    op.add_column('documents', sa.Column('document_type', sa.String(50), server_default='excel'))
    op.add_column('documents', sa.Column('file_extension', sa.String(10), nullable=True))
    op.add_column('documents', sa.Column('table_count', sa.Integer(), nullable=True))
    op.add_column('documents', sa.Column('paragraph_count', sa.Integer(), nullable=True))
    op.add_column('documents', sa.Column('image_count', sa.Integer(), nullable=True))
    op.add_column('documents', sa.Column('heading_count', sa.Integer(), nullable=True))
    op.add_column('documents', sa.Column('list_count', sa.Integer(), nullable=True))
    op.add_column('documents', sa.Column('comment_count', sa.Integer(), nullable=True))

    # Backfill document_type from filename
    op.execute("""
        UPDATE documents
        SET document_type = CASE
            WHEN filename LIKE '%.xlsx' OR filename LIKE '%.xls' OR filename LIKE '%.xlsm' THEN 'excel'
            WHEN filename LIKE '%.docx' OR filename LIKE '%.doc' OR filename LIKE '%.docm' THEN 'word'
            ELSE 'unknown'
        END,
        file_extension = SUBSTRING(filename FROM '\.[^.]+$')
    """)

def downgrade():
    op.drop_column('documents', 'comment_count')
    op.drop_column('documents', 'list_count')
    op.drop_column('documents', 'heading_count')
    op.drop_column('documents', 'image_count')
    op.drop_column('documents', 'paragraph_count')
    op.drop_column('documents', 'table_count')
    op.drop_column('documents', 'file_extension')
    op.drop_column('documents', 'document_type')
```

### Phase 3: Update Document Model

**File:** `src/db/models.py`

```python
class Document(Base):
    """Document model for uploaded files."""

    __tablename__ = "documents"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), 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[Optional[int]] = mapped_column(Integer, nullable=True)

    # Document type
    document_type: Mapped[str] = mapped_column(String(50), default="excel")
    file_extension: Mapped[str] = mapped_column(String(10), nullable=True)

    # Status
    status: Mapped[str] = mapped_column(String(50), default="pending")
    error_message: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # Excel-specific
    sheet_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

    # Universal counts
    record_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    table_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

    # Word-specific counts
    paragraph_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    heading_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    list_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    image_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    comment_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

    # Timestamps
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    processed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
```

---

## Query Examples

### 1. Search All Documents (Excel + Word)

```python
# Unified semantic search across all content types
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("Q1 sales Widget A"),
    limit=10
)
```

### 2. Filter by Content Type

```python
# Search only table rows (Excel OR Word)
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("revenue data"),
    filter="content_type == 'table_row'",
    limit=10
)

# Search only paragraphs (Word)
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("project overview"),
    filter="content_type == 'paragraph'",
    limit=10
)

# Search multiple content types
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("sales performance"),
    filter="content_type in ['table_row', 'paragraph']",
    limit=20
)
```

### 3. Filter by Metadata Attributes

```python
# Excel: Find rows with formulas
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("calculation"),
    filter="content_type == 'table_row' && metadata['has_formula'] == true",
    limit=10
)

# Excel: Search specific sheet
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("quarterly results"),
    filter="metadata['sheet_name'] == 'Q1 Sales'",
    limit=10
)

# Word: Find level 2 headings
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("objectives"),
    filter="content_type == 'heading' && metadata['heading_level'] == 2",
    limit=5
)

# Word: Find AI-generated image captions
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("architecture diagram"),
    filter="content_type == 'image' && metadata['ai_generated_caption'] == true",
    limit=5
)

# Word: Find unresolved comments
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("review needed"),
    filter="content_type == 'comment' && metadata['is_resolved'] == false",
    limit=10
)
```

### 4. Filter by Document Type

```python
# Search only Excel files
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("financial data"),
    filter="file_name like '%.xlsx'",
    limit=10
)

# Search only Word files
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("project plan"),
    filter="file_name like '%.docx'",
    limit=10
)
```

### 5. Complex Queries

```python
# Find Word paragraphs with >100 words about a topic
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("implementation strategy"),
    filter="content_type == 'paragraph' && metadata['word_count'] > 100",
    limit=10
)

# Find Excel tables from specific sheet with formulas
results = vector_store.search(
    collection_name="documents",
    query_vector=get_embedding("revenue calculation"),
    filter="content_type == 'table_row' && metadata['sheet_name'] == 'Revenue' && metadata['has_formula'] == true",
    limit=10
)
```

---

## Benefits of Metadata-Based Design

### ✅ Advantages

1. **Clean Schema:** Only 8 top-level fields (common to all content types)
2. **Single Collection:** All content (Excel + Word) in one unified collection
3. **Flexible:** Add new document types (PDF, PowerPoint) without schema changes
4. **Scalable:** Metadata JSON can hold any format-specific attributes
5. **Queryable:** Filter on metadata fields using JSON path expressions
6. **Backward Compatible:** Existing Excel records can be migrated with minimal changes
7. **No Schema Drift:** Adding new metadata fields doesn't require schema updates
8. **Future-Proof:** Easy to extend for new content types

### ⚠️ Considerations

1. **Migration Required:** Existing Excel records need to be transformed
2. **Metadata Query Performance:** JSON queries may be slower than indexed fields (mitigated by content_type index)
3. **Documentation:** Metadata structure must be well-documented for each content_type

---

## Testing Strategy

### 1. Schema Validation Tests

```python
def test_excel_metadata_schema():
    """Test Excel records have correct metadata structure."""
    record = build_excel_record()

    assert "metadata" in record
    assert "sheet_name" in record["metadata"]
    assert "row_number" in record["metadata"]
    assert "table_id" in record["metadata"]
    assert "data" in record["metadata"]

def test_word_paragraph_metadata_schema():
    """Test Word paragraphs have correct metadata structure."""
    record = build_word_paragraph()

    assert "metadata" in record
    assert "paragraph_index" in record["metadata"]
    assert "style" in record["metadata"]
    assert "word_count" in record["metadata"]

def test_metadata_queryable():
    """Test metadata fields are queryable in Milvus."""
    # Insert test records
    collection.insert(test_records)

    # Query by metadata
    results = collection.query(
        expr="content_type == 'table_row' && metadata['has_formula'] == true"
    )

    assert len(results) > 0
    assert all(r["metadata"]["has_formula"] for r in results)
```

### 2. Migration Tests

```python
def test_excel_migration_to_metadata():
    """Test migrating old Excel records to new metadata schema."""
    old_record = {
        "document_id": "abc-123",
        "file_name": "test.xlsx",
        "sheet": "Sheet1",
        "row": 5,
        "table_id": 0,
        "content": "A | B | C"
    }

    new_record = migrate_record(old_record)

    assert new_record["page"] == "Sheet1"
    assert new_record["content_type"] == "table_row"
    assert "metadata" in new_record
    assert new_record["metadata"]["sheet_name"] == "Sheet1"
    assert new_record["metadata"]["row_number"] == 5
```

### 3. Query Performance Tests

```python
def test_metadata_query_performance():
    """Test query performance with metadata filters."""
    import time

    # Insert 10,000 test records
    test_records = generate_test_records(10000)
    collection.insert(test_records)

    # Test query with metadata filter
    start = time.time()
    results = collection.query(
        expr="content_type == 'table_row' && metadata['has_formula'] == true",
        limit=100
    )
    duration = time.time() - start

    assert duration < 1.0  # Should complete in <1 second
    assert len(results) <= 100
```

---

## Summary

**Changes Required:**

1. ✅ **Milvus Schema:** Create new collection with 8 fields + metadata JSON
2. ✅ **RecordBuilder:** Update to build metadata-based records for Excel
3. ✅ **ContentExtractor:** Build metadata-based records for Word content types
4. ✅ **Pipeline:** Integrate ContentExtractor for Word documents
5. ✅ **Database Models:** Add document_type and content counts
6. ✅ **Migration:** Migrate existing Excel records to new schema
7. ✅ **API:** Support metadata-based filtering in queries
8. ✅ **Tests:** Schema validation, migration, and query tests

**Timeline:**
- Phase 1: Milvus schema update and migration (before Word support)
- Phase 2: PostgreSQL migration (parallel with Phase 1)
- Phase 3: Code updates for metadata-based records (Story 3 implementation)
- Phase 4: API updates for metadata filtering (Story 3 implementation)
- Phase 5: Testing and validation (Story 3 implementation)

**Backward Compatibility:**
- ✅ Existing Excel records migrated to metadata schema
- ✅ New code produces metadata-based records
- ✅ Queries work with both old and new data (after migration)
- ✅ No breaking changes to API (adds filtering capabilities)

**Scalability:**
- ✅ Single collection for all document types
- ✅ Easy to add new content types (just define metadata structure)
- ✅ Future-proof for PDF, PowerPoint, etc.
- ✅ Clean separation of common vs format-specific attributes

---

**Status:** Metadata-based schema design complete. Ready to implement in Story 3.
