Here is Claude's plan: ╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌ Drizzle Migration Plan Context WatermelonDB's web adapter (LokiJS/IndexedDB) has chunk corruption issues requiring retry logic. The library is stagnant (last release April 2025, no OPFS adapter). Replacing with Drizzle ORM + expo-sqlite (mobile) + SQLocal (web) gives us: one shared schema across platforms, real SQLite everywhere, OPFS persistence on web (no corruption), and a path to unify with the server ORM later. --- Phase 1: Replace WatermelonDB with Drizzle (Do Now) Step 1: Shared Drizzle Schema + Dependencies Install packages: - drizzle-orm in shared/utils - drizzle-kit as devDep in shared/utils - expo-sqlite in apps/mobile - sqlocal in apps/client Create shared/utils/database/drizzle/schema.ts - Translate all 16 tables from shared/utils/database/schema.ts into sqliteTable() definitions - Use drizzle-orm/sqlite-core (shared SQLite dialect for both expo-sqlite and SQLocal) - Export inferred types: type PrivatePostRow = InferSelectModel - Keep same table names, column names, and indexes as current schema Create shared/utils/database/drizzle/relations.ts - Define Drizzle relations() for foreign keys (e.g., privateUploads.postId → [privatePosts.id](http://privatePosts.id "‌")) - Enables relational queries (db.query.privatePosts.findFirst({ with: { uploads: true } })) Create shared/utils/database/drizzle/index.ts - Barrel export for schema, relations, types Step 2: Client-Side Migrations Create shared/utils/database/drizzle/drizzle.config.ts - dialect: 'sqlite', schema points to ./schema.ts, output to ./migrations/ Run drizzle-kit generate to produce initial SQL migration (creates all 16 tables) Mobile: Use Drizzle's useMigrations(db, migrations) hook at app startup Web: Run migrations via SQLocal at init time Step 3: Platform-Specific DB Init Create shared/utils/database/drizzle/db.ts (web — SQLocal + OPFS) - Replace shared/utils/database/db.ts (LokiJS) - SQLocal with factiii\_db\_{userId}.sqlite3 filename - drizzle(sqlocal, { schema }) — no more chunk corruption, no retry logic - manuallyDeleteDatabase() via OPFS API Create shared/utils/database/drizzle/db.native.ts (mobile — expo-sqlite) - Replace shared/utils/database/db.native.ts - openDatabaseSync() + drizzle(expoDb, { schema }) - Create performance indexes post-init (same as current lines 43-49 in db.native.ts) - manuallyDeleteDatabase() via expo-file-system Step 4: Replace Model Classes with Types Current 16 model classes in shared/utils/database/models/ extend WatermelonDB Model with getter/setter wrappers around _raw. Replace with: Create shared/utils/database/drizzle/types.ts - Inferred row types from schema (PrivatePostRow, PostEngagementRow, etc.) - Enriched types matching current interfaces (e.g., PrivatePost with nested uploads/factiiis/replies) - Conversion helper functions replacing model toX() methods Modify shared/utils/database/models/index.ts - Re-export Drizzle types instead of WatermelonDB model classes Step 5: Rewrite useDatabase Hook Modify shared/utils/hooks/useDatabase.tsx (2472 lines — largest change) Query pattern translations: ┌─────────────────────────────────────────┬──────────────────────────────────────────────────┐ │ WatermelonDB │ Drizzle │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ db.get('table').query(Q.where('col', │ db.select().from(table).where(eq(table.col, │ │ val)).fetch() │ val)) │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ db.get('table').create(r => { r.x = y │ db.insert(table).values({ x: y }).returning() │ │ }) │ │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ db.write(async () => { ... }) │ Direct insert/update/delete (or db.transaction() │ │ │ for atomicity) │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ record.update(r => { r.x = y }) │ db.update(table).set({ x: y │ │ │ }).where(eq([table.id](http://table.id "‌"), id)) │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ record.destroyPermanently() │ db.delete(table).where(eq([table.id](http://table.id "‌"), id)) │ ├─────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ collection.query().fetchCount() │ db.select({ count: count() }).from(table) │ └─────────────────────────────────────────┴──────────────────────────────────────────────────┘ Step 6: Update Context + Providers Modify shared/utils/context/DatabaseContext.tsx - Change db type from WatermelonDB Database to Drizzle DB type - Remove chunk corruption detection/recovery (not needed with SQLocal/OPFS) - Simplify init to call new platform-specific init functions Modify shared/utils/context/context.ts — update DatabaseState.db type Step 7: Adapt P2P Sync Modify shared/utils/helpers/watermelonSync.ts (1185 lines) - Replace record._raw access with plain Drizzle row objects (already plain JS objects) - Replace db.write() + table.create() with db.insert().values().onConflictDoUpdate() - Create getTableByName(name) helper mapping string names to Drizzle table refs - Core protocol (WebSocket messages, chunking, ID remapping) stays unchanged Step 8: Adapt Snapshot Helpers Modify shared/utils/database/snapshotHelpers.ts - Replace WatermelonDB queries with Drizzle relational queries - collectPostSnapshot can use db.query.privatePosts.findFirst({ with: { ... } }) Step 9: No Changes Needed - shared/utils/database/blobStorage.ts / .native.ts — independent of WatermelonDB - shared/utils/database/computeHash.ts / .native.ts — pure crypto, no DB dependency Step 10: Data Migration Approach: Fresh start — since data syncs P2P between devices, users can re-sync after update. The local DB is a client cache, not the source of truth. On first launch post-update: 1. Detect old WatermelonDB database exists 2. Delete it 3. Initialize fresh Drizzle DB 4. User re-syncs from other device or server Step 11: Cleanup (after migration stabilizes) Delete: - shared/utils/database/schema.ts (WatermelonDB schema) - shared/utils/database/schema-v1.ts - shared/utils/database/db.ts (LokiJS init) - shared/utils/database/db.native.ts (WatermelonDB SQLite init) - shared/utils/database/migrations.ts - All 16 files in shared/utils/database/models/ (replaced by Drizzle types) - \@nozbe/watermelondb + \@nozbe/lokijs from dependencies --- Phase 1 Testing 1. Unit tests: Test Drizzle queries with better-sqlite3 in-memory DB 2. Platform smoke tests: expo-sqlite on iOS/Android sims, SQLocal in Chrome/Firefox/Safari 3. P2P sync test: Verify sync between web (SQLocal) ↔ mobile (expo-sqlite) 4. Existing tests: Update shared/utils/database/tests/ to use Drizzle 5. OPFS verification: Confirm data persists across browser sessions, no corruption --- Phase 2: Server Prisma → Drizzle (Roadmap — Future) Scope - 69 Prisma models, 33 enums, 37+ indexes - 31 tRPC routers with 600+ Prisma operations - PostgreSQL with \@prisma/adapter-pg Step 2.1: Create Server Drizzle Schema - Create apps/server/src/db/schema.ts — translate 69 models to pgTable() defs - Create apps/server/src/db/relations.ts — 200+ relations - Use drizzle-kit pull to introspect live DB and validate against manual schema Step 2.2: Set Up Drizzle Client - Create apps/server/src/db/client.ts — drizzle(process.env.DATABASE_URL!, { schema }) - Create apps/server/drizzle.config.ts — PostgreSQL dialect - Port slow query logging to Drizzle's .logger option Step 2.3: Run Side-by-Side - Both Prisma and Drizzle connect to same PostgreSQL DB during transition - Generate baseline Drizzle migration marking current DB state - New routes use Drizzle, existing routes stay Prisma until converted Step 2.4: Migrate Routers (incremental, by complexity) 1. stats.ts, contact.ts, platform.ts — simple reads 2. localDataHash.ts — bridges client hash system 3. sessions.ts, users.ts — core auth 4. posts.ts, spaces.ts, factiiis.ts — core content (largest) 5. uploads.ts, messages.ts, notifications.ts — media/messaging 6. coins.ts, stripe.ts, awards.ts — financial (highest risk) 7. Remaining routers Step 2.5: Handle Prisma-Specific Features - \@updatedAt → .$onUpdate(() => new Date()) or trigger - $transaction() → db.transaction() - Nested include → Drizzle with or explicit joins - Middleware → Drizzle logger Step 2.6: Cleanup - Remove \@prisma/client, prisma, \@prisma/adapter-pg - Delete apps/server/prisma/ directory - Delete apps/server/src/generated/prisma/ - Delete apps/server/src/clients/prisma.ts