# 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.