Building an AI Research Platform: ETL, RAG, and a Chatbot That Actually Knows Your Data

A European research university came to us with a familiar problem. Their researchers were spending more time locating data than analyzing it. Across 15+ sources, from public funding databases to scraped news articles to internal CSV exports, the same entities appeared in different formats, under different names, with different update schedules. A single researcher trying to answer "which institutions are co-publishing with us on battery technology?" had to manually cross-reference four databases, two APIs, and a folder of spreadsheets. That question should take seconds. It was taking days.

We built them a platform that unifies all of it: a single system where structured data lives in PostgreSQL, unstructured content is searchable via RAG, and researchers interact with everything through a conversational interface powered by MCP.

The data landscape

The sources fell into five categories. REST and GraphQL APIs for publication databases, patent registries, and funding records. CSV and Excel dumps exported from internal systems on varying schedules. Public databases with their own query interfaces and rate limits. Scraped websites and news articles covering industry developments. And a growing collection of PDFs, reports, and whitepapers that researchers uploaded manually.

Each source had its own schema, its own identifiers, its own idea of what an "institution" or "researcher" means. Some updated daily, others quarterly, and a few hadn't been refreshed in years. The first challenge was not building a chatbot. It was building a data foundation worth chatting with.

Architecture overview

The system has four layers, each with a clear responsibility:

┌─────────────────────────────────────────────────┐
│                Chat Interface                    │
│         (Natural language queries)               │
├─────────────────────────────────────────────────┤
│              MCP Server Layer                    │
│   ┌──────────────┐    ┌──────────────────┐      │
│   │  SQL Tools    │    │  RAG Search Tools│      │
│   │  (structured) │    │  (unstructured)  │      │
│   └──────┬───────┘    └────────┬─────────┘      │
├──────────┼─────────────────────┼────────────────┤
│          ▼                     ▼                 │
│   ┌──────────────┐    ┌──────────────────┐      │
│   │  PostgreSQL   │    │  pgvector        │      │
│   │  (unified     │    │  (embeddings +   │      │
│   │   schema)     │    │   chunks)        │      │
│   └──────────────┘    └──────────────────┘      │
├─────────────────────────────────────────────────┤
│              ETL Pipeline Layer                  │
│  ┌────────┐ ┌────────┐ ┌────────┐ ┌──────────┐ │
│  │  API   │ │  CSV   │ │  Web   │ │  PDF     │ │
│  │Ingest  │ │Parser  │ │Scraper │ │Processor │ │
│  └────────┘ └────────┘ └────────┘ └──────────┘ │
└─────────────────────────────────────────────────┘

Each layer can evolve independently. Adding a new data source means writing a new ETL connector and mapping it to the unified schema. The chat layer never needs to change.

ETL: making messy data usable

Every data source gets its own pipeline, but they all follow the same pattern: extract, validate, normalize, load. API connectors handle rate limiting with exponential backoff and automatic retries. CSV parsers infer schemas from headers and sample rows, flagging columns that don't match expected types. Web scrapers run on configurable schedules with deduplication built in.

The critical design decision was making every pipeline idempotent. Running the same pipeline twice on the same data produces exactly the same result. No duplicates, no partial writes, no corrupted state. This matters enormously when you're dealing with sources that occasionally send malformed data or timeout mid-transfer.

Why PostgreSQL for everything

We made an early decision to keep both structured data and vector embeddings in PostgreSQL using pgvector, rather than introducing a separate vector database like Pinecone or Weaviate.

The reasoning was pragmatic. The university's IT team already managed PostgreSQL. Adding another database would mean another backup strategy, another monitoring setup, another set of credentials to manage. With pgvector, embeddings live alongside the structured data they describe. A single query can join a vector similarity search with a relational filter: "find documents similar to this query, but only from papers published after 2024 by authors affiliated with ETH."

For their scale (roughly 2 million chunks and growing), pgvector performs well with HNSW indexes. If they eventually hit tens of millions of chunks, a dedicated vector store might make sense. But at their current trajectory, PostgreSQL handles it comfortably.

MCP: the integration layer

The Model Context Protocol (MCP) server is what makes the chatbot genuinely useful rather than just a novelty. It exposes the platform's capabilities as discrete tools that the LLM can invoke:

# MCP tool definitions (simplified)
tools = [
    {
        "name": "query_structured_data",
        "description": "Query the research database using SQL. "
                       "Use for questions about publications, authors, "
                       "institutions, funding, and their relationships.",
        "parameters": {
            "sql_query": "string: a read-only SQL query",
            "explain": "boolean: include query plan for debugging"
        }
    },
    {
        "name": "search_documents",
        "description": "Search unstructured content (news, reports, "
                       "whitepapers) using semantic similarity. "
                       "Use for questions about trends, opinions, "
                       "or qualitative information.",
        "parameters": {
            "query": "string: natural language search query",
            "filters": "object: optional metadata filters",
            "top_k": "integer: number of results (default 10)"
        }
    },
    {
        "name": "get_entity_details",
        "description": "Retrieve full details for a specific entity "
                       "(researcher, institution, paper, grant) by ID.",
        "parameters": {
            "entity_type": "string: researcher|institution|paper|grant",
            "entity_id": "string: the entity identifier"
        }
    }
]

The LLM decides which tools to call based on the researcher's question. "How many papers did Prof. Mueller publish in 2025?" routes to the structured query tool. "What are the current trends in solid-state battery research?" routes to document search. "Which researchers at our university have co-authored papers with industry partners who also received EU funding?" triggers both tools, with the LLM synthesizing results from structured queries and document search.

What worked well

The platform's biggest impact came from connections that no single researcher could have spotted manually. Within the first month, one research director discovered a funding pattern: three separate EU programs were funding overlapping battery research at institutions the university already collaborated with. That insight led to a coordinated grant application that would have been impossible to assemble without the cross-source view.

Adoption was faster than expected. Researchers who had been skeptical of "AI tools" started using the chat interface daily once they realized it could answer in seconds what previously took hours of manual lookup. The natural language interface eliminated the need to learn SQL or navigate multiple database UIs.

What was hard

Data quality from scraped sources remained a persistent challenge. News articles sometimes attributed research to the wrong institution. Scraped tables lost their structure during extraction. We ended up building a confidence scoring system that flags low-quality data and excludes it from high-precision queries unless the user explicitly asks for it.

Schema evolution was another ongoing effort. Every new data source potentially introduces entities and relationships that don't fit the existing model. We addressed this with a flexible schema migration process and a "staging" layer where new data lives until it's been mapped and validated against the unified schema.

Keeping embeddings fresh required careful engineering. When a source document changes, you need to re-chunk and re-embed it, then update the vector index. We built an incremental embedding pipeline that tracks document hashes and only reprocesses what changed.

Lessons for your own data platform

The pattern we built here, ETL into a unified schema, RAG for unstructured content, MCP for LLM integration, is not specific to academic research. Any organization sitting on fragmented data across multiple systems can benefit from this architecture. The key is starting with the data layer, not the chatbot. A conversational interface on top of messy, disconnected data just produces confident-sounding wrong answers.

If your team is dealing with scattered data sources and wants to explore what a unified, AI-powered platform could look like, we would be happy to discuss it.