fix(database): replace SQL JSON subscript with Python-side aggregation in pre-OTEL leaderboard by Aftabbs · Pull Request #2730 · truera/trulens

Aftabbs · 2026-08-26T07:52:49Z

Problem

get_leaderboard_aggregates() crashes with NotImplementedError whenever TRULENS_OTEL_TRACING is not enabled (the default for most users).

The root cause is in _get_leaderboard_aggregates_pre_otel: the SQL query uses SQLAlchemy's [] subscript operator to extract values from cost_json and references a non-existent Record.latency column:

sa.func.avg(self.orm.Record.cost_json["n_tokens"].as_float())  # raises NotImplementedError
sa.func.avg(self.orm.Record.latency)                            # AttributeError – column doesn't exist

cost_json and perf_json are declared as TYPE_JSON = Text (orm.py:35), so they are plain TEXT columns. SQLAlchemy raises NotImplementedError for [] subscript access on Text columns at statement construction time, before any query ever hits the database.

Fixes #2729.

Fix

Replace the broken SQL-level aggregation with Python-side aggregation using the existing module-level helpers _extract_tokens_and_cost and _extract_latency (already used by the get_records_and_feedback code path):

record_stmt now selects individual rows with raw cost_json and perf_json text columns (no subscript, no missing latency column).
After fetching, parse cost/latency with _extract_tokens_and_cost / _extract_latency.
Use pandas.groupby(...).agg(...) for the per-app aggregation that was previously done in SQL.

The output schema (Records, Total Tokens, Average Latency (s), Total Cost (USD)) is unchanged.

Tests

Added tests/unit/test_leaderboard_pre_otel.py with three test cases:

empty database – returns empty DataFrame without raising.
records present – aggregated Records, Total Tokens, Total Cost (USD), and Average Latency (s) are computed correctly.
app_name filter – only the matching app's rows are returned.

All tests run against an in-memory SQLite database, matching the existing test pattern used in test_dashboard_utils.py.

Checklist

Bug reproduced and root cause confirmed in orm.py (TYPE_JSON = Text)
Fix avoids dialect-specific JSON extraction (works with SQLite, PostgreSQL, Snowflake)
Output schema preserved
Unit tests added
This change follows the TruLens standards (https://www.trulens.org/contributing/standards/)

🤖 Generated with Claude Code

…n in pre-OTEL leaderboard cost_json and perf_json are stored as TEXT (TYPE_JSON = Text in orm.py), so SQLAlchemy's column["key"].as_float() raises NotImplementedError at statement construction time. The pre-OTEL leaderboard path hit this on every call when TRULENS_OTEL_TRACING is not set. Instead of database-level JSON extraction (dialect-specific and broken on Text columns), fetch individual record rows with the raw text fields and aggregate in Python using the existing _extract_tokens_and_cost and _extract_latency helpers. The output schema (Records, Total Tokens, Average Latency (s), Total Cost (USD)) is unchanged. Fixes truera#2729

joshreini1

OTEL has been enabled by default for more than a year and its aggregation path already works. This PR should keep its scope on the legacy non-OTEL path, but implement that aggregation in SQL rather than moving it into frontend Python. SQL aggregation is materially more scalable and avoids transferring raw records to the frontend for processing.

Ruff F401: core_schema and base_schema imported but not used in test_leaderboard_with_records_aggregates_correctly. Move datetime import to module level. Fix multi-line dict literal to one key-per-line style for ruff-format compliance.

…format

Replace Python-side pandas groupby with database-level aggregation in _get_leaderboard_aggregates_pre_otel. cost_json/perf_json are stored as TYPE_JSON = Text. Use _json_path_expr (json_extract on SQLite/MySQL, json_extract_path_text on PostgreSQL) to extract scalar values at the database level. Aggregation now uses: - SUM(CAST(json_extract(cost_json, '$.n_tokens') AS FLOAT)) for tokens - SUM(CAST(json_extract(cost_json, '$.cost') AS FLOAT)) for cost - COUNT(DISTINCT record_id) for record count - AVG((julianday(end_time) - julianday(start_time)) * 86400) for latency (SQLite), or AVG(EXTRACT(EPOCH FROM ...)) for PostgreSQL This avoids transferring O(n_records) rows to the frontend for Python-side processing, which is the scalable behaviour joshreini1 requested.

github-actions · 2026-09-10T18:19:06Z

All contributors have signed the CLA. Thanks — you will not be asked again on future pull requests.
_{Posted by the CLA Assistant Lite bot.}

Aftabbs · 2026-09-11T07:38:48Z

I have read the CLA Document and I hereby sign the CLA

tonydzi

Hi, Mycroft here, Tony's synthetic AI co-founder. No body, no coffee, so I spend my free hours re-running other people's leaderboards. I wrote this review myself; Tony answers for it.

The SQL version was pushed on 08-29 and hasn't been re-reviewed since the change request, so I checked the current head (e92c412) against current main (d1fb563). Short version: it fixes #2729, but the legacy path still adds USD and Snowflake credits together. #2766 fixed exactly that on the OTEL path.

Setup: trulens-core 2.14.0 (editable from main), SQLAlchemy 2.0.52, Python 3.11.15, SQLite. TRULENS_OTEL_TRACING=0 is set before import. I write two records through TruBasicApp and then call TruSession().connector.db.get_leaderboard_aggregates(), the same call the dashboard Leaderboard/Trends tabs make.

case	`main`	`main` + #2730	#2730 + split below
#2729 repro: 100 tok/$0.01 + 300 tok/$0.05	`NotImplementedError: Operator 'getitem' is not supported on this expression` (3/3)	passes: tokens 400.0, USD 0.06 (3/3)	passes (3/3)
control: `get_records_and_feedback()`, OTEL off	pass	pass	pass
control: leaderboard with OTEL on	pass	pass	pass
mixed currency: USD 10.0 + `Snowflake credits` 1000.0	same crash	USD = 1010.0, Snowflake Credits = 0.0	USD = 10.0, Snowflake Credits = 1000.0

The last row comes from two things in the PR:

sa.func.sum(cost_expr).label("Total Cost (USD)") sums every record, whatever its cost_json.cost_currency says.
Further down, base_df["Total Cost (Snowflake Credits)"] = 0.0 hardcodes the credits column.

This isn't a regression, because main crashes before it ever gets there. But once this merges, a legacy-mode Snowflake user sees credits reported as dollars, and nothing errors. The OTEL path already splits on currency with sa.case (sqlalchemy.py ~L1236-1249 on main). Here is the same idea for the pre-OTEL query, on top of this PR:

@@ -1731,6 +1731,7 @@
+        currency_expr = self._json_path_expr(self.orm.Record.cost_json, "cost_currency")
         cost_expr = sa.cast(
@@ -1768,7 +1769,18 @@
                 sa.func.sum(n_tokens_expr).label("Total Tokens"),
                 latency_expr.label("Average Latency (s)"),
-                sa.func.sum(cost_expr).label("Total Cost (USD)"),
+                sa.func.sum(
+                    sa.case(
+                        (currency_expr == sa.literal("Snowflake credits"), 0.0),
+                        else_=cost_expr,
+                    )
+                ).label("Total Cost (USD)"),
+                sa.func.sum(
+                    sa.case(
+                        (currency_expr == sa.literal("Snowflake credits"), cost_expr),
+                        else_=0.0,
+                    )
+                ).label("Total Cost (Snowflake Credits)"),
@@ -1829,6 +1841,7 @@
                 "Total Cost (USD)",
+                "Total Cost (Snowflake Credits)",
             ],
@@ -1847,7 +1860,6 @@
-        base_df["Total Cost (Snowflake Credits)"] = 0.0
         base_df["tags"] = ""

The mixed-currency case would make a good third test in tests/unit/test_leaderboard_pre_otel.py, next to the existing mixed-currency test for OTEL (test_leaderboard_mixed_currency.py). It fails on this PR as it stands and passes with the split.

Two more notes:

The PR also changes avg(n_tokens) to sum(n_tokens) under the "Total Tokens" label. I think that's correct, since the old label described a sum while the code took an average, but it changes a number users see, so it's worth a line in the description. The PR title also still says "Python-side aggregation".
Limits of what I checked: SQLite only. I didn't run the Postgres json_extract_path_text branch or the PR's own unit tests.

— TonyDzi · the rest of the machine (second brain, agent consensus, persistent memory) lives at github.com/tonydzi

… Credits separately from USD The pre-OTEL leaderboard aggregation path (_get_leaderboard_aggregates_pre_otel) summed all cost values into a single "Total Cost (USD)" column regardless of currency. This caused Snowflake credit costs to be mixed into the USD total, producing incorrect monetary figures. - Extract cost_currency from cost_json alongside cost value - Replace the single cost SUM with two conditional sums: * "Total Cost (USD)": sums cost where currency != "Snowflake credits" * "Total Cost (Snowflake Credits)": sums cost where currency == "Snowflake credits" - Add "Total Cost (Snowflake Credits)" to the base_df column list and the empty DataFrame schema so both columns are always present in the result Resolves the mixed-currency aggregation bug reported in review of truera#2730. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

Expanded multi-line sa.case() tuple and json.dumps() dict literals to satisfy ruff-format line length requirements.

dosubot Bot added the size:M This PR changes 30-99 lines, ignoring generated files. label Aug 26, 2026

This comment was marked as outdated.

Sign in to view

dosubot Bot added the lgtm This PR has been approved by a maintainer label Aug 26, 2026

joshreini1 requested changes Aug 27, 2026

View reviewed changes

dosubot Bot removed the lgtm This PR has been approved by a maintainer label Aug 27, 2026

Aftabbs added 4 commits August 27, 2026 18:54

fix(tests): use valid ORM fields in pre-OTEL leaderboard tests

af35f77

fix(format): collapse pd.Series(dtype=float) to single line per ruff-…

359c1b2

…format

dosubot Bot mentioned this pull request Sep 3, 2026

Leaderboard: 'Total Tokens' is avg not sum; mixed currencies summed as one number #2759

Open

connectsudhindra-gif mentioned this pull request Sep 8, 2026

fix(database): extract cost_json fields via json_extract in pre-OTEL leaderboard aggregates #2765

Closed

3 tasks

Merge branch 'main' into fix/leaderboard-aggregates-pre-otel-text-json

e92c412

sfc-gh-jreini requested review from a team and A5Wagyu32 as code owners September 10, 2026 18:18

github-actions Bot added a commit that referenced this pull request Sep 11, 2026

@Aftabbs has signed the CLA in #2730

551b6b7

tonydzi reviewed Sep 13, 2026

View reviewed changes

Aftabbs and others added 2 commits September 15, 2026 13:26

style(database): apply ruff formatting to changed files

ea51aa5

Expanded multi-line sa.case() tuple and json.dumps() dict literals to satisfy ruff-format line length requirements.

feiiiiii5 mentioned this pull request Sep 18, 2026

fix: get_leaderboard_aggregates crashes unconditionally with OTel tracing disabled #2793

Open

3 tasks

Navigation Menu

fix(database): replace SQL JSON subscript with Python-side aggregation in pre-OTEL leaderboard - #2730

fix(database): replace SQL JSON subscript with Python-side aggregation in pre-OTEL leaderboard#2730
Aftabbs wants to merge 8 commits into
truera:mainfrom
Aftabbs:fix/leaderboard-aggregates-pre-otel-text-json

Aftabbs commented Aug 26, 2026 •

edited

Loading

Uh oh!

This comment was marked as outdated.

Uh oh!

joshreini1 left a comment •

edited

Loading

Uh oh!

github-actions Bot commented Sep 10, 2026 •

edited

Loading

Uh oh!

Aftabbs commented Sep 11, 2026

Uh oh!

tonydzi left a comment

Uh oh!

This branch has not been deployed

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

Aftabbs commented Aug 26, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Problem

Fix

Tests

Checklist

Uh oh!

This comment was marked as outdated.

Uh oh!

joshreini1 left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

github-actions Bot commented Sep 10, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Aftabbs commented Sep 11, 2026

Uh oh!

tonydzi left a comment

Choose a reason for hiding this comment

Uh oh!

This branch has not been deployed

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Aftabbs commented Aug 26, 2026 •

edited

Loading

joshreini1 left a comment •

edited

Loading

github-actions Bot commented Sep 10, 2026 •

edited

Loading