Database Schema & Migrations

Last updated: 2026-09-01

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 Database Security Tiers.

---

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 Database Security Tiers.

Authentication & Identity (Tier 1: Owner-Only)

Table	Purpose
user	Account identity, password hash, email
session	Active authentication sessions
api_key	User-generated API keys for external access
passkey	Passwordless login credentials
two_factor	2FA recovery codes and secrets
verification	Email verification tokens
account	Auth credentials and email accounts

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

Table	Purpose
foods	Custom food items created by user
food_variants	Serving size options for foods
food_entries	Logged meals/calories for the day
food_entry_meals	Meal details associated with logged entries
meals	Custom meal templates
meal_foods	Ingredients assigned to meals
meal_types	Custom meal type definitions (breakfast, lunch, etc.)
meal_plans	Weekly meal planning schedules
meal_plan_templates	Reusable meal plan templates (supports multiple active plans per user)
meal_plan_template_assignments	Scheduled meal templates to calendar
meal_plan_assignment_sets	Sets within assigned meal plans

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

Table	Purpose
exercises	Custom exercises created by user
exercise_entries	Logged exercises for the day
exercise_preset_entries	Logged workout presets for the day
exercise_entry_sets	Reps, weights, and sets completed
exercise_entry_activity_details	Heart rate, distance, activity data
exercise_entry_laps	Lap split intervals for workouts
exercise_entry_hr_zones	Heart-rate time-in-zone splits per workout
exercise_entry_gps_points	GPS trackpoints & second-by-second telemetry. One row per workout (the points column holds the whole track as JSONB), not one row per trackpoint
workout_presets	Custom workout/preset templates
workout_preset_exercises	Exercises assigned to presets
workout_preset_exercise_sets	Reps/sets configured in presets
workout_plan_templates	Templates for weekly and sequential workout schedules
workout_plan_template_assignments	Scheduled workout templates (weekday or ordered sequence)
workout_plan_assignment_sets	Sets within assigned workout plans
workout_feedback	Session / per-exercise workout feedback (difficulty, pain flag and note) behind adaptive suggestions

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

Table	Purpose
check_in_measurements	Weight, body composition, BMR, and circumference measurements
check_in_photos	Progress photos
custom_measurements	User-defined custom measurement types
custom_categories	User-defined measurement categories
water_intake	Total water logged for the day
water_intake_entries	Individual logged water cups
water_containers	Configured container sizes
sleep_entries	Sleep logs (bedtime, wake time)
sleep_entry_stages	Sleep stage breakdowns (REM, Deep, Light)
sleep_need_calculations	AI sleep need calculations
daily_sleep_need	Sleep goals calculated for the day
health_metric_samples	Intraday wearable telemetry — heart rate, HRV, respiration, SpO2, stress, body battery, skin temperature. One JSONB-bucketed row per user/metric/day/provider (not one row per sample)
vitals_entries	Blood pressure, blood glucose, and temperature logs
daily_health_metrics	Daily automated wearable summary, scores, and source-timed total calories

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

Table	Purpose
fasting_logs	Fasting timeline logs (start/end fast)
mood_entries	Logged mood and energy levels
user_custom_moods	User-defined mood tags (icon/color)
medications	Custom medication inventory lists
medication_schedules	Reminders and schedules for medications
medication_entries	Logs of medications taken
medication_pens	Trackers for medication delivery pens
medication_titration_steps	Automated titration dosage plans
injection_entries	Injection logs (site, time, etc.)
user_custom_symptoms	Custom tracked health symptoms
symptom_entries	Logs of daily tracked symptom severity

Cycle & Pregnancy (Tier 1: Owner-Only)

Table	Purpose
cycles	Menstrual cycle history records
cycle_settings	Cycle hub settings (mode, parameters)
cycle_daily_entries	Per-day cycle logs (flow, BBT, mood, etc.)
cycle_test_entries	Ovulation and pregnancy test logs
pregnancies	Pregnancy records (due date, status)
pregnancy_kick_sessions	Fetal kick-counter sessions
pregnancy_contractions	Contraction timer logs
pregnancy_photos	Bump photo journal
pregnancy_checklist_state	Weekly pregnancy checklist completion
health_appointments	Prenatal and other health appointments

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

Table	Purpose
profiles	User full name, height, display metrics
user_preferences	Unit, formatting, and chart display preferences
user_nutrient_display_preferences	Nutrient column display preferences
user_meal_visibilities	Visibility settings for meals
user_goals	Active daily calorie/macro goals
user_custom_nutrients	Custom nutrient definitions
user_nutrient_goal_preferences	Per-user minimum/maximum/target goal direction override per nutrient (predefined or custom)
user_water_containers	Configured container sizes
user_dashboard_layouts	Rearranged dashboard widget positions
user_medication_display_preferences	GLP-1/Medication display preferences
user_mood_display_preferences	Mood picker visibility settings
user_cycle_display_preferences	Cycle dashboard tile visibility
user_allergen_preferences	Allergen preferences
user_ignored_updates	Records of skipped release updates

AI & Chat (Tier 1: Owner-Only)

Table	Purpose
sparky_chat_history	AI Assistant chat messages and history
ai_service_settings	User-defined custom assistant configurations

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

Table	Purpose
global_settings	Application feature flags and config
sso_provider	Active Single Sign-On providers
oidc_providers	OpenID Connect integration settings
external_provider_types	Search provider configurations (FatSecret, USDA)
external_data_providers	Configured API integration credentials, including personal or global Open Food Facts accounts
openfoodfacts_product_read_rate_limit	Singleton lease and cooldown coordinating Open Food Facts product reads across server instances
openfoodfacts_sync_queue	Dormant revision-aware automatic upload state and retained history; unused by manual contributions
medication_types	Medication categories lookup
medication_route_types	Medication administration routes lookup
medication_schedule_types	Medication scheduling frequencies lookup
admin_activity_logs	Admin action audits
day_classification_cache	Daily summary caching logs

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

Table	Purpose
onboarding_data	Initial user onboarding metrics
onboarding_status	User onboarding status
family_access	Sharing rules and delegation credentials
backup_settings	Automated database backup settings

---

Provider Credentials (external_data_providers)

Integration credentials are stored encrypted, not in the plaintext columns. This trips up most people writing their first provider.

external_data_providers has both plaintext (app_id, app_key) and encrypted (encrypted_app_id / app_id_iv / app_id_tag, and the matching app_key triplet) columns. The plaintext columns are legacy and stay NULL — reading them returns nothing even for a provider that is configured and syncing correctly, which surfaces as a misleading "email is missing" or "API key is missing" error on a row that genuinely has one.

Open Food Facts contributions in the first release use explicit owner-only preview and confirmation endpoints at /api/v2/foods/:foodId/openfoodfacts/preview and /api/v2/foods/:foodId/openfoodfacts/contribute. The server-wide allow_openfoodfacts_contributions gate makes this available; it does not grant publication consent. The authenticated actor must own the food, have an active personal account or eligible global fallback, and confirm the exact data and own photo. A short-lived signed preview binds the food, outgoing fields, sanitized image, product language, provider configuration and public product revision.

Eligibility excludes Open Food Facts and proprietary-provider imports, requires a non-empty name and checksum-valid non-internal GTIN, and only permits a metric-convertible default variant. Photo upload precedes the structured data write. If the photo succeeds but the data result cannot be confirmed, the response reports that partial result without assuming the data was not saved.

Automatic queue functions and openfoodfacts_sync_queue remain dormant for future work. The release disables automatic worker/scheduler execution, installs no food enqueue or preference-backfill triggers, and does not let an old auto_contribute_openfoodfacts preference activate uploads. Saving a product, provider or setting therefore creates no public contribution or backfill.

Product Opener reads from barcode lookup and manual preview/confirmation share the database-coordinated permit in openfoodfacts_product_read_rate_limit. It is held through the response and enforces at least five seconds between product reads across server instances; search endpoints are not throttled by this limiter.

Encryption is AES-GCM via security/encryption.ts, keyed by SPARKY_FITNESS_API_ENCRYPTION_KEY. Each value needs all three of its columns (ciphertext, IV, auth tag) to be decryptable.

Reading a credential

Two supported paths, depending on what you need:

• A single provider's secret, inside an integration. Query the encrypted triplet directly and decrypt() it. See integrations/hevy/hevyService.ts (getHevyApiKey) for the reference shape — note it uses getSystemClient(), since a background sync has no user request context.
• A user's providers, for the UI or a service. Use externalProviderRepository.getExternalDataProviders(userId). It decrypts on the way out and returns the plaintext on the app_id / app_key properties of the returned object — so those field names are populated even though the columns of the same name are NULL. Failed decryption is logged and yields null rather than throwing, so treat a null credential as "unusable", not "absent".

Writing a credential

Never INSERT/UPDATE the plaintext columns. Encrypt first and write the triplet — externalProviderRepository handles this for the standard create/update paths.

What this means for a new provider

A provider whose required_fields include app_id/app_key is storing a user's real third-party credential. Where that credential is an account password rather than a revocable API key, note that the encryption is necessarily reversible: the server must recover the plaintext to log in, so a database dump plus SPARKY_FITNESS_API_ENCRYPTION_KEY yields working credentials. Prefer providers that issue scoped, revocable API keys, and set is_strictly_private = TRUE on any provider type holding a personal credential so it is never exposed through family sharing.

---

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

2. Write the migration SQL — see the migration checklist in the repo's agent-docs folder (for developers and AI tools)

3. db_schema_backup.sql updates itself — after merge, CI regenerates it from the migrations and opens an automated sync PR; never edit it by hand

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