Alternative to AnyList: a shared shopping list with Claude

Set up a shared shopping list for me in databasedmcp, in one go.

1. Create a database called shopping, described as "Our household shopping lists: everyone adds items through their own assistant, and we tick them off in the shop".
2. Create two tables:
   - lists: id, name (required, unique, such as Groceries or Hardware).
   - items: id, list_id (references lists), name (required), quantity (free text, such as "2 cartons"), category (the part of the shop: Produce, Dairy, Bakery and so on), added_by (the first name of whoever asked), added_at (defaults to now), checked_at (NULL until it's in the basket).
3. Write the database a guide that says what each table is for, that the database is shared by everyone in the household, to always fill in added_by, to check for an unchecked item with the same name before adding one and change its quantity instead, and the queries for "what's left on the list, by category" and "what do we buy most often".

From then on: when I say we need something, add it to the right list with a category. When I say I've got things, set checked_at on them. When I ask what's left, show the unchecked items grouped by category. Show me the tables when you're finished.

CREATE TABLE lists (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  list_id INTEGER NOT NULL REFERENCES lists(id),
  name TEXT NOT NULL,
  quantity TEXT,
  category TEXT,
  added_by TEXT NOT NULL,
  added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checked_at TEXT
);

# shopping: our household lists

Shared by everyone in the household. Each person talks to their own assistant.

- **lists**: Groceries, Hardware and so on.
- **items**: one row per thing to buy. Always set `added_by` to the first name of whoever asked. `category` is the part of the shop (Produce, Dairy, Bakery, Pantry, Household). `checked_at` stays NULL until it's in the basket.

Before adding, check for an unchecked item with the same name on that list; if there is one, update its quantity instead.

What's left: `SELECT category, name, quantity, added_by FROM items WHERE list_id = 1 AND checked_at IS NULL ORDER BY category, name`

Bought most often: `SELECT name, count(*) AS times FROM items WHERE checked_at IS NOT NULL GROUP BY lower(name) ORDER BY times DESC LIMIT 20`

INSERT INTO lists (name) VALUES (?) RETURNING id
-- params: ["Groceries"]

INSERT INTO items (list_id, name, quantity, category, added_by) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?)
-- params: [1,"Oat milk","2 cartons","Dairy","Ana",1,"Bananas","6","Produce","Ana",1,"Coffee beans","1 bag","Pantry","Ana"]

INSERT INTO items (list_id, name, quantity, category, added_by) VALUES (?, ?, ?, ?, ?)
-- params: [1,"Rye bread","1 loaf","Bakery","Tomas"]

UPDATE items SET quantity = ? WHERE list_id = ? AND checked_at IS NULL AND lower(name) = lower(?)
-- params: ["3 cartons",1,"Oat milk"]

UPDATE items SET checked_at = datetime('now') WHERE list_id = ? AND checked_at IS NULL AND name IN (?, ?)
-- params: [1,"Oat milk","Bananas"]

SELECT category, name, quantity, added_by FROM items WHERE list_id = ? AND checked_at IS NULL ORDER BY category, name
-- params: [1]

SELECT name, count(*) AS times, max(checked_at) AS last_bought FROM items WHERE checked_at IS NOT NULL GROUP BY lower(name) ORDER BY times DESC LIMIT 20

SELECT added_by, count(*) AS items, group_concat(name, ', ') AS what FROM items WHERE added_at >= date('now', '-7 days') GROUP BY added_by ORDER BY items DESC