Database Schema & Migrations

This document covers the database structure, schema design, and migration process for SparkyFitness.

Database Overview

SparkyFitness uses PostgreSQL 15+ with Row Level Security (RLS) to ensure data isolation between users. The database follows a normalized design with clear relationships between entities.

Schema Design Principles

Naming Conventions

  • Tables: Snake case, plural (e.g., food_diary_entries, user_preferences)
  • Columns: Snake case (e.g., created_at, user_id, total_calories)
  • Foreign Keys: {table_name}_id format (e.g., user_id, food_item_id)
  • Indexes: Descriptive names (e.g., idx_food_diary_user_date, idx_measurements_user_type)

Standard Fields

All tables include these audit fields:

  • id - UUID primary key using gen_random_uuid()
  • created_at - Timestamp with timezone, defaults to NOW()
  • updated_at - Timestamp with timezone, updated via triggers

Row Level Security (RLS)

Every user-specific table has RLS policies to ensure users only access their own data:

-- Example RLS policy
ALTER TABLE food_diary ENABLE ROW LEVEL SECURITY;
CREATE POLICY food_diary_user_policy ON food_diary
  FOR ALL USING (user_id = auth.uid());

Core Tables

User Management

users

Central user information and authentication.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

user_preferences

User-specific application preferences and settings.

CREATE TABLE user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  theme VARCHAR(20) DEFAULT 'system',
  ai_provider VARCHAR(50) DEFAULT 'openai',
  units_system VARCHAR(10) DEFAULT 'metric',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Food Tracking

food_items

Master catalog of food items from various sources.

CREATE TABLE food_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(255),
  barcode VARCHAR(50),
  source VARCHAR(50), -- 'user', 'nutritionix', 'openfoodfacts', etc.
  source_id VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

nutrition_data

Nutritional information for food items.

CREATE TABLE nutrition_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  food_item_id UUID NOT NULL REFERENCES food_items(id) ON DELETE CASCADE,
  serving_size DECIMAL(10,2),
  serving_unit VARCHAR(50),
  calories DECIMAL(10,2),
  protein DECIMAL(10,2),
  carbohydrates DECIMAL(10,2),
  fat DECIMAL(10,2),
  fiber DECIMAL(10,2),
  sugar DECIMAL(10,2),
  sodium DECIMAL(10,2),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

food_diary

User's daily food intake entries.

CREATE TABLE food_diary (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  food_item_id UUID NOT NULL REFERENCES food_items(id),
  quantity DECIMAL(10,2) NOT NULL,
  unit VARCHAR(50) NOT NULL,
  meal_type VARCHAR(20) NOT NULL, -- 'breakfast', 'lunch', 'dinner', 'snack'
  consumed_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Exercise Tracking

exercises

Master catalog of exercises.

CREATE TABLE exercises (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100),
  muscle_groups TEXT[],
  equipment VARCHAR(100),
  instructions TEXT,
  source VARCHAR(50) DEFAULT 'user',
  source_id VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

exercise_diary

User's exercise session entries.

CREATE TABLE exercise_diary (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  exercise_id UUID NOT NULL REFERENCES exercises(id),
  duration_minutes INTEGER,
  sets INTEGER,
  reps INTEGER,
  weight DECIMAL(10,2),
  distance DECIMAL(10,2),
  notes TEXT,
  performed_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Measurements & Progress

measurements

User body measurements and health metrics.

CREATE TABLE measurements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL, -- 'weight', 'body_fat', 'muscle_mass', etc.
  value DECIMAL(10,2) NOT NULL,
  unit VARCHAR(20) NOT NULL,
  measured_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Goals & Planning

goals

User fitness and nutrition goals.

CREATE TABLE goals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL, -- 'weight_loss', 'muscle_gain', 'calorie_target'
  target_value DECIMAL(10,2),
  target_unit VARCHAR(20),
  target_date DATE,
  current_value DECIMAL(10,2),
  status VARCHAR(20) DEFAULT 'active', -- 'active', 'completed', 'paused'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

meal_plan_templates

Reusable meal plan templates.

CREATE TABLE meal_plan_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  target_calories DECIMAL(10,2),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Family & Sharing

family_access

Permissions for family members to access each other's data.

CREATE TABLE family_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  grantor_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  grantee_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  access_type VARCHAR(50) NOT NULL, -- 'calorie', 'checkin', 'reports', 'food_list'
  granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(grantor_user_id, grantee_user_id, access_type)
);

AI & Chat

chat_history

AI chatbot conversation history.

CREATE TABLE chat_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  message TEXT NOT NULL,
  response TEXT NOT NULL,
  ai_provider VARCHAR(50),
  model_used VARCHAR(100),
  tokens_used INTEGER,
  response_time_ms INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Database Migrations

SparkyFitness uses a custom migration system that runs automatically on server startup.

Migration Process

The migration system:

  1. Checks current database version on startup
  2. Applies pending migrations in order
  3. Tracks applied migrations in the migrations table
  4. Logs migration results for debugging

Migration Structure

Migrations are stored in SparkyFitnessServer/db/migrations/ with the naming pattern:

YYYY_MM_DD_HH_MM_description.sql

Example: 2024_03_15_10_30_add_exercise_tracking.sql

Creating a New Migration

  1. Create the migration file in the migrations directory:
    cd SparkyFitnessServer/db/migrations/
touch 2024_03_15_14_20_add_meal_planning.sql
  2. Write the migration SQL:
    -- Migration: Add meal planning functionality
-- Version: 2024_03_15_14_20

BEGIN;

-- Create meal plans table
CREATE TABLE meal_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  planned_date DATE NOT NULL,
  total_calories DECIMAL(10,2),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE meal_plans ENABLE ROW LEVEL SECURITY;

-- Create RLS policy
CREATE POLICY meal_plans_user_policy ON meal_plans
  FOR ALL USING (user_id = auth.uid());

-- Create indexes
CREATE INDEX idx_meal_plans_user_date ON meal_plans(user_id, planned_date);

-- Update migration version
INSERT INTO migrations (version, applied_at) 
VALUES ('2024_03_15_14_20', NOW());

COMMIT;

Migration Best Practices

Backwards Compatibility

  • Add columns with default values to avoid breaking existing code
  • Create new tables rather than modifying existing ones when possible
  • Use transactions to ensure atomic migrations
  • Test migrations on development data first

Transaction Management

BEGIN;
-- All migration statements here
-- If any statement fails, entire migration rolls back
COMMIT;

Index Creation

-- Create indexes concurrently to avoid blocking
CREATE INDEX CONCURRENTLY idx_food_diary_user_date 
ON food_diary(user_id, consumed_at);

Data Migration

-- Example: Migrating data to new structure
BEGIN;

-- Create new table
CREATE TABLE new_nutrition_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  food_item_id UUID NOT NULL REFERENCES food_items(id),
  calories_per_100g DECIMAL(10,2),
  -- other columns
);

-- Migrate existing data
INSERT INTO new_nutrition_data (food_item_id, calories_per_100g)
SELECT food_item_id, calories * 100 / serving_size
FROM old_nutrition_data
WHERE serving_size > 0;

-- Drop old table (after verifying migration)
-- DROP TABLE old_nutrition_data;

COMMIT;

Rollback Strategy

While not automated, rollback migrations can be created:

  1. Document rollback steps in migration comments
  2. Create rollback scripts for complex migrations
  3. Test rollback procedures in development
  4. Backup database before applying production migrations

Troubleshooting Migrations

Migration Fails

  1. Check migration logs in application startup
  2. Verify database connection and permissions
  3. Check for syntax errors in migration SQL
  4. Ensure migration dependencies are met

Migration Tracking Issues

-- Check applied migrations
SELECT * FROM migrations ORDER BY applied_at;

-- Manually mark migration as applied (if needed)
INSERT INTO migrations (version, applied_at) 
VALUES ('2024_03_15_14_20', NOW());

Database State Issues

-- Check table structure
\d table_name

-- Check RLS policies
SELECT * FROM pg_policies WHERE tablename = 'table_name';

-- Check indexes
SELECT indexname, indexdef FROM pg_indexes 
WHERE tablename = 'table_name';

Database Maintenance

Performance Monitoring

  • Query performance: Use EXPLAIN ANALYZE for slow queries
  • Index usage: Monitor index usage with pg_stat_user_indexes
  • Connection monitoring: Track connection pool usage

Regular Maintenance

  • VACUUM: Regular vacuuming for performance
  • ANALYZE: Update table statistics
  • Index maintenance: Rebuild indexes if needed
  • Log rotation: Rotate and archive database logs

This database design provides a robust foundation for SparkyFitness, ensuring data integrity, security, and performance while supporting all application features.

Table of Contents