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 minute ago, or when a brief network failure blanks a screen that had perfectly good content on it.
• ttl-keyed-json-cache-lenient-decode — A small keyed cache for values that drift — each entry carries the moment it was fetched and answers an isStale check against a time-to-live constant, the whole map is stored as one JSON string in key-value preferences, and decoding is lenient plus wrapped so a schema change degrades to a cache miss instead of destroying every entry. Use when caching resolved covers, lookups or per-key results without a database table, or when a cache stopped working entirely after a model field was added.
• generic-paged-db-accumulator — One reusable helper that reads an entire table through a (limit, offset) data-access function in a bounded loop, stopping on the first short page — plus when reading everything is legitimate (export, backup, bulk mapping) and the smell that means you needed a real query instead. Use when several repositories each hand-roll the same paging loop, or when a "read all" call gets slower than linearly as the table grows.
• continuation-token-pagination-contract — Model an endpoint that returns a page plus a next-token as Flow<Resource<Pair<items, token?>>> — a null token means the end, the caller stores only the token, and a bounded prefetch primes the first pages before anything is shown. Use when wiring an opaque-cursor API into a repository, or when a list stops loading after one failed request and never recovers, or when paging fires twice for one trigger.
• encoded-continuation-tokens-local-sort — Carry locally sorted and shuffled paging through the same token slot a remote API uses, by prefixing the token with a mode tag and encoding a cursor after it — and reject an unrecognised prefix loudly, because a silently ignored token leaves the pager stuck in its in-flight state and the list never loads again. Use when one list must page from either a server cursor or a local ordering, or when local sorting made paging stop working with nothing in the log.
• order-preserving-section-mapping — Map a sectioned API response as a list of (title, items) in the order it arrived, never by reading result[0] and result[1] into named fields — a signed-in account is recorded here as getting an extra section pushed in front, and any such shift mislabels every section after it and drops the last one with no error. Assume the set and order may also vary by locale or region, and capture two responses to find out. Use when modelling a home feed or browse screen made of shelves, or when a screen shows the right content under the wrong headings for some users only.
• remote-index-cached-as-rows-with-validator — Cache a large published index by parsing it into indexed rows once and re-checking it with the server's own ETag, so a routine freshness check costs a couple of hundred bytes — replaying the stored validator only while rows exist, treating a 200 that parses to nothing as a failure, and never letting a failed check refresh the timestamp. Use when a browsable catalogue is fetched from a static host, or when a cached index went empty and never refilled.
• search-over-a-paged-list-queries-the-source — Filtering a paging stream only ever searches the pages already loaded, so whether an item is findable depends on how far the user happened to scroll — the search must query the store and render as a sibling overlay, leaving the paged reader and its drag-reorder, in-place removal and scroll position untouched. Covers the debounce and minimum-length gate, why the escaping belongs one layer above the query, and the one case where filtering in memory is correct. Use when search misses items that are definitely there, when results change after scrolling, or before threading a second data source through a paged list.
• mirror-local-state-to-a-remote-account — An opt-in switch that mirrors a local flag onto a signed-in remote account — turning it on back-fills everything already flagged, turning it off stops mirroring and deliberately does not undo, and the per-item call is three-valued so "not attempted" is distinguishable from "failed". Covers writing locally first and unconditionally, which of the two paths is allowed to speak to the user, and why the back-fill is sequential. Use when a mirrored flag silently disagrees with the account, when the user cannot tell a failure from a no-op, or before wiring a settings switch to a remote write.