Agents · 2026-05-28 · By RedDB team · 4 min read

Agent observability — traces, tokens, and costs in RedDB

Most Claude Code setups can't answer "what did the agent cost me last sprint?" or "which tools dominate p99 latency?". A PostToolUse hook that writes every tool call, token count, and cost into RedDB turns those questions into one-line SQL.

The questions you can’t answer today

Run Claude Code daily for a week and you accumulate a folder of transcripts, a credit-card bill from Anthropic, and no honest answer to questions a manager will ask within a month:

  • How much did the team spend on agent runs last sprint?
  • Which tools account for the bulk of token usage — Read, Bash, sub-agents?
  • What is the p99 latency of Bash vs Read? Is one of them dragging turn times?
  • Which sessions blew past a sensible cost budget, and what were they doing?
  • After we shipped the new memory hook, did average turn cost go up or down?

Each of these is a GROUP BY away once the data is in a table. The work is in getting it there, accurately, without slowing the agent down. A PostToolUse hook plus three tables is enough.

This post assumes you have the same RedDB-as-memory-backend setup from the first post in this pillar and the transactional hook recipe from the audit-log post. It reuses the idempotency pattern verbatim — observability is a special case of structured audit.

The schema

Three tables. One for the tool call itself, one for token/cost facts emitted by the model API, one for session-level rollups so the dashboard does not re-aggregate twelve million rows on every page load.

CREATE TABLE tool_call (
  id              TEXT PRIMARY KEY,                  -- ulid
  session_id      TEXT NOT NULL,
  turn_index      INTEGER NOT NULL,                  -- monotonic within session
  tool_name       TEXT NOT NULL,                     -- Read, Bash, Skill, Task, …
  tool_input_size INTEGER NOT NULL,                  -- bytes of tool_input json
  is_error        BOOLEAN NOT NULL DEFAULT false,
  started_at      TIMESTAMPTZ NOT NULL,
  ended_at        TIMESTAMPTZ NOT NULL,
  duration_ms     INTEGER GENERATED ALWAYS AS
                    (EXTRACT(EPOCH FROM (ended_at - started_at)) * 1000)::int STORED
);

CREATE TABLE token_usage (
  id              TEXT PRIMARY KEY,                  -- ulid
  session_id      TEXT NOT NULL,
  turn_index      INTEGER NOT NULL,
  model           TEXT NOT NULL,                     -- claude-opus-4-7, …
  input_tokens    INTEGER NOT NULL,
  cached_tokens   INTEGER NOT NULL DEFAULT 0,        -- prompt-cache hits
  output_tokens   INTEGER NOT NULL,
  cost_usd        NUMERIC(10,6) NOT NULL,            -- computed from the model price list
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE session_rollup (
  session_id      TEXT PRIMARY KEY,
  started_at      TIMESTAMPTZ NOT NULL,
  last_seen_at    TIMESTAMPTZ NOT NULL,
  total_turns     INTEGER NOT NULL DEFAULT 0,
  total_tools     INTEGER NOT NULL DEFAULT 0,
  total_cost_usd  NUMERIC(10,6) NOT NULL DEFAULT 0,
  project_path    TEXT,
  user_email      TEXT
);

CREATE INDEX tool_call_session_idx ON tool_call (session_id, turn_index);
CREATE INDEX tool_call_name_idx    ON tool_call (tool_name, started_at DESC);
CREATE INDEX token_usage_session_idx ON token_usage (session_id, turn_index);

tool_call is one row per PostToolUse event. token_usage is one row per assistant turn (the model API returns the counts in its response, which the harness exposes via Stop and PostToolUse payloads on the Anthropic adapter). session_rollup is maintained by trigger so the dashboard reads constant-time per session.

The PostToolUse hook

The hook is the audit-log hook from the previous post with three extra columns and a token-usage insert in the same transaction. The idempotency key is the same sha256(session_id + tool_use_id).

#!/usr/bin/env bash
# ~/.claude/hooks/observe.sh
set -euo pipefail

payload="$(cat)"

session_id="$(jq  -r '.session_id'                <<<"$payload")"
turn_index="$(jq  -r '.turn_index // 0'           <<<"$payload")"
tool_use_id="$(jq -r '.tool_use_id // .id // ""'  <<<"$payload")"
tool_name="$(jq   -r '.tool_name // ""'           <<<"$payload")"
tool_input="$(jq    '.tool_input // {}'           <<<"$payload")"
is_error="$(jq    -r '.is_error // false'         <<<"$payload")"
started_at="$(jq  -r '.started_at'                <<<"$payload")"
ended_at="$(jq    -r '.ended_at'                  <<<"$payload")"

# Token usage is only present on Stop and on the turn-ending PostToolUse;
# pull it defensively.
model="$(jq         -r '.usage.model // ""'         <<<"$payload")"
in_tokens="$(jq     -r '.usage.input_tokens   // 0' <<<"$payload")"
cached_tokens="$(jq -r '.usage.cached_tokens  // 0' <<<"$payload")"
out_tokens="$(jq    -r '.usage.output_tokens  // 0' <<<"$payload")"

tool_id="$(ulid)"
idem_key="$(printf '%s:%s' "$session_id" "$tool_use_id" | sha256sum | awk '{print $1}')"
input_size="$(printf '%s' "$tool_input" | wc -c)"

# Price table lives in the hook so a model-list change is one diff.
cost_usd="$(python3 - <<PY
prices = {
  "claude-opus-4-7":   (15.00 / 1e6, 75.00 / 1e6, 1.50 / 1e6),  # in, out, cached-read
  "claude-sonnet-4-6": ( 3.00 / 1e6, 15.00 / 1e6, 0.30 / 1e6),
  "claude-haiku-4-5":  ( 0.80 / 1e6,  4.00 / 1e6, 0.08 / 1e6),
}
m = "$model"
if m not in prices or ($in_tokens + $out_tokens) == 0:
  print("0")
else:
  pi, po, pc = prices[m]
  print(f"{$in_tokens * pi + $out_tokens * po + $cached_tokens * pc:.6f}")
PY
)"

curl -sS --fail --max-time 1 \
  -H "Authorization: Bearer $REDDB_TOKEN" \
  -H "Content-Type: application/json" \
  "$REDDB_URL/tx" \
  -d "$(jq -nc \
        --arg tid       "$tool_id" \
        --arg sid       "$session_id" \
        --argjson turn  "$turn_index" \
        --arg tname     "$tool_name" \
        --argjson isz   "$input_size" \
        --argjson err   "$is_error" \
        --arg sat       "$started_at" \
        --arg eat       "$ended_at" \
        --arg model     "$model" \
        --argjson it    "$in_tokens" \
        --argjson ct    "$cached_tokens" \
        --argjson ot    "$out_tokens" \
        --arg cost      "$cost_usd" \
        --arg ikey      "$idem_key" \
        '{
          statements: [
            { sql: "INSERT INTO tool_call (id, session_id, turn_index, tool_name, tool_input_size, is_error, started_at, ended_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT (id) DO NOTHING",
              args: [$tid, $sid, $turn, $tname, $isz, $err, $sat, $eat] },
            { sql: "INSERT INTO hook_idempotency (key, event_id) VALUES ($1,$2) ON CONFLICT (key) DO NOTHING",
              args: [$ikey, $tid] },
            { sql: "INSERT INTO token_usage (id, session_id, turn_index, model, input_tokens, cached_tokens, output_tokens, cost_usd) SELECT ulid(), $1, $2, $3, $4, $5, $6, $7 WHERE $3 <> ''",
              args: [$sid, $turn, $model, $it, $ct, $ot, $cost] }
          ]
        }')" >/dev/null || true

echo '{"continue":true}'

Two notes:

  • --max-time 1 + || true — the agent never blocks on telemetry. If RedDB is unreachable for a second, that one event is lost. A Stop hook can backfill from the harness’s local JSONL transcript if you need full coverage.
  • The third statement is gated on $model <> ''. Most PostToolUse events do not carry usage data — only the turn-closing one does. The conditional INSERT … SELECT … WHERE skips the row at SQL level rather than branching in bash.

Wire it up in ~/.claude/settings.json:

{
  "hooks": {
    "PostToolUse": [
      { "matcher": ".*", "command": "~/.claude/hooks/observe.sh" }
    ],
    "Stop": [
      { "matcher": ".*", "command": "~/.claude/hooks/observe.sh" }
    ]
  }
}

The rollup trigger

Reading raw tool_call and token_usage for “show me last sprint’s cost by user” is fine at 100k rows and miserable at 10M. A trigger keeps session_rollup warm:

CREATE OR REPLACE FUNCTION bump_rollup() RETURNS trigger AS $$
BEGIN
  INSERT INTO session_rollup (session_id, started_at, last_seen_at, total_turns, total_tools, total_cost_usd)
  VALUES (NEW.session_id, NEW.started_at, NEW.ended_at, 0, 1, 0)
  ON CONFLICT (session_id) DO UPDATE
     SET last_seen_at = GREATEST(session_rollup.last_seen_at, EXCLUDED.last_seen_at),
         total_tools  = session_rollup.total_tools + 1;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER tool_call_rollup AFTER INSERT ON tool_call
FOR EACH ROW EXECUTE FUNCTION bump_rollup();

CREATE OR REPLACE FUNCTION bump_cost() RETURNS trigger AS $$
BEGIN
  UPDATE session_rollup
     SET total_cost_usd = total_cost_usd + NEW.cost_usd,
         total_turns    = GREATEST(total_turns, NEW.turn_index + 1)
   WHERE session_id = NEW.session_id;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER token_usage_rollup AFTER INSERT ON token_usage
FOR EACH ROW EXECUTE FUNCTION bump_cost();

session_rollup is now the queryable summary. The raw tables stay for drill-down.

The five queries that earn the hook

-- 1. Sprint spend by user.
SELECT user_email,
       SUM(total_cost_usd)::numeric(10,2) AS cost_usd,
       COUNT(*)                            AS sessions
  FROM session_rollup
 WHERE started_at >= date_trunc('day', now()) - interval '14 days'
 GROUP BY user_email
 ORDER BY cost_usd DESC;

-- 2. Top tools by total token-equivalent cost (rough — tools don't pay tokens
--    directly, but tool_input bytes correlate with the prompt cost they cause).
SELECT tool_name,
       COUNT(*)                                          AS calls,
       SUM(tool_input_size)                              AS bytes_into_prompt,
       SUM(tool_input_size) * 0.000003                   AS approx_cost_usd
  FROM tool_call
 WHERE started_at >= now() - interval '7 days'
 GROUP BY tool_name
 ORDER BY approx_cost_usd DESC
 LIMIT 10;

-- 3. p99 latency by tool, last 24h.
SELECT tool_name,
       percentile_disc(0.50) WITHIN GROUP (ORDER BY duration_ms) AS p50,
       percentile_disc(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99,
       COUNT(*)                                                   AS n
  FROM tool_call
 WHERE started_at >= now() - interval '24 hours'
 GROUP BY tool_name
 ORDER BY p99 DESC;

-- 4. Sessions over a $5 budget, this week.
SELECT session_id, user_email, total_cost_usd, total_turns, total_tools, project_path
  FROM session_rollup
 WHERE started_at >= date_trunc('week', now())
   AND total_cost_usd > 5.00
 ORDER BY total_cost_usd DESC
 LIMIT 25;

-- 5. Before/after comparison for a hook change deployed at 2026-05-20.
SELECT date_trunc('day', started_at) AS day,
       AVG(total_cost_usd)::numeric(10,4) AS avg_session_cost,
       COUNT(*)                            AS sessions
  FROM session_rollup
 WHERE started_at >= '2026-05-13'
 GROUP BY day
 ORDER BY day;

Each one is the smallest possible question to ask of the schema. EXPLAIN ANALYZE on a year of single-user data on commodity hardware runs all five in under 50ms thanks to the rollup table and the two indexes on tool_call.

A dashboard sketch

The five queries above are also the five panels of a sensible “agent observability” board. Sketched in Grafana terms but the pattern translates to anything that speaks SQL:

┌──────────────────────────────────────────────────────────────────┐
│  Sprint spend by user        │  Top tools by approx cost          │
│  (bar — query 1)             │  (bar — query 2)                   │
├──────────────────────────────┼────────────────────────────────────┤
│  p50 / p99 by tool, 24h      │  Sessions over $5 this week        │
│  (heatmap — query 3)         │  (table — query 4)                 │
├──────────────────────────────┴────────────────────────────────────┤
│  Daily average session cost (line — query 5, annotated with        │
│  vertical markers at each hook deploy)                             │
└────────────────────────────────────────────────────────────────────┘

The vertical annotations on the last panel are the highest-leverage piece. Every time you deploy a new hook, skill, or system prompt, drop a marker. The line either bends down (the change paid off) or it does not. Most teams ship agent changes blind because the feedback loop is “vibes”. This panel is the feedback loop.

What this buys you

QuestionBeforeAfter
Sprint spend by userAsk Anthropic billing CSV, grep for emailsSELECT … FROM session_rollup
Which tools dominate latencyRead transcripts, eyeballOne percentile_disc query
Did the new skill make turns cheaperHope soAnnotated time-series, signed answer
Why did session X cost $40Open the transcript, scrollSELECT * FROM tool_call WHERE session_id = …

The hook is ~50 lines. The schema is three tables and two triggers. The dashboard is one Grafana JSON away. The payoff is that “how is the agent doing” stops being a vibes question and starts being a query.

What is next in this pillar

  • The same tool_call table powers a context-window economics post: knowing the bytes-into-prompt per tool lets you compute the dollar cost of re-reading a file vs. caching it in RedDB.
  • A sub-agent dispatch queue post that reuses tool_call.tool_name = 'Task' rows to track parent/child runtime and cost across processes.
  • An MCP-server post that exposes observability.* to other CLI agents — Cursor and Codex emit the same tool-call shape, so the schema generalises.

The Atom feed carries them when they land.

#claude-code#agents#observability#hooks#cost-tracking

© 2026 RedDB.io. AGPL-3.0 self-host · Managed Cloud.