How We Built an AI Usage Report for Our OpenCode Platform

We built a custom HTML report generator that extracts AI usage statistics from OpenCode (sessions, tokens, costs, MCP tool calls) and delivers it daily via email. The data comes from two sources — a REST API for session-level metrics and direct SQLite access for MCP tool call telemetry.

---

The Challenge

We run OpenCode as our primary AI agent platform in our infrastructure. It handles dozens of AI sessions daily — from infrastructure management to code reviews, DNS changes, and security patching. But we had no visibility into:

• How many AI sessions run per day?
• Which models are used (DeepSeek v4 Flash vs Pro)?
• How many tokens are consumed?
• What does it cost?
• Which subagents are most active?
• Which MCP tools are used most?

The data was there — stored in OpenCode’s SQLite database — but there was no built-in dashboard or report.

Two Data Sources

1. The REST API (Session-level data)

OpenCode runs an HTTP API on port 3099. With proper authentication and the X-Requested-With: XMLHttpRequest header, it returns session data in JSON:

import urllib.request, base64, json

url = "http://opencode-server.example.com:3099/api/session?limit=500"
auth = base64.b64encode(f"{username}:{password}".encode()).decode()
req = urllib.request.Request(url, headers={
    "X-Requested-With": "XMLHttpRequest",
    "Authorization": f"Basic {auth}",
})

with urllib.request.urlopen(req) as resp:
    sessions = json.loads(resp.read())["data"]

Each session object contains:

Field	Description
id	Unique session ID
title	Session title (the user’s prompt)
agent	Subagent used (build, project-manager, vault-manager, etc.)
model	Model object with id, providerID, variant
cost	Total cost in USD
tokens.input	Input tokens consumed
tokens.output	Output tokens generated
tokens.reasoning	Reasoning tokens
tokens.cache.read	Cache reads
tokens.cache.write	Cache writes
time.created	Session creation timestamp (ms)

What you get: Daily session counts, token usage per model, cost tracking, agent activity breakdown, most expensive sessions.

Authentication: HTTP Basic Auth with credentials from the OPENCODE_SERVER_USERNAME and OPENCODE_SERVER_PASSWORD environment variables set in the Docker Compose configuration.

2. SQLite Database (MCP tool call telemetry)

The REST API is great for session metrics, but it doesn’t expose the individual MCP tool calls. For that, you need direct access to the SQLite database at /home/opencode/data/share/opencode.db.

The part table stores every tool call made during AI sessions:

SELECT json_extract(p.data, '$.tool') as tool_name,
       COUNT(*) as call_count
FROM part p
JOIN session s ON p.session_id = s.id
WHERE json_extract(p.data, '$.type') = 'tool'
  AND s.time_created >= ? AND s.time_created < ?
GROUP BY tool_name
ORDER BY call_count DESC;

This reveals which MCP servers are used most. In our case: bash (6,472 calls), read (826), taiga_getUserStory (609), task (348), and 185 other tools.

The Report Generator

We built a Python script (generate_opencode_report.py) with dual-mode support:

• --api mode: Uses the REST API (no SSH needed, runs anywhere)
• Default mode: Reads SQLite directly (run on the OpenCode server, includes MCP data)

The script produces a self-contained HTML file with:

# API mode (remote, no SSH):
python3 generate_opencode_report.py --api \
    --api-url http://opencode-server.example.com:3099 \
    --api-username "$USERNAME" --api-password "$PASSWORD"

# SQLite mode (direct on server, includes MCP data):
python3 generate_opencode_report.py \
    --db /home/opencode/data/share/opencode.db

Report Sections

Executive Summary — Four key metrics at a glance: total AI sessions, total tokens consumed (input + output), total estimated cost, and total MCP tool calls.

Daily Breakdown — A day-by-day table showing sessions, tokens (split by input/output/reasoning), and cost, with visual progress bars.

Session & Token Rate — Tokens-per-session and cost-per-session trends, helping identify efficiency patterns.

Model Distribution — Which models are being used (DeepSeek v4 Flash vs Pro), with session counts, token volumes, and costs.

Agent/Subagent Activity — Each subagent’s usage broken down: project-manager, system-manager, vault-manager, build, devops-manager, monitor, web-search, and more.

Most Expensive Sessions — Top 20 sessions by cost, useful for cost optimization.

Most Token-Heavy Sessions — Top 20 sessions by total token count, useful for understanding peak usage patterns.

Recent Sessions — Last 50 sessions with full details.

MCP Tool Usage — A grid of all 189 tools used, sorted by call count, with visual bars. Top tools include bash, read, taiga_getUserStory, task, glob, grep, webfetch, edit, write, todowrite.

Automated Delivery

The report is delivered via a CI/CD pipeline (e.g., Gitea Actions, GitHub Actions, or similar) on a daily schedule (06:00 UTC / 08:00 Central European time):

on:
  schedule:
    - cron: '0 6 * * *'
  workflow_dispatch:
    inputs:
      month:
        description: 'Report month (YYYY-MM)'
      email_to:
        description: 'Email recipient'

The workflow:

1. Fetches the SSH key from a password vault (e.g., Bitwarden/Vaultwarden CLI)
2. SSHes into the OpenCode server
3. Runs the report generator in SQLite mode (full MCP data)
4. Downloads the generated HTML report
5. Emails it as an attachment via SMTP
6. Uploads it as a workflow artifact (retention depends on configuration)

# The vault credential pattern (adapt to your setup)
VAULT_CREDENTIALS='{"api_key_id":"...","api_key_secret":"...","master_password":"..."}'
export BW_CLIENTID API_KEY_ID
npx @bitwarden/cli login --apikey
npx @bitwarden/cli get item "<vault-item-uuid>" --session $SESSION

Key Takeaways

1. OpenCode has two data layers: a REST API for session-level metrics and a SQLite database for detailed telemetry (MCP calls, parts).
2. The REST API is great for remote monitoring — just HTTP Basic Auth and the right header (X-Requested-With: XMLHttpRequest).
3. MCP tool telemetry requires direct database access — there’s no API endpoint for the part table yet.
4. The same CI/CD pattern (vault → SSH → script execution → email) works for multiple report types — you can use it for both performance monitoring and AI usage analytics.
5. Cost visibility is critical — identifying that DeepSeek v4 Pro accounts for 87% of costs while only handling 38% of sessions was an actionable insight.