# Story 3.4: Record Builder

Status: review

## Story

As a **system**,
I want **to convert extracted table rows into structured records**,
So that **each record has headers as keys and full source metadata**.

## Acceptance Criteria

1. **AC3.4.1:** Build records from resolved grid and headers
   - Input: resolved_grid (from Story 3.3), headers (from Story 3.2), TableBoundary (from Story 3.1)
   - Output: List of ExtractedRecord objects with content, headers, and source metadata
   - Each record represents one data row from the table

2. **AC3.4.2:** Map headers to cell values correctly
   - Use HeaderInfo.display as record keys (e.g., "Sales > Q1")
   - Preserve hierarchical header structure in record
   - Handle cases where columns have different header depths
   - Example: `{"Sales > Q1": 100, "Sales > Q2": 150, "Product": "Widget"}`

3. **AC3.4.3:** Handle different data types correctly
   - Empty cells → `null` (not empty string)
   - Numeric values → preserve as numbers (int or float)
   - Date values → convert to ISO 8601 string format
   - Boolean values → preserve as bool
   - Formula cells → use formula result (not formula itself, requires `data_only=True`)
   - Text values → preserve as strings

4. **AC3.4.4:** Include comprehensive source metadata for each record
   - document_id: UUID reference
   - filename: Original file name
   - sheet: Sheet name
   - table_id: Table identifier within sheet
   - row: Physical row number in Excel (1-indexed)
   - col_range: Column range (e.g., "A:D")
   - Example from PRD lines 94-96

5. **AC3.4.5:** Skip non-data rows intelligently
   - Completely empty rows (all cells null/empty)
   - Summary/total rows (heuristic: contains "Total", "Sum", "Average", etc.)
   - Repeated header rows within data
   - Log skipped rows with reason

6. **AC3.4.6:** Integrate merge metadata from Story 3.3
   - Include `_merged_from` annotation when cell value was propagated
   - Example: `{"Category": "Group A", "_merged_from": {"Category": "A2:A4"}}`
   - Allows downstream processing to know which values were inferred vs explicit

## Tasks / Subtasks

- [x] **Task 1: Create ExtractedRecord data model** (AC: 3.4.4)
  - [x] Add `ExtractedRecord` dataclass to `src/extraction/models.py`
  - [x] Fields: content (dict), headers (list[str]), _source (dict), _merge_metadata (dict, optional)
  - [x] Add validation: non-empty content, valid source fields
  - [x] Add `to_dict()` method for JSON serialization

- [x] **Task 2: Create RecordBuilder class** (AC: 3.4.1)
  - [x] Create `src/extraction/record_builder.py`
  - [x] Implement `RecordBuilder` class
  - [x] Implement `build_records(resolved_grid, headers, merge_metadata, table_boundary, document_id, filename)` method
  - [x] Return list[ExtractedRecord]

- [x] **Task 3: Implement row-to-record conversion** (AC: 3.4.2, 3.4.3)
  - [x] For each row in resolved_grid:
    - [x] Map each cell to its corresponding header
    - [x] Apply type conversion (null, number, date, bool, string)
    - [x] Handle empty cells as `null`
    - [x] Preserve numeric types (use `isinstance(cell.value, (int, float))`)
  - [x] Build content dict with header names as keys
  - [x] Create ExtractedRecord with content + source metadata

- [x] **Task 4: Implement data type handling** (AC: 3.4.3)
  - [x] Detect and convert date values to ISO 8601 strings
  - [x] Preserve boolean values (True/False)
  - [x] Preserve numeric values (int, float) without string conversion
  - [x] Handle formula results (use openpyxl `data_only=True` mode)
  - [x] Handle None/empty cells as `null`

- [x] **Task 5: Implement row filtering** (AC: 3.4.5)
  - [x] Detect empty rows: all cells are None or empty string
  - [x] Detect summary rows: keyword matching ("Total", "Sum", "Average", "Subtotal", etc.)
  - [x] Detect repeated header rows: compare row values with known headers
  - [x] Skip filtered rows and log reason (structured logging)
  - [x] Add configuration for custom skip keywords

- [x] **Task 6: Integrate merge metadata** (AC: 3.4.6)
  - [x] Accept merge_metadata list from Story 3.3
  - [x] Build _merge_metadata dict mapping column to range
  - [x] Include in ExtractedRecord when applicable
  - [x] Format: `{"_merged_from": {"Category": "A2:A4"}}`

- [x] **Task 7: Add source metadata generation** (AC: 3.4.4)
  - [x] Calculate physical row number (table_boundary.start_row + offset)
  - [x] Calculate col_range from table_boundary (start_col:end_col)
  - [x] Include document_id, filename, sheet, table_id in _source
  - [x] Ensure all source fields are present and valid

- [x] **Task 8: Unit tests** (AC: All)
  - [x] Create `tests/extraction/test_record_builder.py`
  - [x] Test ExtractedRecord model validation
  - [x] Test basic record building (simple table with headers + data)
  - [x] Test data type handling (null, numbers, dates, bools, strings)
  - [x] Test row filtering (empty, summary, repeated headers)
  - [x] Test merge metadata integration
  - [x] Test source metadata generation
  - [x] Test header-to-value mapping with multi-level headers

- [x] **Task 9: Integration tests** (AC: All)
  - [x] Add integration tests to `tests/extraction/test_integration.py`
  - [x] Test full pipeline: TableDetector → HeaderExtractor → MergedCellResolver → RecordBuilder
  - [x] Use existing sample Excel files from tests/fixtures/sample_excel/
  - [x] Verify records have correct content, headers, and source metadata
  - [x] Test with multi-level headers (test_two_level_headers.xlsx)
  - [x] Test with merged cells (test_merged_data.xlsx)

## Dev Notes

### Architecture Patterns and Constraints

From architecture.md:
- **Module location:** `src/extraction/record_builder.py` (architecture.md:70)
- **Naming conventions:**
  - Module: `record_builder.py` (snake_case)
  - Class: `RecordBuilder` (PascalCase)
  - Functions: `build_records()`, `_convert_cell_type()`, `_is_summary_row()` (snake_case)
- **Excel library:** openpyxl 3.1.5 with `data_only=True` for formula results
- **Error handling:** Use `ExtractionError(DSOLError)` with code/message/details
- **Logging:** structlog with context (sheet_name, table_id, records_built, rows_skipped, duration_ms)
- **Type hints:** Full type annotations required

From epics.md Story 3.4:
- **Prerequisites:** Story 3.2 (HeaderExtractor), Story 3.3 (MergedCellResolver)
- **Data Type Handling:** Preserve types - don't convert everything to strings
- **Row Filtering:** Use heuristics to skip summary/total rows
- **Formula Handling:** Use `data_only=True` to get results, not formulas

### Learnings from Previous Story

**From Story 3.3: Merged Cell Resolution (Status: review)**

- **New Service Created**: `MergedCellResolver` at `src/extraction/merged_cell_resolver.py` (294 lines)
  - `resolve_merged_cells(sheet, table_boundary)` → returns tuple of (resolved_grid, metadata)
  - resolved_grid: 2D list where resolved[row_idx][col_idx] = cell value
  - metadata: list[MergeMetadata] tracking propagated cells
  - Use this output as input to RecordBuilder

- **MergeMetadata Model Available**: `src/extraction/models.py:108-147`
  - Fields: cell_coordinate, merged_from_range, original_value
  - Story 3.4 should integrate this metadata into ExtractedRecord

- **Pipeline Pattern Established**: TableDetector → HeaderExtractor → MergedCellResolver → **RecordBuilder (this story)**
  - All components accept TableBoundary as input
  - All components use structured logging
  - All components return structured data (not raw openpyxl objects)

- **Testing Pattern**: Story 3.3 achieved 100% pass rate with:
  - 18 unit tests (model validation, core functionality, edge cases)
  - 16 integration tests with real Excel files
  - Sample Excel files in `tests/fixtures/sample_excel/`
  - Follow same testing structure for Story 3.4

- **Existing Models Available**:
  - `TableBoundary` (Story 3.1) - defines table region
  - `HeaderInfo` (Story 3.2) - multi-level header metadata
  - `MergeMetadata` (Story 3.3) - merged cell tracking
  - ADD: `ExtractedRecord` (Story 3.4) - structured record output

- **Known Patterns to Reuse**:
  - openpyxl cell access patterns
  - TableBoundary region filtering
  - Structured logging with context
  - ExtractionError for error handling
  - Full type annotations throughout

[Source: docs/sprint-artifacts/3-3-merged-cell-resolution.md#Dev-Agent-Record]

### Project Structure Notes

Expected module structure after Story 3.4:
```
src/extraction/
├── __init__.py
├── models.py              # ✓ Story 3.1, 3.2, 3.3 | ADD: ExtractedRecord
├── table_detector.py      # ✓ Story 3.1
├── header_extractor.py    # ✓ Story 3.2
├── merged_cell_resolver.py # ✓ Story 3.3
├── record_builder.py      # NEW: Story 3.4 - RecordBuilder class
└── pipeline.py            # FUTURE: Story 3.4a

tests/extraction/
├── __init__.py
├── test_table_detector.py         # ✓ Story 3.1
├── test_header_extractor.py       # ✓ Story 3.2
├── test_merged_cell_resolver.py   # ✓ Story 3.3
├── test_record_builder.py         # NEW: Story 3.4 unit tests
└── test_integration.py            # MODIFY: Add Story 3.4 integration tests

tests/fixtures/sample_excel/
├── test_standard_table.xlsx       # ✓ Story 3.1
├── test_two_level_headers.xlsx    # ✓ Story 3.2
├── test_three_level_headers.xlsx  # ✓ Story 3.2
├── test_merged_data.xlsx          # ✓ Story 3.3
└── test_complex_merges.xlsx       # ✓ Story 3.3
```

### Prerequisites and Dependencies

**Prerequisites:**
- Story 3.2 (Multi-Level Header Extraction) - ✓ REVIEW status
- Story 3.3 (Merged Cell Resolution) - ✓ REVIEW status

**Dependencies:**
- openpyxl 3.1.5: Excel file reading with `data_only=True` mode
- structlog: Structured logging
- TableBoundary from Story 3.1
- HeaderInfo from Story 3.2
- MergeMetadata from Story 3.3

**Database:** No database changes required for Story 3.4

**API:** No API changes required for Story 3.4 (internal extraction module)

### Implementation Strategy

**Record Building Flow:**

```python
# 1. Input: Resolved grid + headers + metadata from previous stories
resolved_grid = [[...]]  # From Story 3.3
headers = {"A": HeaderInfo(...), "B": HeaderInfo(...)}  # From Story 3.2
merge_metadata = [MergeMetadata(...), ...]  # From Story 3.3
boundary = TableBoundary(...)  # From Story 3.1

# 2. Initialize RecordBuilder
builder = RecordBuilder()

# 3. Build records from grid
records = builder.build_records(
    resolved_grid=resolved_grid,
    headers=headers,
    merge_metadata=merge_metadata,
    table_boundary=boundary,
    document_id=uuid,
    filename="spec.xlsx"
)

# 4. For each row in resolved_grid:
for row_idx, row_values in enumerate(resolved_grid):
    # Skip header rows (already processed)
    if row_idx < header_depth:
        continue

    # Skip filtered rows (empty, summary, repeated headers)
    if _should_skip_row(row_values):
        continue

    # Build content dict mapping headers to values
    content = {}
    for col_idx, value in enumerate(row_values):
        col_letter = get_column_letter(col_idx + start_col_idx)
        header_name = headers[col_letter].display

        # Convert cell value to appropriate type
        typed_value = _convert_cell_type(value)
        content[header_name] = typed_value

    # Build source metadata
    physical_row = boundary.start_row + row_idx
    col_range = f"{boundary.start_col}:{boundary.end_col}"
    source = {
        "document_id": document_id,
        "filename": filename,
        "sheet": boundary.sheet,
        "table_id": boundary.table_id,
        "row": physical_row,
        "col_range": col_range
    }

    # Build merge metadata dict
    merge_dict = {}
    for metadata in merge_metadata:
        if metadata.cell_coordinate matches this row:
            merge_dict[header_name] = metadata.merged_from_range

    # Create record
    record = ExtractedRecord(
        content=content,
        headers=list(headers.keys()),
        _source=source,
        _merge_metadata=merge_dict if merge_dict else None
    )
    records.append(record)

return records
```

**Data Type Conversion:**

```python
def _convert_cell_type(value):
    """Convert openpyxl cell value to appropriate Python type."""
    # None or empty string → null
    if value is None or value == "":
        return None

    # Already correct type (openpyxl handles most)
    if isinstance(value, (int, float, bool)):
        return value

    # Date/datetime → ISO 8601 string
    if isinstance(value, (datetime, date)):
        return value.isoformat()

    # Everything else → string
    return str(value).strip()
```

**Row Filtering Heuristics:**

```python
def _is_summary_row(row_values):
    """Detect if row is a summary/total row."""
    summary_keywords = ["total", "sum", "average", "subtotal", "count"]

    # Check first few cells for keywords
    for value in row_values[:3]:
        if value and isinstance(value, str):
            if any(keyword in value.lower() for keyword in summary_keywords):
                return True
    return False

def _is_empty_row(row_values):
    """Detect if row is completely empty."""
    return all(v is None or v == "" for v in row_values)

def _is_repeated_header(row_values, known_headers):
    """Detect if row repeats header values."""
    # Compare row values with known header names
    # If many match, likely a repeated header
    matches = sum(1 for v in row_values if v in known_headers)
    return matches >= len(row_values) * 0.7  # 70% threshold
```

### References

- [Source: docs/epics.md#Story-3.4] - Original story definition with examples
- [Source: docs/prd.md#Table-Aware-Extraction] - Record structure requirements (lines 89-96)
- [Source: docs/architecture.md#Project-Structure] - Module location (line 70)
- [Source: docs/architecture.md#Implementation-Patterns] - Error handling, logging, type hints
- [Source: docs/sprint-artifacts/3-1-table-boundary-detection.md] - TableBoundary model
- [Source: docs/sprint-artifacts/3-2-multi-level-header-extraction.md] - HeaderInfo model
- [Source: docs/sprint-artifacts/3-3-merged-cell-resolution.md] - MergeMetadata model, resolved_grid output

## Dev Agent Record

### Context Reference

N/A - Story context not generated (proceeded with story file only)

### Agent Model Used

Claude Sonnet 4.5 (claude-sonnet-4-5-20250929)

### Debug Log References

N/A - No issues encountered

### Completion Notes List

**Implementation Summary:**
- Successfully implemented Record Builder for converting table rows to structured records
- Created ExtractedRecord dataclass with comprehensive validation
- Implemented RecordBuilder class with full pipeline integration
- All acceptance criteria met and tested
- **Test Results:** 27/27 tests passed (100% pass rate)
  - 21 unit tests
  - 6 integration tests
  - All 102 extraction pipeline tests pass (no regressions)

**Key Implementation Details:**

1. **ExtractedRecord Model (src/extraction/models.py:151-215):**
   - Dataclass with content, headers, _source, _merge_metadata fields
   - Validation: non-empty content/headers, required source fields, valid UUID
   - to_dict() method for JSON serialization
   - Optional _merge_metadata for propagated cells

2. **RecordBuilder Class (src/extraction/record_builder.py:1-299):**
   - build_records() main public API - converts resolved grid to structured records
   - _convert_cell_type() handles null, int, float, bool, date→ISO8601, string
   - _is_empty_row() detects completely empty rows
   - _is_summary_row() detects Total/Sum/Average rows with keyword matching
   - _is_repeated_header() detects repeated headers (70% threshold)
   - _build_merge_lookup() creates coordinate→metadata mapping
   - Comprehensive error handling with ExtractionError
   - Structured logging with context (sheet_name, table_id, records_built, rows_skipped)

3. **Data Type Handling:**
   - None or "" → null
   - int, float, bool → preserved as-is
   - datetime, date → ISO 8601 string (isoformat())
   - Everything else → trimmed string

4. **Row Filtering:**
   - Empty rows: all cells None or empty string → skipped
   - Summary rows: "total", "sum", "average", "subtotal", "count" keywords → skipped
   - Repeated headers: 70% match with known headers → skipped
   - All skipped rows logged with reason

5. **Source Metadata Generation:**
   - document_id: UUID (string)
   - filename: Original filename
   - sheet: Sheet name
   - table_id: Table identifier
   - row: Physical Excel row (1-indexed)
   - col_range: "A:C" format

6. **Merge Metadata Integration:**
   - Accepts MergeMetadata list from Story 3.3
   - Builds _merge_metadata dict mapping header → range
   - Only included when cells were propagated
   - Format: {"Category": "A2:A4"}

7. **Pipeline Integration:**
   - Seamlessly integrates with Stories 3.1 (TableBoundary), 3.2 (HeaderInfo), 3.3 (resolved_grid + MergeMetadata)
   - Complete pipeline: TableDetector → HeaderExtractor → MergedCellResolver → RecordBuilder
   - All components use consistent patterns (structlog, ExtractionError, type hints)

**Test Coverage:**
- 21 unit tests: Model validation, record building, data types, row filtering, merge metadata, source metadata, error handling
- 6 integration tests: Full pipeline with simple tables, multi-level headers, merged cells, source accuracy, filtering, serialization
- All tests use existing sample Excel files (test_standard_table.xlsx, test_two_level_headers.xlsx, test_merged_data.xlsx)

**No Known Limitations or Issues**

### File List

| Status | File Path |
|--------|-----------|
| ✅ MODIFIED | src/extraction/models.py (added ExtractedRecord dataclass) |
| ✅ CREATED | src/extraction/record_builder.py (299 lines) |
| ✅ CREATED | tests/extraction/test_record_builder.py (622 lines, 21 tests) |
| ✅ MODIFIED | tests/extraction/test_integration.py (added 6 RecordBuilder integration tests) |