Meal-Planner/docs/database-schema.md

# Meal Planner - Database Schema

## 1. Schema Overview

PostgreSQL 15+ with the following extensions:
- `uuid-ossp` for UUID generation
- `CITEXT` for case-insensitive text (ingredient names)

---

## 2. Enums

```sql
CREATE TYPE family_member_role_enum AS ENUM ('adult', 'child');
CREATE TYPE meal_type_enum AS ENUM ('breakfast', 'lunch', 'dinner');
CREATE TYPE meal_plan_status_enum AS ENUM ('draft', 'pending_approval', 'approved', 'locked');
CREATE TYPE meal_plan_item_status_enum AS ENUM ('pending', 'approved', 'denied', 'swapped');
CREATE TYPE approval_token_status_enum AS ENUM ('active', 'used', 'expired');
CREATE TYPE denial_reason_enum AS ENUM ('too_expensive', 'boring', 'disliked_ingredient', 'cultural', 'other');
CREATE TYPE never_suggest_reason_enum AS ENUM ('allergy', 'dislike', 'tried_too_much', 'other');
CREATE TYPE scrape_status_enum AS ENUM ('started', 'success', 'failed');
CREATE TYPE email_status_enum AS ENUM ('sent', 'delivered', 'failed', 'bounced');
```

---

## 3. Tables

### 3.1 `family_profile`

Primary household configuration.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK, default uuid_generate_v4() | Primary key |
| name | VARCHAR(100) | NOT NULL | Household name |
| household_size | INTEGER | NOT NULL, CHECK (household_size > 0) | Number of people |
| adult_count | INTEGER | NOT NULL | Number of adults |
| child_count | INTEGER | NOT NULL | Number of children |
| dietary_notes | TEXT | | Free-text dietary notes |
| budget_per_meal | NUMERIC(10,2) | DEFAULT 50.00 | Budget target per meal (in dollars) |
| calorie_target | INTEGER | | Daily calorie target per adult (aspirational) |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| updated_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Constraints**:
- `CHECK (adult_count + child_count = household_size)`

### 3.2 `family_member`

Individual family members for per-person voting and preferences.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) ON DELETE CASCADE | |
| name | VARCHAR(100) | NOT NULL | Member name |
| email | VARCHAR(300) | UNIQUE per family | Email for notifications |
| role | family_member_role_enum | NOT NULL | 'adult' or 'child' |
| likes_mushrooms | BOOLEAN | DEFAULT FALSE | Dietary preference |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| updated_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(family_profile_id, email)`

### 3.3 `ingredient`

Master list of all ingredients. `name_lower` is used for case-insensitive matching.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| name | VARCHAR(200) | NOT NULL | Display name (e.g., "Carrots") |
| name_lower | VARCHAR(200) | NOT NULL, UNIQUE | Lowercase for matching (e.g., "carrots") |
| plural_name | VARCHAR(200) | | For shopping list grouping |
| aisle | VARCHAR(100) | | Lucky California aisle |
| typical_price | NUMERIC(10,2) | | Price per unit |
| unit | VARCHAR(50) | | e.g., "lb", "oz", "bunch" |
| season_months | INTEGER[] | | Array of month numbers 1-12 |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

### 3.4 `recipe`

All recipes in the system. Ingredients stored as JSONB for flexibility.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) | Optional, for family-specific recipes |
| name | VARCHAR(300) | NOT NULL | Recipe name |
| description | TEXT | | Short description |
| image_url | TEXT | | URL to recipe image |
| image_source | VARCHAR(50) | | 'scraped', 'ai_generated', 'manual' |
| prep_time_minutes | INTEGER | | Prep time |
| cook_time_minutes | INTEGER | | Cook time |
| servings | INTEGER | NOT NULL | Default servings |
| servings_scaled | INTEGER | | Current scaled servings |
| cuisine_tags | VARCHAR(50)[] | | Array: 'italian', 'asian', etc. |
| dietary_tags | VARCHAR(50)[] | | Array: 'vegetarian', 'gluten_free', etc. |
| protein_type | VARCHAR(50) | | 'chicken', 'beef', 'vegetarian', 'seafood' |
| spice_level | INTEGER | CHECK (spice_level BETWEEN 1 AND 5) | 1=mild, 5=very spicy |
| ingredients | JSONB | NOT NULL | [{ingredient_id, name, quantity, unit, is_optional}] |
| instructions | TEXT[] | NOT NULL | Array of step strings |
| source_url | TEXT | | Original recipe URL if scraped |
| scraped_at | TIMESTAMPTZ | | When originally scraped |
| is_manually_added | BOOLEAN | DEFAULT FALSE | User-created vs scraped |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| updated_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Note**: `total_time_minutes` is computed as `prep_time_minutes + cook_time_minutes` (not stored).

### 3.5 `meal_plan`

A generated weekly meal plan.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) | |
| week_start_date | DATE | NOT NULL | Monday of the week (ISO: 1=Mon, 7=Sun) |
| status | meal_plan_status_enum | NOT NULL, DEFAULT 'draft' | |
| approval_deadline | TIMESTAMPTZ | | When approval period ends |
| total_estimated_cost | NUMERIC(10,2) | | Sum of all meal costs |
| notes | TEXT | | Admin notes |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| updated_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(family_profile_id, week_start_date)`

### 3.6 `meal_plan_item`

Individual meal within a plan. `day_of_week` uses ISO-8601 convention (1=Monday through 7=Sunday).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| meal_plan_id | UUID | FK → meal_plan(id) ON DELETE CASCADE | |
| recipe_id | UUID | FK → recipe(id) | |
| day_of_week | INTEGER | NOT NULL, CHECK (day_of_week BETWEEN 1 AND 7) | 1=Mon, 7=Sun (ISO-8601) |
| meal_type | meal_type_enum | NOT NULL | 'breakfast', 'lunch', 'dinner' |
| approval_status | meal_plan_item_status_enum | DEFAULT 'pending' | |
| denial_reason | denial_reason_enum | | If denied |
| denial_details | TEXT | | Free-text explanation |
| estimated_cost | NUMERIC(10,2) | | Per serving cost |
| used_pantry_items | UUID[] | | Home pantry items used |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| updated_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(meal_plan_id, day_of_week, meal_type)`

### 3.7 `meal_plan_vote`

Per-member votes on meal plan items. This is the key to the redesigned approval flow.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| meal_plan_item_id | UUID | FK → meal_plan_item(id) ON DELETE CASCADE | |
| family_member_id | UUID | FK → family_member(id) ON DELETE CASCADE | |
| vote | BOOLEAN | NOT NULL | TRUE=approve, FALSE=deny |
| voted_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(meal_plan_item_id, family_member_id)` — one vote per member per meal

### 3.8 `approval_token`

Single-use tokens for email approval links.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| meal_plan_item_id | UUID | FK → meal_plan_item(id) ON DELETE CASCADE | |
| family_member_id | UUID | FK → family_member(id) ON DELETE CASCADE | |
| token | VARCHAR(64) | NOT NULL, UNIQUE | Secure random token |
| status | approval_token_status_enum | DEFAULT 'active' | |
| expires_at | TIMESTAMPTZ | NOT NULL | Token expiration (72h from send) |
| used_at | TIMESTAMPTZ | | When token was used |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(meal_plan_item_id, family_member_id)`

### 3.9 `home_pantry`

Items the family has at home.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) ON DELETE CASCADE | |
| ingredient_id | UUID | FK → ingredient(id) ON DELETE SET NULL | |
| quantity | NUMERIC(10,2) | | How much on hand |
| unit | VARCHAR(50) | | e.g., "cans", "lb" |
| expires_at | DATE | | Perishable expiry date |
| added_at | TIMESTAMPTZ | DEFAULT NOW() | |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Unique constraint**: `(family_profile_id, ingredient_id)`

### 3.10 `feedback`

Meal feedback and ratings. One feedback row per meal, can be associated with a specific member.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) | |
| family_member_id | UUID | FK → family_member(id) ON DELETE SET NULL | |
| meal_plan_item_id | UUID | FK → meal_plan_item(id) ON DELETE CASCADE | |
| rating | INTEGER | CHECK (rating BETWEEN 1 AND 5) | 1-5 stars |
| never_suggest | BOOLEAN | DEFAULT FALSE | Add to "never suggest" list |
| denial_reason | denial_reason_enum | | Same as meal_plan_item |
| feedback_text | TEXT | | Free-text feedback |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

**Note**: Multiple feedback rows can exist for the same meal (one per family member).

### 3.11 `never_suggest`

Global "never suggest" flags per family.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| family_profile_id | UUID | FK → family_profile(id) ON DELETE CASCADE | |
| ingredient_id | UUID | FK → ingredient(id) ON DELETE CASCADE | Optionally block specific ingredients |
| recipe_id | UUID | FK → recipe(id) ON DELETE CASCADE | Or block entire recipes |
| reason | never_suggest_reason_enum | | |
| notes | TEXT | | |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

### 3.12 `grocery_item`

Scraped items from Lucky California. Links to `ingredient` table.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| ingredient_id | UUID | FK → ingredient(id) ON DELETE SET NULL | Links to master ingredient list |
| name | VARCHAR(300) | NOT NULL | Product name |
| brand | VARCHAR(200) | | Brand name |
| current_price | NUMERIC(10,2) | | Current sale price |
| regular_price | NUMERIC(10,2) | | Normal price |
| unit | VARCHAR(50) | | Price unit |
| aisle | VARCHAR(100) | | Store aisle |
| image_url | TEXT | | Product image |
| product_url | TEXT | | Lucky California product page |
| is_on_sale | BOOLEAN | DEFAULT FALSE | Currently on sale |
| sale_start_date | DATE | | |
| sale_end_date | DATE | | |
| in_season | BOOLEAN | DEFAULT FALSE | Currently in season |
| scraped_at | TIMESTAMPTZ | DEFAULT NOW() | |
| scraped_url | TEXT | | Source URL |

### 3.13 `scrape_log`

Scraping operation history.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| source | VARCHAR(50) | NOT NULL | 'lucky_california', 'recipe_site' |
| scrape_type | VARCHAR(50) | NOT NULL | 'weekly_ad', 'product_catalog', 'recipe' |
| status | scrape_status_enum | NOT NULL | |
| items_scraped | INTEGER | DEFAULT 0 | |
| error_message | TEXT | | |
| started_at | TIMESTAMPTZ | DEFAULT NOW() | |
| completed_at | TIMESTAMPTZ | | |
| duration_seconds | INTEGER | | |

### 3.14 `email_log`

Email sending history.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | UUID | PK | |
| recipient_email | VARCHAR(300) | NOT NULL | |
| recipient_name | VARCHAR(200) | | |
| template | VARCHAR(100) | NOT NULL | 'meal_proposal', 'reminder', 'confirmation' |
| meal_plan_id | UUID | FK → meal_plan(id) | Related meal plan |
| meal_plan_item_id | UUID | FK → meal_plan_item(id) | Optional: specific meal |
| sendgrid_message_id | VARCHAR(100) | | SendGrid message ID |
| status | email_status_enum | NOT NULL | |
| error_message | TEXT | | |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |
| delivered_at | TIMESTAMPTZ | | |

---

## 4. Indexes

### 4.1 Primary Indexes
All PKs have default B-tree indexes.

### 4.2 Foreign Key Indexes
```sql
CREATE INDEX idx_family_member_profile ON family_member(family_profile_id);
CREATE INDEX idx_recipe_family_profile ON recipe(family_profile_id);
CREATE INDEX idx_meal_plan_family_profile ON meal_plan(family_profile_id);
CREATE INDEX idx_meal_plan_item_meal_plan ON meal_plan_item(meal_plan_id);
CREATE INDEX idx_meal_plan_item_recipe ON meal_plan_item(recipe_id);
CREATE INDEX idx_meal_plan_vote_item ON meal_plan_vote(meal_plan_item_id);
CREATE INDEX idx_meal_plan_vote_member ON meal_plan_vote(family_member_id);
CREATE INDEX idx_approval_token_item ON approval_token(meal_plan_item_id);
CREATE INDEX idx_approval_token_member ON approval_token(family_member_id);
CREATE INDEX idx_home_pantry_family_profile ON home_pantry(family_profile_id);
CREATE INDEX idx_home_pantry_ingredient ON home_pantry(ingredient_id);
CREATE INDEX idx_feedback_family_profile ON feedback(family_profile_id);
CREATE INDEX idx_feedback_member ON feedback(family_member_id);
CREATE INDEX idx_feedback_meal_plan_item ON feedback(meal_plan_item_id);
CREATE INDEX idx_never_suggest_family_profile ON never_suggest(family_profile_id);
CREATE INDEX idx_grocery_item_ingredient ON grocery_item(ingredient_id);
CREATE INDEX idx_grocery_item_is_on_sale ON grocery_item(is_on_sale) WHERE is_on_sale = TRUE;
```

### 4.3 Full-Text Search Indexes
```sql
CREATE INDEX idx_ingredient_name_lower_fts ON ingredient USING gin(to_tsvector('english', name_lower));
CREATE INDEX idx_recipe_name_fts ON recipe USING gin(to_tsvector('english', name));
```

---

## 5. Day of Week Convention

**ISO-8601 standard** (1=Monday through 7=Sunday):
- 1 = Monday
- 2 = Tuesday
- 3 = Wednesday
- 4 = Thursday
- 5 = Friday
- 6 = Saturday
- 7 = Sunday

This applies to `meal_plan_item.day_of_week`.

**Note**: PostgreSQL's `EXTRACT(DOW FROM date)` returns 0=Sunday, 6=Saturday. Convert accordingly.

---

## 6. Data Migration Strategy

### 6.1 Initial Schema
Use Alembic for version-controlled migrations.

### 6.2 Migration Policy
- All schema changes must go through Alembic migrations
- Migrations must be reversible where possible
- No `Base.metadata.create_all()` in application startup code

### 6.3 Seed Data
- Enum types created first
- Basic ingredients pre-populated (50-100 common items)
- Sample recipes for initial testing (5-10 meals)
- Default family profile with 2 adult members

---

## 7. Data Retention

| Data Type | Retention | Action After Expiry |
|-----------|-----------|---------------------|
| Meal plans | 12 weeks | Archive to JSON, delete rows |
| Feedback | Indefinite | Keep for learning |
| Scraped grocery items | 2 weeks | Delete old items |
| Scrape logs | 30 days | Delete old logs |
| Email logs | 90 days | Delete old logs |
| Never-suggest | Indefinite | Keep |
| Used approval tokens | 7 days | Delete after vote processed |
| Expired approval tokens | 30 days | Delete |

---

## 8. Row-Level Security (Future)

If multi-family support is added:
- RLS on all tables
- Policies based on `family_profile_id`
- Backend enforces tenant isolation
- Current implementation: single-family, no RLS needed