# Session DB Cleanup

Reclaim space and maintain session hygiene in `~/.hermes/state.db`.

## When to Run

- After a month of regular use (empty gateway sessions accumulate)
- Before/after major configuration changes
- When `state.db` exceeds 300MB
- User asks for session list cleanup

## Procedure

```python
import sqlite3, os

db = os.path.expanduser('~/.hermes/state.db')
conn = sqlite3.connect(db)
cur = conn.cursor()
```

### 1. Audit

```python
# All sessions with message counts
cur.execute('''
    SELECT s.id, s.source, s.title, COUNT(m.id) as msgs
    FROM sessions s LEFT JOIN messages m ON m.session_id = s.id
    GROUP BY s.id ORDER BY s.started_at DESC
''')
for r in cur.fetchall():
    print(f'{r[0]:35s} [{r[1]:10s}] {r[3]:4d} msgs — {r[2] or "(no title)"}')
```

### 2. Delete Empty Sessions

Empty sessions are gateway auto-creates (Telegram DMs) that never received a messages row:

```python
# Find empty
cur.execute('''
    SELECT s.id FROM sessions s
    LEFT JOIN messages m ON m.session_id = s.id
    WHERE m.id IS NULL
''')
empty = [r[0] for r in cur.fetchall()]

# Delete
for sid in empty:
    cur.execute('DELETE FROM messages WHERE session_id = ?', (sid,))
    cur.execute('DELETE FROM sessions WHERE id = ?', (sid,))
conn.commit()
```

### 3. Rename Untitled Sessions

Sessions with `title IS NULL` but messages need names. Use the first user message to infer a title:

```python
cur.execute('''
    SELECT s.id, m.content FROM sessions s
    JOIN messages m ON m.session_id = s.id
    WHERE s.title IS NULL AND m.role = 'user'
    GROUP BY s.id HAVING MIN(m.id)
''')
for sid, content in cur.fetchall():
    title = content.strip()[:80].replace('\n', ' ')
    cur.execute('UPDATE sessions SET title = ? WHERE id = ?', (title, sid))
conn.commit()
```

After renaming, review and clean up truncated or auto-generated titles.

### 4. Vacuum

```python
cur.execute('PRAGMA page_count')
before_pages = cur.fetchone()[0]
cur.execute('PRAGMA page_size')
ps = cur.fetchone()[0]
print(f'Before: {(before_pages * ps) / (1024*1024):.1f} MB')

cur.execute('VACUUM')

cur.execute('PRAGMA page_count')
after_pages = cur.fetchone()[0]
print(f'After:  {(after_pages * ps) / (1024*1024):.1f} MB')
print(f'Freed:  {((before_pages - after_pages) * ps) / (1024*1024):.1f} MB')
```

### 5. Clean Gateway Routing Index

Delete the corresponding session key from `~/.hermes/sessions/sessions.json` if a Telegram-gateway session was deleted:

```python
import json
with open(os.path.expanduser('~/.hermes/sessions/sessions.json'), 'r') as f:
    idx = json.load(f)

deleted_ids = set(empty)
for key in list(idx.keys()):
    if key == '_README': continue
    if idx[key].get('session_id') in deleted_ids:
        del idx[key]

with open(os.path.expanduser('~/.hermes/sessions/sessions.json'), 'w') as f:
    json.dump(idx, f, indent=2)
```

## Pitfalls

- **Don't delete sessions the user might still reference** — only delete truly empty (0 message) sessions.
- **Telegram re-creates empty sessions on next contact** — cleaning the routing index prevents ghost accumulation.
- **FTS5 triggers auto-rebuild** messages_fts on session delete — handled by the DB schema.
- **Vacuum is slow on large DBs** (>500MB) — needs free disk space equal to DB size.
- **Backup first**: `cp ~/.hermes/state.db ~/.hermes/state.db.bak`