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 — 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 — 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 — 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 — 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 — 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 — 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 — 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 — 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 — 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 mysteriously shorter than the row count says it should be, or when a per-contributor total refuses to add up to the period's own figure.
• import-format-contract-design — Specify a user-facing exchange file — why a version field can be worse than none, rejecting a file whose parse yields nothing, stating the same-length rule that positionally-aligned arrays imply, naming the legacy values a producer must never emit, and, when the producer is someone else's published format, placing values by the key that means something instead of by position. Use when designing an import/export or backup format, when writing a parser for a published one, or when a user reports importing a file and getting nothing with no error.
• bulk-json-import-progress — Import thousands of rows from a user-supplied file without the UI going dark or the batch dying halfway — chunk the writes and emit progress per chunk, reject a parse that yields nothing before touching the database, and filter incoming rows down to those whose referenced parents exist. Use when building an import/restore feature, when an import of a large file appears frozen, or when a single bad row aborts a whole import.
• stored-timestamp-is-a-local-wall-clock — A timestamp column written through an ORM type converter can hold the local wall clock encoded as if it were UTC — an exact round trip that is only correct through the converter, and the converter is chosen by the field's TARGET TYPE, so declaring a projection field as a raw number silently opts out and applies the offset a second time. Covers why every total still adds up, why the error is exactly zero on some machines, and why the fix is asking for the type the converter understands rather than picking a time zone. Use when an hour-of-day or day-of-week breakdown is shifted by your own offset while every count and sum is right, when a chart says people are most active at 3am, or before typing a stored time column as a number in a hand-written projection.
• bucket-local-time-in-code-not-in-sql — Group events by local hour or local day in application code from one raw scan, not in SQL — the engine's local-time modifier answers from the process time zone rather than the user's, and four local-time aggregates mean four scans that can disagree with each other. Covers where the line sits between an aggregate that belongs in SQL and one that does not, and what the single scan has to return to stay correct. Use when an hour-of-day or weekday chart differs between platforms or between a device and a desktop build, when adding a fourth "group by day" query, or before writing a date function into a query string.
• unbounded-for-shares-capped-for-lists — A top-N query is right for a list and wrong for a share-of-the-whole — the cut tail shrinks the denominator and the entropy normaliser, inflating concentration and diversity alike — so the same grouped data needs two queries with different bounds. Covers why the truncation is invisible in the result, why the unbounded query's ORDER BY becomes load-bearing, and when unbounded is actually safe. Use when a "top 5 share" or diversity score reads implausibly high, when one grouped query is feeding both a leaderboard and a statistic, or before reusing a capped DAO method for anything that divides by a total.
• first-ever-not-first-in-window — Counting entities encountered for the first time means taking MIN over the entity's whole history and asking whether that minimum lands inside the window — the natural version, which filters to the window and then groups, calls every entity new. Covers why the wrong query passes its first test, why the window belongs in HAVING rather than WHERE, what an unbounded scan needs from the index, and how a retention prune quietly redefines "ever". Use when a "new this period" figure tracks the distinct count exactly, when discovery rate is implausibly close to 1, or before writing any first-seen query.
• inclusive-period-boundaries-and-offset-reset — Pick one boundary convention for a stepping period navigator and hold it everywhere — a closed upper bound at 23:59:59 drops the last second's sub-second remainder, and half-open bounds handed to an inclusive BETWEEN double-count the shared instant — then reset the step offset whenever the granularity changes, because N periods back at one length is not N periods back at another. Covers clamping at the present, deriving the forward affordance from the same value, and why the current period's totals are not comparable to the previous one's. Use when a period navigator lands on the wrong span after switching granularity, when a boundary event is missing or counted twice, or when a first-of-the-month comparison reads catastrophically low.

Repositories, caching and paging

• repository-flow-conventions — One table of method shapes for a repository sitting over a local database plus a remote API — local reads as a cold flow moved onto the IO dispatcher, remote reads as a flow of a success/error envelope, writes as withContext. Use when adding methods to a repository, when reviewing one whose shapes have drifted apart, or when a screen sits on its loading state forever with nothing in the log.
• cache-then-network — Serve the stored copy immediately, then the fresh one, from a single repository flow — and emit an error only when nothing was served, because an error after a successful emission replaces working content the user is already reading. Use when a screen shows a spinner on every open despite having shown the same data a minu