Database Schema

id          uuid primary key default gen_random_uuid()
user_id     uuid references auth.users not null
title       text not null
created_at  timestamptz default now()
updated_at  timestamptz default now()

id              uuid primary key default gen_random_uuid()
project_id      uuid references projects not null
parent_id       uuid references nodes null    -- null = root node (Book or Series)
type            text not null                 -- 'series' | 'book' | 'act' | 'chapter' | 'scene' | 'beat'
title           text
position        integer not null default 0    -- ordering among siblings
 
-- Output layer
prose           text                          -- the readable text output
 
-- Data layer
data            jsonb default '{}'            -- typed + freeform data blocks
 
-- Generation layer
generation      jsonb default '{}'            -- beat notes, AI prompt log, decision record
 
-- Metadata
created_at      timestamptz default now()
updated_at      timestamptz default now()

create index nodes_project_id_idx on nodes(project_id);
create index nodes_parent_id_idx on nodes(parent_id);
create index nodes_position_idx on nodes(parent_id, position);

id          uuid primary key default gen_random_uuid()
node_id     uuid references nodes not null
type        text not null           -- 'tutor_red' | 'tutor_amber' | 'tutor_blue' | 'craft' | 'generation'
status      text not null default 'open'  -- 'open' | 'answered' | 'dismissed' | 'snoozed'
question    text not null           -- the question or feedback text
detail      text                    -- expanded detail shown in popup
dismissal   text                    -- reason: 'answered' | 'deliberate' | 'not_applicable' | 'revisit'
note        text                    -- writer's own note when dismissing
source      text                    -- rule ID or 'tutor' for origin tracking
created_at  timestamptz default now()
updated_at  timestamptz default now()