Coverage for .venv/lib/python3.13/site-packages/litellm/proxy/spend_tracking/spend_management_endpoints.py: 26%
1535 statements
« prev ^ index » next coverage.py v7.15.2, created at 2026-10-10 12:01 +0000
1#### SPEND MANAGEMENT #####
2import collections
3import json
4import os
5from collections.abc import Mapping, Sequence
6from dataclasses import dataclass
7from datetime import date, datetime, timedelta, timezone
8from itertools import groupby
9from types import MappingProxyType
10from typing import (
11 TYPE_CHECKING,
12 Annotated,
13 Any,
14 Final,
15 Literal,
16 NamedTuple,
17 Protocol,
18 TypeAlias,
19 TypedDict,
20 TypeVar,
21 cast, # noqa: TID251 # custom-logger and cold-storage payloads are untyped JSON
22)
24import fastapi
25from fastapi import APIRouter, Depends, HTTPException, Request, Response, status
26from pydantic import TypeAdapter
27from typing_extensions import ReadOnly, assert_never
29import litellm
30from litellm._logging import verbose_proxy_logger
31from litellm.constants import (
32 EMPTY_MAPPING,
33 LITELLM_TRUNCATED_PAYLOAD_FIELD,
34 LITTELM_INTERNAL_HEALTH_SERVICE_ACCOUNT_NAME,
35 SPEND_CAPTURE_RATE_MAX_RANGE_DAYS,
36)
37from litellm.litellm_core_utils.classifier_logging import classifier_audit_fields, classifier_input_snapshot
38from litellm.proxy._types import *
39from litellm.proxy._types import ProviderBudgetResponse, ProviderBudgetResponseObject
40from litellm.proxy.auth.user_api_key_auth import user_api_key_auth
41from litellm.proxy.litellm_pre_call_utils import LiteLLMProxyRequestSetup
42from litellm.proxy.spend_tracking.spend_capture_rate import (
43 ProviderBillingCredentialMissing,
44 ProviderBillingRequestFailed,
45 capture_rate_report,
46)
48# NOTE: Avoid module-level import from common_utils: proxy_server imports this
49# module while common_utils may pull proxy_server during init, which can leave
50# those names undefined. Import the helpers locally where they are used.
51from litellm.proxy.spend_tracking.spend_tracking_utils import (
52 get_spend_by_team,
53 get_spend_by_team_and_customer,
54)
55from litellm.proxy.utils import handle_exception_on_proxy
56from litellm.repositories.prisma_protocols import TableActions
57from litellm.repositories.table_repositories import SpendLogsRepository
58from litellm.repositories.team_repository import TeamRepository
59from litellm.repositories.verification_token_repository import (
60 VerificationTokenRepository,
61)
62from litellm.types.proxy.spend_capture_rate import CaptureRateReport, SpendCaptureProvider
64if TYPE_CHECKING: 64 ↛ 65line 64 didn't jump to line 65 because the condition on line 64 was never true
65 from prisma import models as prisma_models
67 from litellm.proxy.proxy_server import PrismaClient
68 from litellm.proxy.spend_tracking.cold_storage_handler import ColdStorageHandler
69else:
70 PrismaClient = Any
72router: Final = APIRouter()
74SPEND_LOGS_PAGINATION_COUNT_CAP: Final = 10000
76_SESSION_KEY_EXPR: Final = "COALESCE(NULLIF(session_id, ''), request_id)"
77_SESSION_GROUP_KEY_SQL: Final = f"{_SESSION_KEY_EXPR}, api_key"
78_MCP_CALL_TYPES_SQL: Final = "('call_mcp_tool', 'list_mcp_tools')"
79_AGENT_CALL_TYPE_SQL: Final = "'asend_message'"
80_BATCH_CALL_TYPES_SQL: Final = "('acreate_batch', 'create_batch', 'aretrieve_batch', 'retrieve_batch')"
81_SPAN_TYPE_SQL_CONDITIONS: Final[Mapping[str, str]] = MappingProxyType(
82 {
83 "mcp": f"call_type IN {_MCP_CALL_TYPES_SQL}",
84 "agent": f"call_type = {_AGENT_CALL_TYPE_SQL}",
85 "batch": f"call_type IN {_BATCH_CALL_TYPES_SQL}",
86 "llm": (
87 f"(call_type NOT IN {_MCP_CALL_TYPES_SQL} AND call_type != {_AGENT_CALL_TYPE_SQL} "
88 f"AND call_type NOT IN {_BATCH_CALL_TYPES_SQL})"
89 ),
90 }
91)
92_SPEND_LOG_LIST_COLUMNS: Final = """
93 request_id, call_type, api_key, spend, total_tokens,
94 prompt_tokens, completion_tokens, "startTime", "endTime",
95 "completionStartTime", model, model_id, model_group,
96 custom_llm_provider, api_base, "user", metadata,
97 cache_hit, cache_key, request_tags, team_id,
98 organization_id, end_user, requester_ip_address,
99 session_id, status, mcp_namespaced_tool_name, agent_id,
100 litellm_call_id,
101 COALESCE(request_duration_ms,
102 (EXTRACT(EPOCH FROM ("endTime" - "startTime")) * 1000)::INTEGER) AS request_duration_ms
103"""
105_INTERNAL_HEALTH_CHECK_API_KEYS: Final = (
106 LITTELM_INTERNAL_HEALTH_SERVICE_ACCOUNT_NAME,
107 hash_token(token=LITTELM_INTERNAL_HEALTH_SERVICE_ACCOUNT_NAME),
108)
110_RowT = TypeVar("_RowT")
113class _SupportsModelDump(Protocol):
114 def model_dump(self) -> Mapping[str, object]: ... 114 ↛ exitline 114 didn't return from function 'model_dump' because
117class _SpendLogOwnerRow(TypedDict):
118 user: ReadOnly[str | None]
119 team_id: ReadOnly[str | None]
122class _ActivityRow(TypedDict):
123 date: str
124 api_requests: int
125 total_tokens: int
128class _ActivityModelRow(TypedDict):
129 model_group: str
130 date: str
131 api_requests: int
132 total_tokens: int
135class _DeploymentExceptionsRow(TypedDict):
136 api_base: str
137 date: str
138 num_rate_limit_exceptions: int
141class _ExceptionsRow(TypedDict):
142 date: str
143 num_rate_limit_exceptions: int
146class _ModelIdSpendRow(TypedDict):
147 model_id: str
148 spend: float
151class _TagNameRow(TypedDict):
152 individual_request_tag: str
155class _TeamSpendRow(TypedDict):
156 team_alias: str | None
157 total_spend: float
160class _TagSpendRow(TypedDict):
161 individual_request_tag: str
162 total_spend: float
165class _SpendLogsCountRow(TypedDict):
166 total_count: int
169class _PgClassRow(TypedDict):
170 relname: str
171 relkind: str
174class _TotalSpendRow(TypedDict):
175 total_spend: float
178class _TeamDailySpendRow(TypedDict):
179 team_alias: str | None
180 spend_date: str | None
181 total_spend: float
184class _EndUserRow(TypedDict):
185 end_user: str | None
188class _DailyTagSpendRow(TypedDict):
189 individual_request_tag: str
190 log_count: int
191 total_spend: float
194class _SessionSpendRow(TypedDict):
195 session_id: str
196 api_key: ReadOnly[str]
197 session_total_count: ReadOnly[int]
198 session_total_spend: float
199 session_total_duration_ms: ReadOnly[int]
200 mcp_tool_call_count: int
201 mcp_tool_call_spend: float
202 session_cache_hit_count: ReadOnly[int]
203 session_llm_count: ReadOnly[int]
204 session_agent_count: ReadOnly[int]
205 session_total_prompt_tokens: ReadOnly[int]
206 session_total_completion_tokens: ReadOnly[int]
207 session_total_tokens: ReadOnly[int]
208 session_models: ReadOnly[Sequence[str]]
211_SESSION_MODELS_LIMIT: Final = 10
212_SESSION_MODEL_NAME_MAX_LEN: Final = 256
215class _SessionSpendStats(NamedTuple):
216 session_total_count: int
217 session_total_spend: float
218 session_total_duration_ms: int
219 mcp_tool_call_count: int
220 mcp_tool_call_spend: float
221 session_cache_hit_count: int
222 session_llm_count: int
223 session_agent_count: int
224 session_total_prompt_tokens: int
225 session_total_completion_tokens: int
226 session_total_tokens: int
227 session_models: Sequence[str]
228 session_models_truncated: bool
231_SessionSpendMap: TypeAlias = Mapping[tuple[str, str], _SessionSpendStats]
234class _SpendDailySummaryRow(TypedDict):
235 day: ReadOnly[str]
236 api_key: ReadOnly[str]
237 user: ReadOnly[str | None]
238 model: ReadOnly[str]
239 spend: ReadOnly[float]
242async def _query_raw(prisma_client: PrismaClient, sql_query: str, *args: object) -> Sequence[_RowT]:
243 """Run a raw read query and return its rows as the row type the caller declares."""
244 return await prisma_client.db.query_raw(sql_query, *args)
247async def _query_raw_or_none(prisma_client: PrismaClient, sql_query: str, *args: object) -> Sequence[_RowT] | None:
248 """``_query_raw`` for the call sites that guard the result against ``None``."""
249 return await _query_raw(prisma_client, sql_query, *args)
252class _TeamTable(Protocol):
253 """The subset of the Prisma team table API this module uses."""
255 async def find_unique(self, *, where: Mapping[str, object]) -> _SupportsModelDump | None: ... 255 ↛ exitline 255 didn't return from function 'find_unique' because
257 async def find_many(self, *, where: Mapping[str, object]) -> Sequence[_SupportsModelDump]: ... 257 ↛ exitline 257 didn't return from function 'find_many' because
259 async def update_many(self, *, data: Mapping[str, float], where: Mapping[str, object]) -> int: ... 259 ↛ exitline 259 didn't return from function 'update_many' because
262class _VerificationTokenTable(Protocol):
263 """The subset of the Prisma verification token table API this module uses."""
265 async def update_many(self, *, data: Mapping[str, float], where: Mapping[str, object]) -> int: ... 265 ↛ exitline 265 didn't return from function 'update_many' because
268def _spend_logs_table(prisma_client: PrismaClient) -> TableActions["prisma_models.LiteLLM_SpendLogs"]:
269 return SpendLogsRepository(prisma_client).table
272def _team_table(prisma_client: PrismaClient) -> _TeamTable:
273 return TeamRepository(prisma_client).table
276def _verification_token_table(prisma_client: PrismaClient) -> _VerificationTokenTable:
277 return VerificationTokenRepository(prisma_client).table
280def _spend_logs_daily_summary_sql(
281 *,
282 start_date_iso: str,
283 end_date_iso: str,
284 api_key: str | None,
285 request_id: str | None,
286 user_id: str | None,
287) -> tuple[str, tuple[object, ...]]:
288 filter_params: Final[tuple[tuple[str, object], ...]] = tuple(
289 (column, value)
290 for column, value in (
291 ("api_key", api_key),
292 ("request_id", request_id),
293 ('"user"', user_id),
294 )
295 if value is not None
296 )
297 filter_clauses: Final[tuple[str, ...]] = tuple(
298 f"AND {column} = ${index}" for index, (column, _) in enumerate(filter_params, start=3)
299 )
300 filter_sql: Final = "\n".join(filter_clauses)
301 sql_query: Final = f"""
302SELECT
303 to_char(date_trunc('day', "startTime"), 'YYYY-MM-DD') AS day,
304 api_key,
305 "user",
306 model,
307 SUM(spend) AS spend
308FROM "LiteLLM_SpendLogs"
309WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC') AND "startTime" <= ($2::timestamptz AT TIME ZONE 'UTC')
310{filter_sql}
311GROUP BY 1, 2, 3, 4
312ORDER BY 1
313"""
314 params: Final[tuple[object, ...]] = (
315 start_date_iso,
316 end_date_iso,
317 *(value for _, value in filter_params),
318 )
319 return sql_query, params
322def _sum_spend_by(
323 rows: Sequence[_SpendDailySummaryRow], column: Literal["api_key", "user", "model"]
324) -> Mapping[str | None, float]:
325 keys: Final = frozenset(row[column] for row in rows)
326 return {key: sum(float(row["spend"]) for row in rows if row[column] == key) for key in keys}
329def _daily_summary_item(summary_date: date, rows: Sequence[_SpendDailySummaryRow]) -> Mapping[str, object]:
330 api_key_spend: Final = {key: value for key, value in _sum_spend_by(rows, "api_key").items() if key is not None}
331 return {
332 **api_key_spend,
333 "startTime": summary_date,
334 "spend": sum(float(row["spend"]) for row in rows),
335 "users": _sum_spend_by(rows, "user"),
336 "models": _sum_spend_by(rows, "model"),
337 }
340async def _find_spend_logs(
341 prisma_client: PrismaClient,
342 where: Mapping[str, object],
343 order: Mapping[str, str],
344 take: int,
345 http_response: Response,
346) -> Sequence[_SupportsModelDump]:
347 """Read spend log rows as Prisma model instances, capped at ``take`` rows."""
348 rows: Final = await _spend_logs_table(prisma_client).find_many(where=where, order=order, take=take)
349 if len(rows) == take:
350 http_response.headers["x-litellm-spend-logs-truncated"] = "true"
351 verbose_proxy_logger.warning(
352 "/spend/logs result truncated to the %s most recent rows; use /spend/logs/v2 for paginated access",
353 take,
354 )
355 return rows
358class _RequestIdEquals(TypedDict):
359 request_id: ReadOnly[str]
362class _LitellmCallIdEquals(TypedDict):
363 litellm_call_id: ReadOnly[str]
366def _request_id_or_call_id_clause(request_id: str) -> tuple[_RequestIdEquals, _LitellmCallIdEquals]:
367 request_id_clause: Final[_RequestIdEquals] = {"request_id": request_id}
368 call_id_clause: Final[_LitellmCallIdEquals] = {"litellm_call_id": request_id}
369 return (request_id_clause, call_id_clause)
372async def _find_spend_log_owners(prisma_client: PrismaClient, request_id: str) -> Sequence[_SpendLogOwnerRow]:
373 """Read the distinct ``(user, team_id)`` owner pairs across every spend log row
374 identified by ``request_id`` or ``litellm_call_id``.
376 ``litellm_call_id`` is populated from the client-settable ``x-litellm-call-id``
377 request header, so it is not guaranteed unique to one tenant: any number of rows
378 can match one id. The read is uncapped because a flood of another tenant's rows
379 carrying the caller's id could otherwise push the caller's own owner pair past a
380 row-sample cap and lock them out of their own lookup.
381 """
382 sql_query: Final = """
383 SELECT DISTINCT "user", team_id
384 FROM "LiteLLM_SpendLogs"
385 WHERE request_id = $1 OR litellm_call_id = $1
386 """
387 owners: Final[Sequence[_SpendLogOwnerRow] | None] = await _query_raw_or_none(prisma_client, sql_query, request_id)
388 return owners if owners is not None else ()
391async def _count_spend_logs(prisma_client: PrismaClient, where: Mapping[str, object]) -> int:
392 """Count the spend log rows matching ``where``."""
393 return await _spend_logs_table(prisma_client).count(where=where)
396async def _find_team_row(prisma_client: PrismaClient, team_id: str) -> _SupportsModelDump | None:
397 """Read a single team row as a Prisma model instance."""
398 return await _team_table(prisma_client).find_unique(where={"team_id": team_id})
401async def _find_team_rows(prisma_client: PrismaClient, team_ids: Sequence[str]) -> Sequence[_SupportsModelDump]:
402 """Read team rows as Prisma model instances."""
403 return await _team_table(prisma_client).find_many(where={"team_id": {"in": team_ids}})
406@router.get(
407 "/spend/keys",
408 tags=["Budget & Spend Tracking"],
409 dependencies=[Depends(user_api_key_auth)],
410 include_in_schema=False,
411)
412async def spend_key_fn(
413 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
414):
415 """
416 View keys created, ordered by spend.
418 - Admin callers (PROXY_ADMIN / PROXY_ADMIN_VIEW_ONLY) see every key in
419 the database.
420 - All other callers (INTERNAL_USER / INTERNAL_USER_VIEW_ONLY, etc.) are
421 scoped to keys they own (``user_id == caller``). A caller with no
422 ``user_id`` has no scope and receives an empty list rather than the
423 full table.
425 Example Request:
426 ```
427 curl -X GET "http://0.0.0.0:8000/spend/keys" \
428-H "Authorization: Bearer sk-1234"
429 ```
430 """
432 from litellm.proxy.proxy_server import prisma_client
434 try:
435 if prisma_client is None:
436 raise Exception(
437 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
438 )
440 if _is_admin_view_safe(user_api_key_dict=user_api_key_dict):
441 return await prisma_client.get_data(table_name="key", query_type="find_all")
443 caller_user_id: Final = user_api_key_dict.user_id
444 if not caller_user_id:
445 return []
446 return await prisma_client.get_data(
447 table_name="key",
448 query_type="find_all",
449 user_id=caller_user_id,
450 )
452 except Exception as e:
453 raise HTTPException(
454 status_code=status.HTTP_400_BAD_REQUEST,
455 detail={"error": str(e)},
456 )
459def _strip_password_from_users(users) -> None:
460 """Strip password field from a list of user objects."""
461 for user in users if isinstance(users, list) else [users]:
462 if user and hasattr(user, "__dict__"):
463 user.__dict__.pop("password", None)
464 elif isinstance(user, dict):
465 user.pop("password", None)
468@router.get(
469 "/spend/users",
470 tags=["Budget & Spend Tracking"],
471 dependencies=[Depends(user_api_key_auth)],
472 include_in_schema=False,
473)
474async def spend_user_fn(
475 user_id: str | None = fastapi.Query(
476 default=None,
477 description="Get User Table row for user_id",
478 ),
479 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
480):
481 """
482 View users created, ordered by spend.
484 - Admin callers (PROXY_ADMIN / PROXY_ADMIN_VIEW_ONLY) see every user, or
485 a specific user when ``user_id`` is supplied.
486 - All other callers may only read their own row. If they supply a
487 ``user_id`` query parameter that does not match their authenticated
488 ``user_id`` the request is rejected with HTTP 403; supplying their
489 own id (or none at all) returns just their row. A caller with no
490 ``user_id`` on their key has no scope and receives an empty list
491 rather than the full table.
493 Example Request:
494 ```
495 curl -X GET "http://0.0.0.0:8000/spend/users" \
496-H "Authorization: Bearer sk-1234"
497 ```
499 View User Table row for user_id
500 ```
501 curl -X GET "http://0.0.0.0:8000/spend/users?user_id=1234" \
502-H "Authorization: Bearer sk-1234"
503 ```
504 """
505 from litellm.proxy.proxy_server import prisma_client
507 try:
508 if prisma_client is None:
509 raise Exception(
510 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
511 )
513 if not _is_admin_view_safe(user_api_key_dict=user_api_key_dict):
514 caller_user_id: Final = user_api_key_dict.user_id
515 if not caller_user_id:
516 return []
517 if user_id is not None and user_id != caller_user_id:
518 raise HTTPException(
519 status_code=status.HTTP_403_FORBIDDEN,
520 detail={"error": "Not authorized to view spend for another user."},
521 )
522 user_id = caller_user_id
524 if user_id is not None:
525 user_info = await prisma_client.get_data(table_name="user", query_type="find_unique", user_id=user_id)
526 result = [user_info]
527 else:
528 user_info = await prisma_client.get_data(table_name="user", query_type="find_all")
529 result = user_info
531 _strip_password_from_users(result)
532 return result
534 except HTTPException:
535 raise
536 except Exception as e:
537 raise HTTPException(
538 status_code=status.HTTP_400_BAD_REQUEST,
539 detail={"error": str(e)},
540 )
543@router.get(
544 "/spend/tags",
545 tags=["Budget & Spend Tracking"],
546 dependencies=[Depends(user_api_key_auth)],
547 responses={
548 200: {"model": list[LiteLLM_SpendLogs]},
549 },
550)
551async def view_spend_tags(
552 start_date: str | None = fastapi.Query(
553 default=None,
554 description="Time from which to start viewing key spend",
555 ),
556 end_date: str | None = fastapi.Query(
557 default=None,
558 description="Time till which to view key spend",
559 ),
560):
561 """
562 LiteLLM Enterprise - View Spend Per Request Tag
564 Example Request:
565 ```
566 curl -X GET "http://0.0.0.0:8000/spend/tags" \
567-H "Authorization: Bearer sk-1234"
568 ```
570 Spend with Start Date and End Date
571 ```
572 curl -X GET "http://0.0.0.0:8000/spend/tags?start_date=2022-01-01&end_date=2022-02-01" \
573-H "Authorization: Bearer sk-1234"
574 ```
575 """
577 from litellm.proxy.proxy_server import prisma_client
579 try:
580 if prisma_client is None: 580 ↛ 581line 580 didn't jump to line 581 because the condition on line 580 was never true
581 raise Exception(
582 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
583 )
585 # run the following SQL query on prisma
586 """
587 SELECT
588 jsonb_array_elements_text(request_tags) AS individual_request_tag,
589 COUNT(*) AS log_count,
590 SUM(spend) AS total_spend
591 FROM "LiteLLM_SpendLogs"
592 GROUP BY individual_request_tag;
593 """
594 response: Final = await get_spend_by_tags(start_date=start_date, end_date=end_date, prisma_client=prisma_client)
596 return response
597 except Exception as e:
598 if isinstance(e, HTTPException):
599 raise ProxyException(
600 message=getattr(e, "detail", f"/spend/tags Error({e})"),
601 type="internal_error",
602 param=getattr(e, "param", "None"),
603 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
604 )
605 elif isinstance(e, ProxyException):
606 raise e
607 raise ProxyException(
608 message="/spend/tags Error" + str(e),
609 type="internal_error",
610 param=getattr(e, "param", "None"),
611 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
612 )
615async def get_global_activity_internal_user(
616 user_api_key_dict: UserAPIKeyAuth, start_date: datetime, end_date: datetime
617):
618 from litellm.proxy.proxy_server import prisma_client
620 if prisma_client is None:
621 raise HTTPException(status_code=500, detail={"error": "No db connected"})
623 user_id: Final = user_api_key_dict.user_id
624 if user_id is None:
625 raise HTTPException(status_code=500, detail={"error": "No user_id found"})
627 sql_query: Final = """
628 SELECT
629 date_trunc('day', "startTime") AS date,
630 COUNT(*) AS api_requests,
631 SUM(total_tokens) AS total_tokens
632 FROM "LiteLLM_SpendLogs"
633 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
634 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
635 AND "user" = $3
636 GROUP BY date_trunc('day', "startTime")
637 """
638 db_response: Final[Sequence[_ActivityRow] | None] = await _query_raw_or_none(
639 prisma_client, sql_query, start_date, end_date, user_id
640 )
642 return db_response
645@router.get(
646 "/global/activity",
647 tags=["Budget & Spend Tracking"],
648 dependencies=[Depends(user_api_key_auth)],
649 responses={
650 200: {"model": list[LiteLLM_SpendLogs]},
651 },
652 include_in_schema=False,
653)
654async def get_global_activity(
655 start_date: str | None = fastapi.Query(
656 default=None,
657 description="Time from which to start viewing spend",
658 ),
659 end_date: str | None = fastapi.Query(
660 default=None,
661 description="Time till which to view spend",
662 ),
663 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
664):
665 """
666 Get number of API Requests, total tokens through proxy
668 {
669 "daily_data": [
670 const chartdata = [
671 {
672 date: 'Jan 22',
673 api_requests: 10,
674 total_tokens: 2000
675 },
676 {
677 date: 'Jan 23',
678 api_requests: 10,
679 total_tokens: 12
680 },
681 ],
682 "sum_api_requests": 20,
683 "sum_total_tokens": 2012
684 }
685 """
687 if start_date is None or end_date is None:
688 raise HTTPException(
689 status_code=status.HTTP_400_BAD_REQUEST,
690 detail={"error": "Please provide start_date and end_date"},
691 )
693 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
694 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
696 from litellm.proxy.proxy_server import prisma_client
698 try:
699 if prisma_client is None:
700 raise Exception(
701 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
702 )
704 db_response: Sequence[_ActivityRow] | None
705 if (
706 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
707 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
708 ):
709 db_response = await get_global_activity_internal_user(user_api_key_dict, start_date_obj, end_date_obj)
710 else:
711 sql_query: Final = """
712 SELECT
713 date_trunc('day', "startTime") AS date,
714 COUNT(*) AS api_requests,
715 SUM(total_tokens) AS total_tokens
716 FROM "LiteLLM_SpendLogs"
717 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
718 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
719 GROUP BY date_trunc('day', "startTime")
720 """
721 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj)
723 if db_response is None:
724 return []
726 sum_api_requests = 0
727 sum_total_tokens = 0
728 daily_data = []
729 for row in db_response:
730 # cast date to datetime
731 _date_obj = datetime.fromisoformat(row["date"])
732 row["date"] = _date_obj.strftime("%b %d")
734 daily_data.append(row)
735 sum_api_requests += row.get("api_requests", 0)
736 sum_total_tokens += row.get("total_tokens", 0)
738 # sort daily_data by date
739 daily_data = sorted(daily_data, key=lambda x: x["date"])
741 data_to_return: Final = {
742 "daily_data": daily_data,
743 "sum_api_requests": sum_api_requests,
744 "sum_total_tokens": sum_total_tokens,
745 }
747 return data_to_return
749 except Exception as e:
750 raise HTTPException(
751 status_code=status.HTTP_400_BAD_REQUEST,
752 detail={"error": str(e)},
753 )
756async def get_global_activity_model_internal_user(
757 user_api_key_dict: UserAPIKeyAuth, start_date: datetime, end_date: datetime
758):
759 from litellm.proxy.proxy_server import prisma_client
761 if prisma_client is None:
762 raise HTTPException(status_code=500, detail={"error": "No db connected"})
764 user_id: Final = user_api_key_dict.user_id
765 if user_id is None:
766 raise HTTPException(status_code=500, detail={"error": "No user_id found"})
768 sql_query: Final = """
769 SELECT
770 model_group,
771 date_trunc('day', "startTime") AS date,
772 COUNT(*) AS api_requests,
773 SUM(total_tokens) AS total_tokens
774 FROM "LiteLLM_SpendLogs"
775 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
776 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
777 AND "user" = $3
778 GROUP BY model_group, date_trunc('day', "startTime")
779 """
780 db_response: Final[Sequence[_ActivityModelRow] | None] = await _query_raw_or_none(
781 prisma_client, sql_query, start_date, end_date, user_id
782 )
784 return db_response
787@router.get(
788 "/global/activity/model",
789 tags=["Budget & Spend Tracking"],
790 dependencies=[Depends(user_api_key_auth)],
791 responses={
792 200: {"model": list[LiteLLM_SpendLogs]},
793 },
794 include_in_schema=False,
795)
796async def get_global_activity_model(
797 start_date: str | None = fastapi.Query(
798 default=None,
799 description="Time from which to start viewing spend",
800 ),
801 end_date: str | None = fastapi.Query(
802 default=None,
803 description="Time till which to view spend",
804 ),
805 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
806):
807 """
808 Get number of API Requests, total tokens through proxy - Grouped by MODEL
810 [
811 {
812 "model": "gpt-4",
813 "daily_data": [
814 const chartdata = [
815 {
816 date: 'Jan 22',
817 api_requests: 10,
818 total_tokens: 2000
819 },
820 {
821 date: 'Jan 23',
822 api_requests: 10,
823 total_tokens: 12
824 },
825 ],
826 "sum_api_requests": 20,
827 "sum_total_tokens": 2012
829 },
830 {
831 "model": "azure/gpt-4-turbo",
832 "daily_data": [
833 const chartdata = [
834 {
835 date: 'Jan 22',
836 api_requests: 10,
837 total_tokens: 2000
838 },
839 {
840 date: 'Jan 23',
841 api_requests: 10,
842 total_tokens: 12
843 },
844 ],
845 "sum_api_requests": 20,
846 "sum_total_tokens": 2012
848 },
849 ]
850 """
852 if start_date is None or end_date is None:
853 raise HTTPException(
854 status_code=status.HTTP_400_BAD_REQUEST,
855 detail={"error": "Please provide start_date and end_date"},
856 )
858 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
859 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
861 from litellm.proxy.proxy_server import prisma_client
863 try:
864 if prisma_client is None:
865 raise Exception(
866 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
867 )
869 db_response: Sequence[_ActivityModelRow] | None
870 if (
871 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
872 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
873 ):
874 db_response = await get_global_activity_model_internal_user(user_api_key_dict, start_date_obj, end_date_obj)
875 else:
876 sql_query: Final = """
877 SELECT
878 model_group,
879 date_trunc('day', "startTime") AS date,
880 COUNT(*) AS api_requests,
881 SUM(total_tokens) AS total_tokens
882 FROM "LiteLLM_SpendLogs"
883 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
884 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
885 GROUP BY model_group, date_trunc('day', "startTime")
886 """
887 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj)
888 if db_response is None:
889 return []
891 model_ui_data: dict = {} # {"gpt-4": {"daily_data": [], "sum_api_requests": 0, "sum_total_tokens": 0}}
893 for row in db_response:
894 _model = row["model_group"]
895 if _model not in model_ui_data:
896 model_ui_data[_model] = {
897 "daily_data": [],
898 "sum_api_requests": 0,
899 "sum_total_tokens": 0,
900 }
901 _date_obj = datetime.fromisoformat(row["date"])
902 row["date"] = _date_obj.strftime("%b %d")
904 model_ui_data[_model]["daily_data"].append(row)
905 model_ui_data[_model]["sum_api_requests"] += row.get("api_requests", 0)
906 model_ui_data[_model]["sum_total_tokens"] += row.get("total_tokens", 0)
908 # sort mode ui data by sum_api_requests -> get top 10 models
909 model_ui_data = dict(
910 sorted(
911 model_ui_data.items(),
912 key=lambda x: x[1]["sum_api_requests"],
913 reverse=True,
914 )[:10]
915 )
917 response: Final = []
918 for model, data in model_ui_data.items():
919 _sort_daily_data = sorted(data["daily_data"], key=lambda x: x["date"])
921 response.append(
922 {
923 "model": model,
924 "daily_data": _sort_daily_data,
925 "sum_api_requests": data["sum_api_requests"],
926 "sum_total_tokens": data["sum_total_tokens"],
927 }
928 )
930 return response
932 except Exception as e:
933 raise HTTPException(
934 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
935 detail={"error": str(e)},
936 )
939@router.get(
940 "/global/activity/exceptions/deployment",
941 tags=["Budget & Spend Tracking"],
942 dependencies=[Depends(user_api_key_auth)],
943 responses={
944 200: {"model": list[LiteLLM_SpendLogs]},
945 },
946 include_in_schema=False,
947)
948async def get_global_activity_exceptions_per_deployment(
949 model_group: str = fastapi.Query(
950 description="Filter by model group",
951 ),
952 start_date: str | None = fastapi.Query(
953 default=None,
954 description="Time from which to start viewing spend",
955 ),
956 end_date: str | None = fastapi.Query(
957 default=None,
958 description="Time till which to view spend",
959 ),
960):
961 """
962 Get number of 429 errors - Grouped by deployment
964 [
965 {
966 "deployment": "https://azure-us-east-1.openai.azure.com/",
967 "daily_data": [
968 const chartdata = [
969 {
970 date: 'Jan 22',
971 num_rate_limit_exceptions: 10
972 },
973 {
974 date: 'Jan 23',
975 num_rate_limit_exceptions: 12
976 },
977 ],
978 "sum_num_rate_limit_exceptions": 20,
980 },
981 {
982 "deployment": "https://azure-us-east-1.openai.azure.com/",
983 "daily_data": [
984 const chartdata = [
985 {
986 date: 'Jan 22',
987 num_rate_limit_exceptions: 10,
988 },
989 {
990 date: 'Jan 23',
991 num_rate_limit_exceptions: 12
992 },
993 ],
994 "sum_num_rate_limit_exceptions": 20,
996 },
997 ]
998 """
1000 if start_date is None or end_date is None:
1001 raise HTTPException(
1002 status_code=status.HTTP_400_BAD_REQUEST,
1003 detail={"error": "Please provide start_date and end_date"},
1004 )
1006 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1007 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1009 from litellm.proxy.proxy_server import prisma_client
1011 try:
1012 if prisma_client is None:
1013 raise Exception(
1014 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
1015 )
1017 sql_query: Final = """
1018 SELECT
1019 api_base,
1020 date_trunc('day', "startTime")::date AS date,
1021 COUNT(*) AS num_rate_limit_exceptions
1022 FROM
1023 "LiteLLM_ErrorLogs"
1024 WHERE
1025 "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1026 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1027 AND model_group = $3
1028 AND status_code = '429'
1029 GROUP BY
1030 api_base,
1031 date_trunc('day', "startTime")
1032 ORDER BY
1033 date;
1034 """
1035 db_response: Final[Sequence[_DeploymentExceptionsRow] | None] = await _query_raw_or_none(
1036 prisma_client, sql_query, start_date_obj, end_date_obj, model_group
1037 )
1038 if db_response is None:
1039 return []
1041 model_ui_data: dict = {} # {"gpt-4": {"daily_data": [], "sum_api_requests": 0, "sum_total_tokens": 0}}
1043 for row in db_response:
1044 _model = row["api_base"]
1045 if _model not in model_ui_data:
1046 model_ui_data[_model] = {
1047 "daily_data": [],
1048 "sum_num_rate_limit_exceptions": 0,
1049 }
1050 _date_obj = datetime.fromisoformat(row["date"])
1051 row["date"] = _date_obj.strftime("%b %d")
1053 model_ui_data[_model]["daily_data"].append(row)
1054 model_ui_data[_model]["sum_num_rate_limit_exceptions"] += row.get("num_rate_limit_exceptions", 0)
1056 # sort mode ui data by sum_api_requests -> get top 10 models
1057 model_ui_data = dict(
1058 sorted(
1059 model_ui_data.items(),
1060 key=lambda x: x[1]["sum_num_rate_limit_exceptions"],
1061 reverse=True,
1062 )[:10]
1063 )
1065 response: Final = []
1066 for model, data in model_ui_data.items():
1067 _sort_daily_data = sorted(data["daily_data"], key=lambda x: x["date"])
1069 response.append(
1070 {
1071 "api_base": model,
1072 "daily_data": _sort_daily_data,
1073 "sum_num_rate_limit_exceptions": data["sum_num_rate_limit_exceptions"],
1074 }
1075 )
1077 return response
1079 except Exception as e:
1080 raise HTTPException(
1081 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
1082 detail={"error": str(e)},
1083 )
1086@router.get(
1087 "/global/activity/exceptions",
1088 tags=["Budget & Spend Tracking"],
1089 dependencies=[Depends(user_api_key_auth)],
1090 responses={
1091 200: {"model": list[LiteLLM_SpendLogs]},
1092 },
1093 include_in_schema=False,
1094)
1095async def get_global_activity_exceptions(
1096 model_group: str = fastapi.Query(
1097 description="Filter by model group",
1098 ),
1099 start_date: str | None = fastapi.Query(
1100 default=None,
1101 description="Time from which to start viewing spend",
1102 ),
1103 end_date: str | None = fastapi.Query(
1104 default=None,
1105 description="Time till which to view spend",
1106 ),
1107):
1108 """
1109 Get number of API Requests, total tokens through proxy
1111 {
1112 "daily_data": [
1113 const chartdata = [
1114 {
1115 date: 'Jan 22',
1116 num_rate_limit_exceptions: 10,
1117 },
1118 {
1119 date: 'Jan 23',
1120 num_rate_limit_exceptions: 10,
1121 },
1122 ],
1123 "sum_api_exceptions": 20,
1124 }
1125 """
1127 if start_date is None or end_date is None:
1128 raise HTTPException(
1129 status_code=status.HTTP_400_BAD_REQUEST,
1130 detail={"error": "Please provide start_date and end_date"},
1131 )
1133 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1134 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1136 from litellm.proxy.proxy_server import prisma_client
1138 try:
1139 if prisma_client is None:
1140 raise Exception(
1141 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
1142 )
1144 sql_query: Final = """
1145 SELECT
1146 date_trunc('day', "startTime")::date AS date,
1147 COUNT(*) AS num_rate_limit_exceptions
1148 FROM
1149 "LiteLLM_ErrorLogs"
1150 WHERE
1151 "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1152 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1153 AND model_group = $3
1154 AND status_code = '429'
1155 GROUP BY
1156 date_trunc('day', "startTime")
1157 ORDER BY
1158 date;
1159 """
1160 db_response: Final[Sequence[_ExceptionsRow] | None] = await _query_raw_or_none(
1161 prisma_client, sql_query, start_date_obj, end_date_obj, model_group
1162 )
1164 if db_response is None:
1165 return []
1167 sum_num_rate_limit_exceptions = 0
1168 daily_data = []
1169 for row in db_response:
1170 # cast date to datetime
1171 _date_obj = datetime.fromisoformat(row["date"])
1172 row["date"] = _date_obj.strftime("%b %d")
1174 daily_data.append(row)
1175 sum_num_rate_limit_exceptions += row.get("num_rate_limit_exceptions", 0)
1177 # sort daily_data by date
1178 daily_data = sorted(daily_data, key=lambda x: x["date"])
1180 data_to_return: Final = {
1181 "daily_data": daily_data,
1182 "sum_num_rate_limit_exceptions": sum_num_rate_limit_exceptions,
1183 }
1185 return data_to_return
1187 except Exception as e:
1188 raise HTTPException(
1189 status_code=status.HTTP_400_BAD_REQUEST,
1190 detail={"error": str(e)},
1191 )
1194@router.get(
1195 "/spend/capture_rate",
1196 tags=["Budget & Spend Tracking"], # mutable-ok: FastAPI tags kwarg is list-typed
1197 dependencies=(Depends(user_api_key_auth),),
1198 response_model=CaptureRateReport,
1199)
1200async def get_spend_capture_rate(
1201 start_date: Annotated[date, fastapi.Query(description="First UTC day of the range, YYYY-MM-DD")],
1202 end_date: Annotated[date, fastapi.Query(description="Last UTC day of the range, YYYY-MM-DD, inclusive")],
1203 user_api_key_dict: Annotated[UserAPIKeyAuth, Depends(user_api_key_auth)],
1204 provider: Annotated[
1205 SpendCaptureProvider,
1206 fastapi.Query(description="Provider whose bill to compare against; needs OPENAI_ADMIN_KEY set on the proxy"),
1207 ] = "openai",
1208 threshold: Annotated[
1209 float, fastapi.Query(gt=0, le=1, description="Ratio under which the report flags below_threshold")
1210 ] = 0.9,
1211 project_ids: Annotated[
1212 list[str] | None,
1213 fastapi.Query(
1214 description=(
1215 "Scope the OpenAI bill to these project ids; omit to compare against the whole organization. Captured "
1216 "spend is never scoped, so pass every project LiteLLM's OpenAI keys belong to"
1217 )
1218 ),
1219 ] = None,
1220) -> CaptureRateReport:
1221 """
1222 Compare the spend LiteLLM captured for a provider against that provider's own bill, per UTC day.
1224 Admin only. Reads the provider's billing API with the billing credential set on the proxy
1225 (OpenAI: `OPENAI_ADMIN_KEY`) and sums `LiteLLM_DailyUserSpend` for the same days.
1227 Example:
1228 ```
1229 curl -H "Authorization: Bearer sk-1234" \
1230 "http://localhost:4000/spend/capture_rate?provider=openai&start_date=2026-09-17&end_date=2026-09-23"
1231 ```
1232 """
1233 from litellm.proxy.proxy_server import prisma_client
1235 if not _is_admin_view_safe(user_api_key_dict): 1235 ↛ 1236line 1235 didn't jump to line 1236 because the condition on line 1235 was never true
1236 raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Only proxy admins can read the capture rate")
1237 if prisma_client is None: 1237 ↛ 1238line 1237 didn't jump to line 1238 because the condition on line 1237 was never true
1238 raise HTTPException(
1239 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=CommonProxyErrors.db_not_connected_error.value
1240 )
1241 if end_date < start_date:
1242 raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="end_date must not be before start_date")
1243 if (end_date - start_date).days >= SPEND_CAPTURE_RATE_MAX_RANGE_DAYS:
1244 raise HTTPException(
1245 status_code=status.HTTP_400_BAD_REQUEST,
1246 detail=f"Date range too large; maximum is {SPEND_CAPTURE_RATE_MAX_RANGE_DAYS} days",
1247 )
1248 result: Final = await capture_rate_report(
1249 prisma_client,
1250 provider=provider,
1251 start_date=start_date,
1252 end_date=end_date,
1253 threshold=threshold,
1254 openai_project_ids=tuple(project_ids or ()),
1255 )
1256 match result:
1257 case ProviderBillingCredentialMissing(env_var=env_var): 1257 ↛ 1262line 1257 didn't jump to line 1262 because the pattern on line 1257 always matched
1258 raise HTTPException(
1259 status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
1260 detail=f"{env_var} is not set on the proxy, so the {provider} bill cannot be read",
1261 )
1262 case ProviderBillingRequestFailed(detail=detail):
1263 raise HTTPException(
1264 status_code=status.HTTP_502_BAD_GATEWAY, detail=f"Could not read the {provider} bill: {detail}"
1265 )
1266 case CaptureRateReport():
1267 return result
1268 case _:
1269 assert_never(result)
1272@router.get(
1273 "/global/spend/provider",
1274 tags=["Budget & Spend Tracking"],
1275 dependencies=[Depends(user_api_key_auth)],
1276 include_in_schema=False,
1277 responses={
1278 200: {"model": list[LiteLLM_SpendLogs]},
1279 },
1280)
1281async def get_global_spend_provider(
1282 start_date: str | None = fastapi.Query(
1283 default=None,
1284 description="Time from which to start viewing spend",
1285 ),
1286 end_date: str | None = fastapi.Query(
1287 default=None,
1288 description="Time till which to view spend",
1289 ),
1290 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
1291):
1292 """
1293 Get breakdown of spend per provider
1294 [
1295 {
1296 "provider": "Azure OpenAI",
1297 "spend": 20
1298 },
1299 {
1300 "provider": "OpenAI",
1301 "spend": 10
1302 },
1303 {
1304 "provider": "VertexAI",
1305 "spend": 30
1306 }
1307 ]
1308 """
1309 from collections import defaultdict
1311 if start_date is None or end_date is None:
1312 raise HTTPException(
1313 status_code=status.HTTP_400_BAD_REQUEST,
1314 detail={"error": "Please provide start_date and end_date"},
1315 )
1317 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1318 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1320 from litellm.proxy.proxy_server import llm_router, prisma_client
1322 try:
1323 if prisma_client is None:
1324 raise Exception(
1325 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
1326 )
1328 db_response: Sequence[_ModelIdSpendRow] | None
1329 if (
1330 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
1331 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
1332 ):
1333 user_id: Final = user_api_key_dict.user_id
1334 if user_id is None:
1335 raise HTTPException(status_code=400, detail={"error": "No user_id found"})
1337 sql_query = """
1338 SELECT
1339 model_id,
1340 SUM(spend) AS spend
1341 FROM "LiteLLM_SpendLogs"
1342 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1343 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1344 AND length(model_id) > 0
1345 AND "user" = $3
1346 GROUP BY model_id
1347 """
1348 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj, user_id)
1349 else:
1350 sql_query = """
1351 SELECT
1352 model_id,
1353 SUM(spend) AS spend
1354 FROM "LiteLLM_SpendLogs"
1355 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1356 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1357 AND length(model_id) > 0
1358 GROUP BY model_id
1359 """
1360 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj)
1362 if db_response is None:
1363 return []
1365 ###################################
1366 # Convert model_id -> to Provider #
1367 ###################################
1369 # we use the in memory router for this
1370 ui_response: Final = []
1371 provider_spend_mapping: Final[defaultdict] = defaultdict(int)
1372 for row in db_response:
1373 _model_id = row["model_id"]
1374 _provider = "Unknown"
1375 if llm_router is not None:
1376 _deployment = llm_router.get_deployment(model_id=_model_id)
1377 if _deployment is not None:
1378 try:
1379 _, _provider, _, _ = litellm.get_llm_provider(
1380 model=_deployment.litellm_params.model,
1381 custom_llm_provider=_deployment.litellm_params.custom_llm_provider,
1382 api_base=_deployment.litellm_params.api_base,
1383 litellm_params=_deployment.litellm_params,
1384 )
1385 provider_spend_mapping[_provider] += row["spend"]
1386 except Exception:
1387 pass
1389 for provider, spend in provider_spend_mapping.items():
1390 ui_response.append({"provider": provider, "spend": spend})
1392 return ui_response
1394 except Exception as e:
1395 raise HTTPException(
1396 status_code=status.HTTP_400_BAD_REQUEST,
1397 detail={"error": str(e)},
1398 )
1401@router.get(
1402 "/global/spend/report",
1403 tags=["Budget & Spend Tracking"],
1404 dependencies=[Depends(user_api_key_auth)],
1405 responses={
1406 200: {"model": list[LiteLLM_SpendLogs]},
1407 },
1408)
1409async def get_global_spend_report(
1410 start_date: str | None = fastapi.Query(
1411 default=None,
1412 description="Time from which to start viewing spend",
1413 ),
1414 end_date: str | None = fastapi.Query(
1415 default=None,
1416 description="Time till which to view spend",
1417 ),
1418 group_by: Literal["team", "customer", "api_key"] | None = fastapi.Query(
1419 default="team",
1420 description="Group spend by internal team or customer or api_key",
1421 ),
1422 api_key: str | None = fastapi.Query(
1423 default=None,
1424 description=(
1425 "View spend for a specific api_key. Pass the key's sha256 hash so the raw key stays "
1426 "out of URLs and access logs. Example api_key='d5345c0ecc68ae6295c69f91926b2bd379e25481a40c34b5884d157a9f65d8fa'"
1427 ),
1428 ),
1429 internal_user_id: str | None = fastapi.Query(
1430 default=None,
1431 description="View spend for a specific internal_user_id. Example internal_user_id='1234",
1432 ),
1433 team_id: str | None = fastapi.Query(
1434 default=None,
1435 description="View spend for a specific team_id. Example team_id='1234",
1436 ),
1437 customer_id: str | None = fastapi.Query(
1438 default=None,
1439 description="View spend for a specific customer_id. Example customer_id='1234. Can be used in conjunction with team_id as well.",
1440 ),
1441):
1442 """
1443 Get Daily Spend per Team, based on specific startTime and endTime. Per team, view usage by each key, model
1444 [
1445 {
1446 "group-by-day": "2024-05-10",
1447 "teams": [
1448 {
1449 "team_name": "team-1"
1450 "spend": 10,
1451 "keys": [
1452 "key": "1213",
1453 "usage": {
1454 "model-1": {
1455 "cost": 12.50,
1456 "input_tokens": 1000,
1457 "output_tokens": 5000,
1458 "requests": 100
1459 },
1460 "audio-modelname1": {
1461 "cost": 25.50,
1462 "seconds": 25,
1463 "requests": 50
1464 },
1465 }
1466 }
1467 ]
1468 ]
1469 }
1470 """
1471 if start_date is None or end_date is None:
1472 raise HTTPException(
1473 status_code=status.HTTP_400_BAD_REQUEST,
1474 detail={"error": "Please provide start_date and end_date"},
1475 )
1477 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1478 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
1480 from litellm.proxy.proxy_server import premium_user, prisma_client
1482 try:
1483 if prisma_client is None: 1483 ↛ 1484line 1483 didn't jump to line 1484 because the condition on line 1483 was never true
1484 raise Exception(
1485 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
1486 )
1488 if premium_user is not True: 1488 ↛ 1492line 1488 didn't jump to line 1492 because the condition on line 1488 was always true
1489 verbose_proxy_logger.debug("accessing /spend/report but not a premium user")
1490 raise ValueError("/spend/report endpoint " + CommonProxyErrors.not_premium_user.value)
1491 db_response: Sequence[Mapping[str, object]] | None
1492 if api_key is not None:
1493 verbose_proxy_logger.debug("Getting /spend for api_key: [set=%s]", api_key is not None)
1494 if api_key.startswith("sk-"):
1495 api_key = hash_token(token=api_key)
1496 sql_query = """
1497 WITH SpendByModelApiKey AS (
1498 SELECT
1499 sl.api_key,
1500 sl.model,
1501 SUM(sl.spend) AS model_cost,
1502 SUM(sl.prompt_tokens) AS model_input_tokens,
1503 SUM(sl.completion_tokens) AS model_output_tokens
1504 FROM
1505 "LiteLLM_SpendLogs" sl
1506 WHERE
1507 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1508 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1509 AND sl.api_key = $3
1510 GROUP BY
1511 sl.api_key,
1512 sl.model
1513 )
1514 SELECT
1515 api_key,
1516 SUM(model_cost) AS total_cost,
1517 SUM(model_input_tokens) AS total_input_tokens,
1518 SUM(model_output_tokens) AS total_output_tokens,
1519 jsonb_agg(jsonb_build_object(
1520 'model', model,
1521 'total_cost', model_cost,
1522 'total_input_tokens', model_input_tokens,
1523 'total_output_tokens', model_output_tokens
1524 )) AS model_details
1525 FROM
1526 SpendByModelApiKey
1527 GROUP BY
1528 api_key
1529 ORDER BY
1530 total_cost DESC;
1531 """
1532 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj, api_key)
1533 if db_response is None:
1534 return []
1536 return db_response
1537 elif internal_user_id is not None:
1538 verbose_proxy_logger.debug("Getting /spend for internal_user_id: %s", internal_user_id)
1539 sql_query = """
1540 WITH SpendByModelApiKey AS (
1541 SELECT
1542 sl.api_key,
1543 sl.model,
1544 SUM(sl.spend) AS model_cost,
1545 SUM(sl.prompt_tokens) AS model_input_tokens,
1546 SUM(sl.completion_tokens) AS model_output_tokens
1547 FROM
1548 "LiteLLM_SpendLogs" sl
1549 WHERE
1550 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1551 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1552 AND sl.user = $3
1553 GROUP BY
1554 sl.api_key,
1555 sl.model
1556 )
1557 SELECT
1558 api_key,
1559 SUM(model_cost) AS total_cost,
1560 SUM(model_input_tokens) AS total_input_tokens,
1561 SUM(model_output_tokens) AS total_output_tokens,
1562 jsonb_agg(jsonb_build_object(
1563 'model', model,
1564 'total_cost', model_cost,
1565 'total_input_tokens', model_input_tokens,
1566 'total_output_tokens', model_output_tokens
1567 )) AS model_details
1568 FROM
1569 SpendByModelApiKey
1570 GROUP BY
1571 api_key
1572 ORDER BY
1573 total_cost DESC;
1574 """
1575 db_response = await _query_raw_or_none(
1576 prisma_client, sql_query, start_date_obj, end_date_obj, internal_user_id
1577 )
1578 if db_response is None:
1579 return []
1581 return db_response
1582 elif team_id is not None and customer_id is not None:
1583 return await get_spend_by_team_and_customer(
1584 start_date_obj, end_date_obj, team_id, customer_id, prisma_client
1585 )
1586 if group_by == "team":
1587 return await get_spend_by_team(start_date_obj, end_date_obj, team_id, prisma_client)
1589 elif group_by == "customer":
1590 sql_query = """
1592 WITH SpendByModelApiKey AS (
1593 SELECT
1594 date_trunc('day', sl."startTime") AS group_by_day,
1595 sl.end_user AS customer,
1596 sl.model,
1597 sl.api_key,
1598 SUM(sl.spend) AS model_api_spend,
1599 SUM(sl.total_tokens) AS model_api_tokens
1600 FROM
1601 "LiteLLM_SpendLogs" sl
1602 WHERE
1603 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1604 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1605 GROUP BY
1606 date_trunc('day', sl."startTime"),
1607 customer,
1608 sl.model,
1609 sl.api_key
1610 )
1611 SELECT
1612 group_by_day,
1613 jsonb_agg(jsonb_build_object(
1614 'customer', customer,
1615 'total_spend', total_spend,
1616 'metadata', metadata
1617 )) AS customers
1618 FROM
1619 (
1620 SELECT
1621 group_by_day,
1622 customer,
1623 SUM(model_api_spend) AS total_spend,
1624 jsonb_agg(jsonb_build_object(
1625 'model', model,
1626 'api_key', api_key,
1627 'spend', model_api_spend,
1628 'total_tokens', model_api_tokens
1629 )) AS metadata
1630 FROM
1631 SpendByModelApiKey
1632 GROUP BY
1633 group_by_day,
1634 customer
1635 ) AS aggregated
1636 GROUP BY
1637 group_by_day
1638 ORDER BY
1639 group_by_day;
1640 """
1642 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj)
1643 if db_response is None:
1644 return []
1646 return db_response
1647 elif group_by == "api_key":
1648 sql_query = """
1649 WITH SpendByModelApiKey AS (
1650 SELECT
1651 sl.api_key,
1652 sl.model,
1653 SUM(sl.spend) AS model_cost,
1654 SUM(sl.prompt_tokens) AS model_input_tokens,
1655 SUM(sl.completion_tokens) AS model_output_tokens
1656 FROM
1657 "LiteLLM_SpendLogs" sl
1658 WHERE
1659 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1660 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1661 GROUP BY
1662 sl.api_key,
1663 sl.model
1664 )
1665 SELECT
1666 api_key,
1667 SUM(model_cost) AS total_cost,
1668 SUM(model_input_tokens) AS total_input_tokens,
1669 SUM(model_output_tokens) AS total_output_tokens,
1670 jsonb_agg(jsonb_build_object(
1671 'model', model,
1672 'total_cost', model_cost,
1673 'total_input_tokens', model_input_tokens,
1674 'total_output_tokens', model_output_tokens
1675 )) AS model_details
1676 FROM
1677 SpendByModelApiKey
1678 GROUP BY
1679 api_key
1680 ORDER BY
1681 total_cost DESC;
1682 """
1683 db_response = await _query_raw_or_none(prisma_client, sql_query, start_date_obj, end_date_obj)
1684 if db_response is None:
1685 return []
1687 return db_response
1689 except Exception as e:
1690 raise HTTPException(
1691 status_code=status.HTTP_400_BAD_REQUEST,
1692 detail={"error": str(e)},
1693 )
1696_SPEND_REPORT_SCOPE_COLUMNS = frozenset({"api_key", "user", "team_id"})
1698_SPEND_REPORT_MAX_RANGE_DAYS = 366
1701def _scoped_spend_report_sql(scope_column: str) -> str:
1702 """Spend grouped by api_key with a per-model breakdown, cut to one scope column.
1704 ``scope_column`` is interpolated into the SQL, so it must come from
1705 ``_SPEND_REPORT_SCOPE_COLUMNS`` — never from caller input. Scope values are
1706 always bound as ``$3``.
1707 """
1708 if scope_column not in _SPEND_REPORT_SCOPE_COLUMNS:
1709 raise ValueError(f"Unsupported spend report scope column: {scope_column!r}")
1710 return f"""
1711 WITH SpendByModelApiKey AS (
1712 SELECT
1713 sl.api_key,
1714 sl.model,
1715 SUM(sl.spend) AS model_cost,
1716 SUM(sl.prompt_tokens) AS model_input_tokens,
1717 SUM(sl.completion_tokens) AS model_output_tokens
1718 FROM
1719 "LiteLLM_SpendLogs" sl
1720 WHERE
1721 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1722 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1723 AND sl.{scope_column} = $3
1724 GROUP BY
1725 sl.api_key,
1726 sl.model
1727 )
1728 SELECT
1729 api_key,
1730 SUM(model_cost) AS total_cost,
1731 SUM(model_input_tokens) AS total_input_tokens,
1732 SUM(model_output_tokens) AS total_output_tokens,
1733 jsonb_agg(jsonb_build_object(
1734 'model', model,
1735 'total_cost', model_cost,
1736 'total_input_tokens', model_input_tokens,
1737 'total_output_tokens', model_output_tokens
1738 )) AS model_details
1739 FROM
1740 SpendByModelApiKey
1741 GROUP BY
1742 api_key
1743 ORDER BY
1744 total_cost DESC;
1745 """
1748_ORG_SPEND_REPORT_SQL = """
1749 WITH SpendByModelApiKey AS (
1750 SELECT
1751 sl.api_key,
1752 sl.team_id,
1753 sl.model,
1754 SUM(sl.spend) AS model_cost,
1755 SUM(sl.prompt_tokens) AS model_input_tokens,
1756 SUM(sl.completion_tokens) AS model_output_tokens
1757 FROM
1758 "LiteLLM_SpendLogs" sl
1759 WHERE
1760 sl."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
1761 AND sl."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
1762 AND (
1763 sl.organization_id = $3
1764 OR (
1765 (sl.organization_id IS NULL OR sl.organization_id = '')
1766 AND sl.team_id = ANY($4::text[])
1767 )
1768 )
1769 GROUP BY
1770 sl.api_key,
1771 sl.team_id,
1772 sl.model
1773 )
1774 SELECT
1775 api_key,
1776 SUM(model_cost) AS total_cost,
1777 SUM(model_input_tokens) AS total_input_tokens,
1778 SUM(model_output_tokens) AS total_output_tokens,
1779 jsonb_agg(jsonb_build_object(
1780 'team_id', team_id,
1781 'model', model,
1782 'total_cost', model_cost,
1783 'total_input_tokens', model_input_tokens,
1784 'total_output_tokens', model_output_tokens
1785 )) AS model_details
1786 FROM
1787 SpendByModelApiKey
1788 GROUP BY
1789 api_key
1790 ORDER BY
1791 total_cost DESC;
1792"""
1795def _spend_report_prereqs() -> PrismaClient:
1796 from litellm.proxy.proxy_server import premium_user, prisma_client
1798 if prisma_client is None: 1798 ↛ 1799line 1798 didn't jump to line 1799 because the condition on line 1798 was never true
1799 raise HTTPException(
1800 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
1801 detail=CommonProxyErrors.db_not_connected_error.value,
1802 )
1803 if premium_user is not True: 1803 ↛ 1808line 1803 didn't jump to line 1808 because the condition on line 1803 was always true
1804 raise HTTPException(
1805 status_code=status.HTTP_403_FORBIDDEN,
1806 detail="/spend/report endpoint " + CommonProxyErrors.not_premium_user.value,
1807 )
1808 return prisma_client
1811def _parse_spend_report_date_range(start_date: str | None, end_date: str | None) -> tuple[datetime, datetime]:
1812 if start_date is None or end_date is None:
1813 raise HTTPException(
1814 status_code=status.HTTP_400_BAD_REQUEST,
1815 detail="Please provide start_date and end_date",
1816 )
1817 try:
1818 parsed = (
1819 datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc),
1820 datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc),
1821 )
1822 except ValueError:
1823 raise HTTPException(
1824 status_code=status.HTTP_400_BAD_REQUEST,
1825 detail="start_date and end_date must be in YYYY-MM-DD format",
1826 )
1827 start_date_obj, end_date_obj = parsed
1828 if end_date_obj < start_date_obj:
1829 raise HTTPException(
1830 status_code=status.HTTP_400_BAD_REQUEST,
1831 detail="start_date must be on or before end_date",
1832 )
1833 if end_date_obj - start_date_obj > timedelta(days=_SPEND_REPORT_MAX_RANGE_DAYS):
1834 raise HTTPException(
1835 status_code=status.HTTP_400_BAD_REQUEST,
1836 detail=f"Date range too large; maximum is {_SPEND_REPORT_MAX_RANGE_DAYS} days",
1837 )
1838 return parsed
1841def _resolve_spend_report_scope(
1842 user_api_key_dict: UserAPIKeyAuth,
1843 requested: str | None,
1844 caller_value: str | None,
1845 scope_name: str,
1846) -> str:
1847 """Return the scope value the caller may query spend for.
1849 Non-admin callers are clamped to their own identity: a ``requested`` value
1850 that differs from ``caller_value`` is a 403. Proxy admins (and admin
1851 viewers) may request any scope.
1852 """
1853 if requested:
1854 if requested != caller_value and not _is_admin_view_safe(user_api_key_dict=user_api_key_dict):
1855 raise HTTPException(
1856 status_code=status.HTTP_403_FORBIDDEN,
1857 detail=f"Not authorized to view spend for a {scope_name} other than your own",
1858 )
1859 return requested
1860 if caller_value is None:
1861 raise HTTPException(
1862 status_code=status.HTTP_400_BAD_REQUEST,
1863 detail=f"No {scope_name} associated with this API key; pass a {scope_name} query param",
1864 )
1865 return caller_value
1868async def _resolve_org_spend_report_scope(
1869 user_api_key_dict: UserAPIKeyAuth,
1870 organization_id: str | None,
1871 prisma_client: PrismaClient,
1872) -> tuple[str, tuple[str, ...]]:
1873 """Return the organization to report on and the team_ids belonging to it.
1875 Callable by proxy admins (any organization) and org admins of the target
1876 organization; every other caller is a 403 from ``_verify_org_access``.
1877 """
1878 from litellm.proxy.management_endpoints.organization_endpoints import _verify_org_access
1880 target_org = organization_id or user_api_key_dict.org_id
1881 if target_org is None:
1882 raise HTTPException(
1883 status_code=status.HTTP_400_BAD_REQUEST,
1884 detail="No organization_id associated with this API key; pass an organization_id query param",
1885 )
1886 await _verify_org_access(
1887 organization_id=target_org,
1888 user_api_key_dict=user_api_key_dict,
1889 prisma_client=prisma_client,
1890 )
1891 teams = await TeamRepository(prisma_client).find_by_organization_id(organization_id=target_org)
1892 return target_org, tuple(team.team_id for team in teams)
1895@router.get(
1896 "/key/spend/report",
1897 tags=("Budget & Spend Tracking",),
1898)
1899async def get_key_spend_report(
1900 user_api_key_dict: Annotated[UserAPIKeyAuth, Depends(user_api_key_auth)],
1901 start_date: Annotated[
1902 str | None, fastapi.Query(description="Time from which to start viewing spend (YYYY-MM-DD)")
1903 ] = None,
1904 end_date: Annotated[str | None, fastapi.Query(description="Time till which to view spend (YYYY-MM-DD)")] = None,
1905 api_key: Annotated[
1906 str | None,
1907 fastapi.Query(
1908 description=(
1909 "View spend for a specific api_key. Proxy admin only; other callers are scoped to their "
1910 "own key. Pass the key's sha256 hash so the raw key stays out of URLs and access logs. "
1911 "Example api_key='d5345c0ecc68ae6295c69f91926b2bd379e25481a40c34b5884d157a9f65d8fa'"
1912 )
1913 ),
1914 ] = None,
1915) -> Sequence[Mapping[str, object]]:
1916 """
1917 Get spend for the calling api_key over a date range, with a per-model breakdown.
1919 Same row shape as `/global/spend/report?api_key=...`, but callable by any key:
1920 non-admin callers are always scoped to their own api_key, while proxy admins
1921 may pass `?api_key=` to view any key.
1922 """
1923 prisma_client = _spend_report_prereqs()
1924 start_date_obj, end_date_obj = _parse_spend_report_date_range(start_date=start_date, end_date=end_date)
1925 requested = hash_token(token=api_key) if api_key is not None and api_key.startswith("sk-") else api_key
1926 scoped_api_key = _resolve_spend_report_scope(
1927 user_api_key_dict=user_api_key_dict,
1928 requested=requested,
1929 caller_value=LiteLLMProxyRequestSetup.get_logged_api_key(user_api_key_dict),
1930 scope_name="api_key",
1931 )
1932 db_response: Sequence[Mapping[str, object]] | None = await _query_raw_or_none(
1933 prisma_client,
1934 _scoped_spend_report_sql(scope_column="api_key"),
1935 start_date_obj,
1936 end_date_obj,
1937 scoped_api_key,
1938 )
1939 return db_response or ()
1942@router.get(
1943 "/user/spend/report",
1944 tags=("Budget & Spend Tracking",),
1945)
1946async def get_user_spend_report(
1947 user_api_key_dict: Annotated[UserAPIKeyAuth, Depends(user_api_key_auth)],
1948 start_date: Annotated[
1949 str | None, fastapi.Query(description="Time from which to start viewing spend (YYYY-MM-DD)")
1950 ] = None,
1951 end_date: Annotated[str | None, fastapi.Query(description="Time till which to view spend (YYYY-MM-DD)")] = None,
1952 internal_user_id: Annotated[
1953 str | None,
1954 fastapi.Query(
1955 description="View spend for a specific internal_user_id. Proxy admin only; other callers are scoped to their own user_id."
1956 ),
1957 ] = None,
1958) -> Sequence[Mapping[str, object]]:
1959 """
1960 Get spend for the calling user over a date range, grouped by api_key with a per-model breakdown.
1962 Same row shape as `/global/spend/report?internal_user_id=...`, but callable by
1963 any key with a user: non-admin callers are always scoped to their own user_id,
1964 while proxy admins may pass `?internal_user_id=` to view any user.
1965 """
1966 prisma_client = _spend_report_prereqs()
1967 start_date_obj, end_date_obj = _parse_spend_report_date_range(start_date=start_date, end_date=end_date)
1968 scoped_user_id = _resolve_spend_report_scope(
1969 user_api_key_dict=user_api_key_dict,
1970 requested=internal_user_id,
1971 caller_value=user_api_key_dict.user_id,
1972 scope_name="internal_user_id",
1973 )
1974 db_response: Sequence[Mapping[str, object]] | None = await _query_raw_or_none(
1975 prisma_client,
1976 _scoped_spend_report_sql(scope_column="user"),
1977 start_date_obj,
1978 end_date_obj,
1979 scoped_user_id,
1980 )
1981 return db_response or ()
1984@router.get(
1985 "/team/spend/report",
1986 tags=("Budget & Spend Tracking",),
1987)
1988async def get_team_spend_report(
1989 user_api_key_dict: Annotated[UserAPIKeyAuth, Depends(user_api_key_auth)],
1990 start_date: Annotated[
1991 str | None, fastapi.Query(description="Time from which to start viewing spend (YYYY-MM-DD)")
1992 ] = None,
1993 end_date: Annotated[str | None, fastapi.Query(description="Time till which to view spend (YYYY-MM-DD)")] = None,
1994 team_id: Annotated[
1995 str | None,
1996 fastapi.Query(
1997 description="View spend for a specific team_id. Proxy admin only; other callers are scoped to their key's team."
1998 ),
1999 ] = None,
2000) -> Sequence[Mapping[str, object]]:
2001 """
2002 Get spend for the calling key's team over a date range, grouped by api_key with a per-model breakdown.
2004 Callable by any key that belongs to a team: non-admin callers are always
2005 scoped to their key's team_id, while proxy admins may pass `?team_id=` to
2006 view any team.
2007 """
2008 prisma_client = _spend_report_prereqs()
2009 start_date_obj, end_date_obj = _parse_spend_report_date_range(start_date=start_date, end_date=end_date)
2010 scoped_team_id = _resolve_spend_report_scope(
2011 user_api_key_dict=user_api_key_dict,
2012 requested=team_id,
2013 caller_value=user_api_key_dict.team_id,
2014 scope_name="team_id",
2015 )
2016 db_response: Sequence[Mapping[str, object]] | None = await _query_raw_or_none(
2017 prisma_client,
2018 _scoped_spend_report_sql(scope_column="team_id"),
2019 start_date_obj,
2020 end_date_obj,
2021 scoped_team_id,
2022 )
2023 return db_response or ()
2026@router.get(
2027 "/organization/spend/report",
2028 tags=("Budget & Spend Tracking",),
2029)
2030async def get_organization_spend_report(
2031 user_api_key_dict: Annotated[UserAPIKeyAuth, Depends(user_api_key_auth)],
2032 start_date: Annotated[
2033 str | None, fastapi.Query(description="Time from which to start viewing spend (YYYY-MM-DD)")
2034 ] = None,
2035 end_date: Annotated[str | None, fastapi.Query(description="Time till which to view spend (YYYY-MM-DD)")] = None,
2036 organization_id: Annotated[
2037 str | None,
2038 fastapi.Query(
2039 description="View spend for a specific organization_id. Proxy admins may pass any organization; org admins are scoped to organizations they administer."
2040 ),
2041 ] = None,
2042) -> Sequence[Mapping[str, object]]:
2043 """
2044 Get spend for an organization over a date range, grouped by api_key with a per-model and per-team breakdown.
2046 Covers spend logged against the organization directly and against any of its
2047 teams. Callable by proxy admins (any organization) and org admins (their own
2048 organizations). Defaults to the calling key's organization_id when
2049 `?organization_id=` is omitted.
2050 """
2051 prisma_client = _spend_report_prereqs()
2052 start_date_obj, end_date_obj = _parse_spend_report_date_range(start_date=start_date, end_date=end_date)
2053 target_org, team_ids = await _resolve_org_spend_report_scope(
2054 user_api_key_dict=user_api_key_dict,
2055 organization_id=organization_id,
2056 prisma_client=prisma_client,
2057 )
2058 db_response: Sequence[Mapping[str, object]] | None = await _query_raw_or_none(
2059 prisma_client,
2060 _ORG_SPEND_REPORT_SQL,
2061 start_date_obj,
2062 end_date_obj,
2063 target_org,
2064 team_ids,
2065 )
2066 return db_response or ()
2069@router.get(
2070 "/global/spend/all_tag_names",
2071 tags=["Budget & Spend Tracking"],
2072 dependencies=[Depends(user_api_key_auth)],
2073 include_in_schema=False,
2074 responses={
2075 200: {"model": list[LiteLLM_SpendLogs]},
2076 },
2077)
2078async def global_get_all_tag_names():
2079 try:
2080 from litellm.proxy.proxy_server import prisma_client
2082 if prisma_client is None:
2083 raise Exception(
2084 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
2085 )
2087 sql_query: Final = """
2088 SELECT DISTINCT
2089 jsonb_array_elements_text(request_tags) AS individual_request_tag
2090 FROM "LiteLLM_SpendLogs";
2091 """
2093 db_response: Final[Sequence[_TagNameRow] | None] = await _query_raw_or_none(prisma_client, sql_query)
2094 if db_response is None:
2095 return []
2097 _tag_names: Final = []
2098 for row in db_response:
2099 _tag_names.append(row.get("individual_request_tag"))
2101 return {"tag_names": _tag_names}
2103 except Exception as e:
2104 if isinstance(e, HTTPException):
2105 raise ProxyException(
2106 message=getattr(e, "detail", f"/spend/all_tag_names Error({e})"),
2107 type="internal_error",
2108 param=getattr(e, "param", "None"),
2109 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
2110 )
2111 elif isinstance(e, ProxyException):
2112 raise e
2113 raise ProxyException(
2114 message="/spend/all_tag_names Error" + str(e),
2115 type="internal_error",
2116 param=getattr(e, "param", "None"),
2117 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
2118 )
2121@router.get(
2122 "/global/spend/tags",
2123 tags=["Budget & Spend Tracking"],
2124 dependencies=[Depends(user_api_key_auth)],
2125 responses={
2126 200: {"model": list[LiteLLM_SpendLogs]},
2127 },
2128)
2129async def global_view_spend_tags(
2130 start_date: str | None = fastapi.Query(
2131 default=None,
2132 description="Time from which to start viewing key spend",
2133 ),
2134 end_date: str | None = fastapi.Query(
2135 default=None,
2136 description="Time till which to view key spend",
2137 ),
2138 tags: str | None = fastapi.Query(
2139 default=None,
2140 description="comman separated tags to filter on",
2141 ),
2142):
2143 """
2144 LiteLLM Enterprise - View Spend Per Request Tag. Used by LiteLLM UI
2146 Example Request:
2147 ```
2148 curl -X GET "http://0.0.0.0:4000/spend/tags" \
2149-H "Authorization: Bearer sk-1234"
2150 ```
2152 Spend with Start Date and End Date
2153 ```
2154 curl -X GET "http://0.0.0.0:4000/spend/tags?start_date=2022-01-01&end_date=2022-02-01" \
2155-H "Authorization: Bearer sk-1234"
2156 ```
2157 """
2158 import traceback
2160 from litellm.proxy.proxy_server import prisma_client
2162 try:
2163 if prisma_client is None: 2163 ↛ 2164line 2163 didn't jump to line 2164 because the condition on line 2163 was never true
2164 raise Exception(
2165 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
2166 )
2168 if end_date is None or start_date is None:
2169 raise ProxyException(
2170 message="Please provide start_date and end_date",
2171 type="bad_request",
2172 param=None,
2173 code=status.HTTP_400_BAD_REQUEST,
2174 )
2175 response: Final = await ui_get_spend_by_tags(
2176 start_date=start_date,
2177 end_date=end_date,
2178 tags_str=tags,
2179 prisma_client=prisma_client,
2180 )
2182 return response
2183 except Exception as e:
2184 error_trace: Final = traceback.format_exc()
2185 error_str: Final = str(e) + "\n" + error_trace
2186 if isinstance(e, HTTPException): 2186 ↛ 2187line 2186 didn't jump to line 2187 because the condition on line 2186 was never true
2187 raise ProxyException(
2188 message=getattr(e, "detail", f"/spend/tags Error({error_str})"),
2189 type="internal_error",
2190 param=getattr(e, "param", "None"),
2191 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
2192 )
2193 elif isinstance(e, ProxyException):
2194 raise e
2195 raise ProxyException(
2196 message="/spend/tags Error" + error_str,
2197 type="internal_error",
2198 param=getattr(e, "param", "None"),
2199 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
2200 )
2203async def _get_spend_report_for_time_range(
2204 start_date: str,
2205 end_date: str,
2206):
2207 from litellm.proxy.proxy_server import prisma_client
2209 if prisma_client is None:
2210 verbose_proxy_logger.error(
2211 "Database not connected. Connect a database to your proxy for weekly, monthly spend reports"
2212 )
2213 return None
2215 # Normalize string inputs to tz-aware UTC datetimes so Prisma serializes
2216 # them with an explicit +00:00 suffix. Raw strings get bound as untyped
2217 # text, which forces Postgres to parse `::timestamptz` using the DB
2218 # session timezone and drifts the window by the offset even with the
2219 # AT TIME ZONE 'UTC' wrap below.
2220 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
2221 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
2223 try:
2224 sql_query = """
2225 SELECT
2226 t.team_alias,
2227 SUM(s.spend) AS total_spend
2228 FROM
2229 "LiteLLM_SpendLogs" s
2230 LEFT JOIN
2231 "LiteLLM_TeamTable" t ON s.team_id = t.team_id
2232 WHERE
2233 s."startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
2234 AND s."startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
2235 GROUP BY
2236 t.team_alias
2237 ORDER BY
2238 total_spend DESC;
2239 """
2240 response: Final[Sequence[_TeamSpendRow] | None] = await _query_raw_or_none(
2241 prisma_client, sql_query, start_date_obj, end_date_obj
2242 )
2244 # get spend per tag for today
2245 sql_query = """
2246 SELECT
2247 jsonb_array_elements_text(request_tags) AS individual_request_tag,
2248 SUM(spend) AS total_spend
2249 FROM "LiteLLM_SpendLogs"
2250 WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
2251 AND "startTime" < (($2::timestamptz + INTERVAL '1 day') AT TIME ZONE 'UTC')
2252 GROUP BY individual_request_tag
2253 ORDER BY total_spend DESC;
2254 """
2256 spend_per_tag: Final[Sequence[_TagSpendRow] | None] = await _query_raw_or_none(
2257 prisma_client, sql_query, start_date_obj, end_date_obj
2258 )
2260 return response, spend_per_tag
2261 except Exception as e:
2262 verbose_proxy_logger.error("Exception in _get_daily_spend_reports %s", e)
2265@router.post(
2266 "/spend/calculate",
2267 tags=["Budget & Spend Tracking"],
2268 dependencies=[Depends(user_api_key_auth)],
2269 responses={
2270 200: {
2271 "description": "The calculated cost",
2272 "content": {
2273 "application/json": {
2274 "schema": {
2275 "type": "object",
2276 "properties": {
2277 "cost": {
2278 "type": "number",
2279 "description": "The calculated cost",
2280 "example": 0.0,
2281 }
2282 },
2283 }
2284 }
2285 },
2286 }
2287 },
2288)
2289async def calculate_spend(request: SpendCalculateRequest):
2290 """
2291 Accepts all the params of completion_cost.
2293 Calculate spend **before** making call:
2295 Note: If you see a spend of $0.0 you need to set custom_pricing for your model: https://docs.litellm.ai/docs/proxy/custom_pricing
2297 ```
2298 curl --location 'http://localhost:4000/spend/calculate'
2299 --header 'Authorization: Bearer sk-1234'
2300 --header 'Content-Type: application/json'
2301 --data '{
2302 "model": "anthropic.claude-v2",
2303 "messages": [{"role": "user", "content": "Hey, how'''s it going?"}]
2304 }'
2305 ```
2307 Calculate spend **after** making call:
2309 ```
2310 curl --location 'http://localhost:4000/spend/calculate'
2311 --header 'Authorization: Bearer sk-1234'
2312 --header 'Content-Type: application/json'
2313 --data '{
2314 "completion_response": {
2315 "id": "chatcmpl-123",
2316 "object": "chat.completion",
2317 "created": 1677652288,
2318 "model": "gpt-3.5-turbo-0125",
2319 "system_fingerprint": "fp_44709d6fcb",
2320 "choices": [{
2321 "index": 0,
2322 "message": {
2323 "role": "assistant",
2324 "content": "Hello there, how may I assist you today?"
2325 },
2326 "logprobs": null,
2327 "finish_reason": "stop"
2328 }]
2329 "usage": {
2330 "prompt_tokens": 9,
2331 "completion_tokens": 12,
2332 "total_tokens": 21
2333 }
2334 }
2335 }'
2336 ```
2337 """
2338 try:
2339 from litellm import completion_cost
2340 from litellm.cost_calculator import CostPerToken
2341 from litellm.proxy.proxy_server import llm_router
2343 _cost = None
2344 if request.model is not None:
2345 if request.messages is None:
2346 raise HTTPException(
2347 status_code=400,
2348 detail="Bad Request - messages must be provided if 'model' is provided",
2349 )
2351 # check if model in llm_router
2352 _model_in_llm_router = None
2353 cost_per_token: CostPerToken | None = None
2354 if llm_router is not None: 2354 ↛ 2369line 2354 didn't jump to line 2369 because the condition on line 2354 was always true
2355 if llm_router.model_group_alias is not None and request.model in llm_router.model_group_alias: 2355 ↛ 2357line 2355 didn't jump to line 2357 because the condition on line 2355 was never true
2356 # lookup alias in llm_router
2357 _model_group_name: Final = llm_router.model_group_alias[request.model]
2358 for model in llm_router.model_list:
2359 if model.get("model_name") == _model_group_name:
2360 _model_in_llm_router = model
2362 else:
2363 # no model_group aliases set -> try finding model in llm_router
2364 # find model in llm_router
2365 for model in llm_router.model_list:
2366 if model.get("model_name") == request.model: 2366 ↛ 2367line 2366 didn't jump to line 2367 because the condition on line 2366 was never true
2367 _model_in_llm_router = model
2369 """
2370 3 cases for /spend/calculate
2372 1. user passes model, and model is defined on litellm config.yaml or in DB. use info on config or in DB in this case
2373 2. user passes model, and model is not defined on litellm config.yaml or in DB. Pass model as is to litellm.completion_cost
2374 3. user passes completion_response
2376 """
2377 if _model_in_llm_router is not None: 2377 ↛ 2378line 2377 didn't jump to line 2378 because the condition on line 2377 was never true
2378 _litellm_params: Final = _model_in_llm_router.get("litellm_params")
2379 _litellm_model_name: Final = _litellm_params.get("model")
2380 input_cost_per_token: Final = _litellm_params.get("input_cost_per_token")
2381 output_cost_per_token: Final = _litellm_params.get("output_cost_per_token")
2382 if input_cost_per_token is not None or output_cost_per_token is not None:
2383 cost_per_token = CostPerToken(
2384 input_cost_per_token=input_cost_per_token,
2385 output_cost_per_token=output_cost_per_token,
2386 )
2388 _cost = completion_cost(
2389 model=_litellm_model_name,
2390 messages=request.messages,
2391 custom_cost_per_token=cost_per_token,
2392 )
2393 else:
2394 _cost = completion_cost(model=request.model, messages=request.messages)
2395 elif request.completion_response is not None:
2396 _completion_response: Final = litellm.ModelResponse(**request.completion_response)
2397 _cost = completion_cost(completion_response=_completion_response)
2398 else:
2399 raise HTTPException(
2400 status_code=400,
2401 detail="Bad Request - Either 'model' or 'completion_response' must be provided",
2402 )
2403 return {"cost": _cost}
2404 except Exception as e:
2405 if isinstance(e, HTTPException):
2406 raise ProxyException(
2407 message=getattr(e, "detail", str(e)),
2408 type=getattr(e, "type", "None"),
2409 param=getattr(e, "param", "None"),
2410 code=getattr(e, "status_code", status.HTTP_400_BAD_REQUEST),
2411 )
2412 if isinstance(e, litellm.exceptions.ModelNotMappedError): 2412 ↛ 2413line 2412 didn't jump to line 2413 because the condition on line 2412 was never true
2413 raise ProxyException(
2414 message=str(e),
2415 type="invalid_request_error",
2416 param="model",
2417 code=status.HTTP_400_BAD_REQUEST,
2418 )
2419 error_msg: Final = f"{e}"
2420 raise ProxyException(
2421 message=getattr(e, "message", error_msg),
2422 type=getattr(e, "type", "None"),
2423 param=getattr(e, "param", "None"),
2424 code=getattr(e, "status_code", 500),
2425 )
2428class _SpendLogSearchCondition(NamedTuple):
2429 sql: str
2430 params: tuple[object, ...]
2433def _build_spend_log_search_condition(
2434 search: str,
2435 start_date: datetime,
2436 end_date: datetime,
2437 next_param_index: int,
2438) -> _SpendLogSearchCondition:
2439 """request_id (indexed) matches across all time; the unindexed id columns only inside the window."""
2440 raw: Final = f"${next_param_index}"
2441 window_start: Final = f"${next_param_index + 1}"
2442 window_end: Final = f"${next_param_index + 2}"
2443 sql: Final = (
2444 f"(request_id = {raw} OR ("
2445 f"\"startTime\" >= ({window_start}::timestamptz AT TIME ZONE 'UTC') "
2446 f"AND \"startTime\" <= ({window_end}::timestamptz AT TIME ZONE 'UTC') "
2447 f'AND (api_key = {raw} OR team_id = {raw} OR "user" = {raw} OR end_user = {raw} '
2448 f"OR session_id = {raw} OR model_id = {raw})))"
2449 )
2450 return _SpendLogSearchCondition(sql=sql, params=(search, start_date, end_date))
2453@router.get(
2454 "/spend/logs/v2",
2455 tags=["Budget & Spend Tracking"],
2456 dependencies=[Depends(user_api_key_auth)],
2457 responses={
2458 200: {"model": dict[str, Any]},
2459 },
2460)
2461@router.get(
2462 "/spend/logs/ui",
2463 tags=["Budget & Spend Tracking"],
2464 dependencies=[Depends(user_api_key_auth)],
2465 include_in_schema=False,
2466 responses={
2467 200: {"model": list[LiteLLM_SpendLogs]},
2468 },
2469)
2470async def ui_view_spend_logs(
2471 request: Request,
2472 api_key: str | None = fastapi.Query(
2473 default=None,
2474 description="Get spend logs based on api key",
2475 ),
2476 user_id: str | None = fastapi.Query(
2477 default=None,
2478 description="Get spend logs based on user_id",
2479 ),
2480 request_id: str | None = fastapi.Query(
2481 default=None,
2482 description="request_id to get spend logs for specific request_id",
2483 ),
2484 session_id: str | None = fastapi.Query(
2485 default=None,
2486 description="Filter spend logs by session_id (partial string match)",
2487 ),
2488 team_id: str | None = fastapi.Query(
2489 default=None,
2490 description="Filter spend logs by team_id",
2491 ),
2492 min_spend: float | None = fastapi.Query(
2493 default=None,
2494 description="Filter logs with spend greater than or equal to this value",
2495 ),
2496 max_spend: float | None = fastapi.Query(
2497 default=None,
2498 description="Filter logs with spend less than or equal to this value",
2499 ),
2500 start_date: str | None = fastapi.Query(
2501 default=None,
2502 description="Time from which to start viewing key spend",
2503 ),
2504 end_date: str | None = fastapi.Query(
2505 default=None,
2506 description="Time till which to view key spend",
2507 ),
2508 page: int = fastapi.Query(default=1, description="Page number for pagination", ge=1),
2509 page_size: int = fastapi.Query(default=50, description="Number of items per page", ge=1, le=1000),
2510 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
2511 status_filter: str | None = fastapi.Query(
2512 default=None, description="Filter logs by status (e.g., success, failure)"
2513 ),
2514 cache_hit_filter: str | None = fastapi.Query(
2515 default=None,
2516 description="Filter logs by cache state: 'hit' or 'miss'. Miss includes legacy rows with a null/unknown cache state",
2517 ),
2518 span_type: str | None = fastapi.Query(
2519 default=None,
2520 description="Filter logs by span type: llm, agent, mcp, or batch",
2521 ),
2522 model: str | None = fastapi.Query(default=None, description="Filter logs by model"),
2523 model_id: str | None = fastapi.Query(
2524 default=None,
2525 description="Filter logs by model ID (litellm model deployment id)",
2526 ),
2527 model_group: str | None = fastapi.Query(default=None, description="Filter logs by model group"),
2528 key_alias: str | None = fastapi.Query(default=None, description="Filter logs by key alias"),
2529 end_user: str | None = fastapi.Query(default=None, description="Filter logs by end user"),
2530 error_code: str | None = fastapi.Query(default=None, description="Filter logs by error code (e.g., '404', '500')"),
2531 error_message: str | None = fastapi.Query(
2532 default=None, description="Filter logs by error message (partial string match)"
2533 ),
2534 sort_by: str = fastapi.Query(
2535 default="startTime",
2536 description="Sort logs by field: spend, total_tokens, startTime, endTime, request_duration_ms, model, or ttft_ms",
2537 ),
2538 sort_order: str | None = fastapi.Query(
2539 default="desc",
2540 description="Sort order: asc or desc",
2541 ),
2542 exclude_internal_health_checks: bool = fastapi.Query(
2543 default=False,
2544 description="Exclude LiteLLM internal health check requests from results",
2545 ),
2546 group_by_session: bool = fastapi.Query(
2547 default=False,
2548 description="Paginate over sessions instead of raw logs: one representative row per session, total counts sessions",
2549 ),
2550 session_cursor: str | None = fastapi.Query(
2551 default=None,
2552 description=(
2553 "Keyset cursor '<last_activity>|<api_key>|<session_key>' from a previous group_by_session page. "
2554 "UI route only, honored when sorting by startTime"
2555 ),
2556 ),
2557 search: str | None = fastapi.Query(
2558 default=None,
2559 description=(
2560 "Match a log whose request_id, api_key (hash), team_id, user, end_user, "
2561 "session_id, or model_id equals this value. request_id matches across all time; the other columns "
2562 "match inside start_date/end_date, which stay required"
2563 ),
2564 ),
2565):
2566 """
2567 View spend logs with pagination support.
2568 Available at both `/spend/logs/v2` (public API) and `/spend/logs/ui` (internal UI).
2570 Returns paginated response with data, total, page, page_size, and total_pages.
2572 Example:
2573 ```
2574 curl -X GET "http://0.0.0.0:8000/spend/logs/v2?start_date=2025-11-25%2000:00:00&end_date=2025-11-26%2023:59:59&page=1&page_size=50" \
2575-H "Authorization: Bearer sk-1234"
2576 ```
2577 """
2578 from litellm.proxy.proxy_server import prisma_client
2580 if prisma_client is None: 2580 ↛ 2581line 2580 didn't jump to line 2581 because the condition on line 2580 was never true
2581 raise ProxyException(
2582 message="Prisma Client is not initialized",
2583 type="internal_error",
2584 param="None",
2585 code=status.HTTP_401_UNAUTHORIZED,
2586 )
2588 # Inline import — auth_utils participates in a proxy import cycle.
2589 from litellm.proxy.auth.auth_utils import get_request_route # noqa: PLC0415
2591 is_v2: Final = "/spend/logs/v2" in get_request_route(request)
2593 # Validate sort_by and sort_order
2594 valid_sort_fields: Final = {
2595 "spend",
2596 "total_tokens",
2597 "startTime",
2598 "endTime",
2599 "request_duration_ms",
2600 "model",
2601 "ttft_ms",
2602 }
2603 if sort_by not in valid_sort_fields:
2604 raise ProxyException(
2605 message=f"Invalid sort_by: {sort_by}. Must be one of: {', '.join(sorted(valid_sort_fields))}",
2606 type="bad_request",
2607 param="sort_by",
2608 code=status.HTTP_400_BAD_REQUEST,
2609 )
2610 if sort_order is not None and sort_order.lower() not in {"asc", "desc"}:
2611 raise ProxyException(
2612 message=f"Invalid sort_order: {sort_order}. Must be one of: asc, desc",
2613 type="bad_request",
2614 param="sort_order",
2615 code=status.HTTP_400_BAD_REQUEST,
2616 )
2617 if isinstance(cache_hit_filter, str) and cache_hit_filter not in {"hit", "miss"}:
2618 raise ProxyException(
2619 message=f"Invalid cache_hit_filter: {cache_hit_filter}. Must be one of: hit, miss",
2620 type="bad_request",
2621 param="cache_hit_filter",
2622 code=status.HTTP_400_BAD_REQUEST,
2623 )
2624 if isinstance(span_type, str) and span_type not in _SPAN_TYPE_SQL_CONDITIONS:
2625 raise ProxyException(
2626 message=f"Invalid span_type: {span_type}. Must be one of: llm, agent, mcp, batch",
2627 type="bad_request",
2628 param="span_type",
2629 code=status.HTTP_400_BAD_REQUEST,
2630 )
2632 try:
2633 is_admin_view: Final = _is_admin_view_safe(user_api_key_dict=user_api_key_dict)
2634 is_request_id_lookup: Final = request_id is not None and not is_v2
2635 is_search_lookup: Final = search is not None
2636 search_owns_window: Final = is_search_lookup and not is_v2
2638 if is_request_id_lookup and not is_search_lookup: 2638 ↛ 2644line 2638 didn't jump to line 2644 because the condition on line 2638 was never true
2639 # request_id is the @id primary key: it identifies a single row, so a
2640 # time window is meaningless. The dashboard always sends a default 24h
2641 # window, which hid ids copied from an older page (LIT-3981). Drop the
2642 # window for the id lookup so it resolves across all time; every other
2643 # query, including the public v2 route, still requires one (below).
2644 start_date_obj: datetime | None = None
2645 end_date_obj: datetime | None = None
2646 else:
2647 if start_date is None or end_date is None: 2647 ↛ 2654line 2647 didn't jump to line 2654 because the condition on line 2647 was always true
2648 raise ProxyException(
2649 message="Start date and end date are required",
2650 type="bad_request",
2651 param="None",
2652 code=status.HTTP_400_BAD_REQUEST,
2653 )
2654 formats: Final = ["%Y-%m-%d %H:%M:%S", "%Y-%m-%d"] if is_v2 else ["%Y-%m-%d %H:%M:%S"]
2656 def parse_date(date_str: str) -> datetime:
2657 date_str = date_str.strip()
2658 for fmt in formats:
2659 try:
2660 return datetime.strptime(date_str, fmt).replace(tzinfo=timezone.utc)
2661 except ValueError:
2662 continue
2663 expected: Final = "'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS'" if is_v2 else "'YYYY-MM-DD HH:MM:SS'"
2664 raise HTTPException(
2665 status_code=status.HTTP_400_BAD_REQUEST,
2666 detail=f"Invalid date format: {date_str}. Expected: {expected}",
2667 )
2669 start_date_obj = parse_date(start_date)
2670 end_date_obj = parse_date(end_date)
2672 # Build where conditions
2673 where_conditions: Final[dict[str, Any]] = {}
2674 if start_date_obj is not None and end_date_obj is not None:
2675 where_conditions["startTime"] = {
2676 "gte": start_date_obj.isoformat(), # Already in UTC, no need to add Z
2677 "lte": end_date_obj.isoformat(),
2678 }
2680 if team_id is not None:
2681 where_conditions["team_id"] = team_id
2683 status_condition: Final = _build_status_filter_condition(status_filter)
2684 if status_condition:
2685 where_conditions.update(status_condition)
2687 if api_key is not None:
2688 where_conditions["api_key"] = api_key
2690 if user_id is not None:
2691 where_conditions["user"] = user_id
2693 if request_id is not None:
2694 where_conditions["request_id"] = request_id
2696 if model is not None:
2697 where_conditions["model"] = model
2699 if model_id is not None:
2700 where_conditions["model_id"] = model_id
2702 if model_group is not None:
2703 where_conditions["model_group"] = model_group
2705 # Build metadata filters
2706 metadata_filters: Final = []
2707 if key_alias is not None:
2708 metadata_filters.append(
2709 {
2710 "path": ["user_api_key_alias"],
2711 "string_contains": key_alias,
2712 }
2713 )
2715 if error_code is not None:
2716 metadata_filters.append(
2717 {
2718 "path": ["error_information", "error_code"],
2719 "equals": f'"{error_code}"',
2720 }
2721 )
2723 if error_message is not None:
2724 metadata_filters.append(
2725 {
2726 "path": ["error_information", "error_message"],
2727 "string_contains": error_message,
2728 }
2729 )
2731 if metadata_filters:
2732 if len(metadata_filters) == 1:
2733 where_conditions["metadata"] = metadata_filters[0]
2734 else:
2735 where_conditions["AND"] = where_conditions.get("AND", []) + [
2736 {"metadata": filter_cond} for filter_cond in metadata_filters
2737 ]
2738 if end_user is not None:
2739 where_conditions["end_user"] = end_user
2741 if min_spend is not None or max_spend is not None:
2742 where_conditions["spend"] = {}
2743 if min_spend is not None:
2744 where_conditions["spend"]["gte"] = min_spend
2745 if max_spend is not None:
2746 where_conditions["spend"]["lte"] = max_spend
2747 # A request_id lookup drops the date window, so a non-admin could otherwise
2748 # reach any single row by id; require they own one of the matches, mirroring
2749 # the detail endpoint, and keep the general scoping below so a colliding
2750 # foreign row is filtered out rather than served or allowed to deny the
2751 # caller their own row. Scoped to the UI route so the public v2 contract is
2752 # unchanged.
2753 if request_id is not None and not is_v2 and not is_admin_view:
2754 await _assert_user_can_view_request_id(
2755 prisma_client=prisma_client,
2756 user_api_key_dict=user_api_key_dict,
2757 request_id=request_id,
2758 )
2759 user_scope_applies: Final = (
2760 not is_admin_view
2761 and team_id is None
2762 and (is_request_id_lookup or _can_user_view_spend_log(user_api_key_dict=user_api_key_dict))
2763 )
2764 permitted_team_ids: Final = (
2765 await _get_permitted_team_ids_for_spend_logs_or_empty(
2766 prisma_client=prisma_client,
2767 user_api_key_dict=user_api_key_dict,
2768 )
2769 if user_scope_applies
2770 else ()
2771 )
2772 explicit_user_requires_caller_scope: Final = (
2773 user_scope_applies and not permitted_team_ids and user_id is not None
2774 )
2775 if not is_admin_view:
2776 if team_id is not None:
2777 can_view_team: Final = await _can_team_member_view_log(
2778 prisma_client=prisma_client,
2779 user_api_key_dict=user_api_key_dict,
2780 team_id=team_id,
2781 )
2782 if not can_view_team:
2783 raise HTTPException(
2784 status_code=status.HTTP_403_FORBIDDEN,
2785 detail={"error": f"Not authorized to view team spend for team_id={team_id}"},
2786 )
2787 where_conditions["team_id"] = team_id
2788 elif user_scope_applies:
2789 if permitted_team_ids:
2790 if user_id is None:
2791 where_conditions.pop("user", None)
2792 where_conditions["OR"] = [
2793 {"user": user_api_key_dict.user_id},
2794 {"team_id": {"in": permitted_team_ids}},
2795 ]
2796 else:
2797 if user_id is None:
2798 where_conditions["user"] = user_api_key_dict.user_id
2799 else:
2800 where_conditions["AND"] = where_conditions.get("AND", []) + [
2801 {"user": user_api_key_dict.user_id}
2802 ]
2803 where_conditions.pop("team_id", None)
2804 # Calculate skip value for pagination
2805 skip: Final = (page - 1) * page_size
2807 # Build order clause from sort_by and sort_order
2808 order_column: Final = sort_by
2809 order_direction: Final = (sort_order or "desc").lower()
2811 # Build raw SQL to fetch paginated data WITHOUT heavy columns
2812 # (messages, response, proxy_server_request can be hundreds of KB per row).
2813 # These are only needed in the detail endpoint /spend/logs/ui/{request_id}.
2814 sql_conditions: Final[list[str]] = []
2815 sql_params: Final[list[object]] = []
2816 p = 1 # parameter index counter
2818 # Date range. Wrap the param side with `AT TIME ZONE 'UTC'` so comparison
2819 # against the plain `timestamp` column does not depend on the DB session
2820 # timezone (see #22529). Absent for a request_id-only lookup (see above).
2821 if start_date_obj is not None and end_date_obj is not None and not search_owns_window:
2822 sql_conditions.append(f"\"startTime\" >= (${p}::timestamptz AT TIME ZONE 'UTC')")
2823 sql_params.append(start_date_obj)
2824 p += 1
2825 sql_conditions.append(f"\"startTime\" <= (${p}::timestamptz AT TIME ZONE 'UTC')")
2826 sql_params.append(end_date_obj)
2827 p += 1
2829 if search is not None and start_date_obj is not None and end_date_obj is not None:
2830 search_condition: Final = _build_spend_log_search_condition(
2831 search=search,
2832 start_date=start_date_obj,
2833 end_date=end_date_obj,
2834 next_param_index=p,
2835 )
2836 sql_conditions.append(search_condition.sql)
2837 sql_params.extend(search_condition.params)
2838 p += len(search_condition.params) # rebind-ok: advances the file's shared $N placeholder counter
2840 # Equality filters - read effective values from where_conditions (post-authorization)
2841 for sql_col, wc_key in [
2842 ("team_id", "team_id"),
2843 ('"user"', "user"),
2844 ("api_key", "api_key"),
2845 ("model", "model"),
2846 ("model_id", "model_id"),
2847 ("model_group", "model_group"),
2848 ("end_user", "end_user"),
2849 ]:
2850 val = where_conditions.get(wc_key)
2851 if val is not None and isinstance(val, str):
2852 sql_conditions.append(f"{sql_col} = ${p}")
2853 sql_params.append(val)
2854 p += 1
2856 request_id_filter: Final = where_conditions.get("request_id")
2857 exact_request_id_first: Final = f"(request_id = ${p}) DESC, " if isinstance(request_id_filter, str) else ""
2858 if isinstance(request_id_filter, str):
2859 sql_conditions.append(f"(request_id = ${p} OR litellm_call_id = ${p})")
2860 sql_params.append(request_id_filter)
2861 p += 1
2863 # Multi-team OR filter: (user = $X OR team_id = ANY($Y))
2864 if permitted_team_ids:
2865 or_clause: Final = f'("user" = ${p} OR team_id = ANY(${p + 1}::text[]))'
2866 sql_params.append(user_api_key_dict.user_id)
2867 sql_params.append(permitted_team_ids)
2868 p += 2
2869 sql_conditions.append(or_clause)
2870 elif explicit_user_requires_caller_scope:
2871 sql_conditions.append(f'"user" = ${p}')
2872 sql_params.append(user_api_key_dict.user_id)
2873 p += 1
2875 if session_id is not None and isinstance(session_id, str):
2876 like_escaped_session_id: Final = session_id.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
2877 sql_conditions.append(f"session_id LIKE ${p}")
2878 sql_params.append(f"%{like_escaped_session_id}%")
2879 p += 1
2881 # Status filter
2882 if status_filter is not None:
2883 if status_filter == "success":
2884 sql_conditions.append("(status = 'success' OR status IS NULL)")
2885 else:
2886 sql_conditions.append(f"status = ${p}")
2887 sql_params.append(status_filter)
2888 p += 1
2890 if cache_hit_filter == "hit":
2891 sql_conditions.append("LOWER(cache_hit) = 'true'")
2892 elif cache_hit_filter == "miss":
2893 sql_conditions.append("(cache_hit IS NULL OR LOWER(cache_hit) != 'true')")
2895 span_type_condition: Final = _span_type_sql_condition(span_type)
2896 if span_type_condition is not None:
2897 sql_conditions.append(span_type_condition)
2899 if exclude_internal_health_checks:
2900 sql_conditions.append(f"api_key NOT IN (${p}, ${p + 1})")
2901 sql_params.extend(_INTERNAL_HEALTH_CHECK_API_KEYS)
2902 p += 2 # rebind-ok: advances the file's shared $N placeholder counter
2904 # Spend range
2905 if min_spend is not None:
2906 sql_conditions.append(f"spend >= ${p}")
2907 sql_params.append(min_spend)
2908 p += 1
2909 if max_spend is not None:
2910 sql_conditions.append(f"spend <= ${p}")
2911 sql_params.append(max_spend)
2912 p += 1
2914 # Metadata JSON filters (PostgreSQL JSONB operators)
2915 if key_alias is not None:
2916 sql_conditions.append(f"metadata->>'user_api_key_alias' LIKE ${p}")
2917 sql_params.append(f"%{key_alias}%")
2918 p += 1
2919 if error_code is not None:
2920 sql_conditions.append(f"metadata->'error_information'->>'error_code' = ${p}")
2921 sql_params.append(error_code)
2922 p += 1
2923 if error_message is not None:
2924 sql_conditions.append(f"metadata->'error_information'->>'error_message' LIKE ${p}")
2925 sql_params.append(f"%{error_message}%")
2926 p += 1
2928 if (
2929 group_by_session is True
2930 and not is_v2
2931 and not is_request_id_lookup
2932 and not is_search_lookup
2933 and sort_by == "startTime"
2934 ):
2935 return await _ui_session_grouped_spend_logs(
2936 prisma_client=prisma_client,
2937 sql_conditions=sql_conditions,
2938 sql_params=sql_params,
2939 next_param_index=p,
2940 page=page,
2941 page_size=page_size,
2942 sort_desc=order_direction != "asc",
2943 session_cursor=session_cursor,
2944 )
2946 # Build the ORDER BY expression. ttft_ms is computed from
2947 # completionStartTime - startTime; non-streaming rows (where
2948 # completionStartTime is null or equals endTime) yield NULL, so we
2949 # append NULLS LAST in that case to keep them at the bottom regardless
2950 # of direction. The other sort columns are non-null in the result set,
2951 # so we leave the NULLS clause off and preserve their existing DESC
2952 # semantics.
2953 _sql_dir: Final = "ASC" if order_direction == "asc" else "DESC"
2954 _nulls_clause = ""
2955 if order_column == "ttft_ms":
2956 _order_expr = (
2957 'CASE WHEN "completionStartTime" IS NULL '
2958 'OR "completionStartTime" = "endTime" THEN NULL '
2959 'ELSE (EXTRACT(EPOCH FROM ("completionStartTime" - "startTime")) * 1000) END'
2960 )
2961 _nulls_clause = " NULLS LAST"
2962 elif order_column in ("startTime", "endTime"):
2963 _order_expr = f'"{order_column}"'
2964 else:
2965 _order_expr = order_column
2967 joined_conditions: Final = " AND ".join(sql_conditions)
2968 session_grouping: Final = group_by_session is True and not is_search_lookup
2969 count_group_clause: Final = f"GROUP BY {_SESSION_GROUP_KEY_SQL}" if session_grouping else ""
2970 count_query: Final = f"""
2971 SELECT COUNT(*) AS total_count
2972 FROM (
2973 SELECT 1
2974 FROM "LiteLLM_SpendLogs"
2975 WHERE {joined_conditions}
2976 {count_group_clause}
2977 LIMIT ${p}
2978 ) AS bounded_matches
2979 """
2980 count_rows: Final[Sequence[_SpendLogsCountRow] | None] = await _query_raw_or_none(
2981 prisma_client, count_query, *sql_params, SPEND_LOGS_PAGINATION_COUNT_CAP + 1
2982 )
2983 raw_total: Final = int(count_rows[0]["total_count"]) if count_rows else 0
2984 total_is_capped: Final = raw_total > SPEND_LOGS_PAGINATION_COUNT_CAP
2985 total_records: Final = SPEND_LOGS_PAGINATION_COUNT_CAP if total_is_capped else raw_total
2987 sql_query: Final = (
2988 f"""
2989 SELECT * FROM (
2990 SELECT DISTINCT ON ({_SESSION_GROUP_KEY_SQL})
2991 {_SPEND_LOG_LIST_COLUMNS}
2992 FROM "LiteLLM_SpendLogs"
2993 WHERE {joined_conditions}
2994 ORDER BY {_SESSION_GROUP_KEY_SQL}, call_type IN {_MCP_CALL_TYPES_SQL}, "startTime" DESC
2995 ) AS session_representatives
2996 ORDER BY {exact_request_id_first}{_order_expr} {_sql_dir}{_nulls_clause}, request_id
2997 LIMIT ${p} OFFSET ${p + 1}
2998 """
2999 if session_grouping
3000 else f"""
3001 SELECT
3002 {_SPEND_LOG_LIST_COLUMNS}
3003 FROM "LiteLLM_SpendLogs"
3004 WHERE {joined_conditions}
3005 ORDER BY {exact_request_id_first}{_order_expr} {_sql_dir}{_nulls_clause}
3006 LIMIT ${p} OFFSET ${p + 1}
3007 """
3008 )
3009 sql_params.extend([page_size, skip])
3011 data: Final = await prisma_client.db.query_raw(sql_query, *sql_params)
3013 if request_id is not None and not is_v2 and not is_admin_view:
3014 await _assert_user_owns_fetched_spend_rows(
3015 prisma_client=prisma_client,
3016 user_api_key_dict=user_api_key_dict,
3017 rows=data,
3018 request_id=request_id,
3019 )
3021 _hydrate_spend_log_metadata(data)
3023 # Calculate total pages
3024 total_pages: Final = (total_records + page_size - 1) // page_size
3026 verbose_proxy_logger.debug("data= %s", json.dumps(data, indent=4, default=str))
3028 return await _build_ui_spend_logs_response(
3029 prisma_client,
3030 data,
3031 total_records,
3032 page,
3033 page_size,
3034 total_pages,
3035 enrich_session_counts=not is_v2,
3036 total_is_capped=total_is_capped,
3037 )
3038 except Exception as e:
3039 verbose_proxy_logger.exception("Error in ui_view_spend_logs: %s", e)
3040 raise handle_exception_on_proxy(e)
3043class _SessionPageRow(TypedDict):
3044 session_key: ReadOnly[str]
3045 api_key: ReadOnly[str]
3046 last_activity: ReadOnly[str]
3049def _parse_session_cursor(session_cursor: str | None) -> tuple[str, str, str] | None:
3050 if session_cursor is None or session_cursor.count("|") < 2:
3051 return None
3052 last_activity, _, rest = session_cursor.partition("|")
3053 api_key, _, session_key = rest.partition("|")
3054 if not last_activity or not session_key:
3055 return None
3056 return (last_activity, session_key, api_key)
3059async def _fetch_session_representatives(
3060 prisma_client: "PrismaClient",
3061 where_clause: str,
3062 sql_params: Sequence[object],
3063 next_param_index: int,
3064 session_keys: Sequence[tuple[str, str]],
3065) -> list[dict[str, object]]: # mutable-ok: _build_ui_spend_logs_response writes session counts onto each row
3066 """Fetch the newest non-MCP row of each ``(session_key, api_key)`` session, in ``session_keys`` order."""
3067 rep_query: Final = f"""
3068 SELECT * FROM (
3069 SELECT DISTINCT ON ({_SESSION_GROUP_KEY_SQL})
3070 {_SPEND_LOG_LIST_COLUMNS}
3071 FROM "LiteLLM_SpendLogs"
3072 WHERE {where_clause}
3073 AND ({_SESSION_GROUP_KEY_SQL}) IN (
3074 SELECT * FROM unnest(${next_param_index}::text[], ${next_param_index + 1}::text[])
3075 )
3076 ORDER BY {_SESSION_GROUP_KEY_SQL}, call_type IN {_MCP_CALL_TYPES_SQL}, "startTime" DESC
3077 ) AS session_representatives
3078 """
3079 rep_rows: Final[Sequence[dict[str, object]]] = await _query_raw( # mutable-ok: rows are enriched in place
3080 prisma_client,
3081 rep_query,
3082 *sql_params,
3083 [session_key for session_key, _ in session_keys], # mutable-ok: prisma serializes array params from a list
3084 [api_key for _, api_key in session_keys], # mutable-ok: prisma serializes array params from a list
3085 )
3086 rep_by_key: Final[Mapping[tuple[str, str], dict[str, object]]] = MappingProxyType( # mutable-ok: same rows
3087 {(str(row["session_id"] or row["request_id"]), str(row["api_key"])): row for row in rep_rows}
3088 )
3089 return [rep_by_key[key] for key in session_keys if key in rep_by_key] # mutable-ok: rows are enriched in place
3092async def _count_grouped_sessions(
3093 prisma_client: "PrismaClient",
3094 where_clause: str,
3095 sql_params: Sequence[object],
3096 next_param_index: int,
3097) -> tuple[int, bool]:
3098 """Count the sessions matching the filter, returning ``(total, total_is_capped)`` bounded by the count cap."""
3099 count_query: Final = f"""
3100 SELECT COUNT(*) AS total_count
3101 FROM (
3102 SELECT 1
3103 FROM "LiteLLM_SpendLogs"
3104 WHERE {where_clause}
3105 GROUP BY {_SESSION_GROUP_KEY_SQL}
3106 LIMIT ${next_param_index}
3107 ) AS bounded_sessions
3108 """
3109 count_rows: Final[Sequence[_SpendLogsCountRow]] = await _query_raw(
3110 prisma_client, count_query, *sql_params, SPEND_LOGS_PAGINATION_COUNT_CAP + 1
3111 )
3112 raw_total: Final = int(count_rows[0]["total_count"]) if count_rows else 0
3113 return (
3114 (SPEND_LOGS_PAGINATION_COUNT_CAP, True) if raw_total > SPEND_LOGS_PAGINATION_COUNT_CAP else (raw_total, False)
3115 )
3118async def _ui_session_grouped_spend_logs(
3119 prisma_client: "PrismaClient",
3120 sql_conditions: Sequence[str],
3121 sql_params: Sequence[object],
3122 next_param_index: int,
3123 page: int,
3124 page_size: int,
3125 sort_desc: bool,
3126 session_cursor: str | None,
3127) -> Mapping[str, object]:
3128 """
3129 One row per session, keyset-paginated by session last activity.
3131 Sessions are derived on the fly from ``LiteLLM_SpendLogs`` (no extra
3132 table): rows sharing a ``session_id`` and ``api_key`` form a session, rows
3133 without a session id are singletons keyed by ``request_id``. A page is the
3134 next ``page_size`` sessions ordered by ``(MAX(startTime), session_key,
3135 api_key)``, resumed from the ``session_cursor`` keyset
3136 ``'<last_activity>|<api_key>|<session_key>'`` instead of an OFFSET, so
3137 page depth does not degrade the query plan. A request for ``page > 1``
3138 without a cursor (the UI jumping straight to the last page, or back to a
3139 page it never walked through) falls back to ``OFFSET (page - 1) *
3140 page_size``, trimmed to the end of the ``SPEND_LOGS_PAGINATION_COUNT_CAP``
3141 window the capped ``total`` promises, so a page never runs past that total
3142 and one starting at or past it returns no rows without a query. Each session is represented
3143 by its newest non-MCP row, enriched by ``_build_ui_spend_logs_response``
3144 exactly like the flat listing, and the response carries
3145 ``next_session_cursor`` / ``has_more`` while ``total`` counts sessions
3146 (capped like the flat total). A page that runs out of sessions while still
3147 holding some is itself the end of the list, so its ``total`` is
3148 ``offset + len(page)`` and the grouped count query is skipped; a page that
3149 starts past the end says nothing about the total, so that one is counted.
3150 """
3151 where_clause: Final = " AND ".join(sql_conditions) if sql_conditions else "TRUE"
3152 cmp_op: Final = "<" if sort_desc else ">"
3153 direction: Final = "DESC" if sort_desc else "ASC"
3155 cursor: Final = _parse_session_cursor(session_cursor)
3156 having_clause: Final = (
3157 f'HAVING (MAX("startTime"), {_SESSION_GROUP_KEY_SQL}) {cmp_op} '
3158 f"(${next_param_index}::timestamp, ${next_param_index + 1}, ${next_param_index + 2})"
3159 if cursor
3160 else ""
3161 )
3162 cursor_params: Final[tuple[object, ...]] = cursor if cursor else ()
3163 limit_index: Final = next_param_index + len(cursor_params)
3164 offset: Final = (page - 1) * page_size if cursor is None else 0
3165 page_limit: Final = min(page_size, SPEND_LOGS_PAGINATION_COUNT_CAP - offset)
3166 offset_params: Final[tuple[int, ...]] = (offset,) if offset and page_limit > 0 else ()
3167 offset_clause: Final = f"OFFSET ${limit_index + 1}" if offset_params else ""
3169 page_query: Final = f"""
3170 SELECT {_SESSION_KEY_EXPR} AS session_key,
3171 api_key,
3172 MAX("startTime")::text AS last_activity
3173 FROM "LiteLLM_SpendLogs"
3174 WHERE {where_clause}
3175 GROUP BY {_SESSION_GROUP_KEY_SQL}
3176 {having_clause}
3177 ORDER BY MAX("startTime") {direction}, {_SESSION_KEY_EXPR} {direction}, api_key {direction}
3178 LIMIT ${limit_index} {offset_clause}
3179 """
3180 page_rows: Final[Sequence[_SessionPageRow]] = (
3181 ()
3182 if page_limit <= 0
3183 else await _query_raw(prisma_client, page_query, *sql_params, *cursor_params, page_limit + 1, *offset_params)
3184 )
3186 has_more: Final = len(page_rows) > page_limit
3187 visible_rows: Final = page_rows[:page_limit]
3188 next_cursor: Final = (
3189 f"{visible_rows[-1]['last_activity']}|{visible_rows[-1]['api_key']}|{visible_rows[-1]['session_key']}"
3190 if has_more and visible_rows
3191 else None
3192 )
3194 page_starts_inside_the_list: Final = offset == 0 or len(page_rows) > 0
3195 page_ends_the_list: Final = cursor is None and page_limit > 0 and not has_more and page_starts_inside_the_list
3196 total_records, total_is_capped = (
3197 (offset + len(page_rows), False)
3198 if page_ends_the_list
3199 else await _count_grouped_sessions(prisma_client, where_clause, sql_params, next_param_index)
3200 )
3202 session_keys: Final = tuple((row["session_key"], row["api_key"]) for row in visible_rows)
3203 data: Final[list[dict[str, object]]] = ( # mutable-ok: _build_ui_spend_logs_response writes onto each row
3204 await _fetch_session_representatives(
3205 prisma_client=prisma_client,
3206 where_clause=where_clause,
3207 sql_params=sql_params,
3208 next_param_index=next_param_index,
3209 session_keys=session_keys,
3210 )
3211 if session_keys
3212 else [] # mutable-ok: downstream enrichment mutates rows in place
3213 )
3214 _hydrate_spend_log_metadata(data)
3216 total_pages: Final = (total_records + page_size - 1) // page_size
3217 response: Final[Mapping[str, object]] = await _build_ui_spend_logs_response(
3218 prisma_client,
3219 data,
3220 total_records,
3221 page,
3222 page_size,
3223 total_pages,
3224 enrich_session_counts=True,
3225 total_is_capped=total_is_capped,
3226 )
3227 return {**response, "next_session_cursor": next_cursor, "has_more": has_more} # mutable-ok: FastAPI response body
3230class RequestResponsePayload(NamedTuple):
3231 messages: str | list | dict | None
3232 response: str | list | dict | None
3233 proxy_server_request: str | dict | None
3236_EMPTY_SPEND_LOG_VALUES: Final = frozenset({"", "{}", "[]", "null"})
3239def _spend_log_field_has_content(value: str | list | dict | None) -> bool:
3240 if value is None:
3241 return False
3242 if isinstance(value, str):
3243 return value.strip() not in _EMPTY_SPEND_LOG_VALUES
3244 if isinstance(value, (list, dict)):
3245 return len(value) > 0
3246 return True
3249def _hydrate_spend_log_metadata(rows: Sequence[Mapping[str, object]]) -> None:
3250 """Re-hydrate the JSONB ``metadata`` column returned by ``query_raw`` as a string.
3252 The Prisma serialiser bypasses the model-layer JSON hydration we get on the ORM
3253 path, while the UI reads ``metadata.status`` / ``metadata.error_information`` /
3254 ``metadata.internal_call_origin`` as object fields. Property access on a string
3255 is silently undefined, so failure rows looked like successes (#29674). Every
3256 ``query_raw`` reader of this column goes through here so a new one cannot
3257 reintroduce that.
3258 """
3259 for row in rows:
3260 if not isinstance(row, dict):
3261 continue
3262 md = row.get("metadata")
3263 if isinstance(md, str):
3264 try:
3265 row["metadata"] = json.loads(md)
3266 except (ValueError, TypeError):
3267 row["metadata"] = {}
3270def _cold_storage_object_key_from_metadata(
3271 metadata: str | Mapping[str, object] | None,
3272) -> str | None:
3273 if isinstance(metadata, str):
3274 try:
3275 metadata = json.loads(metadata)
3276 except (json.JSONDecodeError, TypeError):
3277 return None
3278 if not isinstance(metadata, dict):
3279 return None
3280 object_key: Final = metadata.get("cold_storage_object_key")
3281 return object_key if isinstance(object_key, str) and object_key else None
3284async def _resolve_request_response_payload(
3285 row: Mapping[str, Any],
3286 cold_storage_handler: "ColdStorageHandler",
3287) -> RequestResponsePayload:
3288 """
3289 Decide where the prompt/response come from for a single spend-log row.
3291 PG holds the content when ``store_prompts_in_spend_logs`` is on; otherwise it
3292 holds ``"{}"`` placeholders and the real payload lives in cold storage keyed
3293 by ``metadata.cold_storage_object_key``. The choice is made on actual row
3294 content, not config flags, so historical and mixed-storage rows both resolve
3295 correctly.
3296 """
3297 messages: Final = row.get("messages")
3298 response: Final = row.get("response")
3299 proxy_server_request: Final = row.get("proxy_server_request")
3301 pg_payload: Final = RequestResponsePayload(messages, response, proxy_server_request)
3302 stored_request: Final = classifier_input_snapshot(proxy_server_request)
3303 truncated_audit: Final = bool(stored_request and classifier_audit_fields(stored_request)) and (
3304 LITELLM_TRUNCATED_PAYLOAD_FIELD in str(proxy_server_request)
3305 )
3306 if not truncated_audit and (
3307 _spend_log_field_has_content(messages)
3308 or _spend_log_field_has_content(response)
3309 or _spend_log_field_has_content(proxy_server_request)
3310 ):
3311 return pg_payload
3313 object_key: Final = _cold_storage_object_key_from_metadata(row.get("metadata"))
3314 if object_key is None:
3315 return pg_payload
3317 try:
3318 payload: Final = await cold_storage_handler.get_proxy_server_request_from_cold_storage_with_object_key(
3319 object_key=object_key
3320 )
3321 except Exception:
3322 verbose_proxy_logger.warning(
3323 "Failed to fetch cold storage payload for key %s; falling back to DB values",
3324 object_key,
3325 exc_info=True,
3326 )
3327 return pg_payload
3328 if payload is None:
3329 return pg_payload
3331 cold_audit: Final = classifier_audit_fields(payload)
3332 resolved_request: Final = (
3333 {
3334 **(classifier_input_snapshot(payload.get("proxy_server_request")) or stored_request or EMPTY_MAPPING),
3335 **cold_audit,
3336 }
3337 if cold_audit
3338 else payload.get("proxy_server_request")
3339 )
3340 if truncated_audit:
3341 return RequestResponsePayload(messages, response, resolved_request if cold_audit else proxy_server_request)
3343 return RequestResponsePayload(
3344 messages=payload.get("messages"),
3345 response=payload.get("response"),
3346 proxy_server_request=resolved_request,
3347 )
3350@router.get(
3351 "/spend/logs/ui/{request_id}",
3352 tags=["Budget & Spend Tracking"],
3353 dependencies=[Depends(user_api_key_auth)],
3354 include_in_schema=False,
3355)
3356async def ui_view_request_response_for_request_id(
3357 request_id: str,
3358 start_date: str | None = fastapi.Query(
3359 default=None,
3360 description="Time from which to start viewing key spend",
3361 ),
3362 end_date: str | None = fastapi.Query(
3363 default=None,
3364 description="Time till which to view key spend",
3365 ),
3366 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
3367):
3368 """
3369 View request / response for a specific request_id
3371 - goes through all callbacks, checks if any of them have a @property -> has_request_response_payload
3372 - if so, it will return the request and response payload
3373 """
3374 from litellm.proxy.proxy_server import prisma_client
3376 caller_is_admin: Final = _is_admin_view_safe(user_api_key_dict=user_api_key_dict)
3377 if not caller_is_admin:
3378 if prisma_client is None:
3379 raise HTTPException(
3380 status_code=status.HTTP_403_FORBIDDEN,
3381 detail={
3382 "error": (
3383 "Cannot authorize spend log access without a database "
3384 "connection. Connect a database or use a proxy admin key."
3385 )
3386 },
3387 )
3388 await _assert_user_can_view_request_id(
3389 prisma_client=prisma_client,
3390 user_api_key_dict=user_api_key_dict,
3391 request_id=request_id,
3392 )
3394 custom_loggers: Final = litellm.logging_callback_manager.get_active_additional_logging_utils_from_custom_logger()
3395 start_date_obj: datetime | None = None
3396 end_date_obj: datetime | None = None
3397 if start_date is not None:
3398 start_date_obj = datetime.strptime(start_date, "%Y-%m-%d %H:%M:%S").replace(tzinfo=timezone.utc)
3399 if end_date is not None:
3400 end_date_obj = datetime.strptime(end_date, "%Y-%m-%d %H:%M:%S").replace(tzinfo=timezone.utc)
3402 spend_log_row: Final = (
3403 None
3404 if prisma_client is None
3405 else await _resolve_spend_log_payload_row(
3406 prisma_client=prisma_client,
3407 user_api_key_dict=user_api_key_dict,
3408 request_id=request_id,
3409 caller_is_admin=caller_is_admin,
3410 )
3411 )
3412 stored_request_id: Final = _stored_request_id(spend_log_row, request_id)
3414 for custom_logger in custom_loggers:
3415 payload = await custom_logger.get_request_response_payload(
3416 request_id=stored_request_id,
3417 start_time_utc=start_date_obj,
3418 end_time_utc=end_date_obj,
3419 )
3420 if payload is not None:
3421 if not caller_is_admin and prisma_client is not None:
3422 await _assert_user_owns_cold_storage_payload(
3423 prisma_client=prisma_client,
3424 user_api_key_dict=user_api_key_dict,
3425 payload=cast(Mapping[str, object], payload), # cast-ok: custom-logger payload is untyped
3426 request_id=request_id,
3427 )
3428 return payload
3430 if spend_log_row is None:
3431 return None
3433 # Fallback: the list endpoint omits the heavy columns for performance, so
3434 # serve them here. When prompts were offloaded to cold storage the DB holds
3435 # only placeholders, so _resolve_request_response_payload fetches the real
3436 # payload from the configured cold storage backend by object key.
3437 from litellm.proxy.spend_tracking.cold_storage_handler import ColdStorageHandler
3439 resolved: Final = await _resolve_request_response_payload(spend_log_row, cold_storage_handler=ColdStorageHandler())
3440 return resolved._asdict()
3443@router.get(
3444 "/spend/logs",
3445 tags=["Budget & Spend Tracking"],
3446 dependencies=[Depends(user_api_key_auth)],
3447 responses={
3448 200: {"model": list[LiteLLM_SpendLogs]},
3449 },
3450)
3451async def view_spend_logs(
3452 fastapi_response: Response,
3453 api_key: str | None = fastapi.Query(
3454 default=None,
3455 description="Get spend logs based on api key",
3456 ),
3457 user_id: str | None = fastapi.Query(
3458 default=None,
3459 description="Get spend logs based on user_id",
3460 ),
3461 request_id: str | None = fastapi.Query(
3462 default=None,
3463 description="request_id to get spend logs for specific request_id. If none passed then pass spend logs for all requests",
3464 ),
3465 start_date: str | None = fastapi.Query(
3466 default=None,
3467 description="Time from which to start viewing key spend",
3468 ),
3469 end_date: str | None = fastapi.Query(
3470 default=None,
3471 description="Time till which to view key spend",
3472 ),
3473 summarize: bool = fastapi.Query(
3474 default=True,
3475 description="When start_date and end_date are provided, summarize=true returns aggregated data by date (legacy behavior), summarize=false returns filtered individual logs",
3476 ),
3477 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
3478):
3479 """
3480 [DEPRECATED] This endpoint is not paginated and can cause performance issues.
3481 Please use `/spend/logs/v2` instead for paginated access to spend logs.
3483 Row results are capped at 10,000 most recent entries per response.
3485 View all spend logs, if request_id is provided, only logs for that request_id will be returned
3487 When start_date and end_date are provided:
3488 - summarize=true (default): Returns aggregated spend data grouped by date (maintains backward compatibility)
3489 - summarize=false: Returns filtered individual log entries within the date range
3491 Example Request for all logs
3492 ```
3493 curl -X GET "http://0.0.0.0:8000/spend/logs" \
3494-H "Authorization: Bearer sk-1234"
3495 ```
3497 Example Request for specific request_id
3498 ```
3499 curl -X GET "http://0.0.0.0:8000/spend/logs?request_id=chatcmpl-6dcb2540-d3d7-4e49-bb27-291f863f112e" \
3500-H "Authorization: Bearer sk-1234"
3501 ```
3503 Example Request for specific api_key
3504 ```
3505 curl -X GET "http://0.0.0.0:8000/spend/logs?api_key=d5345c0ecc68ae6295c69f91926b2bd379e25481a40c34b5884d157a9f65d8fa" \
3506-H "Authorization: Bearer sk-1234"
3507 ```
3509 Example Request for specific user_id
3510 ```
3511 curl -X GET "http://0.0.0.0:8000/spend/logs?user_id=ishaan@berri.ai" \
3512-H "Authorization: Bearer sk-1234"
3513 ```
3515 Example Request for date range with individual logs (unsummarized)
3516 ```
3517 curl -X GET "http://0.0.0.0:8000/spend/logs?start_date=2024-01-01&end_date=2024-01-02&summarize=false" \
3518-H "Authorization: Bearer sk-1234"
3519 ```
3520 """
3521 from litellm.proxy.proxy_server import prisma_client
3523 if ( 3523 ↛ 3527line 3523 didn't jump to line 3527 because the condition on line 3523 was never true
3524 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
3525 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
3526 ):
3527 user_id = user_api_key_dict.user_id
3529 try:
3530 verbose_proxy_logger.debug("inside view_spend_logs")
3531 if prisma_client is None: 3531 ↛ 3532line 3531 didn't jump to line 3532 because the condition on line 3531 was never true
3532 raise Exception(
3533 "Database not connected. Connect a database to your proxy - https://docs.litellm.ai/docs/simple_proxy#managing-auth---virtual-keys"
3534 )
3535 if (
3536 start_date is not None
3537 and isinstance(start_date, str)
3538 and end_date is not None
3539 and isinstance(end_date, str)
3540 ):
3541 # Convert the date strings to datetime objects
3542 start_date_obj: Final = datetime.strptime(start_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
3543 end_date_obj: Final = datetime.strptime(end_date, "%Y-%m-%d").replace(tzinfo=timezone.utc)
3545 # Convert to ISO format strings for Prisma
3546 start_date_iso: Final = start_date_obj.isoformat()
3547 end_date_iso: Final = end_date_obj.isoformat()
3549 filter_query: Final[
3550 dict[str, object]
3551 ] = { # mutable-ok: legacy filters are extended for optional parameters
3552 "startTime": {
3553 "gte": start_date_iso, # Greater than or equal to Start Date
3554 "lte": end_date_iso, # Less than or equal to End Date
3555 }
3556 }
3558 summary_api_key: Final[str | None] = (
3559 prisma_client.hash_token(token=api_key)
3560 if api_key is not None and api_key.startswith("sk-")
3561 else api_key
3562 )
3563 if api_key is not None and isinstance(api_key, str):
3564 filter_query["api_key"] = summary_api_key
3565 if request_id is not None and isinstance(request_id, str):
3566 filter_query["OR"] = _request_id_or_call_id_clause(request_id)
3567 if user_id is not None and isinstance(user_id, str):
3568 filter_query["user"] = user_id
3570 # Check if user wants unsummarized data
3571 if not summarize:
3572 # Return filtered individual log entries (similar to UI endpoint)
3573 data = await _find_spend_logs(
3574 prisma_client,
3575 where=filter_query,
3576 order={"startTime": "desc"},
3577 take=SPEND_LOGS_PAGINATION_COUNT_CAP,
3578 http_response=fastapi_response,
3579 )
3580 return data
3582 # Legacy behavior: return summarized data (when summarize=true)
3583 summary_sql_and_params: Final = _spend_logs_daily_summary_sql(
3584 start_date_iso=start_date_iso,
3585 end_date_iso=end_date_iso,
3586 api_key=summary_api_key,
3587 request_id=request_id,
3588 user_id=user_id,
3589 )
3590 sql_query, params = summary_sql_and_params
3591 rows: Final[Sequence[_SpendDailySummaryRow]] = await _query_raw(prisma_client, sql_query, *params)
3592 summary_items: Final = tuple(
3593 _daily_summary_item(date.fromisoformat(day), tuple(day_rows))
3594 for day, day_rows in groupby(rows, key=lambda row: row["day"])
3595 )
3596 final_date: Final = date.fromisoformat(rows[-1]["day"]) if len(rows) > 0 else None
3597 end_date_date: Final = end_date_obj.date()
3598 padding: Final[tuple[Mapping[str, object], ...]] = (
3599 ()
3600 if final_date is None
3601 else tuple(
3602 {
3603 "startTime": final_date + timedelta(days=offset),
3604 "spend": 0,
3605 "users": {},
3606 "models": {},
3607 }
3608 for offset in range(1, (end_date_date - final_date).days + 1)
3609 )
3610 )
3611 return [*summary_items, *padding]
3613 else:
3614 scoped_filter: Final[dict[str, object]] = {}
3615 if api_key is not None and isinstance(api_key, str):
3616 if api_key.startswith("sk-"): 3616 ↛ 3617line 3616 didn't jump to line 3617 because the condition on line 3616 was never true
3617 hashed_token = prisma_client.hash_token(token=api_key)
3618 else:
3619 hashed_token = api_key
3620 scoped_filter["api_key"] = hashed_token
3621 if request_id is not None and isinstance(request_id, str):
3622 scoped_filter["OR"] = _request_id_or_call_id_clause(request_id)
3623 if user_id is not None and isinstance(user_id, str):
3624 scoped_filter["user"] = user_id
3626 data = await _find_spend_logs(
3627 prisma_client,
3628 where=scoped_filter,
3629 order={"startTime": "desc"},
3630 take=SPEND_LOGS_PAGINATION_COUNT_CAP,
3631 http_response=fastapi_response,
3632 )
3633 return data
3635 return None
3637 except Exception as e:
3638 if isinstance(e, HTTPException): 3638 ↛ 3639line 3638 didn't jump to line 3639 because the condition on line 3638 was never true
3639 raise ProxyException(
3640 message=getattr(e, "detail", f"/spend/logs Error({e})"),
3641 type="internal_error",
3642 param=getattr(e, "param", "None"),
3643 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
3644 )
3645 elif isinstance(e, ProxyException): 3645 ↛ 3646line 3645 didn't jump to line 3646 because the condition on line 3645 was never true
3646 raise e
3647 raise ProxyException(
3648 message="/spend/logs Error" + str(e),
3649 type="internal_error",
3650 param=getattr(e, "param", "None"),
3651 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
3652 )
3655@router.post(
3656 "/global/spend/reset",
3657 tags=["Budget & Spend Tracking"],
3658 dependencies=[Depends(user_api_key_auth)],
3659)
3660async def global_spend_reset():
3661 """
3662 ADMIN ONLY / MASTER KEY Only Endpoint
3664 Globally reset spend for All API Keys and Teams, maintain LiteLLM_SpendLogs
3666 1. LiteLLM_SpendLogs will maintain the logs on spend, no data gets deleted from there
3667 2. LiteLLM_VerificationTokens spend will be set = 0
3668 3. LiteLLM_TeamTable spend will be set = 0
3670 """
3671 from litellm.proxy.proxy_server import prisma_client
3673 if prisma_client is None: 3673 ↛ 3674line 3673 didn't jump to line 3674 because the condition on line 3673 was never true
3674 raise ProxyException(
3675 message="Prisma Client is not initialized",
3676 type="internal_error",
3677 param="None",
3678 code=status.HTTP_401_UNAUTHORIZED,
3679 )
3681 await _verification_token_table(prisma_client).update_many(data={"spend": 0.0}, where={})
3682 await _team_table(prisma_client).update_many(data={"spend": 0.0}, where={})
3684 return {
3685 "message": "Spend for all API Keys and Teams reset successfully",
3686 "status": "success",
3687 }
3690@router.post(
3691 "/global/spend/refresh",
3692 tags=["Budget & Spend Tracking"],
3693 dependencies=[Depends(user_api_key_auth)],
3694 include_in_schema=False,
3695)
3696async def global_spend_refresh():
3697 """
3698 ADMIN ONLY / MASTER KEY Only Endpoint
3700 Globally refresh spend MonthlyGlobalSpend view
3701 """
3702 from litellm.proxy.proxy_server import prisma_client
3704 if prisma_client is None:
3705 raise ProxyException(
3706 message="Prisma Client is not initialized",
3707 type="internal_error",
3708 param="None",
3709 code=status.HTTP_401_UNAUTHORIZED,
3710 )
3712 ## RESET GLOBAL SPEND VIEW ###
3713 async def is_materialized_global_spend_view() -> bool:
3714 """
3715 Return True if materialized view exists
3717 Else False
3718 """
3719 sql_query: Final = """
3720 SELECT relname, relkind
3721 FROM pg_class
3722 WHERE relname = 'MonthlyGlobalSpend';
3723 """
3724 try:
3725 resp: Final[Sequence[_PgClassRow]] = await _query_raw(prisma_client, sql_query)
3727 return resp[0]["relkind"] == "m"
3728 except Exception:
3729 return False
3731 view_exists: Final = await is_materialized_global_spend_view()
3733 if view_exists:
3734 # refresh materialized view
3735 sql_query: Final = """
3736 REFRESH MATERIALIZED VIEW "MonthlyGlobalSpend";
3737 """
3738 try:
3739 from litellm.proxy._types import CommonProxyErrors
3740 from litellm.proxy.proxy_server import proxy_logging_obj
3741 from litellm.proxy.utils import PrismaClient
3743 db_url: Final = os.getenv("DATABASE_URL")
3744 if db_url is None:
3745 raise Exception(CommonProxyErrors.db_not_connected_error.value)
3746 new_client: Final = PrismaClient(
3747 database_url=db_url,
3748 proxy_logging_obj=proxy_logging_obj,
3749 http_client={
3750 "timeout": 6000,
3751 },
3752 )
3753 await new_client.db.connect()
3754 await _query_raw(new_client, sql_query)
3755 verbose_proxy_logger.info("MonthlyGlobalSpend view refreshed")
3756 return {
3757 "message": "MonthlyGlobalSpend view refreshed",
3758 "status": "success",
3759 }
3761 except Exception as e:
3762 verbose_proxy_logger.exception("Failed to refresh materialized view - %s", e)
3763 return {
3764 "message": "Failed to refresh materialized view",
3765 "status": "failure",
3766 }
3769async def global_spend_for_internal_user(
3770 api_key: str | None = None,
3771 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
3772):
3773 from litellm.proxy.proxy_server import prisma_client
3775 if prisma_client is None:
3776 raise ProxyException(
3777 message="Prisma Client is not initialized",
3778 type="internal_error",
3779 param="None",
3780 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
3781 )
3782 try:
3783 user_id: Final = user_api_key_dict.user_id
3784 if user_id is None:
3785 raise ValueError("/global/spend/logs Error: User ID is None")
3786 if api_key is not None:
3787 sql_query = """
3788 SELECT * FROM "MonthlyGlobalSpendPerUserPerKey"
3789 WHERE "api_key" = $1 AND "user" = $2
3790 ORDER BY "date";
3791 """
3793 response: Sequence[Mapping[str, object]] = await _query_raw(prisma_client, sql_query, api_key, user_id)
3795 return response
3797 sql_query = """SELECT * FROM "MonthlyGlobalSpendPerUserPerKey" WHERE "user" = $1 ORDER BY "date";"""
3799 response = await _query_raw(prisma_client, sql_query, user_id)
3801 return response
3802 except Exception as e:
3803 verbose_proxy_logger.error("/global/spend/logs Error: %s", e)
3804 raise e
3807@router.get(
3808 "/global/spend/logs",
3809 tags=["Budget & Spend Tracking"],
3810 dependencies=[Depends(user_api_key_auth)],
3811 include_in_schema=False,
3812)
3813async def global_spend_logs(
3814 api_key: str | None = fastapi.Query(
3815 default=None,
3816 description="API Key to get global spend (spend per day for last 30d). Admin-only endpoint",
3817 ),
3818 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
3819):
3820 """
3821 [BETA] This is a beta endpoint. It will change.
3823 Use this to get global spend (spend per day for last 30d). Admin-only endpoint
3825 More efficient implementation of /spend/logs, by creating a view over the spend logs table.
3826 """
3827 import traceback
3829 from litellm.integrations.prometheus_helpers.prometheus_api import (
3830 get_daily_spend_from_prometheus,
3831 is_prometheus_connected,
3832 )
3833 from litellm.proxy.proxy_server import prisma_client
3835 try:
3836 if prisma_client is None:
3837 raise ProxyException(
3838 message="Prisma Client is not initialized",
3839 type="internal_error",
3840 param="None",
3841 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
3842 )
3844 response: Sequence[Mapping[str, object]]
3845 if (
3846 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
3847 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
3848 ):
3849 response = await global_spend_for_internal_user(api_key=api_key, user_api_key_dict=user_api_key_dict)
3851 return response
3853 prometheus_api_enabled: Final = is_prometheus_connected()
3855 if prometheus_api_enabled:
3856 response = await get_daily_spend_from_prometheus(api_key=api_key)
3857 return response
3858 else:
3859 if api_key is None:
3860 sql_query = """SELECT * FROM "MonthlyGlobalSpend" ORDER BY "date";"""
3862 response = await _query_raw(prisma_client, sql_query)
3864 return response
3865 else:
3866 sql_query = """
3867 SELECT * FROM "MonthlyGlobalSpendPerKey"
3868 WHERE "api_key" = $1
3869 ORDER BY "date";
3870 """
3872 response = await _query_raw(prisma_client, sql_query, api_key)
3874 return response
3876 except Exception as e:
3877 error_trace: Final = traceback.format_exc()
3878 error_str: Final = str(e) + "\n" + error_trace
3879 verbose_proxy_logger.error("/global/spend/logs Error: %s", error_str)
3880 if isinstance(e, HTTPException):
3881 raise ProxyException(
3882 message=getattr(e, "detail", f"/global/spend/logs Error({error_str})"),
3883 type="internal_error",
3884 param=getattr(e, "param", "None"),
3885 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
3886 )
3887 elif isinstance(e, ProxyException):
3888 raise e
3889 raise ProxyException(
3890 message="/global/spend/logs Error" + error_str,
3891 type="internal_error",
3892 param=getattr(e, "param", "None"),
3893 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
3894 )
3897@router.get(
3898 "/global/spend",
3899 tags=["Budget & Spend Tracking"],
3900 dependencies=[Depends(user_api_key_auth)],
3901 include_in_schema=False,
3902)
3903async def global_spend():
3904 """
3905 [BETA] This is a beta endpoint. It will change.
3907 View total spend across all proxy keys
3908 """
3909 import traceback
3911 from litellm.proxy.proxy_server import prisma_client
3913 try:
3914 total_spend = 0.0
3916 if prisma_client is None:
3917 raise HTTPException(status_code=500, detail={"error": "No db connected"})
3918 sql_query: Final = """SELECT SUM(spend) as total_spend FROM "MonthlyGlobalSpend";"""
3919 response: Final[Sequence[_TotalSpendRow] | None] = await _query_raw_or_none(prisma_client, sql_query)
3920 if response is not None:
3921 if isinstance(response, list) and len(response) > 0:
3922 total_spend = response[0].get("total_spend", 0.0)
3924 return {"spend": total_spend, "max_budget": litellm.max_budget}
3925 except Exception as e:
3926 error_trace: Final = traceback.format_exc()
3927 error_str: Final = str(e) + "\n" + error_trace
3928 if isinstance(e, HTTPException):
3929 raise ProxyException(
3930 message=getattr(e, "detail", f"/global/spend Error({error_str})"),
3931 type="internal_error",
3932 param=getattr(e, "param", "None"),
3933 code=getattr(e, "status_code", status.HTTP_500_INTERNAL_SERVER_ERROR),
3934 )
3935 elif isinstance(e, ProxyException):
3936 raise e
3937 raise ProxyException(
3938 message="/global/spend Error" + error_str,
3939 type="internal_error",
3940 param=getattr(e, "param", "None"),
3941 code=status.HTTP_500_INTERNAL_SERVER_ERROR,
3942 )
3945async def global_spend_key_internal_user(user_api_key_dict: UserAPIKeyAuth, limit: int = 10):
3946 from litellm.proxy.proxy_server import prisma_client
3948 if prisma_client is None:
3949 raise HTTPException(status_code=500, detail={"error": "No db connected"})
3951 user_id: Final = user_api_key_dict.user_id
3952 if user_id is None:
3953 raise HTTPException(status_code=500, detail={"error": "No user_id found"})
3955 sql_query: Final = """
3956 WITH top_api_keys AS (
3957 SELECT
3958 api_key,
3959 SUM(spend) as total_spend
3960 FROM
3961 "LiteLLM_SpendLogs"
3962 WHERE
3963 "user" = $1
3964 GROUP BY
3965 api_key
3966 ORDER BY
3967 total_spend DESC
3968 LIMIT $2 -- Adjust this number to get more or fewer top keys
3969 )
3970 SELECT
3971 t.api_key,
3972 t.total_spend,
3973 v.key_alias,
3974 v.key_name
3975 FROM
3976 top_api_keys t
3977 LEFT JOIN
3978 "LiteLLM_VerificationToken" v ON t.api_key = v.token
3979 ORDER BY
3980 t.total_spend DESC;
3982 """
3984 response: Final[Sequence[Mapping[str, object]]] = await _query_raw(prisma_client, sql_query, user_id, limit)
3986 return response
3989@router.get(
3990 "/global/spend/keys",
3991 tags=["Budget & Spend Tracking"],
3992 dependencies=[Depends(user_api_key_auth)],
3993 include_in_schema=False,
3994)
3995async def global_spend_keys(
3996 limit: int = fastapi.Query(
3997 default=None,
3998 description="Number of keys to get. Will return Top 'n' keys.",
3999 ),
4000 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
4001):
4002 """
4003 [BETA] This is a beta endpoint. It will change.
4005 Use this to get the top 'n' keys with the highest spend, ordered by spend.
4006 """
4007 from litellm.proxy.proxy_server import prisma_client
4009 response: Sequence[Mapping[str, object]]
4010 if (
4011 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
4012 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
4013 ):
4014 response = await global_spend_key_internal_user(user_api_key_dict=user_api_key_dict)
4016 return response
4017 if prisma_client is None:
4018 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4019 sql_query = """SELECT * FROM "Last30dKeysBySpend";"""
4021 if limit is None:
4022 response = await _query_raw(prisma_client, sql_query)
4023 return response
4024 try:
4025 limit = int(limit)
4026 if limit < 1:
4027 raise ValueError("Limit must be greater than 0")
4028 sql_query = """SELECT * FROM "Last30dKeysBySpend" LIMIT $1 ;"""
4029 response = await _query_raw(prisma_client, sql_query, limit)
4030 except ValueError as e:
4031 raise HTTPException(status_code=422, detail={"error": f"Invalid limit: {limit}, error: {e}"}) from e
4033 return response
4036@router.get(
4037 "/global/spend/teams",
4038 tags=["Budget & Spend Tracking"],
4039 dependencies=[Depends(user_api_key_auth)],
4040 include_in_schema=False,
4041)
4042async def global_spend_per_team():
4043 """
4044 [BETA] This is a beta endpoint. It will change.
4046 Use this to get daily spend, grouped by `team_id` and `date`
4047 """
4048 from litellm.proxy.proxy_server import prisma_client
4050 if prisma_client is None:
4051 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4052 sql_query: Final = """
4053 SELECT
4054 t.team_alias as team_alias,
4055 DATE(s."startTime") AS spend_date,
4056 SUM(s.spend) AS total_spend
4057 FROM
4058 "LiteLLM_SpendLogs" s
4059 LEFT JOIN
4060 "LiteLLM_TeamTable" t ON s.team_id = t.team_id
4061 WHERE
4062 s."startTime" >= CURRENT_DATE - INTERVAL '30 days'
4063 GROUP BY
4064 t.team_alias,
4065 DATE(s."startTime")
4066 ORDER BY
4067 spend_date;
4068 """
4069 response: Final[Sequence[_TeamDailySpendRow]] = await _query_raw(prisma_client, sql_query)
4071 # transform the response for the Admin UI
4072 spend_by_date: Final = {}
4073 team_aliases: Final = set()
4074 total_spend_per_team = {}
4075 for row in response:
4076 row_date = row["spend_date"]
4077 if row_date is None:
4078 continue
4079 team_alias = row["team_alias"]
4080 if team_alias is None:
4081 team_alias = "Unassigned"
4082 team_aliases.add(team_alias)
4083 if row_date in spend_by_date:
4084 # get the team_id for this entry
4085 # get the spend for this entry
4086 spend = row["total_spend"]
4087 spend = round(spend, 2)
4088 current_date_entries = spend_by_date[row_date]
4089 current_date_entries[team_alias] = spend
4090 else:
4091 spend = row["total_spend"]
4092 spend = round(spend, 2)
4093 spend_by_date[row_date] = {team_alias: spend}
4095 if team_alias in total_spend_per_team:
4096 total_spend_per_team[team_alias] += spend
4097 else:
4098 total_spend_per_team[team_alias] = spend
4100 total_spend_per_team_ui: Final = []
4101 # order the elements in total_spend_per_team by spend
4102 total_spend_per_team = dict(sorted(total_spend_per_team.items(), key=lambda item: item[1], reverse=True))
4103 for team_id in total_spend_per_team:
4104 # only add first 10 elements to total_spend_per_team_ui
4105 if len(total_spend_per_team_ui) >= 10:
4106 break
4107 if team_id is None:
4108 team_id = "Unassigned"
4109 total_spend_per_team_ui.append({"team_id": team_id, "total_spend": total_spend_per_team[team_id]})
4111 # sort spend_by_date by it's key (which is a date)
4113 response_data: Final = []
4114 for key in spend_by_date:
4115 value = spend_by_date[key]
4116 response_data.append({"date": key, **value})
4118 return {
4119 "daily_spend": response_data,
4120 "teams": list(team_aliases),
4121 "total_spend_per_team": total_spend_per_team_ui,
4122 }
4125@router.get(
4126 "/global/all_end_users",
4127 tags=["Budget & Spend Tracking"],
4128 dependencies=[Depends(user_api_key_auth)],
4129 include_in_schema=False,
4130)
4131async def global_view_all_end_users():
4132 """
4133 [BETA] This is a beta endpoint. It will change.
4135 Use this to just get all the unique `end_users`
4136 """
4137 from litellm.proxy.proxy_server import prisma_client
4139 if prisma_client is None:
4140 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4142 sql_query: Final = """
4143 SELECT DISTINCT end_user FROM "LiteLLM_SpendLogs"
4144 """
4146 db_response: Final[Sequence[_EndUserRow] | None] = await _query_raw_or_none(prisma_client, sql_query)
4147 if db_response is None:
4148 return []
4150 _end_users: Final = []
4151 for row in db_response:
4152 _end_users.append(row["end_user"])
4154 return {"end_users": _end_users}
4157@router.post(
4158 "/global/spend/end_users",
4159 tags=["Budget & Spend Tracking"],
4160 dependencies=[Depends(user_api_key_auth)],
4161 include_in_schema=False,
4162)
4163async def global_spend_end_users(data: GlobalEndUsersSpend | None = None):
4164 """
4165 [BETA] This is a beta endpoint. It will change.
4167 Use this to get the top 'n' keys with the highest spend, ordered by spend.
4168 """
4169 from litellm.proxy.proxy_server import prisma_client
4171 if prisma_client is None:
4172 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4174 """
4175 Gets the top 100 end-users for a given api key
4176 """
4177 startTime = None
4178 endTime = None
4179 selected_api_key = None
4180 if data is not None:
4181 startTime = data.startTime
4182 endTime = data.endTime
4183 selected_api_key = data.api_key
4185 startTime = startTime or datetime.now() - timedelta(days=30)
4186 endTime = endTime or datetime.now()
4188 sql_query: Final = """
4189SELECT end_user, COUNT(*) AS total_count, SUM(spend) AS total_spend
4190FROM "LiteLLM_SpendLogs"
4191WHERE "startTime" >= ($1::timestamptz AT TIME ZONE 'UTC')
4192 AND "startTime" < ($2::timestamptz AT TIME ZONE 'UTC')
4193 AND (
4194 CASE
4195 WHEN $3::TEXT IS NULL THEN TRUE
4196 ELSE api_key = $3
4197 END
4198 )
4199GROUP BY end_user
4200ORDER BY total_spend DESC
4201LIMIT 100
4202 """
4203 response: Final[Sequence[Mapping[str, object]]] = await _query_raw(
4204 prisma_client, sql_query, startTime, endTime, selected_api_key
4205 )
4207 return response
4210async def global_spend_models_internal_user(user_api_key_dict: UserAPIKeyAuth, limit: int = 10):
4211 from litellm.proxy.proxy_server import prisma_client
4213 if prisma_client is None:
4214 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4216 user_id: Final = user_api_key_dict.user_id
4217 if user_id is None:
4218 raise HTTPException(status_code=500, detail={"error": "No user_id found"})
4220 sql_query: Final = """
4221 SELECT
4222 model,
4223 SUM(spend) as total_spend,
4224 SUM(total_tokens) as total_tokens
4225 FROM
4226 "LiteLLM_SpendLogs"
4227 WHERE
4228 "user" = $1
4229 GROUP BY
4230 model
4231 ORDER BY
4232 total_spend DESC
4233 LIMIT $2;
4234 """
4236 response: Final[Sequence[Mapping[str, object]]] = await _query_raw(prisma_client, sql_query, user_id, limit)
4238 return response
4241@router.get(
4242 "/global/spend/models",
4243 tags=["Budget & Spend Tracking"],
4244 dependencies=[Depends(user_api_key_auth)],
4245 include_in_schema=False,
4246)
4247async def global_spend_models(
4248 limit: int = fastapi.Query(
4249 default=10,
4250 description="Number of models to get. Will return Top 'n' models.",
4251 ),
4252 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
4253):
4254 """
4255 [BETA] This is a beta endpoint. It will change.
4257 Use this to get the top 'n' models with the highest spend, ordered by spend.
4258 """
4259 from litellm.proxy.proxy_server import prisma_client
4261 if (
4262 user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER
4263 or user_api_key_dict.user_role == LitellmUserRoles.INTERNAL_USER_VIEW_ONLY
4264 ):
4265 response = await global_spend_models_internal_user(user_api_key_dict=user_api_key_dict, limit=limit)
4266 return response
4268 if prisma_client is None:
4269 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4271 sql_query: Final = """SELECT * FROM "Last30dModelsBySpend" LIMIT $1 ;"""
4273 response: Sequence[Mapping[str, object]] = await _query_raw(prisma_client, sql_query, int(limit))
4275 return response
4278@router.get(
4279 "/provider/budgets",
4280 dependencies=[Depends(user_api_key_auth)],
4281 response_model=ProviderBudgetResponse,
4282)
4283async def provider_budgets() -> ProviderBudgetResponse:
4284 """
4285 Provider Budget Routing - Get Budget, Spend Details https://docs.litellm.ai/docs/proxy/provider_budget_routing
4287 Use this endpoint to check current budget, spend and budget reset time for a provider
4289 Example Request
4291 ```bash
4292 curl -X GET http://localhost:4000/provider/budgets \
4293 -H "Content-Type: application/json" \
4294 -H "Authorization: Bearer sk-1234"
4295 ```
4297 Example Response
4299 ```json
4300 {
4301 "providers": {
4302 "openai": {
4303 "budget_limit": 1e-12,
4304 "time_period": "1d",
4305 "spend": 0.0,
4306 "budget_reset_at": null
4307 },
4308 "azure": {
4309 "budget_limit": 100.0,
4310 "time_period": "1d",
4311 "spend": 0.0,
4312 "budget_reset_at": null
4313 },
4314 "anthropic": {
4315 "budget_limit": 100.0,
4316 "time_period": "10d",
4317 "spend": 0.0,
4318 "budget_reset_at": null
4319 },
4320 "vertex_ai": {
4321 "budget_limit": 100.0,
4322 "time_period": "12d",
4323 "spend": 0.0,
4324 "budget_reset_at": null
4325 }
4326 }
4327 }
4328 ```
4330 """
4331 from litellm.proxy.proxy_server import llm_router
4333 try:
4334 if llm_router is None: 4334 ↛ 4335line 4334 didn't jump to line 4335 because the condition on line 4334 was never true
4335 raise HTTPException(status_code=500, detail={"error": "No llm_router found"})
4337 provider_budget_config: Final = llm_router.provider_budget_config
4338 if provider_budget_config is None: 4338 ↛ 4343line 4338 didn't jump to line 4343 because the condition on line 4338 was always true
4339 raise ValueError(
4340 "No provider budget config found. Please set a provider budget config in the router settings. https://docs.litellm.ai/docs/proxy/provider_budget_routing"
4341 )
4343 router_budget_logger: Final = llm_router._get_router_deployment_budget_limiter()
4344 if router_budget_logger is None:
4345 raise ValueError("No router budget logger found")
4347 provider_budget_response_dict: Final[dict[str, ProviderBudgetResponseObject]] = {}
4348 for _provider, _budget_info in provider_budget_config.items():
4349 _provider_spend = await router_budget_logger._get_current_provider_spend(_provider) or 0.0
4350 _provider_budget_ttl = await router_budget_logger._get_current_provider_budget_reset_at(_provider)
4351 provider_budget_response_object = ProviderBudgetResponseObject(
4352 budget_limit=_budget_info.max_budget,
4353 time_period=_budget_info.budget_duration,
4354 spend=_provider_spend,
4355 budget_reset_at=_provider_budget_ttl,
4356 )
4357 provider_budget_response_dict[_provider] = provider_budget_response_object
4358 return ProviderBudgetResponse(providers=provider_budget_response_dict)
4359 except Exception as e:
4360 verbose_proxy_logger.exception("/provider/budgets: Exception occured - %s", e)
4361 raise handle_exception_on_proxy(e)
4364async def get_spend_by_tags(prisma_client: PrismaClient, start_date=None, end_date=None):
4365 response: Final[Sequence[Mapping[str, object]]] = await _query_raw(
4366 prisma_client,
4367 """
4368 SELECT
4369 jsonb_array_elements_text(request_tags) AS individual_request_tag,
4370 COUNT(*) AS log_count,
4371 SUM(spend) AS total_spend
4372 FROM "LiteLLM_SpendLogs"
4373 GROUP BY individual_request_tag;
4374 """,
4375 )
4377 return response
4380async def ui_get_spend_by_tags(
4381 start_date: str,
4382 end_date: str,
4383 prisma_client: PrismaClient | None = None,
4384 tags_str: str | None = None,
4385):
4386 """
4387 Should cover 2 cases:
4388 1. When user is getting spend for all_tags. "all_tags" in tags_list
4389 2. When user is getting spend for specific tags.
4390 """
4392 # tags_str is a list of strings csv of tags
4393 # tags_str = tag1,tag2,tag3
4394 # convert to list if it's not None
4395 tags_list: list[str] | None = None
4396 if tags_str is not None and len(tags_str) > 0:
4397 tags_list = tags_str.split(",")
4399 if prisma_client is None: 4399 ↛ 4400line 4399 didn't jump to line 4400 because the condition on line 4399 was never true
4400 raise HTTPException(status_code=500, detail={"error": "No db connected"})
4402 response: Sequence[_DailyTagSpendRow] | None = None
4403 if tags_list is None or (isinstance(tags_list, list) and "all-tags" in tags_list):
4404 # Get spend for all tags
4405 sql_query = """
4406 SELECT
4407 individual_request_tag,
4408 spend_date,
4409 log_count,
4410 total_spend
4411 FROM "DailyTagSpend"
4412 WHERE spend_date >= $1::date AND spend_date <= $2::date
4413 ORDER BY total_spend DESC;
4414 """
4415 response = await _query_raw(
4416 prisma_client,
4417 sql_query,
4418 start_date,
4419 end_date,
4420 )
4421 else:
4422 # filter by tags list
4423 sql_query = """
4424 SELECT
4425 individual_request_tag,
4426 SUM(log_count) AS log_count,
4427 SUM(total_spend) AS total_spend
4428 FROM "DailyTagSpend"
4429 WHERE spend_date >= $1::date AND spend_date <= $2::date
4430 AND individual_request_tag = ANY($3::text[])
4431 GROUP BY individual_request_tag
4432 ORDER BY total_spend DESC;
4433 """
4434 response = await _query_raw(
4435 prisma_client,
4436 sql_query,
4437 start_date,
4438 end_date,
4439 tags_list,
4440 )
4442 # print("tags - spend")
4443 # print(response)
4444 # Bar Chart 1 - Spend per tag - Top 10 tags by spend
4445 total_spend_per_tag: Final[collections.defaultdict] = collections.defaultdict(float)
4446 total_requests_per_tag: Final[collections.defaultdict] = collections.defaultdict(int)
4447 for row in response:
4448 tag_name = row["individual_request_tag"]
4449 tag_spend = row["total_spend"]
4451 total_spend_per_tag[tag_name] += tag_spend
4452 total_requests_per_tag[tag_name] += row["log_count"]
4454 sorted_tags: Final = sorted(total_spend_per_tag.items(), key=lambda x: x[1], reverse=True)
4455 # convert to ui format
4456 ui_tags: Final = []
4457 for tag in sorted_tags:
4458 current_spend = tag[1]
4459 if current_spend is not None and isinstance(current_spend, float): 4459 ↛ 4461line 4459 didn't jump to line 4461 because the condition on line 4459 was always true
4460 current_spend = round(current_spend, 4)
4461 ui_tags.append(
4462 {
4463 "name": tag[0],
4464 "spend": current_spend,
4465 "log_count": total_requests_per_tag[tag[0]],
4466 }
4467 )
4469 return {"spend_per_tag": ui_tags}
4472@router.get(
4473 "/spend/logs/session/ui",
4474 tags=["Budget & Spend Tracking"],
4475 dependencies=[Depends(user_api_key_auth)],
4476 include_in_schema=False,
4477 responses={
4478 200: {"model": list[LiteLLM_SpendLogs]},
4479 },
4480)
4481async def ui_view_session_spend_logs(
4482 session_id: str = fastapi.Query(
4483 description="Get all spend logs for a particular session",
4484 ),
4485 page: int = fastapi.Query(
4486 default=1,
4487 ge=1,
4488 description="Page number for pagination",
4489 ),
4490 page_size: int = fastapi.Query(
4491 default=50,
4492 ge=1,
4493 le=100,
4494 description="Number of items per page",
4495 ),
4496 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
4497):
4498 """
4499 Get paginated spend logs for a particular session.
4501 Returns:
4502 {
4503 "data": List[LiteLLM_SpendLogs],
4504 "total": int,
4505 "page": int,
4506 "page_size": int,
4507 "total_pages": int,
4508 }
4509 """
4510 from litellm.proxy.proxy_server import prisma_client
4512 try:
4513 if prisma_client is None:
4514 raise HTTPException(
4515 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
4516 detail="Database not connected",
4517 )
4519 if _is_admin_view_safe(user_api_key_dict=user_api_key_dict):
4520 scope_sql = ""
4521 scope_params = ()
4522 where_conditions = {"session_id": session_id}
4523 else:
4524 try:
4525 permitted_team_ids = (
4526 await _get_permitted_team_ids_for_spend_logs(
4527 prisma_client=prisma_client,
4528 user_api_key_dict=user_api_key_dict,
4529 )
4530 if _can_user_view_spend_log(user_api_key_dict=user_api_key_dict)
4531 else []
4532 )
4533 except Exception: # noqa: BLE001 # mirror /spend/logs/ui: failed team lookup falls back to own-logs-only scope
4534 permitted_team_ids = []
4535 if permitted_team_ids:
4536 scope_sql = ' AND ("user" = $4 OR team_id = ANY($5::text[]))'
4537 scope_params = (user_api_key_dict.user_id, permitted_team_ids)
4538 where_conditions = {
4539 "session_id": session_id,
4540 "OR": [
4541 {"user": user_api_key_dict.user_id},
4542 {"team_id": {"in": permitted_team_ids}},
4543 ],
4544 }
4545 else:
4546 scope_sql = ' AND "user" = $4'
4547 scope_params = (user_api_key_dict.user_id,)
4548 where_conditions = {"session_id": session_id, "user": user_api_key_dict.user_id}
4550 # Calculate pagination offsets
4551 skip: Final = (page - 1) * page_size
4553 # Get total count for pagination metadata
4554 total_records: Final = await _count_spend_logs(prisma_client, where_conditions)
4556 # Query with raw SQL to exclude heavy columns (messages, response, proxy_server_request)
4557 sql_query: Final = f"""
4558 SELECT
4559 request_id, call_type, api_key, spend, total_tokens,
4560 prompt_tokens, completion_tokens, "startTime", "endTime",
4561 "completionStartTime", model, model_id, model_group,
4562 custom_llm_provider, api_base, "user", metadata,
4563 cache_hit, cache_key, request_tags, team_id,
4564 organization_id, end_user, requester_ip_address,
4565 session_id, status, mcp_namespaced_tool_name, agent_id
4566 FROM "LiteLLM_SpendLogs"
4567 WHERE session_id = $1{scope_sql}
4568 ORDER BY "startTime" DESC
4569 LIMIT $2 OFFSET $3
4570 """
4571 result: Final[Sequence[Mapping[str, object]]] = await _query_raw(
4572 prisma_client, sql_query, session_id, page_size, skip, *scope_params
4573 )
4574 _hydrate_spend_log_metadata(result)
4576 total_pages: Final = (total_records + page_size - 1) // page_size
4578 return {
4579 "data": result,
4580 "total": total_records,
4581 "page": page,
4582 "page_size": page_size,
4583 "total_pages": total_pages,
4584 }
4585 except Exception as e:
4586 if isinstance(e, HTTPException):
4587 raise e
4588 else:
4589 raise HTTPException(
4590 status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
4591 detail=str(e),
4592 )
4595async def _build_ui_spend_logs_response(
4596 prisma_client: "PrismaClient",
4597 data: list,
4598 total_records: int,
4599 page: int,
4600 page_size: int,
4601 total_pages: int,
4602 enrich_session_counts: bool = True,
4603 total_is_capped: bool = False,
4604) -> dict[str, object]:
4605 """
4606 Build the paginated response for the UI spend-logs endpoint.
4608 When ``enrich_session_counts`` is ``True`` (the default for the v1/UI
4609 endpoint), each row is enriched with ``session_total_count`` plus spend,
4610 token and call-type aggregates so the frontend knows which sessions are
4611 expandable (multi-call sessions). One ``GROUP BY (session_id, api_key)``
4612 query serves every referenced session, keyed per api key so two callers
4613 reusing a session id never see each other's totals. Rows without a
4614 ``session_id`` default to ``1``.
4616 When ``enrich_session_counts`` is ``False`` (v2 endpoint), rows are
4617 serialised without the extra query.
4619 Args:
4620 prisma_client: The connected Prisma client instance.
4621 data: A list of Prisma model instances (must support ``.model_dump()``
4622 and have a ``session_id`` attribute).
4623 total_records: Total number of matching records (for pagination).
4624 page: Current page number.
4625 page_size: Number of items per page.
4626 total_pages: Total number of pages.
4627 enrich_session_counts: Whether to add ``session_total_count`` to each
4628 row. Defaults to ``True``.
4629 total_is_capped: Whether ``total_records`` was clamped to the
4630 pagination count cap (there are more matching rows than the cap).
4632 Returns:
4633 A dict with ``data`` (enriched rows), ``total``, ``page``,
4634 ``page_size``, ``total_pages``, and ``total_is_capped``.
4635 """
4636 if enrich_session_counts:
4637 session_ids: Final[Sequence[str | None]] = list(
4638 {
4639 (row.get("session_id") if isinstance(row, dict) else getattr(row, "session_id", None))
4640 for row in data
4641 if (row.get("session_id") if isinstance(row, dict) else getattr(row, "session_id", None))
4642 }
4643 )
4645 session_spend_map: _SessionSpendMap = {}
4646 if enrich_session_counts and session_ids:
4647 from prisma.errors import PrismaError
4649 try:
4650 # Collect api_keys already present in the authorized page rows so the
4651 # aggregate is scoped to the same ownership as the main query — prevents
4652 # cross-tenant disclosure via a colliding session_id.
4653 authorized_api_keys: Final[Sequence[str | None]] = list(
4654 {
4655 (row.get("api_key") if isinstance(row, dict) else getattr(row, "api_key", None))
4656 for row in data
4657 if (row.get("api_key") if isinstance(row, dict) else getattr(row, "api_key", None)) is not None
4658 }
4659 )
4660 rows: Final[Sequence[_SessionSpendRow]] = await _query_raw(
4661 prisma_client,
4662 f"""
4663 SELECT s.*, COALESCE(m.session_models, ARRAY[]::text[]) AS session_models
4664 FROM (
4665 SELECT session_id, api_key,
4666 COUNT(*)::int AS session_total_count,
4667 COALESCE(SUM(spend), 0)::double precision AS session_total_spend,
4668 COALESCE(SUM(
4669 COALESCE(
4670 request_duration_ms,
4671 (EXTRACT(EPOCH FROM ("endTime" - "startTime")) * 1000)::INTEGER
4672 )
4673 ), 0)::bigint AS session_total_duration_ms,
4674 COUNT(*) FILTER (
4675 WHERE call_type IN {_MCP_CALL_TYPES_SQL}
4676 )::int AS mcp_tool_call_count,
4677 COALESCE(SUM(spend) FILTER (
4678 WHERE call_type IN {_MCP_CALL_TYPES_SQL}
4679 ), 0)::double precision AS mcp_tool_call_spend,
4680 COUNT(*) FILTER (WHERE LOWER(cache_hit) = 'true')::int AS session_cache_hit_count,
4681 COUNT(*) FILTER (
4682 WHERE call_type NOT IN {_MCP_CALL_TYPES_SQL} AND call_type != {_AGENT_CALL_TYPE_SQL}
4683 )::int AS session_llm_count,
4684 COUNT(*) FILTER (WHERE call_type = {_AGENT_CALL_TYPE_SQL})::int AS session_agent_count,
4685 COALESCE(SUM(prompt_tokens), 0)::bigint AS session_total_prompt_tokens,
4686 COALESCE(SUM(completion_tokens), 0)::bigint AS session_total_completion_tokens,
4687 COALESCE(SUM(total_tokens), 0)::bigint AS session_total_tokens
4688 FROM "LiteLLM_SpendLogs"
4689 WHERE session_id = ANY($1::text[])
4690 AND api_key = ANY($2::text[])
4691 GROUP BY session_id, api_key
4692 ) s
4693 LEFT JOIN LATERAL (
4694 SELECT ARRAY_AGG(d.model ORDER BY d.model) AS session_models
4695 FROM (
4696 SELECT DISTINCT LEFT(model, $3::int) AS model
4697 FROM "LiteLLM_SpendLogs"
4698 WHERE session_id = s.session_id
4699 AND api_key = s.api_key
4700 AND model IS NOT NULL AND model <> ''
4701 ORDER BY 1
4702 LIMIT $4::int
4703 ) d
4704 ) m ON TRUE
4705 """,
4706 session_ids,
4707 authorized_api_keys,
4708 _SESSION_MODEL_NAME_MAX_LEN,
4709 _SESSION_MODELS_LIMIT + 1,
4710 )
4711 session_spend_map = {
4712 (row["session_id"], row["api_key"]): _SessionSpendStats(
4713 session_total_count=int(row.get("session_total_count") or 0),
4714 session_total_spend=float(row.get("session_total_spend") or 0.0),
4715 session_total_duration_ms=int(row.get("session_total_duration_ms") or 0),
4716 mcp_tool_call_count=int(row.get("mcp_tool_call_count") or 0),
4717 mcp_tool_call_spend=float(row.get("mcp_tool_call_spend") or 0.0),
4718 session_cache_hit_count=int(row.get("session_cache_hit_count") or 0),
4719 session_llm_count=int(row.get("session_llm_count") or 0),
4720 session_agent_count=int(row.get("session_agent_count") or 0),
4721 session_total_prompt_tokens=int(row.get("session_total_prompt_tokens") or 0),
4722 session_total_completion_tokens=int(row.get("session_total_completion_tokens") or 0),
4723 session_total_tokens=int(row.get("session_total_tokens") or 0),
4724 session_models=models[:_SESSION_MODELS_LIMIT],
4725 session_models_truncated=len(models) > _SESSION_MODELS_LIMIT,
4726 )
4727 for row in rows
4728 if row.get("session_id") and row.get("api_key") is not None
4729 for models in (TypeAdapter(list[str]).validate_python(row.get("session_models") or ()),)
4730 }
4731 except PrismaError:
4732 verbose_proxy_logger.debug(
4733 "Failed to enrich session spend aggregates for spend logs UI",
4734 exc_info=True,
4735 )
4737 if enrich_session_counts:
4738 enriched: Final[list[dict]] = []
4739 for row in data:
4740 row_dict = dict(row) if isinstance(row, dict) else row.model_dump()
4741 sid = row_dict.get("session_id")
4742 row_api_key = row_dict.get("api_key")
4743 session_stats = session_spend_map.get((sid, row_api_key)) if sid and row_api_key is not None else None
4744 row_dict["session_total_count"] = session_stats.session_total_count if session_stats else 1
4745 if session_stats:
4746 row_dict["session_total_spend"] = session_stats.session_total_spend
4747 row_dict["session_total_duration_ms"] = session_stats.session_total_duration_ms
4748 if session_stats.mcp_tool_call_count:
4749 row_dict["mcp_tool_call_count"] = session_stats.mcp_tool_call_count
4750 row_dict["mcp_tool_call_spend"] = session_stats.mcp_tool_call_spend
4751 row_dict["session_cache_hit_count"] = session_stats.session_cache_hit_count
4752 row_dict["session_llm_count"] = session_stats.session_llm_count
4753 row_dict["session_agent_count"] = session_stats.session_agent_count
4754 row_dict["session_total_prompt_tokens"] = session_stats.session_total_prompt_tokens
4755 row_dict["session_total_completion_tokens"] = session_stats.session_total_completion_tokens
4756 row_dict["session_total_tokens"] = session_stats.session_total_tokens
4757 row_dict["session_models"] = session_stats.session_models
4758 row_dict["session_models_truncated"] = session_stats.session_models_truncated
4759 enriched.append(row_dict)
4760 response_data: list = enriched
4761 else:
4762 # v2 path: return raw Prisma model instances so FastAPI applies its
4763 # own Pydantic-aware serialisation (preserves alias handling, custom
4764 # serializers, etc.).
4765 response_data = data
4767 return {
4768 "data": response_data,
4769 "total": total_records,
4770 "page": page,
4771 "page_size": page_size,
4772 "total_pages": total_pages,
4773 "total_is_capped": total_is_capped,
4774 }
4777def _build_status_filter_condition(status_filter: str | None) -> Mapping[str, object]:
4778 """
4779 Helper function to build the status filter condition for database queries.
4781 Args:
4782 status_filter (Optional[str]): The status to filter by. Can be "success" or "failure".
4784 Returns:
4785 Mapping[str, object]: A mapping containing the status filter condition.
4786 """
4787 if status_filter is None:
4788 return {}
4790 if status_filter == "success":
4791 return {"OR": [{"status": {"equals": "success"}}, {"status": None}]}
4792 else:
4793 return {"status": {"equals": status_filter}}
4796def _span_type_sql_condition(span_type: str | None) -> str | None:
4797 if span_type is None:
4798 return None
4799 return _SPAN_TYPE_SQL_CONDITIONS.get(span_type)
4802def _is_admin_view_safe(user_api_key_dict: UserAPIKeyAuth) -> bool:
4803 """
4804 Safely determine if the current user has admin view permissions.
4805 Defaults to False on any exception.
4806 """
4807 try:
4808 user_role: Final = getattr(user_api_key_dict, "user_role", None)
4809 if user_role is None: 4809 ↛ 4810line 4809 didn't jump to line 4810 because the condition on line 4809 was never true
4810 return False
4811 return user_role in (
4812 LitellmUserRoles.PROXY_ADMIN,
4813 LitellmUserRoles.PROXY_ADMIN_VIEW_ONLY,
4814 )
4815 except Exception:
4816 return False
4819async def _can_team_member_view_log(
4820 prisma_client: PrismaClient,
4821 user_api_key_dict: UserAPIKeyAuth,
4822 team_id: str | None,
4823) -> bool:
4824 """
4825 Check if the requesting user can view spend logs for the given team.
4826 Returns True if the team exists and the user is either a team admin or
4827 a team member with the ``/spend/logs`` permission.
4828 """
4829 from litellm.proxy.management_endpoints.common_utils import (
4830 _is_user_team_admin,
4831 _team_member_has_permission,
4832 )
4834 if team_id is None:
4835 return False
4836 team_row: Final = await _find_team_row(prisma_client, team_id)
4837 if team_row is None:
4838 return False
4839 team_obj: Final = LiteLLM_TeamTable.model_validate(team_row.model_dump())
4840 if _is_user_team_admin(user_api_key_dict=user_api_key_dict, team_obj=team_obj):
4841 return True
4842 return _team_member_has_permission(
4843 user_api_key_dict=user_api_key_dict,
4844 team_obj=team_obj,
4845 permission=KeyManagementRoutes.SPEND_LOGS.value,
4846 )
4849def _can_user_view_spend_log(user_api_key_dict: UserAPIKeyAuth) -> bool:
4850 """
4851 Check if the requesting user can view their own spend logs.
4852 """
4853 user_role: Final = user_api_key_dict.user_role
4854 user_id: Final = user_api_key_dict.user_id
4855 return (
4856 user_role
4857 in (
4858 LitellmUserRoles.INTERNAL_USER,
4859 LitellmUserRoles.INTERNAL_USER_VIEW_ONLY,
4860 )
4861 and user_id is not None
4862 )
4865async def _user_can_view_spend_log_owner(
4866 prisma_client: PrismaClient,
4867 user_api_key_dict: UserAPIKeyAuth,
4868 owner_user: str | None,
4869 owner_team_id: str | None,
4870) -> bool:
4871 if owner_user is not None and owner_user == user_api_key_dict.user_id:
4872 return True
4873 if owner_team_id:
4874 return await _can_team_member_view_log(
4875 prisma_client=prisma_client,
4876 user_api_key_dict=user_api_key_dict,
4877 team_id=owner_team_id,
4878 )
4879 return False
4882def _spend_log_forbidden(request_id: str) -> HTTPException:
4883 return HTTPException(
4884 status_code=status.HTTP_403_FORBIDDEN,
4885 detail={"error": f"Not authorized to view spend log for request_id={request_id}"},
4886 )
4889async def _assert_user_can_view_request_id(
4890 prisma_client: PrismaClient,
4891 user_api_key_dict: UserAPIKeyAuth,
4892 request_id: str,
4893) -> None:
4894 """
4895 Verify the requesting non-admin user is allowed to view at least one spend-log
4896 row identified by ``request_id`` or ``litellm_call_id``. The latter is
4897 client-settable, so an id lookup can match rows across different tenants; the
4898 data queries scope a non-admin's results to rows they own directly or via a
4899 permitted team, so a colliding foreign row can neither be served nor deny the
4900 caller their own. Raises HTTP 403 when none of the matching rows is theirs to
4901 view, including when no row exists at all (e.g. it was pruned by retention),
4902 so a missing row can't be used to read a payload out of cold storage via the
4903 detail endpoint.
4904 """
4905 owners: Final = await _find_spend_log_owners(prisma_client, request_id)
4906 for owner in owners:
4907 if await _user_can_view_spend_log_owner(prisma_client, user_api_key_dict, owner["user"], owner["team_id"]):
4908 return
4909 raise _spend_log_forbidden(request_id)
4912@dataclass(frozen=True, slots=True)
4913class _SpendLogViewer:
4914 user_id: str | None
4915 team_ids: tuple[str, ...]
4918async def _spend_log_viewer(prisma_client: PrismaClient, user_api_key_dict: UserAPIKeyAuth) -> _SpendLogViewer:
4919 return _SpendLogViewer(
4920 user_id=user_api_key_dict.user_id,
4921 team_ids=await _get_permitted_team_ids_for_spend_logs_or_empty(
4922 prisma_client=prisma_client,
4923 user_api_key_dict=user_api_key_dict,
4924 ),
4925 )
4928def _viewer_scope_clause(viewer: _SpendLogViewer | None) -> tuple[str, tuple[object, ...]]:
4929 match viewer:
4930 case None:
4931 return ("", ())
4932 case _SpendLogViewer(user_id=user_id, team_ids=()):
4933 return (' AND "user" = $2', (user_id,))
4934 case _SpendLogViewer(user_id=user_id, team_ids=team_ids):
4935 return (' AND ("user" = $2 OR team_id = ANY($3::text[]))', (user_id, team_ids))
4938def _spend_log_payload_query(request_id: str, viewer: _SpendLogViewer | None) -> tuple[str, tuple[object, ...]]:
4939 """
4940 Fetch the one row an id lookup resolves to, preferring the exact ``request_id``
4941 match over rows that merely carry the id as their client-set ``litellm_call_id``.
4942 A non-admin viewer only ever gets rows they own or rows of a team they may view.
4943 """
4944 scope, scope_params = _viewer_scope_clause(viewer)
4945 return (
4946 f"""
4947 SELECT request_id, messages, response, proxy_server_request, metadata, "user", team_id
4948 FROM "LiteLLM_SpendLogs"
4949 WHERE (request_id = $1 OR litellm_call_id = $1){scope}
4950 ORDER BY (request_id = $1) DESC
4951 LIMIT 1
4952 """,
4953 (request_id, *scope_params),
4954 )
4957async def _resolve_spend_log_payload_row(
4958 prisma_client: PrismaClient,
4959 user_api_key_dict: UserAPIKeyAuth,
4960 request_id: str,
4961 caller_is_admin: bool,
4962) -> Mapping[str, object] | None:
4963 """
4964 Resolve an id lookup to the caller's own spend-log row before any payload
4965 store is consulted. Cold storage is keyed by the provider ``request_id``, so
4966 asking it for the raw lookup id could hand back another tenant's payload when
4967 that id is only the caller's ``litellm_call_id``; the row's stored
4968 ``request_id`` is the key that names the caller's own request.
4969 """
4970 viewer: Final = None if caller_is_admin else await _spend_log_viewer(prisma_client, user_api_key_dict)
4971 sql_query, sql_params = _spend_log_payload_query(request_id, viewer)
4972 rows: Final[Sequence[Mapping[str, object]] | None] = await _query_raw_or_none(prisma_client, sql_query, *sql_params)
4973 if not rows:
4974 return None
4975 if not caller_is_admin:
4976 await _assert_user_owns_fetched_spend_rows(
4977 prisma_client=prisma_client,
4978 user_api_key_dict=user_api_key_dict,
4979 rows=rows,
4980 request_id=request_id,
4981 )
4982 return rows[0]
4985def _stored_request_id(row: Mapping[str, object] | None, lookup_id: str) -> str:
4986 stored: Final = None if row is None else row.get("request_id")
4987 return stored if isinstance(stored, str) else lookup_id
4990def _fetched_row_owner(row: Mapping[str, object]) -> tuple[str | None, str | None]:
4991 user: Final = row.get("user")
4992 team_id: Final = row.get("team_id")
4993 return (
4994 user if isinstance(user, str) else None,
4995 team_id if isinstance(team_id, str) else None,
4996 )
4999async def _assert_user_owns_fetched_spend_rows(
5000 prisma_client: PrismaClient,
5001 user_api_key_dict: UserAPIKeyAuth,
5002 rows: Sequence[Mapping[str, object]],
5003 request_id: str,
5004) -> None:
5005 """
5006 Re-verify ownership on the rows an id lookup actually fetched.
5007 ``_assert_user_can_view_request_id`` and the data query read the table at
5008 different moments, so a foreign row inserted between them could otherwise be
5009 returned even though the pre-check passed. Checking the fetched rows
5010 themselves means no interleaving can return another tenant's row.
5011 """
5012 for user, team_id in frozenset(_fetched_row_owner(row) for row in rows):
5013 if not await _user_can_view_spend_log_owner(prisma_client, user_api_key_dict, user, team_id):
5014 raise _spend_log_forbidden(request_id)
5017def _cold_storage_payload_owner(payload: Mapping[str, object]) -> tuple[str | None, str | None]:
5018 metadata: Final = payload.get("metadata")
5019 if not isinstance(metadata, Mapping):
5020 return (None, None)
5021 owner: Final = cast(Mapping[str, object], metadata) # cast-ok: cold-storage JSON is untyped
5022 user: Final = owner.get("user_api_key_user_id")
5023 team_id: Final = owner.get("user_api_key_team_id")
5024 return (
5025 user if isinstance(user, str) else None,
5026 team_id if isinstance(team_id, str) else None,
5027 )
5030async def _assert_user_owns_cold_storage_payload(
5031 prisma_client: PrismaClient,
5032 user_api_key_dict: UserAPIKeyAuth,
5033 payload: Mapping[str, object],
5034 request_id: str,
5035) -> None:
5036 """
5037 Authorize a cold-storage payload against the owner recorded inside it.
5038 The custom logger reads the payload straight from cold storage, written
5039 independently of the spend-log table and able to outlive its row, so a
5040 request_id lookup could otherwise hand back another tenant's stored payload
5041 when no row exists for the pre-check to catch. Verifying the payload's own
5042 owner closes that gap, and a payload that records no owner fails closed.
5043 """
5044 owner_user, owner_team_id = _cold_storage_payload_owner(payload)
5045 if not await _user_can_view_spend_log_owner(prisma_client, user_api_key_dict, owner_user, owner_team_id):
5046 raise _spend_log_forbidden(request_id)
5049async def _get_permitted_team_ids_for_spend_logs(
5050 prisma_client: PrismaClient,
5051 user_api_key_dict: UserAPIKeyAuth,
5052) -> list[str]:
5053 """
5054 Return team IDs where the user is either a team admin or has the
5055 ``/spend/logs`` permission, allowing them to view team-wide spend logs.
5056 """
5057 # Imported here to avoid circular import: proxy_server imports this module.
5058 from litellm.proxy.auth.auth_checks import get_user_object
5059 from litellm.proxy.management_endpoints.common_utils import (
5060 _is_user_team_admin,
5061 _team_member_has_permission,
5062 )
5063 from litellm.proxy.proxy_server import proxy_logging_obj, user_api_key_cache
5065 user_obj: Final = await get_user_object(
5066 user_id=user_api_key_dict.user_id,
5067 prisma_client=prisma_client,
5068 user_api_key_cache=user_api_key_cache,
5069 user_id_upsert=False,
5070 proxy_logging_obj=proxy_logging_obj,
5071 )
5072 if user_obj is None or not user_obj.teams:
5073 return []
5075 team_rows: Final = await _find_team_rows(prisma_client, user_obj.teams)
5077 permitted: Final[list[str]] = []
5078 for team_row in team_rows:
5079 team_obj = LiteLLM_TeamTable.model_validate(team_row.model_dump())
5080 if _is_user_team_admin(user_api_key_dict=user_api_key_dict, team_obj=team_obj) or _team_member_has_permission(
5081 user_api_key_dict=user_api_key_dict,
5082 team_obj=team_obj,
5083 permission=KeyManagementRoutes.SPEND_LOGS.value,
5084 ):
5085 permitted.append(team_obj.team_id)
5086 return permitted
5089async def _get_permitted_team_ids_for_spend_logs_or_empty(
5090 prisma_client: PrismaClient,
5091 user_api_key_dict: UserAPIKeyAuth,
5092) -> tuple[str, ...]:
5093 """Resolve permitted teams once, falling back to the caller's own-user scope."""
5094 try:
5095 return tuple(
5096 await _get_permitted_team_ids_for_spend_logs(
5097 prisma_client=prisma_client,
5098 user_api_key_dict=user_api_key_dict,
5099 )
5100 )
5101 except Exception:
5102 return ()