metrics_daily.spend_usd is the co-op-wide total and deliberately spans every
team, operators included — the numbers were already right. What was missing
was a way to tell how much of that total is the co-op's OWN usage, so a
member-facing chart could not be misread as member-only activity.
Adds metrics_daily.operator_spend_usd: the per-day portion of spend_usd that
belongs to the accounts in OPERATOR_EMAILS (teams aliased member:<email>, like
anyone else). NULL = not measured (never a confident 0), mirroring spend_usd.
- metrics_snapshot.py: operator_spend_by_day() + write it on snapshot/backfill
- app/main.py: expose it in metrics_series (so /broker/metrics and
/admin/metrics/series carry it), and add operator_spend /
operator_all_time_spend to /admin/overview totals as the explicit breakout
of total_spend / total_all_time_spend.
spend_usd and every existing column are untouched.
Thread the per-factor source through impact_for() instead of discarding it.
Numeric output (energy_wh, co2e_g) is byte-identical — this is a reporting
change, not a recalculation.
#6 persistence: additive columns tokens_measured, tokens_estimated and
factor_revision (a stable hash of the factor table) on metrics_daily, added via
the existing guarded ALTER TABLE idiom in both app/main.py and
metrics_snapshot.py. Existing rows keep NULL.
#4 API: metrics_series() selects the new columns and derives a per-row
provenance indicator (measured | mixed | estimated | none) from the split; both
/admin/metrics/series and /broker/metrics return it. NULL/unknown splits report
'none', never a confident 'measured'.
docs/energy-metrics.md documents the indicator, the new columns, and the
forward-only-vs-backfill behaviour.
The panel has always excluded operator/utility emails from member counts
(OPERATOR_EMAILS), but the metrics counted every row in the members table, so the
panel said 75 while every metric surface said 76. The one affected row is
info@inference.coop — the reading mailbox, not a person.
- metrics_snapshot.py now applies the same OPERATOR_EMAILS definition (same variable
name, same default) so the two cannot drift apart again. No backfill: history stays
as published and the daily job writes the corrected value going forward.
- admin_overview now also returns an `agents` list, built from the LiteLLM teams it
already fetches (anything not member:*), so bot and sandbox spend is visible rather
than structurally invisible. Kept out of member counts and member aggregates, like
the operator category.
The carbon column was 1000x too low: 2.9 gCO2e for 55 days and 14.35 kWh,
where 14.35 kWh at the factor table's own intensities must be ~2.9 kg. Energy
was right, so nothing looked broken unless you did the arithmetic — 938 Wh in a
day cannot be 0.2 g of CO2.
Adds a tripwire in impact_for(): if the implied grid intensity falls outside
5-500 gCO2e/kWh, it prints a warning rather than quietly returning numbers. The
factors themselves span 24-250, so a blend outside that band means a unit or
factor error, not a cleaner grid.
Two bugs from the first deploy, both caught by reading the numbers back:
1. Tokens were summed per raw `model`, which carries the UPSTREAM name
(openai/deepseek-v4.1-flash), while the factor table is keyed on our
provider aliases (greenpt/deepseek-v4.1-flash). Every model therefore
matched nothing. Now grouped on model_group, the same convention
aggregate_model_usage uses, with model as the fallback for old rows.
2. Every day was written as energy_wh = 0.0 / co2e_g = 0.0 — a false claim that
the co-op used no energy, and precisely the fail-open this module is written
to avoid. A day with no measured tokens now yields NULL for all three impact
columns, and impact_for() refuses to compute from an empty total.
The backfill is idempotent, so re-running it overwrote the zero rows.
Implements the approach Nathan settled on in co-op/support#26: per-token
estimates based on what GreenPT publishes, rather than waiting on the upstream
LiteLLM `impact` passthrough.
- New daily columns: out_tokens, energy_wh, co2e_g.
- Tokens come from OUR OWN spend logs (completion_tokens per model per day), so
member-visible impact does not depend on any client persisting an `impact`
object. GreenPT's metric counts OUTPUT tokens only — that is the unit — so
input tokens are excluded deliberately.
- Factors live in one table with a source per row. The greenpt/* rows are
MEASURED: fitted from live calls to their API across prompt sizes
(energy = overhead + marginal x output tokens, R2 >= 0.999; carbon from the
grid intensity their own emissions imply — ug/mWh and g/kWh are the same
ratio). Everything else falls back to a documented order-of-magnitude proxy,
so a day's total is never silently missing models.
- Migration: CREATE IF NOT EXISTS never adds columns, so both the script and
the app ALTER TABLE on connect.
Refining a factor is a data edit in IMPACT_FACTORS, not a code change.
The backfill's first real curve was wrong: built on welcome_sent_at it showed
34 members on 19 October, when the co-op had ~65. Most members were provisioned
before the portal sent welcome emails, so that field lags by dozens of people —
and a chart built on it would have misrepresented the co-op's growth.
Membership now comes from Listmonk's per-subscription subscription_created_at
on the members list, which the portal's nightly reconcile keeps mirroring
active members. That reproduces the real curve (13 members mid-September → 71
today). Past days are the cumulative joins of today's members — an honest floor,
documented as such, since members who left are absent.
The first live run recorded newsletter_subs = 0 — a wrong zero, the exact
fail-open this module documents against. Cause: Listmonk's subscriber record
carries status enabled/blocked, while the LIST membership carries
subscription_status confirmed/unconfirmed. Filtering on the subscriber's own
status matched nothing and returned a silent 0.
Now counts memberships whose subscription_status is confirmed (verified: 71,
matching the list), and returns NULL when the endpoint yields no rows at all —
"not measured" rather than "nobody is subscribed". The daily job is the
regression test: it must record 71, not 0.
The co-op had no longitudinal data: every number was an ad-hoc query, so
nothing compounded and no single source owned "how many members do we have,
and when did that change". That cost us a wrong figure in the newsletter ("six
new members" — actually 21; the six were forum joins).
New: metrics_snapshot.py writes one row per day into the portal's SQLite store
(the same members.db), and GET /admin/metrics/series returns them oldest-first
for the panel and dashboard to chart later.
- Cumulative columns (members_active, members_total, members_new,
newsletter_subs, matrix_space_members) are counts AS OF that day;
spend_usd/requests are THAT day's traffic.
- FAIL CLOSED PER METRIC: an unreadable source yields NULL, never a zero — a
zero reads as "nobody" and would bake a lie into a chart. Failures print to
stdout, and a scheduler task's stdout lands in the app log.
- Listmonk's cached subscriber_count is NOT trusted (it reads 142 for a list
holding 71): the count comes from paging the subscribers endpoint.
- Member growth comes from welcome_sent_at, the provisioning timestamp —
the closest thing the portal stores to a join date. Manual/founder members
have no welcome email, so backfilled days are a documented LOWER BOUND;
today's row carries the exact count.
- Runs as an in-app scheduler task (addons.scheduler, 03:15 UTC) rather than
an agent cron: member-facing plumbing should not depend on the agent VM.
- Backfill is idempotent; member growth is reconstructible back to
September and spend/requests back to the gateway's first request.
Charts land later, on the surfaces, once the shape of the series is reviewed.
inference-bot
portal-dev