​

Database Schema v1.0.0

​

Overview

• Multi-tenant user data isolation via Row Level Security (RLS)
• Freemium subscription model (50 photos/month free, unlimited premium)
• Full-text search on journal entries
• Emotion tagging (12-emotion taxonomy)
• AI prompt generation and selection tracking
• Apple In-App Purchase integration

​

Schema Diagram

┌─────────────────┐
│   auth.users    │ (Supabase Auth - managed)
│  (id, email)    │
└────────┬────────┘
         │
         │ 1:1
         ▼
┌─────────────────────────────────────────────────┐
│              public.users                       │
├─────────────────────────────────────────────────┤
│ PK  id (UUID, FK → auth.users)                  │
│     email (TEXT, UNIQUE)                        │
│     display_name (TEXT)                         │
│     bio (TEXT, nullable)                        │
│     profile_photo_url (TEXT, nullable)          │
│     subscription_tier (TEXT: free|premium)      │
│     subscription_status (TEXT, nullable)        │
│     monthly_photo_count (INTEGER, default 0)    │
│     last_photo_reset_date (TIMESTAMPTZ)         │
│     created_at, updated_at, deleted_at          │
└────────┬────────────────────────────────────────┘
         │
         │ 1:N
         ▼
┌─────────────────────────────────────────────────┐
│         public.journal_entries                  │
├─────────────────────────────────────────────────┤
│ PK  id (UUID)                                   │
│ FK  user_id (UUID → users.id)                   │
│     photo_url (TEXT)                            │
│     photo_width, photo_height (INTEGER)         │
│     photo_size_bytes (INTEGER)                  │
│     journal_text (TEXT)                         │
│     ai_prompt_used (TEXT, nullable)             │
│     location_name (TEXT, nullable)              │
│     latitude, longitude (DECIMAL, nullable)     │
│     photo_taken_at (TIMESTAMPTZ, nullable)      │
│     search_vector (TSVECTOR, generated)         │
│     created_at, updated_at                      │
└────┬────────────────────────────┬───────────────┘
     │                            │
     │ 1:N                        │ N:M
     ▼                            ▼
┌──────────────────┐      ┌─────────────────────┐
│  ai_prompts      │      │  entry_emotions     │
├──────────────────┤      ├─────────────────────┤
│ PK id (UUID)     │      │ PK entry_id (FK)    │
│ FK entry_id      │      │ PK emotion_id (FK)  │
│    prompt_text   │      │    created_at       │
│    was_selected  │      └──────────┬──────────┘
│    created_at    │                 │
└──────────────────┘                 │ N:1
                                     ▼
                             ┌──────────────────┐
                             │   emotions       │
                             ├──────────────────┤
                             │ PK id (UUID)     │
                             │    name (UNIQUE) │
                             │    category      │
                             │    color_hex     │
                             │    icon_name     │
                             │    display_order │
                             │    created_at    │
                             └──────────────────┘

┌─────────────────────────────────────────────────┐
│           public.subscriptions                  │
├─────────────────────────────────────────────────┤
│ PK  id (UUID)                                   │
│ FK  user_id (UUID → users.id)                   │
│     plan (TEXT: monthly|annual)                 │
│     status (TEXT)                               │
│     trial_start_date, trial_end_date            │
│     current_period_start, current_period_end    │
│     cancelled_at                                │
│     apple_receipt_data (TEXT)                   │
│     apple_transaction_id (TEXT, UNIQUE)         │
│     apple_original_transaction_id (TEXT)        │
│     created_at, updated_at                      │
└─────────────────────────────────────────────────┘

​

Tables

​

1. users (extends auth.users)

• id (UUID, PK, FK → auth.users): User identifier
• email (TEXT, NOT NULL, UNIQUE): User email address
• display_name (TEXT, NOT NULL): User’s display name
• bio (TEXT, nullable): User biography
• profile_photo_url (TEXT, nullable): URL to profile photo in Supabase Storage
• subscription_tier (TEXT, NOT NULL, DEFAULT ‘free’): ‘free’ or ‘premium’
• subscription_status (TEXT, nullable): ‘active’, ‘trial’, ‘cancelled’, ‘expired’
• trial_start_date (TIMESTAMPTZ, nullable): Trial period start
• trial_end_date (TIMESTAMPTZ, nullable): Trial period end
• subscription_start_date (TIMESTAMPTZ, nullable): Subscription start
• subscription_end_date (TIMESTAMPTZ, nullable): Subscription end
• monthly_photo_count (INTEGER, NOT NULL, DEFAULT 0): Current month’s photo count
• last_photo_reset_date (TIMESTAMPTZ, NOT NULL, DEFAULT NOW()): Last reset timestamp
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())
• updated_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())
• deleted_at (TIMESTAMPTZ, nullable): Soft delete timestamp

• idx_users_email on email
• idx_users_subscription_tier on subscription_tier
• idx_users_deleted_at on deleted_at WHERE deleted_at IS NULL

• Photo count resets monthly (1st of month via cron job)
• Soft delete: deleted_at NULL = active, non-NULL = deleted (30-day recovery)
• Free tier: max 50 photos/month
• Premium tier: unlimited photos

​

2. journal_entries

• id (UUID, PK, DEFAULT gen_random_uuid())
• user_id (UUID, NOT NULL, FK → users.id ON DELETE CASCADE)
• photo_url (TEXT, NOT NULL): URL to photo in Supabase Storage
• photo_width (INTEGER, NOT NULL): Photo width in pixels
• photo_height (INTEGER, NOT NULL): Photo height in pixels
• photo_size_bytes (INTEGER, NOT NULL): Photo file size
• journal_text (TEXT, NOT NULL): User’s journal entry text
• ai_prompt_used (TEXT, nullable): The AI prompt user selected
• location_name (TEXT, nullable): Location name (e.g., “San Francisco, CA”)
• latitude (DECIMAL(10, 8), nullable): GPS latitude
• longitude (DECIMAL(11, 8), nullable): GPS longitude
• photo_taken_at (TIMESTAMPTZ, nullable): When photo was taken (from EXIF)
• search_vector (TSVECTOR, GENERATED ALWAYS): Full-text search index
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())
• updated_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())

• idx_journal_entries_user_id on user_id
• idx_journal_entries_created_at on created_at DESC
• idx_journal_entries_photo_taken_at on photo_taken_at DESC
• idx_journal_entries_user_created (composite) on (user_id, created_at DESC)
• idx_journal_entries_search_vector (GIN) on search_vector

• search_vector auto-generated from journal_text + ai_prompt_used
• Photo limit enforced by trigger (check users.monthly_photo_count)
• Deleting entry cascades to entry_emotions and ai_prompts

​

3. emotions

• id (UUID, PK, DEFAULT gen_random_uuid())
• name (TEXT, NOT NULL, UNIQUE): Emotion name
• category (TEXT, NOT NULL): ‘positive’, ‘reflective’, or ‘challenging’
• color_hex (TEXT, NOT NULL): Hex color code (e.g., ‘#FF6B6B’)
• icon_name (TEXT, NOT NULL): Icon identifier (SF Symbols or custom)
• display_order (INTEGER, NOT NULL): Sort order for UI display
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())

• idx_emotions_category on category
• idx_emotions_display_order on display_order

• Positive: Happy, Grateful, Excited, Peaceful
• Reflective: Thoughtful, Nostalgic, Proud, Loving
• Challenging: Sad, Anxious, Frustrated, Hopeful

​

4. entry_emotions

• entry_id (UUID, PK, FK → journal_entries.id ON DELETE CASCADE)
• emotion_id (UUID, PK, FK → emotions.id ON DELETE CASCADE)
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())

• idx_entry_emotions_entry_id on entry_id
• idx_entry_emotions_emotion_id on emotion_id

• One entry can have multiple emotions (no hard limit, app enforces max 12)
• Deleting entry cascades delete of associated emotions

​

5. ai_prompts

• id (UUID, PK, DEFAULT gen_random_uuid())
• entry_id (UUID, NOT NULL, FK → journal_entries.id ON DELETE CASCADE)
• prompt_text (TEXT, NOT NULL): The AI-generated prompt
• was_selected (BOOLEAN, NOT NULL, DEFAULT false): User chose this prompt
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())

• idx_ai_prompts_entry_id on entry_id

• 3 prompts generated per entry (before user selection)
• Max 1 prompt marked was_selected = true per entry
• Used for analytics (prompt popularity, AI quality tracking)

​

6. subscriptions

• id (UUID, PK, DEFAULT gen_random_uuid())
• user_id (UUID, NOT NULL, FK → users.id ON DELETE CASCADE)
• plan (TEXT, NOT NULL): ‘monthly’ or ‘annual’
• status (TEXT, NOT NULL): ‘trial’, ‘active’, ‘cancelled’, ‘expired’, ‘grace_period’, ‘billing_retry’
• trial_start_date (TIMESTAMPTZ, nullable)
• trial_end_date (TIMESTAMPTZ, nullable)
• current_period_start (TIMESTAMPTZ, NOT NULL)
• current_period_end (TIMESTAMPTZ, NOT NULL)
• cancelled_at (TIMESTAMPTZ, nullable)
• apple_receipt_data (TEXT, nullable): Base64-encoded receipt
• apple_transaction_id (TEXT, UNIQUE, nullable): Apple transaction ID
• apple_original_transaction_id (TEXT, nullable): Original transaction ID
• created_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())
• updated_at (TIMESTAMPTZ, NOT NULL, DEFAULT NOW())

• idx_subscriptions_user_id on user_id
• idx_subscriptions_status on status
• idx_subscriptions_apple_transaction_id on apple_transaction_id

• Receipt validation happens server-side (backend verifies with Apple)
• Status transitions: trial → active → cancelled → expired
• Grace period: Apple retrying failed payment
• Billing retry: Payment issue, subscription still temporarily active

​

Functions & Triggers

​

1. check_photo_limit()

1. Get user’s subscription_tier and monthly_photo_count
2. If premium user, allow insert
3. If free user with count >= 50, raise exception
4. Otherwise, increment monthly_photo_count and allow insert

​

2. reset_monthly_photo_counts()

1. Find all users where last_photo_reset_date is in previous month
2. Set monthly_photo_count = 0
3. Set last_photo_reset_date = NOW()

​

3. update_updated_at_column()

​

Row Level Security (RLS) Policies

​

users

• SELECT: Users can view own profile (auth.uid() = id)
• UPDATE: Users can update own profile (auth.uid() = id)

​

journal_entries

• SELECT: Users can view own entries (auth.uid() = user_id)
• INSERT: Users can create own entries (auth.uid() = user_id)
• UPDATE: Users can update own entries (auth.uid() = user_id)
• DELETE: Users can delete own entries (auth.uid() = user_id)

​

emotions

• SELECT: Publicly readable (all authenticated users can view)

​

entry_emotions

• SELECT: Users can view emotions for own entries
• INSERT: Users can add emotions to own entries
• DELETE: Users can remove emotions from own entries

​

ai_prompts

• SELECT: Users can view prompts for own entries
• INSERT: Users can create prompts for own entries (via backend)

​

subscriptions

• SELECT: Users can view own subscriptions (auth.uid() = user_id)
• INSERT/UPDATE: Only backend can modify (receipt validation)

​

Performance Targets

​

Storage Schema (Supabase Storage)

journal-photos/
├── {user_id}/
│   ├── {entry_id}_original.jpg    (max 4MB)
│   ├── {entry_id}_thumbnail.jpg   (400×400px)
│   └── {entry_id}_preview.jpg     (1024×1024px)

• Users can upload/view/delete only in own folder ({user_id}/)
• Authenticated access only

​

Migration Strategy

1. 20250111000001_create_users_table.sql
2. 20250111000002_create_journal_entries_table.sql
3. 20250111000003_create_emotions_tables.sql
4. 20250111000004_create_ai_prompts_table.sql
5. 20250111000005_create_subscriptions_table.sql
6. 20250111000006_create_functions_and_triggers.sql
7. 20250111000007_create_storage_bucket_and_policies.sql

​

Security Considerations

1. RLS Policies: All user data isolated by auth.uid()
2. Soft Delete: 30-day recovery period for user accounts
3. No Secrets: Connection strings in environment variables
4. Receipt Validation: Server-side Apple IAP verification
5. Input Validation: Check constraints on enums, foreign keys enforced
6. Rate Limiting: Handled at application/API gateway layer

​

Related Documentation

• PRD
• System Architecture
• Tech Stack
• DBA Handoff

• v1.0.0 (2025-01-11): Initial schema design