Data

Rules for analyzing query access patterns to drive schema and index decisions, covering read-heavy vs write-heavy tradeoffs, designing for WHERE/JOIN/ORDER BY, identifying missing indexes, and sync-specific batch sizing and transaction patterns.

Pick SwiftData or Core Data for Apple persistence by OS-version floor, model complexity, and migration needs — not by novelty.

Default to a relational database for a server backend; adopt a specialist store only for a concrete, measured requirement.

How to choose the right clock system for distributed sync: physical clocks, Lamport timestamps, vector clocks, Hybrid Logical Clocks, and server-assigned monotonic versions.

How to choose and implement a conflict resolution strategy for sync: LWW, server-wins, field-level merge, CRDTs, Operational Transformation, and manual conflict queues.

Adopt CQRS or event sourcing ONLY when audit/replay or independent read/write scaling justifies their substantial complexity cost.

Use SQLite with WAL mode for concurrent read access. No ORM — use direct SQL via the `sqlite3` standard library module.

Rules for creating effective indexes in SQLite and PostgreSQL, covering B-tree fundamentals, composite ordering, covering indexes, partial and expression indexes, sync metadata indexes, and when not to index.

JSON storage and extraction in SQLite, json_each for array iteration, generated columns for indexing JSON fields, and when JSON is a schema design smell.

Start normalized (3NF), denormalize only measured hotspots. SQLite-specific tradeoffs and sync-safe denormalization guidance.

Design rules for offline-first apps: WAL as the foundation, operation queues, optimistic UI updates, rollback on rejection, offline schema migrations, data expiry, and connectivity-aware sync scheduling.

Choosing between INTEGER PRIMARY KEY, AUTOINCREMENT, UUID, and WITHOUT ROWID — including sync compatibility and cross-database PK design.

One-to-many, many-to-many, polymorphic FKs, self-referential tables, and tree hierarchy patterns with tradeoffs and SQLite-specific guidance.

Comparison and selection guide for SQLite sync tools: Session Extension, cr-sqlite, Litestream, ElectricSQL, PowerSync, Turso/libSQL, and sqlite-sync — with a decision matrix and selection criteria.

How to design a client-side sync engine: layered architecture, entity-agnostic Syncable interface, the six-step sync cycle, scheduling strategies, snapshot rebuilding, and error handling with circuit breakers.

Rules for designing the sync protocol between client and server: push/pull direction, full vs incremental sync, change tracking, batching, idempotency, the outbox pattern, and retry with backoff.

Schema design rules for databases that sync across devices: UUID primary keys, timestamp columns, soft deletes, dirty tracking, version columns, and sync metadata tables.

Rules for transaction management and concurrency in SQLite, covering WAL mode, journal mode selection, BEGIN IMMEDIATE vs DEFERRED, connection strategies, busy_timeout, PRAGMA tuning, and WAL benefits for sync workloads.

Plan RAG retrieval on pgvector with an HNSW index and hybrid dense+lexical+rerank scoring before reaching for a dedicated vector DB.