bibdle/spec.md

Spec: Community Verse Submissions

TODO

• Step 1 — Schema & migration: add verse_submissions table to src/lib/server/db/schema.ts (nullable user_id FK → user.id ON DELETE SET NULL, unique scheduled_date, index on user_id), pushed to dev.db.
• Step 2 — Bible structure + windowing helpers: export getChapterCount/getVerseCount from xml-bible.ts; add composeVerseWindow() (fall-forward 3-verse window, never crosses book, crosses chapter within book) and formatWindowReference() (hyphen same-chapter, en-dash cross-chapter). Tests in tests/verse-window.test.ts (17 pass).
• Step 3 — Public APIs: GET /api/bible/structure (66-book verse counts for cascading dropdowns) and GET /api/verse-window (live 3-verse preview).
• Step 4 — Admin module + /scheduled-verses page: src/lib/server/admin.ts (ADMIN_EMAIL), auth-walled admin view joining verse_submissions → daily_verses → user.
• Step 5 — Submit backend + lazy gap-fill: POST /api/submit-verse (solved-today gate, 7-day cooldown, structural validation, scheduling scan with empty/no-back-to-back/60-day-repeat rules, transaction + retry), GET /api/submit-verse/status, modified getVerseForDate neighbor avoidance.
• Step 6 — Frontend SubmitVerse.svelte: three states (logged-out sign-in dropdown, cooldown + countdown, cascading selects + preview + submit), wired into WinScreen.svelte.
• Step 7 — Tests: scheduling validity, cooldown arithmetic, concurrency.
• Step 8 — Docs: update README schema + routes/API tables.

---

Let authenticated users submit a Bible verse they love. Submissions are scheduled as future "verses of the day," mixed so that no two consecutive calendar days ever feature the same book, and spaced so an exact 3-verse window never repeats within 60 days. Selection is dropdown-based (cascading <select>s) so the system can compose verses purely from existing Bible-lookup functions already in the codebase — no free-text entry, so typos are impossible and content is always canonical NKJV text.

Goals

• A new button on the win screen (WinScreen.svelte), visually consistent with the existing "📈 See your progress" neobrutalist button.
• Logged-out users who click it get a dropdown prompting sign-in (mirroring the existing progress button's Apple/Google dropdown).
• Logged-in users get a cascading Book → Chapter → Verse selector with a live 3-verse preview.
• Each submission immediately reserves a concrete future date; the submitter is shown that date on success.
• A 7-day rolling cooldown (server UTC) gates submissions, with the button greyed out and a countdown timer shown while active.
• Attribution is anonymous everywhere — submissions are never credited to a user publicly.

Non-goals (explicitly decided)

• No giveaway-text filtering. Verses that mention their own book/author (e.g. Eph 1:1, Isa 1:1) are allowed. The random generator already permits these.
• No persistent pending queue / no cron. Assignment happens at submit time, not via a background job.
• No withdrawal / deletion / editing. Submissions are final (references come from dropdowns, so typos are impossible).
• No guardrail on how far out a verse is scheduled. If the calendar is dense, a submission may land months or years in the future; this is accepted.
• No per-user pending cap, no global cap. The 7-day rolling cooldown is the only rate limit.
• No attribution / leaderboard / share-text changes. Submitter identity is recorded only for rate-limiting and the solved-today gate; it is never displayed.
• No backfill of gap days. Sparse future dailyVerses rows are fine.

---

Data Model

New table: verse_submissions

A log of who submitted what and when, plus the assigned scheduled date. The canonical verse text/reference lives in daily_verses (keyed by date); this table links a user to a scheduled date.

Column	Type	Notes
id	text (pk)	Bun.randomUUIDv7()
user_id	text, not null, FK → user.id	the submitter (never displayed)
scheduled_date	text, not null, unique	YYYY-MM-DD of the reserved daily_verses row
selected_book_id	text, not null	the book the user chose
selected_chapter	integer, not null	the chapter the user chose
selected_verse	integer, not null	the single verse the user chose (the "anchor")
submitted_at	integer (timestamp), not null	server UTC millis — drives the 7-day cooldown

Indexes: user_id (cooldown lookup + "your upcoming verses"), scheduled_date (unique — one submission per scheduled date).

No status, withdrawn_at, or attribution columns. Denormalized window/verseText/reference are not stored here; they live on daily_verses and are joined by scheduled_date.

daily_verses (unchanged schema)

Submissions simply write a new row with a future date, exactly as the existing lazy path does for the current day. getVerseForDate(date) already returns a pre-existing row if present, so a pre-written submission row for a future date is served unchanged when its day arrives.

The new behavior:

• Submission rows are written at submit time for a future date (the date returned by the scheduling scan).
• Gap days (dates with no committed row when a player arrives) are filled lazily by the existing random path — extended to respect the no-back-to-back rule (see Lazy gap-fill below).

---

Verse Windowing

A submission selects one verse (book → chapter → verse). The displayed daily verse is a 3-verse window anchored on that verse, for difficulty consistency with the existing game (getRandomVerses defaults to 3 consecutive verses).

Window algorithm (deterministic)

Let v be the selected verse's index within its book (a contiguous 1-based index across all chapters of that book). Let bookStart = 1 (first verse index in the book) and bookEnd = last verse index in the book.

• Default window: [v-2, v-1, v] (the two preceding in-book verses + the selected verse).
• If v - 2 < bookStart (not enough preceding verses in-book — only happens at a book's very start, e.g. Gen 1:1, John 1:1): fall forward so the window is 3 consecutive in-book verses that still includes v, i.e. start = v, window = [v, v+1, v+2]. (Every Bible book has ≥3 verses after its opening, so this always succeeds.)
• The window never crosses a book boundary. Crossing a chapter boundary within the same book is allowed and expected (e.g. a window spanning the end of chapter 1 and the start of chapter 2).

This reuses existing functions: getChapterCount, getVerseCount, extractVerses (called per chapter; for cross-chapter windows, call extractVerses twice and concatenate), getBookByNumber/getBookById, and formatReference (extended to format cross-chapter ranges, e.g. Genesis 1:31–2:1; same-chapter stays Genesis 1:1-3).

The selected verse's book is the day's bookId (the guess target). All three displayed verses come from that same book, so the guessing game stays fair.

---

Scheduling Algorithm (assign-at-submit)

When a submission is accepted, the server scans forward from tomorrow (server UTC date) to find the earliest valid candidate date D, then writes the daily_verses row for D immediately and records a verse_submissions row. The submitter is shown D.

Validity checks for candidate date D and submission book B

D is valid iff all hold:

1. Empty: no daily_verses row exists at D.
2. No back-to-back with prior committed neighbor: the daily_verses row at D-1 (if one exists) must have book_id != B.
3. No back-to-back with next committed neighbor: the daily_verses row at D+1 (if one exists) must have book_id != B.
4. 60-day repeat distance: no daily_verses row with the same 3-verse window exists whose date is within [D-60, D+60] days inclusive. The window identity is its canonical reference string (book_id + formatted chapter:startverse-endverse, cross-chapter-aware). This is a soft repeat rule: repeats are allowed, just not within ±60 days of any prior/scheduled appearance.

Implementation note (performance)

Don't query per candidate date. Instead, fetch all daily_verses rows with date > today ordered by date into memory, then walk day-by-day from tomorrow, checking the four rules against the in-memory set + a single range query for the 60-day repeat check (windowed by reference). Historical rows (date ≤ today) only matter for rule 4; a bounded lookup (rows with the same window-ref within 60 days of D) suffices.

Concurrency

The scan-then-insert is a critical section. Two concurrent submissions could both select the same D. Wrap insert in a transaction; on a SQLITE_CONSTRAINT_UNIQUE failure on daily_verses.date (or verse_submissions.scheduled_date), re-run the scan from D+1. Retry up to a small bound (e.g. 5). SQLite under bun:sqlite handles this with a transaction.

Why this preserves no-back-to-back globally

Adjacency is only ever checked against committed rows (D-1, D+1). Gap days (empty between two committed rows) are filled lazily later by a random verse whose generator also respects committed neighbors (see below). So at play-time, every adjacent pair of days has been checked at write-time. The invariant is maintained inductively.

---

Lazy Gap-Fill (modified getVerseForDate)

When a player opens a date with no committed daily_verses row, the existing random path runs — extended so the random verse's book differs from any committed neighbor:

• If D-1 has a committed row with book X, the random book must be != X.
• If D+1 has a committed row with book Y, the random book must be != Y.
• Re-try getRandomVerses() until both hold (66 books, ≤2 excluded → ~97% success per try; cap at ~20 retries, then accept). getRandomVerses already returns 3 consecutive in-book verses; no windowing change needed for random fillers.

Submission days are pre-written, so they skip this path entirely (getVerseForDate returns the existing row).

---

Rate Limiting & Cooldown

• One submission per user per rolling 7×24h window, measured in server UTC (not gameable; smooth countdown).
• cooldownEndsAt = lastSubmission.submitted_at + 7 days (server UTC millis).
• The win-screen button is always visible; when the cooldown is active it is greyed out and disabled, with a small countdown timer below it (target cooldownEndsAt).
• Query: select the user's most recent verse_submissions.submitted_at; if now - submitted_at < 7d, cooldown is active.

Solved-today gate (API-level)

The POST /api/submit-verse endpoint requires that the user has a daily_completions row for their local today. The client sends localDate (the same YYYY-MM-DD it already computes via toLocaleDateString("en-CA") and sends to other endpoints). The server checks dailyCompletions for a row with anonymous_id = user.id (for logged-in users, anonymousId is the user id after migration) and date = localDate. If absent → 403 with { error: "Solve today's puzzle first" }.

This is an engagement gate, not a security necessity (submissions are anonymous → low spam incentive), but it ties the feature to real play and blocks scripted submissions.

---

Validation (no content moderation)

Because verses are chosen from canonical NKJV via dropdowns, no profanity/inappropriate-content filter is needed. Pre-submit validation is purely structural:

• bookId is one of the 66 known books (getBookById).
• chapter is in [1, getChapterCount(bookNumber)].
• verse is in [1, getVerseCount(bookNumber, chapter)].
• Cooldown not active.
• Solved-today gate passed.
• (The 60-day repeat and no-back-to-back rules are enforced by the scheduling scan, not by pre-rejecting the user's selection. A user can pick any valid verse; the scan will simply place it on the earliest valid date, possibly far out.)

---

API Endpoints

/scheduled-verses (auth-walled, admin-only)

A private admin page gated to the single account geohpowell@gmail.com. This is the one place where submitter identity is surfaced — attribution is anonymous everywhere else (public game, share text, etc.), but this admin view is the exception.

Access control:

• +page.server.ts load requires locals.user and locals.user.email === 'geohpowell@gmail.com'.
• Not authenticated → redirect to the sign-in modal/flow (same pattern as /progress, /stats).
• Authenticated but wrong account → 403 / a plain "not authorized" page. Do not leak the existence of privileged data; the page simply renders an access-denied state.
• The email check is a literal string match; if the admin email ever changes, update the constant (single source in one server module, e.g. src/lib/server/admin.ts exporting ADMIN_EMAIL).
• No isPrivate bypass needed — the admin is trusted to see all submitters regardless of their isPrivate flag.

Page contents: a table of all verse_submissions rows joined to daily_verses (by scheduled_date = daily_verses.date) and user (by user_id), one row per submission:

Column	Source
Scheduled date	verse_submissions.scheduled_date (rendered human-readable)
Reference	daily_verses.reference
Window text	daily_verses.verse_text (the 3-verse window)
Book	daily_verses.book_id (resolve to name via getBookById)
Submitted by	user.email (and firstName lastName if present)
Submitted at	verse_submissions.submitted_at (server UTC, rendered)
Selected verse	verse_submissions.selected_book_id / selected_chapter / selected_verse (the anchor the user picked)

Sorted by scheduled_date ascending (so the upcoming calendar reads top-to-bottom). Include past rows (already-played submissions) — the table is a full historical + future log, not just pending. A small filter toggle (All / Upcoming / Past) is a nice-to-have, not required.

Since attribution appears here, verse_submissions.user_id must resolve even for deleted accounts — use a LEFT JOIN to user and render "(deleted user)" when the join is null. (Matches the ON DELETE SET NULL decision in the edge-cases section: the scheduled verse still plays out; the admin just sees the attribution is gone.)

This route is not linked from the UI (no nav entry, no win-screen button) — it's a direct-URL admin tool.

POST /api/submit-verse (auth required)

Body: { bookId, chapter, verse, localDate }

Flow:

1. Require locals.user (else 401).
2. Solved-today gate: dailyCompletions row for (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 of bookId/chapter/verse.
5. Compute the 3-verse window (fall-forward algorithm).
6. Run the scheduling scan; on unique-constraint conflict, retry.
7. In a transaction: insert daily_verses row { id, date: D, bookId, reference, verseText, createdAt: now } and verse_submissions row { id, user_id, scheduled_date: D, selected_book_id, selected_chapter, selected_verse, submitted_at: now }.
8. Return { scheduledDate: D, reference, windowText }.

Analytics: emit (window as any).rybbit?.event("Submit a verse").

GET /api/submit-verse/status?localDate=YYYY-MM-DD (auth required)

Returns the state for the win-screen button:

{
  "canSubmit": true | false,
  "cooldownEndsAt": <server UTC millis> | null,
  "lastSubmission": { "scheduledDate": "2026-08-24", "reference": "..." } | null,
  "upcoming": [ { "scheduledDate": "...", "reference": "..." }, ... ]  // this user's not-yet-reached submissions, for an optional small list
}

GET /api/bible/structure (public)

Returns the static structure for populating the cascading dropdowns client-side (cached indefinitely):

[
  { "bookId": "gen", "chapters": [31, 25, 24, ...] },  // verse counts per chapter
  ...
]

Built from getChapterCount + getVerseCount over all 66 books. Small payload (~1189 chapter entries).

GET /api/verse-window?bookId=gen&chapter=1&verse=1 (public)

Returns the 3-verse preview for the live preview pane, computed via the fall-forward window algorithm:

{
  "windowVerses": ["In the beginning...", "And the earth...", "And God said..."],
  "reference": "Genesis 1:1-3",
  "bookId": "gen",
  "selectedVerse": 1
}

Called debounced (e.g. 250ms) as the selector changes.

---

Frontend

New component: src/lib/components/SubmitVerse.svelte

Rendered inside WinScreen.svelte, near the existing "📈 See your progress" button, sharing the same neobrutalist .progress-btn styling.

Three visual states:

1. Logged out — button reads "✨ Submit a verse" with a chevron; clicking expands a dropdown identical in structure to the existing logged-out progress dropdown: helper text ("Sign in to submit a verse for a future day") + Apple Sign-In form (POST /auth/apple, with hidden anonymousId) + Google Sign-In form (POST /auth/google). Mirrors the existing markup/classes exactly.

2. Logged in, cooldown active — button is greyed out / disabled ("✨ Submit a verse" + lock/⏳), with a small countdown timer below it counting down to cooldownEndsAt (reuse the CountdownTimer pattern / a compact timer). The dropdown does not expand.

3. Logged in, can submit — clicking expands a panel with:

   • Three cascading native <select> dropdowns: Book (grouped by Testament, <optgroup>), Chapter (1..chapterCount), Verse (1..verseCount). Book list/chapter counts come from /api/bible/structure (fetched once, cached). Selecting book populates chapters; selecting chapter populates verses; selecting verse (or any change) triggers the debounced preview fetch.
   • A live preview card showing the 3-verse window text + reference, updating in real time as the selector changes. (This is not today's verse — it's the user's prospective submission — so showing the book/reference is fine.)
   • A Submit button (neobrutalist). On success, replace the panel with a confirmation: "✅ Your verse is scheduled for August 24, 2026" + the reference, plus a rybbit event. On 429/403, show the server's error message inline.

The win screen already receives isLoggedIn and anonymousId as props; pass them through to SubmitVerse.

Placement note

The button is win-screen-only, which implicitly requires the user to have solved today's puzzle to reach it (the win screen only renders post-solve, and reloads still render it via game-persistence restoring today's completed guesses). This matches the API-level solved-today gate.

---

Reused Existing Functions

Function	Used for
getChapterCount(bookNumber)	Dropdown chapter list bounds
getVerseCount(bookNumber, chapter)	Dropdown verse list bounds
extractVerses(bookNumber, chapter, startVerse, count)	Composing the 3-verse window (per chapter; concat for cross-chapter)
getBookById / getBookByNumber	Book id ↔ number ↔ name resolution
formatReference(bookName, chapter, startVerse, endVerse)	Reference formatting (extend for cross-chapter)
getRandomVerses()	Lazy gap-fill random verse (extended with neighbor avoidance)
getVerseForDate(date)	Returns pre-written submission row unchanged on its day
Auth (locals.user, session cookie)	Identifying the submitter
dailyCompletions (anonymousId = user.id, date)	Solved-today gate

---

Edge Cases & Decisions Log

• Selected verse at a book's start (v1/v2 of chapter 1): fall forward to [v, v+1, v+2]. Always 3 verses, same book.
• Cross-chapter window within a book: allowed; reference formatted as Book C1:S1–C2:E2.
• Cross-book window: never happens (algorithm constrains to one book).
• Concurrent submissions picking the same date: transaction + retry-on-unique-conflict.
• Repeat of an identical 3-verse window: allowed, but the scan skips any candidate date within ±60 days of an existing same-window row, so repeats land far out (this is what "schedule them out a month or two" means in practice).
• Calendar drift (submissions outpace 1/day demand): accepted — no guardrail; a submission may land far in the future. The submitter is shown the resulting date regardless.
• Account deletion: submissions are anonymous; daily_verses rows stay (canonical text). The verse_submissions row's user_id FK should be ON DELETE SET NULL (or cascade-delete the log row) — either is fine since attribution is never shown. The scheduled verse plays out regardless.
• Discord daily-verse cron / RSS / sitemap: unchanged — they call getVerseForDate, which serves pre-written submission rows transparently.
• createdAt on submission-written daily_verses rows: equals submit time, not the future play date. This is consistent with the existing field's meaning ("when the row was created").
• Admin /scheduled-verses view is the sole attribution exception: the public-facing anonymity guarantee does not apply to this admin-only route, which deliberately surfaces user.email for the geohpowell@gmail.com account. All other surfaces (win screen, share text, future potential leaderboard) remain anonymous.

---

Open / Deferred (not blocking v1)

• A small "your upcoming scheduled verses" list on the win screen (data already available from /api/submit-verse/status). Nice-to-have, not required for v1.
• Balancing book distribution beyond no-back-to-back (e.g. avoiding clustering the same book within a month). Explicitly out of scope — only adjacent-day collisions are prevented.
• Email/notification when a user's scheduled verse goes live. Out of scope.