DatabaseSessionService.list_sessions() returns unordered results — sessions[-1] is unreliable for "latest session" logic

[pr-maia]

Describe the Bug:

DatabaseSessionService.list_sessions() executes a SELECT without an ORDER BY clause, so the returned list of sessions has no guaranteed ordering. Any consumer code that relies on positional indexing (e.g. sessions[-1] for "most recent") will intermittently pick up the wrong session depending on PostgreSQL's internal heap ordering.

In our case, this caused session fragmentation: a user with multiple sessions across weeks would randomly get an old session as sessions[-1], fail a "is this session current?" check, and create a new orphan session — losing all conversation history.

Steps to Reproduce:

1. Create a DatabaseSessionService backed by PostgreSQL (asyncpg)
2. Create 3+ sessions for the same (app_name, user_id) over different days
3. Call list_sessions(app_name=..., user_id=...) multiple times
4. Observe that response.sessions[-1] is not consistently the most recently created or most recently updated session

Expected Behavior:

list_sessions() should return sessions in a deterministic, documented order — ideally by create_time ASC (matching the intuition that sessions[-1] is the newest). Alternatively, the docstring should explicitly state that ordering is undefined.

Observed Behavior:

Results come back in PostgreSQL's arbitrary heap order. With 8 sessions for one user, sessions[-1] returned a 3-day-old orphan session instead of the actively-used one. This caused every subsequent message to create a new session, breaking conversation continuity.

The relevant code in database_session_service.py (v2.3.0, line ~583):

stmt = select(schema.StorageSession).filter(
schema.StorageSession.app_name == app_name
)
if user_id is not None:
stmt = stmt.filter(schema.StorageSession.user_id == user_id)

result = await sql_session.execute(stmt)
results = result.scalars().all()

No .order_by() is applied.

Environment Details:

• ADK Library Version: 2.3.0
• Desktop OS: Linux (Docker, python:3.11-slim on Debian Bookworm)
• Python Version: 3.11

Model Information:

• Are you using LiteLLM: No
• Which model is being used: gemini-3.5-flash

---

Suggested Fix:

Add .order_by(schema.StorageSession.create_time.asc()) to the query in list_sessions():

stmt = select(schema.StorageSession).filter(
schema.StorageSession.app_name == app_name
)
if user_id is not None:
stmt = stmt.filter(schema.StorageSession.user_id == user_id)
stmt = stmt.order_by(schema.StorageSession.create_time.asc())

Alternatively, ordering by update_time may be more useful for consumers that want the most recently active session.

Workaround:

Iterate over all returned sessions instead of relying on sessions[-1]:

existing = await session_service.list_sessions(app_name=APP_NAME, user_id=user_id)
if existing.sessions:
latest = max(existing.sessions, key=lambda s: s.last_update_time)

How often has this issue occurred?:

• Often (50%+) — depends on the number of sessions per user and PostgreSQL's heap layout. With 5+ sessions it reproduces reliably; with 1-2 sessions it's masked.

[godququ5-code]

Hi! I verified that DatabaseSessionService.list_sessions currently executes the filtered select without an ORDER BY, and the existing session-service test compares sessions without asserting order. I can add a deterministic ordering clause and a regression test using the existing in-memory SQLite setup. Before implementing, could a maintainer confirm the intended contract: create_time ascending, update_time ordering, or another key? I would also use id as a tie-breaker if deterministic ordering for equal timestamps is desired. Is anyone already working on this?