README.md

Supabase Database Schema

This directory contains the complete database schema, migrations, and RLS policies for Calmlytic.

📁 Directory Structure

supabase/
├── migrations/          # SQL migration files (in chronological order)
├── seed.sql            # Seed data (templates, etc.)
└── README.md           # This file

🚀 Quick Start

Option 1: Using Supabase CLI (Recommended)

1. Install Supabase CLI

   npm install -g supabase

2. Login to Supabase

   supabase login

3. Link your project

   supabase link --project-ref your-project-ref

   Find your project ref in: Supabase Dashboard → Settings → General → Reference ID

4. Pull existing schema (if you have an existing database)

   supabase db pull

   This will create migration files from your existing database.

5. Apply migrations to a new project

   supabase db push

Option 2: Manual SQL Execution

1. Open Supabase SQL Editor

   • Go to your Supabase project dashboard
   • Navigate to SQL Editor

2. Run migrations in order

   • Execute each migration file in migrations/ in chronological order
   • Start with the earliest timestamp

3. Verify setup

   • Check that all tables are created
   • Verify RLS policies are enabled
   • Test that triggers are working

📋 Exporting Schema from Existing Database

If you have an existing Supabase database and want to export the complete schema:

Using Supabase CLI

# Pull the complete schema
supabase db pull

# This will create migration files in supabase/migrations/

Using Supabase Dashboard

1. Go to Database → Schema Visualizer
2. Click Export Schema (if available)
3. Or use the SQL Editor to run:

-- Export all tables
SELECT 
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

-- Export all RLS policies
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public';

-- Export all functions
SELECT 
  routine_name,
  routine_definition
FROM information_schema.routines
WHERE routine_schema = 'public';

-- Export all triggers
SELECT 
  trigger_name,
  event_manipulation,
  event_object_table,
  action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public';

🔧 Migration Files

Migration files are named with timestamps: YYYYMMDDHHMMSS_description.sql

Example: 20240101120000_initial_schema.sql

Current Migrations

1. Initial Schema - Creates all tables
2. RLS Policies - Enables Row Level Security
3. Triggers - Auto-profile creation, updated_at timestamps
4. Functions - Helper functions for security
5. Storage Setup - Image storage bucket and policies
6. Indexes - Performance optimizations

🔒 Security (RLS Policies)

All tables have Row Level Security (RLS) enabled. Users can only access their own data.

Key Policies:

• profiles: Users can only view/update their own profile
• journal_entries: Users can only access their own entries
• ai_analysis: Users can only see analysis for their own entries
• subscriptions: Users can only access their own subscription
• vision_boards: Users can only access their own boards
• user_preferences: Users can only access their own preferences

📊 Database Tables

Table	Purpose	RLS
profiles	User profiles	✅ Own profile only
templates	Journal templates	✅ Public read
journal_entries	Journal entries	✅ Owner only
ai_analysis	AI insights	✅ Own entries
subscriptions	Billing	✅ Own subscription
assessment_responses	Quiz results	✅ Own + anonymous
user_stats	Cached insights	✅ Own stats
user_preferences	User settings	✅ Own preferences
voice_journal_sessions	Voice sessions	✅ Own sessions
vision_boards	Vision boards	✅ Own boards
vision_board_items	Board items	✅ Own items
feedbacks	User feedback	✅ Own feedback
ai_generation_log	AI usage tracking	✅ Own logs

🗄️ Storage Buckets

• journal-images: User-uploaded journal images
  • RLS enabled
  • User-scoped paths: {userId}/filename.jpg
  • Max file size: 2MB

🔄 Triggers

1. handle_new_user - Auto-creates profile on signup
2. update_updated_at - Auto-updates updated_at timestamps
3. reset_insights_daily - Resets daily insight counters

📝 Functions

1. handle_new_user() - Secure profile creation trigger
2. update_updated_at() - Timestamp update trigger

🧪 Testing the Schema

After applying migrations:

-- Check all tables exist
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public'
ORDER BY table_name;

-- Check RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public';

-- Check policies exist
SELECT tablename, policyname 
FROM pg_policies 
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

📚 Additional Resources

• Supabase Documentation
• PostgreSQL Documentation
• RLS Best Practices

⚠️ Important Notes

• Never commit sensitive data - Migrations should not contain API keys or secrets
• Test migrations locally first - Use a test database before applying to production
• Backup before migrations - Always backup your database before running migrations
• Version control - Keep all migrations in version control for reproducibility

---

For questions or issues, see the main CONTRIBUTING.md guide.