How to build a recipe box and meal planner with Claude

Set up a recipe box and meal planner for me in databasedmcp, in one go.

1. Create a database called recipes, described as "Recipe box and meal planner: recipes, ingredients, a weekly plan, and a pantry, so the shopping list skips what I already have".
2. Create four tables:
   - recipes: id, name (required), serves, minutes, tags (a JSON array like ["vegetarian", "quick"], defaults to []), steps (the method as numbered lines), source.
   - ingredients: id, recipe_id (references recipes), item (required, lowercase and singular, like "onion"), qty (a number, decimals allowed), unit (g, ml, tbsp, tsp or pcs).
   - meal_plan: id, day (date, required), meal (breakfast, lunch or dinner), recipe_id (references recipes). One recipe per meal per day: make day and meal unique together.
   - pantry: item (the key, spelled the same way as in ingredients).
3. Write the database a guide that says what each table is for, how to write items and units so the shopping list adds up, to remove a pantry item when I say I've run out, and the query for "what do I need to buy for next week": the week's planned ingredients added up by item and unit, leaving out what's in the pantry.

From then on: when I paste a recipe, save it with its ingredients and steps. When I plan meals, add them to meal_plan on the right dates. When I say I have something, add it to the pantry. Show me the tables when you're finished.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  serves INTEGER,
  minutes INTEGER,
  tags TEXT NOT NULL DEFAULT '[]',
  steps TEXT,
  source TEXT
);

CREATE TABLE ingredients (
  id INTEGER PRIMARY KEY,
  recipe_id INTEGER NOT NULL REFERENCES recipes(id),
  item TEXT NOT NULL,
  qty REAL,
  unit TEXT
);

CREATE TABLE meal_plan (
  id INTEGER PRIMARY KEY,
  day TEXT NOT NULL,
  meal TEXT NOT NULL CHECK (meal IN ('breakfast', 'lunch', 'dinner')),
  recipe_id INTEGER NOT NULL REFERENCES recipes(id),
  UNIQUE (day, meal)
);

CREATE TABLE pantry (
  item TEXT PRIMARY KEY
);

# recipes: recipes, ingredients, plan, pantry

- **recipes**: one per dish. `tags` is a JSON array (["vegetarian", "quick"]). `steps` is the method as numbered lines.
- **ingredients**: one row per ingredient per recipe. `item` is lowercase and singular ("onion", "chickpeas") so the shopping list adds up. `unit` uses g, ml, tbsp, tsp or pcs.
- **meal_plan**: one recipe per day per meal (breakfast, lunch, dinner).
- **pantry**: items I already have. Remove one when I say I've run out.

Shopping list for next week: `SELECT i.item, sum(i.qty) AS qty, i.unit FROM meal_plan m JOIN ingredients i ON i.recipe_id = m.recipe_id WHERE m.day BETWEEN date('now', 'weekday 1') AND date('now', 'weekday 1', '+6 days') AND i.item NOT IN (SELECT item FROM pantry) GROUP BY i.item, i.unit ORDER BY i.item`

INSERT INTO recipes (name, serves, minutes, tags, steps) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?)
-- params: ["Chickpea curry",4,35,"[\"vegetarian\", \"batch\"]","1. Soften the onion in oil.\n2. Add spices, then tomatoes and chickpeas.\n3. Simmer 20 minutes and stir in the spinach.","Lemon pasta",2,20,"[\"vegetarian\", \"quick\"]","1. Boil the pasta.\n2. Toss with butter, lemon and parmesan."]

INSERT INTO ingredients (recipe_id, item, qty, unit) VALUES (1, ?, ?, ?), (1, ?, ?, ?), (1, ?, ?, ?), (1, ?, ?, ?), (2, ?, ?, ?), (2, ?, ?, ?), (2, ?, ?, ?)
-- params: ["onion",1,"pcs","chickpeas",800,"g","chopped tomatoes",400,"g","spinach",200,"g","spaghetti",200,"g","lemon",1,"pcs","parmesan",50,"g"]

INSERT INTO meal_plan (day, meal, recipe_id) VALUES (date('now', 'weekday 1'), 'dinner', 1), (date('now', 'weekday 1', '+1 day'), 'dinner', 2), (date('now', 'weekday 1', '+3 days'), 'dinner', 1)

INSERT OR IGNORE INTO pantry (item) VALUES (?), (?)
-- params: ["onion","spaghetti"]

SELECT i.item, sum(i.qty) AS qty, i.unit FROM meal_plan m JOIN ingredients i ON i.recipe_id = m.recipe_id WHERE m.day BETWEEN date('now', 'weekday 1') AND date('now', 'weekday 1', '+6 days') AND i.item NOT IN (SELECT item FROM pantry) GROUP BY i.item, i.unit ORDER BY i.item

SELECT r.name, r.minutes FROM recipes r, json_each(r.tags) t WHERE t.value = ? AND r.minutes <= ? ORDER BY r.minutes
-- params: ["vegetarian",30]

SELECT r.name, max(m.day) AS last_planned FROM recipes r LEFT JOIN meal_plan m ON m.recipe_id = r.id GROUP BY r.id ORDER BY last_planned