Skip to content

DATABASE.md

Latest commit

 

History

History
206 lines (150 loc) · 4.71 KB

DATABASE.md

File metadata and controls

206 lines (150 loc) · 4.71 KB

Database Schema

Complete database schema for Calmlytic with RLS policies.

Tables

profiles

User profile information linked to Supabase Auth.

Fields:

  • id (uuid) - References auth.users
  • full_name (text, nullable)
  • avatar_url (text, nullable)
  • plan (text) - 'free' or 'pro'
  • created_at (timestamptz)

RLS: Users can view/update their own profile only

templates

Journal templates for structured journaling.

Fields:

  • id (uuid)
  • title (text)
  • description (text, nullable)
  • content_md (text) - Markdown template
  • category (text, nullable)
  • is_public (boolean)
  • plan_access (text) - 'free' or 'pro'
  • created_by (uuid, nullable)
  • created_at (timestamptz)

RLS: Public read; creators manage their own

journal_entries

User journal entries with metadata.

Fields:

  • id (uuid)
  • user_id (uuid)
  • template_id (uuid, nullable)
  • title (text, nullable)
  • content_md (text)
  • tags (text[], nullable)
  • sentiment (text, nullable)
  • created_at (timestamptz)
  • updated_at (timestamptz)

RLS: Full CRUD access restricted to entry owner

ai_analysis

AI-generated insights for journal entries.

Fields:

  • id (uuid)
  • entry_id (uuid)
  • sentiment (text, nullable)
  • summary (text, nullable)
  • keywords (text[], nullable)
  • tone (text, nullable)
  • emotion_score (jsonb, nullable)
  • ai_version (text)
  • created_at (timestamptz)

RLS: Users can only view analysis for their own entries

subscriptions

User subscription and payment information.

Fields:

  • id (uuid)
  • user_id (uuid)
  • plan (text) - 'free' or 'pro'
  • status (text) - 'active', 'canceled', 'trialing'
  • stripe_customer_id (text, nullable)
  • stripe_subscription_id (text, nullable)
  • started_at (timestamptz)
  • ends_at (timestamptz, nullable)

RLS: Users can only access their own subscription

assessment_responses

Quiz/assessment responses and results.

Fields:

  • id (uuid)
  • user_id (uuid, nullable) - null for anonymous
  • session_id (uuid, nullable)
  • assessment_type (text)
  • responses (jsonb)
  • result (jsonb, nullable)
  • created_at (timestamptz)

RLS: Users see their own; anonymous submissions allowed

user_stats

Cached AI-generated insights with simplified metrics.

Fields:

  • user_id (uuid, primary key)
  • cached_insights (jsonb) - Simplified wellness metrics and insights
  • insights_generated_at (timestamptz)
  • insights_count_today (int) - Rate limiting
  • last_reset_date (date) - Auto-resets daily
  • total_insights_generated (int)
  • last_analyzed_entry_id (uuid)
  • created_at (timestamptz)
  • updated_at (timestamptz)

RLS: Users can only access their own stats

Purpose: Stores simplified wellness metrics (mood trend, emotion, wellness score, energy level, word of the week, reflection) for fast dashboard loading

Helper Functions

Organized by table in /src/lib/db/:

File Functions Purpose
profile.ts 4 User profile management
journal.ts 8 Journal CRUD + search + stats
template.ts 7 Template management
ai-analysis.ts 6 AI entry analysis
insights.ts 6 Cached insights & analytics
assessment.ts 6 Quiz responses
subscription.ts 7 Subscription & billing
user-preferences.ts 6 User preferences

Import from: @/lib/db or @/types

Migrations

  1. create_calmlytic_schema - Initial tables
  2. drop_mood_from_journal_entries - Removed mood field
  3. enable_rls_and_policies - RLS policies
  4. create_profile_on_signup_trigger - Auto profile creation
  5. allow_profile_insert_on_signup - Signup RLS
  6. fix_handle_new_user_security - Secure function
  7. setup_journal_images_storage - Image storage
  8. create_user_stats_table - AI insights caching

Storage

Bucket: journal-images (public with RLS)

Structure: {userId}/filename.jpg

RLS: User-scoped access only

Import from: @/lib/storage

File Structure

Database Helpers:

  • profile.ts - User profiles
  • journal.ts - Journal entries
  • template.ts - Journal templates
  • ai-analysis.ts - Entry analysis
  • insights.ts - Cached insights
  • assessment.ts - Quiz responses
  • subscription.ts - Billing
  • user-preferences.ts - User settings
  • index.ts - Central exports

AI Services:

  • gemini.ts - Entry analysis (ChatGPT/Gemini)
  • insights.ts - Insights generation

Storage:

  • image-upload.ts - Journal images

Supabase:

  • client.ts - Client-side
  • server.ts - Server-side
  • middleware.ts - Auth middleware

Type Safety

All types defined in /src/types/database.types.ts

Import: @/types/database.types or @/types