[scd] push down implicit subscription cleanup to Database, if possible

[Shastick]

Is your feature request related to a problem? Please describe. In general, we want to minimize locking time when interacting with Cockroach DB.

When removing and mutating Operational Intent References (OIR), we may do several round-trips to the database.

It is likely that these could be reduced (by eg moving some logic to the SQL and have it executed at the database level)

Describe the solution you'd like The logic that has been factored into a separate method in #1057 could possibly be directly integrated into some CRDB queries, instead of the current back-and forth between the database and the backend.

Describe alternatives you've considered An option is to keep things as-is: this is not urgent but is worth keeping track off.

Whether or not it is worth doing this right away depends on how easy we can push down the cleanup: I'll spend a little bit of time investigating this. If anyone things it is not worth the effort at the moment, please let me know.

Additional context The opportunity for pushing down the cleanup logic became apparent while investigating some dangling implicit subscription issue for the uss_qualifier that led to #1057

Note that with #1057, creation or update of any OIR with implicit subscriptions requires at least an additional roundtrip to the database, so the present issue feels worth adressing.

Tasks

• [x] deletion: #1107
• [x] upsert: #1064
• [ ] update CI qualifier config to cover these with the implicit cleanup scenario

[Shastick]

While we may not be able to do the removal within an existing query, we might be able able to surface the fact that the subscription attached to an OIR can be removed via an existing query.

• DeleteOperationalIntent could be modified to do the deletion directly at the database level, or at least to return a boolean indicating whether or not the removal can occur. The implementation is simple enough and a direct deletion seems possible.

• Similarly UpsertOperationalIntent can likely be modified to at least return whether or not the cleanup can occur, and could possibly handle the deletion directly.

  Both cases seem possible, although I need to check if CRDB's SQL dialect would allow us to do so as I'm not entirely familiar with it.