Alternative to Planta: a plant care log with ChatGPT

Set up a plant care log for me in databasedmcp, in one go.

1. Create a database called plant_care, described as "Plant care log: my plants, where they live and when I watered, fed or repotted them".
2. Create two tables:
   - plants: id, name (required, what I call it), species, room, light (low, medium, bright indirect or direct), water_every_days, acquired_on (date), died_on (NULL while it's alive), notes.
   - care: id, plant_id (references plants), done_on (date, defaults to today), action (water, fertilize, repot, prune or treat), notes.
3. Write the database a guide that says what each table is for, that a plant's next watering is its last watering plus water_every_days, and the queries for "what needs watering", "when was each plant last repotted" and "plants by room".

From then on: when I say I watered, fed or repotted something, add a care row for each plant. When I get a plant, add it with a sensible watering interval for its species and ask me to confirm. When I move one, update its room. Show me the tables when you're finished.

CREATE TABLE plants (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  species TEXT,
  room TEXT,
  light TEXT CHECK (light IN ('low', 'medium', 'bright indirect', 'direct')),
  water_every_days INTEGER,
  acquired_on TEXT,
  died_on TEXT,
  notes TEXT
);

CREATE TABLE care (
  id INTEGER PRIMARY KEY,
  plant_id INTEGER NOT NULL REFERENCES plants(id),
  done_on TEXT NOT NULL DEFAULT (date('now')),
  action TEXT NOT NULL CHECK (action IN ('water', 'fertilize', 'repot', 'prune', 'treat')),
  notes TEXT
);

# plant_care: plants and their care

- **plants**: one row per plant. `name` is what I call it. `water_every_days` is how often it wants water; `died_on` stays NULL while it's alive.
- **care**: one row per plant per thing done. action: water, fertilize, repot, prune or treat.

A plant is due when its last watering plus `water_every_days` is today or earlier.

Needs water: `SELECT p.name, p.room, max(c.done_on) AS last_watered, date(max(c.done_on), '+' || p.water_every_days || ' days') AS due FROM plants p LEFT JOIN care c ON c.plant_id = p.id AND c.action = 'water' WHERE p.died_on IS NULL GROUP BY p.id HAVING due IS NULL OR due <= date('now', '+1 day') ORDER BY due`

By room: `SELECT room, group_concat(name, ', ') AS plants FROM plants WHERE died_on IS NULL GROUP BY room`

INSERT INTO plants (name, species, room, light, water_every_days) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?)
-- params: ["Monty","Monstera deliciosa","Living room","bright indirect",7,"Fern","Boston fern","Kitchen","medium",3,"Snake plant","Dracaena trifasciata","Bedroom","low",14]

INSERT INTO care (plant_id, action) VALUES (?, ?), (?, ?)
-- params: [1,"water",2,"water"]

INSERT INTO care (plant_id, done_on, action, notes) VALUES (?, date('now', 'weekday 0', '-7 days'), ?, ?)
-- params: [1,"repot","24 cm pot, fresh chunky mix"]

UPDATE plants SET room = ? WHERE id = ?
-- params: ["Bathroom",2]

SELECT p.name, p.room, max(c.done_on) AS last_watered, date(max(c.done_on), '+' || p.water_every_days || ' days') AS due FROM plants p LEFT JOIN care c ON c.plant_id = p.id AND c.action = 'water' WHERE p.died_on IS NULL GROUP BY p.id HAVING due IS NULL OR due <= date('now', '+1 day') ORDER BY due

SELECT p.name, max(c.done_on) AS last_repotted FROM plants p LEFT JOIN care c ON c.plant_id = p.id AND c.action = 'repot' WHERE p.died_on IS NULL GROUP BY p.id ORDER BY last_repotted

SELECT room, group_concat(name, ', ') AS plants FROM plants WHERE died_on IS NULL GROUP BY room