Settings Database Mapping

This document provides a comprehensive mapping of all settings fields in the MyStoryFlow application to their corresponding database tables and columns. This ensures a clear 1-1 mapping and prevents duplication.

Database Tables Overview

The settings system uses the following specialized tables:

profiles - Core user profile information (name, email, bio, etc.)
user_settings - UI preferences, accessibility, and general app settings
user_notification_settings - All notification preferences (email, SMS, push, etc.)
user_grammar_settings - AI writing assistance and grammar preferences
user_privacy_settings - Privacy, sharing, and visibility settings
user_voice_settings - Voice recording and transcription preferences
user_data_exports - Data export requests and history
user_devices - User device registrations for push notifications

Settings Page Field Mappings

Profile Information Tab

UI Field	Database Table	Database Column	Type	Default	Notes
First Name	`profiles`	`full_name`	string	-	Derived from full_name split
Last Name	`profiles`	`full_name`	string	-	Derived from full_name split
Email	`auth.users`	`email`	string	-	Read-only, managed by Supabase Auth
Phone	`profiles`	`phone`	string	null	Optional
Bio	`profiles`	`bio`	text	null	Optional
Avatar	`profiles`	`avatar_url`	text	null	File URL
Display Name	`profiles`	`display_name`	text	null	Public display name
Relationship Role	`profiles`	`relationship_role`	text	null	e.g., “Grandmother”, “Son”
Birth Year	`profiles`	`birth_year`	integer	null	Optional
Preferred Method	`profiles`	`preferred_method`	text	’mixed’	Writing preference
Technology Comfort	`profiles`	`technology_comfort`	text	’somewhat_comfortable’	Tech skill level

UI Preferences (Profile Tab)

UI Field	Database Table	Database Column	Type	Default	Notes
Language	`user_settings`	`preferred_language`	varchar(10)	‘en’	ISO language code
Dark Mode	`user_settings`	`dark_mode`	boolean	false	Theme preference
Text Size	`user_settings`	`text_size_preference`	varchar(20)	‘medium’	small/medium/large/extra-large
Timezone	`user_settings`	`timezone`	varchar(100)	‘America/New_York’	IANA timezone
High Contrast	`user_settings`	`high_contrast_mode`	boolean	false	Accessibility
Reduce Motion	`user_settings`	`reduce_motion`	boolean	false	Accessibility
Screen Reader	`user_settings`	`screen_reader_optimized`	boolean	false	Accessibility

Email Notifications Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Email Notifications	`user_notification_settings`	`email_enabled`	boolean	true	Master email toggle
Story Updates	`user_notification_settings`	`story_updates`	boolean	true	Story completion notifications
Family Invitations	`user_notification_settings`	`family_invitations`	boolean	true	Family member invites
Weekly Digest	`user_notification_settings`	`weekly_digest`	boolean	true	Summary emails
Marketing Emails	`user_notification_settings`	`marketing_emails`	boolean	false	Promotional content
System Announcements	`user_settings`	`system_announcements`	boolean	true	Important updates

SMS Notifications Tab

UI Field	Database Table	Database Column	Type	Default	Notes
SMS Notifications	`user_notification_settings`	`sms_enabled`	boolean	false	Master SMS toggle
Story Comments	`user_notification_settings`	`story_comments`	boolean	true	Comment notifications
Collaboration Updates	`user_notification_settings`	`collaboration_updates`	boolean	true	Family collaboration

In-App Notifications Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Push Notifications	`user_notification_settings`	`push_enabled`	boolean	true	Browser/app push
Sound Notifications	`user_settings`	`sound_notifications`	boolean	true	Audio alerts

Reminders & Prompts Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Writing Reminders	`user_notification_settings`	`writing_reminders`	boolean	false	Reminder notifications
Reminder Frequency	`user_notification_settings`	`reminder_frequency`	varchar	’weekly’	daily/weekly/monthly
Reminder Time	`user_notification_settings`	`reminder_time`	time	’09:00:00’	Time of day
Writing Prompts	`user_settings`	`writing_prompts`	boolean	true	Prompt suggestions
Prompt Frequency	`user_settings`	`writing_prompt_frequency`	varchar	’weekly’	Frequency of prompts

Grammar & Writing Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Grammar Assistance	`user_grammar_settings`	`grammar_enabled`	boolean	true	Enable grammar help
Auto-Correct	`user_grammar_settings`	`auto_correct`	boolean	false	Automatic corrections
Suggestion Level	`user_grammar_settings`	`suggestion_level`	varchar	’moderate’	light/moderate/comprehensive
Tone Suggestions	`user_grammar_settings`	`tone_suggestions`	boolean	true	Writing tone help
Clarity Suggestions	`user_grammar_settings`	`clarity_suggestions`	boolean	true	Clarity improvements
AI Enhancement	`user_grammar_settings`	`ai_enhancement_enabled`	boolean	false	Advanced AI features
Enhancement Style	`user_grammar_settings`	`enhancement_style`	varchar	’preserve’	preserve/improve/transform

UI Field	Database Table	Database Column	Type	Default	Notes
Profile Visibility	`user_privacy_settings`	`profile_visibility`	varchar	’private’	public/family/private
Show in Directory	`user_privacy_settings`	`show_in_directory`	boolean	false	Public user directory
Story Discovery	`user_privacy_settings`	`allow_story_discovery`	boolean	false	Public story discovery
Default Story Privacy	`user_privacy_settings`	`default_story_privacy`	varchar	’private’	Default for new stories
Family Collaboration	`user_privacy_settings`	`allow_family_collaboration`	boolean	true	Family can collaborate
Approval for Shares	`user_privacy_settings`	`require_approval_for_shares`	boolean	true	Require share approval

Data Collection Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Analytics	`user_privacy_settings`	`analytics_enabled`	boolean	true	Usage analytics
Improvement Data	`user_privacy_settings`	`improvement_data_sharing`	boolean	true	Product improvement
Marketing Data	`user_privacy_settings`	`marketing_data_usage`	boolean	false	Marketing analytics

Voice Recording Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Recording Quality	`user_voice_settings`	`recording_quality`	varchar	’high’	standard/high/premium
Noise Reduction	`user_voice_settings`	`noise_reduction`	boolean	true	Audio processing
Auto Transcription	`user_voice_settings`	`auto_transcription`	boolean	true	Automatic transcription
Voice Enhancement	`user_voice_settings`	`voice_enhancement`	boolean	false	AI voice processing
Transcription Language	`user_voice_settings`	`preferred_language`	varchar	’en’	Language for transcription
Transcription Accuracy	`user_voice_settings`	`transcription_accuracy`	varchar	’balanced’	speed/balanced/accuracy

App Features Tab

UI Field	Database Table	Database Column	Type	Default	Notes
Auto-Save	`user_settings`	`auto_save_enabled`	boolean	true	Automatic saving
Auto-Save Interval	`user_settings`	`auto_save_interval_minutes`	integer	5	Minutes between saves
Writing Tips	`user_settings`	`show_writing_tips`	boolean	true	Show helpful tips
Onboarding Hints	`user_settings`	`show_onboarding_hints`	boolean	true	Show onboarding help

Subscription Information (Read-Only)

UI Field	Database Table	Database Column	Type	Notes
Subscription Tier	`profiles`	`subscription_tier`	varchar	starter/premium/family
Subscription Status	`profiles`	`subscription_status`	varchar	trial/active/cancelled
Trial Ends At	`profiles`	`trial_ends_at`	timestamp	Trial expiration

Onboarding Information (System)

UI Field	Database Table	Database Column	Type	Notes
Onboarding Completed	`profiles`	`onboarding_completed`	boolean	Setup completion status
Onboarding Step	`profiles`	`onboarding_step`	varchar	Current onboarding step
Onboarding Data	`profiles`	`onboarding_data`	jsonb	Onboarding progress data

Data Export Requests

UI Field	Database Table	Database Column	Type	Notes
Export Type	`user_data_exports`	`export_type`	varchar	stories/profile/all
Export Status	`user_data_exports`	`status`	varchar	pending/processing/completed/failed
File URL	`user_data_exports`	`file_url`	text	Download link
File Size	`user_data_exports`	`file_size_bytes`	bigint	File size in bytes
Expires At	`user_data_exports`	`expires_at`	timestamptz	Download expiration

Important Notes

No Duplicate Fields: Each setting should only exist in one table to maintain data consistency.
Migration Complete: Old duplicate fields in profiles table have been migrated to appropriate specialized tables.
RLS Policies: All settings tables have Row Level Security (RLS) policies ensuring users can only access their own settings.
Foreign Key Constraints: All settings tables reference auth.users(id) with CASCADE DELETE to maintain referential integrity.
Default Values: All fields have sensible defaults to ensure the app works even with minimal configuration.
Indexes: Performance indexes are created on user_id columns for all settings tables.
Updated At Triggers: All settings tables have automatic updated_at timestamp triggers.

API Endpoints

GET/PUT /api/settings/profile - Profile information
GET/PUT /api/settings/ui - UI preferences from user_settings
GET/PUT /api/settings/notifications - All notification settings
GET/PUT /api/settings/grammar - Grammar and writing settings
GET/PUT /api/settings/privacy - Privacy and sharing settings
GET/PUT /api/settings/voice - Voice recording settings
GET/POST /api/settings/data-export - Data export requests