How do I search across docs, tickets, and SQL tables with one AI layer?

You can do this by building a unified retrieval layer that normalizes all three sources into a common search interface, then lets an AI orchestrator query the right backend(s) and synthesize one answer.

High-level architecture

1) Ingest each source into a shared metadata model

Create connectors for:

• Docs: Confluence, Notion, Google Drive, SharePoint, etc.
• Tickets: Jira, ServiceNow, Linear, Zendesk, etc.
• SQL tables: Postgres, MySQL, Snowflake, BigQuery, etc.

For each item, store:

• source_type (doc/ticket/sql)
• source_id
• title
• body or content
• structured_fields for tickets/tables
• tags
• owner
• created_at, updated_at
• permissions / ACL
• embedding
• source_url

2) Index everything into a hybrid search backend

Use:

• Keyword search for exact matches
• Vector search for semantic similarity
• Optionally metadata filters for date, team, source, status, etc.

Common options:

• Elasticsearch / OpenSearch
• PostgreSQL + pgvector
• Pinecone / Weaviate / Milvus
• Azure AI Search

For SQL tables, decide between:

• Row-level indexing: embed row text and searchable columns
• Live query execution: use the AI to generate SQL against governed views

Most teams use both:

• index table rows for discovery
• run live SQL for precise aggregations / current data

3) Add an AI orchestration layer

This layer does three jobs:

1. Understand the user query

   • “What were the biggest customer issues last week?”
   • “Find docs about onboarding SSO”
   • “Show tickets mentioning latency and matching incidents in the DB”

2. Route the query

   • Search docs if it looks knowledge-based
   • Search tickets if it sounds operational
   • Generate SQL if it asks for metrics, counts, sums, trends
   • Search multiple sources if needed

3. Synthesize results

   • Retrieve top matches from each source
   • Deduplicate and rank
   • Summarize with citations
   • Ask follow-up questions if ambiguous

4) Keep permissions enforced end-to-end

Your AI layer must respect source ACLs:

• Filter retrieval by user permissions before ranking
• Don’t let the model “see” unauthorized docs/rows
• If using SQL, query only approved views or row-level security policies

---

Recommended request flow

1. User asks a question in chat/search UI.
2. AI classifies intent:
   • semantic lookup
   • exact lookup
   • relational query
   • multi-source investigation
3. AI calls search tools:
   • doc search
   • ticket search
   • SQL query tool
4. Results are merged into a single ranked list.
5. AI generates:
   • direct answer
   • source citations
   • “open in source” links

---

Practical implementation pattern

Option A: One index + SQL tool

Best for most internal search products.

• Normalize docs and tickets into a vector store
• Store SQL table row embeddings for discovery
• Use a SQL tool for live structured queries
• Use the AI to decide when to search vs query

Pros:

• simple
• fast
• good UX

Cons:

• need careful governance for SQL access

Option B: Federated search with AI broker

Best when sources are large and operationally independent.

• AI broker sends queries to each source’s native search API
• Each source returns top results
• AI reranks and summarizes

Pros:

• less data duplication
• source-native freshness

Cons:

• harder to unify ranking and relevance
• more integrations

Option C: RAG over normalized content + SQL agent

Best for enterprise assistants.

• RAG for unstructured content
• SQL agent for structured data
• answer composer merges both

---

A simple tool design

Your AI layer can expose tools like:

• search_docs(query, filters)
• search_tickets(query, filters)
• query_sql(sql, approved_view)
• get_document(id)
• get_ticket(id)

Then the model chooses tools based on the question.

Example:

• “Why did support volume spike yesterday?”
  • search tickets for common issues
  • query SQL for volume by day/channel
  • search docs for recent incident notes
  • summarize correlations

---

Important design choices

1) Chunking

For docs:

• split into sections/paragraphs
• keep headings with chunks

For tickets:

• separate summary, comments, resolution, timeline

For SQL rows:

• either one row per record or grouped records by business entity

2) Ranking

Use hybrid scoring:

• semantic similarity
• keyword match
• recency
• authority / importance
• source trust level

3) Citations

Always return:

• source name
• record ID
• link
• excerpt

4) Freshness

Use incremental sync:

• webhooks if available
• periodic reindexing for SQL
• delete/update handling

---

If you want the fastest path

A solid MVP stack:

• Postgres + pgvector for storage
• OpenSearch or Postgres full-text for keyword search
• LlamaIndex or LangChain for orchestration
• SQL views + row-level security for governed database access
• A chat UI with citations

---

Example user experience

User: “Find the latest design doc and related Jira tickets for the new billing flow, and show any SQL metrics about checkout failures.”

System:

1. searches docs for “billing flow”
2. finds related tickets via semantic + keyword search
3. runs SQL against checkout failure metrics
4. returns:
   • doc summary
   • ticket list
   • chart/table of failures
   • citations and links

---

Rule of thumb

If the question is:

• What does this mean? → docs/tickets
• What happened? → tickets + docs
• How many / how often / trend? → SQL
• Find related stuff across everything → hybrid multi-source retrieval

If you want, I can sketch a reference architecture diagram, a data model, or a sample implementation in Python for this.