Case Study - AI Research Platform with ETL, RAG, and Natural Language Data Access

How do you build a searchable database from 15+ different data sources?

The research group we worked with had a problem that many data teams will recognize: their knowledge was scattered across more than 15 sources. Public APIs from funding agencies, government open data portals, internal CSV exports from legacy systems, PDF reports from partner institutions, and third party websites that published relevant datasets without offering any API at all.

Researchers spent roughly 60% of their time locating, downloading, cleaning, and reconciling data before they could even begin analysis. Worse, valuable connections between datasets remained invisible because no one had the bandwidth to manually cross reference everything. A researcher studying collaboration networks had no practical way to discover that two groups shared a funding source, because the funding data lived in a different system than the publication data.

We set out to build a platform that would unify all of this into a single searchable database, then layer on natural language access so researchers could query their data without writing SQL.

Designing ETL pipelines that handle APIs, CSVs, and web scraping

We built one pipeline per source, each tailored to the source's format and quirks. The critical design decision was making every pipeline idempotent: running the same pipeline twice with the same input produces the same result without duplicating records. This sounds simple, but it changes everything about how you handle failures. A pipeline can crash halfway through ingesting 50,000 records, and you restart it without worrying about partial state.

For API sources, the main challenges were rate limiting and pagination. Several funding agency APIs enforced strict rate limits (some as low as 60 requests per minute), so we built adaptive throttling that backs off automatically when it detects 429 responses. Pagination required source specific logic because every API implemented it differently: some used cursor based pagination, others used page numbers, and one used a bizarre token system that expired after 30 minutes.

For CSV sources, schema inference was the most time consuming problem. Internal teams exported CSVs with inconsistent column names, date formats, and encoding. We wrote a validation layer that maps incoming columns to our canonical schema using fuzzy matching, then flags anything it cannot resolve for manual review.

# Each pipeline writes to a staging table first, then upserts into the canonical schema.
# This keeps the main tables consistent even if a pipeline fails mid-run.

async def run_pipeline(source: Source, session: AsyncSession):
    raw_records = await source.extract()
    validated = source.transform(raw_records)  # source-specific mapping

    await session.execute(
        insert(staging_table)
        .values(validated)
        .on_conflict_do_update(
            index_elements=["source_id", "external_id"],
            set_={col: val for col, val in validated.items()}
        )
    )

    await promote_staging_to_canonical(session, source.name)

For web scraping, reliability was the dominant concern. Websites change their HTML structure without warning. We used a two layer approach: a structural scraper that extracts data based on CSS selectors, backed by an LLM based fallback that can interpret the page content when the selectors break. The fallback is slower and more expensive, but it keeps the pipeline running while we update the selectors.

Why we chose PostgreSQL for both structured data and vector search

A common architecture for this kind of project is to use PostgreSQL for structured data and a dedicated vector database (Pinecone, Weaviate, Qdrant) for embeddings. We chose a different path: PostgreSQL with pgvector for everything.

The reason is operational simplicity. With a single database, you get unified queries that can join structured data with semantic search results in a single SQL statement. A researcher asking "which groups working on climate modeling received EU funding above 500k in the last three years" needs both a vector similarity search (to find groups whose descriptions match "climate modeling") and a structured filter (funding amount and date range). With pgvector, this is one query. With separate databases, it requires an orchestration layer that queries both systems and merges results.

-- Hybrid query: semantic search + structured filters in one statement
SELECT r.name, r.department, f.amount, f.year,
       1 - (r.embedding <=> $1::vector) AS similarity
FROM research_groups r
JOIN funding_records f ON r.id = f.group_id
WHERE f.amount > 500000
  AND f.year >= 2023
  AND 1 - (r.embedding <=> $1::vector) > 0.7
ORDER BY similarity DESC
LIMIT 20;

We also added HNSW indexes on embedding columns, which brought vector search latency down to under 50ms for our corpus size (roughly 2 million vectors). For this scale, pgvector performs comparably to dedicated vector databases, and we avoid the complexity of keeping two systems in sync.

Building the MCP integration layer

The Model Context Protocol (MCP) is what connects the LLM to the database. We built an MCP server that exposes database operations as callable tools. When a researcher asks a question, the LLM can invoke these tools to retrieve precise, filtered data instead of relying on its training data or hallucinating numbers.

Each tool has a clear schema that tells the LLM what parameters it accepts and what it returns. Here is a simplified version of one of our tool definitions:

{
  "name": "query_funding_records",
  "description": "Search funding records by research group, funding agency, amount range, and year range. Returns structured funding data with group details.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "group_name": {
        "type": "string",
        "description": "Partial or full name of the research group"
      },
      "agency": {
        "type": "string",
        "description": "Funding agency name (e.g., SNF, EU Horizon)"
      },
      "min_amount": {
        "type": "number",
        "description": "Minimum funding amount in CHF"
      },
      "max_amount": {
        "type": "number",
        "description": "Maximum funding amount in CHF"
      },
      "year_from": { "type": "integer" },
      "year_to": { "type": "integer" }
    }
  }
}

We built 12 tools in total, covering funding queries, publication search, collaboration network traversal, researcher profiles, and patent lookups. The key design principle was that each tool should return data the LLM can reason about directly, not raw database rows. So the tools format their output with context: "Professor X received CHF 1.2M from SNF in 2024 for project Y" rather than returning a JSON blob of column values.

Adding RAG for unstructured content

Not everything fits into a structured schema. The platform also ingests PDF reports, grant proposals, meeting notes, and news articles. For these, we built a RAG pipeline that chunks documents, generates embeddings, and stores them in the same PostgreSQL database using pgvector.

The chunking strategy was not one size fits all. Academic papers have a clear structure (abstract, introduction, methods, results) and benefit from larger chunks (800 to 1200 tokens) that preserve the logical flow of arguments. News articles are shorter and more self contained, so we used smaller chunks (300 to 500 tokens) with more overlap. We experimented with semantic chunking (splitting on topic boundaries rather than fixed sizes) but found that for our use case, the simpler approach with source specific chunk sizes performed equally well and was far easier to debug.

Retrieval uses hybrid search: a combination of vector similarity and BM25 keyword matching. Some queries are conceptual ("what are the emerging trends in quantum computing research"), where vector search excels. Others are precise ("find the report mentioning grant number 2024.XY.1234"), where keyword matching is essential. We score both approaches and merge results with reciprocal rank fusion.

What worked and what surprised us

Query routing turned out to be simpler than expected. We initially planned a complex classification system to decide whether a user question should trigger SQL queries, RAG search, or both. In practice, we found that letting the LLM decide based on the tool descriptions worked remarkably well. The LLM naturally calls the structured query tools when the question involves specific filters (amounts, dates, names) and falls back to RAG when the question is open ended. For about 15% of queries, it uses both, which is exactly the behavior we wanted.

Results in numbers: Researchers reported an 80% reduction in time spent on data discovery and preparation. Within the first month, the platform surfaced cross source connections that no team member had previously identified, including collaboration patterns between research groups that shared funding sources but had never communicated directly. The chat interface saw daily active usage from over 40 researchers within six weeks of launch.

One discovery that surprised the research group: the platform identified three separate teams across two departments working on nearly identical problems, using overlapping datasets, without any of them knowing about each other. This alone justified the entire project.

What we would do differently: We underestimated the effort required for data quality monitoring. Pipelines that worked perfectly during development started producing subtle errors when source schemas changed. We ended up building a data quality dashboard with automated alerts, something that should have been in the initial scope rather than added reactively.

If you are building something similar, or want to explore how a research data platform could work for your team, get in touch.

What we did