infrapuzzle/projects/duckdb-backend/README.md

DuckDB Backend

HTTP-based analytical and transactional backend for personal logging data stored in the Knowledge Wiki. Powered by Python 3.12, FastAPI, and in-memory DuckDB querying JSONL files directly.

Architecture

• Single Source of Truth: Knowledge Wiki (/wiki git repository with data in /wiki/data/**/*.jsonl).
• Engine: In-memory DuckDB reading sleep, nutrition, mila, entities, and garmin partitions.
• Git Synchronization:
  • Background periodic git pull --rebase origin main (every 60s).
  • Mutating operations (POST /duckdb-api/v1/events) append to typed JSONL partition files, sort them, commit, rebase, and push to origin main.
  • Conflicts trigger automatic rebase abort and flag git_conflict: true on /status and /healthz.

Endpoints

• GET /duckdb-api/v1/mila/dashboard: Curated aggregated dashboard for Mila (sleep, wake timer, feedings, diapers, WHO percentiles).
• GET /duckdb-api/v1/moritz/dashboard: Curated aggregated dashboard for Moritz (today's intake, yesterday's 24h deficit/surplus, Garmin recovery & sleep, 7d/30d trends).
• POST /duckdb-api/v1/query: Generic read-only SQL queries against DuckDB tables and views.
• POST /duckdb-api/v1/events: Record new events (sleep, nutrition, diaper, feeding, growth).
• POST /duckdb-api/v1/events/stop: Close active open sleep session or interval.
• DELETE /duckdb-api/v1/events/{id}: Soft-delete event.
• POST /duckdb-api/v1/entities: Upsert entities (foods, recipes, places, people).
• GET /duckdb-api/v1/healthz: Liveness and readiness probe.
• GET /duckdb-api/v1/status: Git synchronization status and table row counts.
• POST /duckdb-api/v1/git/sync: Manual pull or webhook sync trigger.