SQLite Offline-First Sync: Replication Design Guide

SQLite is the most widely deployed database engine in the world, yet most apps built on it still assume a live connection. The SQLite project's deployment record documents that reach, but the sync layer around the engine is where teams usually fail.

An offline-first sync layer lets each device read and write a local database first, then reconciles changes with other devices and the server once a connection returns. For operators running 10 to 150 locations, that reconciliation decides whether inventory counts, job tickets and customer records agree at close of day. This guide covers schema design, conflict rules, replication topology and failure testing.

What You'll Learn

• When offline-first architecture beats online-first for multi-location field work, and when it does not
• How to design SQLite tables that track sync state, deletions, and record identity
• Which replication pattern fits push, pull, and change-feed workloads
• How to pick a conflict resolution strategy that matches your business rules
• How to roll out, test, and monitor sync across many locations without inflating cost per location

Why Offline-First Matters for Multi-Location Field Teams

A failed write is a lost record, not a retry waiting to happen. When a technician closes a job in a basement with no signal, the app either blocks the save or drops it. Either way, the office sees the wrong picture.

Multi-location teams feel this in four places:

1. Duplicate jobs, when a technician re-enters work after the first save silently failed.
2. Missing invoices, when the billing record never reaches the central system.
3. Stale parts counts, which send the wrong inventory to the wrong site.
4. Lost leads, when a form filled out on site never reaches the CRM.

The cost scales with location count. Take a hypothetical 40-location group where each site loses two job records a week to dropped saves. That is 80 bad records weekly. Each one needs a person to find it, check it, and fix it by hand.

Assume 15 minutes of staff time per record. That is 20 hours a week of reconciliation work, before counting the revenue that never gets billed. Those hours land on office staff who are already stretched across every site.

Defining offline-first architecture

Offline-first is an architecture where the local database is the first place the user reads and writes. The device saves every change to its own database right away. The network becomes a background job that pushes and pulls changes when a connection exists.

This differs from an online-first design. In that model, the server is the source of truth, and the app waits on it before it confirms a save. A weak signal then becomes a failed action.

The principle has a name in the software community. The Ink and Switch local-first research lists "the network is optional" among its seven ideals for local-first software. Ink and Switch's local-first essay explains why the local copy should carry the user's work.

Why SQLite fits the local layer

SQLite is a serverless, self-contained database engine that reads and writes directly to an ordinary file on the device. No server process has to be running for a write to succeed. That makes it a practical base for the local side of a sync layer.

A custom sync layer still needs design work. You must decide how to queue changes, how to order them, and how to resolve two sites editing the same record. Teams that want this built correctly usually start with custom software development rather than bolting sync onto an online-only app.

Key Insight: Offline-first moves the save from the server to the device, so a dead signal no longer means lost work. Design the local write path first, then treat the network as a delivery channel.

Offline-First vs Online-First: Architecture Tradeoffs

Every architecture choice comes down to one question: what happens when the network drops? Teams answer it in one of three ways.

• Online-first: The server is the source of truth. The app reads and writes over the network every time.
• Offline-capable: The app keeps working without a connection, but only for a narrow set of tasks, usually read-only views or a few queued actions.
• Offline-first: The device's local database is the primary store. The app writes locally first, and a sync layer reconciles with the server later.

SQLite fits the offline-first pattern well because it runs inside the application process with no separate server. SQLite's own guidance on appropriate uses describes this embedded model in detail.

How the three designs compare

Latency and consistency tradeoffs

Latency is the most visible difference. Online-first designs make the user wait for every network call. Offline-first designs remove that wait for reads and writes.

Consider a hypothetical 40-location service group whose technicians often work in low-signal areas. If each server call takes two seconds and a technician opens 30 records a day, online-first adds one full minute of waiting per technician, per day.

Consistency moves in the opposite direction. Offline-first accepts that two people may edit the same record at the same time. You need a rule for that moment:

1. Last-write-wins: The newest change overwrites the older one. It is simple, but it silently drops edits.
2. Field-level merge: Changes to different fields combine. It preserves more work but needs careful schema design.
3. Manual resolution: A person picks the correct version. It is accurate but slows operations.

Development effort and failure behavior

Online-first is the cheapest to build. The server owns the data, and the client mostly displays it. Most teams can ship it with standard REST or GraphQL calls.

Offline-first is the most expensive. Your team must handle local schema migrations, sync queues, retries, conflict rules, and a clear sync status for users. The Service Worker API documentation on MDN covers the browser side of caching and background work.

Failure behavior also differs. Online-first fails loudly, so users know something is wrong. Offline-first fails quietly, which can hide stale data from managers who assume every location sees the same numbers. Always show a visible "last synced" time.

Matching the architecture to the use case

• Choose online-first when data changes constantly across locations and users are almost always connected, such as a live pricing or inventory dashboard.
• Choose offline-capable when users need a few screens offline, such as a reference manual or a checklist.
• Choose offline-first when field staff work in places with poor signal and must record work without waiting, such as inspections, repairs, or intake forms.

If you are weighing an offline-first build, the right service usually starts with a native or cross-platform app. The sync backend often needs custom software built for your own conflict rules.

Key Insight: Offline-first is a product decision before it is a technical one. Pick it only when the cost of waiting or losing work at a location is higher than the cost of building and maintaining conflict rules.

SQLite Schema Design for Offline Sync

The local schema decides whether offline sync works. A device that writes rows without a plan for identity, change tracking, and deletion creates conflicts that are hard to untangle later. Design the local tables before you choose a sync engine.

Use client-generated UUIDs for primary keys

Auto-increment integers come from one database. Two phones that work offline can each create a row with ID 41, and the server has no clean way to merge them.

Generate IDs on the device instead. The RFC 4122 specification defines UUIDs that can be created without asking a central server, which makes them safe for offline writes.

• Store IDs as TEXT for easy debugging, or as a 16-byte BLOB for smaller, faster indexes.
• Never reuse an ID, even after a row is deleted.
• Create the ID at the moment the user creates the record, not when the sync runs.

Use STRICT tables so SQLite rejects a value of the wrong type. This stops bad data from reaching the sync queue.

Add updated_at and version columns

Every synced table needs two bookkeeping columns:

• updated_at: a UTC timestamp in ISO 8601 format, set on every local write.
• version: an integer that increases by one on every write to the row.

Device clocks drift. A phone set to the wrong time can make an old edit look newer than a recent one. Use version for conflict checks and treat updated_at as a hint for display.

Soft deletes with tombstones

A hard delete erases the evidence the server needs. Suppose one technician deletes a work order offline while another edits it. The server must know the order was deleted, or it may bring the record back.

Use a tombstone instead. Set a deleted_at timestamp and keep the row in place. Hide tombstoned rows from the user interface with a filter.

Purge tombstones only after every active device has confirmed the delete. Most teams keep them for 30 to 90 days as a safety window, then remove them in a batch job.

Build an outbound write queue table

The outbox table records every change that has not reached the server. It is local only, so it can use a simple integer sequence for ordering.

A typical outbox includes these columns:

1. seq: an auto-increment integer that sets the send order.
2. entity: the table name, such as work_orders.
3. row_id: the UUID of the changed row.
4. operation: insert, update, or delete.
5. payload: the changed fields as JSON text.
6. base_version: the row version the device last saw, used for conflict checks.
7. attempts and last_error: retry tracking.

Write the data change and the outbox entry in one transaction. SQLite's write-ahead log keeps that write atomic, so a crash cannot save one without the other. Without this step, changes get lost or sent twice.

Teams that build these systems often hire a partner that has done offline data work before. If you need that help, custom offline-capable apps are a common starting point.

Key Insight: Give every row a client-generated UUID, a version, and a tombstone, and log every write in an outbox inside the same transaction. These four pieces make conflicts traceable and keep data from disappearing when devices reconnect.

Need help applying this to your business? Tell us about your project and we will scope it with you.

Replication Patterns: Push, Pull, and Change Feeds

Every offline-first sync layer runs two loops. The push loop sends local changes to the server. The pull loop brings server changes back to the device. Each loop needs clear rules for batching, retries, and duplicate protection.

Push: draining the outbox

Local writes should never go straight to the server. Each mutation lands in an outbox table inside the same SQLite transaction as the data change. If the write commits, the mutation exists. If the app crashes, the row survives.

The push worker reads the outbox in order and sends mutations in batches. A batch of 25 to 100 operations suits most mobile workloads. Keep the order intact for each record, so an edit never arrives before the insert it depends on.

Mark a row as sent only after the server confirms it. Delete the row only after that confirmation. Deleting on send loses data if the response never arrives.

Pull: asking for changes since a cursor

The cursor records how far the device has synced. The server assigns each change an increasing sequence number. The client stores the last sequence it applied and asks for everything after it.

Do not use device clocks for the cursor. Phone clocks drift, and timestamps from different devices cannot be ordered reliably. A server-assigned sequence avoids that problem.

Apply each pulled page in one transaction. If the page fails halfway, the cursor stays where it was. The next pull repeats the page safely.

Local reads should keep working during a sync. SQLite's write-ahead logging mode lets readers continue while a writer commits. Enable it before the first sync so the UI never freezes behind the pull loop.

Retries with backoff

Phones lose connectivity all the time. Retries are required, but unlimited retries drain batteries and overload servers. Use exponential backoff with jitter:

1. Wait 1 second after the first failure.
2. Double the wait after each failure: 2, 4, 8, then 16 seconds.
3. Cap the wait at 5 minutes.
4. Add random jitter of up to 20 percent, so thousands of devices do not retry at the same moment.

Retry only on network errors and server 5xx responses. A 4xx response means the mutation itself is invalid. Move that row to a review queue instead of retrying it forever.

Idempotent operation IDs

Retries create duplicates. A request can reach the server, commit, and lose its response on the way back. The device then sends the same mutation again. Without protection, the server writes the record twice.

Give every mutation an idempotent operation ID when the user makes the change. Generate a UUID or ULID on the device. Store processed IDs on the server under a unique constraint.

Then use an upsert that ignores repeats. In SQLite, INSERT ... ON CONFLICT DO NOTHING does this. The SQLite UPSERT documentation covers the conflict clause in full. Postgres offers the same pattern.

On a repeat, return the stored result. The device receives the original answer and clears its outbox row. The data stays correct, and the user sees one order, not two.

Teams that want this plumbing built and tested end to end can start with the right service for offline mobile apps.

Key Insight: Sync safety depends on three rules working together: the outbox holds every change, the cursor only moves after a successful page, and every operation ID is processed once. Remove any one of them and retries will eventually corrupt data.

Conflict Resolution Strategies That Hold Up in Production

Every offline-first app will produce conflicts. Two devices edit the same record while offline, then both sync. A rule has to decide which change survives. Pick the rule per record type, not once for the whole app.

Last-write-wins

Last-write-wins (LWW) keeps the change with the newest timestamp and discards the other. It is simple to build and easy to explain. It also discards data without warning.

LWW fits records where the latest value is always the right one, such as a technician's availability flag or a preferred contact time. It does not fit anything that counts. Phone clocks drift, so the "newest" write can be the older one. Use a server-issued sequence number or a hybrid logical clock instead of device time.

Field-level merge

Field-level merge compares changes one column at a time. If device A edits a phone number and device B edits the street address, both edits survive. Only changes to the same field need a tiebreaker.

This works well for customer profiles and job tickets with many independent fields. The cost is more code and more test cases. Each field needs a merge rule, and each row needs version data for every column.

Server-authoritative

Server-authoritative resolution makes the central database the final judge. Devices send proposed changes. The server accepts or rejects each one, then returns the official state. The device replaces its local copy with that state.

Use this for money and stock. A balance should never be decided by whichever phone synced last. The trade-off is that a rejected offline edit can vanish from the user's view unless the app explains what happened.

User-resolved

User-resolved conflicts pause the sync and show both versions. A person chooses one or combines them. This is the right call when software cannot judge the business meaning.

Use it for service notes and inspection findings. Two technicians can record different observations about the same unit, and both may be accurate. A silent rule that picks one can hide a safety detail. Keep the queue short, or staff will tap "keep mine" without reading.

Matching rules to business records

Assign one rule to each record type before writing sync code:

1. Inventory counts: server-authoritative, with devices sending deltas (sales, receipts) instead of absolute totals.
2. Invoices and payments: server-authoritative and append-only.
3. Service notes and inspection findings: user-resolved, with both versions kept.
4. Customer contact details: field-level merge.
5. Appointment status and preferences: last-write-wins.

Inventory shows why the rule matters. Suppose two offline devices each record a count of 40. A sale of two units then happens on one device. Overwriting with the last absolute value erases the sale. Storing the sale as a delta applies it exactly once.

SQLite helps with atomic commits. Each transaction either finishes fully or rolls back, so a merge step never leaves half a change behind. SQLite's ON CONFLICT clause handles uniqueness collisions at the row level, but it knows nothing about your business rules. That logic belongs in your sync service. Teams that need help choosing this split can review the right build approach before committing to a schema.

Key Insight: Decide the conflict rule per record type, starting with anything that holds money or stock. Store changes as deltas and let the server apply them, so one offline edit can never erase another.

Rolling Out Sync Across 10 to 150 Locations

Do not switch 150 locations to sync at once. Roll out in waves, and gate each wave on measured results before moving on.

Phase the rollout by location cohort

Group locations into three cohorts. Each cohort has a clear entry and exit rule.

1. Pilot wave: 3 to 5 locations with mixed devices and at least one skeptical manager.
2. Early wave: roughly 20 percent of the remaining fleet, chosen to cover each region.
3. Full wave: the rest, in batches of no more than 25 locations per week.

Move to the next wave only when the sync error rate and the conflict queue stay flat for two weeks. Write the thresholds down before the pilot starts, so nobody argues about them later.

Build a device inventory before any code ships

A 150-location group can run several hundred tablets, handhelds, and laptops. Offline sync fails most often on old hardware with little free storage. List every device before the pilot begins.

Retire or replace any device that cannot hold the database with headroom. Tag every device to a location so support can trace problems fast.

Migrate the schema on existing installs

Existing installs already hold a SQLite file with live rows. Never drop and recreate the database on those devices. Store a schema version in the file. SQLite keeps an integer for this purpose, set with PRAGMA user_version.

SQLite supports only some ALTER TABLE changes, so some migrations need a table rebuild. Follow this order:

1. Ship additive changes first: new tables and new nullable columns.
2. Backfill data in small batches while the app is idle.
3. Keep the old write path working for one full release.
4. Remove old columns only after every device reports the new version.

Test each migration on a copy of a real device database from the pilot wave, not on a clean test file.

Plan the fallback path

Define what happens when sync breaks. Most teams need four fallback modes:

• Local-only mode: staff keep working, and writes queue on the device.
• Read-only snapshot: the device shows the last good copy of job data.
• Version rollback: the app store release is paused, and the prior build is restored.
• Manual export: queued records are exported for the office to enter by hand.

Set one hard rule. A device never deletes unsynced rows during a fallback. The queue stays until the server confirms each record.

We built a custom Next.js marketing site and a client invoice portal with QuickBooks Payments integration for a commercial equipment repair business. That work shows the same principle: field records must reach the back office in a clean, traceable path. For teams that need a build of this scope, custom software is the right starting point.

Key Insight: Roll out sync by cohort, not all at once. Each wave should prove the migration, the device base, and the fallback path before the next one starts.

Testing, Monitoring, and Cost per Location for Sync

Offline bugs rarely show up at a desk with stable Wi-Fi. Test the network states your field staff actually face. Cover at least these cases:

• Full airplane mode for 30 minutes, then reconnect
• Connection drops in the middle of an upload batch
• Slow, lossy connections with high latency
• The operating system kills the app while a write is pending
• Two devices edit the same record while both are offline

Automate the first four with a network simulator. Run the last one by hand on physical devices before each release. Emulators skip battery limits and background task rules, so real hardware belongs in the test pipeline.

Test idempotency too. Replaying the same upload twice must not create a duplicate invoice or visit. Give every local write a unique ID generated on the device.

Teams building on SQLite can lean on the platform's own tools. SQLite's write-ahead logging mode lets readers and one writer operate at the same time, which keeps the UI responsive during background sync. Test that behavior under load, not just in a quiet demo.

Sync health metrics to watch

Track these four numbers for every location, not just for the whole system. A single weak site can hide inside a healthy average.

1. Queue depth: pending local writes that the server has not yet acknowledged.
2. Conflict rate: conflicts per 1,000 synced writes, split by record type.
3. Sync lag: time from a local write to server confirmation, reported at the 95th percentile.
4. Stale devices: devices with no successful sync in the last 7 days.

Alert on sustained growth in queue depth, not short spikes. A queue that rises for a full shift signals a broken connection or a stuck process. Review conflict rates weekly, because a rising rate usually means two workflows are editing the same fields.

We build these dashboards as part of custom software for multi-location operators, so managers can compare sites side by side.

Estimating build and maintenance cost per location

Use one formula so every option gets judged the same way:

Cost per location per year = ((One-time build cost + (Annual maintenance cost x Years)) / Locations) / Years

Illustrative example, not a quote: a 40-location group spends $120,000 to build and $24,000 per year to maintain over 3 years. Total cost is $192,000. Divided by 40 locations, that is $4,800 per location. Divided by 3 years, it is $1,600 per location per year.

Include these line items in the build cost:

• Engineering hours for sync logic, conflict rules, and retries
• Device testing across phones and tablets your staff use
• Sync backend hosting and storage
• Support for OS and browser updates each year
• Monitoring and on-call coverage

Then compare that figure with the alternatives. A per-seat subscription to a sync platform multiplies by devices and locations, so model it the same way. For mobile options, see the right mobile app approach before you commit to one stack.

Key Insight: Test the failures your field staff will hit, watch queue depth and conflict rate per location, and divide total cost by locations and years. That number is the one to compare against every alternative.

Related resources

Explore more from Gaazzeebo on this topic:

• Resource: the business automation playbook

Real-world example

For how this plays out in production, see the breckenridge vipers case study, a concrete walk-through of the approach, timeline, and outcome.

Sources and References

Primary sources cited above:

• The SQLite project's deployment record
• Ink and Switch's local-first essay
• SQLite is a serverless, self-contained database engine
• The Service Worker API documentation on MDN
• STRICT tables
• write-ahead log
• SQLite UPSERT documentation
• transaction
• ON CONFLICT clause
• PRAGMA user_version
• ALTER TABLE changes