Sync pericope boundaries to local SQLite (set-level hybrid, bandwidth-first)

[mattrace-gloo]

Summary

Fluent Mobile has no local pericope data today. Bible and Record tabs are verse-scoped (bible_texts, recordings keyed by bible_text_id). The verse/pericope toggle work (#407, #408, #409) needs offline-readable pericope groupings (verse ranges, titles, cross-chapter spans) so UI tickets can query SQLite instead of hardcoding stubs.

This ticket adds the data layer only: schema, repository writes, sync step, and read queries. Settings toggle UI (#407) and tab rendering (#408 / #409) are separate.

Engineering decision (Slack, 2026-08-28): pericope = new local table(s) + sync via normal post-login sync — not bundled inside #407/#408 auth/UI work.

Bandwidth decision (2026-09-03): Mobile targets low-connectivity regions. Pericope data on fluent-api is set-scoped (project.pericopeSetId), not assignment-scoped. Production sync MUST use a hybrid set-level path — not one HTTP call per assigned chapter.

projects.pericope_set_id
  → if set content already in SQLite at matching version → keep (0 bytes)
  → else if set is shipped in APK assets → seed SQLite (0 runtime bytes)
  → else GET /pericope-sets/{id} with If-None-Match / version → upsert (1 request per set)
  → queries join set pericopes to assigned chapters locally

Why this blocks downstream tickets

Ticket	What it needs from local data
#407	Note: depends on pericope data model existing; toggle preference itself uses /self/settings (out of scope here).
#408	Pericope unit cards, cross-chapter card placement, partial-recording status — needs pericope→verse mapping per chapter.
#409	Pericope range in Current Unit Title + pericope subtitle when available.
#410 / #411	Cross-granularity take labeling and stitching need stable verse-range metadata.

Without this ticket, implementers must stub pericope lists in UI hooks — exactly what we want to avoid.

Bandwidth / sync contract (authoritative)

Rule	Detail
Grain	Cache by pericope_set_id, not by (project, book, chapter) API round-trips
First paint (known sets)	Seed from APK-bundled FIA/FCBH JSON when set id/name maps to a shipped asset (#447)
Network refresh	One set-level download per distinct pericope_set_id when missing or version mismatch; prefer 304 / ETag when unchanged (fluent-api#309)
Offline	Sync step no-ops when offline; cached + seeded rows remain
Skip	pericope_set_id IS NULL → verse mode only; no error
Forbidden in production sync	Looping GET /projects/{id}/pericopes/{bookCode}/{chapter} for every assigned chapter

Chapter-wise project route may remain for web on-demand use; mobile sync must not use it.

Bulk-by-assigned-chapters (mirror of bulk-texts) is not the primary contract — set-level already covers shared sets with fewer bytes and fewer RTTs.

Fluent API

Exists today

Method	Path	Notes
GET	/pericope-sets	List set metadata
GET	/projects/{id}/pericopes/{bookCode}/{chapter}	Chapter groups — do not use for mobile sync

Projects expose pericopeSetId. Mobile ApiProject / projects table do not map this yet — include here.

Required (sibling)

Method	Path	Ticket
GET	/pericope-sets/{id} (optional ?bookCode=) + ETag / 304	fluent-api#309

Until #309 lands, mobile can still ship schema + asset seed + queries; wire the network refresh when the set endpoint exists.

Response shape for groups (same as chapter route, aggregated by set):

Array<{
  pericopeNumber: string;
  pericopeTitle: string | null;
  verses: Array<{ chapterNumber: number; verseNumber: number }>;
}>

• Empty / missing coverage → treat as verse mode for that book.
• verses may span multiple chapters (required for Update Bible Tab for Verse/Pericope Toggle #408 Note 4).

Current mobile state

• No pericope tables in src/db/schema.ts.
• No FluentAPI pericope methods in src/services/api.ts.
• No sync step in syncAllData / sync.ts.
• books.code (USFM) is already synced.

Proposed scope

1. Schema + migration

• projects.pericope_set_id — nullable integer from project sync.
• Set cache metadata — keyed by pericope_set_id: version / etag / content_hash, source (bundle | api), updated_at.
• Pericope rows — keyed by set (not project): pericope_number, pericope_title, verse membership (prefer normalized child rows for Update Bible Tab for Verse/Pericope Toggle #408 partial-recording lookups).

Follow adding a synced entity: schema.ts + migrations.ts + src/types/db/types.ts.

2. API client + types

• Types mirroring fluent-api schemas (PericopeGroup, PericopeVerseRef, PericopeSet).
• FluentAPI.getPericopeSet(id, opts?) — set-level download with conditional headers once fluent-api#309 exists.
• Optional: FluentAPI.getPericopeSets() for metadata.
• Do not add a sync path that calls getChapterPericopes in a chapter loop.

3. Asset seed (depends on #447)

• Map pericope_set_id / set name → bundled JSON.
• On sync: if set not in DB (or bundle newer than cached version), upsert from asset with source = bundle.
• Exact asset layout / size budget owned by Bundle known FIA/FCBH pericope set JSON in the APK (bandwidth-first seed) #447; this ticket consumes the loader API that ticket exposes.

4. Repository + queries

• Writes: replace/upsert entire set snapshot (from asset or API).
• Reads: getChapterPericopes(projectId, bookId, chapterNumber) — resolve project → pericope_set_id, then filter set rows for that chapter (include cross-chapter groups that touch the chapter).

5. Sync orchestration

In sync.ts after projects (+ assignments as needed for UI, not for pericope HTTP):

1. Collect distinct non-null pericope_set_id from synced projects.
2. For each set: seed from bundle if applicable; else conditional set API fetch; else keep cache.
3. Never N chapter GETs.

6. Tests

• Repository upsert/replace for a set; chapter query including cross-chapter verse lists.
• Sync: skips null set; seeds from mock asset; calls set API once per distinct setId; does not call chapter route.
• Conditional refresh / version match keeps cache without rewrite when possible.

Explicit non-goals

• Chapter-wise sync loop for production mobile (forbidden — see bandwidth contract).
• Bulk-by-assigned-chapters as the primary sync API (use set-level instead).
• Add Verse/Pericope Toggle to Mobile Settings #407 Settings toggle UI / /self/settings displayMode.
• Update Bible Tab for Verse/Pericope Toggle #408 / Update Record Tab for Verse/Pericope Toggle #409 UI.
• Pericope-level recording upload (still verse-keyed via bible_text_id unless a future API ticket says otherwise).
• Source audio timestamp / tick-mark data (Update Bible Tab for Verse/Pericope Toggle #408 Note 3).
• APK asset packaging / licensing / size budget — #447.
• fluent-api set download endpoint implementation — fluent-api#309.

Acceptance criteria

• Migration adds set-keyed pericope storage + projects.pericope_set_id; fresh and upgraded DBs migrate cleanly.
• Sync never issues per-chapter pericope HTTP calls for assigned chapters.
• Distinct pericope_set_id values are hydrated at most once per sync (bundle and/or set API).
• When set API + ETag exist: unchanged set prefers conditional 304 / no rewrite.
• When bundle mapping exists for a set: first hydrate can succeed with zero network for that set.
• Empty / missing set coverage handled as “no pericopes” without failing sync.
• queries.ts exposes chapter pericope groups for (projectId, bookId, chapter) offline.
• Cross-chapter pericope verse lists round-trip correctly.
• Unit tests cover repository, mapping, and sync (including “no chapter-loop” assertion).
• CI gates pass (format:check, lint, architecture-guard, typecheck, npm test -- --ci).

How to verify

1. Sign in with a project that has a known bundled pericope_set_id — airplane mode after install/seed path: chapter query still returns groups.
2. Sign in with a set not in the APK — one set-level request populates SQLite; second sync with unchanged version does not re-download body (304 / skip).
3. Project with null pericope_set_id: sync succeeds; query returns empty list.
4. Inspect logs/network: no .../pericopes/{book}/{chapter} spam for N chapters.

File pointers

Area	Files
Schema / migration	src/db/schema.ts, src/db/migrations.ts
API	src/services/api.ts, src/types/api/
Sync	src/services/sync.ts
Persistence	src/db/repository.ts
Reads	src/db/queries.ts
Project mapping	src/services/mapApiProject.ts, src/types/api/responses.ts
Assets	#447 loader
Web reference (on-demand only)	fluent-web useChapterPericopes.ts

Related

• Sibling (APK seed): #447
• Sibling (set API): fluent-api#309
• Blocks (data): #408, #409
• Related UI: #412, #410, #411
• Web product: fluent-web#285, fluent-web#275 / #276

[mattrace-gloo]

Bandwidth redesign (2026-09-03) — body rewritten.

@B3RN153 your Slack note was right: chapter-wise GET …/pericopes/{book}/{chapter} for every assigned chapter is too expensive for low-connectivity users.

New contract for this ticket:

• Cache by pericope_set_id (set-level), not N chapter HTTP calls
• Seed known FIA/FCBH sets from APK assets when mapped (sibling mobile ticket)
• Refresh via forthcoming GET /pericope-sets/{id} + ETag (sibling fluent-api ticket)
• Do not implement the old chapter-loop sync path

Bulk-by-chapters (mirror of bible_text bulk) is explicitly not the primary approach — set-level is cheaper when projects share a set.

Please hold chapter-loop implementation work against the previous AC.