Implement audit logging for OpenRouter API calls. Use when building compliance trails, debugging production issues, or tracking model usage. Triggers: 'openrouter audit', 'openrouter logging', 'audit trail openrouter', 'log openrouter requests'.
Instrucciones de origen · Vista previa de solo lectura
name
openrouter-audit-logging
description
Implement audit logging for OpenRouter API calls. Use when building compliance trails, debugging production issues, or tracking model usage. Triggers: 'openrouter audit', 'openrouter logging', 'audit trail openrouter', 'log openrouter requests'.
Every OpenRouter API call returns a generation ID and metadata that enables comprehensive audit logging. The generation endpoint (GET /api/v1/generation?id=) provides exact cost, token counts, provider used, and latency -- data that the initial response doesn't always include. This skill covers structured logging, cost tracking, PII redaction, and compliance-ready audit trails.
Prerequisites
An OpenRouter API key (sk-or-v1-...) exported as OPENROUTER_API_KEY — see the openrouter-install-auth skill for setup
Python 3.8+ with the OpenAI SDK and requests (pip install openai requests) — the audit wrapper fetches exact cost from the generation endpoint with requests
SQLite: the Python stdlib sqlite3 module writes the audit table; the sqlite3 CLI runs the Audit Queries against openrouter_audit.db
Optional: a SIEM destination (Splunk, Datadog, ELK) if you ship the structured JSON log lines downstream
Instructions
Export your key and wire audited_completion() from Core: Generation Metadata Retrieval — it hashes the prompt (SHA-256), times the call, and fetches exact cost via GET /api/v1/generation?id= after each request.
Create the append-only store with init_audit_db() per Structured Log Storage, then persist every AuditEntry with write_audit() — INSERT OR IGNORE keeps retries from double-writing a generation_id.
Run redact_pii() from PII Redaction Before Logging over any prompt preview before it touches a log: emails, phones, SSNs, card numbers, and sk-or-v1- keys are scrubbed, and raw prompts are never stored (hashes only).
Answer operational questions with the Audit Queries SQL: daily cost by model, error rate per model over the last 24 hours, and top spenders by user_id.
If the generation fetch 404s or total_cost comes back missing, apply the fixes in Error Handling (fetch within 30 minutes; retry after 1-2 seconds).
Harden per Enterprise Considerations: append-only storage (SQLite WAL, S3), retention policy (90 days operational, 7 years financial), and SIEM shipping.
f"Failed to fetch generation metadata for {response.id}"
id
float
"total_cost"
0
round
1
return
Structured Log Storage
import sqlite3
definit_audit_db(db_path: str = "openrouter_audit.db"):
"""Create append-only audit table."""
conn = sqlite3.connect(db_path)
conn.execute("""
CREATE TABLE IF NOT EXISTS audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp TEXT NOT NULL,
generation_id TEXT UNIQUE NOT NULL,
model_requested TEXT NOT NULL,
model_used TEXT NOT NULL,
prompt_tokens INTEGER,
completion_tokens INTEGER,
total_cost REAL,
latency_ms REAL,
status TEXT NOT NULL,
user_id TEXT,
prompt_hash TEXT,
error_code TEXT
)
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_audit_ts ON audit_log(timestamp)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id)")
conn.commit()
return conn
defwrite_audit(conn: sqlite3.Connection, entry: AuditEntry):
"""Write audit entry to SQLite (append-only)."""
conn.execute(
"""INSERT OR IGNORE INTO audit_log
(timestamp, generation_id, model_requested, model_used,
prompt_tokens, completion_tokens, total_cost, latency_ms,
status, user_id, prompt_hash, error_code)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(entry.timestamp, entry.generation_id, entry.model_requested,
entry.model_used, entry.prompt_tokens, entry.completion_tokens,
entry.total_cost, entry.latency_ms, entry.status, entry.user_id,
entry.prompt_hash, entry.error_code),
)
conn.commit()
PII Redaction Before Logging
import re
PII_PATTERNS = [
(r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b', '[EMAIL]'),
(r'\b\d{3}[-.]?\d{3}[-.]?\d{4}\b', '[PHONE]'),
(r'\b\d{3}-\d{2}-\d{4}\b', '[SSN]'),
(r'\bsk-or-v1-[a-zA-Z0-9]+\b', '[API_KEY]'),
(r'\b(?:\d{4}[- ]?){3}\d{4}\b', '[CARD]'),
]
defredact_pii(text: str) -> str:
"""Scrub PII from text before logging."""for pattern, replacement in PII_PATTERNS:
text = re.sub(pattern, replacement, text)
return text
Audit Queries
-- Daily cost by modelSELECTdate(timestamp) asday, model_used,
COUNT(*) as requests, SUM(total_cost) as cost
FROM audit_log GROUPBYday, model_used ORDERBYdayDESC, cost DESC;
-- Error rate by model (last 24h)SELECT model_requested, COUNT(*) as total,
SUM(CASEWHEN status ='error'THEN1ELSE0END) as errors,
ROUND(100.0*SUM(CASEWHEN status='error'THEN1ELSE0END) /COUNT(*), 1) as error_pct
FROM audit_log WHEREtimestamp> datetime('now', '-1 day')
GROUPBY model_requested;
-- Top spendersSELECT user_id, COUNT(*) as requests, SUM(total_cost) as total_cost
FROM audit_log GROUPBY user_id ORDERBY total_cost DESC LIMIT 10;
Output
One structured JSON AuditEntry per request: timestamp, generation_id, model_requested vs model_used, prompt/completion token counts, exact total_cost, latency_ms, status, user_id, and a 16-char prompt_hash
An append-only SQLite audit_log table (openrouter_audit.db) indexed on timestamp and user_id, protected against duplicate writes by INSERT OR IGNORE
SQL report rows from the Audit Queries: per-day per-model cost, 24-hour error percentage per model, and the top-10 spenders by user_id
Examples
Wrap a call with the JSONL AuditLogger variant from the references and read back the entry it appends:
result = audited_completion("user-123", "What is machine learning?")
# [Audit] user=user-123 tokens=97 latency=450ms
The corresponding line in audit.jsonl:
{"timestamp":"2026-03-17T10:00:00Z","user_id":"user-123","model":"openai/gpt-3.5-turbo","prompt_hash":"a1b2c3d4e5f6g7h8","prompt_preview":"What is machine learning?","prompt_tokens":12,"completion_tokens":85,"total_tokens":97,"status":"success","latency_ms":450,"generation_id":"gen-abc123"}
More worked examples: references/examples.md.
Error Handling
Error
Cause
Fix
Generation endpoint 404
Generation ID not found or too old
Fetch within 30 minutes of request
Duplicate generation_id
Retry wrote same request twice
Use INSERT OR IGNORE
Missing total_cost
Generation still processing
Retry fetch after 1-2 seconds
Auth 401 on generation fetch
Wrong API key for that generation
Use same key that made the request
Enterprise Considerations
Log to append-only storage (SQLite WAL mode, S3, or centralized logging) to prevent tampering
Hash prompts rather than logging raw content to satisfy data residency requirements
Set log retention policies (90 days for operational, 7 years for financial compliance)
Ship structured JSON logs to SIEM (Splunk, Datadog, ELK) for real-time alerting
Use user_id field to enable per-user cost attribution and abuse detection
Index generation_id for fast correlation with OpenRouter dashboard
