skills / android

db

Persistence and offline behaviour: where a value lives, what survives, transactions, migrations, and what happens when the network does not answer. Use when the app stores data on the device.

topicv1.0.112 must3 should~1,900 tokens

$npx skyl.dev add android/db

Installs android/core with it, because a layer that refers to its core reads wrong without it.

Rules

installed

Scope, priority, and when the whole skill does not apply

Persistence and offline behaviour: where a value lives, what survives, and what happens when the network does not.

core owns which data has a single source of truth (DATA-1) and when it is destroyed (DATA-4). mvvm owns where the choice between sources is made (REPO-1). android/security owns how a value is protected. This owns how the store itself behaves.

Scope. New code and new tables. Match the schema conventions already in the module.

When a rule here conflicts with the code you are editing the surrounding convention wins for style and structure, but never for a rule whose failure loses user data, leaks a credential, or ships a crash. Fix those in their own change, not inside another one.

When not to apply(whole-skill): a cache that is genuinely disposable and re-fetched every launch, with nothing a user would miss.

Priority. must, the failure loses user data or ships a crash. should, real exceptions exist; name yours.

Choosing a store

STORE-1mustChoose by the shape of the data: scalars and flags in a typed key-value store (DataStore), anything queried or related in the database, large blobs on the filesystem with the path in the database. SharedPreferences is not the answer in new code.

WhySharedPreferences is a synchronous API in front of a file. The first read blocks whichever thread asks, apply() reports nothing when the write fails, and commit() blocks to tell you. DataStore gives the same job an asynchronous API, a typed schema, and failures you can observe.

Not whenan existing, widely-used preference. Migrating it risks losing data for no gain, wrap it, do not move it. See references/storage-choice.md.

STORE-2mustKey-value storage is reached through one class that owns the keys, never by reading a key at the call site.

Whya string key repeated in four files is four chances to mistype it into a silent default and the default looks exactly like a real value. The wrapper is also what makes the store swappable and testable.

Not whennever, this is one small file.

STORE-3mustA secret does not belong in any store this skill describes. android/security owns where it goes and how; follow it rather than choosing here.

Whya secret has different requirements from data, a key with a lifetime, exclusion from backup, a store that is not the one a schema lives in, and every one of them belongs to a different skill. Deciding it here means deciding it twice, and the two answers drift.

Not whennever, and if android/security is not installed on a project that stores a secret that is the thing to fix rather than this rule.

SCHEMA-1mustEvery schema change ships a migration. Destructive fallback is never enabled in a release build.

Whythe fallback drops and recreates the tables, so the app updates and the user's data is gone, silently, with no crash and nothing to recover from. It is a development convenience that reaches production precisely because nothing fails when it does.

Not whena table that is purely a cache of remote data, and even then scope the fallback to that table rather than enabling it database-wide.

SCHEMA-2shouldExport the schema and commit it.

Whythe exported JSON is what makes a migration testable, and what shows a reviewer that a column changed. Without it, migrations are written from memory against a schema nobody can see.

Not whena pre-release app with no installed users.

Writes

WRITE-1mustA write spanning more than one statement is one transaction. Replacing a cached collection, delete, then insert, is the common case.

Whya failure between the delete and the insert leaves the store empty, and a user who was offline now has nothing where they had stale-but-usable data a moment ago. The window is small which is why it survives testing and shows up in the field.

Not whena single statement, which is already atomic.

WRITE-2mustA change the user made is applied locally first and survives without the network. Do not make a user-visible change conditional on a request succeeding.

Whyit is the difference between an app that works on a train and one that does not. A save that exists only once the server acknowledges it is lost on every failed request, and the user is not told.

Not whenthe write genuinely cannot be resolved locally, a payment, an identifier the server must assign.

WRITE-3shouldA local write that must reach the server is recorded as pending in the store, sent in order, and stays visible if it permanently fails.

Whyan in-memory retry queue dies with the process, which is exactly the moment it was needed. And a pending write silently dropped after its retries is a lost write the user believes succeeded.

Not whenthe write is local-only with no server counterpart. See references/offline-writes.md.

Cache lifecycle

CACHE-1mustStaleness is one rule in the data layer with a stated duration, not a judgement made at each call site.

Whythe same question asked in three places gets three answers, and the one that refetches on every screen open is the one that spends the user's data. One rule, one place, one duration that can be changed.

Not whendata that must always be live, and "never cache" is also a rule in one place.

CACHE-2mustA forced refresh is the same path with a flag, not a second path that bypasses the cache.

Whytwo paths diverge. The one behind pull-to-refresh gains a fix the ordinary path does not and the two stop agreeing about what "loaded" means.

Not whennever, if forcing needs different behaviour, that is a parameter.

CACHE-3mustNever empty the cache as part of an ordinary read. Stored data stays until it is replaced or explicitly cleared.

Whyclearing before fetching means every failed refresh costs the user their offline copy. The correct order is fetch, then replace, in one transaction.

Not whenthe user signed out, and that is core DATA-4, which deletes rather than clears.

Deletes and conflict

SYNC-1shouldA delete that must propagate leaves a tombstone, kept at least as long as a device may plausibly stay offline.

Whywithout one, the next sync sees a row the server still has and the client does not, and restores it. The deleted item comes back, which reads to the user as the app ignoring them.

Not whendeletes are local-only, or the server sends authoritative full state.

SYNC-2mustThe client does not arbitrate conflicts with a device clock. Where the server supplies a version, sequence or ETag, send it back and let the server decide. Where it supplies none, the client does not invent an ordering: send the change and accept the server's response as the result, or surface the conflict to the user.

Whydevice clocks are wrong, by seconds usually, by hours sometimes, and the user can set them to anything. The loser of a clock comparison is overwritten with no error raised anywhere, and it never reproduces in testing because every device in the room is synced to the same source. The common case is an API that offers no version at all, and the wrong response to that is to substitute updatedAt from the device and call it resolution. Not arbitrating is a valid behaviour; arbitrating badly is not.

Not whenthe clock is used for cache staleness rather than conflict, that is CACHE-1, and it is fine. Or writes are genuinely commutative, appending to a log, incrementing a counter, where there is no conflict to resolve.

Reads

READ-1mustThe store is observed, not polled. A screen that must reflect a change made elsewhere reads a stream, so one write updates every reader.

Whythis is what makes two screens agree without either knowing the other exists. A one-shot read taken at screen entry is stale the moment anything else writes.

Not whena genuinely one-shot read, an export, a migration, a background job.

READ-2mustNever enable main-thread queries. The database library refuses main-thread access by default, and that refusal is the guard rail.

Whythe flag exists to unblock a test and reaches production because nothing fails when it does. It converts a crash development would have caught into an ANR in the field, on the slowest devices with the largest datasets, the users least able to tolerate it.

Not whennever.

Why

Why the store choice is the first decision and the hardest to undo. Everything else here can be changed in an afternoon. Where a value lives cannot: the data is already there, in a format, on users' devices, and moving it is a migration with a failure mode of its own. That is why SharedPreferences persists in codebases long after everyone agrees it should not, the cost of moving is real and the cost of staying is invisible until a write fails silently.

The shape test settles it without argument. A flag is a scalar. A list you filter is a query. A photo is a file. Ask what you will do with the value in six months, not what is quickest to write today.

Why EncryptedSharedPreferences is the trap in this file. It is the most-recommended secure storage instruction in the published Android material, by a wide margin, and with good reason: for years it was the right answer. androidx.security:security-crypto was deprecated in June 2025 in favour of platform APIs and direct Keystore use, and both EncryptedSharedPreferences and EncryptedFile went with it.

Nothing about that is enforced. The library still resolves, still compiles, still encrypts. An app shipped on it today is running unmaintained cryptography, and the person who wrote it followed the advice they found. This is the clearest example in the whole skill of why a rule needs its reason attached: "use DataStore" without the deprecation is an aesthetic preference, and it loses the argument to a hundred blog posts.

Why offline-first is mostly about writes, not reads. Caching reads is easy and everyone does it. The hard half is what happens to a change the user made while they had no network. If it lives only in memory, the process dies and so does the change. If it is sent optimistically and the request fails, the UI has already said it worked. If it is retried without an identity, it is applied twice. Every one of those is silent, and every one of them is the user losing something they were told they had.

Why the delete case is worse than the write case. A missing write can be retried. A delete that does not propagate is undone the row comes back on the next sync, because from the server's point of view the client simply has less data than it does. To the user, the app ignored them and then contradicted them. That is what a tombstone is for: recording that something was deliberately absent, not merely missing.

Why device clocks cannot arbitrate. Two devices, two clocks, one of them wrong by an hour, and last-write-wins silently discards the write that was actually later. It never reproduces in testing because every device in the room is synced to the same source. A server-assigned version has one authority and no ambiguity.

What changed, if you learned this earlier.| Then | Now | |---|---| | SharedPreferences for scalars | DataStore, async, typed, observable failures | | EncryptedSharedPreferences for secrets | deprecated June 2025; platform APIs and Keystore | | fallbackToDestructiveMigration while iterating | a migration per change, and the schema committed | | a LiveData/one-shot read per screen | one observed stream, so every reader updates | | retry queue in memory | pending state in the store, ordered, visible on failure |

Pitfalls

The app updates and the user's data is gone. Destructive fallback left enabled. No crash nothing in the logs, nothing to recover.
A setting that silently reverts. apply() failed and reported nothing, or a mistyped key is returning the default.
The catalogue is empty offline after a failed refresh. The cache was cleared before the fetch or the delete-then-insert was not one transaction.
A deleted item that keeps coming back. No tombstone, so the next sync restores it.
Two devices, and the older edit wins. Conflict decided by device clock.
A save the user made on the train is gone when they get off. The write was conditional on the request, or queued in memory.
One screen shows stale data while the other is correct. A one-shot read at screen entry instead of an observed stream.
An ANR on old devices only. Main-thread queries enabled to unblock a test.
A shipped app running unmaintained cryptography. EncryptedSharedPreferences, chosen from documentation that is still, at the time of writing, the top search result.

Provenance

WRITE-1 is measured: across the eval-08 runs, Haiku wrote delete-then-insert with no transaction in 4 of 4 runs that used the pattern; Sonnet used one in 3 of 3. See evals/android/eval-08-shared/.

Eval 09 tested this skill against core alone and returned a null. That eval could not test it: the task specified the behaviour most of these rules describe, and four rules had nothing to act on. See evals/android/eval-09-db/RESULTS.md. Everything except WRITE-1 is unmeasured.

SYNC-2 was rewritten after eval 10. Its first form, "resolved by a server-assigned version never by device clocks", failed to land in every treated run: all 10 runs that produced code used the device clock, because the task's API supplied no version and the rule named no alternative. A prohibition with no actionable branch for the common case is not a rule the model can follow. It now states what to do when the server offers nothing.

STORE-3 is a reversal, see registers/android/REVERSALS.md. The deprecation is recorded against the Jetpack Security release notes, not against secondary sources.

STORE-3 shrank to a pointer after eval 20. It previously named the deprecated wrapper and deferred the alternative. Measured at the seam with four arms, security alone produced Keystore-backed encryption in 2/2 Haiku runs and db + security together produced 0/2 the extra rule count displaced the rule that mattered, on the model least able to absorb it. Sonnet was unaffected. Stating one hazard in two skills is the restatement case, and on a small model it is not merely redundant but harmful. See evals/android/eval-20-seams/RESULTS.md.

Measurement is kept out of ## Rules deliberately: a rule that names its own control-arm score tells the model it is being watched and names the rule under observation.

Added later: the shared precedence sentence: when a rule here conflicts with the code you are editing, the surrounding convention wins for style and structure, but never for a rule whose failure loses user data, leaks a credential, or ships a crash. Those get their own change.

That line exists because android/java needed it and had to discover it: eval 11 scored LEAK-2 as failing, and reading the runs showed two rules in the same file disagreeing, every treated run kept a static Context because CONVERT-1 says preserve behaviour exactly, which was correct. Two models arbitrated it without being told. The sentence writes down what they worked out, and it is reasoning rather than measurement everywhere except java.

References

Depth a rule points at, loaded only when the agent asks for it.

Offline writes

Referenced by db WRITE-2, WRITE-3, SYNC-1 and SYNC-2.

Caching reads is the easy half and everyone does it. The hard half is a change the user made with no network, and every way of getting it wrong is silent.

Local first, always

// wrong, the change exists only if the request succeeds
api.save(id)
dao.markSaved(id)

// right, the user's change is real immediately; the server catches up
dao.markSaved(id)
outbox.enqueue(Save(id))

The first version loses the change on every failed request, and the UI has usually already said it worked. The second is what "works offline" means in practice.

The outbox

A pending write belongs in the store, not in memory:

In memory	In the store
dies with the process, exactly when it was needed	survives a kill, a reboot, an update
invisible to the UI	can be shown as pending, or as failed
order lost on restart	order preserved

Three properties make it work:

Ordered. Send in the order the user made the changes. A rename followed by a delete, applied the other way round, is a resurrected row with an old name.
Identified. Give each mutation a client-generated id and have the server deduplicate on it. Without that, a retry after a response that was actually delivered applies the change twice.
Visible on permanent failure. A pending write dropped after its retries is a lost write the user believes succeeded. Leave it visible and actionable, with the reason.

Do not build this by replaying raw HTTP requests from a log. It loses ordering, has no identity, and retries requests whose meaning has since changed.

Tombstones

A delete that must propagate is not the absence of a row, it is a fact that has to be recorded:

// the row is gone locally, so the next sync sees the server has one row more
// than the client, and helpfully restores it

Keep a tombstone at least as long as a device might plausibly stay offline. That window is a product decision, a week for a note-taking app, an hour for a chat, and it is also what sets how long the server must retain deletions.

Not needed when deletes are local-only, or when the server sends authoritative full state and the client simply mirrors it.

Conflict

Two devices edit the same row while both are offline. On reconnect, something has to lose.

Never decide by comparing device clocks. Clocks are wrong, by seconds usually, by hours sometimes, and the user can set them to anything. The loser of a clock comparison is overwritten with no error raised anywhere, and it never reproduces in testing because every device in the room is synced to the same source.

Where the server supplies a version, sequence or ETag send back the one you last saw. The server then knows whether you are writing against current state and can accept, reject, or merge under a documented rule. One authority, no ambiguity.

Where the server supplies nothing which is the common case, do not fill the gap with updatedAt from the device. That is not conflict resolution; it is a coin flip weighted by whichever device has the faster clock. Two options remain, and both are honest:

Let the server be the outcome. Send the change, accept whatever comes back as the new truth and re-render. Last-writer-wins decided by arrival order at one machine is at least one clock.
Surface it. Keep both versions and ask. Expensive in UI, correct in data, and the right answer when the content is something the user would hate to lose silently.

Not arbitrating is a valid behaviour. Arbitrating badly is not.

Using the device clock for cache staleness is a different thing and is fine, being wrong about whether data is an hour old costs a refetch, not a lost edit.

Choosing where a value lives

Referenced by db STORE-1 and STORE-3.

The shape test

The value is	It lives in	Because
a scalar or flag, theme, onboarding-seen, last sync time	`DataStore`	typed, async, observable
queried, filtered, sorted, or related to other rows	the database	that is what a query engine is for
large and opaque, an image, a document, an export	the filesystem, path in the database	rows are not blob stores
a secret, token, key, credential	not decided here see `android/security`	the store is not the protection

Ask what you will do with the value in six months, not what is fastest to write now. A flag that becomes a filter becomes a query, and moving it later is a migration.

`DataStore` over `SharedPreferences`

SharedPreferences is a synchronous API in front of an XML file:

the first read blocks the calling thread while the file loads;
apply() is asynchronous and reports nothing a failed write is silent, and the value you read back is the one in memory, so the failure surfaces after the next process start;
commit() blocks to give you a result;
there is no type safety a wrong-typed read throws at runtime, and a mistyped key returns the default, which looks exactly like a value someone chose.

DataStore addresses all four: it is coroutine-based, exposes reads as a Flow, surfaces write failures to the caller, and, with Proto DataStore, has a schema.

Do not migrate an existing widely-used preference just to comply. Migration is a data-loss risk in exchange for tidiness. Wrap it behind the class STORE-2 asks for, and let new values go to DataStore.

The one class that owns the keys

// not this, a string key at four call sites is four chances to mistype it
prefs.getBoolean("has_onboarded", false)

// this
class AppSettings(private val store: DataStore<Preferences>) {
    private val HAS_ONBOARDED = booleanPreferencesKey("has_onboarded")
    val hasOnboarded: Flow<Boolean> = store.data.map { it[HAS_ONBOARDED] ?: false }
    suspend fun setOnboarded() = store.edit { it[HAS_ONBOARDED] = true }
}

A mistyped key does not fail. It returns the default, and the default is indistinguishable from a real value, so the bug presents as "the setting reset itself".

Secrets: what changed

androidx.security:security-crypto, the library providing EncryptedSharedPreferences and EncryptedFile, was deprecated at 1.1.0-beta01 on 4 June 2025. The release note:

Deprecated all APIs in favour of existing platform APIs and direct use of Android Keystore.

This matters more than a normal deprecation for three reasons:

It is still the top answer everywhere. Years of documentation, courses and answers recommend it, and most have not been updated. Anything trained on that material recommends it confidently.
Nothing fails. It resolves, compiles, and encrypts. An app shipped on it is running unmaintained cryptography with no signal that anything is wrong.
A community fork exists and is explicitly not produced, endorsed or supported by Google, which is a different risk, not a solution to this one.

What replaces it is android/security's subject. What belongs here is only this: do not let the store choice be made by reaching for the deprecated wrapper first.

Source: Security | Jetpack | Android Developers

Evidence

Persistence: what is stored, what survives, what is stale, and what happens to local work that has not reached the server.

What was run

5 evals, 94 recorded runs, on Haiku 4.5 and Sonnet 5. Every run is archived: the generated sources, the prompt each arm received, and the model each one reported.

One of them is partial: it was stopped part-way and is marked as such below.

What loading the skill changed

A write spanning more than one statement is one transaction. Unaided Haiku runs wrote delete-then-insert with no transaction every time the pattern came up; Sonnet used one.

Where a secret goes. This skill points at android/security rather than answering it, after a measurement showed that carrying the same hazard in two skills made the smaller model worse rather than better.

What the tested models already handle

In a later eval, adding a column to a database documented as already shipped produced a version bump and a migration in every run including controls, and nobody enabled destructive fallback. Writing an edit locally first, marking it pending, and observing the store rather than polling were handled in nearly every run. The seed shipped a refresh that cleared the table before fetching, which empties the app if the fetch fails, and every arm replaced it.

That eval was stopped part-way, so its remaining findings are inconclusive.

What one task could not show

One eval returned nothing, because its task specified the behaviour most of these rules describe: several rules had nothing to act on and the rest were told what to do by the prompt. Recorded so the result is not read as a measurement of the skill.

A rule rewritten after measurement

A conflict rule first read "resolved by a server-assigned version, never by device clocks". It did not land in any run: the task's API supplied no version and the rule named no alternative, so every run used the device clock. A prohibition with no actionable branch for the common case is not something a model can follow. It now states what to do when the server offers nothing.

Around this skill

Loads when a project has

gradle dependency

androidx.room:room-runtime
androidx.datastore:datastore
androidx.datastore:datastore-preferences
app.cash.sqldelight

file

**/schemas/*.json

Needs

android/core

Needed by

Nothing yet.

Composes with

Any skill a project matches, on any axis. 12 more in android.

Source