DBA Handoff Summary

AI-Powered Photo Journaling iOS App - MVP Q1 2025

Project: AI-Powered Photo Journaling iOS App Timeline: 12-week MVP (Q1 2025) Your Phase: Database Schema Design & Implementation Tech Stack: Supabase PostgreSQL (500MB free tier) Start Date: Ready to begin immediately Target Completion: Week 6 (Feb 4, 2025)

Executive Summary

You are responsible for designing and implementing the complete database schema for an AI-powered photo journaling iOS app built with Flutter. The app uses Supabase PostgreSQL as the primary database, with Supabase Storage for photos and Supabase Auth for authentication. Core Entities:

Users (authentication, profiles, subscriptions)
Journal Entries (photos, text, emotions, AI prompts, metadata)
Emotions (12-emotion taxonomy)
Subscriptions (freemium model: 50 photos/month free, unlimited premium)

Key Requirements:

Multi-tenancy (user data isolation)
Row Level Security (RLS) policies for all tables
Photo limit enforcement (50/month free tier)
Search optimization (full-text search on journal entries)
Performance targets (< 500ms query response, 60fps timeline scrolling)

Tech Stack

Database: Supabase PostgreSQL 15+ Version Control: Supabase Migrations (SQL files in supabase/migrations/) ORM: None (direct SQL with Supabase Flutter client) Storage: Supabase Storage (1GB free tier for photos) Auth: Supabase Auth (JWT-based) MCP Integration: You have access to Supabase MCP tools for database operations:

mcp__supabase__list_tables, mcp__supabase__execute_sql
mcp__supabase__list_migrations, mcp__supabase__apply_migration
mcp__supabase__list_projects, mcp__supabase__get_project

Database Schema Requirements

1. Users Table (`users`)

Extends Supabase Auth with profile data. Columns:

CREATE TABLE public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  bio TEXT,
  profile_photo_url TEXT,
  subscription_tier TEXT NOT NULL DEFAULT 'free' CHECK (subscription_tier IN ('free', 'premium')),
  subscription_status TEXT CHECK (subscription_status IN ('active', 'trial', 'cancelled', 'expired')),
  trial_start_date TIMESTAMPTZ,
  trial_end_date TIMESTAMPTZ,
  subscription_start_date TIMESTAMPTZ,
  subscription_end_date TIMESTAMPTZ,
  monthly_photo_count INTEGER NOT NULL DEFAULT 0,
  last_photo_reset_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ -- Soft delete (30-day recovery period)
);

-- Indexes
CREATE INDEX idx_users_email ON public.users(email);
CREATE INDEX idx_users_subscription_tier ON public.users(subscription_tier);
CREATE INDEX idx_users_deleted_at ON public.users(deleted_at) WHERE deleted_at IS NULL;

-- RLS Policies
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own profile"
  ON public.users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can update own profile"
  ON public.users FOR UPDATE
  USING (auth.uid() = id);

Business Logic:

monthly_photo_count: Reset to 0 on first day of each month (cron job or function trigger)
last_photo_reset_date: Track when photo count was last reset
deleted_at: Soft delete (NULL = active, non-NULL = deleted, 30-day recovery window)

2. Journal Entries Table (`journal_entries`)

Core table storing all photo journal entries. Columns:

CREATE TABLE public.journal_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,

  -- Photo
  photo_url TEXT NOT NULL,
  photo_width INTEGER NOT NULL,
  photo_height INTEGER NOT NULL,
  photo_size_bytes INTEGER NOT NULL,

  -- Journal Content
  journal_text TEXT NOT NULL,
  ai_prompt_used TEXT, -- Which AI prompt the user selected (nullable if manual entry)

  -- Metadata
  location_name TEXT, -- e.g., "San Francisco, CA"
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),

  -- Timestamps
  photo_taken_at TIMESTAMPTZ, -- When photo was taken (from EXIF or user-selected)
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Full-text search
  search_vector TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('english', COALESCE(journal_text, '') || ' ' || COALESCE(ai_prompt_used, ''))
  ) STORED
);

-- Indexes
CREATE INDEX idx_journal_entries_user_id ON public.journal_entries(user_id);
CREATE INDEX idx_journal_entries_created_at ON public.journal_entries(created_at DESC);
CREATE INDEX idx_journal_entries_photo_taken_at ON public.journal_entries(photo_taken_at DESC);
CREATE INDEX idx_journal_entries_search_vector ON public.journal_entries USING GIN(search_vector);

-- Composite index for timeline queries (most common query pattern)
CREATE INDEX idx_journal_entries_user_created ON public.journal_entries(user_id, created_at DESC);

-- RLS Policies
ALTER TABLE public.journal_entries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own entries"
  ON public.journal_entries FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own entries"
  ON public.journal_entries FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own entries"
  ON public.journal_entries FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own entries"
  ON public.journal_entries FOR DELETE
  USING (auth.uid() = user_id);

Business Logic:

search_vector: Full-text search on journal_text + ai_prompt_used
Photo limit enforcement: Check users.monthly_photo_count before INSERT (function trigger)
photo_taken_at: Defaults to created_at if not provided (e.g., user doesn’t have EXIF data)

3. Emotions Table (`emotions`)

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

CREATE TABLE public.emotions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL CHECK (category IN ('positive', 'reflective', 'challenging')),
  color_hex TEXT NOT NULL, -- e.g., '#FF6B6B'
  icon_name TEXT NOT NULL, -- e.g., 'happy', 'grateful' (maps to SF Symbols or custom icons)
  display_order INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed data (12 emotions)
INSERT INTO public.emotions (name, category, color_hex, icon_name, display_order) VALUES
  ('Happy', 'positive', '#FDCB6E', 'happy', 1),
  ('Grateful', 'positive', '#00B894', 'grateful', 2),
  ('Excited', 'positive', '#FF6B6B', 'excited', 3),
  ('Peaceful', 'positive', '#74B9FF', 'peaceful', 4),
  ('Thoughtful', 'reflective', '#A29BFE', 'thoughtful', 5),
  ('Nostalgic', 'reflective', '#FD79A8', 'nostalgic', 6),
  ('Proud', 'reflective', '#FFEAA7', 'proud', 7),
  ('Loving', 'reflective', '#FF7675', 'loving', 8),
  ('Sad', 'challenging', '#636E72', 'sad', 9),
  ('Anxious', 'challenging', '#D63031', 'anxious', 10),
  ('Frustrated', 'challenging', '#E17055', 'frustrated', 11),
  ('Hopeful', 'challenging', '#55EFC4', 'hopeful', 12);

-- Index
CREATE INDEX idx_emotions_category ON public.emotions(category);
CREATE INDEX idx_emotions_display_order ON public.emotions(display_order);

-- RLS Policy (public read-only)
ALTER TABLE public.emotions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Emotions are publicly readable"
  ON public.emotions FOR SELECT
  USING (true);

4. Entry Emotions Table (`entry_emotions`)

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

CREATE TABLE public.entry_emotions (
  entry_id UUID NOT NULL REFERENCES public.journal_entries(id) ON DELETE CASCADE,
  emotion_id UUID NOT NULL REFERENCES public.emotions(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (entry_id, emotion_id)
);

-- Indexes
CREATE INDEX idx_entry_emotions_entry_id ON public.entry_emotions(entry_id);
CREATE INDEX idx_entry_emotions_emotion_id ON public.entry_emotions(emotion_id);

-- RLS Policies
ALTER TABLE public.entry_emotions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own entry emotions"
  ON public.entry_emotions FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.journal_entries je
      WHERE je.id = entry_emotions.entry_id
      AND je.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can insert own entry emotions"
  ON public.entry_emotions FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.journal_entries je
      WHERE je.id = entry_emotions.entry_id
      AND je.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can delete own entry emotions"
  ON public.entry_emotions FOR DELETE
  USING (
    EXISTS (
      SELECT 1 FROM public.journal_entries je
      WHERE je.id = entry_emotions.entry_id
      AND je.user_id = auth.uid()
    )
  );

5. AI Prompts Table (`ai_prompts`)

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

CREATE TABLE public.ai_prompts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entry_id UUID NOT NULL REFERENCES public.journal_entries(id) ON DELETE CASCADE,
  prompt_text TEXT NOT NULL,
  was_selected BOOLEAN NOT NULL DEFAULT false, -- Did user select this prompt?
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_ai_prompts_entry_id ON public.ai_prompts(entry_id);

-- RLS Policies
ALTER TABLE public.ai_prompts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own prompts"
  ON public.ai_prompts FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.journal_entries je
      WHERE je.id = ai_prompts.entry_id
      AND je.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can insert own prompts"
  ON public.ai_prompts FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.journal_entries je
      WHERE je.id = ai_prompts.entry_id
      AND je.user_id = auth.uid()
    )
  );

Business Logic:

3 prompts generated per entry (stored before user selection)
was_selected: TRUE for the prompt user chose (max 1 per entry)
Used for analytics (which prompts are most popular, AI quality tracking)

6. Subscriptions Table (`subscriptions`)

Tracks Premium subscription details and Apple IAP receipts. Columns:

CREATE TABLE public.subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,

  -- Subscription Details
  plan TEXT NOT NULL CHECK (plan IN ('monthly', 'annual')),
  status TEXT NOT NULL CHECK (status IN ('trial', 'active', 'cancelled', 'expired', 'grace_period', 'billing_retry')),

  -- Dates
  trial_start_date TIMESTAMPTZ,
  trial_end_date TIMESTAMPTZ,
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancelled_at TIMESTAMPTZ,

  -- Apple IAP
  apple_receipt_data TEXT, -- Base64-encoded receipt
  apple_transaction_id TEXT UNIQUE,
  apple_original_transaction_id TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_subscriptions_user_id ON public.subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON public.subscriptions(status);
CREATE INDEX idx_subscriptions_apple_transaction_id ON public.subscriptions(apple_transaction_id);

-- RLS Policies
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own subscriptions"
  ON public.subscriptions FOR SELECT
  USING (auth.uid() = user_id);

-- Only backend can insert/update subscriptions (receipt validation)

Business Logic:

Receipt validation happens server-side (backend verifies with Apple)
status transitions: trial → active → cancelled → expired
grace_period: Apple retrying failed payment
billing_retry: Payment method issue, subscription still active for limited time

Database Functions & Triggers

1. Photo Limit Enforcement Function

Prevents free users from creating >50 entries per month.

CREATE OR REPLACE FUNCTION check_photo_limit()
RETURNS TRIGGER AS $$
DECLARE
  user_tier TEXT;
  user_photo_count INTEGER;
BEGIN
  -- Get user's subscription tier and current photo count
  SELECT subscription_tier, monthly_photo_count
  INTO user_tier, user_photo_count
  FROM public.users
  WHERE id = NEW.user_id;

  -- Premium users have no limit
  IF user_tier = 'premium' THEN
    RETURN NEW;
  END IF;

  -- Free users: check limit
  IF user_photo_count >= 50 THEN
    RAISE EXCEPTION 'Photo limit reached. Upgrade to Premium for unlimited photos.';
  END IF;

  -- Increment photo count
  UPDATE public.users
  SET monthly_photo_count = monthly_photo_count + 1
  WHERE id = NEW.user_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_photo_limit
  BEFORE INSERT ON public.journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION check_photo_limit();

2. Monthly Photo Count Reset Function

Resets monthly_photo_count to 0 on first day of each month (cron job).

CREATE OR REPLACE FUNCTION reset_monthly_photo_counts()
RETURNS void AS $$
BEGIN
  UPDATE public.users
  SET
    monthly_photo_count = 0,
    last_photo_reset_date = NOW()
  WHERE
    DATE_TRUNC('month', last_photo_reset_date) < DATE_TRUNC('month', NOW())
    AND deleted_at IS NULL;
END;
$$ LANGUAGE plpgsql;

-- Schedule with pg_cron (Supabase extension)
-- Run daily at 12:01 AM UTC
SELECT cron.schedule(
  'reset-monthly-photo-counts',
  '1 0 1 * *', -- First day of month, 12:01 AM
  $$SELECT reset_monthly_photo_counts()$$
);

3. Updated_At Trigger Function

Auto-updates updated_at timestamp on row changes.

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON public.users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_journal_entries_updated_at
  BEFORE UPDATE ON public.journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_subscriptions_updated_at
  BEFORE UPDATE ON public.subscriptions
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Search & Filter Queries

Full-Text Search on Journal Entries

-- Search by keyword (uses search_vector GIN index)
SELECT
  je.*,
  ts_rank(je.search_vector, to_tsquery('english', 'beach & sunset')) AS relevance
FROM public.journal_entries je
WHERE
  je.user_id = auth.uid()
  AND je.search_vector @@ to_tsquery('english', 'beach & sunset')
ORDER BY relevance DESC, je.created_at DESC
LIMIT 20;

Filter by Emotions (OR logic)

-- Find entries with ANY of the selected emotions
SELECT DISTINCT je.*
FROM public.journal_entries je
INNER JOIN public.entry_emotions ee ON je.id = ee.entry_id
INNER JOIN public.emotions e ON ee.emotion_id = e.id
WHERE
  je.user_id = auth.uid()
  AND e.name IN ('Happy', 'Grateful', 'Excited')
ORDER BY je.created_at DESC
LIMIT 20;

Filter by Date Range

-- Entries created within a date range
SELECT je.*
FROM public.journal_entries je
WHERE
  je.user_id = auth.uid()
  AND je.created_at BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY je.created_at DESC;

Combined Search + Filters

-- Search + Date Range + Emotions
SELECT DISTINCT je.*
FROM public.journal_entries je
LEFT JOIN public.entry_emotions ee ON je.id = ee.entry_id
LEFT JOIN public.emotions e ON ee.emotion_id = e.id
WHERE
  je.user_id = auth.uid()
  AND je.search_vector @@ to_tsquery('english', 'vacation')
  AND je.created_at BETWEEN '2024-06-01' AND '2024-08-31'
  AND (e.name IN ('Happy', 'Excited') OR e.name IS NULL)
ORDER BY je.created_at DESC
LIMIT 20;

Performance Requirements

Target Metrics:

Timeline query (20 entries): < 100ms
Search query: < 500ms
Entry detail query: < 50ms
Photo limit check: < 10ms (cached in application layer)

Optimization Strategies:

Composite indexes for common query patterns (idx_journal_entries_user_created)
GIN index for full-text search (idx_journal_entries_search_vector)
RLS policies optimized (avoid subqueries when possible)
Connection pooling (Supabase default: 15 connections)
Query result caching in Flutter app (local SQLite cache via sqflite)

Supabase Storage Schema

Bucket: journal-photos (public read, authenticated write) File Structure:

journal-photos/
├── {user_id}/
│   ├── {entry_id}_original.jpg    (original photo, max 4MB)
│   ├── {entry_id}_thumbnail.jpg   (400×400px, for timeline grid)
│   └── {entry_id}_preview.jpg     (1024×1024px, for detail view)

Storage Policies:

-- Users can upload to their own folder
CREATE POLICY "Users can upload own photos"
  ON storage.objects FOR INSERT
  WITH CHECK (
    bucket_id = 'journal-photos'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

-- Users can view own photos
CREATE POLICY "Users can view own photos"
  ON storage.objects FOR SELECT
  USING (
    bucket_id = 'journal-photos'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

-- Users can delete own photos
CREATE POLICY "Users can delete own photos"
  ON storage.objects FOR DELETE
  USING (
    bucket_id = 'journal-photos'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

Migration Strategy

Step 1: Create Migrations Directory

supabase init
supabase migration new create_schema

Step 2: Write Migration Files

Migration 1: 20250110000001_create_users_table.sql

-- Create users table (extends auth.users)
CREATE TABLE public.users (...);
-- Add indexes, RLS policies

Migration 2: 20250110000002_create_journal_entries_table.sql

-- Create journal_entries table
-- Add indexes, RLS policies, triggers

Migration 3: 20250110000003_create_emotions_tables.sql

-- Create emotions, entry_emotions tables
-- Seed emotions data

Migration 4: 20250110000004_create_ai_prompts_table.sql

-- Create ai_prompts table

Migration 5: 20250110000005_create_subscriptions_table.sql

-- Create subscriptions table

Migration 6: 20250110000006_create_functions_and_triggers.sql

-- Create all functions and triggers
-- Schedule cron jobs

Step 3: Apply Migrations

# Using Supabase MCP tools
mcp__supabase__apply_migration --project_id=<project_id> --name="create_users_table" --query="<SQL>"

# Or via Supabase CLI
supabase db push

Testing Checklist

Data Integrity Tests:

Users table: Soft delete sets deleted_at, does not cascade delete entries
Journal entries: Deleting entry cascades to entry_emotions and ai_prompts
Emotions: Seed data contains exactly 12 emotions
Entry emotions: Max 12 emotions per entry (no constraint, but app logic enforces)
Subscriptions: Apple transaction IDs are unique

RLS Policy Tests:

Users can only view/update their own profile
Users can only view/insert/update/delete their own journal entries
Users can view all emotions (public read-only)
Users can only manage emotions for their own entries
Users can only view their own subscriptions

Function/Trigger Tests:

Photo limit: Free users blocked at 51st entry
Photo limit: Premium users can create unlimited entries
Monthly reset: Photo count resets on 1st of month
Updated_at: Timestamp auto-updates on UPDATE

Performance Tests:

Timeline query (20 entries): < 100ms
Search query (keyword + filters): < 500ms
Entry detail query: < 50ms
Full-text search with GIN index works correctly

Storage Tests:

Users can upload photos to own folder
Users cannot access other users’ photos
Photo URLs are publicly readable (signed URLs or public bucket)

Handoff to Backend Developer

After database schema is complete, hand off to @backend-developer with:

✅ All migrations applied and tested
✅ Supabase project URL and anon key (for Flutter client)
✅ Database schema documentation (this document)
✅ Sample queries for common operations
✅ RLS policies verified and tested

Backend will implement:

API endpoints for journal entries CRUD
Photo upload to Supabase Storage
AI prompt generation (OpenAI GPT-5 integration)
Apple IAP receipt validation
Data export (JSON + ZIP of photos)

Linear Ticket References

Create Linear tickets for each migration:

OVE-XX: Database Schema Design (this document)
OVE-XX: Create Users Table Migration
OVE-XX: Create Journal Entries Table Migration
OVE-XX: Create Emotions Tables Migration
OVE-XX: Create AI Prompts Table Migration
OVE-XX: Create Subscriptions Table Migration
OVE-XX: Create Functions & Triggers Migration
OVE-XX: Test Database Performance
OVE-XX: Setup Supabase Storage Policies

Use linear-integration skill to update progress as you complete each migration.

Resources

Supabase Docs: https://supabase.com/docs
Supabase MCP Tools: Available in this environment
Database Schema Diagram: (TODO: Generate ERD diagram)
PRD: docs/product/prds/mvp-q1-2025-prd.md
Architecture: docs/architecture/system-design.md, docs/architecture/tech-stack.md

Questions? Reach out to @software-architect or @backend-developer for clarification. Ready to start! 🚀

Get Started

Workflow & Processes

Examples

DBA HANDOFF

DBA Handoff Summary

Executive Summary

Tech Stack

Database Schema Requirements

1. Users Table (`users`)

2. Journal Entries Table (`journal_entries`)

3. Emotions Table (`emotions`)

4. Entry Emotions Table (`entry_emotions`)

5. AI Prompts Table (`ai_prompts`)

6. Subscriptions Table (`subscriptions`)

Database Functions & Triggers

1. Photo Limit Enforcement Function

2. Monthly Photo Count Reset Function

3. Updated_At Trigger Function

Search & Filter Queries

Full-Text Search on Journal Entries

Filter by Emotions (OR logic)

Filter by Date Range

Combined Search + Filters

Performance Requirements

Supabase Storage Schema

Migration Strategy

Step 1: Create Migrations Directory

Step 2: Write Migration Files

Step 3: Apply Migrations

Testing Checklist

Data Integrity Tests:

RLS Policy Tests:

Function/Trigger Tests:

Performance Tests:

Storage Tests:

Handoff to Backend Developer

Linear Ticket References

Resources

DBA Handoff Summary

Executive Summary

Tech Stack

Database Schema Requirements

1. Users Table (users)

2. Journal Entries Table (journal_entries)

3. Emotions Table (emotions)

4. Entry Emotions Table (entry_emotions)

5. AI Prompts Table (ai_prompts)

6. Subscriptions Table (subscriptions)

Database Functions & Triggers

1. Photo Limit Enforcement Function

2. Monthly Photo Count Reset Function

3. Updated_At Trigger Function

Search & Filter Queries

Full-Text Search on Journal Entries

Filter by Emotions (OR logic)

Filter by Date Range

Combined Search + Filters

Performance Requirements

Supabase Storage Schema

Migration Strategy

Step 1: Create Migrations Directory

Step 2: Write Migration Files

Step 3: Apply Migrations

Testing Checklist

Data Integrity Tests:

RLS Policy Tests:

Function/Trigger Tests:

Performance Tests:

Storage Tests:

Handoff to Backend Developer

Linear Ticket References

Resources

1. Users Table (`users`)

2. Journal Entries Table (`journal_entries`)

3. Emotions Table (`emotions`)

4. Entry Emotions Table (`entry_emotions`)

5. AI Prompts Table (`ai_prompts`)

6. Subscriptions Table (`subscriptions`)