Mobile App Database Schema & Migration Planner

## ROLE
You are a mobile data architect who has designed local-first database systems for apps handling millions of records with seamless cloud sync and zero-downtime migrations.

## OBJECTIVE
Design a local database schema and migration strategy for my mobile application.

## CONTEXT
- Platform: {platform}
- Database Engine: {sqlite_realm_coredata_hive}
- Data Entities: {entity_list_with_relationships}
- Estimated Record Counts: {records_per_entity}
- Sync Requirement: {offline_first_cloud_sync_local_only}
- Current Schema Version: {version_or_new}
- Performance Requirements: {query_patterns}

## TASK

**1. SCHEMA DESIGN**
- Entity definitions with all fields, types, and constraints
- Relationship mapping (one-to-one, one-to-many, many-to-many)
- Index strategy for frequent query patterns
- Soft delete vs hard delete approach
- Timestamp fields for sync (createdAt, updatedAt, deletedAt)
- Conflict resolution fields (version, lastModifiedBy)

**2. QUERY OPTIMIZATION**
- Index recommendations based on your query patterns
- Denormalization decisions for read performance
- Pagination strategy for large datasets
- Full-text search setup if needed
- Query performance benchmarks to target

**3. MIGRATION STRATEGY**
- Version-based migration chain design
- Destructive vs non-destructive migration classification
- Data transformation scripts for schema changes
- Rollback strategy for failed migrations
- Migration testing framework

**4. SYNC ARCHITECTURE** (if applicable)
- Conflict resolution algorithm (last-write-wins, merge, manual)
- Delta sync implementation (only changed records)
- Batch sync for initial data load
- Retry and recovery for interrupted syncs
- Sync status tracking and UI integration

**5. DATA INTEGRITY**
- Validation rules at the database layer
- Referential integrity enforcement
- Data encryption at rest (SQLCipher / encrypted Realm)
- Backup and restore strategy
- Data export for GDPR compliance

## OUTPUT FORMAT
Provide complete schema definitions in the target database format, migration scripts, and sync logic pseudocode. Include entity-relationship diagrams described in text.

## CONSTRAINTS
- Schema must support offline-first if sync is required
- Migrations must be non-destructive (no data loss)
- All queries must complete within 100ms on target devices
- Follow platform-specific best practices for the chosen database