Session ID cannot be recreated after delete: upsert never clears deleted_at, and old events survive

[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

A session ID can never be recreated after the session was deleted, because the upsert never clears deleted_at:

• DeleteSession soft-deletes: SoftDeleteSession sets deleted_at on the session row and leaves the session's event and task rows in place.
• StoreSession upserts with ON CONFLICT (id, user_id) DO UPDATE SET name, agent_id, source, updated_at, so deleted_at stays set.
• Every read (GetSession, ListSessions, ...) filters deleted_at IS NULL.

POST /api/sessions with a previously deleted ID therefore upserts an invisible row: the handler's own reload after StoreSession can't see it and the request fails with 500 "Failed to load created session". Every retry fails the same way.

This bites any client that derives session IDs deterministically (e.g. a chat gateway keying the kagent session on an external conversation/thread ID): once such a session is deleted, via the UI, the REST API, or any cleanup, that conversation can never get a session again. The kagent UI never reuses IDs (a2a.NewContextID), which is presumably why this hasn't surfaced.

Secondary issue, same root: because delete leaves event/task rows keyed by session_id (each filtered only by its own deleted_at), a fix that merely clears the session's deleted_at on re-create would make the recreated session silently inherit the previous incarnation's full event history.

Proposed fix

On create/upsert of a soft-deleted (id, user_id): clear deleted_at AND purge (or re-key) the previous incarnation's event/task rows, so the recreated session starts empty. Alternatively, DeleteSession could hard-delete the session with its dependents, though that interacts with session sharing (session_shares references sessions), so purging on re-create is probably the smaller change.

Steps to reproduce

1. POST /api/sessions with an explicit id → 201.
2. DELETE /api/sessions/{id} → 200.
3. POST /api/sessions with the same id → 500 "Failed to load created session", and every A2A turn using that contextId fails to prepare a session.

Observed on

kagent 0.9.9; code paths verified identical on v0.10.0-beta7 and current main (b2b86c9).

[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