Database Schema v1.0.0

Database: Supabase PostgreSQL 15+ Project: AI-Powered Photo Journaling iOS App Created: 2025-01-11 Status: Implementation in progress

Overview

This schema supports an AI-powered photo journaling iOS app with:

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)

Stores user profiles and subscription state. Columns:

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

Indexes:

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

Business Rules:

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

Core table storing all photo journal entries. Columns:

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())

Indexes:

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

Business Rules:

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

Reference table for 12-emotion taxonomy (read-only). Columns:

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())

Indexes:

idx_emotions_category on category
idx_emotions_display_order on display_order

Seed Data: 12 emotions across 3 categories

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

4. entry_emotions

Junction table linking entries to emotions (many-to-many). Columns:

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())

Composite Primary Key: (entry_id, emotion_id) Indexes:

idx_entry_emotions_entry_id on entry_id
idx_entry_emotions_emotion_id on emotion_id

Business Rules:

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

5. ai_prompts

Stores AI-generated prompts for entries (3 prompts per entry). Columns:

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())

Indexes:

idx_ai_prompts_entry_id on entry_id

Business Rules:

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

Tracks Premium subscriptions and Apple IAP receipts. Columns:

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())

Indexes:

idx_subscriptions_user_id on user_id
idx_subscriptions_status on status
idx_subscriptions_apple_transaction_id on apple_transaction_id

Business Rules:

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()

Purpose: Enforce 50 photos/month limit for free users Trigger: BEFORE INSERT on journal_entries Logic:

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

2. reset_monthly_photo_counts()

Purpose: Reset photo counts on 1st of each month Schedule: Cron job (daily at 12:01 AM UTC on 1st of month) Logic:

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

3. update_updated_at_column()

Purpose: Auto-update updated_at timestamp on row changes Triggers: BEFORE UPDATE on users, journal_entries, subscriptions Logic: Set NEW.updated_at = NOW()

Row Level Security (RLS) Policies

All tables have RLS enabled. Policies enforce multi-tenant data isolation.

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

Query Type	Target	Optimization
Timeline (20 entries)	< 100ms	Composite index `(user_id, created_at)`
Search with filters	< 500ms	GIN index on `search_vector`
Entry detail	< 50ms	Primary key lookup
Photo limit check	< 10ms	Trigger function, app caching

Storage Schema (Supabase Storage)

Bucket: journal-photos Structure:

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

Policies:

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

Migration Strategy

Sequential numbered migrations:

20250111000001_create_users_table.sql
20250111000002_create_journal_entries_table.sql
20250111000003_create_emotions_tables.sql
20250111000004_create_ai_prompts_table.sql
20250111000005_create_subscriptions_table.sql
20250111000006_create_functions_and_triggers.sql
20250111000007_create_storage_bucket_and_policies.sql

Security Considerations

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

Version History:

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

​Database Schema v1.0.0

​Overview

​Schema Diagram

​Tables

​1. users (extends auth.users)

​2. journal_entries

​3. emotions

​4. entry_emotions

​5. ai_prompts

​6. subscriptions

​Functions & Triggers

​1. check_photo_limit()

​2. reset_monthly_photo_counts()

​3. update_updated_at_column()

​Row Level Security (RLS) Policies

​users

​journal_entries

​emotions

​entry_emotions

​ai_prompts

​subscriptions

​Performance Targets

​Storage Schema (Supabase Storage)

​Migration Strategy

​Security Considerations

​Related Documentation

Database Schema v1.0.0

Overview

Schema Diagram

Tables

1. users (extends auth.users)

2. journal_entries

3. emotions

4. entry_emotions

5. ai_prompts

6. subscriptions

Functions & Triggers

1. check_photo_limit()

2. reset_monthly_photo_counts()

3. update_updated_at_column()

Row Level Security (RLS) Policies

users

journal_entries

emotions

entry_emotions

ai_prompts

subscriptions

Performance Targets

Storage Schema (Supabase Storage)

Migration Strategy

Security Considerations

Related Documentation