MyStoryFlow Database Schema v1.1

Document Version: 1.1.1 Last Updated: 2025-11-30 Status: Production - Synchronized with actual database

Overview

This document describes the database schema for MyStoryFlow v1.1, implementing the Story = Chapter model where stories are directly referenced by books without an intermediate chapter layer.

Current vs Target State

Validated Database Status (Updated 2025-11-30)

Table/Feature	Status	Notes
`books` table	ACTIVE	Full KDP publishing features implemented
`books.story_ids UUID[]`	EXISTS	Story = Chapter model implemented
`stories` table	ACTIVE	Enhanced with collaboration, family, AI features
`stories.source_conversation_ids UUID[]`	EXISTS	Already implemented
`recordings` table	ACTIVE	Voice recording support with AI transcription
`conversations` table	ACTIVE	Base table for AI conversations
`ai_conversations`	VIEW	Points to conversations table (backward compatibility)
`campaigns` table	ACTIVE	Story collection projects
`book_chapters` table	DEPRECATED	Use stories directly via books.story_ids
Migration Status	COMPLETE	Story = Chapter model fully implemented

Schema Philosophy

Core Principles

Direct Relationships - User → Content (not User → Campaign → Content)
Story = Chapter - Stories ARE chapters; no intermediate layer
Artifacts Model - Voice/conversation preserved as metadata on stories
Array-Based Ordering - Books use story_ids UUID[] for chapter order

Data Ownership


┌─────────────┐
│    User     │
│ (profiles)  │
└──────┬──────┘
       │
       ├──────────────────────────────────────┐
       │                                      │
       ▼                                      ▼
┌─────────────┐                        ┌─────────────┐
│   Stories   │──────────────────────▶ │    Books    │
│  user_id    │                        │  user_id    │
│  (author)   │  referenced via        │ story_ids[] │
└─────────────┘  story_ids array       └─────────────┘
       │
       │ optional grouping
       ▼
┌─────────────┐
│  Campaigns  │
│ (Story      │
│  Projects)  │
└─────────────┘

Core Tables

profiles

User accounts and profile information. Extends the auth.users table.


CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name TEXT,
  subscription_tier TEXT DEFAULT 'free'
    CHECK (subscription_tier IN ('free', 'starter', 'family', 'premium')),
  stripe_customer_id TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Note: Email comes from auth.users table
-- Additional profile data may include:
-- - avatar_url (from auth.users metadata)
-- - timezone, preferences (stored in user settings tables)
-- - onboarding status (tracked separately)

stories

The primary content table. Stories ARE chapters when added to a book.


CREATE TABLE stories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  campaign_id UUID REFERENCES campaigns(id) ON DELETE SET NULL,
  family_group_id UUID REFERENCES family_groups(id),
 
  -- Content
  title TEXT NOT NULL,
  content TEXT NOT NULL,                 -- TipTap JSON/HTML
  pages JSONB,                           -- Multi-page support
  word_count INTEGER,
  page_count INTEGER,
  reading_time_minutes INTEGER,
 
  -- Source Artifacts (preserved for digital book)
  source_conversation_ids UUID[],        -- AI conversations used (IMPLEMENTED)
  -- source_recording_ids UUID[] would go here if needed for direct recording links
 
  -- Original legacy fields (from campaigns system)
  prompt TEXT,                           -- Original story prompt
  response_text TEXT,                    -- Legacy response field
  response_type TEXT,                    -- 'text' or 'voice'
 
  -- AI Enhancement
  ai_enhanced BOOLEAN,
  original_content TEXT,                 -- Preserved before AI enhancement
  enhancement_notes TEXT,
 
  -- Metadata
  tags TEXT[],
  themes TEXT[],
  category TEXT,
  settings JSONB,
  header_footer_settings JSONB,
  version_history JSONB,
 
  -- Collaboration
  last_edited_by UUID,
  collaboration_notes JSONB,
 
  -- Ordering and engagement
  story_order INTEGER,
  view_count INTEGER,
  engagement_score INTEGER,
  family_rating INTEGER,
 
  -- Status
  status TEXT CHECK (status IN ('draft', 'ready', 'published')),
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_stories_user_id ON stories(user_id);
CREATE INDEX idx_stories_campaign_id ON stories(campaign_id);
CREATE INDEX idx_stories_family_group_id ON stories(family_group_id);
CREATE INDEX idx_stories_source_conversation_ids ON stories USING GIN(source_conversation_ids);
CREATE INDEX idx_stories_tags ON stories USING GIN(tags);

books

Ordered collections of stories that become published books. In the v1.1 “Story = Chapter” model, books reference stories directly via the story_ids array.


CREATE TABLE books (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  campaign_id UUID REFERENCES campaigns(id) ON DELETE SET NULL,
  family_group_id UUID REFERENCES family_groups(id),
 
  -- Basic Information
  title TEXT NOT NULL,
  subtitle TEXT,
  author_name TEXT NOT NULL,
  contributor_names TEXT[],
  series_name TEXT,
  volume_number INTEGER,
  description TEXT,
  dedication TEXT,
 
  -- Content Structure (Story = Chapter model)
  story_ids UUID[],                      -- Ordered array of story IDs (stories ARE chapters)
  outline JSONB,                         -- Book outline structure
  chapters JSONB[],                      -- Chapter metadata (derived from stories)
  chapter_structure JSONB,               -- Additional chapter organization
  word_count INTEGER DEFAULT 0,
  page_count INTEGER,
  estimated_print_pages INTEGER,
 
  -- Publishing Metadata
  keywords TEXT[],
  categories TEXT[],
  language TEXT DEFAULT 'en',
 
  -- KDP/Amazon Publishing
  asin TEXT,                             -- Amazon ASIN
  isbn TEXT,
  isbn_paperback TEXT,
  isbn_ebook TEXT,
  kdp_status TEXT DEFAULT 'draft'
    CHECK (kdp_status IN ('draft', 'review', 'published', 'unpublished')),
  kdp_enrolled BOOLEAN DEFAULT false,
  kdp_select BOOLEAN DEFAULT false,
  publish_date TIMESTAMPTZ,
  publication_date TIMESTAMPTZ,
  price_usd DECIMAL(10,2),
  royalty_rate INTEGER CHECK (royalty_rate IN (35, 70)),
 
  -- Design and Templates
  cover_image_url TEXT,
  template_id UUID REFERENCES book_templates(id),
  cover_template_id UUID REFERENCES cover_templates(id),
  interior_template_id TEXT,
  trim_size TEXT CHECK (trim_size IN ('6x9', '5.5x8.5', '8x10', '8.5x11')),
  book_type TEXT DEFAULT 'memoir'
    CHECK (book_type IN ('memoir', 'biography', 'fiction', 'cookbook', 'family_history', 'poetry')),
  design_settings JSONB,
  digital_formats JSONB,
 
  -- Status and Progress
  status TEXT DEFAULT 'draft'
    CHECK (status IN ('draft', 'designing', 'ready', 'ordered', 'published')),
  completion_percentage INTEGER DEFAULT 0
    CHECK (completion_percentage >= 0 AND completion_percentage <= 100),
 
  -- AI Features
  ai_features_used TEXT[],
  ai_generation_metadata JSONB,
 
  -- Collaboration and Sharing
  collaborators TEXT[],
  contributor_notes JSONB,
  is_public BOOLEAN DEFAULT false,
  share_url TEXT,
 
  -- Output Files
  print_ready_pdf_url TEXT,
  download_count INTEGER,
  version INTEGER,
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_books_user_id ON books(user_id);
CREATE INDEX idx_books_campaign_id ON books(campaign_id);
CREATE INDEX idx_books_family_group_id ON books(family_group_id);
CREATE INDEX idx_books_story_ids ON books USING GIN(story_ids);
CREATE INDEX idx_books_status ON books(status);
CREATE INDEX idx_books_updated_at ON books(updated_at);

campaigns (Story Projects)

Grouping mechanism for stories with storyteller metadata. Campaigns represent story collection projects focused on a specific storyteller or theme.


CREATE TABLE campaigns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id),
 
  -- Project Info
  title TEXT,                            -- Campaign title/name
  description TEXT,
  storyteller_name TEXT NOT NULL,        -- Who is telling the stories
  storyteller_email TEXT,
  storyteller_phone TEXT,
 
  -- Storyteller Relationship
  relationship TEXT NOT NULL
    CHECK (relationship IN ('grandparent', 'parent', 'partner', 'other')),
 
  -- Progress Tracking
  prompts_sent INTEGER DEFAULT 0,
  stories_collected INTEGER DEFAULT 0,
 
  -- Status
  status TEXT DEFAULT 'active'
    CHECK (status IN ('active', 'paused', 'completed')),
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_campaigns_user_id ON campaigns(user_id);
CREATE INDEX idx_campaigns_status ON campaigns(status);

recordings

Voice recordings with transcription and AI analysis.


CREATE TABLE recordings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  campaign_id UUID REFERENCES campaigns(id) ON DELETE CASCADE,
 
  -- Basic Info
  title TEXT NOT NULL,
  description TEXT,
 
  -- Audio File
  audio_url TEXT NOT NULL,               -- Backblaze B2 URL
  audio_format TEXT DEFAULT 'webm',
  duration_seconds NUMERIC,
  file_size_bytes BIGINT,
  backblaze_file_id TEXT,                -- For direct Backblaze reference
 
  -- Transcription
  transcript TEXT,
  -- Note: transcript_segments JSONB would go here if word-level timestamps needed
 
  -- AI Enhancement
  ai_summary TEXT,                       -- AI-generated summary
  tags TEXT[] DEFAULT '{}' NOT NULL,     -- Categorization tags
 
  -- Status
  status TEXT DEFAULT 'processing'
    CHECK (status IN ('processing', 'completed', 'failed')),
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Indexes
CREATE INDEX idx_recordings_user_id ON recordings(user_id);
CREATE INDEX idx_recordings_campaign_id ON recordings(campaign_id);
CREATE INDEX idx_recordings_created_at ON recordings(created_at);
CREATE INDEX idx_recordings_status ON recordings(status);
CREATE INDEX idx_recordings_tags ON recordings USING GIN(tags);

ai_conversations

AI conversation sessions (with Elena). Note: This is actually a VIEW that points to the conversations table for backward compatibility.

The actual base table is conversations:


CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  campaign_id UUID REFERENCES campaigns(id) ON DELETE CASCADE,
 
  -- Content
  title TEXT NOT NULL,
  messages JSONB DEFAULT '[]',           -- Array of {role, content, timestamp}
 
  -- AI Analysis
  overview TEXT,                         -- AI-generated summary
  tags TEXT[] DEFAULT '{}' NOT NULL,     -- Categorization tags
 
  -- Audio Recording (if conversation was voice-based)
  audio_url TEXT,
  audio_duration_seconds NUMERIC,
  audio_file_size_bytes BIGINT,
  audio_format TEXT DEFAULT 'audio/webm',
  backblaze_file_id TEXT,
 
  -- Conversation Flow Tracking
  conversation_phase INTEGER,            -- Which phase of Elena conversation
  momentum_indicators JSONB,             -- Track conversation momentum
  quality_score INTEGER,                 -- Quality assessment
  story_elements_count INTEGER,          -- Number of story elements extracted
 
  -- Status
  status TEXT DEFAULT 'active'
    CHECK (status IN ('active', 'completed', 'archived')),
 
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Backward compatibility view
CREATE OR REPLACE VIEW ai_conversations AS
SELECT * FROM conversations;
 
-- Indexes
CREATE INDEX idx_conversations_user_id ON conversations(user_id);
CREATE INDEX idx_conversations_campaign_id ON conversations(campaign_id);
CREATE INDEX idx_conversations_created_at ON conversations(created_at);
CREATE INDEX idx_conversations_status ON conversations(status);
CREATE INDEX idx_conversations_tags ON conversations USING GIN(tags);

Implementation Notes

Table Relationships

The actual schema implements the Story = Chapter model through:

books.story_ids[] - Array of UUIDs referencing the stories table
stories.source_conversation_ids[] - Links stories to AI conversations
stories.user_id - Direct ownership (not through campaigns)
books.campaign_id - Optional grouping by campaign

Key Architectural Decisions

book_chapters table is deprecated - The book_chapters table that was created in early migrations has been superseded by the Story = Chapter model. Books now reference stories directly via the story_ids array.
conversations vs ai_conversations - The database uses conversations as the base table with ai_conversations as a VIEW for backward compatibility. Code can use either name.
Family Group Support - Both stories and books tables include family_group_id for multi-user family collaboration features.
Enhanced Story Features - Stories include collaboration fields (last_edited_by, collaboration_notes), engagement metrics (view_count, engagement_score), and AI enhancement tracking.

Migration Path (Historical)

The system evolved through these key migrations:

Initial Setup - Basic campaigns and stories for story collection
Books Feature - Added books and book_chapters tables
Story = Chapter Transition - Added books.story_ids[] and enhanced stories
Conversations Enhancement - Evolved conversations table with AI features
Family Features - Added family_group_id to enable collaboration

Current State: Story = Chapter model is fully implemented and active.

Query Patterns

Get Book with Stories (Ordered)


// Fetch book
const { data: book } = await supabase
  .from('books')
  .select('*')
  .eq('id', bookId)
  .single()
 
// Fetch stories if book has any
if (book?.story_ids?.length) {
  const { data: stories } = await supabase
    .from('stories')
    .select(`
      *,
      source_recordings:recordings(id, audio_url, title, duration_seconds),
      source_conversations:ai_conversations(id, title, overview)
    `)
    .in('id', book.story_ids)
 
  // Reorder to match story_ids order
  const orderedStories = book.story_ids
    .map(id => stories?.find(s => s.id === id))
    .filter(Boolean)
}

Add Story to Book


const { data: book } = await supabase
  .from('books')
  .select('story_ids')
  .eq('id', bookId)
  .single()
 
const newStoryIds = [...(book?.story_ids || []), storyId]
 
await supabase
  .from('books')
  .update({ story_ids: newStoryIds })
  .eq('id', bookId)

Add Section Break


const newSectionBreaks = {
  ...book.section_breaks,
  '5': 'Part Two'
}
 
await supabase
  .from('books')
  .update({ section_breaks: newSectionBreaks })
  .eq('id', bookId)

Type Definitions


interface Story {
  id: string
  user_id: string
  campaign_id: string | null
  family_group_id: string | null
 
  // Content
  title: string
  content: string
  pages: Record<string, unknown> | null
  word_count: number
  page_count: number | null
  reading_time_minutes: number | null
 
  // Source tracking
  source_conversation_ids: string[] | null
  prompt: string | null  // Original prompt (legacy)
  response_text: string | null
  response_type: string | null
 
  // AI enhancement
  ai_enhanced: boolean | null
  original_content: string | null
  enhancement_notes: string | null
 
  // Metadata
  tags: string[] | null
  themes: string[] | null
  category: string | null
  settings: Record<string, unknown> | null
  header_footer_settings: Record<string, unknown> | null
  version_history: Record<string, unknown> | null
 
  // Collaboration
  last_edited_by: string | null
  collaboration_notes: Record<string, unknown> | null
 
  // Engagement
  story_order: number | null
  view_count: number | null
  engagement_score: number | null
  family_rating: number | null
 
  // Status
  status: 'draft' | 'ready' | 'published' | null
  created_at: string
  updated_at: string
}
 
interface Book {
  id: string
  user_id: string
  campaign_id: string | null
  family_group_id: string | null
 
  // Basic info
  title: string
  subtitle: string | null
  author_name: string
  contributor_names: string[] | null
  series_name: string | null
  volume_number: number | null
  description: string | null
  dedication: string | null
 
  // Content structure (Story = Chapter model)
  story_ids: string[] | null  // Stories ARE chapters
  outline: Record<string, unknown> | null
  chapters: Record<string, unknown>[] | null
  chapter_structure: Record<string, unknown> | null
  word_count: number
  page_count: number | null
  estimated_print_pages: number | null
 
  // Publishing
  keywords: string[] | null
  categories: string[] | null
  language: string | null
  asin: string | null  // Amazon ASIN
  isbn: string | null
  isbn_paperback: string | null
  isbn_ebook: string | null
  kdp_status: 'draft' | 'review' | 'published' | 'unpublished' | null
  kdp_enrolled: boolean | null
  kdp_select: boolean | null
  publish_date: string | null
  publication_date: string | null
  price_usd: number | null
  royalty_rate: number | null
 
  // Design
  cover_image_url: string | null
  template_id: string | null
  cover_template_id: string | null
  interior_template_id: string | null
  trim_size: '6x9' | '5.5x8.5' | '8x10' | '8.5x11' | null
  book_type: 'memoir' | 'biography' | 'fiction' | 'cookbook' | 'family_history' | 'poetry' | null
  design_settings: Record<string, unknown> | null
  digital_formats: Record<string, unknown> | null
 
  // Status
  status: 'draft' | 'designing' | 'ready' | 'ordered' | 'published' | null
  completion_percentage: number | null
 
  // AI & Collaboration
  ai_features_used: string[] | null
  ai_generation_metadata: Record<string, unknown> | null
  collaborators: string[] | null
  contributor_notes: Record<string, unknown> | null
  is_public: boolean | null
  share_url: string | null
 
  // Output
  print_ready_pdf_url: string | null
  download_count: number | null
  version: number | null
 
  created_at: string
  updated_at: string
}
 
interface Campaign {
  id: string
  user_id: string
  title: string | null
  description: string | null
  storyteller_name: string
  storyteller_email: string | null
  storyteller_phone: string | null
  relationship: 'grandparent' | 'parent' | 'partner' | 'other'
  prompts_sent: number
  stories_collected: number
  status: 'active' | 'paused' | 'completed'
  created_at: string
  updated_at: string
}
 
interface Recording {
  id: string
  user_id: string
  campaign_id: string | null
  title: string
  description: string | null
  audio_url: string
  audio_format: string | null
  duration_seconds: number | null
  file_size_bytes: number | null
  backblaze_file_id: string | null
  transcript: string | null
  ai_summary: string | null
  tags: string[]
  status: 'processing' | 'completed' | 'failed' | null
  created_at: string
  updated_at: string
}
 
interface AIConversation {
  // Note: ai_conversations is a VIEW over conversations table
  id: string
  user_id: string
  campaign_id: string | null
  title: string
  messages: Record<string, unknown> | null
  overview: string | null
  tags: string[] | null
  audio_url: string | null
  audio_duration_seconds: number | null
  audio_file_size_bytes: number | null
  audio_format: string | null
  backblaze_file_id: string | null
  conversation_phase: number | null
  momentum_indicators: Record<string, unknown> | null
  quality_score: number | null
  story_elements_count: number | null
  status: 'active' | 'completed' | 'archived' | null
  created_at: string
  updated_at: string
}