Database Schema
MyStoryFlow uses Supabase (PostgreSQL) as the primary database. This document outlines the complete database schema, relationships, and data models.
Overview
The database is designed to support:
- User Management - Authentication and user profiles
- Campaign Management - Story collection projects
- Story Storage - Text, audio, and multimedia content
- Family Collaboration - Multi-user participation
- Book Creation - Design and ordering system
Core Tables
Users (users)
Extends Supabase Auth users with additional profile information.
CREATE TABLE users (
id UUID PRIMARY KEY REFERENCES auth.users(id),
email TEXT UNIQUE NOT NULL,
name TEXT,
avatar_url TEXT,
subscription_status TEXT DEFAULT 'free',
subscription_expires_at TIMESTAMPTZ,
preferences JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `id` - UUID, references Supabase auth.users
- `email` - User's email address
- `name` - Display name
- `avatar_url` - Profile photo URL
- `subscription_status` - `free`, `premium`, `enterprise`
- `preferences` - JSON object for user settings
### Campaigns (`campaigns`)
Story collection projects organized around a specific person or theme.
```sql
CREATE TABLE campaigns (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
storyteller_name TEXT,
relationship_type TEXT,
status TEXT DEFAULT 'active',
goal_stories INTEGER DEFAULT 10,
total_prompts INTEGER DEFAULT 0,
used_prompts INTEGER DEFAULT 0,
completion_percentage INTEGER DEFAULT 0,
started_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `user_id` - Campaign owner (creator)
- `title` - Campaign name (e.g., "Dad's Life Stories")
- `storyteller_name` - Person the stories are about
- `relationship_type` - `parent`, `grandparent`, `spouse`, `other`
- `status` - `active`, `completed`, `paused`, `archived`
- `goal_stories` - Target number of stories to collect
**Indexes:**
```sql
CREATE INDEX idx_campaigns_user_id ON campaigns(user_id);
CREATE INDEX idx_campaigns_status ON campaigns(status);
``` -->
### Stories (`stories`)
Individual stories within campaigns.
```sql
CREATE TABLE stories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
title TEXT NOT NULL,
content TEXT,
word_count INTEGER,
category TEXT,
story_order INTEGER,
status TEXT DEFAULT 'draft',
audio_url TEXT,
audio_duration INTEGER,
transcription TEXT,
photos TEXT[] DEFAULT '{}',
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `campaign_id` - Parent campaign
- `title` - Story title
- `content` - Main story text
- `word_count` - Calculated word count
- `category` - `childhood`, `career`, `family`, `travel`, etc.
- `story_order` - Order within campaign for book creation
- `status` - `draft`, `completed`, `reviewed`
- `audio_url` - Voice recording URL
- `photos` - Array of photo URLs
- `metadata` - Additional story data (prompts, tags, etc.)
**Indexes:**
```sql
CREATE INDEX idx_stories_campaign_id ON stories(campaign_id);
CREATE INDEX idx_stories_status ON stories(status);
CREATE INDEX idx_stories_category ON stories(category);
``` -->
### Family Members (`family_members`)
Users invited to participate in campaigns.
```sql
CREATE TABLE family_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
email TEXT NOT NULL,
name TEXT,
relationship TEXT,
role TEXT DEFAULT 'contributor',
status TEXT DEFAULT 'invited',
invited_by UUID REFERENCES users(id),
invited_at TIMESTAMPTZ DEFAULT NOW(),
joined_at TIMESTAMPTZ,
permissions JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `campaign_id` - Campaign they're invited to
- `user_id` - Linked user account (null if not registered)
- `email` - Invitation email
- `relationship` - Relationship to storyteller
- `role` - `owner`, `contributor`, `viewer`
- `status` - `invited`, `active`, `inactive`
- `permissions` - JSON object for specific permissions
**Indexes:**
```sql
CREATE INDEX idx_family_members_campaign_id ON family_members(campaign_id);
CREATE INDEX idx_family_members_user_id ON family_members(user_id);
CREATE INDEX idx_family_members_email ON family_members(email);
``` -->
### Books (`books`)
Book creation and design information.
```sql
CREATE TABLE books (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id UUID NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id),
title TEXT NOT NULL,
template TEXT DEFAULT 'classic',
cover_design TEXT,
status TEXT DEFAULT 'draft',
page_count INTEGER DEFAULT 0,
story_count INTEGER DEFAULT 0,
photo_count INTEGER DEFAULT 0,
design_data JSONB DEFAULT '{}',
preview_url TEXT,
pdf_url TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `campaign_id` - Source campaign
- `template` - Design template (`classic`, `modern`, `photo-heavy`)
- `cover_design` - Cover design type
- `status` - `draft`, `ready_to_order`, `ordered`, `completed`
- `design_data` - JSON object with layout and styling info
- `preview_url` - Preview image URL
- `pdf_url` - Generated PDF URL
### Book Orders (`book_orders`)
Physical book orders and fulfillment.
```sql
CREATE TABLE book_orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
book_id UUID NOT NULL REFERENCES books(id),
user_id UUID NOT NULL REFERENCES users(id),
quantity INTEGER NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2),
total_price DECIMAL(10,2),
shipping_address JSONB NOT NULL,
book_options JSONB DEFAULT '{}',
status TEXT DEFAULT 'pending',
order_number TEXT UNIQUE,
tracking_number TEXT,
estimated_delivery DATE,
ordered_at TIMESTAMPTZ DEFAULT NOW(),
shipped_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ
);
``` -->
**Columns:**
- `book_options` - Size, binding, paper type, etc.
- `shipping_address` - JSON object with delivery address
- `status` - `pending`, `processing`, `printed`, `shipped`, `delivered`
- `order_number` - Human-readable order ID
### Photos (`photos`)
Photo storage and metadata.
```sql
CREATE TABLE photos (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
story_id UUID REFERENCES stories(id) ON DELETE SET NULL,
campaign_id UUID REFERENCES campaigns(id) ON DELETE SET NULL,
filename TEXT NOT NULL,
original_filename TEXT,
url TEXT NOT NULL,
thumbnail_url TEXT,
size_bytes INTEGER,
width INTEGER,
height INTEGER,
mime_type TEXT,
caption TEXT,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
``` -->
**Columns:**
- `story_id` - Associated story (optional)
- `campaign_id` - Associated campaign (optional)
- `url` - Full-size image URL
- `thumbnail_url` - Optimized thumbnail URL
- `metadata` - EXIF data, upload info, etc.
## Relationships
### Entity Relationship Diagram
``` -->
┌─────────────────────────────────────────────────────────────────────────────────┐
│ Database Entity Relationships │
└─────────────────────────────────────────────────────────────────────────────────┘
┌─────────────┐ creates ┌─────────────┐ contains ┌─────────────┐
│ users │ ────────────── │ campaigns │ ────────────── │ stories │
│ │ 1 ∞ │ │ 1 ∞ │ │
│ - id │ │ - id │ │ - id │
│ - email │ │ - title │ │ - title │
│ - name │ │ - user_id │ │ - content │
└─────────────┘ └─────────────┘ └─────────────┘
│ │
│ includes │ includes
│ 1:∞ │ 1:∞
▼ ▼
┌─────────────┐ ┌─────────────┐
│family_members│ │ photos │
│ │ │ │
│ - id │ │ - id │
│ - user_id │◄──────────────┤│ - url │
│ - campaign_id│ references │ - story_id │
└─────────────┘ └─────────────┘
│
│ generates
│ 1:∞
▼
┌─────────────┐ ordered ┌─────────────┐
│ books │ ────────────── │ book_orders │
│ │ 1 ∞ │ │
│ - id │ │ - id │
│ - title │ │ - book_id │
│ - campaign_id│ │ - user_id │
└─────────────┘ └─────────────┘
``` -->
### Key Relationships
1. **Users → Campaigns** (1:many)
- Users can create multiple campaigns
- Each campaign has one owner
2. **Campaigns → Stories** (1:many)
- Campaigns contain multiple stories
- Stories belong to one campaign
3. **Campaigns → Family Members** (1:many)
- Campaigns can have multiple family participants
- Family members can participate in multiple campaigns
4. **Campaigns → Books** (1:many)
- Campaigns can generate multiple book versions
- Each book belongs to one campaign
5. **Stories → Photos** (1:many)
- Stories can include multiple photos
- Photos can be shared across stories
## Views and Functions
### Campaign Statistics View
Aggregated campaign data for dashboards.
```sql
CREATE VIEW campaign_stats AS
SELECT
c.id,
c.title,
c.goal_stories,
COUNT(s.id) as story_count,
SUM(s.word_count) as total_words,
ROUND((COUNT(s.id)::float / c.goal_stories) * 100) as completion_percentage,
MAX(s.updated_at) as latest_story_date
FROM campaigns c
LEFT JOIN stories s ON c.id = s.campaign_id
GROUP BY c.id, c.title, c.goal_stories;
``` -->
### Update Word Count Function
Automatically calculate word counts when stories are updated.
```sql
CREATE OR REPLACE FUNCTION update_story_word_count()
RETURNS TRIGGER AS $$
BEGIN
NEW.word_count = array_length(string_to_array(NEW.content, ' '), 1);
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_story_word_count_trigger
BEFORE UPDATE ON stories
FOR EACH ROW
EXECUTE FUNCTION update_story_word_count();
``` -->
## Security Policies (RLS)
Row Level Security policies ensure data privacy and access control.
### Campaigns Policy
```sql
-- Users can only access their own campaigns or campaigns they're invited to
CREATE POLICY "Users can access their campaigns" ON campaigns
FOR ALL USING (
user_id = auth.uid() OR
id IN (
SELECT campaign_id FROM family_members
WHERE user_id = auth.uid() AND status = 'active'
)
);
``` -->
### Stories Policy
```sql
-- Users can access stories in campaigns they have access to
CREATE POLICY "Users can access campaign stories" ON stories
FOR ALL USING (
campaign_id IN (
SELECT id FROM campaigns
WHERE user_id = auth.uid() OR
id IN (
SELECT campaign_id FROM family_members
WHERE user_id = auth.uid() AND status = 'active'
)
)
);
``` -->
### Family Members Policy
```sql
-- Users can see family members in campaigns they have access to
CREATE POLICY "Users can see family members" ON family_members
FOR SELECT USING (
campaign_id IN (
SELECT id FROM campaigns
WHERE user_id = auth.uid()
) OR user_id = auth.uid()
);
``` -->
## Migrations
### Migration Structure
``` -->
lib/database/migrations/
├── 001_create_users_table.sql
├── 002_create_campaigns_table.sql
├── 003_create_stories_table.sql
├── 004_create_family_members_table.sql
├── 005_create_books_table.sql
├── 006_create_photos_table.sql
├── 007_add_rls_policies.sql
└── 008_create_views_and_functions.sql
``` -->
### Running Migrations
```bash
# Apply all pending migrations
npm run db:migrate
# Create new migration
npm run db:migrate:create "add_new_feature"
# Rollback last migration
npm run db:migrate:rollback
``` -->
## Performance Considerations
### Indexing Strategy
- **Primary Keys**: All tables use UUID primary keys
- **Foreign Keys**: Indexed for join performance
- **Query Patterns**: Indexes on frequently filtered columns
- **Text Search**: Full-text search indexes on story content
### Query Optimization
```sql
-- Efficient campaign dashboard query
SELECT
c.*,
cs.story_count,
cs.total_words,
cs.completion_percentage
FROM campaigns c
LEFT JOIN campaign_stats cs ON c.id = cs.id
WHERE c.user_id = $1
ORDER BY c.updated_at DESC;
``` -->
### Connection Pooling
- Supabase handles connection pooling automatically
- Connection limits: 60 concurrent connections (free tier)
- Prepared statements for frequently executed queries
## Backup and Recovery
### Automated Backups
- **Daily Backups**: Automatic daily database backups
- **Point-in-Time Recovery**: 7-day recovery window
- **Cross-Region Replication**: Backup stored in multiple regions
### Data Export
```sql
-- Export user data (GDPR compliance)
SELECT
u.*,
json_agg(c.*) as campaigns,
json_agg(s.*) as stories
FROM users u
LEFT JOIN campaigns c ON u.id = c.user_id
LEFT JOIN stories s ON c.id = s.campaign_id
WHERE u.id = $1
GROUP BY u.id;
``` -->
---
## Development Tools
### Database GUI
- **Supabase Dashboard**: Web-based database management
- **pgAdmin**: Advanced PostgreSQL administration
- **TablePlus**: Modern database client
### Local Development
```bash
# Start local Supabase instance
npx supabase start
# Apply migrations
npx supabase db reset
# Generate TypeScript types
npx supabase gen types typescript --local > types/database.ts
``` -->
For more information on database setup and development, see the [Development Guide](/development).