Indie LLM Cost Meter Starter (CSV + GSheets + SQL)

Copy this header row into a CSV or your Google Sheet tab (A1):

event_id,timestamp,actor_type,provider,model,region,input_tokens,output_tokens,latency_ms,cost_usd,eval_score,confidence,job_id,customer_id,pii_flag,review_required,reviewer_id,decision,error_code

Field notes and defaults:

• event_id: UUIDv4. If missing, generate server-side.
• timestamp: RFC3339 UTC (e.g., 2026-05-01T18:04:23Z).
• actor_type: one of user|agent|human-reviewer.
• provider/model/region: normalized lowercase (see naming section).
• input_tokens/output_tokens: integers from provider usage objects when available.
• latency_ms: integer (round-trip).
• cost_usd: decimal; compute from model pricing if response didn’t include cost.
• eval_score/confidence: decimals 0–1 for internal quality checks (optional).
• job_id/customer_id: your attribution keys.
• pii_flag: boolean (true|false); set true if raw text contained PII (see redaction).
• review_required: boolean; true if routed to human.
• reviewer_id: ID/email handle for human reviewer (if applicable).
• decision: enum approved|rejected|edited|n/a.
• error_code: short code for 429, RATE_LIMIT, TIMEOUT, etc.

Mini example row (copy into your CSV to test ingestion):

0d9a7f9a-1f5f-42a0-9c2d-1c9b7d6e2a31,2026-05-01T18:04:23Z,agent,openai,gpt-5.4-mini,us-east-1,1240,318,1420,0.0021,0.78,0.92,job_8F3A,cus_12KD,false,false,,, 

• Create a Google Sheet named [LLM Cost Meter].
• Add a tab [TAB_NAME]=events and paste the header row in A1.
• Freeze row 1; enable filter view.
• Data validation (optional but helpful):
  • actor_type: list user,agent,human-reviewer.
  • decision: list approved,rejected,edited,n/a.
  • pii_flag and review_required: checkbox TRUE/FALSE.
• Format columns:
  • timestamp: Date time (GMT) display; keep raw values in RFC3339.
  • latency_ms, input_tokens, output_tokens: Number, 0 decimals.
  • cost_usd: Currency USD with 4–6 decimals.
• Create a “Config” tab with:
  • DAILY_CAP_USD in A2 → [50]
  • BURN_7D_CAP_USD in A3 → [200]
  • SLACK_WEBHOOK_URL in A4 → [https://hooks.slack.com/services/...] (or leave blank until ready)
  • SECRET_TOKEN in A5 → [YOUR_LONG_RANDOM_TOKEN]
• Get the Sheet ID: it’s the long string in the URL. Save as [SHEET_ID].

Paste this into Apps Script (Extensions → Apps Script). Replace [PLACEHOLDERS]. Deploy as Web App with “Anyone with the link” access.

const CONFIG = {
  SHEET_ID: '[SHEET_ID]',
  TAB_NAME: '[TAB_NAME]', // events
  SECRET_TOKEN: '[SECRET_TOKEN]'
};

function doPost(e) {
  try {
    const body = e.postData && e.postData.contents ? JSON.parse(e.postData.contents) : {};
    const token = e.parameter.token || body.token;
    if (CONFIG.SECRET_TOKEN && token !== CONFIG.SECRET_TOKEN) {
      return ContentService.createTextOutput(JSON.stringify({ ok:false, error:'unauthorized' })).setMimeType(ContentService.MimeType.JSON);
    }

    // Ensure all fields exist in header order
    const row = [
      body.event_id || Utilities.getUuid(),
      body.timestamp || new Date().toISOString(),
      body.actor_type || 'agent',
      body.provider || 'unknown',
      body.model || 'unknown',
      body.region || 'n/a',
      Number(body.input_tokens || 0),
      Number(body.output_tokens || 0),
      Number(body.latency_ms || 0),
      Number(body.cost_usd || 0),
      body.eval_score === undefined ? '' : Number(body.eval_score),
      body.confidence === undefined ? '' : Number(body.confidence),
      body.job_id || '',
      body.customer_id || '',
      body.pii_flag === true,
      body.review_required === true,
      body.reviewer_id || '',
      body.decision || 'n/a',
      body.error_code || ''
    ];

    const ss = SpreadsheetApp.openById(CONFIG.SHEET_ID);
    const sh = ss.getSheetByName(CONFIG.TAB_NAME);
    const lock = LockService.getScriptLock();
    lock.tryLock(5000);
    sh.getRange(sh.getLastRow()+1, 1, 1, row.length).setValues([row]);
    lock.releaseLock();

    return ContentService.createTextOutput(JSON.stringify({ ok:true })).setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({ ok:false, error:String(err) })).setMimeType(ContentService.MimeType.JSON);
  }
}

Notes:

• Quotas: Apps Script has execution and write limits; suitable up to a few requests/sec. If you outgrow it, move to the Parquet/DuckDB path below.
• Auth: Keep the token in the Config tab and in your server env (not in client code). Rotate if leaked.

Send events server-side after each LLM call. Example JSON payload (matches header order via field names):

{
  "token": "[SECRET_TOKEN]",
  "event_id": "[UUIDV4]",
  "timestamp": "2026-05-01T18:04:23Z",
  "actor_type": "agent",
  "provider": "openai",
  "model": "gpt-5.4-mini",
  "region": "us-east-1",
  "input_tokens": 1240,
  "output_tokens": 318,
  "latency_ms": 1420,
  "cost_usd": 0.0021,
  "eval_score": 0.78,
  "confidence": 0.92,
  "job_id": "job_8F3A",
  "customer_id": "cus_12KD",
  "pii_flag": false,
  "review_required": false,
  "reviewer_id": "",
  "decision": "n/a",
  "error_code": ""
}

cURL:

curl -X POST "[WEB_APP_URL]" \
  -H "Content-Type: application/json" \
  -d @payload.json

Node (fetch):

await fetch('[WEB_APP_URL]', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify(payload)
});

Build a minimal weekly digest without standing up BI.

• Data source: Connect Looker Studio to your [SHEET_URL] (Google Sheets connector) and select the events tab.
• Recommended fields (ensure numeric types):
  • tokens_total = input_tokens + output_tokens
  • $ per 1K tokens = cost_usd / (tokens_total/1000) (guard divide-by-zero)
• Starter report layout:
  • Scorecards: Today cost_usd (date filter = Today), Last 7 days cost_usd (Last 7 days), Last 30 days cost_usd (Last 30 days).
  • Time series: Daily cost_usd over last 30 days.
  • Tables: Top customer_id by cost_usd (Last 7 days); Top model by tokens_total.
  • Filter controls: provider, actor_type, pii_flag.
• Delivery: Schedule a weekly email export (Monday 08:00 UTC) to [TEAM_EMAILS].

Tip: For a true rolling 7‑day sum inside Looker Studio, set the report date range to “Last 7 days” and summarize by Day; for per‑customer rolling windows, use the DuckDB SQL below.

Use Parquet for cheap, portable storage and DuckDB for local SQL. Good for >50k rows/day or when Sheets hits limits.

1. Write events to Parquet (batch or stream). If you’re starting from CSV:

-- From a local CSV into Parquet
COPY (
  SELECT * FROM read_csv_auto('events.csv')
) TO 'data/events_2026-05-01.parquet' (FORMAT 'parquet');

2. Query all Parquet files in a folder or bucket:

-- Point at a folder (local or object storage mounted)
SELECT *
FROM read_parquet('data/events_*.parquet');

3. Daily totals:

WITH base AS (
  SELECT
    CAST(date_trunc('day', to_timestamp(timestamp)) AS DATE) AS day,
    cost_usd,
    input_tokens,
    output_tokens,
    provider
  FROM read_parquet('data/events_*.parquet')
)
SELECT
  day,
  provider,
  SUM(cost_usd)                           AS cost_usd,
  SUM(input_tokens)                       AS in_tokens,
  SUM(output_tokens)                      AS out_tokens,
  SUM(input_tokens+output_tokens)         AS tokens_total
FROM base
GROUP BY 1,2
ORDER BY 1,2;

4. Per‑customer totals (last 7 days):

WITH base AS (
  SELECT
    CAST(date_trunc('day', to_timestamp(timestamp)) AS DATE) AS day,
    customer_id,
    cost_usd
  FROM read_parquet('data/events_*.parquet')
  WHERE day >= current_date - INTERVAL 7 DAY
)
SELECT customer_id, SUM(cost_usd) AS cost_7d
FROM base
GROUP BY 1
ORDER BY cost_7d DESC;

5. Rolling 7‑day burn per customer (full history):

WITH base AS (
  SELECT
    CAST(date_trunc('day', to_timestamp(timestamp)) AS DATE) AS day,
    customer_id,
    SUM(cost_usd) AS daily_cost
  FROM read_parquet('data/events_*.parquet')
  GROUP BY 1,2
)
SELECT
  customer_id,
  day,
  daily_cost,
  SUM(daily_cost) OVER (
    PARTITION BY customer_id ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS burn_7d
FROM base
ORDER BY customer_id, day;

6. Export aggregates for reporting/alerts:

COPY (
  SELECT day, SUM(cost_usd) AS cost_usd
  FROM (
    SELECT CAST(date_trunc('day', to_timestamp(timestamp)) AS DATE) AS day, cost_usd
    FROM read_parquet('data/events_*.parquet')
  )
  GROUP BY 1 ORDER BY 1
) TO 'out/daily_cost.csv' WITH (HEADER, DELIMITER ',');

Run via duckdb -c "<SQL>" in cron, or use a .duckdb database and a scheduled job.

Trigger Slack when caps are crossed. You can compute today/7‑day spend in Sheets (pivot) or via the DuckDB queries above, then call the webhook.

Config placeholders:

• [SLACK_WEBHOOK_URL]
• [DAILY_CAP_USD]
• [BURN_7D_CAP_USD]

Node example:

import fetch from 'node-fetch';

async function notifySlack({ todayCost, burn7d }) {
  const url = process.env.SLACK_WEBHOOK_URL || '[SLACK_WEBHOOK_URL]';
  const blocks = [
    { type: 'header', text: { type: 'plain_text', text: 'LLM Spend Alert' } },
    { type: 'section', text: { type: 'mrkdwn', text: `• Today: $${todayCost.toFixed(4)} (cap $[DAILY_CAP_USD])\n• Last 7 days: $${burn7d.toFixed(2)} (cap $[BURN_7D_CAP_USD])` } },
    { type: 'context', elements: [{ type: 'mrkdwn', text: 'Provider caps + degrade rules are active. See dashboard for details.' }]}
  ];
  await fetch(url, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ blocks }) });
}

cURL payload (simple text):

curl -X POST -H 'Content-Type: application/json' \
  -d '{"text":"LLM Spend Alert: Today=$[TODAY_COST] (cap $[DAILY_CAP_USD]); 7D=$[BURN_7D] (cap $[BURN_7D_CAP_USD])"}' \
  [SLACK_WEBHOOK_URL]

When caps hit, degrade instead of failing hard. Keep it simple and reversible.

Pseudocode (server-side guard):

const DAILY_CAP_USD = Number(process.env.DAILY_CAP_USD || '[DAILY_CAP_USD]');
const BURN_7D_CAP_USD = Number(process.env.BURN_7D_CAP_USD || '[BURN_7D_CAP_USD]');

async function chooseRoute(ctx) {
  const { todayCost, burn7d } = await getSpendStats();
  if (todayCost > DAILY_CAP_USD || burn7d > BURN_7D_CAP_USD) {
    // Degraded mode
    if (ctx.mustHaveAnswer === false) return { route: 'queue', until: '+2h' };
    if (ctx.canUseCheaper) return { route: 'model', model: '[CHEAPER_MODEL_ID]' };
    return { route: 'human_review', reviewer_id: '[REVIEWER_ID]' };
  }
  return { route: 'default', model: ctx.model };
}

Log the final route in decision and set review_required=true when handing to a human.

Use short, predictable values so your SQL and dashboards stay portable.

• provider: openai|anthropic|google|[other]
• model: vendor identifier in lowercase, e.g., gpt-5.4-mini, claude-3.7-sonnet, gemini-2.2-flash.
• region: cloud region or global if unknown, e.g., us-east-1, europe-west4.
• actor_type: user|agent|human-reviewer.
• event_id: UUIDv4 (server-generated if not present).
• timestamp: RFC3339 UTC (always store UTC).
• error_code: short codes like 429, RATE_LIMIT, TIMEOUT, PROVIDER_DOWN.

Folder layout (Parquet path suggestion):

/parquet/
  provider=openai/yyyymmdd/events_*.parquet
  provider=anthropic/yyyymmdd/events_*.parquet

Partitioning by provider/day keeps queries fast and cheap.

Default indie policy you can adopt and tune:

• Retention windows:
  • Hot rows: [30] days kept in Sheets for quick ops.
  • Warm archive: [180] days in Parquet (object storage or disk).
• Redaction:
  • Never store raw prompts/completions in this meter. If needed, hash (SHA-256) and store only hashes.
  • Set pii_flag=true when upstream detects PII; exclude such rows from public dashboards.
  • If you must keep text for review, store it in a separate secured bucket with short TTL and row-level ACL.
• Access:
  • Only the service account writes to Sheets; humans view via Looker Studio.
  • Rotate [SECRET_TOKEN] quarterly or on incident.
• Backups:
  • Nightly export of the events tab to CSV → Parquet archive job.
• Deletion:
  • Hard-delete rows older than [RETENTION_SHORT_DAYS] from Sheets; expire Parquet after [RETENTION_LONG_DAYS] using bucket lifecycle rules.

Lightweight mapping to oversight/audit concepts (the Act is generally applicable on August 2, 2026):

• Evidence log: event_id, timestamp, provider, model, latency_ms, error_code support traceability.
• Human oversight: review_required, reviewer_id, and decision create a clear reviewer trail.
• Risk controls: daily cap + rolling 7‑day alerts show proportionate risk management; keep your alert history.

Add a short SOP: when decision='rejected', re-run in degraded mode or escalate to human with review_required=true.

If you prefer a hosted path:

• PostHog capture:
  • Event name: llm_request
  • Properties: map fields 1:1 (provider, model, input_tokens, output_tokens, latency_ms, cost_usd, customer_id, etc.).
• Metering (e.g., Metronome):
  • Define meters: cost_usd (sum), tokens_total (sum), grouped by customer_id.
  • Alerts: daily and 7‑day thresholds; webhook to Slack or your ops endpoint.
• Governance:
  • Use project-level PII flags; avoid storing text; forward only counters/costs.

You can keep the CSV/Parquet schema identical so you can move between stacks without rewrites.

Prove it works in 10 minutes:

1. Post two sample events (copy the payload example, tweak cost_usd to 0.05 and 3.10).
2. In Sheets, confirm both rows append and numeric columns cast correctly.
3. In Looker Studio, build a quick scorecard for Last 7 days cost_usd and confirm it matches your test total.
4. Run the DuckDB daily totals query against a Parquet export and verify the same total.
5. Temporarily set [DAILY_CAP_USD]=0.01 and run the Slack notifier with today’s sum to trigger an alert. Reset after the test.