Database Schema

MealPal uses PostgreSQL with pgvector on AWS RDS (ca-central-1). Two databases on the same instance:

• mealpal — meals, chat, RAG embeddings, metrics
• auth — Better-Auth tables (users, sessions, JWKS, 2FA)

Design Principles

1. User-Centric — all data anchored to users with proper foreign keys
2. Immutable History — meal logs never deleted, only soft-deleted
3. Flexible Metadata — JSONB for extensible data (nutrition, AI responses)
4. Performance First — strategic indexes on all query patterns
5. Privacy Ready — easy to export/delete user data (GDPR compliant)

Entity Relationship Diagram

erDiagram
    users ||--o{ meal_logs : "logs"
    users ||--o{ user_goals : "has"
    users ||--o{ user_preferences : "has"
    users ||--o{ conversations : "chats"
    users ||--o{ api_usage : "tracks"
    meal_logs ||--o{ meal_foods : "contains"
    conversations ||--o{ messages : "contains"

Core Tables

users

Column	Type	Notes
id	UUID PK
auth_id	string UK	From Better-Auth
email	string UK
tier	string	free or premium
created_at	timestamp

meal_logs

Column	Type	Notes
id	UUID PK
user_id	UUID FK
meal_time	timestamp	When they actually ate
meal_type	string	breakfast/lunch/dinner/snack
nutrition_totals	JSONB	calories, macros, micros
ai_analysis	JSONB	Full AI response
image_url	string	S3 URL
is_deleted	boolean	Soft delete

conversations & messages

Column	Type	Notes
conversations.id	UUID PK
conversations.title	string	Auto-generated from first message
messages.role	string	user or assistant
messages.content	text
messages.message_metadata	JSONB	Citations, referenced meals

api_usage

Tracks AI provider costs per request (provider, model, tokens, cost_usd).

Key Indexes

-- Most queried: meals by user and time
CREATE INDEX idx_meal_logs_user_time ON meal_logs(user_id, meal_time DESC);
CREATE INDEX idx_meal_logs_deleted ON meal_logs(user_id, is_deleted) WHERE is_deleted = false;

-- API cost tracking
CREATE INDEX idx_api_usage_user_date ON api_usage(user_id, created_at DESC);

Design Decisions

• JSONB for nutrition — flexible schema, fast querying with GIN indexes, no 50+ columns
• Soft delete — preserves history for analytics, supports undo, GDPR-compliant purge
• user_id everywhere — easy data export, simple row-level security