## Audit outcome - Audited `/home/agent/.hermes/repos/HealthManager` at `90dd02e40a86a66f842b3be061555e9893159020`. - Branch: `main`; working tree remained clean. - No productive database, rows, health values, credentials, or item/product names were opened or reported. - Files created or modified: **none**. ## Existing nutrition schema Canonical DDL: `database/schema.sql` ### Core imported observations | Table | Columns | |---|---| | `nutrition_items` | `id, source, source_item_id, source_product_id, datum, meal, name, brand, amount, amount_unit, serving, serving_quantity, kcal, protein_g, carb_g, fat_g, raw_item_json, raw_product_json, item_hash, imported_at, updated_at` | | `nutrition_item_nutrients` | `item_id, nutrient_key, value, unit, raw_value` | | `nutrition_meal_summary` | `datum, meal, kcal, protein_g, carb_g, fat_g, item_count, nutrient_json, histamine_score, histamine_label, updated_at` | | `nutrition_daily_summary_v2` | `datum, kcal, protein_g, carb_g, fat_g, item_count, nutrient_json, histamine_score, histamine_max, histamine_unknown_count, histamine_label, updated_at` | ### Mapping and review | Table | Columns | |---|---| | `histamine_food_rules` | `id, canonical_food, sighi_score, category, tags, notes, confidence, source, updated_at` | | `histamine_food_aliases` | `alias, canonical_food` | | `nutrition_histamine_scores` | `item_id, canonical_food, sighi_score, traffic_light, tags, confidence, reason, scored_at` | | `nutrition_review_queue` | `id, normalized_name, example_name, occurrence_count, first_seen, last_seen, suggested_canonical_food, suggested_score, reason, status, updated_at` | | `personal_food_tolerance` | `canonical_food, personal_status, evidence_level, notes, updated_at` | ### Derived and legacy outputs | Table | Columns | |---|---| | `nutrition_daily_features` | `datum, plan_adherence_score, histamine_score, saturated_fat_score, sugar_score, gluten_flag, pork_flag, nightshade_flag, alcohol_flag, fiber_proxy, protein_proxy, omega3_proxy, positive_hits, negative_hits, source, computed_at` | | `nutrition_food_insights` | `id, canonical_food, insight_type, days_seen, avg_same_day_symptom, avg_next_day_symptom, avg_followup_symptom, histamine_avg, confidence, notes, computed_at` | | `nutrition_action_recommendations` | `id, priority, recommendation_type, title, rationale, next_step, status, computed_at` | | `nutrition_correlation_results` | `id, metric, target, lag_days, n, correlation, method, interpretation, computed_at` | | `nutrition_treatment_phase_summary` | `phase, start_date, end_date, nutrition_days, avg_histamine_score, avg_symptom_score, avg_kcal, avg_protein_g, notes, computed_at` | | `ernaehrung` | `id, datum, mahlzeit, beschreibung, wirkung, notizen, created_at` | | `tagebuch` | `id, datum, kategori, titel, inhalt, wirkung, verknuepft, notizen, created_at` | | `sync_state` | `key, value, updated_at` | **Inventory count:** 17 nutrition/supporting tables: 4 core, 5 mapping/review, 5 derived, 3 legacy/state. Existing explicit indexes: - `idx_nutrition_items_day_meal` - `idx_nutrition_items_product` - `idx_nutrition_nutrients_key` ## Current nutrition data paths ### Import/synchronization - `scripts/health/yazio_nutrition_sync.py` - Writes item, nutrient, item-score, meal-summary, daily-summary, legacy diary, and synchronization-state rows. - Reads mapping rules and aliases. - Does **not** populate or update `nutrition_review_queue`. - Assumes the nutrition schema already exists. - Has no explicit `--db` boundary. - `scripts/cron/yazio_sync.py` - Older legacy-only synchronization path. - Writes `ernaehrung`, `tagebuch`, and `sync_state`. - Uses a separate nutrient conversion convention from the newer path. - `scripts/cron/health_daily_sync.py` - Adjacent daily orchestration. - `scripts/health/multimodal_correlations.py` - Consumes only complete `nutrition_daily_summary_v2` aggregate observations. - `scripts/health/health_dashboard_v3.py` - Reads mapping-review and tolerance tables. - Also creates `low_histamine_experiments` during rendering, so this legacy renderer is not strictly read-only. ### Mapping workflow status - `nutrition_review_queue` is read by: - `scripts/health/health_dashboard_v3.py` - `scripts/health/health_dashboard_v4.py` - `scripts/health/dashboard_v5/data_provider.py` - No repository Python module inserts into or updates `nutrition_review_queue`. - No mapping-decision API, mutation leaf, action payload, worker dispatch, immutable decision audit, optimistic-concurrency field, or idempotency key currently exists. - Existing V5 bundle exposes at most 25 open queue entries and derives `mapping_open_count` from that truncated list rather than a separate total count. ## Current nutrition API surface Primary provider: `scripts/health/dashboard_v5/read_api.py` HTTP/auth boundary: `scripts/health/health_dashboard_server.py` There is **no dedicated `/api/v1/nutrition/*` endpoint**. Nutrition is currently available through: - `GET /api/v1/metric-catalog` - One nutrition metric: `nutrition.histamine`. - `GET /api/v1/series?metric=nutrition.histamine&from=&to=&resolution=` - Complete daily aggregate observations only. - `GET /api/v1/day/{date}` - Aggregate fields: energy/macronutrient fields, item count, mapping completeness, unknown-item count, aggregate mapping status. - `GET /api/v1/calendar?from=&to=` - Nutrition-day count/category only. - `GET /api/v1/search?q=` - Catalog-level nutrition metric discovery; no queue/rule search. - Static V5 bundle: - Up to 180 daily aggregate rows. - Up to 25 open mapping-review rows. - Contract in `scripts/health/dashboard_v5/contracts.py`. - Rendering shell in `scripts/health/dashboard_v5/render.py`. Security boundary already present: - SQLite opened with `mode=ro`, `query_only=ON`, and bounded query execution. - Bearer or short-lived browser session authentication. - Host/Origin checks and `no-store` API responses. - Unsupported API mutation methods fail closed. ## Existing action inbox and worker ### Network-facing producer `scripts/health/health_dashboard_server.py` - Only action route: `/health-actions/symptom-checkin`. - Only payload action: `symptom_checkin`, version 1. - Atomic queue write with: - private directory, - `0600` action files, - exclusive temporary creation, - file and directory `fsync`, - bounded pending count, - one-time CSRF and queue receipt. ### Consumer `scripts/health/health_dashboard_action_worker.py` - Supports only `symptom_checkin`. - Revalidates exact payload shape, date, dimensions, ranges, note length, filename, file type, owner, mode, size, symlink status, and duplicate JSON keys. - Requires explicit `HEALTH_DASHBOARD_DB`. - Runs with no network and regenerates the configured dashboard. - Successful actions are deleted; failures become `.failed`. - No nutrition action dispatch or failed-file retention cap exists. ### Deployment files - `deploy/systemd/health-dashboard.service` - `deploy/systemd/health-dashboard-action-worker.service` - `deploy/systemd/health-dashboard-action-worker.path` The existing queue and one-shot worker architecture can be reused for 6G-A without adding another service. ## Existing migrations and tests ### Migrations - `scripts/health/health_dashboard_v3_migrate.py` - Adds only `nutrition_daily_features` among nutrition tables. - `scripts/health/health_system_migrate.py` - Adds general review, summary, and correlation structures; not the core nutrition/mapping schema. - `scripts/health/document_fts_migrate.py` - Document-only migration. - `database/schema.sql` - Canonical schema snapshot, not a deployable nutrition migration. **Gap:** there is no idempotent migration that creates or upgrades the current nutrition import/mapping tables. ### Nutrition-related test files Count: **5** - `tests/test_health_dashboard_v4.py` - `tests/test_health_dashboard_v5.py` - `tests/test_dashboard_v5_sprint6b_catalog.py` - `tests/test_multimodal_correlations_v1.py` - `tests/fixtures/dashboard_v5_fixture.py` Existing coverage includes: - Missing-versus-explicit-zero behavior. - Complete-versus-unknown mapping behavior. - Aggregate metric availability. - Static bundle contract and size limits. - Correlation complete-case handling. - Synthetic fixture integrity. Missing coverage includes: - Queue population from unmapped imports. - Mapping queue pagination and true total count. - Mapping options/rule lookup API. - Mapping action CSRF, replay, concurrency, and queue-cap behavior. - Worker mapping revalidation and idempotency. - Immutable mapping decision audit. - Reclassification and aggregate recomputation after a mapping decision. - Explicit database targeting for nutrition synchronization/mutation. - Failure after DB commit but before regeneration. ## Minimal additive 6G-A implementation plan ### 1. Add an idempotent mapping migration **New** - `scripts/health/nutrition_mapping_migrate.py` - `tests/test_nutrition_mapping_migrate.py` **Update** - `database/schema.sql` Add one audit table: ```text nutrition_mapping_decisions: id action_id review_queue_id decision canonical_food expected_queue_updated_at reason created_at ``` Recommended constraints: - `action_id UNIQUE` for idempotency. - FK from `review_queue_id` to `nutrition_review_queue.id`. - Decision allowlist: map existing alias, defer, or ignore. - Mapping to a canonical entry must reference an existing rule; score/classification is loaded server-side, never accepted from the browser. - Index on `(review_queue_id, created_at)`. - Migration must assert prerequisite nutrition tables and run integrity/FK checks. ### 2. Populate the existing review queue **Update** - `scripts/health/yazio_nutrition_sync.py` **New** - `tests/test_nutrition_sync_mapping_queue.py` Changes: - Add mandatory `--db`. - Upsert unmapped normalized identities into `nutrition_review_queue`. - Increment bounded occurrence counts and update first/last-seen metadata. - Do not automatically map ambiguous/composite identities. - Keep unknown mapping as unknown in item and daily aggregates. - Remove or formally retire the conflicting legacy sync path in a later sprint; do not silently run both. ### 3. Add bounded read-only mapping APIs **New** - `scripts/health/dashboard_v5/nutrition_api.py` - `tests/test_dashboard_v5_sprint6g_a_nutrition_api.py` **Update** - `scripts/health/dashboard_v5/read_api.py` Endpoints: - `GET /api/v1/nutrition/mappings?status=&cursor=&limit=` - `GET /api/v1/nutrition/mapping-options?q=&limit=` Requirements: - SQL filtering and keyset pagination before limit. - Separate bounded `open_total`; do not derive it from the displayed page. - Exact status/query/limit allowlists. - No raw source JSON, local paths, credentials, or unrestricted column selection. - Reuse existing read-only connection, authentication, Origin, response-size, timeout, and `no-store` boundaries. ### 4. Extend the private action queue **New** - `scripts/health/nutrition_mapping_apply.py` - `tests/test_dashboard_v5_sprint6g_a_mapping_actions.py` **Update** - `scripts/health/health_dashboard_server.py` - `scripts/health/health_dashboard_action_worker.py` Add: - Route: `/health-actions/nutrition-mapping` - Action: `nutrition_mapping_decision` - Payload fields: version, action ID, queue ID, expected queue revision/timestamp, decision, optional canonical-rule ID, bounded reason. Worker behavior: - Revalidate independently. - Require explicit DB. - Apply with one transaction and optimistic concurrency. - Insert immutable decision audit. - Update queue status. - Add/update alias only for an explicitly allowed mapping decision. - Re-score affected items and recompute only affected meal/day aggregates. - Treat action replay as an idempotent success. - Preserve queued-versus-applied receipt semantics. - Add a bounded failed-action retention policy. No systemd topology change is required; update unit tests only if executable allowlists or environment variables change. ### 5. Connect the V5 mapping UI **Update** - `scripts/health/dashboard_v5/render.py` - `scripts/health/assets/health-assets/dashboard-v5.js` - `scripts/health/assets/health-assets/dashboard-v5.css` - `scripts/health/dashboard_v5/contracts.py` **New** - `scripts/health/assets/health-assets/dashboard-v5-nutrition.js` - `tests/browser/dashboard_v5_nutrition_mapping_6g_a.spec.js` - `tests/browser/run_v5_nutrition_6g_a_focused.sh` Use the read API for paginated queue/options and the existing private action queue for writes. Keep synthetic placeholders only in fixtures and browser evidence. ### 6. Sprint documentation and release evidence **New** - `docs/sprint6g-a-nutrition-backend-mapping.md` - `docs/test-results/sprint6g-a-integration-gate.json` Document: - schema migration and rollback, - route/payload contracts, - unknown/missing preservation, - alias-only mapping scope, - explicit exclusion of automatic composite-item classification, - synthetic-only acceptance commands, - no deployment without explicit approval. ## Principal architecture issues - Canonical nutrition tables exist, but no deployable nutrition migration owns them. - Mapping review is currently display-only and has no writer. - The review count is truncated-page length, not a true total. - The newer sync assumes a fixed DB and pre-existing schema. - Two sync implementations have divergent normalization behavior. - Mapping decisions lack audit, idempotency, and concurrency control. - Existing secure queue/worker architecture is suitable for extension and should remain the sole browser-originated write boundary.