Repository navigation
Database Schema Updates for User Preferences #43
Description
Feature: Database Schema Updates for User Preferences
Overview
Create and implement database schema updates to support user preferences, categories, and related functionality in Supabase.
Parent Issue
Part of #37 - Add Settings Page with Authentication and User Management
Current Status
- ✅ Supabase authentication configured
- ✅ Basic spending entries table exists
- ❌ No user preferences table
- ❌ No user categories table
- ❌ Missing RLS policies for user data
Requirements
Database Tables
1. User Preferences Table
CREATE TABLE user_preferences (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
currency_code TEXT NOT NULL DEFAULT 'USD',
currency_symbol TEXT NOT NULL DEFAULT '$',
currency_name TEXT NOT NULL DEFAULT 'US Dollar',
theme TEXT DEFAULT 'auto', -- 'light', 'dark', 'auto'
notifications_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(user_id)
);2. User Categories Table
CREATE TABLE categories (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
color TEXT DEFAULT '#3b82f6',
icon TEXT DEFAULT 'folder-outline',
is_default BOOLEAN DEFAULT false,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(user_id, name)
);3. Update Existing Spending Entries Table
-- Add category reference to existing spending_entries
ALTER TABLE spending_entries
ADD COLUMN category_id UUID REFERENCES categories(id) ON DELETE SET NULL;
-- Migrate existing category strings to new category system
-- (This will be handled in a separate migration script)Row Level Security (RLS) Policies
User Preferences RLS
-- Enable RLS
ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;
-- Users can view their own preferences
CREATE POLICY "Users can view own preferences" ON user_preferences
FOR SELECT USING (auth.uid() = user_id);
-- Users can insert their own preferences
CREATE POLICY "Users can insert own preferences" ON user_preferences
FOR INSERT WITH CHECK (auth.uid() = user_id);
-- Users can update their own preferences
CREATE POLICY "Users can update own preferences" ON user_preferences
FOR UPDATE USING (auth.uid() = user_id);
-- Users can delete their own preferences
CREATE POLICY "Users can delete own preferences" ON user_preferences
FOR DELETE USING (auth.uid() = user_id);Categories RLS
-- Enable RLS
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
-- Users can view their own categories + default categories
CREATE POLICY "Users can view own and default categories" ON categories
FOR SELECT USING (auth.uid() = user_id OR is_default = true);
-- Users can insert their own categories
CREATE POLICY "Users can insert own categories" ON categories
FOR INSERT WITH CHECK (auth.uid() = user_id AND is_default = false);
-- Users can update their own categories (not defaults)
CREATE POLICY "Users can update own categories" ON categories
FOR UPDATE USING (auth.uid() = user_id AND is_default = false);
-- Users can delete their own categories (not defaults)
CREATE POLICY "Users can delete own categories" ON categories
FOR DELETE USING (auth.uid() = user_id AND is_default = false);Database Functions & Triggers
Auto-update timestamps
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Triggers for auto-updating timestamps
CREATE TRIGGER update_user_preferences_updated_at
BEFORE UPDATE ON user_preferences
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_categories_updated_at
BEFORE UPDATE ON categories
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();Default user preferences creation
-- Function to create default preferences for new users
CREATE OR REPLACE FUNCTION create_default_user_preferences()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_preferences (user_id)
VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger to create default preferences on user signup
CREATE TRIGGER create_user_preferences_on_signup
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION create_default_user_preferences();Default Categories Setup
-- Insert default spending categories
INSERT INTO categories (user_id, name, color, icon, is_default) VALUES
(NULL, 'Food & Dining', '#ef4444', 'restaurant-outline', true),
(NULL, 'Transportation', '#3b82f6', 'car-outline', true),
(NULL, 'Shopping', '#8b5cf6', 'bag-outline', true),
(NULL, 'Entertainment', '#f59e0b', 'game-controller-outline', true),
(NULL, 'Bills & Utilities', '#10b981', 'document-text-outline', true),
(NULL, 'Healthcare', '#ec4899', 'medical-outline', true),
(NULL, 'Education', '#06b6d4', 'school-outline', true),
(NULL, 'Other', '#6b7280', 'ellipse-outline', true);Migration Strategy
Phase 1: Create Tables
- Create user_preferences table
- Create categories table
- Set up RLS policies
- Create triggers and functions
Phase 2: Data Migration
- Create default categories
- Migrate existing spending entry categories
- Create default user preferences for existing users
- Update application code to use new schema
Phase 3: Cleanup
- Remove old category string fields (after migration)
- Optimize indexes for performance
- Add any missing constraints
Acceptance Criteria
- All tables created with proper constraints and indexes
- RLS policies properly secure user data
- Default categories available to all users
- Automatic user preferences creation on signup
- Existing data migrated without loss
- Performance optimized with appropriate indexes
- Database backup taken before migration
Technical Considerations
- Test migration thoroughly on development database first
- Implement rollback strategy for migration failures
- Consider impact on existing application during migration
- Monitor performance after schema changes
- Ensure proper foreign key constraints and cascading deletes
Priority
Medium - Foundation for user preferences features
Dependencies
- Supabase database access
- Database migration planning
- Coordination with application code updates
Related Issues
- Add Settings Page with Authentication and User Management #37 - Parent issue for Settings page
- Implement Category Management System #40 - Category Management System
- Implement Currency Preference Database Storage #41 - Currency Preference Database Storage
- Backend User Preferences Integration #42 - Backend User Preferences Integration
🤖 Generated with Claude Code
Co-Authored-By: Claude noreply@anthropic.com
Activity
laminko commentedon Jun 22, 2025
✅ Database Schema Updates Complete
The database schema has been successfully updated with the following changes:
📋 What was implemented:
1. User Preferences Table
2. Enhanced Categories Table
3. Database Relationships
4. Row Level Security (RLS)
5. Database Functions & Triggers
6. Default System Categories
7. Performance Optimization
📁 Files Updated:
🔄 Migration Status:
The schema is ready for implementation. Next steps:
✅ Ready for Issues #41 & #42
The database foundation is now complete for currency preferences and backend integration.
🤖 Generated with Claude Code
Co-Authored-By: Claude noreply@anthropic.com
feat: implement database schema for user preferences (Issue #43)
refactor: consolidate database migration documentation (Issue #43)
feat: implement database schema for user preferences (Issue #43)
refactor: consolidate database migration documentation (Issue #43)
feat: Add Settings page with authentication UI and improved components (
Release: Major authentication and user management features (#50)
Release v1.3.0: Facebook OAuth UI removal and version bump (#51)