Files

T

Moritz Graf f717a9a2f7 feat(datastore): implement structured personal data layer (ADR 0004, ForwardAuth /family, and OpenClaw protocol)

2026-10-06 18:18:27 +02:00

11 KiB

Raw Permalink Blame History

status, date, decision-makers, consulted, informed

status

date

decision-makers

consulted

informed

accepted

2026-10-06

Moritz Graf

Antigravity

ADR 0004: Structured Personal Data Layer (DuckDB + JSONL in Knowledge Wiki)

Context and Problem Statement

OpenClaw manages personal logging and family assistance for the Graf family across several daily domains:

Personal Nutrition: Daily food intake, macro tracking (protein, carbs, sugar, fat, fiber, salt, calories) in entities/nutrition-log/moritz/*.md.
Baby Sleep & Routines: Sleep logs, naps, night sleep, bottle feeds, and developmental milestones for Mila in entities/people/mila-schlafprotokoll.md.
Sports & Physiological Balance: Activities, daily calorie expenditure (RMR + active), and net caloric surplus/deficit synced with Garmin Connect.
Culinary Knowledge: Recipes with ingredient proportions, cooking steps, and hardware settings.
Points of Interest (POIs): Local stores, farm stands (Hofläden), beer gardens, and playgrounds in Regensburg.

Historically, this data was logged directly into Markdown tables and bulleted lists in the Knowledge Wiki (knowledge-wiki/). An analysis of existing log files revealed severe systemic failure modes:

LLM Arithmetic Drift: In 2026-09.md and 2026-10.md, the LLM consistently failed at daily macro sums (errors up to 75 kcal and 7.5 g protein per day). The derived "Vortags-Bilanz" (caloric deficit/surplus against Garmin) was calculated from incorrect totals.
Structural and Chronological Degradation: In mila-schlafprotokoll.md, dates drifted out of order (29.09 → Sep Summary → 30.09 → 01.10 → 03.10 → 02.10 → 28.09), and every night sleep was duplicated (once in the evening, once the next morning).
Inability to Perform Spatial & Aggregate Queries: Questions like "What was Mila's average night sleep duration over the last 14 days?" or "Which beer gardens are within 500m of Grabengasse 7?" required either multi-turn LLM hallucination or reading hundreds of lines of raw text.

How do we establish a structured, reliable, and queryable data layer for OpenClaw that guarantees mathematical precision while upholding our Everything-as-Code (ADR 0003) and Git-as-Source-of-Truth architecture?

Decision Drivers

Mathematical Precision: Zero LLM arithmetic. All summations, caloric balances, sleep durations, and nutrient rollups must be computed by a deterministic SQL engine.
Git as the Single Source of Truth (ADR 0003): No external database silos (PostgreSQL, standalone SQLite PVCs) that can drift from the git-backed wiki. All state must remain git-versioned, diffable, and merge-friendly.
Separation of Concerns (Math vs. Narrative): Delegate structured math, joins, and coordinates to DuckDB. Delegate natural-language understanding, nuance, packaging photo interpretation, and cooking advice to the LLM.
Flexible Composability: Foods can become ingredients in recipes, which can yield composite foods (e.g. wheat → baking mix → bread; tomatoes → sauce → pizza).
Multi-Channel Presentation: Auto-updated Markdown files for SilverBullet (wiki.moritzgraf.de), Telegram inline text with chart-to-chat PNGs, and mobile-friendly HTML dashboards on hosting.haumdaucher.de/family/.
Zero-Tax Execution: In-memory query execution in Python via uv without adding heavy runtime containers or pod memory overhead.

Considered Options

Option 1: Stricter Markdown Prompts + Python Lint Rules
- Keep Markdown tables; add linter checks in okf_lint.py to flag wrong sums.
- Drawback: Reactive detection rather than proactive prevention. Cannot do spatial searches or aggregate SQL queries.
Option 2: External Relational Database (PostgreSQL / PVC SQLite)
- Deploy PostgreSQL or write to a binary SQLite database on the Kubernetes PVC.
- Drawback: Violates ADR 0003. Creates a separate data silo outside git, introduces backup synchronization risks, and binary files cannot be cleanly merged or diffed in Git.
Option 3: Pure Event Sourcing for All Data
- Force every piece of data (including stores, recipes, and food labels) into an immutable event log.
- Drawback: Over-engineers static catalogs. Catalog entities (like places or recipes) become awkward to manage and query when forced into point-in-time event streams.
Option 4: Three-Tier Archetype Architecture via DuckDB & JSONL in Git (Selected)
- Store structured records in line-delimited JSON (.jsonl) inside the Knowledge Wiki repository (data/), using in-memory DuckDB as the calculation and query engine.
- Divide data into three natural archetypes: Events, Entities, and Documents.

Decision Outcome

Chosen Option: Option 4: Three-Tier Archetype Architecture via DuckDB & JSONL in Git.

1. The Three Data Archetypes

┌────────────────────────────────────────────────────────────────────────┐
│                        Three Data Archetypes                           │
├─────────────────────────┬─────────────────────────┬────────────────────┤
│ 1. EVENTS (Time-Series) │ 2. ENTITIES (Catalogs)  │ 3. DOCUMENTS (Prose│
├─────────────────────────┼─────────────────────────┼────────────────────┤
│ • data/events/nutrition/│ • data/entities/foods/  │ • entities/recipes/│
│ • data/events/sleep/    │ • data/entities/recipes/│ • syntheses/places/│
│ • data/events/feeding/  │ • data/entities/places/ │ • sources/*.pdf    │
│ • data/events/body/     │ • data/entities/people/ │                    │
│ [Point & Interval Logs] │ [Durable Slug Identifiers] [Markdown / Media]│
└────────────┬────────────┴────────────┬────────────┴──────────┬─────────┘
             │                         │                       │
             ▼                         ▼                       │
    ┌──────────────────────────────────────────────┐           │
    │         In-Memory DuckDB SQL Engine          │           │
    │  • Schema Validation (STRUCT & CHECK)        │           │
    │  • SQL Aggregations & Daily Deficit Joins    │           │
    │  • Spatial Queries (ST_DWithin / Haversine)  │           │
    └──────────────────────┬───────────────────────┘           │
                           │                                   │
              ┌────────────┴────────────┐                      │
              ▼                         ▼                      │
     ┌──────────────────┐      ┌──────────────────┐            │
     │ Telegram Output  │      │ Generated Views  │            │
     │ • Text Summaries │      │ • Markdown Views ◄────────────┘
     │ • chart-to-chat  │      │ • HTML Dashboards│ (Frontmatter:
     └──────────────────┘      │   (/family/...)  │  datastore_ref)
                               └──────────────────┘

Events (Time-Series Logs):
- Stored in data/events/<domain>/<YYYY-MM>.jsonl, sorted by (start, id).
- Unified envelope: id (ULID), kind, subject, actor, start, end (supports open intervals where end=NULL), estimated, meal, note, payload (JSON cast to typed STRUCT), source_ref.
Entities (Structured Catalogs):
- Stored in data/entities/<collection>.jsonl, sorted by slug id.
- foods: EU LMIV per-100g nutritional profile, ingredient lists, barcodes, density.
- recipes: Ingredients list (linking foods with exact grams or raw text), servings, calculated macros.
- places: Points of interest with lat, lon, category, address, tags, and linked documents.
- people: Family profile registry.
Documents (Prose & Media):
- Stored as standard Markdown in knowledge-wiki/.
- Deterministic two-way link: Document YAML frontmatter contains datastore_ref: "<collection>:<id>", while the entity JSONL record lists documents: ["path/to/doc.md"].

2. Dedicated Raw Garmin Mirror (`data/garmin/`)

Nightly zero-LLM Python sync script fetches raw Garmin API responses directly into:
- data/garmin/activities/<YYYY-MM>.jsonl
- data/garmin/daily/<YYYY-MM>.jsonl
- data/garmin/sleep/<YYYY-MM>.jsonl
DuckDB queries raw JSON directly with read_json_auto() and computes the daily caloric deficit/surplus by joining nutrition intake with Garmin total daily energy expenditure.

3. Spatial & Geospatial Radius Lookups

DuckDB attempts to initialize the official spatial extension (LOAD spatial;).
If offline or binary installation fails, it transparently falls back to an internal pure-SQL Haversine macro distance_m(lat1, lon1, lat2, lon2), ensuring zero-failure execution across any environment.

4. Privacy & Hosting Routes (`hosting.haumdaucher.de`)

Public (/recipes/*, /places/*, /public/*): Publicly accessible recipe cards and shareable dossiers.
Family (/family/*): Protected by Traefik ForwardAuth linked to Authentik group family (nutrition dashboards, baby sleep charts).
Private (/private/*): Protected by Authentik for personal vital logs.

Consequences

Positive

Guaranteed Calculation Accuracy: All calories, macronutrient totals, sleep statistics, and energy balances are computed in SQL. Zero LLM math errors.
Git-Native & Mergable: Text-based JSONL files partition data cleanly and produce clean 1-line git diffs.
Offline & Low-Latency: In-memory DuckDB reads months of JSONL in single-digit milliseconds.
Preserved Human Readability: SilverBullet (wiki.moritzgraf.de) and Obsidian continue to display updated Markdown tables generated directly from the database.

Negative & Mitigations

Generated Markdown Views Must Not Be Hand-Edited:
- Mitigation: Generated files include a prominent  warning comment.
- Mitigation: Git pre-commit hook and the weekly IT Guy audit verify that generated views match the underlying JSONL data.
Dual-Step Agent Retrieval: Agents execute datastore query for structured data, then wiki_get on linked documents if full narrative text is needed.
- Mitigation: Clear instructions and query helpers in the datastore skill prompt.

11 KiB Raw Permalink Blame History