Database Schema & Migrations

Last updated: 2026-07-08

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

Note:

• For authoritative table definitions, see db_schema_backup.sql (SQL source) or shared/src/schemas/database/ (TypeScript Zod schemas).
• For security tier classifications and permission mappings, see 11.database-security-tiers.md.

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_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_entries_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 enforce data isolation. PostgreSQL context functions (initialized via public.set_app_context(userId, authenticatedUserId) on every connection) control access:

-- Example: diary logs are readable by delegates with diary read access and
-- writable by delegates with can_manage_diary. rls_policies.sql usually applies
-- this via the create_diary_policy('food_entries') generator, which emits:
ALTER TABLE food_entries ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_policy ON public.food_entries FOR SELECT TO PUBLIC
  USING (has_diary_read_access(user_id));
CREATE POLICY modify_policy ON public.food_entries FOR ALL TO PUBLIC
  USING (has_diary_access(user_id))
  WITH CHECK (has_diary_access(user_id));

Key functions:

• current_user_id() — active profile context (may differ during family delegation; from app.user_id)
• authenticated_user_id() — the true logged-in actor (never changes; from app.authenticated_user_id)
• can_access_user_data(target_user_id, permission_type, authenticated_user_id()) — resolves a logical permission (diary, checkin, medications, reports, plus *_read variants) against the family_access grant. Domain shortcuts wrap it: has_diary_read_access, has_diary_access, has_checkin_read_access, has_medication_access, has_family_access.

Cycle and pregnancy tables are owner-only (no delegation). See SparkyFitnessServer/db/rls_policies.sql for the complete policy set and the create_*_policy generators.

Table Index

Quick reference of all tables by domain and purpose. For detailed security tier, permission type, and access rules, see 11.database-security-tiers.md.

Authentication & Identity (Tier 1: Owner-Only)

Food & Nutrition (Tier 2/3: Owner-Write, Delegate-Read/Write)

Exercise & Workouts (Tier 2/3: Owner-Write, Delegate-Read/Write)

Measurements & Health (Tier 1/3: Owner-Only or Delegate-Write)

Fasting, Mood, Medications, & Symptoms (Tier 1/3: Owner-Only or Delegate-Write)

Cycle & Pregnancy (Tier 1: Owner-Only)

User Preferences & Settings (Tier 2: Owner-Only Write, Delegate-Read)

AI & Chat (Tier 1: Owner-Only)

Admin & System (Tier 1: Admin-Only or Public)

Internal & Shared (Tier 2: Owner-Only or System)

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:

YYYYMMDDHHMMSS_description.sql

Example: 20240315103000_add_exercise_tracking.sql

Creating a New Migration

1. Create the migration file in the migrations directory:

   cd SparkyFitnessServer/db/migrations/
   touch 20240315142000_add_meal_planning.sql
   

   Copy to clipboard
2. Write the migration SQL — see the migration checklist in the repo's agent-docs folder (for developers and AI tools)
3. Update db_schema_backup.sql with the new schema state
4. Apply RLS policies (these are reapplied automatically on startup from db/rls_policies.sql)

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_entries_user_date 
ON food_entries(user_id, created_at);

Data Migration

BEGIN;
-- Create new table
CREATE TABLE new_table (id UUID PRIMARY KEY, ...);

-- Migrate existing data
INSERT INTO new_table (...)
SELECT ... FROM old_table;

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

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 ('20240315142000', 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