Document to MXCP Ingestion

Supported Formats

• •Excel: .xlsx, .xls, .csv
• •Word: .docx (for .doc files, convert to .docx first using LibreOffice or similar)

Required Skills

Invoke these skills as needed during execution:

Always use the appropriate skill - don't try to implement Excel/Word parsing or MXCP operations from scratch.

Environment

• •Package manager: uv with virtual environment (never global installs)
• •Required packages: mxcp pandas openpyxl python-docx duckdb
• •Database: Always use default data/db-default.duckdb (auto-created by MXCP)

Core Principles

1. •Analyze first, ingest second. Fully understand the file before writing anything.
2. •Generate reproducible pipelines. Output is working scripts, not just ingested data. Scripts must be re-runnable from scratch to produce identical results.
3. •Code-first extraction. Always generate dbt models and scripts. Manual extraction is not reproducible.
4. •The project IS the state. Discover existing state from models/, tools/, rag_content/.
5. •Value-driven tools. Understand what information is valuable before creating tools.
6. •Test-first validation. Compute expected results from ORIGINAL SOURCE FILE (not database) before implementing tools.
7. •Ask when uncertain. If classification or linking is ambiguous, ask the user.

Execution Pipeline

Phase 0: Project Context

New project:

mkdir my-project && cd my-project
uv venv && source .venv/bin/activate
uv pip install mxcp pandas openpyxl python-docx duckdb
mxcp init --bootstrap

Create dbt_project.yml with model-paths: ["models"].

Existing project:

# Ensure venv is active
source .venv/bin/activate

# Verify/install dependencies
uv pip install mxcp pandas openpyxl python-docx duckdb

# Discover current state - MUST review before adding new files
ls models/*.py models/*.sql 2>/dev/null     # Existing dbt models
cat models/schema.yml                         # Table schemas, relationships
ls tools/*.yml 2>/dev/null                    # Existing tools
ls scripts/*.py 2>/dev/null                   # Existing RAG extraction scripts
ls rag_content/*.txt 2>/dev/null              # RAG content files

Before proceeding, understand:

• •What entities/tables already exist?
• •What tools are available and what do they query?
• •Will the new file's data overlap with existing data?
• •Should new data extend existing models or stay separate?

State Tracking & Changes

The project IS the state. Check source_data/, models/, rag_content/ before ingestion.

Phase 1: Deep Document Analysis

Goal: Fully understand the file BEFORE any ingestion decisions.

1.1 Excel Analysis

Analyze each sheet independently - sheets may have different content types and destinations.

For each sheet:

1. •Structure: merged cells (>10% = complex), header location, data boundaries
2. •Content type: Determine where on the spectrum:

1. •Multi-entity columns: Same data repeated for different entities
2. •If unclear: Use vision analysis. See vision-analysis.md.

Multi-entity pattern: When columns repeat per entity (e.g., Value_A, Value_B, Value_C):

• •Identify the entity dimension
• •Map columns to entities
• •Denormalize: create one row per (record, entity) with entity-specific columns

Hierarchical structure: Merged header cells with sub-headers underneath:

• •Flatten hierarchy: combine parent + child headers (e.g., "Q1 Revenue" from merged "Q1" + "Revenue")
• •Preserve hierarchy in column names or as separate dimension

Transposed tables: Headers in rows instead of columns:

• •Detect by checking if first column contains field names and other columns contain instances
• •Transpose before extraction: df = df.T or pivot appropriately

Explanatory content (NEVER lose this):

• •Cells or paragraphs explaining what data means → include as metadata or RAG
• •If structured data: add as column (e.g., _description) or separate lookup table
• •If RAG: include as context in the chunk
• •Rule: Every explanatory paragraph must appear somewhere in output

1.2 Word Document Analysis

Process paragraph by paragraph, table by table. Track section context from headings. For each content block, classify and decide destination.

Explanatory paragraphs: Paragraphs describing tables or sections must NOT be discarded:

• •Include with related RAG chunks as context
• •Or store in DB as metadata linked to the data they describe

1.3 Image Analysis

Documents may contain images with valuable information. Extract and analyze to avoid losing context.

Process: Extract images (python-docx for Word, openpyxl for Excel) → Vision classify informational vs decorative → Generate descriptions → Create RAG chunks.

Vision prompt: "Describe this chart/diagram: type, key data points, trends, labels, main insight."

RAG chunk format:

=== SOURCE ===
File: quarterly_report.docx
Section: Sales Analysis
Type: Bar chart

=== CONTENT ===
Bar chart showing Q1-Q4 revenue by region. North America leads at $2.3M...

1.4 Classification: Database vs RAG vs Both

The key question is NOT "is this tabular?" but "will queries be needed?"

Structured Text Patterns → Both DB + RAG:

Text that appears unstructured may have queryable structure. Look for:

For numbered sections:

• •Extract hierarchy: 1 is parent of 1.1, 1.2
• •Store in DB: section_id, parent_section_id, section_number, title, level
• •Store content in RAG with section reference
• •Enables: "Get section 1.1" (DB lookup) + "Find sections about security" (RAG search)

Decision process:

1. •Database only: Pure structured data (numbers, dates, categories)
2. •RAG only: Narrative text with no queryable identifiers
3. •Both: Text with queryable structure (sections, clauses, terms, IDs)

Converting tables to RAG: See data-classification.md for conversion examples.

When to ask user:

• •Unclear if queries will be needed on this data
• •Table has mixed numeric + text columns
• •Same data might need both approaches

1.5 Extraction Strategy

Always generate code - dbt models and scripts that can be re-run.

For large documents (>50 pages): Process section-by-section. See extraction-strategy.md.

Document extraction strategy per section before proceeding.

Phase 2: Integration Analysis

Skip if project is empty or file has only RAG content.

Detect keys and relationships:

• •PK: columns named id, {table}_id, key, code that are unique
• •FK: *_id patterns, values matching another table's PK

For each NEW table: Same entity exists? → extend or create combined view. FK columns? → link to existing tables.

When adding files to existing project:

• •Review existing models/, scripts/, tools/ before creating new ones
• •Same entity in multiple files? Create combined view or extend existing model
• •Never create duplicates - extend existing tools rather than creating _v2 versions

Document decisions before proceeding.

Phase 3: Generate Extraction Scripts

The deliverable is the PIPELINE, not just the data. Generate dbt models and scripts that:

• •Can be re-run from scratch to reproduce identical results
• •Update output automatically when source files change
• •Are version-controlled alongside the project

Folder Structure Per Source

All artifacts for a source file share the same folder name:

{source} = sanitized filename (e.g., sales_report)

models/{source}/          # dbt models for this source
tools/{source}/           # tools for querying this source
rag_content/{source}.txt  # single RAG file for this source
scripts/{source}/         # standalone scripts (if RAG-only)

Example for sales_report.xlsx:

models/sales_report/
├── load_orders.py        # → table: sales_report_orders
└── generate_rag.py       # generates RAG from DB (if Both)

tools/sales_report/
└── get_sales_report_order.yml

rag_content/sales_report.txt  # single file with sections

DB Only → dbt Models

Model requirements:

• •Place in models/{source}/ folder
• •Table name: {source}_{sheet} (e.g., sales_report_orders)
• •Read from source_data/{filename}
• •Clean column names: lowercase, underscores, alphanumeric only

Idempotency: dbt models are inherently idempotent - re-running mxcp dbt run rebuilds tables from source.

schema.yml (REQUIRED): Every model MUST have dbt tests. See testing.md.

Minimum tests: row count, not_null + unique on PK, relationships on FKs, accepted_values on categoricals.

RAG Only → Single File Extraction

One source file = one RAG file. Generate a clean, structured document:

# scripts/annual_report/extract_rag.py
import os
from docx import Document

source_name = "annual_report"
os.makedirs("rag_content", exist_ok=True)

doc = Document("source_data/annual_report.docx")
sections = []
current_section = None

for para in doc.paragraphs:
    if para.style.name.startswith('Heading'):
        if current_section:
            sections.append(current_section)
        current_section = {'title': para.text, 'content': []}
    elif para.text.strip() and current_section:
        current_section['content'].append(para.text)

if current_section:
    sections.append(current_section)

with open(f"rag_content/{source_name}.txt", "w") as f:
    for section in sections:
        f.write(f"## {section['title']}\n\n")
        f.write("\n\n".join(section['content']))
        f.write("\n\n")

Output format: See rag-format.md.

DB + RAG → Single RAG File from DB

# models/product_catalog/generate_rag.py
import os

def model(dbt, session):
    df = dbt.ref("product_catalog_items").df()
    os.makedirs("rag_content", exist_ok=True)

    with open("rag_content/product_catalog.txt", "w") as f:
        for _, row in df.iterrows():
            f.write(f"## {row['name']}\n\n{row['description']}\n\n")
    return df

Idempotency: RAG scripts overwrite the file completely - re-runs produce identical output.

Validate

mxcp validate && mxcp dbt run && mxcp dbt test

Phase 4: Data Value Analysis

Goal: Understand what information is valuable BEFORE creating tools.

Skip if only unstructured data (no tools needed for RAG-only content).

4.1 Domain Understanding

1. •Domain: Financial? Sales? HR? Operations?
2. •Users: What role? What decisions?
3. •Entities: Primary entities and relationships
4. •Metrics: Totals, counts, trends, rankings

4.2 Question Generation

Generate 5-15 concrete questions:

• •"What are the total sales for customer X?"
• •"Which products have the highest revenue?"
• •"List all orders from last month"

4.3 Compute Ground Truth

Compute expected values from the ORIGINAL SOURCE FILE, not the database.

# CORRECT: Read original file with pandas
df = pd.read_excel("source_data/sales_report.xlsx")
expected_count = len(df)
expected_total = df["amount"].sum()

# WRONG: Query database (could have extraction bugs)
# result = duckdb.query("SELECT COUNT(*) FROM sales")  # Don't use this!

If database results don't match source file calculations, the extraction has a bug.

Phase 5: Tool Design & Implementation

Invoke mxcp-expert skill for tool creation.

5.1 Design Principles

Each tool must be:

1. •Self-documenting - LLM understands without extra context
2. •Well-parametrized - Clear names, types, descriptions, examples
3. •Categorical values documented - Enum values listed in description and schema
4. •Focused - One tool = one question type
5. •Predictable - Same inputs → same outputs
6. •Domain-specific naming - Include source/topic in name and description

5.2 Domain-Specific Tool Naming

Critical: Multiple files may have identical structure but different topics. Tools must be distinguishable by the LLM consuming them.

Tool naming pattern: {action}_{source}_{entity}

• •get_sales_report_section
• •search_employee_handbook
• •get_compliance_policy_by_id

Description must state the source:

description: |
  Get a section from the Sales Report 2024 by section number.
  Use this for sales data, revenue analysis, and quarterly metrics.
  NOT for HR policies or compliance documents.

When multiple similar files exist: Make descriptions mutually exclusive so LLM knows exactly which tool handles which domain.

5.3 Categorical Data Handling

For columns with ≤20 unique values: add enum to parameter schema and list valid values in description.

5.4 MXCP Tool Tests (REQUIRED)

Every tool MUST include tests. See testing.md for complete format and examples.

Before writing each tool:

1. •Compute expected values from ORIGINAL SOURCE FILE (Excel/Word) using pandas/python-docx
2. •Add test cases with those pre-computed values
3. •Include edge cases (empty, single, multiple results)

Test values must come from source file, not database. If tool query returns different values than source file analysis, the extraction has a bug - fix the extraction, don't adjust the test.

Phase 6: Run All Tests (REQUIRED)

Both dbt and mxcp tests MUST pass before completion.

mxcp dbt run && mxcp dbt test  # Schema constraints
mxcp validate && mxcp test      # Tool behavior

If tests fail: See testing.md for debugging guide. Never just change expected values - fix root cause.

Phase 7: Summary

Document:

• •File processed, classification decisions
• •Tables created with dbt test results (pass/fail)
• •RAG chunks created
• •Tools created with mxcp test results (pass/fail)
• •All tests must show PASS before declaring completion

Output Structure

project/
├── mxcp-site.yml
├── dbt_project.yml
├── source_data/
│   └── {original files}
├── models/
│   ├── {source}/              # folder per source file
│   │   ├── load_*.py          # dbt models for DB ingestion
│   │   └── generate_rag.py    # RAG generation (if Both)
│   ├── combined_*.sql         # combined views across sources (if needed)
│   └── schema.yml
├── scripts/
│   └── {source}/              # folder per source file
│       └── extract_rag.py     # standalone RAG extraction
├── tools/
│   └── {source}/              # folder per source file
│       └── *.yml
├── sql/
│   └── *.sql
├── rag_content/
│   └── {source}.txt           # one file per source, .txt only
└── data/
    └── db-default.duckdb

Consistency rule: {source} name must match across models/, tools/, scripts/, and rag_content/{source}.txt.

When to Ask User

Error Handling

• •Content block analysis fails → Log, skip block, continue
• •Table extraction fails → Try alternative parsing, log if fails
• •Model build fails → Stop, fix
• •Test fails → Investigate root cause
• •Link detection uncertain → Skip link, log for review