POST /api/sessions with a soft-deleted id writes an invisible row, then 500s

[QuentinBisson]

📋 Prerequisites

• I have searched the existing issues to avoid creating a duplicate
• By submitting this issue, you agree to follow our Code of Conduct

Describe the bug

SessionService.CreateSession with the id of a soft-deleted session writes a row that no read path can see, damages the tombstone on its way, and then fails with an internal error. The write is not rolled back, and every retry behaves the same.

• SoftDeleteSession sets deleted_at on the session row, which keeps its (id, user_id) primary key.
• UpsertSession upserts with ON CONFLICT (id, user_id) DO UPDATE SET name, agent_id, source, updated_at, so deleted_at stays set and the tombstone's name, agent_id and source are overwritten with the new request's values.
• Every read (GetSession, ListSessions, ...) filters deleted_at IS NULL.

So the create path's own reload cannot see the row it just wrote, and the RPC fails codes.Internal "Failed to load created session". The caller is told the request failed, while a write did happen, to the row an audit of the deleted session would read. A request should either create a visible session or change nothing.

Proposed fix

Reject the create with codes.AlreadyExists when the (id, user_id) belongs to a soft-deleted session, and do not write. Excluding soft-deleted rows from the ON CONFLICT update is enough and keeps it atomic; a pre-check in the service leaves a window between the read and the write. The tombstone, its events and its tasks are left exactly as they are.

This is deliberately not a proposal to reuse the id: see #2331, where that was rejected, correctly. A distinct code also lets a caller tell "this id is retired, use another" from "kagent is unhealthy, retry", which an internal error does not.

Steps to reproduce

1. CreateSession with an explicit id.
2. DeleteSession with that id.
3. CreateSession with the same id again: codes.Internal "Failed to load created session", with a row written that no read can see and a tombstone whose name is now the second request's. Every retry behaves the same.

Reproducible directly against the store: StoreSession, DeleteSession, StoreSession returns no error, the following GetSession errors, and SELECT name FROM session shows the second value.

Observed on

kagent 0.9.9; code paths verified identical on v0.10.0-beta7 and reproduced against main at 30e056d.

[QuentinBisson]

Related: #2207 fixes the secondary issue here (it cascades the soft-delete to task/event rows, so a recreated session would no longer inherit the previous incarnation's history) and allows a freed ID to be reused by a different user via a partial unique index on live sessions.

It does not fix the headline bug: the ON CONFLICT (id, user_id) upsert is unchanged, so the same user recreating a deleted ID still hits the tombstoned row, deleted_at is never cleared, and create fails. With #2207's cascade in place, the remaining fix here reduces to clearing deleted_at on re-upsert.

[muskiteer]

I would like to take this one on if no one is working on it

[QuentinBisson]

Apologies @muskiteer, this sat unanswered far too long. Nobody is working on it.

One thing to flag: I have just rewritten the description, and the fix is no longer the one my comment above describes. Clearing deleted_at on re-upsert would make a deleted session id reusable, and that was rejected in #2331, both because it defeats the auditing purpose of the soft delete and because reusable session ids are a cross-user takeover surface that the ownership bound in tasks.sql was written to contain.

What is left is narrower: the create still writes a row that no read path can see and then 500s, on every retry, which is wrong under any retention policy. The fix is to return 409 and not write.

#2207 is unaffected either way, since it cascades the soft-delete without touching the upsert.

To be straight about what happens next: I am implementing this now rather than leaving it open any longer. If you would rather take it, say so and it is yours, and either way your review would be welcome.