# AI Garden Planner — Schema Plan

**Status:** Draft, awaiting rian's review.
**Date:** 2026-04-22
**Context:** Conversation with Claude about fleshing out species/variety info.
The goal is *not* an encyclopedia — Wikipedia and AI already do that. Goal is
to capture what's unique to *this* garden so AI can plan well on the phone.

## Principles we landed on

1. **App = authoritative record of your garden.** Structured fields only for things
   you filter/sort/plan against, or that are unique to your experience (ratings,
   flavor notes, local observations). Everything else, AI can fetch in real time.
2. **Species-level defaults, variety-level overrides.** Variety fields are NULL
   when they should inherit from the parent species. Only populate override fields
   when the variety genuinely differs (e.g. dwarf rootstock).
3. **Ratings live on plant-instances, not varieties.** The same Brandywine in Row 2
   last year vs Row 4 this year performs differently — AI aggregates across
   instances to form a "variety impression" when asked.
4. **Capacity is a computed view, not stored state.** Sum
   `(variety.spacing ?? species.spacing) × quantity` per area → utilization %.
5. **Tagging and categorization are deferred.** We pinned that whole conversation.
   The rotation_family field is the one piece of "grouping" we're keeping because
   it's the minimum needed for rotation advice.

## The core user flow we're designing for

Open garden app on phone → tap an area (e.g. Fenced Garden) → "Plan with AI" →
server builds a prompt that includes area dimensions, sun, occupancy, current/planned
plants, last 2-3 years of planting history per section, rian's local notes, and
rian's rating history → AI responds with "your cauliflower plan overruns by X sqft,
and you had broccoli in R2S3 last year — try moving to R4S1 which had potatoes."

For that to work without the AI making 15 tool calls, all those facts need to be
queryable in one pass. That's what this schema is for.

## Proposed schema changes

### species — 6 new columns

| Column | Type | Purpose |
|---|---|---|
| `mature_spacing_sqft` | REAL (nullable) | Footprint one mature plant takes. Enables area capacity calc. NULL = not set yet. |
| `rotation_family` | TEXT DEFAULT '' | Closed list: brassica / solanaceae / legume / allium / cucurbit / umbellifer / chenopod / grass / perennial / other. Used for rotation advice. |
| `sun_pref` | TEXT DEFAULT '' | Closed list: full / part-sun / part-shade / shade. Matches against `areas.sunlight`. |
| `life_cycle` | TEXT DEFAULT '' | annual / biennial / perennial. Tells AI if rotation applies. |
| `edible_part` | TEXT DEFAULT '' | fruit / leaf / root / stem / flower / tuber / none. Helps harvest planning + filtering. |
| `notes_local` | TEXT DEFAULT '' | Rian's personal notes: preferences, "we eat a lot of X", "don't bother with Y", regional tips. The thing that makes AI planning *yours*. |

### varieties — 3 new columns

| Column | Type | Purpose |
|---|---|---|
| `mature_spacing_sqft` | REAL (nullable) | NULL = inherit from species. Only set when variety genuinely differs (dwarf rootstock, determinate tomato, etc.). |
| `harvest_window` | TEXT DEFAULT '' | early / mid / late. Enables "extend the cherry season" planning. |
| `flavor_notes` | TEXT DEFAULT '' | Free-form personal notes: "the one Jen liked", "too tart raw but great cooked". |

### plants — 4 new columns

| Column | Type | Purpose |
|---|---|---|
| `flavor_rating` | INTEGER (nullable) | 1–5 stars. Instance-level so AI can compare across years/locations. |
| `vigor_rating` | INTEGER (nullable) | 1–5. How well it grew/yielded in that spot that year. |
| `would_plant_again` | INTEGER (nullable) | -1 no / 0 maybe / 1 yes. The one-tap decision field. |
| `outcome_summary` | TEXT DEFAULT '' | Optional one-liner at season end. "Bolted in June", "best tomato of the year". |

### No new tables
No tags table. No species_tags. No rotation_rules. All deferred. If we later want
tagging, the `rotation_family` field is a precedent — one small closed-list text
column per axis, added only when a planning feature needs it.

## What we're explicitly NOT adding

- Botanical family / taxonomy — AI knows Rosaceae.
- Soil pH, watering frequency, fertilizer schedules — too generic, varies too much.
- Pest lists — only log actual pest observations via comments.
- Companion planting matrix — contested, AI-derivable.
- Bloom color, height range for annuals — generic knowledge.
- Chill hours, hardiness zone — generic, zone is constant for this property.
- AI-fetched reference briefs — defer until after the above fields prove useful.
  If we add later, one field: `species.ai_brief TEXT` + `species.ai_brief_fetched_at`.

## Open questions for rian

1. **`would_plant_again` encoding** — is -1/0/1 fine, or would you rather just
   nullable 0/1 (and use NULL for "haven't decided")? Either works.
2. **`harvest_window` granularity** — is early/mid/late enough, or do you want
   to store an approximate month (e.g. `harvest_month_start INTEGER`)? Month is
   more specific but requires more guessing per-variety.
3. **Rotation family for trees/shrubs** — should permanent plantings get
   `rotation_family = 'perennial'` (opts them out of rotation checks) or just
   leave it blank? Blank probably fine; AI can check `life_cycle` too.
4. **`notes_local` vs existing `description`** — we could reuse `description`
   for this instead of adding a new column. Tradeoff: `description` is currently
   thought of as factual, `notes_local` would be personal/opinionated. Separate
   is cleaner but you already have one description column you could repurpose.

## Draft migration code

To go into `init_db()` in `/srv/apps/garden/app/main.py`, after the existing
`comments.kind` migration block (~line 300). **Idempotent** — safe to run
multiple times. Nothing deletes or rewrites existing data; pure additive.

```python
# Species: structured planning/filter fields (2026-04 planner schema)
species_cols = {r[1] for r in con.execute("PRAGMA table_info(species)")}
if "mature_spacing_sqft" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN mature_spacing_sqft REAL")
if "rotation_family" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN rotation_family TEXT DEFAULT ''")
if "sun_pref" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN sun_pref TEXT DEFAULT ''")
if "life_cycle" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN life_cycle TEXT DEFAULT ''")
if "edible_part" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN edible_part TEXT DEFAULT ''")
if "notes_local" not in species_cols:
    con.execute("ALTER TABLE species ADD COLUMN notes_local TEXT DEFAULT ''")

# Variety: overrides (NULL = inherit species), plus personal flavor notes
variety_cols = {r[1] for r in con.execute("PRAGMA table_info(varieties)")}
if "mature_spacing_sqft" not in variety_cols:
    con.execute("ALTER TABLE varieties ADD COLUMN mature_spacing_sqft REAL")
if "harvest_window" not in variety_cols:
    con.execute("ALTER TABLE varieties ADD COLUMN harvest_window TEXT DEFAULT ''")
if "flavor_notes" not in variety_cols:
    con.execute("ALTER TABLE varieties ADD COLUMN flavor_notes TEXT DEFAULT ''")

# Plants: outcome ratings (all nullable — "not rated yet" is meaningful)
plant_cols = {r[1] for r in con.execute("PRAGMA table_info(plants)")}
if "flavor_rating" not in plant_cols:
    con.execute("ALTER TABLE plants ADD COLUMN flavor_rating INTEGER")
if "vigor_rating" not in plant_cols:
    con.execute("ALTER TABLE plants ADD COLUMN vigor_rating INTEGER")
if "would_plant_again" not in plant_cols:
    con.execute("ALTER TABLE plants ADD COLUMN would_plant_again INTEGER")
if "outcome_summary" not in plant_cols:
    con.execute("ALTER TABLE plants ADD COLUMN outcome_summary TEXT DEFAULT ''")
```

Also archived as a standalone snippet at
`/srv/apps/garden/app/migrations_ai_planner_draft.py` so you can diff before merging.

## After schema, the next layers (not doing yet)

1. **Occupancy SQL view** — `area_occupancy` that sums
   `coalesce(v.mature_spacing_sqft, s.mature_spacing_sqft, 0) * plants.quantity`
   per `area_id`, with an optional recursive rollup to parent areas. Exposes
   `occupied_sqft`, `remaining_sqft`, `utilization_pct`, with `planted_only` and
   `incl_planned` variants.
2. **Area page capacity bar** — simple header widget showing both percentages.
3. **Species enrichment flow** — for each species, a "suggest fields" button
   that calls Claude API with species name → returns proposed values for the
   6 structured fields → user confirms/edits. Start by running it on the ~25
   species relevant to the fenced garden + orchard row.
4. **"Plan with AI" on area** — builds a prompt from: area + subareas +
   current/planned plants + 2–3 yr history + species/variety fields + rian's
   notes_local + rating history. Opens a conversation.
5. **Defer longer:** fertilization/pruning schedules as a feature. Your comments
   table already logs these. Build a "plants overdue for X based on comment
   history" view later, instead of rigid stored schedules.

## How to resume

- This doc is the context-restore point. Sharing it with a fresh Claude gives
  you the whole picture.
- If approved as-is, next action is: apply the migration snippet to main.py,
  restart the garden container, verify schema with `sqlite3 data/garden.db .schema`.
- If any of the open questions change the answer, edit the draft snippet first.