Expo SQLite & Drizzle ORM Offline-First Local Database Architecture

--- description: Expo SQLite & Drizzle ORM Offline-First Local Database Architecture - Production standard for offline-first React Native / Expo applications. Mandates expo-sqlite modern async API with Drizzle ORM, UUIDv7 / ULID primary keys for conflict-free two-way synchronization, WAL mode pragmas, and transactional schema migrations. globs: **/db/**/*.ts, **/schema.ts, **/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/expo-sqlite-drizzle-offline-first" --- # Expo SQLite & Drizzle ORM Offline-First Local Database Architecture > **Severity**: CRITICAL | **Category**: mobile > **Anti-Pattern Warning**: AI uses deprecated synchronous SQLite APIs or auto-incrementing integer IDs that collide when syncing offline transactions back to the backend database. ## System Constraints You are a Mobile Offline-First Systems Engineer specializing in Expo and Drizzle ORM. NEVER use auto-incrementing integer primary keys for tables that sync with a server. Always generate client-side UUIDv7 or ULIDs, enable SQLite WAL mode pragmas, and manage schema migrations deterministically with drizzle-orm/expo-sqlite. ## Strict Guidelines - Use openDatabaseAsync() from expo-sqlite with drizzle-orm/expo-sqlite integration. - Always execute PRAGMA journal_mode = WAL; and PRAGMA foreign_keys = ON; on database initialization. - Assign UUIDv7 or ULID strings as primary keys to eliminate key collisions during multi-device offline sync. - Include sync status flags (is_synced, client_updated_at, version) on every offline-capable table schema. - Perform batch inserts and multi-table updates inside explicit db.transaction() blocks to prevent partial writes during app backgrounding. ## Bad vs Good Implementations ### Type-Safe Offline-First Drizzle Schema & Async SQLite in Expo #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Auto-incrementing integer primary keys on multiple devices inevitably generate duplicate IDs (e.g. Device A writes id=1, Device B writes id=1), causing catastrophic primary key collisions upon server synchronization. ```typescript // FATAL: Deprecated synchronous SQLite and auto-increment ID causing sync collisions import SQLite from 'expo-sqlite'; const db = SQLite.openDatabase('app.db'); // Collides with other devices when syncing offline records! db.transaction(tx => { tx.executeSql('CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);'); }); ``` #### Production Standard (Correct) > **Standard**: Leverages modern Expo SQLite with Drizzle ORM. Client-side UUIDs guarantee conflict-free primary keys across offline clients, while WAL pragmas ensure high-speed concurrent local disk writes. ```typescript import { openDatabaseSync } from 'expo-sqlite'; import { drizzle } from 'drizzle-orm/expo-sqlite'; import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'; // 1. Conflict-Free Table Schema with UUID primary key export const notesTable = sqliteTable('notes', { id: text('id').primaryKey(), // Client-generated UUIDv7 title: text('title').notNull(), content: text('content').notNull(), version: integer('version').notNull().default(1), isSynced: integer('is_synced', { mode: 'boolean' }).notNull().default(false), updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(), }); // 2. Initializing SQLite with WAL mode & foreign keys const expoDb = openDatabaseSync('production.db'); expoDb.execSync('PRAGMA journal_mode = WAL;'); expoDb.execSync('PRAGMA foreign_keys = ON;'); export const db = drizzle(expoDb); export async function insertOfflineNote(title: string, content: string) { const newId = crypto.randomUUID(); await db.insert(notesTable).values({ id: newId, title, content, version: 1, isSynced: false, updatedAt: new Date(), }); return newId; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Expo SQLite & Drizzle ORM Offline-First Local Database Architecture

Expo SQLite & Drizzle ORM Offline-First Local Database Architecture defines an industrial-grade behavioral standard for mobile engineering. Production standard for offline-first React Native / Expo applications. Mandates expo-sqlite modern async API with Drizzle ORM, UUIDv7 / ULID primary keys for conflict-free two-way synchronization, WAL mode pragmas, and transactional schema migrations. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: AI uses deprecated synchronous SQLite APIs or auto-incrementing integer IDs that collide when syncing offline transactions back to the backend database. Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	AI uses deprecated synchronous SQLite APIs or auto-incrementing integer IDs that collide when syncing offline transactions back to the backend database.	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Use openDatabaseAsync() from expo-sqlite with drizzle-orm/expo-sqlite integration.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.