hermes-skills/food-nutrition/meal-planning/SKILL.md

name, description, version, tags

name	description	version	tags
meal-planning	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.	2.0.0	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

-- 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)

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.