Minimal Pi token-usage collection from GitHub Actions

Decision

Use one small, immutable usage summary per Actions run/job, upload it as a GitHub Actions artifact, and have a local importer pull completed runs with gh and insert idempotently into SQLite.

Do not make the shared artifact a live SQLite database. Do not upload Pi transcripts unless they are explicitly required: Pi’s JSON stream and session files can contain prompts, source, tool arguments, tool results, and model output.

If the data must survive GitHub’s artifact retention window, keep the same per-run records in S3 (preferred for scale) or a dedicated private repository. The local SQLite database remains the queryable system of record.

What Pi exposes

Pi’s official coding-agent documentation provides two useful formats:

A collector should define its metric before implementing it. For provider billing-like totals, count each completed provider request once, include nested tool/compaction usage when intended, and preserve provider/model/API and stop reason. At minimum, retain the numeric usage fields plus workflow metadata; retain raw JSONL only as a short-lived debugging artifact.

Suggested summary record:

{
  "schema": 1,
  "repo": "OWNER/REPO",
  "workflow": "...",
  "run_id": 123,
  "run_attempt": 1,
  "job": "...",
  "matrix_index": 0,
  "sha": "...",
  "created_at": "...",
  "pi_version": "...",
  "records": [
    {"provider":"...", "model":"...", "input":0, "output":0,
     "cacheRead":0, "cacheWrite":0, "totalTokens":0,
     "cost":{"input":0,"output":0,"cacheRead":0,"cacheWrite":0,"total":0}}
  ]
}

Use run_id + run_attempt + job + matrix_index as the event identity. GitHub documents that run_id stays the same across re-runs while run_attempt increments.

Minimal flow

  1. Run Pi in JSON mode in the job, directing stdout to a runner-temporary JSONL file. Use --no-session if a persistent Pi session is not needed.
  2. Parse terminal usage-bearing events into a summary; do not count message_update snapshots. Write the summary atomically to a normal, non-hidden file.
  3. Upload only the summary under a unique artifact name such as pi-usage-${{ github.run_id }}-${{ github.run_attempt }}-${{ github.job }}-${{ strategy.job-index }}. Upload it with if: ${{ always() }} if failed/cancelled jobs should be represented, while recognizing that a hard cancellation can prevent the post-step.
  4. Locally, enumerate completed runs with gh run list --json ..., then call gh run download <run-id> --name <unique-artifact> --dir <temporary-directory> for each run. Always pass the run ID: gh run download otherwise selects the latest artifact. Validate the JSON schema and insert with INSERT ... ON CONFLICT DO NOTHING.
  5. Track a cursor or imported identity in SQLite. Optionally fetch the artifact REST metadata and store its SHA-256 digest. Keep the downloaded archive outside an executable workspace and never execute its contents.

A practical SQLite table is one row per provider request (plus a runs table), keyed by (repo, run_id, run_attempt, job, matrix_index, ordinal). Store raw summary JSON in a column only if useful. Use integer token counts and decimal-safe cost storage (for example, scaled integer micros or text), rather than binary floating-point money.

Comparison

Destination Strengths Weaknesses and failure modes Fit
— — — —
Per-run JSON/JSONL GitHub artifact Smallest design; natural run isolation; no shared writer; artifact upload/download and digest support; raw evidence can be retained briefly Artifact expires; GitHub Actions read access is enough to retrieve it; many artifacts need enumeration; a failed/cancelled job may produce no post-step; raw Pi JSONL is sensitive Best starting point
Cumulative SQLite artifact/cache Convenient single download; local schema already exists; SQLite transactions are useful locally GitHub artifacts are not an append-only database; v4 artifacts are immutable and same-name multi-job uploads fail. A replacement requires a new artifact/delete-and-recreate. Caches cannot change in place, so every update needs a new key. Two runs restoring the same old DB can lose one another’s rows; a DB copied while WAL files are separate can be inconsistent Avoid as primary transport
Bot commit to private repository Durable Git history; easy git pull; no Actions artifact expiry; simple per-run JSON files are auditable Cross-repository authentication needs a suitable App/PAT/deploy key rather than assuming the source repo’s GITHUB_TOKEN; single-file or SQLite pushes race; history retains sensitive data after deleting the working copy; repository size and clone cost grow Good retention fallback for low volume
S3/object storage via OIDC Durable, scalable, lifecycle/versioning/Object Lock options; no long-lived AWS secret in GitHub; unique object keys remove writer contention; strong read-after-write consistency AWS/IAM/bucket setup and local AWS authentication; policy mistakes can expose or permit writes; storage/request cost; raw transcripts remain sensitive; concurrent PUTs to one key are last-writer-wins unless conditional writes are used Best durable backend when already using AWS

Details and risks

Artifacts

GitHub documents artifacts as data stored after a workflow completes and downloadable by users with repository read access. The upload action reports an artifact SHA-256 digest, and download validates it. Artifacts have a configurable retention period, bounded by repository/organization/enterprise policy; the documented default is 90 days and the maximum is 90 days for the action input.

Use a unique name per job/run rather than an overwrite. This is especially important for matrix jobs and retries. For local collection, query by explicit run ID and artifact name; the GitHub CLI warns that without a run ID it downloads the latest artifact.

Treat an artifact as untrusted input. Validate size, JSON shape, numeric ranges, and expected identity fields before importing. Do not use a Pi transcript as shell input. Upload a summary rather than prompts or tool output.

Cumulative SQLite artifact/cache

SQLite permits multiple readers but only one simultaneous write transaction. That is not enough to coordinate separate ephemeral runners: two runners can both download version N, each produce N+1, and the later upload hides the other’s row. GitHub Actions concurrency can serialize a writer, but it adds workflow scheduling semantics and can cancel or replace pending work; it is simpler to make every event independent.

If a SQLite file is transported anyway, close the connection and checkpoint it before upload, or use SQLite’s backup/VACUUM-into facilities. In WAL mode the -wal file is part of persistent state; separating it from the database can lose committed transactions or corrupt the copy. A live WAL database is also not a network-filesystem sharing protocol.

GitHub Actions cache is worse for retention: existing cache contents cannot be changed, prefix restores can select the most recent matching entry, caches can be read across eligible branch scopes, and GitHub warns that cache contents are unsigned and susceptible to poisoning. GitHub documents removal of entries not accessed for over seven days and repository storage/eviction limits. A usage database is archival data, not a dependency cache.

Bot commits

Prefer one immutable file per run, for example runs/YYYY/MM/<run_id>-<attempt>-<job>-<matrix>.json, rather than repeatedly rewriting usage.sqlite or totals.json. A local importer can clone/pull and process files by filename, then use the same idempotency key.

Give the job only the minimum repository permission it needs. GitHub recommends least-privilege GITHUB_TOKEN permissions. A token from the source repository generally does not grant write access to an unrelated private repository; use a narrowly scoped GitHub App installation token or other deliberately managed credential. Never commit API keys, Pi provider credentials, prompts containing secrets, or unredacted tool results.

Concurrent bot pushes to the same branch need a serialized writer, retry/rebase logic, or disjoint immutable refs/files with a merge process. Branch protection/rulesets can also reject direct pushes. A commit is durable, but Git history, forks, clones, and backups make deletion/retention materially harder than deleting an artifact.

S3 through OIDC

Configure GitHub’s OIDC provider and an AWS role with conditions that bind at least the audience and exact repository subject (and, where applicable, branch, workflow, or protected environment). Grant only s3:PutObject to a run-specific prefix; use a separate read/list role for local collection if possible. id-token: write only permits fetching the GitHub OIDC token; the AWS role policy is what grants S3 access.

Write immutable, content-addressable or run-addressed keys, for example:

pi-usage/OWNER/REPO/<workflow>/<run_id>/<attempt>/<job>-<matrix>.json

Do not maintain one mutable latest.json unless using a versioned manifest and an explicit compare-and-swap/conditional-write protocol. S3 strongly guarantees object read-after-write consistency and atomic per-key replacement, but it does not lock concurrent writers to one key; AWS documents last-writer-wins behavior and provides conditional writes to prevent accidental overwrite. Enable bucket-private access, encryption, lifecycle expiry, and optionally versioning/Object Lock according to the required retention policy.

The local collector can list/get the prefix, validate each object, and import it into SQLite. If list permission is intentionally omitted, maintain a separate trusted manifest or derive keys from GitHub run metadata; do not give the GitHub job permission to rewrite a shared manifest without concurrency control.

Recommendation by requirement

Official sources