Cloud books auth plan

-- Enable Auth in Supabase dashboard (email magic link and/or OAuth).

create table public.books (
 id uuid primary key default gen_random_uuid(),
 user_id uuid not null references auth.users (id) on delete cascade,
 title text not null default '',
 book_type text,
 ked_json jsonb not null,
 created_at timestamptz not null default now(),
 updated_at timestamptz not null default now()
);

create index books_user_updated_idx
 on public.books (user_id, updated_at desc);

alter table public.books enable row level security;

create policy "Users read own books"
 on public.books for select
 using (auth.uid() = user_id);

create policy "Users insert own books"
 on public.books for insert
 with check (auth.uid() = user_id);

create policy "Users update own books"
 on public.books for update
 using (auth.uid() = user_id);

create policy "Users delete own books"
 on public.books for delete
 using (auth.uid() = user_id);
```text

### What goes in `ked_json`

Exactly the object produced by `buildSavedPackagePayload()` today:

- Package fields (`title`, `rows`, `columns`, `book_type`, …)
- `book_settings`
- `pages` (saved HTML for WYSIWYG reopen)
- `template` / `book_design_template_id` when present

Denormalize `title` and `book_type` columns for list UI without parsing JSONB every time; keep them in sync on save.

### Size notes

- Packages with many rows + page HTML can be large. Set a soft limit early (e.g. warn above ~2–5 MB; hard reject above a configured max).
- Do not put Puppeteer PDFs in the row; export remains on demand.

---

## Product UX (v1)

### Auth

- Sign in / sign out in the toolbar or a small account menu (hosted web first).
- Providers for v1: **email magic link**; add Google/GitHub later if needed.
- Signed-out: current behavior (import, edit, download, print PDF, cover PNG).

### Books

| Action | Behavior | |--------|----------| | **My books** | List cloud books (title, type, updated); open loads `ked_json` via existing `loadPackage` | | **Save to cloud** | Upsert current book; create `id` on first save | | **Autosave** | Debounced (e.g. 2–5s after edits) when signed in and book has a cloud `id` | | **Save book…** (download) | Unchanged `.ked.json` file download | | **Delete** | Confirm, then delete row |

### Session restore (nice-to-have in v1.1)

- Remember last opened `book_id` in `localStorage` → reopen after refresh when still signed in.

### Desktop

- v1: no change required (file download / import).
- Later: “Sign in” + “Open from cloud” if we want one account across web and desktop.

---

## Runtime split (align with export)

| Runtime | Cloud books | |---------|-------------| | **Hosted web** | Primary target — auth + DB | | **Local web** | Same code path if env has Supabase keys (useful for testing) | | **Desktop** | Optional later; file save remains default |

---

## Architecture sketch

```mermaid
sequenceDiagram
 participant User
 participant Studio as KEd Studio (web)
 participant Auth as Supabase Auth
 participant DB as Postgres books

 User->>Studio: Edit book
 Studio->>Studio: buildSavedPackagePayload()
 alt Signed in + cloud book id
 Studio->>DB: Upsert ked_json (RLS)
 else Signed out
 Studio->>User: Download .ked.json only
 end
 User->>Studio: My books → Open
 Studio->>Auth: Session
 Studio->>DB: Select own rows
 Studio->>Studio: loadPackage(ked_json)
```text

### App modules (suggested)

| Path | Role | |------|------| | `lib/supabase/client.ts` | Browser Supabase client | | `lib/supabase/server.ts` | Server client if Route Handlers needed | | `lib/books/api.ts` | list / get / upsert / delete | | `hooks/useAuth.ts` | Session, sign-in, sign-out | | `hooks/useCloudBook.ts` | Current `bookId`, autosave, dirty state | | `components/AccountMenu.tsx` | Sign in / out | | `components/MyBooksDialog.tsx` | List + open + delete |

Keep persistence logic out of `useKedStudio` where possible: call `buildSavedPackagePayload` / `loadPackage` from a thin cloud hook.

---

## Implementation phases

### Phase A — Auth shell

- [ ] Enable Supabase Auth on the **existing** project (magic link); add redirect URLs for local + production.
- [ ] Browser client (`lib/supabase/client.ts`) using existing `NEXT_PUBLIC_*` keys.
- [ ] Account menu: sign in / sign out / email shown.
- [ ] Guard: cloud APIs no-op or prompt when signed out.
- [ ] Keep `SUPABASE_SERVICE_ROLE_KEY` server-only (installers); never use it for user book CRUD in the browser.

### Phase B — Schema + CRUD

- [ ] Migration for `books` + RLS (as above).
- [ ] `listBooks` / `getBook` / `upsertBook` / `deleteBook`.
- [ ] Wire **Save to cloud** and **My books** to load via `loadPackage`.
- [ ] Sync `title` / `book_type` columns from payload on write.

### Phase C — Autosave + UX polish

- [ ] Debounced autosave when `bookId` is set.
- [ ] Status line: “Saved” / “Saving…” / “Save failed”.
- [ ] Conflict policy v1: last write wins (document; no OT).
- [ ] Payload size warning / max.

### Phase D — Hardening (after v1)

- [ ] Optional book sharing / lab role (separate policies).
- [ ] Desktop “Open from cloud”.
- [ ] Soft delete / trash.
- [ ] Version history (new `book_revisions` table) if needed.

---

## Security checklist

- [ ] RLS on for all book policies; no service-role key in the browser.
- [ ] Validate `ked_json` shape server-side or via constrained client upsert (at least `title` string + object).
- [ ] Auth redirect allowlist: production host + `localhost`.
- [ ] Rate-limit abuse via Supabase / edge if needed later.

---

## Explicit non-goals (v1)

- Real-time multiplayer editing.
- Replacing `.ked.json` file handoff with generators.
- Storing exported PDF/DOCX blobs in the DB.
- Anonymous / passwordless-without-email cloud books.

---

## Success criteria

- Signed-in user edits a book on hosted web, refreshes, and the book reopens from **My books** (or last-session restore) with pages and cover intact.
- Signed-out user can still download `.ked.json` and is never blocked from editing.
- A user cannot read or mutate another user’s `books` rows (RLS verified).
- Download + desktop DOCX handoff still work unchanged.

---

## Open decisions

| Topic | Options | Lean | |-------|---------|------| | Auth providers | Magic link only vs + OAuth | Magic link first | | New book default | Always create cloud row vs “Save to cloud” explicit | Explicit first save, then autosave | | Local draft before sign-in | Discard vs stash in `localStorage` then upload | Stash one draft on sign-in (v1.1) | | Desktop sync | Skip vs same Auth | Skip in v1 |