Coverage for .venv/lib/python3.13/site-packages/litellm/proxy/db/create_views.py: 72%
97 statements
« prev ^ index » next coverage.py v7.15.2, created at 2026-10-10 12:01 +0000
1from collections.abc import Mapping, Sequence
2from typing import Final, Protocol
4from litellm import verbose_logger
7class SupportsExecuteRaw(Protocol):
8 """The one database operation create_view_tolerating_race needs."""
10 async def execute_raw(self, query: str, *args: object) -> int: ... 10 ↛ exitline 10 didn't return from function 'execute_raw' because
13class SupportsRawQueries(SupportsExecuteRaw, Protocol):
14 """The database operations the view bootstrap needs: probe a relation, then create it."""
16 async def query_raw(self, query: str, *args: object) -> Sequence[Mapping[str, object]]: ... 16 ↛ exitline 16 didn't return from function 'query_raw' because
19# Markers that indicate a view/relation does not yet exist in the database.
20# Keeping these in one place avoids repeating the check across all view blocks
21# and prevents overly broad matches (e.g. bare 'undefined' would also match
22# 'undefined function' or 'column undefined_col referenced in query').
23_VIEW_NOT_FOUND_MARKERS: Final = ("does not exist", "no such table", "undefined table")
25# Markers for the inverse condition: another replica created the view between
26# our existence probe and our CREATE.
27_VIEW_ALREADY_EXISTS_MARKERS: Final = ("already exists", "duplicate object", "duplicate table")
30async def create_view_tolerating_race(db: SupportsExecuteRaw, view_name: str, ddl: str) -> None:
31 """
32 Create a view, treating "a concurrent creator won" as success.
34 Every replica booting against the same fresh database observes the view as
35 absent and issues the CREATE; Postgres fails all but one with a
36 duplicate-object error. The desired end state is still reached, so losing
37 that race is success. Without this, the loser's exception propagates out of
38 a detached startup task and the remaining views are never created.
39 """
40 try:
41 await db.execute_raw(ddl)
42 verbose_logger.debug("%s Created!", view_name)
43 except Exception as e:
44 if not any(marker in str(e).lower() for marker in _VIEW_ALREADY_EXISTS_MARKERS):
45 raise
46 verbose_logger.debug("%s already created by a concurrent replica", view_name)
49async def create_missing_views(db: SupportsRawQueries) -> None:
50 """
51 --------------------------------------------------
52 NOTE: Copy of `litellm/db_scripts/create_views.py`.
53 --------------------------------------------------
54 Checks if the LiteLLM_VerificationTokenView and MonthlyGlobalSpend exists in the user's db.
56 LiteLLM_VerificationTokenView: This view is used for getting the token + team data in user_api_key_auth
58 MonthlyGlobalSpend: This view is used for the admin view to see global spend for this month
60 If the view doesn't exist, one will be created.
61 """
63 try:
64 # Try to select one row from the view
65 await db.query_raw("""SELECT 1 FROM "LiteLLM_VerificationTokenView" LIMIT 1""")
66 verbose_logger.debug("LiteLLM_VerificationTokenView Exists!")
67 except Exception as e:
68 error_msg = str(e).lower()
69 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 69 ↛ 70line 69 didn't jump to line 70 because the condition on line 69 was never true
70 raise
71 # If an error occurs, the view does not exist, so create it
72 await create_view_tolerating_race(
73 db,
74 "LiteLLM_VerificationTokenView",
75 """
76 CREATE VIEW "LiteLLM_VerificationTokenView" AS
77 SELECT
78 v.*,
79 t.spend AS team_spend,
80 t.max_budget AS team_max_budget,
81 t.model_max_budget AS team_model_max_budget,
82 t.tpm_limit AS team_tpm_limit,
83 t.rpm_limit AS team_rpm_limit,
84 t.tpd_limit AS team_tpd_limit,
85 p.project_alias AS project_alias
86 FROM "LiteLLM_VerificationToken" v
87 LEFT JOIN "LiteLLM_TeamTable" t ON v.team_id = t.team_id
88 LEFT JOIN "LiteLLM_ProjectTable" p ON v.project_id = p.project_id;
89 """,
90 )
92 try:
93 await db.query_raw("""SELECT 1 FROM "MonthlyGlobalSpend" LIMIT 1""")
94 verbose_logger.debug("MonthlyGlobalSpend Exists!")
95 except Exception as e:
96 error_msg = str(e).lower()
97 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 97 ↛ 98line 97 didn't jump to line 98 because the condition on line 97 was never true
98 raise
99 sql_query = """
100 CREATE OR REPLACE VIEW "MonthlyGlobalSpend" AS
101 SELECT
102 DATE("startTime") AS date,
103 SUM("spend") AS spend
104 FROM
105 "LiteLLM_SpendLogs"
106 WHERE
107 "startTime" >= (CURRENT_DATE - INTERVAL '30 days')
108 GROUP BY
109 DATE("startTime");
110 """
111 await create_view_tolerating_race(db, "MonthlyGlobalSpend", sql_query)
113 try:
114 await db.query_raw("""SELECT 1 FROM "Last30dKeysBySpend" LIMIT 1""")
115 verbose_logger.debug("Last30dKeysBySpend Exists!")
116 except Exception as e:
117 error_msg = str(e).lower()
118 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 118 ↛ 119line 118 didn't jump to line 119 because the condition on line 118 was never true
119 raise
120 sql_query = """
121 CREATE OR REPLACE VIEW "Last30dKeysBySpend" AS
122 SELECT
123 L."api_key",
124 V."key_alias",
125 V."key_name",
126 SUM(L."spend") AS total_spend
127 FROM
128 "LiteLLM_SpendLogs" L
129 LEFT JOIN
130 "LiteLLM_VerificationToken" V
131 ON
132 L."api_key" = V."token"
133 WHERE
134 L."startTime" >= (CURRENT_DATE - INTERVAL '30 days')
135 GROUP BY
136 L."api_key", V."key_alias", V."key_name"
137 ORDER BY
138 total_spend DESC;
139 """
140 await create_view_tolerating_race(db, "Last30dKeysBySpend", sql_query)
142 try:
143 await db.query_raw("""SELECT 1 FROM "Last30dModelsBySpend" LIMIT 1""")
144 verbose_logger.debug("Last30dModelsBySpend Exists!")
145 except Exception as e:
146 error_msg = str(e).lower()
147 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 147 ↛ 148line 147 didn't jump to line 148 because the condition on line 147 was never true
148 raise
149 sql_query = """
150 CREATE OR REPLACE VIEW "Last30dModelsBySpend" AS
151 SELECT
152 "model",
153 SUM("spend") AS total_spend
154 FROM
155 "LiteLLM_SpendLogs"
156 WHERE
157 "startTime" >= (CURRENT_DATE - INTERVAL '30 days')
158 AND "model" != ''
159 GROUP BY
160 "model"
161 ORDER BY
162 total_spend DESC;
163 """
164 await create_view_tolerating_race(db, "Last30dModelsBySpend", sql_query)
165 try:
166 await db.query_raw("""SELECT 1 FROM "MonthlyGlobalSpendPerKey" LIMIT 1""")
167 verbose_logger.debug("MonthlyGlobalSpendPerKey Exists!")
168 except Exception as e:
169 error_msg = str(e).lower()
170 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 170 ↛ 171line 170 didn't jump to line 171 because the condition on line 170 was never true
171 raise
172 sql_query = """
173 CREATE OR REPLACE VIEW "MonthlyGlobalSpendPerKey" AS
174 SELECT
175 DATE("startTime") AS date,
176 SUM("spend") AS spend,
177 api_key as api_key
178 FROM
179 "LiteLLM_SpendLogs"
180 WHERE
181 "startTime" >= (CURRENT_DATE - INTERVAL '30 days')
182 GROUP BY
183 DATE("startTime"),
184 api_key;
185 """
186 await create_view_tolerating_race(db, "MonthlyGlobalSpendPerKey", sql_query)
187 try:
188 await db.query_raw("""SELECT 1 FROM "MonthlyGlobalSpendPerUserPerKey" LIMIT 1""")
189 verbose_logger.debug("MonthlyGlobalSpendPerUserPerKey Exists!")
190 except Exception as e:
191 error_msg = str(e).lower()
192 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 192 ↛ 193line 192 didn't jump to line 193 because the condition on line 192 was never true
193 raise
194 sql_query = """
195 CREATE OR REPLACE VIEW "MonthlyGlobalSpendPerUserPerKey" AS
196 SELECT
197 DATE("startTime") AS date,
198 SUM("spend") AS spend,
199 api_key as api_key,
200 "user" as "user"
201 FROM
202 "LiteLLM_SpendLogs"
203 WHERE
204 "startTime" >= (CURRENT_DATE - INTERVAL '30 days')
205 GROUP BY
206 DATE("startTime"),
207 "user",
208 api_key;
209 """
210 await create_view_tolerating_race(db, "MonthlyGlobalSpendPerUserPerKey", sql_query)
212 try:
213 await db.query_raw("""SELECT 1 FROM "DailyTagSpend" LIMIT 1""")
214 verbose_logger.debug("DailyTagSpend Exists!")
215 except Exception as e:
216 error_msg = str(e).lower()
217 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 217 ↛ 218line 217 didn't jump to line 218 because the condition on line 217 was never true
218 raise
219 sql_query = """
220 CREATE OR REPLACE VIEW "DailyTagSpend" AS
221 SELECT
222 jsonb_array_elements_text(request_tags) AS individual_request_tag,
223 DATE(s."startTime") AS spend_date,
224 COUNT(*) AS log_count,
225 SUM(spend) AS total_spend
226 FROM "LiteLLM_SpendLogs" s
227 GROUP BY individual_request_tag, DATE(s."startTime");
228 """
229 await create_view_tolerating_race(db, "DailyTagSpend", sql_query)
231 try:
232 await db.query_raw("""SELECT 1 FROM "Last30dTopEndUsersSpend" LIMIT 1""")
233 verbose_logger.debug("Last30dTopEndUsersSpend Exists!")
234 except Exception as e:
235 error_msg = str(e).lower()
236 if not any(marker in error_msg for marker in _VIEW_NOT_FOUND_MARKERS): 236 ↛ 237line 236 didn't jump to line 237 because the condition on line 236 was never true
237 raise
238 sql_query = """
239 CREATE VIEW "Last30dTopEndUsersSpend" AS
240 SELECT end_user, COUNT(*) AS total_events, SUM(spend) AS total_spend
241 FROM "LiteLLM_SpendLogs"
242 WHERE end_user <> '' AND end_user <> user
243 AND "startTime" >= CURRENT_DATE - INTERVAL '30 days'
244 GROUP BY end_user
245 ORDER BY total_spend DESC
246 LIMIT 100;
247 """
248 await create_view_tolerating_race(db, "Last30dTopEndUsersSpend", sql_query)
251async def should_create_missing_views(db: SupportsRawQueries) -> bool:
252 """
253 Run only on first time startup.
255 If SpendLogs table already has values, then don't create views on startup.
256 """
258 sql_query: Final = """
259 SELECT reltuples::BIGINT
260 FROM pg_class
261 WHERE oid = '"LiteLLM_SpendLogs"'::regclass;
262 """
264 result: Final = await db.query_raw(query=sql_query)
266 verbose_logger.debug("Estimated Row count of LiteLLM_SpendLogs = %s", result)
267 if ( 267 ↛ 279line 267 didn't jump to line 279 because the condition on line 267 was always true
268 result
269 and isinstance(result, list)
270 and len(result) > 0
271 and isinstance(result[0], dict)
272 and "reltuples" in result[0]
273 and result[0]["reltuples"] is not None
274 and (result[0]["reltuples"] == 0 or result[0]["reltuples"] == -1)
275 ):
276 verbose_logger.debug("Should create views")
277 return True
279 return False