Database Security & RLS Tiers

Last updated: 2026-07-08

SparkyFitness uses PostgreSQL Row-Level Security (RLS) to enforce strict data isolation between accounts, caregivers, family members, and third-party integrations (API keys). This document covers the security design, context switching mechanism, and the three-tier database table classifications.

Identity & Context Functions

PostgreSQL session variables are initialized at the start of every pool client transaction (via the public.set_app_context(userId, authenticatedUserId) helper). RLS policies evaluate access using these functions:

current_user_id(): Returns the ID of the active profile context being viewed (which changes during family delegation context switching; read from app.user_id).
authenticated_user_id(): Returns the ID of the true authenticated logged-in actor (which never changes during context switching; read from app.authenticated_user_id).
can_access_user_data(targetUserId, permissionType, authenticatedUserId): Checks whether the authenticated user holds the given logical permission over the target user's data (diary, checkin, medications, reports, and read-only *_read variants). Domain helpers such as has_diary_read_access, has_checkin_read_access, and has_medication_access wrap it. Cycle and pregnancy are owner-only and are not delegatable.

The Three Security Tiers

All tables in the SparkyFitness database are classified into one of three security tiers to isolate data access according to the least-privilege principle:

Tier 1: Strictly Private (Owner-Only Read & Write)

These tables contain highly sensitive credentials, API keys, SSO tokens, 2FA recovery data, or private log histories. Only the owner (authenticated_user_id()) can view or modify these records. Family delegates and context-switched API keys have absolutely zero access.

Table Name	Description	Write (insert/update/delete)	Read (select)
`user`	Account identity, password hash, role, email status	Owner-Only (`authenticated_user_id()`)	Owner-Only
`two_factor`	Two-factor authentication (2FA) recovery codes and secrets	Owner-Only	Owner-Only
`verification`	Email verification tokens	Owner-Only	Owner-Only
`api_key`	User-generated API keys for external access	Owner-Only	Owner-Only
`user_oidc_links`	OpenID Connect SSO credentials links	Owner-Only	Owner-Only
`passkey`	Passkey credentials for passwordless login	Owner-Only	Owner-Only
`session`	Active user authentication sessions	Owner-Only	Owner-Only
`passkey_registration_tickets`	Short-lived (~60s), single-use tickets bridging the mobile→browser passkey registration handoff (transient session material)	System-Only (RLS on, no policies; `getSystemClient` only)	System-Only
`account`	Auth credentials and email accounts	Owner-Only	Owner-Only
`sparky_chat_history`	AI Assistant chat messages and history	Owner-Only	Owner-Only
`user_ignored_updates`	Records of skipped release updates	Owner-Only	Owner-Only
`admin_activity_logs`	Admin action audits	Admin-Only	Admin-Only
`ai_service_settings`	User-defined custom assistant configurations	Owner-Only	Owner-Only (Public configs readable by all)
`cycle_settings`	Cycle & pregnancy hub settings (mode, cycle parameters, birth control, conditions)	Owner-Only	Owner-Only
`cycle_daily_entries`	Per-day cycle logs (flow, period products, BBT, cervical mucus, moods, libido, notes)	Owner-Only	Owner-Only
`cycles`	Derived/manually-corrected period & cycle history records	Owner-Only	Owner-Only
`user_cycle_display_preferences`	Private preference on cycle dashboard tile/card visibility	Owner-Only	Owner-Only
`user_mood_display_preferences`	Personal show/hide config for the mood picker	Owner-Only	Owner-Only
`cycle_test_entries`	Ovulation (OPK) and pregnancy test logs	Owner-Only	Owner-Only
`pregnancies`	Pregnancy records (due date, status, linked prenatal medication)	Owner-Only	Owner-Only
`pregnancy_kick_sessions`	Fetal kick-counter sessions	Owner-Only	Owner-Only
`pregnancy_contractions`	Contraction timer logs	Owner-Only	Owner-Only
`pregnancy_photos`	Bump photo journal	Owner-Only	Owner-Only
`pregnancy_checklist_state`	Weekly pregnancy checklist completion/custom items	Owner-Only	Owner-Only
`health_appointments`	Prenatal & other health appointments	Owner-Only	Owner-Only

Tier 2: View-Only Shared (Owner-Only Write, Delegate-Read)

These tables contain user profiles, layouts, display settings, and custom databases (like the foods/exercises database libraries). Switched delegates can view this data to render the dashboard, but only the account owner is allowed to create or edit them.

Table Name	Description	Write (insert/update/delete)	Read (select)
`profiles`	User full name, height, and display metrics	Owner-Only (`authenticated_user_id()`)	Switched Delegate (`current_user_id()`)
`user_preferences`	Unit display preferences (Metric vs Imperial)	Owner-Only (`authenticated_user_id()`)	Switched Delegate
`user_nutrient_display_preferences`	Rearranged nutrient column preferences	Owner-Only	Switched Delegate
`user_medication_display_preferences`	Private preference on GLP-1/Medication display	Owner-Only	Delegate with `can_manage_medications` or `can_view_reports`
`user_dashboard_layouts`	Rearranged dashboard widget positions	Owner-Only	Switched Delegate
`onboarding_data`	Initial user onboarding metrics	Owner-Only	Switched Delegate
`onboarding_status`	User onboarding status	Owner-Only	Switched Delegate
`meal_types`	Custom breakfast, lunch, dinner, snack layouts	Owner-Only	Switched Delegate with `can_manage_diary` or `can_view_reports`
`foods`	Custom food items created by the user	Owner-Only	Delegate with `can_view_food_library`, `can_manage_diary`, or `can_view_reports`
`food_variants`	Serving options / sizes for custom foods	Owner-Only	Delegate with `can_view_food_library`, `can_manage_diary`, or `can_view_reports`
`meals`	Custom meals created by the user	Owner-Only	Delegate with `can_view_food_library`, `can_manage_diary`, or `can_view_reports`
`meal_foods`	Ingredients assigned inside a custom meal — either a food (`food_id`) or a linked reusable sub-meal (`child_meal_id`, `item_type='meal'`). Writing a linked-meal row requires the caller to also have library access to the referenced sub-meal, in addition to owning the parent meal.	Owner-Only (and the referenced food/sub-meal must be accessible to the owner)	Delegate with `can_view_food_library`, `can_manage_diary`, or `can_view_reports`
`exercises`	Custom exercises created by the user	Owner-Only	Delegate with `can_view_exercise_library`, `can_manage_diary`, or `can_view_reports`
`workout_presets`	Custom workouts/presets created by the user	Owner-Only	Delegate with `can_view_exercise_library`, `can_manage_diary`, or `can_view_reports`
`workout_preset_exercises`	Exercises assigned inside a workout preset	Owner-Only	Delegate with `can_view_exercise_library`, `can_manage_diary`, or `can_view_reports`
`workout_preset_exercise_sets`	Reps/sets configured inside a preset exercise	Owner-Only	Delegate with `can_view_exercise_library`, `can_manage_diary`, or `can_view_reports`
`workout_plan_templates`	Templates for weekly workout schedules	Owner-Only	Delegate with `can_view_exercise_library`, `can_manage_diary`, or `can_view_reports`
`external_data_providers`	Configured API integrations (FatSecret, USDA)	Owner-Only	Delegate with `share_external_providers`
`family_access`	Sharing rules & delegation credentials	Owner-Only	Owner or Switched Delegate
`user_goals`	Active daily calorie/macro goals	Owner-Only	Delegate with `can_manage_diary` or `can_view_reports`
`user_nutrient_goal_preferences`	Per-user minimum/maximum/target goal direction override per nutrient	Owner-Only	Delegate with `can_manage_diary` or `can_view_reports`
`weekly_goal_plans`	Scheduled goal plan adjustments	Owner-Only	Delegate with `can_manage_diary` or `can_view_reports`
`user_water_containers`	Configured container sizes (e.g. 500ml bottle)	Owner-Only	Delegate with `can_manage_diary` or `can_view_reports`

Tier 3: Delegate-Writable (Daily Logs & Schedules)

These tables contain daily diaries, logging entries, and scheduler items. Caregivers/delegates with active delegation permissions are granted full read and write access to log or modify data on behalf of the owner.

A. Diary Logs (Writable by delegates with `can_manage_diary` / Readable by `can_view_reports` or `can_manage_diary`)

Table Name	Description	Write (insert/update/delete)	Read (select)
`food_entries`	Logged meals/calories consumed for the day	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`food_entry_meals`	Meal details associated with logged food entries	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`food_favorites`	Starred (favorite) foods/meals for quick re-logging	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`exercise_entries`	Logged exercises/sets completed for the day	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`exercise_preset_entries`	Logged workout presets for the day	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`exercise_entry_sets`	Reps, weights, and sets completed for the logged exercises	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`exercise_entry_activity_details`	Heart rate, distance, and activity details for workout logs	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`water_intake`	Total water logged for the day	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`water_intake_entries`	Individual logged water cups/containers for the day	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`meal_plan_template_assignments`	Active meal templates scheduled to the calendar	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`workout_plan_template_assignments`	Active workout templates scheduled to the calendar	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`
`workout_plan_assignment_sets`	Active sets scheduled in workout plans	Delegate with `can_manage_diary`	Delegate with `can_manage_diary` or `can_view_reports`

B. Medication & Symptom Logs (Writable by delegates with `can_manage_medications` / Readable by `can_view_reports` or `can_manage_medications`)

Table Name	Description	Write (insert/update/delete)	Read (select)
`medications`	Custom medication inventory lists (e.g. insulin)	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`medication_schedules`	Reminders/schedules to take medications	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`medication_entries`	Logs of medications taken	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`medication_pens`	Trackers for medication delivery pens	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`injection_entries`	Injection logs (e.g., site of injection)	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`medication_titration_steps`	Automated titration dosage plans	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`user_custom_symptoms`	Custom tracked health symptoms	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`symptom_entries`	Logs of daily tracked symptom severity	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`
`user_custom_symptom_locations`	Custom locations where symptoms occur	Delegate with `can_manage_medications`	Delegate with `can_manage_medications` or `can_view_reports`

C. Check-In & Wellness Logs (Writable by delegates with `can_manage_checkin` / Readable by `can_view_reports` or `can_manage_checkin`)

Table Name	Description	Write (insert/update/delete)	Read (select)
`check_in_measurements`	Weight, neck, waist, hips measurements	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`check_in_photos`	Progress photos	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`sleep_entries`	Sleep logs (bedtime, wake time)	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`sleep_entry_stages`	Sleep stage breakdowns (REM, Deep, Light)	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`sleep_need_calculations`	AI sleep need calculations	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`daily_sleep_need`	Sleep goals calculated for the day	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`fasting_logs`	Logs of fasting timelines (start/end fast)	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`mood_entries`	Logged mood and energy levels	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`day_classification_cache`	Daily summary caching logs	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`custom_categories`	User-defined custom measurement categories (e.g. GLP-1 daily check-in metrics)	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`custom_measurements`	Logged values for custom measurement categories	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`
`user_custom_moods`	User-defined mood tags (icon/color) for the mood picker	Delegate with `can_manage_checkin`	Delegate with `can_manage_checkin` or `can_view_reports`

System & Global Reference Tables (Public Read, Admin Write)

These tables store global configuration settings, lookup values, and reference metadata. They do not contain user-specific data and do not have Row-Level Security enabled. All authenticated users can read them, but only administrators can update them.

Table Name	Description	Write (insert/update/delete)	Read (select)
`global_settings`	Application global feature flags and system configurations	Admin-Only	Authenticated Users
`backup_settings`	Automated database backup timing and storage credentials	Admin-Only	Admin-Only
`sso_provider`	Active Single Sign-On providers (Google, Apple, etc.)	Admin-Only	Public (Login page)
`oidc_providers`	OpenID Connect integration settings and metadata	Admin-Only	Public
`external_provider_types`	Search provider configurations lookup (FATSecret, USDA)	Admin-Only	Authenticated Users
`medication_types`	Medication categories lookup (GLP-1, Insulin, ADHD, etc.)	Admin-Only	Authenticated Users
`medication_route_types`	Medication administration route lookup (Subcutaneous, Oral)	Admin-Only	Authenticated Users
`medication_schedule_types`	Medication scheduling frequencies lookup (Daily, Weekly)	Admin-Only	Authenticated Users

McpVision Integration Tools

Support The Project