The mobile app architecture decision record: why the offline model you chose determines your conflict resolution complexity and your sync protocol debt

The field sales CRM that lost data in rural Iowa

A 20-person SaaS startup builds a mobile CRM for field sales teams at agricultural equipment distributors. The founding session chooses React Native — the mobile team is JavaScript-native, the company has no iOS or Android engineers, and React Native lets them ship one codebase for both platforms. The data model is straightforward: contacts, accounts, opportunities, and activity logs (call notes, visit notes, email records). Every write — a new contact note, an opportunity stage update, a call log entry — posts immediately to the REST API. There is no local persistence layer beyond the React component state that holds the form values while the user is typing. The founding session documents: "React Native, REST API, no local storage (not needed for v1)."

The demo of the app runs in a downtown San Francisco conference room with full wifi. The founding team's connectivity experience throughout development is office wifi, home wifi, and reliable urban LTE. The app works correctly in every environment the founding team uses it in.

Six months after launch, the sales manager of a farming equipment distributor in rural Iowa emails support: "Our reps are losing data constantly. They enter 30 minutes of notes from a dealer visit, hit Save, it spins and then the notes are just gone. This happens multiple times per week." The startup's support team investigates. The failure mode: the app's mutation flow is React form state → API POST → success response clears the loading indicator, or failure response shows an error toast. The failure toast requires an HTTP error code. But in the React Native app, network timeouts after 8 seconds resolve the fetch promise as undefined rather than rejecting it — a known difference in fetch behavior across environments that the founding team had not tested for. When the cell signal drops mid-POST, the fetch times out at 8 seconds, resolves as undefined, the success branch runs (because the failure branch checks for a defined error object), the loading indicator clears, and the user sees a blank note form where their 30 minutes of notes used to be. The data was never written to the server. There is no local copy.

The timeout bug is a one-day fix — add a null check before the success branch, display an error if the response is undefined. But the root issue is now visible: 40% of the distributor's field reps work in rural territories with intermittent coverage. Dealer visits happen in farm equipment warehouses and outdoor lots where cell signal is unpredictable. The CRM's most critical workflow — entering notes immediately after a dealer meeting, before getting in the car and forgetting the details — is exactly the workflow that fails in low-connectivity environments.

The team opens a "Phase 2: offline support" ticket. The scope: add SQLite via react-native-quick-sqlite, implement a pending_mutations queue (every write goes to SQLite first, a background sync job uploads the queue when connectivity is available), build a local read path (all reads serve from the local SQLite database populated by a pull-based sync from the server), and handle conflict resolution when two users edit the same record while independently offline. The SQLite schema must mirror the server schema — which evolved across 6 months of feature development with 14 Postgres migrations, including 3 column renames and 2 table restructurings that involved merging columns. Adapting these changes to SQLite's ALTER TABLE limitations requires writing equivalent rename-copy migrations that were never designed for SQLite.

Phase 2 takes 8 engineer-weeks. The conflict resolution strategy — what happens when two reps edit the same opportunity while offline and both sync within minutes of each other — is never fully specified. The team defaults to "last-write-wins by the server's updated_at timestamp" because it's what the sync framework documentation shows in its getting-started example. One month after Phase 2 ships, a support ticket arrives from the same distributor: two district managers at the same account edited the same contact record while independently offline at an industry conference. When both synced, one manager's changes (job title, direct phone number) silently overwrote the other manager's changes (email address, LinkedIn URL). The contact record shows one manager's version. Neither manager knows which version is in the system, or that the other's edits were lost.

The decision that was never written down was the offline capability model: what the app should do with a write that fails due to network unavailability. The founding session documented the technology choice but not the behavioral contract. If the behavioral contract had been written — "every write to the activity log must succeed locally and be uploaded when connectivity is restored" — the founding team would have immediately identified that local persistence was required in v1, not deferred to v2. The conflict resolution decision was similarly undocumented: the Phase 2 team chose last-write-wins as a default, not as a considered answer to the specific conflict scenarios that the CRM's field-concurrent editing would produce.

The delivery driver app that overwrote a customer dispute

A grocery delivery logistics startup builds their driver app from the start with offline-first architecture. The founding team has read about the field sales CRM category's data loss problems and makes a deliberate decision: every UI action in the driver app must succeed locally, with sync happening asynchronously when connectivity returns. They choose React Native with WatermelonDB — a SQLite-backed local database designed for offline-first React Native apps — and build a clean architecture: every mutation writes to the local SQLite database first, a pending_mutations queue uploads changes to the backend, and a pull sync populates the local database with server-originated changes (new orders assigned by dispatch, customer messages, route updates).

The conflict resolution strategy: last-write-wins, where "wins" is determined by the server's received_at timestamp for the mutation. The founding session documents "offline-first with WatermelonDB, LWW conflict resolution" in a Notion page that was archived after the sprint ended. The app ships. Drivers can accept orders, update delivery statuses (PICKING_UP, IN_TRANSIT, DELIVERED, PROBLEM_CANNOT_ACCESS, PROBLEM_CUSTOMER_ABSENT), collect digital signatures, and leave delivery notes — all while offline. The app is genuinely good: drivers who previously lost GPS tracking in underground parking garages now keep working seamlessly.

Fourteen months after launch, a post-mortem is filed after a customer experience incident that cost the company a $200 enterprise grocery account. The sequence: a driver delivering to an apartment complex goes into an underground parking garage at 2:47 PM with no cell signal. The driver has three pending deliveries. She marks all three as DELIVERED in the app, capturing signatures for two of the three (the third door had no answer; she left the groceries outside the door per standing instructions). The three DELIVERED mutations go into the pending_mutations queue.

At 2:53 PM — 6 minutes after the driver went underground — a customer calls the grocery chain's support line. The customer reports that her groceries were left unattended outside her door (unit 4B), she was home but the driver didn't buzz the intercom, and she is concerned about food safety (raw chicken left in a hallway for an unknown duration). The customer service rep opens the web dashboard and locates the order. The driver's app shows the order as DELIVERED (the previous delivery the driver completed before entering the garage). The rep sets the order status to PROBLEM_CUSTOMER_ABSENT with a note: "Customer called at 14:53; reports groceries left outside door without buzzing intercom; requests re-delivery or full refund; food safety concern raised (raw chicken)." The server records this mutation with received_at = 2026-01-15T14:53:22Z. The re-delivery workflow is initiated: a second driver is dispatched.

At 3:01 PM, the first driver emerges from the parking garage. The app connects to the network and the pending_mutations queue begins uploading. The three DELIVERED mutations are uploaded to the server. The server's LWW logic evaluates the conflict on the disputed order: the customer_service mutation has received_at = 14:53:22, the driver's DELIVERED mutation has received_at = 15:01:09. LWW keeps the mutation with the later received_at: the driver's DELIVERED status overwrites PROBLEM_CUSTOMER_ABSENT. The order now shows DELIVERED in the system.

The second driver who was dispatched for re-delivery arrives at unit 4B at 3:24 PM. The customer answers the door, the original groceries in hand (they had come back 20 minutes later; the chicken was fine, barely). The second driver's app still shows the re-delivery order as active — but the source order now shows DELIVERED, and the second driver's system cannot mark a re-delivery as complete when the source order is in DELIVERED status (this is a guard against marking re-deliveries for already-resolved disputes). The second driver calls dispatch. Dispatch investigates. The customer service rep discovers her PROBLEM status was overwritten. The audit trail shows "status changed from PROBLEM_CUSTOMER_ABSENT to DELIVERED at 15:01 by driver [ID]." The rep's note is still attached to the order but the status has changed under it. The incident report escalates because the enterprise account manager needs to explain to the grocery chain's procurement team why a confirmed customer dispute was silently reversed by a sync operation.

The post-mortem identifies three failures. First, the conflict resolution model was LWW by received_at, which produces incorrect results for delivery status because the business rule is not "the most recent information is correct" but "customer service overrides driver, and PROBLEM states cannot be reverted to DELIVERED by any actor other than a supervisor." Second, delivery status is a state machine — valid transitions are defined by business rules, not by who wrote last. A transition from PROBLEM_CUSTOMER_ABSENT back to DELIVERED is not a valid transition for any non-supervisor actor, yet the sync layer had no concept of state machine validation. Third, the conflict resolution strategy was not documented: the engineering lead reviewing the incident inherited a sync layer whose conflict resolution model was LWW-by-received_at and had no record of why that strategy was chosen, what conflict scenarios it was designed to handle correctly, or what scenarios it was known to handle incorrectly. The founding session that chose WatermelonDB + LWW documented the technology but not the strategy's preconditions or known failure modes.

Three structural properties the mobile architecture decision determines

1. The offline capability model and the conflict surface

The offline capability model specifies what the app does with user actions when network is unavailable. There are three distinct models, and they differ in how much local state can diverge from the server — the conflict surface:

Online-first: every write calls the server API immediately. If the network is unavailable, the user action fails, and the app must handle the failure explicitly (show an error, retain the form state, allow retry). There is no local write state that can diverge from the server — the conflict surface is zero. The trade-off: every user action that requires offline success must be explicitly designed as an exception, which becomes increasingly complex as the number of offline-required actions grows. Online-first is correct for workflows where server validation is a requirement (booking a seat, reserving inventory, executing a financial transaction) or where users are reliably connected during all core workflows. It is incorrect for field-facing applications where the most valuable user actions happen in connectivity-unreliable environments.

Offline-capable: the app maintains a local cache of server data for read operations, but writes are queued for later upload when connectivity returns. The local cache populates from a pull sync; writes go into a pending_mutations queue that uploads when the network is available. The conflict surface is bounded by the queue depth and the duration of the offline period: mutations queued while offline may conflict with mutations made by other clients during the same offline window. Offline-capable is the right model for most mobile apps that have field-facing read workflows (a technician looking up equipment specs while on-site) but limited field-facing write workflows (the technician submits a work order on completion, which can wait for connectivity).

Offline-first: all reads and writes succeed locally without network access. The local SQLite database is the primary store; the server is the sync target. Every mutation is recorded locally first and synchronized asynchronously. The conflict surface is maximum — every entity in the data model that is mutable from multiple clients simultaneously is a potential conflict point when two clients diverge and later sync. Offline-first provides the richest user experience for field-facing applications where writes must succeed immediately regardless of connectivity, but it requires the most investment in conflict resolution design because the conflict surface covers every entity type, not just queued mutations.

The conflict surface must be enumerated at founding, not discovered in production. For every entity type in the data model that can be mutated from multiple clients concurrently, the founding session must answer: (a) which user roles can write which fields of this entity? (b) can two clients hold conflicting versions of this entity simultaneously? (c) what is the correct outcome when they conflict? The delivery status scenario above fails on question (c): the correct outcome when a driver's DELIVERED and a customer service rep's PROBLEM_CUSTOMER_ABSENT conflict is not determined by timestamp — it is determined by role precedence and state machine validity. That answer is not inferable from "LWW by received_at"; it requires explicitly specifying the state machine transitions and the role hierarchy during the founding session.

Conflict surface enumeration produces a decision matrix: entity type × concurrent writer combination × conflicting field → resolution rule. For a CRM's contact entity: sales_rep × sales_rep concurrent edit → field-level merge (each field independently keeps the last writer's value). For a delivery system's order_status field: driver × customer_service → customer_service wins regardless of timestamp. For a shared note entity: user × user concurrent edit → CRDT-based merge that preserves both users' text insertions. The state management decision record documents the client state model and derived-state complexity; offline-first mobile state management adds the sync state layer to the client state model, and the conflict resolution matrix is the specification that determines how the sync state layer resolves divergent versions before committing the merged state to the local database.

2. The sync protocol and the replication topology

The sync protocol determines how mutations travel between local clients and the server, and what guarantees each direction carries. The three common models have different complexity, staleness, and collaboration characteristics:

Push-pull polling: the client uploads local mutations to the server (push, via REST POST) and periodically downloads server mutations to the local database (pull, on a configurable interval or on app foreground events). The push direction is immediate — mutations are uploaded as soon as connectivity is available, not on the pull schedule. The pull direction is periodic — the local database reflects server state as of the last successful pull, which may be 30 seconds to 5 minutes stale depending on the interval. Push-pull polling is stateless on the server: each pull request sends the client's last-seen server timestamp or sequence number, and the server returns all mutations since that position. There is no persistent connection to maintain, no server-side state per client beyond what the pull endpoint needs to compute the delta. This makes push-pull polling the easiest sync model to debug (each sync is a standard HTTP request visible in the network tab), the easiest to operate (no WebSocket infrastructure, no persistent connection state), and the correct starting point for offline-first apps that do not require real-time collaboration between concurrent users.

Server-sent events with client push: the client uploads mutations via REST POST (same as polling for the push direction) and receives server change events via a persistent Server-Sent Events (SSE) connection. SSE is a standard HTTP mechanism for the server to push events to a connected client over a long-lived HTTP response. Each event carries a numeric event ID that the client tracks as its cursor. When the client reconnects after a disconnect (app backgrounded and foregrounded, network interruption), it sends the Last-Event-ID header and the server replays all events since that position. The advantage over polling: server changes are delivered immediately to connected clients, eliminating pull-interval staleness. A customer service rep on the web dashboard and a driver with the mobile app both see status changes within milliseconds of the server processing them. The server must maintain a per-client cursor (the last event ID the client acknowledged receiving) and an event stream that includes all mutations by all clients, ordered by sequence number, queryable by sequence position. This is more server-side state than polling requires, but it is bounded state — one cursor per connected client — and does not require a persistent connection for the push direction.

Bi-directional WebSocket sync: both push (client to server) and pull (server to client) use a persistent WebSocket connection with a custom sync protocol. The server receives mutations from the client, processes them (resolving conflicts against the authoritative server state), and broadcasts the results to all connected clients that are subscribed to the affected entities. This is the model used by collaborative editing frameworks (Replicache, ElectricSQL, LiveStore, Liveblocks). The server must maintain per-client state (which entities the client is subscribed to, the client's last-confirmed sequence number), implement a formal sync protocol (how the server handles a mutation from client A that conflicts with a mutation from client B received in the same millisecond), and provide a reconnection protocol (how the client recovers from a dropped WebSocket connection without re-requesting the full entity set). Bi-directional WebSocket sync is the correct choice when the application requires real-time collaborative editing — two field techs updating the same work order simultaneously, or a dispatcher and driver both editing the same route simultaneously. It is overkill for applications where concurrent editing is infrequent and a few seconds of staleness is acceptable. The real-time architecture decision record documents the transport layer selection for real-time features; WebSocket sync for offline-first mobile is a specific instance of the real-time architecture choice, and the decision should be driven by the same criteria: does the collaboration model require sub-second delivery of other clients' mutations, or is pull-interval or SSE staleness acceptable?

The replication topology determines what each sync direction requires from the server. A simple server_wins model (any conflict resolved by the server keeping its own version) requires only a per-client sequence number cursor for the pull direction — no conflict history, no version metadata, no per-client state beyond the cursor. A field-level merge model (each field independently resolves using its own strategy) requires storing the merge metadata alongside the current value (who wrote the value, when, from which device) and evaluating the conflict at merge time with both versions available. A CRDT model requires storing the CRDT state for each field (the vector clock for LWW registers, the set of operations for grow-only sets, the per-replica counter for G-Counters) alongside the current value. Each more sophisticated model produces more correct outcomes at the cost of more server-side data storage and more complex merge logic. The conflict resolution strategy chosen in the conflict surface enumeration (section 1) determines which replication topology the sync protocol must support.

3. The local database schema and the migration policy

The local SQLite database is installed on user devices and cannot be updated without the user installing a new app version from the App Store or Play Store. This creates a schema migration constraint with no equivalent in server-side database migrations: the migration must run successfully on every installed app version, across the full range of schema versions that exist in the wild, and if it fails, the app must recover without data loss — or the user experiences a data-corrupting update and files a support ticket that reads "the app update deleted everything."

SQLite's ALTER TABLE subset is the first constraint. SQLite supports: ADD COLUMN (with a default value or NULL); RENAME TABLE; RENAME COLUMN (since SQLite 3.25.0, available on devices running iOS 12+ and Android API level 24+). SQLite does not support: DROP COLUMN (before SQLite 3.35.0, which is newer than many installed device runtimes); changing a column's type; adding a NOT NULL constraint to an existing column; adding a UNIQUE constraint to an existing column (without a table rebuild); removing a default value. Schema changes that require these unsupported operations use the rename-copy pattern: (1) BEGIN TRANSACTION; (2) ALTER TABLE old_name RENAME TO old_name_backup; (3) CREATE TABLE old_name WITH (new schema); (4) INSERT INTO old_name SELECT (mapped columns) FROM old_name_backup; (5) DROP TABLE old_name_backup; (6) COMMIT. Every step of this pattern must succeed atomically, and the transaction must roll back cleanly if any step fails. SQLite's DDL statements (CREATE TABLE, DROP TABLE, ALTER TABLE) are transactional in SQLite — they participate in the same ACID transaction as DML statements, which means a failed rename-copy migration rolls back the entire sequence and leaves the database in its pre-migration state. This is different from most relational databases where DDL is auto-committed; SQLite's transactional DDL is a deliberate design choice that makes migration atomicity achievable.

The version tracking mechanism is PRAGMA user_version — a four-byte integer embedded in every SQLite database file. The migration runner reads user_version on app startup, compares it to the application's expected schema version, and applies all pending migrations in order from current_version + 1 to target_version. Each migration increments user_version after completing successfully (inside the same transaction, before COMMIT). The migration runner structure:

const currentVersion = db.pragma('user_version', { simple: true });
for (const migration of migrations.filter(m => m.version > currentVersion)) {
  db.transaction(() => {
    migration.up(db);
    db.pragma(`user_version = ${migration.version}`);
  })();
}

The idempotency requirement addresses the partial-application failure mode: if the app crashes after COMMIT but before the in-memory state is updated, the next startup re-reads user_version from the file (which is now at the migrated value) and skips the migration correctly. If the app crashes after applying DDL but before COMMIT, the SQLite file is unchanged (DDL rollback) and the next startup re-runs the migration from the correct version. The failure case that is not automatically safe: a migration that produces side effects outside the SQLite transaction (writing files, calling APIs) before committing. Migrations must be pure SQLite operations for the atomicity guarantee to hold. The database vendor decision record documents the framework for selecting a data store based on workload characteristics; for mobile apps, the choice to use SQLite as the local database brings both the migration constraint (limited ALTER TABLE) and the atomicity guarantee (transactional DDL) that distinguishes it from server-side Postgres where DDL is auto-committed and the migration safety model is different.

The deployment gap is the range of schema versions present on installed app versions that must successfully apply the pending migrations in the current release. If version 2.0.0 shipped with user_version=3 and the current release is 2.6.0 with user_version=12, the migration runner must apply migrations 4 through 12 in order for any user who hasn't updated since 2.0.0. A migration added in version 2.3.0 that assumes a column exists (because migration #7, shipped in 2.2.0, added it) will work correctly for any user upgrading from 2.2.0 or later, but will fail for users upgrading directly from 2.0.0 — because the migration runner runs migrations 4 through 7 in order, making migration #8's dependency on #7's column always satisfied. The deployment gap risk is not migration ordering (the sequential runner handles ordering) but migration defensiveness: a migration that uses ALTER TABLE ADD COLUMN column_name without checking whether the column already exists will fail if the same column was added by a schema repair path or a previous installation of a pre-release build. The defensive pattern: check SELECT count(*) FROM pragma_table_info('table') WHERE name='column' and skip ADD COLUMN if it returns 1. Idempotent migrations eliminate the failure class of "migration already partially applied."

The mobile deployment decision record documents the release cadence and forced-upgrade policy; the schema migration policy must be designed in relation to the forced-upgrade policy. If the app forces upgrades after 30 days (all users on the current or previous release), the deployment gap is at most 2 release versions — a manageable migration test matrix. If the app has no forced upgrade and users on 3-year-old versions report bugs, the deployment gap spans years of schema evolution and each migration must handle 40+ versions. The decision to enforce a minimum supported version is not just a product decision — it directly determines the complexity ceiling of every schema migration the team will ever write.

Five ADR sections the mobile app architecture decision record needs

1. Offline capability model selection

Document the offline capability model with the specific user journeys that require offline success and the entity types that must be locally writable when network is unavailable. The decision is not "offline or not" but "which operations must succeed offline, and which can wait for connectivity?" The offline capability model is derived from two inputs: the user's network environment during their most important workflows, and the business cost of those workflows failing due to network unavailability.

For each entity type in the data model, specify the offline write requirement: "always succeeds offline" (write goes to local SQLite immediately, sync when connected), "queued for later" (write succeeds locally but cannot be acted on until confirmed by the server), "requires connectivity" (the write must fail gracefully if network is unavailable, with the form state retained for retry). Document the entity types in each category and the user journey that determines the category. "Contact notes: always succeeds offline — field reps enter notes in dealer lots and rural warehouses after visits; connectivity cannot be a requirement" is a complete specification. "Opportunity stage change: queued for later — reps can mark a stage change offline but the CRM pipeline view won't reflect it until the server confirms" is another complete specification.

The offline model selection also specifies the local read model: does the app read from the local SQLite database for all reads (the offline-first approach, where the local database is the primary store populated by sync), or does it read from the server API for all reads and only use the local database as a write queue (the offline-capable approach, where reads require connectivity)? The local read model determines whether the app can function meaningfully while offline (offline-first) or only accept writes while offline and display stale data for reads (offline-capable). Document which approach is correct for each entity type — often the correct answer is offline-first reads for entity types the user navigates to frequently and offline-capable reads for entity types that are occasionally accessed.

Document the expected conflict rate: given the user population, the entity types that are locally writable, and the typical offline duration, how often will two clients hold conflicting versions of the same entity? A single-user app (one user per account) has zero conflict rate regardless of offline model — there is no second client to conflict with. A team app where multiple users share the same account has a conflict rate that scales with the number of concurrent users and the typical offline duration. A conflict rate below one per day per user may not justify the complexity of a sophisticated conflict resolution model; field-level merge may be overkill if the app's primary use case is single-writer entities (each field rep owns their own contacts and opportunities). A conflict rate above ten per day per user (a delivery platform with multiple dispatchers and drivers all updating the same order entity) requires a well-specified conflict resolution model that handles each conflict type correctly.

2. Conflict resolution strategy

Document the conflict resolution strategy by working through the conflict matrix for each entity type: which writers can conflict, which fields they can both write, and what the correct outcome is for each conflict combination. The resolution strategy is not a global setting ("LWW everywhere") but a per-entity, per-field specification derived from the domain rules that determine correctness.

The resolution strategies available, ordered by complexity and by how often they produce correct results:

Server-wins: any conflict is resolved by keeping the server's current version. The client's offline mutation is discarded. This is correct when the server's version is always more authoritative than the client's version — for example, server-computed fields (totals, aggregate counts, server-generated IDs) that the client should never mutate. It is incorrect for any field where the client has information the server does not have (the field rep's observation during the dealer visit).

Last-write-wins by server received_at: the mutation received later by the server wins. This is correct for entity types with a single writer at any time (a user updating their own profile), or for entity types where the domain rule is genuinely "the most recent information is always more correct" (a GPS location update where the more recently received location is always the better location, regardless of which client sent it). LWW is incorrect when role-based precedence should override timestamp ordering, or when the entity is a state machine where valid transitions are determined by business rules, not by recency.

Field-level merge: each field is merged independently using its own resolution rule. The merge rule for each field is specified in the conflict matrix. Job title field: LWW (whoever wrote last is correct). Order status field: role_precedence (customer_service wins over driver; supervisor wins over customer_service). Delivery note field: append (both notes are preserved, separated by a timestamp marker). Price field: not offline-writable (always server-authoritative). Field-level merge produces correct outcomes for the majority of collaborative mobile apps because most entities have a mix of field types — some genuinely LWW-correct, some role-precedence-correct, some append-correct. The error handling strategy decision record documents the failure handling model; conflict resolution failures (a merge that cannot be resolved automatically because neither resolution rule produces a correct outcome) should surface as a conflict record in the UI that requires human review, not as a silent discard of one version.

State machine validation: entity types whose valid states form a directed graph (order statuses: PLACED → PICKING_UP → IN_TRANSIT → DELIVERED; or PLACED → CANCELLED; or IN_TRANSIT → PROBLEM_CUSTOMER_ABSENT → RESCHEDULED) require state machine validation at merge time. Any mutation that would produce an invalid transition is rejected — the mutation is not applied, and a conflict record is created for human review. State machine validation is not a conflict resolution strategy by itself; it is a pre-condition check that runs before field-level merge. A mutation that passes state machine validation then proceeds to field-level merge. A mutation that fails state machine validation is never applied regardless of its timestamp or the sender's role.

CRDT semantics: for entities that support collaborative editing (shared notes, shared checklists, collaborative route planning), CRDTs (Conflict-free Replicated Data Types) provide merge semantics that mathematically guarantee convergence — any ordering of operations produces the same final state. The appropriate CRDT type depends on the field's semantics: G-Set (grow-only set) for tags or labels that are only ever added; 2P-Set (two-phase set) for tags that can be added and removed; LWW-Register for single-valued fields where last-write-wins is correct; RGA (Replicated Growable Array) or CRDT text types for collaborative text editing. CRDTs require storing the CRDT state alongside the current value and merging at the CRDT state level, not at the value level. The merge is deterministic and automatic; there are no "conflict records" because by definition there are no conflicts — any pair of CRDT states can be merged to produce a single correct state. The trade-off: CRDT state is larger than the value itself (a LWW-Register for a string field stores the string plus a vector clock), and implementing CRDT merge correctly requires library support or significant expertise.

Document the conflict escalation path: when no automatic resolution rule produces a correct outcome (a conflict that requires human judgment), the system creates a conflict record that surfaces both versions to a user with resolution authority. Document who sees conflict records, where they appear in the UI, and what actions they can take. A conflict resolution record that disappears into a log table with no UI surface is equivalent to silent data loss — one version is kept and the other is invisible to everyone. The background job infrastructure decision record documents the execution model for async jobs; conflict detection (comparing two versions of the same entity to determine if a merge is needed) and conflict resolution (computing the merged version) are background operations that run during sync, and their failure mode (a conflict detection job that crashes halfway through processing the queue) must be handled with the same retry and dead-letter visibility that any background job requires.

3. Sync protocol and replication topology

Document the sync protocol with the specific latency and collaboration requirements that determine the choice. The sync protocol documentation must specify: the push direction (how local mutations travel to the server), the pull direction (how server mutations travel to the local database), the cursor model (how the client tracks which server mutations it has already received), and the reconnection protocol (how the client recovers from an extended offline period or an app restart without re-fetching the full entity set).

For push-pull polling: document the pull interval and the events that trigger an out-of-schedule pull (app foreground event, explicit user refresh gesture, push notification from server indicating new data). Document the cursor model: a server-side sequence number that increments monotonically with each mutation, or a server timestamp that the client sends with each pull to receive mutations since that time. Sequence numbers are more reliable than timestamps because they are not subject to clock skew between the client and server; a sequence number of 14,832 unambiguously identifies a position in the mutation log, while a timestamp of 2026-01-15T14:53:00Z may collide with other mutations at the same millisecond. Document the pull endpoint's response format: a list of mutations since the cursor position, ordered by sequence number, with each mutation containing the entity type, entity ID, the changed fields, and the new cursor position.

For SSE: document the event stream schema (event type, event ID, event data), the reconnection behavior (Last-Event-ID header sent by the client on reconnect, server replay of all events since that ID), and the maximum event stream age the server retains (events older than 7 days are purged; clients offline for more than 7 days must do a full re-sync rather than event replay). Document the server-side resource implications: one SSE connection per active client means one open HTTP connection per mobile user actively using the app. At 10,000 concurrent active users, the server must maintain 10,000 open HTTP connections with low-latency write capability. This requires an event loop-based HTTP server (Node.js, Go, Elixir) rather than a thread-per-connection model; a thread-per-connection server attempting to maintain 10,000 SSE connections requires 10,000 threads, which is typically impractical.

Document the sync conflict window: the time window during which two clients can produce conflicting mutations without either client knowing about the other's changes. For push-pull polling with a 60-second pull interval, the conflict window is up to 60 seconds — client A's mutation may not appear in client B's local database for up to 60 seconds after client A uploads it. For SSE, the conflict window is the round-trip latency between the server receiving client A's mutation and the SSE event reaching client B — typically sub-second. A narrower conflict window does not eliminate conflicts; it reduces the probability that two users concurrently edit the same entity in a short window. The conflict resolution strategy must still be correct regardless of the conflict window size.

4. Local database schema and migration policy

Document the local SQLite schema with the specific subset of the server schema that the app requires locally, the mapping from server entity types to local SQLite tables, and the columns required for sync metadata. Every locally-stored entity requires at minimum: a local primary key (a UUID generated on the client, which becomes the server ID after sync), a sync_status column (pending_upload / synced / conflict), a server_version column (the server's sequence number or timestamp for this entity at the last sync), and an updated_at column (the client's local timestamp when the entity was last modified, used for display and for LWW conflict resolution where that strategy applies).

Document the migration runner implementation: which library manages the migration lifecycle (Drizzle ORM's migration runner for react-native-drizzle, custom migration runner using PRAGMA user_version, or op-sqlite's built-in migration support), how migration files are organized (sequentially numbered SQL files, or JavaScript migration functions in an array), and how the migration runner handles the app being killed during a migration (relying on SQLite's transactional DDL for atomicity, with PRAGMA user_version updated inside the same transaction).

Document the migration testing matrix: which prior schema versions are included in the automated migration test suite, and how migration tests are structured. A migration test runs the migration on a database populated at the specified prior version and then validates the post-migration schema using PRAGMA table_info for each table and PRAGMA index_list for each index. The test passes if the post-migration schema matches the expected schema and fails if any table, column, or index is missing or has the wrong type. Migration tests must cover at minimum: the immediately prior release version, and any version that shipped to more than 5% of the active user base. The deployment gap analysis — checking the app analytics for the distribution of active users by app version — determines which prior versions are included in the test matrix.

Document the column naming and type conventions that reduce migration complexity. Using TEXT for all string columns (never VARCHAR with a length constraint, since SQLite ignores length constraints anyway) eliminates the need to change column types when length requirements change. Using INTEGER (0/1) for booleans instead of SQLite's BOOLEAN type (which is stored as INTEGER anyway) avoids the false impression that a type change is needed when the column semantics change. Using ISO 8601 string format for timestamps instead of UNIX integers keeps the values human-readable in SQLite CLI debugging and avoids integer-to-text migration complexity. The caching strategy decision record documents the cache invalidation approach and consistency guarantees; for offline-first apps, the distinction between "local cache" (data fetched from server and stored locally for read performance) and "offline-first local data" (data written locally that will be synced to server) determines the cache invalidation model — cached read-only data can be invalidated and re-fetched on pull sync, while offline-first data must be merged with server data using the conflict resolution strategy, not overwritten by a cache refresh.

5. Security model for local data storage

The local SQLite database contains a subset of server-side data on a device that is not under the company's physical control. A user who loses their device, sells it without a factory reset, or has their device inspected (in an enterprise security incident or law enforcement context) may expose the locally stored data. The security model for local data storage must specify: what data categories are stored locally, what encryption is applied to the local database, where authentication credentials are stored, and what happens to local data when the user logs out or the app is uninstalled.

For data categories: document which server-side entity types are stored in the local SQLite database and whether any of those entity types are classified as sensitive (personally identifiable information, financial data, health data, proprietary business data). A field sales CRM that stores contact names, phone numbers, and email addresses locally stores PII under GDPR and CCPA. A delivery driver app that stores customer names and addresses locally stores sensitive logistics data. The local storage of sensitive data categories triggers compliance requirements that must be documented in the ADR: encryption at rest, remote wipe capability, data retention policy for local data.

For encryption: SQLite databases are stored as plaintext files in the app's sandboxed file system by default. On iOS, all app files are encrypted by the operating system when the device is locked (Data Protection level "Protected Until First User Authentication" by default, "Protected" — encrypted even when unlocked — as an opt-in). On Android, file-level encryption is applied on modern devices but the encryption key is derived from the device's credential, not the user's app password. For enterprise apps with strict data security requirements, SQLCipher (an SQLite extension that encrypts the database using AES-256 with a per-database key) provides database-level encryption with a key that can be tied to the user's authentication credentials, so the database is unreadable without the app-level key even if the SQLite file is extracted from the device.

For authentication credentials: the local database should not store the user's password or long-lived credentials directly. Authentication tokens (JWT access tokens, refresh tokens) should be stored in the platform's secure credential store (iOS Keychain, Android Keystore), not in the SQLite database or in AsyncStorage (which is plaintext on disk on Android). The access token stored in the Keychain is used for API requests; the refresh token is used to obtain a new access token after expiry. If the refresh token is revoked (server-side logout, account suspension), the app's next API request will receive a 401, triggering a local logout that clears the SQLite database, the token from the Keychain, and any in-memory state. The authentication strategy decision record documents the session management approach; for mobile apps with local data storage, the logout event must include a local data purge step that removes or re-encrypts the SQLite database — leaving the local database intact on logout means that the next user who unlocks the device can potentially access the prior session's data without authenticating.

For remote wipe: enterprise deployments often require the ability to remotely wipe the app's local data if a device is lost or if an employee is terminated. Remote wipe is implemented by the server revoking the user's refresh token (which causes the app to fail authentication on next sync) and sending a push notification to the device that triggers a local data purge. The local data purge deletes the SQLite database file, clears the Keychain entry, and logs the user out. Document the remote wipe procedure in the ADR so that the mobile team knows it is a supported operation and designs the logout/wipe flow to handle database deletion atomically (delete the database file, not individual rows, to avoid partial wipe states).

Further reading

• Decisions never written down — the offline capability model (what the app does with every user action when network is unavailable), the conflict resolution strategy (the precedence model for concurrent mutations from different user roles), and the local database migration policy (how SQLite schema changes apply to every installed app version) are exactly the mobile architecture decisions most commonly made implicitly during founding sprints and discovered as customer data loss incidents or retrofit engineering projects six months later
• Database vendor decision record — for offline-first mobile apps, the choice to use SQLite as the local database brings both the migration constraint (limited ALTER TABLE requiring the rename-copy pattern for unsupported operations) and the atomicity guarantee (transactional DDL that rolls back cleanly on failure) that distinguish it from server-side Postgres; the local database is a second database in the system with its own schema evolution lifecycle and its own schema migration policy, and the database vendor decision record framework — evaluated against workload characteristics, operational constraints, and scaling requirements — applies to the SQLite selection just as it applies to the server-side database choice
• Mobile deployment decision record — the release cadence and forced-upgrade policy directly determine the deployment gap (the range of schema versions on installed app versions that must successfully apply every pending migration); a 30-day forced upgrade policy limits the deployment gap to two release versions, making the migration test matrix manageable; no forced upgrade means migrations must handle years of schema evolution, and the complexity ceiling for every schema migration grows proportionally; the deployment decision and the migration policy must be designed together, not independently
• State management decision record — offline-first mobile state management adds the sync state layer to the client state model: the local SQLite database is the authoritative client-side store, mutations write to SQLite first and sync asynchronously, and reads serve from SQLite exclusively; the conflict resolution matrix is the specification that determines how the sync state layer resolves divergent versions before committing the merged state to the local database; the derived-state complexity of an offline-first app is higher than an online-first app because the UI must reflect three states for any entity: synced (server and local agree), pending_upload (local has mutations not yet on the server), and conflict (server and local have divergent versions requiring resolution)
• Real-time architecture decision record — the sync protocol for an offline-first mobile app is a specific instance of the real-time architecture choice: push-pull polling is the SSE-less equivalent of HTTP long polling (acceptable latency, low infrastructure complexity), server-sent events provide sub-second server-to-client delivery without persistent bidirectional connections, and WebSocket sync is the full real-time collaborative architecture; the selection should be driven by the same criteria as any real-time architecture decision: whether the use case requires sub-second delivery of other clients' mutations, and whether the infrastructure can support the persistent connection model at the required concurrent user scale
• Error handling strategy decision record — the error handling model for offline-first mutation sync has three distinct failure cases that require different responses: upload failure (the pending mutation fails to reach the server — retry with exponential backoff, preserve the mutation in the queue), conflict failure (the mutation is rejected by the server because it conflicts with a server-side mutation that arrived first — create a conflict record, surface both versions to the user or apply the field-level merge rule), and validation failure (the mutation is rejected by the server because it violates a business rule — for example, a state machine transition that is invalid — present the error to the user and remove the mutation from the queue); conflating these three failure cases produces incorrect behavior: treating a validation failure as a retryable error means the mutation retries indefinitely and never surfaces the business rule violation to the user
• Background job infrastructure decision record — the sync upload job (uploading the pending_mutations queue to the server when connectivity is available) and the pull sync job (downloading server mutations to the local database on the pull schedule) are background operations that run outside the main UI thread and must handle failures with retry and dead-letter visibility; the upload job's specific requirements (must respect device battery budget, must not retry failed validations, must checkpoint progress so that a partial upload can resume without re-uploading already-processed mutations) should be designed as part of the sync protocol ADR and implemented with the same rigor as any other background job with durable state
• Caching strategy decision record — for offline-first mobile apps, the local SQLite database serves two distinct purposes that require different invalidation models: the offline-first store (entity types that the user can mutate offline, merged with the server on sync using the conflict resolution strategy — these are never overwritten by a cache refresh, only merged) and the read cache (server-fetched data that the app stores locally for fast reads but never mutates — these can be invalidated and replaced on any pull sync without conflict resolution); conflating these two purposes in a single SQLite schema (treating offline-writeable entities the same as read-only cached entities) produces incorrect behavior when a cache refresh overwrites a locally-mutated entity that hasn't synced yet
• Authentication strategy decision record — the logout event in a mobile app with local data storage must include a local data purge step: clearing the SQLite database, removing the authentication token from the platform Keychain, and clearing in-memory state; a logout that only clears the in-memory token but leaves the SQLite database intact means that the local data remains readable on a shared device (a device passed to another employee) or after a device wipe that preserves app data; the session management approach and the local data lifecycle are coupled decisions that must be specified together in the authentication ADR
• The new-CTO onboarding problem — inheriting a mobile app with an undocumented offline model means inheriting a codebase where the conflict resolution strategy is either implicit in the sync library's default behavior (which may be LWW by received_at regardless of domain correctness) or in a comment that says "we'll figure out conflicts later"; the new technical leader cannot answer "what happens when two district managers edit the same account record while independently offline?" from the code alone — the answer requires domain knowledge about which role should win and which fields should merge, knowledge that existed in the founding team's heads during the sprint where the sync layer was built and was never written down