# Hermes Session DB Cleanup

## When to Use
- User says "delete old sessions", "clean up my session list", "remove empty sessions"
- Accumulated short-lived subagent stubs (1-2 messages) or empty Telegram stubs
- User wants to prune by message count or age

## Schema

Hermes stores sessions in `~/.hermes/state.db` in the `sessions` table:

| Column | Type | Notes |
|--------|------|-------|
| `id` | TEXT | Session ID (e.g. `20260622_220015_58ac0840`) |
| `title` | TEXT | User-visible title |
| `source` | TEXT | Platform: `telegram`, `tui`, `cli`, `subagent` |
| `message_count` | INTEGER | Number of messages in the session |
| `started_at` | REAL | Unix timestamp |
| `messages` table | — | FTS-backed individual messages, FK on `session_id` |

## Cleanup Patterns

### Delete sessions with ≤N messages

Python (no sqlite3 CLI needed):

```python
import sqlite3
db = sqlite3.connect("/home/rurouni/.hermes/state.db")

# Preview
ids = [r[0] for r in db.execute("""
    SELECT id FROM sessions 
    WHERE message_count IS NULL OR message_count <= 10
""").fetchall()]
print(f"{len(ids)} sessions to delete")

# Execute
for sid in ids:
    db.execute("DELETE FROM messages WHERE session_id = ?", (sid,))
placeholders = ','.join('?' for _ in ids)
db.execute(f"DELETE FROM sessions WHERE id IN ({placeholders})", ids)
db.commit()
```

### Delete sessions by age

```python
import sqlite3, time
db = sqlite3.connect("/home/rurouni/.hermes/state.db")

cutoff = time.time() - (30 * 86400)  # 30 days ago
ids = [r[0] for r in db.execute("""
    SELECT id FROM sessions 
    WHERE started_at < ? AND started_at IS NOT NULL
""", (cutoff,)).fetchall()]
```

### Delete by source + count combo

```python
# Empty telegram stubs
ids = [r[0] for r in db.execute("""
    SELECT id FROM sessions 
    WHERE source = 'telegram' AND (message_count IS NULL OR message_count = 0)
""").fetchall()]
```

## Pitfalls
- **Always delete from `messages` table first** — FTS entries become orphaned if you delete sessions first
- **No `sqlite3` CLI on Debian 13** — use Python's built-in `sqlite3` module instead
- **Do not touch `state_meta` or `compression_locks` tables** — they're Hermes internal state
- **Session IDs are also referenced in `session_search` FTS** — deleting from `messages` table handles this
- **Hermes Desktop may cache session titles** — restart Hermes Desktop to refresh the list after cleanup

## Typical Cleanup Yield

On a 6-week install with ~100 sessions, expect:
- 3-5 empty stubs (0 messages)
- 8-15 subagent stubs (1-3 messages, spawned for quick tasks)
- Total removal: 10-20 sessions (80-90 remaining)