| 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:
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:
DELETE FROM notification
WHERE artistId NOT IN (SELECT artistId FROM artist WHERE followed = 1);
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.
