Help with a Local Document RAG System (Storage + Ingestion + Query + Highlighting)

Beginners
1

Hey folks,

I’m working on designing a local, offline document retrieval + LLM pipeline and would love your input on the architecture. Here’s what I’m aiming for:

STORAGE

  • Upload PDF, DOCX, XLSX, CSV, tables
  • All data stored locally (no cloud)

DOCUMENT INGESTION

  • Watch folder (e.g., Watchdog) → auto-ingest on file add/modify/delete
  • Nested folder structure → auto-tagging
  • Supported formats: PDF, scanned PDF, DOCX, XLSX, CSV, JPG/PNG
  • Version control on re-upload

QUERY & RETRIEVAL

  • Restrict queries to a single client’s documents (no cross-client leakage)
  • Structured queries (e.g., “Show invoices > ₹1 lakh”)
  • Comparative queries (e.g., “Compare FY23 vs FY24 gross profit”)
  • Keyword fallback

HIGHLIGHTING & RENDERING

  • Annotated PDF served to frontend
  • XLSX → colored cell export
  • Jump directly to highlighted page
  • Multi-document highlights in one response

ANSWER GENERATION

  • Local LLM only
  • Every claim cited with doc + page reference

MY QUESTIONS

  1. Parsing: I’m considering LlamaIndex LiteParse.
    → Should I store document IDs + chunk IDs for PDFs to enable highlighting?

  2. Vector DB:

    • Do I need one (e.g., Qdrant)?
    • If yes, how do I store doc IDs + chunk IDs alongside embeddings for highlighting?
    • Would pgvector in Postgres be sufficient?

  3. GraphRAGs:

    • How effective are systems like Neo4j or Microsoft GraphRAG?
    • Can they run locally/offline, or are they too computationally heavy?
    • Is this GraphRAG pipeline from LlamaIndex a good starting point?

  4. Highlighting UX:

    • I want something like Turnitin/iThenticate reports → exact sentence highlighted + citation.
    • Any open-source projects that already do this?
    • I found Kotaemon and AnythingLLM, which are close but don’t highlight documents.

TL;DR
Trying to build a local RAG system with:

  • Storage + ingestion + tagging
  • Query + retrieval + highlighting
  • Local LLM answer generation with citations

Looking for advice on:

  • Vector DB vs pgvector
  • GraphRAG feasibility offline
  • Best way to implement document highlighting + citation preview

Would love to hear from anyone who’s built something similar or explored these tools.

2

Hmm… with PDFs and XLSX files, a lot of RAG projects get stuck before the actual RAG part starts. Anyway, as a general architecture, I would think about it like this:

Direct answers

1. Should you store document IDs and chunk IDs?

Yes, but I would treat document_id and chunk_id as only the minimum.

For highlighting, you probably want a more provenance-heavy model:

  • document_id
  • document_version_id
  • chunk_id
  • span_id
  • page number
  • character offsets
  • bounding boxes
  • parser element IDs
  • table IDs
  • sheet names
  • cell ranges
  • source text hash

A chunk is usually a retrieval unit.
A highlight needs an evidence unit.

That distinction matters a lot. A chunk may contain the answer, but it may still be too coarse to highlight the exact sentence, paragraph, table cell, or spreadsheet range.

For PDF visual citations, LiteParse visual citations are worth looking at because they focus on page screenshots and bounding boxes. I would not treat that as “just citation formatting”; it is closer to a document-layout/provenance problem.

2. Do you need a vector database?

For a local MVP, I would probably start with:

  • Postgres
  • pgvector
  • Postgres full-text search
  • structured extraction tables
  • optional reranking

The reason is that your system is not only semantic retrieval. You also need:

  • client isolation
  • versioning
  • file metadata
  • structured invoice queries
  • fiscal-period comparisons
  • extracted table data
  • audit trails
  • citation metadata
  • highlight metadata

Those are relational/database problems before they are vector-search problems.

So I would not make the vector DB the source of truth. I would make the document/version/provenance database the source of truth, and then attach vector search to that.

3. Would pgvector be sufficient?

Probably yes for the first serious local version.

pgvector is a good default if you want a single local system where SQL filters, metadata, document versions, and embeddings live close together.

I would move to Qdrant when vector search becomes its own subsystem:

  • larger collections
  • heavier vector-search tuning
  • more demanding payload filtering
  • separate scaling
  • separate retrieval infrastructure
  • need for a dedicated vector-search service

Qdrant is a very reasonable choice, especially for vector search with payload filters. I just would not start there unless you already know Postgres will be the bottleneck.

A simple rule:

Start with Postgres + pgvector if your hardest problems are metadata, versions, permissions, structured queries, and local simplicity. Add Qdrant when vector search itself becomes the hard part.

4. How should doc IDs and chunk IDs be stored with embeddings?

Whichever vector store you use, store the embedding together with enough metadata to get back to the original evidence.

Example payload / metadata:

{
  "client_id": "client_a",
  "document_id": "doc_123",
  "document_version_id": "doc_123_v4",
  "chunk_id": "chunk_0037",
  "page_start": 12,
  "page_end": 13,
  "span_ids": ["span_8801", "span_8802"],
  "parser": "docling_or_liteparse",
  "source_hash": "..."
}

But I would not try to put all visual-highlight data directly inside the vector DB.

Better split:

  • vector DB / pgvector row: retrieval metadata
  • relational tables: full provenance, spans, bboxes, cells, tables, versions
  • file store: original raw documents

The retrieval result should point back to the canonical evidence records.

5. What about GraphRAG?

GraphRAG can be useful, but I would not make it the first layer.

I would use GraphRAG later for questions like:

  • “Which companies, people, contracts, and obligations are connected?”
  • “What are the recurring themes across this corpus?”
  • “Which documents refer to the same entity or obligation?”
  • “Summarize relationships across many documents.”

I would not use it as the first tool for:

  • “Show invoices > ₹1 lakh.”
  • “Compare FY23 vs FY24 gross profit.”
  • “Search only this client’s documents.”
  • “Find this spreadsheet value.”

Those are structured extraction + SQL problems first.

Microsoft GraphRAG and Neo4j GraphRAG are good references, but I would add that layer only after the simpler system shows what relation/global-summary questions it cannot answer.

6. How should highlighting work?

I would separate citation from visual highlighting.

A citation can be:

  • document title
  • page number
  • chunk ID
  • quote
  • source link

A visual highlight needs more:

  • page coordinates
  • character offsets
  • bounding boxes
  • table cell coordinates
  • spreadsheet ranges
  • rendering/export logic

For PDF, you probably want one or both of:

For XLSX, the evidence unit is usually not a bounding box. It is more like:

workbook_version_id
sheet_name
cell_address
cell_range
table_id
row_header_context
column_header_context
displayed_value
formula

Then you can generate a colored spreadsheet copy with a library such as openpyxl.

7. Why existing tools may feel close but not enough

Many local RAG apps optimize for “chat with files.”

Your target sounds closer to “audit-grade document evidence.”

That is a different problem.

Existing tools often cite a chunk, a page, or a source node. You want to jump to the exact sentence, bounding box, table cell, or spreadsheet range. That is a stricter requirement.

This is probably why tools like Kotaemon / AnythingLLM / RAGFlow can feel close but still not fully match your UX target. I would study them as design references, but I would expect some custom provenance and rendering work.

Kotaemon is especially worth studying for citation/document-preview patterns. RAGFlow DeepDoc is worth studying for document parsing/OCR/layout recognition ideas. I would still design your own canonical provenance layer if you need strict PDF/XLSX highlighting and client isolation.

Longer architecture notes

Recommended mental model

I would not design this as “a vector database with chunks.”

I would design it as:

provenance-first document system
  + structured extraction
  + hybrid retrieval
  + local LLM answer generation
  + visual evidence rendering

The key design question is not only:

Can I retrieve the right chunk?

It is also:

Can I prove exactly where the answer came from in the original document version?

That means provenance should be captured during ingestion, before chunking destroys layout and table structure.

Suggested pipeline

watch folder
  -> raw file store
  -> parser / normalizer
  -> canonical document records
  -> structured extraction
  -> embeddings + full-text index
  -> hybrid retrieval
  -> reranking
  -> local LLM
  -> cited answer
  -> PDF/XLSX highlight rendering

In other words:

  1. Keep the original file.
  2. Parse it into a structured representation.
  3. Store provenance.
  4. Create retrieval chunks.
  5. Create structured records where appropriate.
  6. Retrieve with SQL + full-text + vector search.
  7. Generate an answer.
  8. Render citations/highlights using stored provenance.

Suggested storage layers

Layer Purpose
Raw file store Original PDF/DOCX/XLSX/CSV/images
Document table Logical document identity
Version table Re-upload/version tracking
Parsed pages/elements Layout, reading order, table structure
Chunks Retrieval units
Evidence spans Citation/highlight units
Structured records Invoices, amounts, dates, fiscal metrics
Embeddings Semantic retrieval
Full-text index Exact terms, names, IDs, invoice numbers
Retrieval logs Debugging and evaluation
Highlight records PDF bboxes / XLSX cell ranges

Minimal schema sketch

Something like this:

documents
document_versions
pages
elements
chunks
spans
tables
table_cells
spreadsheet_sheets
spreadsheet_cells
structured_records
embeddings
retrieval_runs
retrieval_hits

The exact schema depends on your app, but I would keep these concepts separate:

  • document identity
  • document version
  • parsed structure
  • retrieval chunk
  • evidence span
  • visual location
  • extracted structured fact

Do not collapse all of those into “chunk metadata” too early.

Retrieval strategy

Use different retrieval paths for different query types.

Query type Better first path
Exact keyword / invoice number Full-text search
Semantic question Vector search
Invoice threshold SQL over extracted records
Fiscal comparison Structured extraction + SQL
Table question Table-aware extraction
Spreadsheet value Cell/range lookup
Cross-document summary Hybrid retrieval + rerank
Relationship/theme question Maybe GraphRAG later

A useful local retrieval stack is:

SQL filter
  -> full-text search candidate set
  -> vector search candidate set
  -> merge
  -> rerank
  -> answer with citations

For example:

  1. Apply client_id filter.
  2. Apply document type/date/folder/version filters.
  3. Use SQL if the query is structured.
  4. Use full-text for exact terms.
  5. Use vector search for semantic recall.
  6. Rerank the final candidates.
  7. Send only evidence-backed context to the LLM.

Client isolation

Do not rely on the prompt for client separation.

Apply client/tenant restrictions before retrieval.

Bad:

retrieve from all clients
  -> ask LLM to only use client A

Better:

client_id filter
  -> retrieval
  -> rerank
  -> generation

This matters even more if you later add multiple retrieval paths. Every path needs the same tenant filter:

  • SQL
  • full-text
  • vector search
  • GraphRAG
  • document preview
  • citation lookup
  • highlight rendering

PDF/XLSX are their own abyss

PDF/XLSX are not normal “text documents.”

They are often the main problem.

Bad parsing will look like bad retrieval.
Bad retrieval will look like hallucination.
Bad provenance will look like broken citations.

For PDF/XLSX, I would avoid trying to solve every edge case in v1. The safer approach is:

  1. keep the raw file
  2. persist parser output
  3. preserve provenance before chunking
  4. build an ugly-file eval set
  5. only then tune retrieval

Useful parser/document references:

XLSX is not just another document format

I would not flatten XLSX into plain text too early.

A workbook can be:

Workbook type Better treatment
Database-like table Extract to relational table
Report sheet Preserve section/header context
Financial model Preserve formulas and references
Visual workbook Preserve sheet/cell/range evidence
Mixed workbook Split by sheet/table/section

At minimum, preserve:

Preserve Why
sheet name sheet-level citation
cell address exact evidence
cell range table/range citation
displayed value user-visible answer
formula audit/debug
number format money/date interpretation
merged range header/context recovery
hidden status avoid accidental evidence
row/column header context semantic meaning
named range/table range structured extraction

With XLSX, the visible spreadsheet and the underlying workbook structure are not always the same thing. Merged cells, formulas, hidden sheets, multiple tables, and manually formatted reports can all break naive text extraction.

For XLSX highlighting, I would generate an annotated copy rather than modifying the original file.

PDF highlighting caveats

For PDF highlights, store more than the bbox.

I would store:

Field Why
page number jump target
bbox / quad points visual highlight
page width / height coordinate normalization
page rotation avoid shifted highlights
coordinate system parser/viewer compatibility
render scale PDF.js overlay
source text hash detect stale parse
parser version debug drift

For browser preview, PDF.js-style overlay is convenient.
For exportable reports, PyMuPDF-style annotations are useful.

But coordinate systems can drift. Rotation, scale, page crop boxes, OCR output, and parser versions can all matter.

So I would store enough metadata to re-render or debug the highlight later.

Structured extraction

Do not ask embeddings to do SQL’s job.

For examples like:

Show invoices > ₹1 lakh
Compare FY23 vs FY24 gross profit

I would extract structured records first.

Example tables:

invoices
invoice_line_items
financial_periods
financial_metrics
entities
dates
amounts

Then answer structured questions with SQL, and use RAG only to provide explanations and citations.

Example:

SQL finds the invoice rows
  -> evidence spans/cells locate source
  -> LLM explains result
  -> citations/highlights point to source

That is more reliable than asking vector search to retrieve “large invoices” semantically.

GraphRAG position

I would add GraphRAG only after the basic system is working.

Good GraphRAG use cases:

  • entity relationship discovery
  • corpus-level summaries
  • theme clustering
  • contract/person/company relationships
  • multi-hop relationship questions

Weak first-use cases:

  • invoice thresholds
  • fiscal calculations
  • client filters
  • spreadsheet values
  • exact citation highlighting

If you add GraphRAG, still keep the graph connected to source spans. Otherwise the graph may answer something that cannot be visually proven in the original file.

Evaluation: build this early

I would create a small ugly-file evaluation set before tuning retrieval.

Do not only evaluate the final answer. Evaluate each layer:

Check Meaning
parse_ok Was the relevant content preserved by the parser?
doc_hit Did retrieval find the right document?
page_or_sheet_hit Did it find the right page/sheet?
span_or_cell_hit Did it find the exact evidence span/cell?
answer_ok Is the answer correct?
citation_ok Does the citation support the answer?
highlight_ok Does the visual highlight land correctly?
tenant_ok Did client isolation hold?

A starter evaluation set could be 30-50 questions:

Category Example
Simple PDF lookup “What is the invoice date?”
PDF page citation “Which page mentions X?”
PDF highlight “Highlight the sentence supporting Y.”
Scanned PDF/OCR “Find this value in a scanned invoice.”
XLSX cell lookup “What is FY24 gross profit?”
XLSX range citation “Which cells support this total?”
Table extraction “Show invoices > ₹1 lakh.”
Multi-document comparison “Compare FY23 vs FY24.”
Client isolation “Search only client A.”
Abstention “Ask for a value not present.”

Useful evaluation references:

For this system, the eval set should test evidence location, not only answer correctness.

Component references

I would not treat these as “use this product instead” recommendations. They are useful as component/design references.

Project / tool Why it is relevant
LiteParse visual citations, spatial text parsing, bounding boxes
Docling broad document conversion, layout, tables, OCR
Marker document-to-Markdown/JSON/chunks/HTML conversion
Kotaemon PDF citation UI and document-preview patterns
RAGFlow DeepDoc OCR/layout/table-structure recognition ideas
OpenContracts document annotation / extraction / QA reference
LexReviewer grounded PDF review with bounding-box citations
PyMuPDF PDF annotation/export
PDF.js browser-side PDF rendering/overlay
openpyxl XLSX highlight/export

Minimal roadmap

Phase 1: Provenance-first ingestion

  • raw file storage
  • document versioning
  • parser output persistence
  • page/element/span storage
  • chunking
  • pgvector
  • full-text search
  • basic citations

Phase 2: Structured extraction

  • invoice fields
  • dates
  • amounts
  • parties
  • fiscal periods
  • spreadsheet tables
  • table/cell references

Phase 3: Strong retrieval

  • SQL routing
  • full-text search
  • vector search
  • reranker
  • retrieval logs
  • small evaluation set

Phase 4: Visual evidence

  • PDF page jump
  • PDF bbox overlay
  • annotated PDF export
  • XLSX colored cell export

Phase 5: Optional advanced layers

  • Qdrant, if vector search outgrows Postgres
  • GraphRAG, if entity/relation/global-summary questions matter
  • visual retrieval, if scanned/layout-heavy PDFs defeat text extraction

Practical default stack

If I had to choose a default local stack, I would start with:

Postgres
+ pgvector
+ Postgres full-text search
+ Docling / Marker / LiteParse evaluation
+ local embedding model
+ optional reranker
+ local LLM
+ PDF.js preview
+ PyMuPDF export
+ openpyxl XLSX export

Then add Qdrant or GraphRAG only when there is a concrete failure mode that justifies them.

One rule

Validate ingestion before optimizing retrieval.

With PDF/XLSX, bad parsing will look like bad RAG.

3

Thank you very very much for the in-depth explanation on what to do,

I have another question where in we are talking about structural extraction right for “Compare this”, “Which is FY did I earn more” and other such queries, we need to extract text into a typed model right?

so we first send the “text” of the chunk to the LLM and then ask it to extract such information into a typed model and then store everything into embeddings?

or should we use a parser to extract info into a typed model

and during retrieval we query based on the embeddings in pgvector and we will fetch
the column and also the typed models then send it for answering?

one more question how would .xlsv and .csv work?
you mentioned, storing rows and cols with cell_id etc
but in excel such comparison queries will occur more frequently right?

so is it recommend to make generalized typed model for them as well? or to parse them into a dataframe or something similar?

will locally run models be good enough for such extraction into typed models?

do we have to worry about chunk overlapping in case of .xlsv? as sending long sheets to the model will raise token errors right?

Sorry for continuing to bug you regarding this

4

I think structured output often depends less on the model alone and more on how robust the checking/verification layer is:

Short version

I would separate the pipeline like this:

Parser extracts structure.
LLM extracts semantics.
Structured output constrains shape.
Validation checks types.
Verification checks evidence.
SQL stores facts.
Embeddings help retrieval.
Citations/highlights point back to spans/cells.

So, no, I would not store the typed model primarily as embeddings.

I would store typed extraction results as validated structured records, linked back to the source evidence. Then I would optionally embed either the original source chunk or a short textual summary of the extracted record for semantic recall.

A rough flow:

raw document
  -> parsed text/tables/cells
  -> typed extraction
  -> validation
  -> evidence verification
  -> SQL rows
  -> source refs
  -> optional embeddings

Embeddings are useful as an index.
They should not be the source of truth for structured facts.

1. Parser vs extractor

I would keep these two concepts separate.

Layer Job
Parser Recover document/table/cell structure
Normalizer Turn messy pages/sheets/tables into stable internal records
Extractor Produce typed objects like Invoice or FinancialMetric
Structured output layer Make the model response parseable
Validator Check schema, types, required fields, ranges
Verifier Check source span/cell and deterministic values
SQL store Store extracted facts as queryable records
Embeddings Support fuzzy/semantic retrieval
Citation layer Point answers back to pages/spans/cells

A parser might tell you:

{
  "sheet": "FY24",
  "cell": "D17",
  "value": "1250000",
  "number_format": "₹#,##0",
  "row_header": "Gross Profit",
  "column_header": "FY24"
}

An extractor might turn that into:

{
  "metric_name": "Gross Profit",
  "period": "FY24",
  "value": 1250000,
  "currency": "INR",
  "source": {
    "document_id": "doc_123",
    "document_version_id": "v3",
    "sheet": "FY24",
    "cell": "D17"
  }
}

Those are different layers.

The parser preserves structure.
The extractor assigns meaning.

2. Where typed models should go

For typed data, I would use something like Pydantic / JSON Schema / SQL tables.

For example:

from datetime import date
from decimal import Decimal
from pydantic import BaseModel

class Invoice(BaseModel):
    invoice_number: str | None
    vendor_name: str | None
    invoice_date: date | None
    total_amount: Decimal | None
    currency: str | None

    source_document_id: str
    source_version_id: str
    source_page: int | None = None
    source_sheet: str | None = None
    source_cell_range: str | None = None
    source_span_id: str | None = None

Then store it as a structured row.

Possible tables:

invoices
invoice_line_items
financial_metrics
period_values
entities
source_refs

If you want semantic search over extracted records, you can also create a text summary and embed that:

Invoice INV-2024-018 from Vendor ABC, dated 2024-03-18, total ₹125,000, source: doc_123 page 4.

But the embedding should point back to the structured row, not replace it.

So I would use both:

Object Store where? Why
Typed record SQL / relational DB source of truth
Source span/cell provenance tables citation/highlight
Original text/table chunk document store audit/debug
Embedding vector index / pgvector / Qdrant recall
Text summary of typed record optional embedding input semantic lookup

The risky design is:

typed fact -> only embedding -> retrieve later

The safer design is:

typed fact -> validated SQL row -> evidence link -> optional embedding

3. Structured output helps, but does not prove correctness

Local models can be used for typed extraction. I would just frame it as an evaluation question rather than a yes/no model-size question.

A better question is:

Can this local model extract my target fields from my real files, with valid schema, correct values, and source evidence links?

There are several tools/patterns worth knowing:

But the important caveat is:

Structured output can make the output parseable. It does not automatically make the extracted value correct.

For example, this can be valid JSON and still wrong:

{
  "metric_name": "Gross Profit",
  "period": "FY24",
  "value": 1900000,
  "currency": "INR",
  "source_cell": "D17"
}

The JSON may be valid.
The Pydantic model may validate.
But the value may still not match the sheet.

So I would use a pipeline like:

LLM extraction
  -> schema validation
  -> deterministic checks where possible
  -> evidence/source check
  -> retry or mark uncertain
  -> store structured record

For invoices, dates, amounts, currencies, fiscal years, and spreadsheet cells, many checks can be partly deterministic.

Examples:

Field Possible check
amount parse as decimal, compare against source span/cell
currency normalize symbol/code
date parse date and keep original string
fiscal year normalize FY23 / FY2023 / 2023
cell reference verify sheet/cell exists
invoice total compare against table total if available
source quote check quote appears in source span
source cell check extracted value matches cell/range

This is why I would store both the extracted value and its evidence reference.

4. CSV/XLSX: do not start with text chunks

For CSV/XLSX, I would not begin with normal text chunking.

Think in:

workbook
  -> sheet
  -> table / detected region
  -> row
  -> column
  -> cell
  -> range
  -> header context

CSV is usually closer to a table.
XLSX can be a table, a report, a financial model, or a visual workbook.

So I would treat them differently.

Format First representation
CSV DataFrame / SQL table
Simple XLSX table DataFrame + sheet/cell provenance
Messy XLSX report sheet/region/table/cell model
Financial model workbook graph + formulas + values
Highlight/export target workbook/sheet/cell/range provenance

For table-like data, pandas read_csv and pandas read_excel are natural starting points.

For cell-level provenance, formulas, styles, and annotated XLSX export, openpyxl is useful.

A practical rule:

Use DataFrames for table-like regions, but keep workbook/sheet/cell provenance separately.

Do not flatten XLSX into plain text too early if you need citations or highlights.

5. Chunk overlap for spreadsheets

For normal prose documents, chunk overlap can help because sentences/paragraphs cross chunk boundaries.

For spreadsheets, overlap is usually not the main question.

A better question is:

What is the correct table/window/header context for this row or cell?

For a spreadsheet row, the useful context may be:

  • sheet name
  • table title
  • section title
  • row label
  • column label
  • nearby headers
  • units
  • currency
  • fiscal period
  • notes
  • formula
  • displayed value

A good spreadsheet “chunk” might be:

sheet + detected table + header rows + row group + relevant rows

or:

cell/range + row header context + column header context + surrounding table metadata

So instead of arbitrary text overlap, I would use structure-aware windows.

A simple approach:

Unit Good for
whole sheet summary sheet-level routing
detected table summary choosing relevant table
row-level record filtering/search
cell/range evidence citation/highlight
row window with headers LLM explanation
structured SQL row numeric query

There is also research around structure-aware chunking for tabular data, which is relevant because ordinary text chunking does not preserve table structure well.

6. Should CSV/XLSX become generalized typed models?

I would not use one universal typed model for everything.

I would use a few domain-specific typed models plus a generic source reference.

For example:

Document
DocumentVersion
SourceRef
ParsedTable
ParsedCell
Invoice
InvoiceLineItem
FinancialMetric
PeriodValue
Entity

SourceRef is the glue.

Example:

{
  "source_ref_id": "src_8821",
  "document_id": "doc_123",
  "document_version_id": "v3",
  "page": null,
  "sheet": "FY24",
  "cell_range": "D17:D18",
  "span_id": null,
  "bbox": null,
  "source_text_hash": "..."
}

Then an extracted fact can point to source_ref_id.

That gives you:

  • SQL queryability
  • auditability
  • citation
  • highlight/export
  • reprocessing safety

7. What to embed

I would embed different things for different purposes.

Embedding input Purpose
original text chunks semantic retrieval over prose
table summaries route to relevant table
row summaries semantic lookup over rows
typed record summaries semantic lookup over extracted facts
document summaries coarse routing
raw numeric-only cells usually weak for embeddings

For example, this is more useful to embed than just 1250000:

FY24 Gross Profit is ₹1,250,000, extracted from sheet FY24 cell D17 in document doc_123.

But the value should still live in SQL as a numeric value.

That way:

  • semantic search finds candidate records
  • SQL does exact filtering/comparison
  • source refs provide citation/highlight

8. Example flow: invoice threshold query

For:

Show invoices > ₹1 lakh

I would not rely on embeddings.

I would do:

parse documents
  -> extract Invoice records
  -> validate amount/currency/date
  -> store in SQL
  -> SQL query total_amount > 100000
  -> fetch source refs
  -> render answer with citations/highlights

Embeddings may help if the user asks a fuzzy question like:

Find large vendor bills related to consulting.

But once the candidate domain is identified, the threshold should be SQL.

9. Example flow: FY23 vs FY24 gross profit

For:

Compare FY23 vs FY24 gross profit

I would aim for:

parse spreadsheet/PDF table
  -> identify relevant table/metric
  -> extract FinancialMetric records
  -> normalize fiscal periods
  -> store values in SQL
  -> compare numerically
  -> cite source cells/spans

Possible typed model:

from decimal import Decimal
from pydantic import BaseModel

class FinancialMetric(BaseModel):
    metric_name: str
    period: str
    value: Decimal
    currency: str | None = None
    unit: str | None = None
    source_ref_id: str

Again, embeddings can help find “gross profit” semantically, but the comparison itself should be structured.

10. Suggested minimal implementation

If you want a simple version first:

1. Parse CSV/XLSX into DataFrames where possible.
2. Preserve sheet/cell/range provenance.
3. Define 2-3 typed models only.
4. Use local structured output to extract records.
5. Validate records.
6. Verify source refs and deterministic values where possible.
7. Store records in Postgres.
8. Store source refs separately.
9. Embed source chunks and optional record summaries.
10. Use SQL for structured questions.
11. Use embeddings for fuzzy recall.

Start small.

Maybe begin with:

Invoice
FinancialMetric
SourceRef

Then expand only when needed.

A slightly more concrete data model

Core tables

documents
document_versions
source_refs
parsed_tables
parsed_cells
chunks
embeddings

Example structured tables

invoices
invoice_line_items
financial_metrics
period_values
entities

source_refs

id
document_id
document_version_id
page_number
sheet_name
cell_address
cell_range
span_id
bbox
source_text
source_text_hash
parser_name
parser_version

financial_metrics

id
metric_name
period
value
currency
unit
source_ref_id
confidence
extraction_run_id
created_at

invoices

id
vendor_name
invoice_number
invoice_date
total_amount
currency
source_ref_id
confidence
extraction_run_id
created_at

embeddings

id
object_type
object_id
embedding_text
embedding_vector
metadata

Where object_type might be:

chunk
table_summary
row_summary
invoice_summary
financial_metric_summary
Possible extraction loop 
for each document_version:
    parse document
    store parser output
    normalize pages/tables/cells
    create source_refs
    create retrieval chunks
    run typed extraction for selected schemas
    validate extracted objects
    verify source refs / values where possible
    store structured rows
    create optional embedding summaries

For local structured output:

prompt + source context + schema
  -> model output
  -> JSON/Pydantic validation
  -> deterministic checks
  -> retry if invalid
  -> store if acceptable
  -> mark uncertain if not

Important caveat: if you use grammar/schema decoding, still explain the extraction task and schema semantics in the prompt. The decoder can constrain the form, but the model still needs to understand the task.

Tiny eval set I would build early

I would make a small extraction eval set before scaling this.

Not just answer QA. Extraction QA.

Check Meaning
schema_valid Did the model return valid typed output?
value_correct Are extracted values correct?
source_correct Does the source ref point to the right page/cell/span?
sql_correct Does the stored record answer structured queries?
citation_correct Does citation support the answer?
highlight_correct Does the PDF/XLSX highlight land correctly?

Example test cases:

Case What it tests
clean CSV invoice table easy DataFrame extraction
XLSX with multiple sheets sheet routing
XLSX with merged headers header context
XLSX with formulas formula vs displayed value
PDF invoice page/span provenance
scanned PDF invoice OCR/parsing failure
FY23/FY24 metric table period normalization
missing value abstention / uncertainty
repeated vendor name entity disambiguation
client-restricted query tenant isolation

This matters because a valid JSON object is not the same thing as a correct extraction.

My practical answer

So for your specific question:

Should the LLM extract typed models from chunk text and store them in embeddings?

I would answer:

Let the LLM extract typed models from parsed text/table/cell context, but store those typed models as validated structured records linked to evidence. Use embeddings only as a retrieval index over chunks, table summaries, row summaries, or record summaries.

And for CSV/XLSX:

Do not treat them as prose documents first. Treat them as tables/sheets/cells/ranges first, and only create text representations after preserving that structure.

That separation should keep the system much easier to debug later.