ha-data-stores

# Hope Agent Data Stores

Use this skill when you need to locate or inspect Hope Agent's persisted local
data. Prefer product tools first; use direct SQLite only for diagnostics or
ad-hoc analysis that existing tools do not cover.

## Priority order

1. Use dedicated model tools when they answer the question:
   - `sessions_search` for finding exact details in current or historical chat
     messages.
   - `sessions_history` for paginated transcript reading once a session is
     known.
   - `recall_memory` / `memory_get` for user/project/agent memory.
   - `note_*` / `knowledge_recall` for attached knowledge spaces.
   - `get_settings` for AppConfig settings.
   - `job_status` for background job state visible to the current session.
2. Use read-only SQLite queries only when a dedicated tool is missing, too
   coarse, or the task is diagnostic/audit work.
3. Never mutate app databases directly. Do not run `UPDATE`, `DELETE`,
   `INSERT`, `DROP`, `CREATE`, `ALTER`, `VACUUM`, `REINDEX`, or `ATTACH`.

## Data root

All app-managed data lives under the Hope Agent data root:

- If `HA_DATA_DIR` is set: use it exactly.
- Otherwise: `~/.hope-agent`.

Shell helper:

```bash
ROOT="${HA_DATA_DIR:-$HOME/.hope-agent}"
```

Use this helper instead of hard-coding `~/.hope-agent` when running queries.

## SQLite read-only patterns

Use the sqlite CLI with `-readonly`:

```bash
ROOT="${HA_DATA_DIR:-$HOME/.hope-agent}"
sqlite3 -readonly -cmd ".headers on" -cmd ".mode column" "$ROOT/sessions.db" \
  "SELECT id, title, agent_id, updated_at FROM sessions ORDER BY updated_at DESC LIMIT 10;"
```

Python fallback:

```bash
python3 - <<'PY'
import os, sqlite3
root = os.environ.get("HA_DATA_DIR") or os.path.expanduser("~/.hope-agent")
path = os.path.join(root, "sessions.db")
con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
for row in con.execute("SELECT id, title, updated_at FROM sessions ORDER BY updated_at DESC LIMIT 10"):
    print(row)
PY
```

## Main stores

| Store | Path | Purpose |
|---|---|---|
| Sessions | `$ROOT/sessions.db` | Sessions, messages, chat context snapshots, projects, knowledge registry/bindings, channel mappings, subagent runs, tasks, learning events |
| Memory | `$ROOT/memory.db` | Long-term memories, memory FTS/vector data, Dreaming claims/evidence/profile data |
| Logs | `$ROOT/logs.db` | Structured app logs emitted by `app_info!` / `app_warn!` / `app_error!` / `app_debug!` |
| Background jobs | `$ROOT/background_jobs.db` | Unified background job cache for async tools and subagent/group projections |
| Cron | `$ROOT/cron.db` | Scheduled tasks managed by `manage_cron` |
| Wakeups | `$ROOT/wakeups.db` | Agent self-scheduled wakeups from `schedule_wakeup` |
| Recap | `$ROOT/recap/recap.db` | Cached recap/report facets and summaries |
| Knowledge index | `$ROOT/knowledge/index.db` | Rebuildable note/chunk/link/tag search cache; real notes are Markdown files |
| Canvas | `$ROOT/canvas/canvas.db` | Canvas projects and versions |
| Local model jobs | `$ROOT/local_model_jobs.db` | Ollama/local-model install, pull, preload jobs |
| Local LLM cache | `$ROOT/local_llm_library_cache.db` | Cached Ollama Library search/tag metadata |

`async_jobs.db` is legacy. Current code uses `background_jobs.db`; an old
`async_jobs.db` file may be leftover cache and should not be treated as current
truth.

## Important non-DB paths

| Path | Purpose |
|---|---|
| `$ROOT/config.json` | AppConfig: settings, providers metadata, feature toggles |
| `$ROOT/user.json` | User profile and UI preferences |
| `$ROOT/agents/` | Agent definitions and agent prompt files |
| `$ROOT/{agent_id}-home/` | Per-agent scratch/home directory |
| `$ROOT/home/` | Shared directory across agents |
| `$ROOT/attachments/{session_id}/` | Persisted chat attachments |
| `$ROOT/sessions/{session_id}/transcript.jsonl` | Hook transcript mirror |
| `$ROOT/tool_results/{session_id}/` | Large tool-result spill files |
| `$ROOT/background_jobs/` | Background job result spool |
| `$ROOT/knowledge/{kb_id}/notes/` | Internal knowledge-base Markdown files |
| `$ROOT/credentials/` | OAuth/API credentials. Do not read unless the user explicitly asks and it is necessary. Never print secrets. |

## Query guide

### Session messages

Prefer `sessions_search` first. For raw SQL:

```sql
SELECT id, session_id, role, timestamp, substr(content, 1, 500) AS content
  FROM messages
 WHERE session_id = ?
 ORDER BY id ASC
 LIMIT 100;
```

Use `messages_fts` for keyword search over user/assistant content:

```sql
SELECT m.id, m.session_id, m.role, m.timestamp,
       snippet(messages_fts, 0, '[', ']', '...', 16) AS snippet
  FROM messages_fts
  JOIN messages m ON m.id = messages_fts.rowid
 WHERE messages_fts MATCH ?
 ORDER BY rank
 LIMIT 20;
```

Global searches must exclude private/non-user surfaces unless the task
explicitly targets them:

```sql
... JOIN sessions s ON s.id = m.session_id
WHERE s.incognito = 0 AND s.kind != 'knowledge'
```

### Logs

```sql
SELECT timestamp, level, category, source, message
  FROM logs
 WHERE level IN ('ERROR', 'WARN')
 ORDER BY timestamp DESC
 LIMIT 50;
```

### Background jobs

```sql
SELECT job_id, kind, session_id, status, tool_name, created_at, completed_at, error
  FROM background_jobs
 ORDER BY created_at DESC
 LIMIT 50;
```

### Memory

Prefer `recall_memory` and `memory_get`. Use SQL only for diagnostics:

```sql
SELECT id, memory_type, scope, scope_agent_id, scope_project_id,
       pinned, created_at, substr(content, 1, 500) AS content
  FROM memories
 ORDER BY updated_at DESC
 LIMIT 50;
```

### Knowledge spaces

Registry and access bindings live in `sessions.db`; the index cache lives in
`knowledge/index.db`. Internal note bodies are Markdown files and are the source
of truth. Do not edit `index.db`; rebuild it through product flows.

Useful registry tables include `knowledge_bases`, `session_knowledge_bases`,
`project_knowledge_bases`, `knowledge_chat_threads`, and
`kb_maintenance_proposals`.

## Safety notes