Say It Now - Entity Relationship Diagram v2
Created: 2026-01-22 Purpose: Complete data model for MVP (Phases 1-9)
Current State (v1)
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ profiles │ │ books │ │ submissions │
├─────────────────┤ ├─────────────────┤ ├─────────────────┤
│ id (PK, FK) │──────<│ owner_id (FK) │ │ id (PK) │
│ email │ │ id (PK) │>──────│ book_id (FK) │
│ name │ │ title │ │ contributor_name│
│ created_at │ │ honoree_name │ │ contributor_email│
└─────────────────┘ │ occasion │ │ relationship │
│ occasion_date │ │ content │
│ share_code │ │ content_cleaned │
│ cover_photo_url │ │ photo_urls[] │
│ status │ │ display_order │
│ tier │ │ is_visible │
│ stripe_* │ │ created_at │
│ pdf_url │ └─────────────────┘
│ created_at │
│ updated_at │
└─────────────────┘
Limitations:
- No structured prompts (single freeform content field)
- Contributors are anonymous (no account linkage)
- No editorial workflow (just is_visible toggle)
- No admin role distinction
- No help request system
Target State (v2)
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ profiles │ │ books │ │ prompts │
├─────────────────┤ ├─────────────────┤ ├─────────────────┤
│ id (PK, FK) │──────<│ owner_id (FK) │ │ id (PK) │
│ email │ │ id (PK) │>──┐ │ code (unique) │
│ name │ │ title │ │ │ text │
│ role │ NEW │ honoree_name │ │ │ category │
│ created_at │ │ occasion │ │ │ relationship │
└─────────────────┘ │ occasion_date │ │ │ occasion │
│ │ share_code │ │ │ purpose │
│ │ cover_photo_url │ │ │ display_order │
│ │ status │ UPD │ is_active │
│ │ tier │ │ │ created_at │
│ │ stripe_* │ │ └─────────────────┘
│ │ pdf_url │ │ │
│ │ deadline │ NEW │
│ │ completed_at │ NEW │
│ │ created_at │ │ │
│ │ updated_at │ │ │
│ └─────────────────┘ │ │
│ │ │ │
│ │ │ │
│ ┌───────┴───────┐ │ ┌───────┴───────┐
│ │ book_prompts │ │ │ │
│ ├───────────────┤ │ │ │
│ │ id (PK) │ │ │ │
│ │ book_id (FK) │<────┘ │ │
│ │ prompt_id (FK)│<────────┘ │
│ │ custom_text │ (for custom prompts) │
│ │ display_order │ │
│ │ is_required │ │
│ └───────────────┘ │
│ │ │
│ │ │
│ ┌───────┴───────┐ │
│ │ submissions │ │
│ ├───────────────┤ │
└────────────────>│ contributor_id│ NEW (nullable - can be anonymous)
│ id (PK) │
│ book_id (FK) │
│ book_prompt_id│ NEW (FK to book_prompts)
│ contributor_name│
│ contributor_email│
│ relationship │
│ content │ (response to prompt)
│ content_cleaned│
│ photo_urls[] │
│ status │ NEW: pending/approved/needs_edit/hidden
│ owner_feedback│ NEW: revision request text
│ display_order │
│ created_at │
│ updated_at │ NEW
└───────────────┘
┌─────────────────┐
│ help_requests │ NEW
├─────────────────┤
│ id (PK) │
│ user_id (FK) │ → profiles
│ book_id (FK) │ → books (nullable)
│ subject │
│ message │
│ status │ open/in_progress/resolved/closed
│ admin_notes │
│ created_at │
│ resolved_at │
└─────────────────┘
Table Definitions
profiles (MODIFY)
Add role for admin access:
ALTER TABLE profiles
ADD COLUMN role text DEFAULT 'user' CHECK (role IN ('user', 'admin'));
books (MODIFY)
Expand status enum, add completion tracking:
-- Update status check constraint
ALTER TABLE books DROP CONSTRAINT IF EXISTS books_status_check;
ALTER TABLE books ADD CONSTRAINT books_status_check
CHECK (status IN ('draft', 'collecting', 'review', 'complete', 'published', 'archived'));
-- Add new columns
ALTER TABLE books ADD COLUMN deadline date;
ALTER TABLE books ADD COLUMN completed_at timestamptz;
Status Flow:
draft- Book created, not yet paidcollecting- Paid, accepting submissions (was 'active')review- Owner reviewing submissions before completioncomplete- Owner marked as complete, ready for PDFpublished- PDF generated, delivered to honoreearchived- Book closed/hidden
prompts (NEW)
Master library of curated prompts:
CREATE TABLE prompts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
code text UNIQUE NOT NULL, -- e.g., 'U1', 'P3', 'W2'
text text NOT NULL, -- The actual prompt text with [Name] placeholder
category text NOT NULL CHECK (category IN ('universal', 'relationship', 'occasion')),
relationship text, -- NULL for universal, else: parent, child, sibling, spouse, friend, coworker, mentor
occasion text, -- NULL unless occasion-specific: birthday, retirement, memorial, just_because
purpose text, -- Brief description of what this prompt elicits
display_order int DEFAULT 0,
is_active boolean DEFAULT true,
created_at timestamptz DEFAULT now() NOT NULL
);
-- Index for filtering
CREATE INDEX prompts_category_idx ON prompts(category);
CREATE INDEX prompts_relationship_idx ON prompts(relationship);
CREATE INDEX prompts_occasion_idx ON prompts(occasion);
book_prompts (NEW)
Which prompts are enabled for a specific book:
CREATE TABLE book_prompts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
book_id uuid REFERENCES books(id) ON DELETE CASCADE NOT NULL,
prompt_id uuid REFERENCES prompts(id), -- NULL for custom prompts
custom_text text, -- For owner-written prompts
display_order int NOT NULL,
is_required boolean DEFAULT false,
created_at timestamptz DEFAULT now() NOT NULL,
-- Either prompt_id OR custom_text must be set
CONSTRAINT prompt_or_custom CHECK (
(prompt_id IS NOT NULL AND custom_text IS NULL) OR
(prompt_id IS NULL AND custom_text IS NOT NULL)
)
);
CREATE INDEX book_prompts_book_id_idx ON book_prompts(book_id);
submissions (MODIFY)
Link to contributor accounts and prompts, add editorial workflow:
-- Add new columns
ALTER TABLE submissions
ADD COLUMN contributor_id uuid REFERENCES profiles(id), -- NULL = anonymous
ADD COLUMN book_prompt_id uuid REFERENCES book_prompts(id),
ADD COLUMN status text DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'needs_edit', 'hidden')),
ADD COLUMN owner_feedback text,
ADD COLUMN updated_at timestamptz DEFAULT now();
-- Create index for contributor lookup
CREATE INDEX submissions_contributor_id_idx ON submissions(contributor_id);
CREATE INDEX submissions_status_idx ON submissions(status);
-- Add trigger for updated_at
CREATE TRIGGER submissions_updated_at
BEFORE UPDATE ON submissions
FOR EACH ROW EXECUTE PROCEDURE public.update_updated_at();
help_requests (NEW)
Support ticket system:
CREATE TABLE help_requests (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid REFERENCES profiles(id) NOT NULL,
book_id uuid REFERENCES books(id), -- Optional context
subject text NOT NULL,
message text NOT NULL,
status text DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'resolved', 'closed')),
admin_notes text,
created_at timestamptz DEFAULT now() NOT NULL,
resolved_at timestamptz
);
CREATE INDEX help_requests_user_id_idx ON help_requests(user_id);
CREATE INDEX help_requests_status_idx ON help_requests(status);
RLS Policies (New/Updated)
profiles
-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles"
ON profiles FOR SELECT
USING (
EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
);
books
-- Admins can view all books
CREATE POLICY "Admins can view all books"
ON books FOR SELECT
USING (
EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
);
prompts
-- Everyone can read active prompts
ALTER TABLE prompts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can view active prompts"
ON prompts FOR SELECT
USING (is_active = true);
CREATE POLICY "Admins can manage prompts"
ON prompts FOR ALL
USING (
EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
);
book_prompts
ALTER TABLE book_prompts ENABLE ROW LEVEL SECURITY;
-- Owners can manage their book prompts
CREATE POLICY "Owners can manage book prompts"
ON book_prompts FOR ALL
USING (
EXISTS (SELECT 1 FROM books WHERE books.id = book_prompts.book_id AND books.owner_id = auth.uid())
);
-- Contributors can view prompts for books they're contributing to
CREATE POLICY "Public can view book prompts"
ON book_prompts FOR SELECT
USING (true);
submissions
-- Contributors can view and update their own submissions
CREATE POLICY "Contributors can view own submissions"
ON submissions FOR SELECT
USING (contributor_id = auth.uid());
CREATE POLICY "Contributors can update own submissions"
ON submissions FOR UPDATE
USING (contributor_id = auth.uid() AND status IN ('pending', 'needs_edit'));
help_requests
ALTER TABLE help_requests ENABLE ROW LEVEL SECURITY;
-- Users can manage their own help requests
CREATE POLICY "Users can view own help requests"
ON help_requests FOR SELECT
USING (user_id = auth.uid());
CREATE POLICY "Users can create help requests"
ON help_requests FOR INSERT
WITH CHECK (user_id = auth.uid());
-- Admins can view and update all help requests
CREATE POLICY "Admins can manage all help requests"
ON help_requests FOR ALL
USING (
EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin')
);
Seed Data: Prompts
From docs/planning/prompt-options.md:
INSERT INTO prompts (code, text, category, relationship, occasion, purpose, display_order) VALUES
-- Universal
('U1', 'Tell the story behind this photo.', 'universal', NULL, NULL, 'Photo-first anchor', 1),
('U2', 'What''s a moment with [Name] you''ll always remember?', 'universal', NULL, NULL, 'Opens storytelling', 2),
('U3', 'What has [Name] taught you about life?', 'universal', NULL, NULL, 'Captures wisdom/impact', 3),
('U4', 'In one word, how does [Name] make you feel? Now explain that word.', 'universal', NULL, NULL, 'Emotional + explanation', 4),
('U5', 'What would you want [Name] to know, but maybe haven''t said?', 'universal', NULL, NULL, 'The "say it now" moment', 5),
('U6', 'Describe [Name] to someone who''s never met them.', 'universal', NULL, NULL, 'Character portrait', 6),
('U7', 'What do you admire most about [Name]?', 'universal', NULL, NULL, 'Direct appreciation', 7),
('U8', 'When you think of [Name], what''s the first memory that comes to mind?', 'universal', NULL, NULL, 'Spontaneous, authentic', 8),
-- Relationship: Parent
('P1', 'What''s a moment when [Name] made you feel truly loved or supported?', 'relationship', 'parent', NULL, 'Emotional core', 10),
('P2', 'What''s something [Name] always says that sticks with you?', 'relationship', 'parent', NULL, 'Voice/catchphrase', 11),
('P3', 'What''s a lesson from [Name] that you''ve passed on (or will pass on)?', 'relationship', 'parent', NULL, 'Legacy/impact', 12),
('P4', 'Describe a time when [Name] surprised you.', 'relationship', 'parent', NULL, 'Reveals depth', 13),
('P5', 'What do you hope [Name] knows they gave you?', 'relationship', 'parent', NULL, 'Gratitude', 14),
-- Relationship: Child/Grandchild
('C1', 'What''s a moment with [Name] that fills you with pride or joy?', 'relationship', 'child', NULL, 'Celebration', 20),
('C2', 'What has [Name] taught you about life or love?', 'relationship', 'child', NULL, 'Child teaching parent', 21),
('C3', 'Describe the person you see [Name] becoming.', 'relationship', 'child', NULL, 'Future/potential', 22),
('C4', 'What''s something about [Name] that makes you laugh?', 'relationship', 'child', NULL, 'Joy/personality', 23),
('C5', 'What do you want [Name] to always remember about your love for them?', 'relationship', 'child', NULL, 'Core message', 24),
-- Relationship: Sibling
('S1', 'What''s a childhood memory with [Name] that still makes you smile?', 'relationship', 'sibling', NULL, 'Shared history', 30),
('S2', 'What''s something only a sibling would know about [Name]?', 'relationship', 'sibling', NULL, 'Unique perspective', 31),
('S3', 'How has your relationship with [Name] changed over the years?', 'relationship', 'sibling', NULL, 'Growth/evolution', 32),
('S4', 'What''s your earliest memory of [Name]?', 'relationship', 'sibling', NULL, 'Origin story', 33),
('S5', 'What would life have been like without [Name] in it?', 'relationship', 'sibling', NULL, 'Impact/appreciation', 34),
-- Relationship: Spouse/Partner
('SP1', 'What moment made you realize [Name] was the one?', 'relationship', 'spouse', NULL, 'Love story', 40),
('SP2', 'What''s something [Name] does that you''ll never stop loving?', 'relationship', 'spouse', NULL, 'Daily appreciation', 41),
('SP3', 'What''s a challenge you faced together that made you stronger?', 'relationship', 'spouse', NULL, 'Partnership', 42),
('SP4', 'What do you love about how [Name] loves you?', 'relationship', 'spouse', NULL, 'Reciprocal', 43),
('SP5', 'What''s your favorite "ordinary" moment with [Name]?', 'relationship', 'spouse', NULL, 'Intimate/daily life', 44),
-- Relationship: Friend
('F1', 'What''s your favorite memory with [Name]?', 'relationship', 'friend', NULL, 'Classic, open', 50),
('F2', 'How did you and [Name] become friends?', 'relationship', 'friend', NULL, 'Origin story', 51),
('F3', 'What makes [Name] a great friend?', 'relationship', 'friend', NULL, 'Character', 52),
('F4', 'What''s an adventure or experience you''ll never forget sharing with [Name]?', 'relationship', 'friend', NULL, 'Shared history', 53),
('F5', 'What''s something about [Name] that more people should know?', 'relationship', 'friend', NULL, 'Hidden qualities', 54),
-- Relationship: Coworker
('W1', 'What''s one project or moment at work with [Name] you''ll never forget?', 'relationship', 'coworker', NULL, 'Professional memory', 60),
('W2', 'What''s something [Name] taught you that you still use today?', 'relationship', 'coworker', NULL, 'Mentorship/skills', 61),
('W3', 'How has [Name] made the workplace better?', 'relationship', 'coworker', NULL, 'Culture/impact', 62),
('W4', 'Describe [Name]''s greatest professional strength.', 'relationship', 'coworker', NULL, 'Recognition', 63),
('W5', 'What would you tell a new employee about working with [Name]?', 'relationship', 'coworker', NULL, 'Character reference', 64),
-- Relationship: Teacher/Mentor
('T1', 'What''s a lesson [Name] taught you that changed how you see things?', 'relationship', 'mentor', NULL, 'Transformative', 70),
('T2', 'How did [Name] help you become who you are today?', 'relationship', 'mentor', NULL, 'Long-term impact', 71),
('T3', 'Describe a time when [Name] believed in you.', 'relationship', 'mentor', NULL, 'Encouragement', 72),
('T4', 'What''s something [Name] said that you still think about?', 'relationship', 'mentor', NULL, 'Lasting words', 73),
('T5', 'What would you say to [Name] that you never got to say in class/practice?', 'relationship', 'mentor', NULL, 'Unsaid gratitude', 74),
-- Occasion: Birthday
('B1', 'What''s your favorite birthday memory with [Name]?', 'occasion', NULL, 'birthday', 'Celebratory', 80),
('B2', 'What do you wish for [Name] in this next year?', 'occasion', NULL, 'birthday', 'Future-focused', 81),
('B3', 'What makes [Name] worth celebrating?', 'occasion', NULL, 'birthday', 'Appreciation', 82),
-- Occasion: Retirement
('R1', 'What will you miss most about working with [Name]?', 'occasion', NULL, 'retirement', 'Transition', 90),
('R2', 'What''s [Name]''s greatest professional legacy?', 'occasion', NULL, 'retirement', 'Impact', 91),
('R3', 'What advice would you give [Name] for retirement?', 'occasion', NULL, 'retirement', 'Looking forward', 92),
-- Occasion: Memorial
('M1', 'What''s the first thing you think of when you remember [Name]?', 'occasion', NULL, 'memorial', 'Immediate memory', 100),
('M2', 'How did [Name] make the world a better place?', 'occasion', NULL, 'memorial', 'Legacy', 101),
('M3', 'What part of [Name] lives on in you?', 'occasion', NULL, 'memorial', 'Continuation', 102),
('M4', 'What would you want to say to [Name] one more time?', 'occasion', NULL, 'memorial', 'Final message', 103),
-- Occasion: Just Because
('J1', 'Why did you want to be part of this tribute to [Name]?', 'occasion', NULL, 'just_because', 'Intent', 110),
('J2', 'What''s something [Name] should know, right now?', 'occasion', NULL, 'just_because', 'Urgency/present', 111),
('J3', 'Describe [Name] in the best way you know how.', 'occasion', NULL, 'just_because', 'Portrait', 112);
Migration Strategy
- Create new tables first (prompts, book_prompts, help_requests)
- Alter existing tables (profiles, books, submissions)
- Add new RLS policies (don't break existing ones)
- Seed prompts data
- Test on staging before production
See supabase/migrations/20260122000000_v2_schema.sql for the complete migration.
Data Flow Examples
Owner Creates Book with Prompts
- Owner signs up →
profilesrow created - Owner pays → Stripe webhook updates ready state
- Owner creates book →
booksrow created (status: 'collecting') - System suggests default prompts for occasion → owner customizes
- Selected prompts →
book_promptsrows created - Owner shares link → contributors can access
Contributor Submits Tribute
- Contributor clicks invite link → sees book info
- Contributor signs in (magic link) →
profilesrow created/found - Contributor sees prompts → from
book_promptsfor this book - Contributor uploads photo, answers prompts →
submissionsrow(s) created - AI cleanup offered →
content_cleanedpopulated - Submission saved with
status: 'pending'
Owner Reviews & Approves
- Owner views dashboard → sees submissions with
status: 'pending' - Owner can:
- Approve →
status: 'approved' - Request edit →
status: 'needs_edit',owner_feedbackset - Hide →
status: 'hidden'
- Approve →
- Contributor notified of edit request
- Contributor updates →
statusback to 'pending'
Admin Views Dashboard
- Admin signs in → has
role: 'admin' - Admin dashboard queries all books, submissions, help_requests
- RLS policies allow admin access based on role check