# Hermes state.db token-audit query patterns

Use these patterns during Hermes token/context burn audits. Keep outputs bounded and summarize; do not dump raw messages or secrets.

## DB location and schema hints

Default profile DB:

```text
~/.hermes/state.db
```

Relevant tables commonly include:

- `sessions`: `source`, `model`, `started_at`, `message_count`, `tool_call_count`, `api_call_count`, `input_tokens`, `output_tokens`, `cache_read_tokens`, `cache_write_tokens`, `reasoning_tokens`, `estimated_cost_usd`, `system_prompt`, `title`.
- `messages`: `session_id`, `role`, `content`, `tool_name`, `timestamp`, `token_count`.

If `sqlite3` CLI is unavailable, use Python stdlib `sqlite3` from a normal terminal command.

## Official overview first

```bash
hermes insights --days 30
hermes insights --days 7
```

Use this to get top-level Sessions/Messages/Tool calls/Input/Output/Total and top tools/skills. Then use DB queries for deeper breakdowns.

## Aggregate by source/model

```python
import sqlite3, os, time
con = sqlite3.connect(os.path.expanduser('~/.hermes/state.db'))
con.row_factory = sqlite3.Row
cur = con.cursor()
now = time.time()

for r in cur.execute('''
select source, coalesce(model,'?') model, count(*) sessions,
       sum(api_call_count) calls,
       sum(input_tokens) input,
       sum(output_tokens) output,
       sum(cache_read_tokens) cache_read,
       sum(cache_write_tokens) cache_write,
       sum(reasoning_tokens) reasoning,
       sum(coalesce(estimated_cost_usd,0)) cost
from sessions
where started_at > ?
group by source, model
order by (sum(input_tokens)+sum(output_tokens)+sum(cache_read_tokens)+sum(cache_write_tokens)+sum(reasoning_tokens)) desc
limit 20
''', (now - 30*86400,)):
    total = sum(r[k] or 0 for k in ['input','output','cache_read','cache_write','reasoning'])
    print(dict(r), 'total_tokens', total)
```

## Top sessions by total tokens

```python
for r in cur.execute('''
select id, title, source, model, started_at, message_count, tool_call_count, api_call_count,
       input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens,
       estimated_cost_usd, length(system_prompt) sys_chars
from sessions
where started_at > ?
order by (coalesce(input_tokens,0)+coalesce(output_tokens,0)+coalesce(cache_read_tokens,0)+coalesce(cache_write_tokens,0)+coalesce(reasoning_tokens,0)) desc
limit 25
''', (now - 30*86400,)):
    total = sum(r[k] or 0 for k in ['input_tokens','output_tokens','cache_read_tokens','cache_write_tokens','reasoning_tokens'])
    print(total, r['api_call_count'], r['tool_call_count'], r['title'])
```

## Top sessions by non-cache tokens

Non-cache is often more useful for cost/context pressure:

```python
for r in cur.execute('''
select title, source, model, started_at, api_call_count, tool_call_count, message_count,
       input_tokens, output_tokens, reasoning_tokens, cache_read_tokens, length(system_prompt) syschars
from sessions
where started_at > ?
order by (coalesce(input_tokens,0)+coalesce(output_tokens,0)+coalesce(reasoning_tokens,0)) desc
limit 20
''', (now - 30*86400,)):
    non = (r['input_tokens'] or 0) + (r['output_tokens'] or 0) + (r['reasoning_tokens'] or 0)
    total = non + (r['cache_read_tokens'] or 0)
    print(f"{non/1e6:.2f}M non-cache | {total/1e6:.2f}M total | calls {r['api_call_count']} | tools {r['tool_call_count']} | {r['title']}")
```

## Daily totals

```python
for r in cur.execute('''
select date(started_at,'unixepoch','localtime') d,
       count(*) sessions,
       sum(api_call_count) calls,
       sum(input_tokens+output_tokens+reasoning_tokens) non,
       sum(cache_read_tokens) cache
from sessions
where started_at > ?
group by d
order by d desc
limit 14
''', (now - 14*86400,)):
    print(r['d'], r['sessions'], r['calls'], (r['non'] or 0)/1e6, (r['cache'] or 0)/1e6)
```

## Tool output volume

```python
for r in cur.execute('''
select m.role, coalesce(m.tool_name,'') tool, count(*) n,
       sum(length(coalesce(m.content,''))) chars
from messages m
join sessions s on s.id = m.session_id
where s.started_at > ?
group by m.role, tool
order by chars desc
limit 30
''', (now - 30*86400,)):
    print(dict(r))
```

Use this to spot `read_file`, `terminal`, `skill_view`, `session_search`, browser snapshots, or other heavy tool outputs.

## Skill load frequency from messages

Skill tool outputs are JSON-ish. Parse defensively:

```python
import json, re, collections
cnt = collections.Counter(); chars = collections.Counter()
for r in cur.execute('''
select content
from messages m join sessions s on s.id=m.session_id
where s.started_at>? and m.tool_name='skill_view'
''', (now - 30*86400,)):
    c = r['content'] or ''
    name = '?'
    try:
        obj = json.loads(c); name = obj.get('name') or obj.get('skill') or '?'
    except Exception:
        m = re.search(r'"name"\s*:\s*"([^"]+)"', c)
        if m: name = m.group(1)
    cnt[name] += 1; chars[name] += len(c)

for name, n in cnt.most_common(20):
    print(f"{n} calls {chars[name]//4} rough tokens {name}")
```

## Category estimates from titles

When producing a user-facing report, group titles with regexes, but label it as an estimate:

```python
cats = [
  ('Crypto/Trading', r'Crypto|Trading|Trader|Hyperliquid|Paper|Live|Market'),
  ('FamilyDashboard', r'Family|Dashboard|Benefits|Reserv|Zeitstrahl|iPad'),
  ('AutoShorts/Video', r'Scam|Video|Short|YouTube|TikTok|AutoShort'),
  ('Health', r'Health|Gesund|Symptom|Ernährung|YAZIO|Apple Health|Marker'),
  ('Finance', r'Finance|Finanz|TrueWealth|Portfolio'),
  ('Protocols/Work', r'FPS|Protocol|Protokoll|ERNE|Furkastrasse'),
]
```

Avoid overclaiming: titles are not perfect labels.

## Cron risk classification

Use cron metadata to separate script-only from LLM jobs:

- `no_agent=True` => model-token cheap; optimize script frequency/output only if noisy.
- `no_agent=False` with long prompt/skills/model => inspect first.
- Jobs with `context_from` may inject prior outputs; check size.
- Jobs with broad toolsets should be narrowed when possible.

## Interpretation heuristics

- If Telegram/interactivity is >95% of tokens, optimize working style before cron.
- Sessions with hundreds of `api_call_count` are usually bigger problems than large system prompts alone.
- High cache-read tokens often reflect long cached context; cheaper than fresh input but still a symptom of long sessions/tool loops.
- Large `skill_view` output means split skills into compact SKILL.md plus references, or avoid repeated reloads.
- Large `terminal`/`read_file` output means summarize/filter via scripts before returning to the model.