Code: A7. Cross-Device Sync — Supabase auth + bidirectional sync

[CraigBuckmaster]

Summary

Supabase Auth (Apple/Google/Email), bidirectional sync for all user data, offline queue, first-sign-in migration, account deletion. Premium feature.

Sessions: 4–5 (sequential — each builds on the last)

Prerequisite: Craig creates a Supabase project, configures Apple/Google OAuth providers, provides URL + anon key.

Last updated: April 2026 — corrected against current codebase state. See "Drift Corrections" section at bottom.

Session Breakdown

Session	Scope	Key Deliverables
1	Supabase Auth (Apple/Google/Email sign-in)	Native Apple Sign-In, SecureStore JWT, LoginScreen redesign (Facebook → Apple), deps install
2	Sync engine + notes/highlights/bookmarks/topics	Migration v15 (cloud_id, sync_queue extension, soft delete), 20 write functions get sync triggers, push/pull with LWW
3	Reading progress + study depth + plans + settings sync	Union merge for progress, additive for depth, LWW for prefs, plan sync, sync status UI
4	First-sign-in migration + edge cases + account deletion	Bulk upload with progress UI, queue compaction, Edge Function for deletion

Architecture

• Offline-first: Local SQLite is always source of truth. Cloud is backup + sync layer. App never blocks on network.
• Conflict resolution: Last-write-wins for notes/bookmarks/highlights/topics. Union merge for reading progress (never un-completes). Additive for study depth.
• Soft deletes: All deletable entities get deleted_at column. Both sides honor soft deletes.
• Queue-based push: Every local write enqueues to sync_queue. Debounced 3s push. Retry with backoff. Drop after 5 failures.

File Impact

• 10+ new files (services, stores, tests)
• 12+ modified files (auth, db, settings, navigation)
• 6 new dependencies
• ~75 new tests

Decisions (Confirmed)

• Drop Facebook auth: Yes — heavy SDK, low value for Bible app demographic
• Sync bookmarked topics: Yes — lightweight, user expects cross-device
• Sync reading plans/progress: Yes — plan progress should follow user across devices
• Sync study sessions: No — high-frequency device-local analytics, not user content
• Sync flagged content: No — moderation, server-bound via separate mechanism (v14 synced column)

Pre-Session Checklist (Craig's Homework)

• Create Supabase project at supabase.com
• Copy SUPABASE_URL and SUPABASE_ANON_KEY
• Add both to .env, app.json extras, and eas.json build profiles
• Enable Apple provider in Supabase Auth settings
  • Create Apple Services ID in Apple Developer Console
  • Generate a key for Sign in with Apple
  • Paste credentials into Supabase
• Enable Google provider in Supabase Auth settings
  • Create OAuth 2.0 Client ID in Google Cloud Console
  • Paste Client ID + Secret into Supabase
• Confirm scheme: "scripture" in app.json (already set ✓)
• Confirm whether any production users are on migration v14 already (affects sync_queue strategy)

---

Full Dev Plan

A7. Cross-Device Sync — Dev Plan (Corrected April 2026)

Scope: Supabase Auth (Apple/Google/Email), bidirectional sync for all user data,
offline queue, first-sign-in migration, account deletion.

Sessions: 4–5 (can't be parallelized — each builds on the last)

Prerequisite: Craig creates a Supabase project at supabase.com, provides the
SUPABASE_URL and SUPABASE_ANON_KEY, and configures Apple/Google OAuth providers
in the Supabase dashboard.

---

Current State (April 2026)

What	Status
authStore.ts	178 lines, functional, Supabase client via env vars (process.env.SUPABASE_URL)
LoginScreen.tsx	336 lines, Google + Facebook + Email — missing Apple Sign-In
SignUpScreen.tsx	295 lines, exists and working
ForgotPasswordScreen.tsx	184 lines, exists and working
lib/supabase.ts	Stub using process.env.SUPABASE_URL pattern, CONFIGURED = !!process.env.SUPABASE_URL
lib/oauthHelpers.ts	Working Google/Facebook OAuth via expo-auth-session
db/user.ts	Barrel re-export → userQueries.ts (496 lines) + userMutations.ts (372 lines)
userDatabase.ts	14 migrations, no cloud_id, no sync_state, no soft delete. sync_queue exists (v14) but incomplete schema
Cloud schema	None — Supabase project doesn't exist yet
Tests	authStore.test.ts exists, supabaseMock.js exists
Dependencies installed	expo-auth-session, expo-web-browser, expo-crypto
Dependencies NOT installed	@supabase/supabase-js, expo-secure-store, expo-apple-authentication, @react-native-community/netinfo, @react-native-async-storage/async-storage, react-native-url-polyfill

---

Session 1: Supabase Auth (Apple/Google/Email Sign-In)

Goal

Users can sign in with Apple, Google, or email/password. Session persists across
app restarts. Sign-out clears session. Account section in MoreMenu shows user info.
Facebook auth removed.

Pre-session (Craig)

1. Create Supabase project → copy URL + anon key
2. Add SUPABASE_URL and SUPABASE_ANON_KEY to .env, app.json extras, and eas.json build profiles
3. In Supabase Dashboard → Authentication → Providers:
   • Enable Apple: add Services ID, Team ID, Key ID, private key
   • Enable Google: add Client ID + secret from Google Cloud Console
   • Enable Email: already on by default
4. In Apple Developer Console:
   • Create a Services ID for "Sign in with Apple"
   • Register the Supabase callback URL as a redirect URI

1A. Dependencies

cd app/
npx expo install @supabase/supabase-js @react-native-async-storage/async-storage \
 react-native-url-polyfill expo-secure-store expo-apple-authentication \
 @react-native-community/netinfo

Already installed: expo-auth-session, expo-web-browser, expo-crypto.

expo-apple-authentication is needed for native Apple Sign-In on iOS (the App
Store rejects web-based Apple OAuth — it must use the native credential API).

1B. Files to Modify

lib/supabase.ts — Activate the client

// Changes:
// 1. Keep env var pattern (already correct): process.env.SUPABASE_URL
// 2. Switch auth storage from AsyncStorage to expo-secure-store
// (SecureStore is encrypted; AsyncStorage is not)
// 3. Replace the placeholder SupabaseClient interface with actual import

import * as SecureStore from 'expo-secure-store';

const ExpoSecureStoreAdapter = {
 getItem: (key: string) => SecureStore.getItemAsync(key),
 setItem: (key: string, value: string) => SecureStore.setItemAsync(key, value),
 removeItem: (key: string) => SecureStore.deleteItemAsync(key),
};

// In createClient options:
auth: {
 storage: ExpoSecureStoreAdapter,
 autoRefreshToken: true,
 persistSession: true,
 detectSessionInUrl: false,
 flowType: 'pkce',
}

Note: expo-secure-store needs to be installed (see 1A above).

lib/oauthHelpers.ts — Add Apple Sign-In

Add signInWithApple() as a separate function (not a new case in signInWithProvider —
Apple uses native credential flow via signInWithIdToken, architecturally different from
the browser-redirect flow used by Google):

export async function signInWithApple(): Promise<{ error?: string }> {
 const AppleAuthentication = require('expo-apple-authentication');
 const supabase = getSupabase();
 if (!supabase) return { error: 'Auth not available' };

 try {
 const credential = await AppleAuthentication.signInAsync({
 requestedScopes: [
 AppleAuthentication.AppleAuthenticationScope.FULL_NAME,
 AppleAuthentication.AppleAuthenticationScope.EMAIL,
 ],
 });

 if (!credential.identityToken) {
 return { error: 'No identity token from Apple' };
 }

 const { error } = await supabase.auth.signInWithIdToken({
 provider: 'apple',
 token: credential.identityToken,
 });

 if (error) return { error: error.message };
 return {};
 } catch (err: any) {
 if (err.code === 'ERR_REQUEST_CANCELED') return { error: 'Sign-in canceled' };
 return { error: err.message ?? 'Apple sign-in failed' };
 }
}

stores/authStore.ts — Add Apple, drop Facebook

// Changes:
// 1. Add signInWithApple method
// 2. Remove signInWithFacebook from interface and implementation
// 3. Keep signInWithGoogle and signInWithEmail as-is

signInWithApple: async () => {
 const { isSupabaseAvailable } = getAuth();
 if (!isSupabaseAvailable()) {
 return { error: 'Sign-in requires a development build.' };
 }
 set({ isLoading: true });
 try {
 const { signInWithApple } = require('../lib/oauthHelpers');
 return await signInWithApple();
 } finally {
 set({ isLoading: false });
 },
},

screens/LoginScreen.tsx — Replace Facebook with Apple

Since SignUpScreen.tsx and ForgotPasswordScreen.tsx already exist with working
navigation routes, the redesign is simpler than originally planned:

1. Replace Facebook button → Apple button (black bg, white text, Apple icon)
2. Keep existing Google button
3. Keep email form + existing navigation to SignUp / ForgotPassword
4. Add "Continue without signing in" footer (navigation.goBack())
5. Add privacy reassurance text
6. Remove signInWithFacebook import/usage

Layout (top to bottom):

┌─────────────────────────────────┐
│ ScreenHeader "Sign In" │
│ │
│ "Sign in to sync your study │
│ data across devices." │
│ │
│ ┌─────────────────────────┐ │
│ │ ◉ Continue with Apple │ │ ← Black button, white text, Apple icon
│ └─────────────────────────┘ │
│ ┌─────────────────────────┐ │
│ │ G Continue with Google│ │ ← White button, dark text
│ └─────────────────────────┘ │
│ │
│ ──────── or ──────── │
│ │
│ [ Email ] │
│ [ Password ] │
│ ┌─────────────────────────┐ │
│ │ Sign In │ │ ← Gold button
│ └─────────────────────────┘ │
│ Forgot password? ← navigates │
│ to ForgotPasswordScreen │
│ │
│ Don't have an account? Sign Up │ ← navigates to SignUpScreen
│ │
│ ───────────────────────────── │
│ Your study data stays on your │
│ device even without an │
│ account. Signing in adds │
│ cross-device sync. │
│ │
│ ┌─────────────────────────┐ │
│ │ Continue without │ │ ← Text button, muted
│ │ signing in │ │
│ └─────────────────────────┘ │
└─────────────────────────────────┘

screens/MoreMenuScreen.tsx — Enhanced auth section

Currently shows user email when signed in, "Sign In" when not. Enhance:

┌──────────────────────────────────────┐
│ SIGNED IN STATE: │
│ │
│ [Avatar] Craig Buckmaster │
│ craig@companionstudy.app │
│ ✓ Synced 2 min ago │ ← sync status line (Session 2)
│ │
│ [Sign Out] │
│ │
│ NOT SIGNED IN: │
│ │
│ [Lock icon] Sign In │
│ Sync your study data │
│ across devices │
│ │
└──────────────────────────────────────┘

1C. App Config Changes

app.json — Add Apple auth entitlement

{
 "expo": {
 "ios": {
 "usesAppleSignIn": true
 },
 "plugins": [
 "expo-apple-authentication",
 // ... existing plugins (expo-font, expo-screen-orientation, etc.)
 ]
 }
}

scheme: "scripture" is already set ✓

1D. Tests

__tests__/stores/authStore.test.ts — Extend

Add tests for:

• signInWithApple dispatches correctly when available
• signInWithApple returns error in Expo Go
• Facebook methods are removed
• Hydrate restores session from SecureStore
• Sign-out clears SecureStore + local profile
• Error states propagate correctly

__tests__/screens/LoginScreen.test.tsx — New

describe('LoginScreen', () => {
 it('renders Apple, Google, and email sign-in options', () => { ... });
 it('does NOT render Facebook button', () => { ... });
 it('navigates to SignUpScreen on "Sign Up" press', () => { ... });
 it('navigates to ForgotPasswordScreen on "Forgot password" press', () => { ... });
 it('shows error message on failed sign-in', async () => { ... });
 it('navigates back on "Continue without signing in"', () => { ... });
});

__tests__/lib/supabase.test.ts — New

describe('supabase client', () => {
 it('returns null when env vars not set', () => { ... });
 it('creates client singleton when configured', () => { ... });
 it('uses SecureStore adapter for auth storage', () => { ... });
});

1E. Session 1 Deliverables

Deliverable	Verification
Apple Sign-In works on device	Sign in → session persists on restart
Google Sign-In works	Sign in → session persists
Email sign-in/sign-up works	Create account → sign in → session persists
Sign-out clears session	Sign out → MoreMenu shows "Sign In"
Facebook removed	No Facebook button in UI, no Facebook in authStore
Forgot password works	Request reset → email received
Tests pass	npm test -- --testPathPattern="auth|Login|supabase"

---

Session 2: Sync Engine + Notes/Highlights/Bookmarks/Topics

Goal

All user writes (notes, highlights, bookmarks, collections, note links, topic bookmarks)
are queued for sync. On app launch and periodically, queued changes push to Supabase.
Pull merges cloud changes into local SQLite.

2A. Supabase Cloud Schema

Run in Supabase SQL Editor (one-time setup):

-- ══════════════════════════════════════════════════
-- TABLES
-- ══════════════════════════════════════════════════

CREATE TABLE user_notes (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 verse_ref TEXT NOT NULL,
 note_text TEXT NOT NULL,
 tags_json TEXT DEFAULT '[]',
 collection_id UUID,
 created_at TIMESTAMPTZ DEFAULT now(),
 updated_at TIMESTAMPTZ DEFAULT now(),
 deleted_at TIMESTAMPTZ
);

CREATE TABLE study_collections (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 name TEXT NOT NULL,
 description TEXT DEFAULT '',
 color TEXT DEFAULT '#bfa050',
 created_at TIMESTAMPTZ DEFAULT now(),
 updated_at TIMESTAMPTZ DEFAULT now(),
 deleted_at TIMESTAMPTZ
);

ALTER TABLE user_notes
 ADD CONSTRAINT fk_notes_collection
 FOREIGN KEY (collection_id) REFERENCES study_collections(id)
 ON DELETE SET NULL;

CREATE TABLE note_links (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 from_note_id UUID NOT NULL REFERENCES user_notes(id) ON DELETE CASCADE,
 to_note_id UUID NOT NULL REFERENCES user_notes(id) ON DELETE CASCADE,
 created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE bookmarks (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 verse_ref TEXT NOT NULL,
 label TEXT,
 created_at TIMESTAMPTZ DEFAULT now(),
 deleted_at TIMESTAMPTZ
);

CREATE TABLE verse_highlights (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 verse_ref TEXT NOT NULL,
 color TEXT NOT NULL,
 collection_id TEXT,
 note TEXT,
 created_at TIMESTAMPTZ DEFAULT now(),
 deleted_at TIMESTAMPTZ,
 UNIQUE(user_id, verse_ref)
);

CREATE TABLE highlight_collections (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 name TEXT NOT NULL,
 color TEXT NOT NULL,
 sort_order INTEGER DEFAULT 0,
 created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE bookmarked_topics (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 topic_id TEXT NOT NULL,
 topic_type TEXT NOT NULL DEFAULT 'official',
 cached_title TEXT,
 cached_summary TEXT,
 created_at TIMESTAMPTZ DEFAULT now(),
 deleted_at TIMESTAMPTZ,
 UNIQUE(user_id, topic_id, topic_type)
);

-- ══════════════════════════════════════════════════
-- ROW LEVEL SECURITY (all tables)
-- ══════════════════════════════════════════════════

DO $$
DECLARE
 tbl TEXT;
BEGIN
 FOR tbl IN VALUES
 ('user_notes'), ('study_collections'), ('note_links'),
 ('bookmarks'), ('verse_highlights'), ('highlight_collections'),
 ('bookmarked_topics')
 LOOP
 EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', tbl);

 EXECUTE format(
 'CREATE POLICY "Users own their %1$s" ON %1$I
 FOR ALL USING (user_id = auth.uid())
 WITH CHECK (user_id = auth.uid())', tbl);
 END LOOP;
END $$;

-- ══════════════════════════════════════════════════
-- INDEXES
-- ══════════════════════════════════════════════════

CREATE INDEX idx_notes_user ON user_notes(user_id);
CREATE INDEX idx_notes_updated ON user_notes(user_id, updated_at);
CREATE INDEX idx_collections_user ON study_collections(user_id);
CREATE INDEX idx_note_links_user ON note_links(user_id);
CREATE INDEX idx_bookmarks_user ON bookmarks(user_id);
CREATE INDEX idx_bookmarks_updated ON bookmarks(user_id, created_at);
CREATE INDEX idx_highlights_user ON verse_highlights(user_id);
CREATE INDEX idx_highlight_collections_user ON highlight_collections(user_id);
CREATE INDEX idx_bookmarked_topics_user ON bookmarked_topics(user_id);

-- ══════════════════════════════════════════════════
-- AUTO-UPDATE updated_at
-- ══════════════════════════════════════════════════

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
 NEW.updated_at = now();
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notes_updated BEFORE UPDATE ON user_notes
 FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_collections_updated BEFORE UPDATE ON study_collections
 FOR EACH ROW EXECUTE FUNCTION update_updated_at();

2B. Local Database Migration

userDatabase.ts — Migration v15: Sync infrastructure

The existing sync_queue table (from v14) has an incomplete schema. v15 extends it
and adds cloud sync columns to all user tables.

-- cloud_id for all synced tables
ALTER TABLE user_notes ADD COLUMN cloud_id TEXT;
ALTER TABLE study_collections ADD COLUMN cloud_id TEXT;
ALTER TABLE note_links ADD COLUMN cloud_id TEXT;
ALTER TABLE bookmarks ADD COLUMN cloud_id TEXT;
ALTER TABLE verse_highlights ADD COLUMN cloud_id TEXT;
ALTER TABLE highlight_collections ADD COLUMN cloud_id TEXT;
ALTER TABLE bookmarked_topics ADD COLUMN cloud_id TEXT;

-- Soft delete support
ALTER TABLE user_notes ADD COLUMN deleted_at TEXT;
ALTER TABLE study_collections ADD COLUMN deleted_at TEXT;
ALTER TABLE bookmarks ADD COLUMN deleted_at TEXT;
ALTER TABLE verse_highlights ADD COLUMN deleted_at TEXT;
ALTER TABLE bookmarked_topics ADD COLUMN deleted_at TEXT;

-- Extend existing sync_queue (from v14) with routing columns
ALTER TABLE sync_queue ADD COLUMN table_name TEXT NOT NULL DEFAULT '';
ALTER TABLE sync_queue ADD COLUMN local_id TEXT NOT NULL DEFAULT '';

-- Sync state table (last_pull_at, migration flags)
CREATE TABLE IF NOT EXISTS sync_state (
 key TEXT PRIMARY KEY,
 value TEXT NOT NULL
);

-- LWW support for preferences
ALTER TABLE user_preferences ADD COLUMN updated_at TEXT;

-- Indexes for merge lookups
CREATE UNIQUE INDEX IF NOT EXISTS idx_notes_cloud_id ON user_notes(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_collections_cloud_id ON study_collections(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_note_links_cloud_id ON note_links(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_bookmarks_cloud_id ON bookmarks(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_highlights_cloud_id ON verse_highlights(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_hl_collections_cloud_id ON highlight_collections(cloud_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_bookmarked_topics_cloud_id ON bookmarked_topics(cloud_id);

2C. New Files

services/syncEngine.ts — Core sync orchestrator (~400 lines)

The sync engine has three responsibilities:

1. Queue writes — After every local write, enqueue a sync operation:

export async function enqueueSync(
 tableName: SyncableTable,
 localId: string | number,
 operation: 'INSERT' | 'UPDATE' | 'DELETE',
 payload: Record<string, any>,
): Promise<void> {
 const db = getUserDb();
 await db.runAsync(
 `INSERT INTO sync_queue (table_name, local_id, operation, payload_json)
 VALUES (?, ?, ?, ?)`,
 [tableName, String(localId), operation, JSON.stringify(payload)],
 );
 schedulePush();
}

2. Push — Flush the queue to Supabase:

export async function pushQueue(): Promise<SyncResult> {
 const supabase = getSupabase();
 if (!supabase) return { pushed: 0, failed: 0 };

 const session = await supabase.auth.getSession();
 if (!session.data.session) return { pushed: 0, failed: 0 };

 const db = getUserDb();
 const pending = await db.getAllAsync<SyncQueueRow>(
 'SELECT * FROM sync_queue ORDER BY created_at ASC LIMIT 100',
 );

 let pushed = 0, failed = 0;

 for (const item of pending) {
 try {
 const payload = JSON.parse(item.payload_json);
 await pushItem(supabase, item.table_name, item.operation, payload);
 await db.runAsync('DELETE FROM sync_queue WHERE id = ?', [item.id]);
 pushed++;
 } catch (err) {
 const retries = (item.attempts ?? 0) + 1;
 if (retries >= 5) {
 await db.runAsync('DELETE FROM sync_queue WHERE id = ?', [item.id]);
 logger.error('Sync', `Dropping item after 5 retries`, item);
 } else {
 await db.runAsync(
 'UPDATE sync_queue SET attempts = ?, last_error = ? WHERE id = ?',
 [retries, String(err), item.id],
 );
 }
 failed++;
 }
 }

 return { pushed, failed };
}

3. Pull — Fetch changes since last sync and merge:

export async function pullChanges(): Promise<SyncResult> {
 const supabase = getSupabase();
 if (!supabase) return { pulled: 0, conflicts: 0 };

 const db = getUserDb();
 const lastSync = await getSyncState('last_pull_at');
 const since = lastSync ?? '1970-01-01T00:00:00Z';

 let pulled = 0, conflicts = 0;

 for (const table of SYNCABLE_TABLES) {
 const { data, error } = await supabase
 .from(table)
 .select('*')
 .gte('updated_at', since)
 .order('updated_at', { ascending: true });

 if (error) { logger.error('Sync', `Pull failed for ${table}`, error); continue; }

 for (const row of data ?? []) {
 const result = await mergeRow(db, table, row);
 if (result === 'merged') pulled++;
 if (result === 'conflict') conflicts++;
 }
 }

 await setSyncState('last_pull_at', new Date().toISOString());
 return { pulled, conflicts };
}

Merge logic — Last Write Wins:

async function mergeRow(db, table, cloudRow): Promise<'merged' | 'conflict' | 'skipped'> {
 const local = await db.getFirstAsync(`SELECT * FROM ${table} WHERE cloud_id = ?`, [cloudRow.id]);

 if (!local) {
 await insertFromCloud(db, table, cloudRow);
 return 'merged';
 }

 if (cloudRow.deleted_at) {
 await db.runAsync(`DELETE FROM ${table} WHERE cloud_id = ?`, [cloudRow.id]);
 return 'merged';
 }

 const cloudTime = new Date(cloudRow.updated_at).getTime();
 const localTime = new Date(local.updated_at).getTime();

 if (cloudTime > localTime) {
 await updateFromCloud(db, table, cloudRow, local.id);
 return 'merged';
 }

 return cloudTime === localTime ? 'skipped' : 'conflict';
}

services/syncScheduler.ts — Background sync timing (~80 lines)

// Schedules sync operations:
// 1. On app foreground (via AppState listener)
// 2. Every 5 minutes while app is active
// 3. On NetInfo change from offline → online
// 4. Debounced 3s after any local write (via enqueueSync)

import { AppState } from 'react-native';
import NetInfo from '@react-native-community/netinfo';

stores/syncStore.ts — Sync state for UI (~60 lines)

interface SyncState {
 isSyncing: boolean;
 lastSyncAt: string | null;
 pendingCount: number;
 error: string | null;
 triggerSync: () => Promise<void>;
 refreshPendingCount: () => Promise<void>;
}

2D. Modifying db/userMutations.ts — Add Sync Triggers

Note: The original issue referenced db/user.ts. That file was split into
userQueries.ts (reads) and userMutations.ts (writes). All sync triggers
go in userMutations.ts.

Every write function gets a sync enqueue call. The pattern:

export async function saveNote(verseRef: string, text: string): Promise<number> {
 const cloudId = generateUUID();
 const result = await getUserDb().runAsync(
 "INSERT INTO user_notes (verse_ref, note_text, cloud_id) VALUES (?, ?, ?)",
 [verseRef, text, cloudId]
 );
 const noteId = result.lastInsertRowId;
 await getUserDb().runAsync(
 "INSERT INTO notes_fts(rowid, note_text) VALUES (?, ?)",
 [noteId, text]
 );

 enqueueSync('user_notes', noteId, 'INSERT', {
 id: cloudId, verse_ref: verseRef, note_text: text,
 tags_json: '[]', collection_id: null,
 }).catch(noop);

 return noteId;
}

Soft delete pattern:

export async function deleteNote(id: number): Promise<void> {
 const row = await getUserDb().getFirstAsync<{ cloud_id: string | null }>(
 'SELECT cloud_id FROM user_notes WHERE id = ?', [id]
 );

 await getUserDb().runAsync(
 "UPDATE user_notes SET deleted_at = datetime('now') WHERE id = ?", [id]
 );
 await getUserDb().runAsync("DELETE FROM notes_fts WHERE rowid = ?", [id]);

 if (row?.cloud_id) {
 enqueueSync('user_notes', id, 'DELETE', { id: row.cloud_id }).catch(noop);
 }
}

Complete function list (20 functions — corrected from original 14):

#	Function	Table	Operation	Notes
1	saveNote	user_notes	INSERT	Generate cloud_id
2	updateNote	user_notes	UPDATE
3	deleteNote	user_notes	DELETE (soft)	Set deleted_at, remove from FTS
4	updateNoteTags	user_notes	UPDATE	Added — missing from original
5	setNoteCollection	user_notes	UPDATE	Added — missing from original
6	addBookmark	bookmarks	INSERT	Generate cloud_id
7	removeBookmark	bookmarks	DELETE (soft)
8	setHighlight	verse_highlights	UPSERT	Generate cloud_id on insert
9	removeHighlight	verse_highlights	DELETE (soft)
10	createCollection	study_collections	INSERT	Generate cloud_id
11	updateCollection	study_collections	UPDATE
12	deleteCollection	study_collections	DELETE (soft)
13	createHighlightCollection	highlight_collections	INSERT	Local ID is TEXT — use as cloud_id
14	deleteHighlightCollection	highlight_collections	DELETE (hard→soft)
15	linkNotes	note_links	INSERT	Generate cloud_id
16	unlinkNotes	note_links	DELETE (hard)
17	bookmarkTopic	bookmarked_topics	UPSERT	Added — generate cloud_id
18	unbookmarkTopic	bookmarked_topics	DELETE (soft)	Added

NOT synced (local-only):

• startStudySession, endStudySession, recordSessionEvent — device-local analytics
• flagContent — moderation, separate sync mechanism (v14 synced column)
• upsertAuthProfile, clearAuthProfile — auth state, not user content
• startPlan, completePlanDay, abandonPlan — synced in Session 3
• resetToNewUser — dev tool

UUID generation: Use expo-crypto (already installed):

import * as Crypto from 'expo-crypto';
export function generateUUID(): string {
 return Crypto.randomUUID();
}

2E. Modifying db/userQueries.ts — Add Soft Delete Filters

All read queries touching soft-deletable tables must add WHERE deleted_at IS NULL:

Function	Table
getNotesForChapter	user_notes
getNoteCount	user_notes
getAllNotes	user_notes
searchNotes	user_notes
searchNotesFTS	user_notes
getBookmarks	bookmarks
isBookmarked	bookmarks
getHighlightsForChapter	verse_highlights
getAllHighlights	verse_highlights
getCollections	study_collections
getCollection	study_collections
getNotesInCollection	user_notes
getCollectionNoteCounts	user_notes
getAllTags	user_notes
getNotesByTag	user_notes
getLinkedNotes	note_links → user_notes
getReferencingNotes	note_links → user_notes
getBookmarkedTopics	bookmarked_topics
isTopicBookmarked	bookmarked_topics

2F. Tests

__tests__/services/syncEngine.test.ts — New (~250 lines)

describe('syncEngine', () => {
 describe('enqueueSync', () => {
 it('inserts queue entry with table_name and local_id', async () => { ... });
 it('schedules a push after enqueue', async () => { ... });
 });
 describe('pushQueue', () => {
 it('pushes INSERT operations to Supabase upsert', async () => { ... });
 it('pushes DELETE as soft-delete', async () => { ... });
 it('removes queue entries after success', async () => { ... });
 it('increments attempts on failure', async () => { ... });
 it('drops items after 5 failed attempts', async () => { ... });
 it('does nothing when not authenticated', async () => { ... });
 });
 describe('pullChanges', () => {
 it('fetches rows updated since last_pull_at', async () => { ... });
 it('inserts new cloud rows locally with cloud_id', async () => { ... });
 it('LWW: cloud wins on newer timestamp', async () => { ... });
 it('LWW: local wins on newer timestamp', async () => { ... });
 it('soft-deletes locally when cloud has deleted_at', async () => { ... });
 });
});

__tests__/db/userMutations.test.ts — New (~150 lines)

Test each write function enqueues sync correctly:

describe('userMutations sync integration', () => {
 it('saveNote enqueues INSERT with cloud_id', async () => { ... });
 it('updateNote enqueues UPDATE', async () => { ... });
 it('deleteNote soft-deletes and enqueues DELETE', async () => { ... });
 it('updateNoteTags enqueues UPDATE', async () => { ... });
 it('bookmarkTopic enqueues UPSERT', async () => { ... });
 // ... one test per write function
});

__tests__/db/userQueries.test.ts — New (~80 lines)

describe('userQueries soft-delete filtering', () => {
 it('getAllNotes excludes soft-deleted rows', async () => { ... });
 it('getBookmarks excludes soft-deleted rows', async () => { ... });
 it('getBookmarkedTopics excludes soft-deleted rows', async () => { ... });
 // ... one test per query with soft-delete filter
});

2G. Session 2 Deliverables

Deliverable	Verification
Cloud schema deployed	Tables visible in Supabase dashboard
Migration v15 runs cleanly	App launches, cloud_id columns exist
Note CRUD syncs	Create note → appears in Supabase → pull on second device
Highlight CRUD syncs	Same
Bookmark CRUD syncs	Same
Collection CRUD syncs	Same
Topic bookmark syncs	Same
Soft deletes work	Delete note → deleted_at set → synced → hidden on other device
Offline queue persists	Airplane mode → create note → reconnect → note pushes
LWW conflict resolution	Edit same note on 2 devices → newer wins
Tests pass	npm test -- --testPathPattern="sync|user"

---

Session 3: Reading Progress + Study Depth + Plans + Settings Sync

Goal

Sync reading progress (union merge), study depth (additive), reading plan progress,
and user preferences (LWW).

3A. Additional Cloud Tables

CREATE TABLE reading_progress (
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 book_id TEXT NOT NULL,
 chapter_num INTEGER NOT NULL,
 completed_at TIMESTAMPTZ,
 PRIMARY KEY (user_id, book_id, chapter_num)
);

CREATE TABLE study_depth (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 chapter_id TEXT NOT NULL,
 section_id TEXT NOT NULL,
 panel_type TEXT NOT NULL,
 first_opened_at TIMESTAMPTZ DEFAULT now(),
 UNIQUE(user_id, section_id, panel_type)
);

CREATE TABLE user_preferences (
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 key TEXT NOT NULL,
 value TEXT NOT NULL,
 updated_at TIMESTAMPTZ DEFAULT now(),
 PRIMARY KEY (user_id, key)
);

CREATE TABLE reading_streaks (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 date TEXT NOT NULL,
 chapters_read INTEGER NOT NULL DEFAULT 0,
 books_touched TEXT,
 UNIQUE(user_id, date)
);

CREATE TABLE plan_progress (
 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 plan_id TEXT NOT NULL,
 day_num INTEGER NOT NULL,
 completed_at TIMESTAMPTZ,
 PRIMARY KEY (user_id, plan_id, day_num)
);

-- RLS for all new tables
DO $$
DECLARE tbl TEXT;
BEGIN
 FOR tbl IN VALUES
 ('reading_progress'), ('study_depth'), ('user_preferences'),
 ('reading_streaks'), ('plan_progress')
 LOOP
 EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', tbl);
 EXECUTE format(
 'CREATE POLICY "Users own their %1$s" ON %1$I
 FOR ALL USING (user_id = auth.uid())
 WITH CHECK (user_id = auth.uid())', tbl);
 END LOOP;
END $$;

3B. Special Merge Strategies

Reading Progress — Union Merge

Never un-completes. If either side says completed, it stays completed.
Keep the earliest completed_at timestamp.

Study Depth — Additive Only

Append-only. Cloud accumulates all panels ever opened. Merge = INSERT OR IGNORE.

Reading Plans — Union Merge

Plan progress uses same union logic as reading progress — once a day is completed,
it stays completed.

User Preferences — LWW

Simple key-value pairs with updated_at for conflict resolution.

3C. Sync Triggers for Session 3 Functions

recordVisit in userMutations.ts: Enqueue sync for reading_progress.

completePlanDay in userMutations.ts: Enqueue sync for plan_progress.

settingsStore.ts preference setters: Each setPreference call gets a sync enqueue.

3D. UI: Sync Status in Settings

Add a "Sync" section to SettingsScreen:

┌──────────────────────────────────────┐
│ SYNC │
│ │
│ Status ✓ Up to date │
│ Last sync 2 min ago │
│ Pending 0 changes │
│ │
│ [Sync Now] │
│ │
│ Only visible when signed in. │
└──────────────────────────────────────┘

3E. Session 3 Deliverables

Deliverable	Verification
Reading progress syncs (union)	Read chapter → mark on device A → appears on device B
Never un-completes	Delete cloud row → local stays completed
Study depth syncs (additive)	Open panel → record appears in Supabase
Plan progress syncs (union)	Complete day on A → appears on B
Preferences sync (LWW)	Change font size on A → appears on B
Sync status UI in Settings	Shows status, last sync time, pending count
Tests pass	npm test -- --testPathPattern="sync|settings"

---

Session 4: First-Sign-In Migration + Edge Cases + Account Deletion

Goal

Handle the critical first-sign-in scenario (user has months of local data),
edge cases (offline→online, large datasets, stale queues), and App Store-compliant
account deletion.

4A. First-Sign-In Migration

services/initialMigration.ts — New (~150 lines)

When a user signs in for the first time, they may have hundreds of notes, bookmarks,
and reading progress entries with no cloud_id. One-time bulk upload:

1. Check if migration already happened (sync_state.initial_migration_complete)
2. Check cloud for existing data (returning user on new device?)
3. Assign cloud_id to all local rows missing them
4. Upload in batches of 50 (collections first for FK resolution, then notes, etc.)
5. Mark complete

Migration UI — SyncMigrationSheet

Bottom-sheet showing progress: "Uploading 47 notes, 312 chapters read..." with
progress bar. Auto-dismisses on completion.

4B. Edge Cases

• Offline → online flush: pushQueue() processes max 100 items per call, chains until empty
• Queue compaction: When queue > 200 items, compact by keeping only latest per table+local_id
• Note links with unmapped IDs: Upload notes before note_links; defer links with unresolved refs
• Interrupted migration: initial_migration_complete only set on full success; idempotent upserts prevent duplicates on retry
• Stale INSERT+DELETE pairs: Compaction drops both when INSERT never pushed

4C. Account Deletion

services/accountDeletion.ts — New (~50 lines)

1. Invoke Supabase Edge Function delete-user-data (service_role deletes all user tables + auth account)
2. Clear local auth state
3. Stop sync scheduler
4. Local study data preserved on device

Supabase Edge Function: delete-user-data

Server-side function using service_role key to delete all user rows across all tables
and delete the auth account. Client-side RLS can't cascade across tables.

Deletion UI in SettingsScreen

"Delete Account" in a Danger Zone section (only visible when signed in).
Two-step confirmation: alert → type "DELETE" to confirm.

4D. Session 4 Deliverables

Deliverable	Verification
First sign-in migration works	User with 6 months data → sign in → all data in Supabase
Returning user on new device	Sign in on device B → all data appears
Migration progress UI	Bottom sheet shows progress during initial upload
Offline queue compaction	500 offline edits → compacted → clean push
Account deletion works	Delete → cloud data gone → local data preserved
Two-step delete confirmation	Can't accidentally delete
Edge function deployed	delete-user-data in Supabase Functions
Tests pass	Full suite green

---

Complete File Inventory (Corrected)

New Files (10+)

File	Lines (est.)	Session
services/syncEngine.ts	~400	2
services/syncScheduler.ts	~80	2
services/initialMigration.ts	~150	4
services/accountDeletion.ts	~50	4
stores/syncStore.ts	~60	2
__tests__/services/syncEngine.test.ts	~350	2-3
__tests__/services/syncScheduler.test.ts	~60	2
__tests__/db/userMutations.test.ts	~150	2
__tests__/db/userQueries.test.ts	~80	2
__tests__/services/initialMigration.test.ts	~120	4
__tests__/services/accountDeletion.test.ts	~60	4
__tests__/screens/LoginScreen.test.tsx	~80	1

Modified Files (12+)

File	Change Summary	Session
lib/supabase.ts	Activate, SecureStore adapter, real import	1
lib/oauthHelpers.ts	Add signInWithApple()	1
stores/authStore.ts	Add Apple, remove Facebook	1
screens/LoginScreen.tsx	Replace Facebook → Apple, add guest option	1
screens/MoreMenuScreen.tsx	Enhanced auth section + sync status	1, 3
screens/SettingsScreen.tsx	Sync status section + account deletion	3, 4
db/userDatabase.ts	Migration v15: cloud_id, sync_queue extension, soft delete	2
db/userMutations.ts	Sync triggers on all 18+ write functions + soft delete	2, 3
db/userQueries.ts	WHERE deleted_at IS NULL on 19+ queries	2
stores/settingsStore.ts	Sync preference changes	3
app.json	Apple auth entitlement + plugin	1
types/user.ts	Add cloud_id and deleted_at to interfaces	2

New Dependencies (6)

Package	Purpose
@supabase/supabase-js	Supabase client
@react-native-async-storage/async-storage	Required by Supabase (session fallback)
react-native-url-polyfill	Required by Supabase (URL parsing)
expo-secure-store	Encrypted JWT storage
expo-apple-authentication	Native Apple Sign-In
@react-native-community/netinfo	Network state for sync scheduler

Test Summary (~75 tests)

Test File	Tests (est.)
syncEngine.test.ts	~30
syncScheduler.test.ts	~5
userMutations.test.ts	~18
userQueries.test.ts	~10
initialMigration.test.ts	~8
accountDeletion.test.ts	~5
LoginScreen.test.tsx	~6
authStore.test.ts (extend)	~3

---

Risk Register

Risk	Impact	Mitigation
Apple rejects without native Apple Sign-In	App Store rejection	Using expo-apple-authentication (native), not web OAuth
Supabase free tier limits	Sync stops at 50K MAU	Upgrade to Pro ($25/mo) — covered by revenue model
Large initial migration fails	User data doesn't sync	Idempotent upserts; retry on next launch; progress UI
LWW loses edits on simultaneous edit	User loses a note edit	Acceptable for v1; realtime sync (v2) reduces window
expo-secure-store 2KB value limit	JWT too large	Supabase JWTs are ~1KB; within limit
NetInfo false positives	Sync on captive portals	Timeout + retry with backoff
Soft delete bloat	Deleted rows accumulate	Periodic cleanup: hard-delete rows > 30 days old
v14 sync_queue schema mismatch	ALTER may fail on existing data	v15 uses ADD COLUMN with defaults; safe for existing rows

---

Drift Corrections (April 2026)

This issue was originally written against an older codebase. The following corrections
were applied:

1. db/user.ts split → now userQueries.ts (reads) + userMutations.ts (writes)
2. Migrations at v14 (not v8) — sync migration is now v15
3. sync_queue already exists (v14) — v15 extends it with table_name and local_id
4. @supabase/supabase-js NOT installed — full dep install list corrected
5. SignUpScreen.tsx and ForgotPasswordScreen.tsx exist — LoginScreen redesign simplified
6. 20 write functions (not 14) — updateNoteTags, setNoteCollection, bookmarkTopic, unbookmarkTopic added
7. highlight_collections.id is TEXT — can use local ID as cloud_id directly
8. user_preferences needs updated_at — added to v15 migration
9. supabase.ts uses env vars — kept (better than hardcoded)
10. Decisions confirmed: Drop Facebook ✓, Sync bookmarked topics ✓, Sync reading plans ✓, Don't sync study sessions ✓

[CraigBuckmaster]

Financial Model Update (April 2026)

Revised infrastructure cost analysis confirms Supabase is the right choice for sync. Here's the cost profile at scale:

Supabase Cost by Tier

Users	Plan	Monthly Cost	Notes
0–3K	Free	$0	50K MAU, 500MB DB, 1GB storage
3K–50K	Pro	$25	100K MAU, 8GB DB, 100GB storage
50K+	Team	$599	Unlimited MAU, 16GB DB, custom limits

What Supabase Handles (vs. what stays local)

Data Type	Location	Sync?	Why
Scholarly commentary, panels, verses	Device (scripture.db)	No	Static content, offline-first, read-heavy. Delivered via R2 delta updates (#758)
Highlights, bookmarks, notes	user.db + Supabase	Yes	User-generated, needs cross-device sync
Reading progress, study depth	user.db + Supabase	Yes	Union merge (never un-completes)
Settings/preferences	user.db + Supabase	Yes	LWW, low volume
Premium status	RevenueCat + local cache	No	RevenueCat is source of truth
Auth tokens	SecureStore	No	Device-local, JWT refresh via Supabase Auth

Cost as % of Revenue (Moderate Scenario)

Year	Users	Supabase Cost	Monthly Revenue	Cost as % Rev
Y1	100K	$25→$599/mo	$6.5K→$13K/mo	4–9%
Y2	240K	$599/mo	$13K→$30K/mo	2–4%
Y3	362K	$599/mo	$30K→$46K/mo	1–2%

Supabase sync adds 2–9% to costs while unlocking premium's most-requested feature (cross-device continuity). Healthy margin impact.

Dependency Chain

1. Translation licensing (Code: Translation Licensing — Pre-launch blocking dependency for premium tier #88) — must resolve before premium launches
2. RevenueCat integration — premium gate wiring (premiumStore.ts is scaffolded)
3. This issue (Code: A7. Cross-Device Sync — Supabase auth + bidirectional sync #65) — Supabase auth + sync (4 sessions)
4. Delta DB delivery ([EPIC] Delta DB Delivery via CloudFlare R2 #758) — independent, can run in parallel

Prerequisite Checklist (unchanged)

• Craig creates Supabase project
• Configures Apple/Google OAuth providers
• Provides project URL + anon key
• RLS policies reviewed before Session 2

[CraigBuckmaster]

Cross-ref: #818 (Premium Tier Reference) lists "Cross-Device Sync" as a Companion+ feature. This issue is the implementation behind that promise.

Until #65 ships, Companion+ subscribers see the feature in the upgrade screen but can't actually use it. Worth flagging when prioritizing — every day this stays unimplemented is a Companion+ feature that's marketed but undelivered.

The Pastor Mode plan (Apr 26 artifact) also depends on this for multi-device pastor workflows; pastors editing sermons on iPad and iPhone need the sync working before Pastor tier launch.