bibdle/plans/community-verse-submissions.md

# Community Verse Submissions — Implementation Plan

Source of truth: `spec.md`. Each step is independently verifiable and builds on the previous.

## Step 1 — Schema & migration
- Add `verse_submissions` table to `src/lib/server/db/schema.ts`:
  - `id` text pk (`Bun.randomUUIDv7()`)
  - `user_id` text not null, FK → `user.id` **ON DELETE SET NULL** (nullable column to allow that)
  - `scheduled_date` text not null **unique**
  - `selected_book_id` text not null
  - `selected_chapter` integer not null
  - `selected_verse` integer not null
  - `submitted_at` integer not null (server UTC **millis**; raw integer, drives 7-day cooldown)
  - indexes: `user_id`, unique on `scheduled_date` (the column-level unique handles this)
- `bun run db:push` to dev.db.
- Verify: `sqlite3 dev.db ".schema verse_submissions"`.

> Note: spec says `user_id` FK should be SET NULL on account deletion, so the column is **nullable** despite "not null" in the table sketch (the edge-cases section overrides: "FK should be ON DELETE SET NULL"). Resolution: make `user_id` nullable + ON DELETE SET NULL. The admin LEFT JOIN renders "(deleted user)" when null. This is the spec-intended behavior.

## Step 2 — Bible structure + windowing helpers
- Export `getChapterCount`, `getVerseCount` from `xml-bible.ts` (currently private).
- Add `composeVerseWindow(bookNumber, chapter, verse)`:
  - Computes the selected verse's index `v` within the book (contiguous 1-based across chapters), `bookStart`=1, `bookEnd`=total verses in book.
  - Default window `[v-2, v-1, v]`; if `v-2 < 1` fall forward to `[v, v+1, v+2]`.
  - Returns `{ bookId, bookName, verses: string[], startChapter, startVerse, endChapter, endVerse, selectedVerse }` by calling `extractVerses` per chapter and concatenating for cross-chapter windows.
- Add `formatWindowReference(bookName, startChapter, startVerse, endChapter, endVerse)`: same-chapter → `Book C:S-E`; cross-chapter → `Book C1:S1–C2:E2`.
- Unit tests in `tests/verse-window.test.ts`.

## Step 3 — Public APIs
- `GET /api/bible/structure` → `[{ bookId, chapters: number[] }, ...]` for all 66 books (verse counts per chapter). Cached in-memory.
- `GET /api/verse-window?bookId=gen&chapter=1&verse=1` → `{ windowVerses, reference, bookId, selectedVerse }` via `composeVerseWindow`.
- Verify with curl.

## Step 4 — Admin module + `/scheduled-verses` page
- `src/lib/server/admin.ts` exporting `ADMIN_EMAIL = 'geohpowell@gmail.com'`.
- `src/routes/scheduled-verses/+page.server.ts`: require `locals.user?.email === ADMIN_EMAIL` else auth redirect / 403 access-denied page.
- `+page.svelte`: table joining `verse_submissions` LEFT JOIN `daily_verses` (on `scheduled_date = date`) LEFT JOIN `user` (on `user_id`), sorted by `scheduled_date` asc. Columns per spec. "(deleted user)" when user null.
- Not linked from nav. Verify by direct URL.

## Step 5 — Submit backend + lazy gap-fill
- `POST /api/submit-verse` (auth required): body `{ bookId, chapter, verse, localDate }`.
  1. Require `locals.user` (401).
  2. Solved-today gate: `dailyCompletions` row `(anonymousId=user.id, date=localDate)` else 403.
  3. Cooldown: most recent `verse_submissions.submitted_at` for user; if `< 7d` ago → 429 `{ cooldownEndsAt }`.
  4. Structural validation: bookId valid, chapter in `[1, getChapterCount]`, verse in `[1, getVerseCount]`.
  5. Compute window via `composeVerseWindow`.
  6. Scheduling scan: fetch all `daily_verses` with `date > today` (server UTC) ordered; walk day-by-day from tomorrow; validity = empty + no back-to-back with D-1/D+2 committed neighbors + no same-window-ref row within ±60 days (bounded lookup). Find earliest valid `D`.
  7. Transaction: insert `daily_verses` `{ id, date: D, bookId, reference, verseText, createdAt: now }` + `verse_submissions` `{ id, user_id, scheduled_date: D, selected_book_id, selected_chapter, selected_verse, submitted_at: now }`. On `SQLITE_CONSTRAINT_UNIQUE` retry scan from `D+1`, up to 5 attempts.
  8. Return `{ scheduledDate: D, reference, windowText }`. Emit rybbit event.
- `GET /api/submit-verse/status?localDate=YYYY-MM-DD` (auth required): `{ canSubmit, cooldownEndsAt, lastSubmission, upcoming }`.
- Modify `getVerseForDate` lazy path: re-roll `getRandomVerses()` until book differs from committed D-1 and D+1 neighbors (cap ~20 retries).
- Verify with curl end-to-end.

## Step 6 — Frontend `SubmitVerse.svelte`
- New component rendered in `WinScreen.svelte` near the progress button, sharing `.progress-btn` styling.
- States: logged-out (Apple/Google dropdown mirroring progress button), cooldown (greyed + `CountdownTimer`-style countdown to `cooldownEndsAt`), can-submit (cascading Book→Chapter→Verse `<select>`s from `/api/bible/structure`, debounced 250ms preview via `/api/verse-window`, Submit button, success confirmation with scheduled date, inline 429/403 errors).
- Pass `isLoggedIn` + `anonymousId` through (already on WinScreen props).
- Verify in browser.

## Step 7 — Tests
- `tests/verse-window.test.ts`: book-start fall-forward (Gen 1:1 → [1,2,3]), cross-chapter window, same-chapter window, end-of-book safety.
- `tests/scheduling.test.ts` (or unit-test the pure scan function against an in-memory fixture): no-back-to-back, 60-day repeat skip, earliest-valid-date selection.
- Cooldown arithmetic test.

## Step 8 — Docs
- Update `README.md` schema table (add `verse_submissions`) and routes/API tables (new endpoints + `/scheduled-verses`).
- Note `ADMIN_EMAIL` constant location.