Database Schema

Complete database schema and relationships for My Progress Planner.

🏗️ Architecture Overview

The application uses Supabase (PostgreSQL) with Row Level Security (RLS) for complete user data isolation.

Key Features

Row Level Security (RLS) - Users can only access their own data
UUID Primary Keys - Globally unique identifiers
Automatic Timestamps - Created/updated tracking
Foreign Key Constraints - Data integrity enforcement
Indexed Queries - Optimized for common operations

📊 Entity Relationship Diagram

erDiagram
    users {
        uuid id PK
        text email UK
        text name
        text friend_code UK
        timestamp created_at
        timestamp updated_at
    }
    
    meals {
        uuid id PK
        uuid user_id FK
        meal_type_enum meal_type
        text content
        text ai_response
        timestamp logged_at
        timestamp created_at
    }
    
    summaries {
        uuid id PK
        uuid user_id FK
        date summary_date
        text content
        integer meals_count
        timestamp created_at
    }
    
    friends {
        uuid id PK
        uuid user_id FK
        text friend_code
        text friend_name
        timestamp created_at
    }
    
    push_subscriptions {
        uuid id PK
        uuid user_id FK
        jsonb subscription_data
        timestamp created_at
    }
    
    users ||--o{ meals : "has"
    users ||--o{ summaries : "has"
    users ||--o{ friends : "has"
    users ||--o{ push_subscriptions : "has"

📋 Table Definitions

👤 users

Stores user profiles and authentication data.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  friend_code TEXT UNIQUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_friend_code ON users(friend_code);

-- Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own profile" ON users
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON users
  FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Users can insert own profile" ON users
  FOR INSERT WITH CHECK (auth.uid() = id);

Columns:

id - Primary key (UUID)
email - User's email address (unique)
name - Display name
friend_code - Unique code for friend connections
created_at - Account creation timestamp
updated_at - Last profile update

🍽️ meals

Stores individual meal entries and AI responses.

-- Custom enum for meal types
CREATE TYPE meal_type_enum AS ENUM ('breakfast', 'lunch', 'dinner');

CREATE TABLE meals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  meal_type meal_type_enum NOT NULL,
  content TEXT NOT NULL,
  ai_response TEXT,
  logged_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(user_id, meal_type, DATE(logged_at))
);

-- Indexes for performance
CREATE INDEX idx_meals_user_id ON meals(user_id);
CREATE INDEX idx_meals_logged_at ON meals(logged_at);
CREATE INDEX idx_meals_user_date ON meals(user_id, DATE(logged_at));
CREATE INDEX idx_meals_user_type_date ON meals(user_id, meal_type, DATE(logged_at));

-- Row Level Security
ALTER TABLE meals ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own meals" ON meals
  FOR ALL USING (auth.uid() = user_id);

Columns:

id - Primary key (UUID)
user_id - Foreign key to users table
meal_type - breakfast, lunch, or dinner
content - User's meal description
ai_response - GPT-generated response
logged_at - When the meal was consumed
created_at - When the entry was created

Constraints:

One meal per type per day (unique constraint)
Cascading delete when user is deleted

📋 summaries

Stores daily AI-generated summaries of user's meal progress.

CREATE TABLE summaries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  summary_date DATE NOT NULL,
  content TEXT NOT NULL,
  meals_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(user_id, summary_date)
);

-- Indexes
CREATE INDEX idx_summaries_user_id ON summaries(user_id);
CREATE INDEX idx_summaries_date ON summaries(summary_date);
CREATE INDEX idx_summaries_user_date ON summaries(user_id, summary_date);

-- Row Level Security
ALTER TABLE summaries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own summaries" ON summaries
  FOR ALL USING (auth.uid() = user_id);

Columns:

id - Primary key (UUID)
user_id - Foreign key to users table
summary_date - Date of the summary
content - AI-generated summary text
meals_count - Number of meals logged that day
created_at - When summary was generated

👥 friends

Stores friend connections between users.

CREATE TABLE friends (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  friend_code TEXT NOT NULL,
  friend_name TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(user_id, friend_code)
);

-- Indexes
CREATE INDEX idx_friends_user_id ON friends(user_id);
CREATE INDEX idx_friends_code ON friends(friend_code);

-- Row Level Security
ALTER TABLE friends ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own friends" ON friends
  FOR ALL USING (auth.uid() = user_id);

Columns:

id - Primary key (UUID)
user_id - The user who added the friend
friend_code - The friend's unique code
friend_name - Cached friend's display name
created_at - When friendship was established

🔔 push_subscriptions

Stores web push notification subscriptions.

CREATE TABLE push_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  subscription_data JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(user_id, subscription_data)
);

-- Indexes
CREATE INDEX idx_push_subscriptions_user_id ON push_subscriptions(user_id);
CREATE INDEX idx_push_subscriptions_data ON push_subscriptions USING GIN(subscription_data);

-- Row Level Security
ALTER TABLE push_subscriptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own subscriptions" ON push_subscriptions
  FOR ALL USING (auth.uid() = user_id);

Columns:

id - Primary key (UUID)
user_id - Foreign key to users table
subscription_data - Web Push subscription object (JSON)
created_at - When subscription was created

🔍 Common Queries

Get Today's Meals for User

SELECT 
  meal_type,
  content,
  ai_response,
  logged_at
FROM meals 
WHERE user_id = $1 
  AND DATE(logged_at) = CURRENT_DATE
ORDER BY 
  CASE meal_type 
    WHEN 'breakfast' THEN 1 
    WHEN 'lunch' THEN 2 
    WHEN 'dinner' THEN 3 
  END;

Calculate Current Streak

WITH RECURSIVE streak_calc AS (
  -- Start with today
  SELECT 
    CURRENT_DATE as check_date,
    CASE 
      WHEN EXISTS (
        SELECT 1 FROM meals 
        WHERE user_id = $1 
          AND DATE(logged_at) = CURRENT_DATE
      ) THEN 1 
      ELSE 0 
    END as has_meals,
    0 as days_back

  UNION ALL

  -- Go back day by day
  SELECT 
    check_date - INTERVAL '1 day',
    CASE 
      WHEN EXISTS (
        SELECT 1 FROM meals 
        WHERE user_id = $1 
          AND DATE(logged_at) = check_date - INTERVAL '1 day'
      ) THEN 1 
      ELSE 0 
    END,
    days_back + 1
  FROM streak_calc
  WHERE has_meals = 1 
    AND days_back < 365 -- Prevent infinite recursion
)
SELECT COUNT(*) as current_streak
FROM streak_calc
WHERE has_meals = 1;

Get Recent Summaries

SELECT 
  summary_date,
  content,
  meals_count,
  created_at
FROM summaries 
WHERE user_id = $1 
ORDER BY summary_date DESC 
LIMIT 10;

Find Friend by Code

SELECT 
  name,
  friend_code,
  created_at
FROM users 
WHERE friend_code = $1 
  AND id != $2; -- Exclude self

🔧 Database Functions

Generate Unique Friend Code

CREATE OR REPLACE FUNCTION generate_friend_code()
RETURNS TEXT AS $
DECLARE
  chars TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  result TEXT := '';
  i INTEGER := 0;
  code_exists BOOLEAN := TRUE;
BEGIN
  WHILE code_exists LOOP
    result := '';
    FOR i IN 1..6 LOOP
      result := result || substr(chars, floor(random() * length(chars) + 1)::integer, 1);
    END LOOP;
    
    SELECT EXISTS(SELECT 1 FROM users WHERE friend_code = result) INTO code_exists;
  END LOOP;
  
  RETURN result;
END;
$ LANGUAGE plpgsql;

Update User Timestamp Trigger

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_updated_at 
  BEFORE UPDATE ON users 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Auto-generate Friend Code Trigger

CREATE OR REPLACE FUNCTION auto_generate_friend_code()
RETURNS TRIGGER AS $
BEGIN
  IF NEW.friend_code IS NULL THEN
    NEW.friend_code := generate_friend_code();
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER auto_friend_code_trigger
  BEFORE INSERT ON users
  FOR EACH ROW EXECUTE FUNCTION auto_generate_friend_code();

📈 Performance Optimizations

Materialized Views for Analytics

-- Daily meal statistics
CREATE MATERIALIZED VIEW daily_meal_stats AS
SELECT 
  DATE(logged_at) as meal_date,
  COUNT(*) as total_meals,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) FILTER (WHERE meal_type = 'breakfast') as breakfast_count,
  COUNT(*) FILTER (WHERE meal_type = 'lunch') as lunch_count,
  COUNT(*) FILTER (WHERE meal_type = 'dinner') as dinner_count
FROM meals 
WHERE logged_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(logged_at)
ORDER BY meal_date DESC;

-- Refresh daily at midnight
CREATE UNIQUE INDEX ON daily_meal_stats (meal_date);

Partitioning for Large Tables

-- Partition meals table by month for better performance
CREATE TABLE meals_partitioned (
  LIKE meals INCLUDING ALL
) PARTITION BY RANGE (logged_at);

-- Create monthly partitions
CREATE TABLE meals_2025_01 PARTITION OF meals_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE meals_2025_02 PARTITION OF meals_partitioned
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Continue for each month...

🔒 Security Policies

Additional RLS Policies

Admin Access Policy

-- Allow admin users to view all data (for support)
CREATE POLICY "Admin can view all" ON meals
  FOR SELECT TO admin_role
  USING (true);

-- Create admin role
CREATE ROLE admin_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO admin_role;

Rate Limiting with Database

-- Track API usage per user
CREATE TABLE api_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  endpoint TEXT NOT NULL,
  request_count INTEGER DEFAULT 1,
  window_start TIMESTAMP WITH TIME ZONE DEFAULT now(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Function to check rate limits
CREATE OR REPLACE FUNCTION check_rate_limit(
  p_user_id UUID, 
  p_endpoint TEXT, 
  p_max_requests INTEGER DEFAULT 100,
  p_window_minutes INTEGER DEFAULT 60
) RETURNS BOOLEAN AS $
DECLARE
  current_count INTEGER;
BEGIN
  SELECT COALESCE(SUM(request_count), 0) 
  INTO current_count
  FROM api_usage 
  WHERE user_id = p_user_id 
    AND endpoint = p_endpoint 
    AND window_start > now() - (p_window_minutes || ' minutes')::INTERVAL;
  
  RETURN current_count < p_max_requests;
END;
$ LANGUAGE plpgsql;

🧪 Test Data Setup

Sample Data for Development

-- Insert test user
INSERT INTO users (id, email, name, friend_code) VALUES 
('123e4567-e89b-12d3-a456-426614174000', '[email protected]', 'Test User', 'TEST01');

-- Insert sample meals
INSERT INTO meals (user_id, meal_type, content, ai_response, logged_at) VALUES 
('123e4567-e89b-12d3-a456-426614174000', 'breakfast', 'Oatmeal with berries', 'What a nutritious start! 💕', now()),
('123e4567-e89b-12d3-a456-426614174000', 'lunch', 'Grilled chicken salad', 'Perfect protein choice! 🌟', now()),
('123e4567-e89b-12d3-a456-426614174000', 'dinner', 'Salmon with vegetables', 'Ending the day strong! 💪', now());

-- Insert sample summary
INSERT INTO summaries (user_id, summary_date, content, meals_count) VALUES 
('123e4567-e89b-12d3-a456-426614174000', CURRENT_DATE, 'Amazing day with all meals logged!', 3);

Data Cleanup Functions

-- Clean old test data
CREATE OR REPLACE FUNCTION cleanup_test_data() RETURNS VOID AS $
BEGIN
  DELETE FROM meals WHERE user_id IN (
    SELECT id FROM users WHERE email LIKE '%@test.%' OR email LIKE '%@example.%'
  );
  DELETE FROM summaries WHERE user_id IN (
    SELECT id FROM users WHERE email LIKE '%@test.%' OR email LIKE '%@example.%'  
  );
  DELETE FROM users WHERE email LIKE '%@test.%' OR email LIKE '%@example.%';
END;
$ LANGUAGE plpgsql;

🔄 Migration Scripts

Database Setup Script

-- Complete setup script for new installations
DO $
BEGIN
  -- Create enum type if not exists
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'meal_type_enum') THEN
    CREATE TYPE meal_type_enum AS ENUM ('breakfast', 'lunch', 'dinner');
  END IF;
  
  -- Enable required extensions
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
  
  -- Set up Row Level Security
  ALTER DATABASE current_database() SET row_security = on;
END $;

Version Migration Example

-- Migration from v1.0 to v1.1
-- Add friend_name column to friends table
ALTER TABLE friends ADD COLUMN IF NOT EXISTS friend_name TEXT;

-- Update existing friend records
UPDATE friends SET friend_name = (
  SELECT name FROM users WHERE friend_code = friends.friend_code
) WHERE friend_name IS NULL;

📊 Monitoring Queries

Database Health Check

-- Check table sizes
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats 
WHERE schemaname = 'public' 
ORDER BY tablename, attname;

-- Check index usage
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stat_user_indexes 
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Performance Monitoring

-- Slow query detection
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
WHERE query LIKE '%meals%' 
ORDER BY total_time DESC 
LIMIT 10;

📞 Support

Schema Issues: Open an issue
Migration Help: GitHub Discussions
Database Questions: [email protected]

Database Schema

Database Schema

🏗️ Architecture Overview

Key Features

📊 Entity Relationship Diagram

📋 Table Definitions

👤 users

🍽️ meals

📋 summaries

👥 friends

🔔 push_subscriptions

🔍 Common Queries

Get Today's Meals for User

Calculate Current Streak

Get Recent Summaries

Find Friend by Code

🔧 Database Functions

Generate Unique Friend Code

Update User Timestamp Trigger

Auto-generate Friend Code Trigger

📈 Performance Optimizations

Materialized Views for Analytics

Partitioning for Large Tables

🔒 Security Policies

Additional RLS Policies

Admin Access Policy

Rate Limiting with Database

🧪 Test Data Setup

Sample Data for Development

Data Cleanup Functions

🔄 Migration Scripts

Database Setup Script

Version Migration Example

📊 Monitoring Queries

Database Health Check

Performance Monitoring

📞 Support

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally