LLM Cost Tracker

Track OpenClaw LLM token usage and cost from OpenRouter API. Reports: last 24h, 7d, 30d, 365d with model breakdown and DB health footer. Run manually or sche...

Security Scan

The skill's code and instructions mostly match its stated purpose (collect OpenRouter usage from local OpenClaw session files and report costs), but there are inconsistencies and privacy-sensitive behaviors you should understand before installing.

Purpose & Capability

Name/description align with what the code does: it parses OpenClaw session JSONL files, extracts OpenRouter usage.cost.total and token counts, stores rows in a local SQLite DB, and generates reports. Calls to openrouter.ai for credits/auth info are consistent with a cost-tracker.

Instruction Scope

Runtime instructions and scripts read the user's OpenClaw session JSONL files (full conversation content and per-request usage) and insert raw JSON into the DB. SKILL.md also instructs an agent to forward the script's Telegram output 'EXACTLY' as the final reply — a coercive instruction that could cause raw outputs (which may include sensitive context) to be delivered unchanged. The scripts use shell calls (curl, openclaw CLI) and subprocess.run(shell=True); this is expected but increases risk if inputs are uncontrolled.

Install Mechanism

No install spec; this is an instruction-and-script bundle only. Dependencies are minimal (requests, tabulate) and there are no downloads from untrusted URLs. Files live in the skill directory — no obscure install steps were found.

Credentials

The skill requires access to an OpenRouter API key (it looks for OPENROUTER_API_KEY in env, config/env.json, and various OpenClaw config files) and to the OpenClaw sessions directory — both are proportionate to its function. However: registry metadata lists no required env vars while the code clearly reads OPENROUTER_API_KEY; portable_setup writes config/env.json with the detected key but SETUP_GUIDE.md says 'do NOT store the API key in config/env.json' (contradiction). The code also attempts to read OpenClaw config files (e.g., auth-profiles.json), which may contain other credentials, though the code only extracts the openrouter key.

Persistence & Privilege

The skill does not request always:true or system-wide privileges. It writes a local SQLite DB (config/usage.db) and an optional config/env.json under the skill directory. It suggests adding cron jobs (normal for scheduled reports). It does not modify other skills' configs.

What to consider before installing

This skill appears to do what it says, but review these points before installing: 1) Privacy: the collector reads your OpenClaw session JSONL files and stores raw usage/response JSON in config/usage.db — those session files contain full conversation text and possibly other secrets. Only install/run this on a machine where you are comfortable allowing that access. 2) API key handling: the scripts expect an OPENROUTER_API_KEY (env or OpenClaw config). portable_setup may write that key into config/env.json; SETUP_GUIDE contradicts that — verify env.json contents and remove the key if you prefer it only in your central OpenClaw config or in the environment. 3) Output forwarding: SKILL.md's instruction to forward run_tracker.py --output telegram output EXACTLY could cause raw content to be reposted unchanged; confirm what the script prints before automating any forwarding. 4) Review scripts locally: because subprocess.run(shell=True) and curl are used, inspect the exact commands and consider running collect_usage.py with --dry-run or --verify first. 5) If you plan to run scheduled jobs, run the initial backfill and health checks in an isolated environment (or on a single-user machine) and inspect usage.db for unexpected data. If you want, provide the contents of config/env.json and a small sample session file (sanitized) and I can point out exactly what would be collected.

See CHANGELOG.md for version history.

Track and report LLM token usage and cost for OpenClaw sessions powered by OpenRouter.

Core Design

Source of truth: usage.cost.total from OpenRouter's API response — this is the actual billed amount after cache discounts, reasoning charges, and all other pricing adjustments. We never recompute it from token counts and price lists.

Append-only fact table: one row per completed OpenRouter request, keyed by openrouter_request_id (the generation/response ID). Duplicates from retries or stream reconnects are handled idempotently.

Token categories (from OpenRouter usage, kept SEPARATE):

• prompt_tokens = usage.input — raw prompt tokens before cache discount
• cached_tokens = usage.cacheRead — cached read tokens (discounted billing)
• cache_write_tokens = usage.cacheWrite — cache build tokens (separate cost)
• completion_tokens = usage.output — generated output tokens
• reasoning_tokens = usage.reasoning — thinking tokens (if model exposes)
• total_tokens = usage.totalTokens — sum of all above (should equal the sum)

Billing rules:

• Billed cost = usage.cost.total directly — do NOT use prompt_tokens * input_price + completion_tokens * output_price as the primary formula
• OpenRouter applies cache discounts, reasoning charges, and all other pricing adjustments automatically in cost.total
• cached_tokens and cache_write_tokens are separate billing categories from prompt_tokens — do NOT add them together for a "total prompt" count
• Only use cost.total; if absent, fall back to cost.input + cost.output + cost.cacheRead + cost.cacheWrite

Time windows:

• last 24h = rolling, now_utc − 24 hours (UTC)
• last 7d / 30d / 90d / 365d = calendar days (HKT), inclusive of today

Quick Start

cd skills/llm-cost-tracker

# First-time setup (creates DB, backfills, verifies) — run once on a new machine
python3 scripts/collect_usage.py --init

# Telegram-formatted report (default)
python3 scripts/run_tracker.py --output telegram

# Full terminal report
python3 scripts/run_tracker.py --output terminal

# Per-request debug view
python3 scripts/run_tracker.py --output debug --debug-hours 24

Optional: Scheduled Reports

If you want reports delivered automatically to Telegram every day:

# Midnight collection (silent, populates DB — run daily)
openclaw cron add \
  --name "llm-cost:collect" \
  --message "collect usage data" \
  --cron "5 0 * * *" \
  --tz "Asia/Hong_Kong" \
  --session isolated \
  --no-deliver \
  --description "Populate request_facts from session files"

# 9 AM report (delivers to Telegram)
openclaw cron add \
  --name "llm-cost:daily" \
  --message "llm cost" \
  --cron "0 9 * * *" \
  --tz "Asia/Hong_Kong" \
  --session isolated \
  --description "Daily LLM cost report to Telegram"

Report Layout

📊 LLM Cost Report — Apr 26, 2026 18:34 HKT

• Messages (24h): 124
• Est. Tokens (24h): 4.75M
• Est. Cost (24h): $0.4933
• Total Spend (API key): $93.67
• Limit Remaining: $45.74

🏆 Top Models (24h):
  1. minimax-m2.7: $10.7432
  2. minimax-m2.5:free: $0.0000
  3. delivery-mirror: $0.0000

📈 Trend:
• Last 24h: $0.4933
• Last 7 days: $0.8542
• Last 30 days: $10.7432
• Last 90 days: $10.7432
• Last 365 days: $10.7432

💾 DB: 1.6 MB · 1,872 rows · Since Apr 5, 2026

_Sent via llm-cost-tracker_

Note: "Last 7/30/90/365 days" uses calendar days (HKT), inclusive of today. "Last 24h" uses a rolling 24-hour window (UTC). The DB footer shows the oldest record in the DB — useful for deciding when to prune old data.

First-Time Setup

One command sets up everything — DB creation, backfill, and health check:

cd skills/llm-cost-tracker
python3 scripts/collect_usage.py --init

⚠️ Don't forget — set up your scheduled jobs next!
Without them, no new data gets collected after the backfill finishes. Run these two commands before you close this terminal:

# Midnight: collect usage data (silent, no output to Telegram)
openclaw cron add \
  --name "llm-cost:collect" \
  --message "collect usage data" \
  --cron "5 0 * * *" \
  --tz "Asia/Hong_Kong" \
  --session isolated \
  --no-deliver \
  --description "Populate request_facts from session files"

# 9 AM: daily cost report delivered to Telegram
openclaw cron add \
  --name "llm-cost:daily" \
  --message "llm cost" \
  --cron "0 9 * * *" \
  --tz "Asia/Hong_Kong" \
  --session isolated \
  --description "Daily LLM cost report to Telegram"

This runs:

1. Creates config/usage.db with the correct schema (if it doesn't exist)
2. Finds your OpenClaw sessions directory automatically
3. Backfills all available session JSONL files
4. Runs a health check to verify everything is working
5. Prints DB summary (size, row count, date range)

Ongoing / step by step (alternative to --init):

# Backfill all historical sessions
python3 scripts/collect_usage.py --backfill

# Verify DB health
python3 scripts/run_tracker.py --health

# Run first report
python3 scripts/run_tracker.py --output telegram

--init is idempotent — re-running it is safe. It will append any new sessions since the last backfill rather than overwriting anything.

DB Maintenance

The report footer (💾 DB: ...) shows current DB size, row count, and the oldest record date. Over time the DB grows. To prune old data:

# Preview how many rows would be deleted (dry run)
python3 scripts/prune_usage.py 2024-04-25 --dry-run

# Delete rows older than 2024-04-25 and reclaim disk space
python3 scripts/prune_usage.py 2024-04-25 --vacuum

Retention tip: 2-year retention = delete rows older than ~730 days ago. A quarterly prune is recommended — pick a date, run dry-run first, then execute without --dry-run and add --vacuum to reclaim space.

Architecture

Session JSONL files  ──▶  collect_usage.py  ──▶  request_facts (SQLite)
                                                 │
                                                 └──▶  run_tracker.py  ──▶  Report
                                                                 │
OpenRouter API  ──▶  get_openrouter_total_usage()  ───────────────┘

Schema

Table: request_facts (one row per OpenRouter request)

Column	Type	Notes
openrouter_request_id	TEXT UNIQUE	responseId / generation ID
created_at_utc	TEXT	ISO timestamp in UTC
model	TEXT	model ID
provider	TEXT	provider (openrouter, etc.)
status	TEXT	stopReason (completed, toolUse, etc.)
prompt_tokens	INTEGER	usage.input
completion_tokens	INTEGER	usage.output
total_tokens	INTEGER	usage.totalTokens
reasoning_tokens	INTEGER	usage.reasoning
cached_tokens	INTEGER	usage.cacheRead
cache_write_tokens	INTEGER	usage.cacheWrite
billed_cost	REAL	usage.cost.total (canonical)
streamed	INTEGER	1=yes, 0=no
raw_usage_json	TEXT	full usage object as JSON

Validation

To validate against OpenRouter's own total:

1. OpenRouter dashboard → API Keys → check total_usage against run_tracker.py sum
2. Run --output debug to inspect per-request billed_cost and raw_usage_json
3. The OpenRouter API total (all-time) is shown in every report as a reference point

Known gap: session JSONL files only go back to ~April 5 2026. Earlier usage appears in OpenRouter's all-time total but not in the per-request breakdown.

---