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.