ha-logs

# Hope Agent Logs — Self-Service Diagnostics

Hope Agent persists every log line, every session message, and every async tool job into local SQLite databases under `~/.hope-agent/`. You can query these directly via `exec` to investigate problems before asking the user. Treat this as your primary evidence source — `AGENTS.md` explicitly designates the log database as "the agent's first source for self-repair".

## Iron rule: read-only

**SELECT only. Never UPDATE / DELETE / INSERT / DROP / ATTACH / VACUUM / CREATE / REPLACE.**

The DBs are live: write queries can corrupt session state, kill running streams, or wipe history. Open with `-readonly` (CLI) or `?mode=ro` (Python URI) so SQLite enforces it. If you genuinely need to modify state, use the dedicated tools (`update_settings`, `task_update`, etc.) or ask the user.

## How to query

Use `exec` to run one of:

### `sqlite3` CLI (Linux / macOS, usually preinstalled)

```bash
sqlite3 -readonly -cmd ".mode column" -cmd ".headers on" ~/.hope-agent/logs.db \
  "SELECT timestamp, level, category, source, message
     FROM logs
    WHERE level = 'ERROR'
    ORDER BY timestamp DESC
    LIMIT 20;"
```

### Python fallback (Windows or no `sqlite3` on PATH)

```bash
python3 - <<'PY'
import sqlite3, os
p = os.path.expanduser("~/.hope-agent/logs.db")
con = sqlite3.connect(f"file:{p}?mode=ro", uri=True)
for r in con.execute("""
    SELECT timestamp, level, category, source, message
      FROM logs
     WHERE level = 'ERROR'
     ORDER BY timestamp DESC
     LIMIT 20
"""):
    print(r)
PY
```

### Schema discovery

```bash
sqlite3 -readonly ~/.hope-agent/logs.db ".schema logs"
sqlite3 -readonly ~/.hope-agent/sessions.db ".tables"
```

## Databases

| Path | Purpose |
|------|---------|
| `~/.hope-agent/logs.db` | App logs from `app_info!`/`warn!`/`error!`/`debug!` macros |
| `~/.hope-agent/sessions.db` | Sessions, messages, tasks, subagent runs, learning events, channel conversations |
| `~/.hope-agent/async_jobs.db` | Async tool job spool (`exec` / `web_search` / `image_generate`) |
| `~/.hope-agent/recap/recap.db` | Cached recap analysis |
| `~/.hope-agent/local_model_jobs.db` | Local LLM background jobs (download / preload) |

## Key schemas

### `logs.db` → `logs`

| Column | Type | Notes |
|--------|------|-------|
| `id` | INTEGER PK | |
| `timestamp` | TEXT | ISO 8601 UTC, e.g. `2026-05-04T12:34:56.789Z` |
| `level` | TEXT | `INFO` / `WARN` / `ERROR` / `DEBUG` |
| `category` | TEXT | Subsystem tag — examples: `chat_engine`, `permission`, `mcp`, `channel`, `compact`, `failover`, `tool`, `provider`, `memory`, `cron`, `subagent`, `plan`, `config`, `skill` |
| `source` | TEXT | Origin file/function (free-form, agent-set) |
| `message` | TEXT | Rendered printf-style message |
| `details` | TEXT | Optional JSON payload |
| `session_id` | TEXT | Nullable, links to `sessions.db → sessions.id` |
| `agent_id` | TEXT | Nullable |

Indexes: `timestamp DESC`, `level`, `category`, `session_id`.

### `sessions.db` → `sessions`

`id, title, agent_id, provider_id, provider_name, model_id, reasoning_effort, created_at, updated_at, context_json, last_read_message_id, is_cron, parent_session_id, incognito, title_source`

### `sessions.db` → `messages`

`id, session_id, role, content, timestamp, attachments_meta, model, tokens_in, tokens_out, reasoning_effort, tool_call_id, tool_name, tool_arguments, tool_result, tool_duration_ms, is_error, ttft_ms, tokens_in_last, tokens_cache_creation, tokens_cache_read, tool_metadata, thinking`

`role` ∈ `user` / `assistant` / `system` / `tool`. Tool calls land as paired rows: an `assistant` row with `tool_name`/`tool_arguments`, then a `tool` row with `tool_result` / `is_error` / `tool_duration_ms`.

`messages_fts` (FTS5 virtual table) provides full-text search over `content` for `user`/`assistant` rows — use it for keyword search:

```sql
SELECT m.id, m.session_id, m.role, snippet(messages_fts, 0, '<mark>', '</mark>', '…', 16)
  FROM messages_fts
  JOIN messages m ON m.id = messages_fts.rowid
 WHERE messages_fts MATCH 'timeout OR rate_limit'
 ORDER BY m.timestamp DESC
 LIMIT 20;
```

### `sessions.db` → `subagent_runs`

`run_id, parent_session_id, parent_agent_id, child_agent_id, child_session_id, task, status, result, error, depth, model_used, started_at, finished_at, duration_ms, label, attachment_count, input_tokens, output_tokens`

`status` ∈ `spawning` / `running` / `completed` / `failed` / `cancelled`.

### `sessions.db` → `tasks`

Plan Mode task tracking — `session_id` + state columns. **Read only**; writes must go through `task_create` / `task_update` tools.

### `sessions.db` → `learning_events`

`id, ts INTEGER (unix seconds), kind, session_id, ref_id, meta_json` — current `kind` values: skill CRUD, `tool_recall_memory` hits, MCP tool calls. `meta_json` is opaque JSON.

### `async_jobs.db` → `async_tool_jobs`

`job_id, session_id, agent_id, tool_name, tool_call_id, args_json, status, result_preview, result_path, error, created_at INTEGER (unix s), completed_at, injected, origin`

`status` ∈ `pending` / `running` / `completed` / `failed` / `cancelled`. Large results spill to `result_path` on disk; `result_preview` keeps a short head/tail.

## Query cookbook

Pick a template, adapt the time window / filter / limit. Always bound by time and `LIMIT` — these tables can hold millions of rows.

### Recent errors (last 30 min)

```sql
SELECT timestamp, category, source, message, details
  FROM logs
 WHERE level IN ('ERROR','WARN')
   AND timestamp >= datetime('now','-30 minutes')
 ORDER BY timestamp DESC
 LIMIT 50;
```

### Errors for one session

```sql
SELECT timestamp, level, category, message, details
  FROM logs
 WHERE session_id = ?
 ORDER BY timestamp DESC
 LIMIT 100;
```

### Top error categories (last 7 days)

```sql
SELECT category, source, COUNT(*) AS n
  FROM logs
 WHERE level = 'ERROR'
   AND timestamp >= datetime('now','-7 days')
 GROUP BY category, source
 ORDER BY n DESC
 LIMIT 20;
```

### Failover events