# 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.