/data-layer-footguns

name: data-layer-footguns
description: "Room/SQLite, DataStore and repository traps: migrations, NOT IN with NULLs, LIKE escaping, delete order, local-time bucketing, imports, caching, paging. Use before writing a query or migration, or when data comes back wrong."

Data-layer footguns: Room, SQL and repositories

26 traps mined from a production Kotlin and Compose Multiplatform app, one short file each under `references/`. Every file gives the working pattern, the **Traps** (the specific ways it fails in practice, and why), and a **Verifying it** section with commands to run against the user's own tree.

**How to use this index.** Match the code about to be written, or the symptom being chased, against the entries below: each names its topic and the symptom that should send you to it. Read every file that plausibly applies with the Read tool before proposing code or a fix; each is under 150 lines. Where a Verifying command names the source project's paths, substitute the equivalent paths in the user's tree.

**Cross-references.** A backticked trap name inside a file that is not listed here lives in a sibling area skill: glob `../*/references/<name>.md` to open it.

Databases and SQL

• [`cascading-delete-ordering`](references/cascading-delete-ordering.md) — Sweeping a local cache database down to what the user actually owns — ordering container deletes before leaf deletes, telling "kept by state" columns apart from genuine garbage, re-checking conditions inside the DELETE, and pinning the record currently in use. Reach for it when a "clear cache" or "clear history" pass completes without error and frees nothing, when it instead wipes the user's favourites or downloads, or when the item playing on screen vanishes mid-sweep.
• [`sql-not-in-nullable-trap`](references/sql-not-in-nullable-trap.md) — Why `x NOT IN (subquery)` matches zero rows and still reports success whenever a NULL is actually present in the subquery result — a standing risk for any nullable column — how to guard every such subquery, and how to make a silently-inert statement detectable instead of invisible. Reach for it when a DELETE or SELECT with a NOT IN filter returns nothing on data you can see with your own eyes, when a cleanup pass "succeeds" and frees nothing, or before writing any NOT IN over a nullable column.
• [`like-wildcard-escaping-ids`](references/like-wildcard-escaping-ids.md) — Matching a machine-generated id inside a text or JSON column with LIKE — why `_` and `%` in the id silently widen the match, how to escape them with nested replace() plus an explicit ESCAPE character, and why the id must be matched as a quoted token rather than as a bare substring. Reach for it when a cleanup spares rows it should have deleted, when a lookup returns a row belonging to a different id, or before putting any id into a LIKE pattern.
• [`room-rawquery-readonly-vacuum`](references/room-rawquery-readonly-vacuum.md) — Why a raw-query DAO method runs on a read-only connection, so database compaction fails there with "attempt to write a readonly database" while a checkpoint on the same connection succeeds and hides the problem. Reach for it when a bulk delete frees no disk space, or when a raw statement reports a readonly database you clearly opened for writing.
• [`clean-satellite-at-transition`](references/clean-satellite-at-transition.md) — Deleting the dependent rows that exist only to serve a state at the exact transition that ends that state — rather than leaving them to a later bulk sweep — and why a foreign key cannot do this for you when only a flag changes. Reach for it when tables grow without bound and nothing ever deletes from them, when rows survive whose parent no longer justifies them, or when a bulk cleanup has to reason about rows whose parent has already been swept away.
• [`room-kmp-setup`](references/room-kmp-setup.md) — Set up one Room database shared across Android, JVM/desktop and iOS with an expect/actual builder per platform, a bundled SQLite driver chosen once at the injection site, and a per-architecture audit of the driver artifact. Use when adding Room to a Kotlin Multiplatform module, when one target fails at the first database connection while the others work, or when a target compiles but its generated database implementation is missing.
• [`room-migrations-at-scale`](references/room-migrations-at-scale.md) — Keep a Room database upgradable after twenty-plus schema versions — declaring the whole graph of (from,to) edges instead of assuming users only ever hop one version, filling the gaps a generated migration cannot express, and recreating triggers idempotently in the on-open callback. Use when an upgrade from an old build reports no migration path, when a trigger exists on upgraded databases but not on fresh installs (or the reverse), or before shipping a schema change to a long-lived app.
• [`datastore-kmp-manager`](references/datastore-kmp-manager.md) — Build a multiplatform preferences manager — the store instance produced per platform from nothing but a file path, one observing flow plus one suspend setter per key, and the interface declared in the domain layer so feature code never imports the storage library. Use when adding shared settings to a Kotlin Multiplatform app, when a setting reads back as its default after an upgrade, or when a settings screen shows a stale value until it is reopened.
• [`local-listening-analytics`](references/local-listening-analytics.md) — Build per-user listening or usage analytics entirely on-device — an append-only event table plus a denormalized per-contributor table that carries a copy of the timestamp, two completion thresholds instead of one, bare ids in events enriched to titles and artwork only at read time, and every window query parameterised as (start, end) so "last N days" stays an argument. Use when adding a "your year in review" or top-items screen without a backend, when a time-window chart is slow, when a chart is mysterious