Offline-First Flutter: SQLite vs Hive, and Handling Sync Conflicts 

Between 2021 and 2022 I built the Agent and Broker apps at Bijak — India's largest agri-trading platform — from zero to one. Real-time area analytics, demographic insights, comparable-sales filtering, and bulk commodity trading with automated brokerage-fee calculation. The users were not in Bangalore offices with fibre. They were in mandis and on field visits in rural connectivity conditions, on Android phones, where the network is not "slow" in a way you can fix with a spinner. It is present, then absent, then present again with 4KB/s and a proxy that swallows requests.

That is the context that makes offline-first a design constraint rather than a feature. And it is the context where the interesting question is not "which local database" — it is what happens when two people edited the same trade while both were offline. This post covers both, but the second one is the one that matters.

When offline-first is a requirement, not a nice-to-have

Offline-first is expensive. It changes your data model, your error handling, your testing, and your definition of "saved." So the first honest question is whether you need it, and most apps do not.

You need it when the user's task cannot wait for the network. That is the whole test. A social feed can wait — the user shrugs and refreshes. A broker standing in a mandi recording a commodity trade cannot wait, because the counterparty is walking away and the trade is happening whether your app is ready or not. If the app cannot capture it, the app is not part of the workflow anymore. It gets replaced by a notebook, and you do not get a second chance at that.

The signals that you are in requirement territory:

• Users work where connectivity is structurally bad — rural, underground, in-transit, industrial. Not "sometimes slow." Structurally bad.
• The task is time-bound to a physical event that will not pause for a retry.
• Losing an input costs the user real money or real trust. A dropped trade entry is not a UX papercut.
• The read data is useful without being fresh. Yesterday's comparable sales are still worth something; a live auction price is not.

And the counter-case, which deserves saying: if your users are on urban 4G doing tasks that tolerate a retry, offline-first is a large amount of complexity buying you a marginally nicer failure mode. A good cache and honest error states get you most of the way. Do not import a distributed systems problem you do not have — the MVP scoping post is the general version of this argument.

SQLite vs Hive: relational queries vs speed and simplicity

Both are on my résumé and I have shipped both. They are not really competitors either — they answer different questions, and picking wrong shows up months later as a workaround rather than as a bug.

Dimension	SQLite (sqflite, Drift)	Hive
Model	Relational tables, SQL	Key-value boxes of typed objects
Querying	Joins, WHERE, GROUP BY, indexes — the full toolkit	Load the box, filter in Dart
Filtering large sets	Indexed query, memory stays flat	You iterate; memory scales with the box
Write path	Transactional, ACID, rollback on failure	Fast appends to a log-structured file
Schema changes	Explicit migrations — real work, but a real record	Adapter versioning; easy to get subtly wrong
Relationships	Foreign keys, cascade, integrity enforced	You model references by hand and enforce nothing
Setup cost	Higher — schema, DAOs, migrations	Very low — register an adapter and go
Typical failure at scale	A slow query you can EXPLAIN and index	A jank you fix by rewriting to SQLite
Best for	Domain data you query across: trades, listings, comparables	Settings, tokens, cached blobs, a small mutation queue

The heuristic I would actually give: if you will ever want to ask a question that has the word "where" or "join" in it, you want SQLite. Comparable-sales filtering is a hard example — filter by commodity, by region, by date range, sorted by price, across enough rows that you cannot hold them all in memory on a mid-range Android phone. That is a query. Doing it in Dart over a Hive box means loading everything to filter to a handful, and on a device with 2GB of RAM in a list the user scrolls, that is not a performance problem, it is a crash — the same image-and-memory class I wrote about in the crash reduction post. Hive earns its place for the things that are genuinely key-value: session state, feature flags, user preferences, a small durable queue. It is fast, it has almost no ceremony, and using SQLite for a settings map is its own kind of mistake.

Using both is not a compromise, it is usually the right shape. Relational data in SQLite, the boring key-value tail in Hive. What you should not do is start in Hive because it is quick and discover your domain was relational all along, because that migration is a rewrite of your data layer under a live app with unsynced local data on real users' phones. That is the worst refactor in mobile.

The sync problem is the real problem

Choosing a database takes an afternoon. Sync takes the rest of the project.

The mental model that helps: an offline-first app is a distributed system with a very bad network, and your users are the partition. Every hard thing about distributed systems is now in your mobile app: ordering, conflict, idempotency, partial failure. The only mercy is that the partitions are small and eventually heal.

At Bijak the sync side went through AWS — S3, SNS, and AppSync for real-time data synchronisation — but the conflict question is independent of the transport. It is a design decision, and there are three real answers.

Strategy	How it decides	What it costs	Use when
Last-write-wins	Highest timestamp wins	Silently destroys the loser's edit; depends on clocks you do not control	Single-user data where a lost edit is an annoyance — drafts, preferences
Vector clocks / version vectors	Detects genuine concurrency vs stale writes	Real complexity; still hands you a conflict to resolve, just an honest one	Multi-device collaborative editing where merging is meaningful
Server-authoritative	Client proposes, server validates and decides	You must design the API around intent, not state	Anything where money, inventory, or contracts are at stake

Why last-write-wins is a trap on a trading app

Last-write-wins is the default because it is what you get if you do nothing: the client PUTs the object, the server stores it, the later request wins. It works fine right up until the data is money.

Consider a brokerage-fee calculation on a bulk commodity trade. The broker edits the quantity offline. Somewhere else, someone edits the rate. Both sync. Last-write-wins picks a timestamp and the other person's edit vanishes — and the surviving record is a combination that neither human ever agreed to, with a fee computed from it. Nothing errored. Nothing alerted. The number is just wrong, and it stays wrong until someone in accounts finds it.

The clock problem compounds it. Device clocks are wrong, sometimes by hours, sometimes deliberately. If "later" is decided by a client timestamp, you have delegated financial correctness to a setting the user can change in Settings.

Server-authoritative, and why intent beats state

The pattern that holds up when money is at stake: the client does not sync state, it submits intent, and the server decides.

The difference is everything. Syncing state means sending "this trade is now 40 tonnes at ₹2,000" — a claim about the world that will overwrite whatever is there. Submitting intent means sending "set quantity to 40, and I believed the version was 7" — a request the server can accept, reject, or flag.

That gives you three properties you cannot get from state sync:

• Conflicts become detectable. Version 7 is no longer current, so the server knows this write raced another one instead of quietly winning.
• Business rules apply at the boundary. The brokerage fee is computed server-side from validated inputs. A client that was offline for six hours cannot push a fee computed from a stale rate table.
• Resolution can involve a human. Which, on a trade, is often the only correct answer. Two humans disagreeing about a quantity is not a merge problem. It is a phone call. The app's job is to notice and surface it, not to pick a winner and hide the evidence.

That last point is the one I would most want to transfer. Automatic conflict resolution is a decision to lose data quietly. Sometimes that is fine — a draft, a preference, a read-receipt. On anything with a rupee attached, quiet is the failure. Surfacing "this trade changed while you were offline, here is both versions" is more work and a worse demo, and it is correct.

Queueing mutations properly

The queue is where offline-first apps are actually won or lost, and it is smaller than people expect. The rules:

• Persist the queue, not the intention to have one. An in-memory list of pending writes is gone when Android kills your process to reclaim memory — which, on a mid-range phone with the app backgrounded in a mandi, is not an edge case, it is Tuesday.
• Give every mutation a client-generated ID. A UUID minted on the device, sent with the request, used by the server as an idempotency key. This is the single highest-value line of code in the whole system: it is what makes retries safe. Without it, "request succeeded but the response was lost" — which is the single most common outcome on a bad network — becomes a duplicate trade.
• Preserve order where order is semantic. Create-then-update must not become update-then-create. Order per entity, and be willing to be strict about it: if a mutation fails permanently, everything queued behind it for that entity is now suspect. Do not blithely continue.
• Distinguish retryable from terminal. A timeout retries. A 422 does not — that request will fail identically forever, and retrying it every 30 seconds until the battery dies is a bug I have seen ship. Terminal failures need a human, which means they need UI.
• Back off, and add jitter. When a village's connectivity returns, every device in it retries at once. Synchronised retry storms are self-inflicted.
• Make pending state visible. The user must be able to tell "saved on this phone" from "the server has it." Collapsing those two into one checkmark is a lie that costs you trust exactly once.

class PendingMutation {
  final String id;          // client UUID — the idempotency key
  final String entityId;    // ordering domain
  final int seq;            // order within the entity
  final String type;        // 'trade.updateQuantity'
  final Map<String, dynamic> payload;
  final int baseVersion;    // what the client believed — enables detection
  final int attempts;
  final DateTime queuedAt;
}

baseVersion is the field that turns a dumb queue into a correct one. Without it the server cannot distinguish "this client is applying a change to current data" from "this client has been offline for six hours and is about to clobber three edits."

What breaks at 2G

Testing on airplane mode is testing the easy case. Airplane mode is binary and honest: the request fails immediately, your error handling runs, everyone is happy. Real rural connectivity is worse than offline because it is ambiguous.

What actually happens on the bad network:

• Requests hang instead of failing. No response, no error, for a very long time. Without an aggressive client timeout, your app appears frozen while the user stands there. Set timeouts shorter than you think — an unanswered request is worse than a failed one, because a failed one you can queue.
• Writes succeed and the response is lost. The server committed; the client never heard. The client retries. Without idempotency keys you now have two trades. This is the failure that makes the client UUID non-negotiable.
• Connectivity lies. The OS reports a connection because the phone is attached to a tower. That says nothing about whether packets reach your API. Treat connectivity checks as a hint for scheduling, never as a precondition for correctness — the code path must assume the request can fail regardless.
• Payloads that were fine become fatal. A 2MB sync response at office speeds is invisible. At 4KB/s it is minutes, and it will not survive the walk between two buildings. Paginate small, sync deltas rather than snapshots, and accept partial progress rather than restarting.
• Everything is a partial failure. Batches half-apply. Design every sync operation to be resumable from wherever it stopped, because it will stop.

The instinct that fixes most of this: the local database is the source of truth for the UI, always. The user's action writes locally, the UI reflects the local write, and sync is a background reconciliation the user never waits on. If any screen shows a spinner because the network is thinking, you have built an online app with a cache. The distinction sounds pedantic and it is the entire architecture.

It is also why the ~25% reduction in user-error support tickets across those apps was mostly a design outcome rather than a code one — showing people honestly what had synced and what had not removed a whole category of "did it save?" confusion that no amount of retry logic addresses.

The Takeaway

Offline-first is a requirement when the user's task is bound to a physical event that will not wait — rural field work, a trade happening now — and expensive complexity everywhere else. Use SQLite for anything you will query across and Hive for the genuine key-value tail; both is normal, and starting in Hive with relational data is the worst refactor in mobile. But the database is the easy half. The hard half is sync, and on anything with money attached the answer is server-authoritative with client-submitted intent, never last-write-wins on a device clock you do not control. Persist the queue, mint a client UUID as an idempotency key, carry the base version so conflicts are detectable, and surface conflicts to a human rather than resolving them quietly. At 2G the network does not fail cleanly — it hangs, half-succeeds, and lies about being connected, so treat the local database as the source of truth and sync as background reconciliation nobody waits on.

I built these apps 0→1 at Bijak — the full story is in the Bijak case study. I'm a mobile engineer open to full-time roles; more about me or my résumé.

Frequently asked questions

Should I use SQLite or Hive in a Flutter app?

SQLite for anything you will query across — filters, joins, sorting over more rows than fit comfortably in memory. Hive for the genuine key-value tail: settings, tokens, cached blobs, a small mutation queue. Using both is normal.

When is offline-first actually necessary?

When the user's task is bound to a physical event that will not wait for a retry — a trade being recorded in a mandi, field work in rural connectivity. If your users are on urban 4G doing things that tolerate a retry, a good cache and honest error states get you most of the way.

How should I resolve sync conflicts in an offline-first app?

For anything with money attached, server-authoritative: the client submits intent plus the version it believed, and the server validates and decides. Last-write-wins silently destroys an edit and depends on a device clock the user can change.

How do I stop duplicate writes when the network is unreliable?

Give every queued mutation a client-generated UUID and have the server treat it as an idempotency key. The most common bad-network outcome is a request that succeeded while the response was lost — without an idempotency key, the retry creates a duplicate.

What breaks on a 2G connection that does not break offline?

Ambiguity. Requests hang instead of failing, writes half-succeed, and the OS reports connectivity that says nothing about whether packets reach your API. Airplane mode is the easy case because it fails honestly.

Bottom line

Picking a local database takes an afternoon; sync takes the rest of the project. An offline-first app is a distributed system whose partition is your user — and automatic conflict resolution is a decision to lose data quietly, which is only acceptable when no money is attached.