Skip to content

maxrave-dev/kotlin-footguns / skills/data-layer-footguns

data-layer-footguns

Verified

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.

FileKindSize
SKILL.mdskill md16.6 KB
references/bucket-local-time-in-code-not-in-sql.mdmarkdown6.7 KB
references/bulk-json-import-progress.mdmarkdown7.2 KB
references/cache-then-network.mdmarkdown6.2 KB
references/cascading-delete-ordering.mdmarkdown6.5 KB
references/clean-satellite-at-transition.mdmarkdown4.9 KB
references/continuation-token-pagination-contract.mdmarkdown7.2 KB
references/datastore-kmp-manager.mdmarkdown7.7 KB
references/encoded-continuation-tokens-local-sort.mdmarkdown8.4 KB
references/first-ever-not-first-in-window.mdmarkdown6.7 KB
references/generic-paged-db-accumulator.mdmarkdown6.3 KB
references/import-format-contract-design.mdmarkdown8.5 KB
references/inclusive-period-boundaries-and-offset-reset.mdmarkdown7.5 KB
references/like-wildcard-escaping-ids.mdmarkdown5.4 KB
references/local-listening-analytics.mdmarkdown8.1 KB
references/mirror-local-state-to-a-remote-account.mdmarkdown8.1 KB
references/order-preserving-section-mapping.mdmarkdown5.5 KB
references/remote-index-cached-as-rows-with-validator.mdmarkdown7.3 KB
references/repository-flow-conventions.mdmarkdown7.1 KB
references/room-kmp-setup.mdmarkdown7.6 KB
references/room-migrations-at-scale.mdmarkdown8.8 KB
references/room-rawquery-readonly-vacuum.mdmarkdown5.0 KB
references/search-over-a-paged-list-queries-the-source.mdmarkdown7.4 KB
references/sql-not-in-nullable-trap.mdmarkdown4.9 KB
references/stored-timestamp-is-a-local-wall-clock.mdmarkdown8.0 KB
references/ttl-keyed-json-cache-lenient-decode.mdmarkdown6.4 KB
references/unbounded-for-shares-capped-for-lists.mdmarkdown6.6 KB

references/clean-satellite-at-transition.md

View on GitHub
---
name: clean-satellite-at-transition
description: 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.
---

# Clean up satellites at the transition

Some rows exist only because of a state on another row: notifications for a followed creator, a
new-release tracking row for a subscription, draft rows for an open editor. When that state ends,
those rows have no reason to exist — and nothing in the schema knows that.

Do the delete where the state changes:

```kotlin
// adapted
override suspend fun updateFollowedStatus(artistId: String, followedStatus: Int) {
    localDataSource.updateFollowed(followedStatus, artistId)
    if (followedStatus == 0) {
        localDataSource.deleteNotificationsByArtistId(artistId)
        localDataSource.deleteReleaseTrackingRow(artistId)
    }
}
```

Before that `if`, unfollowing flipped a flag and nothing else. Every satellite row of every artist
ever unfollowed survived, forever, with no code path anywhere that would remove it.

## Traps

**A flag flip is not a delete, so no cascade fires.** `ON DELETE CASCADE` triggers on a deleted
parent row. Here the parent row is still there — only its state changed — so the engine has no
mechanism to offer and there is nothing to configure. If a state can be turned off, the cleanup for
it is application code you have to write.

**A bulk sweep keyed on "parent row missing" misses the parents that survive.** A cache sweep does
not delete every parent that left the state — it spares any that still backs other kept data — and
a satellite rule shaped as "delete rows whose parent is gone" never reaches the satellites of those
spared, still-unfollowed parents. Key the sweep on the **state**, not on the row's existence, and
run it *after* the parent sweep so deleted and spared parents are both covered:

```sql
DELETE FROM notification
 WHERE artistId NOT IN (SELECT artistId FROM artist WHERE followed = 1);
-- artistId is declared NOT NULL here; over a nullable column this needs IS NOT NULL
-- (that failure has a sibling skill: sql-not-in-nullable-trap)
```

Among the parents the sweep spared, the unfollowed ones now lose their satellites and the followed
ones keep theirs — which is the wanted answer, and only works because the predicate asks about the
follow rather than about the row.

**Shipping the transition cleanup does not repair existing databases.** Every user who unfollowed
before the fix still carries the rows. Keep the bulk sweep permanently as the migration path for
those, and say so in a comment — otherwise the sweep looks redundant once the transition cleanup
exists and someone deletes it.

**Branch on the new value, not on "the value changed".** `if (followedStatus == 0)` runs the cleanup
only when the state actually ends. A branch on "changed" also fires on the follow direction, and one
on "was previously 1" needs a read you would otherwise not do.

**Put it behind the state change, not behind the button.** If the cleanup lives in a screen's click
handler, every other writer of that column — a settings toggle, a sync pass, an undo, a bulk action —
silently skips it. It belongs in the single repository method that owns the transition, and every
caller has to route through that method.

**Do not extend it into deletes the user can undo.** Only rows that are genuinely worthless after the
transition qualify. Anything the user could reasonably expect back on re-following (their own notes,
a read/unread marker they set) is data, not a satellite — leave it, or the cleanup becomes a
data-loss bug that only shows up after a round trip.

## Verifying it

- **Count the stranded rows.** Run the sweep's predicate as a `SELECT COUNT(*)` on a database that
  predates the fix. On a database that has only ever run the fixed build it should be zero after any
  unfollow; on an older one it is positive and never decreases — that difference is the proof the
  transition cleanup is doing something.
- **Find every writer of the state column before believing the coverage.** Search for assignments to
  the column and for calls to the update method, and confirm each path reaches the cleanup. This is
  the check that catches the click-handler placement above.
- **Round-trip it by hand.** Turn the state off, inspect the satellite tables, turn it back on, and
  inspect again. Both halves matter: the first proves the delete runs, the second proves you did not
  delete something the user expected to survive.
- **Watch the table's row count over a long session**, not just across one action. An unbounded table
  is the symptom that started this, and the only observable one before the fix.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall