Drizzle ORM & Offline-First

The mobile app uses Drizzle ORM with expo-sqlite for a fully offline-first local database.

Architecture Layers

UI Components (React)
        |
   React Query Hooks (src/db/hooks/)
        |
   Repository Functions (src/db/repositories/)
        |
   Drizzle ORM (src/db/client.ts)
        |
   expo-sqlite (resibibo.db)

Schema

Table definitions live in src/db/schema/ using Drizzle's table builders:

Table	PK	Notes
categories	INTEGER auto	serverId for server mapping, isDefault flag
transactions	TEXT UUID	Soft delete via deletedAt, syncStatus column
transaction_line_items	INTEGER auto	FK to transactions (cascade)
budgets	TEXT UUID	period enum, isActive flag
recurring_rules	TEXT UUID	frequency enum, nextDate
receipts	TEXT UUID	processingStatus, parsedData as JSON text

All tables with sync support have a syncStatus column with values: pending, syncing, synced, conflict.

Key Differences from Server

• Categories use integer PK + serverId for server mapping
• Other entities use text PK with client-generated UUIDs (via expo-crypto)
• Monetary amounts stored as real (not decimal)
• Dates stored as ISO 8601 text strings
• No encrypted_data column (encryption happens at sync time)

Repositories

Repository functions in src/db/repositories/ are plain async functions that perform CRUD operations:

// Example: insert a transaction
export async function insertTransaction(db: DrizzleDB, data: NewTransaction) {
  return db.insert(transactions).values({
    ...data,
    syncStatus: "pending",
  });
}

All write operations set syncStatus: 'pending' to flag records for sync.

React Query Hooks

Hooks in src/db/hooks/ wrap repositories with react-query-kit:

• createQuery for reads (auto-caching, refetching)
• createMutation for writes (invalidation on success)

Mutation hooks invalidate related queries. For example, inserting a transaction invalidates transactions, summaries, and budgets.

Migrations

Migrations are auto-generated by drizzle-kit generate and applied on app boot:

1. Run pnpm db:generate after schema changes to generate SQL migration files
2. Migration files are committed to src/db/migrations/
3. On app start, DatabaseProvider runs useMigrations() from drizzle-orm/expo-sqlite/migrator
4. After migrations, default categories are seeded if the table is empty

Metro config includes .sql in sourceExts so migration files are bundled correctly.

Database Provider

The DatabaseProvider component gates the entire app until:

1. Migrations have been applied
2. Default categories have been seeded (18 categories matching the server)

This ensures the app never renders with an uninitialized database.