317 lines
35 KiB
Markdown
317 lines
35 KiB
Markdown
---
|
||
name: meal-planning
|
||
description: "Class-level skill for weekly family meal planning. 3-phase workflow: automated proposal (Wed cronjob) → interactive discussion (Telegram chat) → finalization (Fri cronjob). Weather adaptation, diet constraints, grocery list production."
|
||
version: 2.0.0
|
||
tags: [meal-planning, proposal, discussion, generator, weather, family]
|
||
---
|
||
|
||
## Overview
|
||
|
||
This skill manages the weekly meal planning for the Schön family (Dominik, Sarah, Cleo 4).
|
||
|
||
The combined skill provides:
|
||
1. **Automated proposal generation** (Wednesday cronjob via `nutrition-plan-generator-v4.py`)
|
||
2. **Interactive discussion** (Wednesday/Thursday in Telegram DM with nutrition-coach bot)
|
||
3. **Finalization + shopping list** (Friday cronjob)
|
||
4. **Scoring algorithm** (see `references/generator-v4-scoring.md`)
|
||
|
||
### Usage
|
||
|
||
- Load this skill before any meal‑planning request.
|
||
- **3-Phase Workflow:** Vorschlag (Mi 19:00 cron) → Diskussion (Mi/Do interaktiv) → Finale (Fr 18:00 cron)
|
||
- **Phase 1 (Vorschlag):** Cronjob `essensplan-vorschlag` generiert Plan via `nutrition-plan-generator-v4.py`, speichert `plan_data/current_proposal.md`, liefert Summary in Telegram DM
|
||
- **Phase 2 (Diskussion):** User reagiert im Telegram-Chat → Coach lädt `current_proposal.md`, tauscht Gerichte, aktualisiert die Datei
|
||
- **Phase 3 (Finale):** Cronjob `essensplan-finale` liest `current_proposal.md`, schreibt `KW<NN>_<YYYY>_plan.md` + `KW<NN>_<YYYY>_einkaufsliste.md`, liefert finale Summary
|
||
- **v4 uses real recipes**: Reads from `real_recipes.jsonl` (25k+ Chefkoch entries), NOT the old `recipes.json` which contained 5000 dummy placeholders.
|
||
- **Compact Telegram output**: Cronjob delivers a 7-line summary; full details remain in `~/Documents/Essensplanung/`.
|
||
- **Delivery**: All cronjobs deliver to `origin` (Telegram DM), NOT to group chats.
|
||
- **Model**: `vllm/glm-5-2-nvfp4` via `noris`.
|
||
|
||
## Pitfalls
|
||
|
||
- **Cronjobs cannot conduct interactive interviews.** A cronjob fires once, sends output, and exits. User replies arrive in Telegram but are never read back. The old Wednesday interview cronjob produced `current_interview.json` with all answers `null`.
|
||
- *Fixed:* Replaced with 3-phase workflow. Wednesday cronjob generates proposal (no interview needed). Discussion happens in interactive Telegram chat.
|
||
- **Cron delivery target must be `origin` or numeric chat ID.** `telegram:Schoen Inc` fails with `invalid literal for int() with base 10: 'Schoen Inc'`. Use `origin` for DM delivery.
|
||
- **Per-job model overrides in cron JSON.** Each job stores its own `model`/`provider` fields in `profiles/nutrition-coach/cron/jobs.json`. Bulk-switching the profile model does **not** update existing cron jobs. Patch the JSON or recreate the jobs after any model migration.
|
||
- **Scraper ID prefixes must stay consistent across versions.** v5.3 used `hf_` for HelloFresh recipe IDs. v6 initially generated `hellofresh_` — this silently broke deduplication and would have re-scraped all 555 existing recipes. Always check existing ID formats in `real_recipes.jsonl` before changing a normalizer.
|
||
- **Scraper state migration across versions.** Legacy state files from older scraper versions use different field names (`ck_cycle` vs `ck_query_cycle`, `hf_cycle: 8763` from v5.3). When upgrading the scraper, explicitly initialize/reset state fields. Don't assume the new code handles old state gracefully.
|
||
- **Python variable ordering in config sections.** When adding a new source config block (e.g., `HF_HEADERS` referencing `UA`), ensure shared variables like `UA` are defined **before** any config dict that references them. Module-level execution is top-down; a `NameError` at import time kills the entire scraper.
|
||
- **HelloFresh is NOT exhausted.** Previous assessment said "~548 scraped, exhausted" — wrong. HelloFresh has ~16,831 recipes across 19 working categories (9 categories 404). Estimated ~10,000 unique after cross-category dedup. Highest data quality of all sources (99% have calories, 93% ratings, all have instructions+descriptions).
|
||
- **Quality ≠ Volume — don't dismiss low-volume sources without assessing data quality.** HelloFresh was dropped from v6 because "only 548 recipes, marginal ROI." User corrected: HelloFresh recipes are professionally curated with exact quantities, step-by-step instructions, calorie data, and difficulty ratings — qualitatively superior to community-submitted recipes. Always evaluate a source's data completeness (11-field checklist) alongside its volume before deciding to include/exclude it.
|
||
- **GuteKueche.de integrated in v6.** 29,392 recipes with 9/11 JSON-LD quality fields (calories ✅, ratings ✅, instructions ✅, prep/cook time ✅). Gzipped sitemap at `cdn.gutekueche.de/sitemaps/recipe.xml.gz` (found via robots.txt). Added to `SITEMAP_SOURCES` with `"gzipped": True` flag. Scraper `discover_sitemap_urls()` now auto-detects gzip via magic bytes `\x1f\x8b`. Combined potential: Chefkoch 25k + Kuechengoetter 30k + GuteKueche 29k + HelloFresh 10k + Kitchen Stories 3.3k = ~97k — 100k target reachable without international sources.
|
||
|
||
## References
|
||
|
||
- `references/recipe-sources.md` — Tested recipe data sources (Chefkoch API, Kuechengoetter, Kitchen Stories, GuteKueche, HelloFresh), JSON-LD availability, sitemap URLs, v7 scraper architecture
|
||
- `references/international-recipe-sources.md` — English-language sources for Asian/light/healthy cuisine (RecipeTin Eats, Budget Bytes, Cookie and Kate, Pick Up Limes, Just One Cookbook, Serious Eats, Minimalist Baker) with JSON-LD/sitemap status, translation strategy, and gap analysis
|
||
- `references/semantic-search-implementation.md` — Implemented semantic search + preference learning system: harrier embeddings, DB schema, API usage, tested examples
|
||
- `references/semantic-ontology-classification.md` — Rule-based ontology classifier: 11 dimensions, recipe_semantics table schema, Galera pitfalls (collation, FK, deadlocks, VARCHAR sizes)
|
||
- `references/semantic-search-investigation.md` — Historical investigation notes (pre-implementation, kept for context on what was tested and ruled out)
|
||
- `references/generator-v4-scoring.md` — Current v4 scoring dimensions, event overrides, Cleo logic
|
||
- `references/nutrition-interview-soest/generator-scoring-algorithm.md` — Legacy v3 algorithm (superseded)
|
||
- `references/multi-source-recipe-scraper/` — Scraper integration docs (mass-scraping, API v2, Playwright bypass, v5.2 migration)
|
||
- `templates/interview-data-schema.json` — JSON schema for `current_interview.json` (legacy, kept for reference)
|
||
|
||
## Scraper (v7 — Direct MySQL)
|
||
|
||
The v7 scraper (`profiles/nutrition-coach/scripts/recipe_scraper_v7.py`) writes recipes **directly** to MariaDB Galera — no JSONL intermediate.
|
||
- **Sources:** Chefkoch API + Kuechengoetter sitemap (30k) + GuteKueche sitemap (29k, gzipped) + Kitchen Stories sitemap (3.3k) + HelloFresh categories (~10k unique)
|
||
- **Target:** 100,000 recipes
|
||
- **Cronjob:** `recipe-refill-v7` runs daily at 06:00, delivers to `origin`
|
||
- **State:** `scraper_state.json` tracks tried URLs, query cycles, source counts, `hf_cat_offset` (per-category skip)
|
||
- **Dedup:** Loads all existing IDs into RAM at startup (`db_all_ids()`), checks in-memory — NEVER per-row DB roundtrips
|
||
- **URL pre-check:** Derives likely recipe ID from URL path and skips HTTP request entirely if ID already in DB
|
||
- **Insert:** `INSERT IGNORE` per recipe (idempotent, safe for re-runs)
|
||
- **Scrape order:** Sitemap sources first (finite URL sets) → HelloFresh (high quality) → Chefkoch (fill remainder)
|
||
- **Legacy JSONL:** `real_recipes.jsonl` is kept as historical archive only. v7 does NOT read or write it. The `migrate_to_galera.py` script was a one-time migration tool.
|
||
- See `references/recipe-sources.md` for full source documentation including HelloFresh `__NEXT_DATA__` technique, GuteKueche discovery, and source scanning methodology
|
||
- **Embeddings must be regenerated after scraping new recipes.** v7 does NOT generate embeddings. The `embedding-refill` cronjob (daily 06:30) runs `generate_embeddings.py` incrementally — only embeds recipes where `embedding IS NULL`. As of 2026-06-28, all 54,613 recipes have embeddings (100% coverage). The script includes `safe_db_execute()` with Galera deadlock retry (rollback + backoff) for robustness during concurrent writes.
|
||
|
||
## Storage Architecture
|
||
|
||
**Active: MariaDB Galera Cluster** (migrated from JSONL 2026-06-28, v7 writes directly — no JSONL intermediate).
|
||
|
||
User chose Galera over SQLite — reuses existing HA infrastructure, no new dependencies. v7 scraper writes directly via `INSERT IGNORE`; `real_recipes.jsonl` is a historical archive only.
|
||
|
||
- **Connection:** `10.0.30.70:3306` (MaxScale VIP) → 3-node Galera cluster (10.0.30.71-73)
|
||
- **Database:** `recipes`, **User:** `recipe_app@%`
|
||
- **Table:** `recipes` with InnoDB engine, utf8mb4
|
||
- **Indexes:** FULLTEXT on (title, description) and (ingredients); BTREE on source, category, rating, difficulty
|
||
- **Schema fields (verified 2026-06-28 via DESCRIBE):** `id` (PK), `title`, `name`, `description`, `category`, `tags`, `ingredients` (JSON text, FULLTEXT indexed), `instructions`, `source`, `source_url`, `rating`, `rating_value`, `rating_count`, `prep_time`, `cook_time`, `total_time`, `servings`, `calories`, `difficulty`, `scraped_at`, `embedding` (JSON, nullable)
|
||
- **⚠️ Column name reality vs older docs:** There is NO `image_url` column (use `source_url`). There is NO `ingredients_json` column (use `ingredients`). There is NO `url` column (use `source_url`). There is NO `created_at` column (use `scraped_at`). Columns that exist but were previously undocumented: `name`, `rating_value`, `total_time`, `difficulty`.
|
||
- **Migration script:** `profiles/nutrition-coach/scripts/migrate_to_galera.py` — one-time migration tool (INSERT IGNORE with batch=200, skips existing IDs). No longer needed for ongoing operation — v7 writes directly to Galera.
|
||
- **Concurrent access:** MaxScale readwritesplit → scraper writes while planner reads (no locking)
|
||
- **Query examples:** `SELECT * FROM recipes WHERE rating >= 4.3 AND MATCH(title, description) AGAINST('hähnchen curry' IN BOOLEAN MODE)`
|
||
- **Legacy JSONL:** `real_recipes.jsonl` is kept as historical archive only. v7 does NOT read or write it. The `migrate_to_galera.py` script was a one-time migration tool.
|
||
|
||
## Semantic Search + Preference Learning (Implemented 2026-06-28)
|
||
|
||
Fully built and tested. Uses `vllm/harrier-oss-v1-0.6b` (1024-dim) embeddings via norris `/v1/embeddings` endpoint.
|
||
|
||
### Scripts (all in `profiles/nutrition-coach/scripts/`)
|
||
|
||
- **`semantic_search.py`** — Core module: `semantic_search()`, `record_decision()`, `get_preference_adjustments()`, `rank_with_preferences()`, `mine_rules()`
|
||
- **`generate_embeddings.py`** — Batch embedding generator (64 recipes/API call, ~31/s, ETA ~15min for 31k recipes). Stores as JSON in `recipes.embedding` column.
|
||
- **`recipe_scraper_v7.py`** — Direct MySQL scraper (does NOT generate embeddings; run `generate_embeddings.py` separately after scraping)
|
||
|
||
### Database Tables
|
||
|
||
- **`recipes.embedding`** (JSON) — 1024-dim embedding array. Generated by `generate_embeddings.py`.
|
||
- **`meal_decisions`** — Every accept/reject with context. Columns (verified 2026-06-28): `id`, `decision_date`, `recipe_id`, `recipe_title`, `recipe_category`, `accepted` (bool), `context_temp`, `context_season`, `context_weekday`, `context_last_meals`, `reason`, `created_at`. NOTE: context fields are separate columns, NOT a single JSON blob.
|
||
- **`preference_rules`** — Learned rules. Columns (verified 2026-06-28): `id`, `rule_type`, `condition_json`, `effect_json`, `weight` (FLOAT), `confirmations` (INT), `rejections` (INT), `created_at`, `updated_at`. NOTE: condition/effect are JSON strings, not separate columns.
|
||
|
||
### How It Works
|
||
|
||
1. **Semantic search:** Query text → harrier embedding → cosine similarity against all embedded recipes → ranked results blended with rating score
|
||
2. **Decision recording:** User accepts/rejects a recipe → `record_decision()` logs context + reason → auto-creates rule if reason matches weather/temperature patterns
|
||
3. **Preference ranking:** `rank_with_preferences()` applies learned rules (boost/penalty) on top of semantic scores. Also enforces variety (penalizes categories from recent meals).
|
||
4. **Rule mining:** `mine_rules()` analyzes accumulated decisions for statistical patterns (e.g., "Eintopf rejected 80% of the time when temp>20°C" → creates/upgrades rule)
|
||
|
||
### Embedding Model: vllm/harrier-oss-v1-0.6b
|
||
|
||
- **NOT** an LLM — it's a dedicated embedding model on norris (0.6B params)
|
||
- 1024 dimensions, multilingual (German works well)
|
||
- Available via `POST https://ai.noris.de/v1/embeddings` with `{"model": "vllm/harrier-oss-v1-0.6b", "input": [...]}`
|
||
- Batch API supports up to 64 inputs per call
|
||
- Other noris models (bge-m3, e5-large, etc.) are recognized by the gateway but NOT activated — only harrier works
|
||
- API key: same noris key from `config.yaml` providers.noris.api_key
|
||
|
||
See `references/semantic-search-implementation.md` for full architecture details.
|
||
|
||
## Semantic Ontology Classification (Added 2026-06-28)
|
||
|
||
Rule-based classifier annotates every recipe across 11 ontology dimensions: diet type, meal course, cuisine, cooking method, protein source, allergens, flavor profile, complexity, season, caloric density, and preparation style. Pure keyword matching — no LLM/API calls. Stores results in `recipe_semantics` table.
|
||
|
||
- **Script:** `profiles/nutrition-coach/scripts/semantic_classifier.py` (~500/batch, ~50-500/s, ~10min for 54k recipes)
|
||
- **Table:** `recipe_semantics` (recipe_id PK, 11 dimension columns as ENUM/JSON, confidence, analyzed_at)
|
||
- **Cronjob:** `semantic-classifier` daily at 07:00 (after scraper 06:00 + embeddings 06:30)
|
||
- **Incremental:** LEFT JOIN finds unclassified recipes, only processes new ones. Uses `INSERT IGNORE` to skip duplicate recipe IDs (some Chefkoch IDs appear multiple times in the DB due to URL-state-encoded duplicates).
|
||
- See `references/semantic-ontology-classification.md` for full ontology dimensions, table schema, and Galera-specific pitfalls
|
||
|
||
## Ingredient Normalization + Substitution Graph (Added 2026-06-28)
|
||
|
||
Relational ingredient system enabling exact filtering, allergen cross-checking, and "what can I substitute?" queries. Three tables + a parser/normalizer script.
|
||
|
||
### Tables
|
||
|
||
| Table | Purpose | Key Columns |
|
||
|-------|---------|-------------|
|
||
| `ingredients` | Master ingredient table (normalized) | `id`, `canonical_name`, `slug`, `category` (ENUM 21 values), `allergen_flags` (JSON), `is_vegan`, `is_vegetarian` |
|
||
| `recipe_ingredient_links` | Junction table (many-to-many) | `recipe_id`, `ingredient_id`, `quantity` (DECIMAL), `unit`, `raw_text`, `modifier`, `position` |
|
||
| `ingredient_substitutes` | Substitution graph (bidirectional) | `ingredient_id`, `substitute_id`, `similarity_score` (0-1), `context`, `reason` |
|
||
|
||
### Script: `scripts/ingredient_normalizer.py`
|
||
|
||
- **Parsing:** Regex extracts (quantity, unit, name, modifier) from raw strings like `"400 g Lachsfilet(s) (mit Haut)"` → `(400, "g", "Lachsfilet", "mit Haut")`
|
||
- **Canonicalization:** Plural→singular, alias overrides (~250 manual mappings, e.g. `Mohrrüben→karotte`, `hühnchen→hähnchen`), descriptor stripping (`frisch`, `getrocknet`, `Bio`, etc.), English→German mapping (`garlic→knoblauch`)
|
||
- **Categorization:** 21 food categories (meat, poultry, fish, seafood, vegetable, fruit, grain, legume, nut_seed, dairy, egg, fat_oil, spice_herb, condiment_sauce, sweetener, flour_starch, liquid, baking, beverage, other) via keyword matching
|
||
- **Allergen detection:** Checks 14 EU allergen categories against canonical name
|
||
- **Substitution graph:** Seeded with ~60 culinary substitution pairs, bidirectional. Each pair has similarity_score (0-1), context (when the substitution works), and reason (why they're similar). Examples: koriander↔petersilie (0.75, "European/Western dishes"), schmand↔crème fraîche (0.95, "General"), milch↔hafermilch (0.90, "General/vegan")
|
||
|
||
### Query Examples
|
||
|
||
```sql
|
||
-- Recipes WITH zucchini, WITHOUT paprika, <500 kcal
|
||
SELECT DISTINCT r.* FROM recipes r
|
||
JOIN recipe_ingredient_links rl ON r.id = rl.recipe_id
|
||
JOIN ingredients i ON rl.ingredient_id = i.id AND i.canonical_name = 'zucchini'
|
||
WHERE r.calories < 500
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM recipe_ingredient_links rl2
|
||
JOIN ingredients i2 ON rl2.ingredient_id = i2.id
|
||
WHERE rl2.recipe_id = r.id AND i2.canonical_name = 'paprika'
|
||
)
|
||
|
||
-- What can I substitute for coriander?
|
||
SELECT i2.canonical_name, s.similarity_score, s.context, s.reason
|
||
FROM ingredient_substitutes s
|
||
JOIN ingredients i1 ON s.ingredient_id = i1.id AND i1.canonical_name = 'koriander'
|
||
JOIN ingredients i2 ON s.substitute_id = i2.id
|
||
ORDER BY s.similarity_score DESC
|
||
```
|
||
|
||
### Pitfalls (Ingredient Normalization)
|
||
|
||
- **Galera FK creation fails on collation mismatch.** Same issue as `recipe_semantics` — `recipes` uses `utf8mb4_unicode_ci`, new tables default to `utf8mb4_general_ci`. Omit FKs or specify matching COLLATE.
|
||
- **Galera deadlocks from concurrent writes.** Running `ingredient_normalizer.py` and `semantic_classifier.py` simultaneously causes Deadlock (1213). Run sequentially or add retry logic.
|
||
- **Some recipe IDs exceed VARCHAR(128).** Chefkoch IDs with URL-encoded state fragments can be 248 chars. Use VARCHAR(255) for `recipe_id` columns.
|
||
- **Compound ingredients like "Salz und Pfeffer"** are kept as-is (not split). The parser only splits when a quantity prefix is present.
|
||
- **Ingredient string diversity is massive.** ~250k unique raw strings across 54k recipes, normalizing to ~2-5k canonical ingredients. The `CANONICAL_OVERRIDES` dict needs expansion over time for better coverage.
|
||
|
||
## Scraper v8 — International Sources + Auto-Translation (Added 2026-06-28)
|
||
|
||
The v8 scraper (`profiles/nutrition-coach/scripts/recipe_scraper_v8_intl.py`) extends v7 with 13 international English-language sources and integrated LLM translation to German.
|
||
|
||
### Sources (13 international, ~20k recipes total)
|
||
|
||
All sources use sitemap + JSON-LD (same infrastructure as v7). See `references/international-recipe-sources.md` for full details including sitemap URLs, recipe counts, and ID prefixes.
|
||
|
||
BBC Good Food (~5k), SkinnyTaste (~2.3k), Woks of Life (~1.5k), RecipeTin Eats (~1.7k), Rasa Malaysia (~1.4k), Sanjeev Kapoor (~2k, custom parser), Budget Bytes (~2k), Pinch of Yum (~1.5k), Omnivore's Cookbook (~772), Hot Thai Kitchen (~464), Spoon Fork Bacon (~911), Korean Bapsang (~275), Minimalist Baker (~650).
|
||
|
||
### Translation Pipeline
|
||
|
||
1. **Scrape** recipe page → extract JSON-LD → normalize to recipe dict
|
||
2. **Batch** 3 recipes per LLM call
|
||
3. **Translate** via `vllm/gemma-4-31b-it-dynamo` (non-reasoning model, noris API)
|
||
4. **Unit conversion** baked into translation prompt: cups→g/ml, tbsp→EL, tsp→TL, oz→30g, lb→450g, °F→°C, stick butter→125g
|
||
5. **Insert** translated recipe into Galera (German title, ingredients, instructions)
|
||
|
||
### Translation Model Choice — CRITICAL
|
||
|
||
- **USE `vllm/gemma-4-31b-it-dynamo`** (non-reasoning, fast, 4s/batch, 169 completion tokens)
|
||
- **DO NOT USE `vllm/glm-5-2-nvfp4`** for translation — it's a reasoning model that wastes tokens on chain-of-thought (1710 completion tokens for same task, 10x cost) and sometimes returns EMPTY `content` field when reasoning exhausts max_tokens before generating the answer.
|
||
- User explicitly directed: "nutze für übersetzungen ein nicht-reasoning modell wie gemma4-dynamo"
|
||
|
||
### Unit Conversion Rules (embedded in LLM system prompt)
|
||
|
||
| US Unit | Metric Equivalent |
|
||
|---------|-------------------|
|
||
| 1 cup (liquid) | 240 ml |
|
||
| 1 cup (solid: flour/sugar) | 240 g |
|
||
| 1 tablespoon (tbsp) | 1 EL |
|
||
| 1 teaspoon (tsp) | 1 TL |
|
||
| 1 ounce (oz) | 30 g |
|
||
| 1 pound (lb) | 450 g |
|
||
| 1 pint | 475 ml |
|
||
| 1 quart | 950 ml |
|
||
| 1 stick butter | 125 g Butter |
|
||
| °F | °C ((F-32) × 5/9) |
|
||
|
||
User requirement: "Keine cups und spoons" — ALL recipes must use metric units.
|
||
|
||
### Pitfalls (v8 Scraper + Translation)
|
||
|
||
- **Reasoning models return empty `content` for translation tasks.** GLM-5.2 spends tokens on internal reasoning before generating the answer. With max_tokens=4000, reasoning consumed all tokens and `content` was empty string. Fix: use non-reasoning model (gemma-dynamo) OR increase max_tokens to 6000+. The non-reasoning model is the correct fix — translation is mechanical, not reasoning-intensive.
|
||
- **Translation batch size matters for reliability.** Batch of 5 recipes sometimes exceeded the model's reliable output capacity. Reduced to 3 recipes per batch for consistent results.
|
||
- **Sanjeev Kapoor needs custom parser.** Non-standard JSON-LD with single-quote `@type: 'Recipe` instead of `"Recipe"`. The v8 scraper includes `extract_sanjeev_recipe()` with regex fallback for this case.
|
||
- **BBC Good Food quarterly sitemaps.** Must iterate through all quarterly sitemap files (`YYYY-QN-recipe.xml`) to get full coverage. 24 quarters yielded ~5,045 recipes.
|
||
- **Translation adds significant latency.** Each batch of 3 recipes takes ~4-10s for the LLM call plus scraping time. Total throughput: ~1 recipe/2s. For 20k recipes, expect ~11 hours. Run as background process overnight.
|
||
- **Post-scrape pipeline runs automatically.** The v8 scraper's `main()` function calls `generate_embeddings.py` → `semantic_classifier.py` → `ingredient_normalizer.py` via `subprocess.run()` after scraping completes (only if `grand_added > 0`). No separate cronjobs needed. See "Daily Pipeline" section above.
|
||
|
||
## Daily Pipeline (Integrated — Updated 2026-06-28)
|
||
|
||
**Single cronjob** `recipe-scraper-v8-pipeline` (daily 06:00) runs the full pipeline sequentially:
|
||
|
||
```
|
||
recipe_scraper_v8_intl.py
|
||
├── 1. Scrape international sources + translate to German
|
||
├── 2. generate_embeddings.py (embeddings for new recipes)
|
||
├── 3. semantic_classifier.py (ontology classification)
|
||
└── 4. ingredient_normalizer.py (relational ingredient normalization)
|
||
```
|
||
|
||
Pipeline steps 2–4 only run if `grand_added > 0` (no wasted work when nothing new scraped). Each step has a 2h timeout. Logs go to `scraper_v8_log.txt`.
|
||
|
||
**Why integrated (user correction):** User asked "Warum nicht embedding refill, classifier und ingredient normalizer direkt am Ende des scraper laufs?" — separate cronjobs with fixed schedules waste time waiting (06:30 embeddings waits 30min after scraper finishes) and risk overlapping if the scraper runs long. Integrated pipeline runs immediately after scraping, sequentially, no deadlocks.
|
||
|
||
**Previous setup (REMOVED):** Three separate cronjobs — `recipe-refill-v7` (06:00), `embedding-refill` (06:30), `semantic-classifier` (07:00). The 06:30 and 07:00 jobs were deleted; the 06:00 job was renamed to `recipe-scraper-v8-pipeline` and its prompt updated to run the v8 scraper (which includes the pipeline internally).
|
||
|
||
## Recipe MCP Server (Added 2026-06-28)
|
||
|
||
A stdio MCP server (`profiles/nutrition-coach/scripts/recipe_mcp_server.py`) exposes the Galera recipe DB as 7 first-class Hermes tools. Registered in `config.yaml` under `mcp_servers.recipes`. Tools auto-discover at gateway startup and appear as `mcp_recipes_*` in all profiles.
|
||
|
||
### Tools
|
||
|
||
| Tool | Purpose |
|
||
|------|---------|
|
||
| `search_recipes` | FULLTEXT search on title+description and ingredients, with filters (category, calories, prep_time, rating, source) |
|
||
| `semantic_recipe_search` | Vector similarity search using harrier embeddings (cosine sim over 500-candidate pool, re-ranked by similarity) |
|
||
| `get_recipe` | Full recipe by ID (includes ingredients, instructions, all fields) |
|
||
| `random_recipes` | Random recipes for meal-planning inspiration (with category/source/rating filters) |
|
||
| `recipe_stats` | DB statistics: total count, per-source breakdown, embedding coverage |
|
||
| `record_meal_decision` | Log accept/reject for preference learning (denormalizes recipe title+category into meal_decisions) |
|
||
| `get_preferences` | Show learned preference rules + decision stats |
|
||
|
||
### Config (in `~/.hermes/config.yaml`)
|
||
|
||
```yaml
|
||
mcp_servers:
|
||
recipes:
|
||
command: "/home/debian/hermes-agent/venv/bin/python3"
|
||
args: ["/home/debian/.hermes/profiles/nutrition-coach/scripts/recipe_mcp_server.py"]
|
||
env:
|
||
RECIPES_DB_PASS: "<galera-password>"
|
||
NORIS_API_KEY: "<noris-api-key>"
|
||
timeout: 60
|
||
```
|
||
|
||
### Pitfalls
|
||
|
||
- **Column names differ from older skill docs.** The MCP server initially referenced `image_url` and `url` columns that don't exist. Always `DESCRIBE recipes` before writing SQL. See the verified schema above.
|
||
- **FULLTEXT search requires German umlauts.** Searching "Haehnchen" returns 0 results; "Hähnchen" works. The FULLTEXT index is case-insensitive but NOT umlaut-normalized.
|
||
- **MCP env vars are filtered.** Hermes only passes `PATH`, `HOME`, etc. to stdio subprocesses. DB password and API key MUST be explicitly set in the `env` block of the MCP server config — they are NOT inherited from `.env`.
|
||
- **Gateway restart required after adding MCP servers.** MCP tools are discovered at startup. Adding a server to `config.yaml` does not hot-reload. Use `/restart` (gateway) or restart the systemd service.
|
||
|
||
- **Scraper max_new limits must be high for background operation.** v7 defaulted to `max_new=5000` per sitemap source and `max_new=2000` for Chefkoch per run. This was designed for daily cronjob safety, but in background operation it needlessly caps throughput — Kuechengoetter (30k URLs) and GuteKueche (29k URLs) stopped at 5k each per run, requiring 6+ runs to fully scrape. Fix (2026-06-28): raised to 50k (sitemap), 50k (HelloFresh), 10k (Chefkoch). Result: Kuechengoetter jumped from 5k to 20,442 in one run. For cronjob mode, the daily run naturally bounds total work; for background runs, the higher limits allow completing a source in one pass.
|
||
- **Per-row dedup checks are catastrophically slow on Galera.** v7 initial design called db_has_id(rid) for every candidate URL — each a network roundtrip through MaxScale. With 28k existing recipes and 30k sitemap URLs mostly already in DB, the scraper appeared hung for minutes doing nothing but dedup queries. Fix: db_all_ids() loads all IDs into a Python set() once at startup (~0.3s for 28k), then all dedup checks are in-memory. Additionally, derive the likely recipe ID from the URL path and skip the HTTP fetch entirely if the ID is already known.
|
||
- **Preference rule category matching must check title and tags, not just the category field.** Most Eintopf recipes have category Kochen, Kartoffeln, or Hauptspeise — not Eintopf. A rule penalizing category Eintopf would miss Moehren-Kartoffel-Eintopf (category: Kochen). Fix in rank_with_preferences(): concatenate category + title + tags into one lowercase string and check if the penalty/boost term appears anywhere in it.
|
||
- **recipe_to_row() must sanitize dict/list fields before MySQL INSERT.** Some recipes have category as a dict (e.g., {"name": "..."}) or calories as a nested dict. PyMySQL raises TypeError: dict can not be used as parameter. Always coerce to string/primitive before passing to executemany().
|
||
- **HelloFresh recipes may have EMPTY ingredients arrays in the DB.** Some HelloFresh recipes show ingredients as an empty JSON array despite being successfully scraped. The _hf_normalize() function builds ingredients from yields[0].ingredients[] joined with the ingredients[] lookup — if the yield structure differs or ingredient IDs do not match, the result is empty. This breaks ingredient-based SQL searches (LIKE finds nothing). Chefkoch recipes reliably have populated ingredient lists.
|
||
- **Searching JSON columns with LIKE requires CAST.** MariaDB stores ingredients as LONGTEXT (JSON-encoded). Direct LIKE may fail depending on MariaDB version. Use LOWER(CAST(ingredients AS CHAR)) LIKE for reliable JSON field searching. For production queries, prefer the FULLTEXT index: MATCH(ingredients) AGAINST in BOOLEAN MODE.
|
||
- **Norris embedding endpoint only works with vllm/harrier-oss-v1-0.6b.** All other norris models (glm, gemma, qwen, bge-reranker) return 404 on /v1/embeddings. Standard embedding model names (bge-m3, e5-large, gte-large) are recognized by the Bifrost gateway but NOT activated on any API key. Only harrier-oss-v1-0.6b (1024 dim) is available for embeddings.
|
||
- **Galera FK creation fails on collation mismatch.** The `recipes` table uses `utf8mb4_unicode_ci` but new tables default to `utf8mb4_general_ci`. FK creation fails with `errno 150`. Fix: omit the FK (preferred) or specify matching COLLATE on the new table.
|
||
- **Galera JOINs between tables with different collations fail.** `LEFT JOIN ... ON r.id = rs.recipe_id` fails with `Illegal mix of collations`. Fix: add `COLLATE utf8mb4_unicode_ci` to the JOIN condition.
|
||
- **VARCHAR(128) is too short for some recipe IDs.** Chefkoch IDs with encoded state fragments (e.g., `ck_Kinderueberraschungsbrot.html#eyJvYm...`) can be up to 248 chars. Use `VARCHAR(255)` for any column storing recipe IDs.
|
||
- **Time/calorie columns contain string values.** Some `prep_time`, `cook_time`, `total_time`, `calories` values are stored as strings (e.g., `"PT20M"`, `"450 kcal"`), not integers. Always use a `safe_int()` helper before comparisons/arithmetic.
|
||
- **Concurrent writes cause Galera deadlocks.** Running the semantic classifier and embedding generator simultaneously causes `Deadlock (1213)`. Fix: add retry logic with rollback+backoff, or run sequentially (daily cronjob schedule ensures this).
|
||
- **Classifier batch inserts hit duplicate-key errors.** Some Chefkoch recipe IDs appear multiple times in the `recipes` table (URL-encoded state fragments create variant IDs that normalize to the same key). Using `INSERT IGNORE` in the `executemany()` call silently skips duplicates instead of failing the entire batch. Without `INSERT IGNORE`, every batch containing a duplicate fails and falls through to one-by-one insertion, cutting throughput by ~10×.
|
||
- **Cloudflare blocks ≠ "no sitemap".** Just One Cookbook was initially diagnosed as "no sitemap" — actually the sitemap exists but Cloudflare returns an HTML challenge page instead of XML. Always check HTTP status codes and content-type, not just whether XML parse succeeds. Maangchi similarly returns HTTP 403 for all programmatic requests despite a permissive robots.txt.
|
||
- **AI-bot blocks in robots.txt are selective.** Many sites (RecipeTin Eats, Cookie and Kate, Healthy Nibbles, SkinnyTaste) block `anthropic-ai`, `Claude-Web`, `GPTBot`, or `ChatGPT-User` specifically. They do NOT block generic `Mozilla/5.0` user agents. Use a standard browser-like UA, not an AI-identifying one.
|
||
- **Empty first sitemap ≠ empty site.** Minimalist Baker's `post-sitemap.xml` returns 0 URLs but `post-sitemap2.xml` has 650. Always check ALL sitemap files in the index.
|
||
- **Non-standard JSON-LD.** Sanjeev Kapoor has recipe data but in a non-standard JSON structure (`@type: 'Recipe` with single quotes). Standard JSON-LD parsers will miss it. May need regex-based extraction or custom parser.
|
||
- **Quarterly sitemap pattern.** BBC Good Food organizes recipe sitemaps by quarter (`YYYY-QN-recipe.xml`). Must iterate through all quarters to get full coverage (~5,045 recipes across 24+ quarterly files).
|
||
- **AllRecipes sitemaps are empty shells.** Despite having `sitemap_1.xml` through `sitemap_4.xml`, they contain 0 URLs. Not scrapable via sitemap approach.
|
||
- **Archana's Kitchen has 8,428 sitemap URLs but NO JSON-LD.** Would need custom HTML parsing — not worth the effort vs. Sanjeev Kapoor which has (non-standard) JSON-LD.
|
||
- **Bon Appétit and Epicurious use Condé Nast's proprietary structured data format,** not schema.org/Recipe JSON-LD. Not scrapable with standard JSON-LD approach.
|
||
- **Running semantic_classifier.py and ingredient_normalizer.py simultaneously causes Galera deadlocks.** Both write to different tables but certification conflicts on shared rows cause `Deadlock (1213)`. Run sequentially or add retry logic with rollback+backoff.
|
||
- **Ingredient normalizer produces ~33k unique ingredient names from 54k recipes.** The `CANONICAL_OVERRIDES` dict (~250 manual mappings) covers the most common cases but many rare ingredients remain un-canonicalized. Expect ~16k ingredients categorized as "other" — expand the categorization keyword lists and canonical overrides over time.
|
||
- **Substitution graph seeding skips ingredients not in the DB.** 27 of 92 planned substitution pairs were skipped because one ingredient wasn't found in the first run. Re-seed after expanding canonical overrides.
|
||
- **Galera FK creation fails on collation mismatch.** Same issue as `recipe_semantics` — `recipes` uses `utf8mb4_unicode_ci`, new tables default to `utf8mb4_general_ci`. Omit FKs or specify matching COLLATE.
|
||
- **Galera deadlocks from concurrent writes.** Running `ingredient_normalizer.py` and `semantic_classifier.py` simultaneously causes Deadlock (1213). Run sequentially or add retry logic.
|
||
- **Some recipe IDs exceed VARCHAR(128).** Chefkoch IDs with URL-encoded state fragments can be 248 chars. Use VARCHAR(255) for `recipe_id` columns.
|
||
- **Compound ingredients like "Salz und Pfeffer"** are kept as-is (not split). The parser only splits when a quantity prefix is present.
|
||
- **Ingredient string diversity is massive.** ~250k unique raw strings across 54k recipes, normalizing to ~33k canonical ingredients. The `CANONICAL_OVERRIDES` dict needs expansion over time for better coverage.
|
||
- **Ingredients ARE relationally searchable (implemented 2026-06-28).** Three tables power the ingredient subsystem: `ingredients` (master table with canonical names, categories, allergen flags, vegan/vegetarian), `recipe_ingredient_links` (junction with quantity/unit/modifier/position), and `ingredient_substitutes` (substitution graph with similarity scores and context). Script: `scripts/ingredient_normalizer.py`. Parses raw ingredient strings via regex (quantity + unit + name + modifier), canonicalizes names (plural→singular, alias overrides, descriptor stripping), categorizes into 21 food categories, and seeds a culinary substitution graph (~60 pairs, e.g. koriander↔petersilie 0.75, schmand↔crème fraîche 0.95). Enables SQL queries like "recipes WITH zucchini AND WITHOUT paprika AND <500 kcal" and "what can I substitute for coriander?".
|
||
- **Galera FK constraints are fragile.** Certification-based replication can reject DDL that works on standalone MySQL. Prefer logical relationships (application-level integrity) over physical FKs.
|
||
- **International recipe sources research (updated 2026-06-28).** 15 sources evaluated across 4 tiers. Tier 1 (ready to integrate, ~20k recipes total): BBC Good Food (~5k), SkinnyTaste (~2.3k, light/healthy), Woks of Life (~1.5k, Chinese), RecipeTin Eats (~1.7k, pan-Asian), Rasa Malaysia (~1.4k, SE-Asian), Sanjeev Kapoor (~2k, Indian, non-standard JSON-LD), Budget Bytes (~2k), Pinch of Yum (~1.5k), Omnivore's Cookbook (~772, Chinese), Hot Thai Kitchen (~464, authentic Thai), Spoon Fork Bacon (~911, Asian fusion), Korean Bapsang (~275, Korean), Minimalist Baker (~650, vegan/GF). Tier 2 (AI-bot blocks, use generic UA): Cookie and Kate (~920, vegetarian), Healthy Nibbles (~640, Asian healthy). Tier 3 (not feasible): Just One Cookbook (Cloudflare), Maangchi (403), AllRecipes (empty sitemaps), Archana's Kitchen (no JSON-LD), Bon Appétit/Epicurious (proprietary format), Serious Eats (no JSON-LD confirmed). See `references/international-recipe-sources.md` for full details including sitemap URLs, recipe counts, and ID prefixes.
|