Trader stats silently truncate wallets above 10,000 trades and report partial history as totals

[Bayyan16]

Classification

Field	Value
Status	Confirmed
Category	Calculation/data-integrity error; performance
Affected endpoint	GET /api/trader/:wallet/stats
Affected UI	Trade statistics panel
Audited branch	playground
Audited commit	41bb1304705cd7652b49ef7a4303454d1646415c
Validation date	2026-08-12

Executive summary

GET /api/trader/:wallet/stats promises aggregate statistics and returns fields named totalTrades, totalVolume, totalFees, uniqueMarkets, firstTradeAt, and lastTradeAt. Both database backends, however, fetch at most the oldest 10,000 matching trade rows before the application computes those values.

For any wallet with more than 10,000 trades, the endpoint returns HTTP 200 with a structurally valid but incomplete response. It does not expose a truncated flag, cursor, warning, or time range. The UI consequently presents partial values as exact totals.

The implementation is also unnecessarily expensive: every cache miss transfers and materializes up to 10,000 rows so JavaScript can reduce them into one aggregate object. The database can compute the same values over the full matching history and return a single row.

Affected code

• Public response contract: app/app/api/trader/[wallet]/stats/route.ts:7-32
• JavaScript aggregation: app/app/api/trader/[wallet]/stats/route.ts:35-76
• Local-indexer call site: app/app/api/trader/[wallet]/stats/route.ts:101-108
• Supabase primary and fallback limits: app/app/api/trader/[wallet]/stats/route.ts:115-150
• Local-indexer SQL limit: app/lib/indexer-db.ts:447-475
• Client fetch: app/hooks/useTraderStats.ts:25-63
• UI labels partial values as totals: app/components/trade/TradeStatsPanel.tsx:137-175

Correctness invariant

Fields presented as totals must aggregate the full defined dataset. If a response is intentionally sampled or truncated, that limitation must be explicit in both the API contract and the UI.

The current response has no bounded-window definition and no partial-data marker, so a caller reasonably interprets totalTrades and the other fields as all-history statistics.

The original feature PR provides independent product-intent evidence. PR #832 describes the endpoint as “aggregating the trades table,” lists Total Trades, and defines lastTradeAt as the most recent trade. The implementation comment that 10,000 rows are “sufficient for any realistic trader history” explains why the safeguard was added, but it does not redefine the API contract or add truncation metadata. The finding is therefore not that a performance guard exists; it is that a partial slice is returned and displayed as an exact aggregate.

Root cause

The local-indexer path documents and implements a hard row cap:

SELECT side, size::text AS size, price::text AS price,
       fee::text AS fee, slab_address, created_at
FROM trades
WHERE trader = $wallet
ORDER BY created_at ASC
LIMIT 10000

Both Supabase variants make the same decision:

.order("created_at", { ascending: true })
.limit(10_000)

The application then treats the returned slice as the complete dataset:

return {
  totalTrades: rows.length,
  longTrades,
  shortTrades,
  totalVolume: totalVolume.toString(),
  totalFees: totalFees.toString(),
  uniqueMarkets: markets.size,
  firstTradeAt,
  lastTradeAt,
};

Because the queries sort ascending before applying the limit, they retain the oldest 10,000 trades. Every later trade is omitted. This makes lastTradeAt particularly misleading: once the threshold is crossed, it stops advancing even though new trades continue to occur.

Preconditions and reachability

The bug is triggered whenever a wallet has more than 10,000 matching rows in trades for the selected query scope. No malformed input or attacker action is required.

This threshold is plausible for:

• market-making wallets;
• automated trading bots;
• high-frequency strategy wallets;
• shared operational wallets;
• long-lived active accounts.

The local indexer and Supabase fallback are both affected, so changing backend availability does not restore correctness.

Deterministic PoC

The following safe local harness constructs 10,001 ordered rows, applies the same ascending 10,000-row backend boundary, and then computes the directly affected fields using the route's aggregation semantics:

const fullHistory = Array.from({ length: 10_001 }, (_, i) => ({
  side: i % 2 ? "long" : "short",
  size: "1",
  price: "1",
  fee: "1",
  slab_address: `market-${i}`,
  created_at: String(i + 1),
}));

// Equivalent boundary to ORDER BY created_at ASC LIMIT 10000.
const rowsReturnedByBackend = fullHistory.slice(0, 10_000);

function aggregateLikeRoute(rows) {
  return {
    totalTrades: rows.length,
    uniqueMarkets: new Set(rows.map((row) => row.slab_address)).size,
    firstTradeAt: rows[0]?.created_at ?? null,
    lastTradeAt: rows.at(-1)?.created_at ?? null,
  };
}

console.log(JSON.stringify({
  expected: aggregateLikeRoute(fullHistory),
  actual: aggregateLikeRoute(rowsReturnedByBackend),
}, null, 2));

Observed output:

{
  "expected": {
    "totalTrades": 10001,
    "uniqueMarkets": 10001,
    "firstTradeAt": "1",
    "lastTradeAt": "10001"
  },
  "actual": {
    "totalTrades": 10000,
    "uniqueMarkets": 10000,
    "firstTradeAt": "1",
    "lastTradeAt": "10000"
  }
}

This one-row overflow already produces incorrect totals. With N > 10,000 matching rows, the trade-count underreporting is N - 10,000, and all omitted rows are absent from the remaining aggregates.

Independent SQL-boundary reproduction

To avoid relying only on a JavaScript model of the query, a second PoC was run against an in-memory SQL database. It inserts 10,001 ordered rows, compares a full database aggregate with aggregation after ORDER BY created_at ASC LIMIT 10000, and does not access Supabase or production data:

import sqlite3, json

conn = sqlite3.connect(":memory:")
conn.execute(
    "CREATE TABLE trades (created_at INTEGER NOT NULL, slab_address TEXT NOT NULL)"
)
conn.executemany(
    "INSERT INTO trades VALUES (?, ?)",
    ((i, f"market-{i}") for i in range(1, 10002)),
)

full = conn.execute("""
    SELECT COUNT(*), COUNT(DISTINCT slab_address),
           MIN(created_at), MAX(created_at)
    FROM trades
""").fetchone()

capped = conn.execute("""
    SELECT COUNT(*), COUNT(DISTINCT slab_address),
           MIN(created_at), MAX(created_at)
    FROM (
      SELECT * FROM trades ORDER BY created_at ASC LIMIT 10000
    )
""").fetchone()

print(json.dumps({
    "database_expected_full_aggregate": full,
    "current_query_boundary_then_aggregate": capped,
    "undercount": full[0] - capped[0],
}))

Observed output:

{
  "database_expected_full_aggregate": [10001, 10001, 1, 10001],
  "current_query_boundary_then_aggregate": [10000, 10000, 1, 10000],
  "undercount": 1
}

SQLite is used only as a deterministic local SQL harness; the relevant ORDER BY ... LIMIT semantics are the same as the PostgreSQL/PostgREST query boundary at issue. The source itself independently proves that both production data paths apply the same limit before JavaScript aggregation.

Fields affected

Field	Effect after 10,000 rows
totalTrades	Hard-capped at 10,000 instead of the actual count
longTrades	Counts only the first 10,000 rows
shortTrades	Counts only the first 10,000 rows
totalVolume	Omits volume from every later trade
totalFees	Omits fees from every later trade
uniqueMarkets	Omits markets first encountered after row 10,000
firstTradeAt	Usually remains correct because the slice contains the oldest rows
lastTradeAt	Becomes the timestamp of row 10,000, not the latest trade

The UI also derives the long/short percentage from the truncated counters, so that percentage may differ from the wallet's actual all-history distribution.

Actual behavior

• The endpoint returns HTTP 200 and a normal-looking TraderStatsResponse.
• The response does not disclose that only 10,000 rows were considered.
• The client stores the response as authoritative stats.
• The UI labels the values “Total Trades,” “Volume Traded,” “Fees Paid,” and “Long / Short Split.”
• After the threshold is crossed, recent activity may not change any displayed value because the queries retain the oldest rows.

Expected behavior

• Aggregate fields should be calculated over every matching trade row in the defined scope.
• The database should return one aggregate result rather than thousands of raw rows.
• lastTradeAt should be the true maximum timestamp.
• If the product intentionally chooses an approximate or bounded result, the response must expose that fact and the UI must not label it as an exact total.

Performance impact

For every cache miss, the current route can:

1. scan and return up to 10,000 raw database rows;
2. transfer all selected columns to the application;
3. convert each field to strings/objects in the Supabase path;
4. allocate a JavaScript Set for market addresses;
5. perform BigInt/Number conversions for every row;
6. reduce the entire array to one response object.

The response cache (s-maxage=30) reduces repeated work for an identical URL, but it does not eliminate cold requests, distinct wallets, revalidation, or multi-instance execution. Database-side aggregation reduces both transfer size and application memory while also fixing the truncation.

Severity rationale

Severity is Low after conservative recalibration:

Factor	Assessment
Impact	User-facing activity analytics are understated; no trade, balance, position, authorization decision, or on-chain state is changed
Likelihood	Requires more than 10,000 matching trades for one wallet; plausible for bots and long-lived market makers but uncommon for ordinary users
Reach	Affects only wallets above the boundary and only consumers of this statistics response
Detectability	Poor in the UI because the response is successful and has no truncation marker; maintainers can detect it with a count query
Recovery	No stored data is corrupted; correcting the aggregate query immediately restores correct output

The finding is still substantive: every advertised aggregate can become wrong and lastTradeAt can stop advancing. However, the trigger is a high-volume boundary and the consequence is limited to analytics presentation. Without evidence that this response drives financial execution or authorization, Medium would overstate the demonstrated impact.

Recommended fix

Move aggregation to the database and return a single aggregate row. For the local Postgres path, use expressions equivalent to:

SELECT
  COUNT(*) AS total_trades,
  COUNT(*) FILTER (WHERE side = 'long') AS long_trades,
  COUNT(*) FILTER (WHERE side <> 'long') AS short_trades,
  COUNT(DISTINCT slab_address) AS unique_markets,
  MIN(created_at) AS first_trade_at,
  MAX(created_at) AS last_trade_at,
  -- Use the schema's exact numeric units for these expressions.
  SUM(/* exact volume expression */) AS total_volume,
  SUM(/* exact fee expression */) AS total_fees
FROM trades
WHERE trader = $wallet;

Implementation guidance:

1. Implement a dedicated aggregate query for the local indexer.
2. Implement an equivalent Supabase RPC/database function or aggregate view so the fallback has the same contract.
3. Preserve exact integer/numeric types through database aggregation.
4. Serialize values that may exceed JavaScript's safe integer range as decimal strings, as the current response already does for volume and fees.
5. Define the side classification explicitly; do not accidentally count unexpected values as shorts without validation.
6. Ensure both backends apply the same network scoping rules.
7. Remove the raw-row LIMIT 10000 from the totals path.

If exact aggregation cannot be deployed immediately, a temporary mitigation is to return metadata such as truncated: true, rowsConsidered: 10000, and through: <timestamp> and to change UI labels from totals to partial-history values. That mitigation makes the response honest but does not correct the values or the performance cost.

Suggested regression tests

• Integration fixture with 10,001 trades returns totalTrades = 10001.
• The 10,001st trade updates lastTradeAt.
• A market first used after trade 10,000 increments uniqueMarkets.
• Volume and fees from rows beyond the boundary are included.
• Long/short distribution includes post-boundary rows.
• Local-indexer and Supabase paths return identical values for the same fixture.
• Large exact totals are serialized without JavaScript precision loss.
• Query-shape or integration assertion confirms that only an aggregate row is returned to the application.
• Empty history still returns the existing zero/null response contract.
• Network-filter fallback does not merge or double-count trades from another network.

Existing test gap

trader-stats.test.ts covers an empty wallet and a three-trade fixture. Its mocked query chain stops at .limit(), so it verifies the reducer for small inputs but cannot detect database truncation. No existing test places a trade beyond the 10,000-row boundary or asserts a true database aggregate.

Duplicate analysis

Searches covered open issues, closed issues, pull requests, and commits using exact and broad terms including trader stats, trader-stats, 10,000, 10000, LIMIT 10000, totalTrades, and truncate.

Related items address different defects:

• Issue #833 concerns an in-memory rate-limit map leak.
• PR #832 introduced the feature and establishes the aggregate/Total Trades contract, but neither its issue body nor implementation discloses partial results after the 10,000-row guard.
• PR #1920 adds network-column fallback behavior.
• Issue #2487 and PR #2490 concern distributed rate limiting.
• PR #2288 prevents stale responses during wallet switches.

No matching issue or PR was found for silent 10,000-row truncation of wallet aggregates as of 2026-08-12.

Validation limitations

The snapshot contained no installed dependencies, so the full Next.js/Vitest suite and a real Postgres/Supabase integration environment were unavailable. No dependencies were installed during this read-only audit. The conclusion is supported by:

• identical explicit limits in both backend paths;
• deterministic ordering before the limit;
• direct inspection of the reducer and response contract;
• inspection of the client and UI consumers;
• a deterministic boundary PoC;
• existing-test boundary analysis;
• GitHub issue, PR, and commit deduplication searches.

The missing live database does not create uncertainty about the truncation itself: SQL/PostgREST limit semantics and the reducer's rows.length calculation make the incorrect result unavoidable once more than 10,000 rows match.

Final verdict

Confirmed, High confidence, Low severity. This is a valid new analytics-correctness and performance issue in the audited commit. Both supported data paths are affected, and the user-facing response provides no indication that it is partial.

[dcccrypto]

CONFIRMED, and the truncation was compounded by a mislabelled figure. Fixed in PR #2526.

Trader stats silently truncated above the row cap, so a wallet past the limit got a partial answer presented as a complete one.

Fixed with an explicit TRADER_STATS_MAX_ROWS and a truncated flag on the response, so a consumer can tell a partial result from a whole one.

The part not in the report: while fixing it I found totalTrades was being populated with the capped row count — so a truncated response did not merely omit trades, it reported the cap as if it were the true total. A user at 12,000 trades saw a confident, wrong number rather than a missing one. That is the worse failure, and it only became visible once the truncation was made explicit.

Suite 3094/0.

[dcccrypto]

FIXED by 8d3e280a (PR #2512) — not by me. Closing, and recording why theirs was the better fix.

I had written a fix for this too, and dropped it. Theirs removes the cap entirely by aggregating in the database; mine kept the 10,000-row fetch and added a truncated flag on top of it. Theirs is strictly better: the flag tells you the answer is incomplete, the aggregation makes it complete.

They also found the sharper detail, which I had not:

both queries order by created_at ASC, so the cap kept the OLDEST 10 000 trades. lastTradeAt was therefore not an approximation of the most recent trade — it was the timestamp of the 10 000th oldest one, and it would stop advancing entirely once a wallet passed the cap.

That reframes the bug. I had it as "totals are under-reported past the cap." It is worse: lastTradeAt freezes — a field that looks live and is not, which is the more misleading failure.

One thing the new aggregation missed, now fixed

The replacement query landed without a network predicate, so a wallet's stats summed devnet and mainnet trades into one answer — every field affected. Caught by the #2513 guard, which named the line, and fixed in #2527:

expected [ 'line 526: FROM trades' ] to deeply equal []

Not an argument against the aggregation, which is the right design. An argument for the guard.

Launch suite on merged playground: 3111 passed / 16 skipped / 0 failed.