Speakr is a personal, self-hosted web application designed for transcribing audio recordings
Python
"""System Statistics overview for the admin dashboard.
One read for the whole tab: headline numbers for a range of days with the
range before it for comparison, recordings per day by source, processing
time and failure rate, model usage per day by operation, cost per model,
twelve months of cost, the latest failed recordings and one row per user.
Days are UTC calendar days, like the usage tables they read.
"""
import os
from collections import defaultdict
from datetime import date, datetime, timedelta
from statistics import median
from sqlalchemy import case, extract
from src.database import db
from src.models import Recording, TokenUsage, TranscriptionUsage, User
from src.services.token_tracking import token_tracker
RANGES = (7, 30, 90)
SOURCES = ('upload', 'recording', 'merge', 'other')
_SOURCE_MAP = {'upload': 'upload', 'auto_process': 'upload', 'recording': 'recording',
'recording_session': 'recording', 'merge': 'merge'}
def _source(value):
return _SOURCE_MAP.get(value or 'upload', 'other')
def _days(start, end):
d = start
while d <= end:
yield d
d += timedelta(days=1)
def _usage_window(start, end):
"""Tokens, cost and transcription seconds between two dates, inclusive."""
tok = db.session.query(TokenUsage.operation_type, db.func.sum(TokenUsage.total_tokens),
db.func.sum(TokenUsage.cost), db.func.sum(TokenUsage.request_count),
db.func.sum(TokenUsage.cached_tokens), db.func.sum(TokenUsage.cache_write_tokens)) \
.filter(TokenUsage.date >= start, TokenUsage.date <= end).group_by(TokenUsage.operation_type).all()
tr = db.session.query(db.func.sum(TranscriptionUsage.audio_duration_seconds),
db.func.sum(TranscriptionUsage.estimated_cost)) \
.filter(TranscriptionUsage.date >= start, TranscriptionUsage.date <= end).first()
out = {'llm_cost': 0.0, 'embedding_cost': 0.0, 'ai_requests': 0, 'cache_reads': 0, 'cache_writes': 0,
'transcription_seconds': int(tr[0] or 0), 'transcription_cost': float(tr[1] or 0)}
for op, _tokens, cost, requests, cached, written in tok:
out['cache_reads'] += int(cached or 0)
out['cache_writes'] += int(written or 0)
if token_tracker.is_embedding_op(op):
out['embedding_cost'] += float(cost or 0)
else:
out['llm_cost'] += float(cost or 0)
out['ai_requests'] += int(requests or 0)
out['cost'] = round(out['llm_cost'] + out['embedding_cost'] + out['transcription_cost'], 6)
return out
def _recordings_window(start, end):
"""Recordings created between two dates: count and active users."""
lo = datetime.combine(start, datetime.min.time())
hi = datetime.combine(end + timedelta(days=1), datetime.min.time())
count, users = db.session.query(db.func.count(Recording.id), db.func.count(db.distinct(Recording.user_id))) \
.filter(Recording.created_at >= lo, Recording.created_at < hi).first()
return int(count or 0), int(users or 0)
def _monthly_cost(months, today):
y, m = today.year, today.month
keys = []
for _ in range(months):
keys.append((y, m))
y, m = (y, m - 1) if m > 1 else (y - 1, 12)
keys.reverse()
start = date(*keys[0], 1)
rows = {k: {'year': k[0], 'month': k[1], 'llm_cost': 0.0, 'embedding_cost': 0.0,
'transcription_cost': 0.0, 'llm_tokens': 0, 'embedding_tokens': 0, 'minutes': 0}
for k in keys}
y_, m_ = extract('year', TokenUsage.date), extract('month', TokenUsage.date)
for yy, mm, op, tokens, cost in db.session.query(
y_, m_, TokenUsage.operation_type, db.func.sum(TokenUsage.total_tokens), db.func.sum(TokenUsage.cost)) \
.filter(TokenUsage.date >= start).group_by(y_, m_, TokenUsage.operation_type):
row = rows.get((int(yy), int(mm)))
if row is None:
continue
kind = 'embedding' if token_tracker.is_embedding_op(op) else 'llm'
row[f'{kind}_cost'] += float(cost or 0)
row[f'{kind}_tokens'] += int(tokens or 0)
ty, tm = extract('year', TranscriptionUsage.date), extract('month', TranscriptionUsage.date)
for yy, mm, seconds, cost in db.session.query(
ty, tm, db.func.sum(TranscriptionUsage.audio_duration_seconds), db.func.sum(TranscriptionUsage.estimated_cost)) \
.filter(TranscriptionUsage.date >= start).group_by(ty, tm):
row = rows.get((int(yy), int(mm)))
if row is not None:
row['transcription_cost'] += float(cost or 0)
row['minutes'] += int(seconds or 0) // 60
return [rows[k] for k in keys]
def build_overview(days=30, today=None):
days = days if days in RANGES else 30
today = today or date.today()
start = today - timedelta(days=days - 1)
prev_end = start - timedelta(days=1)
prev_start = prev_end - timedelta(days=days - 1)
lo = datetime.combine(start, datetime.min.time())
hi = datetime.combine(today + timedelta(days=1), datetime.min.time())
# ---- headline numbers, this range against the one before it
cur, prev = _usage_window(start, today), _usage_window(prev_start, prev_end)
rec_cur, users_cur = _recordings_window(start, today)
rec_prev, users_prev = _recordings_window(prev_start, prev_end)
storage = db.session.query(db.func.coalesce(db.func.sum(Recording.file_size), 0)) \
.filter(Recording.audio_deleted_at.is_(None)).scalar() or 0
# ---- recordings per day by source, processing time, failure rate
daily_rec = {d.isoformat(): {'date': d.isoformat(), **{s: 0 for s in SOURCES}, 'failed': 0}
for d in _days(start, today)}
durations = []
failed = 0
rows = db.session.query(Recording.created_at, Recording.processing_source, Recording.status,
Recording.processing_time_seconds) \
.filter(Recording.created_at >= lo, Recording.created_at < hi).all()
for created, source, status, seconds in rows:
day = daily_rec.get(created.date().isoformat())
if day is None:
continue
day[_source(source)] += 1
if status == 'FAILED':
day['failed'] += 1
failed += 1
if status == 'COMPLETED' and seconds:
durations.append(seconds)
# ---- model usage per day by operation, transcription minutes per day
# Embedding tokens run an order of magnitude above the rest, so they are
# a series of their own; 'operations' holds the language-model calls.
daily_use = {d.isoformat(): {'date': d.isoformat(), 'operations': {}, 'embedding_tokens': 0, 'minutes': 0}
for d in _days(start, today)}
operations = defaultdict(int)
for d, op, tokens in db.session.query(TokenUsage.date, TokenUsage.operation_type, db.func.sum(TokenUsage.total_tokens)) \
.filter(TokenUsage.date >= start, TokenUsage.date <= today) \
.group_by(TokenUsage.date, TokenUsage.operation_type):
row = daily_use.get(d.isoformat())
if row is None:
continue
if token_tracker.is_embedding_op(op):
row['embedding_tokens'] += int(tokens or 0)
else:
row['operations'][op] = row['operations'].get(op, 0) + int(tokens or 0)
operations[op] += int(tokens or 0)
for d, seconds in db.session.query(TranscriptionUsage.date, db.func.sum(TranscriptionUsage.audio_duration_seconds)) \
.filter(TranscriptionUsage.date >= start, TranscriptionUsage.date <= today).group_by(TranscriptionUsage.date):
row = daily_use.get(d.isoformat())
if row is not None:
row['minutes'] += round((seconds or 0) / 60, 1)
# ---- cost per model in the range
by_model = []
for model, op, tokens, requests, cost in db.session.query(
TokenUsage.model_name, TokenUsage.operation_type, db.func.sum(TokenUsage.total_tokens),
db.func.sum(TokenUsage.request_count), db.func.sum(TokenUsage.cost)) \
.filter(TokenUsage.date >= start, TokenUsage.date <= today) \
.group_by(TokenUsage.model_name, TokenUsage.operation_type):
by_model.append(('embedding' if token_tracker.is_embedding_op(op) else 'llm', model or '', op,
int(tokens or 0), int(requests or 0), float(cost or 0)))
models = {}
for kind, model, _op, tokens, requests, cost in by_model:
row = models.setdefault((kind, model), {'kind': kind, 'model': model, 'tokens': 0, 'minutes': 0,
'requests': 0, 'cost': 0.0})
row['tokens'] += tokens
row['requests'] += requests
row['cost'] += cost
for model, connector, seconds, requests, cost in db.session.query(
TranscriptionUsage.model_name, TranscriptionUsage.connector_type,
db.func.sum(TranscriptionUsage.audio_duration_seconds), db.func.sum(TranscriptionUsage.request_count),
db.func.sum(TranscriptionUsage.estimated_cost)) \
.filter(TranscriptionUsage.date >= start, TranscriptionUsage.date <= today) \
.group_by(TranscriptionUsage.model_name, TranscriptionUsage.connector_type):
name = model or connector or ''
row = models.setdefault(('transcription', name), {'kind': 'transcription', 'model': name, 'tokens': 0,
'minutes': 0, 'requests': 0, 'cost': 0.0})
row['minutes'] += round((seconds or 0) / 60, 1)
row['requests'] += int(requests or 0)
row['cost'] += float(cost or 0)
model_rows = sorted(models.values(), key=lambda r: (-r['cost'], -r['tokens'], -r['minutes'], r['model']))
# ---- latest failed recordings
failed_recent = [
{'id': r.id, 'title': r.title or r.original_filename or f'#{r.id}', 'username': username,
'created_at': r.created_at.isoformat() + 'Z' if r.created_at else None,
'error': (r.error_message or '')[:300]}
for r, username in db.session.query(Recording, User.username).join(User, User.id == Recording.user_id)
.filter(Recording.status == 'FAILED').order_by(Recording.created_at.desc()).limit(5)
]
# ---- one row per user
month_start = today.replace(day=1)
rec_by_user = {uid: (int(n or 0), int(size or 0), last) for uid, n, size, last in db.session.query(
Recording.user_id, db.func.count(Recording.id),
db.func.coalesce(db.func.sum(case((Recording.audio_deleted_at.is_(None), Recording.file_size), else_=0)), 0),
db.func.max(Recording.created_at)).group_by(Recording.user_id)}
tok_by_user = dict(db.session.query(TokenUsage.user_id, db.func.sum(TokenUsage.total_tokens))
.filter(TokenUsage.date >= month_start, TokenUsage.date <= today).group_by(TokenUsage.user_id).all())
cost_by_user = defaultdict(float)
for uid, cost in db.session.query(TokenUsage.user_id, db.func.sum(TokenUsage.cost)) \
.filter(TokenUsage.date >= month_start, TokenUsage.date <= today).group_by(TokenUsage.user_id):
cost_by_user[uid] += float(cost or 0)
sec_by_user = {}
for uid, seconds, cost in db.session.query(TranscriptionUsage.user_id, db.func.sum(TranscriptionUsage.audio_duration_seconds),
db.func.sum(TranscriptionUsage.estimated_cost)) \
.filter(TranscriptionUsage.date >= month_start, TranscriptionUsage.date <= today) \
.group_by(TranscriptionUsage.user_id):
sec_by_user[uid] = int(seconds or 0)
cost_by_user[uid] += float(cost or 0)
users = []
for u in User.query.order_by(User.username).all():
n, size, last = rec_by_user.get(u.id, (0, 0, None))
tokens = int(tok_by_user.get(u.id) or 0)
seconds = sec_by_user.get(u.id, 0)
tb, sb = u.monthly_token_budget, u.monthly_transcription_budget
users.append({
'id': u.id, 'username': u.username, 'recordings': n, 'storage': size,
'storage_quota': u.storage_quota_mb * 1024 * 1024 if u.storage_quota_mb else None,
'storage_pct': round(size / (u.storage_quota_mb * 1024 * 1024) * 100, 1) if u.storage_quota_mb else None,
'last_recording_at': last.isoformat() + 'Z' if last else None,
'tokens_month': tokens, 'token_budget': tb,
'token_pct': round(tokens / tb * 100, 1) if tb else None,
'minutes_month': seconds // 60, 'minutes_budget': (sb // 60) if sb else None,
'minutes_pct': round(seconds / sb * 100, 1) if sb else None,
'cost_month': round(cost_by_user.get(u.id, 0.0), 6),
})
from src.services.embeddings import embeddings_are_local
return {
'days': days,
'start': start.isoformat(),
'end': today.isoformat(),
'overview': {
'recordings': {'current': rec_cur, 'previous': rec_prev},
'audio_minutes': {'current': cur['transcription_seconds'] // 60, 'previous': prev['transcription_seconds'] // 60},
'ai_cost': {'current': cur['cost'], 'previous': prev['cost']},
'ai_requests': {'current': cur['ai_requests'], 'previous': prev['ai_requests']},
'active_users': {'current': users_cur, 'previous': users_prev, 'total': len(users)},
'storage': int(storage),
},
'activity': {
'daily': list(daily_rec.values()),
'created': len(rows),
'failed': failed,
'failure_rate': round(failed / len(rows) * 100, 1) if rows else None,
'median_processing_seconds': int(median(durations)) if durations else None,
},
'usage': {
'daily': list(daily_use.values()),
'operations': [op for op, _ in sorted(operations.items(), key=lambda kv: -kv[1])],
'by_model': model_rows,
# Prompt tokens served from a prefix cache, as the backend reports them.
'cache_reads': cur['cache_reads'],
'cache_writes': cur['cache_writes'],
'monthly': _monthly_cost(12, today),
'embeddings_local': embeddings_are_local(),
'models_local': os.environ.get('MODELS_ARE_LOCAL', 'false').lower() == 'true',
},
'failed_recent': failed_recent,
'users': users,
}