# Usage reporting

`machinery/report_usage.py` runs as the `cswap-report` service, snapshots `cswap list --json`
every 15 minutes, and writes to `~/.omni-cswap/usage/`:

- **`usage.jsonl`** — append-only, one JSON object per account per tick. Raw audit trail.
- **`usage.db`** — SQLite, table `usage_snapshots`, keyed `(ts, machine, account)`.

Both sinks are local and credential-free, so the installer is portable. Shipping to a central
database is a deliberate opt-in — see *Remote push* below.

## What this data can and cannot tell you

You asked for two things. One is available; the other isn't, and that distinction is not a
limitation of this script — it's the shape of what cswap exposes.

**Available.** cswap reports each account's **utilization as a percentage** of three rate-limit
windows — 5-hour, 7-day, and per-model (currently Fable) — plus each window's reset timestamp,
the account's email/org, and which slot was the active default. Sampled every 15 min and stamped
with the machine, that supports: which machine leaned on which account, when accounts approached
their caps, how evenly load spread across the pool, and how often rotation fired.

**Not available.**

- **Token counts and cost.** cswap reports percentages, not tokens and not dollars. There is no
  factor to convert one to the other — a percentage is against an opaque, model-weighted limit.
  Anything claiming to be a token count derived from these numbers would be fabricated.
- **Per-session or per-prompt attribution.** The snapshot is per *account*, not per session. You
  can see account 3 went from 40% → 55% between two ticks; you cannot see which session did it.
  Correlating `~/.omni-cswap/shim.log` (session → account, timestamped) with snapshot deltas gets
  you a rough attribution, but it's inference, not measurement — several sessions may share an
  account within one interval.
- **Per-person attribution.** Not applicable here: one operator. If that ever changes, this data
  still can't answer it — the account is the only identity in the snapshot.

Sampling caveat: 15-minute snapshots of a 5-hour window are coarse. A spike that starts and
resets between two ticks is invisible. Lower `OMNI_REPORT_INTERVAL` if you need finer resolution;
don't go below ~5 min, since cswap serves cached usage and you'd just record the same row twice.

## Schema

| column | type | notes |
|---|---|---|
| `ts` | TEXT | UTC `%Y-%m-%dT%H:%M:%SZ`, snapshot time |
| `machine` | TEXT | `OMNI_MACHINE_ID` or hostname |
| `account` | TEXT | cswap slot number |
| `email`, `org_name`, `org_uuid` | TEXT | account identity |
| `is_active` | INT | 1 if this was the active default at snapshot time |
| `usage_status` | TEXT | `ok`, `relogin_required`, … |
| `five_hour_pct`, `five_hour_resets` | REAL, TEXT | 5h window |
| `seven_day_pct`, `seven_day_resets` | REAL, TEXT | 7d window |
| `scoped_json` | TEXT | JSON array of per-model windows, e.g. `[{"name":"Fable","pct":43.0,…}]` |

`NULL` percentages are normal: an account with `usage_status != 'ok'` (dead refresh token) reports
no usage at all.

## Queries

```sql
-- current state, one row per account
SELECT account, email, five_hour_pct, seven_day_pct, usage_status
FROM usage_snapshots WHERE ts = (SELECT MAX(ts) FROM usage_snapshots) ORDER BY account;

-- peak 7d utilization per account per day
SELECT substr(ts,1,10) AS day, account, MAX(seven_day_pct) AS peak_7d
FROM usage_snapshots GROUP BY day, account ORDER BY day DESC, peak_7d DESC;

-- how often each account was the active default (rotation balance)
SELECT machine, account, SUM(is_active) AS ticks_active, COUNT(*) AS ticks_seen
FROM usage_snapshots GROUP BY machine, account ORDER BY machine, ticks_active DESC;

-- Fable per-model utilization, pulled out of the scoped JSON
SELECT ts, account, json_extract(value,'$.pct') AS fable_pct
FROM usage_snapshots, json_each(scoped_json)
WHERE json_extract(value,'$.name') = 'Fable'
ORDER BY ts DESC LIMIT 50;

-- accounts that hit the ceiling (rotation pressure)
SELECT account, COUNT(*) FROM usage_snapshots WHERE five_hour_pct >= 90 GROUP BY account;
```

## Remote push

`push_to_remote(rows)` in `report_usage.py` is a no-op. To ship to Postgres:

1. Create the table (mirror the schema above; `PRIMARY KEY (ts, machine, account)`).
2. Implement the function:

```python
def push_to_remote(rows):
    dsn = os.environ.get("OMNI_REPORT_DSN")
    if not dsn:
        return
    import psycopg
    with psycopg.connect(dsn) as con, con.cursor() as cur:
        cur.executemany(
            "INSERT INTO usage_snapshots (ts,machine,account,email,org_name,org_uuid,"
            "is_active,usage_status,five_hour_pct,five_hour_resets,seven_day_pct,"
            "seven_day_resets,scoped_json) VALUES (%(ts)s,%(machine)s,%(account)s,%(email)s,"
            "%(org_name)s,%(org_uuid)s,%(is_active)s,%(usage_status)s,%(five_hour_pct)s,"
            "%(five_hour_resets)s,%(seven_day_pct)s,%(seven_day_resets)s,%(scoped_json)s) "
            "ON CONFLICT (ts, machine, account) DO NOTHING", rows)
```

3. Provide `OMNI_REPORT_DSN` and `psycopg` to the service. `python3` on the host is the system
   interpreter with no psycopg — point the service at a venv that has it, or run the reporter via
   `uv run --with psycopg`.
4. Keep it best-effort. The caller already swallows exceptions so a reporting outage can't take
   down the loop — preserve that. Local sinks are written *before* the push, so nothing is lost if
   the DB is down; a backfill from `usage.jsonl` is a `PRIMARY KEY … DO NOTHING` replay.

Don't hardcode the DSN in the repo — this script is copied to every machine you provision.