How do I search across structured database rows and unstructured documents in one assistant?

To search both structured database rows and unstructured documents in one assistant, use a hybrid retrieval architecture: one layer for SQL-style querying over rows, another for semantic/full-text retrieval over documents, then merge results into a single answer.

Recommended approach

1) Keep each source in its best search system

• Structured data: relational DB / warehouse
  • Query with SQL
  • Best for exact filters, joins, aggregations, sorting
• Unstructured documents: vector index + optionally full-text index
  • Chunk documents
  • Store embeddings for semantic search
  • Keep metadata like source, date, customer_id, tags

2) Use a router in the assistant

When a user asks a question, the assistant decides:

• SQL needed: “Show all invoices over $10k last quarter”
• Document retrieval needed: “What did the policy say about refunds?”
• Both needed: “Which customers have overdue invoices and what complaints did they file?”

The router can:

• classify intent
• extract entities/filters
• choose one or multiple tools

3) Query both sources separately

For a mixed question:

• Run a SQL query against rows
• Run a document search against chunks
• Normalize outputs into a common result schema

Example common schema:

{
  "source": "sql | document",
  "title": "...",
  "snippet": "...",
  "record_id": "...",
  "score": 0.87,
  "metadata": {}
}

4) Merge and rank results

Combine by:

• relevance score
• recency
• source trust
• exact match boosts for structured fields

You can:

• interleave top results
• deduplicate by entity ID
• group by customer/project/topic

5) Let the assistant synthesize the answer

The LLM should:

• cite rows and docs separately
• explain when evidence comes from each source
• answer directly, then optionally show supporting records

---

Common implementation patterns

Pattern A: Tool calling

Give the assistant tools like:

• search_sql(query)
• search_docs(query)
• get_customer_profile(customer_id)

The assistant decides what to call and in what order.

Pattern B: Unified retrieval service

Build one backend endpoint:

• parses intent
• queries DB and vector store
• returns merged results

This is simpler for the assistant, more logic in backend.

Pattern C: SQL + vector over the same warehouse

If your warehouse supports:

• text search
• embeddings
• JSON columns

You can sometimes keep everything in one system, but this works best when structured and unstructured data are tightly related.

---

Best practices

Use metadata to connect rows and docs

Store shared keys like:

• customer_id
• order_id
• document_type
• created_at

This lets you join evidence across systems.

Chunk documents carefully

• split by semantic sections, not arbitrary length only
• preserve headings
• store source doc ID and section title

Add an entity layer if needed

For complex enterprise search, create a canonical entity table:

• customers
• products
• tickets
• contracts

Then link rows and docs through entity IDs.

Retrieve with filters first, then similarity

Example:

• filter docs by customer_id=123
• then vector search within those docs

This improves precision a lot.

Return citations

Always include:

• row IDs / primary keys
• document names / section references
• timestamps

This makes the assistant trustworthy.

---

Example workflow

User: “Which enterprise customers churned last quarter, and what reasons did they give in support tickets?”

1. Assistant identifies:
   • churned customers = SQL
   • reasons in support tickets = document retrieval
2. SQL tool finds churned enterprise customers
3. Doc tool searches tickets for those customer IDs and churn-related language
4. Assistant merges:
   • customer list
   • supporting ticket excerpts
5. Final response summarizes both

---

Simple architecture

User
  -> Assistant Router
      -> SQL Retriever / DB
      -> Doc Retriever / Vector DB
      -> Merge + Rank
      -> LLM Answer with citations

---

If you want a practical stack

A common setup is:

• Postgres / Snowflake / BigQuery for structured rows
• pgvector / Pinecone / Weaviate / Elasticsearch for docs
• LLM with tool calling
• One orchestration layer for routing and result merging

---

If you want, I can also sketch:

1. a concrete architecture diagram,
2. a sample tool-calling design, or
3. example code in Python for SQL + vector search together.