The three credential stores stage writes for their routes and re-encrypt without a version check

[dpoulopoulos]

provider_store_service, search_tool_store_service and guardrail_store_service share one shape, and two review findings on #1120 are really findings about that shape. Both apply to all three equally, which is why neither belongs in a PR that touches only the newest one.

Transaction ownership

src/gateway/AGENTS.md says "Services own commits and rollbacks." These three do not: save_* and delete_* stage mutations and say so in their docstrings ("staged; caller commits"), leaving the route to call db.commit() and to map an IntegrityError to a 409.

There is a reason it ended up this way. The route needs the commit to fail in its own hands so it can turn a primary-key collision into a 409 with a resource-specific message, and it needs the write and the cache refresh that follows to be ordered. But the convention and the code disagree, and right now the code wins three times over. Either the services should own their transactions and hand back something a route can turn into a 409, or the convention should record this as the deliberate exception it has become.

Re-encryption without a version check

reencrypt_* reads every row holding a secret, decrypts, re-encrypts and lets the ORM flush. Nothing pins the write to the ciphertext that was read. A PATCH committing between the read and the flush is overwritten with a re-encryption of the value it replaced.

The window is small and the operation is rare, run by hand during a key rotation. It is also not guarded at all today: with_for_update() is not used on this path, and SQLite would not emit FOR UPDATE if it were. A conditional update matching the original ciphertext, treating zero rows as "someone else got there first", would close it.

Why this was deferred

Raised in review on #1120: #1120 (comment) and #1120 (comment)

Changing one store to answer them would leave three stores with two shapes, which is worse than three stores with one flawed shape: the next person copies whichever they find first. Both points want a single change across all three.

[dpoulopoulos]

Two updates from the review of #1211, which adds a fourth store.

There is now a fourth, and it is only half in scope. services/guardrail_credential_service.py joins provider_store_service and search_tool_store_service. (The guardrail_store_service named above was the projected name for it; the file landed as guardrail_credential_service.py.) It does not share the transaction-ownership flaw: it owns its own commits and turns an IntegrityError into a 409 itself, which is the shape the first half of this issue argues the other two should move to. So there is now a worked example of the target shape in the tree.

It does share the re-encryption flaw, unchanged: reencrypt_guardrail_credentials reads, decrypts, re-encrypts and commits with nothing pinning the write to the ciphertext it read.

A second dimension on the same pass: the read is unbounded. Surfaced on #1211 (comment). list_encrypted_guardrail_credentials materializes every encrypted row into one Python list, and the provider and search-tool equivalents do the same. .github/instructions/performance-review.instructions.md asks for batched lookups and against loading large result sets into memory. Today the tables are operator-authored and small, so this is about what nothing prevents rather than what hurts now.

It belongs here rather than in its own issue because it wants the same rewrite: the conditional update this issue already asks for has to happen per row, and doing that over a keyset batch is the same loop. Splitting them would mean touching the same function in all three stores twice.

So the scope of this half is now: read in bounded keyset batches, and make each write conditional on the ciphertext that was read, treating zero rows affected as "someone else got there first".

Related: #1212 bounds the ordinary list reads of the provider and search-tool stores, the same guideline on the non-rotation path. #1211 bounded the guardrail one in place.