Clean Satellite At Transition Agent Skill

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:

// 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:

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.