All projects

Data engineering and RAG

DeutschlandApp

Seven official sources in one database, plus an AI that answers from them through RAG, with sources and charts.

Local prototype, never deployed

111,103,767

official data points in a single PostgreSQL database

  • 8.6Mtime series
  • 2.4MBundestag documents
  • 6,691GENESIS tables
  • 30 GBdatabase size

110.8M rows in under 44 hours

The first import, July 2026. Peak: 10.8M rows in one hour.

025M50M75M100M12 July, 12:0013 July, 00:0013 July, 12:0014 July, 00:00110.8M

Data from 1871 to 2070

95% of the values are from 2000 to 2029. From 2030 they are official projections.

1870
1920
1970
46.6M
2020
2070

Where the 30 GB sit

The observations live in a TimescaleDB hypertable with 202 chunks.

  • Observations22.6 GB111,103,767 rows
  • Time series3.5 GB8,598,448 rows
  • Bundestag documents2.8 GB2,403,885 rows
  • Politician records0.9 GB687,433 rows

The largest single tables

9 tables hold over 1 million values, 242 over 100,000.

  • Industrial production survey1.7M42131BV003, 252,208 time series
  • Industrial production survey1.4M42131BV203, 211,123 time series
  • Hospital diagnoses1.1M23131BJ022, 47,958 time series
  • Monthly industry report1.1M42111BM104, 6,708 time series
  • Hospital diagnoses1.1M23131BJ019, 52,494 time series

Turning the data into answers: RAG

The model answers from the data it fetched. Retrieval runs through tools and full-text search.

  1. Question“How have consumer prices changed?”
  2. RetrievalFull-text search finds the statistic among 13,164 catalogue entries.
  3. Fetch19 MCP tools fetch the values straight from the database.
  4. AnswerText and a chart from the fetched values, with the source.
All values as a table
Hour+total
12 July, 03:00411,050411,050
12 July, 04:00528,359939,409
12 July, 05:0053,308992,717
12 July, 13:001,181,4282,174,145
12 July, 14:0047,0672,221,212
12 July, 17:0019,2642,240,476
12 July, 20:00485,8622,726,338
12 July, 21:0042,3962,768,734
12 July, 22:001082,768,842
12 July, 23:0099,0742,867,916
13 July, 00:0062,7642,930,680
13 July, 03:0081,6123,012,292
13 July, 04:003,219,0716,231,363
13 July, 05:007,849,67114,081,034
13 July, 06:0010,771,35624,852,390
13 July, 07:007,315,33632,167,726
13 July, 08:001,359,01133,526,737
13 July, 09:00459,63933,986,376
13 July, 10:001,667,67735,654,053
13 July, 11:006,770,26842,424,321
13 July, 12:006,726,30749,150,628
13 July, 13:007,132,23056,282,858
13 July, 14:005,029,85361,312,711
13 July, 15:008,702,53770,015,248
13 July, 16:007,247,19277,262,440
13 July, 17:008,902,12186,164,561
13 July, 18:009,718,38495,882,945
13 July, 19:005,449,487101,332,432
13 July, 20:003,533,825104,866,257
13 July, 21:004,491,783109,358,040
13 July, 22:001,369,868110,727,908
13 July, 23:0064,131110,792,039
Data from 1871 to 2070values
1870s3,636
1880s4,040
1890s4,040
1900s4,040
1910s4,054
1920s4,056
1930s4,060
1940s4,120
1950s18,294
1960s71,607
1970s172,376
1980s371,240
1990s3,689,756
2000s26,711,131
2010s46,561,292
2020s32,144,978
2030s325,917
2040s324,352
2050s324,180
2060s324,180
2070s32,418

Measured in the app's database, data as of July 2026. A local prototype, not an official service.

Core skills

  • Data engineering
  • LLMs and agents
  • Backend and APIs
  • Reliability
  • Mobile and native
  • Integrations

Tech stack

  • PostgreSQL 16
  • TimescaleDB
  • JSONB
  • Full-text search
  • Playwright
  • NestJS 11
  • TypeORM
  • Zod
  • Swagger
  • RAG
  • OpenAI Responses API
  • MCP
  • Whisper
  • Expo
  • React Native
  • NativeWind
  • Reanimated

The hardest part

The numbers that supposedly couldn't be fetched

“Impossible” is only a guess until you've tried the path a real browser takes.

The whole story

  1. What it looked like

    On Dashboard Deutschland, the values are there for anyone to see. The program still couldn't get them: the JSON held no values, and the hidden endpoints answered 403.

  2. What was really going on

    The site has no documentation, and the obvious path led nowhere. So the notes first said: unreachable.

  3. The fix

    Open the site in a real browser and watch what it loads in the background. That's where the CSV download IDs turned up. This is also how the pipeline now picks up new topics automatically.

7
official and public data sources in one database
19
MCP tools for retrieval
49,766
retroactive corrections to old values detected
92
HTTP routes, 18 entities and 14 migrations
In plain words

How has unemployment changed? How did the Bundestag vote? The answers are public but scattered across many government sites. This app puts seven sources into one database, and an AI answers from them instead of guessing. It isn't an official service, just a personal project.

Official data is public but hard to find, and an LLM likes to guess. The idea: the model has to fetch every number from a database and show the source. For that, the data first had to be in one place.

How it works

Step 1 of 5

Sources

A daily cron job refreshes every source in turn behind a lock. Dumps can continue after a restart.

What was built
  • Seven mirrored sources, including GENESIS (Federal Statistical Office), the Bundestag's DIP and abgeordnetenwatch
  • A time series store in a TimescaleDB hypertable, with 111 million observations
  • Change detection with separate hashes and a log of retroactive corrections (49,766 entries)
  • An MCP server with 19 tools for search, series, comparisons, votes and legislation
  • An assistant with visible tool steps, voice input and interactive charts inside its answers
  • An app with 16 screens, 14 of them built: chat, votes, legislation, statistics search, politician cards
How those skills were used
Data engineering
Idempotent ingestion, revisions, resumable bulk loads and daily syncs on over 100 million rows
LLMs and agents
RAG through 19 self-written MCP tools and German full-text search, connected to a hosted model, with prompt rules against invented values
Backend and APIs
92 HTTP routes, 42 of them a typed facade over the GENESIS API, 18 entities and 14 TypeORM migrations
Reliability
Semaphores and token buckets against external limits, and one failing source doesn't stop the others
Mobile and native
An Expo app with typed routes and interactive SVG charts, built from a design handoff
Integrations
Seven external data sources, each with its own formats, limits and failure modes
My role

Concept, architecture, directing the agents and verification. Claude Code wrote most of the code, inside an agent workflow I developed myself.

See the Context Observer project
Next projectContext Observer