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.

Match the Postgres index type to the access pattern, build on live tables with CONCURRENTLY, and validate with EXPLAIN ANALYZE.

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.

Front Postgres with a transaction-mode pooler for serverless or many-instance backends, and never depend on session-scoped state.

CHECK constraints for SQLite: evaluation rules, enum-like constraints, boolean enforcement, range and pattern validation, NULL truthiness, and limitations.

Define a retention schedule per data category and automate deletion or anonymization that cascades to every derived store.

SQLite type affinity rules, the STRING gotcha, STRICT tables, and type mapping between SQLite and PostgreSQL for cross-database compatibility.

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

Prescriptive rules for SQLite backup methods, corruption prevention, integrity checking, recovery procedures, VACUUM strategy, and database size management.

Snake_case naming rules for tables, columns, primary keys, foreign keys, indexes, constraints, and triggers — including reserved word avoidance.

Always use the roadmap file's own UUID from its YAML frontmatter. Never generate random UUIDs. IDs must be determinis...

Enabling and declaring foreign keys in SQLite, ON DELETE/UPDATE actions, deferred constraints, indexing FK columns, and common pitfalls.

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.

Rules for writing efficient SQLite and PostgreSQL queries, including query planner behavior, anti-patterns that cause full table scans, JSON query performance, subquery elimination, and CTE vs subquery tradeoffs.

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

Use Room with KSP, Flow-returning observable reads, suspend writes, and @Transaction-wrapped multi-step operations on Android.

Migration strategies for SQLite: PRAGMA user_version, ALTER TABLE limitations, the 12-step recreate procedure, and sync-compatible migration rules.

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.

Partition large tables only when a measured size or retention need justifies the operational cost, and apply time-series patterns deliberately.

Choose an isolation level per transaction and retry serialization failures with bounded, idempotent backoff.

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.

Migrate a live database with expand-and-contract: every step backward-compatible with the running app, indexes built concurrently.