Token Usage Ingestion

The token usage ingestion workflow parses local coding-agent logs, prices the token usage, folds events into daily aggregates, and writes those aggregates into the token_usage table. AgentUsage may also POST session-level effort aggregates into token_effort_usage.

Parsing happens locally because agent logs live on the machine that ran the agents. Production writes happen through the deployed API route, so the production DATABASE_URL never has to be present on a local machine.

Endpoint

POST /api/usage/ingest

The endpoint is implemented in the web app and upserts rows into whichever database that deployment is configured to use.

apps/web/src/app/api/usage/ingest/route.ts

Authentication

Requests must send Authorization: Bearer <access_token>, where the token is an OAuth access token issued by this site's OAuth provider to a user whose role is admin. agent-usage auth login obtains one.

The static BLOG_MCP_AUTH_TOKEN bearer and Better Auth sessions are rejected.

Request Body

The body must match UsageIngestPayload from @workspace/usage/ingest. Field names are camelCase (matching AgentUsage Codable and the token-row wire style).

{
  "rows": [
    {
      "date": "2026-06-02",
      "agent": "claude",
      "provider": "anthropic",
      "model": "claude-sonnet-4-5",
      "inputTokens": 1200,
      "outputTokens": 420,
      "cacheReadTokens": 3000,
      "cacheWriteTokens": 250,
      "reasoningTokens": 0,
      "totalTokens": 5870,
      "costUsd": "0.012345",
      "messages": 3
    }
  ],
  "effortRows": [
    {
      "date": "2026-06-02",
      "agent": "claude",
      "levels": [{ "level": "high", "sessionCount": 2 }],
      "classifiedSessionCount": 2,
      "unclassifiedSessionCount": 1
    }
  ],
  "effortSnapshotComplete": true
}

effortRows and effortSnapshotComplete are optional (default [] / false) for older clients. Token rows remain required — effort-only POSTs are rejected (400).

Row Contract

Each row represents one daily aggregate for a specific (date, agent, provider, model) tuple.

The request must include at least one row and at most 20,000 rows.

Effort Row Contract

Each effort row is one daily session-level aggregate for (date, agent). Counts are sessions, not requests or tokens — each session contributes its dominant effort level (ties → mixed). Levels are open-ended strings (minimal | low | medium | high | xhigh | max | ultra | mixed | future).

effortSnapshotComplete is accepted on the wire but does not delete unspecified dates — only provided keys are upserted.

Idempotency

Ingestion is idempotent. Token rows are upserted by the composite key:

date + agent + provider + model

Effort rows are upserted by:

date + agent

Non-decreasing on conflict. Both tables guard against prune erosion: a day is overwritten only when the incoming snapshot is more complete — larger totalTokens for tokens, or larger classifiedSessionCount + unclassifiedSessionCount for effort. Smaller re-parses from pruned logs are ignored. The whole incoming row wins together.

Cache Revalidation

After a successful upsert, the route revalidates the usage cache tag so the public usage page can pick up the new data (tokens and effort share this tag via getUsageProfile).

Ingesting From This Machine

The agent-usage CLI (a Rust collector in apps/cli) parses local agent logs and POSTs daily rows to the ingest endpoint.

pnpm usage:login   # once per server
pnpm usage:ingest

Sign in once per server (OAuth, admin account). Login follows AGENT_USAGE_URL, so a local dev server and production keep separate logins. Production tokens are stored in the Keychain; other servers keep theirs in a 0600 file in ~/.config/agent-usage, so rebuilding the CLI never triggers a Keychain password prompt. The installed collector is production only. Rows are sent with costUsd: null, and the route prices them after the model registry sync. The CLI emits token rows only; effort rows come from the AgentUsage app, a separate client.

Optional environment variables:

• AGENT_USAGE_URL overrides the endpoint (agent-usage ingest --url <URL> wins over it), e.g. https://blog.localhost/api/usage/ingest for the local dev server (defaults to VERCEL_PROJECT_PRODUCTION_URL, VERCEL_URL, then https://ruchern.dev)
• AGENT_USAGE_DRY_RUN=1 prints the payload without POSTing (same as agent-usage ingest --dry-run)

agent-usage auth status shows the stored sign-in for the current server without a network call.

To install the prebuilt collector and its LaunchAgent on a Mac without a checkout:

curl -fsSL https://github.com/ruchernchong/blog/releases/latest/download/install.sh | bash

agent-usage update installs the latest release in place after checking its SHA-256 (--check only reports the versions).

The CLI was renamed from usage-ingest. Each AGENT_USAGE_* variable falls back to its legacy USAGE_INGEST_* name when unset, so existing shells and .envrc files keep working.

The production database connection string stays inside the deployment environment. The local machine only sends the OAuth bearer and JSON rows.

Example Request

curl -X POST "https://ruchern.dev/api/usage/ingest" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "rows": [
      {
        "date": "2026-06-02",
        "agent": "claude",
        "provider": "anthropic",
        "model": "claude-sonnet-4-5",
        "inputTokens": 1200,
        "outputTokens": 420,
        "cacheReadTokens": 3000,
        "cacheWriteTokens": 250,
        "reasoningTokens": 0,
        "totalTokens": 5870,
        "costUsd": "0.012345",
        "messages": 3
      }
    ],
    "effortRows": [
      {
        "date": "2026-06-02",
        "agent": "claude",
        "levels": [{ "level": "high", "sessionCount": 2 }],
        "classifiedSessionCount": 2,
        "unclassifiedSessionCount": 1
      }
    ],
    "effortSnapshotComplete": true
  }'

Success Response

{
  "ok": true,
  "upserted": 1,
  "effortUpserted": 1,
  "syncRunId": "run_123"
}

effortUpserted is 0 when effortRows is omitted or empty.

Failure Modes

Source Files

• packages/usage/src/ingest.ts defines the wire schema.
• apps/cli parses, folds, and posts token rows.
• apps/web/src/app/api/usage/ingest/route.ts authenticates and upserts rows.
• apps/web/src/lib/queries/usage.ts performs the chunked upsert and usage profile aggregation.
• apps/web/src/schema/token-effort-usage.ts defines the token_effort_usage table.