epic: R2-only DB delivery — remove scripture.db from git + bundle

[CraigBuckmaster]

Context

The current DB delivery architecture has a fundamental coupling problem: content updates require both an R2 upload AND an eas update because scripture.db is tracked in git and bundled into the JS update. This means:

• Every content change triggers a full JS bundle push (unnecessary)
• Forgetting either step leaves users on stale content
• scripture.db is ~94MB tracked in git, approaching GitHub's 100MB limit

Decision: R2 is the single source of truth for scripture.db. The JS bundle never carries the DB. Two separate CI workflows handle content vs. code deploys independently.

---

Architecture After This Epic

content/** changes → content.yml CI → build DB → validate → delta → R2 upload → done
app/** changes     → app.yml CI     → lint/test → eas update → done

App first launch: no DB on device → show download screen → download from R2 → open app
App subsequent launches: ContentUpdater checks R2 manifest in background (already built)

---

Implementation Order (do in this sequence — each step is independently deployable)

Step 1 — Build first-launch download screen

This must exist BEFORE the bundled DB is removed, so there's a fallback.

New file: app/src/screens/DbDownloadScreen.tsx

A full-screen component shown in App.tsx when initDatabase() detects no DB on device. Uses ContentUpdater.downloadFullDb() to fetch from R2. Shows:

• Companion Study logo / title
• "Setting up your library..." subtitle
• Progress bar (gold, #bfa050)
• Download size / percentage
• Error state with retry button

The screen must hook into ContentUpdater.downloadFullDb() — that method already handles download, checksum verification, and writing to the SQLite directory. It just needs a progress callback added.

Modify: app/src/services/ContentUpdater.ts

Add optional onProgress?: (pct: number) => void callback param to downloadFullDb(). The existing FileSystem.downloadAsync doesn't support progress natively — use FileSystem.createDownloadResumable instead, which has a progressCallback. Pass bytes downloaded / total bytes as 0–100 percentage.

Modify: app/src/db/database.ts

Change initDatabase() to return an enum/status instead of throwing, so App.tsx can decide whether to show the download screen or proceed normally:

export type DbInitStatus = 'ready' | 'needs_download';

export async function initDatabase(): Promise<DbInitStatus> {
  if (Platform.OS === 'web') { ... return 'ready'; }
  
  const dbPath = `${FileSystem.documentDirectory}SQLite/scripture.db`;
  const info = await FileSystem.getInfoAsync(dbPath);
  
  if (!info.exists || !info.size || info.size < 1000) {
    return 'needs_download';  // Caller shows download screen
  }
  
  // DB exists — open it (ContentUpdater handles hash check in background)
  db = await SQLite.openDatabaseAsync('scripture.db');
  await db.execAsync('PRAGMA journal_mode=WAL');
  return 'ready';
}

Remove copyAssetDatabaseIfNeeded() entirely. Remove the EXPECTED_CONTENT_HASH / db-manifest.json require at the top of the file — that mechanism is no longer needed since the DB isn't bundled.

Modify: app/App.tsx

Change the init flow:

const [dbStatus, setDbStatus] = useState<'loading' | 'needs_download' | 'ready'>('loading');

useEffect(() => {
  async function init() {
    try {
      await ScreenOrientation.lockAsync(...);
      const status = await initDatabase();
      await initUserDatabase();
      await useSettingsStore.getState().hydrate();
      await useAuthStore.getState().hydrate();
      await usePremiumStore.getState().hydrate();
      pruneEvents(90);
      setDbStatus(status); // 'ready' or 'needs_download'
    } catch (e) {
      console.error('Init error:', e);
      setDbStatus('needs_download'); // Fail safe — show download screen
    }
  }
  init();
}, []);

When dbStatus === 'needs_download', render <DbDownloadScreen onComplete={() => setDbStatus('ready')} /> instead of the normal app tree. DbDownloadScreen calls onComplete() after successful download, which transitions to 'ready' and renders the full app.

---

Step 2 — Write content.yml GitHub Actions workflow

New file: .github/workflows/content.yml

Triggers: push to master, paths: content/**, _tools/build_sqlite*.py, _tools/validate_sqlite.py, _tools/config.py, _tools/content_writer.py, _tools/upload_to_r2.py, _tools/generate_delta.py

Steps:

1. actions/checkout@v4 with fetch-depth: 0
2. actions/setup-python@v5 with python-version: '3.11'
3. pip install boto3
4. python _tools/build_sqlite.py
5. python _tools/validate_sqlite.py
6. python _tools/generate_delta.py (generates + uploads delta to R2, updates manifest)
7. python _tools/upload_to_r2.py (uploads full DB + updates manifest)

Note: generate_delta.py must run BEFORE upload_to_r2.py because it downloads the currently-deployed DB (old version) from R2 to diff against. After upload_to_r2.py runs, the manifest points to the new version.

Required secrets (add to GitHub repo → Settings → Secrets → Actions):

• R2_ACCOUNT_ID
• R2_ACCESS_KEY_ID
• R2_SECRET_ACCESS_KEY
• R2_BUCKET_NAME
• R2_PUBLIC_URL

No eas update in this workflow. No DB committed back to git.

---

Step 3 — Write app.yml GitHub Actions workflow

New file: .github/workflows/app.yml

Close/delete PR #1311 (the broken workflow we already opened) before merging this.

Triggers: push to master, paths: app/** (excluding app/assets/scripture.db which is gitignored anyway)

Steps:

1. actions/checkout@v4
2. actions/setup-node@v4 with node-version: '20', cache npm, cache-dependency-path app/package-lock.json
3. cd app && npm ci
4. npm run lint (fails workflow on lint errors)
5. npm install -g eas-cli@latest
6. eas update --branch production --message "App update from ${{ github.sha }}" --non-interactive

Required secret: EXPO_TOKEN (expo.dev → Account Settings → Access Tokens → create one named github-actions)

No DB rebuild. No R2 upload.

---

Step 4 — Remove DB from EAS bundle

Modify: app/app.json

Confirm assets array doesn't include scripture.db. It currently shows [] so this may already be clean. Double-check.

Delete: app/scripts/setup.js

This script's only purpose is copying scripture.db into app/assets/ for Metro bundling. With the DB no longer bundled, it's dead code. Also remove from package.json scripts (setup, prestart, start, dev, android, ios, web all call it).

Delete: app/scripts/pre-eas-update.js

Same — exists solely to verify/copy DB before eas update. Dead code.

Modify: app/package.json scripts

Remove node scripts/setup.js && prefix from all scripts. The update script becomes just:

"update": "eas update --branch production --message"

Keep: app/assets/db-manifest.json

This file can stay for now — it's tiny and harmless. Once database.ts no longer reads from it (Step 1), it becomes vestigial but won't cause harm. Can be cleaned up later.

---

Step 5 — Remove scripture.db from git

Run locally after all the above is merged and verified working:

# Add to root .gitignore
echo "scripture.db" >> .gitignore

# Remove from git tracking (keeps local file)
git rm --cached scripture.db

# Commit
git add .gitignore
git commit -m "chore: remove scripture.db from git tracking — delivered via R2"
git push

Note: This does NOT delete the local file — you still need it locally to run build_sqlite.py and upload_to_r2.py. It just stops git from tracking changes to it.

---

Files Changed Summary

File	Action
app/src/screens/DbDownloadScreen.tsx	CREATE
app/src/services/ContentUpdater.ts	MODIFY — add progress callback to downloadFullDb()
app/src/db/database.ts	MODIFY — return DbInitStatus, remove bundled DB copy logic
app/App.tsx	MODIFY — handle needs_download state
.github/workflows/content.yml	CREATE
.github/workflows/app.yml	CREATE
.github/workflows/content-deploy.yml	DELETE (PR #1311 — close without merging)
app/scripts/setup.js	DELETE
app/scripts/pre-eas-update.js	DELETE
app/package.json	MODIFY — remove setup.js calls from scripts
.gitignore (root)	MODIFY — add scripture.db
scripture.db	REMOVE from git tracking (git rm --cached)

Key Design Decisions

• database.ts no longer reads db-manifest.json — the hash check against a bundled expected hash is meaningless once the DB isn't bundled
• ContentUpdater owns ALL DB acquisition: first download AND subsequent updates. Same code path.
• generate_delta.py runs before upload_to_r2.py in CI — delta diffs old-vs-new, upload then replaces the manifest's current_version
• The [skip ci] trick from the old workflow is gone — no DB committed back to git at all
• PR ci: auto DB rebuild + EAS deploy on content changes #1311 should be CLOSED WITHOUT MERGING before this epic starts

GitHub Secrets Required

In GitHub repo → Settings → Secrets and variables → Actions:

• EXPO_TOKEN — from expo.dev → Account Settings → Access Tokens
• R2_ACCOUNT_ID — Cloudflare dashboard
• R2_ACCESS_KEY_ID — R2 API token
• R2_SECRET_ACCESS_KEY — R2 API token secret
• R2_BUCKET_NAME — companionstudybucket
• R2_PUBLIC_URL — https://contentcompanionstudy.com

These are already in your local .env file — just copy them to GitHub.

[CraigBuckmaster]

All 5 steps of this epic are implemented and open as separate PRs. They must be merged in order — Step 1 first, Step 5 last. Each step is independently reviewable; the ordering is a deploy-safety constraint, not a code-dependency one.

PRs

#	Step	PR	Summary
1	First-launch download screen	#1353	DbDownloadScreen.tsx + ContentUpdater.downloadFullDb(onProgress) + initDatabase() returns DbInitStatus. App now handles the "no DB on device" case gracefully — must merge before any step that removes the bundled DB.
2	content.yml CI workflow	#1354	Post-merge deploy of scripture.db to R2 on content/** or _tools/*.py changes: build → validate → generate_delta.py → upload_to_r2.py. Coexists with existing content-pipeline.yml (PR-time schema validation).
3	app.yml CI workflow	#1355	Push-to-master JS-only pipeline: npm ci → npm run lint → eas update --branch production. No DB rebuild, no R2 upload.
4	Remove DB from EAS bundle	#1356	Deletes app/scripts/setup.js + app/scripts/pre-eas-update.js, strips node scripts/setup.js && prefixes from package.json scripts, removes setup / prestart / pre-update. Depends on Step 1 landing first (no download fallback otherwise).
5	Remove scripture.db from git	#1357	git rm --cached scripture.db + app/assets/scripture.db, explicit gitignore entry added. Local files preserved. Must land last — merging before Step 1 would ship a broken app.

Recommended merge order

#1353  (Step 1 — download screen)       ← merge first, deploys safely on its own
  ↓
#1354  (Step 2 — content.yml CI)         ← can merge any time after step 1
  ↓
#1355  (Step 3 — app.yml CI)             ← independent of step 2
  ↓
#1356  (Step 4 — remove bundle scripts)  ← requires step 1 in production
  ↓
#1357  (Step 5 — untrack scripture.db)   ← MUST land last

Moving this epic to In Review.

---

Generated by Claude Code