Todo 30: SQLite Session Store

Status

done

Goal

Persist debugging-console data in SQLite and expose read APIs for session list, chat messages, audit replay, and usage summaries.

Scope

Add a small synchronous SQLite store under infrastructure/.
Keep the store independent from runtime orchestration so it can be tested directly.
Add FastAPI endpoints for creating/listing sessions and reading messages, audit logs, and usage.
Add AGENT_LAB_DATABASE_PATH via Settings.database_path.
Ignore local SQLite database files in git.

Data Model

sessions: id, title, timestamps, full workspace/agent config JSON.
turns: session id, turn index, user message, timestamps.
messages: ordered chat transcript rows.
audit_logs: ordered audit rows with event name and JSON details.
usage_stats: one row per model call; REST response also returns turn and session aggregates.

Verification

Direct store tests cover create/list/detail/messages/audit/usage, including a regression for usage totals not being multiplied by turn joins.
API tests cover /api/sessions and session detail/replay endpoints with empty and non-empty data.
uv run pytest tests/test_sqlite_store.py tests/test_websocket_api.py -q passes: 36 tests.
uv run pytest passes: 66 tests.